On 23 February 2015 at 10:35, Drazen Kacar <drazen.kacar@xxxxxxxxxxx> wrote: > > > On 23 February 2015 at 11:12, Simon Riggs <simon@xxxxxxxxxxxxxxx> wrote: >> >> On 23 February 2015 at 08:19, Drazen Kacar <drazen.kacar@xxxxxxxxxxx> >> wrote: >> >> > At the time they happened on the standby there was vacuuming of one >> > table participating in the select query on the primary. >> >> The VACUUM will have generated a WAL record that needs super exclusive >> access to the block. Since feedback was enabled that record would not >> have removed data visible by the query, but still needs to edit the >> block. >> >> The query was pinning that block, so this situation led to a delay on >> the standby, which then led to cancellation of the query. > > > OK, that makes sense. > > So if I increase max_standby_archive_delay (or set it to infinite) that > shouldn't happen? > > My problem with max_standby_archive_delay in this case is that I don't > understand relative priorities between obtaining a lock by queries and > obtaining a lock by vacuum (or other applications of WAL records). > > If the first query obtains a lock that the vacuum needs and I have > sufficiently large max_standby_archive delay, I suppose the vacuum will wait > and the query won't be cancelled. What happens if another query that needs a > lock on the same table comes in while vacuum is waiting? Is there a way to > guarantee that the vacuum (which blocks application of subsequent WAL > records, I assume) will be the first one to get the lock? If not, then (with > max_standby_archive_delay=-1) it's possible that the application of WAL > records could wait indefinitely. > > OTOH, if max_standby_archive_delay is finite and there is no way to > guarantee that vacuum gets its lock before other queries, then there's no > way to avoid query cancellation. > > Am I getting something wrong here? Yes, you are confusing block and relation level locks. The contention is at block level. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
