Re: [HACKERS] Auto-updated fields
Christopher Browne wrote:
On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian br...@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
$$
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian br...@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 $$
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
On Thu, 2008-05-08 at 00:41 -0400, Tom Lane wrote: Tino Wildenhain [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Auto-updated fields
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
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. smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Auto-updated fields
Tino Wildenhain [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
