The following bug has been logged online:
Bug reference: 4417
Logged by: Benjamin Bihler
Email address: benjamin.bihler@gmx.de
PostgreSQL version: 8.3.1
Operating system: SUSE Linux
Description: Foreign keys do not work after altering table/column
names
Details:
We have a SQL-script to create tables and constraints, insert data into
these tables, alter the tables and again insert some data. There is one
"master" script file that includes subscripts with
\i Subscript.sql
These scripts do the following:
We create tables A and B with a foreign key stating that A.column references
B.column.
We rename table B into B_NEW and the column B.column into B_NEW.column_new.
Afterwards we want to update values in A.column. We get an error message
(translated from German):
Relation "public.B" does not exist.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE "B.column"
OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
and
Updating or Deletion in table ... violates foreign key contraint.
DETAIL: There is still a reference to ... in table ...
These scripts work correctly with PostgreSQL 8.2.5.
Back to PostgreSQL 8.3.1: If we change the master script, so that it does
not include the subscripts to alter table B and to update A.column and we
call these subscripts separately after the master script has finished, they
WORK!
I mean:
psql -f Master_complete.sql DATABASE_NAME does not work!
psql -f Master_without_altering_B_and_updating_A.sql DATABASE_NAME
psql -f Altering_B_and_updating_A.sql DATABASE_NAME works!
Unfortunately it is not possible to give more details, since these scripts
are part of a commercial product.
