Re: Am I in the same transaction block in complex PLPGSQL?

On Fri, Mar 11, 2022 at 9:24 AM Durumdara <durumdara(at)gmail(dot)com> wrote:

> Do you have any idea? Or we must upgrade to min. PGSQL 11 for access
> transaction handling and could post the logs through another transaction?
>

You really do need to open a second session somehow if you want the first
session to be able to fail while still allowing for stuff happening during
its execution to commit.

You can either do this in client-side code by simply opening up two
connections and doing the main work on one while doing the logging on the
other.

As far as I know to do this in-database you would need to use the dblink
extension:

https://www.postgresql.org/docs/current/dblink.html

While much of that module's purpose was subsumed by the addition of FOREIGN
DATA WRAPPERS the ability to have a transaction independent connection back
into the database was not one of those things - using FDW the remote work
is done in the same transaction context as the local database.

David J.