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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
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 ('[EMAIL PROTECTED]', '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;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;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
