Triggers

What is a syntax of triggers in Vertica?
(yes, Vertica has triggers)

daniel=> select load_trigger('<TRIGGER BODY>');
   load_trigger   
-------------------
 Installed trigger
(1 row)



Comments

  • Hi Daniel, This is a bit of compatibility syntax. It is a no-op in current versions of Vertica. If you are interested in trigger support, post to the Ideas section of the site. Thanks, Adam
  • Hi!

    >> It is a no-op in current versions of Vertica.
    load_trigger isn't a PG function that Vertica inherits from PG interface, so its a Vertica function.
    PG has little different syntax, so what is a syntax of triggers?


    PG syntax
    daniel=> CREATE TRIGGER check_update BEFORE UPDATE ON company FOR EACH ROW EXECUTE PROCEDURE check_company_update();
    ERROR 2676:  Command CREATE TRIGGER is not supported

    LOG
    2014-09-07 11:29:45.934 Init Session:0x7f977000f8e0-a0000000020a0b [Session] <INFO> [Query] TX:a0000000020a0b(xps-2613:0x326) CREATE TRIGGER check_update BEFORE UPDATE ON company FOR EACH ROW EXECUTE PROCEDURE check_account_update();
    2014-09-07 11:29:45.935 Init Session:0x7f977000f8e0-a0000000020a0b <ERROR> @v_cooldata_node0001: 0A000/2676: Command CREATE TRIGGER is not supported
        LOCATION:  checkVerticaUtilityStmtSupported, vertica.c:2530


    Vertica syntax
    No-op? It doesn't look like no-op, because Im getting an WARNING for unknown trigger type. If no-op, so why WARNING?

    daniel=> select load_trigger('foo');
       load_trigger   
    -------------------
     Installed trigger
    (1 row)

    LOG
    2014-09-07 11:17:02.839 Init Session:0x7f977000f8e0-a000000002084c [Session] <INFO> [Query] TX:a000000002084c(xps-2613:0x326) select load_trigger('foo');
    2014-09-07 11:17:02.841 Init Session:0x7f977000f8e0-a000000002084c [Util] <WARNING> Ignoring unknown trigger type ''
    2014-09-07 11:17:02.841 Init Session:0x7f977000f8e0-a000000002084c [Util] <INFO> Stored trigger 'foo'

    ===========================================

    How about DELETE/DROP cascade? Based object dependency?  How about next tables?

     vs_catalog_event_add_derived_column
     vs_catalog_event_alter_partition
     vs_catalog_event_drop_partition
     vs_catalog_event_rebalance
     vs_catalog_event_restore
     vs_catalog_event_rollback
     vs_catalog_event_truncate


    Its not for event-driven functionality?
    In an event-driven application, there is generally a main loop that listens for events, and then triggers a callback function when one of those events is detected.
  • Its not NO-OP:

    TriggerActionTime
    TriggerEvents
    TriggerOneEvent
    TriggerForSpec
    TriggerForOpt
    TriggerForType
    TriggerFuncArgs
    TriggerFuncArg


    TuppleMover also based on triggers!

    PS
    /scratch_a/release/vbuild/vertica/Util/DebugTrigger.cpp
    static void Util::DebugTrigger::do_trigger(const std::string&, void*)

  • Hi , Daniel .

    Nice fishing on the Vertica binary ( you probably use the  strings command  method ) :)  .

    In big data platform the benefit of triggers is significant can be problematic , it will lead to record level validation and will kill your load process ,  as I see it , in big data platform , set base processing should be your  favorite  method for triggering changes ( One of the options can be  staging area + merge command ) .  Despite that  still I may see some use cases that triggers can be helpful (mainly for dimensions  data) .

    Thanks 

  • Hi Daniel, Congratulations! You have discovered a piece of debugging code that our linker doesn't prune out of the released Vertica binary. I can see that you want Vertica to be based on something like SQL triggers. I can't discuss how we do run automated tasks, but I will say that this function is not it. It is a no-op in the sense that there should be no known trigger types in the build of Vertica that you (or any other customer, under ordinary circumstances) is using. So, what does it do? Well, again, implementation details :-) But I will say that, as you might guess from the name of the function that you discovered, it is a debuging aid, currently used in-house by people who have access to Vertica tools and can modify our source code. It seems to me that, with your last citations, you're fishing for information about triggers. I repeat, SQL triggers (in anything resembling what SQL users mean when,they use the term) *do not exist* in Vertica at this time. Sorry. We're not secretly hiding them; as of today (Vertica 7.1.0) they're just not there. (This is not a statement about our architecture, nor about future plans; simply about the functionality that we expose, documented or otherwise, via SQL at this time.) The terms "trigger" and "event" are both more-general terms. If we were documenting(/enabling) this functionality for public consumption, we might well choose a different naming scheme, just to avoid this sort of confusion. The observations here about event-based programming are apt; though I will note that Vertica (like any large program) is very much more complicated than can be describeded by any simple programming paradigm. Also -- I should note that Vertica's license contains a "no reverse engineering" clause. Please don't take my comments as an approval of the level of investigation that you have done so far; I don't have that authority. Thanks, Adam
  • Hi!
    it is a debuging aid, currently used in-house by people who have access to Vertica tools and can modify our source code.
    Acceptable.
    We're not secretly hiding them;
    Im not saying that, but a lot of things are undocumented and I can't understand it. For example: http://www.vertica.com/2014/05/06/inside-the-secret-world-of-the-workload-analyzer/
    Why its in blog and not in DOCs?

    PS
    And some  things in Support are annoying, like Case 00021304.
    If work around will not be documented I will post a lot of bugs, like: MATCH-PATTERN is non-deterministic (because bug is not fixed and I can't post a real reason). I have no other way to push on Support.

    https://community.vertica.com/vertica/topics/analytic_function_for_conditional_reset_cumulative_sum
    it is a closed data set! no loads, no data modification and if I will remove RLE from projections, so MATCH-PATTERN will be a deterministic(stable/immutable).
  • Hi Daniel,

    The Workload Analyzer is documented:

    https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunct...

    Regarding MATCH being non-deterministic -- ah, I hadn't noticed your note about RLE'd data before.  That's an interesting specific detail.  Can you specify which columns on that projection (and used anywhere in the MATCH expression) are RLE'ed?  The more specific you can be, the more easily we can address the issue.

    Thanks,
    Adam
  • Hi Adam!

    Have you access to HP Labs in Grenoble? I want give you a full access to data and so you can investigate it by yourself. Where can I leave a private message for you?
    (data will exists till 15/9/2014, after it data will be deleted).

    PS
    I don't ask your mail, I just want to leave a private message
    ([email protected])
    Im done with Vertica, so please ask any details before 15/9/2014.
  • Hi!

    >> you probably use the  strings command  method
    :)))) in strings too

    http://www.thegeekstuff.com/2012/03/reverse-engineering-tools/

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file