Upcoming events

[Chris Travers]

Re: Touch row ?

> Interesting. Yet another item to tack onto my list of differences between[color=blue]
> MySQL and Postgres.[/color]

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]

Re: Touch row ?

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]

Re: Touch row ?

On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:[color=blue]
> 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.[/color]

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.

[color=blue]
> 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);[/color]

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_pk ey" 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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

Re: Touch row ?

"Chris Travers" <chris@travelam ericas.com> writes:
[color=blue][color=green]
>> Interesting. Yet another item to tack onto my list of differences between
>> MySQL and Postgres.[/color]
>
> Does MySQL apply defaults to updates?[/color]

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]

Re: Touch row ?

"Eric B.Ridge" <ebr@tcdi.com > writes:[color=blue]
> [ 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[/color]

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 majordomo@postg resql.org

[Tom Lane]

Re: Touch row ?

"Eric B.Ridge" <ebr@tcdi.com > writes:[color=blue]
> On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:[color=green]
>> CREATE RULE touch_row AS ON UPDATE TO mytable DO
>> (UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);[/color][/color]
[color=blue]
> [ ... but that produces ]
> test=# update mytable set my_id = 1;
> ERROR: infinite recursion detected in rules for relation "mytable"[/color]
[color=blue]
> 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.[/color]

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]

Re: Touch row ?

On Jan 24, 2004, at 12:18 PM, Tom Lane wrote:[color=blue]
> 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?[/color]

yes.
[color=blue]
> 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.)[/color]

\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_statem ent (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_statem ent(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]

Re: Touch row ?

On Jan 24, 2004, at 2:34 PM, Tom Lane wrote:[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

I sure want rule bodies to be rule-expaned! Rule's are super cool and
extremely flexible as they are.
[color=blue]
> 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.[/color]

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

CREATE TABLE foo (
id int4 DEFAULT nextval('foo_se q'),
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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: Touch row ?

Eric Ridge <ebr@tcdi.com > writes:[color=blue]
> On Jan 24, 2004, at 2:34 PM, Tom Lane wrote:[color=green]
>> 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".[/color][/color]
[color=blue]
> This thought ran through my head last night. Something like:[/color]
[color=blue]
> CREATE TABLE foo (
> id int4 DEFAULT nextval('foo_se q'),
> d timestamp DEFAULT now() ON UPDATE now()
> );[/color]
[color=blue]
> But it seems that if the user explicitly provided a value for 'd',
> you'd want to use that over the computed value.[/color]

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]

Re: Touch row ?

On Jan 24, 2004, at 3:58 PM, Tom Lane wrote:
[color=blue]
> 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.[/color]

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]

Re: Touch row ?

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" <tgl@sss.pgh.pa .us>
To: "Eric B.Ridge" <ebr@tcdi.com >
Cc: "Chris Travers" <chris@travelam ericas.com>; "Jan Wieck"
<JanWieck@Yahoo .com>; "NTPT" <ntpt@centrum.c z>; "Mike Mascari"
<mascarm@mascar i.com>; "PostgreSQL-general" <pgsql-general@postgre sql.org>
Sent: Sunday, January 25, 2004 2:34 AM
Subject: Re: [GENERAL] Touch row ?

[color=blue]
> "Eric B.Ridge" <ebr@tcdi.com > writes:[color=green]
> > On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:[color=darkred]
> >> CREATE RULE touch_row AS ON UPDATE TO mytable DO
> >> (UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);[/color][/color]
>[color=green]
> > [ ... but that produces ]
> > test=# update mytable set my_id = 1;
> > ERROR: infinite recursion detected in rules for relation "mytable"[/color]
>[color=green]
> > 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.[/color]
>
> 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
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Jan Wieck]

Re: Touch row ?

Chris Travers wrote:
[color=blue]
> 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.[/color]

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
[color=blue]
>
> Best Wishes,
> Chris Travers
>
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa .us>
> To: "Eric B.Ridge" <ebr@tcdi.com >
> Cc: "Chris Travers" <chris@travelam ericas.com>; "Jan Wieck"
> <JanWieck@Yahoo .com>; "NTPT" <ntpt@centrum.c z>; "Mike Mascari"
> <mascarm@mascar i.com>; "PostgreSQL-general" <pgsql-general@postgre sql.org>
> Sent: Sunday, January 25, 2004 2:34 AM
> Subject: Re: [GENERAL] Touch row ?
>
>[color=green]
>> "Eric B.Ridge" <ebr@tcdi.com > writes:[color=darkred]
>> > 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);[/color]
>>[color=darkred]
>> > [ ... but that produces ]
>> > test=# update mytable set my_id = 1;
>> > ERROR: infinite recursion detected in rules for relation "mytable"[/color]
>>[color=darkred]
>> > 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.[/color]
>>
>> 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
>>
>>[/color][/color]


--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== JanWieck@Yahoo. com #


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

[NTPT]

Re: Touch row ?

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



----- Puvodní zpráva -----
Od: "Chris Travers" <chris@travelam ericas.com>
Komu: "Chris Boget" <chris@wild.net >; "Doug McNaught" <doug@mcnaught. org>
Kopie: "PostgreSQL-general" <pgsql-general@postgre sql.org>
Odesláno: 24. ledna 2004 4:27
Predmet: Re: [GENERAL] Touch row ?

[color=blue][color=green]
> > Interesting. Yet another item to tack onto my list of differences between
> > MySQL and Postgres.[/color]
>
> 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
>
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Martijn van Oosterhout]

Re: Touch row ?

On Sun, Jan 25, 2004 at 02:09:02AM +0100, NTPT wrote:[color=blue]
> 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 .......[/color]

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 <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> (... 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[/color]

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

iD8DBQFAFGyTY5T wig3Ge+YRAr6vAJ 9OURXp/pIxfMTzSX6DNbvD QHMkOQCg2/wV
9EBnNSbwWQefB6Z j928gsZI=
=Wfrx
-----END PGP SIGNATURE-----

[Brendan Jurd]

Update Default (was: Touch row ?)



Tom Lane wrote:
<snip>
[color=blue]
>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
>
>
>[/color]
</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
[color=blue]
>---------------------------(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
>
>[/color]


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