AW: Insert/update value manipulation

Schediwy, Thomas (itsc) Thomas.Schediwy at ITSC.de
Wed Jul 11 16:00:27 CEST 2018


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 <developers-list-bounces+thomas.schediwy=itsc.de at monetdb.org> Im Auftrag von Stefan Manegold
Gesendet: Mittwoch, 11. Juli 2018 11:17
An: Communication channel for developers of the MonetDB <developers-list at monetdb.org>
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 at 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 at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list

-- 
| Stefan.Manegold at 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 at monetdb.org
https://www.monetdb.org/mailman/listinfo/developers-list


More information about the developers-list mailing list