Hi All, I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 and the connections to the database are going through a JDBC driver. The details of what we are running into are as follows:
A plpgsql function call is being made through the JDBC driver, auto-commits are off, and any commits or rollbacks are dependent on the results of the function. When more then one client evokes the function (again, through the JDBC driver), the first caller is able to gain a lock with out issue, via a SELECT ... FOR UPDATE.. clause. Any connections that are made during the select are obviously set in a wait queue. Once the first transaction has completed, then the next call in the wait queue is process, and so on. The issue that we are seeing is that if there is a update that takes place on a record, the results are available on any transactions that follow the initial update, regardless of whether they have been in a wait queue or not. However, if there are inserts that are mode during a transcation, those inserts are not becomming available if a transaction is already in motion (unlike the updates, which do show up). If the transaction is closed and a new one is reopened, after all of the inserts have been completed, then we can see them. Is this the standard behaviour associate to transactions? -- Chris Bowlby <[EMAIL PROTECTED]> PostgreSQL Inc. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
