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@2ndquadrant.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