Re: "writable CTEs"

As a side note, I think the term "writable CTE" is a misnomer. The CTE
is not writable. The CTE is the result of a write operation.

A writable CTE would look like this:

WITH foo AS (SELECT ...) UPDATE foo SET ...

a bit like an updatable view.

AFAICT, the current patch doesn't use the term, so there is no problem,
but just for those who are preparing propaganda and such.

On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
> As a side note, I think the term "writable CTE" is a misnomer. The CTE
> is not writable. The CTE is the result of a write operation.
>
> A writable CTE would look like this:
>
> WITH foo AS (SELECT ...) UPDATE foo SET ...
>
> a bit like an updatable view.
>
> AFAICT, the current patch doesn't use the term, so there is no problem,
> but just for those who are preparing propaganda and such.

I think I've used "DML WITH" in the patch, but I don't like that either.
Naming this feature seems to be quite a challenge.

I'd prefer something short but easily understandable, but those two
might be mutually exclusive.

Regards,
Marko Tiikkaja

On Wed, Dec 22, 2010 at 10:44 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> I'd prefer something short but easily understandable, but those two might be
> mutually exclusive.

Volatile CTE's doesn't add any more clarity either. Maybe "Round Trip
Reduction" CTE's. :)

--
Regards,
Richard Broersma Jr.

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:

> I think I've used "DML WITH" in the patch, but I don't like that
> either. Naming this feature seems to be quite a challenge.
>
> I'd prefer something short but easily understandable, but those
> two might be mutually exclusive.

How about?:

DML CTEs
DML-based CTEs
RETURNING-based CTEs

-Kevin

On Wed, Dec 22, 2010 at 12:54:39PM -0600, Kevin Grittner wrote:
> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
>
> > I think I've used "DML WITH" in the patch, but I don't like that
> > either. Naming this feature seems to be quite a challenge.
> >
> > I'd prefer something short but easily understandable, but those
> > two might be mutually exclusive.
>
> How about?:
>
> DML CTEs
> DML-based CTEs
> RETURNING-based CTEs

When I designed the feature, I'd planned to be able to put most kinds
of statement inside or outside the CTE, not just DML writes. You can
imagine cases for DCL (GRANT/REVOKE based on a catalog query) or DDL
(partition management), and I did.

We could call them, "Expanded CTEs," but that only freezes the prior
norm making them read-only, so I think "Writeable CTEs" captures it
pretty well.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

On ons, 2010-12-22 at 20:44 +0200, Marko Tiikkaja wrote:
> On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
> > As a side note, I think the term "writable CTE" is a misnomer. The CTE
> > is not writable. The CTE is the result of a write operation.
> >
> > A writable CTE would look like this:
> >
> > WITH foo AS (SELECT ...) UPDATE foo SET ...
> >
> > a bit like an updatable view.
> >
> > AFAICT, the current patch doesn't use the term, so there is no problem,
> > but just for those who are preparing propaganda and such.
>
> I think I've used "DML WITH" in the patch, but I don't like that either.
> Naming this feature seems to be quite a challenge.

*Writing* CTEs is more accurate.

On Mon, Dec 27, 2010 at 11:47:14PM +0200, Peter Eisentraut wrote:
> On ons, 2010-12-22 at 20:44 +0200, Marko Tiikkaja wrote:
> > On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
> > > As a side note, I think the term "writable CTE" is a misnomer. The CTE
> > > is not writable. The CTE is the result of a write operation.
> > >
> > > A writable CTE would look like this:
> > >
> > > WITH foo AS (SELECT ...) UPDATE foo SET ...
> > >
> > > a bit like an updatable view.
> > >
> > > AFAICT, the current patch doesn't use the term, so there is no problem,
> > > but just for those who are preparing propaganda and such.
> >
> > I think I've used "DML WITH" in the patch, but I don't like that either.
> > Naming this feature seems to be quite a challenge.
>
> *Writing* CTEs is more accurate.

OK :)

On the bright side, we have a decades-long tradition of horrible names
on this project, one early example of which is a name that abbreviates
the phrase, 'POST-"interactive GRaphics REtrieval System."' ;)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.

Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs at
certain select places? I could write a documentation patch.

--
Regards,
Peter Geoghegan

On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
> It's worth noting that officially (i.e. in the docs), we don't even
> call CTEs CTEs at any point. We call them WITH queries. I think that
> that's a mistake because we call them CTEs everywhere else.

Agreed.

> Is there interest in correcting this, by putting "CTEs" or "Common
> table expressions" in parenthesis after "WITH queries" in the docs
> at certain select places? I could write a documentation patch.

+1 :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

On Mon, Dec 27, 2010 at 9:28 PM, David Fetter <david(at)fetter(dot)org> wrote:
> On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
>> It's worth noting that officially (i.e. in the docs), we don't even
>> call CTEs CTEs at any point. We call them WITH queries. I think that
>> that's a mistake because we call them CTEs everywhere else.
>
> Agreed.
>
>> Is there interest in correcting this, by putting "CTEs" or "Common
>> table expressions" in parenthesis after "WITH queries" in the docs
>> at certain select places? I could write a documentation patch.
>
> +1 :)

Would it make sense to work a section into the documentation
explaining the semantics of CTEs? e.g., evaluate-once.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Dec 27, 2010 at 09:51:01PM -0500, Robert Haas wrote:
> On Mon, Dec 27, 2010 at 9:28 PM, David Fetter <david(at)fetter(dot)org> wrote:
> > On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
> >> It's worth noting that officially (i.e. in the docs), we don't even
> >> call CTEs CTEs at any point. We call them WITH queries. I think that
> >> that's a mistake because we call them CTEs everywhere else.
> >
> > Agreed.
> >
> >> Is there interest in correcting this, by putting "CTEs" or "Common
> >> table expressions" in parenthesis after "WITH queries" in the docs
> >> at certain select places? I could write a documentation patch.
> >
> > +1 :)
>
> Would it make sense to work a section into the documentation
> explaining the semantics of CTEs? e.g., evaluate-once.

Yep. It should probably go where "WITH Queries" is now, i.e.

http://www.postgresql.org/docs/current/static/queries-with.html

would become (doesn't exist yet):

http://www.postgresql.org/docs/current/static/queries-common-table-expression.html

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
> It's worth noting that officially (i.e. in the docs), we don't even
> call CTEs CTEs at any point. We call them WITH queries. I think that
> that's a mistake because we call them CTEs everywhere else.

I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
>> It's worth noting that officially (i.e. in the docs), we don't even
>> call CTEs CTEs at any point. We call them WITH queries. I think that
>> that's a mistake because we call them CTEs everywhere else.

> I think "WITH query" or "WITH clause" is more understandable than CTE,
> which to me is a term that has no relationship with anything else.

I'm with Peter on that. CTE is a completely meaningless term to most
users.

As for the problem at hand, couldn't we use "WITH ... RETURNING", or
some other phrase based on what users actually see/write? DML has
the same problem as CTE, namely it's just another damn TLA. It may
be one that more people have heard of, but that doesn't make it
particularly attractive.

regards, tom lane

On 28 December 2010 14:53, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm with Peter on that.  CTE is a completely meaningless term to most
> users.

I don't believe that that's the case. If CTE is a completely
meaningless term to most users, WITH query is even more meaningless. I
never refer to WITH queries in conversation, and I have never heard
someone else do so. I have often talked about CTEs though. Besides,
I'm not suggesting that we should completely change the title, or
change the section name at all, or change any existing text from the
docs. The doc patch is just a clarification that I believe will be
useful.

If I search for "common table expressions" on Wikipedia, I am sent to
the common table expressions article, without any re-direction. The
article doesn't mention "with query" as a synonym of CTE at any point.
If I search for "With query", the first page of results (20 articles)
doesn't have anything about CTEs at all. The situation with Google is
similar. The situation with postgresql.org is similar, except that
searching for CTE there is fairly useless too. Granted, all of this
may have something to do with the ambiguity of the term "with query"
in a more general context, but the fact that I never hear the term in
conversation probably has something to do with that too.

--
Regards,
Peter Geoghegan

On Tue, Dec 28, 2010 at 04:35:26PM +0200, Peter Eisentraut wrote:
> On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
> > It's worth noting that officially (i.e. in the docs), we don't even
> > call CTEs CTEs at any point. We call them WITH queries. I think that
> > that's a mistake because we call them CTEs everywhere else.
>
> I think "WITH query" or "WITH clause" is more understandable than CTE,
> which to me is a term that has no relationship with anything else.

Common Table Expression, or CTE for short, is the standard
terminology, and I don't just mean SQL:2008. It's standard in DB2,
Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and
Sybase SQL Anywhere, at a minimum.

"WITH query" is a pure PostgreSQL invention, and not a super helpful
one to the vast majority of users. Please bear in mind that if this
is to remain a successful project, the vast majority of users are
*future* users, not current or past ones. We don't talk about
"Subquery queries" or "FULL JOIN queries" and give them their own doc
section, nor should we. We should instead refactor the docs to point
to CTEs in the appropriate places, and it's my hope that those places
will increase over time.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

On tis, 2010-12-28 at 16:04 +0000, Peter Geoghegan wrote:
> If I search for "common table expressions" on Wikipedia, I am sent to
> the common table expressions article, without any re-direction. The
> article doesn't mention "with query" as a synonym of CTE at any point.
> If I search for "With query", the first page of results (20 articles)
> doesn't have anything about CTEs at all.

Apparently, the term "common table expression" comes from Microsoft and
IBM. If you search for "SELECT WITH clause" you get a bunch of Oracle
links.

On tis, 2010-12-28 at 09:31 -0800, David Fetter wrote:
> Common Table Expression, or CTE for short, is the standard
> terminology, and I don't just mean SQL:2008. It's standard in DB2,
> Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and
> Sybase SQL Anywhere, at a minimum.
>
> "WITH query" is a pure PostgreSQL invention, and not a super helpful
> one to the vast majority of users.

The phrase "common table expression" does not appear anywhere in the SQL
standard. The standard uses the grammar symbol <with clause>.

On 28 December 2010 20:07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> The phrase "common table expression" does not appear anywhere in the SQL
> standard.  The standard uses the grammar symbol <with clause>.

I think we're losing sight of the issue a bit here.

No one is proposing that we call WITH queries common table
expressions. As I think we all agree, the term "WITH query" and
"common table expression" are not synonymous. A WITH query is
comprised of one or more common table expressions, plus a conventional
SELECT query.

All that I'm asking is that we /specify/ that the "subqueries" already
mentioned in the docs are common table expressions. This terminology
is less confusing and ambiguous, is demonstrably already in widespread
use, and will probably lay the groundwork for whatever name we choose
for wCTEs.

I think that it makes sense to change the title of the relevant
section from "WITH Queries" to "WITH Queries (Common Table
Expressions)" because CTEs are the defining characteristic of WITH
queries, and, as I've said, the term "common table expression" has
mindshare in a way that "WITH query" clearly doesn't.

--
Regards,
Peter Geoghegan

On 29/12/10 03:35, Peter Eisentraut wrote:
> On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
>> It's worth noting that officially (i.e. in the docs), we don't even
>> call CTEs CTEs at any point. We call them WITH queries. I think that
>> that's a mistake because we call them CTEs everywhere else.
> I think "WITH query" or "WITH clause" is more understandable than CTE,
> which to me is a term that has no relationship with anything else.
>
>

Peter's comment certainly resonates with me. When I first heard about
this "CTE" business I had to go to the web to discover that they were
components of the WITH clause - which I was familiar with from my DB2
days...

regards

Mark

On 2010-12-29 09:16, Mark Kirkwood wrote:
> On 29/12/10 03:35, Peter Eisentraut wrote:
>> On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
>>> It's worth noting that officially (i.e. in the docs), we don't even
>>> call CTEs CTEs at any point. We call them WITH queries. I think that
>>> that's a mistake because we call them CTEs everywhere else.
>> I think "WITH query" or "WITH clause" is more understandable than CTE,
>> which to me is a term that has no relationship with anything else.
>>
>>
>
> Peter's comment certainly resonates with me. When I first heard about
> this "CTE" business I had to go to the web to discover that they were
> components of the WITH clause - which I was familiar with from my DB2
> days...
For me it was the converse.. I first heard of Common Table Expressions
from SQLserver users, at roughly the same time that CTE's were
introduced in 8.4. When I decided to use them, it took me a while to
figure out the docs refered to it as "WITH queries".

ISTM we're already past the choice to have a single name. IMHO it would
be best if the documentation has a reference / index part in which both
WITH queries and Common Table Expressions (CTE) are listed.

Also, the terms CTE and CTEScan appear in EXPLAIN output, it would be
nice to have a meaningful hit when looking for the term in the
documentation page, instead of 'Your search for *cte* returned no hits.'

regards,
Yeb Havinga

On Wed, Dec 29, 2010 at 4:19 AM, Yeb Havinga <yebhavinga(at)gmail(dot)com> wrote:
> Also, the terms CTE and CTEScan appear in EXPLAIN output, it would be nice
> to have a meaningful hit when looking for the term in the documentation
> page, instead of 'Your search for cte returned no hits.'

This is an excellent point.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 28, 2010 at 11:07:59PM +0000, Peter Geoghegan wrote:
> On 28 December 2010 20:07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > The phrase "common table expression" does not appear anywhere in the SQL
> > standard.  The standard uses the grammar symbol <with clause>.
>
> I think we're losing sight of the issue a bit here.
>
> No one is proposing that we call WITH queries common table
> expressions. As I think we all agree, the term "WITH query" and
> "common table expression" are not synonymous. A WITH query is
> comprised of one or more common table expressions, plus a conventional
> SELECT query.

As of 9.1, the thing appended to the CTE(s) can be a conventional DML
query (SELECT, INSERT, UPDATE or DELETE). I'm hoping to expand this
in future versions. :)

> All that I'm asking is that we /specify/ that the "subqueries"
> already mentioned in the docs are common table expressions.

+1

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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