pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedautoupdating mtime column
10+ messages / 4 participants
[nested] [flat]
* autoupdating mtime column
@ 2006-08-04 12:16 David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Garamond @ 2006-08-04 12:16 UTC (permalink / raw)
To: pgsql-sql
Dear all,
Please see SQL below. I'm already satisfied with everything except I wish in
#4, mtime got automatically updated to NOW() if not explicitly SET in UPDATE
statement. Is there a way to make the mtime column behave more like I
wanted? Thanks in advance.
create table t1 (
id int primary key,
t text,
ctime timestamp with time zone,
mtime timestamp with time zone
);
create or replace function update_times() returns trigger as $$
begin
if tg_op='INSERT' then
if NEW.ctime is null then NEW.ctime = NOW(); end if;
if NEW.mtime is null then NEW.mtime = NOW(); end if;
elsif tg_op='UPDATE' then
if NEW.ctime is null then NEW.ctime = OLD.ctime; end if;
if NEW.mtime is null then NEW.mtime = NOW(); end if;
end if;
return NEW;
end;
$$ language plpgsql;
create trigger update_times before update or insert on t1
for each row execute procedure trig1();
-- #1. mtime & ctime set to NOW()
insert into t1 values (1,'text1',null,null);
-- #2. mtime & ctime set to '2001-01-01'
insert into t1 values (2,'text2','2000-01-01','2000-01-01');
-- #3. mtime and ctime set to '2006-06-06'
update t1 set t='new text1',ctime='2006-6-6',mtime='2006-6-6' where id=1;
-- #4. mtime and ctime unchanged
update t1 set t='new text1' where id=1;
-- #5. mtime automatically updated to NOW()
update t1 set t='new text1',mtime=null where id=1;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
@ 2006-08-04 12:49 ` Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 16:05 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 10+ messages in thread
From: Richard Huxton @ 2006-08-04 12:49 UTC (permalink / raw)
To: David Garamond <davidgaramond@gmail.com>; +Cc: pgsql-sql
David Garamond wrote:
> Dear all,
>
> Please see SQL below. I'm already satisfied with everything except I
> wish in
> #4, mtime got automatically updated to NOW() if not explicitly SET in
> UPDATE
> statement. Is there a way to make the mtime column behave more like I
> wanted? Thanks in advance.
Just check for OLD.mtime = NEW.mtime, or am I missing something here?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
@ 2006-08-04 15:11 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 15:39 ` Re: autoupdating mtime column Rodrigo De León <rdeleonp@gmail.com>
2006-08-04 16:10 ` Re: autoupdating mtime column Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 10+ messages in thread
From: David Garamond @ 2006-08-04 15:11 UTC (permalink / raw)
To: pgsql-sql
On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
>
> David Garamond wrote:
> > Dear all,
> >
> > Please see SQL below. I'm already satisfied with everything except I
> > wish in
> > #4, mtime got automatically updated to NOW() if not explicitly SET in
> > UPDATE
> > statement. Is there a way to make the mtime column behave more like I
> > wanted? Thanks in advance.
>
> Just check for OLD.mtime = NEW.mtime, or am I missing something here?
>
How do I differentiate between:
UPDATE t SET mtime=mtime ...;
in which mtime is specifically set and should not change, and
UPDATE t SET foo=bar ...;
in which mtime is not mentioned and should be updated automagically to
NOW().
--
dave
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
@ 2006-08-04 15:39 ` Rodrigo De León <rdeleonp@gmail.com>
2006-08-04 15:51 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rodrigo De León @ 2006-08-04 15:39 UTC (permalink / raw)
To: pgsql-sql
On 8/4/06, David Garamond <davidgaramond@gmail.com> wrote:
> On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
> > David Garamond wrote:
> > > Dear all,
> > >
> > > Please see SQL below. I'm already satisfied with everything except I
> > > wish in
> > > #4, mtime got automatically updated to NOW() if not explicitly SET in
> > > UPDATE
> > > statement. Is there a way to make the mtime column behave more like I
> > > wanted? Thanks in advance.
> >
> > Just check for OLD.mtime = NEW.mtime, or am I missing something here?
> >
>
>
> How do I differentiate between:
>
> UPDATE t SET mtime=mtime ...;
>
> in which mtime is specifically set and should not change, and
>
> UPDATE t SET foo=bar ...;
>
> in which mtime is not mentioned and should be updated automagically to
> NOW().
How about:
create or replace function
update_times()
returns trigger as $$
begin
if TG_OP='INSERT' then
new.ctime = coalesce(new.ctime,now());
new.mtime = coalesce(new.mtime,now());
elsif TG_OP='UPDATE' then
new.ctime = old.ctime;
new.mtime = now();
end if;
return new;
end;
$$ language plpgsql;
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 15:39 ` Re: autoupdating mtime column Rodrigo De León <rdeleonp@gmail.com>
@ 2006-08-04 15:51 ` David Garamond <davidgaramond@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: David Garamond @ 2006-08-04 15:51 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql
On 8/4/06, Rodrigo De León <rdeleonp@gmail.com> wrote:
>
> How about:
>
> create or replace function
> update_times()
> returns trigger as $$
> begin
> if TG_OP='INSERT' then
> new.ctime = coalesce(new.ctime,now());
> new.mtime = coalesce(new.mtime,now());
> elsif TG_OP='UPDATE' then
> new.ctime = old.ctime;
> new.mtime = now();
> end if;
> return new;
> end;
> $$ language plpgsql;
>
But that would disallow setting mtime and ctime to arbitrary values, which I
want to permit. But ctime and mtime should be set to current time if not
specified in INSERT, and mtime to current time if not specified in UPDATE. I
guess what I want is the MySQL's TIMESTAMP autoupdate behaviour, whichI
can't seem to be able to emulate in Postgres, because there's no information
given about which columns are specified in the SET clause, only the NEW and
OLD records.
--
dave
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
@ 2006-08-04 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-04 17:36 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-08-04 16:10 UTC (permalink / raw)
To: David Garamond <davidgaramond@gmail.com>; +Cc: pgsql-sql
"David Garamond" <davidgaramond@gmail.com> writes:
> On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
>> Just check for OLD.mtime = NEW.mtime, or am I missing something here?
> How do I differentiate between:
> UPDATE t SET mtime=mtime ...;
> in which mtime is specifically set and should not change,
You don't. A trigger has no way to know the history of the row it's
looking at --- consider the possibility that it was already modified
by earlier triggers.
If you are really intent on having a way to suppress the mtime update
you could dedicate an additional field to the purpose, eg
UPDATE t SET foo=..., bar=..., keepmtime = true ...
and in the trigger something like
if new.keepmtime then
new.keepmtime = false;
else
new.mtime = now();
As long as nothing else ever touches keepmtime this would work.
Personally I'm dubious that it's worth the trouble --- do you
have a real use-case for suppressing mtime updates?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 16:10 ` Re: autoupdating mtime column Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-04 17:36 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 18:18 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Garamond @ 2006-08-04 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 8/4/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> If you are really intent on having a way to suppress the mtime update
> you could dedicate an additional field to the purpose, eg
>
> UPDATE t SET foo=..., bar=..., keepmtime = true ...
>
> and in the trigger something like
>
> if new.keepmtime then
> new.keepmtime = false;
> else
> new.mtime = now();
>
> As long as nothing else ever touches keepmtime this would work.
> Personally I'm dubious that it's worth the trouble
Yeah, it's too expensive an overhead just for the sake of a slightly shorter
UPDATE statement.
--- do you
> have a real use-case for suppressing mtime updates?
>
Syncing tables between databases (a la "rsync --times"). Btw, I'm
considering temporarily disabling the update_times() trigger when sync-ing.
Thanks,
--
dave
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 16:10 ` Re: autoupdating mtime column Tom Lane <tgl@sss.pgh.pa.us>
2006-08-04 17:36 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
@ 2006-08-04 18:18 ` Richard Huxton <dev@archonet.com>
2006-08-05 01:27 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2006-08-04 18:18 UTC (permalink / raw)
To: David Garamond <davidgaramond@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
David Garamond wrote:
> On 8/4/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> --- do you
>> have a real use-case for suppressing mtime updates?
>>
>
> Syncing tables between databases (a la "rsync --times"). Btw, I'm
> considering temporarily disabling the update_times() trigger when sync-ing.
I'd consider running the sync as a different (privileged) user and
checking the current user in the trigger. Ordinary users always get
now(), privileged users always get what they provide (and they are
forced to provide some value). Does what you want and adds a safety
catch too.
Alternatively, you could do something similar with views.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 16:10 ` Re: autoupdating mtime column Tom Lane <tgl@sss.pgh.pa.us>
2006-08-04 17:36 ` Re: autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 18:18 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
@ 2006-08-05 01:27 ` David Garamond <davidgaramond@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: David Garamond @ 2006-08-05 01:27 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On 8/5/06, Richard Huxton <dev@archonet.com> wrote:
>
> >> have a real use-case for suppressing mtime updates?
> > Syncing tables between databases (a la "rsync --times"). Btw, I'm
> > considering temporarily disabling the update_times() trigger when
> sync-ing.
>
> I'd consider running the sync as a different (privileged) user and
> checking the current user in the trigger. Ordinary users always get
> now(), privileged users always get what they provide (and they are
> forced to provide some value). Does what you want and adds a safety
> catch too.
>
> Alternatively, you could do something similar with views.
>
That's a nice idea indeed. Thanks!
--
dave
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: autoupdating mtime column
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Re: autoupdating mtime column Richard Huxton <dev@archonet.com>
@ 2006-08-04 16:05 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2006-08-04 16:05 UTC (permalink / raw)
To: David Garamond <davidgaramond@gmail.com>; +Cc: pgsql-sql
David Garamond wrote:
> On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
>> David Garamond wrote:
>> > Dear all,
>> >
>> > Please see SQL below. I'm already satisfied with everything except I
>> > wish in
>> > #4, mtime got automatically updated to NOW() if not explicitly SET in
>> > UPDATE
>> > statement. Is there a way to make the mtime column behave more like I
>> > wanted? Thanks in advance.
>>
>> Just check for OLD.mtime = NEW.mtime, or am I missing something here?
>
> How do I differentiate between:
>
> UPDATE t SET mtime=mtime ...;
>
> in which mtime is specifically set and should not change, and
>
> UPDATE t SET foo=bar ...;
>
> in which mtime is not mentioned and should be updated automagically to
> NOW().
You can't. The trigger knows nothing about the original statement, just
the old and new tuple values.
You could use SET foo=DEFAULT, but that's neither more or less
convenient than now() in my eyes.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-08-05 01:27 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-04 12:16 autoupdating mtime column David Garamond <davidgaramond@gmail.com>
2006-08-04 12:49 ` Richard Huxton <dev@archonet.com>
2006-08-04 15:11 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 15:39 ` Rodrigo De León <rdeleonp@gmail.com>
2006-08-04 15:51 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-04 17:36 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 18:18 ` Richard Huxton <dev@archonet.com>
2006-08-05 01:27 ` David Garamond <davidgaramond@gmail.com>
2006-08-04 16:05 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox