[Pgpool-general] Transactions not working properly from ODBC

[Tatsuo Ishii]

Thanks for the report.

The difference between psql and ODBC is the protocol version
number. Namely psql speaks version 3 which is standard protocol since
PostgreSQL 7.4, while your ODBC driver speaks version 2, which is
pretty old.

Unfortunately pgpool-II 2.2 seems to have a problem with insert_lock
handling used with version 2 protocol.

Can you try to turn off insert_lock diective when using ODBC?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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