Re: wCTE: about the name of the feature

The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.

The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a view

Comments?

regards, tom lane

On 2011-02-24 6:20 PM +0200, Tom Lane wrote:
> The wCTE patch refers to the feature it's adding as "DML WITH". I'm
> still pretty unhappy with that terminology. In my view of the world,
> "DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
> entry about the term
> http://en.wikipedia.org/wiki/Data_Manipulation_Language
> agrees that that's at least the majority usage, and even our own docs
> seem to use it to include SELECT as often as not. Since the distinction
> is absolutely critical to talking about this feature sensibly, I don't
> think it's a good plan to use an acronym that is guaranteed to produce
> uncertainty in the reader's mind.

Agreed.

> The best idea I have at the moment is to spell out "data modifying
> command" (or "statement") rather than relying on the acronym.
> In the code, we could change hasDmlWith to hasModifyingWith, for
> example. The error messages could read like
> data-modifying statement in WITH is not allowed in a view
>
> Comments?

Out of everything suggested so far, I think this is the best we have, if
we can fit the whole thing into out error messages. Quickly grepping
through the patch suggests that we can, at least for the cases in the
current patch.

I also prefer "statement" over "command".

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-24 6:20 PM +0200, Tom Lane wrote:
>> The best idea I have at the moment is to spell out "data modifying
>> command" (or "statement") rather than relying on the acronym.
>> In the code, we could change hasDmlWith to hasModifyingWith, for
>> example. The error messages could read like
>> data-modifying statement in WITH is not allowed in a view
>>
>> Comments?

> Out of everything suggested so far, I think this is the best we have, if
> we can fit the whole thing into out error messages. Quickly grepping
> through the patch suggests that we can, at least for the cases in the
> current patch.

> I also prefer "statement" over "command".

OK, I will make those adjustments. Are you going to do more work on the
documentation part of the patch? I can stick to working on the code
part meanwhile, if you are.

regards, tom lane

On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
> OK, I will make those adjustments. Are you going to do more work on the
> documentation part of the patch? I can stick to working on the code
> part meanwhile, if you are.

I am planning on working on the documentation this weekend.

Regards,
Marko Tiikkaja

On 02/24/2011 11:20 AM, Tom Lane wrote:
> The wCTE patch refers to the feature it's adding as "DML WITH". I'm
> still pretty unhappy with that terminology. In my view of the world,
> "DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
> entry about the term
> http://en.wikipedia.org/wiki/Data_Manipulation_Language
> agrees that that's at least the majority usage, and even our own docs
> seem to use it to include SELECT as often as not. Since the distinction
> is absolutely critical to talking about this feature sensibly, I don't
> think it's a good plan to use an acronym that is guaranteed to produce
> uncertainty in the reader's mind.
>
> The best idea I have at the moment is to spell out "data modifying
> command" (or "statement") rather than relying on the acronym.
> In the code, we could change hasDmlWith to hasModifyingWith, for
> example. The error messages could read like
> data-modifying statement in WITH is not allowed in a view
>
> Comments?
>

I think your're absolutely right. DML means that to me too. It's in
effect the opposite of DDL.

log_statement used "mod" for this category of statements. If we need a
new acronym, I modestly suggest "CUD" (CRUD without the R). The we could
make all sorts of puns about "chewing the CUD" :-)

cheers

andrew

On Thu, Feb 24, 2011 at 11:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The wCTE patch refers to the feature it's adding as "DML WITH".  I'm
> still pretty unhappy with that terminology.  In my view of the world,
> "DML" includes SELECT as well as INSERT/UPDATE/DELETE.  The wikipedia
> entry about the term
> http://en.wikipedia.org/wiki/Data_Manipulation_Language
> agrees that that's at least the majority usage, and even our own docs
> seem to use it to include SELECT as often as not.  Since the distinction
> is absolutely critical to talking about this feature sensibly, I don't
> think it's a good plan to use an acronym that is guaranteed to produce
> uncertainty in the reader's mind.
>
> The best idea I have at the moment is to spell out "data modifying
> command" (or "statement") rather than relying on the acronym.
> In the code, we could change hasDmlWith to hasModifyingWith, for
> example.  The error messages could read like
>        data-modifying statement in WITH is not allowed in a view
>
> Comments?

Great idea. I had the same complaint when I looked at this patch a
year ago, but didn't come up with nearly as good an idea as to what to
do about it.

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

On Feb 24, 2011, at 10:43 AM, Robert Haas wrote:

>> The best idea I have at the moment is to spell out "data modifying
>> command" (or "statement") rather than relying on the acronym.
>> In the code, we could change hasDmlWith to hasModifyingWith, for
>> example. The error messages could read like
>> data-modifying statement in WITH is not allowed in a view
>>
>> Comments?
>
> Great idea. I had the same complaint when I looked at this patch a
> year ago, but didn't come up with nearly as good an idea as to what to
> do about it.

I like "statement" better than "command," too, but love the acronym DMC. As in, "you want to Run [a] DMC." ;-P

David

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
>> OK, I will make those adjustments. Are you going to do more work on the
>> documentation part of the patch? I can stick to working on the code
>> part meanwhile, if you are.

> I am planning on working on the documentation this weekend.

I've gone ahead and applied the code portion of the patch, with
modifications as per discussion, and other editorialization.
I'll wait on you to produce documentation updates before dealing
with the docs, but I figured we might as well get some buildfarm
cycles on it meanwhile.

regards, tom lane

On 2011-02-26 2:00 AM, Tom Lane wrote:
> I've gone ahead and applied the code portion of the patch, with
> modifications as per discussion, and other editorialization.

Thanks a lot!

One thing bothers me though: what was the reason for requiring a
RETURNING clause for data-modifying statements in WITH?

> I'll wait on you to produce documentation updates before dealing
> with the docs, but I figured we might as well get some buildfarm
> cycles on it meanwhile.

Thanks, I'll send an improved version tomorrow.

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-26 2:00 AM, Tom Lane wrote:
>> I've gone ahead and applied the code portion of the patch, with
>> modifications as per discussion, and other editorialization.

> Thanks a lot!

> One thing bothers me though: what was the reason for requiring a
> RETURNING clause for data-modifying statements in WITH?

That test was in your patch, no? I moved the code to another place
but it's still enforcing the same thing, namely that you can't reference
the output of an INSERT/UPDATE/DELETE that hasn't got RETURNING.

regards, tom lane

On Thu, Feb 24, 2011 at 11:20:48AM -0500, Tom Lane wrote:
> The wCTE patch refers to the feature it's adding as "DML WITH". I'm
> still pretty unhappy with that terminology. In my view of the world,
> "DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
> entry about the term
> http://en.wikipedia.org/wiki/Data_Manipulation_Language
> agrees that that's at least the majority usage, and even our own docs
> seem to use it to include SELECT as often as not. Since the distinction
> is absolutely critical to talking about this feature sensibly, I don't
> think it's a good plan to use an acronym that is guaranteed to produce
> uncertainty in the reader's mind.
>
> The best idea I have at the moment is to spell out "data modifying
> command" (or "statement") rather than relying on the acronym.
> In the code, we could change hasDmlWith to hasModifyingWith, for
> example. The error messages could read like
> data-modifying statement in WITH is not allowed in a view
>
> Comments?

+1

If we ever decide add in what I'd originally envisioned, namely DCL
and DDL, the name continues to describe what's going on :)

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 Thu, Feb 24, 2011 at 11:35:30AM -0800, David Wheeler wrote:
> On Feb 24, 2011, at 10:43 AM, Robert Haas wrote:
>
> >> The best idea I have at the moment is to spell out "data modifying
> >> command" (or "statement") rather than relying on the acronym.
> >> In the code, we could change hasDmlWith to hasModifyingWith, for
> >> example. The error messages could read like
> >> data-modifying statement in WITH is not allowed in a view
> >>
> >> Comments?
> >
> > Great idea. I had the same complaint when I looked at this patch
> > a year ago, but didn't come up with nearly as good an idea as to
> > what to do about it.
>
> I like "statement" better than "command," too, but love the acronym
> DMC. As in, "you want to Run [a] DMC." ;-P

Hit it, Run! ;)

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 Fri, Feb 25, 2011 at 7:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
>>> OK, I will make those adjustments.  Are you going to do more work on the
>>> documentation part of the patch?  I can stick to working on the code
>>> part meanwhile, if you are.
>
>> I am planning on working on the documentation this weekend.
>
> I've gone ahead and applied the code portion of the patch,

Yay! I'm excited about this, particularly the possible "pipelining"
stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... and
have it be like cool and fast and stuff.

Or at least I hope you can do that.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Yay! I'm excited about this, particularly the possible "pipelining"
> stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... and
> have it be like cool and fast and stuff.

> Or at least I hope you can do that.

It's gonna need some work yet. As things stand, the tuples are indeed
pipelined through, but the CteScan nodes *also* stash them aside into
tuplestores, just in case somebody demands a rescan. Fixing that will
require revisiting the exec flags (EXEC_FLAG_REWIND etc). We don't
currently distinguish "it's unlikely you'll have to rescan" from
"you're guaranteed not to have to rescan", but a CteScan that's covering
a ModifyTable has to know the latter to not have to keep hold of copies
of the RETURNING tuples.

It might be a small enough change to do after alpha starts, but I don't
have time for it right now.

regards, tom lane

On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yay!  I'm excited about this, particularly the possible "pipelining"
>> stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ...  and
>> have it be like cool and fast and stuff.
>
>> Or at least I hope you can do that.
>
> It's gonna need some work yet.  As things stand, the tuples are indeed
> pipelined through, but the CteScan nodes *also* stash them aside into
> tuplestores, just in case somebody demands a rescan.  Fixing that will
> require revisiting the exec flags (EXEC_FLAG_REWIND etc).  We don't
> currently distinguish "it's unlikely you'll have to rescan" from
> "you're guaranteed not to have to rescan", but a CteScan that's covering
> a ModifyTable has to know the latter to not have to keep hold of copies
> of the RETURNING tuples.
>
> It might be a small enough change to do after alpha starts, but I don't
> have time for it right now.

Well, if nothing else, the potential is there for a future release.
I'm probably not quite as excited about this feature as David Fetter
(and my 100-Watt lightbulb is not quite as bright as the sun at high
noon in midsummer) but I do think it's pretty cool, and I appreciate
you getting it in, even in a somewhat basic form.

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

On 2011-02-26 4:41 AM +0200, Tom Lane wrote:
> Marko Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> One thing bothers me though: what was the reason for requiring a
>> RETURNING clause for data-modifying statements in WITH?
>
> That test was in your patch, no? I moved the code to another place
> but it's still enforcing the same thing, namely that you can't reference
> the output of an INSERT/UPDATE/DELETE that hasn't got RETURNING.

Oh, right. I misunderstood.

Regards,
Marko Tiikkaja

On Sat, Feb 26, 2011 at 12:52:40AM -0500, Robert Haas wrote:
> On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> Yay!  I'm excited about this, particularly the possible "pipelining"
> >> stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ...  and
> >> have it be like cool and fast and stuff.
> >
> >> Or at least I hope you can do that.
> >
> > It's gonna need some work yet.  As things stand, the tuples are indeed
> > pipelined through, but the CteScan nodes *also* stash them aside into
> > tuplestores, just in case somebody demands a rescan.  Fixing that will
> > require revisiting the exec flags (EXEC_FLAG_REWIND etc).  We don't
> > currently distinguish "it's unlikely you'll have to rescan" from
> > "you're guaranteed not to have to rescan", but a CteScan that's covering
> > a ModifyTable has to know the latter to not have to keep hold of copies
> > of the RETURNING tuples.
> >
> > It might be a small enough change to do after alpha starts, but I don't
> > have time for it right now.
>
> Well, if nothing else, the potential is there for a future release.

That's kinda where I am on this one. In the grand PostgreSQL
tradition, we can have something that works before we have something
that works fast :)

> I'm probably not quite as excited about this feature as David Fetter

Heh!

> (and my 100-Watt lightbulb is not quite as bright as the sun at high
> noon in midsummer) but I do think it's pretty cool, and I appreciate
> you getting it in, even in a somewhat basic form.

Me, too!

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 2011-02-24 6:40 PM, I wrote:
> I am planning on working on the documentation this weekend.

And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.

I tried to be more strict about using "subquery" when talking about
WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-24 6:40 PM, I wrote:
>> I am planning on working on the documentation this weekend.

> And here's my attempt. The language is a bit poor at some places but I
> can't think of anything better.

Thanks, will work on this next.

regards, tom lane

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-24 6:40 PM, I wrote:
>> I am planning on working on the documentation this weekend.

> And here's my attempt. The language is a bit poor at some places but I
> can't think of anything better.

Applied after some rather heavy editorialization.

> I tried to be more strict about using "subquery" when talking about
> WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.

I undid most of those changes --- it didn't seem to add anything to be
strict in this way, and anyway you hadn't done it consistently,
eg the syntax still had "with_query".

regards, tom lane

On 2011-02-28 8:20 AM +0200, Tom Lane wrote:
> Marko Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> On 2011-02-24 6:40 PM, I wrote:
>>> I am planning on working on the documentation this weekend.
>
>> And here's my attempt. The language is a bit poor at some places but I
>> can't think of anything better.
>
> Applied after some rather heavy editorialization.

Thanks again.

>> I tried to be more strict about using "subquery" when talking about
>> WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.
>
> I undid most of those changes --- it didn't seem to add anything to be
> strict in this way, and anyway you hadn't done it consistently,
> eg the syntax still had "with_query".

I wasn't so sure about those changes either. It does seem more
consistent this way.

Regards,
Marko Tiikkaja