On Oct 10, 2014, at 3:14 PM, Andres Freund <andres@2ndquadrant.com> =
wrote:
>=20
> 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=20
-----------+---------+-----------
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)=3D(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=20
-----------+---------+-----------
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=20
-----------+---------+-----------
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)=3D(1) is not present in table "table1".
ROLLBACK
Time: 0.150 ms
Table "test.table2"
Column | Type | Modifiers=20
-----------+---------+-----------
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,
--=20
Casey Allen Shobe=