Re: pg_dump blocks insert update on table

Also same time I checked backup logs.
It is at same table. Dumping data for stage.etl_logs.

On Thu, 29 Feb 2024, 23:03 Bindra Bambharoliya, <
bindra(dot)bambharoliya(at)gmail(dot)com> wrote:

> Hi,
> I executed below query and
>
> SELECT blocked_locks.pid AS blocked_pid,
> blocked_activity.usename AS blocked_user,
> blocking_locks.pid AS blocking_pid,
> blocking_activity.usename AS blocking_user,
> blocked_activity.query AS blocked_statement,
> blocking_activity.query AS current_statement_in_blocking_process
> FROM pg_catalog.pg_locks blocked_locks
> JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
> JOIN pg_catalog.pg_locks blocking_locks
> ON blocking_locks.locktype = blocked_locks.locktype
> AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
> AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
> AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
> AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
> AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
> AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
> AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
> AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
> AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
> AND blocking_locks.pid != blocked_locks.pid
>
> JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
> WHERE NOT blocked_locks.granted;
>
> Output I got is blocked_statement--call etl_master();
>
> current_statement_in_blocking_process-- copy stage.etl_logs(....) to
> stdout.
>
> Hope this helps. Let me know if more details required..
>
>
> Thanks & Regards
> Bindra Bambharoliya
>
>
> On Thu, 29 Feb 2024, 22:45 Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Bindra <11bindra11(at)gmail(dot)com> writes:
>> > We are using PostgreSQL 11.17
>>
>> You realize that 11.x is out of support, right?
>>
>> > While doing pg_dumpall, it blocks insert/update on some table where copy
>> > statemnets is executed.
>>
>> pg_dump only takes AccessShareLock on tables it dumps. That does not
>> block inserts or updates. What may be happening is that you have some
>> third process that is trying to take an exclusive lock on the table.
>> It will queue up behind pg_dump's nonexclusive lock, and then other
>> operations such as insert/update will block behind it. This is just
>> speculation since you've provided zero detail, but that's what I'd
>> look for. The pg_locks view could help you identify the culprit.
>>
>> regards, tom lane
>>
>>
>>