Subject: Re: constraint deferred but fails?
To: pgsql-admin@lists.postgresql.org
References: <7db1a61c-1f0e-d233-0756-b7eecf2f420d@shopzeus.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <fcf3589d-7f24-c12a-18d2-54cac14ea1c0@gmail.com>
Date: Thu, 24 Jan 2019 01:01:27 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <7db1a61c-1f0e-d233-0756-b7eecf2f420d@shopzeus.com>
Content-Type: multipart/alternative;
 boundary="------------4E19363D73B146D4DB322788"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------4E19363D73B146D4DB322788
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------4E19363D73B146D4DB322788
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    On 1/24/19 12:35 AM, Nagy László Zsolt wrote:<br>
    <blockquote type="cite"
      cite="mid:7db1a61c-1f0e-d233-0756-b7eecf2f420d@shopzeus.com">
      <pre class="moz-quote-pre" wrap="">
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=&gt; ALTER TABLE doc.display_mode disable trigger user;
ALTER TABLE
master=&gt; BEGIN;
BEGIN
master=&gt; SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
SET CONSTRAINTS
master=&gt; 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=&gt;</pre>
    </blockquote>
    <br>
    Deferring a constraint doesn't mean "disappearing" the constraint;
    it just means... <b>deferring</b> it until later (i.e. commit
    time).  It's only purpose is to allow your code to delete from <font
      color="#3333ff"><tt>doc.display_mode</tt></font>
    before deleting from <font color="#3333ff"><tt>book</tt></font>
    .  But you <b>do</b> have to delete from <font color="#3333ff"><tt>book</tt></font>
    at some point in the transaction.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------4E19363D73B146D4DB322788--