1

I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed the result set of the former one is fetched. Below a simplified sketch:

while(true) {
    PreparedStatement stmt = //...
    ResultSet rs = stmt.executeQuery();
    //process result
}

The query that is causing trouble invokes the following stored procedure:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id BIGINT
                                            , _source_client_id BIGINT)
 RETURNS TEXT AS
$$
   DECLARE rv TEXT;
   BEGIN
     DELETE FROM message m
     USING (
        SELECT * FROM message
        WHERE message.queue_id = _queue_id
        AND (message.receiver_client_id = _source_client_id OR
             message.receiver_client_id = -1)
        ORDER BY insertion_time ASC LIMIT 1) AS tmp
     WHERE m.id = tmp.id
     RETURNING m.content INTO rv;

     IF (SELECT rv IS NULL) THEN
       RAISE 'No message available.';
     END IF;
     RETURN rv;
   END;
$$ LANGUAGE plpgsql;

I am observing behavior as it is described here http://www.postgresql.org/docs/9.1/static/transaction-iso.html in the section 13.2.1. Read Committed Isolation Level.

What happens is that the exception in the procedure is triggered although when I abort everything when the exception happens and invoke the procedure manually the exception is not raised. I noticed further that if a transaction is already holding a row exclusive lock on the message table that then the exception is raised.

Also if I add

     LOCK TABLE message IN ROW EXCLUSIVE MODE;

at the beginning of the procedure the problem disappears.

I thus suspect that in the above loop that a query is returning a result before its transaction is actually committed such that the next transaction is conflicting with it. Can that happen? N.B. I did not find something that explains this in the PostgreSQL documentation. Also I don't have Async commit enabled.

3
  • docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html - I guess you disable autocommit, but don't actually commit anything. Is this the case?
    – dezso
    Oct 28 '15 at 16:43
  • No. I'm using default setting i.e. autocommit is enabled and I also verified that changes happen in the database.
    – ben
    Oct 28 '15 at 17:54
  • Please provide your precise table definition and clarify whether your user is the only one writing to the table. I fixed a syntax error in your function def that's mostly likely orthogonal to your question. Oct 29 '15 at 23:37
2

To answer your question in the title: No.

Consecutive queries in the same session (same transaction or not) are never concurrent. And there can only be one transaction at a time in the same session. Concurrency issues are impossible that way, those only happen between transactions in multiple sessions.

One possible reason for your error message might be a glitch in your function, which can be simplified:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id bigint
                                            , _source_client_id bigint)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   DELETE FROM message m
   USING (
      SELECT id
      FROM   message
      WHERE  queue_id = _queue_id
      AND    receiver_client_id IN (_source_client_id, -1)
      ORDER  BY insertion_time
      LIMIT  1
      ) m1
   WHERE  m.id = m1.id
   RETURNING m.content;

   IF NOT FOUND THEN
      RAISE 'No message available.';
   END IF;
 END
$func$ LANGUAGE plpgsql;

Your original check:

IF (SELECT rv IS NULL) THEN

could be simplified to:

IF rv IS NULL THEN

But that's still no good. It fires when message.content is NULL, not only when no row has been found. The table definition is missing in the question, so we don't know if that's possible.

Either way, the special variable FOUND is the better tool for the purpose. My audited version is also simpler and faster. Note that plpgsql functions do not return results until the end of the function or RETURN is reached. We can just return rows and still cancel the result later if need be. There is an example in the manual, which is a spin-off from this related question on SO:

1
  • Because you stated it is impossible to have more than one transaction running concurrently in one session I checked again and I could not reproduce the error (which was consistently occurring when I asked the question). I thus assume that I messed something up in my setup that day. Thank you for the extended answer and all the hints!
    – ben
    Oct 31 '15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.