pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate before drop causes OID problems in transaction?
14+ messages / 3 participants
[nested] [flat]
* update before drop causes OID problems in transaction?
@ 2006-03-17 22:41 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-17 22:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-17 22:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2006-03-17 22:52 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-17 22:55 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-17 22:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 04:20 Jeff Frost <jeff@frostconsultingllc.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-18 04:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 | character varying(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) WHERE active =
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 without time 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_update AS
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 06:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2006-03-18 06:27 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 06:48 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-18 06:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 06:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2006-03-18 06:55 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 07:11 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-18 07:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-18 07:18 Jeff Frost <jeff@frostconsultingllc.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 14+ messages in thread
From: Jeff Frost @ 2006-03-18 07:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-19 02:21 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Jeff Frost @ 2006-03-19 02:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
Attachments:
transaction-test-case-setup.sql (text/plain, 4.7K) download
BEGIN;
CREATE TABLE accounts_basics
(
id SERIAL PRIMARY KEY,
country text NOT NULL DEFAULT 'US'::text,
email text NOT NULL,
password_reset bool DEFAULT false,
public_handle varchar(32) NOT NULL,
valid_email bool NOT NULL DEFAULT false,
modified_by int4,
event_type text
);
CREATE TABLE credit_card_type
(
id SERIAL PRIMARY KEY,
credit_card_type text NOT NULL,
CONSTRAINT ck_cc_type UNIQUE (credit_card_type)
);
CREATE TABLE credit_card
(
id SERIAL PRIMARY KEY,
account_id int4 NOT NULL,
profile_id int4 NOT NULL,
expires date NOT NULL,
active bool,
cc_number varchar(64),
credit_card_type text NOT NULL,
billing_name varchar(30) NOT NULL,
modified_by int4,
event_type text,
CONSTRAINT "$1" FOREIGN KEY (account_id)
REFERENCES accounts_basics (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT fk_cc_type FOREIGN KEY (credit_card_type)
REFERENCES credit_card_type (credit_card_type) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE credit_card_audit
(
credit_card_audit_id SERIAL PRIMARY KEY,
actor text NOT NULL DEFAULT "current_user"(),
"action" text NOT NULL,
credit_card_action_time timestamptz NOT NULL DEFAULT now(),
event_type text,
modified_by int4,
account_id int4,
credit_card_old credit_card,
credit_card_new credit_card,
CONSTRAINT credit_card_audit_action_check CHECK ("action" = 'INSERT'::text OR "action" = 'UPDATE'::text OR "action" = 'DELETE'::text),
CONSTRAINT credit_card_audit_modified_by_fkey FOREIGN KEY (modified_by)
REFERENCES accounts_basics (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
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
);
CREATE OR REPLACE FUNCTION 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;
$$;
CREATE TRIGGER audit_credit_card
BEFORE INSERT OR UPDATE OR DELETE
ON credit_card
FOR EACH ROW
EXECUTE PROCEDURE audit_credit_card();
CREATE INDEX credit_card_audit_event_type_idx
ON credit_card_audit
USING btree
(event_type);
CREATE INDEX credit_card_audit_modified_by_idx
ON credit_card_audit
USING btree
(modified_by);
CREATE OR REPLACE RULE credit_card_audit_no_delete AS
ON DELETE TO credit_card_audit DO INSTEAD NOTHING;
CREATE OR REPLACE RULE credit_card_audit_no_update AS
ON UPDATE TO credit_card_audit DO INSTEAD NOTHING;
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);
INSERT INTO credit_card_type (credit_card_type) VALUES ('fake');
INSERT INTO accounts_basics ( email, public_handle, modified_by, event_type )
VALUES ('nobody@nowhere.int', 'Dude567', 1, 'Test');
INSERT INTO credit_card (account_id, profile_id, expires, active, cc_number, credit_card_type, billing_name, modified_by, event_type)
VALUES (1, 1, '12/12/2020', 't', '1234567890', 'fake', 'The Dude', 1, 'test');
INSERT INTO credit_card (account_id, profile_id, expires, active, cc_number, credit_card_type, billing_name, event_type)
VALUES (1, 1, '1/15/2006', 'f', '1234567890', 'fake', 'Jeff Lebowski', 'test');
COMMIT;
transaction-test-case.sql (text/plain, 198B) download
BEGIN;
DROP RULE credit_card_audit_no_update ON credit_card_audit;
UPDATE credit_card_audit SET modified_by = 1
WHERE modified_by IS NULL;
DROP TABLE credit_card_audit;
COMMIT;
* Re: update before drop causes OID problems in transaction?
@ 2006-03-20 14:17 Richard Huxton <dev@archonet.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Richard Huxton @ 2006-03-20 14:17 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-20 17:18 Jeff Frost <jeff@frostconsultingllc.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 14+ messages in thread
From: Jeff Frost @ 2006-03-20 17:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-20 17:29 Richard Huxton <dev@archonet.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 0 replies; 14+ messages in thread
From: Richard Huxton @ 2006-03-20 17:29 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: update before drop causes OID problems in transaction?
@ 2006-03-20 17:30 Richard Huxton <dev@archonet.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 0 replies; 14+ messages in thread
From: Richard Huxton @ 2006-03-20 17:30 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-03-20 17:30 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-17 22:41 update before drop causes OID problems in transaction? Jeff Frost <jeff@frostconsultingllc.com>
2006-03-17 22:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-17 22:55 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-18 04:20 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-18 06:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-18 06:48 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-18 06:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-18 07:11 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-18 07:18 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-19 02:21 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-20 14:17 ` Richard Huxton <dev@archonet.com>
2006-03-20 17:18 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-20 17:29 ` Richard Huxton <dev@archonet.com>
2006-03-20 17:30 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox