Re: figuring out if there was a transaction in this connection already

On Sat, Sep 25, 2004 at 07:16:19AM -0700, Kundham Saare wrote:
> I would like to be able to figure out if a table has
> been updated in this connection from within a C
> trigger.
>
> I have already tried to the use a query with currval
> on the autoincremented primary key but that exits the
> trigger with
>
> table.currval is not yet defined in this session

Checking currval() isn't a valid test for inserts because an insert
might have been rolled back, but the sequence would still have been
incremented:

test=> SELECT currval('person_id_seq');
ERROR:  currval of sequence "person_id_seq" is not yet defined in this session
test=> BEGIN;
BEGIN
test=> INSERT INTO person (name) VALUES ('John Doe');
INSERT 30437 1
test=> ROLLBACK;
ROLLBACK
test=> SELECT currval('person_id_seq');
 currval
---------
      12

Checking currval() also wouldn't tell you whether any rows in a
table had been updated.

> Is there a way to trap / ignore this error? Or a way
> to check if there was a transaction in this connection
> before.

Why do you need to know this?  What are you trying to do?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/