[Bucardo-general] Patch to Enable Filtering in bucardo_add_delta functions

[Greg Sabino Mullane]

> 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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 226 bytes
Desc: OpenPGP digital signature
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090421/9c727877/attachment.bin