Auto-updated fields

Folks,

A co-worker pointed out to me that MySQL has a feature that, properly
implemented and maybe extended, could be handy, namely what MySQL
calls a "timestamp" field, so here's a proposal:

1. Create a generic (possibly overloaded) trigger function, bundled
with PostgreSQL, which sets a field to some value. For example, a
timestamptz version might set the field to now().

2. Have some kind of pre-processing of CREATE and ALTER statements on
tables which would attach the above function to the field at hand,
something like:

CREATE TABLE foo(
last_updated TIMESTAMPTZ_UPDATED(),
...
);

which would turn last_updated into a TIMESTAMPTZ with the expected
behavior on UPDATEs.

What do folks think of this idea?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> Folks,
>
> A co-worker pointed out to me that MySQL has a feature that, properly
> implemented and maybe extended, could be handy, namely what MySQL
> calls a "timestamp" field, so here's a proposal:
>
> 1. Create a generic (possibly overloaded) trigger function, bundled
> with PostgreSQL, which sets a field to some value. For example, a
> timestamptz version might set the field to now().
>
> 2. Have some kind of pre-processing of CREATE and ALTER statements on
> tables which would attach the above function to the field at hand,
> something like:
>
> CREATE TABLE foo(
> last_updated TIMESTAMPTZ_UPDATED(),
> ...
> );
>
> which would turn last_updated into a TIMESTAMPTZ with the expected
> behavior on UPDATEs.
>
> What do folks think of this idea?

Having the pre defined triggers at hand could be useful, especially
for people not writing triggers so often to get used to it but I'm
really not happy with the idea of magic preprocessing.

I guess this is commonly used with timestamp fields so why not
include a receipe to the docs under examples for timestamp which
shows how to create and use a trigger?

I may be wrong but my feeling is, not to much weirdness in the core
please :) (I guess mysql had it because of lacking triggers and stuff
for a long time?)

T.

Tino Wildenhain <tino(at)wildenhain(dot)de> writes:
> I may be wrong but my feeling is, not to much weirdness in the core
> please :)

+1 ... we have wasted more than enough man-hours trying to get the magic
"serial" type to play nicely. If I had it to do over, we'd never have
put that in at all. The underlying mechanisms are perfectly good ---
it's the idea that the user shouldn't need to know what they're doing
that causes problems.

regards, tom lane

On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
> 1. Create a generic (possibly overloaded) trigger function, bundled
> with PostgreSQL, which sets a field to some value. For example, a
> timestamptz version might set the field to now().

Doesn't the SQL standard GENERATED BY functionality work for this? Or
won't that handle updates?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout írta:
> On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
>
>> 1. Create a generic (possibly overloaded) trigger function, bundled
>> with PostgreSQL, which sets a field to some value. For example, a
>> timestamptz version might set the field to now().
>>
>
> Doesn't the SQL standard GENERATED BY functionality work for this? Or
> won't that handle updates?
>

You mean GENERATED ALWAYS AS (expression)?
Yes, they should be updated on every UPDATE as the expression
may include other fields in the same row.

A GENERATED column implemented as a stored column would
work for this but a virtual column would not. A virtual column
would return different values for "now()" in every SELECT.

However we can argue for use cases of a virtual column and implement
it similarly as VIEWs, i.e an ON SELECT rule can expand the original
expression of the column definition.

I suggest using these syntaxes if we decide to implement them:

GENERATED ALWAYS AS (expression) -- SQL standard stored GENERATED column
GENERATED VIRTUAL AS (expression) -- virtual column, obviously

> Have a nice day,
>

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Zoltan Boszormenyi írta:
> Martijn van Oosterhout írta:
>> On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
>>
>>> 1. Create a generic (possibly overloaded) trigger function, bundled
>>> with PostgreSQL, which sets a field to some value. For example, a
>>> timestamptz version might set the field to now().
>>>
>>
>> Doesn't the SQL standard GENERATED BY functionality work for this? Or
>> won't that handle updates?
>>
>
> You mean GENERATED ALWAYS AS (expression)?
> Yes, they should be updated on every UPDATE as the expression
> may include other fields in the same row.
>
> A GENERATED column implemented as a stored column would
> work for this but a virtual column would not. A virtual column
> would return different values for "now()" in every SELECT.
>
> However we can argue for use cases of a virtual column and implement
> it similarly as VIEWs, i.e an ON SELECT rule can expand the original
> expression of the column definition.
>
> I suggest using these syntaxes if we decide to implement them:
>
> GENERATED ALWAYS AS (expression) -- SQL standard stored GENERATED column
> GENERATED VIRTUAL AS (expression) -- virtual column, obviously

Or, as found in Oracle 11g:

GENERATED ALWAYS AS (expr) VIRTUAL

>
>> Have a nice day,
>>
>
> Best regards,
> Zoltán Böszörményi
>

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> David Fetter wrote:
> > Folks,
> >
> > A co-worker pointed out to me that MySQL has a feature that, properly
> > implemented and maybe extended, could be handy, namely what MySQL
> > calls a "timestamp" field, so here's a proposal:
> >
> > 1. Create a generic (possibly overloaded) trigger function, bundled
> > with PostgreSQL, which sets a field to some value. For example, a
> > timestamptz version might set the field to now().
> >
> > 2. Have some kind of pre-processing of CREATE and ALTER statements on
> > tables which would attach the above function to the field at hand,
> > something like:
> >
> > CREATE TABLE foo(
> > last_updated TIMESTAMPTZ_UPDATED(),
> > ...
> > );
> >
> > which would turn last_updated into a TIMESTAMPTZ with the expected
> > behavior on UPDATEs.
> >
> > What do folks think of this idea?
>
> Having the pre defined triggers at hand could be useful, especially
> for people not writing triggers so often to get used to it but I'm
> really not happy with the idea of magic preprocessing.
>
> I guess this is commonly used with timestamp fields so why not
> include a receipe to the docs under examples for timestamp which
> shows how to create and use a trigger?
>

I have a generic version of this in pagila.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

DF,

> 2. Have some kind of pre-processing of CREATE and ALTER statements on
> tables which would attach the above function to the field at hand,
> something like:
>
> CREATE TABLE foo(
> last_updated TIMESTAMPTZ_UPDATED(),

So you're suggesting a user-definable version of SERIAL?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, 2008-05-08 at 00:41 -0400, Tom Lane wrote:
> Tino Wildenhain <tino(at)wildenhain(dot)de> writes:
> > I may be wrong but my feeling is, not to much weirdness in the core
> > please :)
>
> +1 ... we have wasted more than enough man-hours trying to get the magic
> "serial" type to play nicely. If I had it to do over, we'd never have
> put that in at all. The underlying mechanisms are perfectly good ---
> it's the idea that the user shouldn't need to know what they're doing
> that causes problems.

This kind of hiding will mostly hit the Leaky Abstraction "pattern"

http://www.joelonsoftware.com/articles/LeakyAbstractions.html

http://en.wikipedia.org/wiki/Leaky_abstraction

----------------
Hannu

On Thu, May 08, 2008 at 08:44:46AM +0200, Martijn van Oosterhout wrote:
> On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
> > 1. Create a generic (possibly overloaded) trigger function,
> > bundled with PostgreSQL, which sets a field to some value. For
> > example, a timestamptz version might set the field to now().
>
> Doesn't the SQL standard GENERATED BY functionality work for this?
> Or won't that handle updates?

It appears to, at least according to 6WD2_02_Foundation_2007-12.pdf :)

4.14.8 Base columns and generated columns

A column of a base table is either a base column or a generated
column. A base column is one that is not a generated column. A
generated column is one whose values are determined by evaluation
of a generation expression, a <value expression> whose declared
type is by implication that of the column. A generation expression
can reference base columns of the base table to which it belongs
but cannot otherwise access SQL data. Thus, the value of the field
corresponding to a generated column in row R is determined by the
values of zero or more other fields of R. A generated column GC
depends on each column that is referenced by a <column reference>
in its generation expression, and each such referenced column is a
parametric column of GC.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Robert Treat wrote:
> On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > David Fetter wrote:

Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php

> > > 1. Create a generic (possibly overloaded) trigger function, bundled
> > > with PostgreSQL, which sets a field to some value. For example, a
> > > timestamptz version might set the field to now().

> > Having the pre defined triggers at hand could be useful, especially
> > for people not writing triggers so often to get used to it but I'm
> > really not happy with the idea of magic preprocessing.

> I have a generic version of this in pagila.

Now that we have a specific file in core for generic triggers (right now with a
single one), how about adding this one to it?

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)

Alvaro Herrera wrote:
> Robert Treat wrote:
> > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > > David Fetter wrote:
>
> Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
>
> > > > 1. Create a generic (possibly overloaded) trigger function, bundled
> > > > with PostgreSQL, which sets a field to some value. For example, a
> > > > timestamptz version might set the field to now().
>
> > > Having the pre defined triggers at hand could be useful, especially
> > > for people not writing triggers so often to get used to it but I'm
> > > really not happy with the idea of magic preprocessing.
>
> > I have a generic version of this in pagila.
>
> Now that we have a specific file in core for generic triggers (right now with a
> single one), how about adding this one to it?

Any progress on this? TODO?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Robert Treat wrote:
> > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > > > David Fetter wrote:
> >
> > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
> >
> > > > > 1. Create a generic (possibly overloaded) trigger function,
> > > > > bundled with PostgreSQL, which sets a field to some value. For
> > > > > example, a timestamptz version might set the field to now().
> > > >
> > > > Having the pre defined triggers at hand could be useful, especially
> > > > for people not writing triggers so often to get used to it but I'm
> > > > really not happy with the idea of magic preprocessing.
> > >
> > > I have a generic version of this in pagila.
> >
> > Now that we have a specific file in core for generic triggers (right now
> > with a single one), how about adding this one to it?
>
> Any progress on this? TODO?

I think this is a TODO, but not sure who is working on it or what needs to be
done. The generic version in pagila is perhaps not generic enough:

CREATE FUNCTION last_updated() RETURNS trigger
AS $$
BEGIN
NEW.last_update = CURRENT_TIMESTAMP;
RETURN NEW;
END $$
LANGUAGE plpgsql;

It requires you name your column last_update, which is what the naming
convention is in pagila, but might not work for everyone. Can someone work
with that and move forward? Or maybe give a more specific pointer to the
generic trigger stuff (I've not looked at it before)

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

Robert Treat wrote:
> On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > Robert Treat wrote:
> > > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > > > > David Fetter wrote:
> > >
> > > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
> > >
> > > > > > 1. Create a generic (possibly overloaded) trigger function,
> > > > > > bundled with PostgreSQL, which sets a field to some value. For
> > > > > > example, a timestamptz version might set the field to now().
> > > > >
> > > > > Having the pre defined triggers at hand could be useful, especially
> > > > > for people not writing triggers so often to get used to it but I'm
> > > > > really not happy with the idea of magic preprocessing.
> > > >
> > > > I have a generic version of this in pagila.
> > >
> > > Now that we have a specific file in core for generic triggers (right now
> > > with a single one), how about adding this one to it?
> >
> > Any progress on this? TODO?
>
> I think this is a TODO, but not sure who is working on it or what needs to be
> done. The generic version in pagila is perhaps not generic enough:
>
> CREATE FUNCTION last_updated() RETURNS trigger
> AS $$
> BEGIN
> NEW.last_update = CURRENT_TIMESTAMP;
> RETURN NEW;
> END $$
> LANGUAGE plpgsql;
>
> It requires you name your column last_update, which is what the naming
> convention is in pagila, but might not work for everyone. Can someone work
> with that and move forward? Or maybe give a more specific pointer to the
> generic trigger stuff (I've not looked at it before)

Well, I thought it was a good idea, but no one seems to want to do the
work.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Feb 04, 2009 at 01:23:04PM -0500, Bruce Momjian wrote:
> Robert Treat wrote:
> > On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
> > > Alvaro Herrera wrote:
> > > > Robert Treat wrote:
> > > > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > > > > > David Fetter wrote:
> > > >
> > > > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
> > > >
> > > > > > > 1. Create a generic (possibly overloaded) trigger function,
> > > > > > > bundled with PostgreSQL, which sets a field to some value. For
> > > > > > > example, a timestamptz version might set the field to now().
> > > > > >
> > > > > > Having the pre defined triggers at hand could be useful, especially
> > > > > > for people not writing triggers so often to get used to it but I'm
> > > > > > really not happy with the idea of magic preprocessing.
> > > > >
> > > > > I have a generic version of this in pagila.
> > > >
> > > > Now that we have a specific file in core for generic triggers (right now
> > > > with a single one), how about adding this one to it?
> > >
> > > Any progress on this? TODO?
> >
> > I think this is a TODO, but not sure who is working on it or what needs to be
> > done. The generic version in pagila is perhaps not generic enough:
> >
> > CREATE FUNCTION last_updated() RETURNS trigger
> > AS $$
> > BEGIN
> > NEW.last_update = CURRENT_TIMESTAMP;
> > RETURN NEW;
> > END $$
> > LANGUAGE plpgsql;
> >
> > It requires you name your column last_update, which is what the naming
> > convention is in pagila, but might not work for everyone. Can someone work
> > with that and move forward? Or maybe give a more specific pointer to the
> > generic trigger stuff (I've not looked at it before)
>
> Well, I thought it was a good idea, but no one seems to want to do the
> work.

It's a very short piece of work, but it's new work, and I can't in
good conscience propose including it in the 8.4 release :(

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Treat wrote:
>> On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
>> > Alvaro Herrera wrote:
>> > > Robert Treat wrote:
>> > > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
>> > > > > David Fetter wrote:
>> > >
>> > > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
>> > >
>> > > > > > 1. Create a generic (possibly overloaded) trigger function,
>> > > > > > bundled with PostgreSQL, which sets a field to some value. For
>> > > > > > example, a timestamptz version might set the field to now().
>> > > > >
>> > > > > Having the pre defined triggers at hand could be useful, especially
>> > > > > for people not writing triggers so often to get used to it but I'm
>> > > > > really not happy with the idea of magic preprocessing.
>> > > >
>> > > > I have a generic version of this in pagila.
>> > >
>> > > Now that we have a specific file in core for generic triggers (right now
>> > > with a single one), how about adding this one to it?
>> >
>> > Any progress on this? TODO?
>>
>> I think this is a TODO, but not sure who is working on it or what needs to be
>> done. The generic version in pagila is perhaps not generic enough:
>>
>> CREATE FUNCTION last_updated() RETURNS trigger
>> AS $$
>> BEGIN
>> NEW.last_update = CURRENT_TIMESTAMP;
>> RETURN NEW;
>> END $$
>> LANGUAGE plpgsql;
>>
>> It requires you name your column last_update, which is what the naming
>> convention is in pagila, but might not work for everyone. Can someone work
>> with that and move forward? Or maybe give a more specific pointer to the
>> generic trigger stuff (I've not looked at it before)
>
> Well, I thought it was a good idea, but no one seems to want to do the
> work.

I'd like to see more options than that, which, it seems to me,
establishes a need for more design work.

Another perspective on temporality is to have a "transaction column"
which points (via foreign key) to a transaction table, where you would
use currval('transaction_sequence') as the value instead of
CURRENT_TIMESTAMP.

Thus...

create or replace function update_txid () returns trigger as $$
begin
if TG_OP = 'UPDATE' then
NEW.tx_id := currval('some-schema.tx_sequence');
return NEW;
else
raise exception 'tx update requested on non-update request - %', TG_OP;
end if;
return NEW;
end
$$ language plpgsql;

Thus, I'd encourage having the column as well as the kind of value
(timestamp vs sequence value) both being parameters for this.
--
http://linuxfinances.info/info/linuxdistributions.html
Calvin Trillin - "Health food makes me sick."

Christopher Browne wrote:
>On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Robert Treat wrote:
>>> On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
>>> CREATE FUNCTION last_updated() RETURNS trigger
>>> AS $$
>>> BEGIN
>>> NEW.last_update = CURRENT_TIMESTAMP;
>>> RETURN NEW;
>>> END $$
>>> LANGUAGE plpgsql;

>>> It requires you name your column last_update, which is what the naming
>>> convention is in pagila, but might not work for everyone. Can someone work
>>> with that and move forward? Or maybe give a more specific pointer to the
>>> generic trigger stuff (I've not looked at it before)

>> Well, I thought it was a good idea, but no one seems to want to do the
>> work.

>I'd like to see more options than that, which, it seems to me,
>establishes a need for more design work.

>Another perspective on temporality is to have a "transaction column"
>which points (via foreign key) to a transaction table, where you would
>use currval('transaction_sequence') as the value instead of
>CURRENT_TIMESTAMP.

I use the following:

CREATE OR REPLACE FUNCTION lastupdate() RETURNS TRIGGER AS
$$
BEGIN
IF OLD.lastupdate=NEW.lastupdate
THEN
NEW.lastupdate:=CURRENT_TIMESTAMP;
ELSIF OLD.lastupdate IS NULL OR NEW.lastupdate IS NULL
THEN
RAISE EXCEPTION 'Concurrent modification of table %',TG_ARGV[0];
END IF;
RETURN NEW;
END;$$ LANGUAGE PLPGSQL;

Which allows detection of concurrent updates on the same page (if the
lastupdate value is being fetched before the update-template is filled).
--
Sincerely,
Stephen R. van den Berg.
Auto repair rates: basic labor $40/hour; if you wait, $60; if you watch, $80;
if you ask questions, $100; if you help, $120; if you laugh, $140.

Christopher Browne wrote:
>>>
>>> I think this is a TODO, but not sure who is working on it or what needs to be
>>> done. The generic version in pagila is perhaps not generic enough:
>>>
>>> CREATE FUNCTION last_updated() RETURNS trigger
>>> AS $$
>>> BEGIN
>>> NEW.last_update = CURRENT_TIMESTAMP;
>>> RETURN NEW;
>>> END $$
>>> LANGUAGE plpgsql;
>>>
>>> It requires you name your column last_update, which is what the naming
>>> convention is in pagila, but might not work for everyone. Can someone work
>>> with that and move forward? Or maybe give a more specific pointer to the
>>> generic trigger stuff (I've not looked at it before)
>>>
>> Well, I thought it was a good idea, but no one seems to want to do the
>> work.
>>
>
> I'd like to see more options than that, which, it seems to me,
> establishes a need for more design work.
>
>
>

At the very least it should not have a hard-coded field name in it. You
should pass the field name to be set as a parameter in the trigger setup.

That's probably a lot more doable if the trigger is written in C, and in
any case I think any prepackaged triggers we provide should be written in C.

cheers

andrew

> At the very least it should not have a hard-coded field name in it. You
> should pass the field name to be set as a parameter in the trigger setup.
>
> That's probably a lot more doable if the trigger is written in C, and in any
> case I think any prepackaged triggers we provide should be written in C.

+1.

Although, I'm not sure there's much point in providing a prepackaged
trigger that does something you could accomplish just as well with a
single line of PL/pgsql, even if we do rewrite it in C.

...Robert