Thread: DEFERRABLE and DEFERRED

Hi!

It seems not possible to defer anything other than FOREIGN KEY
constraints. Is this true, and are there reasons for this?

What we want to do fill a record step by step, leaving some NOT NULL
columns empty in the first place and later in the transaction UPDATEing
them to contain some values. Collecting the values first would be
impractical as the update statements are somewhat scattered over various
modules of the program.

something like
avbidder=> create table blah ( fasel text not null deferrable initially
deferred ) ;
ERROR:  Misplaced DEFERRABLE clause

should imho be possible (of course it doesn't make sense if there's only
one column, but this doesn't matter - the error persists).

any hints? thanks.

-- vbi


--
secure email with gpg            avbidder@fortytwo.ch: key id 0x92082481
                                 avbidder@acter.ch:    key id 0x5E4B731F

At least one idea is that you aren't altering the row multiple times. Why
not cache/queue your changes in the application and avoid the
extraneous database IO in the first place?

Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200
1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230
200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200
1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232
200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22

On 5 Jun 2002, Adrian 'Dagurashibanipal' von Bidder wrote:

> Hi!
>
> It seems not possible to defer anything other than FOREIGN KEY
> constraints. Is this true, and are there reasons for this?
>
> What we want to do fill a record step by step, leaving some NOT NULL
> columns empty in the first place and later in the transaction UPDATEing
> them to contain some values. Collecting the values first would be
> impractical as the update statements are somewhat scattered over various
> modules of the program.
>
> something like
> avbidder=> create table blah ( fasel text not null deferrable initially
> deferred ) ;
> ERROR:  Misplaced DEFERRABLE clause
>
> should imho be possible (of course it doesn't make sense if there's only
> one column, but this doesn't matter - the error persists).
>
> any hints? thanks.
>
> -- vbi
>
>
> --
> secure email with gpg            avbidder@fortytwo.ch: key id 0x92082481
>                                  avbidder@acter.ch:    key id 0x5E4B731F
>
>

On 5 Jun 2002, Adrian 'Dagurashibanipal' von Bidder wrote:

> It seems not possible to defer anything other than FOREIGN KEY
> constraints. Is this true, and are there reasons for this?

Yes and because noone's done it yet. :)
Seriously, it's generally less important for not null, unique and primary
key.  I'd expect check constraints to get it first.

> What we want to do fill a record step by step, leaving some NOT NULL
> columns empty in the first place and later in the transaction UPDATEing
> them to contain some values. Collecting the values first would be
> impractical as the update statements are somewhat scattered over various
> modules of the program.

You could probably write trigger functions that you could use with
create constraint trigger that would let you defer the check.  You'd have
to make sure that the row you were given is still valid (do nothing if
it is not).