Re: Dump CLUSTER in pg_dump

Currently, the fact that an index is clustered is not dumped. Unfortunately
the only way of dumping this information is with a cluster statement itself.

One possible improvement would be to sort clustered indexes first and dump
them first, that way the cluster operation does not have to do so much
reindexing.

Example output:

--
-- TOC entry 7 (OID 17078)
-- Name: test_idx; Type: INDEX; Schema: public; Owner: chriskl
--

CREATE INDEX test_idx ON test USING btree (a);

CLUSTER test_idx ON test;

On Thu, Feb 27, 2003 at 02:24:02PM +0800, Christopher Kings-Lynne wrote:
> Currently, the fact that an index is clustered is not dumped. Unfortunately
> the only way of dumping this information is with a cluster statement itself.
>
> One possible improvement would be to sort clustered indexes first and dump
> them first, that way the cluster operation does not have to do so much
> reindexing.

Is this really a good idea? I think the clustering itself should be
done later and manually by the DBA, and the dump should only include a
command to set the indisclustered bit appropiately. What about
inventing a command to only set the bit, maybe

ALTER TABLE <tablename> CLUSTER ON <indexname>

or something like that? I can do that if people thinks it's a good
idea.

(Just returning from vacation and catching up on email).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)

> > One possible improvement would be to sort clustered indexes first and
dump
> > them first, that way the cluster operation does not have to do so much
> > reindexing.
>
> Is this really a good idea? I think the clustering itself should be
> done later and manually by the DBA, and the dump should only include a
> command to set the indisclustered bit appropiately. What about
> inventing a command to only set the bit, maybe

No, it's not - but it's the _only_ way of doing it for 7.3.x pg_dump...

> ALTER TABLE <tablename> CLUSTER ON <indexname>
>
> or something like that? I can do that if people thinks it's a good
> idea.

That's exactly what Tom's idea was - I like that particular syntax though.
It's slightly weird I guess to have the two different syntaxes I guess.

I'll add it to my list :)

Chris

On Fri, Feb 28, 2003 at 10:03:44AM +0800, Christopher Kings-Lynne wrote:

> > ALTER TABLE <tablename> CLUSTER ON <indexname>
> >
> > or something like that? I can do that if people thinks it's a good
> > idea.
>
> That's exactly what Tom's idea was - I like that particular syntax though.
> It's slightly weird I guess to have the two different syntaxes I guess.

Ok, done. The syntax is what I proposed, because no discussion
arised... Let me know what do you think. (Perhaps I got the locking
issues right this time.)

I'm not very SGML literate and haven't been able to build the
documentation in ages, so if there are mistakes in the markup please let
me know.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La vida es para el que se aventura"

On Sun, Mar 09, 2003 at 01:01:15AM -0400, Alvaro Herrera wrote:

> Ok, done. The syntax is what I proposed, because no discussion
> arised... Let me know what do you think. (Perhaps I got the locking
> issues right this time.)

Lucky I forgot to attach, because there was a mistake. Hopefully this
one is good...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

On Sun, Mar 09, 2003 at 01:37:06AM -0400, Alvaro Herrera wrote:
> On Sun, Mar 09, 2003 at 01:01:15AM -0400, Alvaro Herrera wrote:
>
> > Ok, done. The syntax is what I proposed, because no discussion
> > arised... Let me know what do you think. (Perhaps I got the locking
> > issues right this time.)
>
> Lucky I forgot to attach, because there was a mistake. Hopefully this
> one is good...

I have received no comments for this patch. Is it going to be accepted,
rejected, commented on?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)

> I have received no comments for this patch. Is it going to be accepted,
> rejected, commented on?

I think Bruce is just taking time getting through them all. If Alvaro's is
accepted, we'll need to change the syntax my 'dump cluster' patch uses...

Chris

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> On Fri, Feb 28, 2003 at 10:03:44AM +0800, Christopher Kings-Lynne wrote:
>
> > > ALTER TABLE <tablename> CLUSTER ON <indexname>
> > >
> > > or something like that? I can do that if people thinks it's a good
> > > idea.
> >
> > That's exactly what Tom's idea was - I like that particular syntax though.
> > It's slightly weird I guess to have the two different syntaxes I guess.
>
> Ok, done. The syntax is what I proposed, because no discussion
> arised... Let me know what do you think. (Perhaps I got the locking
> issues right this time.)
>
> I'm not very SGML literate and haven't been able to build the
> documentation in ages, so if there are mistakes in the markup please let
> me know.
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "La vida es para el que se aventura"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> On Fri, Feb 28, 2003 at 10:03:44AM +0800, Christopher Kings-Lynne wrote:
>
> > > ALTER TABLE <tablename> CLUSTER ON <indexname>
> > >
> > > or something like that? I can do that if people thinks it's a good
> > > idea.
> >
> > That's exactly what Tom's idea was - I like that particular syntax though.
> > It's slightly weird I guess to have the two different syntaxes I guess.
>
> Ok, done. The syntax is what I proposed, because no discussion
> arised... Let me know what do you think. (Perhaps I got the locking
> issues right this time.)
>
> I'm not very SGML literate and haven't been able to build the
> documentation in ages, so if there are mistakes in the markup please let
> me know.
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "La vida es para el que se aventura"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073