Dear Members!
We have to port a native, complex Win32 code to a stored procedure. PGSQL: V9.6-V11.
One of the problems is: how to log (to client and to server side)?
In the native code we:
1. Open a transaction.
2. Start a complex process.
3. Make local (filesystem based) log in every important step.
4. Some of the logs are kept "in memory" too.
5. In the end we Commit the transaction (or Rollback on failure).
6. Later we post the "in memory" logs into the database too (to a special log table).
If we try to port this, the first problem is that in PLPGSQL we CAN'T manage the transaction.
We can make (raise) notices, and we can catch them in Win32 code - but maybe not in PHP (Symphony) or in other client calls.
Ok - we can also log into a local (stored proc) variable, which will be posted into a database log table - as before.
Buf on a possible failure the whole transaction rolled back - so our posted log is also lost (reverted).
Or it can't be posted at the end (because of tr. dead state), like:ERROR: current transaction is aborted, commands ignored until end of transaction block.
One way I can see: if we don't allow any exception in the stored procedure (catch them all), and we define these IN/OUT variables to pass back the results and logs.
- LOG TEXT (this contain the log)
- ERRORMSG TEXT (the error of any exception, or inner errors)
- ERRORCODE TEXT (to identify the error)
- SUCC BOOL (if it was successful)
And the caller MUST handle the transaction and MUST log these things at once.
So then there is no partial logging possibility (now we have it in the Win32 programme).
Do you have any other ideas for client side logging in stored procedures?
Thank you for any help or suggestions!
Best regards
dd