Hackers,
There's a bug with temporary tables signalled ON COMMIT DELETE ROWS,
when they contain foreign key references. An example:
alvherre=# begin;
BEGIN
alvherre=# CREATE TEMP TABLE foo (a int PRIMARY KEY) ON COMMIT DELETE ROWS;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
alvherre=# CREATE TEMP TABLE bar (a int REFERENCES foo) ON COMMIT DELETE ROWS;
CREATE TABLE
alvherre=# COMMIT;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "bar" references "foo" via foreign key constraint "bar_a_fkey".
Say again? Certainly this shouldn't happen, because both tables are
supposed to lose rows on transaction commit. But this isn't working.
The attached patch fixes this bug. (In all likelyhood, not a lot of
people uses referential integrity on temp tables, and that's why this
hasn't been reported. But it's a bug anyway.)
Incidentally ("collateral damage"), the patch modifies the TRUNCATE
command so that it can work on multiple tables. In particular, if
foreign key references are all internal to the group that's being
truncated, the command is allowed.
There's one thing that bothers me on this patch: the fact that
pg_constraint has to be scanned multiple times, and they are all
seqscans. Not sure what to do about that. Maybe there's a way to do
better?
Also, observe that when the TRUNCATE operation is aborted because of a
foreign key, a HINT is emitted as well telling the user to truncate the
referencing table too. This is IMHO a good hint, but it may be
misleading when the truncation has taken the ON COMMIT DELETE ROWS path.
Not sure if it's worth fixing (maybe the hint should suggest to add ON
COMMIT DELETE ROWS to the referencing table as well?).
Please have a look. The patch is not as intrusive as it looks; there's
a lot of whitespace change.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
