Re: update before drop causes OID problems in transaction?
От | Jeff Frost |
---|---|
Тема | Re: update before drop causes OID problems in transaction? |
Дата | |
Msg-id | Pine.LNX.4.64.0603172012190.11424@discord.dyndns.org обсуждение исходный текст |
Ответ на | Re: update before drop causes OID problems in transaction? (Jeff Frost <jeff@frostconsultingllc.com>) |
Ответы |
Re: update before drop causes OID problems in transaction?
|
Список | 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
В списке pgsql-sql по дате отправления:
Предыдущее
От: Jeff FrostДата:
Сообщение: Re: update before drop causes OID problems in transaction?