Clarion and PostgreSQL Transactions

I’ve been using Clarion with PostgreSQL for several years now. However, it seems I still struggle to understand the finer details of the combination. Case in point is an ABC browse on which a button/popup calls a PG function to pass the primary key of the highlighted record and then simply update that record with a timestamp after checking some conditions. To the immediate user, the update appears complete with the browse now showing the new timestamp.

The problem is that PG does not actually commit the change until the user exits the browse procedure. This behaviour is, of course, acceptable for a form procedure because it is closed after a record is updated. However, this browse is one that many users leave open for extended periods.

Calling the PG function from outside Clarion verifies that the function completes and immediately updates without error. In addition, because BEGIN/END in a PG function automatically frames the code in a transaction, we know an explicit transaction on the PG side is not necessary (or may cause an error).

The question then is why or whether a LOGOUT/COMMIT in Clarion is necessary?


3 posts were split to a new topic: Is it possible for the originator to correct the spelling after posting?

Can you share an example of the code perhaps? Could be that there is some trick to it that someone can help out with.

Hi Douglas,
Your assertion that the BEGIN END in the function makes it a transaction is incorrect.
The pg function is treated as a single statement. So in a way it’s transactional, the function either completes or it doesn’t.
But it’s a part of the transaction that calls it.

So depending on your isolation levels etc, your function call may still be in an open transaction.
In fact based on what you’ve said it pretty much has to be.

Sean H


Thanks for the reply.
No doubt, my understanding of transaction details is still lacking.
I was working on the basis for the PG docs for BEGIN (excerpts below).

I agree with you that a transaction seems to still be open. Since the code
never issues a START TRANSACTION, it is unclear to me where the
isolation levels stand.

BEGIN initiates a transaction block, that is, all statements after a BEGIN
command will be executed in a single transaction until an
explicit COMMIT or ROLLBACK is given. …
a commit is implicitly performed at the end of the statement (if execution
was successful, otherwise a rollback is done).

START TRANSACTION has the same functionality as BEGIN.

BEGIN is a PostgreSQL language extension. It is
equivalent to the SQL-standard command START TRANSACTION

Issuing BEGIN when already inside a transaction block will provoke a warning message.


I found this:
It might help you figure things out. Either or make things worse :slight_smile:

Also remember the ODBC layer comes into play as well. The transaction may be started there by the clarion drivers.


Yes, it looks like the old “must get worse before it gets better” learning curve.
So far, not starting the transaction in Clarion. Was hoping to avoid it and instead keep code at the server.