Re: BUG #11638: Transaction safety fails when constraints are dropped and analyze is done

On Oct 10, 2014, at 3:14 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>
> Please attach - the paste will expire in a couple days. That makes
> researching issues later quite annoying.

Well I set it to a month, but in any case, scripts are attached.

I have updated this to include two different tests - in the first I expanded the test to show that primary keys and indexes are also lost. In the second I show that by adding their re-create statements before the re-create for the foreign key, they somehow are there after the transaction rollback.

For the first test case, run the following with the attached scripts:
psql -f create.sql
psql -f test.sql

Output:

Table "test.table2"
Column | Type | Modifiers
-----------+---------+-----------
table1_id | integer | not null
value | text | not null
Indexes:
"table2_pkey" PRIMARY KEY, btree (table1_id)
"testindex" btree (value)
Foreign-key constraints:
"table2_table1_id_fkey" FOREIGN KEY (table1_id) REFERENCES test.table1(id)

BEGIN
Time: 0.155 ms
ALTER TABLE
Time: 1.194 ms
ALTER TABLE
Time: 0.368 ms
DROP INDEX
Time: 0.294 ms
Time: 0.347 ms
ANALYZE
Time: 0.427 ms
psql:test.sql:17: ERROR: insert or update on table "table2" violates foreign key constraint "table2_table1_id_fkey"
DETAIL: Key (table1_id)=(1) is not present in table "table1".
psql:test.sql:19: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:21: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
Time: 0.088 ms
Table "test.table2"
Column | Type | Modifiers
-----------+---------+-----------
table1_id | integer | not null
value | text | not null

For the second test case, run the following:
psql -f create.sql
psql -f test2.sql

Output:

Table "test.table2"
Column | Type | Modifiers
-----------+---------+-----------
table1_id | integer | not null
value | text | not null
Indexes:
"table2_pkey" PRIMARY KEY, btree (table1_id)
"testindex" btree (value)
Foreign-key constraints:
"table2_table1_id_fkey" FOREIGN KEY (table1_id) REFERENCES test.table1(id)

BEGIN
Time: 0.255 ms
ALTER TABLE
Time: 1.164 ms
ALTER TABLE
Time: 0.329 ms
DROP INDEX
Time: 0.378 ms
Time: 0.296 ms
ANALYZE
Time: 0.533 ms
ALTER TABLE
Time: 11.655 ms
CREATE INDEX
Time: 5.871 ms
psql:test2.sql:21: ERROR: insert or update on table "table2" violates foreign key constraint "table2_table1_id_fkey"
DETAIL: Key (table1_id)=(1) is not present in table "table1".
ROLLBACK
Time: 0.150 ms
Table "test.table2"
Column | Type | Modifiers
-----------+---------+-----------
table1_id | integer | not null
value | text | not null
Indexes:
"table2_pkey" PRIMARY KEY, btree (table1_id)
"testindex" btree (value)

As a workaround to this bug, does it make sense to delay all analyzes until the end of the transaction?

Thanks,
--
Casey Allen Shobe