Hi,

The new cluster is cool in that :

1. It works
2. It saves the indisclustered status

However, after a dump and restore, this indisclustered status will be lost.
Would it be an idea to issue a CLUSTER command after the CREATE TABLE
statement in SQL dumps for tables that have an indisclustered index?

Chris



Fri, 21 Feb 2003 16:10:13 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Loss of cluster status
> The new cluster is cool in that :
>
> 1. It works
> 2. It saves the indisclustered status
>
> However, after a dump and restore, this indisclustered status will be
lost.
> Would it be an idea to issue a CLUSTER command after the CREATE TABLE
> statement in SQL dumps for tables that have an indisclustered index?

Actually, rather than a full-blown CLUSTER, how about...:

UPDATE pg_index SET indisclustered=true WHERE indrelid=(SELECT oid FROM
pg_class WHERE relname='mytable' AND relnamespace = (SELECT oid FROM
pg_namespace WHERE nspname=CURRENT_SCHEMA()));

Hmmm...need something for index name as well tho...

Is that an idea?

Chris



Fri, 21 Feb 2003 16:32:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Loss of cluster status
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Would it be an idea to issue a CLUSTER command after the CREATE TABLE
>> statement in SQL dumps for tables that have an indisclustered index?

Yeah...

> Actually, rather than a full-blown CLUSTER, how about...:

> UPDATE pg_index SET indisclustered=true WHERE indrelid=(SELECT oid FROM
> pg_class WHERE relname='mytable' AND relnamespace = (SELECT oid FROM
> pg_namespace WHERE nspname=CURRENT_SCHEMA()));

No; directly manipulating the system catalogs in dump scripts is a
crummy idea, because (a) it only works if you're superuser, and (b)
it creates a nasty backwards-compatibility problem if we change the
catalogs involved.

A CLUSTER command issued just after table creation, while it's still
empty, would be cheap ... but we don't put the index in place until
we've loaded the data, do we?  Darn.

			regards, tom lane

Fri, 21 Feb 2003 10:27:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Loss of cluster status 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> >> Would it be an idea to issue a CLUSTER command after the CREATE TABLE
> >> statement in SQL dumps for tables that have an indisclustered index?
>
> Yeah...

<snip>

> A CLUSTER command issued just after table creation, while it's
still
> empty, would be cheap ... but we don't put the index in place until
> we've loaded the data, do we?  Darn.

CREATE CLUSTERED INDEX ...?

Chris



Sun, 23 Feb 2003 18:52:33 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Loss of cluster status 
> No; directly manipulating the system catalogs in dump scripts is a
> crummy idea, because (a) it only works if you're superuser, and (b)
> it creates a nasty backwards-compatibility problem if we change the
> catalogs involved.
>
> A CLUSTER command issued just after table creation, while it's still
> empty, would be cheap ... but we don't put the index in place until
> we've loaded the data, do we?  Darn.

Maybe we should issue it after the CREATE INDEX and ADD CONSTRAINT has
occurred and just bite it.  We could have a pg_dump --no-cluster option to
suppress them.   However, we need to guarantee to the user that we restore
their database exactly as they had it.

Other potential problem - ALTER TABLE / SET STORAGE ?

Chris




Mon, 24 Feb 2003 10:21:55 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Loss of cluster status 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> A CLUSTER command issued just after table creation, while it's still
>> empty, would be cheap ... but we don't put the index in place until
>> we've loaded the data, do we?  Darn.

> Maybe we should issue it after the CREATE INDEX and ADD CONSTRAINT has
> occurred and just bite it.

The real problem I think is that we've confused the notion of setting a
policy for CLUSTER (ie, marking the preferred thing to cluster on) with
the notion of actually doing a CLUSTER.  Perhaps we need an ALTER
command that says "this is what to cluster on" without actually doing
it.

> Other potential problem - ALTER TABLE / SET STORAGE ?

Yeah, pg_dump should be dumping that too, probably.

			regards, tom lane

Sun, 23 Feb 2003 21:33:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Loss of cluster status 
> > Maybe we should issue it after the CREATE INDEX and ADD CONSTRAINT has
> > occurred and just bite it.
>
> The real problem I think is that we've confused the notion of setting a
> policy for CLUSTER (ie, marking the preferred thing to cluster on) with
> the notion of actually doing a CLUSTER.  Perhaps we need an ALTER
> command that says "this is what to cluster on" without actually doing
> it.

Hmmm...I don't know if I can be bothered working on that - anyone else want
to do it?

> > Other potential problem - ALTER TABLE / SET STORAGE ?
>
> Yeah, pg_dump should be dumping that too, probably.

I'll do a patch for that then.

Chris



Mon, 24 Feb 2003 11:29:22 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Loss of cluster status 