Open
Description
Amazon Q Hallucination:
The answer provided by Amazon Q below is incorrect.
Question:
Give me a short explanation about the PostgreSQL error "multixact members limit exceeded"
Amazon Q:
The "multixact members limit exceeded" error in PostgreSQL occurs when too many concurrent transactions try to place shared locks on the same rows. This typically happens in three main scenarios:
Multiple concurrent SELECT FOR SHARE operations
Heavy foreign key reference checking
Sub-transactions using savepoints
Quick example of what can trigger it: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockmultixact.html
-- Multiple sessions running simultaneously
Session 1: SELECT * FROM orders WHERE id = 100 FOR SHARE;
Session 2: SELECT * FROM orders WHERE id = 100 FOR SHARE;
Session 3: SELECT * FROM orders WHERE id = 100 FOR SHARE;
Copy
Insert at cursor
sql
To fix this, you can:
Reduce transaction duration
-- Use shorter transactions
BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;
COMMIT;
Copy
Insert at cursor
sql
Perform regular maintenance
-- Regular VACUUM
VACUUM VERBOSE orders;
-- More aggressive cleanup when needed
VACUUM (VERBOSE, FREEZE) orders;
Copy
Insert at cursor
sql
Adjust PostgreSQL settings
-- Increase limits in postgresql.conf
max_locks_per_transaction = 128 -- Default is 64
autovacuum_multixact_freeze_max_age = 1000000000
Copy
Insert at cursor
sql
The key is to:
Keep transactions short
Use regular VACUUM maintenance
Monitor lock activity
Consider using NOWAIT or timeout options
Implement proper connection pooling
Question:
I just want a short and crisp explanation
Amazon Q:
The "multixact members limit exceeded" error occurs in PostgreSQL when too many concurrent transactions attempt to place shared locks on the same rows. This typically happens during heavy concurrent operations with SELECT FOR SHARE statements, foreign key checks, or when using multiple savepoints in transactions. To resolve it, perform regular VACUUM maintenance, keep transactions short, and consider increasing the max_locks_per_transaction parameter.
Question:
what is the limit for multixact members
Amazon Q:
The multixact members limit in PostgreSQL is determined by the formula:
Maximum members = MaxBackends × MaxLocksPerTransaction
Copy
Insert at cursor
text
Where:
MaxBackends is controlled by max_connections (default varies by system)
max_locks_per_transaction (default is 64)
For example:
If max_connections = 100
max_locks_per_transaction = 64
Maximum multixact members = 100 × 64 = 6,400
You can check your current settings with:
SHOW max_connections;
SHOW max_locks_per_transaction;
Copy
Insert at cursor
sql
To increase the limit, you can:
Increase max_locks_per_transaction in postgresql.conf
Restart PostgreSQL for changes to take effect
Note: Increasing these values will consume more shared memory, so adjust carefully based on your system resources.
The above shows a clear indication of hallucination. None of the reliable sources gives anywhere near the above explanation.