Re: update before drop causes OID problems in transaction? - Mailing list pgsql-sql

From Jeff Frost
Subject Re: update before drop causes OID problems in transaction?
Date
Msg-id Pine.LNX.4.64.0603200910200.30710@discord.dyndns.org
Whole thread Raw
In response to Re: update before drop causes OID problems in transaction?  (Richard Huxton <dev@archonet.com>)
Responses Re: update before drop causes OID problems in transaction?
Re: update before drop causes OID problems in transaction?
List pgsql-sql
On Mon, 20 Mar 2006, Richard Huxton wrote:

>> I stripped the tables and queries down to the minimum that demonstrated the 
>> error.  Interestingly, the problem was not reproducible until I added the 
>> credit_card_audit_account_id constraint below:
>>
>>    CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id)
>>       REFERENCES accounts_basics (id) MATCH FULL
>>       ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
>
> I'm not sure a deferred constraint makes sense if you're dropping the table 
> before the end of the transaction. I'm not sure whether the DROP should be 
> prevented or what other error should be provided, but I can't see how both 
> the constraint and the drop can occur.

Indeed much of this transaction might not make sense as it is really all done 
just for schema change and not part of normal operation.  And in fact, you're 
correct that removing the DEFERRABLE property of the constraint allows the 
transaction to commit, so the workaround for my update as part 
of the transaction problem would be to set constraints immediate as part of 
that transaction like so:

SET CONSTRAINTS credit_card_audit_account_id_fkey IMMEDIATE;

And indeed this does work.

> Another problem might well be with your plpgsql trigger function. If you're 
> dropping/re-creating credit_card_audit then that'll give you the error you're 
> seeing.

The trigger shouldn't be firing at all in this scenario as it is on 
credit_card and not credit_card_audit.  Are you saying that it could cause 
this sort of problem even though it doesn't fire?

-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954


pgsql-sql by date:

Previous
From: PFC
Date:
Subject: Re: have you feel anything when you read this ?
Next
From: Richard Huxton
Date:
Subject: Re: update before drop causes OID problems in transaction?