psql problem

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 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

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