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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
