Thread: psql problem

psql problem

From
Gary Fu
Date:
Hi,

System information:
- psql 8.4.4 on a client with CentOS 5.5 (64 bits)
- postgres 8.4.4 on the server with CentOS 5.5 (64 bits)
- the client is connected with vpn

I have a script to create a table with some comments in front.  When I
use the command 'psql -f script.sql' to load it, it hangs.  However, if
I remove the comments, OR remove some of the columns from the table, it
works okay.   It looks like to me, the psql will hang with large size of
the script file.  I tried 'psql < script.sql' and 'cat script.sql |
psql' with the same result.

However, I tried it on another client host (CentOS 5.5 32 bits), I don't
see this problem.

Any idea and suggestion ?

Thanks,
Gary

Re: psql problem

From
"shakahshakah@gmail.com"
Date:
On Jul 22, 5:03 pm, Gary Fu <gary...@sigmaspace.com> wrote:
> Hi,
>
> System information:
> - psql 8.4.4 on a client with CentOS 5.5 (64 bits)
> - postgres 8.4.4 on the server with CentOS 5.5 (64 bits)
> - the client is connected with vpn
>
> I have a script to create a table with some comments in front.  When I
> use the command 'psql -f script.sql' to load it, it hangs.  However, if
> I remove the comments, OR remove some of the columns from the table, it
> works okay.   It looks like to me, the psql will hang with large size of
> the script file.  I tried 'psql < script.sql' and 'cat script.sql |
> psql' with the same result.
>
> However, I tried it on another client host (CentOS 5.5 32 bits), I don't
> see this problem.
>
> Any idea and suggestion ?
>
> Thanks,
> Gary

Are you sure it is hanging? Maybe piping into psql via "pv" (http://
www.ivarch.com/programs/pv.shtml) could give some insight as to where
it might be hanging, or if it is just going slowly, or whatever.

E.g., if your script is 240500 bytes long, the following will give a
simple progress meter, elapsed time, and ETA display:
  cat yourscript.file | pv -t -r -e -b -s 240500 | psql


Re: psql problem

From
Andy Colson
Date:
On 07/22/2010 04:03 PM, Gary Fu wrote:
> Hi,
>
> System information:
> - psql 8.4.4 on a client with CentOS 5.5 (64 bits)
> - postgres 8.4.4 on the server with CentOS 5.5 (64 bits)
> - the client is connected with vpn
>
> I have a script to create a table with some comments in front. When I use the command 'psql -f script.sql' to load
it,it hangs. However, if I remove the comments, OR remove some of the columns from the table, it works okay. It looks
liketo me, the psql will hang with large size of the script file. I tried 'psql < script.sql' and 'cat script.sql |
psql'with the same result. 
>
> However, I tried it on another client host (CentOS 5.5 32 bits), I don't see this problem.
>
> Any idea and suggestion ?
>
> Thanks,
> Gary

Line endings?

How about a sample?

What comment style: -- /* (* # ; ' //


-Andy

Re: psql problem

From
Gary Fu
Date:
On 07/22/2010 09:02 PM, Andy Colson wrote:
> On 07/22/2010 04:03 PM, Gary Fu wrote:
>> Hi,
>>
>> System information:
>> - psql 8.4.4 on a client with CentOS 5.5 (64 bits)
>> - postgres 8.4.4 on the server with CentOS 5.5 (64 bits)
>> - the client is connected with vpn
>>
>> I have a script to create a table with some comments in front. When I
>> use the command 'psql -f script.sql' to load it, it hangs. However, if
>> I remove the comments, OR remove some of the columns from the table,
>> it works okay. It looks like to me, the psql will hang with large size
>> of the script file. I tried 'psql < script.sql' and 'cat script.sql |
>> psql' with the same result.
>>
>> However, I tried it on another client host (CentOS 5.5 32 bits), I
>> don't see this problem.
>>
>> Any idea and suggestion ?
>>
>> Thanks,
>> Gary
>
> Line endings?
>
> How about a sample?
>
> What comment style: -- /* (* # ; ' //
>
>
> -Andy

Below is an example that I created.  It works okay, but when I add any
character in the comment or in the table definition, it fails (hangs).
I checked the server process (with ps command), and I can see that
connection is 'idle'.  By the way, the size 1484 may not mean anything,
since I tried another case (with different comment and table) and the
problem still happens but the size 1484 is not the break point.

I think this may be CentOS(64 bits)/ssh related, since I don't have the
problem with CentOS(32 bits) and we have the same application to install
the tables with the same command on mandriva.

Thanks,
Gary

Sample file:

/*

=head1 NAME

ProblemFiles

=head1 DESCRIPTION

The ProblemFiles table is used to store the file names that have
problem to be handled by PollAndArchive and VerifyFiles programs.

=head1 FIELDS

   ProblemId - The Id for the problem file
   FileName - The full file name with problem
   Reason - The reason for the file to be inserted
   IsDN -  This FileName is a DN (DDR or DS) file
   DNFile - The DN file for the FileName in problem
   DNType - The DN type (1 for DDR, 2 for DS, 0 for Unknown)
   FtpPath - The ftp incoming path for the problem file, so we know
where to get
             the file again if necessary
   Adhoc - None for sd3e normal subscription,
           SD3E for sd3e adhoc (handled as subscription)
           Land/Ocean/Atmosphere/Ozone/Sounder/NICSE for peates' ad-hoc
   CkSum - Th checksum of the file (only for file with DDR file on Reason :
           Missing, Duplicate, so that they can be verified again if
necessary
   test test test tt

=cut

*/

--
-- Name: ProblemFiles  Type: Table
--

create table ProblemFiles
(
         ProblemId            serial               primary key,
    FileName             varchar(256)         not null,
         Reason               varchar(16)          not null,
         IsDN                 int                  not null default 0,
         DNFile               varchar(256)         null,
         DNType               int                  not null default 1,
    InsertTime           timestamp            not null default now()
);





Re: psql problem

From
Ireneusz Pluta
Date:
Gary Fu pisze:
>
> I have a script to create a table with some comments in front.  When I
> use the command 'psql -f script.sql' to load it, it hangs.  However,
> if I remove the comments, OR remove some of the columns from the
> table, it works okay.   It looks like to me, the psql will hang with
> large size of the script file.  I tried 'psql < script.sql' and 'cat
> script.sql | psql' with the same result.
>
you may eventually track the progress more verbosely with -a switch to
psql and this way isolate the part where it hangs

Re: psql problem

From
Tom Lane
Date:
Gary Fu <gary.fu@sigmaspace.com> writes:
> Below is an example that I created.  It works okay, but when I add any
> character in the comment or in the table definition, it fails (hangs).
> I checked the server process (with ps command), and I can see that
> connection is 'idle'.  By the way, the size 1484 may not mean anything,
> since I tried another case (with different comment and table) and the
> problem still happens but the size 1484 is not the break point.

> I think this may be CentOS(64 bits)/ssh related, since I don't have the
> problem with CentOS(32 bits) and we have the same application to install
> the tables with the same command on mandriva.

FWIW, I cannot reproduce this problem using 8.4.latest on Fedora 13 64bit.
So it might indeed be something specific to the openssl version you're
using.  I assume you tested that the problem goes away if you use a
non-SSL connection?

The openssl installation I'm testing with is
openssl-1.0.0a-1.fc13.x86_64
I don't know offhand what RHEL/CentOS 5.x are using but it's probably
quite a lot older.

            regards, tom lane

Re: psql problem

From
Greg Smith
Date:
Tom Lane wrote:
> The openssl installation I'm testing with is
> openssl-1.0.0a-1.fc13.x86_64
> I don't know offhand what RHEL/CentOS 5.x are using but it's probably
> quite a lot older.
>

Here's a CentOS 5.5 install that's kept up to date:

$ rpm -qi openssl
Name        : openssl                      Relocations: (not relocatable)
Version     : 0.9.8e                            Vendor: CentOS
Release     : 12.el5_4.6                    Build Date: Fri 26 Mar 2010
04:55:17 PM EDT
Install Date: Fri 09 Apr 2010 01:23:38 AM EDT      Build Host:
builder10.centos.org
Group       : System Environment/Libraries   Source RPM:
openssl-0.9.8e-12.el5_4.6.src.rpm
Size        : 3610575                          License: BSDish
Signature   : DSA/SHA1, Sat 27 Mar 2010 01:29:08 PM EDT, Key ID
a8a447dce8562897

Looks like this package set:
https://rhn.redhat.com/errata/RHSA-2010-0162.html is the current one
still, with backports for the CVEs.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


Re: psql problem

From
Gary Fu
Date:
On 07/27/2010 10:20 PM, Tom Lane wrote:
> Gary Fu<gary.fu@sigmaspace.com>  writes:
>> Below is an example that I created.  It works okay, but when I add any
>> character in the comment or in the table definition, it fails (hangs).
>> I checked the server process (with ps command), and I can see that
>> connection is 'idle'.  By the way, the size 1484 may not mean anything,
>> since I tried another case (with different comment and table) and the
>> problem still happens but the size 1484 is not the break point.
>
>> I think this may be CentOS(64 bits)/ssh related, since I don't have the
>> problem with CentOS(32 bits) and we have the same application to install
>> the tables with the same command on mandriva.
>
> FWIW, I cannot reproduce this problem using 8.4.latest on Fedora 13 64bit.
> So it might indeed be something specific to the openssl version you're
> using.  I assume you tested that the problem goes away if you use a
> non-SSL connection?
>
> The openssl installation I'm testing with is
> openssl-1.0.0a-1.fc13.x86_64
> I don't know offhand what RHEL/CentOS 5.x are using but it's probably
> quite a lot older.
>
>             regards, tom lane
>

Thanks for your response.  Our SA said that there was a network
configuration set up incorrectly. After the 'Jumbo Frames' was enabled
on the network between the 10G and 1G hosts, the problem was gone.
Sorry, I don't know the detail about the network configuration.

Thanks,
Gary