Triggers

Daniel_Leybovic

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

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

[Deleted User]

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

Daniel_Leybovic

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.

Daniel_Leybovic

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

eli_revach

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 

[Deleted User]

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

Daniel_Leybovic

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

[Deleted User]

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

Daniel_Leybovic

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
(setarckos@gmail.com)
Im done with Vertica, so please ask any details before 15/9/2014.

Daniel_Leybovic

Hi!

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

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