Thread: autoupdating mtime column

autoupdating mtime column

From
"David Garamond"
Date:
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 /> 

Re: autoupdating mtime column

From
Richard Huxton
Date:
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


Re: autoupdating mtime column

From
"David Garamond"
Date:
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

Re: autoupdating mtime column

From
"Rodrigo De León"
Date:
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


Re: autoupdating mtime column

From
"David Garamond"
Date:
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

Re: autoupdating mtime column

From
Richard Huxton
Date:
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



Re: autoupdating mtime column

From
Tom Lane
Date:
"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


Re: autoupdating mtime column

From
"David Garamond"
Date:
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

Re: autoupdating mtime column

From
Richard Huxton
Date:
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


Re: autoupdating mtime column

From
"David Garamond"
Date:
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