search

lzlabs / pg_statement_rollback

Server side rollback at statement level for PostgreSQL like Oracle or DB2
ISC License
48 stars 7 forks source link

pg_dump failed #4

Open pp-sepag opened 6 days ago

pp-sepag commented 6 days ago

Hi,

with this extension enabled, the tool, backing up the DB fails with the following error:

postgres@localhost:~$ pg_dump
pg_dump: error: query failed: ERROR:  SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction
pg_dump: error: query was: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY

This error seems to occur in any PostgreSQL version.

The configuration looks like this

postgresql.conf
# Add settings for extensions here
session_preload_libraries = 'pg_statement_rollback'
pg_statement_rollback.enabled = on

Is this a known issue? Can the extension be temporarily disabled for the backup session, while keep it enabled globally? Or is there maybe another better solution? Thanks!

darold commented 2 days ago

Commit f81f4a1 might fix this issue.

pp-sepag commented 18 hours ago

Seems to work well! At least if invoked from the command line. Backup is still not possible from within the pgAdmin tool. I dug a little deeper and found out, that the application name is passed in this case as "pgAdmin 4 - DB:xxx". Obviously the fix won't help in this situation. Anyway, thanks for the quick response and fix, much appreciated! 😊

darold commented 17 hours ago

Yes this is a quick workaround to be able to use pg_dump, I still have to fix that globally