PostgreSQL vs SQL/XML Standards

classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

PostgreSQL vs SQL/XML Standards

Chapman Flack
Inspired by the wiki page on PostgreSQL vs SQL Standard in general,
I have made another wiki page specifically about $subject. I hope
this was not presumptuous, and invite review / comment. I have not
linked to it from any other page yet.

https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards

-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Alvaro Herrera-9
On 2018-Oct-24, Chapman Flack wrote:

> Inspired by the wiki page on PostgreSQL vs SQL Standard in general,
> I have made another wiki page specifically about $subject. I hope
> this was not presumptuous, and invite review / comment. I have not
> linked to it from any other page yet.
>
> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards

Wow, that's ... overwhelming.  (I do wonder if we should stop relying on
libxml2 and instead look for something supporting XQuery).

Would you review Markus Winand patch here?
https://postgr.es/m/8BDB0627-2105-4564-AA76-7849F028B96E@...
I think doing that would probably point out a couple of ways in which
our XMLTABLE implementation is non-conformant, and then fixes it :-)
I've been unsure as to applying it to all branches since 10 or just to
master.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Tom Lane-2
Alvaro Herrera <[hidden email]> writes:
> On 2018-Oct-24, Chapman Flack wrote:
>> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards

> Wow, that's ... overwhelming.  (I do wonder if we should stop relying on
> libxml2 and instead look for something supporting XQuery).

I think getting out from under libxml2's idiosyncrasies and security
lapses would be great, but is there a plausible alternative out there?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 10/25/18 10:39 AM, Tom Lane wrote:
> I think getting out from under libxml2's idiosyncrasies and security
> lapses would be great, but is there a plausible alternative out there?

Depends on whether anything in [1] sounds plausible.

-Chap


[1]:
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Possible_ways_forward

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Alvaro Herrera-9
On 2018-Oct-25, Chapman Flack wrote:

> On 10/25/18 10:39 AM, Tom Lane wrote:
> > I think getting out from under libxml2's idiosyncrasies and security
> > lapses would be great, but is there a plausible alternative out there?
>
> Depends on whether anything in [1] sounds plausible.
>
> [1]:
> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Possible_ways_forward

Heh, I didn't notice this part of the document.  Integrating a C runtime
of a Java library sounds nightmarish -- I wouldn't even think about
that.

XQilla seems to depend on Xerces, and seems to have died in 2011.

Zorba appears to have been taken propietary, from the looks of its last
commits.

Maybe the best way forward is to implement all the JSON functionality
and remove the SQL/XML bits.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


čt 25. 10. 2018 v 17:09 odesílatel Alvaro Herrera <[hidden email]> napsal:
On 2018-Oct-25, Chapman Flack wrote:

> On 10/25/18 10:39 AM, Tom Lane wrote:
> > I think getting out from under libxml2's idiosyncrasies and security
> > lapses would be great, but is there a plausible alternative out there?
>
> Depends on whether anything in [1] sounds plausible.
>
> [1]:
> https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Possible_ways_forward

Heh, I didn't notice this part of the document.  Integrating a C runtime
of a Java library sounds nightmarish -- I wouldn't even think about
that.

XQilla seems to depend on Xerces, and seems to have died in 2011.

Zorba appears to have been taken propietary, from the looks of its last
commits.

Maybe the best way forward is to implement all the JSON functionality
and remove the SQL/XML bits.

It can be bigger compatibility break in Postgres history. SQL/XML functions are widely used.

Regards

Pavel


--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Andreas Karlsson
In reply to this post by Chapman Flack
On 10/25/2018 03:53 PM, Chapman Flack wrote:
> On 10/25/18 10:39 AM, Tom Lane wrote:
>> I think getting out from under libxml2's idiosyncrasies and security
>> lapses would be great, but is there a plausible alternative out there?
>
> Depends on whether anything in [1] sounds plausible.

The libraries we depend on should really either be available in the
package repositories of the major Linux distribution or be something we
can put in our own repository and maintain without too much pain. So
using Saxon/C does not seem like a realistic option.

Andreas

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Andrew Dunstan-8


On 10/25/2018 11:23 AM, Andreas Karlsson wrote:

> On 10/25/2018 03:53 PM, Chapman Flack wrote:
>> On 10/25/18 10:39 AM, Tom Lane wrote:
>>> I think getting out from under libxml2's idiosyncrasies and security
>>> lapses would be great, but is there a plausible alternative out there?
>>
>> Depends on whether anything in [1] sounds plausible.
>
> The libraries we depend on should really either be available in the
> package repositories of the major Linux distribution or be something
> we can put in our own repository and maintain without too much pain.
> So using Saxon/C does not seem like a realistic option.
>


Yeah, very good point. xqilla/xerces-C appears to be widely available
(Centos and ubuntu, at least).

cheers

andrew

--
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
In reply to this post by Alvaro Herrera-9
On 10/25/18 11:08 AM, Alvaro Herrera wrote:

> XQilla seems to depend on Xerces, and seems to have died in 2011.

¿Eh? The latest release, 2.3.4 [1], is dated 2018-07-03.

It looks like the latest development has been happening on the
xquilla_2_3 branch. Sometimes project "activity" statistics rely
exclusively on the "master" branch ("xqilla" branch in this case),
and are deceptive if the project isn't being developed exclusively
by coding on master and backpatching to others.

I've noticed I'm facing the same thing in PL/Java ... plenty of
development lately, but on the REL1_5_STABLE branch. GitHub's
project statistics (and also Open Hub's) are just looking at master
and saying the project's been dead for two years. Now that 1.5.1 is
released, as soon as I get some of REL1_5_STABLE merged /up/ into
master, the statistics will probably magically show it's been alive
all along.

> Zorba appears to have been taken propietary, from the looks of its last
> commits.

It does seem harder to see what's going on there, but the commits
with "copyright changed" as the message turn out to be changing
only the copyright holder in the Apache 2.0 license from
"The FLWOR Foundation" to "zorba.io". But Matthias Brantner
participated with interest in the 2010 thread here where Zorba
was brought up before[2], so he may know something.

> Integrating a C runtime of a Java library sounds nightmarish --
> I wouldn't even think about that.

Or whether or not nightmarish, certainly duplicative of something
we can kinda already do.

In a way, some of the pressure is off, because if you need
a true XMLQUERY or XMLTABLE, you can get them with the Saxon-in-PL/Java
implementation, and right now in any supported PG version. You just
have to spell them funny, doing without the sugary syntax built into
the parser. They're missing some of the automatic casts from
the standard at the moment, which isn't really a loss of function,
as explicit casts can be added to any query needing them ... a temporary
annoyance until the rest of that example's in place.

But a roadmap that could lead to eventual availability of one of the
C/C++ implementations would be nice too.

-Chap


[1]: https://sourceforge.net/projects/xqilla/files/
[2]:
https://www.postgresql.org/message-id/7DDDB18E-041F-4238-B91D-3277EB1CE5BC%4028msec.com

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Jesper Pedersen
In reply to this post by Andrew Dunstan-8
On 10/25/18 2:33 PM, Andrew Dunstan wrote:
>
> Yeah, very good point. xqilla/xerces-C appears to be widely available
> (Centos and ubuntu, at least).
>

xqilla/xerces-c are in the Fedora/RHEL repo too.

Best regards,
  Jesper

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule
In reply to this post by Chapman Flack
Hi


But a roadmap that could lead to eventual availability of one of the
C/C++ implementations would be nice too.

Somebody should to do some work and write patch :/. Although libxml2 is after feature freeze - it is code widely used. The change of XML support should be safe, because there can be lot of work.

I am thinking so I can fix some issues related to XMLTABLE. Please, send me more examples and test cases.

Regards

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Alvaro Herrera-9
On 2018-Oct-25, Pavel Stehule wrote:

> I am thinking so I can fix some issues related to XMLTABLE. Please, send me
> more examples and test cases.

Please see Markus Winand's patch that I referenced upthread.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
In reply to this post by Pavel Stehule
On 10/25/18 11:15, Pavel Stehule wrote:
> čt 25. 10. 2018 v 17:09 odesílatel Alvaro Herrera <[hidden email]>
> napsal:
>> Maybe the best way forward is to implement all the JSON functionality
>> and remove the SQL/XML bits.
>
> It can be bigger compatibility break in Postgres history. SQL/XML functions
> are widely used.

It seems to me that evolution to the 2006+ standard version could be done
mostly non-disruptively (provided an agreeable library can be found).

I think Tom's suggestion[1] to just make XML OPTION CONTENT mean what
it means in 2006+ would be an easy change to make immediately, and would
not disrupt anybody ... it would only make some things succeed that now
fail, and it would match what our documentation already says. It would
make our XML type equivalent to 2006+ XML(CONTENT(ANY)).

Beyond that, further steps toward 2006+ could largely avoid disruption.

If we implement the typmod'ed XML types, surely the parser would simply
treat untypmod'ed 'XML' as meaning XML(CONTENT(ANY)). (The standard does
allow for the typmod to be missing, and leaves it "implementation-defined
whether SEQUENCE, CONTENT(ANY), or CONTENT(UNTYPED) is implicit", so
that's all by the book.)

The existing functions xpath and xpath_exists can be kept unchanged,
as their names are distinct from anything in the standard. A library
that supports XQuery is likely also to support XPath in "1.0 compatibility
mode", so those functions could keep their semantics.

The current xmlvalidate() has the wrong semantics and return type, but it
also does nothing but ereport unimplemented, so no current uses would be
hurt by redefining it.

XMLTABLE would be the headache. Using the standard name for something
that ain't the standard function has not left any painless way that the
standard function could be added. OTOH, it has only been in the wild
since 10, so renaming it to something else (xpath_table?) will probably
be more painless if done soon than it ever would be later.

On 10/25/18 11:23, Andreas Karlsson wrote:
> The libraries we depend on should really either be available in the
> package repositories of the major Linux distribution or be something
> we can put in our own repository and maintain without too much pain.
> So using Saxon/C does not seem like a realistic option.

That makes good sense. The approach I proposed in [2] would be to
target the XQC API as an integration point. If there is one library
that might be most acceptable (it seems xqilla is in several repositories),
it could become a preferred or supported choice, but others could be
available if an administrator wanted to separately obtain them, perhaps
because of better performance on a particular workload, or avoidance of
some bug that a given workload turns up.

-Chap

[1]: https://www.postgresql.org/message-id/22271.1540458133%40sss.pgh.pa.us
[2]:
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#One_proposal

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


XMLTABLE would be the headache. Using the standard name for something
that ain't the standard function has not left any painless way that the
standard function could be added. OTOH, it has only been in the wild
since 10, so renaming it to something else (xpath_table?) will probably
be more painless if done soon than it ever would be later.


I don't share your opinion. XMLTABLE implements subset of standard. More it is well compatible with Oracle (in this subset).

If we have library with XPath 2.0 or higher, we can continue with it.

Regards

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
In reply to this post by Alvaro Herrera-9
On 10/25/18 09:56, Alvaro Herrera wrote:
> Would you review Markus Winand patch here?
> https://postgr.es/m/8BDB0627-2105-4564-AA76-7849F028B96E@...
> I think doing that would probably point out a couple of ways in which
> our XMLTABLE implementation is non-conformant, and then fixes it :-)
> I've been unsure as to applying it to all branches since 10 or just to
> master.

Well, modulo the key observation that it simply *is not* conformant
until it accepts XML Query expressions and uses the XPath 2.0 type system
and data model and the SQL/XML 2006+ casting rules ...

... and there is no ISO standard that says anything about how an XPath 1.0-
based quasi-XMLTABLE-ish function ought to behave, so it's hard to say
that anything this function does is right or wrong, per ISO ...

I think all of the changes in these patches do make it a more useful
quasi-XMLTABLE-ish function, as the pre-patch behaviors were less useful
(if not outright bewildering). And they produce output that better matches
what the XQuery-based ISO rules produce (for the subset of queries that
mean the same thing as XQuery and as XPath 1.0).

I also looked at the (not yet applied?)
XML-XPath-comments-processing-instructions-array-ind patch and I think
it, too, makes the behavior more useful. I did not actually take the
time to build a PostgreSQL with the patch, but I took the two added
regression queries, syntax-desugared them[1] and called the Saxon XQuery-
based "xmltable" example with them, and got the same expected results:

SELECT xmltable.* FROM
(SELECT '<root><element>a1a<!-- aaaa -->a2a<?aaaaa pi?> <!--z-->
bbbb<x>xxx</x>cccc</element></root>'::xml AS ".") AS p,
"xmltable"('/root', PASSING => p, COLUMNS => ARRAY[
'string(element)', 'string(element/comment()[2])',
'string(element/processing-instruction())', 'string(element/text()[1])',
'serialize(element)'])
AS (element text, cmnt text, pi text, t1 text, x text);

       element        | cmnt | pi | t1  |
     x
----------------------+------+----+-----+---------------------------------------------------------------------------------
 a1aa2a   bbbbxxxcccc | z    | pi | a1a | <element>a1a<!-- aaaa
-->a2a<?aaaaa pi?> <!--z-->  bbbb<x>xxx</x>cccc</element>
(1 row)

SELECT xmltable.* FROM
(SELECT '<root><element>a1a<!-- aaaa -->a2a<?aaaaa pi?> <!--z-->
bbbb<x>xxx</x>cccc</element></root>'::xml AS ".") AS p,
"xmltable"('/root', PASSING => p, COLUMNS => ARRAY[
'string(element/text())', 'string(element/comment()[2])',
'string(element/processing-instruction())', 'string(element/text()[1])',
'serialize(element)'])
AS (element text, cmnt text, pi text, t1 text, x text);

ERROR:  java.sql.SQLException: A sequence of more than one item is not
allowed as the first argument of fn:string() (text("a1a"), text("a2a"))


Agreement. Agreement is good. :)

So I think they are worth applying. I can't bring myself to a strong
opinion on whether they are or aren't worth backpatching; if it were
the function described by the standard, they'd be bugs and they would
be, but does making a non-standard function behave slightly more like
the standard function that it isn't count as a bug fix or an enhancement?

My overall feeling, at least in directing my own effort, is that I'd rather
spend time toward getting the real XQuery-based semantics in place somehow,
and ongoing enhancements to the XPath-1.0-based stuff feel more like
pouring treasure down a hole.

But these enhancements seem like good ones, and if there's interest in
patching a couple more, the "unexpected XPath object type {2,3}" in [2]
might be good candidates. That would be backpatchable, as the current
behavior clearly isn't useful.

One other thing: I think the commit message on the context-item patch
is really somewhat misleading: "According to the SQL standard, the context
of XMLTABLE's XPath row_expression is the document node of the XML input
document..." Really the standard says nothing of the sort. It is a
limitation of XPath 1.0 that the input even has to be a document at all.
In the real XMLTABLE, you could be passing a context item that is a
document node (of either 'document' or 'content' flavor), or a one-item
'sequence' holding an atomic type like a number or date, or even a naked
XML node like a PI or comment or attribute node you're more used to seeing
only inside a document. The real rule is just that the context item is
exactly the thing you passed (or a copy of it, when the rules say so).
It collapses in the XPath 1.0 case to having to be a document node, simply
because that's the only thing you can pass in.

What was wrong with the pre-patch code was that it wasn't just using
the 'doc' it was given, but actually calling xmlDocGetRootElement() on it
and setting the CI to one of its children. The patch just directly
assigns doc to xpathctx->node, which I would call correct, not because
it's a document node, but because it's the thing that was passed.

-Chap




[1] You might notice in addition to desugaring the XMLTABLE syntax, I
wrapped the text-returning column paths in string(), and wrapped the
xml-returning one in serialize() and changed its result column to text.
Those changes are just to work around the parts of the Saxon example that
aren't implemented yet, as explained in [3].

[2] https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#XMLTABLE

[3] https://tada.github.io/pljava/examples/saxon.html#Using_the_Saxon_examples

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
In reply to this post by Pavel Stehule
On 10/25/18 23:16, Pavel Stehule wrote:

>> XMLTABLE would be the headache. Using the standard name for something
>> that ain't the standard function has not left any painless way that the
>> standard function could be added. OTOH, it has only been in the wild
>> since 10, so renaming it to something else (xpath_table?) will probably
>> be more painless if done soon than it ever would be later.
>>
> I don't share your opinion. XMLTABLE implements subset of standard. More it
> is well compatible with Oracle (in this subset).
>
> If we have library with XPath 2.0 or higher, we can continue with it.

The difficulty here is that the expression language required by the standard
is XQuery, and an XPath expression (whether 1.0 or 2.0+) can always be
parsed as an XQuery expression. (So, /syntactically/, yes, "subset".)

For XPath 2.0, that is no problem, because an XPath 2.0 expression and
the identically-spelled XQuery expression /mean the same thing/.

For XPath 1.0, it is very definitely a problem, because an XPath 1.0
expression and the identically-spelled XQuery expression /do not mean
the same thing/. Some of the important semantic differences are in [1].

So, if a future PostgreSQL version has an XMLTABLE function that accepts
XQuery, as the standard requires, and existing users upgrade and they have
XMLTABLE query expressions written as XPath 1.0, those queries will be
accepted and parsed, but they will not mean the same thing. The function
will not be able to tell when it is being called with XQuery semantics
intended, vs. when it is being called with XPath 1.0 semantics intended.

Now, perhaps there is a nicer way than renaming the function. It could
work like overloading. Create two trivial domains over text, say xpath1
and xquery, and have two XMLTABLE functions with different first parameter
types. Then if you called with the expression '"cat" < "dog"'::xquery
you would get the correct result 't', and with '"cat" < "dog"'::xpath1
you would get the (also correct) result 'f'.

(It would not be exactly overloading, because of the special sugared
syntax known to the parser, but it could look like overloading, and be
intuitive to the user.)

If you have convenient access to Oracle to check compatibility, could you
compare this query?

SELECT * FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a boolean PATH 'string("cat" < "dog")',
b boolean PATH 'string("cat" > "dog")',
c boolean PATH 'string(sale/@taxable = false())');

(I suspect in Oracle it would also work without the string() wrappings,
but just to make it easy, I think this way it will work in both Oracle
and PG—that is, not error, though results may differ.)

-Chap


[1]
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Related_to_version_of_XPath

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


pá 26. 10. 2018 v 6:25 odesílatel Chapman Flack <[hidden email]> napsal:
On 10/25/18 23:16, Pavel Stehule wrote:
>> XMLTABLE would be the headache. Using the standard name for something
>> that ain't the standard function has not left any painless way that the
>> standard function could be added. OTOH, it has only been in the wild
>> since 10, so renaming it to something else (xpath_table?) will probably
>> be more painless if done soon than it ever would be later.
>>
> I don't share your opinion. XMLTABLE implements subset of standard. More it
> is well compatible with Oracle (in this subset).
>
> If we have library with XPath 2.0 or higher, we can continue with it.

The difficulty here is that the expression language required by the standard
is XQuery, and an XPath expression (whether 1.0 or 2.0+) can always be
parsed as an XQuery expression. (So, /syntactically/, yes, "subset".)

For XPath 2.0, that is no problem, because an XPath 2.0 expression and
the identically-spelled XQuery expression /mean the same thing/.

For XPath 1.0, it is very definitely a problem, because an XPath 1.0
expression and the identically-spelled XQuery expression /do not mean
the same thing/. Some of the important semantic differences are in [1].

So, if a future PostgreSQL version has an XMLTABLE function that accepts
XQuery, as the standard requires, and existing users upgrade and they have
XMLTABLE query expressions written as XPath 1.0, those queries will be
accepted and parsed, but they will not mean the same thing. The function
will not be able to tell when it is being called with XQuery semantics
intended, vs. when it is being called with XPath 1.0 semantics intended.

If we have a library with XQuery, then we can change the behave. But world accepting of XQuery is not wide, unfortunately.

When I wrote and tested XMLTABLE, I found only few examples of where XQuery was used. So first there should be any library with XQUery implementation that can be used in Postgres. This library should be fast, well tested without memory leaks. Elsewhere discussion is premature. I am not terrible happy from libxml2 design, documentation, manuals - and I will not against we can migrate to some better. On second hand - libxml2 code is working - and it is widely used. It can be big mistake if we use Java library and if we create dependency on Java. After, there are only few libs that doesn't significantly better than libxml2.



Now, perhaps there is a nicer way than renaming the function. It could
work like overloading. Create two trivial domains over text, say xpath1
and xquery, and have two XMLTABLE functions with different first parameter
types. Then if you called with the expression '"cat" < "dog"'::xquery
you would get the correct result 't', and with '"cat" < "dog"'::xpath1
you would get the (also correct) result 'f'.

Probably it can works, but needs more work on Postgres infrastructure. If you overload functions like this, then type should be used every time.


(It would not be exactly overloading, because of the special sugared
syntax known to the parser, but it could look like overloading, and be
intuitive to the user.)

If you have convenient access to Oracle to check compatibility, could you
compare this query?

SELECT * FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a boolean PATH 'string("cat" < "dog")',
b boolean PATH 'string("cat" > "dog")',
c boolean PATH 'string(sale/@taxable = false())');

(I suspect in Oracle it would also work without the string() wrappings,
but just to make it easy, I think this way it will work in both Oracle
and PG—that is, not error, though results may differ.)

I will test it.

Regards

Pavel


-Chap


[1]
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Related_to_version_of_XPath
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule
In reply to this post by Alvaro Herrera-9
Hi

čt 25. 10. 2018 v 21:47 odesílatel Alvaro Herrera <[hidden email]> napsal:
On 2018-Oct-25, Pavel Stehule wrote:

> I am thinking so I can fix some issues related to XMLTABLE. Please, send me
> more examples and test cases.

Please see Markus Winand's patch that I referenced upthread.

here is a fix of some XMLTABLE mentioned issues.

Regards

Pavel

 

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

xmltable.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule
In reply to this post by Pavel Stehule

(It would not be exactly overloading, because of the special sugared
syntax known to the parser, but it could look like overloading, and be
intuitive to the user.)

If you have convenient access to Oracle to check compatibility, could you
compare this query?

SELECT * FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a boolean PATH 'string("cat" < "dog")',
b boolean PATH 'string("cat" > "dog")',
c boolean PATH 'string(sale/@taxable = false())');

(I suspect in Oracle it would also work without the string() wrappings,
but just to make it easy, I think this way it will work in both Oracle
and PG—that is, not error, though results may differ.)



I have a access to too old 11.2 Oracle.  There I had to modify query because there is not boolean type. I replaced bool by int, but I got a error
ORA-19224:XPTY-004 .. expected node()*, got xs:string - it doesn't work with/without string() wrappings.

Regards

Pavel Stehule



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


po 29. 10. 2018 v 11:05 odesílatel Pavel Stehule <[hidden email]> napsal:

(It would not be exactly overloading, because of the special sugared
syntax known to the parser, but it could look like overloading, and be
intuitive to the user.)

If you have convenient access to Oracle to check compatibility, could you
compare this query?

SELECT * FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a boolean PATH 'string("cat" < "dog")',
b boolean PATH 'string("cat" > "dog")',
c boolean PATH 'string(sale/@taxable = false())');

(I suspect in Oracle it would also work without the string() wrappings,
but just to make it easy, I think this way it will work in both Oracle
and PG—that is, not error, though results may differ.)



I have a access to too old 11.2 Oracle.  There I had to modify query because there is not boolean type. I replaced bool by int, but I got a error
ORA-19224:XPTY-004 .. expected node()*, got xs:string - it doesn't work with/without string() wrappings.

The problem is in last line - the expression  "sale/@taxable = false()" is not valid on Oracle. Using string() wrapping is a issue, because it returns "true", "false", but Oracle int doesn't accept it.

Pavel


Regards

Pavel Stehule



12