Re: update before drop causes OID problems in transaction?

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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954