Re: update before drop causes OID problems in transaction? - Mailing list 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     |
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