Thread: PostgreSQL -> PHP problem

PostgreSQL -> PHP problem

From
"Mitch Vincent"
Date:
This is the debug output for the last query that seems to be throwing PHP
into a fit (a fit that somehow closes the backend connection - note, it
doesn't crash, it just closes)..

I don't think anything is going on here that shouldn't be, it looks the same
as any other query that succeeds.. I just wanted someone that could actually
read and understand this to take a look..

Thanks!

Debug output follows ---


ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT * FROM app_degrees
parser outputs:

{ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false
:isBinary false :isTemp false :unionall false :distinctClause <> :sortClause
<> :rtable ({ RTE :relname app_degrees :ref { ATTR :relname app_degrees
:attrs <>} :relid 660864 :inh false :inFromCl true :inJoinSet true :skipAcl
false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname degree_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resno 2 :restype 1043 :restypmod 14 :resname abbr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname
description :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 54  :varlevelsup 0
:varnoold 1 :varoattno 3}}) :qual <> :groupClause <> :havingQual <> :hasAggs
false :hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <>
:limitCount <> :rowMark <>}

after rewriting:
{ QUERY  :command 1  :utility <>  :resultRelation 0  :into <>  :isPortal false  :isBinary false  :isTemp false
:unionallfalse  :distinctClause <>  :sortClause <>  :rtable (     { RTE     :relname app_degrees     :ref        { ATTR
      :relname app_degrees        :attrs <>        }
 
     :relid 660864     :inh false     :inFromCl true     :inJoinSet true     :skipAcl false     }  )
  :targetlist (     { TARGETENTRY     :resdom        { RESDOM        :resno 1        :restype 23        :restypmod -1
    :resname degree_id        :reskey 0        :reskeyop 0        :ressortgroupref 0        :resjunk false        }
 
     :expr        { VAR        :varno 1        :varattno 1        :vartype 23        :vartypmod -1        :varlevelsup
0       :varnoold 1        :varoattno 1        }     }
 
     { TARGETENTRY     :resdom        { RESDOM        :resno 2        :restype 1043        :restypmod 14
:resnameabbr        :reskey 0        :reskeyop 0        :ressortgroupref 0        :resjunk false        }
 
     :expr        { VAR        :varno 1        :varattno 2        :vartype 1043        :vartypmod 14
:varlevelsup0        :varnoold 1        :varoattno 2        }     }
 
     { TARGETENTRY     :resdom        { RESDOM        :resno 3        :restype 1043        :restypmod 54
:resnamedescription        :reskey 0        :reskeyop 0        :ressortgroupref 0        :resjunk false        }
 
     :expr        { VAR        :varno 1        :varattno 3        :vartype 1043        :vartypmod 54
:varlevelsup0        :varnoold 1        :varoattno 3        }     }  )
 
  :qual <>  :groupClause <>  :havingQual <>  :hasAggs false  :hasSubLinks false  :unionClause <>  :intersectClause <>
:limitOffset<>  :limitCount <>  :rowMark <>  }
 

plan:

{ SEQSCAN :startup_cost 0.00 :total_cost 20.00 :rows 1000 :width 28 :state
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname degree_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resno 2 :restype 1043 :restypmod 14 :resname abbr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname
description :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 54  :varlevelsup 0
:varnoold 1 :varoattno 3}}) :qpqual <> :lefttree <> :righttree <> :extprm ()
:locprm () :initplan <> :nprm 0  :scanrelid 1 }

ProcessQuery
CommitTransactionCommand
proc_exit(0)
shmem_exit(0)
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 45155 exited with status 0







Re: PostgreSQL -> PHP problem

From
"Mitch Vincent"
Date:
In the PHP bugs I see...


> > ===============================================[PostgreSQL
>related]===========
> > 5862 Open       Consecutive pg_open statements cause second statement to
>fail
> > 6525 Open       Connection problem
> > 7007 Open       The pg_close function doesn't close the connection.
> > 7236 Open       1 is not a valid PostgreSQL link resource
> > 7264 Open       1 is not a valid PostgreSQL link
> > 7298 Open       ... not a valid link resource... after pg_connect
> > 7312 Open       Problems with pg_connect() i pg_fetch_row()
> > 7333 Open       Connection fault in circled query
> > 7529 Open       pg_connect() returns invalid connection id
> > 7536 Open       Warning: is not a valid PostgreSQL link resource xxxx
> > 7931 Feedback   Undefined symbol "_PQconnectdb"
> > 8053 Open       PGSQL doesn't detects on FBSD4
> > 8225 Open       Suddenly doesnt allow multiple psql connections from one
>php page
> > 8317 Open       postgresql table uppercase field name
> > 8689 Open       pg_Connect() seems to do some type of caching that
doesn't
>quite work
> > 8769 Open       Persistent connections aren't closed when using
>dynamically loaded module
> > 8907 Open       pg_Close on multiple connections to same host
> > 9048 Open       problem to open several connections on 4.0.4pl1 that
>worked on 4.0.2

Ouch. It looks like this is exactly what is happening to me. pg_open gets
called several times in these scripts.. It looks like I'll have to install
an old version of PHP.. Son of a.... er nevermind..

Thanks guys..

-Mitch




RE: Re: PostgreSQL -> PHP problem

From
"Christopher Kings-Lynne"
Date:
I tell you what I'd like to see in PHP.  If you're using a Postgres
persistent connection, and it detects a 'BEGIN TRANSACTION' going thru, once
that script has finished, the connection should not be returned to the
connection pool.

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Mitch Vincent
> Sent: Tuesday, February 06, 2001 2:29 AM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] Re: PostgreSQL -> PHP problem
>
>
> In the PHP bugs I see...
>
>
> > > ===============================================[PostgreSQL
> >related]===========
> > > 5862 Open       Consecutive pg_open statements cause second
> statement to
> >fail
> > > 6525 Open       Connection problem
> > > 7007 Open       The pg_close function doesn't close the connection.
> > > 7236 Open       1 is not a valid PostgreSQL link resource
> > > 7264 Open       1 is not a valid PostgreSQL link
> > > 7298 Open       ... not a valid link resource... after pg_connect
> > > 7312 Open       Problems with pg_connect() i pg_fetch_row()
> > > 7333 Open       Connection fault in circled query
> > > 7529 Open       pg_connect() returns invalid connection id
> > > 7536 Open       Warning: is not a valid PostgreSQL link resource xxxx
> > > 7931 Feedback   Undefined symbol "_PQconnectdb"
> > > 8053 Open       PGSQL doesn't detects on FBSD4
> > > 8225 Open       Suddenly doesnt allow multiple psql
> connections from one
> >php page
> > > 8317 Open       postgresql table uppercase field name
> > > 8689 Open       pg_Connect() seems to do some type of caching that
> doesn't
> >quite work
> > > 8769 Open       Persistent connections aren't closed when using
> >dynamically loaded module
> > > 8907 Open       pg_Close on multiple connections to same host
> > > 9048 Open       problem to open several connections on 4.0.4pl1 that
> >worked on 4.0.2
>
> Ouch. It looks like this is exactly what is happening to me. pg_open gets
> called several times in these scripts.. It looks like I'll have to install
> an old version of PHP.. Son of a.... er nevermind..
>
> Thanks guys..
>
> -Mitch
>
>



Re: Re: PostgreSQL -> PHP problem

From
"Mitch Vincent"
Date:
Bruce said he and Rasmus (from PHP devel) were fixing this. That'll be
great!

-Mitch

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: <pgsql-hackers@postgresql.org>
Sent: Monday, February 05, 2001 8:55 PM
Subject: RE: Re: PostgreSQL -> PHP problem


> I tell you what I'd like to see in PHP.  If you're using a Postgres
> persistent connection, and it detects a 'BEGIN TRANSACTION' going thru,
once
> that script has finished, the connection should not be returned to the
> connection pool.
>
> Chris
>
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Mitch Vincent
> > Sent: Tuesday, February 06, 2001 2:29 AM
> > To: pgsql-hackers@postgresql.org
> > Subject: [HACKERS] Re: PostgreSQL -> PHP problem
> >
> >
> > In the PHP bugs I see...
> >
> >
> > > > ===============================================[PostgreSQL
> > >related]===========
> > > > 5862 Open       Consecutive pg_open statements cause second
> > statement to
> > >fail
> > > > 6525 Open       Connection problem
> > > > 7007 Open       The pg_close function doesn't close the connection.
> > > > 7236 Open       1 is not a valid PostgreSQL link resource
> > > > 7264 Open       1 is not a valid PostgreSQL link
> > > > 7298 Open       ... not a valid link resource... after pg_connect
> > > > 7312 Open       Problems with pg_connect() i pg_fetch_row()
> > > > 7333 Open       Connection fault in circled query
> > > > 7529 Open       pg_connect() returns invalid connection id
> > > > 7536 Open       Warning: is not a valid PostgreSQL link resource
xxxx
> > > > 7931 Feedback   Undefined symbol "_PQconnectdb"
> > > > 8053 Open       PGSQL doesn't detects on FBSD4
> > > > 8225 Open       Suddenly doesnt allow multiple psql
> > connections from one
> > >php page
> > > > 8317 Open       postgresql table uppercase field name
> > > > 8689 Open       pg_Connect() seems to do some type of caching that
> > doesn't
> > >quite work
> > > > 8769 Open       Persistent connections aren't closed when using
> > >dynamically loaded module
> > > > 8907 Open       pg_Close on multiple connections to same host
> > > > 9048 Open       problem to open several connections on 4.0.4pl1 that
> > >worked on 4.0.2
> >
> > Ouch. It looks like this is exactly what is happening to me. pg_open
gets
> > called several times in these scripts.. It looks like I'll have to
install
> > an old version of PHP.. Son of a.... er nevermind..
> >
> > Thanks guys..
> >
> > -Mitch
> >
> >
>
>