Re: [HACKERS] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
