Open
Description
What happens?
I tried to reclaim space using approach described here: Compacting a Database by Copying.
The copying fails with the error:
» ATTACH 'reclaim_space_demo.db' AS old;
» ATTACH 'reclaim_space_demo_copy.db' AS new;
» COPY FROM DATABASE old TO new;
Constraint Error:
Violates foreign key constraint because key "id: 1" does not exist in the referenced table
Am I doing something wrong or this is a bug?
The same error occurs when I try to do the same using duckdb-rs crate (v1.2.1).
To Reproduce
- Create tables and fill them with some data:
duckdb reclaim_space_demo.dbCREATE SEQUENCE IF NOT EXISTS id_sequence START 1;
CREATE TABLE IF NOT EXISTS nodes (
id VARCHAR PRIMARY KEY,
name VARCHAR NOT NULL
);
CREATE TABLE IF NOT EXISTS nodes_watchlist (
node_id VARCHAR UNIQUE NOT NULL,
FOREIGN KEY (node_id) REFERENCES nodes(id)
);
CREATE TABLE IF NOT EXISTS datasources (
id INTEGER PRIMARY KEY DEFAULT nextval('id_sequence'),
name VARCHAR NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS datasources_nodes (
datasource_id INTEGER NOT NULL,
node_id VARCHAR NOT NULL,
last_sync_date TIMESTAMP_MS,
FOREIGN KEY (datasource_id) REFERENCES datasources(id),
FOREIGN KEY (node_id) REFERENCES nodes(id),
UNIQUE (datasource_id, node_id)
);
CREATE TABLE IF NOT EXISTS data (
datasource_id INTEGER NOT NULL,
created TIMESTAMP_MS NOT NULL,
value DOUBLE NOT NULL,
FOREIGN KEY (datasource_id) REFERENCES datasources(id),
UNIQUE (datasource_id, created)
);
CREATE VIEW IF NOT EXISTS v_nodes_info AS
WITH
cte_last_sync_dates AS (
SELECT
n.id,
dsn.last_sync_date
FROM nodes n
JOIN datasources_nodes dsn ON dsn.node_id = n.id
GROUP BY ALL
)
SELECT
n.*,
nw.node_id IS NOT NULL AS watched,
lsd.last_sync_date
FROM nodes n
LEFT JOIN nodes_watchlist nw ON nw.node_id = n.id
LEFT JOIN cte_last_sync_dates lsd ON lsd.id = n.id
ORDER BY n.id;
INSERT INTO datasources (name) VALUES ('a'), ('b'), ('c');
INSERT INTO nodes (id, name) VALUES ('node_1', 'Node 1'), ('node_2', 'Node 2'), ('node_3', 'Node 3');
INSERT INTO nodes_watchlist (node_id) VALUES ('node_1');
INSERT INTO datasources_nodes (datasource_id, node_id) VALUES (1, 'node_1');
INSERT INTO data (datasource_id, created, value)
VALUES
(1, '2025-01-01 00:00:00', 0.0),
(1, '2025-01-02 00:00:00', 1.0),
(1, '2025-01-03 00:00:00', 2.0),
(1, '2025-01-04 00:00:00', 3.0),
(1, '2025-01-05 00:00:00', 4.0),
(1, '2025-01-06 00:00:00', 5.0),
(1, '2025-01-07 00:00:00', 6.0),
(1, '2025-01-08 00:00:00', 7.0),
(1, '2025-01-09 00:00:00', 8.0);- Now try to reclaim space by copying:
% duckdb
-- Loading resources from /Users/slavik/.duckdbrc
v1.2.1 8e52ec4395
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
ATTACH 'reclaim_space_demo.db' AS old;
ATTACH 'reclaim_space_demo_copy.db' AS new;
COPY FROM DATABASE old TO new;
Constraint Error:
Violates foreign key constraint because key "id: node_1" does not exist in the referenced table
OS:
macos
DuckDB Version:
v1.2.1
DuckDB Client:
cli, duckdb_rs
Hardware:
No response
Full Name:
Slavik Bubnov
Affiliation:
Self employed
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have