pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedApparent referential integrity bug in PL/pgSQL
2+ messages / 2 participants
[nested] [flat]
* Apparent referential integrity bug in PL/pgSQL
@ 2002-10-18 07:15 Brian Blaha <bblaha@umr.edu>
2002-10-18 15:05 ` Re: Apparent referential integrity bug in PL/pgSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Brian Blaha @ 2002-10-18 07:15 UTC (permalink / raw)
To: pgsql-sql
I have a function that operates on two tables A and B, such that B has a
foreign key on A, as follows:
INSERT INTO A (...) several times
INSERT INTO B (...) several times, with foreign keys pointing to the new
members of A
DELETE FROM A (...), possibly including some of the newly added members
Even though B's foreign key is defined ON DELETE CASCADE, I get a
referential integrity
violation when I run this function. If instead, I comment out the DELETE
statement, start a
transaction block, run the function, run the DELETE statement, and end
the transaction, no
errors occur. To run those statements with one function call, I need to
split the INSERTs and
DELETEs into separate functions, and call them separately from a third
function. I am using
version 7.2.2. Has this been corrected in the beta versions or can
someone confirm this for me?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Apparent referential integrity bug in PL/pgSQL
2002-10-18 07:15 Apparent referential integrity bug in PL/pgSQL Brian Blaha <bblaha@umr.edu>
@ 2002-10-18 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-10-18 15:05 UTC (permalink / raw)
To: Brian Blaha <bblaha@umr.edu>; +Cc: pgsql-sql
On Fri, 18 Oct 2002, Brian Blaha wrote:
> I have a function that operates on two tables A and B, such that B has a
> foreign key on A, as follows:
> INSERT INTO A (...) several times
> INSERT INTO B (...) several times, with foreign keys pointing to the new
> members of A
> DELETE FROM A (...), possibly including some of the newly added members
>
> Even though B's foreign key is defined ON DELETE CASCADE, I get a
> referential integrity
> violation when I run this function. If instead, I comment out the DELETE
> statement, start a
> transaction block, run the function, run the DELETE statement, and end
> the transaction, no
> errors occur. To run those statements with one function call, I need to
> split the INSERTs and
> DELETEs into separate functions, and call them separately from a third
> function. I am using
> version 7.2.2. Has this been corrected in the beta versions or can
> someone confirm this for me?
No it hasn't, but I see what's happening. The rows from b are being
checked before they are deleted by the trigger that runs after the check.
I think that to do this case right, all of the ref actions would need
to be done before any of the noaction or check triggers. This is
technically what the spec says to do afaict, but I don't think we'd seen a
case before where it matters.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-18 15:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-18 07:15 Apparent referential integrity bug in PL/pgSQL Brian Blaha <bblaha@umr.edu>
2002-10-18 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox