Thread: PostgreSQL -> PHP problem
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
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
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 > >
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 > > > > > >