Thread: autoupdating mtime column

Dear all,<br /><br />Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got
automaticallyupdated to NOW() if not explicitly SET in UPDATE statement. Is there a way to make the mtime column behave
morelike I wanted? Thanks in advance. <br /><br />create table t1 (<br />  id int primary key,<br />  t text,<br /> 
ctimetimestamp with time zone,<br />  mtime timestamp with time zone<br />);<br /><br />create or replace function
update_times()returns trigger as $$<br />begin <br />  if tg_op='INSERT' then<br />    if NEW.ctime is null then
NEW.ctime= NOW(); end if;<br />    if NEW.mtime is null then NEW.mtime = NOW(); end if;<br />  elsif tg_op='UPDATE'
then<br />    if NEW.ctime is null then NEW.ctime = OLD.ctime; end if;<br />    if NEW.mtime is null then NEW.mtime =
NOW();end if;<br />  end if;<br />  return NEW;<br />end;<br />$$ language plpgsql;<br /><br />create trigger
update_timesbefore update or insert on t1 <br />for each row execute procedure trig1(); <br /><br />-- #1. mtime &
ctimeset to NOW()<br />insert into t1 values (1,'text1',null,null);<br /><br />-- #2. mtime & ctime set to
'2001-01-01'<br/>insert into t1 values (2,'text2','2000-01-01','2000-01-01');<br /><br />-- #3. mtime and ctime set to
'2006-06-06'<br />update t1 set t='new text1',ctime='2006-6-6',mtime='2006-6-6' where id=1;<br /><br />-- #4. mtime and
ctimeunchanged<br />update t1 set t='new text1' where id=1;<br /><br />-- #5. mtime automatically updated to NOW()<br
/>updatet1 set t='new text1',mtime=null where id=1; <br /><br /> 

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

On 8/4/06, Richard Huxton <dev@archonet.com> wrote:

--
dave

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

On 8/4/06, Rodrigo De León <rdeleonp@gmail.com> wrote:
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

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

"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

On 8/4/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Syncing tables between databases (a la "rsync --times"). Btw, I'm considering temporarily disabling the update_times() trigger when sync-ing.

Thanks,
--
dave

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

On 8/5/06, Richard Huxton <dev@archonet.com> wrote:
That's a nice idea indeed. Thanks!

--
dave