record import matching can fail with "shared memory" PostgreSQL errors

Bug #1271661 reported by Galen Charlton on 2014-01-22
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Evergreen
Undecided
Unassigned

Bug Description

Attempting to load a large number of bib records into a Vandelay import queue can stall and fail, with no records getting staged into the import queue. The following PostgreSQL errors are associated with this failure:

[4272-1] WARNING: out of shared memory
[4272-2] CONTEXT: SQL statement "DROP TABLE _vandelay_tmp_jrows"
[4272-3] #011PL/pgSQL function vandelay.match_set_test_marcxml(integer,text,integer) line 55 at SQL statement
[4272-4] #011PL/pgSQL function vandelay.match_bib_record() line 46 at FOR over SELECT rows
[4273-1] ERROR: out of shared memory
[4273-2] HINT: You might need to increase max_locks_per_transaction.
[4273-3] CONTEXT: SQL statement "DROP TABLE _vandelay_tmp_jrows"
[4273-4] #011PL/pgSQL function vandelay.match_set_test_marcxml(integer,text,integer) line 55 at SQL statement
[4273-5] #011PL/pgSQL function vandelay.match_bib_record() line 46 at FOR over SELECT rows
[4273-6] STATEMENT: INSERT INTO vandelay.queued_bib_record (id,create_time,import_time,marc,queue,bib_source,imported_as,import_error,error_detail,purpose,quality) VALUES (DEFAULT,DEFAULT,DEFAULT,'<record xmlns:xsi="http://www.w3.o

My diagnosis is that one or more locks get consumed each time the _vandelay_tmp_jrows and _vandelay_tmp_qrows temporary tables get created or dropped in the course of record matching by the vandelay.match_bib_record() trigger function. At some point, with a large enough batch of records loaded in one transaction, Pg will run out of space available for tracking table locks.

Here is the part of the Pg documentation that describes the Pg settings that influence the number of locks that can be tracked:

"The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects(e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table..."

Possible solutions:

- use a mechanism other than temporary tables to pass the jrow and qrow data around (e.g., rewrite the affected functions in PL/Perl and use the %_SHARED hash)
- decrease the number of tables touched in each transaction by committing after processing each record in the spool, and write application-side logic to handle failures of individual records

Evergreen: 2.5.1
PostgreSQL: 9.3

Galen Charlton (gmc) on 2014-01-22
tags: added: vandelay
tags: added: cataloging
description: updated
description: updated
Galen Charlton (gmc) wrote :

Another option would be using permanent unlogged tables rather than temporary tables.

Ben Shum (bshum) on 2014-02-16
Changed in evergreen:
status: New → Triaged
Mike Rylander (mrylander) wrote :

Not adding a pullrequest, because the UNLOGGED TABLE parts are intermingled with unrelated code, but as a pointer for later see the UNLOGGED bits in this branch: http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/miker/1304559_and_1271661_vandelay_fixes_with_upgrades

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers