Getting rows in statement-level triggers

So the manual says there is no way for a statement-level trigger to
examine the row(s) modified by the statement.

Is there any way to get the xmin or cmin of the transaction that fired
the trigger? Or can I look up the last xid for a table some where?

Ok, so it took a lot of googling to figure this one out, but you can do
it with something like so.

SELECT *
FROM strand_scores
WHERE xmin::text = txid_current()::text

It appears you can't convert a xid type to int or bigint, not sure why.
I guess I should leave a comment on the manual page.

On Fri, Oct 3, 2008 at 11:42 AM, Artacus <artacus@comcast.net> wrote:

So the manual says there is no way for a statement-level trigger to

examine the row(s) modified by the statement.

Is there any way to get the xmin or cmin of the transaction that fired the
trigger? Or can I look up the last xid for a table some where?

Ok, so it took a lot of googling to figure this one out, but you can do it
with something like so.

SELECT *
FROM strand_scores
WHERE xmin::text = txid_current()::text

It appears you can't convert a xid type to int or bigint, not sure why. I
guess I should leave a comment on the manual page.

That's an interesting find, i'd say.

xmin::bigint doesn't work because that implicit CAST doesn't exist. If
needed, I'd use xmin::text::bigint; that should work.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Artacus <artacus@comcast.net> writes:

Ok, so it took a lot of googling to figure this one out, but you can do
it with something like so.

SELECT *
FROM strand_scores
WHERE xmin::text = txid_current()::text

This will not work, or at least it will stop working after your
installation passes 4 billion transactions.

The traditional way to find out your own XID is to insert a dummy row
somewhere (perhaps in a temp table) and see what its xmin is. I'm not
sure why we don't expose a more convenient way --- maybe just that
there's not been any demand for it.

regards, tom lane

Tom Lane wrote:

The traditional way to find out your own XID is to insert a dummy row
somewhere (perhaps in a temp table) and see what its xmin is. I'm not
sure why we don't expose a more convenient way --- maybe just that
there's not been any demand for it.

I think we've suggested the trick of checking the xmin of some row
enough times that a better way to access it is warranted. There's
certainly been more demand than "not any".

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

SELECT *
FROM strand_scores
WHERE xmin::text = txid_current()::text

This will not work, or at least it will stop working after your
installation passes 4 billion transactions.

The traditional way to find out your own XID is to insert a dummy row
somewhere (perhaps in a temp table) and see what its xmin is. I'm not
sure why we don't expose a more convenient way --- maybe just that
there's not been any demand for it.

regards, tom lane

Well the good news there is that it won't break during the period we
have to provide free bug fixes :)
Inserting a row to get the xid seems pretty kludgy.

If you wouldn't mind answering a few more questions Tom:

* So what exactly happens at the 4 billion mark?
* Why can't I cast an xid to int or bigint?
* The table in question is the largest in the db. How performative
is it to query based on xmin? Is xmin indexed? Can it be?

BTW, if there were no demand for a convenient way to get your xid, then
there shouldn't be a "traditional" way :)

Artacus

Artacus <artacus@comcast.net> writes:

This will not work, or at least it will stop working after your
installation passes 4 billion transactions.

* So what exactly happens at the 4 billion mark?

txid keeps going (that's why it's a bigint). xid will wrap around.

* Why can't I cast an xid to int or bigint?

No very strong reason except that no such code is provided...
but you do have to think hard about the fact that xid comparison
is circular and does not work at all like integer comparison.

* The table in question is the largest in the db. How performative
is it to query based on xmin? Is xmin indexed? Can it be?

Not very, no, and no.

If you were really desperate you could make an indexed bigint column
that was filled with current_txid by an insert/update trigger, and
then search on that.

regards, tom lane