Failure when running "show all tables" after attaching to a postgres database

[juja0]

What happens?

Running query "show all tables" after attaching to a postgres database fails with "Invalid Error".

In rare occasions, it also hangs and never returns. Seems like a mismatch in the reported length of query (message sent to postgres server) vs actual message length, causing postgres to wait forever.

The error only happens when run in this order: "attach" -> "use db" -> "show all tables"

Other combinations of queries seem to work just fine.

Works:

• "attach" -> "use " -> "show tables"
• "attach" -> "show all tables"
• "attach" -> "use " -> "show tables" -> "show all tables"

Doesn't work:

• "attach" -> "use " -> "show all tables"

To Reproduce

Steps to replicate on duckdb cli (v1.3.2, apple-silicon, postgres v17)

Note: Requires postgres to be running locally.

docker run --name my-postgres -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres

DuckDB v1.3.2 (Ossivalis) 0b83e5d2f6
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

D ATTACH 'host=localhost' AS ttt (TYPE POSTGRES);

D use ttt;

D show all tables;

Invalid Error:
Failed to execute query "
SELECT pg_namespace.oid AS namespace_id, relname, relpages, attname,
    pg_type.typname type_name, atttypmod type_modifier, pg_attribute.attndims ndim,
    attnum, pg_attribute.attnotnull AS notnull, NULL constraint_id,
    NULL constraint_type, NULL constraint_key
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
JOIN pg_type ON atttypid=pg_type.oid
WHERE attnum > 0 AND relkind IN ('r', 'v', 'm', 'f', 'p') AND pg_namespace.nspname='information_schema'AND relname='duckdb_columns'
UNION ALL
SELECT pg_namespace.oid AS namespace_id, relname, NULL relpages, NULL attname, NULL type_name,
    NULL type_modifier, NULL ndim, NULL attnum, NULL AS notnull,
    pg_constraint.oid AS constraint_id, contype AS constraint_type,
    conkey AS constraint_key
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_constraint ON (pg_class.oid=pg_constraint.conrelid)
WHERE relkind IN ('r', 'v', 'm', 'f', 'p') AND contype IN ('p', 'u') AND pg_namespace.nspname='information_schema'AND relname='duckdb_columns'
ORDER BY namespace_id, relname, attnum, constraint_id;
": 

Can also be replicated by adding a test case for "show all tables" in "attach_list_tables.test" in postgres-scanner repository.

i.e. this works

statement ok
ATTACH 'dbname=postgresscanner' AS s1 (TYPE POSTGRES)

statement ok
USE s1;

statement ok
SHOW TABLES;

statement ok
SHOW ALL TABLES;

i.e. this fails

statement ok
ATTACH 'dbname=postgresscanner' AS s1 (TYPE POSTGRES)

statement ok
USE s1;

statement ok
SHOW ALL TABLES;

statement ok
SHOW TABLES;

OS:

macOS 15.4

PostgreSQL Version:

17 (also fails on 14,15,16)

DuckDB Version:

1.3.2 (also fails on 1.3.1)

DuckDB Client:

cli

Full Name:

Gautham Kumar

Affiliation:

Protium Finance

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