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

Hi,
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

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

Thanks for any input.

Best wishes,

M

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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/

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

I only need to know if there a row has been inserted.
I am trying to extend dbmirror so that I can easily
replicate all the transaction from a query even if
there it contains multiple update statements within
it.

Currently, this will insert multiple rows into a
pending table with different ID's.

Alternatively, is there some way to figure out what
the pid of the current connection is from the C
trigger function?

Best wishes,

M



__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

On Sat, Sep 25, 2004 at 08:44:18AM -0700, Kundham Saare wrote:

> Alternatively, is there some way to figure out what
> the pid of the current connection is from the C
> trigger function?

getpid() should work.

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