pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml
11+ messages / 8 participants
[nested] [flat]

* org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml
@ 2013-11-19 06:27  gajendra s v <svgajendra@gmail.com>
  0 siblings, 2 replies; 11+ messages in thread

From: gajendra s v @ 2013-11-19 06:27 UTC (permalink / raw)
  To: pgsql-general

Hi All,


I have added one column with xml type ,after adding I am getting following
error.


org.postgresql.util.PSQLException: ERROR: could not identify an equality
operator for type xml

If I have removed column following query works fine

select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID from
 (select  OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from  KM_REL_OBJ_PER_ACTION
where OBJ_TYPE='COURSETYPE') g where  PERFORMER_TYPE='GROUP' and
PERFORMER_ID in ((WITH RECURSIVE parents as ( select PARENT_ID from
KM_REL_SELF_GROUP where CHILD_ID in ( SELECT GROUP_ID FROM KM_REL_GRP_USER
WHERE USER_ID=46467) UNION select a.PARENT_ID FROM KM_REL_SELF_GROUP a
,parents p where a.CHILD_ID = p.PARENT_ID ) select PARENT_ID from  parents
order by PARENT_ID asc)union SELECT GROUP_ID FROM KM_REL_GRP_USER WHERE
USER_ID=46467)) union select * from KM_COURSE_MAST where
CREATED_BY='46467') KM_COURSE_MAST   order by DISPLAYORDER

Thanks,
Gajendra

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml
@ 2013-11-19 06:42  David Johnston <polobo@yahoo.com>
  parent: gajendra s v <svgajendra@gmail.com>
  1 sibling, 0 replies; 11+ messages in thread

From: David Johnston @ 2013-11-19 06:42 UTC (permalink / raw)
  To: pgsql-general

gajendra s v wrote
> Hi All,
> 
> 
> I have added one column with xml type ,after adding I am getting following
> error.
> 
> 
> org.postgresql.util.PSQLException: ERROR: could not identify an equality
> operator for type xml
> 
> If I have removed column following query works fine
> 
> select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID
> from
>  (select  OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from  KM_REL_OBJ_PER_ACTION
> where OBJ_TYPE='COURSETYPE') g where  PERFORMER_TYPE='GROUP' and
> PERFORMER_ID in ((WITH RECURSIVE parents as ( select PARENT_ID from
> KM_REL_SELF_GROUP where CHILD_ID in ( SELECT GROUP_ID FROM KM_REL_GRP_USER
> WHERE USER_ID=46467) UNION select a.PARENT_ID FROM KM_REL_SELF_GROUP a
> ,parents p where a.CHILD_ID = p.PARENT_ID ) select PARENT_ID from  parents
> order by PARENT_ID asc)union SELECT GROUP_ID FROM KM_REL_GRP_USER WHERE
> USER_ID=46467)) union select * from KM_COURSE_MAST where
> CREATED_BY='46467') KM_COURSE_MAST   order by DISPLAYORDER
> 
> Thanks,
> Gajendra

XML has no concept of equality though I do not see any obvious use of an XML
column in the supplied query.  Is there maybe a view involved you are not
telling us about?  Are you positive it's this query that has the problem? 
Your report is short on specifics - like the name of the XML column you
added and on what table, for instance.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/org-postgresql-util-PSQLException-ERROR-could-not-identify-a...
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml
@ 2013-11-19 22:11  Kevin Grittner <kgrittn@ymail.com>
  parent: gajendra s v <svgajendra@gmail.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Kevin Grittner @ 2013-11-19 22:11 UTC (permalink / raw)
  To: gajendra s v <svgajendra@gmail.com>; pgsql-general

gajendra s v <svgajendra@gmail.com> wrote:

> I have added one column with xml type ,after adding I am getting
> following error.
>
> org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml

The UNION operation requires comparing all columns in the result
record to remove duplicates.  To identify duplicates, there must be
a notion of equality defined (getting technical here) by a default
btree opclass for the type.  XML does not have one, so UNION of a
result set including an XML column is not allowed.

Do you need the duplicate removal?  If not, use UNION ALL.  If you
need that you may need to cast the XML to text for intermediate
steps and then cast back to XML at the outermost level.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml
@ 2013-11-19 22:19  David Johnston <polobo@yahoo.com>
  parent: Kevin Grittner <kgrittn@ymail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: David Johnston @ 2013-11-19 22:19 UTC (permalink / raw)
  To: pgsql-general

Kevin Grittner-5 wrote
> gajendra s v &lt;

> svgajendra@

> &gt; wrote:
> 
>> I have added one column with xml type ,after adding I am getting
>> following error.
>>
>> org.postgresql.util.PSQLException: ERROR: could not identify an equality
>> operator for type xml
> 
> The UNION operation requires comparing all columns in the result
> record to remove duplicates.  To identify duplicates, there must be
> a notion of equality defined (getting technical here) by a default
> btree opclass for the type.  XML does not have one, so UNION of a
> result set including an XML column is not allowed.
> 
> Do you need the duplicate removal?  If not, use UNION ALL.  If you
> need that you may need to cast the XML to text for intermediate
> steps and then cast back to XML at the outermost level.

The other option is to only union the PK field(s) on KM_COURSE_MAST then as
a final outer query join that union-ed result back with KM_COURSE_MAST using
the PK and attach whatever additional columns your query desires.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/org-postgresql-util-PSQLException-ERROR-could-not-identify-a...
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory
@ 2018-03-06 01:54  Raghavendra Rao J S V <raghavendrajsv@gmail.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Raghavendra Rao J S V @ 2018-03-06 01:54 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi,

Few of the tables and indexes are bloated though auto vacuum has enables.

Two reclaim the space vacuum, I have ran vacuum full on the larger tables
and also performed reindex on the indexes which are in larger size. Now the
size of the database is in control.

After perfoming the vacuum full and reindexing on larger tables, I am
facing below error.

*org.postgresql.util.PSQLException: Error could not open file "base/": No
such file or directory*

Please guide me how to resolve the above error and let me know does this
has any relation with vacumm full or reindexing operation which are
performed by me.

-- 
Regards,
Raghavendra Rao J S V

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory
@ 2018-03-06 02:05  Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
  parent: Raghavendra Rao J S V <raghavendrajsv@gmail.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Alvaro Aguayo Garcia-Rada @ 2018-03-06 02:05 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org, Raghavendra Rao J S V <raghavendrajsv@gmail.com>

What do you have on your postgresql logs, on the server?

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Raghavendra Rao J S V wrote ----

Hi,

Few of the tables and indexes are bloated though auto vacuum has enables.

Two reclaim the space vacuum, I have ran vacuum full on the larger tables
and also performed reindex on the indexes which are in larger size. Now the
size of the database is in control.

After perfoming the vacuum full and reindexing on larger tables, I am
facing below error.

*org.postgresql.util.PSQLException: Error could not open file "base/": No
such file or directory*

Please guide me how to resolve the above error and let me know does this
has any relation with vacumm full or reindexing operation which are
performed by me.

-- 
Regards,
Raghavendra Rao J S V

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory
@ 2018-03-06 05:13  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Raghavendra Rao J S V <raghavendrajsv@gmail.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Adrian Klaver @ 2018-03-06 05:13 UTC (permalink / raw)
  To: Raghavendra Rao J S V <raghavendrajsv@gmail.com>; pgsql-general@lists.postgresql.org

On 03/05/2018 05:54 PM, Raghavendra Rao J S V wrote:
> Hi,
> 
> Few of the tables and indexes are bloated though auto vacuum has enables.
> 
> Two reclaim the space vacuum, I have ran vacuum full on the larger 
> tables and also performed reindex on the indexes which are in larger 
> size. Now the size of the database is in control.
> 
> After perfoming the vacuum full and reindexing on larger tables, I am 
> facing below error.
> 
> *org.postgresql.util.PSQLException: Error could not open file "base/": 
> No such file or directory*

Assuming the database is up and running then base/ is actually there:

https://www.postgresql.org/docs/10/static/storage-file-layout.html


When I know a file or directory exists and I see an error like:

'Error could not open file "base/": No such file or directory '

then I generally check permissions on said file/directory. In particular 
does the app throwing the error having the permissions to 'see' the 
file/directory?

> 
> Please guide me how to resolve the above error and let me know does this 
> has any relation with vacumm full or reindexing operation which are 
> performed by me.
> 
> -- 
> Regards,
> Raghavendra Rao J S V
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup
@ 2023-08-04 02:22  Sai Teja <saitejasaichintalapudi@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sai Teja @ 2023-08-04 02:22 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup
@ 2023-08-04 02:33  Ron <ronljohnsonjr@gmail.com>
  parent: Sai Teja <saitejasaichintalapudi@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Ron @ 2023-08-04 02:33 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup
@ 2023-08-04 03:46  Sai Teja <saitejasaichintalapudi@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sai Teja @ 2023-08-04 03:46 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup
@ 2023-08-04 04:53  Ron <ronljohnsonjr@gmail.com>
  parent: Sai Teja <saitejasaichintalapudi@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Ron @ 2023-08-04 04:53 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2023-08-04 04:53 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-19 06:27 org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml gajendra s v <svgajendra@gmail.com>
2013-11-19 06:42 ` David Johnston <polobo@yahoo.com>
2013-11-19 22:11 ` Kevin Grittner <kgrittn@ymail.com>
2013-11-19 22:19   ` David Johnston <polobo@yahoo.com>
2018-03-06 01:54 org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory Raghavendra Rao J S V <raghavendrajsv@gmail.com>
2018-03-06 02:05 ` RE: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2018-03-06 05:13 ` Re: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-04 02:22 org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup Sai Teja <saitejasaichintalapudi@gmail.com>
2023-08-04 02:33 ` Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup Ron <ronljohnsonjr@gmail.com>
2023-08-04 03:46   ` Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup Sai Teja <saitejasaichintalapudi@gmail.com>
2023-08-04 04:53     ` Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox