synchronization problems with COPY on connection failure

As a heads up to anyone that may be using COPY, we ran into some
problems with COPY synchronization on connection failure. I'm not
quite sure how extensive they are yet, but here's the sequence that
causes a problem:

1. Start a COPY IN query
2. Break the connection (I'm not quite sure if you need to break the
network connection or just bring down PostgreSQL; I think either
should work but I'm not positive)
3. Cancel the COPY. This will try to send a CopyFail to the backend,
but get a SocketException and propagate it wrapped in a PSQLException
4. Catch the exception, and try to execute a query

This will hang on waitOnLock() in QueryExecutorImpl, since the failing
cancelCopy() never released its lock (the long-lived COPY lock) on the
connection. This is typically reset on an RFQ, except that in this
case, there are no more RFQs because the connection is dead. So a
ghost COPY lock is held by a failed COPY operation, and most other
connection operations will block indefinitely.

A workaround is to check the exception cause and avoid further queries
if the connection is borked, but since failing to do that leads to a
hung thread, it would be nice to have more robust behavior here.

Unfortunately, moving the current COPY synchronization mechanism to
something more robust would take some doing (I tried before in
attempting to resolve the Terminate-during-COPY issue), and the
RFQ-based unlocking is a reasonable approach 99.9% of the time. A
possible approach would be to keep the current synchronization
mechanism, but release locks on connection failure. All COPY
operations already handle IOExceptions specially, so I think this
would be a fairly minor change.

Any thoughts?

Thanks,
---
Maciek Sakrejda | Software Engineer | Truviso

1065 E. Hillsdale Blvd., Suite 230
Foster City, CA 94404
(650) 242-3500 Main
(650) 242-3501 F
www.truviso.com