Modification Dates

[Sven Schwyn]

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()
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Nigel J. Andrews]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dennis Gearon]

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?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Sven Schwyn]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Sven Schwyn]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

Sven Schwyn <ze**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[E. Zorn]

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Fabrizio Mazzoni]

Hi .. i have the same problem ..

If i left forms open i would get the same error and could not close them unless i would change to design mode ..

and solved it this way :

In the access db open the database then in the menu open Tools --> Options Then go in then Advanced section and set the ODBC Update interval to 32766 (instead of 1500)

Next run regedit on the windows machine that opens the db and go to the following key:

HKLM --> Software --> Microsoft --> Jet --> 4.0 --> ODBC and change the following key : (If you use access 97 than jet will be 3.5 and not 4.0)

Connection Timeout from 600 to 0 (Remember to use decimal and not hexadecimal when you edit)
It took me about 5 motnhs to understand this problem but finally it worked!!!

Best Regards,

Fabrizio Mazzoni
Macron Srl
On Mon, 29 Sep 2003 17:34:22 +0200
"E. Zorn (RDG-rational) postsql" <po*****@rdg-rational.de> wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly