Feedback-ID: ia2694551:Fastmail
Date: Tue, 13 Sep 2022 13:11:14 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Leandro Bugalho <leandrohb@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Error when execute insert/update
Message-ID: <20220913111114.h6ksiclfj6e2nnrf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAExYQ-pMgOt1V-oDjoa+bv=xfEM2PfnaHkPTiwr9oSv-_w1ONQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220913111114.h6ksiclfj6e2nnrf%40alvherre.pgsql>
Precedence: bulk

On 2022-Sep-11, Leandro Bugalho wrote:

> Hi guys, I have a PostgreSQL 9.5 server, which started showing the
> following message in the log:
> 
> ,ERROR,54000,"multixact ""members"" limit exceeded","This command
> would create a multixact with 2 members, but the remaining space is
> only enough for 0 members.","Run a database-wide VACUUM in database
> with OID 139059 with reduced vacuum_multixact_freeze_min_age and
> vacuum_multixact_freeze_table_age settings.",,,"SQL statement ""SELECT
> 1 FROM ONLY ""public"".""alf_server"" x WHERE ""id""
> OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x""","insert into
> 
> I started the vacuum in the database , but the same for the size ,I
> believe it will take a long time. Is there any way to increase this
> limit member while the vacuum is running to free up the application?

Sorry I'm late to the party.  I hope you're already out of this hole.
So this reply is for the archives' sake.

There's no configuration parameter you can change that will help you in
this case.

One way out of this that's perhaps a tad quicker than vacuuming the
entire database, is vacuuming just the table(s) that have the oldest
multixacts.  If you're lucky, it's just a not-so-big table that can be
dealt with quicker than the entire database.
You're looking for the table(s) with the highest values of
mxid_age(pg_class.relminmxid).  Have VACUUM process them successfully
and their relminmxid will advance to a current value, and that will
enable VACUUM to move the global minmxid limit forward and delete the
oldest multixact data, giving some extra room for new multixacts to be
created.

Now, it is suprising that autovacuum didn't already did this by itself.
It definitely should have.  However, there are some problem cases:

Sometimes, this is caused by tables that have corrupted data; those
cause vacuum to terminate with an error without advancing the limit, so
your database as a whole is stuck in old-multixactid land.  If you can
fix the corruption and let vacuum complete, that will work.  If you
cannot, you could just DROP the table.  If it has valuable data, maybe
you can CREATE TABLE AS SELECT FROM corrupt_table, with enough smarts to
avoid falling over due to the corruption.

Another cause we've seen is leftover temp tables: autovacuum cannot
process them because temp tables cannot be read/written by sessions
other than the one that created them.  What you can do in this case is
just drop those temp tables.  We added some additional protections for
this case in Postgres 10, but 9.5 still had some holes.  This is
particularly problematic if the server crashes and there is some session
that is using temp tables, and afterwards the session ID is not reused.
Those temp tables would be dropped at startup by another session that
uses the same ID, but if --say-- the ID is very high and you don't have
that many sessions again afterwards, they won't.

(If you do DROP any tables, you will need to vacuum some other table
afterwards, to let the code that advances the minimum have a chance to
run.)

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)