CODEWITHSUNDEEP
postgresqldbeaver
Luis
Luis

Reputation: 75

Encountering a SQL Error 40001 in Dbeaver

I'm doing a count * query that executes correctly but for some reason when I attempt to export it to CSV, I'm encountering a SQL Error [40001]. Any ideas what the issue could be?enter image description here

Upvotes: 1

Views: 1473

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247330

You are running a long query on a standby server, and some modifications that are replicated from the primary server conflict with your query. In particular, VACUUM removed some old row versions that your query still might want to use.

PostgreSQL has to make a choice: either delay applying the changes from the primary, or cancel the query that blocks replication.

How PostgreSQL behaves is determined by the parameter max_standby_streaming_delay. The default value gives the query 30 seconds to finish before it is canceled.

You have three options:

  1. Retry the query and hope it succeeds this time.

  2. Increase max_standby_streaming_delay on the standby.

    The risk you are running is that replication will fall behind.

  3. Set the parameter hot_standby_feedback to on on the standby, then the primary won't VACUUM row versions the standby might still need.

    The risk you are running is table bloat on the primary, because autovacuum cannot do its job.

Upvotes: 1

Related Questions