0

I have a MonetDB merge table with about 620 million rows of data in it, let's call it merge_table. It has 14 member tables, each containing some monthly data (e.g. data1701 for January 2017). The Java program that handles loading data into the database and adding/removing member tables somehow managed to add the same table to the merge table multiple times. My problem is that now I can't remove these duplicated tables from the merge table, even though MonetDB says the operation was successful: 

ALTER TABLE myschema.merge_table DROP TABLE data1708;

operation successful (0.608ms)

Then, when I try to drop the table: 

DROP TABLE myschema.data1708;

DROP TABLE: unable to drop table data1708 (there are database objects which depend on it)

Of course, when I list the members of merge_table, data1708 is still there.

Is there some other method I could use to remove these duplicates?

Edit:

MonetDB version is 11.25.23 (monetdbd[21491] 1.7 (Dec2016-SP5)). JDBC version is 2.19.

Running the DROP TABLE command multiple times yields the same results. Trying to add any of the tables again doesn't work, so it works as expected without JDBC:

ALTER TABLE myschema.merge_table ADD TABLE data1708;

ALTER TABLE: table 'myschema.data1708' is already part of the MERGE TABLE 'myschema.merge_table'

The ID of my merge table is 9020, so I ran the rest of the suggested queries with that:

SELECT * FROM sys.dependencies JOIN sys.dependency_types ON depend_type = dependency_type_id WHERE depend_id = 9020; +-------+-----------+-------------+--------------------+----------------------+ | id | depend_id | depend_type | dependency_type_id | dependency_type_name | +=======+===========+=============+====================+======================+ | 9668 | 9020 | 2 | 2 | TABLE | | 9722 | 9020 | 2 | 2 | TABLE | | 9776 | 9020 | 2 | 2 | TABLE | | 9830 | 9020 | 2 | 2 | TABLE | | 9884 | 9020 | 2 | 2 | TABLE | | 9938 | 9020 | 2 | 2 | TABLE | | 13891 | 9020 | 2 | 2 | TABLE | | 13945 | 9020 | 2 | 2 | TABLE | | 13999 | 9020 | 2 | 2 | TABLE | | 14053 | 9020 | 2 | 2 | TABLE | | 14107 | 9020 | 2 | 2 | TABLE | | 14161 | 9020 | 2 | 2 | TABLE | | 14215 | 9020 | 2 | 2 | TABLE | | 14269 | 9020 | 2 | 2 | TABLE | +-------+-----------+-------------+--------------------+----------------------+

SELECT * FROM sys.objects WHERE id = 9020; +------+------------+------+ | id | name | nr | +======+============+======+ | 9020 | data1607 | 1 | | 9020 | data1608 | 2 | | 9020 | data1609 | 3 | | 9020 | data1610 | 4 | | 9020 | data1611 | 5 | | 9020 | data1612 | 6 | | 9020 | data1701 | 7 | | 9020 | data1702 | 8 | | 9020 | data1703 | 9 | | 9020 | data1704 | 10 | | 9020 | data1705 | 11 | | 9020 | data1706 | 12 | | 9020 | data1707 | 13 | | 9020 | data1707 | 12 | | 9020 | data1708 | 13 | | 9020 | data1707 | 12 | | 9020 | data1708 | 13 | +------+------------+------+

Marcell Bernát
  • 11
  • 1
  • 6

1 Answers1

0

First, for completeness, which version of MonetDB are you using and which version of the JDBC driver? Also, I take it that running the

ALTER TABLE myschema.merge_table DROP TABLE data1708;

a second time didn't make the issue go away?

I tried - without JDBC - the following script:

DROP SCHEMA IF EXISTS demo;
CREATE SCHEMA demo;
SET SCHEMA demo;

CREATE TABLE template (i INT, j INT);

CREATE TABLE foo (LIKE template);
INSERT INTO foo VALUES (1,10), (2,20);

CREATE TABLE bar (LIKE template);
INSERT INTO bar VALUES (3,30);

CREATE MERGE TABLE merged (LIKE template);
ALTER TABLE merged ADD TABLE foo;
ALTER TABLE merged ADD TABLE bar;
ALTER TABLE merged ADD TABLE bar;

ALTER TABLE merged DROP TABLE bar;

DROP TABLE bar;

SELECT * FROM merged;

This works correctly. That is, the second 'ALTER ADD bar' yields an error message and the DROP TABLE bar succeeds. Could you try doing something similar but using a toy Java program?

Also, before doing that, could you peek in the 'sys' schema? In particular,

SELECT * FROM sys.tables WHERE name = 'merge_table';

to figure out the id of the merge table.

SELECT * 
FROM sys.dependencies JOIN sys.dependency_types 
ON depend_type = dependency_type_id 
WHERE depend_id = <merge-table-id>;

and

SELECT * FROM sys.objects WHERE id = <merge-table-id>;

Note that trying to modify these tables to 'fix' things will not work as intended. I'm simply curious about their current state.

Joeri

Joeri van Ruth
  • 181
  • 4
  • Hey Joeri, I've edited my question with the query results. Unfortunately the Java program is not mine, but it's being fixed right now to not include certain assumptions. It's possible that the problem is in the JDBC driver though. – Marcell Bernát Jul 26 '17 at 12:01
  • Your output indeed shows three occurrences of data1707 and two of data1708. I tried with JDBC 2.25 and MonetDB versions Dec2016-SP5 and Jul2017 but failed to reproduce this behaviour. So JDBC is likely not where the problem is. I'm afraid I have no further suggestions on figuring out how this happened. The best you can do is probably to reload the database and hope the various improvements to your application are sufficient to not run into this again. If later on you ever find out how to trick MonetDB into ADDing the table multiple times, please let us know so we can fix it. – Joeri van Ruth Aug 07 '17 at 14:16
  • No problem, thanks for the help anyways. We recreated the DB (and the merge table) from dumps, fixed the program up a little bit and updated to the latest MonetDB version. If we figure something out, I'll definitely let you guys know through the mailing list. – Marcell Bernát Aug 08 '17 at 18:51