Re: locking [user] catalog tables vs 2pc vs logical rep

On Wed, Mar 31, 2021 at 5:47 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, Mar 31, 2021 at 2:35 PM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
> >
> > The patch applies fine on HEAD and "make check" passes fine. No major comments on the patch, just a minor comment:
> >
> > If you could change the error from, " cannot PREPARE a transaction that has a lock on user catalog/system table(s)"
> > to "cannot PREPARE a transaction that has an exclusive lock on user catalog/system table(s)" that would be a more
> > accurate instruction to the user.
> >
>
> Thanks for reviewing the patch.
> Please find the updated patch which includes the fix for the same.

This similar problem exists in case of synchronous replication setup
having synchronous_standby_names referring to the subscriber, when we
do the steps "begin;lock pg_class; insert into test1 values(10);
commit". In this case while decoding of commit, the commit will wait
while trying to acquire a lock on pg_class relation, stack trace for
the same is given below:
#4 0x0000556936cd5d37 in ProcSleep (locallock=0x556937de8728,
lockMethodTable=0x5569371c2620 <default_lockmethod>) at proc.c:1361
#5 0x0000556936cc294a in WaitOnLock (locallock=0x556937de8728,
owner=0x556937e3cd90) at lock.c:1858
#6 0x0000556936cc1231 in LockAcquireExtended (locktag=0x7ffcbb23cff0,
lockmode=1, sessionLock=false, dontWait=false, reportMemoryError=true,
locallockp=0x7ffcbb23cfe8)
at lock.c:1100
#7 0x0000556936cbdbce in LockRelationOid (relid=1259, lockmode=1) at lmgr.c:117
#8 0x00005569367afb12 in relation_open (relationId=1259, lockmode=1)
at relation.c:56
#9 0x00005569368888a2 in table_open (relationId=1259, lockmode=1) at table.c:43
#10 0x0000556936e90a91 in RelidByRelfilenode (reltablespace=0,
relfilenode=16385) at relfilenodemap.c:192
#11 0x0000556936c40361 in ReorderBufferProcessTXN (rb=0x556937e8e760,
txn=0x556937eb8778, commit_lsn=23752880, snapshot_now=0x556937ea0a90,
command_id=0, streaming=false)
at reorderbuffer.c:2122
#12 0x0000556936c411b7 in ReorderBufferReplay (txn=0x556937eb8778,
rb=0x556937e8e760, xid=590, commit_lsn=23752880, end_lsn=23752928,
commit_time=672204445820756,
origin_id=0, origin_lsn=0) at reorderbuffer.c:2589
#13 0x0000556936c41239 in ReorderBufferCommit (rb=0x556937e8e760,
xid=590, commit_lsn=23752880, end_lsn=23752928,
commit_time=672204445820756, origin_id=0, origin_lsn=0)
at reorderbuffer.c:2613
#14 0x0000556936c2f4d9 in DecodeCommit (ctx=0x556937e8c750,
buf=0x7ffcbb23d610, parsed=0x7ffcbb23d4b0, xid=590, two_phase=false)
at decode.c:744

Thoughts?

Regards,
Vignesh