SET client_min_messages = warning; SELECT col1, to_char("time", 'YYYY-MM-DD HH24:MI:SS'), ","")" FROM tbl_cluster ORDER BY 1, 2; col1 | to_char | ,") ------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 | 2008-12-31 10:00:00 | admin 2 | 2008-01-01 00:00:00 | king 3 | 2008-03-04 12:00:00 | joker 4 | 2008-03-05 15:00:00 | queen 5 | 2008-01-01 00:30:00 | 1.4142135623730950488016887242096980785696718753769480731766797379907324784621070388503875343276415727350138462309122970249248360558507372126441214970999358314132226659275055927557999505011527820605714701095599716059702745345968620147285174186408891986095523292304843087143214508397626036279952514079896872533965463318088296406206152583523950547457502877599617298355752203375318570113543746034084988471603868999706990048150305440277903164542478230684929369186215805784631115966687130130156185689872372352885092648612494977154218334204285686060146824720771435854874155657069677653720226485447015858801620758474922657226002085584466521458398893944370926591800311388246468157082630100594858704003186480342194897278290641045072636881313739855256117322040245091227700226941127573627280495738108967504018369868368450725799364729060762996941380475654823728997180326802474420629269124859052181004459842150591120249441341728531478105803603371077309182869314710171111683916581726889419758716582152128229518488471.732050807568877293527446341505872366942805253810380628055806979451933016908800037081146186757248575675626141415406703029969945094998952478811655512094373648528093231902305582067974820101084674923265015312343266903322886650672254668921837971227047131660367861588019049986537379859389467650347506576050756618348129606100947602187190325083145829523959832997789824508288714463832917347224163984587855397667958063818353666110843173780894378316102088305524901670023520711144288695990956365797087168498072899493296484283020786408603988738697537582317317831395992983007838702877053913369563312103707264019249106768231199288375641141422016742752102372994270831059898459475987664288897796147837958390228854852903576033852808064381972344661059689722872865264153822664698420021195484155278441181286534507035191650016689294415480846071277143999762926834629577438361895110127148638746976545982451788550975379013880664961911962222957110555242923723192197738262561631468842032853716682938649611917049738836395495938 (5 rows) SELECT * FROM tbl_only_ckey ORDER BY 1; col1 | col2 | ,") ------+--------------------------+----- 1 | Tue Jan 01 00:00:00 2008 | abc 2 | Fri Feb 01 00:00:00 2008 | def (2 rows) SELECT * FROM tbl_only_pkey ORDER BY 1; col1 | ,") | a1 ------+-----+---- 1 | abc | 2 | def | (2 rows) SELECT * FROM tbl_gistkey ORDER BY 1; id | c | a1 ----+-----------+---- 1 | <(1,2),3> | 2 | <(4,5),6> | (2 rows) SET enable_seqscan = on; SET enable_indexscan = off; SELECT * FROM tbl_with_dropped_column ; c1 | id | c2 | c3 | a1 ----+----+----+----+---- c1 | 1 | c2 | | c1 | 2 | c2 | | (2 rows) SELECT * FROM view_for_tbl ORDER BY 1, 2; c1 | id | c2 ----+----+---- c1 | 1 | c2 c1 | 2 | c2 (2 rows) SELECT * FROM tbl_with_dropped_toast; i | j | a1 ---+----+---- 1 | 10 | 2 | 20 | (2 rows) SET enable_seqscan = off; SET enable_indexscan = on; SELECT * FROM tbl_with_dropped_column ORDER BY 1, 2; c1 | id | c2 | c3 | a1 ----+----+----+----+---- c1 | 1 | c2 | | c1 | 2 | c2 | | (2 rows) SELECT * FROM view_for_tbl; c1 | id | c2 ----+----+---- c1 | 2 | c2 c1 | 1 | c2 (2 rows) SELECT * FROM tbl_with_dropped_toast; i | j | a1 ---+----+---- 1 | 10 | 2 | 20 | (2 rows) RESET enable_seqscan; RESET enable_indexscan; -- check if storage option for both table and TOAST table didn't go away. SELECT CASE relkind WHEN 'r' THEN relname WHEN 't' THEN 'toast_table' END as table, reloptions FROM pg_class WHERE relname = 'tbl_with_toast' OR relname = 'pg_toast_' || 'tbl_with_toast'::regclass::oid ORDER BY 1; table | reloptions ----------------+--------------------------------------------------------------------- tbl_with_toast | {autovacuum_vacuum_scale_factor=30,autovacuum_vacuum_threshold=300} toast_table | {autovacuum_vacuum_scale_factor=40,autovacuum_vacuum_threshold=400} (2 rows) SELECT pg_relation_size(reltoastrelid) = 0 as check_toast_rel_size FROM pg_class WHERE relname = 'tbl_with_mod_column_storage'; check_toast_rel_size ---------------------- t (1 row) -- -- check broken links or orphan toast relations -- SELECT oid, relname FROM pg_class WHERE relkind = 't' AND oid NOT IN (SELECT reltoastrelid FROM pg_class WHERE relkind = 'r'); oid | relname -----+--------- (0 rows) SELECT oid, relname FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 AND reltoastrelid NOT IN (SELECT oid FROM pg_class WHERE relkind = 't'); oid | relname -----+--------- (0 rows) -- check columns options SELECT attname, attstattarget, attoptions FROM pg_attribute WHERE attrelid = 'tbl_idxopts'::regclass AND attnum > 0 ORDER BY attnum; attname | attstattarget | attoptions ---------+---------------+------------------- i | -1 | t | -1 | {n_distinct=-0.5} a1 | -1 | (3 rows) -- -- NOT NULL UNIQUE -- CREATE TABLE tbl_nn (col1 int NOT NULL, col2 int NOT NULL); CREATE TABLE tbl_uk (col1 int NOT NULL, col2 int , UNIQUE(col1, col2)); CREATE TABLE tbl_nn_uk (col1 int NOT NULL, col2 int NOT NULL, UNIQUE(col1, col2)); CREATE TABLE tbl_pk_uk (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1, col2), UNIQUE(col2, col1)); CREATE TABLE tbl_nn_puk (col1 int NOT NULL, col2 int NOT NULL); CREATE UNIQUE INDEX tbl_nn_puk_pcol1_idx ON tbl_nn_puk(col1) WHERE col1 < 10; \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_nn WARNING: relation "public.tbl_nn" must have a primary key or not-null unique keys -- => WARNING \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_uk WARNING: relation "public.tbl_uk" must have a primary key or not-null unique keys -- => WARNING \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_nn_uk INFO: migrating table "public.tbl_nn_uk" INFO: altering table with: ADD COLUMN a1 INT -- => OK \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_pk_uk INFO: migrating table "public.tbl_pk_uk" INFO: altering table with: ADD COLUMN a1 INT -- => OK \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_nn_puk WARNING: relation "public.tbl_nn_puk" must have a primary key or not-null unique keys -- => WARNING -- -- Triggers handling -- CREATE FUNCTION trgtest() RETURNS trigger AS $$BEGIN RETURN NEW; END$$ LANGUAGE plpgsql; CREATE TABLE trg1 (id integer PRIMARY KEY); CREATE TRIGGER repack_trigger_1 AFTER UPDATE ON trg1 FOR EACH ROW EXECUTE PROCEDURE trgtest(); \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=trg1 INFO: migrating table "public.trg1" INFO: altering table with: ADD COLUMN a1 INT CREATE TABLE trg2 (id integer PRIMARY KEY); CREATE TRIGGER repack_trigger AFTER UPDATE ON trg2 FOR EACH ROW EXECUTE PROCEDURE trgtest(); \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=trg2 INFO: migrating table "public.trg2" INFO: altering table with: ADD COLUMN a1 INT CREATE TABLE trg3 (id integer PRIMARY KEY); CREATE TRIGGER repack_trigger_1 BEFORE UPDATE ON trg3 FOR EACH ROW EXECUTE PROCEDURE trgtest(); \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=trg3 INFO: migrating table "public.trg3" INFO: altering table with: ADD COLUMN a1 INT -- -- Dry run -- \! pg_migrate --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=tbl_cluster INFO: Dry run enabled, not executing migration, run with --execute to process. INFO: migrating table "public.tbl_cluster" -- Test --schema -- CREATE SCHEMA test_schema1; CREATE TABLE test_schema1.tbl1 (id INTEGER PRIMARY KEY); CREATE TABLE test_schema1.tbl2 (id INTEGER PRIMARY KEY); CREATE SCHEMA test_schema2; CREATE TABLE test_schema2.tbl1 (id INTEGER PRIMARY KEY); CREATE TABLE test_schema2.tbl2 (id INTEGER PRIMARY KEY); -- => OK \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=test_schema1.tbl1 INFO: migrating table "test_schema1.tbl1" ERROR: query failed: command string is a null pointer DETAIL: query was: (null) -- -- don't kill backend -- \! pg_migrate --execute --alter='ADD COLUMN dkb1 INT' --dbname=contrib_regression --table=tbl_cluster --no-kill-backend INFO: migrating table "public.tbl_cluster" INFO: altering table with: ADD COLUMN dkb1 INT -- -- table inheritance check -- CREATE TABLE parent_a(val integer primary key); CREATE TABLE child_a_1(val integer primary key) INHERITS(parent_a); CREATE TABLE child_a_2(val integer primary key) INHERITS(parent_a); CREATE TABLE parent_b(val integer primary key, i1 int NOT NULL); CREATE TABLE child_b_1(val integer primary key) INHERITS(parent_b); CREATE TABLE child_b_2(val integer primary key) INHERITS(parent_b); -- => OK \! pg_migrate --execute --alter='ADD COLUMN a1 INT' --dbname=contrib_regression --table=parent_a INFO: migrating table "public.parent_a" INFO: altering table with: ADD COLUMN a1 INT -- => OK \! pg_migrate --execute --alter='ADD COLUMN a1 TEXT' --dbname=contrib_regression --table=child_a_1 INFO: migrating table "public.child_a_1" INFO: altering table with: ADD COLUMN a1 TEXT -- => ERROR -- TODO non deterministic output \! pg_migrate --execute --alter='NO INHERIT parent_a' --dbname=contrib_regression --table=child_a_2 -- => ERROR -- TODO non deterministic output \! pg_migrate --execute --alter='ADD COLUMN i1 TEXT' --dbname=contrib_regression --table=child_b_1