Postgresql – Question regarding PSQLException: User was holding shared buffer pin for too long

amazon-rdspostgresql

I recently encountered a strange error that I don't quite understand what could cause it:

caused by org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery Detail: User was holding shared buffer pin for too long.

I have a fixed timeout 10s set for each query, so any long run query should get terminated and the error message I expected to see User query get cancelled by user request. However, I see this sometimes, but the above as well which is really confusing. Furthermore, it only show up when I see high lag from our RDS replica, then resolve itself after 5, 10 minutes.

I tried to reproduce this issue locally but no luck. Any thought would be greatly appreciated!

Best Answer

If the replay process is already behind by more than max_standby_streaming_delay (or max_standby_archive_delay), then it will immediately terminate anything which is in its way. If it is behind by less, then it is willing to wait only until it reaches that delay. In other words, the max_standby_streaming_delay sets a grace period for the entire system, not for each individual action within the system.

So if max_standby_streaming_delay is set to 30 seconds, but the replay process is already 28 seconds behind, then "too long" to hold a buffer pin could be 2 seconds. If is already more than 30 seconds behind, then a few nanoseconds could be "too long"--just long enough to detect the blockage and then send, receive, and process a signal.

Related Solutions

PostgreSQL 9.3 – Deleting Many Rows in Chunks Without Long Locks

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.

Each individual row that is being deleted will be locked for the duration. This should only block other processes if those other processes are trying to update the rows (or delete them themselves). Normally with bulk deletions you are deleting rows that no one else cares about, which means no one else will be trying to update them.

If you really need to unlock all the deleted rows and the table periodically, then you need to do each loop of the deletions in a separate transaction. There is no easy way to do this in a DO block because the entire block runs in a single transaction. So you should put your loop outside of PostgreSQL, like in bash, perl, python, etc.

PostgreSQL Error – Value Too Long for Type Character Varying(5)

So first, what's the difference..

SELECT x, length(x)
FROM ( VALUES
  ('Cincinnati'),
  ('San Francisco')
) AS t(x);

Here is the output

       x       | length 
---------------+--------
 Cincinnati    |     10
 San Francisco |     13

So..

San Francisco is three characters longer.
They're both over 5 characters.
That can't be the problem.

And further, if Cincinnati was in a varchar(5), it'd have to get truncated.

So the problem is your cityid. It is varchar(5). you probably want that to be an int anyway -- it'll be more compact and faster. So ALTER the table and fix it.

ALTER TABLE cities
  ALTER COLUMN cityid SET DATA TYPE int
  USING cityid::int;

As a side note... maybe someday PostgreSQL will speak column names in error messages. until then at least it's more verbose than SQL Server.