Thread: 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
dateto track this. 

Thanks
Glenn






***************************************************************************************************
The information in this email is confidential and may be legally privileged.  Access to this email by anyone other than
theintended 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
deletethe 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:
> 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
dateto 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.

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 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.


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 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.

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/
> 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.

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 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 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.
>
> 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

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

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