Thread: Upcoming events
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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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?
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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
is it possible to add column to database, that will automatically contain date+time (or likely Unix timestamp) when therow was touched/changed - ie by INSERT or UPDATE ?
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 <dustin@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. ____________
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 orUPDATE ? > > 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
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
> > 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
"Chris Boget" <chris@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
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
> >> > 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
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
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.
Holger Marzen <holger@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
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
> 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
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
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
"Chris Travers" <chris@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
"Eric B.Ridge" <ebr@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
"Eric B.Ridge" <ebr@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
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
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
Eric Ridge <ebr@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
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
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@travelamericas.com>; "Jan Wieck" <JanWieck@Yahoo.com>; "NTPT" <ntpt@centrum.cz>; "Mike Mascari" <mascarm@mascari.com>; "PostgreSQL-general" <pgsql-general@postgresql.org> Sent: Sunday, January 25, 2004 2:34 AM Subject: Re: [GENERAL] Touch row ? > "Eric B.Ridge" <ebr@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 > >
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" <tgl@sss.pgh.pa.us> > To: "Eric B.Ridge" <ebr@tcdi.com> > Cc: "Chris Travers" <chris@travelamericas.com>; "Jan Wieck" > <JanWieck@Yahoo.com>; "NTPT" <ntpt@centrum.cz>; "Mike Mascari" > <mascarm@mascari.com>; "PostgreSQL-general" <pgsql-general@postgresql.org> > Sent: Sunday, January 25, 2004 2:34 AM > Subject: Re: [GENERAL] Touch row ? > > >> "Eric B.Ridge" <ebr@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. # #================================================== JanWieck@Yahoo.com #
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@travelamericas.com> Komu: "Chris Boget" <chris@wild.net>; "Doug McNaught" <doug@mcnaught.org> Kopie: "PostgreSQL-general" <pgsql-general@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 > >
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 <kleptog@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
Attachment
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 > >
Brendan Jurd <blakjak@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
<br /> Tom Lane wrote:<br /><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap="">Brendan Jurd <aclass="moz-txt-link-rfc2396E" href="mailto:blakjak@blakjak.sytes.net"><blakjak@blakjak.sytes.net></a> writes: </pre><blockquotetype="cite"><pre wrap="">I think the idea of the update default has interesting possbilities. Perhaps what is needed is two classes of defaults. </pre></blockquote><pre wrap=""> </pre><blockquote type="cite"><prewrap="">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. </pre></blockquote><pre wrap=""> </pre><blockquote type="cite"><prewrap="">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; </pre></blockquote><pre wrap=""> How is #2 different from your "slightly different approach"? </pre></blockquote> Ah, sorry if this was unclear. #2 would mean that the column definition has an explicit "update default",which could potentially be different from the "insert default", if that was desired. The "slightly different approach"would mean that explicit SET to DEFAULT instructions would just use the insert default. I was just unsure whetherit would be useful in practice to have separate values for the explicit update default and the insert default.<br/><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap=""></pre><blockquote type="cite"><prewrap="">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. </pre></blockquote><pre wrap=""> 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. </pre></blockquote> Agreed, UPDATE SET x = DEFAULT isn't a good solution for the last modtime column. But that doesn't mean it wouldn't be useful in other situations.<br /><br /><blockquote cite="mid5288.1075256131@sss.pgh.pa.us"type="cite"><pre wrap="">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. </pre></blockquote> Sounds good -- column based privileges would have a lot of handy applications.<br /><br /> Cheers<br/><br /> BJ<br /><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap=""> regards,tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend </pre></blockquote>
Hi,
Any chance of extending SET SESSION AUTHORIZATION to have a form which:
a. Takes a password
b. Can be executed by a non-privileged user -- when a password is
supplied.
How can I request this be added?
Any guess of how hard it would be? (Making arrays accept NULL values -- a
previous request -- was too involved for an entrance into the Postgres code
base for me as a relative newbie, but if this is a localized function I'd be
happy to have a first crack at it...)
Thanks,
== Ezra E.