Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.

3 May 2016

      Hi all,

I'm splitting my data into per-day tables and importing them into monetdb. Before the import, I first create a per-day table and then use 'ALTER TABLE "table" ADD TABLE "table__2016-05-03";' command (passed to cursor.execute() in python. "table" is defined to be a MERGE TABLE.

Now, I had only a single day that was populated on the initial run, when both MERGE TABLE and day table were created. Today, I added a new day table and ran this:

sql> SELECT day, count(*) FROM "table" GROUP BY day;
+------------+---------+
| day        | L1      |
+============+=========+
| 2016-04-26 | 2959601 |
+------------+---------+
sql>select day, count(*) from "trades__2016-05-02" group by day;
+------------+---------+
| day        | L1      |
+============+=========+
| 2016-05-02 | 2220143 |
+------------+---------+

So, the output is missing the current day, and I thought my python code didn't properly tie these tables together. However, inspecting sys.* tables gives me this:

sql>\d
(snip...)
MERGE TABLE  my_namespace.trades
TABLE        my_namespace.trades__2016-04-26
TABLE        my_namespace.trades__2016-05-02
(snip...)
sql>select * from sys.tables where name like 'trades%';
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+
| id    | name               | schema_id | query | type | system | commit_action | access | temporary |
+=======+====================+===========+=======+======+========+===============+========+===========+
| 13480 | trades             |      6497 | null  |    3 | false  |             0 |      0 |         0 |
| 13518 | trades__2016-04-26 |      6497 | null  |    0 | false  |             0 |      0 |         0 |
| 16203 | trades__2016-05-02 |      6497 | null  |    0 | false  |             0 |      0 |         0 |
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+
3 tuples (3.323ms)
sql>select * from sys.dependencies where depend_id=13480;
+-------+-----------+-------------+
| id    | depend_id | depend_type |
+=======+===========+=============+
| 13518 |     13480 |           2 |
| 16203 |     13480 |           2 |
+-------+-----------+-------------+

Finally, if I manually add the table, it succeeds:

sql>alter table "trades" add table "trades__2016-05-02";
operation successful (13.894ms)
sql>select day, count(*) from "trades" group by day;
+------------+---------+
| day        | L1      |
+============+=========+
| 2016-04-26 | 2959601 |
| 2016-05-02 | 2220143 |
+------------+---------+
2 tuples (44.196ms)
sql>select * from sys.tables where name like 'trades%';
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+
| id    | name               | schema_id | query | type | system | commit_action | access | temporary |
+=======+====================+===========+=======+======+========+===============+========+===========+
| 13480 | trades             |      6497 | null  |    3 | false  |             0 |      0 |         0 |
| 13518 | trades__2016-04-26 |      6497 | null  |    0 | false  |             0 |      0 |         0 |
| 16203 | trades__2016-05-02 |      6497 | null  |    0 | false  |             0 |      0 |         0 |
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+
3 tuples (7.445ms)
sql>select * from sys.dependencies where depend_id=13480;
+-------+-----------+-------------+
| id    | depend_id | depend_type |
+=======+===========+=============+
| 13518 |     13480 |           2 |
| 16203 |     13480 |           2 |
+-------+-----------+-------------+
2 tuples (1.714ms)

For completeness, python code looks like this (cleaned up, to present the case, keep in mind that on initial import, this worked):

if args.create:
        if args.partition and has_day:
            dest_create_merge_table = re.sub('CREATE TABLE', 'CREATE MERGE TABLE', dest_create_table, count=1)
            print("Creating table with the following content:\n{ddl}".format(ddl=dest_create_merge_table), file=sys.stderr)
            try:
                cursor = monet_conn.cursor()
                cursor.execute(dest_create_merge_table)
            except monetdb.exceptions.OperationalError as e:
                if re.search('CREATE TABLE: name .* already in use', e.args[0]):
                    print("Master table already present")
                else:
                    raise(e)
            finally:
                cursor.close()

            if args.partition and has_day:
                cursor = monet_conn.cursor()
                for c in conditions:
                    (text, day, sth) = c
                    dest_create_partition = re.sub('CREATE TABLE "{table}"'.format(table=table),
                                                   'CREATE TABLE "{partition}"'.format(partition=partition_name(table, day)),
                                                   dest_create_table, count=1)

                    print("Creating partition with the following content:\n{ddl}".format(ddl=dest_create_partition), file=sys.stderr)
                    cursor.execute(dest_create_partition)
                    if args.debug:
                        print('ALTER TABLE "{table}" ADD TABLE "{partition}"'.format(table=table, partition=partition_name(table, day)))
                    cursor.execute('ALTER TABLE "{table}" ADD TABLE "{partition}"; COMMIT;'.format(table=table, partition=partition_name(table, day)))
                cursor.close()

Can someone please help me debug this issue? Is this a bug? IIUC, metadata points that "trades__2016-05-02" belongs to "trades", but the queries do not reflect that.

Thanks,
Nikola

Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.

Knezevic Nikola