agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Viral Shah <viralshah009@gmail.com>
Cc: Rene Romero Benavides <rene.romero.b@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg_dump fails when a table is in ACCESS SHARE MODE
Date: Fri, 01 May 2020 10:18:08 -0400
Message-ID: <17875.1588342688@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEVFvu3N4sANuPzWuvN-UmPP--mphPM3MiXMtQhgH+WctSjq5A@mail.gmail.com>
References: <CAEVFvu0aY4qgxVtMJvPTqty0pmL-jqsbsY9DkmA8REDbQtwKcw@mail.gmail.com>
<CANaGW086GF5qioi8=R0p6-Hh=Y-p4gsxr4FCYi9eHC5TzCgr+A@mail.gmail.com>
<CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com>
<CANaGW0-XmCzJuME3EqQjO=g=hWv45v345pE8F-kVMygW2fBTJA@mail.gmail.com>
<CANaGW0_cQHOBQ9fLKwc8qEObBvpP_Ocy=mNuzF0h9dfWfxVevw@mail.gmail.com>
<CAEVFvu3gDgSOayvOxHXe7e0=6w_6WhQk4m9S=gz5iHvpT0DmDg@mail.gmail.com>
<CANaGW0_Rps9JtszHMei3seN_kSt_WQhNY6Ch=2qTPuM_B1MB+Q@mail.gmail.com>
<CAEVFvu3N4sANuPzWuvN-UmPP--mphPM3MiXMtQhgH+WctSjq5A@mail.gmail.com>
Viral Shah <viralshah009@gmail.com> writes:
> Per the postgres documentation,
> *max_locks_per_transaction * (max_connections + max_prepared_transactions)*
> this formula determines the max no of allowed objects that can be locked on
> the database. Currently my database has the following values:
> *max_locks_per_transaction = 64 (default)*
> *max_connections = 100*
> *max_prepared_transactions = 0*
> Using this value in the above formula tells that our database or rather
> postgres server can/should handle *6400* locked objects at a time.
Right.
> What is surprising is why Postgres complains of insufficient locks per
> transaction if only 10 processes (parallel jobs in pg_dump) are running on
> the database while taking the dump.
They're taking more than 6400 locks, evidently. How many tables are
in your database? Have you tried looking into pg_locks while the dump
is running?
regards, tom lane
view thread (11+ messages) latest in thread
Message-ID: <17875.1588342688@sss.pgh.pa.us>
Permalink: ../17875.1588342688@sss.pgh.pa.us/
Also on: postgresql.org/message-id/17875.1588342688@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, viralshah009@gmail.com, rene.romero.b@gmail.com
Subject: Re: pg_dump fails when a table is in ACCESS SHARE MODE
In-Reply-To: <17875.1588342688@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox