executing SELECT xmlelement(name foo); causes "server closed the connection unexpectedly" Error

Hi,

I am trying to exploit XML features of PostgreSQL 8.3.0 DB.

I tried executing following query:

SELECT xmlelement(name foo);

from psql prompt.

I received Following error on screen:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Environment details are:

OS: *CentOS release 5 (Final)*
DB: *PostgreSQL 8.3.0*

Following is DB server log when the query was submitted:

DEBUG: forked new backend, pid=4411 socket=8
LOG: connection received: host=[local]
LOG: connection authorized: user=postgres database=testdb
DEBUG: postmaster child[4411]: starting with (
DEBUG: postgres
DEBUG: -v196608
DEBUG: -y
DEBUG: testdb
DEBUG: )
DEBUG: InitPostgres
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children: <>
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children: <>
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children: <>
LOG: statement: SELECT xmlelement(name foo);
DEBUG: parse tree:
DETAIL: {QUERY :commandType 1 :querySource 0 :canSetTag true
:utilityStmt <>
:resultRelation 0 :intoClause <> :hasAggs false :hasSubLinks
false :rtable <>
:jointree {FROMEXPR :fromlist <> :quals <>} :targetList
({TARGETENTRY :expr
{XMLEXPR :op 1 :name foo :named_args <> :arg_names <> :args <>
:xmloption 0
:type 0 :typmod 0} :resno 1 :resname xmlelement :ressortgroupref
0 :resorigtbl
0 :resorigcol 0 :resjunk false}) :returningList <> :groupClause
<> :havingQual
<> :distinctClause <> :sortClause <> :limitOffset <> :limitCount
<> :rowMarks
<> :setOperations <>}

DEBUG: rewritten parse tree:
DETAIL: ({QUERY :commandType 1 :querySource 0 :canSetTag true
:utilityStmt <>
:resultRelation 0 :intoClause <> :hasAggs false :hasSubLinks
false :rtable <>
:jointree {FROMEXPR :fromlist <> :quals <>} :targetList
({TARGETENTRY :expr
{XMLEXPR :op 1 :name foo :named_args <> :arg_names <> :args <>
:xmloption 0
:type 0 :typmod 0} :resno 1 :resname xmlelement :ressortgroupref
0 :resorigtbl
0 :resorigcol 0 :resjunk false}) :returningList <> :groupClause
<> :havingQual
<> :distinctClause <> :sortClause <> :limitOffset <> :limitCount
<> :rowMarks
<> :setOperations <>})

DEBUG: plan:
DETAIL: {PLANNEDSTMT :commandType 1 :canSetTag true :planTree {RESULT
:startup_cost
0.00 :total_cost 0.01 :plan_rows 1 :plan_width 0 :targetlist
({TARGETENTRY
:expr {XMLEXPR :op 1 :name foo :named_args <> :arg_names <> :args <>
:xmloption 0 :type 0 :typmod 0} :resno 1 :resname xmlelement
:ressortgroupref
0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :qual <> :lefttree <>
:righttree <> :initPlan <> :extParam (b) :allParam (b)
:resconstantqual <>}
:rtable <> :resultRelations <> :utilityStmt <> :intoClause <>
:subplans <>
:rewindPlanIDs (b) :returningLists <> :rowMarks <> :relationOids <>
:nParamExec 0}

*postgres: postgres testdb [local] SELECT: symbol lookup error:
postgres: postgres testdb [local] SELECT: undefined symbol:
xmlNewTextWriterMemory*
DEBUG: reaping dead processes
DEBUG: server process (PID 4411) exited with exit code 127
LOG: server process (PID 4411) exited with exit code 127
LOG: terminating any other active server processes
DEBUG: sending SIGQUIT to process 4401
DEBUG: sending SIGQUIT to process 4402
DEBUG: sending SIGQUIT to process 4403
DEBUG: sending SIGQUIT to process 4404
LOG: all server processes terminated; reinitializing
DEBUG: shmem_exit(0)
DEBUG: invoking IpcMemoryCreate(size=39288832)
DEBUG: reaping dead processes
LOG: database system was interrupted; last known up at 2008-11-20
03:48:07 EST
LOG: connection received: host=[local]
FATAL: the database system is in recovery mode
DEBUG: checkpoint record is at 0/367B0468
DEBUG: redo record is at 0/367B0468; shutdown TRUE
DEBUG: next transaction ID: 0/682118; next OID: 81920
DEBUG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
DEBUG: proc_exit(1)
DEBUG: shmem_exit(1)
DEBUG: forked new backend, pid=4413 socket=8
DEBUG: exit(1)
DEBUG: reaping dead processes
DEBUG: server process (PID 4413) exited with exit code 1
LOG: record with zero length at 0/367B04B0
LOG: redo is not required
DEBUG: transaction ID wrap limit is 2147484025, limited by database
"template1"
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

The server appeared as crashed and got restarted.

Seek your help to have this issue fixed.

Thanks,
Sushil

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.

Sushil wrote:
> I am trying to exploit XML features of PostgreSQL 8.3.0 DB.

You should upgrade to the latest 8.3 release, currently 8.3.5. There
were some fixes in this area (and other areas).

I tried installing following RPMs

postgresql-8.3.5-1PGDG.rhel5.x86_64.rpm
postgresql-libs-8.3.5-1PGDG.rhel5.x86_64.rpm
postgresql-server-8.3.5-1PGDG.rhel5.x86_64.rpm

still this issue is appearing.

is there any thing i am missing?

Please advise.

Thanks,

Peter Eisentraut wrote:
> Sushil wrote:
>> I am trying to exploit XML features of PostgreSQL 8.3.0 DB.
>
> You should upgrade to the latest 8.3 release, currently 8.3.5. There
> were some fixes in this area (and other areas).
>
>

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.

Sushil wrote:
> *postgres: postgres testdb [local] SELECT: symbol lookup error:
> postgres: postgres testdb [local] SELECT: undefined symbol:
> xmlNewTextWriterMemory*

Your problem appears to be here. Check you libxml installation. Maybe
someone forgot to export this symbol.

xmlNewTextWriterMemory might be causing this issue.

I have following details in this regard.

the query works on postgres 8.3 installation on a system with following
libxml2 istalled

libxml2-2.6.26-2.1.2
libxml2-python-2.6.26-2.1.2
libxml2-2.6.26-2.1.2

its a Centos 5.1 environment.

the system on which this issue is appearing has following libxml2 RPMs
installed

libxml2-2.6.26-2.1.2.1
libxml2-python-2.6.26-2.1.2.1
libxml2-2.6.26-2.1.2.1

please note that system where the issue is has libxml2 library with
higher version.

to see if this higher version is causing the issue, i installed these
RPMs on earlier system (where the select query works). the query still
worked.

Peter Eisentraut wrote:
> Sushil wrote:
>> *postgres: postgres testdb [local] SELECT: symbol lookup error:
>> postgres: postgres testdb [local] SELECT: undefined symbol:
>> xmlNewTextWriterMemory*
>
> Your problem appears to be here. Check you libxml installation.
> Maybe someone forgot to export this symbol.
>
>

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.

Sushil <sdive(at)vertex(dot)co(dot)in> writes:
> the query works on postgres 8.3 installation on a system with following
> libxml2 istalled

> libxml2-2.6.26-2.1.2
> libxml2-python-2.6.26-2.1.2
> libxml2-2.6.26-2.1.2

> its a Centos 5.1 environment.

> the system on which this issue is appearing has following libxml2 RPMs
> installed

> libxml2-2.6.26-2.1.2.1
> libxml2-python-2.6.26-2.1.2.1
> libxml2-2.6.26-2.1.2.1

According to Red Hat's CVS, the only difference between 2.1.2 and
2.1.2.1 is addition of a patch to fix UTF-8 decoding problem
CVE-2007-6284. I looked at the patch and it is nowhere near
xmlNewTextWriterMemory.

Perhaps you have a corrupted download of that RPM? In any case
it's out of date (latest Red Hat release is 2.6.26-2.1.9) so an
update wouldn't be a bad idea.

regards, tom lane

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Sushil wrote:
>> *postgres: postgres testdb [local] SELECT: symbol lookup error:
>> postgres: postgres testdb [local] SELECT: undefined symbol:
>> xmlNewTextWriterMemory*

> Your problem appears to be here. Check you libxml installation. Maybe
> someone forgot to export this symbol.

I think it most likely is a corrupted download. Red Hat's normal
release process includes checks for ABI breakage such as disappearing
symbols, so it's hard to believe there's really a problem of that ilk.

The annoying thing about this from a Postgres standpoint is that the
missing symbol results in a runtime crash; a failure at server startup
would be a lot better IMHO. Ideally, shared libraries would be
processed with the equivalent of dlopen(RTLD_NOW). But as far as I
can find we can't easily force that on Linux --- the only available
way to determine it is to set a magic environment variable that the
linker consults.

I could fix this in the RPM distribution by putting export LD_BIND_NOW=1
into the postmaster start script, but I wonder whether there's a better
way.

regards, tom lane

Here is an update.

the 8.3 version is now working after i reinstalled the OS.

most probably the issue was due to corrupt libxml2.

Thank you so much for all the help to get rid off the issue.

Regards,
Sushil

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>
>> Sushil wrote:
>>
>>> *postgres: postgres testdb [local] SELECT: symbol lookup error:
>>> postgres: postgres testdb [local] SELECT: undefined symbol:
>>> xmlNewTextWriterMemory*
>>>
>
>
>> Your problem appears to be here. Check you libxml installation. Maybe
>> someone forgot to export this symbol.
>>
>
> I think it most likely is a corrupted download. Red Hat's normal
> release process includes checks for ABI breakage such as disappearing
> symbols, so it's hard to believe there's really a problem of that ilk.
>
> The annoying thing about this from a Postgres standpoint is that the
> missing symbol results in a runtime crash; a failure at server startup
> would be a lot better IMHO. Ideally, shared libraries would be
> processed with the equivalent of dlopen(RTLD_NOW). But as far as I
> can find we can't easily force that on Linux --- the only available
> way to determine it is to set a magic environment variable that the
> linker consults.
>
> I could fix this in the RPM distribution by putting export LD_BIND_NOW=1
> into the postmaster start script, but I wonder whether there's a better
> way.
>
> regards, tom lane
>
>

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.