Determining when a row was inserted
Folks - hi
Is it possible to determine when a row was inserted into a table using the system catalogs or such.
I have the situation where I need to find out when a user was added to a user table - the table was not setup with a date to track this.
Thanks
Glenn
***************************************************************************************************
The information in this email is confidential and may be legally privileged. Access to this email by anyone other than the intended addressee is unauthorized. If you are not the intended recipient of this message, any review, disclosure, copying, distribution, retention, or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. If you are not the intended recipient, please reply to or forward a copy of this message to the sender and delete the message, any attachments, and any copies thereof from your system.
***************************************************************************************************
I don't think there is a way to do that. You'll have to create an audit table
and a rule to update it or you'll have to add a column to the table and a
trigger to update it.
On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Show quoted text
Folks - hi
Is it possible to determine when a row was inserted into a table using the
system catalogs or such. I have the situation where I need to find out when
a user was added to a user table - the table was not setup with a date to
track this.Thanks
Glenn
On Thu, Jun 02, 2005 at 06:22:01 +0100,
"Eisenhut, Glenn" <glenn.eisenhut@bearingpoint.com> wrote:
Folks - hi
Is it possible to determine when a row was inserted into a table using the system catalogs or such.
I have the situation where I need to find out when a user was added to a user table - the table was not setup with a date to track this.
No. If you want this information, you have to set it up yourself.
The way I do it is to add a timestamp field with a default value of now().
Unfortunately, this won't help with any records that have already been
created.
Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was inserted
I don't think there is a way to do that. You'll have to create an audit
table
and a rule to update it or you'll have to add a column to the table and a
trigger to update it.
On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table using the
system catalogs or such. I have the situation where I need to find out
when
a user was added to a user table - the table was not setup with a date to
track this.Thanks
Glenn
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
One might even suggest that this should really be a default for all tables
everywhere, because at some time or another, someone wants to know when
something got put in the database...
Alex.
Show quoted text
On 6/3/05, Wiebe de Jong <wiebedj@shaw.ca> wrote:
The way I do it is to add a timestamp field with a default value of now().
Unfortunately, this won't help with any records that have already been
created.Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create an audit
table
and a rule to update it or you'll have to add a column to the table and a
trigger to update it.On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table using
the
system catalogs or such. I have the situation where I need to find out
when
a user was added to a user table - the table was not setup with a date
to
track this.
Thanks
Glenn---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply at bottom...
On Fri, 2005-06-03 at 13:40, Alex Turner wrote:
One might even suggest that this should really be a default for all
tables everywhere, because at some time or another, someone wants to
know when something got put in the database...Alex.
On 6/3/05, Wiebe de Jong <wiebedj@shaw.ca> wrote:
The way I do it is to add a timestamp field with a default
value of now().
Unfortunately, this won't help with any records that have
already been
created.Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry
Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create
an audit
table
and a rule to update it or you'll have to add a column to the
table and a
trigger to update it.On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a
table using the
system catalogs or such. I have the situation where I need
to find out
whena user was added to a user table - the table was not setup
with a date to
track this.
No, this is a terrible idea as a default. while I wouldn't mind having
a switch for it, the cost of storing a timestamp AND having to produce
it for each insert is not worth it, since there are just as likely to be
tables no one cares a wit about when they were last changed.
That kind of designing is what leads to bloated, overweight programs...
Reply at bottom...
On Fri, 2005-06-03 at 12:53, Wiebe de Jong wrote:
The way I do it is to add a timestamp field with a default value of now().
Unfortunately, this won't help with any records that have already been
created.Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create an audit
table
and a rule to update it or you'll have to add a column to the table and a
trigger to update it.On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table using the
system catalogs or such. I have the situation where I need to find outwhen
a user was added to a user table - the table was not setup with a date to
track this.
There are plenty of examples of a trigger to do this so that ANY time
the row is updated, or when it's inserted, the timestamp gets updated to
now() or timeofday.
On 6/3/05, Wiebe de Jong <wiebedj@shaw.ca> wrote:
The way I do it is to add a timestamp field with a default value of now().
Unfortunately, this won't help with any records that have already been
created.
On Fri, Jun 03, 2005 at 02:40:08PM -0400, Alex Turner wrote:
One might even suggest that this should really be a default for all tables
everywhere, because at some time or another, someone wants to know when
something got put in the database...
Except it's still a complete waste of space for most tables. People
have been arguing for years that OIDs are a waste of space and now
they've been made optional and will soon default to off. There's not
likely to be support to add another field in it's place.
Just like timetravel was removed from the core given that most people
don't need it and it's prohibitive in diskspace usage. If you want it,
enable it yourself.
Have a nice day.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
I don't use this for all tables, only the ones with important information in
them, like people, accounts, etc.
I actually have two fields, tsCreated and tsUpdated, both which default to
now(). When I do an update, I set the value of tsUpdated to now(). The
tsCreated field is always left alone. This way, I always know when the
record was created and last updated.
This is much simpler than using triggers to update a separate audit table.
Wiebe
_____
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Friday, June 03, 2005 11:55 AM
To: Wiebe de Jong
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was inserted
Reply at bottom...
On Fri, 2005-06-03 at 12:53, Wiebe de Jong wrote:
The way I do it is to add a timestamp field with a default value of now().
Unfortunately, this won't help with any records that have already been
created.Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create an audit
table
and a rule to update it or you'll have to add a column to the table and a
trigger to update it.On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table using
the
system catalogs or such. I have the situation where I need to find out
when
a user was added to a user table - the table was not setup with a date
to
track this.
There are plenty of examples of a trigger to do this so that ANY time
the row is updated, or when it's inserted, the timestamp gets updated to
now() or timeofday.
Please reply on the bottom, it makes it much easier to follow the
responses...
On Fri, 2005-06-03 at 14:05, Wiebe de Jong wrote:
I don't use this for all tables, only the ones with important
information in them, like people, accounts, etc.I actually have two fields, tsCreated and tsUpdated, both which
default to now(). When I do an update, I set the value of tsUpdated to
now(). The tsCreated field is always left alone. This way, I always
know when the record was created and last updated.This is much simpler than using triggers to update a separate audit
table.Wiebe
______________________________________________________________________
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Friday, June 03, 2005 11:55 AM
To: Wiebe de Jong
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedReply at bottom...
On Fri, 2005-06-03 at 12:53, Wiebe de Jong wrote:
The way I do it is to add a timestamp field with a default value of
now().
Unfortunately, this won't help with any records that have already
been
created.
Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Terry LeeTucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create an
audit
table
and a rule to update it or you'll have to add a column to the tableand a
trigger to update it.
On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table
using the
system catalogs or such. I have the situation where I need to find
out
when
a user was added to a user table - the table was not setup with a
date to
track this.
There are plenty of examples of a trigger to do this so that ANY time
the row is updated, or when it's inserted, the timestamp gets updated
to
now() or timeofday.
You don't have to have a separate audit table, and the trigger ensures
that you have the right time whether your application tried to set it
wrong or not. So, if a new user thinks he needs to set it to something,
a trigger will still set it to what it should be.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
On Fri, 2005-06-03 at 13:40, Alex Turner wrote:
One might even suggest that this should really be a default for all
tables everywhere, because at some time or another, someone wants to
know when something got put in the database...
That kind of designing is what leads to bloated, overweight programs...
Agreed --- it is much more important to be sure that we have the
features needed to let people add these sorts of behaviors for
themselves (in this case, triggers).
As it happens, the original Berkeley-era Postgres did indeed add
creation and deletion timestamps to every row, as part of their "time
travel" feature. That got ripped out very soon after the code left
Berkeley, because the overhead was just unacceptable ... and our
threshold for unacceptable performance was a whole lot higher then
than it is today ...
It's worth noting in connection with this Joe Hellerstein's description
of Berkeley-era Postgres:
http://archives.postgresql.org/pgsql-hackers/2002-06/msg00085.php
regards, tom lane
True, although a trigger have the benefit of being able to capture the value
before it was changed allowing some measure of versioning in your data which
can be a lifesaver...
Alex Turner
netEconomist
Show quoted text
On 6/3/05, Wiebe de Jong <wiebedj@shaw.ca> wrote:
I don't use this for all tables, only the ones with important information
in them, like people, accounts, etc.I actually have two fields, tsCreated and tsUpdated, both which default
to now(). When I do an update, I set the value of tsUpdated to now(). The
tsCreated field is always left alone. This way, I always know when the
record was created and last updated.This is much simpler than using triggers to update a separate audit
table.Wiebe
------------------------------
*From:* Scott Marlowe [mailto:smarlowe@g2switchworks.com]
*Sent:* Friday, June 03, 2005 11:55 AM
*To:* Wiebe de Jong
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] Determining when a row was insertedReply at bottom...
On Fri, 2005-06-03 at 12:53, Wiebe de Jong wrote:
The way I do it is to add a timestamp field with a default value of
now().
Unfortunately, this won't help with any records that have already been
created.Wiebe
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org<pgsql-general-owner@postgresql.org>]On Behalf Of Terry Lee Tucker
Sent: Thursday, June 02, 2005 5:51 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Determining when a row was insertedI don't think there is a way to do that. You'll have to create an audit
table
and a rule to update it or you'll have to add a column to the table anda
trigger to update it.
On Thursday 02 June 2005 01:22 am, Eisenhut, Glenn saith:
Folks - hi
Is it possible to determine when a row was inserted into a table using
the
system catalogs or such. I have the situation where I need to find out
when
a user was added to a user table - the table was not setup with a date
to
track this.
There are plenty of examples of a trigger to do this so that ANY time
the row is updated, or when it's inserted, the timestamp gets updated to
now() or timeofday.
I really wasn't suggesting it be put in the table structure at the DB level,
more a sidebar suggestion for people building schemas for companies. I can't
count the number of times I've been asked when something was inserted and we
didn't have an answer for the question. Wouldn't it be nice for a change to
be _ahead_ of the game?
Alex Turner
netEconomist
Show quoted text
On 6/3/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
On 6/3/05, Wiebe de Jong <wiebedj@shaw.ca> wrote:
The way I do it is to add a timestamp field with a default value of
now().
Unfortunately, this won't help with any records that have already been
created.On Fri, Jun 03, 2005 at 02:40:08PM -0400, Alex Turner wrote:
One might even suggest that this should really be a default for all
tables
everywhere, because at some time or another, someone wants to know when
something got put in the database...Except it's still a complete waste of space for most tables. People
have been arguing for years that OIDs are a waste of space and now
they've been made optional and will soon default to off. There's not
likely to be support to add another field in it's place.Just like timetravel was removed from the core given that most people
don't need it and it's prohibitive in diskspace usage. If you want it,
enable it yourself.Have a nice day.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting forsomeone
else to do the other 95% so you can sue them.
Tom Lane <tgl@sss.pgh.pa.us> writes:
As it happens, the original Berkeley-era Postgres did indeed add
creation and deletion timestamps to every row, as part of their "time
travel" feature. That got ripped out very soon after the code left
Berkeley, because the overhead was just unacceptable ... and our
threshold for unacceptable performance was a whole lot higher then
than it is today ...It's worth noting in connection with this Joe Hellerstein's description
of Berkeley-era Postgres:
http://archives.postgresql.org/pgsql-hackers/2002-06/msg00085.php
The followups are fascinating too. The next three messages immediately begin
discussing how to get back this feature at least as an option.
--
greg
In article <33c6269f05060312363ff334df@mail.gmail.com>,
Alex Turner <armtuk@gmail.com> wrote:
% I really wasn't suggesting it be put in the table structure at the DB level,
% more a sidebar suggestion for people building schemas for companies. I can't
% count the number of times I've been asked when something was inserted and we
% didn't have an answer for the question. Wouldn't it be nice for a change to
% be _ahead_ of the game?
Just sticking a time stamp on the row doesn't solve this problem, though,
unless you preclude the possibility of the row being updated. Typically,
someone wants to know when a particular field held a particular value,
and you need an audit table for that.
--
Patrick TJ McPhee
North York Canada
ptjm@interlog.com