Thread: 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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@wildenhain.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@svana.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@wildenhain.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 generatedcolumn. A generated column is one whose values are determined by evaluation of a generation expression, a <valueexpression> whose declared type is by implication that of the column. A generation expression can reference basecolumns 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 generatedcolumn GC depends on each column that is referenced by a <column reference> in its generation expression, andeach such referenced column is a parametric column of GC. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@momjian.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@momjian.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@momjian.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 $$ beginif TG_OP = 'UPDATE' then NEW.tx_id := currval('some-schema.tx_sequence'); return NEW;else raise exception 'txupdate 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@momjian.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 $$
BEGINIF OLD.lastupdate=NEW.lastupdateTHEN NEW.lastupdate:=CURRENT_TIMESTAMP;ELSIF OLD.lastupdate IS NULL OR
NEW.lastupdateIS NULLTHEN 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