Hi, I am using ROW-LEVEL triggers and trigger functions in an application
which is installed in version 8.0.1, Windows 2003 .

I am having an apparent problem with how I am handling transactions, that
MVCC (Multi Version Concurrency Control) apparently does not handle. The
application is using ROW AFTER triggers to summarize and propagate the
results of transactions inserted into a transaction history table into 2
other tables. Those '2 other tables' are summarizations of the transaction
data, and are being summarized into records having a primary key of the hour
(2005-12-15 14:00:00) and day (2005-12-15 00:00:00) of the transactions.

I am noticing that there is now enough activity in the database so that I
may have multiple triggers active at the same time. At the beginning of each
hour or day, my application detects the need to insert a new (empty) hourly
or daily summary record; all subsequent transactions during that hour or day
are updated to a hourly and daily summary record.

At the turn of the hour and day, I am getting duplicate key violations as
each of the triggers cause the need to set up new hourly / daily records.

At the moment, I have not used PostgreSQL LOCKing before, and I think that
duplicate key violations I am now getting on the hourly and daily
summarization tables are the result. From what I can read in the docs, it
looks like I need to solve the problem by using the following, as soon as I
determine that an INSERT (rather than an UPDATE) of transaction info is
required:
BEGIN WORK;
LOCK <hourly_table> IN ACCESS EXCLUSIVE MODE;
INSERT INTO <hourly_table> VALUES ...;
COMMIT;
Will this make the next triggered transaction find the new row I just
inserted. Am I using these commands correctly? Is this (probably) all I
need?

Also, this application creates new hourly records first, then daily records
(if needed). Is it considered good practice to do the commit after the daily
INSERT (if needed) or LOCK the daily table separately?

- I am assuming that MVCC will allow concurrent updates without loss of
data, once the INSERT is completed.
- Does EXCLUSIVE MODE work at a table level? What I really want is for other
transactions going after the ROW to be stopped until it is inserted.



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