Re: [HACKERS] Logical decoding - filtering tables
On 3 January 2017 at 21:32, valeriof wrote: > Craig Ringer-3 wrote >> Take a look at how pglogical does it in its replication set handling >> and relation metadata cache. > > I checked it out but for what I understand it uses the inline parameter. It specifies which replication sets to use with logical decoding parameters. Those are in turn looked up in user-catalog tables to find mappings of relations to replication sets. A cache is used to avoid reading those tables for every tuple. > Would it be possible to store this info in some config table and then run a > select from inside the plugin? Yes, that's what pglogical does. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logical decoding - filtering tables
Craig Ringer-3 wrote > Take a look at how pglogical does it in its replication set handling > and relation metadata cache. I checked it out but for what I understand it uses the inline parameter. Would it be possible to store this info in some config table and then run a select from inside the plugin? -- View this message in context: http://postgresql.nabble.com/Logical-decoding-filtering-tables-tp5937103p5937144.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logical decoding - filtering tables
On 3 January 2017 at 17:11, valeriof wrote: > My question is if there is a way to call some sort of initializer where I > pass all the filtering data information beforehand, so that I can keep > calling the pg_logical_slot_get_binary_changes without having to > re-initialize this list all the time. If this is not possible, what other > options could I use? There isn't any such functionality at the moment. Each logical decoding plugin is supposed to supply its own. Take a look at how pglogical does it in its replication set handling and relation metadata cache. I would quite like to generalise this approach, but am currently more focused on things that cannot already be done without core changes. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Logical decoding - filtering tables
Hi, I'm developing a logical decoding plugin to extract changes from the database. This is working pretty well so far, but I have a question on the architectural aspect of the application. I want to filter changes based on the tables that are subscribed for CDC in my application and avoid network traffic for changes that don't involve these tables. To do that, I added a custom parameter in the call where I pass a list of table OIDs: SELECT * FROM pg_logical_slot_get_binary_changes ('replication_slot', NULL, 1000, 'version', '1', 'table-filter', '24625,16425,16411,24620,24612', 'diagnostic', '2') The problem is that I don't want to limit the function to a max number of tables and clearly if the list gets long this may be a problem - not sure if I could even hit the max allowed length of the SQL statement. Also, since this call will be executed continuously, the application will have to read and parse this long list every time. [BTW, I understand that creating multiple replication slots could reduce the size of this list, but I'm not convinced that creating thousands of slots can be the solution, considering the amount of resource that the db uses for each slot.] My question is if there is a way to call some sort of initializer where I pass all the filtering data information beforehand, so that I can keep calling the pg_logical_slot_get_binary_changes without having to re-initialize this list all the time. If this is not possible, what other options could I use? Thanks, Valerio -- View this message in context: http://postgresql.nabble.com/Logical-decoding-filtering-tables-tp5937103.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers