Thread: Help with hang on empty query (PG 7.3.2, psqlodbc-07.03.0200)

Help with hang on empty query (PG 7.3.2, psqlodbc-07.03.0200)

From
Steve Wampler
Date:
I recently upgraded a system from RedHat 8 to RedHat 9, which
meant an upgrade of PG 7.2 to 7.3.2.  Several applications
that are written in a language that relies upon odbc to access
postgresql have stopped working.

I've installed from source psqlodbc-07.03.0200 (pthread
and iodbc support).  From the logs it looks as though the
connection is succeeding up to the point that an
empty query is sent (dunno why the empty query is being
sent - it appears to be part of the connection procedure).
At this point the connection hangs - no messages, or errors.
Killing the client program causes postgresql to log a:

   LOG:  pq_recvbuf: unexpected EOF on client connection

message.

These programs had all been working with the odbc RPM that was
available for 7.2.3, so I assume it's something to do with
how I configured the build.

(Can someone tell me why the odbc RPM was dropped with 7.3?)

Thanks for any help!

Various logs and traces:

odbc.trace:

   SQLAllocEnv ( ... )
   SQL_SUCCESS

   SQLAllocConnect ( ... )
   SQL_SUCCESS

   SQLConnect ( ... )

psqlodbc_swampler17169.log:

   conn = 135226032, PGAPI_Connect(DSN='swampler', UID='swampler',
         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=135226032, query=' '

mylog_swampler17169.log:

    [1075229312]CC_connect: entering...
    [1075229312]CC_connect(): DSN = 'swampler', server = 'weaver',
           port = '5432', database = 'swampler', username = 'swampler',
           password='xxxxx'
    [1075229312]connecting to the server socket...
    [1075229312]connection to the server socket succeeded.
    [1075229312]sizeof startup packet = 292
    [1075229312]sent the authentication block.
    [1075229312]sent the authentication block successfully.
    [1075229312]gonna do authentication
    [1075229312]read 15, global_socket_buffersize=4096
    [1075229312]auth got 'R'
    [1075229312]areq = 0
    [1075229312]auth got 'K'
    [1075229312]auth got 'Z'
    [1075229312]sending an empty query...
    [1075229312]send_query(): conn=135226032, query=' '

--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
                    monax materiam possit materiari?

Re: Help with hang on empty query (PG 7.3.2, psqlodbc-07.03.0200)

From
Jeff Eckermann
Date:
--- Steve Wampler <swampler@noao.edu> wrote:
>
> I recently upgraded a system from RedHat 8 to RedHat
> 9, which
> meant an upgrade of PG 7.2 to 7.3.2.  Several
> applications
> that are written in a language that relies upon dbc
> to access
> postgresql have stopped working.
>
> I've installed from source psqlodbc-07.03.0200
> (pthread
> and iodbc support).  From the logs it looks as
> though the
> connection is succeeding up to the point that an
> empty query is sent (dunno why the empty query is
> being
> sent - it appears to be part of the connection
> procedure).
> At this point the connection hangs - no messages, or
> errors.
> Killing the client program causes postgresql to log
> a:
>
>    LOG:  pq_recvbuf: unexpected EOF on client
> connection
>
> message.
>
> These programs had all been working with the odbc
> RPM that was
> available for 7.2.3, so I assume it's something to
> do with
> how I configured the build.
>
> (Can someone tell me why the odbc RPM was dropped
> with 7.3?)
>
> Thanks for any help!

Someone posted a similar problem to the list not long
ago.  Turned out to be a connection problem, i.e. they
could not connect to the server from the new client
machine at all.  Can you successfully connect and
issue queries from this machine using psql or any
other interface?

>
> Various logs and traces:
>
> odbc.trace:
>
>    SQLAllocEnv ( ... )
>    SQL_SUCCESS
>
>    SQLAllocConnect ( ... )
>    SQL_SUCCESS
>
>    SQLConnect ( ... )
>
> psqlodbc_swampler17169.log:
>
>    conn = 135226032, PGAPI_Connect(DSN='swampler',
> UID='swampler',
>          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=135226032, query=' '
>
> mylog_swampler17169.log:
>
>     [1075229312]CC_connect: entering...
>     [1075229312]CC_connect(): DSN = 'swampler',
> server = 'weaver',
>            port = '5432', database = 'swampler',
> username = 'swampler',
>            password='xxxxx'
>     [1075229312]connecting to the server socket...
>     [1075229312]connection to the server socket
> succeeded.
>     [1075229312]sizeof startup packet = 292
>     [1075229312]sent the authentication block.
>     [1075229312]sent the authentication block
> successfully.
>     [1075229312]gonna do authentication
>     [1075229312]read 15,
> global_socket_buffersize=4096
>     [1075229312]auth got 'R'
>     [1075229312]areq = 0
>     [1075229312]auth got 'K'
>     [1075229312]auth got 'Z'
>     [1075229312]sending an empty query...
>     [1075229312]send_query(): conn=135226032,
> query=' '
>
> --
> Steve Wampler -- swampler@noao.edu
> Quantum materiae materietur marmota monax si marmota
>                     monax materiam possit materiari?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Help with hang on empty query (PG 7.3.2,

From
Steve Wampler
Date:
On Fri, 2003-11-07 at 11:38, Jeff Eckermann wrote:
> --- Steve Wampler <swampler@noao.edu> wrote:
> >
> > I recently upgraded a system from RedHat 8 to RedHat
> > 9, which
> > meant an upgrade of PG 7.2 to 7.3.2.  Several
> > applications
> > that are written in a language that relies upon dbc
> > to access
> > postgresql have stopped working.
> >
> > I've installed from source psqlodbc-07.03.0200
> > (pthread
> > and iodbc support).  From the logs it looks as
> > though the
> > connection is succeeding up to the point that an
> > empty query is sent (dunno why the empty query is
> > being
> > sent - it appears to be part of the connection
> > procedure).
> > At this point the connection hangs - no messages, or
> > errors.
> > Killing the client program causes postgresql to log
> > a:
> >
> >    LOG:  pq_recvbuf: unexpected EOF on client
> > connection
> >
> > message.
> >
> > These programs had all been working with the odbc
> > RPM that was
> > available for 7.2.3, so I assume it's something to
> > do with
> > how I configured the build.
> >
> > (Can someone tell me why the odbc RPM was dropped
> > with 7.3?)
> >
> > Thanks for any help!
>
> Someone posted a similar problem to the list not long
> ago.  Turned out to be a connection problem, i.e. they
> could not connect to the server from the new client
> machine at all.  Can you successfully connect and
> issue queries from this machine using psql or any
> other interface?

Yes, with no problem, both psql and jdbc.
--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
                    monax materiam possit materiari?

Re: Help with hang on empty query (PG 7.3.2,

From
Jeff Eckermann
Date:
--- Steve Wampler <swampler@noao.edu> wrote:
> On Fri, 2003-11-07 at 11:38, Jeff Eckermann wrote:
> > --- Steve Wampler <swampler@noao.edu> wrote:
> > >
> > > I recently upgraded a system from RedHat 8 to
> RedHat
> > > 9, which
> > > meant an upgrade of PG 7.2 to 7.3.2.  Several
> > > applications
> > > that are written in a language that relies upon
> dbc
> > > to access
> > > postgresql have stopped working.
> > >
> > > I've installed from source psqlodbc-07.03.0200
> > > (pthread
> > > and iodbc support).  From the logs it looks as
> > > though the
> > > connection is succeeding up to the point that an
> > > empty query is sent (dunno why the empty query
> is
> > > being
> > > sent - it appears to be part of the connection
> > > procedure).
> > > At this point the connection hangs - no
> messages, or
> > > errors.
> > > Killing the client program causes postgresql to
> log
> > > a:
> > >
> > >    LOG:  pq_recvbuf: unexpected EOF on client
> > > connection
> > >
> > > message.
> > >
> > > These programs had all been working with the
> odbc
> > > RPM that was
> > > available for 7.2.3, so I assume it's something
> to
> > > do with
> > > how I configured the build.
> > >
> > > (Can someone tell me why the odbc RPM was
> dropped
> > > with 7.3?)
> > >
> > > Thanks for any help!
> >
> > Someone posted a similar problem to the list not
> long
> > ago.  Turned out to be a connection problem, i.e.
> they
> > could not connect to the server from the new
> client
> > machine at all.  Can you successfully connect and
> > issue queries from this machine using psql or any
> > other interface?
>
> Yes, with no problem, both psql and jdbc.

That's me out of ideas, then.  I believe the driver
issues an empty query as part of the connection
routine to get a response back from the server, just
to test that someone is listening.  In principle, if
that doesn't work, other queries wouldn't work either.
 I don't remember whether you posted your odbc log (I
deleted the message), so please post it if you didn't
before: maybe someone can make something out of it.

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: Help with hang on empty query (PG 7.3.2,

From
Steve Wampler
Date:
On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> I recently upgraded a system from RedHat 8 to RedHat 9, which
> meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> that are written in a language that relies upon odbc to access
> postgresql have stopped working.

I've now verified that the same hang occurs when using the
odbctest program that comes with libiodbc.

Can anyone suggest a way to track this down further?  Thanks!

> I've installed from source psqlodbc-07.03.0200 (pthread
> and iodbc support).  From the logs it looks as though the
> connection is succeeding up to the point that an
> empty query is sent (dunno why the empty query is being
> sent - it appears to be part of the connection procedure).
> At this point the connection hangs - no messages, or errors.
> Killing the client program causes postgresql to log a:
>
>    LOG:  pq_recvbuf: unexpected EOF on client connection
>
> message.
>
> These programs had all been working with the odbc RPM that was
> available for 7.2.3, so I assume it's something to do with
> how I configured the build.
>
> (Can someone tell me why the odbc RPM was dropped with 7.3?)
>
> Thanks for any help!
>
> Various logs and traces:
>
> odbc.trace:
>
>    SQLAllocEnv ( ... )
>    SQL_SUCCESS
>
>    SQLAllocConnect ( ... )
>    SQL_SUCCESS
>
>    SQLConnect ( ... )
>
> psqlodbc_swampler17169.log:
>
>    conn = 135226032, PGAPI_Connect(DSN='swampler', UID='swampler',
>          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=135226032, query=' '
>
> mylog_swampler17169.log:
>
>     [1075229312]CC_connect: entering...
>     [1075229312]CC_connect(): DSN = 'swampler', server = 'weaver',
>            port = '5432', database = 'swampler', username = 'swampler',
>            password='xxxxx'
>     [1075229312]connecting to the server socket...
>     [1075229312]connection to the server socket succeeded.
>     [1075229312]sizeof startup packet = 292
>     [1075229312]sent the authentication block.
>     [1075229312]sent the authentication block successfully.
>     [1075229312]gonna do authentication
>     [1075229312]read 15, global_socket_buffersize=4096
>     [1075229312]auth got 'R'
>     [1075229312]areq = 0
>     [1075229312]auth got 'K'
>     [1075229312]auth got 'Z'
>     [1075229312]sending an empty query...
>     [1075229312]send_query(): conn=135226032, query=' '
--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
                    monax materiam possit materiari?

Re: Help with hang on empty query (PG 7.3.2,

From
Tom Lane
Date:
Steve Wampler <swampler@noao.edu> writes:
>> I've installed from source psqlodbc-07.03.0200 (pthread
>> and iodbc support).  From the logs it looks as though the
>> connection is succeeding up to the point that an
>> empty query is sent (dunno why the empty query is being
>> sent - it appears to be part of the connection procedure).
>> At this point the connection hangs - no messages, or errors.

I'm just guessing, but my first guess is that the empty-query path in
the driver fails to do an fflush() to push out the query to the backend.
You could possibly confirm this by sniffing the connection with a packet
monitor to see what is the last data sent by the client side.

            regards, tom lane

Re: Help with hang on empty query (PG 7.3.2,

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Steve Wampler
>
> On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> > I recently upgraded a system from RedHat 8 to RedHat 9, which
> > meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> > that are written in a language that relies upon odbc to access
> > postgresql have stopped working.
>
> I've now verified that the same hang occurs when using the
> odbctest program that comes with libiodbc.
>
> Can anyone suggest a way to track this down further?  Thanks!

Could you try to remove ENTER_CONN_CS in CC_send_query
in connection.c and change all RETURN_AFTER_LEAVE_CS(self, ..)
to "return ..(the 2nd parameter) "?

regards,
Hiroshi Inoue


Re: Help with hang on empty query (PG 7.3.2,

From
Steve Wampler
Date:
On Sat, 2003-11-08 at 16:33, Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Steve Wampler
> >
> > On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> > > I recently upgraded a system from RedHat 8 to RedHat 9, which
> > > meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> > > that are written in a language that relies upon odbc to access
> > > postgresql have stopped working.
> >
> > I've now verified that the same hang occurs when using the
> > odbctest program that comes with libiodbc.
> >
> > Can anyone suggest a way to track this down further?  Thanks!
>
> Could you try to remove ENTER_CONN_CS in CC_send_query
> in connection.c and change all RETURN_AFTER_LEAVE_CS(self, ..)
> to "return ..(the 2nd parameter) "?

Hi Hiroshi,

Thanks for the suggestion.  I get the same behavior, but
the mylog file has more information in it (the other logs
are unchanged):
-------------------------------------------------------
->cat mylog*
[1074087680]CC_connect: entering...
[1074087680]CC_connect(): DSN = 'swampler', server = 'weaver', port =
'5432', database = 'swampler', username = 'swampler', password='xxxxx'
[1074087680]connecting to the server socket...
[1074087680]connection to the server socket succeeded.
[1074087680]sizeof startup packet = 292
[1074087680]sent the authentication block.
[1074087680]sent the authentication block successfully.
[1074087680]gonna do authentication
[1074087680]read 15, global_socket_buffersize=4096
[1074087680]auth got 'R'
[1074087680]areq = 0
[1074087680]auth got 'K'
[1074087680]auth got 'Z'
[1074087680]sending an empty query...
[1074087680]send_query(): conn=134528568, query=' '
[1074087680]send_query: done sending query
[1074087680]in QR_Constructor
[1074087680]exit QR_Constructor
[1074087680]read 3, global_socket_buffersize=4096
[1074087680]send_query: got id = 'I'
[1074087680]send_query: got id = 'Z'
[1074087680]QResult: in DESTRUCTOR
[1074087680]QResult: free memory in, fcount=0
[1074087680]QResult: free memory out
[1074087680]QResult: exit DESTRUCTOR
[1074087680]empty query seems to be OK.
[1074087680]CC_lookup_pg_version: entering...
[1074087680]PGAPI_AllocStmt: entering...
[1074087680]**** PGAPI_AllocStmt: hdbc = 134528568, stmt = 134552160
[1074087680]CC_add_statement: self=134528568, stmt=134552160
[1074087680]PGAPI_ExecDirect: entering...
[1074087680]**** PGAPI_ExecDirect: hstmt=134552160, statement='select
version()'
[1074087680]PGAPI_ExecDirect: calling PGAPI_Execute...
[1074087680]PGAPI_Execute: entering...
[1074087680]PGAPI_Execute: clear errors...
[1074087680]recycle statement: self= 134552160
[1074087680]Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=16, stmt='select version()'
[1074087680]   stmt_with_params = 'select version()'
--------------------------------------------------------------
--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
                    monax materiam possit materiari?

Re: Help with hang on empty query (PG 7.3.2,

From
Hiroshi Inoue
Date:
Steve Wampler wrote:
>
> On Sat, 2003-11-08 at 16:33, Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: Steve Wampler
> > >
> > > On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> > > > I recently upgraded a system from RedHat 8 to RedHat 9, which
> > > > meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> > > > that are written in a language that relies upon odbc to access
> > > > postgresql have stopped working.
> > >
> > > I've now verified that the same hang occurs when using the
> > > odbctest program that comes with libiodbc.
> > >
> > > Can anyone suggest a way to track this down further?  Thanks!
> >
> > Could you try to remove ENTER_CONN_CS in CC_send_query
> > in connection.c and change all RETURN_AFTER_LEAVE_CS(self, ..)
> > to "return ..(the 2nd parameter) "?
>
> Hi Hiroshi,
>
> Thanks for the suggestion.  I get the same behavior, but
> the mylog file has more information in it (the other logs
> are unchanged):

OK I can reproduce the phenomenon here.
Please try the attached patch.

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Attachment

Re: Help with hang on empty query (PG 7.3.2,

From
Steve Wampler
Date:
On Sun, 2003-11-09 at 10:15, Steve Wampler wrote:
> On Sat, 2003-11-08 at 16:33, Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: Steve Wampler
> > >
> > > On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> > > > I recently upgraded a system from RedHat 8 to RedHat 9, which
> > > > meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> > > > that are written in a language that relies upon odbc to access
> > > > postgresql have stopped working.
> > >
> > > I've now verified that the same hang occurs when using the
> > > odbctest program that comes with libiodbc.
> > >
> > > Can anyone suggest a way to track this down further?  Thanks!
> >
> > Could you try to remove ENTER_CONN_CS in CC_send_query
> > in connection.c and change all RETURN_AFTER_LEAVE_CS(self, ..)
> > to "return ..(the 2nd parameter) "?
>
> Hi Hiroshi,
>
> Thanks for the suggestion.  I get the same behavior, but
> the mylog file has more information in it (the other logs
> are unchanged):

I've done a bit more digging and the apps are hanging in
a call to pthread_mutex_lock() inside SC_execute().  (I had
configured the pgsqlodbc library with pthread support.)

Dropping pthread support has fixed the problem, whatever it
was...

--
Steve Wampler -- swampler@noao.edu
Quantum materiae materietur marmota monax si marmota
                    monax materiam possit materiari?

Re: Help with hang on empty query (PG 7.3.2,

From
Hiroshi Inoue
Date:

Steve Wampler wrote:
>
> On Sun, 2003-11-09 at 10:15, Steve Wampler wrote:
> > On Sat, 2003-11-08 at 16:33, Hiroshi Inoue wrote:
> > > > -----Original Message-----
> > > > From: Steve Wampler
> > > >
> > > > On Fri, 2003-11-07 at 09:05, Steve Wampler wrote:
> > > > > I recently upgraded a system from RedHat 8 to RedHat 9, which
> > > > > meant an upgrade of PG 7.2 to 7.3.2.  Several applications
> > > > > that are written in a language that relies upon odbc to access
> > > > > postgresql have stopped working.
> > > >
> > > > I've now verified that the same hang occurs when using the
> > > > odbctest program that comes with libiodbc.
> > > >
> > > > Can anyone suggest a way to track this down further?  Thanks!
> > >
> > > Could you try to remove ENTER_CONN_CS in CC_send_query
> > > in connection.c and change all RETURN_AFTER_LEAVE_CS(self, ..)
> > > to "return ..(the 2nd parameter) "?
> >
> > Hi Hiroshi,
> >
> > Thanks for the suggestion.  I get the same behavior, but
> > the mylog file has more information in it (the other logs
> > are unchanged):
>
> I've done a bit more digging and the apps are hanging in
> a call to pthread_mutex_lock() inside SC_execute().  (I had
> configured the pgsqlodbc library with pthread support.)
>
> Dropping pthread support has fixed the problem, whatever it
> was...

The pthread_mutex_lock call is blocking the thread itself.
I changed the behavior of the mutex in my patch I sent you
in another posting.

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/