[BUGS] Commenting a FK crashes ALTER TABLE statements - Mailing list 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

Oradian Ltd., Head of Technology
marko.elezovic@oradian.com
+385 91 2646 342