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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general