Re: Watching Views - Mailing list pgsql-general

Nick Guenther wrote
> As you said, attaching the trigger to a view is useless (for
> BEFORE/AFTER, which I'm interested in, also only works on statement
> level changes, which I would rather not have to deal with). I tried
> attaching my trigger to a materialized view and found that postgres
> does not support that; as you said, I need to write a trigger on the
> source to keep the materialized view and the source in sync. But in
> that case I don't think a materialized view helps me at all, since
> without triggers on it I can't even have it compute the diffs for me
> (and I suspect that internally postgres simply truncates the old table
> and refills it, so there would be nothing to hook anyway).
>
> My bottleneck is the size of my database and the network: I want to
> take slices of a potentially gigabytes-large database and stream them
> out to multiple clients. Thus I need to stream--not poll--for changes.
> I think a materialized view would force me to poll for changes, and in
> that case I would be doing REFRESH MATERIALIZED VIEW on each poll and
> therefore not winning anything over a regualar view. Ideally, when an
> update comes in to a parent table I want all views that have sliced it
> to be notified; I'm interested in doing dataflow* programming,
> essentially. Is there maybe some sort of extension that does
> dependency tracking inside of postgres?

While PostgreSQL has materialized view functionality built in the concept is
general and can be done manually.  Instead of the main table having the link
to the FIFO I was thinking you would instead replicate record changes to all
active subset tables and then triggers on those tables would send the
relevant changes out to the world.

Keep in mind you can attach a where clause to your trigger, and I think you
can pass in arguments to it as well.  You should have on trigger per view
attached to the source table - though with good meta-data and some imposed
limits you can probably pool some clients into the same stream.

Database size is less an issue compared to the change rate of the affected
table(s).  Triggers let you plug into the change stream.

You could even cascade the triggers so less restrictive filters are grouped
together at one layer and those materialized views then forward to other
tables with more restrictive filters.

If you make these other tables UNLOGGED you should be able to mitigate the
performance hit somewhat.

Beyond that if your views have common and reasonably broad high-level
filters you should consider both in-database partitioning and multiserver
sharding.

The client, not the trigger, should create the FIFO.  If the FIFO is
unusable the trigger should update a control table and a monitor process
should remove that trigger at the next scan.  This should then be extended
to provide control over the addition and removal of viewers and their
corresponding schematic objects.

The view tables also help avoid the problem since then even if a FIFO write
fails you have an idea of what should have been, but was not, written and
can cause it to be written later once the client is listening.

Before stock 9.4 triggers are all you got.  9.4 gives logical wal but no
clue how that works.  There my be solutions via third-party tools like Slony
but I am not familiar with them but they focus on direct database
replication.

Mostly theorizing as I've never actually coded this kind of process...so
some of these ideas may not pan out.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Watching-Views-tp5811927p5812680.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.