Re: [PATCHES] ALTER INDEX
Gavin Sherry wrote: On Fri, 20 Aug 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: No, but it is a missing capability many will complain about. I can easily remove it. I saw no one comment when I added it to the patches queue. I hadn't seen you add it to the patches queue ... I did see Gavin's submission but did not yet have time to look at the details. What does it *do* exactly --- simply allow INDEX as a substitute for TABLE in the syntax, or more? I'm not thrilled at the idea of adding a lot of duplicate coding for this. I tried to avoid any duplication. The patch still uses all the ALTER TABLE code. Its just a grammar modification and some setting of completion tags. That being said, I felt obliged to provide at patch when I started hearing noise about ALTER TABLE index name being a bit of a hack -- which it is. The issue was that few people currently modify indexes because in the past you could only rename an index or change its owner. With tablespaces, we are going to have lots more people moving indexes around, and I think people were getting confused because there was no ALTER INDEX to move them. I can still back it out and leave it for 8.1 but it probably will reduce confusion and perhaps need for an FAQ, How do I move an index between tablespaces? FYI, I just fixed a typo in the ALTER INDEX manual page that mentioned INDEXSPACE instead of TABLESPACE. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ALTER INDEX
Patch applied. Thanks. I originally thought of this as a feature addition, but I realized that ALTER INDEX is being added because people are going to want to move tablespaces for indexes, and without this, they can't easily. --- Gavin Sherry wrote: This patch has a fix for a 'thought-o' in the docs. Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ALTER INDEX
I have made this adjustment. --- Stefan Kaltenbrunner wrote: Gavin Sherry wrote: Index: src/bin/psql/tab-complete.c === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v retrieving revision 1.109 diff -2 -c -r1.109 tab-complete.c *** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 - 1.109 --- src/bin/psql/tab-complete.c 13 Aug 2004 06:34:55 - *** *** 633,637 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, NULL}; COMPLETE_WITH_LIST(list_ALTER); --- 633,638 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, INDEX, !NULL}; COMPLETE_WITH_LIST(list_ALTER); *** *** 647,650 --- 648,661 COMPLETE_WITH_LIST(list_ALTERDATABASE); } + /* ALTER INDEX name */ + else if (pg_strcasecmp(prev3_wd, ALTER) == 0 + pg_strcasecmp(prev2_wd, INDEX) == 0) + { + static const char *const list_ALTERDATABASE[] = + {SET TABLESPACE, OWNER TO, RENAME TO, NULL}; + + COMPLETE_WITH_LIST(list_ALTERDATABASE); minor issue/nit(?): reusing list_ALTERDATABASE for the ALTER INDEX part looks a little strange ... Stefan(who could really need some feedback on his own tab-complete patch *g*) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ALTER INDEX
On Fri, 20 Aug 2004, Bruce Momjian wrote: Patch applied. Thanks. I originally thought of this as a feature addition, but I realized that ALTER INDEX is being added because people are going to want to move tablespaces for indexes, and without this, they can't easily. Which would fall under adding a feature onto the tablespaces, not fixing a bug in tablespaces itself ... does *not* having ALTER INDEX *break* tablespaces? Causes it not to work, or not build? --- Gavin Sherry wrote: This patch has a fix for a 'thought-o' in the docs. Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ALTER INDEX
Marc G. Fournier wrote: On Fri, 20 Aug 2004, Bruce Momjian wrote: Patch applied. Thanks. I originally thought of this as a feature addition, but I realized that ALTER INDEX is being added because people are going to want to move tablespaces for indexes, and without this, they can't easily. Which would fall under adding a feature onto the tablespaces, not fixing a bug in tablespaces itself ... does *not* having ALTER INDEX *break* tablespaces? Causes it not to work, or not build? No, but it is a missing capability many will complain about. I can easily remove it. I saw no one comment when I added it to the patches queue. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ALTER INDEX
Bruce Momjian [EMAIL PROTECTED] writes: No, but it is a missing capability many will complain about. I can easily remove it. I saw no one comment when I added it to the patches queue. I hadn't seen you add it to the patches queue ... I did see Gavin's submission but did not yet have time to look at the details. What does it *do* exactly --- simply allow INDEX as a substitute for TABLE in the syntax, or more? I'm not thrilled at the idea of adding a lot of duplicate coding for this. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ALTER INDEX
On Fri, 20 Aug 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: No, but it is a missing capability many will complain about. I can easily remove it. I saw no one comment when I added it to the patches queue. I hadn't seen you add it to the patches queue ... I did see Gavin's submission but did not yet have time to look at the details. What does it *do* exactly --- simply allow INDEX as a substitute for TABLE in the syntax, or more? I'm not thrilled at the idea of adding a lot of duplicate coding for this. I tried to avoid any duplication. The patch still uses all the ALTER TABLE code. Its just a grammar modification and some setting of completion tags. That being said, I felt obliged to provide at patch when I started hearing noise about ALTER TABLE index name being a bit of a hack -- which it is. Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ALTER INDEX
Your adjustment has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Stefan Kaltenbrunner wrote: Gavin Sherry wrote: Index: src/bin/psql/tab-complete.c === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v retrieving revision 1.109 diff -2 -c -r1.109 tab-complete.c *** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 - 1.109 --- src/bin/psql/tab-complete.c 13 Aug 2004 06:34:55 - *** *** 633,637 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, NULL}; COMPLETE_WITH_LIST(list_ALTER); --- 633,638 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, INDEX, !NULL}; COMPLETE_WITH_LIST(list_ALTER); *** *** 647,650 --- 648,661 COMPLETE_WITH_LIST(list_ALTERDATABASE); } + /* ALTER INDEX name */ + else if (pg_strcasecmp(prev3_wd, ALTER) == 0 + pg_strcasecmp(prev2_wd, INDEX) == 0) + { + static const char *const list_ALTERDATABASE[] = + {SET TABLESPACE, OWNER TO, RENAME TO, NULL}; + + COMPLETE_WITH_LIST(list_ALTERDATABASE); minor issue/nit(?): reusing list_ALTERDATABASE for the ALTER INDEX part looks a little strange ... Stefan(who could really need some feedback on his own tab-complete patch *g*) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] ALTER INDEX
This patch has a fix for a 'thought-o' in the docs. Gavin Index: doc/src/sgml/ref/alter_index.sgml === RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_index.sgml,v retrieving revision 1.1 diff -2 -c -r1.1 alter_index.sgml *** doc/src/sgml/ref/alter_index.sgml 13 Aug 2004 04:32:27 - 1.1 --- doc/src/sgml/ref/alter_index.sgml 13 Aug 2004 06:35:17 - *** *** 0 --- 1,188 + !-- + $PostgreSQL$ + PostgreSQL documentation + -- + + refentry id=SQL-ALTERINDEX + refmeta + refentrytitle id=sql-alterindex-titleALTER INDEX/refentrytitle + refmiscinfoSQL - Language Statements/refmiscinfo + /refmeta + + refnamediv + refnameALTER INDEX/refname + refpurposechange the definition of an index/refpurpose + /refnamediv + + indexterm zone=sql-alterindex + primaryALTER INDEX/primary + /indexterm + + refsynopsisdiv + synopsis + ALTER INDEX replaceable class=PARAMETERname/replaceable + replaceable class=PARAMETERaction/replaceable [, ... ] + ALTER INDEX replaceable class=PARAMETERname/replaceable + RENAME TO replaceable class=PARAMETERnew_name/replaceable + + where replaceable class=PARAMETERaction/replaceable is one of: + + OWNER TO replaceable class=PARAMETERnew_owner/replaceable + SET INDEXSPACE replaceable class=PARAMETERindexspace_name/replaceable + /synopsis + /refsynopsisdiv + + refsect1 + titleDescription/title + + para +commandALTER INDEX/command changes the definition of an existing index. +There are several subforms: + + variablelist + +varlistentry + termliteralOWNER/literal/term + listitem + para + This form changes the owner of the index to the + specified user. + /para + /listitem +/varlistentry + +varlistentry + termliteralSET TABLESPACE/literal/term + listitem + para + This form changes the index's tablespace to the specified tablespace and + moves the data file(s) associated with the index to the new tablespace. + See also + xref linkend=SQL-CREATETABLESPACE endterm=sql-createtablespace-title. + /para + /listitem +/varlistentry + +varlistentry + termliteralRENAME/literal/term + listitem + para + The literalRENAME/literal forms change the name of the index. + There is no effect on the stored data. + /para + /listitem +/varlistentry + + /variablelist + /para + + para +All the actions except literalRENAME/literal can be combined into +a list of multiple alterations to apply in parallel. + /para + + /refsect1 + + refsect1 + titleParameters/title + + variablelist + + varlistentry + termreplaceable class=PARAMETERname/replaceable/term + listitem +para + The name (possibly schema-qualified) of an existing index to + alter. +/para + /listitem + /varlistentry + + + varlistentry + termreplaceable class=PARAMETERnew_name/replaceable/term + listitem +para + New name for the index. +/para + /listitem + /varlistentry + + + varlistentry + termreplaceable class=PARAMETERnew_owner/replaceable/term + listitem +para + The user name of the new owner of the index. +/para + /listitem + /varlistentry + + varlistentry + termreplaceable class=PARAMETERtablespace_name/replaceable/term + listitem +para + The tablespace name to which the index will be moved. +/para + /listitem + /varlistentry + + /variablelist + /refsect1 + + refsect1 + titleNotes/title + +para + This same operations are supported by literalALTER TABLE/. See also + xref linkend=SQL-ALTERTABLE endterm=SQL-ALTERTABLE-TITLE. +/para + +para + Changing any part of a system catalog index is not permitted. +/para + /refsect1 + + refsect1 + titleExamples/title + para +To rename an existing index: + programlisting + ALTER INDEX distributors RENAME TO suppliers; + /programlisting + /para + + para + To move a index to a different tablespace: + programlisting + ALTER INDEX distributors SET TABLESPACE fasttablespace; + /programlisting + /para + + /refsect1 + + refsect1 + titleCompatibility/title + + para + literalALTER INDEX/ is a PostgreSQL extension. + /para + /refsect1 + /refentry + + !-- Keep this comment at the end of the file + Local variables: + mode: sgml + sgml-omittag:nil + sgml-shorttag:t + sgml-minimize-attributes:nil + sgml-always-quote-attributes:t + sgml-indent-step:1 + sgml-indent-data:t + sgml-parent-document:nil + sgml-default-dtd-file:../reference.ced + sgml-exposed-tags:nil + sgml-local-catalogs:/usr/lib/sgml/catalog + sgml-local-ecat-files:nil + End: + -- Index: src/backend/parser/gram.y
Re: [PATCHES] ALTER INDEX
Gavin Sherry wrote: Index: src/bin/psql/tab-complete.c === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v retrieving revision 1.109 diff -2 -c -r1.109 tab-complete.c *** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 - 1.109 --- src/bin/psql/tab-complete.c 13 Aug 2004 06:34:55 - *** *** 633,637 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, NULL}; COMPLETE_WITH_LIST(list_ALTER); --- 633,638 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, INDEX, ! NULL}; COMPLETE_WITH_LIST(list_ALTER); *** *** 647,650 --- 648,661 COMPLETE_WITH_LIST(list_ALTERDATABASE); } + /* ALTER INDEX name */ + else if (pg_strcasecmp(prev3_wd, ALTER) == 0 + pg_strcasecmp(prev2_wd, INDEX) == 0) + { + static const char *const list_ALTERDATABASE[] = + {SET TABLESPACE, OWNER TO, RENAME TO, NULL}; + + COMPLETE_WITH_LIST(list_ALTERDATABASE); minor issue/nit(?): reusing list_ALTERDATABASE for the ALTER INDEX part looks a little strange ... Stefan(who could really need some feedback on his own tab-complete patch *g*) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] ALTER INDEX
Oops. Too much with the ol' cut and paste. I'm happy to send an updated patch but perhaps the committer, assuming the patch is accepted, would be kind enough to update for me. Thanks for reviewing. Gavin On Fri, 13 Aug 2004, Stefan Kaltenbrunner wrote: Gavin Sherry wrote: Index: src/bin/psql/tab-complete.c === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v retrieving revision 1.109 diff -2 -c -r1.109 tab-complete.c *** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 - 1.109 --- src/bin/psql/tab-complete.c 13 Aug 2004 06:34:55 - *** *** 633,637 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, NULL}; COMPLETE_WITH_LIST(list_ALTER); --- 633,638 { static const char *const list_ALTER[] = ! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, INDEX, !NULL}; COMPLETE_WITH_LIST(list_ALTER); *** *** 647,650 --- 648,661 COMPLETE_WITH_LIST(list_ALTERDATABASE); } + /* ALTER INDEX name */ + else if (pg_strcasecmp(prev3_wd, ALTER) == 0 + pg_strcasecmp(prev2_wd, INDEX) == 0) + { + static const char *const list_ALTERDATABASE[] = + {SET TABLESPACE, OWNER TO, RENAME TO, NULL}; + + COMPLETE_WITH_LIST(list_ALTERDATABASE); minor issue/nit(?): reusing list_ALTERDATABASE for the ALTER INDEX part looks a little strange ... Stefan(who could really need some feedback on his own tab-complete patch *g*) !DSPAM:411c802d169118747610806! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org