Upcoming events

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 the row 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 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

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.

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

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.

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