Thread: Modification Dates

Hi

Many people are asking how to automatically update columns containing a
modification date on updates. I'm wondering if the only solutions in
the current pgsql really are...

- adding "modification=NOW()" to every update query "manually"
- defining a trigger called on updates

While addings things "manually" is quite clumsy, a trigger actually
causes a second update thus slowing down the Db.

As far as I know, rules don't help due to circular conditions (an
update causes an update causes an update...) and functions stil require
to add stuff to each and every update. But I could be wrong. Please -
anyone - enlighten me, us and the world :-)

I'm dreaming of something like the following:
CREATE TABLE table (modified TIMESTAMP NOT NULL DEFAULT NOW(), data
INTEGER)
CREATE RULE table_rule AS ON UPDATE TO table DO ADD modified=NOW()
INSERT INTO TABLE table (data) VALUES (1)   <-- modified is defaulted
to NOW()
UPDATE TABLE table SET data=2   <-- modified is implicitly (by rule)
set to NOW()

On Sat, 27 Sep 2003, Sven Schwyn wrote:

> Hi
>
> Many people are asking how to automatically update columns containing a
> modification date on updates. I'm wondering if the only solutions in
> the current pgsql really are...
>
> - adding "modification=NOW()" to every update query "manually"
> - defining a trigger called on updates
>
> While addings things "manually" is quite clumsy, a trigger actually
> causes a second update thus slowing down the Db.
>

I suspect you're misunderstanding something about triggers, an on update
trigger setting a such a field to the current timestamp shouldn't be causing a
second update. You're actually doing an update statement within the trigger I
presume? That's not the way, just set NEW.modified to the value you want,
eg. the current timestamp.

> As far as I know, rules don't help due to circular conditions (an
> update causes an update causes an update...) and functions stil require
> to add stuff to each and every update. But I could be wrong. Please -
> anyone - enlighten me, us and the world :-)

Does an update within a rule get rewritten by the rule system if it's on the
same table as the rule?


Nigel Andrews

Sven Schwyn wrote:

> Hi
>
> Many people are asking how to automatically update columns containing
> a modification date on updates. I'm wondering if the only solutions in
> the current pgsql really are...

I'd like to know this myself, maybe views would work?

Hi again

Got it, quite obvious too. The trigger has to be called BEFORE the
UPDATE, not AFTER. (Hmmm, very obvious even.)

CREATE TRIGGER _modified BEFORE UPDATE ON any_table FOR EACH ROW
EXECUTE PROCEDURE
touch();

That does the trick!

Greets,  -sven

Hi Nigel

> I suspect you're misunderstanding something about triggers, an on
> update
> trigger setting a such a field to the current timestamp shouldn't be
> causing a
> second update. You're actually doing an update statement within the
> trigger I
> presume? That's not the way, just set NEW.modified to the value you
> want,
> eg. the current timestamp.

I'm doing it this way but I've read somewhere that this causes a second
UPDATE. If that's not the case, the better!

However, I seem to be missing something else. All my tables contain a
column...

modified             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

The following function exists...

CREATE FUNCTION touch() RETURNS OPAQUE AS 'BEGIN new.modified = NOW();
RETURN ne
w; END;' LANGUAGE 'plpgsql';

And all tables have the following trigger defined...

CREATE TRIGGER _modified AFTER UPDATE ON any_table FOR EACH ROW EXECUTE
PROCEDURE
touch();

All this returned no errors. I do get a notice though:
NOTICE:  CreateTrigger: changing return type of function touch() from
OPAQUE to TRIGGER

I had the impression that now the modified-column should be set to the
NOW() whenever an UPDATE is made on the row. That's not the case, the
value remains unchanged. What's wrong with this?

Your help is greatly apprechiated!     -sven

Sven Schwyn <zeug@bluewin.ch> writes:

> CREATE TRIGGER _modified AFTER UPDATE ON any_table FOR EACH ROW
> EXECUTE PROCEDURE
> touch();

You want BEFORE UPDATE here.  AFTER UPDATE happens, well, after the
update.  :)

-Doug

Hi,
i use Access as a frontend via ODBC to an Linux-based postgresql.

I have a problem in a form where I use a lot of subforms. In some
cases ( i couldn't detect when - but I really try to) the form and Access
hangs.

The only way to close the form is to close Access via "cross" (upper right
corner).
If I try then to view once again the form I get the following error message:
"Sie konnen diese Aktion momentan nicht ausfuhren" means : "You counldn't do
this now".

Has anybody an idea - why this is hanging or where i can start to debug.

-Elmar