This patch removes ALTER TYPE rewrites in cases we can already prove valid. I add a function GetCoerceExemptions() that walks an Expr according to rules discussed in the design thread, simplified slightly pending additions in the next patch. See the comment at that function for a refresher. I use it to populate two new bools to AlteredTableInfo, "new_bits" and "mayerror". "new_bits" is a superset of "new_changedoids", so I subsume that. I change ATRewriteTable to act on those and support the notion of evaluating the transformation expressions when we're not rewriting the table.
As unintended fallout, it's no longer an error to add oids or a column with a default value to a table whose rowtype is used in columns elsewhere. This seems best. Defaults on the origin table do not even apply to new inserts into such a column, and the rowtype does not gain an OID column via its table. This helps on conversions like varchar(X)->text, xml->text, and conversions between domains and their base types.
*** 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" *************** *** 140,147 **** typedef struct AlteredTableInfo /* Information saved by Phases 1/2 for Phase 3: */ List *constraints; /* List of NewConstraint */ 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 */ Oid newTableSpace; /* new tablespace; 0 means no change */ /* Objects to rebuild after completing ALTER TYPE operations */ List *changedConstraintOids; /* OIDs of constraints to rebuild */ --- 141,149 ---- /* Information saved by Phases 1/2 for Phase 3: */ List *constraints; /* List of NewConstraint */ List *newvals; /* List of NewColumnValue */ + bool new_bits; /* Could stored tuple or OID bits change? */ + bool mayerror; /* Could the newval expressions throw errors? */ bool new_notnull; /* T if we added new NOT NULL constraints */ Oid newTableSpace; /* new tablespace; 0 means no change */ /* Objects to rebuild after completing ALTER TYPE operations */ List *changedConstraintOids; /* OIDs of constraints to rebuild */ *************** *** 3184,3190 **** ATRewriteTables(List **wqueue, LOCKMODE lockmode) * 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; --- 3186,3192 ---- * 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->new_bits) { /* Build a temporary relation and copy data */ Relation OldHeap; *************** *** 3250,3256 **** 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); /* --- 3252,3258 ---- * Test the current data within the table against new constraints * generated by ALTER TABLE commands, but don't rebuild data. */ ! if (tab->mayerror || tab->constraints != NIL || tab->new_notnull) ATRewriteTable(tab, InvalidOid, lockmode); /* *************** *** 3310,3316 **** ATRewriteTables(List **wqueue, LOCKMODE lockmode) /* * ATRewriteTable: scan or rewrite one table * ! * OIDNewHeap is InvalidOid if we don't need to rewrite */ static void ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) --- 3312,3319 ---- /* * ATRewriteTable: scan or rewrite one table * ! * OIDNewHeap is InvalidOid if we don't need to rewrite. If the only new ! * constraints are foreign key constraints, we do nothing here. */ static void ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) *************** *** 3319,3325 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) Relation newrel; TupleDesc oldTupDesc; TupleDesc newTupDesc; ! bool needscan = false; List *notnull_attrs; int i; ListCell *l; --- 3322,3328 ---- Relation newrel; TupleDesc oldTupDesc; TupleDesc newTupDesc; ! bool needscan = tab->mayerror; List *notnull_attrs; int i; ListCell *l; *************** *** 3365,3383 **** 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 */ --- 3368,3373 ---- *************** *** 3430,3435 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) --- 3420,3426 ---- needscan = true; } + /* newrel && !needscan arises when only adding OIDs. */ if (newrel || needscan) { ExprContext *econtext; *************** *** 3494,3511 **** 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. --- 3485,3515 ---- while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { ! /* ! * If we're rewriting or verifying, compute new tuple values using ! * each transformation expression. When rewriting, also form a new ! * physical tuple. In Assert-enabled builds, check for false ! * negatives of tab->new_bits by comparing the data. ! */ ! if (newrel || tab->mayerror) { 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. *************** *** 3522,3538 **** 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 */ --- 3526,3569 ---- 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 new_bits == true when + * the typlen actually changed. attbyval could differ in + * theory, but we assume it does not. + */ + Assert(tab->new_bits || + (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, --- 4314,4321 ---- newval->expr = defval; tab->newvals = lappend(tab->newvals, newval); + tab->new_bits = true; + tab->mayerror = true; } /* *************** *** 4299,4305 **** ATExecAddColumn(AlteredTableInfo *tab, Relation rel, * table to fix that. */ if (isOid) ! tab->new_changeoids = true; /* * Add needed dependency entries for the new column. --- 4332,4338 ---- * table to fix that. */ if (isOid) ! tab->new_bits = true; /* * Add needed dependency entries for the new column. *************** *** 4970,4976 **** ATExecDropColumn(List **wqueue, Relation rel, const char *colName, tab = ATGetQueueEntry(wqueue, rel); /* Tell Phase 3 to physically remove the OID column */ ! tab->new_changeoids = true; } } --- 5003,5009 ---- tab = ATGetQueueEntry(wqueue, rel); /* Tell Phase 3 to physically remove the OID column */ ! tab->new_bits = true; } } *************** *** 4994,5000 **** 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; --- 5027,5033 ---- /* 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->new_bits; /* suppress notices when rebuilding existing index */ quiet = is_rebuild; *************** *** 6293,6298 **** ATPrepAlterColumnType(List **wqueue, --- 6326,6333 ---- 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 *************** *** 6353,6358 **** ATPrepAlterColumnType(List **wqueue, --- 6388,6394 ---- (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 *************** *** 6363,6368 **** ATPrepAlterColumnType(List **wqueue, --- 6399,6423 ---- newval->expr = (Expr *) transform; tab->newvals = lappend(tab->newvals, newval); + if (!(exempt & COERCE_EXEMPT_NOCHANGE)) + tab->new_bits = true; + if (!(exempt & COERCE_EXEMPT_NOERROR)) + tab->mayerror = true; + + /* + * If we need to rewrite the table to update tuple values or scan + * it to verify tuple values, tables using this table's rowtype as + * a column type would need the same treatment. + */ + find_composite_type_dependencies(rel->rd_rel->reltype, + RelationGetRelationName(rel), + NULL); + } + else if (tab->relkind == RELKIND_COMPOSITE_TYPE) + { + find_composite_type_dependencies(rel->rd_rel->reltype, + NULL, + RelationGetRelationName(rel)); } else if (tab->relkind == RELKIND_FOREIGN_TABLE) { *************** *** 6372,6388 **** ATPrepAlterColumnType(List **wqueue, errmsg("ALTER TYPE USING is not supported on foreign tables"))); } - if (tab->relkind == RELKIND_COMPOSITE_TYPE) - { - /* - * For composite types, do this check now. Tables will check - * it later when the table is being rewritten. - */ - find_composite_type_dependencies(rel->rd_rel->reltype, - NULL, - RelationGetRelationName(rel)); - } - ReleaseSysCache(tuple); /* --- 6427,6432 ---- *** 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,1870 ---- } + /* 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 (always-noop) and never yield an error (never-error). 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 always-noop invariant from its sole + * argument. When GetDomainConstraints() == NIL, it also inherits + * never-error. Otherwise, never-error 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. *** 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); *** a/src/test/regress/expected/alter_table.out --- b/src/test/regress/expected/alter_table.out *************** *** 1479,1489 **** 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 -- -- Deeper alter-column-type tests -- --- 1479,1487 ---- 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; alter table tab1 drop a; ! alter table tab1 set with oids; -- -- Deeper alter-column-type tests -- *************** *** 1754,1798 **** FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY tgname; -- though mostly not stated here. -- Constraint failures induced by a no-work type change. ALTER TABLE t ALTER constraint0 TYPE trickint; -- verify-e ! DEBUG: Rewriting table "t" ERROR: check constraint "t_constraint0_check" is violated by some row ALTER TABLE t ALTER constraint1 TYPE trickint; -- noop-e - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_expr_idx" DEBUG: Rebuilding index "t_touchy_f_idx" ERROR: could not create unique index "t_touchy_f_idx" DETAIL: Key (touchy_f(constraint1))=(100) is duplicated. ALTER TABLE t ALTER constraint2 TYPE trickint; -- noop-e - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" DEBUG: Rebuilding index "t_expr_idx" ERROR: could not create unique index "t_expr_idx" DETAIL: Key ((1))=(1) is duplicated. --- 1752,1764 ---- -- though mostly not stated here. -- Constraint failures induced by a no-work type change. ALTER TABLE t ALTER constraint0 TYPE trickint; -- verify-e ! DEBUG: Verifying table "t" ERROR: check constraint "t_constraint0_check" is violated by some row ALTER TABLE t ALTER constraint1 TYPE trickint; -- noop-e DEBUG: Rebuilding index "t_touchy_f_idx" ERROR: could not create unique index "t_touchy_f_idx" DETAIL: Key (touchy_f(constraint1))=(100) is duplicated. ALTER TABLE t ALTER constraint2 TYPE trickint; -- noop-e DEBUG: Rebuilding index "t_expr_idx" ERROR: could not create unique index "t_expr_idx" DETAIL: Key ((1))=(1) is duplicated. *************** *** 1956,2013 **** DEBUG: Rebuilding index "t_constraint4_key" DEBUG: Validating foreign key constraint "child_keycol_fkey" -- Type-specific tests. ALTER TABLE t ALTER integral TYPE abstime USING integral::abstime; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" DEBUG: Rebuilding index "t_integral_key" ALTER TABLE t ALTER integral TYPE oid USING integral::int4; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" DEBUG: Rebuilding index "t_integral_key" ALTER TABLE t ALTER integral TYPE regtype; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" DEBUG: Rebuilding index "t_integral_key" ALTER TABLE t ALTER integral TYPE int8; -- rewrite DEBUG: Rewriting table "t" --- 1922,1931 ---- *************** *** 2118,2139 **** DEBUG: Rebuilding index "t_constraint4_key" DEBUG: Rebuilding index "t_integral_key" DEBUG: Rebuilding index "t_rational_key" ALTER TABLE t ALTER rational TYPE numeric; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" DEBUG: Rebuilding index "t_rational_key" ALTER TABLE t ALTER rational TYPE numeric(5,4); -- verify-e DEBUG: Rewriting table "t" --- 2036,2041 ---- *************** *** 2183,2242 **** ALTER TABLE t ALTER string TYPE shortdom; -- rewrite-e DEBUG: Rewriting table "t" ERROR: value too long for type character varying(1) ALTER TABLE t ALTER string TYPE checkdom; -- verify - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" DEBUG: Rebuilding index "t_string_idx1" DEBUG: Rebuilding index "t_string_idx" ALTER TABLE t ALTER string TYPE faildom; -- verify-e ! DEBUG: Rewriting table "t" ERROR: value for domain faildom violates check constraint "faildom_check" ALTER TABLE t ALTER string TYPE loosedom; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" DEBUG: Rebuilding index "t_string_idx" DEBUG: Rebuilding index "t_string_idx1" ALTER TABLE t ALTER string TYPE text; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" DEBUG: Rebuilding index "t_string_idx1" DEBUG: Rebuilding index "t_string_idx" ALTER TABLE t ALTER string TYPE varchar(20); -- rewrite-v --- 2085,2102 ---- DEBUG: Rewriting table "t" ERROR: value too long for type character varying(1) ALTER TABLE t ALTER string TYPE checkdom; -- verify DEBUG: Rebuilding index "t_string_idx1" DEBUG: Rebuilding index "t_string_idx" + DEBUG: Verifying table "t" ALTER TABLE t ALTER string TYPE faildom; -- verify-e ! DEBUG: Rebuilding index "t_string_idx" ! DEBUG: Rebuilding index "t_string_idx1" ! DEBUG: Verifying table "t" ERROR: value for domain faildom violates check constraint "faildom_check" ALTER TABLE t ALTER string TYPE loosedom; -- noop DEBUG: Rebuilding index "t_string_idx" DEBUG: Rebuilding index "t_string_idx1" ALTER TABLE t ALTER string TYPE text; -- noop DEBUG: Rebuilding index "t_string_idx1" DEBUG: Rebuilding index "t_string_idx" ALTER TABLE t ALTER string TYPE varchar(20); -- rewrite-v *************** *** 2851,2872 **** DEBUG: Rebuilding index "t_stamp_key" DEBUG: Rebuilding index "t_timegap_key" DEBUG: Rebuilding index "t_bits_key" ALTER TABLE t ALTER network TYPE inet; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" DEBUG: Rebuilding index "t_network_key" ALTER TABLE t ALTER network TYPE cidr; -- rewrite-v DEBUG: Rewriting table "t" --- 2711,2716 ---- *************** *** 2887,2909 **** DEBUG: Rebuilding index "t_timegap_key" DEBUG: Rebuilding index "t_bits_key" DEBUG: Rebuilding index "t_network_key" ALTER TABLE t ALTER document TYPE text; -- noop - DEBUG: Rewriting table "t" - DEBUG: Rebuilding index "t_strarr_idx" - DEBUG: Rebuilding index "t_square_idx" - DEBUG: Rebuilding index "t_touchy_f_idx" - DEBUG: Rebuilding index "t_expr_idx" - DEBUG: Rebuilding index "t_constraint4_key" - DEBUG: Rebuilding index "t_integral_key" - DEBUG: Rebuilding index "t_rational_key" - DEBUG: Rebuilding index "t_string_idx1" - DEBUG: Rebuilding index "t_string_idx" - DEBUG: Rebuilding index "t_daytimetz_key" - DEBUG: Rebuilding index "t_daytime_key" - DEBUG: Rebuilding index "t_stamptz_key" - DEBUG: Rebuilding index "t_stamp_key" - DEBUG: Rebuilding index "t_timegap_key" - DEBUG: Rebuilding index "t_bits_key" - DEBUG: Rebuilding index "t_network_key" ALTER TABLE t ALTER document TYPE xml USING document::xml; -- verify DEBUG: Rewriting table "t" DEBUG: Rebuilding index "t_strarr_idx" --- 2731,2736 ---- *** a/src/test/regress/expected/rowtypes.out --- b/src/test/regress/expected/rowtypes.out *************** *** 82,91 **** select * from people; (Joe,Blow) | 01-10-1984 (1 row) ! -- at the moment this will not work due to ALTER TABLE inadequacy: alter table fullname add column suffix text default ''; ! ERROR: cannot alter table "fullname" because column "people"."fn" uses its rowtype ! -- but this should work: alter table fullname add column suffix text default null; select * from people; fn | bd --- 82,91 ---- (Joe,Blow) | 01-10-1984 (1 row) ! -- accepted, but the default does not propagate alter table fullname add column suffix text default ''; ! alter table fullname drop column suffix; ! -- same effect alter table fullname add column suffix text default null; select * from people; fn | bd *** a/src/test/regress/sql/alter_table.sql --- b/src/test/regress/sql/alter_table.sql *************** *** 1096,1104 **** 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 -- -- Deeper alter-column-type tests --- 1096,1104 ---- 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; alter table tab1 drop a; ! alter table tab1 set with oids; -- -- Deeper alter-column-type tests *** a/src/test/regress/sql/rowtypes.sql --- b/src/test/regress/sql/rowtypes.sql *************** *** 45,54 **** insert into people values ('(Joe,Blow)', '1984-01-10'); select * from people; ! -- at the moment this will not work due to ALTER TABLE inadequacy: alter table fullname add column suffix text default ''; ! -- but this should work: alter table fullname add column suffix text default null; select * from people; --- 45,55 ---- select * from people; ! -- accepted, but the default does not propagate alter table fullname add column suffix text default ''; + alter table fullname drop column suffix; ! -- same effect alter table fullname add column suffix text default null; select * from people;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers