Обсуждение: Problem with libpq3 & postgresql8

Hello,

I have a client program which worked ok, using libpq3 agains a 7.4
database. Both linux versions. The client and server are in separate
machines connecting via TCP/IP.

I've just updated the database to 8.0, and the client program now can't
connect. There's no error message, it simply gets stuck when doing the
connection call and remains so indefinitely.

Is this a known incompatibility? If not, any suggestions on what to try?

Thanks in advance,

Alex.

Hi,

On Wed, 21 Sep 2005, Alex R. Mosteo wrote:

> I have a client program which worked ok, using libpq3 agains a 7.4
> database. Both linux versions. The client and server are in separate
> machines connecting via TCP/IP.
>
> I've just updated the database to 8.0, and the client program now can't
> connect. There's no error message, it simply gets stuck when doing the
> connection call and remains so indefinitely.
>
> Is this a known incompatibility? If not, any suggestions on what to try?

libpq version was changed in 8.0.2.

If you are using Red Hat / Fedora Core, install this package to solve the
problem:

http://developer.postgresql.org/~devrim/rpms/compat/compat-postgresql-libs-3-3PGDG.i686.rpm

This RPM installs the following files:

#rpm -qlp compat-postgresql-libs-3-3PGDG.i686.rpm
/usr/lib/libecpg.so.4
/usr/lib/libecpg.so.4.1
/usr/lib/libecpg_compat.so.1
/usr/lib/libecpg_compat.so.1.2
/usr/lib/libpgtypes.so.1
/usr/lib/libpgtypes.so.1.2
/usr/lib/libpq.so.3
/usr/lib/libpq.so.3.1

Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                       http://www.gunduz.org

On Wed, Sep 21, 2005 at 04:18:33PM +0300, Devrim GUNDUZ wrote:
> On Wed, 21 Sep 2005, Alex R. Mosteo wrote:
> >I have a client program which worked ok, using libpq3 agains a 7.4
> >database. Both linux versions. The client and server are in separate
> >machines connecting via TCP/IP.
> >
> >I've just updated the database to 8.0, and the client program now can't
> >connect. There's no error message, it simply gets stuck when doing the
> >connection call and remains so indefinitely.
> >
> >Is this a known incompatibility? If not, any suggestions on what to try?
>
> libpq version was changed in 8.0.2.
>
> If you are using Red Hat / Fedora Core, install this package to solve the
> problem:

While it's a good idea to upgrade the libraries to match the server,
a 7.4 libpq should nevertheless be able to connect to an 8.0 server
(unless the server demands a client certificate, in which case the
connection should fail instead of hanging).  What's your theory
about why the 7.4 library is hanging on the connection attempt?

Alex, are you sure it's the connection call that's stuck?  Have you
used a debugger or added print statements immediately before and
after the connection attempt?  Have you done a process trace or
network sniff to see what's happening when the program hangs?

--
Michael Fuhr

Michael Fuhr wrote:
> On Wed, Sep 21, 2005 at 04:18:33PM +0300, Devrim GUNDUZ wrote:
>
>>On Wed, 21 Sep 2005, Alex R. Mosteo wrote:
>>
>>>I have a client program which worked ok, using libpq3 agains a 7.4
>>>database. Both linux versions. The client and server are in separate
>>>machines connecting via TCP/IP.
>>>
>>>I've just updated the database to 8.0, and the client program now can't
>>>connect. There's no error message, it simply gets stuck when doing the
>>>connection call and remains so indefinitely.
>>>
>>>Is this a known incompatibility? If not, any suggestions on what to try?
>>
>>libpq version was changed in 8.0.2.
>>
>>If you are using Red Hat / Fedora Core, install this package to solve the
>>problem:
>
>
> While it's a good idea to upgrade the libraries to match the server,
> a 7.4 libpq should nevertheless be able to connect to an 8.0 server
> (unless the server demands a client certificate, in which case the
> connection should fail instead of hanging).  What's your theory
> about why the 7.4 library is hanging on the connection attempt?
>
> Alex, are you sure it's the connection call that's stuck?  Have you
> used a debugger or added print statements immediately before and
> after the connection attempt?  Have you done a process trace or
> network sniff to see what's happening when the program hangs?

The most I've checked is adding print statements before and after the
call. There's an extra layer of error source, which is that the call is
done from Ada through a binding to the C library. So it could be that
the Ada layer is behaving wrongly with an unexpected error from the C
layer. But then, I would expect an exception and not a hang. I plan on
conducting some more detailed tests if upgrading libpq fails.

That said, the only thing that I've changed is the database, which is
now the one that ships with kubuntu 5.10 experimental, 8.0.3 according
to psql --version.

About the certificate thing, I'm not sure what you mean, but I've
configured the pg_hba.conf file useing hostnossl. I see this line in the
server log:

LOG:  no se pudo aceptar una conexión SSL: se detectó EOF

Which translates to: "Couldn't accept a SSL connection: EOF detected"

but this message only appears after I kill the client process, which
could mean that indeed the connection is alive and hung.

I don't understand where SSL comes into play. Is there any parameter
that I should be adding to the client connection string to disable it?
My connection string is like:

"host=xxxxx.homeip.net dbname=xxx user=xxxxx password=xxxxxx"

Thanks for your help,

Alex.

"Alex R. Mosteo" <alejandro@mosteo.com> writes:
> About the certificate thing, I'm not sure what you mean, but I've
> configured the pg_hba.conf file useing hostnossl. I see this line in the
> server log:
> LOG:  no se pudo aceptar una conexi�n SSL: se detect� EOF
> Which translates to: "Couldn't accept a SSL connection: EOF detected"
> but this message only appears after I kill the client process, which
> could mean that indeed the connection is alive and hung.

Hm.  Does it work if you add "sslmode = disable" to your connection
string?

What's the platform exactly, and exactly which PG 8.0.* release are you
using?  For that matter, are you sure you are linking your program to
the 8.0 libpq.so, and not still the 7.4 one?

            regards, tom lane

hi,
tom and many others helped me with someting similar to this.

Check for the following:
(a) in /usr/lib check what libpq you have installed. It should be libpq3.2 or higher. If it is not, you can copy from your compiled source and then create the symbolic links there.
(b) Try using etheral and see what is going through and what is happening. It really helps.
(c) You need to have keys on each (client and server side). What certificates are you using (self signed or commercial). I did mine with self-signed and it worked. My certificates go in /var/lib/pgsql
(d) Have your pg_hba.conf set up for openssl.
(e) Have the connection string give sslmode=allow
(f) when you use psql, use /usr/local/pgsql/bin/psql instead of directly writing psql (that searches in /usr/bin in my machine and it isnt the right version) ...

[root@localhost DBApi]# ls -l /usr/lib/libpq*
-rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
lrwxrwxrwx  1 postgres root      21 Aug 29 15:00 /usr/lib/libpq.so -> /usr/lib/libpq.so.3.2
lrwxrwxrwx  1 postgres root      21 Aug 29 14:59 /usr/lib/libpq.so.3 -> /usr/lib/libpq.so.3.2
-rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2

"allow" attempts a non-SSL connection first, whereas
"prefer" attempts an SSL connection first.  If the server permits
non-SSL connections then "allow" will get you connected as non-SSL
without ever attempting SSL.

thanks,
vish

vishal saberwal wrote:
> hi,
> tom and many others helped me with someting similar to this.
>
> Check for the following:
> (a) in /usr/lib check what libpq you have installed. It should be
> libpq3.2 or higher. If it is not, you can copy from your compiled source
> and then create the symbolic links there.
> (b) Try using etheral and see what is going through and what is
> happening. It really helps.
> (c) You need to have keys on each (client and server side). What
> certificates are you using (self signed or commercial). I did mine with
> self-signed and it worked. My certificates go in /var/lib/pgsql
> (d) Have your pg_hba.conf set up for openssl.
> (e) Have the connection string give sslmode=allow
> (f) when you use psql, use /usr/local/pgsql/bin/psql instead of directly
> writing psql (that searches in /usr/bin in my machine and it isnt the
> right version) ...

Thanks for these insightful clues. I've already adopted a workaround
suggested by Tom... but is always good to know.

> [root@localhost DBApi]# ls -l /usr/lib/libpq*
> -rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
> lrwxrwxrwx  1 postgres root      21 Aug 29 15:00 /usr/lib/libpq.so ->
> /usr/lib/libpq.so.3.2
> lrwxrwxrwx  1 postgres root      21 Aug 29 14:59 /usr/lib/libpq.so.3 ->
> /usr/lib/libpq.so.3.2
> -rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
> -rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2
>
> "allow" attempts a non-SSL connection first, whereas
> "prefer" attempts an SSL connection first.  If the server permits
> non-SSL connections then "allow" will get you connected as non-SSL
> without ever attempting SSL.
>
> thanks,
> vish

Tom Lane wrote:
> "Alex R. Mosteo" <alejandro@mosteo.com> writes:
>
>>About the certificate thing, I'm not sure what you mean, but I've
>>configured the pg_hba.conf file useing hostnossl. I see this line in the
>>server log:
>>LOG:  no se pudo aceptar una conexión SSL: se detectó EOF
>>Which translates to: "Couldn't accept a SSL connection: EOF detected"
>>but this message only appears after I kill the client process, which
>>could mean that indeed the connection is alive and hung.
>
>
> Hm.  Does it work if you add "sslmode = disable" to your connection
> string?

Thanks Tom, this did the trick. My data is not sensitive so I don't care
about using ssl.

> What's the platform exactly, and exactly which PG 8.0.* release are you
> using?  For that matter, are you sure you are linking your program to
> the 8.0 libpq.so, and not still the 7.4 one?

Server is 8.0.3, client is in another machine which only has the
libpq.3.1 installed. This client has not been recompiled.

>
>             regards, tom lane
>

"Alex R. Mosteo" <alejandro@mosteo.com> writes:
> Tom Lane wrote:
>> What's the platform exactly, and exactly which PG 8.0.* release are you
>> using?  For that matter, are you sure you are linking your program to
>> the 8.0 libpq.so, and not still the 7.4 one?

> Server is 8.0.3, client is in another machine which only has the
> libpq.3.1 installed. This client has not been recompiled.

Hm, there were a passel of bugs in the SSL certificate support in 7.4,
so maybe you've run into one of them.  Try updating the client's copy
of libpq.

            regards, tom lane