Thread: Loss of cluster status

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

> 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

"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

> "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

> 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

"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

> > 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