Thread: update before drop causes OID problems in transaction?

update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
Not sure if this is the best list for this issue, but I ran into something 
that I thought should work inside a transaction, but obviously PostgreSQL 
thought otherwise.

Postgres version is 8.1.3.  The transaction I wrote is basically:

BEGIN;
DROP RULE foo_audit_no_update ON foo_audit;

UPDATE foo_audit SET modified_by = 1
WHERE modified_by IS NULL;

I then copy the data out of foo_audit into some temporary tables,
drop foo_audit, alter table foo as I want it to be, then recreate foo_audit 
and all the triggers, functions, copy the data back into foo_audit from the 
temp tables and then

COMMIT;

After commit, I get a lovely:
 ERROR:  could not open relation with OID x

Is this expected?  To solve this, I simply moved my initial update outside the 
transaction.


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


Re: update before drop causes OID problems in transaction?

From
Tom Lane
Date:
Jeff Frost <jeff@frostconsultingllc.com> writes:
> After commit, I get a lovely:
>   ERROR:  could not open relation with OID x

Could we see a complete test case, rather than handwaving?  I'd expect
some issues like this if you were using any prepared statements or
plpgsql functions with non-EXECUTEd queries involving the dropped table,
but your description doesn't mention either of those risk factors.
        regards, tom lane


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Fri, 17 Mar 2006, Tom Lane wrote:

> Jeff Frost <jeff@frostconsultingllc.com> writes:
>> After commit, I get a lovely:
>>   ERROR:  could not open relation with OID x
>
> Could we see a complete test case, rather than handwaving?  I'd expect
> some issues like this if you were using any prepared statements or
> plpgsql functions with non-EXECUTEd queries involving the dropped table,
> but your description doesn't mention either of those risk factors.

Tom, it's for a client, so let me see if they'll allow me to post the 
transaction, if not, I'll have to write something equivalent.  More later.


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


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Fri, 17 Mar 2006, Jeff Frost wrote:

>> Could we see a complete test case, rather than handwaving?  I'd expect
>> some issues like this if you were using any prepared statements or
>> plpgsql functions with non-EXECUTEd queries involving the dropped table,
>> but your description doesn't mention either of those risk factors.
>
> Tom, it's for a client, so let me see if they'll allow me to post the 
> transaction, if not, I'll have to write something equivalent.  More later.

Alright, they are fine with me sharing the SQL, so here goes:

I suspect I've answered my own question while preparing the test case. Is it 
the use of pg_get_serial_sequence at the bottom of the transaction?  If so, 
why does it only have a problem when there is an update to credit_card_audit 
in the transaction?

If I'm looking at this correctly, the OID referenced is credit_card_audit:

SELECT * from pg_class where relfilenode = 29976142;      relname      | relnamespace | reltype  | relowner | relam |
relfilenode
 
| reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | 
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | 
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | 
relhassubclass | relacl

-------------------+--------------+----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
credit_card_audit|         2200 | 29976143 |    16387 |     0 |    29976142 
 
|             0 |      133 |      3329 |      29976148 |             0 | t 
| f           | r       |        9 |         1 |           6 |        0 | 
0 |       0 | t          | t          | t           | f              |
(1 row)



Below is the transaction and following that is a \d of the credit_card and 
credit_card_audit tables:

BEGIN;

DROP RULE credit_card_audit_no_update ON credit_card_audit;

-- We have a not null constraint in the new table
-- Without this UPDATE, the transaction is fine
-- but with it, we get the ERROR:  could not open relation
-- with OID 29976142

UPDATE credit_card_audit SET modified_by = 1
WHERE modified_by IS NULL;


CREATE TEMP TABLE ca_common (LIKE credit_card_audit) ON COMMIT DROP;
INSERT INTO ca_common SELECT * FROM credit_card_audit;
ALTER TABLE ca_common DROP COLUMN credit_card_old;
ALTER TABLE ca_common DROP COLUMN credit_card_new;

CREATE TEMP TABLE ca_old (credit_card_audit_id INTEGER, LIKE credit_card) ON 
COMMIT DROP;
ALTER TABLE ca_old ALTER column id drop not null;
ALTER TABLE ca_old ALTER column account_id drop not null;
ALTER TABLE ca_old ALTER column profile_id drop not null;
ALTER TABLE ca_old ALTER column expires drop not null;
ALTER TABLE ca_old ALTER column credit_card_type drop not null;
ALTER TABLE ca_old ALTER column billing_name drop not null;

INSERT INTO ca_old
SELECT
credit_card_audit_id,
(credit_card_old).*
FROM credit_card_audit;

CREATE TEMP TABLE ca_new (credit_card_audit_id INTEGER, LIKE credit_card) ON 
COMMIT DROP;
ALTER TABLE ca_new ALTER column id drop not null;
ALTER TABLE ca_new ALTER column account_id drop not null;
ALTER TABLE ca_new ALTER column profile_id drop not null;
ALTER TABLE ca_new ALTER column expires drop not null;
ALTER TABLE ca_new ALTER column credit_card_type drop not null;
ALTER TABLE ca_new ALTER column billing_name drop not null;

INSERT INTO ca_new
SELECT
credit_card_audit_id,
(credit_card_new).*
FROM credit_card_audit;

DROP TRIGGER audit_credit_card ON credit_card;
DROP TABLE credit_card_audit;

DROP VIEW cc_with_id_view;

ALTER TABLE credit_card DROP COLUMN billing_name;

-- recreate credit_card_audit

CREATE TABLE public.credit_card_audit (
credit_card_audit_id BIGSERIAL PRIMARY KEY
, actor TEXT NOT NULL DEFAULT current_user
, action TEXT NOT NULL CHECK(action IN ('INSERT', 'UPDATE', 'DELETE'))
, credit_card_action_time TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP
, event_type TEXT
, modified_by INTEGER NOT NULL REFERENCES accounts_basics(id)
, credit_card_old public.credit_card
, credit_card_new public.credit_card
);

COMMENT ON TABLE public.credit_card_audit IS $$
Timestamp, old and new column sets for auditing.
This gets written on any change to public.credit_card.

It was created via
/home/jeff.frost/p4/ops-dbtools/set_up_audit.pl -t credit_card -e 
modified_by=INTEGER NOT NULL REFERENCES accounts_basics(id) -e event_type=TEXT
$$;

CREATE RULE credit_card_audit_no_delete AS
ON DELETE TO public.credit_card_audit
DO INSTEAD NOTHING;

CREATE RULE credit_card_audit_no_update AS
ON UPDATE TO public.credit_card_audit
DO INSTEAD NOTHING;


CREATE INDEX credit_card_audit_event_type_idx
ON public.credit_card_audit(event_type);

CREATE INDEX credit_card_audit_modified_by_idx
ON public.credit_card_audit(modified_by);

CREATE OR REPLACE FUNCTION public.audit_credit_card ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
rows_affected INTEGER;
BEGIN
PERFORM tablename
FROM pg_tables
WHERE tablename = TG_RELNAME || '_audit';
IF NOT FOUND THEN    RAISE EXCEPTION 'No audit table found for %', TG_RELNAME;
END IF;
IF TG_OP = 'INSERT' THEN    INSERT INTO public.credit_card_audit (action, event_type, modified_by, 
credit_card_new)    VALUES ('INSERT', NEW.event_type, NEW.modified_by, NEW );
ELSIF TG_OP = 'UPDATE' THEN    INSERT INTO public.credit_card_audit (action, event_type, modified_by, 
credit_card_old, credit_card_new)    VALUES ('UPDATE', NEW.event_type, NEW.modified_by, OLD , NEW );
ELSIF TG_OP = 'DELETE' THEN    INSERT INTO public.credit_card_audit (action, event_type, modified_by, 
credit_card_old)    VALUES ('DELETE', OLD.event_type, OLD.modified_by, OLD );
ELSE    RAISE EXCEPTION 'TG_OP is none of INSERT, UPDATE or DELETE.';
END IF;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
IF rows_affected = 1 THEN    IF TG_OP IN ('INSERT', 'UPDATE') THEN        RETURN NEW;    ELSE        RETURN OLD;    END
IF;
ELSE    RAISE EXCEPTION 'INSERT failed on public.credit_card_audit';
END IF;
END;
$$;

COMMENT ON FUNCTION public.audit_credit_card ()
IS $$
Trigger function that logs actions on the public.credit_card table to
public.credit_card_audit for auditing purposes.

It was created via
/home/jeff.frost/p4/ops-dbtools/set_up_audit.pl -t credit_card -e 
modified_by=INTEGER NOT NULL REFERENCES accounts_basics(id) -e event_type=TEXT
$$;

CREATE TRIGGER audit_credit_card
BEFORE INSERT OR UPDATE OR DELETE ON public.credit_card
FOR EACH ROW EXECUTE PROCEDURE public.audit_credit_card ();

COMMENT ON TRIGGER audit_credit_card ON public.credit_card IS
$$
Trigger that calls public.audit_credit_card().

It was created via
/home/jeff.frost/p4/ops-dbtools/set_up_audit.pl -t credit_card -e 
modified_by=INTEGER NOT NULL REFERENCES accounts_basics(id) -e event_type=TEXT
$$;


--end of credit_card_audit setup

INSERT INTO credit_card_audit (credit_card_audit_id, actor, action, credit_card_action_time, event_type, modified_by,
credit_card_old,credit_card_new)
 
SELECT c.credit_card_audit_id,  c.actor,c.action,  c.credit_card_action_time,  c.event_type,  c.modified_by,
(o.id, o.account_id, o.profile_id, o.expires, o.active, o.cc_number, o.credit_card_type, o.modified_by,
o.event_type)::credit_card,
(n.id, n.account_id, n.profile_id, n.expires, n.active, n.cc_number, n.credit_card_type, n.modified_by,
n.event_type)::credit_card
FROM    ca_common c
JOIN    ca_old o    ON (c.credit_card_audit_id = o.credit_card_audit_id)
JOIN    ca_new n    ON (c.credit_card_audit_id = n.credit_card_audit_id);

SELECT    setval(        pg_get_serial_sequence(            'credit_card_audit',            'credit_card_audit_id'
 ),    max(credit_card_audit_id)
 
)
FROM credit_card_audit;


CREATE OR REPLACE VIEW cc_with_id_view AS SELECT cc.id, cc.account_id, cc.profile_id, cc.expires, cc.active, 
cc.cc_number, cct.id AS credit_card_type_id   FROM credit_card cc   JOIN credit_card_type cct USING
(credit_card_type);

ALTER TABLE cc_with_id_view OWNER TO perpetual;

COMMIT;

\d credit_card                                     Table "public.credit_card"      Column      |         Type
|                       Modifiers
 
------------------+-----------------------+---------------------------------------------------------- id
|integer               | not null default 
 
nextval('credit_card_id_seq'::regclass) account_id       | integer               | not null profile_id       | integer
            | not null expires          | date                  | not null active           | boolean               |
cc_number       | character varying(64) | credit_card_type | text                  | not null billing_name     |
charactervarying(30) | not null modified_by      | integer               | event_type       | text                  |
 
Indexes:    "credit_cards_pkey" PRIMARY KEY, btree (id)    "account_cc_uniq" UNIQUE, btree (account_id, cc_number)
WHEREactive = 
 
true    "credit_cards_account_id" btree (account_id)
Foreign-key constraints:    "$1" FOREIGN KEY (account_id) REFERENCES accounts_basics(id) ON DELETE 
CASCADE    "$2" FOREIGN KEY (profile_id) REFERENCES billing_profile(id) ON DELETE 
CASCADE    "credit_card_event_type_fkey" FOREIGN KEY (event_type) REFERENCES 
event_type(event_type) MATCH FULL    "credit_card_modified_by_fkey" FOREIGN KEY (modified_by) REFERENCES 
accounts_basics(id) MATCH FULL    "fk_cc_type" FOREIGN KEY (credit_card_type) REFERENCES 
credit_card_type(credit_card_type)
Triggers:    audit_credit_card BEFORE INSERT OR DELETE OR UPDATE ON credit_card FOR 
EACH ROW EXECUTE PROCEDURE audit_credit_card()



\d credit_card_audit                                                     Table 
"public.credit_card_audit"         Column          |            Type             | 
Modifiers

-------------------------+-----------------------------+----------------------------------------------------------------------------------
actor                  | text                        | not null default 
 
"current_user"() action                  | text                        | not null credit_card_action_time | timestamp
withouttime zone | not null default 
 
('now'::text)::timestamp(6) with time zone credit_card_old         | credit_card                 | credit_card_new
  | credit_card                 | credit_card_audit_id    | bigint                      | not null default 
 
nextval('credit_card_audit_credit_card_audit_id_seq'::regclass) account_id              | integer                     |
event_type             | text                        | modified_by             | integer                     |
 
Indexes:    "credit_card_audit_pkey" PRIMARY KEY, btree (credit_card_audit_id)    "credit_card_audit_account_id_idx"
btree(account_id)    "credit_card_audit_event_type_idx" btree (event_type)    "credit_card_audit_modified_by_idx" btree
(modified_by)
Check constraints:    "credit_card_audit_action_check" CHECK ("action" = 'INSERT'::text OR 
"action" = 'UPDATE'::text OR "action" = 'DELETE'::text)
Foreign-key constraints:    "credit_card_audit_account_id_fkey" FOREIGN KEY (account_id) REFERENCES 
accounts_basics(id) MATCH FULL DEFERRABLE INITIALLY DEFERRED    "credit_card_audit_event_type_fkey" FOREIGN KEY
(event_type)REFERENCES 
 
event_type(event_type) MATCH FULL    "credit_card_audit_modified_by_fkey" FOREIGN KEY (modified_by) REFERENCES 
accounts_basics(id) MATCH FULL
Rules:    credit_card_audit_no_delete AS    ON DELETE TO credit_card_audit DO INSTEAD NOTHING
credit_card_audit_no_updateAS    ON UPDATE TO credit_card_audit DO INSTEAD NOTHING
 



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


Re: update before drop causes OID problems in transaction?

From
Tom Lane
Date:
Jeff Frost <jeff@frostconsultingllc.com> writes:
> I suspect I've answered my own question while preparing the test case. Is it 
> the use of pg_get_serial_sequence at the bottom of the transaction?  If so, 
> why does it only have a problem when there is an update to credit_card_audit 
> in the transaction?

No, I think it's that you've got a plpgsql trigger function that
contains queries referring to credit_card_audit.  Dropping and
recreating that table invalidates plpgsql's cached plans for those
queries.

We do have in mind to fix this (Neil Conway was poking at it, last
I heard) but it won't happen before 8.2 at the earliest.  In the
meantime I'm wondering why you are insistent on dropping and recreating
credit_card_audit, as opposed to something less invasive like TRUNCATE.
        regards, tom lane


Re: update before drop causes OID problems in transaction?

From
Tom Lane
Date:
Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Sat, 18 Mar 2006, Tom Lane wrote:
>> No, I think it's that you've got a plpgsql trigger function that
>> contains queries referring to credit_card_audit.  Dropping and
>> recreating that table invalidates plpgsql's cached plans for those
>> queries.

> Is that the case whether the triggers are executed or not?

If the trigger function hasn't ever been executed in the current
session, it wouldn't have a cached plan ... but I suspect you meant
"if it hasn't been executed in the current transaction", and that
doesn't help.

> However, we drop that trigger before 
> dropping credit_card_audit, so I'd think that would be ok.

IIRC you'd have to drop the underlying plpgsql function, not only
the trigger object that connects the function to a table.  We cache
stuff with respect to the function.
        regards, tom lane


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Sat, 18 Mar 2006, Tom Lane wrote:

> No, I think it's that you've got a plpgsql trigger function that
> contains queries referring to credit_card_audit.  Dropping and
> recreating that table invalidates plpgsql's cached plans for those
> queries.

Is that the case whether the triggers are executed or not?  There aren't any 
triggers on credit_card_audit, but credit_card has the audit_credit_card 
trigger which calls a plpgsql function.  However, we drop that trigger before 
dropping credit_card_audit, so I'd think that would be ok.  Also, we aren't 
modifying data in credit_card, so I wouldn't think that trigger would fire 
anyway.  Of course, I probably am missing something here.

>
> We do have in mind to fix this (Neil Conway was poking at it, last
> I heard) but it won't happen before 8.2 at the earliest.  In the
> meantime I'm wondering why you are insistent on dropping and recreating
> credit_card_audit, as opposed to something less invasive like TRUNCATE.

I inherited this procedure from the previous DBA and hadn't looked at 
streamlining until now.  I would guess it's because we have a script which 
generates the SQL responsible for setting up the audit table and associated 
trigger, constraints and functions..thus making it easier to just drop and 
recreate the table with the automatically generated SQL.

The procedure has worked well in the past, but this is the first time I needed 
to incorporate an update due to changing a NOT NULL constraint.  I didn't 
think this to be the expected behavior for this query, so I thought I'd post 
and see whether I was thinking along the wrong lines.  If this is the expected 
behavior, then TRUNCATE...ALTER TABLE appears like the way to go in the 
future.

Thanks, as always, for the info!

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


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Sat, 18 Mar 2006, Tom Lane wrote:

>> Is that the case whether the triggers are executed or not?
>
> If the trigger function hasn't ever been executed in the current
> session, it wouldn't have a cached plan ... but I suspect you meant
> "if it hasn't been executed in the current transaction", and that
> doesn't help.

well, actually, I sort of meant both, though of course I'd prefer the same 
transaction.  I was actually calling this script via psql -f so it would only 
be that transaction in that one session.

>> However, we drop that trigger before
>> dropping credit_card_audit, so I'd think that would be ok.
>
> IIRC you'd have to drop the underlying plpgsql function, not only
> the trigger object that connects the function to a table.  We cache
> stuff with respect to the function.

I'll try that and see if that makes the difference, since we're recreating 
(create or replace) that function in that transaction anyway, but perhaps that 
needs to happen before the update.

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


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Fri, 17 Mar 2006, Jeff Frost wrote:

> I'll try that and see if that makes the difference, since we're recreating 
> (create or replace) that function in that transaction anyway, but perhaps 
> that needs to happen before the update.

I added this at the top of the transaction:

DROP FUNCTION public.audit_credit_card ();
and had to move the drop trigger above it, so the order looked like so:

BEGIN;
DROP RULE credit_card_audit_no_update ON credit_card_audit;
DROP TRIGGER audit_credit_card ON credit_card;
DROP FUNCTION public.audit_credit_card ();

Same result:

psql:transaction-test-case.sql:212: ERROR:  could not open relation with OID 
29976142



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


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
On Sat, 18 Mar 2006, Tom Lane wrote:

> IIRC you'd have to drop the underlying plpgsql function, not only
> the trigger object that connects the function to a table.  We cache
> stuff with respect to the function.

Tom, sorry it took me a little while to make a test case.  The test case is 
attached.  If the attachments don't get through to the mailing list, you can 
grab the files here:

http://www.frostconsultingllc.com/testcase/transaction-test-case-setup.sql
http://www.frostconsultingllc.com/testcase/transaction-test-case.sql

transaction-test-case-setup.sql will create the appropriate tables and 
transaction-test-case.sql will demonstrate the error.

You can reproduce the problem like so:

createdb testcase
createlang plpgsql testcase
psql -f doc/perpetual/transaction-test-case-setup.sql testcase
psql -f doc/perpetual/transaction-test-case.sql testcase

psql:transaction-test-case.sql:10: ERROR:  could not open relation with OID 
2038878

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
 

Now that I've got a test case for you guys to look at, I'm off to rewrite our 
standard procedure to use TRUNCATE instead of DROP.

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

Re: update before drop causes OID problems in transaction?

From
Richard Huxton
Date:
Jeff Frost wrote:
> On Sat, 18 Mar 2006, Tom Lane wrote:
> 
>> IIRC you'd have to drop the underlying plpgsql function, not only
>> the trigger object that connects the function to a table.  We cache
>> stuff with respect to the function.
> 
> Tom, sorry it took me a little while to make a test case.  The test case 
> is attached.  If the attachments don't get through to the mailing list, 
> you can grab the files here:
> 
> http://www.frostconsultingllc.com/testcase/transaction-test-case-setup.sql
> http://www.frostconsultingllc.com/testcase/transaction-test-case.sql
> 
> transaction-test-case-setup.sql will create the appropriate tables and 
> transaction-test-case.sql will demonstrate the error.
> 
> You can reproduce the problem like so:
> 
> createdb testcase
> createlang plpgsql testcase
> psql -f doc/perpetual/transaction-test-case-setup.sql testcase
> psql -f doc/perpetual/transaction-test-case.sql testcase
> 
> psql:transaction-test-case.sql:10: ERROR:  could not open relation with 
> OID 2038878
> 
> 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.

> Now that I've got a test case for you guys to look at, I'm off to 
> rewrite our standard procedure to use TRUNCATE instead of DROP.

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.

--   Richard Huxton  Archonet Ltd


Re: update before drop causes OID problems in transaction?

From
Jeff Frost
Date:
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


Re: update before drop causes OID problems in transaction?

From
Richard Huxton
Date:
Jeff Frost wrote:
>> 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?

My mistake - I'd misread the trigger definition and assumed it was 
attached to the table you were dropping.

--   Richard Huxton  Archonet Ltd


Re: update before drop causes OID problems in transaction?

From
Richard Huxton
Date:
Jeff Frost wrote:
> 
>> 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?

Actually, my last reply isn't quite accurate. Looking at it, you do 
update "credit_card_audit" from within the trigger.

So, it can cause a problem if called, but if it isn't called then it 
will not.

--   Richard Huxton  Archonet Ltd