Duckdb schema is disconnected on postgres error #344
Description
What happens?
- Launch a postgres - given a docker-compose.yml snippet below
- Attach a ducklake database with postgres as catalog store.
- Create a table
- Insert into the table.
- Delete from table with wrong column name to simulate connection error and rollback flow.
- Select on the table fails with the error below and the ducklake schema is disconnected.
Leads to schema being disconnected or the duckdb_schemas() queries hangs.
Invalid Error:
Failed to get global stats information from DuckLake: Failed to execute query "
SELECT oid, nspname
FROM pg_namespace
ORDER BY oid;
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')
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')
ORDER BY namespace_id, relname, attnum, constraint_id;
SELECT n.oid, enumtypid, typname, enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
JOIN pg_namespace AS n ON (typnamespace=n.oid)
ORDER BY n.oid, enumtypid, enumsortorder;
SELECT n.oid, t.typrelid AS id, t.typname as type, pg_attribute.attname, sub_type.typname
FROM pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_class ON pg_class.oid = t.typrelid
JOIN pg_attribute ON attrelid=t.typrelid
JOIN pg_type sub_type ON (pg_attribute.atttypid=sub_type.oid)
WHERE pg_class.relkind = 'c'
AND t.typtype='c'
ORDER BY n.oid, t.oid, attrelid, attnum;
SELECT pg_namespace.oid, tablename, indexname
FROM pg_indexes
JOIN pg_namespace ON (schemaname=nspname)
ORDER BY pg_namespace.oid;
": another command is already in progress
To Reproduce
version: "3.9"
services:
db:
image: postgres:16.2
restart: always
ports:
- 5432:5432
environment:
POSTGRES_DB: test_db
POSTGRES_USER: root
POSTGRES_PASSWORD: password
POSTGRES_HOST_AUTH_METHOD: trust
docker compose up
duckdb
ATTACH 'ducklake:postgres:dbname=test_db host=localhost user=root' AS test_db (DATA_PATH '/tmp/db');
use test_db;
CREATE TABLE connectionerror (s_id INTEGER, name TEXT, age INTEGER,_sm_created_at TIMESTAMP);
insert into connectionerror values (1, 'test', 12, now());
delete from connectionerror where idea = 1;
select * from connectionerror;
OS:
macos
PostgreSQL Version:
16.2
DuckDB Version:
1.3.2
DuckDB Client:
duckdb
Full Name:
Prakash
Affiliation:
Summation
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