From: | Marko Elezovic <marko(dot)elezovic(at)oradian(dot)com> |
---|---|
To: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Commenting a FK crashes ALTER TABLE statements |
Date: | 2017-05-14 15:43:16 |
Message-ID: | DB6PR03MB30775D58E732D4EB0C13725B9AE00@DB6PR03MB3077.eurprd03.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Affected versions: 9.5.0 up to 9.6.3 (regression from 9.4.12)
Affected OS: All
Script to reproduce:
psql -Upostgres -c"DROP DATABASE IF EXISTS cod;" postgres
psql -Upostgres -c"CREATE DATABASE cod;" postgres
psql -Upostgres -c"CREATE TABLE foo(id int PRIMARY KEY);" cod
psql -Upostgres -c"CREATE TABLE bar(id int CONSTRAINT baz REFERENCES foo);" cod
psql -Upostgres -c"COMMENT ON CONSTRAINT baz ON bar IS 'Fubar';" cod
psql -Upostgres -c"ALTER TABLE foo ALTER COLUMN id TYPE int;" cod
*IMPORTANT NOTE*: The example above is 100% reproducible if executed verbatim, from command line of 9.5+ while on either Windows/Linux
On the other hand, when ran from psql by pasting all the commands or running them within the same script it is not always reproducible.
I also haven't been able to reproduce the issue by adding a new test into src/test/regress/sql/comments.sql
Expected output after last alter should be a message with random garbage instead of foreign key name:
ERROR: relation "public.¬" does not exist
ERROR: relation "public." does not exist
I've traced the issue into Re-adding an existing comment (starting in tablecmds.c):
case AT_ReAddComment: /* Re-add existing comment */
address = CommentObject((CommentStmt *) cmd->def);
... which fails in pg_constraint.c while traversing the constraints:
scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
NULL, 1, skey);
while (HeapTupleIsValid(tuple = systable_getnext(scan)))
Here tuple sometimes gets garbage memory addresses, so it explains at least 50% of the craziness I've seen while reproducing.
The same example also has a chance to produce these as well:
ERROR: constraint "baz" for table "foo" does not exist <-- due to not being found in the above while loop
ERROR: "foo_pkey" is an index <-- when trying to open the heap for pkey constraint
ERROR: relation "public.pg_class_tblspc_relfilenode_index" does not exist <-- ??? Heisenbugs, frequently visible in production SQL migrations
ERROR: relation "public.pg_constraint_conname_nsp_index" does not exist <-- ??? but could never reproduce when in debug mode / tracing
I'll be happy to continue the tracing effort if need be, but hoping this is reproducible on your machines
With heartfelt regards,
Marko
Oradian Ltd., Head of Technology
marko(dot)elezovic(at)oradian(dot)com<mailto:marko(dot)elezovic(at)oradian(dot)com>
+385 91 2646 342
From | Date | Subject | |
---|---|---|---|
Next Message | james+postgres | 2017-05-14 20:06:02 | BUG #14654: With high statistics targets on ts_vector, unexpectedly high memory use & OOM are triggered |
Previous Message | Stephen Frost | 2017-05-14 15:40:25 | Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist |