Thread: nextval parameter is not clear

On Tue, Oct 18, 2022 at 08:17:12AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/functions-sequence.html
> Description:
>
> https://www.postgresql.org/docs/14/functions-sequence.html
>
> I don't see here any hints about how to use this function, and what the
> Maybe add some links to other topics or clarify examples for nextval here.

Uh, the last sentence in that section says:

        The sequence to be operated on by a sequence function is specified by a
        regclass argument, which is simply the OID of the sequence in the
        pg_class system catalog. You do not have to look up the OID by hand,
        however, since the regclass data type's input converter will do the work
        for you. See Section 8.19 for details.

On Tue, Nov 22, 2022 at 2:10 PM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Oct 18, 2022 at 08:17:12AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/functions-sequence.html
> Description:
>
> https://www.postgresql.org/docs/14/functions-sequence.html
>
> I don't see here any hints about how to use this function, and what the
> Maybe add some links to other topics or clarify examples for nextval here.

Uh, the last sentence in that section says:

        The sequence to be operated on by a sequence function is specified by a
        regclass argument, which is simply the OID of the sequence in the
        pg_class system catalog. You do not have to look up the OID by hand,
        however, since the regclass data type's input converter will do the work
        for you. See Section 8.19 for details.

As someone who is "newer", I'd like to point out that a "clarifying example" as

simple as SELECT nextval("your_seq"::regclass);    

is about 100 times more CLEAR about the essence than that paragraph.

And, yes, I read the page, and I've seen the examples just one paragraph lower.

A huge majority of us are hyper-visual, and learn by example.  But that example

below does not translate to this example... Unless you already know it!

Regards
PS: I am willing to help create that gigantic page, if we can standardize a way to link to it.

PPS: Even to the point of successive refinement.  Over time, we may add "do this / not this"

On Tue, Nov 22, 2022 at 8:42 PM Kirk Wolak <wolakk@gmail.com> wrote:

On Tue, Nov 22, 2022 at 2:10 PM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Oct 18, 2022 at 08:17:12AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/functions-sequence.html
> Description:
>
> https://www.postgresql.org/docs/14/functions-sequence.html
>
> I don't see here any hints about how to use this function, and what the
> Maybe add some links to other topics or clarify examples for nextval here.

We've only fairly recently made incorporating useful and expansive examples into the reference part of the documentation generally doable.  I agree that this particular one warrants such an example.  If you are willing to do the harder work you describe below, submit a patch for this.  It's specific, small, an improvement, and nothing Bruce said indicates it isn't wanted, he just focused on a different aspect of the complaint.

..

The wiki is an excellent place to prototype and coordinate.  Beyond that, this idea falls outside what should be discussed on this thread.  Here, let's either patch the docs with some examples, or not.

David J.

On Tue, Nov 22, 2022 at 11:43 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Nov 22, 2022 at 8:42 PM Kirk Wolak <wolakk@gmail.com> wrote:

..

The wiki is an excellent place to prototype and coordinate.  Beyond that, this idea falls outside what should be discussed on this thread.  Here, let's either patch the docs with some examples, or not.

David J.

David, as requested.  I took this page, and created examples in each row of each function.

I took a little liberty to normalize the warning about the error message between the last 2 functions (currval()/lastval()).

I also decorated the comments with -- prefix (because I HATE not being able to quickly copy code and paste and use it).

Also, I added a CREATE SEQUENCE in each block, so that the resulting code will work if you cut/paste it in.

[Who knows, eventually we could add CSS that adds the copy  and maybe an Open in PGFiddle someday]

Now I edited this as pure sgml.  I have no idea how to make this into html to view.  Do you guys use specific tooling?

 

Have a look.  Cut it to shreds.  But I believe this is functional and informative.

Regards Kirk

On Wed, Nov 23, 2022 at 11:06 PM Kirk Wolak <wolakk@gmail.com> wrote:

On Tue, Nov 22, 2022 at 11:43 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Nov 22, 2022 at 8:42 PM Kirk Wolak <wolakk@gmail.com> wrote:

Here is the updated version.

Regards Kirk

On Thu, 2022-11-24 at 02:41 -0500, Kirk Wolak wrote:
> Alright, as I have the documentation build working, and a slightly better stylesheet,
> the comments on the last block were not aligned.
> They are fixed now.
>
> Apologies for spamming this in...  My first patch turned into 3 emails... 

That is no problem.

I think the patch is fine.  I like the addition of the "--" comments.
The one think that should be unified is that you include an explicit
cast to "regclass" everywhere, but the pre-existing example does not.

I would omit that cast.  It might confuse beginners (who are probably
satisfied to know that you have to use the sequence name as a string),
and it is explained in the last paragraph of the page anyway.

Yours,
Laurenz Albe

On Thu, Nov 24, 2022 at 5:33 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-11-24 at 02:41 -0500, Kirk Wolak wrote:
> Alright, as I have the documentation build working, and a slightly better stylesheet,
> the comments on the last block were not aligned.
> They are fixed now.
>
> Apologies for spamming this in...  My first patch turned into 3 emails... 

That is no problem.

I think the patch is fine.  I like the addition of the "--" comments.
The one think that should be unified is that you include an explicit
cast to "regclass" everywhere, but the pre-existing example does not.

I would omit that cast.  It might confuse beginners (who are probably
satisfied to know that you have to use the sequence name as a string),
and it is explained in the last paragraph of the page anyway.

Yours,
Laurenz Albe

Laurenz, 

  First, thanks for the support..

  It's funny you mention that (with and without ::regclass).  I went both ways on it.  

The reason I did not address it, was that I liked the variety because it sticks out.  

I thought about adding a comment like 

-- while you can omit  the ::regclass casting, it is preferred

  The fact that it is different engages the brain of the reader whose pattern matching 

just got challenged.  It almost begs them to test it out. 

  I think it might deserve a comment, but I would really prefer to keep it in there.

On Thu, Nov 24, 2022 at 7:33 AM Kirk Wolak <wolakk@gmail.com> wrote:

On Thu, Nov 24, 2022 at 5:33 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-11-24 at 02:41 -0500, Kirk Wolak wrote:
> Alright, as I have the documentation build working, and a slightly better stylesheet,
> the comments on the last block were not aligned.
> They are fixed now.
>...

I dislike having CREATE SEQUENCE in the table examples.  Keep those focused on only the syntax of the call expression.  That requires removing the nextval calls too and not showing the error path.  I'd be fine with not showing examples of code that would produce an error.  (if we are going to show the error path I'd suggest we show the actual error message produced)

At the bottom of the page I would add an extended example of how these sequences can be used in practice.

CREATE TABLE seq_example ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) -- implicit sequence named seq_example_seq is created (or whatever is generated...)

INSERT INTO seq_example RETURNING id; -- 1

SELECT currval('seq_example_seq'::regclass); -- 1

SELECT setval('seq_example_seq', 10);

INSERT INTO seq_example (id)

  VALUES (nextval('seq_example_seq')) -- works as-is due to choosing BY DEFAULT for the identity

  RETURNING id; -- 11

SELECT lastval(); -- 11

I would remove the casting of the sequence name from the table examples and just keep one in the main usage example.  I agree that having both to make the reader stop and think is a good idea.

David J.

On Thu, 2022-11-24 at 15:50 -0500, Kirk Wolak wrote:
>
> David, let me see how that looks.  From an approach standpoint, I am hearing:
> 1) Keep the example near the definition minimal [just this function]
> 2) It's okay to create an extra "row" [each function is in a <row>], right after the last function, with a detailed example, like you put below!

The detailed example should not be another row in the table.  The table is only for
the description of individual functions.  The functions "nextval", "setval", "currval"
and "lastval" each have their own row.

I think the elaborate example should be at the bottom of the page, similar to
the "Examples" section in the reference pages.  See for example
https://www.postgresql.org/docs/current/sql-createstatistics.html

> >
> > CREATE TABLE seq_example ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) -- implicit sequence named seq_example_seq is created (or whatever is generated...)
> > INSERT INTO seq_example RETURNING id; -- 1
> > SELECT currval('seq_example_seq'::regclass); -- 1

I don't think that is a good example to explain sequences to a beginner.
The sequence behind an identity column is an implementation detail, and
I wouldn't burden the reader with all that.

I think it would be better to create the sequence explicitly and use
it in the DEFAULT clause of a column definition.

On Fri, Nov 25, 2022 at 12:40 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-11-24 at 15:50 -0500, Kirk Wolak wrote:
>
> David, let me see how that looks.  From an approach standpoint, I am hearing:
> 1) Keep the example near the definition minimal [just this function]
> 2) It's okay to create an extra "row" [each function is in a <row>], right after the last function, with a detailed example, like you put below!

The detailed example should not be another row in the table.  The table is only for
the description of individual functions.  The functions "nextval", "setval", "currval"
and "lastval" each have their own row.

I think the elaborate example should be at the bottom of the page, similar to
the "Examples" section in the reference pages.  See for example
https://www.postgresql.org/docs/current/sql-createstatistics.html

+1

> >
> > CREATE TABLE seq_example ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) -- implicit sequence named seq_example_seq is created (or whatever is generated...)
> > INSERT INTO seq_example RETURNING id; -- 1
> > SELECT currval('seq_example_seq'::regclass); -- 1

I don't think that is a good example to explain sequences to a beginner.
The sequence behind an identity column is an implementation detail, and
I wouldn't burden the reader with all that.

If you are using these functions it is most commonly in a situation where serial/identity is involved. I don't see much benefit to having the example be sanitized to the point of not reflecting realistic usage.

I think it would be better to create the sequence explicitly and use
it in the DEFAULT clause of a column definition.

I wasn't too happy with that comment when I wrote it either.

I would probably do without the DEFAULT if going the explicit route (but it isn't a deal breaker).

However, I remembered that we have:

pg_get_serial_sequence ( table text, column text ) → text

I'd be inclined to stay with the GENERATED example but incorporate that function call into the other examples.

Regardless of the above choice for the example, it seems appropriate for this page, somewhere, to mention this function and link to its page.

I'd even argue for moving that function definition here.

David J.

On Fri, Nov 25, 2022 at 12:40 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-11-24 at 15:50 -0500, Kirk Wolak wrote:
>
> David, let me see how that looks.  From an approach standpoint, I am hearing:

I think it would be better to create the sequence explicitly and use
it in the DEFAULT clause of a column definition.

I wasn't too happy with that comment when I wrote it either.

I would probably do without the DEFAULT if going the explicit route (but it isn't a deal breaker).

However, I remembered that we have:

pg_get_serial_sequence ( table text, column text ) → text

I'd be inclined to stay with the GENERATED example but incorporate that function call into the other examples.

Regardless of the above choice for the example, it seems appropriate for this page, somewhere, to mention this function and link to its page.

I'd even argue for moving that function definition here.

David J.

On Fri, Nov 25, 2022 at 9:58 AM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Nov 25, 2022 at 12:40 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-11-24 at 15:50 -0500, Kirk Wolak wrote:

Regardless of the above choice for the example, it seems appropriate for this page, somewhere, to mention this function and link to its page.

I'd even argue for moving that function definition here.

David J.

Okay, I've really reworked the example, and it all tests out.

I took the advice of Laurenz about the separate section outside the table.

I did not move the function, it seemed alphabetical where it was (easy enough to move), but I did use that function twice!

Break out the RED ink and let me know what you think!

Kirk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Now I think that is taking it too far.  Your code sample would be great
> for a tutorial, but it is too elaborate for the technical documentation.
> The example should focus on the sequence functions, but more than half
> of the code describes other parts of PostgreSQL:

Yeah, that stuff seems quite out of place here.

> I am alright with having a CREATE TABLE with a DEFAULT and an INSERT or two;
> whatever it takes to show the functions in a realistic scenario.
> For example, you could INSERT a row that overrides the DEFAULT, then call
> "setval()" to readjust the sequence.

I don't believe we have such detail around very many, if indeed any,
of our other functions' documentation.

                        regards, tom lane

On Sat, 2022-11-26 at 23:22 -0500, Kirk Wolak wrote:
> Your feedback would be appreciated.

> +  <para>Example

I think you should use the <title> tag to render that more conspicuous.

I am not saying that this is bad material, but if anything, it should go to
chapter 3 (Advanced Features) of the tutorial (and then it could be even more
elaborate).

Yours,
Laurenz Albe

Kirk Wolak <wolakk@gmail.com> writes:
> my goal is to get an "approach" accepted, this is the first.  Ultimately, I
> would love to see
> the documentation of functions with examples like this everywhere.

I'm not for that.  func.sgml is already massive, and doubling its size 

...
reference material, so extended examples ought to go somewhere else.

I could see adding a section about sequences and identity columns to
chapter 3 in the tutorial.

> Preferably where someone
> can copy/paste the example and see the results.

As far as that goes, there's already an idea that src/tutorial/
should contain SQL scripts that you can step-execute to follow
along with the tutorial docs.  This hasn't been followed through
on very well --- chapter 2 is covered, but chapter 3 mostly not.
No time like the present to make it better, though.  If your
ambition is to make self-contained working examples, making sure
that they execute as a script is good discipline.

                        regards, tom lane

On Mon, Nov 28, 2022 at 12:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Kirk Wolak <wolakk@gmail.com> writes:
> my goal is to get an "approach" accepted, this is the first.  Ultimately, I
> would love to see
> the documentation of functions with examples like this everywhere.

I'm not for that.  func.sgml is already massive, and doubling its size 

...
reference material, so extended examples ought to go somewhere else.

I could see adding a section about sequences and identity columns to
chapter 3 in the tutorial.

> Preferably where someone
> can copy/paste the example and see the results.

As far as that goes, there's already an idea that src/tutorial/
should contain SQL scripts that you can step-execute to follow
along with the tutorial docs.  This hasn't been followed through
on very well --- chapter 2 is covered, but chapter 3 mostly not.
No time like the present to make it better, though.  If your
ambition is to make self-contained working examples, making sure
that they execute as a script is good discipline.

                        regards, tom lane

 

Two questions follow from the above:

1) For the novice who finds this page (funcs), how will they know to find the tutorial?  

2) Should each function link to that? or One link per page? [to the appropriate example section]

I will gladly move my focus to adding to the tutorials... (but I want to know how even I would know it was available if I hit here first)

And I still believe that each function in the documentation section should have at least a one-line example usage???

After a quick review of the tutorial section, if you end up on any random page in the tutorial, 

the example code does not stand alone.

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Now I think that is taking it too far.  Your code sample would be great
> for a tutorial, but it is too elaborate for the technical documentation.
> The example should focus on the sequence functions, but more than half
> of the code describes other parts of PostgreSQL:

Yeah, that stuff seems quite out of place here.

> I am alright with having a CREATE TABLE with a DEFAULT and an INSERT or two;
> whatever it takes to show the functions in a realistic scenario.
> For example, you could INSERT a row that overrides the DEFAULT, then call
> "setval()" to readjust the sequence.

I don't believe we have such detail around very many, if indeed any,
of our other functions' documentation.

                        regards, tom lane

Hello.

I would like to get some opinion on a petite idea I got while reading
the "nextval
parameter is not clear" thread. Because it may not fit well to the
thread itself
I managed to create a new one instead...

...
The very best example of the approach of a book with runable examples
is the
"The Rust Programming Language" [1][2] aka "The Book". Who has not
known about it,
please give it few minutes to see how it works and what a great
experience it is
to read it. :-)

...
I am also not aware if DB Fiddle or another service allows to run
examples covered
in such tutorial book via some API or not. If not, the results could
still be
"pregenerated" and hardcoded at compile time. Maybe, in this case, it
would be even
better.

References:
[1] https://doc.rust-lang.org/book/
[2] https://github.com/rust-lang/book (source code of "the book")
[3] https://rust-lang.github.io/mdBook/format/mdbook.html
--
Matous Jan Fialka

On Tue, Nov 29, 2022 at 3:40 AM Matous Jan Fialka <mjf@mjf.cz> wrote:

Hello.

I would like to get some opinion on a petite idea I got while reading
the "nextval
parameter is not clear" thread. Because it may not fit well to the
thread itself
I managed to create a new one instead...

+1 (right approach, I was clearly wrong, learning...)

...
The very best example of the approach of a book with runable examples
is the
"The Rust Programming Language" [1][2] aka "The Book". Who has not
known about it,
please give it few minutes to see how it works and what a great
experience it is
to read it. :-)

I actually recorded this interaction.  I think this is cool.

And as I think about WHERE this goes, I DEFINITELY believe it goes

in the tutorial world.  I love the "peek" and the "play" icons, on mouse over.

https://app.screencast.com/VddQpV2LYoUdx

for those that want to see it themselves:
https://rust-lang.github.io/mdBook/format/mdbook.html

On Tue, Nov 29, 2022 at 2:42 PM Kirk Wolak <wolakk@gmail.com> wrote:

On Tue, Nov 29, 2022 at 3:40 AM Matous Jan Fialka <mjf@mjf.cz> wrote:

Hello.

I would like to get some opinion on a petite idea I got while reading
the "nextval
parameter is not clear" thread. Because it may not fit well to the
thread itself
I managed to create a new one instead...

+1 (right approach, I was clearly wrong, learning...)

...
The very best example of the approach of a book with runable examples
is the
"The Rust Programming Language" [1][2] aka "The Book". Who has not
known about it,
please give it few minutes to see how it works and what a great
experience it is
to read it. :-)

I actually recorded this interaction.  I think this is cool.

And as I think about WHERE this goes, I DEFINITELY believe it goes

in the tutorial world.  I love the "peek" and the "play" icons, on mouse over.

https://app.screencast.com/VddQpV2LYoUdx

for those that want to see it themselves:
https://rust-lang.github.io/mdBook/format/mdbook.html

Haven't dived into these things yet - but I had the thought that maybe the tutorial section of our documentation is not a good place for this.  Nor, frankly, do I think sgml be a great technology from which to try and leverage this.  Hosting a second "site", and git repository, geared toward this kind of effort, seems like a reasonable thing to do.  It doesn't need, nor want, the same versioning and releases cadence policies as the documentation.

I think the main question is, if that were the intended route, how much material would we consider a minimum viable release (done elsewhere probably) to go to the effort to get the infrastructure in place to host long term - i.e., after the initial novelty wears off and committers have to take on ownership?  Specifically, require that at minimum the tutorial section of the documentation be removed from the documentation and instead the same material, ideally improved, placed in this new location with the new design goals and requirements published.

We'd get rid of any output formats except HTML (no PDF or man pages).  Cross-references would still be desirable and so a plan for those (done as part of the tutorial migration) would be required.  There wouldn't be any "older version support" either IMO, just one main release branch.

IOW, I have my doubts regarding the success of a project to retrofit the tutorial to this extent.  I don't think teaching the old dog these new tricks is going to go well.

I do like the idea of having a place where people who want to produce this kind of stuff, but not write an entire book or tutorial, or try and market their own blog, can find a home to publish.

I'm not too keen on worrying about "auto-execute" - if anything, convincing the reader to physically type any example code into a fiddle, or their own installation, is a better idea - even to the extent of removing copy-paste...

Any automation or tests that would help ensure the published material is valid should be done during "build" using psql.

David J.

On Tue, 2022-11-29 at 16:26 -0500, Kirk Wolak wrote:
> On Mon, Nov 28, 2022 at 12:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > > Now I think that is taking it too far.  Your code sample would be great
> > > for a tutorial, but it is too elaborate for the technical documentation.
> > > The example should focus on the sequence functions, but more than half
> > > of the code describes other parts of PostgreSQL:
> >
> > Yeah, that stuff seems quite out of place here.
> >
> > > I am alright with having a CREATE TABLE with a DEFAULT and an INSERT or two;
> > > whatever it takes to show the functions in a realistic scenario.
> > > For example, you could INSERT a row that overrides the DEFAULT, then call
> > > "setval()" to readjust the sequence.
> >
> > I don't believe we have such detail around very many, if indeed any,
> > of our other functions' documentation.
> >
> >                         regards, tom lane
>
>
> All changes specified have been addressed.
> The Example is significantly reduced, but readable.
> The extra "SELECT "'s have been removed off of the inline examples, excluding the existing paragraph.
>
> This is the smallest possible change, that still has an example.
>
> The "Example" is not <title> as every attempt to make it such fails the LINT process.

You can have <title> only after the start of a section.

The new examples in the table don't really add anything to the function declaration...

I realized that there already are some examples in the CREATE SEQUENCE documentation,
so what about linking there instead of writing more examples?

The attached patch does that and removes the less useful examples.  What do you think?

Yours,
Laurenz Albe

On Thu, Dec 1, 2022 at 4:21 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2022-11-29 at 16:26 -0500, Kirk Wolak wrote:
> On Mon, Nov 28, 2022 at 12:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > > Now I think that is taking it too far.  Your code sample would be great
> > > for a tutorial, but it is too elaborate for the technical documentation.
> > > The example should focus on the sequence functions, but more than half
> > > of the code describes other parts of PostgreSQL:
> >
> > Yeah, that stuff seems quite out of place here.
> >
> > > I am alright with having a CREATE TABLE with a DEFAULT and an INSERT or two;
> > > whatever it takes to show the functions in a realistic scenario.
> > > For example, you could INSERT a row that overrides the DEFAULT, then call
> > > "setval()" to readjust the sequence.
> >
> > I don't believe we have such detail around very many, if indeed any,
> > of our other functions' documentation.
> >
> >                         regards, tom lane
>
>
> All changes specified have been addressed.
> The Example is significantly reduced, but readable.
> The extra "SELECT "'s have been removed off of the inline examples, excluding the existing paragraph.
>
> This is the smallest possible change, that still has an example.
>
> The "Example" is not <title> as every attempt to make it such fails the LINT process.

You can have <title> only after the start of a section.

The new examples in the table don't really add anything to the function declaration...

I realized that there already are some examples in the CREATE SEQUENCE documentation,
so what about linking there instead of writing more examples?

The attached patch does that and removes the less useful examples.  What do you think?

Yours,
Laurenz Albe

+1