Re: [HACKERS] wCTE: about the name of the feature
On 2011-02-28 8:20 AM +0200, Tom Lane wrote: Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 *** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *** *** 1526,1532 SELECT select_list FROM table_expression ! WITH Queries (Common Table Expressions) WITH --- 1526,1532 ! WITH Statements (Common Table Expressions) WITH *** *** 1539,1549 SELECT select_list FROM table_expression !WITH provides a way to write subqueries for use in a larger !query. The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining !temporary tables that exist just for this query. One use of this feature !is to break down complicated queries into simpler parts. An example is: WITH regional_sales AS ( --- 1539,1559 !WITH provides a way to write auxiliary statements for use in a !larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining !temporary tables that exist just for this query. ! ! ! !SELECT Queries ! ! ! * ! ! !One use of this feature is to break down complicated queries into simpler !parts. An example is: WITH regional_sales AS ( *** *** 1806,1811 SELECT n FROM t LIMIT 100; --- 1816,1917 In each case it effectively provides temporary table(s) that can be referred to in the main command. + + +Data-Modifying Statements + + + * + + + + You can also use data-modifying statements INSERT, + UPDATE and DELETE in WITH. This allows + you to perform many different operations in the same query. An example is: + + + WITH moved_rows AS ( + DELETE FROM ONLY products + WHERE + "date" >= '2010-10-01' AND + "date" < '2010-11-01' + RETURNING * + ) + INSERT INTO products_log + SELECT * FROM moved_rows; + + + which moves rows from "products" to "products_log". In the example above, + the WITH clause is attached to the INSERT, not the + SELECT. This is important, because data-modifying statements + are not allowed in WITH clauses which are not attached to the + top level statement. However, normal WITH visibility rules + apply: it is possible to refer to a data-modifying WITH from a + subquery. + + + + Recursive self-references in data-modifying statements are not + allowed. In some cases it is possible to work around this limitation by + referring to the output of a recursive WITH: + + + WITH RECURSIVE included_parts(sub_part, part) AS ( + SELECT sub_part, part FROM parts WHERE part = 'our_product' + UNION ALL + SELECT p.sub_part, p.part + FROM included_parts pr, parts p + WHERE p.part = pr.sub_part + ) + DELETE FROM parts + WHERE part IN (SELECT part FROM included_parts); + + + The above query would remove all direct and indirect subparts of a product. + + + + The execution of data-modifying statements in WITH is + interleaved with the main plan, and the order in which the statements + are executed is arbitrary. The changes made by data-modifying statements + are not visible to the query. + + + + Trying to update the same row twice in a single command is not supported. + Only one of the modifications takes place, but it is not easy (and + sometimes not possible) to reliably predict which one. This also applies + to deleting a row that was already updated in the same command; only the + update is performed. You should generally avoid trying to modify a single + row twice in a single command. + + + + + Data-modifying statements are executed exactly once, and always to + completion. If a WITH containing a data-modifying statement + is not referred to in the query, it is possible to omit the + RETURNING clause: + + + WITH t AS ( + DELETE FROM foo + ) + DELETE FROM bar; + + + The example above would remove all rows from tables "foo" and "bar". The + number of affected rows returned to the client would only include rows + removed from "bar". + + + + Any table used as the target of a data-modifying statement in + WITH must not contain a conditional rule, an ALSO + rule or an INSTEAD rule with multiple statements. + + + *** a/doc/src/sgml/ref/delete.sgml --- b/doc/src/sgml/ref/delete.sgml *** *** 89,95 DELETE FROM [ ONLY ] table [ [ AS ] The WITH clause
Re: [HACKERS] wCTE: about the name of the feature
On Sat, Feb 26, 2011 at 12:52:40AM -0500, Robert Haas wrote: > On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane wrote: > > Robert Haas 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
On 2011-02-26 4:41 AM +0200, Tom Lane wrote: Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane wrote: > Robert Haas 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Robert Haas 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
On Fri, Feb 25, 2011 at 7:00 PM, Tom Lane wrote: > Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
On Thu, Feb 24, 2011 at 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? 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
Marko Tiikkaja 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: about the name of the feature
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
