pgsql-3672

Version:

8.2.4

Bug Link:

http://archives.postgresql.org/pgsql-bugs/2007-10/msg00085.php

http://postgresql.1045698.n5.nabble.com/BUG-3672-ALTER-lt-column-gt-TYPE-change-the-underlying-index-tablespace-to-default-td2124408.html

Patch Link:

http://archives.postgresql.org/pgsql-committers/2008-01/msg00241.php

Symptom:

ALTER <column> TYPE change the underlying index tablespace to default

Failure type:

Incorrect output

Is there any log message?

No

How it is diagnosed:

Reproduced!

How to reproduce:
postgres=# create table tbltest ( id serial, constraint tbltest_pk primary key  (id)  

USING INDEX TABLESPACE tblspc_idx_ciclocal

)

postgres=# select tablespace from pg_indexes where indexname ='tbltest_pk'

=> tblspc_idx_ciclocal

postgres=# alter table tbltest ALTER id TYPE int2;

postgres=# select tablespace from pg_indexes where indexname ='tbltest_pk'

=> NULL <--"tbltest_pk" has been moved from its original tablespace to the default

Root Cause:

ALTER COLUMN TYPE didn’t preserve the tablespace and reloptions of indexes
it affects(for the sake of pg_dump) . The original coding neglected tablespace entirely (causing
the indexes to move to the database's default tablespace) and for an index
belonging to a UNIQUE or PRIMARY KEY constraint, it would actually try to
assign the parent table's reloptions to the index.

-static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
+static char *pg_get_indexdef_worker(Oid indexrelid, int colno, bool showTblSpc,
                                           int prettyFlags);
...
+static Oid        get_constraint_index(Oid constraintId);

 * In the extended version, there is a colno argument as well as pretty bool.
 *        if colno == 0, we want a complete index definition.
 *        if colno > 0, we only want the Nth index key's variable or expression.
+ *
+ * Note that the SQL-function versions of this omit any info about the
+ * index tablespace; this is intentional because pg_dump wants it that way.
+ * However pg_get_indexdef_string() includes index tablespace if not default.
 * ----------
 */
Datum
 pg_get_indexdef(PG_FUNCTION_ARGS)
{
        Oid                        indexrelid = PG_GETARG_OID(0);

-        PG_RETURN_TEXT_P(string_to_text(pg_get_indexdef_worker(indexrelid, 0, 0)));
+        PG_RETURN_TEXT_P(string_to_text(pg_get_indexdef_worker(indexrelid, 0,
+                                                                                                                   false, 0)));
}

Datum
@@ -596,18 +603,20 @@ pg_get_indexdef_ext(PG_FUNCTION_ARGS)
        int                        prettyFlags;

        prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : 0;
-        PG_RETURN_TEXT_P(string_to_text(pg_get_indexdef_worker(indexrelid, colno, prettyFlags)));
+        PG_RETURN_TEXT_P(string_to_text(pg_get_indexdef_worker(indexrelid, colno,
+                                                                                                                 false, prettyFlags)));
}

/* Internal version that returns a palloc'd C string */
char *
pg_get_indexdef_string(Oid indexrelid)
{
-        return pg_get_indexdef_worker(indexrelid, 0, 0);
+        return pg_get_indexdef_worker(indexrelid, 0, true, 0);
}

static char *
-pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags)
+pg_get_indexdef_worker(Oid indexrelid, int colno, bool showTblSpc,
+                                           int prettyFlags)
{
                ...
                /*
-                 * XXX we don't include the tablespace ... this is for pg_dump
+                 * If it's in a nondefault tablespace, say so, but only if requested
                 */
+                if (showTblSpc)
+                {
+                        Oid                        tblspc;
+
+                        tblspc = get_rel_tablespace(indexrelid);
+                        if (OidIsValid(tblspc))
+                                appendStringInfo(&buf, " TABLESPACE %s",
+                                                                 quote_identifier(get_tablespace_name(tblspc)));
+                }


pg_get_constraintdef_worker(...)
{
        ...
-                                if (fullCommand && OidIsValid(conForm->conrelid))
+                                indexId = get_constraint_index(constraintId);
+
+                        /* XXX why do we only print these bits if fullCommand? */
+                                if (fullCommand && OidIsValid(indexId))
                                {
-                                        char           *options =                                                                                                         flatten_reloptions(conForm->conrelid);
+                                        char           *options = flatten_reloptions(indexId);
+                                        Oid                        tblspc;
                                        if (options)
                                        {
                                        appendStringInfo(&buf, " WITH (%s)", options);
                                        pfree(options);
                                        }
+
+                                        tblspc = get_rel_tablespace(indexId);
+                                        if (OidIsValid(tblspc))
+                                                appendStringInfo(&buf, " USING INDEX TABLESPACE %s",
+                                                                                 quote_identifier(get_tablespace_name(tblspc)));
                                }

                                break;
        ...
}


-/* ----------
+/*
+ * get_constraint_index
+ *                Given the OID of a unique or primary-key constraint, return the
+ *                OID of the underlying unique index.
+ *
+ * Return InvalidOid if the index couldn't be found; this suggests the
+ * given OID is bogus, but we leave it to caller to decide what to do.
+ */
+static Oid
+get_constraint_index(Oid constraintId)
+{
        ...
+}
+
+
+/*

Can we handle:

No