Thread: Foreign key constraint still active after table row removed

Foreign key constraint still active after table row removed

From

"Christoph Jaeger"

Date:

10 September 2003, 13:41:13

=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.

Re: Foreign key constraint still active after table row removed

From

Tom Lane

Date:

10 September 2003, 16:42:17

"Christoph Jaeger" <christoph.jaeger@dhl.com> writes:
>   PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3

7.1.3 is ancient history, and no it doesn't have defenses against you
changing a column definition that a foreign key linkage refers to.
I'd recommend updating to 7.3.4.

> 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.

In 7.1, drop the triggers and you're done.  AFAIR this would also be
necessary in 7.2.  In 7.3 you could have just dropped the columns you
wanted to drop, and not had all these problems.

            regards, tom lane

Re: Foreign key constraint still active after table row removed

From

"Christoph Jaeger"

Date:

15 September 2003, 14:23:33

> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org=20
> [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Wednesday, September 10, 2003 6:42 PM
> To: Christoph Jaeger
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] Foreign key constraint still active after=20
> table row removed=20
>=20
>=20
> "Christoph Jaeger" <christoph.jaeger@dhl.com> writes:
> >   PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3=20=
=20
>=20
> 7.1.3 is ancient history, and no it doesn't have defenses against you
> changing a column definition that a foreign key linkage refers to.
> I'd recommend updating to 7.3.4.
>=20

Ok, so I will upgrade.=20

There are already some invalid constraints in my database due to some
fields I dropped earlier. I only found out about these now, because the
data in these tables is quite static, and I did only INSERTs (which do
not trigger the constraints), no UPDATEs. The problem is, these
constraints get exported in a pg_dump, and, of course, reimported with a
pg_restore. I guess I can solve this by manually editing the dump file
(remove the unneeded CREATE CONSTRAINT statements). Is there a better
way to do this?

Thanks a lot,

Best Regards,

Christoph J=E4ger

> > The table pg_trigger shows three rows, which seem to point=20
> to this no
> > longer valid constraint, but I do not think it is a good=20
> idea to fiddle
> > with this unless one really knows how this all works together.
>=20
> In 7.1, drop the triggers and you're done.  AFAIR this would also be
> necessary in 7.2.  In 7.3 you could have just dropped the columns you
> wanted to drop, and not had all these problems.
>=20
>             regards, tom lane
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>=20