XML load fails on postgresql 9.1, but not in 9.3, any workaround?

Question

I have a problem loading an xml on an xml field of a table.
The load is done by ruby code, but this is not the point.
Everything worked well for years, but xml is increasing size....

So one day, today, xml is near 12 megabytes. Sysadmin called dba (me): the service is not working!

I did some test. Result is: when I test this query:

SELECT * FROM XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('xmlstuff.xml'), 'UTF8'))

It works fine on Postgresql 9.3
It gives me this in 9.1 in production or test:

ERROR: invalid XML document
DETAIL: Entity: line 1: parser error : internal error
Entity: line 1: parser error : Extra content at the end of the document

Any workaround for this? I'm planning an upgrade in the next months, but I have the problem now :-(

• Kassandry: Can you give us an example of the XML that's failing to parse?
• user_0: I try to retrive one. Sadly I cannot publish a real internal XML.

Answer 1

You hint us about the XML size but it may well not have anything to do with your problem.

If it worked fine for years, I would suspect something has changed in the XML format.

If the XML file appears to be valid (since it works in 9.3), you may be hitting a bug with previous versions of POSTGRES.

Try comparing 2 XMLs, before and after it started failing.

Apparently POSTGRES wasn't working well with XML files having DOCTYPE elements in it. This is only an example (although that could be it)

So while the XML file may be technically good, a change in its format may have place you in the way of a bug in versions prior to 9.3.

For your test, try an older XML file and a new one on the 9.1 version. If one works, you know it's a problem in the file format. If not, you at least know it's not the XML format.

You may also want to verify if there was any patching of the OS done. Postgres uses libxml2 to parse XMLs. if that has changed, this could be it and a downgrade could solve your problem.