Upcoming events

[Bruce Momjian]

I have events in the next few weeks in New York City, Copenhagen, Paris,
and Atlanta. Check the News section on the web site for more
information. I will also be in Amsterdam February 2-3, though I have no
public events scheduled there.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joseph Shraibman]

Bruce Momjian wrote:

I have events in the next few weeks in New York City, Copenhagen, Paris,
and Atlanta. Check the News section on the web site for more
information. I will also be in Amsterdam February 2-3, though I have no
public events scheduled there.

You mean the events section, don't you?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruce Momjian]

Joseph Shraibman wrote:

Bruce Momjian wrote:

I have events in the next few weeks in New York City, Copenhagen, Paris,
and Atlanta. Check the News section on the web site for more
information. I will also be in Amsterdam February 2-3, though I have no
public events scheduled there.

You mean the events section, don't you?

Yes.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[NTPT]

is it possible to add column to database, that will automatically contain date+time (or likely Unix timestamp) when the row was touched/changed - ie by INSERT or UPDATE ?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Dustin Sallings]

On Jan 23, 2004, at 1:11, NTPT wrote:

is it possible to add column to database, that will automatically
contain date+time (or likely Unix timestamp) when the row was
touched/changed - ie by INSERT or UPDATE ?

Yes, a very simple trigger can do this.

--
SPY My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <du****@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Mike Mascari]

NTPT wrote:

is it possible to add column to database,
ALTER TABLE foo
ADD COLUMN mod_date TIMESTAMP;
that will automatically contain date+time (or likely Unix timestamp) when the row was touched/changed - ie by INSERT or UPDATE ?

CREATE FUNCTION touch() RETURNS trigger AS '
begin
NEW.mod_date = LOCALTIMESTAMP;
return NEW;
end;
' language 'plpgsql';

CREATE TRIGGER t_foo
BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE touch();

If you want timezone information, use TIMESTAMP WITH TIME ZONE and
CURRENTTIMESTAMP. These are transaction start times.

HTH,

Mike Mascari


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Mike Mascari]

Mike Mascari wrote:

CREATE FUNCTION touch() RETURNS trigger AS '
begin
NEW.mod_date = LOCALTIMESTAMP;
return NEW;
end;
' language 'plpgsql';

CREATE TRIGGER t_foo
BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE touch();

If you want timezone information, use TIMESTAMP WITH TIME ZONE and
CURRENTTIMESTAMP. These are transaction start times.

Should read: CURRENT_TIMESTAMP

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Chris Boget]

> > is it possible to add column to database, that will automatically

contain date+time (or likely Unix timestamp) when the row was
touched/changed - ie by INSERT or UPDATE ?

Yes, a very simple trigger can do this.

Wouldn't just setting the default value of the field to be NOW() accomplish
the same thing? Or can you not default the value of a timestamp field?

Chris
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Doug McNaught]

"Chris Boget" <ch***@wild.net> writes:

> is it possible to add column to database, that will automatically
> contain date+time (or likely Unix timestamp) when the row was
> touched/changed - ie by INSERT or UPDATE ?

Yes, a very simple trigger can do this.

Wouldn't just setting the default value of the field to be NOW() accomplish
the same thing? Or can you not default the value of a timestamp field?

Defaults only apply at INSERT time, not UPDATE.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Csaba Nagy]

On Fri, 2004-01-23 at 16:52, Chris Boget wrote:

is it possible to add column to database, that will automatically
contain date+time (or likely Unix timestamp) when the row was
touched/changed - ie by INSERT or UPDATE ? Yes, a very simple trigger can do this.

Wouldn't just setting the default value of the field to be NOW() accomplish
the same thing? Or can you not default the value of a timestamp field?

No, that would only set the timestamp on INSERT, but not on any UPDATE.
The OP wanted to timestamp all updates too.

Cheers,
Csaba.

Chris
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Chris Boget]

> >> > is it possible to add column to database, that will automatically

> contain date+time (or likely Unix timestamp) when the row was
> touched/changed - ie by INSERT or UPDATE ?
Yes, a very simple trigger can do this.

Wouldn't just setting the default value of the field to be NOW() accomplish
the same thing? Or can you not default the value of a timestamp field?

Defaults only apply at INSERT time, not UPDATE.

Interesting. Yet another item to tack onto my list of differences between
MySQL and Postgres.

Thanks!

Chris
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

On Friday 23 January 2004 15:52, Chris Boget wrote:

is it possible to add column to database, that will automatically
contain date+time (or likely Unix timestamp) when the row was
touched/changed - ie by INSERT or UPDATE ?

Yes, a very simple trigger can do this.

Wouldn't just setting the default value of the field to be NOW() accomplish
the same thing? Or can you not default the value of a timestamp field?

That will work for insert, but won't change when you update.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Holger Marzen]

Hi all,

in the German magazine "iX" from Feb 2004 I read an article about Open
Source databases. The part about PostgreSQL wasn't bad but I am not sure
if the author is right when he writes about crash revcovery. He writes
that PostgreSQL has no UNDO function that resets unfinished transactions
after a crash but only a REDO function that finishes completed
transactions.

I thought that PostgreSQL's crash recovery automatically rolls back
everything that's not committed.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Holger Marzen <ho****@marzen.de> writes:

in the German magazine "iX" from Feb 2004 I read an article about Open
Source databases. The part about PostgreSQL wasn't bad but I am not sure
if the author is right when he writes about crash revcovery. He writes
that PostgreSQL has no UNDO function that resets unfinished transactions
after a crash but only a REDO function that finishes completed
transactions. I thought that PostgreSQL's crash recovery automatically rolls back
everything that's not committed.

That is correct. It is also correct that we don't need an explicit UNDO
operation to make it happen --- the correct behavior falls out of MVCC
support automatically, and we leave it to a subsequent VACUUM to reclaim
any space that can be reclaimed.

If the author stated that the lack of UNDO caused us not to be
crash-correct, he's wrong, but he is correct that there's no UNDO code.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Eric B.Ridge]

On Jan 23, 2004, at 4:35 AM, Mike Mascari wrote:

that will automatically contain date+time (or likely Unix timestamp)
when the row was touched/changed - ie by INSERT or UPDATE ?

CREATE FUNCTION touch() RETURNS trigger AS

<snip> CREATE TRIGGER t_foo

<snip>

I was bored this evening and played around with the trigger approach
versus an update-able view via a rule (using PG 7.4).

View/Rule:
create sequence foo_seq;
create table foo(id int4 NOT NULL PRIMARY KEY default
nextval('foo_seq'), d timestamp default now());
insert into foo default values; -- (32k times)
create view foo_view as select * from foo;
create rule foo_view_update_rule as on update to foo_view do instead
update foo set id = NEW.id, d=now() where foo.id = NEW.id;
-- NOTE: should define INSERT and DELETE rules too

Trigger:
create sequence foo2_seq;
create table foo2(id int4 NOT NULL PRIMARY KEY default
nextval('foo2_seq'), d timestamp default now());
insert into foo2 default values; -- (32k times)
create function foo2_update() returns trugger as 'BEGIN NEW.d = now();
return NEW; END;' language 'plpgsql';
create trigger foo2_update_trigger before update on foo2 for each row
execute procedure foo2_update();
Next, I did some EXPLAIN ANALYZE-ing for updates against "foo_view" and
"foo2":
(I realize my queries are dumb, but this is was just a quick experiment)

explain analyze update foo_view set id = 1 where id = 1;
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------------
Nested Loop (cost=0.00..990.53 rows=26896 width=6) (actual
time=0.060..0.074 rows=1 loops=1)
-> Index Scan using idxfooid on foo (cost=0.00..3.88 rows=164
width=6) (actual time=0.031..0.036 rows=1 loops=1)
Index Cond: (id = 1)
-> Index Scan using idxfooid on foo (cost=0.00..3.88 rows=164
width=0) (actual time=0.007..0.015 rows=1 loops=1)
Index Cond: (id = 1)
Average runtime for 10 executions: 0.165ms

explain analyze update foo2 set id = 1 where id = 1;
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------
Index Scan using idxfoo2id on foo2 (cost=0.00..3.88 rows=164
width=14) (actual time=0.031..0.039 rows=1 loops=1)
Index Cond: (id = 1)
Average runtime for 10 executions: 0.328ms
So the view/rule trick is nearly twice as fast as using a trigger. The
down-side (if you really want to call it that) is you're "forced" to
use the view instead of the table for access to the data, and you're
forced to manually maintain the "do instead" part of the rules.

However, considering the seemingly near lack of overhead involved in
views (and apparently rules), combined with the extra layer of
abstraction views provide, this seems like a more efficient and
flexible approach. Plus, it still gives you the ability to use
triggers on the underlying table for more complicated tasks. A
real-world example could prove all this wrong, but it's really cool to
see a 2x performance improvement for something simple.

One thing I did notice however, is that if you have lots of rows, you
*really* need an index on the primary key column (which you get by
default) in order for the view/rule to win.

eric
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Chris Travers]

> Interesting. Yet another item to tack onto my list of differences between

MySQL and Postgres.

Does MySQL apply defaults to updates?

If so, I can only stare in amazement.... Something like "update customer
set address = '1 my road' where customer_id = '123' SHOULD NOT touch any
other tables unless one has specifically enabled such a tracking using a
trigger...

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Chris Travers]

I too have also noticed that the rule is a really cool way to write
lightweight triggers. They are also simpler to use, and often perform
better. You can also make them conditional which you cannot do with
triggers at the moment.

I think this timestamp concept is a perfect example of where a rule is
better. It doesn't have to be done on a view either.
For example:
CREATE TABLE mytable (
my_id SERIAL PRIMARY KEY,
last_updated TIMESTAMP);
CREATE RULE touch_row AS ON UPDATE TO mytable DO
(UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

I assume that if you have extremely complex business logic in your triggers,
triggers might be better because they are executed in a defined order. But
for something like this, I fail to see how it makes things easier rather
than harder.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Eric B.Ridge]

On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:

I think this timestamp concept is a perfect example of where a rule is
better. It doesn't have to be done on a view either.
No, it doesn't, but a rule on a table can't reference the target table
in the command definition. RULES are very much like C #define macros
-- they're placed in-line in the query plan. They're not functions,
they don't return values; they're essentially constants that transform
all query types against the target.

Your options when using a rule on a table are limited to either doing
nothing (basically ignoring the user command -- cool for making a table
read-only), doing something against a completely separate table, or
doing a custom command against a separate table in conjunction with the
user command.

For example:
CREATE TABLE mytable (
my_id SERIAL PRIMARY KEY,
last_updated TIMESTAMP);
CREATE RULE touch_row AS ON UPDATE TO mytable DO
(UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

Unless your version of postgres works differently (I'm using 7.4), your
example above does *not* work:

test=# CREATE TABLE mytable (
test(# my_id SERIAL PRIMARY KEY,
test(# last_updated TIMESTAMP);
NOTICE: CREATE TABLE will create implicit sequence "mytable_my_id_seq"
for "serial" column "mytable.my_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"mytable_pkey" for table "mytable"
CREATE TABLE
test=# CREATE RULE touch_row AS ON UPDATE TO mytable DO
test-# (UPDATE mytable SET last_updated = NOW() WHERE my_id =
NEW.my_id);
CREATE RULE
test=# insert into mytable default values;
INSERT 9950968 1
test=# update mytable set my_id = 1;
ERROR: infinite recursion detected in rules for relation "mytable"

I might have missed something in the docs (been awhile since I've read
'em), but I don't believe a rule command can reference its target.

eric
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

"Chris Travers" <ch***@travelamericas.com> writes:

Interesting. Yet another item to tack onto my list of differences between
MySQL and Postgres.

Does MySQL apply defaults to updates?

Not quite. AIUI MySQL has a "magic timestamp" feature where the first
TIMESTAMP column in a table will be auto-stamped on insert and update
whether you like it or not. That's probably what the OP was
expecting.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Eric B.Ridge" <eb*@tcdi.com> writes:

[ update timestamp via a rule ]
explain analyze update foo_view set id = 1 where id = 1;
Average runtime for 10 executions: 0.165ms
[ update timestamp via a trigger ]
explain analyze update foo2 set id = 1 where id = 1;
Average runtime for 10 executions: 0.328ms

This surprises me. There's a moderate amount of overhead involved in
a plpgsql trigger, but I'd not have thought it would swamp the added
inefficiencies involved in a rule. Notice that you're getting a double
indexscan in the rule case --- that takes more time to plan, and more
time to execute (observe the nearly double actual time for the top level
plan node).

What were you averaging here --- just the "total runtime" reported by
EXPLAIN ANALYZE? It would be interesting to factor in the planning time
too. Could you retry this and measure the total elapsed time? (psql's
\timing command will help.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"Eric B.Ridge" <eb*@tcdi.com> writes:

On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:

CREATE RULE touch_row AS ON UPDATE TO mytable DO
(UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

[ ... but that produces ]
test=# update mytable set my_id = 1;
ERROR: infinite recursion detected in rules for relation "mytable" I might have missed something in the docs (been awhile since I've read
'em), but I don't believe a rule command can reference its target.

The restriction is not that: the restriction is that you can't have an
infinite recursion in your rules. The above is infinitely recursive
because it says that for any UPDATE on mytable, you should also do an
UPDATE on mytable ... but then for that UPDATE you also need to do
another UPDATE on mytable ... etc. The bodies of rules are not exempt
from rule expansion.

It might be interesting to change that definition, so that a rule like
the above could be written that wouldn't recursively trigger itself.
This would need a lot of careful thought though. In most cases you *do*
want rule bodies to be rule-expanded.

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Eric B.Ridge]

On Jan 24, 2004, at 12:18 PM, Tom Lane wrote:

This surprises me. There's a moderate amount of overhead involved in
a plpgsql trigger, but I'd not have thought it would swamp the added
inefficiencies involved in a rule. Notice that you're getting a double
indexscan in the rule case --- that takes more time to plan, and more
time to execute (observe the nearly double actual time for the top
level
plan node).

What were you averaging here --- just the "total runtime" reported by
EXPLAIN ANALYZE?
yes.
It would be interesting to factor in the planning time
too. Could you retry this and measure the total elapsed time? (psql's
\timing command will help.)

\timing is cool! never knew about it until just now.

test=# \timing
Timing is on.
test=# update foo_view set id = 1 where id = 1;
For 10 executions, the average is about 1.487ms

test=# update foo2 set id = 1 where id = 1;
For 10 executions, the average is about 1.420ms

so yeah, yer right, the view/rule is a bit slower.

I'm going to start using \timing for here on out...

<short pause>

Okay, so now I created two prepared statements:
prepare foo_view_statement (int, int) as update foo_view set id=$1
where id = $2;
prepare foo2_statement (int, int) as update foo2 set id=$1 where id =
$2;

execute foo_view_statement(1, 1);
average timing: 1.137

execute foo2_statement(1, 1);
average timing: 1.359;

So it seems if the plan is already made, the update against the rule is
actually a tad faster. I don't know if the difference in speed is
enough to convince one (myself included) to start using prepared
statements, but it's another data point.

But still, a real-world example might prove all of this wrong.

eric

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Eric Ridge]

On Jan 24, 2004, at 2:34 PM, Tom Lane wrote:

The restriction is not that: the restriction is that you can't have an
infinite recursion in your rules. The above is infinitely recursive
because it says that for any UPDATE on mytable, you should also do an
UPDATE on mytable ... but then for that UPDATE you also need to do
another UPDATE on mytable ... etc. The bodies of rules are not exempt
from rule expansion.
Understood. Even after 12 hours of sleep (I love Saturdays!), I still
can't see how an update rule wouldn't cause infinite recursion if it
tried to update its target.
It might be interesting to change that definition, so that a rule like
the above could be written that wouldn't recursively trigger itself.
This would need a lot of careful thought though. In most cases you
*do*
want rule bodies to be rule-expanded.
I sure want rule bodies to be rule-expaned! Rule's are super cool and
extremely flexible as they are.
A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

This thought ran through my head last night. Something like:

CREATE TABLE foo (
id int4 DEFAULT nextval('foo_seq'),
d timestamp DEFAULT now() ON UPDATE now()
);

But it seems that if the user explicitly provided a value for 'd',
you'd want to use that over the computed value.

Whatever the details, it would be a very useful feature to have.

eric
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Eric Ridge <eb*@tcdi.com> writes:

On Jan 24, 2004, at 2:34 PM, Tom Lane wrote:

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default".

This thought ran through my head last night. Something like: CREATE TABLE foo (
id int4 DEFAULT nextval('foo_seq'),
d timestamp DEFAULT now() ON UPDATE now()
); But it seems that if the user explicitly provided a value for 'd',
you'd want to use that over the computed value.

True. So if your goal is to force the timestamp column to be the
correct value even when the user tries to set it to something else,
you'd still have to use a trigger or rule.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Eric Ridge]

On Jan 24, 2004, at 3:58 PM, Tom Lane wrote:

True. So if your goal is to force the timestamp column to be the
correct value even when the user tries to set it to something else,
you'd still have to use a trigger or rule.

Maybe the rule is that the computed value is always used, unless:
UPDATE foo OVERRIDE DEFAULTS set d=yesterday();

*shrug*. At least with something like the above, the user makes his
intention explicit. Perhaps if user doesn't specify OVERRIDE DEFAULTS,
postgres outputs a warning:
WARNING: value for column 'd' ignored.
HINT: Use UPDATE ... OVERRIDE DEFAULTS to override ON UPDATE DEFAULT
values

and of course, this would be handy too:
UPDATE foo OVERRIDE DEFAULTS set d=DEFAULT;

eric
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Chris Travers]

Interestingly enough, a where clause is insufficient to free yourself from
the infinite recursion. For example

CREATE RULE ON mytable where old.mytimestamp != now() do update mytable set
timestamp = now() still infinitely recurses even though on the second
attempt one would expect the where clause to drop the row out on the second
recursion. I can only assume that this would create a lot of work for the
planner (determining if the recursion is real or just possible) and too much
work to do at the moment. Also one has the question of how many times a
rule should be allowed to recurse before considering it infinite.

Best Wishes,
Chris Travers

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Eric B.Ridge" <eb*@tcdi.com>
Cc: "Chris Travers" <ch***@travelamericas.com>; "Jan Wieck"
<Ja******@Yahoo.com>; "NTPT" <nt**@centrum.cz>; "Mike Mascari"
<ma*****@mascari.com>; "PostgreSQL-general" <pg***********@postgresql.org>
Sent: Sunday, January 25, 2004 2:34 AM
Subject: Re: [GENERAL] Touch row ?

"Eric B.Ridge" <eb*@tcdi.com> writes:

On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:

CREATE RULE touch_row AS ON UPDATE TO mytable DO
(UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

[ ... but that produces ]
test=# update mytable set my_id = 1;
ERROR: infinite recursion detected in rules for relation "mytable"

I might have missed something in the docs (been awhile since I've read
'em), but I don't believe a rule command can reference its target.

The restriction is not that: the restriction is that you can't have an
infinite recursion in your rules. The above is infinitely recursive
because it says that for any UPDATE on mytable, you should also do an
UPDATE on mytable ... but then for that UPDATE you also need to do
another UPDATE on mytable ... etc. The bodies of rules are not exempt
from rule expansion.

It might be interesting to change that definition, so that a rule like
the above could be written that wouldn't recursively trigger itself.
This would need a lot of careful thought though. In most cases you *do*
want rule bodies to be rule-expanded.

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Jan Wieck]

Chris Travers wrote:

Interestingly enough, a where clause is insufficient to free yourself from
the infinite recursion. For example

CREATE RULE ON mytable where old.mytimestamp != now() do update mytable set
timestamp = now() still infinitely recurses even though on the second
attempt one would expect the where clause to drop the row out on the second
recursion. I can only assume that this would create a lot of work for the
planner (determining if the recursion is real or just possible) and too much
work to do at the moment. Also one has the question of how many times a
rule should be allowed to recurse before considering it infinite.
One would not expect that if one would know how the rewriter works. It
does not evaluate the where clause at the time of rewriting (and how
could it ... there are no rows at hand at the time of rewriting ...
there is not even an execution plan at that time).
Jan

Best Wishes,
Chris Travers

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Eric B.Ridge" <eb*@tcdi.com>
Cc: "Chris Travers" <ch***@travelamericas.com>; "Jan Wieck"
<Ja******@Yahoo.com>; "NTPT" <nt**@centrum.cz>; "Mike Mascari"
<ma*****@mascari.com>; "PostgreSQL-general" <pg***********@postgresql.org>
Sent: Sunday, January 25, 2004 2:34 AM
Subject: Re: [GENERAL] Touch row ?

"Eric B.Ridge" <eb*@tcdi.com> writes:

> On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:
>> CREATE RULE touch_row AS ON UPDATE TO mytable DO
>> (UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

> [ ... but that produces ]
> test=# update mytable set my_id = 1;
> ERROR: infinite recursion detected in rules for relation "mytable"

> I might have missed something in the docs (been awhile since I've read
> 'em), but I don't believe a rule command can reference its target.

The restriction is not that: the restriction is that you can't have an
infinite recursion in your rules. The above is infinitely recursive
because it says that for any UPDATE on mytable, you should also do an
UPDATE on mytable ... but then for that UPDATE you also need to do
another UPDATE on mytable ... etc. The bodies of rules are not exempt
from rule expansion.

It might be interesting to change that definition, so that a rule like
the above could be written that wouldn't recursively trigger itself.
This would need a lot of careful thought though. In most cases you *do*
want rule bodies to be rule-expanded.

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

regards, tom lane

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[NTPT]

And why in POSTGRESQL there is not just an appropriate DATATYPE for creating a column type touch_timestamp in table (I suggest touch_timestampto be the same in one transaction... ) ?
I thing that it might be useful (and add no overhead) for lot of tasks .......

----- Puvodní zpráva -----
Od: "Chris Travers" <ch***@travelamericas.com>
Komu: "Chris Boget" <ch***@wild.net>; "Doug McNaught" <do**@mcnaught.org>
Kopie: "PostgreSQL-general" <pg***********@postgresql.org>
Odesláno: 24. ledna 2004 4:27
Predmet: Re: [GENERAL] Touch row ?

Interesting. Yet another item to tack onto my list of differences between
MySQL and Postgres.

Does MySQL apply defaults to updates?

If so, I can only stare in amazement.... Something like "update customer
set address = '1 my road' where customer_id = '123' SHOULD NOT touch any
other tables unless one has specifically enabled such a tracking using a
trigger...

Best Wishes,
Chris Travers


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Martijn van Oosterhout]

On Sun, Jan 25, 2004 at 02:09:02AM +0100, NTPT wrote:

And why in POSTGRESQL there is not just an appropriate DATATYPE for creating a column type touch_timestamp in table (I suggest touch_timestamp to be the same in one transaction... ) ?
I thing that it might be useful (and add no overhead) for lot of tasks .......
It would have overhead but why not just use a trigger which is the supported
and far more flexible way of doing this?

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAFGyTY5Twig3Ge+YRAr6vAJ9OURXp/pIxfMTzSX6DNbvDQHMkOQCg2/wV
9EBnNSbwWQefB6Zj928gsZI=
=Wfrx
-----END PGP SIGNATURE-----

[Brendan Jurd]

Tom Lane wrote:
<snip>

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

regards, tom lane
</snip>

I think the idea of the update default has interesting possbilities.
Perhaps what is needed is two classes of defaults.

1. "implicit default" -- any updates to a tuple either not specifying a
value for the target column at all, or specifying DEFAULT will set that
column to the default. This would be useful for our "touch row" or
"last modified" scenario, as discussed in the previous thread.

2. "explicit default" -- this default can only be actioned if requested
deliberately by the user. e.g. UPDATE foo SET a='x', b='y', c=DEFAULT;

A slightly different approach would be to not have explicit update
defaults at all, and instead make statements like UPDATE foo SET
c=DEFAULT actually set c to the "insert default" value. I suppose this
decision hinges on whether there are a significant set of cases where
you would want your explicit update default to be different from your
insert default.

I would tentatively suggest that (2) be the default for update defaults,
since the implicit version could generate some unexpected, and possibly
data-destructive, results if not used carefully. My idea of the column
definition syntax would be something like:

1. t timestamp NOT NULL DEFAULT NOW() UPDEF NOW() IMPLICIT;
2. c int NOT NULL UPDEF 100;

Cheers

BJ
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Brendan Jurd <bl*****@blakjak.sytes.net> writes:

I think the idea of the update default has interesting possbilities.
Perhaps what is needed is two classes of defaults. 1. "implicit default" -- any updates to a tuple either not specifying a
value for the target column at all, or specifying DEFAULT will set that
column to the default. This would be useful for our "touch row" or
"last modified" scenario, as discussed in the previous thread. 2. "explicit default" -- this default can only be actioned if requested
deliberately by the user. e.g. UPDATE foo SET a='x', b='y', c=DEFAULT;
How is #2 different from your "slightly different approach"?
A slightly different approach would be to not have explicit update
defaults at all, and instead make statements like UPDATE foo SET
c=DEFAULT actually set c to the "insert default" value.

That exists already (and is SQL-standard), but I'm not convinced that
it does the job conveniently. In the example of a time-of-last-change
column, you do not want the user to have to remember to write
SET modtime = DEFAULT. In fact, you really don't want ordinary users to
be able to set the column at all. If we had per-column privilege
controls (which the spec says we should, and I think we will eventually)
then disallowing write of the modtime column to ordinary users, along
with an update default expression, would get the job done very nicely.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend