The following bug has been logged on the website:
Bug reference: 15379
Logged by: Daisuke Ando
Email address: do.daisuke@gmail.com
PostgreSQL version: 9.5.14
Operating system: CentOS6 64bit
Description:
When an error occurs in `SELECT FOR UPDATE` using an index, release process
of the index access method is not called.
How to reproduce :
Prepare an indexed table.
```
CREATE TABLE a (
id int
);
CREATE INDEX b ON a (id);
```
Open one connection and insert a record.
```
INSERT INTO a VALUES (1);
```
Open another connection and lock the record.
```
-- another connection
BEGIN;
SELECT * FROM a WHERE id = 1 FOR UPDATE;
-- keep connecting without leaving
```
The first connection is locked in the same way, but if it can not be locked
with NOWAIT, it makes an error.
```
-- first connection(insert record)
BEGIN;
SELECT * FROM a WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "a"
```
At this time, the index access method ambeginscan() is called but since
amendscan() is not called, ambeginscan() can not properly release the
resource set in IndexScanDesc::opaque.
Document:
https://www.postgresql.org/docs/9.5/static/index-functions.html
In this example we use the btree index so the corresponding ambeginscan() is
btbeginscan() and amendscan() is btendscan().
If you set a breakpoint for these two functions in the debugger, it is
confirmed that there is not enough call to btendscan() once when an error
occurs (the number of calls to btendscan() is less than the number of calls
to btbeginscan()) it can.
Solution:
For example, if an error occurs while calling PortalRun() in
exec_simple_query() of src/backend/tcop/postgresq.c, amendscan() will not be
called because PortalDrop() will not be called. Therefore, it is good to
postpone even when an error occurs as follows.
```
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7a9ada2c71..daa6228535 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1116,16 +1116,28 @@ exec_simple_query(const char *query_string)
*/
MemoryContextSwitchTo(oldcontext);
- /*
- * Run the portal to completion, and then drop it (and the receiver).
- */
- (void) PortalRun(portal,
- FETCH_ALL,
- true, /* always top level */
- true,
- receiver,
- receiver,
- completionTag);
+ PG_TRY();
+ {
+ /*
+ * Run the portal to completion, and then drop it (and the receiver).
+ */
+ (void) PortalRun(portal,
+ FETCH_ALL,
+ true, /* always top level */
+ true,
+ receiver,
+ receiver,
+ completionTag);
+ }
+ PG_CATCH();
+ {
+ receiver->rDestroy(receiver);
+
+ PortalDrop(portal, false);
+
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
receiver->rDestroy(receiver);
```