ALTER TYPE 0: Introduction; test cases
Started by Noah Mischabout 15 years ago23 messages
This begins the patch series for the design I recently proposed[1]http://archives.postgresql.org/pgsql-hackers/2010-12/msg02360.php for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. I'm posting these
patches today:
0 - new test cases
1 - recheck UNIQUE constraints on ALTER TYPE
2 - skip cases where we can already prove there's no work
3 - add ability to identify more cases; demo with varchar and xml
4 - support temporal types
5 - support varbit
6 - support numeric
Patches 0-2 are each freestanding. Patch 3 depends on patch 2. Patches 4-6 all
depend on 3, but not on each other. I haven't tested permutations of patch
application other than a linear progression, so YMMV -- those are the conceptual
dependencies, though perhaps not the lexical ones.
This first patch adds various test cases that will exercise the conditions
pertinent to this patch series. Later patches generally do not change the test
cases, but they do update the expected output, and this illustrates the
improvements each patch brings. To make that possible, this patch also adds
DEBUG-level messages for when we build/rebuild an index, rewrite a table, scan a
table for CHECK constraint verification, or validate a foreign key constraint.
[1]: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02360.php
Attachments:
at0-debug-tests.patchtext/plain; charset=us-asciiDownload
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4dd89e1..2795511 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1417,6 +1417,10 @@ index_build(Relation heapRelation,
procedure = indexRelation->rd_am->ambuild;
Assert(RegProcedureIsValid(procedure));
+ ereport(IsToastRelation(indexRelation) ? DEBUG2 : DEBUG1,
+ (errmsg("Rebuilding index \"%s\"",
+ RelationGetRelationName(indexRelation))));
+
/*
* Switch to the table owner's userid, so that any index functions are run
* as that user. Also lock down security-restricted operations and
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f3bd565..487d0ac 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -3443,6 +3443,15 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
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,6 +3845,10 @@ ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
* 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.
*
+ * Table, 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,6 +5802,10 @@ validateForeignKeyConstraint(Constraint *fkconstraint,
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/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 3d126bb..387aeaf 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1477,6 +1477,1796 @@ create table tab1 (a int, b text);
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
+--
+-- Deeper alter-column-type tests
+--
+SET client_min_messages = debug1; -- Track rewrites.
+SET timezone = UTC;
+-- 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 loosedom AS text;
+CREATE DOMAIN lendom AS varchar(10);
+CREATE DOMAIN shortdom AS varchar(1);
+CREATE DOMAIN checkdom AS text CHECK (VALUE LIKE 'A_');
+CREATE DOMAIN faildom AS text CHECK (VALUE LIKE 'B_');
+CREATE TABLE parent (keycol numeric PRIMARY KEY);
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
+DEBUG: Rebuilding index "parent_pkey"
+INSERT INTO parent VALUES (1.1), (1.05), (1.15);
+CREATE TABLE t (
+ constraint0 int4 NOT NULL,
+ constraint1 int4 NOT NULL,
+ constraint2 int4 NOT NULL,
+ constraint3 numeric NOT NULL REFERENCES parent,
+ constraint4 numeric UNIQUE NOT NULL,
+ integral int4 UNIQUE NOT NULL,
+ rational numeric UNIQUE NOT NULL,
+ string varchar(2) NOT NULL,
+ daytimetz timetz UNIQUE NOT NULL,
+ daytime time UNIQUE NOT NULL,
+ stamptz timestamptz UNIQUE NOT NULL,
+ stamp timestamp UNIQUE NOT NULL,
+ timegap interval UNIQUE NOT NULL,
+ bits bit varying UNIQUE NOT NULL,
+ network cidr UNIQUE NOT NULL,
+ document xml NOT NULL,
+ strarr varchar(2)[] NOT NULL,
+ square box NOT NULL,
+ CHECK (touchy_f(constraint0) < 10)
+);
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_constraint4_key" for table "t"
+DEBUG: Rebuilding index "t_constraint4_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_integral_key" for table "t"
+DEBUG: Rebuilding index "t_integral_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_rational_key" for table "t"
+DEBUG: Rebuilding index "t_rational_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_daytimetz_key" for table "t"
+DEBUG: Rebuilding index "t_daytimetz_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_daytime_key" for table "t"
+DEBUG: Rebuilding index "t_daytime_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_stamptz_key" for table "t"
+DEBUG: Rebuilding index "t_stamptz_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_stamp_key" for table "t"
+DEBUG: Rebuilding index "t_stamp_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_timegap_key" for table "t"
+DEBUG: Rebuilding index "t_timegap_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_bits_key" for table "t"
+DEBUG: Rebuilding index "t_bits_key"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_network_key" for table "t"
+DEBUG: Rebuilding index "t_network_key"
+CREATE INDEX ON t (string);
+DEBUG: Rebuilding index "t_string_idx"
+CREATE INDEX ON t USING hash (string);
+DEBUG: Rebuilding index "t_string_idx1"
+CREATE INDEX ON t USING gin (strarr);
+DEBUG: Rebuilding index "t_strarr_idx"
+CREATE INDEX ON t USING gist (square);
+DEBUG: Rebuilding index "t_square_idx"
+CREATE UNIQUE INDEX ON t ((touchy_f(constraint1)));
+DEBUG: Rebuilding index "t_touchy_f_idx"
+CREATE UNIQUE INDEX ON t ((1)) WHERE touchy_f(constraint2) = 100;
+DEBUG: Rebuilding index "t_expr_idx"
+INSERT INTO t VALUES
+(
+ 1,
+ 2,
+ 3,
+ 1.05,
+ 1.06,
+ 4,
+ 10.12,
+ 'AB',
+ '8:44:00.19',
+ '8:44:00.29',
+ '2000-01-01 08:44:00.39',
+ '2000-01-01 08:44:00.49',
+ '00:00:01.59',
+ '011101',
+ '10.0.0.0/16',
+ '<foo/>',
+ '{ab,cd}',
+ '(1,1),(0,0)'
+),
+(
+ 2,
+ 3,
+ 4,
+ 1.15,
+ 1.16,
+ 5,
+ 11.12,
+ 'AC',
+ '9:44:00.19',
+ '9:44:00.29',
+ '2000-01-01 09:44:00.39',
+ '2000-01-01 09:44:00.49',
+ '00:00:02.59',
+ '001101',
+ '10.1.0.0/16',
+ '<bar/>',
+ '{ef,gh}',
+ '(2,2),(0,0)'
+);
+CREATE TABLE child (keycol numeric PRIMARY KEY REFERENCES t(constraint4));
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "child_pkey" for table "child"
+DEBUG: Rebuilding index "child_pkey"
+INSERT INTO child VALUES (1.06), (1.16);
+-- Data and catalog begin state. Keep this synchronized with the copy below.
+SELECT * FROM t ORDER BY 1;
+ constraint0 | constraint1 | constraint2 | constraint3 | constraint4 | integral | rational | string | daytimetz | daytime | stamptz | stamp | timegap | bits | network | document | strarr | square
+-------------+-------------+-------------+-------------+-------------+----------+----------+--------+----------------+-------------+---------------------------------+-----------------------------+-------------+--------+-------------+----------+---------+-------------
+ 1 | 2 | 3 | 1.05 | 1.06 | 4 | 10.12 | AB | 08:44:00.19+00 | 08:44:00.29 | Sat Jan 01 08:44:00.39 2000 UTC | Sat Jan 01 08:44:00.49 2000 | @ 1.59 secs | 011101 | 10.0.0.0/16 | <foo/> | {ab,cd} | (1,1),(0,0)
+ 2 | 3 | 4 | 1.15 | 1.16 | 5 | 11.12 | AC | 09:44:00.19+00 | 09:44:00.29 | Sat Jan 01 09:44:00.39 2000 UTC | Sat Jan 01 09:44:00.49 2000 | @ 2.59 secs | 001101 | 10.1.0.0/16 | <bar/> | {ef,gh} | (2,2),(0,0)
+(2 rows)
+
+SELECT relname, relnamespace, relowner, relam, reltablespace, relhasindex,
+ relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids,
+ relhaspkey, relhasexclusion, relhasrules, relhastriggers, relacl,
+ reloptions
+FROM pg_class WHERE oid = 't'::regclass
+ OR oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1;
+ relname | relnamespace | relowner | relam | reltablespace | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules | relhastriggers | relacl | reloptions
+-------------------+--------------+----------+-------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-----------------+-------------+----------------+--------+------------
+ t | 2200 | 10 | 0 | 0 | t | f | p | r | 18 | 1 | f | f | f | f | t | |
+ t_bits_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_constraint4_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_daytime_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_daytimetz_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_expr_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_integral_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_network_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_rational_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_square_idx | 2200 | 10 | 783 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_stamp_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_stamptz_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_strarr_idx | 2200 | 10 | 2742 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_string_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_string_idx1 | 2200 | 10 | 405 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_timegap_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_touchy_f_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+(17 rows)
+
+SELECT relname, indnatts, indisunique, indisprimary, indimmediate,
+ indisclustered, indisvalid, indcheckxmin, indisready, indkey, indclass,
+ indoption
+FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+WHERE indrelid = 't'::regclass ORDER BY 1;
+ relname | indnatts | indisunique | indisprimary | indimmediate | indisclustered | indisvalid | indcheckxmin | indisready | indkey | indclass | indoption
+-------------------+----------+-------------+--------------+--------------+----------------+------------+--------------+------------+--------+----------+-----------
+ t_bits_key | 1 | t | f | t | f | t | f | t | 14 | 10051 | 0
+ t_constraint4_key | 1 | t | f | t | f | t | f | t | 5 | 10037 | 0
+ t_daytime_key | 1 | t | f | t | f | t | f | t | 10 | 10045 | 0
+ t_daytimetz_key | 1 | t | f | t | f | t | f | t | 9 | 10049 | 0
+ t_expr_idx | 1 | t | f | t | f | t | f | t | 0 | 1978 | 0
+ t_integral_key | 1 | t | f | t | f | t | f | t | 6 | 1978 | 0
+ t_network_key | 1 | t | f | t | f | t | f | t | 15 | 10025 | 0
+ t_rational_key | 1 | t | f | t | f | t | f | t | 7 | 10037 | 0
+ t_square_idx | 1 | f | f | t | f | t | f | t | 18 | 10074 | 0
+ t_stamp_key | 1 | t | f | t | f | t | f | t | 12 | 10054 | 0
+ t_stamptz_key | 1 | t | f | t | f | t | f | t | 11 | 10047 | 0
+ t_strarr_idx | 1 | f | f | t | f | t | f | t | 17 | 10103 | 0
+ t_string_idx | 1 | f | f | t | f | t | f | t | 8 | 10043 | 0
+ t_string_idx1 | 1 | f | f | t | f | t | f | t | 8 | 10044 | 0
+ t_timegap_key | 1 | t | f | t | f | t | f | t | 13 | 10031 | 0
+ t_touchy_f_idx | 1 | t | f | t | f | t | f | t | 0 | 1978 | 0
+(16 rows)
+
+SELECT relname, attname, atttypid, attstattarget, attlen, attnum, attndims,
+ attcacheoff, atttypmod, attbyval, attstorage, attalign, attnotnull,
+ atthasdef, attisdropped, attislocal, attinhcount, attacl, attoptions
+FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+WHERE attrelid = 't'::regclass OR
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1, 2;
+ relname | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attacl | attoptions
+-------------------+-------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------+------------
+ t | bits | 1562 | -1 | -1 | 14 | 0 | -1 | -1 | f | x | i | t | f | f | t | 0 | |
+ t | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint0 | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint1 | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint2 | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint3 | 1700 | -1 | -1 | 4 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | constraint4 | 1700 | -1 | -1 | 5 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | t | 0 | |
+ t | daytime | 1083 | -1 | 8 | 10 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | daytimetz | 1266 | -1 | 12 | 9 | 0 | -1 | -1 | f | p | d | t | f | f | t | 0 | |
+ t | document | 142 | -1 | -1 | 16 | 0 | -1 | -1 | f | x | i | t | f | f | t | 0 | |
+ t | integral | 23 | -1 | 4 | 6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | network | 650 | -1 | -1 | 15 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | rational | 1700 | -1 | -1 | 7 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | square | 603 | -1 | 32 | 18 | 0 | -1 | -1 | f | p | d | t | f | f | t | 0 | |
+ t | stamp | 1114 | -1 | 8 | 12 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | stamptz | 1184 | -1 | 8 | 11 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | strarr | 1015 | -1 | -1 | 17 | 1 | -1 | 6 | f | x | i | t | f | f | t | 0 | |
+ t | string | 1043 | -1 | -1 | 8 | 0 | -1 | 6 | f | x | i | t | f | f | t | 0 | |
+ t | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | timegap | 1186 | -1 | 16 | 13 | 0 | -1 | -1 | f | p | d | t | f | f | t | 0 | |
+ t | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t_bits_key | bits | 1562 | -1 | -1 | 1 | 0 | -1 | -1 | f | x | i | f | f | f | t | 0 | |
+ t_constraint4_key | constraint4 | 1700 | -1 | -1 | 1 | 0 | -1 | -1 | f | m | i | f | f | f | t | 0 | |
+ t_daytime_key | daytime | 1083 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_daytimetz_key | daytimetz | 1266 | -1 | 12 | 1 | 0 | -1 | -1 | f | p | d | f | f | f | t | 0 | |
+ t_expr_idx | expr | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_integral_key | integral | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_network_key | network | 650 | -1 | -1 | 1 | 0 | -1 | -1 | f | m | i | f | f | f | t | 0 | |
+ t_rational_key | rational | 1700 | -1 | -1 | 1 | 0 | -1 | -1 | f | m | i | f | f | f | t | 0 | |
+ t_square_idx | square | 603 | -1 | 32 | 1 | 0 | -1 | -1 | f | p | d | f | f | f | t | 0 | |
+ t_stamp_key | stamp | 1114 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_stamptz_key | stamptz | 1184 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_strarr_idx | strarr | 1043 | -1 | -1 | 1 | 1 | -1 | -1 | f | x | i | f | f | f | t | 0 | |
+ t_string_idx | string | 1043 | -1 | -1 | 1 | 0 | -1 | 6 | f | x | i | f | f | f | t | 0 | |
+ t_string_idx1 | string | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_timegap_key | timegap | 1186 | -1 | 16 | 1 | 0 | -1 | -1 | f | p | d | f | f | f | t | 0 | |
+ t_touchy_f_idx | touchy_f | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+(40 rows)
+
+SELECT conname, connamespace, contype, condeferrable, condeferred, confupdtype,
+ confdeltype, confmatchtype, conislocal, coninhcount, conkey, confkey,
+ conpfeqop, conppeqop, conffeqop, conexclop, consrc
+FROM pg_constraint WHERE conrelid = 't'::regclass
+ORDER BY 1;
+ conname | connamespace | contype | condeferrable | condeferred | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | consrc
+---------------------+--------------+---------+---------------+-------------+-------------+-------------+---------------+------------+-------------+--------+---------+-----------+-----------+-----------+-----------+------------------------------
+ t_bits_key | 2200 | u | f | f | | | | t | 0 | {14} | | | | | |
+ t_constraint0_check | 2200 | c | f | f | | | | t | 0 | {1} | | | | | | (touchy_f(constraint0) < 10)
+ t_constraint3_fkey | 2200 | f | f | f | a | a | u | t | 0 | {4} | {1} | {1752} | {1752} | {1752} | |
+ t_constraint4_key | 2200 | u | f | f | | | | t | 0 | {5} | | | | | |
+ t_daytime_key | 2200 | u | f | f | | | | t | 0 | {10} | | | | | |
+ t_daytimetz_key | 2200 | u | f | f | | | | t | 0 | {9} | | | | | |
+ t_integral_key | 2200 | u | f | f | | | | t | 0 | {6} | | | | | |
+ t_network_key | 2200 | u | f | f | | | | t | 0 | {15} | | | | | |
+ t_rational_key | 2200 | u | f | f | | | | t | 0 | {7} | | | | | |
+ t_stamp_key | 2200 | u | f | f | | | | t | 0 | {12} | | | | | |
+ t_stamptz_key | 2200 | u | f | f | | | | t | 0 | {11} | | | | | |
+ t_timegap_key | 2200 | u | f | f | | | | t | 0 | {13} | | | | | |
+(12 rows)
+
+-- RI trigger names include the table OID. We don't have a great sort order
+-- available, but tgname probably serves acceptably in practice.
+SELECT tgfoid, tgtype, tgenabled, tgisinternal, tgdeferrable, tginitdeferred,
+ tgnargs, tgattr, tgargs, tgqual
+FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY tgname;
+ tgfoid | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+--------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ 1644 | 5 | O | t | f | f | 0 | | \x |
+ 1645 | 17 | O | t | f | f | 0 | | \x |
+ 1654 | 9 | O | t | f | f | 0 | | \x |
+ 1655 | 17 | O | t | f | f | 0 | | \x |
+(4 rows)
+
+-- Comments "rewrite", "verify" and "noop" signify whether ATRewriteTables
+-- rewrites, scans or does nothing to the table proper. An "-e" suffix denotes
+-- an error outcome. A "-v" suffix means that the rewrite does not actually
+-- change data, but we did it anyway out of an inability to prove that in
+-- advance. Decisions to rebuild indexes or recheck foreign keys are notable,
+-- 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"
+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"
+-- Temporary fixup until behavior of the previous two improves.
+ALTER TABLE t ALTER constraint1 TYPE int, ALTER constraint2 TYPE int;
+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"
+-- Change a column with an outgoing foreign key constraint.
+ALTER TABLE t ALTER constraint3 TYPE numeric(8,1); -- rewrite, FK error
+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"
+DEBUG: Validating foreign key constraint "t_constraint3_fkey"
+ERROR: insert or update on table "t" violates foreign key constraint "t_constraint3_fkey"
+DETAIL: Key (constraint3)=(1.2) is not present in table "parent".
+ALTER TABLE t ALTER constraint3 TYPE numeric(8,2); -- rewrite, FK verify
+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"
+DEBUG: Validating foreign key constraint "t_constraint3_fkey"
+ALTER TABLE t ALTER constraint3 TYPE numeric(7,2); -- verify; FK noop
+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"
+DEBUG: Validating foreign key constraint "t_constraint3_fkey"
+ALTER TABLE t ALTER constraint3 TYPE numeric(9,2); -- noop; FK noop
+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"
+DEBUG: Validating foreign key constraint "t_constraint3_fkey"
+-- Change a column with an incoming foreign key constraint.
+ALTER TABLE t ALTER constraint4 TYPE numeric(8,1); -- rewrite; FK error
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_constraint4_key"
+DEBUG: Validating foreign key constraint "child_keycol_fkey"
+ERROR: insert or update on table "child" violates foreign key constraint "child_keycol_fkey"
+DETAIL: Key (keycol)=(1.06) is not present in table "t".
+ALTER TABLE t ALTER constraint4 TYPE numeric(8,2); -- rewrite; FK verify
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_constraint4_key"
+DEBUG: Validating foreign key constraint "child_keycol_fkey"
+ALTER TABLE t ALTER constraint4 TYPE numeric(7,2); -- verify; FK noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_constraint4_key"
+DEBUG: Validating foreign key constraint "child_keycol_fkey"
+ALTER TABLE t ALTER constraint4 TYPE numeric(9,2); -- noop; FK noop
+DEBUG: Rewriting table "t"
+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"
+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"
+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 int2; -- rewrite
+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 rational TYPE numeric(10,2); -- rewrite-v
+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(12,4); -- rewrite
+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(13,4); -- 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(11,4); -- 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_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; -- 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"
+ERROR: numeric field overflow
+DETAIL: A field with precision 5, scale 4 must round to an absolute value less than 10^1.
+ALTER TABLE t ALTER rational TYPE numeric(4,3); -- rewrite-e
+DEBUG: Rewriting table "t"
+ERROR: numeric field overflow
+DETAIL: A field with precision 4, scale 3 must round to an absolute value less than 10^1.
+ALTER TABLE t ALTER string TYPE varchar(4); -- 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 lendom; -- 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 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
+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 varchar(1); -- rewrite-e
+DEBUG: Rewriting table "t"
+ERROR: value too long for type character varying(1)
+ALTER TABLE t ALTER string TYPE varchar(1) USING t::varchar(1); -- rewrite
+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 text USING 'foo'::varchar; -- rewrite
+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 char(3); -- rewrite-v
+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 char(2); -- rewrite-e
+DEBUG: Rewriting table "t"
+ERROR: value too long for type character(2)
+ALTER TABLE t ALTER string TYPE char(2) USING string::char(2); -- rewrite
+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 char(4); -- rewrite
+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 daytimetz TYPE timetz(3); -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+ALTER TABLE t ALTER daytimetz TYPE timetz(1); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+ALTER TABLE t ALTER daytimetz TYPE timetz(2); -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+ALTER TABLE t ALTER daytimetz TYPE time; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+ALTER TABLE t ALTER daytime TYPE time(3); -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+ALTER TABLE t ALTER daytime TYPE time(1); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+ALTER TABLE t ALTER daytime TYPE time(2); -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+ALTER TABLE t ALTER daytime TYPE timetz; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+ALTER TABLE t ALTER stamptz TYPE timestamptz(3); -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+ALTER TABLE t ALTER stamptz TYPE timestamptz(1); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+ALTER TABLE t ALTER stamptz TYPE timestamptz(2); -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+ALTER TABLE t ALTER stamptz TYPE timestamp; -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+SET timezone = 'Asia/Jakarta';
+ALTER TABLE t ALTER stamptz TYPE timestamptz; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+SET timezone = 'UTC';
+ALTER TABLE t ALTER stamp TYPE timestamp(3); -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+DEBUG: Rebuilding index "t_stamp_key"
+ALTER TABLE t ALTER stamp TYPE timestamp(1); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+DEBUG: Rebuilding index "t_stamp_key"
+ALTER TABLE t ALTER stamp TYPE timestamp(2); -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+DEBUG: Rebuilding index "t_stamp_key"
+ALTER TABLE t ALTER stamp TYPE timestamptz; -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+DEBUG: Rebuilding index "t_stamp_key"
+SET timezone = 'Asia/Jakarta';
+ALTER TABLE t ALTER stamp TYPE timestamp; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_daytimetz_key"
+DEBUG: Rebuilding index "t_daytime_key"
+DEBUG: Rebuilding index "t_stamptz_key"
+DEBUG: Rebuilding index "t_stamp_key"
+SET timezone = 'UTC';
+ALTER TABLE t ALTER timegap TYPE interval(3); -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER timegap TYPE interval(1); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER timegap TYPE interval(2); -- noop
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER bits TYPE bit(6); -- verify
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER bits TYPE bit(7); -- verify-e
+DEBUG: Rewriting table "t"
+ERROR: bit string length 6 does not match type bit(7)
+ALTER TABLE t ALTER bits TYPE bit(7) USING bits::bit(7); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+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"
+-- Next one could be a made a noop with an added cast.
+ALTER TABLE t ALTER bits TYPE varbit(8); -- verify
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER bits TYPE varbit(7); -- verify
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER bits TYPE varbit(5); -- verify-e
+DEBUG: Rewriting table "t"
+ERROR: bit string too long for type bit varying(5)
+ALTER TABLE t ALTER bits TYPE varbit(5) USING bits::varbit(5); -- rewrite
+DEBUG: Rewriting table "t"
+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"
+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"
+ALTER TABLE t ALTER bits TYPE varbit(8); -- noop
+DEBUG: Rewriting table "t"
+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"
+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"
+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"
+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 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"
+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 char(20); -- rewrite
+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"
+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 varchar(30); -- rewrite-v
+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"
+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 strarr TYPE varchar(4)[]; -- noop
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_strarr_idx"
+ALTER TABLE t ALTER strarr TYPE varchar(3)[]; -- rewrite-v
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_strarr_idx"
+ALTER TABLE t ALTER strarr TYPE varchar(1)[]; -- rewrite-e
+DEBUG: Rewriting table "t"
+ERROR: value too long for type character varying(1)
+ALTER TABLE t ALTER strarr TYPE text[]; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_strarr_idx"
+ALTER TABLE t ALTER square TYPE polygon; -- rewrite
+DEBUG: Rewriting table "t"
+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"
+DEBUG: Rebuilding index "t_strarr_idx"
+DEBUG: Rebuilding index "t_square_idx"
+ALTER TABLE t ADD CONSTRAINT u0 UNIQUE (integral); -- build index exactly once
+NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "u0" for table "t"
+DEBUG: Rebuilding index "u0"
+ALTER TABLE t ADD CONSTRAINT u1 UNIQUE (integral), -- build index exactly once
+ ALTER integral TYPE int4; -- rewrite
+NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "u1" for table "t"
+DEBUG: Rewriting table "t"
+DEBUG: Rebuilding index "t_touchy_f_idx"
+DEBUG: Rebuilding index "t_expr_idx"
+DEBUG: Rebuilding index "t_constraint4_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"
+DEBUG: Rebuilding index "t_strarr_idx"
+DEBUG: Rebuilding index "t_square_idx"
+DEBUG: Rebuilding index "u0"
+DEBUG: Rebuilding index "t_integral_key"
+DEBUG: Rebuilding index "u1"
+-- Data and catalog end state. We omit the columns that bear unstable OIDs.
+SELECT * FROM t ORDER BY 1;
+ constraint0 | constraint1 | constraint2 | constraint3 | constraint4 | integral | rational | string | daytimetz | daytime | stamptz | stamp | timegap | bits | network | document | strarr | square
+-------------+-------------+-------------+-------------+-------------+----------+----------+--------+------------+---------------+--------------------------------+----------------------------+------------+-------+-------------+----------------------+---------+---------------------------
+ 1 | 2 | 3 | 1.05 | 1.06 | 4 | 10.1200 | fo | 08:44:00.2 | 08:44:00.3+00 | Sat Jan 01 01:44:00.4 2000 UTC | Sat Jan 01 15:44:00.5 2000 | @ 1.6 secs | 01110 | 10.0.0.0/16 | <foo/> | {ab,cd} | ((0,0),(0,1),(1,1),(1,0))
+ 2 | 3 | 4 | 1.15 | 1.16 | 5 | 11.1200 | fo | 09:44:00.2 | 09:44:00.3+00 | Sat Jan 01 02:44:00.4 2000 UTC | Sat Jan 01 16:44:00.5 2000 | @ 2.6 secs | 00110 | 10.1.0.0/16 | <bar/> | {ef,gh} | ((0,0),(0,2),(2,2),(2,0))
+(2 rows)
+
+SELECT relname, relnamespace, relowner, relam, reltablespace, relhasindex,
+ relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids,
+ relhaspkey, relhasexclusion, relhasrules, relhastriggers, relacl,
+ reloptions
+FROM pg_class WHERE oid = 't'::regclass
+ OR oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1;
+ relname | relnamespace | relowner | relam | reltablespace | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules | relhastriggers | relacl | reloptions
+-------------------+--------------+----------+-------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-----------------+-------------+----------------+--------+------------
+ t | 2200 | 10 | 0 | 0 | t | f | p | r | 18 | 1 | f | f | f | f | t | |
+ t_bits_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_constraint4_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_daytime_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_daytimetz_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_expr_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_integral_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_network_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_rational_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_square_idx | 2200 | 10 | 783 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_stamp_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_stamptz_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_strarr_idx | 2200 | 10 | 2742 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_string_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_string_idx1 | 2200 | 10 | 405 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_timegap_key | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ t_touchy_f_idx | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ u0 | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+ u1 | 2200 | 10 | 403 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | |
+(19 rows)
+
+SELECT relname, indnatts, indisunique, indisprimary, indimmediate,
+ indisclustered, indisvalid, indcheckxmin, indisready, indkey, indclass,
+ indoption
+FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+WHERE indrelid = 't'::regclass ORDER BY 1;
+ relname | indnatts | indisunique | indisprimary | indimmediate | indisclustered | indisvalid | indcheckxmin | indisready | indkey | indclass | indoption
+-------------------+----------+-------------+--------------+--------------+----------------+------------+--------------+------------+--------+----------+-----------
+ t_bits_key | 1 | t | f | t | f | t | f | t | 14 | 10051 | 0
+ t_constraint4_key | 1 | t | f | t | f | t | f | t | 5 | 10037 | 0
+ t_daytime_key | 1 | t | f | t | f | t | f | t | 10 | 10049 | 0
+ t_daytimetz_key | 1 | t | f | t | f | t | f | t | 9 | 10045 | 0
+ t_expr_idx | 1 | t | f | t | f | t | f | t | 0 | 1978 | 0
+ t_integral_key | 1 | t | f | t | f | t | f | t | 6 | 1978 | 0
+ t_network_key | 1 | t | f | t | f | t | f | t | 15 | 10025 | 0
+ t_rational_key | 1 | t | f | t | f | t | f | t | 7 | 10037 | 0
+ t_square_idx | 1 | f | f | t | f | t | f | t | 18 | 10076 | 0
+ t_stamp_key | 1 | t | f | t | f | t | f | t | 12 | 10054 | 0
+ t_stamptz_key | 1 | t | f | t | f | t | f | t | 11 | 10047 | 0
+ t_strarr_idx | 1 | f | f | t | f | t | f | t | 17 | 10079 | 0
+ t_string_idx | 1 | f | f | t | f | t | f | t | 8 | 10012 | 0
+ t_string_idx1 | 1 | f | f | t | f | t | f | t | 8 | 10013 | 0
+ t_timegap_key | 1 | t | f | t | f | t | f | t | 13 | 10031 | 0
+ t_touchy_f_idx | 1 | t | f | t | f | t | f | t | 0 | 1978 | 0
+ u0 | 1 | t | f | t | f | t | f | t | 6 | 1978 | 0
+ u1 | 1 | t | f | t | f | t | f | t | 6 | 1978 | 0
+(18 rows)
+
+SELECT relname, attname, atttypid, attstattarget, attlen, attnum, attndims,
+ attcacheoff, atttypmod, attbyval, attstorage, attalign, attnotnull,
+ atthasdef, attisdropped, attislocal, attinhcount, attacl, attoptions
+FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+WHERE attrelid = 't'::regclass OR
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1, 2;
+ relname | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attacl | attoptions
+-------------------+-------------+----------+---------------+--------+--------+----------+-------------+------------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------+------------
+ t | bits | 1562 | -1 | -1 | 14 | 0 | -1 | 8 | f | x | i | t | f | f | t | 0 | |
+ t | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint0 | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint1 | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint2 | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | constraint3 | 1700 | -1 | -1 | 4 | 0 | -1 | 589830 | f | m | i | t | f | f | t | 0 | |
+ t | constraint4 | 1700 | -1 | -1 | 5 | 0 | -1 | 589830 | f | m | i | t | f | f | t | 0 | |
+ t | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | t | 0 | |
+ t | daytime | 1266 | -1 | 12 | 10 | 0 | -1 | -1 | f | p | d | t | f | f | t | 0 | |
+ t | daytimetz | 1083 | -1 | 8 | 9 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | document | 142 | -1 | -1 | 16 | 0 | -1 | -1 | f | x | i | t | f | f | t | 0 | |
+ t | integral | 23 | -1 | 4 | 6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | network | 650 | -1 | -1 | 15 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | rational | 1700 | -1 | -1 | 7 | 0 | -1 | -1 | f | m | i | t | f | f | t | 0 | |
+ t | square | 604 | -1 | -1 | 18 | 0 | -1 | -1 | f | x | d | t | f | f | t | 0 | |
+ t | stamp | 1114 | -1 | 8 | 12 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | stamptz | 1184 | -1 | 8 | 11 | 0 | -1 | -1 | t | p | d | t | f | f | t | 0 | |
+ t | strarr | 1009 | -1 | -1 | 17 | 1 | -1 | -1 | f | x | i | t | f | f | t | 0 | |
+ t | string | 1042 | -1 | -1 | 8 | 0 | -1 | 8 | f | x | i | t | f | f | t | 0 | |
+ t | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | timegap | 1186 | -1 | 16 | 13 | 0 | -1 | 2147418114 | f | p | d | t | f | f | t | 0 | |
+ t | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | |
+ t_bits_key | bits | 1562 | -1 | -1 | 1 | 0 | -1 | 8 | f | x | i | f | f | f | t | 0 | |
+ t_constraint4_key | constraint4 | 1700 | -1 | -1 | 1 | 0 | -1 | 589830 | f | m | i | f | f | f | t | 0 | |
+ t_daytime_key | daytime | 1266 | -1 | 12 | 1 | 0 | -1 | -1 | f | p | d | f | f | f | t | 0 | |
+ t_daytimetz_key | daytimetz | 1083 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_expr_idx | expr | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_integral_key | integral | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_network_key | network | 650 | -1 | -1 | 1 | 0 | -1 | -1 | f | m | i | f | f | f | t | 0 | |
+ t_rational_key | rational | 1700 | -1 | -1 | 1 | 0 | -1 | -1 | f | m | i | f | f | f | t | 0 | |
+ t_square_idx | square | 603 | -1 | 32 | 1 | 0 | -1 | -1 | f | p | d | f | f | f | t | 0 | |
+ t_stamp_key | stamp | 1114 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_stamptz_key | stamptz | 1184 | -1 | 8 | 1 | 0 | -1 | -1 | t | p | d | f | f | f | t | 0 | |
+ t_strarr_idx | strarr | 25 | -1 | -1 | 1 | 1 | -1 | -1 | f | x | i | f | f | f | t | 0 | |
+ t_string_idx | string | 1042 | -1 | -1 | 1 | 0 | -1 | 8 | f | x | i | f | f | f | t | 0 | |
+ t_string_idx1 | string | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ t_timegap_key | timegap | 1186 | -1 | 16 | 1 | 0 | -1 | 2147418114 | f | p | d | f | f | f | t | 0 | |
+ t_touchy_f_idx | touchy_f | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ u0 | integral | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+ u1 | integral | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 | |
+(42 rows)
+
+SELECT conname, connamespace, contype, condeferrable, condeferred, confupdtype,
+ confdeltype, confmatchtype, conislocal, coninhcount, conkey, confkey,
+ conpfeqop, conppeqop, conffeqop, conexclop, consrc
+FROM pg_constraint WHERE conrelid = 't'::regclass
+ORDER BY 1;
+ conname | connamespace | contype | condeferrable | condeferred | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | consrc
+---------------------+--------------+---------+---------------+-------------+-------------+-------------+---------------+------------+-------------+--------+---------+-----------+-----------+-----------+-----------+------------------------------
+ t_bits_key | 2200 | u | f | f | | | | t | 0 | {14} | | | | | |
+ t_constraint0_check | 2200 | c | f | f | | | | t | 0 | {1} | | | | | | (touchy_f(constraint0) < 10)
+ t_constraint3_fkey | 2200 | f | f | f | a | a | u | t | 0 | {4} | {1} | {1752} | {1752} | {1752} | |
+ t_constraint4_key | 2200 | u | f | f | | | | t | 0 | {5} | | | | | |
+ t_daytime_key | 2200 | u | f | f | | | | t | 0 | {10} | | | | | |
+ t_daytimetz_key | 2200 | u | f | f | | | | t | 0 | {9} | | | | | |
+ t_integral_key | 2200 | u | f | f | | | | t | 0 | {6} | | | | | |
+ t_network_key | 2200 | u | f | f | | | | t | 0 | {15} | | | | | |
+ t_rational_key | 2200 | u | f | f | | | | t | 0 | {7} | | | | | |
+ t_stamp_key | 2200 | u | f | f | | | | t | 0 | {12} | | | | | |
+ t_stamptz_key | 2200 | u | f | f | | | | t | 0 | {11} | | | | | |
+ t_timegap_key | 2200 | u | f | f | | | | t | 0 | {13} | | | | | |
+ u0 | 2200 | u | f | f | | | | t | 0 | {6} | | | | | |
+ u1 | 2200 | u | f | f | | | | t | 0 | {6} | | | | | |
+(14 rows)
+
+-- RI trigger names include the table OID. We don't have a great sort order
+-- available, but tgname probably serves acceptably in practice.
+SELECT tgfoid, tgtype, tgenabled, tgisinternal, tgdeferrable, tginitdeferred,
+ tgnargs, tgattr, tgargs, tgqual
+FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY tgname;
+ tgfoid | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+--------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ 1644 | 5 | O | t | f | f | 0 | | \x |
+ 1645 | 17 | O | t | f | f | 0 | | \x |
+ 1654 | 9 | O | t | f | f | 0 | | \x |
+ 1655 | 17 | O | t | f | f | 0 | | \x |
+(4 rows)
+
+-- Done. Retain the table under a less-generic name.
+ALTER TABLE t RENAME TO alter_type_test;
+RESET client_min_messages;
+RESET timezone;
--
-- lock levels
--
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 4895768..18d60e1 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1094,6 +1094,308 @@ drop table another;
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
+
+--
+-- Deeper alter-column-type tests
+--
+SET client_min_messages = debug1; -- Track rewrites.
+SET timezone = UTC;
+
+-- 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 loosedom AS text;
+CREATE DOMAIN lendom AS varchar(10);
+CREATE DOMAIN shortdom AS varchar(1);
+CREATE DOMAIN checkdom AS text CHECK (VALUE LIKE 'A_');
+CREATE DOMAIN faildom AS text CHECK (VALUE LIKE 'B_');
+
+CREATE TABLE parent (keycol numeric PRIMARY KEY);
+INSERT INTO parent VALUES (1.1), (1.05), (1.15);
+
+CREATE TABLE t (
+ constraint0 int4 NOT NULL,
+ constraint1 int4 NOT NULL,
+ constraint2 int4 NOT NULL,
+ constraint3 numeric NOT NULL REFERENCES parent,
+ constraint4 numeric UNIQUE NOT NULL,
+
+ integral int4 UNIQUE NOT NULL,
+ rational numeric UNIQUE NOT NULL,
+ string varchar(2) NOT NULL,
+ daytimetz timetz UNIQUE NOT NULL,
+ daytime time UNIQUE NOT NULL,
+ stamptz timestamptz UNIQUE NOT NULL,
+ stamp timestamp UNIQUE NOT NULL,
+ timegap interval UNIQUE NOT NULL,
+ bits bit varying UNIQUE NOT NULL,
+ network cidr UNIQUE NOT NULL,
+ document xml NOT NULL,
+ strarr varchar(2)[] NOT NULL,
+ square box NOT NULL,
+
+ CHECK (touchy_f(constraint0) < 10)
+);
+CREATE INDEX ON t (string);
+CREATE INDEX ON t USING hash (string);
+CREATE INDEX ON t USING gin (strarr);
+CREATE INDEX ON t USING gist (square);
+CREATE UNIQUE INDEX ON t ((touchy_f(constraint1)));
+CREATE UNIQUE INDEX ON t ((1)) WHERE touchy_f(constraint2) = 100;
+INSERT INTO t VALUES
+(
+ 1,
+ 2,
+ 3,
+ 1.05,
+ 1.06,
+
+ 4,
+ 10.12,
+ 'AB',
+ '8:44:00.19',
+ '8:44:00.29',
+ '2000-01-01 08:44:00.39',
+ '2000-01-01 08:44:00.49',
+ '00:00:01.59',
+ '011101',
+ '10.0.0.0/16',
+ '<foo/>',
+ '{ab,cd}',
+ '(1,1),(0,0)'
+),
+(
+ 2,
+ 3,
+ 4,
+ 1.15,
+ 1.16,
+
+ 5,
+ 11.12,
+ 'AC',
+ '9:44:00.19',
+ '9:44:00.29',
+ '2000-01-01 09:44:00.39',
+ '2000-01-01 09:44:00.49',
+ '00:00:02.59',
+ '001101',
+ '10.1.0.0/16',
+ '<bar/>',
+ '{ef,gh}',
+ '(2,2),(0,0)'
+);
+
+CREATE TABLE child (keycol numeric PRIMARY KEY REFERENCES t(constraint4));
+INSERT INTO child VALUES (1.06), (1.16);
+
+
+-- Data and catalog begin state. Keep this synchronized with the copy below.
+SELECT * FROM t ORDER BY 1;
+
+SELECT relname, relnamespace, relowner, relam, reltablespace, relhasindex,
+ relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids,
+ relhaspkey, relhasexclusion, relhasrules, relhastriggers, relacl,
+ reloptions
+FROM pg_class WHERE oid = 't'::regclass
+ OR oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1;
+
+SELECT relname, indnatts, indisunique, indisprimary, indimmediate,
+ indisclustered, indisvalid, indcheckxmin, indisready, indkey, indclass,
+ indoption
+FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+WHERE indrelid = 't'::regclass ORDER BY 1;
+
+SELECT relname, attname, atttypid, attstattarget, attlen, attnum, attndims,
+ attcacheoff, atttypmod, attbyval, attstorage, attalign, attnotnull,
+ atthasdef, attisdropped, attislocal, attinhcount, attacl, attoptions
+FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+WHERE attrelid = 't'::regclass OR
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1, 2;
+
+SELECT conname, connamespace, contype, condeferrable, condeferred, confupdtype,
+ confdeltype, confmatchtype, conislocal, coninhcount, conkey, confkey,
+ conpfeqop, conppeqop, conffeqop, conexclop, consrc
+FROM pg_constraint WHERE conrelid = 't'::regclass
+ORDER BY 1;
+
+-- RI trigger names include the table OID. We don't have a great sort order
+-- available, but tgname probably serves acceptably in practice.
+SELECT tgfoid, tgtype, tgenabled, tgisinternal, tgdeferrable, tginitdeferred,
+ tgnargs, tgattr, tgargs, tgqual
+FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY tgname;
+
+
+-- Comments "rewrite", "verify" and "noop" signify whether ATRewriteTables
+-- rewrites, scans or does nothing to the table proper. An "-e" suffix denotes
+-- an error outcome. A "-v" suffix means that the rewrite does not actually
+-- change data, but we did it anyway out of an inability to prove that in
+-- advance. Decisions to rebuild indexes or recheck foreign keys are notable,
+-- though mostly not stated here.
+
+-- Constraint failures induced by a no-work type change.
+ALTER TABLE t ALTER constraint0 TYPE trickint; -- verify-e
+ALTER TABLE t ALTER constraint1 TYPE trickint; -- noop-e
+ALTER TABLE t ALTER constraint2 TYPE trickint; -- noop-e
+-- Temporary fixup until behavior of the previous two improves.
+ALTER TABLE t ALTER constraint1 TYPE int, ALTER constraint2 TYPE int;
+
+-- Change a column with an outgoing foreign key constraint.
+ALTER TABLE t ALTER constraint3 TYPE numeric(8,1); -- rewrite, FK error
+ALTER TABLE t ALTER constraint3 TYPE numeric(8,2); -- rewrite, FK verify
+ALTER TABLE t ALTER constraint3 TYPE numeric(7,2); -- verify; FK noop
+ALTER TABLE t ALTER constraint3 TYPE numeric(9,2); -- noop; FK noop
+
+-- Change a column with an incoming foreign key constraint.
+ALTER TABLE t ALTER constraint4 TYPE numeric(8,1); -- rewrite; FK error
+ALTER TABLE t ALTER constraint4 TYPE numeric(8,2); -- rewrite; FK verify
+ALTER TABLE t ALTER constraint4 TYPE numeric(7,2); -- verify; FK noop
+ALTER TABLE t ALTER constraint4 TYPE numeric(9,2); -- noop; FK noop
+
+-- Type-specific tests.
+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 integral TYPE int8; -- rewrite
+ALTER TABLE t ALTER integral TYPE int2; -- rewrite
+
+ALTER TABLE t ALTER rational TYPE numeric(10,2); -- rewrite-v
+ALTER TABLE t ALTER rational TYPE numeric(12,4); -- rewrite
+ALTER TABLE t ALTER rational TYPE numeric(13,4); -- noop
+ALTER TABLE t ALTER rational TYPE numeric(11,4); -- verify
+ALTER TABLE t ALTER rational TYPE numeric; -- noop
+ALTER TABLE t ALTER rational TYPE numeric(5,4); -- verify-e
+ALTER TABLE t ALTER rational TYPE numeric(4,3); -- rewrite-e
+
+ALTER TABLE t ALTER string TYPE varchar(4); -- noop
+ALTER TABLE t ALTER string TYPE lendom; -- noop
+ALTER TABLE t ALTER string TYPE shortdom; -- rewrite-e
+ALTER TABLE t ALTER string TYPE checkdom; -- verify
+ALTER TABLE t ALTER string TYPE faildom; -- verify-e
+ALTER TABLE t ALTER string TYPE loosedom; -- noop
+ALTER TABLE t ALTER string TYPE text; -- noop
+ALTER TABLE t ALTER string TYPE varchar(20); -- rewrite-v
+ALTER TABLE t ALTER string TYPE varchar(1); -- rewrite-e
+ALTER TABLE t ALTER string TYPE varchar(1) USING t::varchar(1); -- rewrite
+ALTER TABLE t ALTER string TYPE text USING 'foo'::varchar; -- rewrite
+ALTER TABLE t ALTER string TYPE char(3); -- rewrite-v
+ALTER TABLE t ALTER string TYPE char(2); -- rewrite-e
+ALTER TABLE t ALTER string TYPE char(2) USING string::char(2); -- rewrite
+ALTER TABLE t ALTER string TYPE char(4); -- rewrite
+
+ALTER TABLE t ALTER daytimetz TYPE timetz(3); -- rewrite-v
+ALTER TABLE t ALTER daytimetz TYPE timetz(1); -- rewrite
+ALTER TABLE t ALTER daytimetz TYPE timetz(2); -- noop
+ALTER TABLE t ALTER daytimetz TYPE time; -- rewrite
+
+ALTER TABLE t ALTER daytime TYPE time(3); -- rewrite-v
+ALTER TABLE t ALTER daytime TYPE time(1); -- rewrite
+ALTER TABLE t ALTER daytime TYPE time(2); -- noop
+ALTER TABLE t ALTER daytime TYPE timetz; -- rewrite
+
+ALTER TABLE t ALTER stamptz TYPE timestamptz(3); -- rewrite-v
+ALTER TABLE t ALTER stamptz TYPE timestamptz(1); -- rewrite
+ALTER TABLE t ALTER stamptz TYPE timestamptz(2); -- noop
+ALTER TABLE t ALTER stamptz TYPE timestamp; -- noop
+SET timezone = 'Asia/Jakarta';
+ALTER TABLE t ALTER stamptz TYPE timestamptz; -- rewrite
+SET timezone = 'UTC';
+
+ALTER TABLE t ALTER stamp TYPE timestamp(3); -- rewrite-v
+ALTER TABLE t ALTER stamp TYPE timestamp(1); -- rewrite
+ALTER TABLE t ALTER stamp TYPE timestamp(2); -- noop
+ALTER TABLE t ALTER stamp TYPE timestamptz; -- noop
+SET timezone = 'Asia/Jakarta';
+ALTER TABLE t ALTER stamp TYPE timestamp; -- rewrite
+SET timezone = 'UTC';
+
+ALTER TABLE t ALTER timegap TYPE interval(3); -- rewrite-v
+ALTER TABLE t ALTER timegap TYPE interval(1); -- rewrite
+ALTER TABLE t ALTER timegap TYPE interval(2); -- noop
+
+ALTER TABLE t ALTER bits TYPE bit(6); -- verify
+ALTER TABLE t ALTER bits TYPE bit(7); -- verify-e
+ALTER TABLE t ALTER bits TYPE bit(7) USING bits::bit(7); -- rewrite
+-- Next one could be a made a noop with an added cast.
+ALTER TABLE t ALTER bits TYPE varbit(8); -- verify
+ALTER TABLE t ALTER bits TYPE varbit(7); -- verify
+ALTER TABLE t ALTER bits TYPE varbit(5); -- verify-e
+ALTER TABLE t ALTER bits TYPE varbit(5) USING bits::varbit(5); -- rewrite
+ALTER TABLE t ALTER bits TYPE varbit(8); -- noop
+
+ALTER TABLE t ALTER network TYPE inet; -- noop
+ALTER TABLE t ALTER network TYPE cidr; -- rewrite-v
+
+ALTER TABLE t ALTER document TYPE text; -- noop
+ALTER TABLE t ALTER document TYPE xml USING document::xml; -- verify
+ALTER TABLE t ALTER document TYPE char(20); -- rewrite
+ALTER TABLE t ALTER document TYPE xml USING document::xml; -- verify
+ALTER TABLE t ALTER document TYPE varchar(30); -- rewrite-v
+ALTER TABLE t ALTER document TYPE xml USING document::xml; -- verify
+
+ALTER TABLE t ALTER strarr TYPE varchar(4)[]; -- noop
+ALTER TABLE t ALTER strarr TYPE varchar(3)[]; -- rewrite-v
+ALTER TABLE t ALTER strarr TYPE varchar(1)[]; -- rewrite-e
+ALTER TABLE t ALTER strarr TYPE text[]; -- rewrite
+
+ALTER TABLE t ALTER square TYPE polygon; -- rewrite
+
+ALTER TABLE t ADD CONSTRAINT u0 UNIQUE (integral); -- build index exactly once
+ALTER TABLE t ADD CONSTRAINT u1 UNIQUE (integral), -- build index exactly once
+ ALTER integral TYPE int4; -- rewrite
+
+
+-- Data and catalog end state. We omit the columns that bear unstable OIDs.
+SELECT * FROM t ORDER BY 1;
+
+SELECT relname, relnamespace, relowner, relam, reltablespace, relhasindex,
+ relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids,
+ relhaspkey, relhasexclusion, relhasrules, relhastriggers, relacl,
+ reloptions
+FROM pg_class WHERE oid = 't'::regclass
+ OR oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1;
+
+SELECT relname, indnatts, indisunique, indisprimary, indimmediate,
+ indisclustered, indisvalid, indcheckxmin, indisready, indkey, indclass,
+ indoption
+FROM pg_index JOIN pg_class c ON c.oid = indexrelid
+WHERE indrelid = 't'::regclass ORDER BY 1;
+
+SELECT relname, attname, atttypid, attstattarget, attlen, attnum, attndims,
+ attcacheoff, atttypmod, attbyval, attstorage, attalign, attnotnull,
+ atthasdef, attisdropped, attislocal, attinhcount, attacl, attoptions
+FROM pg_attribute JOIN pg_class c ON c.oid = attrelid
+WHERE attrelid = 't'::regclass OR
+ attrelid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 't'::regclass)
+ORDER BY 1, 2;
+
+SELECT conname, connamespace, contype, condeferrable, condeferred, confupdtype,
+ confdeltype, confmatchtype, conislocal, coninhcount, conkey, confkey,
+ conpfeqop, conppeqop, conffeqop, conexclop, consrc
+FROM pg_constraint WHERE conrelid = 't'::regclass
+ORDER BY 1;
+
+-- RI trigger names include the table OID. We don't have a great sort order
+-- available, but tgname probably serves acceptably in practice.
+SELECT tgfoid, tgtype, tgenabled, tgisinternal, tgdeferrable, tginitdeferred,
+ tgnargs, tgattr, tgargs, tgqual
+FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY tgname;
+
+
+-- Done. Retain the table under a less-generic name.
+ALTER TABLE t RENAME TO alter_type_test;
+RESET client_min_messages;
+RESET timezone;
--
-- lock levels
Re: ALTER TYPE 0: Introduction; test cases
On Sun, 2011-01-09 at 16:59 -0500, Noah Misch wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. I'm posting these
patches today:
These sound very good.
I have a concern that by making the ALTER TABLE more complex that we
might not be able to easily tell if a rewrite happens, or not.
Perhaps we should add a WITHOUT REWRITE clause? That would allow a user
to specify that they do not wish a rewrite, so if the AT requires them
to have one it would then fail.
You might point out I didn't do anything like that for my ALTER TABLE
patch, and I would agree with you. WITHOUT ACCESS EXCLUSIVE LOCK might
be an option here also.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
Re: ALTER TYPE 0: Introduction; test cases
On Sun, Jan 9, 2011 at 4:59 PM, Noah Misch <noah@leadboat.com> wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. I'm posting these
patches today:0 - new test cases
This doesn't look right. You might be building it, but you sure
aren't rebuilding it.
+CREATE TABLE parent (keycol numeric PRIMARY KEY);
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"parent_pkey" for table "parent"
+DEBUG: Rebuilding index "parent_pkey"In general, I think this is six kinds of overkill. I don't think we
really need 2000 lines of new regression tests for this feature. I'd
like to see that chopped down by at least 10x.
I don't like this bit:
+ ereport(IsToastRelation(indexRelation) ? DEBUG2 : DEBUG1,
I see no reason to set the verbosity differently depending on whether
or not something's a toast relation; that seems more likely to be
confusing than helpful. I guess my vote would be to make all of these
messages DEBUG2, period. A quick test suggests that doesn't produce
too much noise executing DDL commands.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 09:24:46AM +0000, Simon Riggs wrote:
On Sun, 2011-01-09 at 16:59 -0500, Noah Misch wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. I'm posting these
patches today:These sound very good.
Thanks.
I have a concern that by making the ALTER TABLE more complex that we
might not be able to easily tell if a rewrite happens, or not.Perhaps we should add a WITHOUT REWRITE clause? That would allow a user
to specify that they do not wish a rewrite, so if the AT requires them
to have one it would then fail.
These changes do make it harder to guess how much work the ALTER TABLE will do.
Indeed, about 1/4 of my own guesses prior to writing were wrong. Something like
WITHOUT REWRITE might be the way to go, though there are more questions: if it
does not rewrite, does it scan the table? Which indexes, if any, does it
rebuild? Which foreign key constraints, if any, does it recheck? With patch 0,
you can answer all these questions by enabling DEBUG1 messages and trying the
command on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally showing
them at DEBUG1. In any case, if a WITHOUT REWRITE like you describe covers the
important question, it's certainly easy enough to implement.
You might point out I didn't do anything like that for my ALTER TABLE
patch, and I would agree with you. WITHOUT ACCESS EXCLUSIVE LOCK might
be an option here also.
True. At least we could completely document the lock choices on the ALTER TABLE
reference page. The no-rewrite cases are defined at arms length from ALTER
TABLE, and users can define their own, so it's a tougher fit.
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 06:37:33AM -0500, Robert Haas wrote:
On Sun, Jan 9, 2011 at 4:59 PM, Noah Misch <noah@leadboat.com> wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. ?I'm posting these
patches today:0 - new test cases
This doesn't look right. You might be building it, but you sure
aren't rebuilding it.+CREATE TABLE parent (keycol numeric PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent" +DEBUG: Rebuilding index "parent_pkey"
Yes. I considered saying "Building" unconditionally. Differentiating the debug
message by passing down the fact that the index recently existed seemed like
overkill. What do you think?
In general, I think this is six kinds of overkill. I don't think we
really need 2000 lines of new regression tests for this feature. I'd
like to see that chopped down by at least 10x.
I just included all the tests I found useful to check my own work. If 200 lines
of SQL and expected output is the correct amount, I'll make it so.
I don't like this bit:
+ ereport(IsToastRelation(indexRelation) ? DEBUG2 : DEBUG1,
I see no reason to set the verbosity differently depending on whether
or not something's a toast relation; that seems more likely to be
confusing than helpful. I guess my vote would be to make all of these
messages DEBUG2, period. A quick test suggests that doesn't produce
too much noise executing DDL commands.
The theoretical basis is that users can do little to directly change the need to
rebuild a TOAST index. We'll rebuild the TOAST index if and only if we rewrote
the table. The practical basis is that the TOAST relation names contain parent
relation OIDs, so we don't want them mentioned in regression test output.
Thanks for reviewing.
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 7:14 AM, Noah Misch <noah@leadboat.com> wrote:
I have a concern that by making the ALTER TABLE more complex that we
might not be able to easily tell if a rewrite happens, or not.Perhaps we should add a WITHOUT REWRITE clause? That would allow a user
to specify that they do not wish a rewrite, so if the AT requires them
to have one it would then fail.These changes do make it harder to guess how much work the ALTER TABLE will do.
Indeed, about 1/4 of my own guesses prior to writing were wrong. Something like
WITHOUT REWRITE might be the way to go, though there are more questions: if it
does not rewrite, does it scan the table? Which indexes, if any, does it
rebuild? Which foreign key constraints, if any, does it recheck? With patch 0,
you can answer all these questions by enabling DEBUG1 messages and trying the
command on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally showing
them at DEBUG1. In any case, if a WITHOUT REWRITE like you describe covers the
important question, it's certainly easy enough to implement.
I'm doubtful that it's worth complicating the parser logic. Our DDL
is transactional, so someone can always begin a transaction, increase
client_min_messages, and then look at the output from these added
debug messages to see what is happening. It should be clear within a
second or two if it's not what is wanted, and they can just hit ^C.
True. At least we could completely document the lock choices on the ALTER TABLE
reference page. The no-rewrite cases are defined at arms length from ALTER
TABLE, and users can define their own, so it's a tougher fit.
I don't think it's prohibitively, tough, though, and I think it would
be very valuable. We may have to scratch our heads over exactly where
to put the information, but we can figure out something that works.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: ALTER TYPE 0: Introduction; test cases
On Tue, 2011-01-11 at 07:14 -0500, Noah Misch wrote:
On Tue, Jan 11, 2011 at 09:24:46AM +0000, Simon Riggs wrote:
I have a concern that by making the ALTER TABLE more complex that we
might not be able to easily tell if a rewrite happens, or not.
What about add EXPLAIN support to it, then whoever wants to know what it
does should just run explain on it. I have no idea how hard that would
be to implement and if it makes sense at all, but I sure would like to
see a plan for DDLs too to estimate how long it would take.
Cheers,
Csaba.
Re: ALTER TYPE 0: Introduction; test cases
On Tue, 2011-01-11 at 07:14 -0500, Noah Misch wrote:
These changes do make it harder to guess how much work the ALTER TABLE
will do. Indeed, about 1/4 of my own guesses prior to writing were
wrong. Something like WITHOUT REWRITE might be the way to go, though
there are more questions: if it does not rewrite, does it scan the
table? Which indexes, if any, does it rebuild? Which foreign key
constraints, if any, does it recheck? With patch 0, you can answer
all these questions by enabling DEBUG1 messages and trying the command
on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally
showing them at DEBUG1. In any case, if a WITHOUT REWRITE like you
describe covers the important question, it's certainly easy enough to
implement.
Trouble is, only superusers can set DEBUG1.
You're right, its more complex than I made out, though that strengthens
the feeling that we need a way to check what it does before it does it,
or a way to limit your expectations. Ideally that would be a
programmatic way, so that pgAdmin et al can issue a warning.
Given your thoughts above, my preference would be for
EXPLAIN ALTER TABLE to describe the actions that will take place.
Or other ideas...
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 12:37:28PM +0000, Simon Riggs wrote:
On Tue, 2011-01-11 at 07:14 -0500, Noah Misch wrote:
These changes do make it harder to guess how much work the ALTER TABLE
will do. Indeed, about 1/4 of my own guesses prior to writing were
wrong. Something like WITHOUT REWRITE might be the way to go, though
there are more questions: if it does not rewrite, does it scan the
table? Which indexes, if any, does it rebuild? Which foreign key
constraints, if any, does it recheck? With patch 0, you can answer
all these questions by enabling DEBUG1 messages and trying the command
on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally
showing them at DEBUG1. In any case, if a WITHOUT REWRITE like you
describe covers the important question, it's certainly easy enough to
implement.Trouble is, only superusers can set DEBUG1.
Setting client_min_messages in one's session works, as does "ALTER ROLE myself
SET client_min_messages = debug1". Still, indeed, DEBUG1 is not the usual place
to send a user for information.
You're right, its more complex than I made out, though that strengthens
the feeling that we need a way to check what it does before it does it,
or a way to limit your expectations. Ideally that would be a
programmatic way, so that pgAdmin et al can issue a warning.Given your thoughts above, my preference would be for
EXPLAIN ALTER TABLE to describe the actions that will take place.
That does seem like the best UI. Offhand, I would guess that's a project larger
than the patch series I have here. We'd need to restructure ALTER TABLE into
clear planning and execution stages, if not use the actual planner and executor.
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 07:27:46AM -0500, Robert Haas wrote:
On Tue, Jan 11, 2011 at 7:14 AM, Noah Misch <noah@leadboat.com> wrote:
True. ?At least we could completely document the lock choices on the ALTER TABLE
reference page. ?The no-rewrite cases are defined at arms length from ALTER
TABLE, and users can define their own, so it's a tougher fit.I don't think it's prohibitively, tough, though, and I think it would
be very valuable. We may have to scratch our heads over exactly where
to put the information, but we can figure out something that works.
Agreed. I don't mean to suggest giving up on documenting anything, just that
some behaviors are clear enough by documentation alone, while others benefit
from additional syntax/messages to constrain/see what actually happens.
Re: ALTER TYPE 0: Introduction; test cases
On Tue, 2011-01-11 at 08:06 -0500, Noah Misch wrote:
On Tue, Jan 11, 2011 at 12:37:28PM +0000, Simon Riggs wrote:
On Tue, 2011-01-11 at 07:14 -0500, Noah Misch wrote:
These changes do make it harder to guess how much work the ALTER TABLE
will do. Indeed, about 1/4 of my own guesses prior to writing were
wrong. Something like WITHOUT REWRITE might be the way to go, though
there are more questions: if it does not rewrite, does it scan the
table? Which indexes, if any, does it rebuild? Which foreign key
constraints, if any, does it recheck? With patch 0, you can answer
all these questions by enabling DEBUG1 messages and trying the command
on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally
showing them at DEBUG1. In any case, if a WITHOUT REWRITE like you
describe covers the important question, it's certainly easy enough to
implement.Trouble is, only superusers can set DEBUG1.
Setting client_min_messages in one's session works, as does "ALTER ROLE myself
SET client_min_messages = debug1". Still, indeed, DEBUG1 is not the usual place
to send a user for information.You're right, its more complex than I made out, though that strengthens
the feeling that we need a way to check what it does before it does it,
or a way to limit your expectations. Ideally that would be a
programmatic way, so that pgAdmin et al can issue a warning.Given your thoughts above, my preference would be for
EXPLAIN ALTER TABLE to describe the actions that will take place.That does seem like the best UI. Offhand, I would guess that's a project larger
than the patch series I have here. We'd need to restructure ALTER TABLE into
clear planning and execution stages, if not use the actual planner and executor.
Please do something that works in this release, whatever that is. I will
follow your lead in putting a similar mechanism in for judging lock
levels.
I don't want to be looking through the docs each time I run this,
sweating between it taking 5 secs and 5 hours on a big server.
We need to be able to run stuff overnight, with certainty that we know
what will happen.
And I want a clear answer when the "but how can you be certain?"
question gets asked.
Thank you for the rest of the patch.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 01:17:23PM +0000, Simon Riggs wrote:
On Tue, 2011-01-11 at 08:06 -0500, Noah Misch wrote:
On Tue, Jan 11, 2011 at 12:37:28PM +0000, Simon Riggs wrote:
Given your thoughts above, my preference would be for
EXPLAIN ALTER TABLE to describe the actions that will take place.That does seem like the best UI. Offhand, I would guess that's a project larger
than the patch series I have here. We'd need to restructure ALTER TABLE into
clear planning and execution stages, if not use the actual planner and executor.Please do something that works in this release, whatever that is. I will
follow your lead in putting a similar mechanism in for judging lock
levels.
Okay; I'll see what I can come up with. The other part I was going to try to
finish before the last commitfest begins is avoiding unnecessary rebuilds of
indexes involving changed columns. Is that more or less important than having
an EXPLAIN ALTER TABLE?
I don't want to be looking through the docs each time I run this,
sweating between it taking 5 secs and 5 hours on a big server.
We need to be able to run stuff overnight, with certainty that we know
what will happen.And I want a clear answer when the "but how can you be certain?"
question gets asked.
Just to clarify: does Robert's suggestion of starting the command in a
transaction block and cancelling it after messages appear (on any other DB with
the same schema, if need be) give too little certainty? You could check
pg_locks to see what lock was taken, too. It's surely not the ideal user
experience, but it seems dependable at least.
Thanks,
nm
Re: ALTER TYPE 0: Introduction; test cases
On Jan 11, 2011, at 8:50 AM, Noah Misch <noah@leadboat.com> wrote:
Okay; I'll see what I can come up with. The other part I was going to try to
finish before the last commitfest begins is avoiding unnecessary rebuilds of
indexes involving changed columns. Is that more or less important than having
an EXPLAIN ALTER TABLE?
I think something like EXPLAIN ALTER TABLE would be #%^* awesome (and kudos to Simon for thinking of it), but your chances of getting into 9.1 are not very good. So I'd focus on the index rebuild issue, which is more clear-cut.
...Robert
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 7:25 AM, Noah Misch <noah@leadboat.com> wrote:
On Tue, Jan 11, 2011 at 06:37:33AM -0500, Robert Haas wrote:
On Sun, Jan 9, 2011 at 4:59 PM, Noah Misch <noah@leadboat.com> wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. ?I'm posting these
patches today:0 - new test cases
This doesn't look right. You might be building it, but you sure
aren't rebuilding it.+CREATE TABLE parent (keycol numeric PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent" +DEBUG: Rebuilding index "parent_pkey"Yes. I considered saying "Building" unconditionally. Differentiating the debug
message by passing down the fact that the index recently existed seemed like
overkill. What do you think?
I'm wondering if we should consider moving this call to index_build()
so that it appears everywhere that we build an index rather than just
in ALTER TABLE, and saying something like:
building index "%s" on table "%s"
The theoretical basis is that users can do little to directly change the need to
rebuild a TOAST index. We'll rebuild the TOAST index if and only if we rewrote
the table. The practical basis is that the TOAST relation names contain parent
relation OIDs, so we don't want them mentioned in regression test output.
Perhaps in this case we could write:
building TOAST index for table "%s"
There's limited need for users to know the actual name of the TOAST
table, but I think it's better to log a line for all the actions we're
performing or none of them, rather than some but not others.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 09:41:35PM -0500, Robert Haas wrote:
On Tue, Jan 11, 2011 at 7:25 AM, Noah Misch <noah@leadboat.com> wrote:
On Tue, Jan 11, 2011 at 06:37:33AM -0500, Robert Haas wrote:
On Sun, Jan 9, 2011 at 4:59 PM, Noah Misch <noah@leadboat.com> wrote:
This begins the patch series for the design I recently proposed[1] for avoiding
some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. ?I'm posting these
patches today:0 - new test cases
This doesn't look right. ?You might be building it, but you sure
aren't rebuilding it.+CREATE TABLE parent (keycol numeric PRIMARY KEY); +NOTICE: ?CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent" +DEBUG: ?Rebuilding index "parent_pkey"Yes. ?I considered saying "Building" unconditionally. ?Differentiating the debug
message by passing down the fact that the index recently existed seemed like
overkill. ?What do you think?I'm wondering if we should consider moving this call to index_build()
so that it appears everywhere that we build an index rather than just
in ALTER TABLE, and saying something like:building index "%s" on table "%s"
The patch does have it in index_build. That new wording seems better.
The theoretical basis is that users can do little to directly change the need to
rebuild a TOAST index. ?We'll rebuild the TOAST index if and only if we rewrote
the table. ?The practical basis is that the TOAST relation names contain parent
relation OIDs, so we don't want them mentioned in regression test output.Perhaps in this case we could write:
building TOAST index for table "%s"
Good idea; thanks.
I'll incorporate those changes into the next version.
Re: ALTER TYPE 0: Introduction; test cases
Here's v2 based on your feedback.
I pruned test coverage down to just the highlights. By the end of patch series,
the net change becomes +67 to alter_table.sql and +111 to alter_table.out. The
alter_table.out delta is larger in this patch (+150), because the optimizations
don't yet apply and more objects are reported as rebuilt.
If this looks sane, I'll rebase the rest of the patches accordingly.
On Tue, Jan 11, 2011 at 09:41:35PM -0500, Robert Haas wrote:
On Tue, Jan 11, 2011 at 7:25 AM, Noah Misch <noah@leadboat.com> wrote:
I'm wondering if we should consider moving this call to index_build()
so that it appears everywhere that we build an index rather than just
in ALTER TABLE, and saying something like:building index "%s" on table "%s"
Done.
I also fixed the leading capital letter on the other new messages.
The theoretical basis is that users can do little to directly change the need to
rebuild a TOAST index. ?We'll rebuild the TOAST index if and only if we rewrote
the table. ?The practical basis is that the TOAST relation names contain parent
relation OIDs, so we don't want them mentioned in regression test output.Perhaps in this case we could write:
building TOAST index for table "%s"
There's limited need for users to know the actual name of the TOAST
table, but I think it's better to log a line for all the actions we're
performing or none of them, rather than some but not others.
That was a good idea, but the implementation is awkward. index_build has the
TOAST table and index relations, and there's no good way to find the parent
table from either. No index covers pg_class.reltoastrelid, so scanning by that
would be a bad idea. Autovacuum solves this by building its own hash table with
the mapping; that wouldn't fit well here. We could parse the parent OID out of
the TOAST name (heh, heh). I suppose we could pass the parent relation from
create_toast_table down through index_create to index_build. Currently,
index_create knows nothing of the fact that it's making a TOAST index, and
changing that to improve this messages seems a bit excessive. Thoughts?
For this version, I've kept the DEBUG1/DEBUG2 split by TOAST.
Attachments:
at0v2-debug-tests.patchtext/plain; charset=us-asciiDownload
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5790f87..8ff6927 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
***************
*** 1420,1425 **** index_build(Relation heapRelation,
--- 1420,1430 ----
procedure = indexRelation->rd_am->ambuild;
Assert(RegProcedureIsValid(procedure));
+ ereport(IsToastRelation(indexRelation) ? DEBUG2 : 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
diff --git a/src/backend/commands/index f3bd565..e3921e4 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 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.
*
+ * Table, 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/test/regress/GNUmakefiindex 15b9ec4..c33ecb9 100644
diff --git a/src/test/regress/expecindex 3d126bb..4002f7f 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,1632 ----
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);
+ 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 =)
+ );
+ 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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
Re: ALTER TYPE 0: Introduction; test cases
On Sat, Jan 15, 2011 at 1:30 AM, Noah Misch <noah@leadboat.com> wrote:
Here's v2 based on your feedback.
I pruned test coverage down to just the highlights. By the end of patch series,
the net change becomes +67 to alter_table.sql and +111 to alter_table.out. The
alter_table.out delta is larger in this patch (+150), because the optimizations
don't yet apply and more objects are reported as rebuilt.If this looks sane, I'll rebase the rest of the patches accordingly.
+ * Table, 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.This comment seems somewhat unrelated to the rest of the patch, and I
really hope that the first word ("Table") actually means "CHECK",
because we certainly shouldn't be treating table check constraints and
column check constraints differently, at least AIUI.
That was a good idea, but the implementation is awkward. index_build has the
TOAST table and index relations, and there's no good way to find the parent
table from either. No index covers pg_class.reltoastrelid, so scanning by that
would be a bad idea. Autovacuum solves this by building its own hash table with
the mapping; that wouldn't fit well here. We could parse the parent OID out of
the TOAST name (heh, heh). I suppose we could pass the parent relation from
create_toast_table down through index_create to index_build. Currently,
index_create knows nothing of the fact that it's making a TOAST index, and
changing that to improve this messages seems a bit excessive. Thoughts?For this version, I've kept the DEBUG1/DEBUG2 split by TOAST.
Well, I pretty much think that split is going to be not what anyone
wants for any purpose OTHER than the regression tests. So if there's
no other way around it I'd be much more inclined to remove the
regression tests than to keep that split.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: ALTER TYPE 0: Introduction; test cases
On Sat, Jan 15, 2011 at 08:57:30AM -0500, Robert Haas wrote:
On Sat, Jan 15, 2011 at 1:30 AM, Noah Misch <noah@leadboat.com> wrote:
Here's v2 based on your feedback.
I pruned test coverage down to just the highlights. ?By the end of patch series,
the net change becomes +67 to alter_table.sql and +111 to alter_table.out. ?The
alter_table.out delta is larger in this patch (+150), because the optimizations
don't yet apply and more objects are reported as rebuilt.If this looks sane, I'll rebase the rest of the patches accordingly.
+ * Table, 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.This comment seems somewhat unrelated to the rest of the patch, and I
really hope that the first word ("Table") actually means "CHECK",
because we certainly shouldn't be treating table check constraints and
column check constraints differently, at least AIUI.
"Table" should be "CHECK"; thanks. I added the comment in this patch because
it's clarifying existing behavior that was not obvious to me, rather than
documenting a new fact arising due to the patch series. A few of the new test
cases address this behavior.
That was a good idea, but the implementation is awkward. ?index_build has the
TOAST table and index relations, and there's no good way to find the parent
table from either. ?No index covers pg_class.reltoastrelid, so scanning by that
would be a bad idea. ?Autovacuum solves this by building its own hash table with
the mapping; that wouldn't fit well here. ?We could parse the parent OID out of
the TOAST name (heh, heh). ?I suppose we could pass the parent relation from
create_toast_table down through index_create to index_build. ?Currently,
index_create knows nothing of the fact that it's making a TOAST index, and
changing that to improve this messages seems a bit excessive. ?Thoughts?For this version, I've kept the DEBUG1/DEBUG2 split by TOAST.
Well, I pretty much think that split is going to be not what anyone
wants for any purpose OTHER than the regression tests. So if there's
no other way around it I'd be much more inclined to remove the
regression tests than to keep that split.
Do you value test coverage so little?
Re: ALTER TYPE 0: Introduction; test cases
On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <noah@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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: ALTER TYPE 0: Introduction; test cases
On Sat, Jan 15, 2011 at 02:31:21PM -0500, Robert Haas wrote:
On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <noah@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.
Attachments:
at0v3-debug-tests.patchtext/plain; charset=us-asciiDownload
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
Re: ALTER TYPE 0: Introduction; test cases
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <noah@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.
Quite honestly, I'd be inclined to rip out most of the DEBUG messages I
see in that regression test altogether. They are useless, and so is the
regression test itself. An appropriate regression test would involve
something more like checking that the relfilenode changed and then
checking that the contained data is still sane.
regards, tom lane
Re: ALTER TYPE 0: Introduction; test cases
On Sun, Jan 16, 2011 at 12:07:44PM -0500, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <noah@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.Quite honestly, I'd be inclined to rip out most of the DEBUG messages I
see in that regression test altogether. They are useless, and so is the
regression test itself. An appropriate regression test would involve
something more like checking that the relfilenode changed and then
checking that the contained data is still sane.
This patch is the first of a series. Divorced from the other patches, many of
the test cases exercise the same code path, making them redundant. Even so, the
tests revealed a defect we released with 9.0; that seems sufficient to promote
them out of the "useless" bucket.
One can easily confirm by inspection that the relfilenode will change if and
only if the "rewriting" DEBUG message appears. Your proposed direct comparison
of the relfilenode in the regression tests adds negligible sensitivity. If that
were all, I'd call it a question of style. However, a relfilenode comparison
does not distinguish no-op changes from changes entailing a verification scan.
A similar ambiguity would arise without the "foreign key" DEBUG message.
As for "checking that the contained data is still sane", what do you have in
mind? After the test cases, I SELECT the table-under-test and choice catalog
entries. If later patches in the series leave these expected outputs unchanged,
that confirms the continued sanity of the data. Perhaps I should do this after
every test, or also test forced index scans.
nm
Re: ALTER TYPE 0: Introduction; test cases
On Sun, Jan 16, 2011 at 12:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Jan 15, 2011 at 10:25 AM, Noah Misch <noah@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.Quite honestly, I'd be inclined to rip out most of the DEBUG messages I
see in that regression test altogether. They are useless, and so is the
regression test itself. An appropriate regression test would involve
something more like checking that the relfilenode changed and then
checking that the contained data is still sane.
From my point of view, the value of those messages is that if someone
is altering or clustering a large table, they might like to get a
series of messages: rewriting the table, rebuilding this index,
rebuilding that index, rewriting the toast table index, .... as a
crude sort of progress indication.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company