[Pgpool-general] Transactions not working properly from ODBC

[Daniel.Crespo at l-3com.com]

Hi again,

Just in case, I tested connecting with isql (the psql-like odbc
interface) directly to PostgreSQL and there's no problem at all. I guess
there's something pgpool doesn't like that is received from odbc layer.
Any clue?

Thanks,
Daniel

-----Original Message-----
From: pgpool-general-bounces at pgfoundry.org
[mailto:pgpool-general-bounces at pgfoundry.org] On Behalf Of
Daniel.Crespo at l-3com.com
Sent: Wednesday, March 11, 2009 5:48 PM
To: pgpool-general at pgfoundry.org
Subject: [Pgpool-general] Transactions not working properly from ODBC

Hi,

I have a very particular problem here. I have two odbc configurations to
access PostgreSQL through pgpool and directly to PostgreSQL. Bottomline
is that INSERTs within a transaction block using ODBC to pgpool, does
not work.

Here's what I have:

Pgpool 2.2beta1
PostgreSQL 8.2.1
unixODBC 2.2.11

The SQL statement is simple:
BEGIN;
INSERT INTO test VALUES (1);
COMMIT;

>From isql (odbc interface, it's analogous to psql):
[root at coyote ~]# isql testdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> begin;
SQLRowCount returns -1
SQL> insert into test values (6);
[ISQL]ERROR: Could not SQLExecute
SQL> quit

The related logged error in pgpool is:


>From psql:
[root at coyote ~]# psql -U postgres -d testdb -p 9999
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=# begin;
BEGIN
testdb=# insert into test values (5);
INSERT 0 1
testdb=# commit;
COMMIT
testdb=# \q

The problem seems to be in ODBC, but it seems to happen ONLY when pgpool
is the connection interface to PostgreSQL. Any clue? Attached are the
log files.

Thanks,
Daniel