Обсуждение: Strange behavior with large datas

    I'm trying to bring up a production system using postgresql and cygwin.

    I'm using the latest cygwin packages (as well as cygipc 1.09-2)

    Installation was done without any problem. Evrything works smoothly
except queries on tables containing large toasted values. a select on such a
table will Hang (client locked, use the attached test script to reproduce).
I have tried with an earlier version of postgres (7.1) as well as current
CVS : same problem.
    I found that downgrading cygwin package to cygwin 1.3.1-1 does remove
this problem.

    But with cygwin 1.3.1, there another strange behavior : in one
transaction, I create 20 record with quite large toasted values in them
(~30Ko each in 2 fields). Transaction commit take a very long time (5-10 s).


    Any Idea will be welcome,

    thanks


            cyril


test script :

    create table test (tf test);

    insert into test (tf) values ('aaaaaaaaaa');
    update test set tf=tf||tf||tf||tf||ff||tf||tf||tf||tf||ff
    update test set tf=tf||tf||tf||tf||ff||tf||tf||tf||tf||ff
    update test set tf=tf||tf||tf||tf||ff||tf||tf||tf||tf||ff
    update test set tf=tf||tf||tf||tf||ff||tf||tf||tf||tf||ff

    select tf from test -> Hang

Cyril,

On Fri, Aug 17, 2001 at 09:29:35PM +0200, Cyril VELTER wrote:
>     Installation was done without any problem. Evrything works smoothly
> except queries on tables containing large toasted values. a select on such a
> table will Hang (client locked, use the attached test script to reproduce).
> I have tried with an earlier version of postgres (7.1) as well as current
> CVS : same problem.
>     I found that downgrading cygwin package to cygwin 1.3.1-1 does remove
> this problem.
>
>     But with cygwin 1.3.1, there another strange behavior : in one
> transaction, I create 20 record with quite large toasted values in them
> (~30Ko each in 2 fields). Transaction commit take a very long time (5-10 s).
>
>     Any Idea will be welcome,

Can you try the latest Cygwin snapshot and report back to the list?

You can find the snapshots at:

    http://www.cygwin.com/snapshots/

or one of the Cygwin mirrors:

    http://www.cygwin.com/mirrors.html

Thanks,
Jason

     Jason,

    Just tried it (13 august snapshot). The behavior is exactly the same
 than 1.3.2 : insertion of large fields is fast, but the large field select
 still fail (Hang).

     I've done some more tests and the problem seem libpq related (and not
 backend related). In fact, if I use the windows psql to connect to a linux
 server, I get the exact same problem. When I use linux psql with either
 linux or cygwin backend all is fine.


         cyril


> > Cyril,
> >
> > On Fri, Aug 17, 2001 at 09:29:35PM +0200, Cyril VELTER wrote:
> > >     Installation was done without any problem. Evrything works
smoothly
> > > except queries on tables containing large toasted values. a select on
> such a
> > > table will Hang (client locked, use the attached test script to
> reproduce).
> > > I have tried with an earlier version of postgres (7.1) as well as
> current
> > > CVS : same problem.
> > >     I found that downgrading cygwin package to cygwin 1.3.1-1 does
> remove
> > > this problem.
> > >
> > >     But with cygwin 1.3.1, there another strange behavior : in one
> > > transaction, I create 20 record with quite large toasted values in
them
> > > (~30Ko each in 2 fields). Transaction commit take a very long time
(5-10
> s).
> > >
> > >     Any Idea will be welcome,
> >
> > Can you try the latest Cygwin snapshot and report back to the list?
> >
> > You can find the snapshots at:
> >
> >     http://www.cygwin.com/snapshots/
> >
> > or one of the Cygwin mirrors:
> >
> >     http://www.cygwin.com/mirrors.html
> >
> > Thanks,
> > Jason
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>

Cyril,

On Sat, Aug 18, 2001 at 06:51:53PM +0200, Cyril VELTER wrote:
>      I've done some more tests and the problem seem libpq related (and not
>  backend related). In fact, if I use the windows psql to connect to a linux
>  server, I get the exact same problem. When I use linux psql with either
>  linux or cygwin backend all is fine.

Does "windows psql" imply Cygwin or Win32 psql?  If Cygwin, can you
repeat with the Win32 psql?  Also, please try to use strace to figure
out what is going on under Cygwin:

    $ strace -o psql.log psql ...

Please report back to the list with your findings.

Thanks,
Jason

     Jason,

 > On Sat, Aug 18, 2001 at 06:51:53PM +0200, Cyril VELTER wrote:
> >      I've done some more tests and the problem seem libpq related (and
not
> >  backend related). In fact, if I use the windows psql to connect to a
linux
> >  server, I get the exact same problem. When I use linux psql with either
> >  linux or cygwin backend all is fine.
>
> Does "windows psql" imply Cygwin or Win32 psql?  If Cygwin, can you
> repeat with the Win32 psql?  Also, please try to use strace to figure
> out what is going on under Cygwin:

    it's cygwin psql (by oppostion to linux one). I've no access to a VC++
compiler so I have no native win32 psql. But meantime I've tried my
application with zde libpq.dll. The beavior is not the same : the datas
which caused cygwin libpq to Hang are returned truncated ...

>
>     $ strace -o psql.log psql ...
>

see attached file


    cyril

    I've done some test to find where libpq hang. It does hang on the recv
call of fe-misc.c line 444. When it hangs, all datas have already been read
successfully on the socket, so recv should return 0 on a non blocking socket
?

    The problem is solved (but I'm sure that it isn't the right solution)
with doing the recv call only if pqReadReady is true (else we set nread to
0).

    BTW, does somebody know where I can find a recent native win32 libpq.dll
binarie (the ones I find on the WEB have also some problems with large
datas) ? For my client side program, I would prefer to have the native libpq
to avoid deploying cygwin on all clients.

            cyril


>  > On Sat, Aug 18, 2001 at 06:51:53PM +0200, Cyril VELTER wrote:
> > >      I've done some more tests and the problem seem libpq related (and
> not
> > >  backend related). In fact, if I use the windows psql to connect to a
> linux
> > >  server, I get the exact same problem. When I use linux psql with
either
> > >  linux or cygwin backend all is fine.
> >
> > Does "windows psql" imply Cygwin or Win32 psql?  If Cygwin, can you
> > repeat with the Win32 psql?  Also, please try to use strace to figure
> > out what is going on under Cygwin:
>
>     it's cygwin psql (by oppostion to linux one). I've no access to a VC++
> compiler so I have no native win32 psql. But meantime I've tried my
> application with zde libpq.dll. The beavior is not the same : the datas
> which caused cygwin libpq to Hang are returned truncated ...
>

Cyril,

On Tue, Aug 21, 2001 at 02:01:58PM +0200, Cyril VELTER wrote:
>     I've done some test to find where libpq hang. It does hang on the recv
> call of fe-misc.c line 444. When it hangs, all datas have already been read
> successfully on the socket, so recv should return 0 on a non blocking
> socket?

recv should return -1 with errno set to EAGAIN in nonblocking mode when
no data is available.

>     The problem is solved (but I'm sure that it isn't the right solution)
> with doing the recv call only if pqReadReady is true (else we set nread to
> 0).

Did you workaround this problem by modifying the PostgreSQL code? If
so, then please try to determine whether the problem is in Cygwin (most
likely) or PostgreSQL.  If in Cygwin, then please report it to the Cygwin
list (cygwin@cygwin.com) with a small test case, if possible.

>     BTW, does somebody know where I can find a recent native win32 libpq.dll
> binarie (the ones I find on the WEB have also some problems with large
> datas) ? For my client side program, I would prefer to have the native libpq
> to avoid deploying cygwin on all clients.

You can find the above at:

    http://members.home.net/jtishler/software/postgresql/postgresql-7.1.2-1-win32.tar.gz

Note that they are untested by me but others seem to have enjoyed
using them.

Jason

    Jason,

> >     I've done some test to find where libpq hang. It does hang on the
recv
> > call of fe-misc.c line 444. When it hangs, all datas have already been
read
> > successfully on the socket, so recv should return 0 on a non blocking
> > socket?
>
> recv should return -1 with errno set to EAGAIN in nonblocking mode when
> no data is available.

    OK, it does make sense. But the call to recv just block forever when all
datas are received. I suspect the problem is in cygwin (using cygwin 1.3.1-1
doens't show this problem, but there is the slow update on large fields
...).

> Did you workaround this problem by modifying the PostgreSQL code? If
> so, then please try to determine whether the problem is in Cygwin (most
> likely) or PostgreSQL.  If in Cygwin, then please report it to the Cygwin
> list (cygwin@cygwin.com) with a small test case, if possible.

    Yes, the workaround is a small modification of fe-misc.c to test data
availability before calling recv. I can make a report to cygwin, but I'm not
enought a sockets guru to write a small test case. Perhaps the strace output
of a session which hang at the end migh help them?


> You can find the above at:
>
>
http://members.home.net/jtishler/software/postgresql/postgresql-7.1.2-1-win3
2.tar.gz


    Thanks,

        This lib also have a problem with large reads, but instead of
hanging completly, an error is returned. I have eared recently some
discussion on Hacker regarding some problems with large queries with win32
libpq. Maybe it's related ?

        I'm using win2000 sp2 if it matters.

            cyril