Cursor error with postgresql, pgpool and php

Question

Hey, I am struggling a bit to determine the exact cause of an error that has been popping up in our release environment. There does not seem to be much dealing with this particular error on Google.

This is the error message we are getting:

SQLSTATE[34000]: Invalid cursor name: 7 ERROR: portal "" does not exist

The error only pops up when we are using PDO prepared statements.

This is the setup for our release environment:

1. pgpool 3.0.1 (The postgresql backend is in Streaming Replication mode!)
2. PHP 5.3.5
3. PostgreSQL 9.0

Edit: Architecture is 64bit.

The same error does not manifest in our test environment (Edit: forgot to mention, the standard test environment uses Postgresql 9.0 without pgpool). Thus, I am led to suspect that pgpool is at least partly suspect.

Does anyone know what the probable causes for this error are?

Edit: ok, here is an example of the kind of code that causes the error.

$sql = 'SELECT * ';
$sql .= 'FROM "myTable" as "myStuff" ';
$sql .= 'WHERE "myTable"."status" = 1 ';
$sql .= 'AND "myTable"."myTableId" = :tableId ';
$sth = $this->_db->prepare($sql);
$sth->bindParam(':tableId', $tableId, PDO::PARAM_INT);
$sth->execute();

Edit: Some log file output;

Postgresql:

postgresql-Sun.log-129- ORDER BY "id" 
postgresql-Sun.log:130:ERROR:  portal "" does not exist
postgresql-Sun.log-131-ERROR:  prepared statement "pdo_stmt_00000011" does not exist
postgresql-Sun.log-132-STATEMENT:  DEALLOCATE pdo_stmt_00000011


postgresql-Mon.log-82-  where "id" = 32024
postgresql-Mon.log:83:ERROR:  portal "" does not exist
postgresql-Mon.log-84-ERROR:  prepared statement "pdo_stmt_00000002" does not exist
postgresql-Mon.log-85-STATEMENT:  DEALLOCATE pdo_stmt_00000002

pgpool:

LOG:   pid 22071: Replication of node:1 is behind 2080 bytes from the primary server (node:0)
LOG:   pid 22071: Replication of node:2 is behind 2080 bytes from the primary server (node:0)
LOG:   pid 13499: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 8470 statement:  message: portal "" does not exist
LOG:   pid 13499: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 8470 statement: DEALLOCATE pdo_stmt_00000003 message: prepared statement "pdo_stmt_00000003" does not exist

Answer 1

If you could duplicate the problem in your test environment, I wouldn't hesitate to recommend running the server with the -d (debug) option.

Since that's not the case, I'll just remind you that it's an option.

PostgreSQL command line options

On that page, there are a couple of "Semi-internal Options" that might help isolate the problem. Might not.

Answer 2

I believe I have a temporary work around, and am invested in finding a permanent solution. I am working on a high-availability PG cluster on Amazon EC2, and have also ran into this exact issue.

It occurs randomly for queries executed using DBI's prepare/execute blocks when DBI is routed through PGPool2 (3.0.3). The portal errors do not occur when PGPool2 is removed and we use Postgres 9 directly.

We run Perl, using DBI and DBD::PG. The common factor seems to be PGPool2.

One possible solution we have found is to set 'ignore_leading_white_space' = false in the pgpool.conf. The errors completely disappear for us with this option set. Unfortunately this does have the downside of potentially routing selects to the master that should be load balanced, as such I do not consider it a final solution.

Example of code that randomly generates this issue:

$sth = $dbh->prepare("SELECT * FROM TABLEX WHERE ID = ?" ) 
|| die "Can't prepare statement: " . $dbh->errstr;
$sth->execute($self->id) || die "Can't get inventory " . $dbh->errstr;

Answer 3

I've found a solution. Turns out this bug we are experiencing does not exists in Pgpool-II 3.1 alpha2. It looks like this bug was fixed in March after the 3.0.3 release. The solution is to download the release and build/install manually. Some of the paths are different, for instance the config path is /usr/local/etc/

Pgpool-II 3.1 alpha2 is available here: http://pgfoundry.org/frs/?group_id=1000055

It's possible that the latest 3.0-Stable tree also has a fix for this issue. I am hoping to test an export from the CVS later tonight.