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