bug in ALTER TABLE / TYPE

From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: bug in ALTER TABLE / TYPE
Date: 2005-06-29 08:08:13
Message-ID: 42C256ED.3000504@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

A coworker of mine reported a subtle issue in ATExecAlterColumnType() in
tablecmds.c. Suppose we have the following DDL:

CREATE TABLE pktable (a int primary key, b int);
CREATE TABLE fktable (fk int references pktable, c int);
ALTER TABLE pktable ALTER COLUMN a TYPE bigint;

Circa line 4891 in current sources, we begin a system table scan to look
for pg_depend rows that depend on the column we're modifying. In this
case, there are two dependencies on the column: the pg_constraint row
for pktable's PK constraint, and the pg_constraint row for fktable's FK
constraint. The bug is that we require the systable scan to return the
FK constraint before the PK constraint -- if we attempt to remove the PK
constraint first, it will fail because the FK constraint still exists
and depends on the PK constraint.

This bug is difficult to reproduce with a normal postmaster and vanilla
sources, as the systable scan is usually implemented via a B+-tree scan
on (refclassid, refobjid, refobjsubid). For this particular test case
these three fields have equal values for the PK and FK constraint
pg_depend rows, so the order in which the two constraints are returned
is undefined. It just so happens that the Postgres b+-tree
implementation *usually* returns the FK constraint first (per comments
in nbtinsert.c, we usually place an equal key value at the end of a
chain of equal keys, but stop looking for the end of the chain with a
probability of 1%). And of course there is no ordering constraint if the
systable scan is implemented via a heap scan (which would happen if,
say, we're ignoring indexes on system catalogs in a standalone backend).

To reproduce, any of:

(1) Run the above SQL in a standalone backend started with the "-P" flag

(2) Change "true" to "false" in the third argument to
systable_beginscan() at tablecmds.c:4891, which means a heap scan will
be used by a normal backend.

(3) I've attached a dirty kludge of a patch that shuffles the results of
the systable scan with probability 50%. Applying the patch should repro
the bug with a normal backend (approx. 1 in 2 times, naturally).

I'm not too familiar with the pg_depend code, so I'm not sure the right
fix. Comments?

-Neil

Attachment Content-Type Size
alter_table_bug_repro-1.patch text/x-patch 2.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2005-06-29 09:02:06 Re: Open items
Previous Message Teodor Sigaev 2005-06-29 07:30:02 Re: GiST concurrency commited