| Lists: | pgsql-general |
|---|
| From: | Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | deferrable foreign keys |
| Date: | 2009-12-02 14:29:21 |
| Message-ID: | 20091202152921.0b939407@tucholsky.experteer.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Hi,
are there downsides of making foreign keys deferrable (but initially
immediate) for updates, when the transaction does not set the
constraint behaviour to deferred?
I'd expect that to have the same behaviour as non deferrable foreign
keys.
What I don't understand is, why is non deferrable the default, then.
So maybe I miss something.
regards
Morus
| From: | Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> |
|---|---|
| To: | Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: deferrable foreign keys |
| Date: | 2009-12-02 14:34:09 |
| Message-ID: | 2f4958ff0912020634y2ea7c238o37139b38e3ecd254@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Wed, Dec 2, 2009 at 2:29 PM, Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com
> wrote:
> Hi,
>
> are there downsides of making foreign keys deferrable (but initially
> immediate) for updates, when the transaction does not set the
> constraint behaviour to deferred?
>
> I'd expect that to have the same behaviour as non deferrable foreign
> keys.
> What I don't understand is, why is non deferrable the default, then.
>
> it is just sometimes desired to not check the constraints, until comit. For
instance, if you run bit of code that is old, and you don't want to mess
around with keys.
Or you have some strange way of putting information together.
Basically it is all about order of operation within transaction. Sometimes
it cannot be guaranteed, and hence an option to defer the constraint check.
--
GJ
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: deferrable foreign keys |
| Date: | 2009-12-02 15:42:12 |
| Message-ID: | 13460.1259768532@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> writes:
> are there downsides of making foreign keys deferrable (but initially
> immediate) for updates, when the transaction does not set the
> constraint behaviour to deferred?
> I'd expect that to have the same behaviour as non deferrable foreign
> keys.
> What I don't understand is, why is non deferrable the default, then.
Because the SQL standard says so. I don't believe there is any actual
penalty for deferrable within the PG implementation, but perhaps there
is in other systems' implementations.
regards, tom lane
| From: | Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: deferrable foreign keys |
| Date: | 2009-12-03 07:55:55 |
| Message-ID: | 20091203085555.6d32151b@tucholsky.experteer.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Hallo Tom,
> Morus Walter <morus(dot)walter(dot)ml(at)googlemail(dot)com> writes:
> > are there downsides of making foreign keys deferrable (but initially
> > immediate) for updates, when the transaction does not set the
> > constraint behaviour to deferred?
>
> > I'd expect that to have the same behaviour as non deferrable foreign
> > keys.
> > What I don't understand is, why is non deferrable the default, then.
>
> Because the SQL standard says so.
Ok. Understood.
> I don't believe there is any actual
> penalty for deferrable within the PG implementation, but perhaps there
> is in other systems' implementations.
>
Thanks a lot for your help.
Morus