Thread: psql problem
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
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
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
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()
);
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
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
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
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