Support Postgres on AWS Aurora

[otarmelia]

What happens?

postgres_scan function does not supports postgres in aws aurora. When executing statement:

SELECT * FROM postgres_scan('dbname=db user=postgres password=*** host=127.0.0.1', 'public', 'table_name');

it gives error:

Error: Invalid Error: Failed to execute query "SELECT pg_is_in_recovery(), pg_export_snapshot(), (select count(*) from pg_stat_wal_receiver)": ERROR: Function pg_stat_get_wal_receiver() is currently not supported in Aurora

To Reproduce

Create postgresql database in amazon RDS and execurte query:
SELECT * FROM postgres_scan('dbname=db user=postgres password=*** host=127.0.0.1', 'public', 'table_name');

OS:

Windows

PostgreSQL Version:

PostgreSQL 13.9

DuckDB Version:

v0.9.2

DuckDB Client:

CLI

Full Name:

Otar

Affiliation:

Personal

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Mytherin]

Thanks for the report! This looks like a duplicate of #129 which has already been fixed in #131. Could you try this out with the latest development build? If the problem persists there then feel free to re-open.

[StephanGeorg]

@Mytherin

No, does not work with latest build. Latest working version is DuckDB v.0.9.1.

CALL postgres_attach('dbname=db user=postgres host=127.0.0.1 port=5432', source_schema='public'); does connect but SHOW TABLES; or any query does not work.

[Mytherin]

It has been fixed in the latest dev build, not the latest release build. Which one are you using?

[StephanGeorg]

I have compiled the source of main branch.

[Mytherin]

Try using the new ATTACH workflow instead of postgres_scan/postgres_attach. postgres_attach and postgres_scan are deprecated.

i.e.:

ATTACH 'dbname=db user=postgres host=127.0.0.1 port=5432' AS pgdb (TYPE POSTGRES);
USE pgdb;
SHOW TABLES;
SELECT * FROM[table];

[StephanGeorg]

Thanks. That works.