Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING

On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov <bashtanov@imap.cc> wrote:

Hello,

Currently the documentation says that one can put "a list of table
expressions"
after FROM in UPDATE or after USING in DELETE.
However, "table expression" is defined as a complex of
FROM, WHERE, GROUP BY and HAVING clauses [1].
The thing one can list in the FROM clause in a comma-separated manner
is called a table reference [2].
SELECT reference does not use this term but explains what they could be
[3].

Please could someone have a look at the patch attached?
It's not just pedantry but rather based on a real-life example of
someone reading and being not sure
whether e.g. joins can be used in there.

Best, Alex

[1] https://www.postgresql.org/docs/devel/queries-table-expressions.html
[2]

https://www.postgresql.org/docs/devel/queries-table-expressions.html#QUERIES-FROM
[3] https://www.postgresql.org/docs/devel/sql-select.html#SQL-FROM

Drive-by comment - I'm on board with the idea but I do not believe this
patch accomplishes the goal.

IMO there is too much indirection happening and trying to get terms exactly
right, so the user can find or remember them from elsewhere in the
documentation, doesn't seem like the best solution. The material isn't
that extensive and since it is covered elsewhere a little bit more
explicitness in the DELETE and FROM documentation seems like a better path
forward.

David J.

On Thu, Feb 13, 2020 at 11:13:32AM +0000, Alexey Bashtanov wrote:

Hello,

Currently the documentation says that one can put "a list of table
expressions"
after FROM in UPDATE or after USING in DELETE.
However, "table expression" is defined as a complex of
FROM, WHERE, GROUP BY and HAVING clauses [1].
The thing one can list in the FROM clause in a comma-separated manner
is called a table reference [2].
SELECT reference does not use this term but explains what they could be [3].

Please could someone have a look at the patch attached?
It's not just pedantry but rather based on a real-life example of someone
reading and being not sure
whether e.g. joins can be used in there.

Thanks for doing this!

Speaking of examples, there should be more of them illustrating some
of the cases you name.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov <bashtanov@imap.cc> wrote:

Please could someone have a look at the patch attached?
It's not just pedantry but rather based on a real-life example of
someone reading and being not sure
whether e.g. joins can be used in there.

Drive-by comment - I'm on board with the idea but I do not believe this
patch accomplishes the goal.
IMO there is too much indirection happening and trying to get terms exactly
right, so the user can find or remember them from elsewhere in the
documentation, doesn't seem like the best solution. The material isn't
that extensive and since it is covered elsewhere a little bit more
explicitness in the DELETE and FROM documentation seems like a better path
forward.

I see where you're coming from, but I do not think that repeating the
whole from_item syntax in UPDATE and DELETE is the best way forward.
In the first place, we'd inevitably forget to update those copies,
and in the second, I'm not sure that the syntax is all that helpful
without all the supporting text in the SELECT ref page --- which
surely we aren't going to duplicate.

I think the real problem with the places Alexey is on about is that
they're too waffle-y. They use wording like "similar to", leaving
one wondering what discrepancies exist but are being papered over.
In point of fact, as a look into gram.y will show, what you can
write after UPDATE ... FROM or DELETE ... USING is *exactly* the
same thing as what you can write after SELECT ... FROM. So what
I'm in favor of here is:

* Change the synopsis entries to look like "FROM from_item [, ...]"
and "USING from_item [, ...]", so that they match the SELECT
synopsis exactly.

* In the text, describe from_item as being exactly the same as
it is in SELECT.

(Compare the handling of with_query, which has pretty much the
same problem of being way too complex to document three times.)

regards, tom lane

On Thu, Feb 13, 2020 at 9:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov <bashtanov@imap.cc>

wrote:

Please could someone have a look at the patch attached?
It's not just pedantry but rather based on a real-life example of
someone reading and being not sure
whether e.g. joins can be used in there.

Drive-by comment - I'm on board with the idea but I do not believe this
patch accomplishes the goal.
IMO there is too much indirection happening and trying to get terms

exactly

right, so the user can find or remember them from elsewhere in the
documentation, doesn't seem like the best solution. The material isn't
that extensive and since it is covered elsewhere a little bit more
explicitness in the DELETE and FROM documentation seems like a better

path

forward.

I see where you're coming from, but I do not think that repeating the
whole from_item syntax in UPDATE and DELETE is the best way forward.
In the first place, we'd inevitably forget to update those copies,
and in the second, I'm not sure that the syntax is all that helpful
without all the supporting text in the SELECT ref page --- which
surely we aren't going to duplicate.

I think the real problem with the places Alexey is on about is that
they're too waffle-y. They use wording like "similar to", leaving
one wondering what discrepancies exist but are being papered over.
In point of fact, as a look into gram.y will show, what you can
write after UPDATE ... FROM or DELETE ... USING is *exactly* the
same thing as what you can write after SELECT ... FROM. So what
I'm in favor of here is:

* Change the synopsis entries to look like "FROM from_item [, ...]"
and "USING from_item [, ...]", so that they match the SELECT
synopsis exactly.

* In the text, describe from_item as being exactly the same as
it is in SELECT.

+1

I didn't want a wholesale repetition but the whole "similar to" piece is
indeed my issue and this addresses it sufficiently.

David J.

On Thu, Feb 13, 2020 at 11:26:45AM -0500, Tom Lane wrote:

I see where you're coming from, but I do not think that repeating the
whole from_item syntax in UPDATE and DELETE is the best way forward.
In the first place, we'd inevitably forget to update those copies,
and in the second, I'm not sure that the syntax is all that helpful
without all the supporting text in the SELECT ref page --- which
surely we aren't going to duplicate.

I think the real problem with the places Alexey is on about is that
they're too waffle-y. They use wording like "similar to", leaving
one wondering what discrepancies exist but are being papered over.
In point of fact, as a look into gram.y will show, what you can
write after UPDATE ... FROM or DELETE ... USING is *exactly* the
same thing as what you can write after SELECT ... FROM. So what
I'm in favor of here is:

* Change the synopsis entries to look like "FROM from_item [, ...]"
and "USING from_item [, ...]", so that they match the SELECT
synopsis exactly.

* In the text, describe from_item as being exactly the same as
it is in SELECT.

(Compare the handling of with_query, which has pretty much the
same problem of being way too complex to document three times.)

I have implemented the ideas above in the attached patch. I have
synchronized the syntax to match SELECT, and synchronized the paragraphs
describing the item.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Bruce Momjian <bruce@momjian.us> writes:

I have implemented the ideas above in the attached patch. I have
synchronized the syntax to match SELECT, and synchronized the paragraphs
describing the item.

I think that the DELETE synopsis should look like

[ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]

so that there's not any question which part of the SELECT syntax we're
talking about. I also think that the running text in both cases should
say in exactly these words "from_item means the same thing as it does
in SELECT"; the wording you propose still seems to be dancing around
the point, leaving readers perhaps not quite sure about what is meant.

In the DELETE case you could alternatively say "using_item means the same
thing as from_item does in SELECT", but that doesn't really seem like an
improvement to me.

regards, tom lane

On Tue, Mar 17, 2020 at 10:58:54PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I have implemented the ideas above in the attached patch. I have
synchronized the syntax to match SELECT, and synchronized the paragraphs
describing the item.

I think that the DELETE synopsis should look like

[ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]

so that there's not any question which part of the SELECT syntax we're
talking about. I also think that the running text in both cases should
say in exactly these words "from_item means the same thing as it does
in SELECT"; the wording you propose still seems to be dancing around
the point, leaving readers perhaps not quite sure about what is meant.

In the DELETE case you could alternatively say "using_item means the same
thing as from_item does in SELECT", but that doesn't really seem like an
improvement to me.

OK, updated patch attached.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Bruce Momjian <bruce@momjian.us> writes:

OK, updated patch attached.

LGTM, thanks.

regards, tom lane

On Wednesday, March 18, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bruce Momjian <bruce@momjian.us> writes:

OK, updated patch attached.

LGTM, thanks

+1

David J.

On Wed, Mar 18, 2020 at 12:24:45PM -0400, Bruce Momjian wrote:

On Tue, Mar 17, 2020 at 10:58:54PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I have implemented the ideas above in the attached patch. I have
synchronized the syntax to match SELECT, and synchronized the paragraphs
describing the item.

I think that the DELETE synopsis should look like

[ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]

so that there's not any question which part of the SELECT syntax we're
talking about. I also think that the running text in both cases should
say in exactly these words "from_item means the same thing as it does
in SELECT"; the wording you propose still seems to be dancing around
the point, leaving readers perhaps not quite sure about what is meant.

In the DELETE case you could alternatively say "using_item means the same
thing as from_item does in SELECT", but that doesn't really seem like an
improvement to me.

OK, updated patch attached.

Patch appied through 9.5.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +