Well, let's say. Then the next question. Can I merge these tables?
---------------------- MERGE INTO monet2_test USING monet1_test AS t_1 ON ("monet2_test"."key" = t_1."key" OR ("monet2_test"."key" IS NULL AND t_1."key" IS NULL)) WHEN MATCHED THEN UPDATE SET "key" = t_1."key", "value" = t_1."value" WHEN NOT MATCHED THEN INSERT ("Id", "key", "value") VALUES (t_1."Id", t_1."key", t_1."value"); ----------------------
I get the same error. Union doesn't seem to fit here. The size of the heap increase not can. The original merge query had tables of ~60k rows and ~2kk rows. Then there was an attempt to allocate ~ 3TB.
Or maybe you can tell me how you can implement Upsert by null-safe equals for tables of such sizes? Surely someone did something like this.
24.07.2019, 17:12, "Sjoerd Mullender" sjoerd@acm.org:
On 24/07/2019 15.56, Илья Хайбуллин wrote:
According to this logic, this request should not work either, but it does. Or I something not understand? select count(1) from monet2_test t1 inner join monet1_test t2 on t1.key = t2.key;
This test is simpler and can be optimized. The test (t1.key is null and t2.key is null) is also simple enough to be optimized. Combining the two with an OR is currently too complex.
24.07.2019, 16:51, "Sjoerd Mullender" sjoerd@acm.org:
On 24/07/2019 15.14, Илья Хайбуллин wrote:
How can I write a workable join by null-safe eqauls?
I have two tables: create table monet1_test ("Id" bigserial, key int, value varchar(2000)); create table monet2_test ("Id" bigserial, key int, value varchar(2000));
insert into monet1_test select value "Id", value "key", value "value" from generate_series(1,60000); insert into monet2_test select value "Id", value "key", value "value" from generate_series(1,200000);
I tried
select count(1) from monet2_test t1 inner join monet1_test t2 on ((t1.key = t2.key) or ((t1.key is null) and (t2.key is null)));
and got errors
GDK reported error: MT_mmap: CreateFileMapping(0000000000001200, &sa, 4, 22, 1508704256, NULL) failed OS: The printer is out of paper. HEAPalloc: Insufficient space for HEAP of 95997921280 bytes. MT_mmap: CreateFileMapping(00000000000011B4, &sa, 4, 22, 1508704256, NULL) failed OS: The printer is out of paper. HEAPalloc: Insufficient space for HEAP of 95997921280 bytes.
Clearly a cross product. Table monet1_test has (60000-1) rows and table monet2_test has (200000-1) rows. If you multiply those numbers and multiply that with 8 (the size of the items produced by the cross product), you get 95997920008 which is only slightly smaller than the heap the server is trying to allocate. Of course, cross product produces two of those heaps, so you need double this space.
Perhaps you can use UNION instead of OR to make the join condition simpler. I know it's only a work around.
I create bug-report: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6737
I saw that. ;-) I won't close the bug report, even if the work around works.
-- Sjoerd Mullender
,
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Sjoerd Mullender
,
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list