On Wed, Jan 26, 2011 at 07:31:40AM -0500, Robert Haas wrote:
> I'd also suggest that this big if-block you changed to a case
> statement could just as well stay as an if-block. There are only
> three cases, and we want to avoid rearranging things more than
> necessary. It complicates both review and back-patching to no good
> end.
Okay. I've also left out the large reindent in ATRewriteTable for now. Easy to
re-add it later if desired.
> I think you should collect up what's left of ALTER TABLE 0 and the
> stuff on this thread, rebase it, and submit it as a single patch on
> this thread that applies directly against the master branch. We may
> decide to split it back up again in some other way, but I think the
> current division isn't actually buying us much.
Done as attached. This preserves compatibility with our current handling of
composite type dependencies. The rest you've seen before.
Thanks,
nm
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 1d52be6..4efb02e 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 403,411 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
for details on the available parameters. Note that the table contents
will not be modified immediately by this command; depending on the
parameter you might need to rewrite the table to get the desired
effects.
! That can be done with <xref linkend="SQL-CLUSTER">
! or one of the forms of <command>ALTER
! TABLE</> that forces a table rewrite.
</para>
<note>
--- 403,411 ----
for details on the available parameters. Note that the table contents
will not be modified immediately by this command; depending on the
parameter you might need to rewrite the table to get the desired
effects.
! That can be done with <link linkend="SQL-VACUUM">VACUUM
! FULL</>, <xref linkend="SQL-CLUSTER"> or one of the forms
! of <command>ALTER TABLE</> that forces a table rewrite.
</para>
<note>
***************
*** 746,759 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
</para>
<para>
! Adding a column with a non-null default or changing the type of an
! existing column will require the entire table and indexes to be rewritten.
! This might take a significant amount of time for a large table; and it
will
! temporarily require double the disk space. Adding or removing a system
<literal>oid</> column likewise requires rewriting the entire table.
</para>
<para>
Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
scanning the table to verify that existing rows meet the constraint.
</para>
--- 746,770 ----
</para>
<para>
! Adding a column with a non-null default will rewrite the entire table and
! all indexes. Changing the type of an existing column will do the same
! unless a binary-coercible cast implements the type conversion. Refer to
! <xref linkend="SQL-CREATECAST"> for further information. A rewrite might
! take a significant amount of time for a large table, and it will
temporarily
! require double the disk space. Adding or removing a system
<literal>oid</> column likewise requires rewriting the entire table.
</para>
<para>
+ Similar to the behavior of <link linkend="SQL-VACUUM">VACUUM FULL</>, the
+ rewriting process eliminates any dead space in the table. Prior
+ to <productname>PostgreSQL</> 9.0, <literal>SET DATA TYPE</>
+ outpaced <literal>VACUUM FULL</> at this task, so it was useful even
absent
+ the need for a column type change. This speed advantage no longer holds,
+ and <literal>SET DATA TYPE</> may not even rewrite the table.
+ </para>
+
+ <para>
Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
scanning the table to verify that existing rows meet the constraint.
</para>
***************
*** 777,797 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
</para>
<para>
- The fact that <literal>SET DATA TYPE</> requires rewriting the whole table
- is sometimes an advantage, because the rewriting process eliminates
- any dead space in the table. For example, to reclaim the space occupied
- by a dropped column immediately, the fastest way is:
- <programlisting>
- ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
- </programlisting>
- where <literal>anycol</> is any remaining table column and
- <literal>anytype</> is the same type that column already has.
- This results in no semantically-visible change in the table,
- but the command forces rewriting, which gets rid of no-longer-useful
- data.
- </para>
-
- <para>
The <literal>USING</literal> option of <literal>SET DATA TYPE</> can
actually
specify any expression involving the old values of the row; that is, it
can refer to other columns as well as the one being converted. This
allows
--- 788,793 ----
diff --git a/src/backend/catalog/index.cindex 5254b65..411de09 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
***************
*** 1673,1678 **** index_build(Relation heapRelation,
--- 1673,1688 ----
procedure = indexRelation->rd_am->ambuild;
Assert(RegProcedureIsValid(procedure));
+ if (indexInfo->ii_ToastForRelName != NULL)
+ ereport(DEBUG1,
+ (errmsg("building TOAST index for table \"%s\"",
+
indexInfo->ii_ToastForRelName)));
+ else
+ ereport(DEBUG1,
+ (errmsg("building index \"%s\" on table \"%s\"",
+
RelationGetRelationName(indexRelation),
+
RelationGetRelationName(heapRelation))));
+
/*
* Switch to the table owner's userid, so that any index functions are
run
* as that user. Also lock down security-restricted operations and
***************
*** 2663,2669 **** IndexGetRelation(Oid indexId)
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId, bool skip_constraint_checks)
{
Relation iRel,
heapRelation,
--- 2673,2680 ----
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId, const char *toastFor,
! bool skip_constraint_checks)
{
Relation iRel,
heapRelation,
***************
*** 2721,2726 **** reindex_index(Oid indexId, bool skip_constraint_checks)
--- 2732,2740 ----
indexInfo->ii_ExclusionStrats = NULL;
}
+ /* Pass the name of relation this TOAST index serves, if any. */
+ indexInfo->ii_ToastForRelName = toastFor;
+
/* We'll build a new physical relation for the index */
RelationSetNewRelfilenode(iRel, InvalidTransactionId);
***************
*** 2780,2785 **** reindex_index(Oid indexId, bool skip_constraint_checks)
--- 2794,2802 ----
* reindex_relation - This routine is used to recreate all indexes
* of a relation (and optionally its toast relation too, if any).
*
+ * If this is a TOAST relation, toastFor may bear the parent relation name,
+ * facilitating improved messages.
+ *
* "flags" can include REINDEX_SUPPRESS_INDEX_USE and
REINDEX_CHECK_CONSTRAINTS.
*
* If flags has REINDEX_SUPPRESS_INDEX_USE, the relation was just completely
***************
*** 2802,2808 **** reindex_index(Oid indexId, bool skip_constraint_checks)
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too, int flags)
{
Relation rel;
Oid toast_relid;
--- 2819,2826 ----
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, const char *toastFor,
! bool toast_too, int flags)
{
Relation rel;
Oid toast_relid;
***************
*** 2879,2885 **** reindex_relation(Oid relid, bool toast_too, int flags)
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, !(flags &
REINDEX_CHECK_CONSTRAINTS));
CommandCounterIncrement();
--- 2897,2903 ----
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, toastFor, !(flags &
REINDEX_CHECK_CONSTRAINTS));
CommandCounterIncrement();
***************
*** 2902,2912 **** reindex_relation(Oid relid, bool toast_too, int flags)
if (is_pg_class)
RelationSetIndexList(rel, indexIds, ClassOidIndexId);
- /*
- * Close rel, but continue to hold the lock.
- */
- heap_close(rel, NoLock);
-
result = (indexIds != NIL);
/*
--- 2920,2925 ----
***************
*** 2916,2922 **** reindex_relation(Oid relid, bool toast_too, int flags)
*/
Assert(!(toast_too && (flags & REINDEX_SUPPRESS_INDEX_USE)));
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false, flags);
return result;
}
--- 2929,2941 ----
*/
Assert(!(toast_too && (flags & REINDEX_SUPPRESS_INDEX_USE)));
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid,
RelationGetRelationName(rel),
! false,
flags);
!
! /*
! * Close rel, but continue to hold the lock.
! */
! heap_close(rel, NoLock);
return result;
}
diff --git a/src/backend/catalog/tindex c4be3a9..f60b7c1 100644
*** a/src/backend/catalog/toasting.c
--- b/src/backend/catalog/toasting.c
***************
*** 36,43 **** extern Oid binary_upgrade_next_toast_pg_class_oid;
Oid binary_upgrade_next_toast_pg_type_oid = InvalidOid;
! static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
! Datum reloptions);
static bool needs_toast_table(Relation rel);
--- 36,43 ----
Oid binary_upgrade_next_toast_pg_type_oid = InvalidOid;
! static bool create_toast_table(Relation rel, const char *finalRelName,
! Oid toastOid, Oid toastIndexOid, Datum
reloptions);
static bool needs_toast_table(Relation rel);
***************
*** 46,51 **** static bool needs_toast_table(Relation rel);
--- 46,54 ----
* If the table needs a toast table, and doesn't already have one,
* then create a toast table for it.
*
+ * make_new_heap fills finalRelName, so messages display the permanent table
+ * name, not the rewrite-temporary name. Most callers should pass NULL.
+ *
* reloptions for the toast table can be passed, too. Pass (Datum) 0
* for default reloptions.
*
***************
*** 54,60 **** static bool needs_toast_table(Relation rel);
* to end with CommandCounterIncrement if it makes any changes.
*/
void
! AlterTableCreateToastTable(Oid relOid, Datum reloptions)
{
Relation rel;
--- 57,64 ----
* to end with CommandCounterIncrement if it makes any changes.
*/
void
! AlterTableCreateToastTable(Oid relOid, const char *finalRelName,
! Datum reloptions)
{
Relation rel;
***************
*** 66,72 **** AlterTableCreateToastTable(Oid relOid, Datum reloptions)
rel = heap_open(relOid, AccessExclusiveLock);
/* create_toast_table does all the work */
! (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions);
heap_close(rel, NoLock);
}
--- 70,77 ----
rel = heap_open(relOid, AccessExclusiveLock);
/* create_toast_table does all the work */
! (void) create_toast_table(rel, finalRelName,
! InvalidOid,
InvalidOid, reloptions);
heap_close(rel, NoLock);
}
***************
*** 92,98 **** BootstrapToastTable(char *relName, Oid toastOid, Oid
toastIndexOid)
relName)));
/* create_toast_table does all the work */
! if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0))
elog(ERROR, "\"%s\" does not require a toast table",
relName);
--- 97,103 ----
relName)));
/* create_toast_table does all the work */
! if (!create_toast_table(rel, NULL, toastOid, toastIndexOid, (Datum) 0))
elog(ERROR, "\"%s\" does not require a toast table",
relName);
***************
*** 104,114 **** BootstrapToastTable(char *relName, Oid toastOid, Oid
toastIndexOid)
* create_toast_table --- internal workhorse
*
* rel is already opened and exclusive-locked
* toastOid and toastIndexOid are normally InvalidOid, but during
* bootstrap they can be nonzero to specify hand-assigned OIDs
*/
static bool
! create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum
reloptions)
{
Oid relOid = RelationGetRelid(rel);
HeapTuple reltup;
--- 109,121 ----
* create_toast_table --- internal workhorse
*
* rel is already opened and exclusive-locked
+ * finalRelName is normally NULL; make_new_heap overrides it
* toastOid and toastIndexOid are normally InvalidOid, but during
* bootstrap they can be nonzero to specify hand-assigned OIDs
*/
static bool
! create_toast_table(Relation rel, const char *finalRelName,
! Oid toastOid, Oid toastIndexOid, Datum
reloptions)
{
Oid relOid = RelationGetRelid(rel);
HeapTuple reltup;
***************
*** 259,264 **** create_toast_table(Relation rel, Oid toastOid, Oid
toastIndexOid, Datum reloptio
--- 266,273 ----
indexInfo->ii_ExclusionOps = NULL;
indexInfo->ii_ExclusionProcs = NULL;
indexInfo->ii_ExclusionStrats = NULL;
+ indexInfo->ii_ToastForRelName
+ = finalRelName != NULL ? finalRelName :
RelationGetRelationName(rel);
indexInfo->ii_Unique = true;
indexInfo->ii_ReadyForInserts = true;
indexInfo->ii_Concurrent = false;
diff --git a/src/backend/commands/cluindex 59a4394..87af84f 100644
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***************
*** 681,687 **** make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
if (isNull)
reloptions = (Datum) 0;
! AlterTableCreateToastTable(OIDNewHeap, reloptions);
ReleaseSysCache(tuple);
}
--- 681,688 ----
if (isNull)
reloptions = (Datum) 0;
! AlterTableCreateToastTable(OIDNewHeap,
RelationGetRelationName(OldHeap),
! reloptions);
ReleaseSysCache(tuple);
}
***************
*** 1400,1406 **** finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
reindex_flags = REINDEX_SUPPRESS_INDEX_USE;
if (check_constraints)
reindex_flags |= REINDEX_CHECK_CONSTRAINTS;
! reindex_relation(OIDOldHeap, false, reindex_flags);
/* Destroy new heap with old filenode */
object.classId = RelationRelationId;
--- 1401,1407 ----
reindex_flags = REINDEX_SUPPRESS_INDEX_USE;
if (check_constraints)
reindex_flags |= REINDEX_CHECK_CONSTRAINTS;
! reindex_relation(OIDOldHeap, NULL, false, reindex_flags);
/* Destroy new heap with old filenode */
object.classId = RelationRelationId;
diff --git a/src/backend/commands/indindex 94ed437..1dab757 100644
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
***************
*** 1482,1488 **** ReindexIndex(RangeVar *indexRelation)
ReleaseSysCache(tuple);
! reindex_index(indOid, false);
}
/*
--- 1482,1488 ----
ReleaseSysCache(tuple);
! reindex_index(indOid, NULL, false);
}
/*
***************
*** 1514,1520 **** ReindexTable(RangeVar *relation)
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true, 0))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
--- 1514,1520 ----
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, NULL, true, 0))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
***************
*** 1627,1633 **** ReindexDatabase(const char *databaseName, bool do_system,
bool do_user)
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, true, 0))
ereport(NOTICE,
(errmsg("table \"%s.%s\" was reindexed",
get_namespace_name(get_rel_namespace(relid)),
--- 1627,1633 ----
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, NULL, true, 0))
ereport(NOTICE,
(errmsg("table \"%s.%s\" was reindexed",
get_namespace_name(get_rel_namespace(relid)),
diff --git a/src/backend/commands/tableindex 1ecba02..67fdafc 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 71,76 ****
--- 71,77 ----
#include "storage/smgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/datum.h"
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/lsyscache.h"
***************
*** 129,134 **** static List *on_commits = NIL;
--- 130,138 ----
#define AT_PASS_MISC 8 /* other stuff */
#define AT_NUM_PASSES 9
+ /* Level of effort required in Phase 3 (ATRewriteTables). */
+ typedef enum { WORK_NONE = 0, WORK_SCAN = 1, WORK_REWRITE = 2 } WorkLevel;
+
typedef struct AlteredTableInfo
{
/* Information saved before any work commences: */
***************
*** 142,147 **** typedef struct AlteredTableInfo
--- 146,152 ----
List *newvals; /* List of NewColumnValue */
bool new_notnull; /* T if we added new NOT NULL
constraints */
bool new_changeoids; /* T if we added/dropped the OID column
*/
+ WorkLevel worklevel; /* How much work shall we do on
the heap? */
Oid newTableSpace; /* new tablespace; 0 means no
change */
/* Objects to rebuild after completing ALTER TYPE operations */
List *changedConstraintOids; /* OIDs of constraints to
rebuild */
***************
*** 1078,1084 **** ExecuteTruncate(TruncateStmt *stmt)
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true, 0);
}
}
--- 1083,1089 ----
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, NULL, true, 0);
}
}
***************
*** 2990,2996 **** ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode)
(tab->subcmds[AT_PASS_ADD_COL] ||
tab->subcmds[AT_PASS_ALTER_TYPE] ||
tab->subcmds[AT_PASS_COL_ATTRS]))
! AlterTableCreateToastTable(tab->relid, (Datum) 0);
}
}
--- 2995,3001 ----
(tab->subcmds[AT_PASS_ADD_COL] ||
tab->subcmds[AT_PASS_ALTER_TYPE] ||
tab->subcmds[AT_PASS_COL_ATTRS]))
! AlterTableCreateToastTable(tab->relid, NULL, (Datum) 0);
}
}
***************
*** 3193,3202 **** ATRewriteTables(List **wqueue, LOCKMODE lockmode)
continue;
/*
* We only need to rewrite the table if at least one column
needs to
* be recomputed, or we are adding/removing the OID column.
*/
! if (tab->newvals != NIL || tab->new_changeoids)
{
/* Build a temporary relation and copy data */
Relation OldHeap;
--- 3198,3232 ----
continue;
/*
+ * Any operation has to be propagated to tables that use this
table's
+ * rowtype as a column type, but this is not yet implemented.
Reject
+ * changes where this ommission would be particularly glaring:
column
+ * type changes, new columns with default values, and OIDs
changes.
+ *
+ * (Eventually this will probably become true for scans as
well, but at
+ * the moment a composite type does not enforce any
constraints, so it's
+ * not necessary/appropriate to enforce them just during ALTER.)
+ */
+ if (tab->newvals != NIL || tab->new_changeoids)
+ {
+ Relation rel;
+
+ rel = heap_open(tab->relid, NoLock);
+ find_composite_type_dependencies(rel->rd_rel->reltype,
+
RelationGetRelationName(rel),
+
NULL);
+ heap_close(rel, NoLock);
+ }
+
+ /* New NOT NULL constraints always require a scan. */
+ if (tab->new_notnull)
+ tab->worklevel = Max(tab->worklevel, WORK_SCAN);
+
+ /*
* We only need to rewrite the table if at least one column
needs to
* be recomputed, or we are adding/removing the OID column.
*/
! if (tab->worklevel == WORK_REWRITE)
{
/* Build a temporary relation and copy data */
Relation OldHeap;
***************
*** 3263,3269 **** ATRewriteTables(List **wqueue, LOCKMODE lockmode)
* Test the current data within the table against new
constraints
* generated by ALTER TABLE commands, but don't rebuild
data.
*/
! if (tab->constraints != NIL || tab->new_notnull)
ATRewriteTable(tab, InvalidOid, lockmode);
/*
--- 3293,3299 ----
* Test the current data within the table against new
constraints
* generated by ALTER TABLE commands, but don't rebuild
data.
*/
! if (tab->worklevel == WORK_SCAN)
ATRewriteTable(tab, InvalidOid, lockmode);
/*
***************
*** 3332,3338 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
Relation newrel;
TupleDesc oldTupDesc;
TupleDesc newTupDesc;
- bool needscan = false;
List *notnull_attrs;
int i;
ListCell *l;
--- 3362,3367 ----
***************
*** 3378,3396 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
}
/*
- * If we need to rewrite the table, the operation has to be propagated
to
- * tables that use this table's rowtype as a column type.
- *
- * (Eventually this will probably become true for scans as well, but at
- * the moment a composite type does not enforce any constraints, so it's
- * not necessary/appropriate to enforce them just during ALTER.)
- */
- if (newrel)
- find_composite_type_dependencies(oldrel->rd_rel->reltype,
-
RelationGetRelationName(oldrel),
-
NULL);
-
- /*
* Generate the constraint and default execution states
*/
--- 3407,3412 ----
***************
*** 3404,3410 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
switch (con->contype)
{
case CONSTR_CHECK:
- needscan = true;
con->qualstate = (List *)
ExecPrepareExpr((Expr *) con->qual,
estate);
break;
--- 3420,3425 ----
***************
*** 3439,3450 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
!newTupDesc->attrs[i]->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
- if (notnull_attrs)
- needscan = true;
}
! if (newrel || needscan)
! {
ExprContext *econtext;
Datum *values;
bool *isnull;
--- 3454,3462 ----
!newTupDesc->attrs[i]->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
}
! { /* XXX
reindent? */
ExprContext *econtext;
Datum *values;
bool *isnull;
***************
*** 3456,3461 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
--- 3468,3482 ----
List *dropped_attrs = NIL;
ListCell *lc;
+ if (newrel)
+ ereport(DEBUG1,
+ (errmsg("rewriting table \"%s\"",
+
RelationGetRelationName(oldrel))));
+ else
+ ereport(DEBUG1,
+ (errmsg("verifying table \"%s\"",
+
RelationGetRelationName(oldrel))));
+
econtext = GetPerTupleExprContext(estate);
/*
***************
*** 3498,3515 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
while ((tuple = heap_getnext(scan, ForwardScanDirection)) !=
NULL)
{
! if (newrel)
{
Oid tupOid = InvalidOid;
! /* Extract data from old tuple */
! heap_deform_tuple(tuple, oldTupDesc, values,
isnull);
! if (oldTupDesc->tdhasoid)
! tupOid = HeapTupleGetOid(tuple);
!
! /* Set dropped attributes to null in new tuple
*/
! foreach(lc, dropped_attrs)
! isnull[lfirst_int(lc)] = true;
/*
* Process supplied expressions to replace
selected columns.
--- 3519,3549 ----
while ((tuple = heap_getnext(scan, ForwardScanDirection)) !=
NULL)
{
! /*
! * If we're changing the TupleDesc, compute new tuple
values using
! * each transformation expression. When rewriting,
also form a new
! * physical tuple. In Assert-enabled builds, check for
cases that
! * should have been WORK_REWRITE by comparing the data.
! */
! if (tab->newvals != NIL || tab->new_changeoids)
{
Oid tupOid = InvalidOid;
! if (newrel
! #ifdef USE_ASSERT_CHECKING
! || assert_enabled
! #endif
! )
! {
! /* Extract data from old tuple */
! heap_deform_tuple(tuple, oldTupDesc,
values, isnull);
! if (oldTupDesc->tdhasoid)
! tupOid = HeapTupleGetOid(tuple);
!
! /* Set dropped attributes to null in
new tuple */
! foreach(lc, dropped_attrs)
! isnull[lfirst_int(lc)] = true;
! }
/*
* Process supplied expressions to replace
selected columns.
***************
*** 3526,3542 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
econtext,
&isnull[ex->attnum - 1],
NULL);
}
! /*
! * Form the new tuple. Note that we don't
explicitly pfree it,
! * since the per-tuple memory context will be
reset shortly.
! */
! tuple = heap_form_tuple(newTupDesc, values,
isnull);
! /* Preserve OID, if any */
! if (newTupDesc->tdhasoid)
! HeapTupleSetOid(tuple, tupOid);
}
/* Now check any constraints on the possibly-changed
tuple */
--- 3560,3603 ----
econtext,
&isnull[ex->attnum - 1],
NULL);
+
+ #ifdef USE_ASSERT_CHECKING
+ if (assert_enabled)
+ {
+ Datum oldval =
values[ex->attnum - 1];
+ bool oldisnull =
isnull[ex->attnum - 1];
+ Form_pg_attribute f =
newTupDesc->attrs[ex->attnum - 1];
+
+ if (f->attbyval && f->attlen ==
-1)
+ oldval =
PointerGetDatum(PG_DETOAST_DATUM(oldval));
+
+ /*
+ * We don't detect the gross
error of !newrel when the
+ * typlen actually changed.
attbyval could differ in
+ * theory, but we assume it
does not.
+ */
+ Assert(newrel ||
+ (isnull[ex->attnum -
1] == oldisnull
+ && (oldisnull ||
+
datumIsEqual(oldval,
+
values[ex->attnum - 1],
+
f->attbyval, f->attlen))));
+ }
+ #endif
}
! if (newrel)
! {
! /*
! * Form the new tuple. Note that we
don't explicitly pfree it,
! * since the per-tuple memory context
will be reset shortly.
! */
! tuple = heap_form_tuple(newTupDesc,
values, isnull);
! /* Preserve OID, if any */
! if (newTupDesc->tdhasoid)
! HeapTupleSetOid(tuple, tupOid);
! }
}
/* Now check any constraints on the possibly-changed
tuple */
***************
*** 4283,4288 **** ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
--- 4344,4350 ----
newval->expr = defval;
tab->newvals = lappend(tab->newvals, newval);
+ tab->worklevel = WORK_REWRITE;
}
/*
***************
*** 4299,4305 **** ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
--- 4361,4370 ----
* table to fix that.
*/
if (isOid)
+ {
tab->new_changeoids = true;
+ tab->worklevel = WORK_REWRITE;
+ }
/*
* Add needed dependency entries for the new column.
***************
*** 4973,4978 **** ATExecDropColumn(List **wqueue, Relation rel, const char
*colName,
--- 5038,5044 ----
/* Tell Phase 3 to physically remove the OID column */
tab->new_changeoids = true;
+ tab->worklevel = WORK_REWRITE;
}
}
***************
*** 4996,5002 **** ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
/* suppress schema rights check when rebuilding existing index */
check_rights = !is_rebuild;
/* skip index build if phase 3 will have to rewrite table anyway */
! skip_build = (tab->newvals != NIL);
/* suppress notices when rebuilding existing index */
quiet = is_rebuild;
--- 5062,5068 ----
/* suppress schema rights check when rebuilding existing index */
check_rights = !is_rebuild;
/* skip index build if phase 3 will have to rewrite table anyway */
! skip_build = (tab->worklevel == WORK_REWRITE);
/* suppress notices when rebuilding existing index */
quiet = is_rebuild;
***************
*** 5205,5210 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab,
Relation rel,
--- 5271,5277 ----
newcon->qual = (Node *) make_ands_implicit((Expr *) ccon->expr);
tab->constraints = lappend(tab->constraints, newcon);
+ tab->worklevel = Max(tab->worklevel, WORK_SCAN);
/* Save the actually assigned name if it was defaulted */
if (constr->conname == NULL)
***************
*** 5551,5556 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation
rel,
--- 5618,5627 ----
newcon->qual = (Node *) fkconstraint;
tab->constraints = lappend(tab->constraints, newcon);
+ /*
+ * No need to set tab->worklevel; foreign key validation is a
distinct
+ * aspect of Phase 3.
+ */
}
/*
***************
*** 5874,5879 **** validateForeignKeyConstraint(Constraint *fkconstraint,
--- 5945,5954 ----
HeapTuple tuple;
Trigger trig;
+ ereport(DEBUG1,
+ (errmsg("validating foreign key constraint \"%s\"",
+ fkconstraint->conname)));
+
/*
* Build a trigger call structure; we'll need it either way.
*/
***************
*** 6361,6366 **** ATPrepAlterColumnType(List **wqueue,
--- 6436,6443 ----
if (tab->relkind == RELKIND_RELATION)
{
+ CoerceExemptions exempt;
+
/*
* Set up an expression to transform the old data value to the
new type.
* If a USING option was given, transform and use that
expression, else
***************
*** 6421,6426 **** ATPrepAlterColumnType(List **wqueue,
--- 6498,6504 ----
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("column \"%s\" cannot be cast
to type %s",
colName,
format_type_be(targettype))));
+ exempt = GetCoerceExemptions(transform, 1, attnum);
/*
* Add a work queue item to make ATRewriteTable update the
column
***************
*** 6431,6436 **** ATPrepAlterColumnType(List **wqueue,
--- 6509,6519 ----
newval->expr = (Expr *) transform;
tab->newvals = lappend(tab->newvals, newval);
+ if (!(exempt & COERCE_EXEMPT_NOCHANGE))
+ tab->worklevel = WORK_REWRITE;
+ else if (!(exempt & COERCE_EXEMPT_NOERROR))
+ tab->worklevel = Max(tab->worklevel, WORK_SCAN);
+ /* else, both bits set: WORK_NONE */
}
else if (tab->relkind == RELKIND_FOREIGN_TABLE)
{
diff --git a/src/backend/executor/execMindex 600f7e0..56b10a3 100644
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 2307,2313 **** OpenIntoRel(QueryDesc *queryDesc)
(void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);
! AlterTableCreateToastTable(intoRelationId, reloptions);
/*
* And open the constructed table for writing.
--- 2307,2313 ----
(void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);
! AlterTableCreateToastTable(intoRelationId, NULL, reloptions);
/*
* And open the constructed table for writing.
diff --git a/src/backend/parser/parse_index 5b0dc14..b27e5d2 100644
*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
***************
*** 19,24 ****
--- 19,25 ----
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+ #include "commands/typecmds.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_coerce.h"
***************
*** 1805,1810 **** IsBinaryCoercible(Oid srctype, Oid targettype)
--- 1806,1871 ----
}
+ /* GetCoerceExemptions()
+ * Assess invariants of a coercion expression.
+ *
+ * Various common expressions arising from type coercion are subject to
+ * optimizations. For example, a simple varchar -> text cast will never
change
+ * the underlying data (COERCE_EXEMPT_NOCHANGE) and never yield an error
+ * (COERCE_EXEMPT_NOERROR). A varchar(8) -> varchar(4) will never change the
+ * data, but it may yield an error. Given a varno and varattno denoting "the"
+ * source datum, determine which invariants hold for an expression by walking
it
+ * per these rules:
+ *
+ * 1. A Var with the varno/varattno in question has both invariants.
+ * 2. A RelabelType node inherits the invariants of its sole argument.
+ * 3. A CoerceToDomain node inherits any COERCE_EXEMPT_NOCHANGE invariant
from
+ * its sole argument. When GetDomainConstraints() == NIL, it also
inherits
+ * COERCE_EXEMPT_NOERROR. Otherwise, COERCE_EXEMPT_NOERROR
becomes false.
+ * 4. All other nodes have neither invariant.
+ *
+ * Returns a bit string that may contain the following bits:
+ * COERCE_EXEMPT_NOCHANGE: expression result will always have the same
binary
+ * representation as a Var expression having the
given varno and
+ * varattno
+ * COERCE_EXEMPT_NOERROR: expression will never throw an error
+ */
+ CoerceExemptions
+ GetCoerceExemptions(Node *expr,
+ Index varno, AttrNumber varattno)
+ {
+ CoerceExemptions ret = COERCE_EXEMPT_NOCHANGE | COERCE_EXEMPT_NOERROR;
+
+ Assert(expr != NULL);
+
+ for (;;)
+ {
+ if (IsA(expr, Var)
+ && ((Var *) expr)->varno == varno
+ && ((Var *) expr)->varattno == varattno)
+ {
+ return ret;
+ }
+ if (IsA(expr, RelabelType))
+ {
+ expr = (Node *) ((RelabelType *) expr)->arg;
+ }
+ else if (IsA(expr, CoerceToDomain))
+ {
+ CoerceToDomain *d = (CoerceToDomain *) expr;
+
+ if (GetDomainConstraints(d->resulttype) != NIL)
+ ret &= ~COERCE_EXEMPT_NOERROR;
+ expr = (Node *) d->arg;
+ }
+ else
+ {
+ return 0;
+ }
+ }
+ }
+
+
/*
* find_coercion_pathway
* Look for a coercion pathway between two types.
diff --git a/src/backend/tcop/utility.c index 9500037..e6416e4 100644
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
***************
*** 540,546 **** standard_ProcessUtility(Node *parsetree,
(void)
heap_reloptions(RELKIND_TOASTVALUE, toast_options,
true);
!
AlterTableCreateToastTable(relOid, toast_options);
}
else if (IsA(stmt,
CreateForeignTableStmt))
{
--- 540,546 ----
(void)
heap_reloptions(RELKIND_TOASTVALUE, toast_options,
true);
!
AlterTableCreateToastTable(relOid, NULL, toast_options);
}
else if (IsA(stmt,
CreateForeignTableStmt))
{
diff --git a/src/include/catalog/index 60387cc..3e90656 100644
*** a/src/include/catalog/index.h
--- b/src/include/catalog/index.h
***************
*** 85,95 **** extern double IndexBuildHeapScan(Relation heapRelation,
extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot);
! extern void reindex_index(Oid indexId, bool skip_constraint_checks);
#define REINDEX_CHECK_CONSTRAINTS 0x1
#define REINDEX_SUPPRESS_INDEX_USE 0x2
! extern bool reindex_relation(Oid relid, bool toast_too, int flags);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
--- 85,97 ----
extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot);
! extern void reindex_index(Oid indexId, const char *toastFor,
! bool skip_constraint_checks);
#define REINDEX_CHECK_CONSTRAINTS 0x1
#define REINDEX_SUPPRESS_INDEX_USE 0x2
! extern bool reindex_relation(Oid relid, const char *toastFor,
! bool toast_too, int flags);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/catalog/tindex de3623a..7bd2bdd 100644
*** a/src/include/catalog/toasting.h
--- b/src/include/catalog/toasting.h
***************
*** 17,23 ****
/*
* toasting.c prototypes
*/
! extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
--- 17,24 ----
/*
* toasting.c prototypes
*/
! extern void AlterTableCreateToastTable(Oid relOid, const char *finalRelName,
! Datum reloptions);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
diff --git a/src/include/nodes/execnoindex 546b581..46d9d1a 100644
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 64,69 **** typedef struct IndexInfo
--- 64,70 ----
Oid *ii_ExclusionOps; /* array with one entry per
column */
Oid *ii_ExclusionProcs; /* array with one entry
per column */
uint16 *ii_ExclusionStrats; /* array with one entry per
column */
+ const char *ii_ToastForRelName; /* TOAST index only: name of
main rel */
bool ii_Unique;
bool ii_ReadyForInserts;
bool ii_Concurrent;
diff --git a/src/include/parser/parsindex ceaff2f..4303acf 100644
*** a/src/include/parser/parse_coerce.h
--- b/src/include/parser/parse_coerce.h
***************
*** 30,37 **** typedef enum CoercionPathType
--- 30,44 ----
COERCION_PATH_COERCEVIAIO /* need a CoerceViaIO node */
} CoercionPathType;
+ /* Bits in the return value of GetCoerceExemptions. */
+ typedef int CoerceExemptions;
+
+ #define COERCE_EXEMPT_NOCHANGE 0x1 /* expression never
changes storage */
+ #define COERCE_EXEMPT_NOERROR 0x2 /* expression never throws an
error */
extern bool IsBinaryCoercible(Oid srctype, Oid targettype);
+ extern CoerceExemptions GetCoerceExemptions(Node *expr,
+ Index varno, AttrNumber varattno);
extern bool IsPreferredType(TYPCATEGORY category, Oid type);
extern TYPCATEGORY TypeCategory(Oid type);
diff --git a/src/test/regress/GNUmakefilindex 15b9ec4..c33ecb9 100644
diff --git a/src/test/regress/expecindex 3280065..7a5b96e 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 1482,1487 **** create table tab2 (x int, y tab1);
--- 1482,1630 ----
alter table tab1 alter column b type varchar; -- fails
ERROR: cannot alter table "tab1" because column "tab2"."y" uses its rowtype
--
+ -- ALTER COLUMN ... SET DATA TYPE optimizations
+ --
+ SET client_min_messages = debug1; -- Track rewrites.
+ -- Model a type change that throws the semantics of dependent expressions.
+ CREATE DOMAIN trickint AS int;
+ CREATE FUNCTION touchy_f(trickint) RETURNS int4 LANGUAGE sql AS 'SELECT
100';
+ CREATE FUNCTION touchy_f(int4) RETURNS int4 LANGUAGE sql AS
'SELECT $1';
+ CREATE DOMAIN lendom AS varchar(8);
+ CREATE DOMAIN checkdom AS text CHECK (VALUE LIKE '<%');
+ CREATE TABLE parent (keycol numeric PRIMARY KEY);
+ DEBUG: building TOAST index for table "parent"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey"
for table "parent"
+ DEBUG: building index "parent_pkey" on table "parent"
+ INSERT INTO parent VALUES (0.12), (1.12);
+ CREATE TABLE t (
+ integral int4 NOT NULL,
+ rational numeric(9,4) UNIQUE NOT NULL REFERENCES
parent,
+ string varchar(4) NOT NULL,
+ strarr varchar(2)[] NOT NULL,
+ CHECK (touchy_f(integral) < 10),
+ EXCLUDE (integral WITH =)
+ );
+ DEBUG: building TOAST index for table "t"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_rational_key"
for table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ NOTICE: CREATE TABLE / EXCLUDE will create implicit index "t_integral_excl"
for table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ CREATE INDEX ON t USING gin (strarr);
+ DEBUG: building index "t_strarr_idx" on table "t"
+ INSERT INTO t VALUES (1, 0.12, '<a/>', '{ab,cd}'), (2, 1.12, '<b/>',
'{ef,gh}');
+ -- Comments "rewrite", "verify" and "noop" signify whether ATRewriteTables
+ -- rewrites, scans or does nothing to the table proper. An "-e" suffix
denotes
+ -- an error outcome.
+ ALTER TABLE t ALTER integral TYPE trickint;
-- verify-e
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: verifying table "t"
+ ERROR: check constraint "t_integral_check" is violated by some row
+ ALTER TABLE t DROP CONSTRAINT t_integral_check;
+ ALTER TABLE t ALTER integral TYPE abstime USING integral::abstime; -- noop
+ DEBUG: building index "t_integral_excl" on table "t"
+ ALTER TABLE t ALTER integral TYPE oid USING integral::int4;
-- noop
+ DEBUG: building index "t_integral_excl" on table "t"
+ ALTER TABLE t ALTER integral TYPE regtype;
-- noop
+ DEBUG: building index "t_integral_excl" on table "t"
+ ALTER TABLE t ALTER rational TYPE numeric(7,4);
-- verify
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ DEBUG: validating foreign key constraint "t_rational_fkey"
+ ALTER TABLE t ALTER rational TYPE numeric(8,4);
-- noop
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ DEBUG: validating foreign key constraint "t_rational_fkey"
+ ALTER TABLE t ALTER rational TYPE numeric(8,1);
-- rewrite-e
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ DEBUG: validating foreign key constraint "t_rational_fkey"
+ ERROR: insert or update on table "t" violates foreign key constraint
"t_rational_fkey"
+ DETAIL: Key (rational)=(0.1) is not present in table "parent".
+ ALTER TABLE t ALTER string TYPE varchar(6);
-- noop
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ ALTER TABLE t ALTER string TYPE lendom;
-- noop
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ ALTER TABLE t ALTER string TYPE xml USING string::xml;
-- verify
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ ALTER TABLE t ALTER string TYPE checkdom;
-- verify
+ DEBUG: verifying table "t"
+ ALTER TABLE t ALTER string TYPE text USING 'foo'::varchar;
-- rewrite
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ ALTER TABLE t ALTER strarr TYPE varchar(4)[];
-- noop
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ ALTER TABLE t ADD CONSTRAINT u0 UNIQUE (integral), -- build index exactly once
+ ALTER integral TYPE int8;
-- rewrite
+ NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "u0" for table
"t"
+ DEBUG: building TOAST index for table "t"
+ DEBUG: rewriting table "t"
+ DEBUG: building index "t_rational_key" on table "t"
+ DEBUG: building index "t_strarr_idx" on table "t"
+ DEBUG: building index "t_integral_excl" on table "t"
+ DEBUG: building index "u0" on table "t"
+ -- Data and catalog end state. We omit the columns that bear unstable OIDs.
+ SELECT * FROM t ORDER BY 1;
+ integral | rational | string | strarr
+ ----------+----------+--------+---------
+ 1 | 0.1200 | foo | {ab,cd}
+ 2 | 1.1200 | foo | {ef,gh}
+ (2 rows)
+
+ SELECT relname, indclass FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+ WHERE indrelid = 't'::regclass ORDER BY 1;
+ relname | indclass
+ -----------------+----------
+ t_integral_excl | 10029
+ t_rational_key | 10037
+ t_strarr_idx | 10103
+ u0 | 10029
+ (4 rows)
+
+ SELECT relname, attname, atttypid, atttypmod
+ FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+ WHERE attnum > 0 AND
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid =
't'::regclass)
+ ORDER BY 1, 2;
+ relname | attname | atttypid | atttypmod
+ -----------------+----------+----------+-----------
+ t_integral_excl | integral | 20 | -1
+ t_rational_key | rational | 1700 | 524296
+ t_strarr_idx | strarr | 1043 | -1
+ u0 | integral | 20 | -1
+ (4 rows)
+
+ -- Done. Retain the table under a less-generic name.
+ ALTER TABLE t RENAME TO alter_type_test;
+ RESET client_min_messages;
+ --
-- lock levels
--
drop type lockmodes;
diff --git a/src/test/regress/expected/big_alternew file mode 100644
index 0000000..1609c01
diff --git a/src/test/regress/sql/alter_table.sql b/index cfbfbb6..2525fe0
100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 1098,1103 **** create table tab2 (x int, y tab1);
--- 1098,1165 ----
alter table tab1 alter column b type varchar; -- fails
--
+ -- ALTER COLUMN ... SET DATA TYPE optimizations
+ --
+ SET client_min_messages = debug1; -- Track rewrites.
+
+ -- Model a type change that throws the semantics of dependent expressions.
+ CREATE DOMAIN trickint AS int;
+ CREATE FUNCTION touchy_f(trickint) RETURNS int4 LANGUAGE sql AS 'SELECT
100';
+ CREATE FUNCTION touchy_f(int4) RETURNS int4 LANGUAGE sql AS
'SELECT $1';
+ CREATE DOMAIN lendom AS varchar(8);
+ CREATE DOMAIN checkdom AS text CHECK (VALUE LIKE '<%');
+
+ CREATE TABLE parent (keycol numeric PRIMARY KEY);
+ INSERT INTO parent VALUES (0.12), (1.12);
+
+ CREATE TABLE t (
+ integral int4 NOT NULL,
+ rational numeric(9,4) UNIQUE NOT NULL REFERENCES
parent,
+ string varchar(4) NOT NULL,
+ strarr varchar(2)[] NOT NULL,
+ CHECK (touchy_f(integral) < 10),
+ EXCLUDE (integral WITH =)
+ );
+ CREATE INDEX ON t USING gin (strarr);
+ INSERT INTO t VALUES (1, 0.12, '<a/>', '{ab,cd}'), (2, 1.12, '<b/>',
'{ef,gh}');
+
+ -- Comments "rewrite", "verify" and "noop" signify whether ATRewriteTables
+ -- rewrites, scans or does nothing to the table proper. An "-e" suffix
denotes
+ -- an error outcome.
+ ALTER TABLE t ALTER integral TYPE trickint;
-- verify-e
+ ALTER TABLE t DROP CONSTRAINT t_integral_check;
+ ALTER TABLE t ALTER integral TYPE abstime USING integral::abstime; -- noop
+ ALTER TABLE t ALTER integral TYPE oid USING integral::int4;
-- noop
+ ALTER TABLE t ALTER integral TYPE regtype;
-- noop
+ ALTER TABLE t ALTER rational TYPE numeric(7,4);
-- verify
+ ALTER TABLE t ALTER rational TYPE numeric(8,4);
-- noop
+ ALTER TABLE t ALTER rational TYPE numeric(8,1);
-- rewrite-e
+ ALTER TABLE t ALTER string TYPE varchar(6);
-- noop
+ ALTER TABLE t ALTER string TYPE lendom;
-- noop
+ ALTER TABLE t ALTER string TYPE xml USING string::xml;
-- verify
+ ALTER TABLE t ALTER string TYPE checkdom;
-- verify
+ ALTER TABLE t ALTER string TYPE text USING 'foo'::varchar;
-- rewrite
+ ALTER TABLE t ALTER strarr TYPE varchar(4)[];
-- noop
+ ALTER TABLE t ADD CONSTRAINT u0 UNIQUE (integral), -- build index exactly once
+ ALTER integral TYPE int8;
-- rewrite
+
+ -- Data and catalog end state. We omit the columns that bear unstable OIDs.
+ SELECT * FROM t ORDER BY 1;
+
+ SELECT relname, indclass FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+ WHERE indrelid = 't'::regclass ORDER BY 1;
+
+ SELECT relname, attname, atttypid, atttypmod
+ FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+ WHERE attnum > 0 AND
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid =
't'::regclass)
+ ORDER BY 1, 2;
+
+ -- Done. Retain the table under a less-generic name.
+ ALTER TABLE t RENAME TO alter_type_test;
+ RESET client_min_messages;
+
+ --
-- lock levels
--
drop type lockmodes;
diff --git a/src/test/regress/sql/big_alternew file mode 100644
index 0000000..3824d96
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
