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

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


  • Options
    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
  • Options

    >> 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

    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');
     Installed trigger
    (1 row)

    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?


    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.
  • Options
    Its not NO-OP:


    TuppleMover also based on triggers!

    static void Util::DebugTrigger::do_trigger(const std::string&, void*)

  • Options
    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) .


  • Options
    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
  • Options
    it is a debuging aid, currently used in-house by people who have access to Vertica tools and can modify our source code.
    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?

    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.

    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).
  • Options
    Hi Daniel,

    The Workload Analyzer is documented:


    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.

  • Options
    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).

    I don't ask your mail, I just want to leave a private message
    Im done with Vertica, so please ask any details before 15/9/2014.
  • Options

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


Leave a Comment

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