Hi Stefan, thanks for your hints. I'll take a look for the C functions. I can understand your question. My question must look wired. For research I want to implement some different compression schemas in a column based DB. Afterwards I want to check some performance issues. I want to implement some compression schemas that aren't implemented for now. So my first step is to manipulate the inserted/updated data. Best regards Thomas -----Ursprüngliche Nachricht----- Von: developers-list Im Auftrag von Stefan Manegold Gesendet: Mittwoch, 11. Juli 2018 11:17 An: Communication channel for developers of the MonetDB Betreff: Re: Insert/update value manipulation Hi Thomas, please find below some hints how to fine the code related to inserts and updates. However, the most important question is: Why do you want/need to manipulate (or in fact *mess with*) the values that are to be inserted or updated in a database ??? This obviously invalidates SQL semantics and correctness ... Best, Stefan ======== sql>create table t (a int, b int); operation successful #-- EXPLAIN yields the physical (MAL) plan for the given query/statement sql>EXPLAIN insert into t values (1,2),(3,4); +---------------------------------------------------------------------------------------------------------------+ | mal | +======================================================================= +========================================+ | function user.s50_1():void; | | X_5:void := querylog.define("explain insert into t values (1,2),(3,4);":str, "default_pipe":str, 22:int); | | X_9:bat[:int] := bat.new(nil:int); -- tmeporary column for to-be-inserted "a" values | | X_8:int := sql.mvc(); | | X_13:bat[:int] := bat.append(X_9:bat[:int], 1:int, true:bit); -- first "a" value: "1" | | X_17:bat[:int] := bat.append(X_13:bat[:int], 3:int, true:bit); -- second "a" value: "3" | | X_18:bat[:int] := bat.new(nil:int); -- tmeporary column for to-be-inserted "b values | | X_21:bat[:int] := bat.append(X_18:bat[:int], 2:int, true:bit); -- first "b" value: "2" | | X_24:bat[:int] := bat.append(X_21:bat[:int], 4:int, true:bit); -- second "b" value: "4" | | X_26:int := sql.append(X_8:int, "sys":str, "t":str, "a":str, X_17:bat[:int]); -- appending new "a" column| | X_31:int := sql.append(X_26:int, "sys":str, "t":str, "b":str, X_24:bat[:int]); -- appending new "b" column| | X_33:lng := aggr.count(X_24:bat[:int]); | | sql.affectedRows(X_31:int, X_33:lng); | | end user.s50_1; | | # optimizer.mitosis() | | # optimizer.dataflow() | | #inline actions= 0 time=2 usec | | #remap actions= 0 time=4 usec | | #costmodel actions= 1 time=2 usec | | #coercion actions= 0 time=2 usec | | #evaluate actions= 8 time=83 usec | | #emptybind actions= 0 time=1 usec | | #pushselect actions= 0 time=11 usec | | #aliases actions= 8 time=13 usec | | #mergetable actions= 0 time=73 usec | | #deadcode actions= 0 time=14 usec | | #aliases actions= 0 time=0 usec | | #constants actions= 4 time=11 usec | | #commonTerms actions= 0 time=9 usec | | #projectionpath actions= 0 time=6 usec | | #deadcode actions= 0 time=11 usec | | #reorder actions= 1 time=47 usec | | #matpack actions= 0 time=0 usec | | #multiplex actions= 0 time=3 usec | | #profiler actions=1 time=1 usec | | #candidates actions=1 time=2 usec | | #deadcode actions= 0 time=10 usec | | #wlc actions= 0 time=2 usec | | #garbagecollector actions= 1 time=110 usec | | #total actions=26 time=674 usec | +---------------------------------------------------------------------------------------------------------------+ 40 tuples #-- find the C functions ("address") that implement the respective MAL functions #-- You'd need to find the C functions in the source code yourself sql>select * from sys.malfunctions() where "module" in ('bat','sql') and sql>"function" = 'append'; +--------+----------+---------------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+ | module | function | signature | address | comment | +========+==========+=================================================== +====================================+============================+===== +======================================================================= +==================================================================+ | bat | append | command bat.append(i:bat[:any_1], u:any_1):bat[:any_1] | BKCappend_val_wrap; | append the value u to i | | bat | append | command bat.append(i:bat[:any_1], u:any_1, force:bit):bat[:any_1] | BKCappend_val_force_wrap; | append the value u to i | | bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], s:bat[:oid], force:bit):bat[:any_1] | BKCappend_cand_force_wrap; | append the content of u with candidate list s to i | | bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], s:bat[:oid]):bat[:any_1] | BKCappend_cand_wrap; | append the content of u with candidate list s to i | | bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], force:bit):bat[:any_1] | BKCappend_force_wrap; | append the content of u to i | | bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1]):bat[:any_1] | BKCappend_wrap; | append the content of u to i | | sql | append | pattern sql.append(mvc:int, sname:str, tname:str, cname:str, ins:any):int | mvc_append_wrap; | Append to the column tname.cname (possibly optimized to replace the insert bat of tname.cname. Returns sequence number for order dependence. | +--------+----------+---------------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+ 7 tuples sql>insert into t values (1,2),(3,4); 2 affected rows sql>EXPLAIN update t set a = 9; +--------------------------------------------------------------------------------------------------+ | mal | +======================================================================= +===========================+ | function user.s54_1():void; | | X_2:void := querylog.define("explain update t set a = 9;":str, "default_pipe":str, 11:int); | | X_5:int := sql.mvc(); | | C_6:bat[:oid] := sql.tid(X_5:int, "sys":str, "t":str); | | X_11:bat[:int] := algebra.project(C_6:bat[:oid], 9:int); -- creating new "a" column with "9" | | X_13:int := sql.update(X_5:int, "sys":str, "t":str, "a":str, C_6:bat[:oid], X_11:bat[:int]); -- updating existing "a" column | | X_15:lng := aggr.count(C_6:bat[:oid]); | | sql.affectedRows(X_13:int, X_15:lng); | | end user.s54_1; | | # optimizer.mitosis() | | # optimizer.dataflow() | | #inline actions= 0 time=1 usec | | #remap actions= 0 time=31 usec | | #costmodel actions= 1 time=3 usec | | #coercion actions= 0 time=2 usec | | #evaluate actions= 2 time=45 usec | | #emptybind actions= 0 time=0 usec | | #pushselect actions= 0 time=8 usec | | #aliases actions= 2 time=8 usec | | #mergetable actions= 0 time=63 usec | | #deadcode actions= 0 time=12 usec | | #aliases actions= 0 time=0 usec | | #constants actions= 1 time=7 usec | | #commonTerms actions= 0 time=7 usec | | #projectionpath actions= 0 time=5 usec | | #deadcode actions= 0 time=10 usec | | #reorder actions= 1 time=41 usec | | #matpack actions= 0 time=0 usec | | #multiplex actions= 0 time=3 usec | | #profiler actions=1 time=1 usec | | #candidates actions=1 time=1 usec | | #deadcode actions= 0 time=9 usec | | #wlc actions= 0 time=1 usec | | #garbagecollector actions= 1 time=88 usec | | #total actions=26 time=579 usec | +--------------------------------------------------------------------------------------------------+ 35 tuples sql>explain update t set a = 9 where b < 7; +-------------------------------------------------------------------------------------------------------------+ | mal | +======================================================================= +======================================+ | function user.s56_1():void; | | X_3:void := querylog.define("explain update t set a = 9 where b < 7;":str, "default_pipe":str, 21:int); | | barrier X_87:bit := language.dataflow(); | | X_6:int := sql.mvc(); | | X_10:bat[:int] := sql.bind(X_6:int, "sys":str, "t":str, "b":str, 0:int); | | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "t":str); | | C_22:bat[:oid] := algebra.thetaselect(X_10:bat[:int], C_7:bat[:oid], 7:int, "<":str); | | X_24:bat[:int] := algebra.projection(C_22:bat[:oid], X_10:bat[:int]); | | X_28:bat[:int] := algebra.project(X_24:bat[:int], 9:int); -- creating new "a" column with "9" | | language.pass(X_10:bat[:int]); | | exit X_87:bit; | | X_30:int := sql.update(X_6:int, "sys":str, "t":str, "a":str, C_22:bat[:oid], X_28:bat[:int]); -- updating existing "a" column | | X_32:lng := aggr.count(C_22:bat[:oid]); | | sql.affectedRows(X_30:int, X_32:lng); | | end user.s56_1; | | #inline actions= 0 time=1 usec | | #remap actions= 0 time=4 usec | | #costmodel actions= 1 time=3 usec | | #coercion actions= 0 time=3 usec | | #evaluate actions= 4 time=58 usec | | #emptybind actions= 1 time=19 usec | | #pushselect actions= 3 time=25 usec | | #aliases actions= 7 time=14 usec | | #mergetable actions= 0 time=69 usec | | #deadcode actions= 2 time=14 usec | | #aliases actions= 0 time=0 usec | | #constants actions= 2 time=11 usec | | #commonTerms actions= 0 time=9 usec | | #projectionpath actions= 0 time=8 usec | | #deadcode actions= 0 time=12 usec | | #reorder actions= 1 time=58 usec | | #matpack actions= 0 time=1 usec | | #dataflow actions= 1 time=45 usec | | #multiplex actions= 0 time=6 usec | | #profiler actions=1 time=2 usec | | #candidates actions=1 time=2 usec | | #deadcode actions= 0 time=19 usec | | #wlc actions= 0 time=1 usec | | #garbagecollector actions= 1 time=180 usec | | #total actions=28 time=912 usec | +-------------------------------------------------------------------------------------------------------------+ 40 tuples sql>select * from sys.malfunctions() where "module" = 'algebra' and sql>"function" = 'project'; +---------+----------+--------------------------------------------------------------+-----------------+-------------------------------+ | module | function | signature | address | comment | +=========+==========+================================================== +============+=================+===============================+ | algebra | project | pattern algebra.project(b:bat[:any_1], | v:any_3):bat[:any_3] | ALGprojecttail; | Fill the tail with a | constant | +---------+----------+--------------------------------------------------------------+-----------------+-------------------------------+ 1 tuple sql>select * from sys.malfunctions() where "module" = 'sql' and sql>"function" = 'update'; +--------+----------+--------------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------------------------+ | module | function | signature | address | comment | +========+==========+=================================================== +===================================+==================+================ +======================================================================= +=====+ | sql | update | pattern sql.update(mvc:int, sname:str, tname:str, cname:str, rids:any, upd:any):int | mvc_update_wrap; | Update the values of the column tname.cname. Returns sequence number for order dependence) | +--------+----------+--------------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------------------------+ 1 tuple sql> ======== ----- On Jul 10, 2018, at 3:44 PM, Schediwy, Thomas (itsc) Thomas.Schediwy@ITSC.de wrote:

Hi there,

I am looking for the function where I can manipulate the values from an insert or update statement.

For now, I am using a Solaris 10 system. But I havent found the functions where the insert or update will get done. I have looked trogh the files stream.c, mapi.c and mclient.c. But everytime I tried to change the values in the variables nothing happend.

Can somebody please help me?

Thanks in advise

Thomas

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Hi Panos, thank you for your hints. I'll take a look for the file. I haven't found that file before. Well UDFs may help, but I want to implement some additional compression schemas for some performance research. So do you think UDFs are still a good idea to measure the performance between compressed and uncompressed data? Best regards Thomas -----Ursprüngliche Nachricht----- Von: developers-list Im Auftrag von Panagiotis Koutsourakis Gesendet: Mittwoch, 11. Juli 2018 12:19 An: Communication channel for developers of the MonetDB suite. Betreff: Re: Insert/update value manipulation Hi Thomas, Take a look at the file sql/server/rel_updates.c but before you dive into the internals of the sql layer, think if you can accomplish your goals in a simpler way (with UDFs for instance: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti..., https://www.monetdb.org/node/437 and https://www.monetdb.org/blog/embedded-pythonnumpy-monetdb). Best regards, Panos. Schediwy @ 2018-07-10 13:44 GMT:

Hi there,

I am looking for the function where I can manipulate the values from an insert or update statement.

For now, I am using a Solaris 10 system. But I havent found the functions where the insert or update will get done. I have looked trogh the files stream.c, mapi.c and mclient.c. But everytime I tried to change the values in the variables nothing happend.

Can somebody please help me?

Thanks in advise

Thomas

developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list