postgres_scanner

postgres_scanner copied to clipboard

After attaching to a postgres database, the statement:

CALL postgres_execute('pg', 'VACUUM my_schema.my_table');

fails with

Invalid Error:
Failed to execute query "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
VACUUM my_schema.my_table": ERROR:  VACUUM cannot run inside a transaction block

which makes sense. You cannot vacuum inside a transaction block in postgres itself either.

But a helpful feature would be a flag on CALL or adding another extension statement that would allow VACUUM to be executed without a transaction, given that DuckDb targets large etl operations, and Postgres benefits from vacuuming after large updates or deletes.