PostgreSQL -> PHP problem

Started by Mitch Vincentalmost 25 years ago4 messages
#1Mitch Vincent
mitch@venux.net

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

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

#2Mitch Vincent
mitch@venux.net
In reply to: Mitch Vincent (#1)
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

#3Christopher Kings-Lynne
chriskl@familyhealth.com.au
In reply to: Mitch Vincent (#2)
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

Show quoted text

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

#4Mitch Vincent
mitch@venux.net
In reply to: Christopher Kings-Lynne (#3)
Re: Re: PostgreSQL -> PHP problem

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

Show quoted text

an old version of PHP.. Son of a.... er nevermind..

Thanks guys..

-Mitch