Column Level Locking in INSERT commands.
Execture an insert into table command using the following table and user functions:
CREATE TABLE TBL ( _n INTEGER PK, -- columns hosted on server cluster nodes _a INTEGER source="1.0.0.1:db=db;user=user", _b INTEGER source="1.0.0.2:db=db;user=user", _txt TEXT, ); -- array of initialization keys for first row DECLARE KEYS['_a','_b']=[0,1]; /* list of used function parameters N: integer, value in column TBL._n COL: text, column name X: integer, input value of an expression */ -- function that returns value in column _n -- - in row being inserted PKID()=$_$ RETURN 'SELECT N FROM TBL WITH ROWID AS R WHERE R='||TRANSACTION_ROWID(); $_$; -- function Stamp -- - returns key when N=1 or value in -- - row _n=N-1 for column COL STM(COL TEXT)=$_$ N=PKID(); RETURN N-1==0? KEY(COL): EXE('SELECT '||COL||' FROM TBL WHERE _n='||N-1); $_$; -- function Positive or Negative -- - returns -1 for even values in -- - column _n and 1 for odd -- function Calculation -- - returns COL*X*PON()+_a+_b+_c+_d in row N PON() = $_$ RETURN PKID()%2==0?-1:1; $_$; CLC(COL TEXT,X INT) = $_$ N=PKID(); RETURN 'SELECT '||COL*X*PON(N)+_a+_b||' FROM TBL WHERE _n='||N; $_$;
Perform an insert into TBL command where values in columns _a and _b are derived from calculations of user-defined functions STM() and CALC(). Value inserted into column _txt is a concatenated string of columns _a and _b. Incremental value from series 1..2 is inserted into primary key column _n.
-- transaction start INSERT INTO TBL ( _n, _a, _b, _txt ) SELECT ( N._N, CALC('_a',STM('_a'), CALC('_b',STM('_b'), (SELECT _a||_b FROM TBL WHERE _n = PKID()) ) FROM SERIES(1..2) AS N(_N) ); SELECT * FROM TBL; _n | _a | _b | _txt ===|====|====|===== 1 | 0 | 2 | '02' 2 | 2 | -1 | '2-1' -- transaction end
Sprout system code:
-- class containing system functionality TRANSACTION_ROWID() => SYS.ASYNC.INIT.row(); CLASS SYS() $_$ ... -- subclass containing functionality for execution -- - of asynchronous transactions -- - method to instantiate a request object from SQL string -- note: synchronous transaction object contains similar methods CLASS ASYNC() $_$ ... METHOD INIT(SQL TEXT) $_$ ... /* method instantiates an object with the following .row() - unique identifier of a new table row .levels[] - array of transaction stack - following is per each level in stack: .scalars[] - array of scalar values .functions[] - array of function calls .statements[] - array of sub-select statements scalars[], functions[] and statements[] indices are unique identifiers of table columns */ ... $_$; -- functionality of asynchronous transaction execution -- - employs PGQ http://wiki.postgresql.org/wiki/PGQ_Tutorial METHOD THREAD(ARR ARRAY[]) $_$ ... /* accepts array of scalars, SQL functions or sub select statements, launches threads of asynchronous subtransactions via queue, monitors thread execution in delayed loop, and runs data definition statements on columns supplied in input array indices with results of thread executions */ ... $_$; $_$; -- execute a SQL command -- - this method is called from SQL client -- - supplied with "insert into" command METHOD SQL(SQL TEXT) $_$ ... -- instantiate object request request = NEW SYS.ASYNC.INIT(SQL); -- iterate over levels of transaction stack LOOP i OVER 1..request.levels.length -- insert scalars SYS.ASYNC.THREAD(request.levels[i].scalars); -- insert functions SYS.ASYNC.THREAD(request.levels[i].functions); -- insert statements SYS.ASYNC.THREAD(request.levels[i].statements); $_$; ... $_$;