[Bucardo-general] Bucardo Custom Triggers, RE: Patch to Enable Filtering in bucardo_add_delta functions

[Ben Allen]

Here is what I've come up with for a better custom trigger  
implementation. I've added a new table in the bucardo DB named  
bucardo_custom_trigger as follows:

id - row id
goat - FK to goat table
trigger_name - random descriptive name of row. Not used anywhere in  
code.
trigger_type - Either 'triggerkick' or 'delta'
trigger_language - Defaults to plpgsql. Is inserted in the function  
dynamically as such:
	CREATE OR REPLACE FUNCTION bucardo.$func1()
	RETURNS TRIGGER
	LANGUAGE $rv->{rows}[0]{trigger_language}
trigger_body - The actual code of the function, i.e. everything right  
after the AS. So your code needs to start with BEGIN and end with END.  
Trigger_body is wrapped in two $$ so no need to include them.
trigger_level - Used for the triggerkick custom trigger. Can be set to  
either ROW or STATEMENT. Used to set triggerkick's to ROW if you need  
additional info to work with. Note: 'delta' custom triggers don't pay  
attention to this value.
status - Only 'active' rows are used.

For triggerkick triggers a new function is created with the default  
$func0 appended with _$info->{tablename}.

I've only done limited testing with this code, so let me know if you  
see anything amiss. It appears to work in my specific schema and  
custom trigger entries (see attachments).

GitHub soon? ;-)

Regards,

Ben Allen

-------------- next part --------------
A non-text attachment was scrubbed...
Name: bucardo_custom_trigger.patch
Type: application/octet-stream
Size: 5945 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090506/41928c5c/attachment.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: set_lookup_custom_triggerkick.sql
Type: application/octet-stream
Size: 502 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090506/41928c5c/attachment-0001.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: bucardo_add_delta_custom.sql
Type: application/octet-stream
Size: 1032 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090506/41928c5c/attachment-0002.obj 
-------------- next part --------------

On Apr 21, 2009, at 6:04 PM, Greg Sabino Mullane wrote:

>> I'm wondering if anyone can think up a better way or place of doing
>> filtering of data. I've explored using PostgreSQL Views and Bucardo
>> customcode and found no real logical way of doing it. By filtering  
>> it at
>> the bucardo_add_delta function its eliminating any additional network
>> traffic and processing from Bucardo. Thoughts?
>
> I think your approach is probably the best one.
>
>> To solve the problem Meg and I were having with being able to  
>> filter out
>> data from a table (goat) from being synced I've added a goatfilter  
>> table
>> to the bucardo database and modified the validate_sync function. The
>> table has an id, status, FK to the goat table, and a src_code column.
>
> First thing I'd recommend is renaming that to something more  
> intuitive - maybe
> custom_goat_delta_trigger or something?
>
>> - The src_code of goatfilter totally replaces the default function
>> source code if it exists. This is a bit clunky as you have to store  
>> the
>> entire function in the src_code column.
>>    - Maybe instead of writing out the entire function, we could  
>> insert
>> just a chunk of code into the already existing code. If you look at  
>> the
>> example function you can see we only added code at the beginning of  
>> the
>> function.
>
> I think storing the whole thing is the way to go, as it allows for  
> maximum
> flexibility in the function. In other words, as long as we're  
> putting custom
> code in there, let's also allow it to do other things in the future,  
> and not
> assume that it always has to end with the IF TG_OP tree.
>
>> - Even though the data we want filtered is not added to the
>> bucardo_delta table the sync is still kicked when filtered data is
>> touched in the database.
>>    - Fixing this would involve doing filtering in the
>> bucardo_triggerkick function.
>
> That should be as simple as adding a matching custom trigger with  
> the same name,
>  inside a table such as custom_goat_notify_trigger. I'm tempted to  
> shove those
> together into a single table as well. I'd also advocate only setting  
> the body
> itself for these replacement triggers, and leaving all the rest the  
> same. Most
> importantly, there are many parts of Bucardo that depend on the  
> triggers having
> somewhat consistent names, so that could be a tricky part that may  
> require us
> specifying the name (or part of it). We could allow other languages  
> though, as
> another column in the table. So it's starting to look something like  
> this:
>
> CREATE TABLE bucardo_custom_trigger (
>  id INT sequence..blahblah
>  goat ID FK..blahblah
>  trigtype TEXT (CHECK 'delta', 'triggerkick')
>  trigger_name (or just 'bucardo_add_delta_custom_<oid>?)
>  trigger_language TEXT DEFAULT 'plpgsql'
>  trigger_body TEXT
>  status TEXT
>  cdate TZ
> )
>
>> - Current modifications to the validate_sync method takes just the  
>> first
>> row from the select statement on the goatfilter table for a given
>> goat.id. There's no validation that only one entry is created in the
>> goatfilter table per goat.
>
> Simple enough to throw a unique index on the goat and trigtype  
> columns in there,
> if I  understand you correctly here.
>
> One caveat with a custom triggerkick replacement: it currently fires  
> at the
> statement level, but a custom one would need to fire at the row  
> level to examine
> rows of interest, and thus would have some overhead. Postgres is  
> nice enough to
> consolidate multiple NOTIFY calls into a single one on commit at  
> least.
>
> -- 
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
>