SEO

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIIqFeIB7bNG8LQkwRAlAeAJ4qc/z9RYx7w4puVWBFdmuY0gbuTACggwUH
C+5HeH8/wc9aYoaLK5oaeyc=
=j4XC
-----END PGP SIGNATURE-----

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

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

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

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

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers