How to log to client (port native code to stored procedure)?

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: How to log to client (port native code to stored procedure)?
Date: 2022-03-02 09:14:05
Message-ID: CAEcMXhmiaqEjEXjhZvo_n_2EkeRyNyQ1sX6yS1vJVqXwz7UV6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message DAN LU 2022-03-02 12:21:53 PostgreSQL Upgrade with Replication managed via repmgr
Previous Message Sonai muthu raja M 2022-03-01 12:46:25 Re: Require details that can we set characters length of a password to a User account in PostgreSQL Database.