Обсуждение: update before drop causes OID problems in transaction?
Not sure if this is the best list for this issue, but I ran into something that I thought should work inside a transaction, but obviously PostgreSQL thought otherwise. Postgres version is 8.1.3. The transaction I wrote is basically: BEGIN; DROP RULE foo_audit_no_update ON foo_audit; UPDATE foo_audit SET modified_by = 1 WHERE modified_by IS NULL; I then copy the data out of foo_audit into some temporary tables, drop foo_audit, alter table foo as I want it to be, then recreate foo_audit and all the triggers, functions, copy the data back into foo_audit from the temp tables and then COMMIT; After commit, I get a lovely: ERROR: could not open relation with OID x Is this expected? To solve this, I simply moved my initial update outside the transaction. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost <jeff@frostconsultingllc.com> writes:
> After commit, I get a lovely:
> ERROR: could not open relation with OID x
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.
regards, tom lane
On Fri, 17 Mar 2006, Tom Lane wrote: > Jeff Frost <jeff@frostconsultingllc.com> writes: >> After commit, I get a lovely: >> ERROR: could not open relation with OID x > > 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. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
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
Jeff Frost <jeff@frostconsultingllc.com> writes:
> 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?
No, I think it's that you've got a plpgsql trigger function that
contains queries referring to credit_card_audit. Dropping and
recreating that table invalidates plpgsql's cached plans for those
queries.
We do have in mind to fix this (Neil Conway was poking at it, last
I heard) but it won't happen before 8.2 at the earliest. In the
meantime I'm wondering why you are insistent on dropping and recreating
credit_card_audit, as opposed to something less invasive like TRUNCATE.
regards, tom lane
Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Sat, 18 Mar 2006, Tom Lane wrote:
>> No, I think it's that you've got a plpgsql trigger function that
>> contains queries referring to credit_card_audit. Dropping and
>> recreating that table invalidates plpgsql's cached plans for those
>> queries.
> Is that the case whether the triggers are executed or not?
If the trigger function hasn't ever been executed in the current
session, it wouldn't have a cached plan ... but I suspect you meant
"if it hasn't been executed in the current transaction", and that
doesn't help.
> However, we drop that trigger before
> dropping credit_card_audit, so I'd think that would be ok.
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.
regards, tom lane
On Sat, 18 Mar 2006, Tom Lane wrote: > No, I think it's that you've got a plpgsql trigger function that > contains queries referring to credit_card_audit. Dropping and > recreating that table invalidates plpgsql's cached plans for those > queries. Is that the case whether the triggers are executed or not? There aren't any triggers on credit_card_audit, but credit_card has the audit_credit_card trigger which calls a plpgsql function. However, we drop that trigger before dropping credit_card_audit, so I'd think that would be ok. Also, we aren't modifying data in credit_card, so I wouldn't think that trigger would fire anyway. Of course, I probably am missing something here. > > We do have in mind to fix this (Neil Conway was poking at it, last > I heard) but it won't happen before 8.2 at the earliest. In the > meantime I'm wondering why you are insistent on dropping and recreating > credit_card_audit, as opposed to something less invasive like TRUNCATE. I inherited this procedure from the previous DBA and hadn't looked at streamlining until now. I would guess it's because we have a script which generates the SQL responsible for setting up the audit table and associated trigger, constraints and functions..thus making it easier to just drop and recreate the table with the automatically generated SQL. The procedure has worked well in the past, but this is the first time I needed to incorporate an update due to changing a NOT NULL constraint. I didn't think this to be the expected behavior for this query, so I thought I'd post and see whether I was thinking along the wrong lines. If this is the expected behavior, then TRUNCATE...ALTER TABLE appears like the way to go in the future. Thanks, as always, for the info! -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Sat, 18 Mar 2006, Tom Lane wrote: >> Is that the case whether the triggers are executed or not? > > If the trigger function hasn't ever been executed in the current > session, it wouldn't have a cached plan ... but I suspect you meant > "if it hasn't been executed in the current transaction", and that > doesn't help. well, actually, I sort of meant both, though of course I'd prefer the same transaction. I was actually calling this script via psql -f so it would only be that transaction in that one session. >> However, we drop that trigger before >> dropping credit_card_audit, so I'd think that would be ok. > > 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. I'll try that and see if that makes the difference, since we're recreating (create or replace) that function in that transaction anyway, but perhaps that needs to happen before the update. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Fri, 17 Mar 2006, Jeff Frost wrote: > I'll try that and see if that makes the difference, since we're recreating > (create or replace) that function in that transaction anyway, but perhaps > that needs to happen before the update. I added this at the top of the transaction: DROP FUNCTION public.audit_credit_card (); and had to move the drop trigger above it, so the order looked like so: BEGIN; DROP RULE credit_card_audit_no_update ON credit_card_audit; DROP TRIGGER audit_credit_card ON credit_card; DROP FUNCTION public.audit_credit_card (); Same result: psql:transaction-test-case.sql:212: ERROR: could not open relation with OID 29976142 -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
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 <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost wrote: > 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 I'm not sure a deferred constraint makes sense if you're dropping the table before the end of the transaction. I'm not sure whether the DROP should be prevented or what other error should be provided, but I can't see how both the constraint and the drop can occur. > 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. Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing. -- Richard Huxton Archonet Ltd
On Mon, 20 Mar 2006, Richard Huxton wrote: >> 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 > > I'm not sure a deferred constraint makes sense if you're dropping the table > before the end of the transaction. I'm not sure whether the DROP should be > prevented or what other error should be provided, but I can't see how both > the constraint and the drop can occur. Indeed much of this transaction might not make sense as it is really all done just for schema change and not part of normal operation. And in fact, you're correct that removing the DEFERRABLE property of the constraint allows the transaction to commit, so the workaround for my update as part of the transaction problem would be to set constraints immediate as part of that transaction like so: SET CONSTRAINTS credit_card_audit_account_id_fkey IMMEDIATE; And indeed this does work. > Another problem might well be with your plpgsql trigger function. If you're > dropping/re-creating credit_card_audit then that'll give you the error you're > seeing. The trigger shouldn't be firing at all in this scenario as it is on credit_card and not credit_card_audit. Are you saying that it could cause this sort of problem even though it doesn't fire? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost wrote: >> Another problem might well be with your plpgsql trigger function. If >> you're dropping/re-creating credit_card_audit then that'll give you >> the error you're seeing. > > The trigger shouldn't be firing at all in this scenario as it is on > credit_card and not credit_card_audit. Are you saying that it could > cause this sort of problem even though it doesn't fire? My mistake - I'd misread the trigger definition and assumed it was attached to the table you were dropping. -- Richard Huxton Archonet Ltd
Jeff Frost wrote: > >> Another problem might well be with your plpgsql trigger function. If >> you're dropping/re-creating credit_card_audit then that'll give you >> the error you're seeing. > > The trigger shouldn't be firing at all in this scenario as it is on > credit_card and not credit_card_audit. Are you saying that it could > cause this sort of problem even though it doesn't fire? Actually, my last reply isn't quite accurate. Looking at it, you do update "credit_card_audit" from within the trigger. So, it can cause a problem if called, but if it isn't called then it will not. -- Richard Huxton Archonet Ltd