[Pgpool-general] Connection to PgPool-II through ODBC

[Tatsuo Ishii]

Hi,

Could you try with -d -n (with debug option) and send me the log?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hi, Tatsuo.
>  
> I'm doing the integration of a system, and I'm very interested in using
> PgPool-II. We are using FC6, PostgreSQL 8.2.1 and unixODBC 2.2.11.
>  
> Everything is fine when I set up PgPool to connect to a local database
> and a remote database (backend0, and backend1, in pgpool.conf). If both
> databases are up and running and I turn on pgpool -n, I can see that
> everything is fine. Inserts are replicated as expected. The problem is
> as follows:
>  
> 1. isql (unixODBC ODBC interface) connects successfully.
> 2. SQL statements like Inserts are replicated as expected.
> 3. Stop PostgreSQL on host0.
> 4. Connection through isql does not work anymore.
>  
> This happens from every ODBC interface (I tested 1. Qt Database
> Connection object using QODBC and 2. Otl (Oracle/ODBC Database Interface
> for C++). However, I'm still able to connect via psql -p 9999.
>  
> Everything, including odbc.ini file, is set correctly, since I don't see
> any problem with them. So, is there something pgpool is doing at
> connection time that is incompatible with ODBC when one of the
> configured backends is no longer available?
>  
> Here is some output from pgpool -n:
>  
> Start pgpool with both backends up and running:
>  
> -*-*-*-*-*-*-*-*-*-*-*-
> bash-3.1$ pgpool -n
> 2008-11-24 11:20:53 LOG:   pid 15364: pgpool successfully started
>  
>  
> Shutdown PostgreSQL in host0:
>  
> 
> 2008-11-24 11:20:53 ERROR: pid 15364: connect_inet_domain_socket:
> connect() failed: Connection refused
> 2008-11-24 11:20:53 ERROR: pid 15364: health check failed. 0 th host
> 172.20.79.226 at port 5432 is down
> 2008-11-24 11:20:53 LOG:   pid 15364: set 0 th backend down status
> 2008-11-24 11:20:53 LOG:   pid 15364: starting degeneration. shutdown
> host 172.20.79.226(5432)
> 2008-11-24 11:20:53 LOG:   pid 15364: failover_handler: set new master
> node: 1
> 2008-11-24 11:20:53 LOG:   pid 15364: failover done. shutdown host
> 172.20.79.226(5432)
> 
>  
>  
> After doing 'isql dsn_name':
>  
>  
> 2008-11-24 11:21:39 LOG:   pid 15560: connection received:
> host=127.0.0.1 port=49734
> 2008-11-24 11:21:39 LOG:   pid 15560: statement:
> 2008-11-24 11:21:39 LOG:   pid 15560: statement: select version()
> 
>  
>  
> isql throws the following error:
> bash-3.1$ isql dsn_name
> [ISQL]ERROR: Could not SQLConnect
>  
> -*-*-*-*-*-*-*-*-*-*-*-
> Another test, but shutting down backend1:
>  
> -bash-3.1$ pgpool -n
> 2008-11-24 11:23:07 LOG:   pid 15716: pgpool successfully started
>  
>  
> Shutdown PostgreSQL in host1:
>  
>  
> 2008-11-24 11:23:17 ERROR: pid 15716: connect_inet_domain_socket:
> connect() failed: Connection refused
> 2008-11-24 11:23:17 ERROR: pid 15716: health check failed. 1 th host
> 172.20.79.25 at port 5432 is down
> 2008-11-24 11:23:17 LOG:   pid 15716: set 1 th backend down status
> 2008-11-24 11:23:17 LOG:   pid 15716: starting degeneration. shutdown
> host 172.20.79.25(5432)
> 2008-11-24 11:23:17 LOG:   pid 15716: failover_handler: do not restart
> pgpool. same master node 0 was selected
> 2008-11-24 11:23:17 LOG:   pid 15716: failover done. shutdown host
> 172.20.79.25(5432)
>  
>  
> After doing 'isql dsn_name':
>  
>  
> 2008-11-24 11:23:24 LOG:   pid 15816: connection received:
> host=127.0.0.1 port=49758
> 2008-11-24 11:23:24 LOG:   pid 15816: statement:
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: select version()
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: set DateStyle to 'ISO'
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: set geqo to 'OFF'
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: set extra_float_digits
> to 2
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: select oid, typbasetype
> from pg_type where typname = 'lo'
> 2008-11-24 11:23:24 LOG:   pid 15816: statement: select
> pg_client_encoding()
> 
> -bash-3.1$ isql eds_db
> +---------------------------------------+
> | Connected!                            |
> |                                       |
> | sql-statement                         |
> | help [tablename]                      |
> | quit                                  |
> |                                       |
> +---------------------------------------+
> SQL>
> -*-*-*-*-*-*-*-*-*-*-*-
>  
> Any clue?
>  
> Thanks,
> Daniel Crespo
> Software Engineer
> L3 Communications
> Security & Detection Systems
> Office: 727-369-4142
> Fax: 727-369-4358
> 
>