postgres_scanner

postgres_scanner copied to clipboard

Fixes https://github.com/duckdb/duckdb-postgres/issues/298

postgres_execute(db, query, use_transaction=TRUE);
postgres_query(db, query, use_transaction=TRUE);

use_transaction is an optional named parameter and set to true by default. If it is not mentioned, no behavior changes. Not allowed to be turned off in read-only mode or inside an ongoing DuckDB transaction.

When turned off explicitly, it allows to execute queries that are not encapsulated by a transaction. This allows to run operations such as VACUUM on your attached postgres database and gives you more control as to whether you want to use transactions in your queries or not.

Sample output:

D LOAD '~/projects/duckdb/duckdb-postgres/build/extension/postgres_scanner/postgres_scanner.duckdb_extension';
D SET pg_debug_show_queries=TRUE;
D ATTACH 'postgres://postgres@localhost:5432/postgres' AS pg (TYPE POSTGRES);
SELECT version(), (SELECT COUNT(*) FROM pg_settings WHERE name LIKE 'rds%')

D CALL postgres_execute('pg', 'VACUUM');
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
VACUUM

ROLLBACK

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

D CALL postgres_execute('pg', 'VACUUM', use_transaction=FALSE);
VACUUM

┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows  │
└─────────┘
D CALL postgres_query('pg', 'SELECT 1');
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ

COPY (SELECT "?column?" FROM (SELECT 1) AS __unnamed_subquery ) TO STDOUT (FORMAT "binary");

COMMIT

┌──────────┐
│ ?column? │
│  int32   │
├──────────┤
│    1     │
└──────────┘
D CALL postgres_query('pg', 'SELECT 1', use_transaction=FALSE);
COPY (SELECT "?column?" FROM (SELECT 1) AS __unnamed_subquery ) TO STDOUT (FORMAT "binary");

┌──────────┐
│ ?column? │
│  int32   │
├──────────┤
│    1     │
└──────────┘