figuring out if there was a transaction in this connection already

[Kundham Saare]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Kundham Saare]

> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org