On Sat, Jan 15, 2011 at 02:31:21PM -0500, Robert Haas wrote:
> On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <n...@leadboat.com> wrote:
> > Do you value test coverage so little?
>
> If you're asking whether I think real-world usability is more
> important than test coverage, then yes.
No, I wasn't asking that.
The attached version implements your preferred real-world usability for the
index_build DEBUG message.
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5790f87..6f5ffb3 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
***************
*** 1420,1425 **** index_build(Relation heapRelation,
--- 1420,1435 ----
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
***************
*** 2412,2418 **** 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,
--- 2422,2429 ----
* 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,
***************
*** 2470,2475 **** reindex_index(Oid indexId, bool skip_constraint_checks)
--- 2481,2489 ----
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);
***************
*** 2529,2534 **** reindex_index(Oid indexId, bool skip_constraint_checks)
--- 2543,2551 ----
* 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.
+ *
* If heap_rebuilt is true, then the relation was just completely rebuilt by
* an operation such as VACUUM FULL or CLUSTER, and therefore its indexes are
* inconsistent with it. This makes things tricky if the relation is a system
***************
*** 2548,2554 **** reindex_index(Oid indexId, bool skip_constraint_checks)
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt)
{
Relation rel;
Oid toast_relid;
--- 2565,2572 ----
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, const char *toastFor,
! bool toast_too, bool heap_rebuilt)
{
Relation rel;
Oid toast_relid;
***************
*** 2625,2631 **** reindex_relation(Oid relid, bool toast_too, bool
heap_rebuilt)
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, heap_rebuilt);
CommandCounterIncrement();
--- 2643,2649 ----
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, toastFor, heap_rebuilt);
CommandCounterIncrement();
***************
*** 2648,2658 **** reindex_relation(Oid relid, bool toast_too, bool
heap_rebuilt)
if (is_pg_class)
RelationSetIndexList(rel, indexIds, ClassOidIndexId);
- /*
- * Close rel, but continue to hold the lock.
- */
- heap_close(rel, NoLock);
-
result = (indexIds != NIL);
/*
--- 2666,2671 ----
***************
*** 2660,2666 **** reindex_relation(Oid relid, bool toast_too, bool
heap_rebuilt)
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false, false);
return result;
}
--- 2673,2685 ----
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid,
RelationGetRelationName(rel),
! false,
false);
!
! /*
! * Close rel, but continue to hold the lock.
! */
! heap_close(rel, NoLock);
return result;
}
diff --git a/src/backend/catalog/tindex 142beae..031471b 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;
***************
*** 255,260 **** create_toast_table(Relation rel, Oid toastOid, Oid
toastIndexOid, Datum reloptio
--- 262,269 ----
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 19c3cf9..25f5a3d 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);
}
***************
*** 1395,1401 **** finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
* so no chance to reclaim disk space before commit. We do not need a
* final CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(OIDOldHeap, false, true);
/* Destroy new heap with old filenode */
object.classId = RelationRelationId;
--- 1396,1402 ----
* so no chance to reclaim disk space before commit. We do not need a
* final CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(OIDOldHeap, NULL, false, true);
/* Destroy new heap with old filenode */
object.classId = RelationRelationId;
diff --git a/src/backend/commands/indindex f809a26..b84e2b2 100644
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
***************
*** 1597,1603 **** ReindexIndex(RangeVar *indexRelation)
ReleaseSysCache(tuple);
! reindex_index(indOid, false);
}
/*
--- 1597,1603 ----
ReleaseSysCache(tuple);
! reindex_index(indOid, NULL, false);
}
/*
***************
*** 1629,1635 **** ReindexTable(RangeVar *relation)
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true, false))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
--- 1629,1635 ----
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, NULL, true, false))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
***************
*** 1742,1748 **** 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, false))
ereport(NOTICE,
(errmsg("table \"%s.%s\" was reindexed",
get_namespace_name(get_rel_namespace(relid)),
--- 1742,1748 ----
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, NULL, true, false))
ereport(NOTICE,
(errmsg("table \"%s.%s\" was reindexed",
get_namespace_name(get_rel_namespace(relid)),
diff --git a/src/backend/commands/tableindex f3bd565..7c4488e 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 1076,1082 **** ExecuteTruncate(TruncateStmt *stmt)
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true, false);
}
}
--- 1076,1082 ----
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, NULL, true, false);
}
}
***************
*** 2981,2987 **** 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);
}
}
--- 2981,2987 ----
(tab->subcmds[AT_PASS_ADD_COL] ||
tab->subcmds[AT_PASS_ALTER_TYPE] ||
tab->subcmds[AT_PASS_COL_ATTRS]))
! AlterTableCreateToastTable(tab->relid, NULL, (Datum) 0);
}
}
***************
*** 3443,3448 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
--- 3443,3457 ----
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);
/*
***************
*** 3836,3841 **** ATTypedTableRecursion(List **wqueue, Relation rel,
AlterTableCmd *cmd,
--- 3845,3854 ----
* Eventually, we'd like to propagate the check or rewrite operation
* into other such tables, but for now, just error out if we find any.
*
+ * CHECK, NOT NULL and DEFAULT constraints and the "oid" system column do
+ * not (currently) follow the row type, so they require no attention here.
+ * The non-propagation of DEFAULT and NOT NULL make ADD COLUMN safe, too.
+ *
* Caller should provide either a table name or a type name (not both) to
* report in the error message, if any.
*
***************
*** 5789,5794 **** validateForeignKeyConstraint(Constraint *fkconstraint,
--- 5802,5811 ----
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.
*/
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/tcop/utility.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 2804c63..12656fa 100644
*** a/src/include/catalog/index.h
--- b/src/include/catalog/index.h
***************
*** 70,77 **** 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);
! extern bool reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
--- 70,79 ----
extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot);
! extern void reindex_index(Oid indexId, const char *toastFor,
! bool skip_constraint_checks);
! extern bool reindex_relation(Oid relid, const char *toastFor,
! bool toast_too, bool heap_rebuilt);
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/test/regress/GNUmakindex 15b9ec4..c33ecb9 100644
diff --git a/src/test/regress/expecindex 3d126bb..282de46 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 1477,1482 **** create table tab1 (a int, b text);
--- 1477,1648 ----
create table tab2 (x int, y tab1);
alter table tab1 alter column b type varchar; -- fails
ERROR: cannot alter table "tab1" because column "tab2"."y" uses its rowtype
+ alter table tab1 add check (b <> 'foo');
+ alter table tab1 add c int not null;
+ alter table tab1 add d int not null default 1; -- fails
+ ERROR: cannot alter table "tab1" because column "tab2"."y" uses its rowtype
+ alter table tab1 drop a;
+ alter table tab1 set with oids; -- 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 TOAST index for table "t"
+ DEBUG: rewriting 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 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"
+ ALTER TABLE t ALTER integral TYPE oid USING integral::int4;
-- noop
+ 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"
+ ALTER TABLE t ALTER integral TYPE regtype;
-- noop
+ 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"
+ 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: 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 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
--
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 4895768..8e5090e
100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 1094,1099 **** drop table another;
--- 1094,1166 ----
create table tab1 (a int, b text);
create table tab2 (x int, y tab1);
alter table tab1 alter column b type varchar; -- fails
+ alter table tab1 add check (b <> 'foo');
+ alter table tab1 add c int not null;
+ alter table tab1 add d int not null default 1; -- fails
+ alter table tab1 drop a;
+ alter table tab1 set with oids; -- 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
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
