Triggers

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, 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
(setarckos@gmail.com)
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/