Re: updatable cursors and ORDER BY
On 5/9/18 22:57, Tom Lane wrote: > I think you misread that note: it says nothing about what is allowed > in DECLARE CURSOR per se. It is talking about whether you can apply > UPDATE/DELETE WHERE CURRENT OF to that cursor. Moreover, what it says > is that if you use FOR UPDATE then such an UPDATE/DELETE *will* work, > whereas without it we don't guarantee that. I think that last part isn't actually written down anywhere. (It only states the converse.) How about a clarification like this: @@ -271,7 +271,10 @@ Notes and not use grouping or ORDER BY). Cursors that are not simply updatable might work, or might not, depending on plan choice details; so in the worst case, an application might work in testing - and then fail in production. + and then fail in production. If FOR UPDATE is + specified, then the cursor is guaranteed to be updatable, or the + DECLARE command will error if an updatable cursor + cannot be created for the supplied query. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: updatable cursors and ORDER BY
Peter Eisentraut writes: > I think that last part isn't actually written down anywhere. (It only > states the converse.) How about a clarification like this: > @@ -271,7 +271,10 @@ Notes > and not use grouping or ORDER BY). Cursors > that are not simply updatable might work, or might not, depending on > plan > choice details; so in the worst case, an application might work in > testing > - and then fail in production. > + and then fail in production. If FOR UPDATE is > + specified, then the cursor is guaranteed to be updatable, or the > + DECLARE command will error if an updatable cursor > + cannot be created for the supplied query. > OK by me, except we don't usually use "error" as a verb. Either "fail" or "throw an error" would read better IMO. Or you could just stop with "guaranteed to be updatable"; I don't think the rest adds much. regards, tom lane
Re: Mistakes between an example and its description
Unique Constraint and Unique Index should be the same in this single-column case, right? Because, according to CONSTRAINT page in Postgres Documentation, Unique Constraint on single column is realized as Unique B-TREE index. I have just realized that it is still a btree index, but I think this example is not exactly a regular btree example. At least I will put the example as: CREATE INDEX title_idx ON films USING BTREE(title); On Wed, May 9, 2018 at 7:59 PM, Martín Marqués < martin.marq...@2ndquadrant.com> wrote: > El 09/05/18 a las 18:52, PG Doc comments form escribió: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/9.6/static/sql-createindex.html > > Description: > > > > In Postgres Doc: Index > > page(https://www.postgresql.org/docs/9.6/static/sql-createindex.html), > there > > is an mismatch between an example description and an example SQL > statement. > > > > > > The description is below : > > To create a B-tree index on the column title in the table films: > > > > Meanwhile, the example provided is an UNIQUE constrain. > > CREATE UNIQUE INDEX title_idx ON films (title); > > It's not exactly a unique constraint, but it is a unique index, and it > should be mentioned in the title of the documentation. > > -- > Martín Marquéshttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: Mistakes between an example and its description
Eugene Wang wrote: > Unique Constraint and Unique Index should be the same in this single-column > case, right? > > Because, according to CONSTRAINT page in Postgres Documentation, Unique > Constraint on single column is realized as Unique B-TREE index. > > I have just realized that it is still a btree index, but I think this > example is not exactly a regular btree example. At least I will put the > example as: CREATE INDEX title_idx ON films USING BTREE(title); Yeah, I see no point for having UNIQUE in that example. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Mistakes between an example and its description
2018-05-10 16:04 GMT-03:00 Eugene Wang : > Unique Constraint and Unique Index should be the same in this single-column > case, right? The unique index is what enforces the uniqueness of the field with a UNIQUE constraint, but a unique index is not necessarily a constraint. For example, you can use a unique index to create a primary key constraint with the USING clause, but you can't use the UNIQUE index created by a UNIQUE CONSTRAINT for this action. > Because, according to CONSTRAINT page in Postgres Documentation, Unique > Constraint on single column is realized as Unique B-TREE index. > > I have just realized that it is still a btree index, but I think this > example is not exactly a regular btree example. At least I will put the > example as: CREATE INDEX title_idx ON films USING BTREE(title); Yes, I agree on dropping the UNIQUE, just like Alvaro. There's another example which creates a UNIQUE index later in the doc, and not the best choice for a first example. Regards, -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Mistakes between an example and its description
On Thu, May 10, 2018 at 12:16 PM, Martín Marqués wrote: > 2018-05-10 16:04 GMT-03:00 Eugene Wang : > > Unique Constraint and Unique Index should be the same in this > single-column > > case, right? > > The unique index is what enforces the uniqueness of the field with a > UNIQUE constraint, but a unique index is not necessarily a constraint. > Specifically, a constraint cannot have a WHERE clause while the index can (i.e., unique indexes can be partial, unique constraints always cover the entire table). Thinking this over I'd probably remove UNIQUE from both existing examples. I'd then add an example of a partial unique index (especially since we lack an example of a partial index presently) - and note that non-partial unique indexes are better implemented by defining a constraint on the relation as opposed to creating the index explicitly. David J.
