Thread: [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.

On 3 January 2017 at 17:11, valeriof <valerio_farruggio@hotmail.com> 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

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.

On 3 January 2017 at 21:32, valeriof <valerio_farruggio@hotmail.com> 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