PostgreSQL vs SQL/XML Standards

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
112 messages Options
123456
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


ne 13. 1. 2019 v 0:39 odesílatel Chapman Flack <[hidden email]> napsal:
On 12/30/18 03:23, Pavel Stehule wrote:
> Unfortunately, there is a different releases of libxml2 with different
> error reporting and it is hard (impossible) to prepare for all variants. :-/
>
> I prepare xml.out for my FC29 (fresh libxml2) and for no support xml.
> Other I prepare by patching - and this error (in context) is expected.

It turns out that the variant was already accounted for in the xml_2.out
variant result file, it just needed to have the new results added.

Done in xmltable-xpath-result-processing-bugfix-4.patch attached.


On 12/31/18 01:03, Pavel Stehule wrote:
> po 31. 12. 2018 v 3:15 odesílatel Chapman Flack <[hidden email]>
> napsal:
>> But the PostgreSQL situation is a little more strange. PG uses BY VALUE
>> semantics as the default when no passing method is specified. PG also uses
>> BY VALUE semantics when BY REF is explicitly requested, which is rude,
>> just like Oracle. But why should an explicit specification of BY VALUE
>> (which is, after all, the semantics we're going to use anyway!) produce
>> this?
>>
>> ERROR:  syntax error at or near "value"
>>
>> To me, that doesn't seem like least astonishment.
>>
>> I am not seeing what would be complicated about removing that astonishment
>> by simply allowing the grammar productions to also consume BY VALUE and
>> ignore it.
>
> ok - I am not against implementation of ignored BY VALUE. But I don't like
> a idea to disable BY REF.

Done in attached xmltable-xmlexists-passing-mechanisms-1.patch along with
some corresponding documentation adjustments.

I am still working on more extensive documentation, but it seemed best
to include the changes related to BY REF / BY VALUE in the same patch
with the grammar change.

looks well, thank you for patch

Pavel


-Chap
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
There is a bug that remains, in the
else if (xpathobj->type == XPATH_STRING)
case.

As it is now, it simply passes the string value of the result
into the output column's type-input function, regardless of the
output column type.

If the output column type is xml, this will attempt to parse the
string as xml. The result should simply be xml content consisting of
a text node representing the string (as by XMLTEXT()). If it contains
XML metacharacters, they should be escaped.

For a non-xml output column, the string should be used directly,
as it is now.

# select * from xmltable('.' passing xmlelement(name a)
columns a text path '"<foo/>"', b xml path '"<foo/>"');
   a    |   b
--------+--------
 <foo/> | <foo/>

Oracle fiddle for comparison:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=26f91a9e55a6908c2bcf848b464ca381

A B
<foo/> &lt;foo/&gt;

-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule
Hi

po 14. 1. 2019 v 2:41 odesílatel Chapman Flack <[hidden email]> napsal:
There is a bug that remains, in the
else if (xpathobj->type == XPATH_STRING)
case.

As it is now, it simply passes the string value of the result
into the output column's type-input function, regardless of the
output column type.

If the output column type is xml, this will attempt to parse the
string as xml. The result should simply be xml content consisting of
a text node representing the string (as by XMLTEXT()). If it contains
XML metacharacters, they should be escaped.

For a non-xml output column, the string should be used directly,
as it is now.

# select * from xmltable('.' passing xmlelement(name a)
columns a text path '"<foo/>"', b xml path '"<foo/>"');
   a    |   b
--------+--------
 <foo/> | <foo/>

Oracle fiddle for comparison:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=26f91a9e55a6908c2bcf848b464ca381

A       B
<foo/>  &lt;foo/&gt;

should be fixed in last patch

Regards

Pavel


-Chap

xmltable-xpath-result-processing-bugfix-5.patch (19K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

House style for DocBook documentation?

Chapman Flack
In reply to this post by Chapman Flack
Hi,

Is there, somewhere, a written-up "house style" for what DocBook 4.2
elements to use for which types of content in the manual?

In func.sgml I'm seeing what looks like a variety of examples, and
I'm not sure which ones to try to follow.

Thanks,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: House style for DocBook documentation?

Alvaro Herrera-9
Hi

On 2019-Jan-18, Chapman Flack wrote:

> Is there, somewhere, a written-up "house style" for what DocBook 4.2
> elements to use for which types of content in the manual?
>
> In func.sgml I'm seeing what looks like a variety of examples, and
> I'm not sure which ones to try to follow.

I don't think we do.  I'd suggest to come up with something and then see
if it makes sense to patch the docs to apply it regularly.

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

Reply | Threaded
Open this post in threaded view
|

Re: House style for DocBook documentation?

Chapman Flack
Hi,

On 01/19/19 08:35, Alvaro Herrera wrote:
>> Is there, somewhere, a written-up "house style" for what DocBook 4.2
>> elements to use for which types of content in the manual?
>> ...
> I don't think we do.  I'd suggest to come up with something and then see
> if it makes sense to patch the docs to apply it regularly.

I think my ambition at the moment is just to complete a particular
addition to func.sgml and do so as consistently as I can manage with
what's there now.

I have noticed a couple of things:

- 'SQL' is often marked up as <acronym>SQL</acronym>, but far from always.

- no such markup is applied to 'JSON' or 'XML' at all, at least
  not in func.sgml.

- there is a README.links with this guideline:

  o  Do not use text with <ulink> so the URL appears in printed output

  but a grep -r in doc/src/sgml turns up 112 uses that observe the
  guideline, and 147 that supply link text.

(thinks to self half-seriously about an XSL transform for generating
printed output that could preserve link-texted links, add raised numbers,
and produce a numbered URLs section at the back)

-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
In reply to this post by Pavel Stehule
Working slowly through the documentation, I came upon:

 For XMLTABLE:

  - The xmltable function produces a table based on the given XML value,
    an XPath filter to extract rows, and an optional set of column
    definitions.                            ^^^^^^^^
    ...
    The mandatory COLUMNS clause specifies the list of columns ...
        ^^^^^^^^^
    if the COLUMNS clause is omitted, the rows in the result set contain
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    a single column of type xml containing the data matched by
    row_expression.

   This documentation seems undecided on whether the COLUMNS clause
   is mandatory or optional.

   It is mandatory in the SQL standard. It's mandatory in our grammar.
   We give a syntax_error if it's omitted.

   Is some of the documentation left over from an earlier contemplated
   design of having the clause be optional?

   Oracle does seem to allow the clause to be omitted, and produces a
   single xml column, as described. Was there an earlier plan to imitate
   Oracle's nonstandard behavior on that point? (Hardly seems worth the
   effort, as porting an Oracle query depending on it would simply entail
   adding COLUMNS COLUMN_VALUE XML PATH '.' and then it's portable and
   standard.)

 - It is possible for a default_expression to reference the value of
   output columns that appear prior to it in the column list, so the
   default of one column may be based on the value of another column.

  Is there an example that clearly shows this to work? If I write a
  default_expression referring to a prior column in /xmltable's own/
  column list, I get an undefined_column error. I can successfully refer
  to a column of /an earlier FROM item in the SELECT/, but I am not sure
  that demonstrates the behavior claimed here.

  There is what looks like an example among the regression tests
  (the one with DEFAULT ascii(_path) - 54), but that seems only to
  demonstrate xmltable getting invoked four times (as documented for
  LATERAL), not a single xmltable invocation producing multiple rows
  with recomputed defaults.

  If it's any comfort, I haven't gotten Oracle's xmltable to recognize
  earlier columns in its own column list either.

 - Unlike regular PostgreSQL functions, column_expression and
   default_expression are not evaluated to a simple value before calling
   the function. column_expression is normally evaluated exactly once
   per input row, and default_expression is evaluated each time a default
   is needed for a field.

  I've already covered the question about default_expression, but what
  this passage says about column_expression seems, at least, ambiguously
  worded, too:

  It goes without saying that /the XPath evaluator/ evaluates the
  column_expression exactly once per input row. In the standard, that's
  the only per-row evaluation happening; the column_expression SQL value
  only gets compiled to an XPath expression once at the start. (In fact,
  in the standard, it can't even be an arbitrary SQL expression, only a
  string literal. Oracle enforces that too.)

  It seems that our implementation is meant to extend the standard and
  actually allow the column_expression to vary per-row, and go through
  the XPath expression compiler each time. The regression test with
   COLUMNS a int PATH '' || lower(_path) || 'c'
  seems to be intended to confirm that behavior. But again, I think
  it is only confirming that LATERAL results in xmltable being called
  four consecutive times, with a different PATH in each call. It does
  not seem to demonstrate a single xmltable call doing anything special
  with recompiling a column path.

Am I overlooking something?

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


ne 20. 1. 2019 v 5:37 odesílatel Chapman Flack <[hidden email]> napsal:
Working slowly through the documentation, I came upon:

 For XMLTABLE:

  - The xmltable function produces a table based on the given XML value,
    an XPath filter to extract rows, and an optional set of column
    definitions.                            ^^^^^^^^
    ...
    The mandatory COLUMNS clause specifies the list of columns ...
        ^^^^^^^^^
    if the COLUMNS clause is omitted, the rows in the result set contain
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    a single column of type xml containing the data matched by
    row_expression.

   This documentation seems undecided on whether the COLUMNS clause
   is mandatory or optional.

   It is mandatory in the SQL standard. It's mandatory in our grammar.
   We give a syntax_error if it's omitted.

   Is some of the documentation left over from an earlier contemplated
   design of having the clause be optional?

   Oracle does seem to allow the clause to be omitted, and produces a
   single xml column, as described. Was there an earlier plan to imitate
   Oracle's nonstandard behavior on that point? (Hardly seems worth the
   effort, as porting an Oracle query depending on it would simply entail
   adding COLUMNS COLUMN_VALUE XML PATH '.' and then it's portable and
   standard.)
 
If I remember, described functionality was implemented in early patches, but was removed to simplify code. To now, there was not a request to do it.

Unfortunately, the documentation was not fixed.
 

 - It is possible for a default_expression to reference the value of
   output columns that appear prior to it in the column list, so the
   default of one column may be based on the value of another column.

  Is there an example that clearly shows this to work? If I write a
  default_expression referring to a prior column in /xmltable's own/
  column list, I get an undefined_column error. I can successfully refer
  to a column of /an earlier FROM item in the SELECT/, but I am not sure
  that demonstrates the behavior claimed here.

  There is what looks like an example among the regression tests
  (the one with DEFAULT ascii(_path) - 54), but that seems only to
  demonstrate xmltable getting invoked four times (as documented for
  LATERAL), not a single xmltable invocation producing multiple rows
  with recomputed defaults.

  If it's any comfort, I haven't gotten Oracle's xmltable to recognize
  earlier columns in its own column list either.

 - Unlike regular PostgreSQL functions, column_expression and
   default_expression are not evaluated to a simple value before calling
   the function. column_expression is normally evaluated exactly once
   per input row, and default_expression is evaluated each time a default
   is needed for a field.

  I've already covered the question about default_expression, but what
  this passage says about column_expression seems, at least, ambiguously
  worded, too:

  It goes without saying that /the XPath evaluator/ evaluates the
  column_expression exactly once per input row. In the standard, that's
  the only per-row evaluation happening; the column_expression SQL value
  only gets compiled to an XPath expression once at the start. (In fact,
  in the standard, it can't even be an arbitrary SQL expression, only a
  string literal. Oracle enforces that too.)

column expressions are evaluated once per row, but XPath  expression is compiled per row too, if I remember well. We designed it more tolerant as we expected possibility to store XPath expression in one column and data in second column.

Regards

Pavel
 

  It seems that our implementation is meant to extend the standard and
  actually allow the column_expression to vary per-row, and go through
  the XPath expression compiler each time. The regression test with
   COLUMNS a int PATH '' || lower(_path) || 'c'
  seems to be intended to confirm that behavior. But again, I think
  it is only confirming that LATERAL results in xmltable being called
  four consecutive times, with a different PATH in each call. It does
  not seem to demonstrate a single xmltable call doing anything special
  with recompiling a column path.

Am I overlooking something?

Regards,
-Chap
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 01/19/19 23:49, Pavel Stehule wrote:

> If I remember, described functionality was implemented in early patches,
> but was removed to simplify code. To now, there was not a request to do it.
>
> Unfortunately, the documentation was not fixed.

I'll do that, as I'm working in there anyway. :)

> column expressions are evaluated once per row, but XPath  expression is
> compiled per row too, if I remember well.

I looked for evidence of that in the code, but did not find it; the
compilation appears to happen in XmlTableSetColumnFilter, which is
called from tfuncInitialize.

I can't guarantee I didn't miss something, though.

> We designed it more tolerant as
> we expected possibility to store XPath expression in one column and data in
> second column.

Perhaps if I could see an example showing the functionality... The nearest
I could find in the regression tests was the test with
 COLUMNS a int PATH '' || lower(_path) || 'c'
but, again, I think that test only demonstrates how LATERAL works, not
any behavior specific to xmltable.

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


ne 20. 1. 2019 v 6:06 odesílatel Chapman Flack <[hidden email]> napsal:
On 01/19/19 23:49, Pavel Stehule wrote:

> If I remember, described functionality was implemented in early patches,
> but was removed to simplify code. To now, there was not a request to do it.
>
> Unfortunately, the documentation was not fixed.

I'll do that, as I'm working in there anyway. :)

> column expressions are evaluated once per row, but XPath  expression is
> compiled per row too, if I remember well.

I looked for evidence of that in the code, but did not find it; the
compilation appears to happen in XmlTableSetColumnFilter, which is
called from tfuncInitialize.

it is called per input row.


I can't guarantee I didn't miss something, though.

> We designed it more tolerant as
> we expected possibility to store XPath expression in one column and data in
> second column.

Perhaps if I could see an example showing the functionality... The nearest
I could find in the regression tests was the test with
 COLUMNS a int PATH '' || lower(_path) || 'c'
but, again, I think that test only demonstrates how LATERAL works, not
any behavior specific to xmltable.

the main reason for this was not to support support some specific patterns - just used design doesn't requires stronger changes in executor or internal multi call caching of some internal data. Probably you can find discussion related to this topic in mailing list archive.

sure - using any expression in PATH clause should to demonstrate this functionality.



Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
Hi,

On 01/20/19 00:26, Pavel Stehule wrote:
>>> column expressions are evaluated once per row, but XPath  expression is
>>> compiled per row too, if I remember well.
>>
>> I looked for evidence of that in the code, but did not find it; the
>> compilation appears to happen in XmlTableSetColumnFilter, which is
>> called from tfuncInitialize.
>
> it is called per input row.

I could be misunderstanding what you mean by 'input row' here.

If I write a simple xmltable query where the row_expression produces
six rows:

SELECT *
FROM
  xmltable('pg_am/row'
    PASSING table_to_xml('pg_am', true, false, '')
    COLUMNS amname text PATH 'amname');

six rows are produced, though a breakpoint set on XmlTableSetColumnFilter
fires only once, from tfuncInitialize at the start of xmltable's execution.


By contrast, in the regression test example with PATH ''||lower(_path)||'c',
four rows are produced and the breakpoint fires four times.

However ... that isn't because one call to xmltable is producing four rows
and recomputing the column_expression each time.

It's because that xmltable is the RHS of a LATERAL, and the LHS of the
LATERAL is producing four tuples with different values of columns the RHS
depends on, so the RHS (xmltable) is being called four different times,
producing one row each time, still with XmlTableSetColumnFilter being called
only during initialization.


> sure - using any expression in PATH clause should to demonstrate this
> functionality.

Well, there seem to be two distinct features touched on in the docs:

1. The column_expression is allowed to be an expression, not restricted
   to a string literal as it is in the standard (and Oracle).

2. Not only is it an expression, but it's an expression whose evaluation
   is deferred and can happen more than once in the same xmltable call.

The example in the regression tests certainly demonstrates (1). Without (1),
it would be a syntax error.

I think the same example would produce the same output even with feature (2)
absent. It's LATERAL doing the magic there. So I am doubtful that it
demonstrates (2).

I put some effort last night into trying to even construct any query that
would demonstrate (2), and I came up short, but that could be my lack of
imagination. (Somewhere in that effort I happened to notice that xmltable
doesn't seem to be parsed successfully inside a ROWS FROM (...) construct,
which might be another issue for another time....)

So, if you have a way to build a query that demonstrates (2), my aha! moment
might then arrive.

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


ne 20. 1. 2019 v 17:06 odesílatel Chapman Flack <[hidden email]> napsal:
Hi,

On 01/20/19 00:26, Pavel Stehule wrote:
>>> column expressions are evaluated once per row, but XPath  expression is
>>> compiled per row too, if I remember well.
>>
>> I looked for evidence of that in the code, but did not find it; the
>> compilation appears to happen in XmlTableSetColumnFilter, which is
>> called from tfuncInitialize.
>
> it is called per input row.

I could be misunderstanding what you mean by 'input row' here.

input row mean a row of processed relation. The xml value from this row can be transformed to 0..M output rows.

The column filter expressions are evaluated once per input rows, default expressions are evaluated when it is necessary - possibly once for any output row
 

If I write a simple xmltable query where the row_expression produces
six rows:

SELECT *
FROM
  xmltable('pg_am/row'
    PASSING table_to_xml('pg_am', true, false, '')
    COLUMNS amname text PATH 'amname');

six rows are produced, though a breakpoint set on XmlTableSetColumnFilter
fires only once, from tfuncInitialize at the start of xmltable's execution.


By contrast, in the regression test example with PATH ''||lower(_path)||'c',
four rows are produced and the breakpoint fires four times.

it is expected - the input relation has four lines - the function was 4x initialized. In this case 1 call of xmltable produces 1 row.
 

However ... that isn't because one call to xmltable is producing four rows
and recomputing the column_expression each time.

It's because that xmltable is the RHS of a LATERAL, and the LHS of the
LATERAL is producing four tuples with different values of columns the RHS
depends on, so the RHS (xmltable) is being called four different times,
producing one row each time, still with XmlTableSetColumnFilter being called
only during initialization.


> sure - using any expression in PATH clause should to demonstrate this
> functionality.

Well, there seem to be two distinct features touched on in the docs:

1. The column_expression is allowed to be an expression, not restricted
   to a string literal as it is in the standard (and Oracle).

2. Not only is it an expression, but it's an expression whose evaluation
   is deferred and can happen more than once in the same xmltable call.

yes, it is any expressions used there are evaluated per 1 call. If input relation has N rows, then xmltable is initialized N times - by different words. xmltable is evaluated independently for any processed XML document.



The example in the regression tests certainly demonstrates (1). Without (1),
it would be a syntax error.

I think the same example would produce the same output even with feature (2)
absent. It's LATERAL doing the magic there. So I am doubtful that it
demonstrates (2).

LATERAL is necessary, because  XMLTABLE can be used only in FROM clause, and in this case XMLTABLE has mutable parameters.


I put some effort last night into trying to even construct any query that
would demonstrate (2), and I came up short, but that could be my lack of
imagination. (Somewhere in that effort I happened to notice that xmltable
doesn't seem to be parsed successfully inside a ROWS FROM (...) construct,
which might be another issue for another time....)

So, if you have a way to build a query that demonstrates (2), my aha! moment
might then arrive.

Regards,
-Chap
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 01/20/19 11:55, Pavel Stehule wrote:
> input row mean a row of processed relation. The xml value from this row can
> be transformed to 0..M output rows.
>
> The column filter expressions are evaluated once per input rows, default
> expressions are evaluated when it is necessary - possibly once for any
> output row
> ...
> it is expected - the input relation has four lines - the function was 4x
> initialized. In this case 1 call of xmltable produces 1 row.

Good ... I think we're converging on a shared understanding.

I am just used to speaking of xmltable as a simple function that executes
one row_expression one time against one supplied input, and generates 0..M
output rows from it. If there are multiple rows in a relation that you
want to apply xmltable to, that's a simple matter of calling xmltable
multiple times (which is just what SQL is doing when the xmltable is on
the RHS of an explicit or implied LATERAL).

The upshot seems to be that there is nothing necessarily special about
how xmltable treats its column_expressions: it compiles them once upon
entry to the function, as one would naïvely expect. (Or, if there is
anything more special about how the column_expression is being handled,
it seems not to be necessary, as the naïve behavior would be adequate.)

Accordingly, I think the paragraph beginning "Unlike regular PostgreSQL
functions," is more likely to perplex readers than to enlighten them.
What it says about column_expression does not seem to lead to any useful
difference from the behavior if it were "just like regular PostgreSQL
functions".

The part about usefully using volatile functions in default_expression
remains important to mention.

The statement in an earlier paragraph that "It is possible for a
default_expression to reference the value of output columns that appear
prior to it in the column list" still may need some rework, because it
does not seem possible to refer to prior columns /within xmltable's own
column list/ (though that could be useful, and I think it is intended
in the standard). Doesn't seem to work in Oracle either....

While it does seem possible to refer to columns supplied by
/earlier FROM items in the containing SELECT/, that simply results in
multiple calls of xmltable, just as in the column_expression case.

>> I think the same example would produce the same output even with feature
>> (2)
>> absent. It's LATERAL doing the magic there. So I am doubtful that it
>> demonstrates (2).
>
> LATERAL is necessary, because  XMLTABLE can be used only in FROM clause,
> and in this case XMLTABLE has mutable parameters.

For what it's worth, if I repeat the query with the word LATERAL removed,
it works just the same. I think that's simply because the LATERAL behavior
is implied for a function-call FROM item, so the explicit word isn't needed.
The main thing is, evaluation proceeds in the way described under LATERAL
in the ref page for SELECT.

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule


Accordingly, I think the paragraph beginning "Unlike regular PostgreSQL
functions," is more likely to perplex readers than to enlighten them.
What it says about column_expression does not seem to lead to any useful
difference from the behavior if it were "just like regular PostgreSQL
functions".

regular Postgres' functions has evaluated all arguments before own execution. I think so this note is related much more to expressions used as defaults.


The part about usefully using volatile functions in default_expression
remains important to mention.

The statement in an earlier paragraph that "It is possible for a
default_expression to reference the value of output columns that appear
prior to it in the column list" still may need some rework, because it
does not seem possible to refer to prior columns /within xmltable's own
column list/ (though that could be useful, and I think it is intended
in the standard). Doesn't seem to work in Oracle either....

While it does seem possible to refer to columns supplied by
/earlier FROM items in the containing SELECT/, that simply results in
multiple calls of xmltable, just as in the column_expression case.

>> I think the same example would produce the same output even with feature
>> (2)
>> absent. It's LATERAL doing the magic there. So I am doubtful that it
>> demonstrates (2).
>
> LATERAL is necessary, because  XMLTABLE can be used only in FROM clause,
> and in this case XMLTABLE has mutable parameters.

For what it's worth, if I repeat the query with the word LATERAL removed,
it works just the same. I think that's simply because the LATERAL behavior
is implied for a function-call FROM item, so the explicit word isn't needed.
The main thing is, evaluation proceeds in the way described under LATERAL
in the ref page for SELECT.

In this case the LATERAL keyword is optional - with or without this keyword it is lateral join.

Regards

Pavel
 

Regards,
-Chap
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 01/20/19 12:48, Pavel Stehule wrote:

>>
>> Accordingly, I think the paragraph beginning "Unlike regular PostgreSQL
>> functions," is more likely to perplex readers than to enlighten them.
>> What it says about column_expression does not seem to lead to any useful
>> difference from the behavior if it were "just like regular PostgreSQL
>> functions".
>
> regular Postgres' functions has evaluated all arguments before own
> execution. I think so this note is related much more to expressions used as
> defaults.

Sure, but again, is there an example, or can one easily be constructed,
that shows the default expressions working in such a way?

I am not able to use a default expression to refer to an earlier
column in the column list of the xmltable call.

I am able to use a default expression to refer to a column of an earlier
FROM item in the enclosing SELECT. But such a query ends up having LATERAL
form (whether or not the word LATERAL is used), and re-executes xmltable
whenever the referenced column value changes. In that case, whether the
default argument is evaluated at function entry or later doesn't seem
to matter: the function is re-executed, so evaluating the new default
at the time of entry is sufficient.

So, I have still not been able to construct a query that requires the
deferred evaluation behavior. But perhaps there is a way I haven't
thought of.

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 01/20/19 17:13, Chapman Flack wrote:
> On 01/20/19 12:48, Pavel Stehule wrote:
>> regular Postgres' functions has evaluated all arguments before own
>> execution. I think so this note is related much more to expressions used as
>> defaults.
>
> Sure, but again, is there an example, or can one easily be constructed,
> that shows the default expressions working in such a way?

To make my question more concrete, here is the current regression test
query that uses an SQL expression for a default:

SELECT xmltable.*
FROM
  xmltest2,
  xmltable(('/d/r/' || lower(_path) || 'c')
    PASSING x
    COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
 a
----
 11
 12
 13
 14
(4 rows)


Here is the same query desugared into a call of the PL/Java "xmltable":

SELECT xmltable.*
FROM
  xmltest2,
  LATERAL (SELECT x AS ".",   ascii(_path) - 54 AS "A_DFT") AS p,
  "xmltable"(('/d/r/' || lower(_path) || 'c'),
    PASSING => p,
    COLUMNS => ARRAY[
      'let $a := x return xs:int(if (exists($a)) then $a else $A_DFT)'
    ]
  ) AS (a int);
 a
----
 11
 12
 13
 14
(4 rows)


So the PL/Java version works and produces the same results. And yet
it certainly is a "regular PostgreSQL function" made with CREATE FUNCTION,
no special treatment of arguments, all evaluated before entry in the usual
way.

So it appears that this example does not depend on any special treatment
of the default_expression.

Is there an example that can be constructed that would depend on the
special treatment (in which case, the PL/Java implementation would be
unable to produce the same result)?

Regards,
-Chap



... the xs:int(...) cast above is needed for now, just because the PL/Java
implementation does not yet include the standard's algorithm to find
the right cast automatically.

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Chapman Flack
On 01/20/19 20:07, Chapman Flack wrote:

> So it appears that this example does not depend on any special treatment
> of the default_expression.
>
> Is there an example that can be constructed that would depend on the
> special treatment (in which case, the PL/Java implementation would be
> unable to produce the same result)?

I see that I briefly forgot one difference that does matter, namely the
timing of a call to a volatile function like nextval. That detail certainly
needs to remain in the docs.

I am left wondering if that might be the only effect of the deferred
argument evaluation that really does matter.

Regards,
-Chap

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL vs SQL/XML Standards

Pavel Stehule
In reply to this post by Chapman Flack


ne 20. 1. 2019 23:13 odesílatel Chapman Flack <[hidden email]> napsal:
On 01/20/19 12:48, Pavel Stehule wrote:
>>
>> Accordingly, I think the paragraph beginning "Unlike regular PostgreSQL
>> functions," is more likely to perplex readers than to enlighten them.
>> What it says about column_expression does not seem to lead to any useful
>> difference from the behavior if it were "just like regular PostgreSQL
>> functions".
>
> regular Postgres' functions has evaluated all arguments before own
> execution. I think so this note is related much more to expressions used as
> defaults.

Sure, but again, is there an example, or can one easily be constructed,
that shows the default expressions working in such a way?

I am not able to use a default expression to refer to an earlier
column in the column list of the xmltable call.


probably you can see the effect if you use some volatile function .. random(), nextval(),

I think so notice in documentation was not a motivation to use it. It was explanation of implementation and warnings against side effect.



I am able to use a default expression to refer to a column of an earlier
FROM item in the enclosing SELECT. But such a query ends up having LATERAL
form (whether or not the word LATERAL is used), and re-executes xmltable
whenever the referenced column value changes. In that case, whether the
default argument is evaluated at function entry or later doesn't seem
to matter: the function is re-executed, so evaluating the new default
at the time of entry is sufficient.

it has sense only for volatile functions. it was not often. On second hand deferred evaluation shoul not be a problem, and more, it is evaluated only when it is necessary, what is more sensible for me.


So, I have still not been able to construct a query that requires the
deferred evaluation behavior. But perhaps there is a way I haven't
thought of.

Regards,
-Chap
Reply | Threaded
Open this post in threaded view
|

Re: House style for DocBook documentation?

Alvaro Herrera-9
In reply to this post by Chapman Flack
On 2019-Jan-19, Chapman Flack wrote:

> I have noticed a couple of things:
>
> - 'SQL' is often marked up as <acronym>SQL</acronym>, but far from always.
>
> - no such markup is applied to 'JSON' or 'XML' at all, at least
>   not in func.sgml.

I think these inconsistencies just stem from lack of a strong reviewer
hand on the topic.  Let's change that?

> - there is a README.links with this guideline:
>
>   o  Do not use text with <ulink> so the URL appears in printed output
>
>   but a grep -r in doc/src/sgml turns up 112 uses that observe the
>   guideline, and 147 that supply link text.

I think the README.links was written in the SGML times; now that we're
in XML it may need to be reconsidered.

> (thinks to self half-seriously about an XSL transform for generating
> printed output that could preserve link-texted links, add raised numbers,
> and produce a numbered URLs section at the back)

Well, if you have the time and inclination, and you think such changes
are improvements, feel free to propose them.  Do keep in mind we have a
number of outputs that would be good to keep consistent.

Thanks,

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

Reply | Threaded
Open this post in threaded view
|

Re: House style for DocBook documentation?

Chapman Flack
On 01/21/19 09:12, Alvaro Herrera wrote:

>> (thinks to self half-seriously about an XSL transform for generating
>> printed output that could preserve link-texted links, add raised numbers,
>> and produce a numbered URLs section at the back)
>
> Well, if you have the time and inclination, and you think such changes
> are improvements, feel free to propose them.  Do keep in mind we have a
> number of outputs that would be good to keep consistent.

Well, "consistent" what I was half-thinking about, FSVO "consistent".

For me, if I'm looking at an online document, I would prefer to see
the descriptive text of the link, rather than a long jaggy URL. If I
want to see the URL, I can hover over it, and if I want to go there,
I can click it.

But the point's well taken that in /printed output/, that's of no use.
Which is, in a sense, an inconsistency: in one format, you can follow the
links, while in another, you're out of luck.

Maybe a simpler transform for printed output, rather than collecting
all URLs into one section at the back, would just be to follow any
<ulink> that has link text with a <footnote> containing the same ulink
without the link text, so it shows the URL, and that would be right at
the bottom of the same 'page'.

That'd be an introductory XSL exercise....

In practice, applying such a transform "for printed output" would
probably mean applying it when generating PDF output, which of course
can also be viewed online (and probably most often is, these days).

So preserving the original ulink run into the text is still of use,
in a PDF viewer that can follow links, but adding the footnote ensures
that an actual hard copy is still usable.

I wouldn't think it important to apply the same treatment when making HTML.
So maybe the right value of "consistent" is the one that comes from
listing out the various output formats and specifying the right
transformation to be applied to each one.

(Now wonders if there's a way to do the same transform into HTML while
styling the footnote and marker to hide behind @media print....)

Regards,
-Chap

123456