Thread: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup

Hi team,

I am trying to migrate the data from db2 to postgreSQL in which one of the table is having XML data.
For one of the file (13MB) I'm facing an error with 

ERROR: invalid XML content
Detail: line 418061: internal error: Huge input lookup
nested exception is org.postgresql.util.PSQLException: ERROR: invalid XML content

I even tried with more size of XML data (30MB) but for this case I can able to migrate the date successfully.

But only for that file I'm facing the issue.
Later, I got to know that there are some special characters in that file not sure whether this can be the issue or not.

I'm using java.sql.preparedstatement
Preparedstatement.setSQLXML(iterator, xmlobject) to migrate the data from db2 to postgreSQL
When I tried in my local postgreSQL (which is windows and located in APAC region. I can able to migrate successfully)
But when I'm trying to migrate into azure postgreSQL (Linux OS, located in Germany) facing an issue.

I request you to please look into this and help me to resolve this error.

Please let me know if any queries.

Looking forward for the resolution.

Thanks & regards,
Sai Teja

On 8/3/23 21:22, Sai Teja wrote:
Hi team,

I am trying to migrate the data from db2 to postgreSQL in which one of the table is having XML data.
For one of the file (13MB) I'm facing an error with 

ERROR: invalid XML content
Detail: line 418061: internal error: Huge input lookup
nested exception is org.postgresql.util.PSQLException: ERROR: invalid XML content

I even tried with more size of XML data (30MB) but for this case I can able to migrate the date successfully.

But only for that file I'm facing the issue.
Later, I got to know that there are some special characters in that file not sure whether this can be the issue or not.

I'm using java.sql.preparedstatement
Preparedstatement.setSQLXML(iterator, xmlobject) to migrate the data from db2 to postgreSQL
When I tried in my local postgreSQL (which is windows and located in APAC region. I can able to migrate successfully)
But when I'm trying to migrate into azure postgreSQL (Linux OS, located in Germany) facing an issue.

That sounds like it has to do with locale differences between the two systems.

Exactly what Pg version (including patch level) and locale are in each of the two systems?

Even if it's not that, something is configured differently between the two systems.

--
Born in Arizona, moved to Babylonia.
Thank you for the reply.

The pg version is 14.7 (Azure DB)
Locale :-
LC_COLLATE & LC_CTYPE : en_US.utf_8

Local postgreSQL:- Version:- (15.1-1-windows-x64-binaries)

LC_COLLATE & LC_CTYPE:-
English_United States.1252

Please let me know if any other information is needed.

Thanks & Best Regards,
Sai Teja






On Fri, 4 Aug, 2023, 8:03 am Ron, <ronljohnsonjr@gmail.com> wrote:
On 8/3/23 21:22, Sai Teja wrote:
Hi team,

I am trying to migrate the data from db2 to postgreSQL in which one of the table is having XML data.
For one of the file (13MB) I'm facing an error with 

ERROR: invalid XML content
Detail: line 418061: internal error: Huge input lookup
nested exception is org.postgresql.util.PSQLException: ERROR: invalid XML content

I even tried with more size of XML data (30MB) but for this case I can able to migrate the date successfully.

But only for that file I'm facing the issue.
Later, I got to know that there are some special characters in that file not sure whether this can be the issue or not.

I'm using java.sql.preparedstatement
Preparedstatement.setSQLXML(iterator, xmlobject) to migrate the data from db2 to postgreSQL
When I tried in my local postgreSQL (which is windows and located in APAC region. I can able to migrate successfully)
But when I'm trying to migrate into azure postgreSQL (Linux OS, located in Germany) facing an issue.

That sounds like it has to do with locale differences between the two systems.

Exactly what Pg version (including patch level) and locale are in each of the two systems?

Even if it's not that, something is configured differently between the two systems.

--
Born in Arizona, moved to Babylonia.
I would install 14.7 on your Windows box (multiversioning is supported, at least on Linux; probably Windows too), making sure that it uses the en_US.utf_8 locale (or the closest Windows analog).  Then I'd try the same migration on the Windows box.

After all development boxes should run the same software version as production...

On 8/3/23 22:46, Sai Teja wrote:
Thank you for the reply.

The pg version is 14.7 (Azure DB)
Locale :-
LC_COLLATE & LC_CTYPE : en_US.utf_8

Local postgreSQL:- Version:- (15.1-1-windows-x64-binaries)

LC_COLLATE & LC_CTYPE:-
English_United States.1252

Please let me know if any other information is needed.

Thanks & Best Regards,
Sai Teja






On Fri, 4 Aug, 2023, 8:03 am Ron, <ronljohnsonjr@gmail.com> wrote:
On 8/3/23 21:22, Sai Teja wrote:
Hi team,

I am trying to migrate the data from db2 to postgreSQL in which one of the table is having XML data.
For one of the file (13MB) I'm facing an error with 

ERROR: invalid XML content
Detail: line 418061: internal error: Huge input lookup
nested exception is org.postgresql.util.PSQLException: ERROR: invalid XML content

I even tried with more size of XML data (30MB) but for this case I can able to migrate the date successfully.

But only for that file I'm facing the issue.
Later, I got to know that there are some special characters in that file not sure whether this can be the issue or not.

I'm using java.sql.preparedstatement
Preparedstatement.setSQLXML(iterator, xmlobject) to migrate the data from db2 to postgreSQL
When I tried in my local postgreSQL (which is windows and located in APAC region. I can able to migrate successfully)
But when I'm trying to migrate into azure postgreSQL (Linux OS, located in Germany) facing an issue.

That sounds like it has to do with locale differences between the two systems.

Exactly what Pg version (including patch level) and locale are in each of the two systems?

Even if it's not that, something is configured differently between the two systems.

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.