Commenting a FK crashes ALTER TABLE statements

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

Responses

Browse pgsql-bugs by date

  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