Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: alter_table_bug_repro-1.patch
Description: text/x-patch (2.3 KB)

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2005-06-29 09:02:06
Subject: Re: Open items
Previous:From: Teodor SigaevDate: 2005-06-29 07:30:02
Subject: Re: GiST concurrency commited

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group