1. Forum
  2. Usenet
  3. COMP.DATABASES.INGRES

  • Triggers and locks

    From Fabrizio Di Renzo@21:1/5 to All on Tue Sep 12 02:40:05 2023
    Hi all,
    we have over 2000 tables for which we want to register all insert, update and delete operations.
    For this reason we have created rules that insert a row in a table that is used to store the operation performed.

    These are the rules

    create rule spider_insert_tab_01 AFTER INSERT ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
    create rule spider_insert_tab_01 AFTER DELETE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
    create rule spider_insert_tab_01 AFTER UPDATE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

    create rule spider_insert_tab_02 AFTER INSERT ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
    create rule spider_insert_tab_02 AFTER DELETE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
    create rule spider_insert_tab_02 AFTER UPDATE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

    This is the procedure

    create procedure prd_spider_update (
    cd_cliente VARCHAR(5) NOT NULL,
    cd_doc VARCHAR(3) NOT NULL,
    orario VARCHAR(2) NOT NULL,
    nm_faldone INTEGER NOT NULL,
    nm_pratica INTEGER NOT NULL,
    gr_pratica INTEGER NOT NULL,
    tp_operazione INTEGER NOT NULL
    ) AS
    begin
    INSERT INTO spider (cd_cliente, cd_doc ,nm_faldone, nm_pratica, gr_pratica, tp_operazione, tm_archiviaz)
    SELECT :cd_cliente, :cd_doc, :nm_faldone, :nm_pratica, :gr_pratica, :tp_operazione, CURRENT_TIMESTAMP()
    end

    In this way, cuncurrency is generated on the table spider which causes locks on the database.
    There is a way to force a row-level lock on the table spider?

    Fabrizio

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Fabrizio Di Renzo on Tue Sep 12 10:36:35 2023
    Fabrizio Di Renzo wrote:

    Hi all, we have over 2000 tables for which we want to register all insert, update and delete operations. For this reason we have created rules that insert a row in a table that is used to store the operation performed.

    These are the rules

    [snip]

    This is the procedure

    create procedure prd_spider_update (
    cd_cliente VARCHAR(5) NOT NULL, cd_doc VARCHAR(3) NOT NULL, orario
    VARCHAR(2) NOT NULL, nm_faldone INTEGER NOT NULL, nm_pratica INTEGER
    NOT NULL, gr_pratica INTEGER NOT NULL, tp_operazione INTEGER NOT NULL
    ) AS begin
    INSERT INTO spider (cd_cliente, cd_doc ,nm_faldone, nm_pratica,
    gr_pratica, tp_operazione, tm_archiviaz)
    SELECT :cd_cliente, :cd_doc, :nm_faldone, :nm_pratica,
    :gr_pratica, :tp_operazione, CURRENT_TIMESTAMP()
    end

    In this way, cuncurrency is generated on the table spider which causes
    locks on the database. There is a way to force a row-level lock on the table spider?

    There is no way to turn on row-level locking in the DDL, if that is what
    you are hoping for.

    You can use SET LOCKMODE ON tablename WHERE LEVEL=ROW in every application. Setting it using ING_SET (and ingsetenv) might make that a bit easier to
    apply, but not everything respects ING_SET; it depends on what languages
    your applications are written in.

    There may be other ways to tackle the problem. You could consider using
    auditdb to trawl the journal files and write an application to insert the relevant output to a table. The application would not block itself.

    Or maybe instead of inserting into spider in the prd_spider_update procedure, raise an event and attach the arguments as a message. Then write a listener
    for the events that would insert into spider, similar to the suggestion
    above. (This would have the disadvantage that it could have already raised
    an event when a subsequent rollback occurs.)

    Or you could consider using HVR, but that costs money and there's a
    learning curve.

    (I haven't mentioned using MVCC because I assume your application is
    using locking for consistency control. MVCC is far preferable to normal row-level locking but the testing burden would probably be prohibitive
    for a large existing application. You _can_ turn on MVCC for individual
    tables, but it is probably imprudent to mix locking and MVCC so I won't
    suggest it.)

    Roy

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From G Jones@21:1/5 to All on Tue Sep 12 07:41:54 2023
    Perhaps partitioning the 'spider' table would help, so your concurrent rule-fired inserts are acting on multiple physical tables rather than just one.
    (It's a less effective approach if spider has secondary indexes though, those can't be partitioned so a single index spans all partitions of the base table).

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Paul White@21:1/5 to All on Tue Sep 12 22:03:06 2023
    Ciao Fabrizio,

    Roy's suggestion of extracting audit journals is a fine idea. I keep 12 months of journals for auditing and offline analysis - and sometimes for recovery.

    If you considered making use of a DBevent with a separate monitoring program, such as OpenROAD or ESQL/C, it could inspect the parameters to decide to write to different audit tables or write to a different database or could output to text file or some
    other system service.

    I have given some excerpts below for ESQL/C. This code is rehashed from a production implementation which monitors changes to critical tables and listens for client DBevent request. It updates data, sends emails, kicks off print jobs and performs other
    important functions depending on various (customisable) rules (for example customer balance > credit_limit).

    Something to keep in mind is that you will miss events if the monitoring program is not running.

    == Schema ==
    create dbevent spider_audit
    \p\g
    grant register on dbevent "ingres".spider_audit to public
    \p\g
    grant raise on dbevent "ingres".spider_audit to public
    \p\g

    create procedure prd_spider_update (
    audittab VARCHAR(32) NOT NULL,
    cd_cliente VARCHAR(5) NOT NULL,
    cd_doc VARCHAR(3) NOT NULL,
    orario VARCHAR(2) NOT NULL,
    nm_faldone INTEGER NOT NULL,
    nm_pratica INTEGER NOT NULL,
    gr_pratica INTEGER NOT NULL,
    tp_operazione INTEGER NOT NULL
    ) =
    DECLARE
    Lv_EventText = varchar(2000) not null with default;
    BEGIN
    Lv_EventText = 'INSERT INTO ' + :audittab +
    '(cd_cliente, cd_doc ,nm_faldone, nm_pratica, gr_pratica, tp_operazione, tm_archiviaz) ' +
    ' VALUES(' +
    '''' + cd_cliente + ''',' +
    '''' + cd_doc + ''',' +
    '''' + orario + ''',' +
    varchar(nm_faldone) + ',' +
    varchar(nm_pratica) + ',' +
    varchar(gr_pratica) + ',' +
    varchar(tp_operazione) + ',' +
    '''' + varchar(CURRENT_TIMESTAMP()) + ''')';

    RAISE DBEVENT "ingres".spider_audit :Lv_EventText WITH SHARE;
    END

    \p\g


    == esql/c program ==
    ...
    EXEC SQL CONNECT :DBNAME IDENTIFIED BY spider_audit_service;
    ...
    EXEC SQL register dbevent spider_audit;
    ...
    while (1) {
    EXEC SQL get dbevent with wait;
    ...
    EXEC SQL inquire_sql (:evtext = dbeventtext);
    ...
    if ( ! strncmp(evtext,"exit",4) ) {
    printf ( "Exit command received");
    break;
    }
    EXECUTE IMMEDIATE :evtext;
    }

    ps. your AFTER INSERT rule looks like it needs to refer to the new record rather than the old record?

    Paul
    &

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Fabrizio Di Renzo@21:1/5 to All on Tue Sep 12 23:53:35 2023
    Thank you all,
    we will try to use dbevent, I think this is a good idea.
    We never used it, but it seem the better solution.

    ps. your AFTER INSERT rule looks like it needs to refer to the new record rather than the old record?

    We need to refer to old record

    Fabrizio

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Fabrizio Di Renzo on Wed Sep 13 08:54:29 2023
    Fabrizio Di Renzo wrote:

    Thank you all,
    we will try to use dbevent, I think this is a good idea.
    We never used it, but it seem the better solution.

    Keep in mind that a raised event won't be "unraised" if there is a
    rollback, so there is risk of recording things which--in a sense--didn't happen.

    Geraint's partitioning suggestion might be the quickest, easiest, and
    most robust thing to try first. It requires no code and can be
    backed out quickly if it doesn't help.

    Roy (Kicking himself for forgetting to mention his own favorite solution)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)