Thread: What the heck is happening here?

What the heck is happening here?

From

Paul Tomblin

Date:

11 June 2001, 20:06:59

I have a mod_perl script that opens a database connection, and keeps it
open.  But every day the script stops running.  Today I look in the logs,
and at 10:37 I see my script detect a bad return from a $conn->exec of the
following query:
SELECT     id, datasource_key, type, name, address,
           state, country, latitude, longitude, declination,
           main_frequency, elevation, datasource
FROM       waypoint
WHERE      latitude >= -90 AND latitude <= 90 AND
           longitude >= -180 AND longitude <= 180 AND
           (country IN ('UNITED STATES') OR
           (state IN
('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY')
ANDcountry = 'UNITED STATES') OR 
           (state IN ('') AND country = 'CANADA') ) AND
           type IN ('AIRPORT','DME','FAN MARKER','MARINE
NDB','NDB','NDB/DME','TACAN','UHF/NDB','VOR','VOR/DME','VORTAC')
ORDER BY   id, datasource

(yeah, I know the query has some redundancy in it - that's not the problem
here) but it prints the following message:

message: Unknown protocol character 'Unknown protocol character '8' read from backend.  (The protocol character is the
firstcharacter the backend sends in response to a query it receives). 
Unknown protocol character '0' read from backend.  (The protocol character is the first character the backend sends in
responseto a query it receives). 
Unknown protocol character '.' read from backend.  (The protocol character is the first character the backend sends in
responseto a query it receives). 
Unknown protocol character '3' read from backend.  (The protocol character is the first character the backend sends in
responseto a query it receives). 
Unknown protocol character 'Unknown protocol character '.' read from backend.  (The protocol character is the first
characterthe backend sends in response to a query it receives). 

It returns all the query rows, but I do another query within the query,
and every one of those prints out the following error message:
    PQexec: you gotta get out of a COPY state yourself.
Which is kind of strange because I don't even know how to use COPY.

Then the next connection to the web server does the same thing, but the
next one gets the "you gotta get out of a COPY state yourself" on the big
query.

Any ideas?  Should I stop keeping the connection open so long?

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Your mouse has moved. Windows NT must be restarted for the change to
take effect. Reboot now?  [ OK ]

Re: What the heck is happening here?

From

Alex Pilosov

Date:

11 June 2001, 20:24:24

It smells like somewhere, something, is stepping on itself and tries to
use the same connection in two different processes. Usually I'm getting
this if I'm not careful and if I establish connection before forking. (You
shouldn't do that).

In mod_perl case, make sure that your stuff does not try to establish
connections on startup, and only does that (using ApacheDBI to cache
connections) when the page is requested.

Also, it is possible that every day some script tries to rotate logs and
SIGHUPs the httpd. mod_perl may do strange stuff on restart. Try manually
sending HUP to server and see what happens.

-alex

On Mon, 11 Jun 2001, Paul Tomblin wrote:

> I have a mod_perl script that opens a database connection, and keeps it
> open.  But every day the script stops running.  Today I look in the logs,
> and at 10:37 I see my script detect a bad return from a $conn->exec of the
> following query:
> SELECT     id, datasource_key, type, name, address,
>            state, country, latitude, longitude, declination,
>            main_frequency, elevation, datasource
> FROM       waypoint
> WHERE      latitude >= -90 AND latitude <= 90 AND
>            longitude >= -180 AND longitude <= 180 AND
>            (country IN ('UNITED STATES') OR
>            (state IN
('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY')
ANDcountry = 'UNITED STATES') OR 
>            (state IN ('') AND country = 'CANADA') ) AND
>            type IN ('AIRPORT','DME','FAN MARKER','MARINE
NDB','NDB','NDB/DME','TACAN','UHF/NDB','VOR','VOR/DME','VORTAC')
> ORDER BY   id, datasource
>
> (yeah, I know the query has some redundancy in it - that's not the problem
> here) but it prints the following message:
>
> message: Unknown protocol character 'Unknown protocol character '8' read from backend.  (The protocol character is
thefirst character the backend sends in response to a query it receives). 
> Unknown protocol character '0' read from backend.  (The protocol character is the first character the backend sends
inresponse to a query it receives). 
> Unknown protocol character '.' read from backend.  (The protocol character is the first character the backend sends
inresponse to a query it receives). 
> Unknown protocol character '3' read from backend.  (The protocol character is the first character the backend sends
inresponse to a query it receives). 
> Unknown protocol character 'Unknown protocol character '.' read from backend.  (The protocol character is the first
characterthe backend sends in response to a query it receives). 
>
> It returns all the query rows, but I do another query within the query,
> and every one of those prints out the following error message:
>     PQexec: you gotta get out of a COPY state yourself.
> Which is kind of strange because I don't even know how to use COPY.
>
> Then the next connection to the web server does the same thing, but the
> next one gets the "you gotta get out of a COPY state yourself" on the big
> query.
>
> Any ideas?  Should I stop keeping the connection open so long?
>
>

Re: What the heck is happening here?

From

Paul Tomblin

Date:

11 June 2001, 20:44:53

Quoting Alex Pilosov (alex@pilosoft.com):
> In mod_perl case, make sure that your stuff does not try to establish
> connections on startup, and only does that (using ApacheDBI to cache
> connections) when the page is requested.

Oh oh.  I *am* doing that - I make a connection to grab some stuff right
at start up, and keep that $conn for all the pages.  Is there a good
example of using ApacheDBI to cache connections using Pg.pm I can look at?

> Also, it is possible that every day some script tries to rotate logs and
> SIGHUPs the httpd. mod_perl may do strange stuff on restart. Try manually
> sending HUP to server and see what happens.

Actually, sending HUP to all the httpd processes is the only thing that
fixes it.  For a while.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Courtroom Quotes

Q: Were you alone or by yourself?

Re: What the heck is happening here?

From

Alex Pilosov

Date:

11 June 2001, 21:05:53

On Mon, 11 Jun 2001, Paul Tomblin wrote:

> Quoting Alex Pilosov (alex@pilosoft.com):
> > In mod_perl case, make sure that your stuff does not try to establish
> > connections on startup, and only does that (using ApacheDBI to cache
> > connections) when the page is requested.
>
> Oh oh.  I *am* doing that - I make a connection to grab some stuff right
> at start up, and keep that $conn for all the pages.  Is there a good
> example of using ApacheDBI to cache connections using Pg.pm I can look at?

Don't do it. If you _must_ get some stuff on startup, close the conn after
you are done. See mod_perl references for examples on using of ApacheDBI
(mostly its just 'use ApacheDBI' vs 'use DBI') and no, you cannot use Pg
(the oldstyle, libpq-alike) interface with ApacheDBI, you must use
DBD::Pg.

> > Also, it is possible that every day some script tries to rotate logs and
> > SIGHUPs the httpd. mod_perl may do strange stuff on restart. Try manually
> > sending HUP to server and see what happens.
>
> Actually, sending HUP to all the httpd processes is the only thing that
> fixes it.  For a while.
-alex

Re: What the heck is happening here?

From

Paul Tomblin

Date:

11 June 2001, 21:28:30

Quoting Alex Pilosov (alex@pilosoft.com):
> Don't do it. If you _must_ get some stuff on startup, close the conn after
> you are done. See mod_perl references for examples on using of ApacheDBI
> (mostly its just 'use ApacheDBI' vs 'use DBI') and no, you cannot use Pg
> (the oldstyle, libpq-alike) interface with ApacheDBI, you must use
> DBD::Pg.

That shows how out of it I am - I didn't even know there was a DBD::Pg.
Why isn't it in the postgres-perl RPM?

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
The e-mail of the species is more deadly than the mail.

Re: What the heck is happening here?

From

Alex Pilosov

Date:

11 June 2001, 21:47:20

On Mon, 11 Jun 2001, Paul Tomblin wrote:

> Quoting Alex Pilosov (alex@pilosoft.com):
> > Don't do it. If you _must_ get some stuff on startup, close the conn after
> > you are done. See mod_perl references for examples on using of ApacheDBI
> > (mostly its just 'use ApacheDBI' vs 'use DBI') and no, you cannot use Pg
> > (the oldstyle, libpq-alike) interface with ApacheDBI, you must use
> > DBD::Pg.
>
> That shows how out of it I am - I didn't even know there was a DBD::Pg.
> Why isn't it in the postgres-perl RPM?
Because its not considered (yet, there are talks about inclusion of it) a
part of postgresql core. Its distributed on cpan, use 'perl -MCPAN
-eshell' then 'install DBD::Pg'

-alex

Re: What the heck is happening here?

From

Neil Conway

Date:

12 June 2001, 03:16:50

On Mon, Jun 11, 2001 at 06:17:31PM -0400, Paul Tomblin wrote:
> Quoting Alex Pilosov (alex@pilosoft.com):
> > Don't do it. If you _must_ get some stuff on startup, close the conn after
> > you are done. See mod_perl references for examples on using of ApacheDBI
> > (mostly its just 'use ApacheDBI' vs 'use DBI') and no, you cannot use Pg
> > (the oldstyle, libpq-alike) interface with ApacheDBI, you must use
> > DBD::Pg.
>
> That shows how out of it I am - I didn't even know there was a DBD::Pg.
> Why isn't it in the postgres-perl RPM?

A lot of people don't seem to know this -- I guess they think that
configuring PgSQL '--with-perl' installs the 'one and only' Perl
interface.  Unfortunately for them, they often never find out about
DBD::Pg and DBI, which many people consider to be a more pleasant way
to access databases from Perl.

Perhaps a note in the install documentation could mention the existence
of multiple Perl interfaces and where to go to get DBD::Pg
(www.cpan.org)

Cheers,

Neil

Re: What the heck is happening here?

From

Bruce Momjian

Date:

12 June 2001, 03:46:15

> On Mon, Jun 11, 2001 at 06:17:31PM -0400, Paul Tomblin wrote:
> > Quoting Alex Pilosov (alex@pilosoft.com):
> > > Don't do it. If you _must_ get some stuff on startup, close the conn after
> > > you are done. See mod_perl references for examples on using of ApacheDBI
> > > (mostly its just 'use ApacheDBI' vs 'use DBI') and no, you cannot use Pg
> > > (the oldstyle, libpq-alike) interface with ApacheDBI, you must use
> > > DBD::Pg.
> >
> > That shows how out of it I am - I didn't even know there was a DBD::Pg.
> > Why isn't it in the postgres-perl RPM?
>
> A lot of people don't seem to know this -- I guess they think that
> configuring PgSQL '--with-perl' installs the 'one and only' Perl
> interface.  Unfortunately for them, they often never find out about
> DBD::Pg and DBI, which many people consider to be a more pleasant way
> to access databases from Perl.
>
> Perhaps a note in the install documentation could mention the existence
> of multiple Perl interfaces and where to go to get DBD::Pg
> (www.cpan.org)

I think we need to get DBD:Pg into our distribution.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: What the heck is happening here?

From

Alex Pilosov

Date:

12 June 2001, 04:16:06

On Tue, 12 Jun 2001, Bruce Momjian wrote:

> I think we need to get DBD:Pg into our distribution.
While I think its a great idea for quality of DBD::Pg (Edmund, with all
due respect, does not scale :), it needs to be agreed with Edmund
first, and CPAN releases of DBD::Pg should continue, based on snapshots
from postgresql canonic source. Preferably these releases should be once
every few month (i.e. not necessarily coincided with postgres core
releases).

-alex

Re: What the heck is happening here?

From

Paul Tomblin

Date:

12 June 2001, 10:30:34

Quoting Alex Pilosov (alex@pilosoft.com):
> While I think its a great idea for quality of DBD::Pg (Edmund, with all
> due respect, does not scale :), it needs to be agreed with Edmund
> first, and CPAN releases of DBD::Pg should continue, based on snapshots
> from postgresql canonic source. Preferably these releases should be once
> every few month (i.e. not necessarily coincided with postgres core
> releases).

I don't see why that should be a problem.  There are a lot of perl modules
that are installed by RedHat, for instance, that could also be grabbed
directly from CPAN if you felt like it.  Bundle::CPAN, for one.

Actually, I don't know what would happen if you upgraded a package using
CPAN, and then upgraded the RPM.  I suspect it would work just fine.

But my point is that having a different distribution mechanism (ie making
it part of the official distribution) as well as CPAN seems to work fine.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Programmer (n): One who makes the lies the salesman told come true.

Re: What the heck is happening here?

From

Souk-Aloun Samuel

Date:

12 June 2001, 20:01:59

Paul Tomblin a écrit :
>
> Quoting Alex Pilosov (alex@pilosoft.com):
> > While I think its a great idea for quality of DBD::Pg (Edmund, with all
> > due respect, does not scale :), it needs to be agreed with Edmund
> > first, and CPAN releases of DBD::Pg should continue, based on snapshots
> > from postgresql canonic source. Preferably these releases should be once
> > every few month (i.e. not necessarily coincided with postgres core
> > releases).
>
> I don't see why that should be a problem.  There are a lot of perl modules
> that are installed by RedHat, for instance, that could also be grabbed
> directly from CPAN if you felt like it.  Bundle::CPAN, for one.
>
> Actually, I don't know what would happen if you upgraded a package using
> CPAN, and then upgraded the RPM.  I suspect it would work just fine.
>


There is a problem with man pages:
on my system, rpms packages use bziped man pages,
cpan installs uncompressed man pages (standard make install)
I've noticed that uncompressed man pages have a higher priority ,
so, if you rpm over cpan, you will still use the cpan man page.

a turnover is to use perldoc, you will use the pod file that is common
to both systems


> But my point is that having a different distribution mechanism (ie making
> it part of the official distribution) as well as CPAN seems to work fine.
>
> --
> Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
> Programmer (n): One who makes the lies the salesman told come true.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org