pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedconstraint deferred but fails?
6+ messages / 4 participants
[nested] [flat]
* constraint deferred but fails?
@ 2019-01-24 06:35 Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 3 replies; 6+ messages in thread
From: Nagy László Zsolt @ 2019-01-24 06:35 UTC (permalink / raw)
To: pgsql-admin
Hello,
I have deferred the only fk constraint to this table inside a
transaction, but I still cannot delete rows from it.
How is that possible?
master=> ALTER TABLE doc.display_mode disable trigger user;
ALTER TABLE
master=> BEGIN;
BEGIN
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
SET CONSTRAINTS
master=> DELETE FROM doc.display_mode;
ERROR: update or delete on table "display_mode" violates foreign key
constraint "fk_book_display_mode_id" on table "book"
DETAIL: Key (id)=(1) is still referenced from table "book".
master=>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: constraint deferred but fails?
@ 2019-01-24 06:39 David G. Johnston <david.g.johnston@gmail.com>
parent: Nagy László Zsolt <gandalf@shopzeus.com>
2 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2019-01-24 06:39 UTC (permalink / raw)
To: Nagy László Zsolt <gandalf@shopzeus.com>; +Cc: pgsql-admin
On Wednesday, January 23, 2019, Nagy László Zsolt <gandalf@shopzeus.com>
wrote:
>
> master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
>
The constraint itself has to be changed to deferrable in order for it to be
affected by the above.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: constraint deferred but fails?
@ 2019-01-24 06:42 Nagy László Zsolt <gandalf@shopzeus.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nagy László Zsolt @ 2019-01-24 06:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
It is already deferrable:
ALTER TABLE doc.book ADD CONSTRAINT fk_book_display_mode_id FOREIGN KEY
(display_mode_id) REFERENCES doc.display_mode(id) DEFERRABLE
2019-01-24 07:39 keltezéssel, David G. Johnston írta:
> On Wednesday, January 23, 2019, Nagy László Zsolt
> <gandalf@shopzeus.com <mailto:gandalf@shopzeus.com>> wrote:
>
> master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
>
>
> The constraint itself has to be changed to deferrable in order for it
> to be affected by the above.
>
> David J.
>
>
> --
> This message has been scanned for viruses and
> dangerous content by *MailScanner* <http://www.mailscanner.info/;, and is
> believed to be clean.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: constraint deferred but fails?
@ 2019-01-24 07:01 Ron <ronljohnsonjr@gmail.com>
parent: Nagy László Zsolt <gandalf@shopzeus.com>
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2019-01-24 07:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/24/19 12:35 AM, Nagy László Zsolt wrote:
> Hello,
>
> I have deferred the only fk constraint to this table inside a
> transaction, but I still cannot delete rows from it.
>
> How is that possible?
>
>
> master=> ALTER TABLE doc.display_mode disable trigger user;
> ALTER TABLE
> master=> BEGIN;
> BEGIN
> master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
> SET CONSTRAINTS
> master=> DELETE FROM doc.display_mode;
> ERROR: update or delete on table "display_mode" violates foreign key
> constraint "fk_book_display_mode_id" on table "book"
> DETAIL: Key (id)=(1) is still referenced from table "book".
> master=>
Deferring a constraint doesn't mean "disappearing" the constraint; it just
means... *deferring* it until later (i.e. commit time). It's only purpose
is to allow your code to delete from doc.display_mode before deleting from
book . But you *do* have to delete from book at some point in the transaction.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: constraint deferred but fails?
@ 2019-01-24 07:08 David G. Johnston <david.g.johnston@gmail.com>
parent: Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2019-01-24 07:08 UTC (permalink / raw)
To: Nagy László Zsolt <gandalf@shopzeus.com>; +Cc: pgsql-admin
Please don’t top-post replies on these lists.
On Wednesday, January 23, 2019, Nagy László Zsolt <gandalf@shopzeus.com>
wrote:
> It is already deferrable:
>
> ALTER TABLE doc.book ADD CONSTRAINT fk_book_display_mode_id FOREIGN KEY
> (display_mode_id) REFERENCES doc.display_mode(id) DEFERRABLE
>
Ignoring the question of why you want to do things this way it seems you
need to provide a self-contained, complete, test case to get a sound
answer. Though I suspect it may have to do with an on delete clause you
haven’t shown.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: constraint deferred but fails?
@ 2019-01-24 07:08 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Nagy László Zsolt <gandalf@shopzeus.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shreeyansh Dba @ 2019-01-24 07:08 UTC (permalink / raw)
To: Nagy László Zsolt <gandalf@shopzeus.com>; +Cc: pgsql-admin
To automate this, you could define the foreign key constraint with ON
DELETE CASCADE.
So when deleting first delete child rows and then parent.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Thu, Jan 24, 2019 at 12:05 PM Nagy László Zsolt <gandalf@shopzeus.com>
wrote:
>
> Hello,
>
> I have deferred the only fk constraint to this table inside a
> transaction, but I still cannot delete rows from it.
>
> How is that possible?
>
>
> master=> ALTER TABLE doc.display_mode disable trigger user;
> ALTER TABLE
> master=> BEGIN;
> BEGIN
> master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
> SET CONSTRAINTS
> master=> DELETE FROM doc.display_mode;
> ERROR: update or delete on table "display_mode" violates foreign key
> constraint "fk_book_display_mode_id" on table "book"
> DETAIL: Key (id)=(1) is still referenced from table "book".
> master=>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-01-24 07:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-24 06:35 constraint deferred but fails? Nagy László Zsolt <gandalf@shopzeus.com>
2019-01-24 06:39 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-24 06:42 ` Nagy László Zsolt <gandalf@shopzeus.com>
2019-01-24 07:08 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-24 07:01 ` Ron <ronljohnsonjr@gmail.com>
2019-01-24 07:08 ` Shreeyansh Dba <shreeyansh2014@gmail.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