Thread: How to get Transaction Timestamp ?
Respected All,
Can we get the transaction timestamp for INSERT/UPDATE/DELETE ran against table in the database ?
postgres=# create table trx_test(id int, name char(30));
CREATE TABLE
postgres=# insert into trx_test VALUES (1,'AAA');
INSERT 0 1
postgres=# insert into trx_test VALUES (2,'BBB');
INSERT 0 1
postgres=# insert into trx_test VALUES (3,'CCC');
INSERT 0 1
postgres=# select xmin,* from trx_test ;
xmin | id | name
---------+----+--------------------------------
1348711 | 1 | AAA
1348712 | 2 | BBB
1348713 | 3 | CCC
(3 rows)
We can get a Transaction ID, but not the transaction timestamp when it performed.
Kindly advice me.
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Fri, Sep 16, 2011 at 21:39, Raghavendra <raghavendra.rao@enterprisedb.com> wrote: > We can get a Transaction ID, but not the transaction timestamp when it > performed. Short answer: You can't. Instead, add a new "timestamptz default now()" column, that will get you the time of the insert. If you want the update time, create a BEFORE UPDATE ON x FOR EACH ROW trigger on this table to update it. Regards, Marti
Thank you for your valuable inputs.
Agreed, with the help of two workarounds we can pull the trx-timestamp one with additional-column/trigger and another with log_line_prefix from pg_log/logs.
However, I was curious to know any thing stored at Page-Level(like XID) to help me in getting the transaction timestamp.
--Raghav
On Sat, Sep 17, 2011 at 7:41 AM, Marti Raudsepp <marti@juffo.org> wrote:
On Fri, Sep 16, 2011 at 21:39, Raghavendra<raghavendra.rao@enterprisedb.com> wrote:> We can get a Transaction ID, but not the transaction timestamp when itShort answer: You can't. Instead, add a new "timestamptz default
> performed.
now()" column, that will get you the time of the insert.
If you want the update time, create a BEFORE UPDATE ON x FOR EACH ROW
trigger on this table to update it.
Regards,
Marti
Trigger may store timestamp to other table "timestamps". You join then xmin with xmin from this table to access timestamp. 2011/9/17, Raghavendra <raghavendra.rao@enterprisedb.com>: > Thank you for your valuable inputs. > > Agreed, with the help of two workarounds we can pull the trx-timestamp one > with additional-column/trigger and another with log_line_prefix from > pg_log/logs. > > However, I was curious to know any thing stored at Page-Level(like XID) to > help me in getting the transaction timestamp. > > --Raghav > > On Sat, Sep 17, 2011 at 7:41 AM, Marti Raudsepp <marti@juffo.org> wrote: > >> On Fri, Sep 16, 2011 at 21:39, Raghavendra >> <raghavendra.rao@enterprisedb.com> wrote: >> > We can get a Transaction ID, but not the transaction timestamp when it >> > performed. >> >> Short answer: You can't. Instead, add a new "timestamptz default >> now()" column, that will get you the time of the insert. >> >> If you want the update time, create a BEFORE UPDATE ON x FOR EACH ROW >> trigger on this table to update it. >> >> Regards, >> Marti >> > -- ------------ pasman
2011/9/17 pasman pasmański <pasman.p@gmail.com>: > Trigger may store timestamp to other table "timestamps". You join then > xmin with xmin from this table to access timestamp. But note that this is not reliable. Vacuum *does* change xmin during the freeze process, as does a pg_dump&reload of data. Possibly other cases that I didn't think of. I wouldn't use this hack in a real application. Regards, Marti
On Sep 17, 2011, at 1:09 AM, Raghavendra wrote: > However, I was curious to know any thing stored at Page-Level(like XID) to help me in getting the transaction timestamp. No, there is no such thing. If you want timestamps, you have to record them yourself. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice