When changing the schema name of a table, referencing rows from sys.columns, sys.keys and more tables are not removed

[monetdb-team]

Date: 2019-04-18 19:01:29 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2019-09-02 16:05:27 +0200

Comment 26965

Date: 2019-04-18 19:01:29 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0
Build Identifier:

Note: this issue applies to version Apr2019 (compiled on 18 april)
When changing the schema name of a table, the id of the row in sys._tables is changed (possibly by adding a new row and removing the original row).
However any referencing rows from sys._columns.table_id, sys.keys.table_id, sys.objects, sys.idxs, sys.dependencies (and possibly more) to the old sys.tables.id are not changed/removed. They become orphaned rows which is not allowed/correct. Referential integrity of system tables is violated.

Reproducible: Always

Steps to Reproduce:

-- First check that no invalid FK references exist in the db. All next queries should return zero rows:
SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);

-- create a table with a serial column (which implicitly createa a primary key constraint and index and a sequence)
create table sys.test2 (col1 serial);
select * from tables where name = 'test2'
-- 1 row: test2 (id = 9046)
select * from keys where table_id in (select id from tables where name = 'test2')
-- 1 row: test2_col1_pkey
select * from objects where id in (select id from keys where table_id in (select id from tables where name = 'test2'))
-- 1 row: col1

ALTER TABLE sys.test2 SET SCHEMA profiler;
select * from tables where name = 'test2'
-- 1 row: test2. NOTE that the id has changed from 9046 into 9048
select * from keys where table_id in (select id from tables where name = 'test2')
-- 1 row: test2_col1_pkey
select * from objects where id in (select id from keys where table_id in (select id from tables where name = 'test2'))
-- 2 rows! which are also identical: col1

ALTER TABLE profiler.test2 SET SCHEMA json;
select * from tables where name = 'test2'
-- 1 row: test2. NOTE that the id has changed from 9048 into 9050
select * from keys where table_id in (select id from tables where name = 'test2')
-- 1 row: test2_col1_pkey
select * from objects where id in (select id from keys where table_id in (select id from tables where name = 'test2'))
-- 3 rows! which are also identical: col1

ALTER TABLE json.test2 SET SCHEMA sys;
select * from tables where name = 'test2'
-- 1 row: test2. NOTE that the id has changed from 9050 into 9052
select * from keys where table_id in (select id from tables where name = 'test2')
-- 1 row: test2_col1_pkey
select * from objects where id in (select id from keys where table_id in (select id from tables where name = 'test2'))
-- 4 rows! which are also identical: col1

-- Now repeat the invalid FK references exist in the db. All next queries should return zero rows:
SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 3 invalid rows
SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
-- lists 3 invalid rows
SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 3 invalid rows
SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
-- lists 3 invalid rows
SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 3 invalid rows
SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);

drop table sys.test2;

-- Now repeat the invalid FK references exist in the db. All next queries should return zero rows:
SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 4 invalid rows
SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
-- lists 4 invalid rows
SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 4 invalid rows
SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
-- lists 4 invalid rows
SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
-- lists 4 invalid rows
SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);

-- now try to recreate the dropped table
create table sys.test2 (col1 serial);
-- it produces Error: CONSTRAINT PRIMARY KEY: key test2_col1_pkey already exists SQLState: 42000

Actual Results:

Orphaned rows found in many sys.* tables which violate referential integrity.
Recreating of the table after dropping it first, fails.

Expected Results:

No orphaned rows (invalid FKs) should be created after executing
ALTER TABLE qname SET SCHEMA ident;

After dropping the table, it should be possible to create the table again without the constraint error.

FYI: You can list the invalid rows by executing the queries in
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql

Comment 26966

Date: 2019-04-18 19:32:43 +0200
From: MonetDB Mercurial Repository <>

Changeset f02c941f4e25 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=f02c941f4e25

Changeset description:

Add test and desired output for bug #6701

Comment 26971

Date: 2019-04-25 17:04:56 +0200
From: MonetDB Mercurial Repository <>

Changeset a903388a5459 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=a903388a5459

Changeset description:

Fixes for bug #6701. Instead of recreating the table, we add the schema change in the transaction level and apply it before any schema changes.

The case of a table changing it's schema twice during the transaction is not handled properly yet, but anyway it's a rare case.