Amazon Q sevice feedback: Hallucination

[gabclau]

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.

• https://aws.amazon.com/blogs/database/multixacts-in-postgresql-usage-side-effects-and-monitoring/
• https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockmultixact.html
• https://www.postgresql.org/docs/current/routine-vacuuming.html

[justinmk3]

Thanks for the feedback. This issue tracker is for the Amazon Q extension for VSCode. We have forwarded your feedback to the Amazon Q service team, however, feedback on specific Q prompt results will reach them directly by using the thumbs-up/down buttons.

Clicking thumbs-down reveals a Report an issue button, where you can provide more details: