Re: NEXT VALUE FOR sequence
Ashutosh Bapat wrote: > On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe > wrote: > > Tom Lane wrote: > > > Laurenz Albe writes: > > > > The SQL standard has the expression "NEXT VALUE FOR asequence" to do > > > > what we traditionally do with "nextval('asequence')". > > > > This is an attempt to implement this on top of the recently introduced > > > > NextValueExpr node. > > > > > > This has been proposed repeatedly, and rejected repeatedly, because in > > > fact the standard's semantics for NEXT VALUE FOR are *not* like nextval(). > > > See SQL:2011 4.22.2 "Operations involving sequence generators": > > > > > > If there are multiple instances of s specifying > > > the same sequence generator within a single SQL-statement, all those > > > instances return the same value for a given row processed by that > > > SQL-statement. > > > > > > This is not terribly exact --- what is a "processed row" in a join query, > > > for instance? But it's certainly not supposed to act like independent > > > executions of nextval() or NextValueExpr would. Pending somebody doing > > > the legwork to produce something that at least arguably conforms to the > > > spec's semantics, we've left the syntax unimplemented. > > > > Would it be reasonable to say that any two NextValueExpr in the same > > target list are "in one row"? > > I think, "processed row" thing gets pretty complicated. Consider > simple case. What happens when NextValueExpr appears in one of the > conditions and that row gets eliminated, do we consider that as a > processed row and increment the NextValueExpr or do not increment it? I think that is an unrelated question. In my opinion both would be ok. SQL does not decree in which order conditions are evaluated, and it is ok to evaluate an expression even if it is never used, right? All that has to be guaranteed is that using "NEXT VALUE FOR asequence" several times "in one row" (whatever exactly that is) will return the same value each time. The exact rules seem to be: a) If NVE is directly contained in a QS, then the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”, are applied once per row in the result of QS with SEQ as SEQUENCE. The result of each evaluation of NVE for a given row is the RESULT returned by the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”. I understand: For each NEXT VALUE FOR that appears in a select list (not a subselect), return the same value per row. b) If NVE is directly contained in a TVC, then the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”, are applied once per contained in TVC. The result of each evaluation of NVE for a given is the RESULT returned by the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”. I understand: In a VALUES clause, each invocation of NEXT VALUE FOR should return the same value. c) If NVE is directly contained in an , then the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”, are applied once per row to be updated by the or . The result of each evaluation of NVE for a given row is the RESULT returned by the General Rules of Subclause 9.21, “Generation of the next value of a sequence generator”. I understand: In an UPDATE statement, all invocations of NEXT VALUE FOR in the SET clause should return the same value per row. Yours, Laurenz Albe
Re: NEXT VALUE FOR sequence
On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe wrote: > Tom Lane wrote: >> Laurenz Albe writes: >> > The SQL standard has the expression "NEXT VALUE FOR asequence" to do >> > what we traditionally do with "nextval('asequence')". >> > This is an attempt to implement this on top of the recently introduced >> > NextValueExpr node. >> >> This has been proposed repeatedly, and rejected repeatedly, because in >> fact the standard's semantics for NEXT VALUE FOR are *not* like nextval(). >> See SQL:2011 4.22.2 "Operations involving sequence generators": >> >> If there are multiple instances of s specifying >> the same sequence generator within a single SQL-statement, all those >> instances return the same value for a given row processed by that >> SQL-statement. >> >> This is not terribly exact --- what is a "processed row" in a join query, >> for instance? But it's certainly not supposed to act like independent >> executions of nextval() or NextValueExpr would. Pending somebody doing >> the legwork to produce something that at least arguably conforms to the >> spec's semantics, we've left the syntax unimplemented. > > Would it be reasonable to say that any two NextValueExpr in the same > target list are "in one row"? I think, "processed row" thing gets pretty complicated. Consider simple case. What happens when NextValueExpr appears in one of the conditions and that row gets eliminated, do we consider that as a processed row and increment the NextValueExpr or do not increment it? -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: NEXT VALUE FOR sequence
Tom Lane wrote: > Laurenz Albe writes: > > The SQL standard has the expression "NEXT VALUE FOR asequence" to do > > what we traditionally do with "nextval('asequence')". > > This is an attempt to implement this on top of the recently introduced > > NextValueExpr node. > > This has been proposed repeatedly, and rejected repeatedly, because in > fact the standard's semantics for NEXT VALUE FOR are *not* like nextval(). > See SQL:2011 4.22.2 "Operations involving sequence generators": > > If there are multiple instances of s specifying > the same sequence generator within a single SQL-statement, all those > instances return the same value for a given row processed by that > SQL-statement. > > This is not terribly exact --- what is a "processed row" in a join query, > for instance? But it's certainly not supposed to act like independent > executions of nextval() or NextValueExpr would. Pending somebody doing > the legwork to produce something that at least arguably conforms to the > spec's semantics, we've left the syntax unimplemented. Would it be reasonable to say that any two NextValueExpr in the same target list are "in one row"? Yours, Laurenz Albe
Re: NEXT VALUE FOR sequence
Laurenz Albe writes: > The SQL standard has the expression "NEXT VALUE FOR asequence" to do > what we traditionally do with "nextval('asequence')". > This is an attempt to implement this on top of the recently introduced > NextValueExpr node. This has been proposed repeatedly, and rejected repeatedly, because in fact the standard's semantics for NEXT VALUE FOR are *not* like nextval(). See SQL:2011 4.22.2 "Operations involving sequence generators": If there are multiple instances of s specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement. This is not terribly exact --- what is a "processed row" in a join query, for instance? But it's certainly not supposed to act like independent executions of nextval() or NextValueExpr would. Pending somebody doing the legwork to produce something that at least arguably conforms to the spec's semantics, we've left the syntax unimplemented. regards, tom lane
Re: NEXT VALUE FOR sequence
Laurenz Albe wrote: > The SQL standard has the expression "NEXT VALUE FOR asequence" to do > what we traditionally do with "nextval('asequence')". The behavior mandated by the standard is that several invocations of NEXT VALUE on the same sequence on the same output row must produce the same value. That is: CREATE SEQUENCE s; SELECT NEXT VALUE FOR s, NEXT VALUE FOR s UNION SELECT NEXT VALUE FOR s, NEXT VALUE FOR s should produce (1,1) (2,2) It makes sense that the value does not depend on the position of the expression as a column. The trouble of course is that the equivalent with nextval() would produce instead (1,2) (3,4) There have been previous discussions on the standard syntax that said that when it will get into postgres, it should go with the standard conforming semantics. I guess it would be a much more difficult patch. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite