Horton
Menu

Re: Column Filtering in Logical Replication

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Justin Pryzby <pryzby@telsasoft.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Smith <smithpb2250@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, "shiy.fnst@fujitsu.com" <shiy.fnst@fujitsu.com>
Date: 2022-03-18T17:12:20Z
Lists: pgsql-hackers 

On 3/18/22 15:43, Tomas Vondra wrote:
> 
> 
> On 3/18/22 06:52, Amit Kapila wrote:
>> On Fri, Mar 18, 2022 at 12:47 AM Tomas Vondra
>> <tomas.vondra@enterprisedb.com> wrote:
>>>
>>> I pushed the second fix. Interestingly enough, wrasse failed in the
>>> 013_partition test. I don't see how that could be caused by this
>>> particular commit, though - see the pgsql-committers thread [1].
>>>
>>
>> I have a theory about what's going on here. I think this is due to a
>> test added in your previous commit c91f71b9dc. The newly added test
>> added hangs in tablesync because there was no apply worker to set the
>> state to SUBREL_STATE_CATCHUP which blocked tablesync workers from
>> proceeding.
>>
>> See below logs from pogona [1].
>> 2022-03-18 01:33:15.190 CET [2551176][client
>> backend][3/74:0][013_partition.pl] LOG:  statement: ALTER SUBSCRIPTION
>> sub2 SET PUBLICATION pub_lower_level, pub_all
>> 2022-03-18 01:33:15.354 CET [2551193][logical replication
>> worker][4/57:0][] LOG:  logical replication apply worker for
>> subscription "sub2" has started
>> 2022-03-18 01:33:15.605 CET [2551176][client
>> backend][:0][013_partition.pl] LOG:  disconnection: session time:
>> 0:00:00.415 user=bf database=postgres host=[local]
>> 2022-03-18 01:33:15.607 CET [2551209][logical replication
>> worker][3/76:0][] LOG:  logical replication table synchronization
>> worker for subscription "sub2", table "tab4_1" has started
>> 2022-03-18 01:33:15.609 CET [2551211][logical replication
>> worker][5/11:0][] LOG:  logical replication table synchronization
>> worker for subscription "sub2", table "tab3" has started
>> 2022-03-18 01:33:15.617 CET [2551193][logical replication
>> worker][4/62:0][] LOG:  logical replication apply worker for
>> subscription "sub2" will restart because of a parameter change
>>
>> You will notice that the apply worker is never restarted after a
>> parameter change. The reason was that the particular subscription
>> reaches the limit of max_sync_workers_per_subscription after which we
>> don't allow to restart the apply worker. I think you might want to
>> increase the values of
>> max_sync_workers_per_subscription/max_logical_replication_workers to
>> make it work.
>>
> 
> Hmmm. So the theory is that in most runs we manage to sync the tables
> faster than starting the workers, so we don't hit the limit. But on some
> machines the sync worker takes a bit longer, we hit the limit. Seems
> possible, yes. Unfortunately we don't seem to log anything when we hit
> the limit, so hard to say for sure :-( I suggest we add a WARNING
> message to logicalrep_worker_launch or something. Not just because of
> this test, it seems useful in general.
> 
> However, how come we don't retry the sync? Surely we don't just give up
> forever, that'd be a pretty annoying behavior. Presumably we just end up
> sleeping for a long time before restarting the sync worker, somewhere.
> 

I tried lowering the max_sync_workers_per_subscription to 1 and making
the workers to run for a couple seconds (doing some CPU intensive
stuff), but everything still works just fine.

Looking a bit closer at the logs (from pogona and other), I doubt this
is about hitting the max_sync_workers_per_subscription limit. Notice we
start two sync workers, but neither of them ever completes. So we never
update the sync status or start syncing the remaining tables.

So the question is why those two sync workers never complete - I guess
there's some sort of lock wait (deadlock?) or infinite loop.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching