Good day to all, when switching from Oracle to Postgres, we faced the problem of autonomous transactions, which are not available in PG.
On the forums, it is suggested to use dblink as a solution, but as noted, it is expensive in terms of performance.
In oracle, we used our own logging procedure, which was called in the code, and in cases of errors and rollbacks in oracle, the data was saved until the error occurred.In the PG, everything that was before the error is rolled back and the logs are not saved. I had to rewrite one function in python, and I thought, why not use functions on plpython3u, in which a new connection would be created and the necessary logic would be executed. In general, this approach works, but I haven’t seen any examples of it anywhere. Or is it essentially the same dblink, only also executed via plpython3u, which is an “Untrusted” language. So there is no gain compared to dblink?
The question for those who know, does it make sense to use this approach?
I simplified the script, just to show what I mean.
create function pr_log
( pv_proc text
) returns void
language plpython3u as
$$
import psycopg2
try:
conn = psycopg2.connect()
cursor = conn.cursor()
cursor.execute(""" INSERT INTO log (proc) VALUES (%s)""", (pv_proc))
conn.commit()
cursor.close()
conn.close()
$$;
I tried to create a semblance of an autonomous transaction, but I’m not sure if this approach is good or bad.
I plan to solve the security issue in terms of data visibility for connection by storing them in a separate table inaccessible to ordinary users and retrieving data from it through the function
3
Great day to you!
To handle error logging in PostgreSQL, you can use PL/pgSQL with error handling, avoiding the need for external connections like plpython3u. Below is a simplified approach,
CREATE OR REPLACE FUNCTION log_error(p_error_message VARCHAR)
RETURNS VOID AS $$
BEGIN
INSERT INTO error_log (error_message) VALUES (p_error_message);
END;
$$ LANGUAGE plpgsql;
This function logs error messages into the error_log table. You can use it within your transaction block to capture and store error details.
Please let me know if I’m wrong or if you need further clarification on this.
Nagulan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1