Modification Dates

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 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

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,
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

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

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

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

On Monday 29 September 2003 10:34 am, E. Zorn (RDG-rational) postsql 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

Hmmm. Try the following:

1. Review the subforms.
a. Be careful about inadvertently doing wierd things with table
relationships in queries in subforms.
b. Make sure you've linked subforms to forms in a way that's compatible
with table relationships.
c. Be very careful about nesting subforms.

2. Table definitions and relationships.
a. Even though you may have created the relationships in PostgreSQL, it's
good to define them in MS Access as well.
b. Make sure all bit fields (including/especially boolean fields) are
defined with 'not null' and a default value in PostgreSQL. MS Access is
funny about this.
c. When you link to tables that do not have primary keys in MS Access, it
will ask you to define a primary key so that Access can identify unique rows.
If possible/appropriate, do so.

3. ODBC:
a. It is important to use the latest version of pgsqlodbc. Start here:
http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads
b. Make sure you've selected the options for "Declare/Fetch" and "True is
-1".

Best of luck,

Andrew Gould

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" <postsql@rdg-rational.de> wrote: