Thread: BUG #1600: Interrupt routine causes libPQ call to fail w/o error

The following bug has been logged online:

Bug reference:      1600
Logged by:          David McClellan and Alun Prytherch
Email address:      David.McClellan@ateb.com
PostgreSQL version: 8.0.1
Operating system:   Linux (RedHat 9)
Description:        Interrupt routine causes libPQ call to fail w/o error
Details:

We are using a Postgres database on Linux to service both a webapp and a
backend process. The backend process is written in GCC-compile standard C,
and uses libpq to query, insert and update records in the database. It is a
pharmacy-service app, and also interfaces with DialLogic cards to make phone
calls. The DialLogic cards support event-callback routines and will
interrupt the background process with callbacks, even during the process's
database calls.

It appears as though a system interrupt occurred at some point while doing a
database insert, and the result was:
a) the database insert did not happen
b) there did not appear to be any PQ/DB errors reported back from the libPQ
code.


Here is the normal sequence of events on the correct inserts ...

->RDBJOBQResultProps (our routine)
->pgres memory alloc orig=832d568 now=83468d8
->pgres memory alloc orig=832d568 now=83468d8
->PQexec[PGRES_COMMAND_OK/INSERT INTO
contact_result_prop(item_id,block_id,tag,value)]
->PGRES_COMMAND_OK [1] rows affected
->pgres memory alloc orig=832d568 now=83468d8
->PQexec[PGRES_COMMAND_OK/COMMIT]


Here is the sequence that failed to insert the expected database record
...

->RDBJOBQResultProps
->pgres memory alloc orig=832d568 now=83468f0
->setting a low ... (4 Interrupted system call)
->pgres memory alloc orig=832d568 now=83468d8
->PQexec[PGRES_COMMAND_OK/COMMIT]

Notes:
- The sequences above are extracted from our event files and abbreviated to
avoid cluttering with unrelated data.
- The "pgres memory alloc" lines come each time we do a PQexec.
- We are running over a remote DB connection (Linux to Linux) using a
connections string  like "host=dbhost port=5432 dbname=xxx password=yyy".

The outline code for what happens in RDBJOBQResultProps is ...

    pgres = PQexec(pgconn, "BEGIN");
    PQclear(pgres);/* clear result */

        <sql> =
        * INSERT INTO contact_result_prop
        * (item_id, block_id, tag, value)
        * VALUES(my_item_id,my_block_id,'my_tag','my_val');
        pgres = PQexec(pgconn, "<sql>");
        PQclear(pgres);/* clear result */

    pgres = PQexec(pgconn, "COMMIT");
    PQclear(pgres);/* clear result */

---------------------------------------------------

Has this been seen before, and is there any way we can either
 1) Ensure the PQexec calls return an error when they fail due to an
interrupt?
or
 2) Insulate the PQexec calls from callback interrupts

Thanks - Dave and Alun

"David McClellan and Alun Prytherch" <David.McClellan@ateb.com> writes:
> We are using a Postgres database on Linux to service both a webapp and a
> backend process. The backend process is written in GCC-compile standard C,
> and uses libpq to query, insert and update records in the database. It is a
> pharmacy-service app, and also interfaces with DialLogic cards to make phone
> calls. The DialLogic cards support event-callback routines and will
> interrupt the background process with callbacks, even during the process's
> database calls.

Perhaps one of your interrupt routines tried to do something with the
database connection?  That certainly won't work safely.

            regards, tom lane