Thread: psqlODBC hangs

I'm having problems getting psqlODBC to work on HP-UX 11.11.
Whenever I try to start my client, it hangs while connecting to the DB.
The client and DB are both running on the same machine.

I've downloaded and installed a precompiled version of Postgresql 7.4.2 from
http://www.software.hp.com/cgi-bin/swdepot_parser.cgi/cgi/displayProductInfo.pl?productNumber=HPUXIEXP1111
without any problems and can use psql to query the DB without problems.

I've built and installed psqlODBC driver ver. 07.03.0200.
The configure line is :

   $ ./configure --without-readline --without-zlib --enable-pthreads
--prefix=/ips01/IPS/parlayGW/pgsql CFLAGS=-g +DD64


My .odbc.ini file looks like:
---- start ----
[ODBC Data Sources]
gateway = Causeway Gateway Database

[gateway]
Debug = 1
CommLog = 1
ReadOnly = 0
Servername = 10.10.29.11
Port = 5432
Protocol = 6.4
Database = gateway
Driver = /ips01/IPS/parlayGW/pgsql/lib/libpsqlodbc.sl

[ODBC]
InstallDir = /ips01/IPS/parlayGW/pgsql/lib/libpsqlodbc.sl
---- end ----

The ODBC log output is:
psqlodbc_gwuser21363.log
---- start ----
conn = 411376, PGAPI_Connect(DSN='gateway', UID='gwuser', PWD='xxxxx')
Global Options: Version='07.03.0200', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
                 disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
                 text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
                 extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=411376, query=' '
---- end ----

mylog_gwuser21363.log
---- start ----
[1]CC_connect: entering...
[1]CC_connect(): DSN = 'gateway', server = '10.10.29.11 ', port =
'5432', database = 'gateway', username = 'gwuser', password='xxxxx'
[1]connecting to the server socket...
[1]connection to the server socket succeeded.
[1]sizeof startup packet = 292
[1]sent the authentication block.
[1]sent the authentication block successfully.
[1]gonna do authentication
[1]read 28, global_socket_buffersize=4096
[1]auth got 'R'
[1]areq = 0
[1]auth got 'N'
[1]read 10, global_socket_buffersize=4096
[1]auth got 'K'
[1]auth got 'Z'
[1]sending an empty query...
[1]send_query(): conn=411376, query=' '
---- end ----

Relevant Postgres logging:
---- start ----
2004-09-27 17:38:32 [21364] LOG:  00000: connection received:
host=127.0.0.1 port=60493
LOCATION:  BackendFork, postmaster.c:2396
2004-09-27 17:38:32 [21364] LOG:  00000: connection authorized:
user=gwuser database=gateway
LOCATION:  BackendFork, postmaster.c:2458
2004-09-27 17:38:32 [21364] DEBUG:  00000:
/ips01/IPS/parlayGW/pgsql/bin/postmaster child[21364]: starting with (
LOCATION:  BackendFork, postmaster.c:2555
2004-09-27 17:38:32 [21364] DEBUG:  00000:      postgres
LOCATION:  BackendFork, postmaster.c:2558
2004-09-27 17:38:32 [21364] DEBUG:  00000:      -v131072
LOCATION:  BackendFork, postmaster.c:2558
2004-09-27 17:38:32 [21364] DEBUG:  00000:      -p
LOCATION:  BackendFork, postmaster.c:2558
2004-09-27 17:38:32 [21364] DEBUG:  00000:      gateway
LOCATION:  BackendFork, postmaster.c:2558
2004-09-27 17:38:32 [21364] DEBUG:  00000: )
LOCATION:  BackendFork, postmaster.c:2560
2004-09-27 17:38:32 [21364] DEBUG:  00000: InitPostgres
LOCATION:  PostgresMain, postgres.c:2640
2004-09-27 17:38:32 [21306] DEBUG:  00000: forked new backend, pid=21364
socket=8
LOCATION:  BackendStartup, postmaster.c:2236
---- end ----


My pg_hba.conf file contains:
---- start ----
local   all         all                                            trust
# IPv4-style local connections:
host    all         all         127.0.0.1         255.255.255.255  trust
# IPv6-style local connections:
host    all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff     trust
---- end ----


Any help at all would be greatly appreciated.

Peter Kelly <Peter.Kelly@aepona.com> writes:
> I'm having problems getting psqlODBC to work on HP-UX 11.11.
> Whenever I try to start my client, it hangs while connecting to the DB.
> The client and DB are both running on the same machine.
> I've downloaded and installed a precompiled version of Postgresql 7.4.2 from
> http://www.software.hp.com/cgi-bin/swdepot_parser.cgi/cgi/displayProductInfo.pl?productNumber=HPUXIEXP1111
> without any problems and can use psql to query the DB without problems.

psql will by default connect via a Unix socket, whereas the ODBC code
is evidently set up to connect via TCP/IP.  I bet if you try
"psql -h 10.10.29.11 ..." it will block too.

It could be a problem with your pg_hba.conf entry for local TCP
connections, but usually mistakes there don't result in a complete
hang.  I'm wondering whether kernel-level packet filtering is preventing
the connection request from going through.  If you've got any sort of
software firewall setup, check its rules.

            regards, tom lane

Followup to that: I hadn't looked closely at your log, but it proves
that the connection request packet (client -> postmaster) went through.
It doesn't prove that the reverse direction (postmaster -> client) could
get through.  So if it's a firewalling problem, the direction to look at
is outbound from postmaster to client.

            regards, tom lane

The ODBC code was deadlocking on a mutex.
The mutex was being locked in SQLConnect and then the code attempted to
lock it agin in CC_send_query, since it isn't a recursive mutex, the
client hangs.

I rebuilt the ODBC code without the '--enable-pthreads' option and it works.

Stack trace below:

#0  0x800003ffff090cc4 in __ksleep+0x2c () from /usr/lib/pa20_64/libc.2
#1  0x800003ffff1c4078 in pthread_mutex_lock+0x180 () from
/usr/lib/pa20_64/libpthread.1
#2  0x800003ffff7a4828 in CC_send_query (self=0x8000000100057b90,
query=0x800003ffff76fc50 " ", qi=0x0, flag=1) at connection.c:1257
#3  0x800003ffff7a3874 in CC_connect (self=0x8000000100057b90,
password_req=0 '\000', salt_para=0x0) at connection.c:918
#4  0x800003ffff7a11a0 in PGAPI_Connect (hdbc=0x8000000100057b90 "\200",
szDSN=0x8000000100039268 "gateway", cbDSN=7, szUID=0x8000000100039270
"gwuser", cbUID=6, szAuthStr=0x8000000100039278 "", cbAuthStr=0) at
connection.c:131
#5  0x800003ffff7de7f8 in SQLConnect
(ConnectionHandle=0x8000000100057b90 "\200",
ServerName=0x8000000100039268 "gateway", NameLength1=7,
UserName=0x8000000100039270 "gwuser", NameLength2=6,
Authentication=0x8000000100039278 "", NameLength3=0) at odbcapi.c:184
#6  0x4000000000034e78 in CmnDbConnection::getConnectionHandle
(this=0x8000000100011268, DBConnHandle=0x800003ffff7f0d00) at
/product/cs_gw/workspaces/pk0043_ws_itanium/Dev/cmn/db/src/CmnDbConnection.cxx:201
#7  0x400000000002c450 in CmnDbConnectionPool::getConnectionHandle
(this=0x8000000100051220, pConnectionHandle=0x800003ffff7f0b68) at
/product/cs_gw/workspaces/pk0043_ws_itanium/Dev/cmn/db/src/CmnDbConnectionPool.cxx:583
#8  0x400000000001c094 in test_connection_pool () at
/product/cs_gw/workspaces/pk0043_ws_itanium/Dev/cmn/db/test/CmnDbTestMain.cxx:35
#9  0x400000000001e568 in main (argc=1, argv=0x800003ffff7f0758) at
/product/cs_gw/workspaces/pk0043_ws_itanium/Dev/cmn/db/test/CmnDbTestMain.cxx:126