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

Fw: Problem with plpgsql functions and foreign key constraints.

From: "Brian Hirt" <bhirt(at)mobygames(dot)com>
To: "Postgres Hackers" <pgsql-hackers(at)postgresql(dot)org>
Cc: <bhirt(at)mobygames(dot)com>
Subject: Fw: Problem with plpgsql functions and foreign key constraints.
Date: 2001-06-24 07:17:08
Message-ID: 008e01c0fc7d$aef3d1e0$640b0a0a@berkhirt.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I forgot to mention that this is happening on 7.0.3and 7.1.1  -- and I'm
running on a RedHat 7.0 machine.

----- Original Message -----
From: "Brian Hirt" <bhirt(at)berkhirt(dot)com>
To: "Postgres Hackers" <pgsql-hackers(at)postgresql(dot)org>
Cc: <bhirt(at)mobygames(dot)com>
Sent: Sunday, June 24, 2001 1:12 AM
Subject: Problem with plpgsql functions and foreign key constraints.


> I recently ran across this (i think) bug relating to constraints and
> functions written in plpgsql. It seems that I'm getting erroneous foreign
> key violations.  I've included two scripts which create the simplest test
> case I can reproduce.  One script has a foreign key defined and the other
> one doesn't.  Other than that, they are identical.  From the data in the
> scripts, it's obvious there aren't any violations of keys.
>
> [postgres(at)loopy postgres]$ diff /tmp/good.sql /tmp/bad.sql
> 18c18
> < create table c2 ( id int, value_sum int);
> ---
> > create table c2 ( id int references c(id), value_sum int);
>
> [postgres(at)loopy postgres]$ psql test < /tmp/good.sql
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'c_pkey' for
> table 'c'
> CREATE
> INSERT 19107 1
> INSERT 19108 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> INSERT 19126 1
> INSERT 19127 1
> INSERT 19128 1
> INSERT 19129 1
> INSERT 19130 1
> INSERT 19131 1
> CREATE
> CREATE
> CREATE
> UPDATE 6
>  id | value_sum
> ----+-----------
>   1 |         6
>   2 |        17
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
> [postgres(at)loopy postgres]$ psql test < /tmp/bad.sql
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'c_pkey' for
> table 'c'
> CREATE
> INSERT 19164 1
> INSERT 19165 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> INSERT 19183 1
> INSERT 19184 1
> INSERT 19185 1
> INSERT 19186 1
> INSERT 19187 1
> INSERT 19188 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> CREATE
> CREATE
> ERROR:  triggered data change violation on relation "c2"
>  id | value_sum
> ----+-----------
> (0 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
> (0 rows)
>
>
>

Attachment: bad.sql
Description: application/octet-stream (975 bytes)
Attachment: good.sql
Description: application/octet-stream (958 bytes)

pgsql-hackers by date

Next:From: Bernardo PonsDate: 2001-06-24 10:05:12
Subject: RE: Extracting metadata about attributes from catalog
Previous:From: Brian HirtDate: 2001-06-24 07:12:38
Subject: Problem with plpgsql functions and foreign key constraints.

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