HANA 2.0 Limit amount of rows per column value

[TobiT]

Dear community,

i would like to limit the amount of rows that can be inserted into a hdbtable depending on the values that are inserted.

Simple example:

Table
{
 ID : UUID,
 TYPE : VARCHAR(50)
}

On insert i would like to check if the type beeing inserted already exists n times. Meaning if my limit is say 10 entries of the same type the insert should fail.

Hope this is an easy one for the experts.

Cheers

[lbreddemann · Accepted solution]

This sounds a lot like the ask for a table/set-constraint.

While there is no specific feature for this in HANA, it is possible to implement such a constraint with triggers.

As a general comment: if your application data model requires such constraints to absolutely hold true and you have many of such constraints, implementing those on the DB level in HANA can easily become "messy". Managing the order of trigger execution, the consistency of possibly inconsistent rules in those triggers, and the impact on data insert/update performance are complications that can easily make for a very complex (and thus expensive) implementation choice.
For such requirements, often "rules-engines" are used, i.e. transaction intermediaries that channel all data changes, enforce the desired rules, and allow for effective management of the rules.

But now, how to do something like this in HANA:

The following is the plain SQL version of the example.
To implement this in HDI, have a look into the .hdbtrigger artifact.

The trigger is a BEFORE INSERT or UPDATE trigger. Every time it is executed it counts how many entries of the given TYPE are currently (that is, currently visible to the session) in the table and throws an error if this number is equal to or larger than 10.

CREATE TABLE stuff 
 ( ID NVARCHAR(32) NOT NULL
 , "TYPE" VARCHAR(50) NOT NULL 
);

CREATE OR REPLACE TRIGGER max_ten_entries 
    BEFORE INSERT OR UPDATE OF "TYPE"  
    ON STUFF
    REFERENCING NEW ROW AS _new
    FOR EACH ROW 
    -- ONLINE    -- DO NOT use ONLINE for this case, as we need the exclusive lock behavior!
 BEGIN 
    DECLARE current_cnt integer := 0;
    DECLARE err_too_many_entries CONDITION 
        FOR SQL_ERROR_CODE 10001;
     
    DECLARE EXIT HANDLER 
        FOR err_too_many_entries
        RESIGNAL;
     
     SELECT count(*) INTO current_cnt
     FROM 
        stuff
     WHERE 
        "TYPE" = :_new."TYPE";
    
     IF :current_cnt >=10 THEN
        SIGNAL err_too_many_entries SET MESSAGE_TEXT = 'Too many entries inserted for the same "TYPE".';
     END IF;
 
 END;

INSERT INTO stuff (id, "TYPE") VALUES (sysuuid, 'TYPE1' ) ;
INSERT INTO stuff (id, "TYPE") VALUES (sysuuid, 'TYPE1' ) ;
[...]
SELECT "TYPE", count(*) AS cnt FROM stuff GROUP BY "TYPE";

--TYPE |CNT|
-------+---+
--TYPE1| 10|

--- Now, 10 entries of TYPE1 are in the table, add one more!

INSERT INTO stuff (id, "TYPE") VALUES (sysuuid, 'TYPE1' ) ;
-- SQL Error [10001] [HY000]: SAP DBTech JDBC: [10001]: 
-- user-defined error: "DEVDUDE"."MAX_TEN_ENTRIES": line 9 col 9 (at pos 333): 
-- Too many entries inserted for the same "TYPE".

-- Clearly, this didn't work. How about we insert a TYPE2 and then update it to TYPE1?

INSERT INTO stuff (id, "TYPE") VALUES (sysuuid, 'TYPE2' ) ;
UPDATE stuff SET "TYPE" = 'TYPE1' WHERE "TYPE" = 'TYPE2';
-- SQL Error [10001] [HY000]: SAP DBTech JDBC: [10001]: 
-- user-defined error: "DEVDUDE"."MAX_TEN_ENTRIES": line 9 col 9 (at pos 333): 
-- Too many entries inserted for the same "TYPE".

-- same custom error message - the trigger effectively stopped us from inserting more than
-- 10 TYPE1 entries.

Looks good, doesn't it? And if you run it in your developer session (where the default is AUTOCOMMIT=ON) this works just fine.

Where it breaks down is when there is more than one session actively changing the data and those sessions handle their COMMITs themselves.

Note: this is the normal situation!

So, what can we do?

If we just throw in a LOCK TABLE IN EXCLUSIVE MODE into the trigger, we are greeted with this friendly error message:

SQL Error [451] [HY000]: SAP DBTech JDBC: [451]: 
modification of subject table in trigger not allowed: DEVDUDE.STUFF

That's too bad.

What other options are left then?
One option could be to enforce that all INSERTS/UPDATES are performed via stored procedures and not allowed directly on the tables. This can be achieved with the appropriate setup of GRANTS (i.e. grant EXECUTE on the procedures but not UPDATE/INSERT on the tables).

Another alternative could be the use of an INSTEAD OF TRIGGER.

This requires having a view defined on top of the table and the INSTEAD OF TRIGGER on defined on the view:

-- create a simple projection view on top of the table
CREATE VIEW v_stuff AS SELECT * FROM stuff;

-- build the INSTEAD OF TRIGGER on top of that view
CREATE OR REPLACE TRIGGER v_max_ten_entries 
    INSTEAD OF INSERT OR UPDATE   
    ON V_STUFF
    REFERENCING NEW ROW AS _new
    -- ONLINE 
 BEGIN 
    DECLARE current_cnt integer := 0;
    DECLARE err_too_many_entries CONDITION 
        FOR SQL_ERROR_CODE 10001;
     
    DECLARE EXIT HANDLER 
        FOR err_too_many_entries
        RESIGNAL;

    -- we also want to resignal any lock wait errors (error code 131):
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL;

    -- with this we LOCK the table EXCLUSIVELY for all sessions
    -- if we cannot get the LOCK within 10 secs. we abort (in theory).
    -- on HXE 2.00.054 I could only provoke a lock timeout via the DB-wide
    -- parameter lock_wait_timeout, which defaults to 30 mins.
    LOCK TABLE stuff IN EXCLUSIVE MODE WAIT 10;
     
    SELECT count(*) INTO current_cnt
     FROM 
        stuff
     WHERE 
        "TYPE" = :_new."TYPE";
    
     IF :current_cnt >=10 THEN
        SIGNAL err_too_many_entries SET MESSAGE_TEXT = 'Too many entries inserted for the same "TYPE".';
     END IF;

     INSERT INTO stuff (id, "TYPE") VALUES (:_new.id, :_new."TYPE");
 END;

Now, with this approach, it is still required to block INSERT/UPDATE to the table directly.

It should be obvious, that without a properly working lock wait timeout, this approach can easily lead to a blocked system, where sessions wait for other sessions to eventually COMMIT or ROLLBACK and thereby release the exclusive lock.

In summary:

As shown it is possible to implement table/set-wide constraints in HANA via triggers.

However, enforcing those constraints comes with the cost of LOCKs and depending on the requirements such a scenarios may be better addressed in the application layer. (Of course, the application layer could include a set of stored procedures to managed insertion/update of data...).

Cheers,

Lars

[TobiT · Accepted solution]

Thank you very much Lars for this extended Answer! I will dive into your suggestions and read up on rule engines.