2

I have a PostgreSQL database with some pretty big documents in XML format added into a table which has an XML type field. I am running an XMLTABLE query with Xpath expression and I have the following error:

[2200M] ERROR: could not parse XML document 
Detail: line 91735: internal error: Huge input lookup

The query is pretty simple and it works without issues on other records:

SELECT xmltable.*
FROM poc.xmlcontent,
    XMLTABLE(
            '//section'
            PASSING document
            COLUMNS
                id varchar PATH '@id'
        )
WHERE poc.xmlcontent.id = 375;

Some of the pgsql settings that I checked:

huge_page_size|0
huge_pages|on
shared_buffers|327680
shared_memory_size|278MB
shared_memory_size_in_huge_pages|140
shared_memory_type|mmap
temp_buffers|8MB
work_mem|4MB

Thanks

UPDATE I just found out that this happens only in documents where the file ends with an image and the images in the documents are base 64 decoded from the file and included into the markup. Here is an example of the end of a document that produces this error:

            ...
            <p class="content_center">
                <img src="data:image/jpeg;base64,/9j/4AAQSkZJ...."/>
            </p>
            </section>
        </section>
    </section>
</body>
</xml>

Only if I put an extra section with content right before the closing body it will get fixed. But if I add some content after the image on the same level it will still produce the error. If I remove the image its ok and also if the image is not base 64 into the document it is also ok. If I use any image it will still fail, so the image itself is not the problem.

3
  • 1
    Seems like a bug in the xml parser. You should consider filing a bug report.
    – Nifle
    Commented Sep 29, 2023 at 6:54
  • Have you tried to change the XML parse option from CONTENT to DOCUMENT as mentioned in the solution from: stackoverflow.com/a/66949422/290087 ? Commented Oct 3, 2023 at 16:25
  • @MirceaVutcovici I just tried it, but the error is the same unfortunately. Thank you for your suggestion, it was a good idea.
    – thefallen
    Commented Oct 4, 2023 at 10:51

1 Answer 1

0

I think that the XML element is too large to be parsed.

From the parser.c source code of libxml2 you can see where this error is generated:

if ((ctxt->input != NULL) &&
     (((ctxt->input->end - ctxt->input->cur) > XML_MAX_LOOKUP_LIMIT) ||
     ((ctxt->input->cur - ctxt->input->base) > XML_MAX_LOOKUP_LIMIT)) &&
    ((ctxt->options & XML_PARSE_HUGE) == 0)) {
    xmlFatalErr(ctxt, XML_ERR_INTERNAL_ERROR, "Huge input lookup");
    xmlHaltParser(ctxt);
}

The limit of XML_MAX_LOOKUP_LIMIT is set in parserInternals.h to 10000000:

/**
 * XML_MAX_LOOKUP_LIMIT:
 *
 * Maximum size allowed by the parser for ahead lookup
 * This is an upper boundary enforced by the parser to avoid bad
 * behaviour on "unfriendly' content
 * Introduced in 2.9.0
 */
#define XML_MAX_LOOKUP_LIMIT 10000000

So I expect any image larger than 7.15MB, that is base64 encoded into the XML, to generate an exception. Could you verify this limit?

I think that you need to parse the XML in the application server, not in the postgresql DB, or use smaller images.

4
  • Like I mentioned in my update, if I put another section tag right before the closing body tag it will get fixed, so I don't think its related to this size. My thinking is more that after a certain size (large documents) the parser doesn't like it when the document ends with a base64 image. Thank you for your suggestion.
    – thefallen
    Commented Oct 5, 2023 at 3:53
  • Would it be practical to parse the XML inside the application? Or you need to use the DB for this? Commented Oct 5, 2023 at 3:59
  • I need to run this XMLTABLE Xpath to pull specific data from the document and this is working fine for another 180 documents, but not this one. At this point I don't have any hope of solving this, so I might SELECT the entire XML and just run the Xpath query with PHP for this specific document.
    – thefallen
    Commented Oct 5, 2023 at 4:11
  • You need to set XML_PARSE_HUGE, but I cound't find how to do this in postgresql. Commented Oct 5, 2023 at 4:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .