[pg-devel] Prepared statements problem

[Robert J. Macomber]

I've got a problem involving prepared statements and multiple queries
per connection.  It seems pg gets out of sync with the database when
using them.  Sometimes this leads to protocol errors, sometimes to
strange results being returned from queries.  Here's a test case that
shows what I mean:

---------------------------------------
(defpackage #:prepared-test
  (:use #:cl #:pg))

(in-package #:prepared-test)

;; assumes there's a table "test" created with:
;; create table test (id serial, blah varchar not null)

(defun test (dbname user)
  (with-pg-connection (db dbname user :host "/var/run/postgresql/")
    (list
     (pg-prepare db "si" "insert into test (blah) values ($1)" '(:|varchar|))
     (pg-prepare db "ss" "select currval('test_id_seq')")
     (pg-bind db "pi" "si" '((:string "test")))
     (pg-execute db "pi")
     (pg-bind db "ps" "ss" '())
     (pg-execute db "ps")
     (pg-close-statement db "ss")
     (pg-close-statement db "si")
     (pg-exec db "select id,blah from test")
     (pg-exec db "select blah,id from test"))))
---------------------------------------

When you run the test, after the statements are closed, the selects
get "out of sync", the first returning nothing (the :status,
:attributes, and :tuples of the result object are all NIL), and the
second returning the results of the first.  If the portals are
explicitly closed as well, it gets two "steps" out of sync.

I'm using the latest CVS pg and postgresql 8.1.5.  Investigating, but
everything I know about postgresql's protocol I've learned while
trying to debug this, so I don't know what's causing it exactly yet.
-- 
Robert Macomber
pgsql at rojoma.com