Foreign key constraint still active after table row removed - Mailing list pgsql-bugs

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D
                        POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D


Your name          : Christoph J=E4ger
Your email address : christoph.jaeger ( at ) dhl ( dot ) com


System Configuration
---------------------
  Architecture (example: Intel Pentium) : Intel Celeron 800 Mhz

  Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.10

  PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3=20=20

  Compiler used (example:  gcc 2.95.2)        : unknown


Please enter a FULL description of your problem:
------------------------------------------------
I get the following error-message when trying to update a row in one of
my tables:

> update systemcode set name=3D'48h' where id=3D740;
ERROR:  constraint <unnamed>: table orgunit does not have an attribute
country_code_sc

There are two tables involved:

CREATE TABLE systemcode
(
    id        INT4 NOT NULL PRIMARY KEY,
    type        TEXT NOT NULL,
    name        TEXT NOT NULL,
    description TEXT,
    deleted     BOOLEAN
);

CREATE TABLE orgunit
(
    id            INT4 NOT NULL PRIMARY KEY,
    code            TEXT NOT NULL,
    iata_code        TEXT NOT NULL,
    name            TEXT NOT NULL,
    description     TEXT NOT NULL,
    street        TEXT,
    city            TEXT,
    zip             TEXT,
    country_code_sc   INT4,
      operating_hours TEXT,
    deleted         BOOLEAN,

    FOREIGN KEY (country_code_sc) REFERENCES systemcode(id)
);

This is how the tables were originally created. Both were filled with
data. Then I decided to drop some columns (also the column
country_code_sc) from the orgunit table:

BEGIN;

CREATE TABLE temp AS SELECT id, code, name, description,
    operating_hours, location_id, deleted FROM orgunit;
=09
ALTER TABLE orgunit RENAME TO orgunit_old;
DROP INDEX orgunit_pkey;

CREATE TABLE orgunit
(
    id            INT4 NOT NULL PRIMARY KEY,
    code            TEXT NOT NULL,
    name            TEXT NOT NULL,
    description     TEXT NOT NULL,
    operating_hours TEXT,
    location_id     INT4,
    deleted         BOOLEAN,

    FOREIGN KEY (location_id) REFERENCES location(id)
);
CREATE UNIQUE INDEX orgunit_code_idx ON orgunit (code);

INSERT INTO orgunit SELECT * FROM temp;

DROP TABLE temp;

COMMIT;

This worked fine, until I found out, that I can no longer issue UPDATE
statements for the systemcode table:

> update systemcode set name=3D'48h' where id=3D740;
ERROR:  constraint <unnamed>: table orgunit does not have an attribute
country_code_sc

It seems the old constraint used for the foreign key
(orgunit.country_code_sc -> systemcode.id) was not removed when I change
the orgunit table structure. The orgunit table no longer has a
country_code_sc field, but the constraint still wants to check it.

I did this on my development machine, running postgresql 7.1.3. Before I
can issue the table structure change on the production machine also, I
need to know how I can resolve this problem. On this production machine
I run postgres 7.2.3. Maybe this problem is already fixed on this
version, but I do not want to try it out and leave my production system
in an inconsistent state in case it does not work.

The table pg_trigger shows three rows, which seem to point to this no
longer valid constraint, but I do not think it is a good idea to fiddle
with this unless one really knows how this all works together.

I found references to similar problems, and the solution was something
like dropping the table, recreate it and fill it with data again. The
problem here is, the orgunit table was already recreated (and this
seemed to start my problems), and the systemcode table is used as
foreign key in a lot of other tables, and I do not really want to
recreate all the other foreign key constraints after recreating the
systemcode table.


Please describe a way to repeat the problem.   Please
try to provide a
concise reproducible example, if at all possible:=20
----------------------------------------------------------------------
The problem may be reproduced by following the steps described above.



If you know how this problem might be fixed, list the
solution below:
---------------------------------------------------------------------
Sorry, I do not know how the problem may be fixed.