Re: 8.5.2 "integral" - "integer"
On Saturday, January 7, 2023, Tom Lane wrote: > PG Doc comments form writes: > > I think, the "integer" will be good choice instead "integral'": > > 8.5.2 > > ... > > "The offset will be shown as hh (hours only) if it is an integral number > of > > hours, else as hh:mm if it is an integral number of minutes, else as > > hh:mm:ss. (The third case is not possible with any modern" > > "Integral" seems like perfectly good English to me here. > Even if technically correct I’d probably go with integer in deference to non-native speakers and the fact that integral has two other much more commonly used meanings than as a synonym for integer. David J.
Re: https://www.postgresql.org/account/comments/new/15/sql-alterdatabase.html/
On Tue, Jan 24, 2023 at 12:43 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-alterdatabase.html > Description: > > Would love to see more information about each of the ALTER DATABASE > parameters... a specific... "connlimit"... -1 means no connections, but > what > does 0 mean? Are there users that connect even though the connection limit > is exceeded. Is there special allowances for other database connections, ie > backups? > I would expect superuser roles to ignore all of those options. Everyone else, not so much. connlimit=0 should have the same effect as allowcon=false There is some enhancement at the margins here but unless you are trying to do unusual stuff it should be working as one would intuitively expect. David J.
Re: gen_random_uuid is only available with pgcrypto enabled
On Tue, Jan 24, 2023 at 12:43 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/functions-uuid.html > Description: > > Regarding the documentation on this page: > https://www.postgresql.org/docs/15/functions-uuid.html > > It think the documentation should state clearly, that postgres natively > does > not have any UUID generation functions. But that isn't true... postgres=# select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---+-+--+--+++---+-- 12765 | plpgsql | 10 | 11 | f | 1.0| | (1 row) postgres=# select gen_random_uuid(); gen_random_uuid -- 0a1be850-b1ae-48b9-bfe1-038df1e227f7 (1 row) David J.
Re: Naming of network_ops vs. inet_ops for SP-GIST
Michael, please see below: On Tue, Jan 24, 2023 at 12:44 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html > Description: > > I wanted to add an SP-GIST index for an inet field ip_address > > In https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html > network_ops is stated as the built-in opclass for (inet, inet) > [...] > I notice inet_ops, not network_ops, is mentioned in the docs for 13. Perhaps > it was renamed to network_ops in 15 but not 14? > I'm fairly certain this is a simple typo while performing some refactoring work here: https://github.com/postgres/postgres/commit/7a1cd5260aa20bc13aec8960a57904b5623d1830 doc/src/sgml/spgist.sgml L105 + network_ops L185 - inet_ops David J.
Re: unclear syntax explanation for multiple window definitions in a SELECT query
On Thu, Feb 16, 2023 at 5:13 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-select.html > Description: > > The syntax documentation for SELECT in SQL COMMANDS section is unclear in > explaining how to use multiple window definitions. It's easy to read it > and > believe that multiple WINDOW keywords, one for each definition, are > correct, > which is not the case. > > See this stackoverflow question and answer on point: > > https://stackoverflow.com/questions/59651529/multiple-named-windows-in-a-postgres-query > > This is how we've chosen to document this kind of grammar and I don't see a compelling reason to revisit that decision on the basis of this report. I could consider some additional wording regarding "each clause keyword only ever appears once in a given (sub)query" though that seems almost self-evident as part of the learning of SQL. The fact that you write WINDOW and then a comma-separated listing of names plus definitions is how that syntax diagram is read, just like with WITH, FROM, and GROUP BY clauses in the same diagram. David J.
Re: unclear syntax explanation for multiple window definitions in a SELECT query
On Thu, Feb 16, 2023 at 10:18 AM Adam Mackler wrote: > > Maybe the reference section for SELECT is the wrong place for that longer > form, but section 4.2.8 (or some other referenced from there) could be. > > Expanding Section 7.2.5 to be more consistent in level-of-detail with the rest of that page (syntax and examples in particular) seems like an easy sell. https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WINDOW I'm not convinced that modifying the SELECT page to make all of the relevant [, ...] items [, blah-blah-blah ] instead is going to be a net improvement. David J.
Re: MERGE examples not clear
On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/sql-merge.html
> Description:
>
> On this page: https://www.postgresql.org/docs/15/sql-merge.html
> the first and second examples seems to be contrasted (by "this would be
> exactly equivalent to the following statement"), however the difference
> does
> not seem to related to the stated reason ("the MATCHED result does not
> change"). It seems like the difference should involve the order of WHEN
> clauses?
> Of course, it might be that I don't understand the point, in which case
> maybe the point could be stated more clearly?
>
Yeah, that is a pretty poor pair of examples. Given that a given customer
can reasonably be assumed to have more than one recent transaction the
MERGE has a good chance of failing.
The only difference between the two is the second one uses an explicit
subquery as the source while the first simply names a table. If the
subquery had a GROUP BY customer_id that would be a good change explaining
that the second query is different because it is resilient in the face of
duplicate customer recent transactions.
While here...source_alias (...completely hides...the fact that a query was
issued). What? Probably it should read (not verified) that it is actually
required when the source is a query (maybe tweaking the syntax to match).
David J.
Re: Mistake in statement example
On Wed, Mar 1, 2023 at 9:34 AM Tom Lane wrote: > PG Doc comments form writes: > > I believe there is a mistake in an example on > > https://www.postgresql.org/docs/current/transaction-iso.html section > > 13.2.1: > > BEGIN; > > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; > > COMMIT; > > > The acctnum is expected to be 12345 in both cases. > > No, I think that's intentional: the example depicts transferring > $100 from account 7534 to account 12345. > > That may be, but the descriptive text and point of the example (which isn't atomicity, but concurrency) doesn't even require the second update command to be present. What the example could use is a more traditional two-session depiction of the commands instead of having a single transaction and letting the user envision the correct concurrency. Something like: S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100 S1: BEGIN; S2: BEGIN; S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //200 S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //WAITING ON S1 S1: COMMIT; S2: UPDATED; balance = 300 S2: COMMIT; Though maybe "balance" isn't a good example domain, the incrementing example used just after this one seems more appropriate along with the added benefit of consistency. David J.
Re: Seeming contradiction in 22.1
On Monday, March 20, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/database-roles.html > Description: > > In chapter 22.1 the following text seems to imply the role will have "the > same name as the user that initialised" it and also the "role will be named > postgres". Which cannot both be true, or perhaps the text is trying to say > something else, but in doing so is not clear. > > "This role is always a “superuser”, and by default (unless altered when > running initdb) it will have the same name as the operating system user > that > initialized the database cluster. Customarily, this role will be named > postgres. " > The words “default” and “unless altered” seem quite clear to me. In particular, the “customarily” part is accomplished usually by creating an operating system user named postgres and then running initdb as that user. David J.
Re: Seeming contradiction in 22.1
On Wed, Mar 22, 2023 at 3:41 PM maja zaloznik wrote: > My issue is that - at least as far as i can recall - the default superuser > was already named postgres > There is no compiled-in default for the name of the bootstrap superuser. It is chosen during initdb using the algorithm described (i.e., o/s user running initdb or the name specified on the CLI) David J.
Seeming contradiction in 22.1
On Thursday, March 23, 2023, maja zaloznik wrote: > > My point being that if what you say is true but runs counter to what > essentially all other instructions on the internet say, it would seem > prudent to mention this disparity and the reasons for it in the canonical > documentation, which would be especially valuable for noobs like me. > The root issue, IMO, is that all packagers have settled on the convention of using Postgres for the superuser name, so if one only ever sees that, it is easy to assume it is some kind of default of the software itself. In the face of consistent evidence, people not reading the documentation is my supposed assumption for their ignorance of this point. But reading the docs, it is clear how the system really works (I.e., the name of the bootstrap superuser comes is derived at initdb time from the environment executing initdb). David J.
Re: Confusion between psql options and pg_service file config
On Tue, Mar 28, 2023 at 1:30 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/libpq-connect.html > Description: > I feel like I'm going in circles trying to square `psql` options (15: psql) > with connection service file parameters (15: 34.17). I've lost exactly > which page I started on, but it was something like this... > > - I start at https://www.postgresql.org/docs/15/app-psql.html > - Under "Options" > -c _command_ shows that I can run an arbitrary command on connection. > Great! > > - But I'd like to specify something similar in the pg_service file, so I > look up I look up the Connection Service File, section 34.17 - > https://www.postgresql.org/docs/15/libpq-pgservice.html That isn't going to work. You use the startup script, PSQLRC, if you want to perform some actions after the connection is established. > > - So far, so good. The "parameters are connection parameters; see > [Section > 34.1.2]( > https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-PARAMKEYWORDS) > for a list." > - Following that link, I scroll down to the key word "options" which > "Specifies command line options" presumably like those for psql. This is an incorrect assumption. The command line options are precisely those that the server recognizes since the point of this feature is to configure the initially established session. In effect it is an alternative to the "SET" SQL Command. > What I have found particularly confusing is some of the keywords for the > pg_service file clearly duplicate the options for `psql` but some of them > are equivalent, but not identical keywords (eg, `psql --username` vs > pg_service.conf keyword 'user'). Are both valid, or are they restricted to > their respective realm? (That's a question I'm asking the documentation to > answer, not a question I'm directly asking you.) > You should assume that options described within a specific context are only valid (or the only valid spelling) in said context. > > There is also no particularly clear connection between psql options and the > connection service file; it's even hard to find where the app-psql.html > page > indicates that using a service name in conjunction with a connection > service > file can make invoking psql so much easier :- ) > > Additionally, since the concepts are so similar to each other, I suppose, but the pg_service.conf file is a fairly small subset of the things that psql has to deal with. For the overlapping subset I do agree there is great value in using the options defined in the pg_service.conf file and then tell psql to use the named service instead of environment variables or command line options. But psql can do so much more, none of which is related to establishing the initial connection to the database. In short, "-c" has nothing to do with making an initial connection to the database. "-o" is a bit of a hack, and if you use a connection pooler (at least pg_bouncer) one that isn't actually supported. David J.
Re: I think that the transaction tutorial document (3.4) should mention transaction isolation
On Wed, Mar 29, 2023 at 12:56 PM Bruce Momjian wrote: > On Thu, Mar 2, 2023 at 01:55:31PM +, PG Doc comments form wrote: > > > I think that a notice saying that for absolute transaction isolation you > > should set the default transaction isolation to serializable, and be > > prepared to have transactions fail, and referring to the more details > > document, would help users prevent consistency bugs. > > Well, the point is that the application is _not_ retrieving a value and > setting is +100, but rather having the SQL add 100, which is kind of the > point. I don't think a tutorial should get into more details than that. > I'm on the fence, but leaning toward agreeing in principle. The following paragraph from that page does cover the dynamics reasonably well under the default configuration and the tutorial should assume the reader hasn't changed the default. """ So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously. """ That said, the tutorial lacks any forward reference to non-tutorial information for the reader that wants to expand their knowledge in this area. A paragraph mentioning isolation levels, including stating the default that the tutorial was operating under, and where that and the other modes are defined/explained, should be added to the end of that page. David J.
Re: DROP INDEX - dropping index of a table in a named schema
On Monday, May 22, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-dropindex.html > Description: > > I had to find out (as it is not in the given documentation) that to drop an > index for a table in a named schema (later "schemaname.tablename") you have > to prefix the schemaname for the index inte DROP statement. > The create index docs say that an index for a table always exists within the same schema as that table. If that schema isn’t in your search_path then it follows you need to schema qualify references to it. David J.
Re: Typo
On Tue, May 23, 2023 at 3:32 PM Michael Paquier wrote: > On Tue, May 23, 2023 at 08:52:25PM +, PG Doc comments form wrote: > > There appears to be a typo, here: > > > https://www.postgresql.org/docs/current/history.html#:~:text=Postgres95%20code%20was%20completely%20ANSI%20C > . > > A word or two should be added between 'completely' and 'ANSI C', such as > > 're-written in', or 're-coded using', or some such. > > This is the current sentence, and it sounds kind of OK to me, FWIW: > "Postgres95 code was completely ANSI C and trimmed in size by 25%. > > I agree with the OP, that is missing something. Maybe: Between the 4.2 release and the release of Postgres95 the code was made to completely adhere to ANSI C and the size was reduced by 25%. David J.
Re: Typo
On Tuesday, May 23, 2023, Tom Lane wrote: > Laurenz Albe writes: > > On Wed, 2023-05-24 at 07:32 +0900, Michael Paquier wrote: > >> This is the current sentence, and it sounds kind of OK to me, FWIW: > >> "Postgres95 code was completely ANSI C and trimmed in size by 25%. > > > That uses "ANSI C" as an adjective, which I think is sloppy wording > > (even though English is somewhat relaxed about the distinction between > > classes of words). > > Yeah, it's not great English, but it's not awful English either; > just a rather telegraphic (abbreviated) style. > > Here's the thing: at this point, this documentation is itself a > historical artifact. git excavation dates the current wording to > 8baa8fcf4 of 1999-06-21, and that was just a small adjustment of > c8cfb0cea of 1998-03-01, and it seems likely that that was pulled > verbatim from some older source. > > So I'm disinclined to change it on grounds of "I think the grammar > is a bit shaky". It is what it is. > > > Agreed. Besides, after a couple of more passes it grew on me, once I filled in the missing “compared to what” sufficiently. David J.
Re: bpchar datatype is not equal to character(1) data type
On Tue, Jun 6, 2023 at 7:31 AM yanliang lei wrote: > > Based on the above description, there is a deduction as follows: > bpchar datatype is equal to character(1) data type > Nope, bpchar is not equal to any user-facing data type by virtue of it being an internal implementation-only data type. Based upon your example I'd say it is basically a length-unconstrained character equivalent and there is no such user-facing data type defined. David J.
Re: Sequence Dependency
On Saturday, June 10, 2023, Umut TEKİN wrote: > > it does not create any pg_depend entry for this sequence and table pair. So, > it is not possible to track down to find the pairs. Is there any other way to > find the sequence and the table pairs created using method 2? > > Parse the defaults and check for matching sequence names? You can alter a manually created sequence to be owned by a table and thus get the dependency added even in the second case. David J.
Re: Further clarification in documentation: No deletion of unreferenced large objects
On Mon, Jun 12, 2023 at 8:32 AM PG Doc comments form wrote: You may be onto something, but: > > Page: https://www.postgresql.org/docs/15/datatype-binary.html This page isn't relevant to the discussion at hand as it doesn't have anything to do with large objects. Whether it should would be a different complaint. > When deleting a row that references (contains) a large object, I think that > most users expect the DBMS to take care of the, now unreferenced, BLOB. On what grounds? To me this looks just like any other foreign key situation and removing FK rows does not impact the PK. What would lead one to think large objects behave differently? David J.
Re: group by can use alias from select list
On Tue, Jul 11, 2023 at 7:19 AM Laurenz Albe wrote: > On Fri, 2023-07-07 at 07:36 +, PG Doc comments form wrote: > > i have a discussion in pgsql-b...@lists.postgresql.org about this and > it is > > mentioned in the answers that it is documented and a "feature" of > Postgres > > that this can be done. > > If this is wanted the documentation > > (https://www.postgresql.org/docs/current/sql-select.html) that > mentioned: > > [execution order of SELECT that says that GROUP BY is before SELECT] > > isn't correct because how can 4.) be done and the alias from 5.) is > used? > > Here is a hint important that there is an exception for alias used in > group > > by > > I think that is already documented: > > I think the complaint is that someone seeing the behavior in the wild comes to this order-of-operations and doesn't see that the observed behavior is documented. Sure, they can go into the GROUP BY section and figure out that there is a "oh, by the way" comment within there that output columns/aliases are indeed allowed. But I tend to agree that a mention in the "order of operations" section that output columns from step 5 can be seen in step 4 seems like an improvement if we really want the order of operations to be the main reference entry point for people trying to work out query behavior. Something like: "(while unadvised it is possible for the aliases defined in the next step to be used here as well)". David J.
Re: Confusing reference to MERGE use inside WITH
On Sun, Jul 16, 2023 at 5:48 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/queries-with.html > Description: > > Hi. > > In the queries WITH documentation > https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing > that MERGE is referenced in several places as being something you can use > inside with, even though it can't. > > If you search for MERGE on the page, you will find the 2 first results is a > reference saying MERGE can be used inside WITH, the same as SELECT, INSERT, > UPDATE, or DELETE. No, they are not, see below. The 3rd result then correctly states that MERGE cannot be > used inside WITH. > > Yes. The first non-intro section says that the following syntax is allowed (you can attach WITH /TO/ MERGE) [ WITH with_query [, ...] ] MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ] https://www.postgresql.org/docs/current/sql-merge.html The second section is saying that the "with_query" above cannot contain MERGE, i.e., you cannot put MERGE /IN/ WITH Namely because you cannot attach a RETURNING clause to MERGE. The intro makes it quite clear as well: Specifically, reading the last sentence makes it clear: Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE. Here the inside is called the auxiliary while the outside is called primary. David J.
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson wrote:
> The actual results (shown below) are different than shown on this doc
> page. The reason is because the second parameter to the UDF that is
> passed to SPI_exec is the maximum number of rows to return, or 0 for
> no limit. It is not the maximum number of rows to process. In the case
> of "SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1)", it returned
> 0 rows, but it inserted (processed) 2 rows. This example should be
> corrected.
>
>
> db=# SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
> execq
> ---
> 2
> (1 row)
>
>
SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you
passed and so 2 is the output of the execq function call.
No INFO messages appear because you did not include a returning clause.
The 1 you passed to the call is immaterial if the query you supply doesn't
produce a result set.
David J.
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 6:22 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson > wrote: > >> The actual results (shown below) are different than shown on this doc > >> page. > > > SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you > > passed and so 2 is the output of the execq function call. > > No INFO messages appear because you did not include a returning clause. > > The 1 you passed to the call is immaterial if the query you supply > doesn't > > produce a result set. > > I think his point is that this example does not behave as the > documentation claims. Which it does not, according to my > tests here. I find this a bit disturbing --- did we intentionally > change the behavior of SPI_exec somewhere along the line? > > Appears to be a documentation fix oversight back in v9.0 https://github.com/postgres/postgres/commit/2ddc600f8f0252a0864e85d5cc1eeb3b9687d7e9 We fixed the wording for the API argument but not the example that demonstrated it. David J.
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 7:45 PM Curt Kolovson wrote:
> I’d vote for showing both (with RETURNING and without), since without it
> the second argument to SPI_exec has no effect in this example, which may
> not be obvious. That seems to be one of the subtle points illustrated by
> this example.
>
I concur:
=> SELECT execq('CREATE TABLE a (x integer)', 0); // start at 0
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0)); // insert 2
=> SELECT execq('SELECT * FROM a', 0);
+ => SELECT execq('INSERT INTO a SELECT x + 2 FROM a returning x', 1); --
one more
+ -- (IIUC non-deterministically regardless of observed behavior even if
there were a limit in the SELECT)
=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1); // three more
=> SELECT execq('SELECT * FROM a', 10); // 6 as 10 is a limit
...[next block]
David J.
Re: Uniquness of ctid
On Wed, Jul 19, 2023 at 2:31 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/ddl-system-columns.html > Description: > > Hi, > Looking at the explanation abour ctid, it is "The physical location of the > row version within its table. " > From that line, I think ctid is unique in the table. > Unique but not stable - if you give your actual record an ID value the associated ctid for it may very well change over time and a given ctid can be associated with any number of IDs > And I also think ctid might be unique across the database since it is the > physical location. > The concept doesn't even apply - the value itself only makes sense within a given physical table. i.e., the table is implied. It's like saying "I live at 123 Main St." to someone. Sure in any given place there can only be a single 123 Main St. but that really isn't useful by itself. And to extend back to the previous point, you may live there now but you will likely have a different address in the future and someone else will have 123 Main St. David J.
Re: word incorrectly on document
On Tue, Aug 1, 2023 at 1:39 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/executor.html > Description: > > Document location: 52.6. Executor > fed up -> feed up > My instinct says the existing wording is correct. FWIW, ChatAI[1] agrees, and explicitly disagrees with "feed". I suspect the use of passive voice in the present tense is confusing things. "The executor feeds the data to the ModifyTable node", but its "the ModifyTable node is fed by the executor". See the explanation below which matches my experience here. David J. [1] The sentence "The meat is feed into the machine" is not valid English in terms of spelling. The correct form of the verb in this context is "fed," not "feed." The correct sentence is: "The meat is fed into the machine." The verb "fed" is the past participle of the verb "feed," which is used in passive constructions to indicate that something is being given or supplied to an object or subject. In this case, the meat is the object, and it is receiving the action of being fed into the machine.
Re: ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error
On Sun, Aug 20, 2023 at 8:56 AM PG Doc comments form wrote: > When the folloing SQL is executed: > ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error > then an error is returned: > Yes, because if you take the RESTART branch in the syntax there is no word SET preceding RESTART. Please fix the documentation or the functionality. > The documentation is already correct. David J.
Re: In docs there is no "Installation from Binaries" section
On Sun, Aug 20, 2023 at 8:56 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/install-binaries.html > Description: > > Installing software from ZIP archive is a common practice, pgsql provides > such archive, but doesn't provide instructions on what to do with it. Where are you seeing such a zip archive? I'm familiar with us producing tar gzip archives of the source code for compiling, but we don't produce binaries that I know of. https://www.postgresql.org/docs/current/install-getsource.html David J.
Re: Create Role Documentation - Not Clear for new users
On Monday, August 21, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/database-roles.html > Description: > > Hi! Hope you are doing great! > > I'm trying to learn about PostgreSQL, I'm totally new, and I think that > for > people like me, the documentation is not clear, for example, you are > stating > that we need to run the ` CREATE ROLE name; `, but you are not specifying > if > that is directly on the terminal? > > In my case, I'm trying to run that on my terminal and I'm getting: > `CREATE: not found`, > > I think would be great if that part could be improved! > > I'm reading that in chapter 22 because in chapter 1.4 I'm being referenced > to chapter 22 > Thanks for letting me share :D my thoughts! > All SQL is to be run within the psql program or whatever client you choose to execute SQL from. Pointing that out everywhere would be exceedingly tedious. David J.
Re: General Feedback
On Mon, Aug 28, 2023 at 7:33 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-createschema.html > Description: > > "To create a schema, the invoking user must have the CREATE privilege for > the current database. (Of course, superusers bypass this check.)" - maybe > you can show how to check if the user has the CREATE privilege? Or is this > omitted on purpose so readers will take their own initiative to find that > out themselves :-) > In the interest of brevity it is assumed the reader is familiar with the definitional chapter regarding privileges and can use the knowledge gained there to perform such database inspection. https://www.postgresql.org/docs/current/ddl-priv.html David J.
Ambiguous statement about order
On Thursday, September 14, 2023, PG Doc comments form < nore...@postgresql.org> wrote: > > I observed that arrays inside JSONB preserved order of insertion. An > explicit line about whether the array order will be preserved or not would > be helpful. We don’t take on responsibility for teaching people what json is. Json arrays are slotted composite elements where the slots are numerically indexed. That, by definition, makes their contents “ordered”. The fact that object keys are unordered and that arrays can be object keys is immaterial to whether the contents of arrays are ordered. David J.
Re: pg_upgrade doc uses inconsistent versions within the doc.
On Tue, Sep 26, 2023 at 10:35 AM Bruce Momjian wrote: > On Mon, Sep 18, 2023 at 08:14:04PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/pgupgrade.html > > Description: > > > > I feel like for readability the pg_upgrade doc should use the same > > old_version and new_version examples when showing examples throughout the > > doc page. > > > > as an exmaple I'm looking at : > > https://www.postgresql.org/docs/current/pgupgrade.html > > > > in some places the example commands use v9.6 and v16. (see step 8) > > > > but in other places it mixes versions v9.5 and v9.6 (see step 11 sub > step 8) > > Good point. I came up with the attached patch. It uses 9.6 and current > I would get rid of any mentions of our old pre-v10 versioning scheme in the current documentation. David J.
Re: `pg_restore --if-exists` clarification
On Thursday, September 28, 2023, Gulyás Attila wrote: > So `--if-exists` simply suppresses any notice / warning that would occur? > That switch causes the drop DDL produced by pg_restore to include if exists clauses. The server then simply treats the DDL as a no-op if said object being dropped cannot be found. > >> But "failure" is not what happens. If you read the part regarding >> --clean, you will see that it says: >> >> (Unless --if-exists is used, this might generate some *harmless error >> messages*, if any objects were not present in the destination database.) >> *[emphasis >> added]* >> with extra emphasis on the word "harmless" -- no failure is caused; the >> restore proceeds just fine. >> If anything were to change in this regard, it might be better to >> reconsider what we call the message (i.e.what language pg_restore emits in >> this scenario.) It's true, I suppose, in a literal sense that it's an >> error in that pg_restore couldn't drop a table when instructed to, where no >> such table exists. But pragmatically it doesn't matter, so why not >> reclassify this as a "warning" or a "notice"? >> > Because pg_restore is just a client and it is repeating back what the server tells it. And for the server it is an error to drop an object that doesn’t exist. psql and pg_restore, as clients, can choose to ignore the errors they see, regardless of what kind of error it is, but they don’t take on the added burden of trying to reclassify errors into something else. David J.
Re: Not clear spelling about transaction
On Sat, Sep 30, 2023 at 1:07 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/continuous-archiving.html > Description: > > >No committed transactions will be lost > > Hello. It is not clear to me. Did you mean: > Not committed transactions will be lost > Or > None of committed transactions will be lost > ? > > It would be simpler if you write that explicitly: > All committed transactions will not be lost. > "Committed transactions will not be lost." The word "No" means "none", it doesn't mean "not". David J.
Re: missing GRANTED BY explanations
On Sat, Sep 30, 2023 at 1:56 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/sql-revoke.html > Description: > > Hi, > In the documentation for revoke command, there is no description for > GRANTED > BY clause. > please explain that what will happen when using this clause in revoke > command. > > Grants specifications include the role doing the granting. So when you remove a grant you have to also specify which role the grant came from. That clause lets you specify said role explicitly. David J.
Re: missing GRANTED BY explanations
On Saturday, September 30, 2023, PG Doc comments form < nore...@postgresql.org> wrote: > > > In the documentation for revoke command, there is no description for > GRANTED > BY clause. > > As the doc says read the grant page for more detailed information. Repeating everything here was deemed redundant. David J.
Re: Corresponding documentation page does not mention about `spread` mode
On Tue, Oct 3, 2023 at 2:48 PM PG Doc comments form
wrote:
>
> On [this
> page](https://www.postgresql.org/docs/current/app-pgbasebackup.html)
> `spread` is mentioned:
>
> -c {fast|spread}
> --checkpoint={fast|spread}
>
> Sets checkpoint mode to fast (immediate) or spread (the default) (see
> Section 26.3.3).
>
> But mentioned section 26.3.3 does not describe `spread` mode:
>
> https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
>
>
It does, though it is phrased in terms of "fast => false" meaning "spread"
whereas "fast => true" means "immediate".
But since the CLI wanted a label instead of true/false it had to call the
default "not fast" mode something and it chose "spread".
Not saying this couldn't be made more clear/better but it is accurate and
complete.
David J.
Re: This is too implicit that recovery.signal will be removed
On Thu, Oct 5, 2023 at 8:15 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/runtime-config-wal.html > Description: > > Hello. > On the page > https://www.postgresql.org/docs/current/runtime-config-wal.html > Actual: > >To start the server in targeted recovery mode, create a file called > recovery.signal in the data directory. If both standby.signal and > recovery.signal files are created, standby mode takes precedence. Targeted > recovery mode ends when the archived WAL is fully replayed, or when > recovery_target is reached. In this mode, the parameters from both this > section and Section 20.5.6 will be used. > > Expected: > >To start the server in targeted recovery mode, create a file called > recovery.signal in the data directory. If both standby.signal and > recovery.signal files are created, standby mode takes precedence. Targeted > recovery mode ends and recovery.signal file is removed when the archived > WAL > is fully replayed, or when recovery_target is reached. In this mode, the > parameters from both this section and Section 20.5.6 will be used. File > 'recovery.signal' is not removed when `recovery_target_action` is > *shutdown*. > > Except that doesn't seem to be true since "pause" is the default action. We probably should add instead: Targeted recovery mode ends when the archived WAL is fully replayed, or when recovery_target is reached. The server is left in the state corresponding to the recovery_target_action setting. David J.
Re: Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html
On Wed, Oct 4, 2023 at 6:37 PM Erik Wienhold wrote: > On 2023-10-04 16:24 +0200, PG Doc comments form write: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/queries-order.html > > Description: > > > > The document only says this about unsorted queries: > > > > > After a query has produced an output table (after the select list has > been > > > processed) it can optionally be sorted. If sorting is not chosen, the > rows > > > will be returned in an unspecified order. The actual order in that > case will > > > depend on the scan and join plan types and the order on disk, but it > must > > > not be relied on. A particular output ordering can only be guaranteed > if the > > > sort step is explicitly chosen. > > > > It mentions "If sorting is not chosen". This sort of implies that if you > > pick a sort the output order is predictable. However I believe that the > only > > actual guarantee is if the sort columns selected produce a unique value. > > > > For example if you do `ORDER BY name` and have two rows with the same > name I > > don't think the order of those rows is predictable. > > "The relative ordering of two rows that are not distinct with respect to > the is implementation-dependent." > The OP is assuming a promise of a deterministic ordering of all output rows and such a promise is only possible if the order by clause columns uniquely identify every row in the output. This is because all the order by promises is that output ordering will conform to the order by specification, and indeed if it is under-specified such that multiple rows match a given bin, then there is no deterministic relative ordering among those rows. I don't feel that the wording makes any such inference regarding determinism of row output due to the mere presence of an order by clause. Nor doesn't such determinism in the face of an under-specific clause even make logical sense. I'm mostly inclined to leave the wording alone given this single report. My only complaints are style-istic at this point. That said, maybe a final sentence: Assuming every output row can be uniquely identified by some subset of the output columns, that subset must all be listed within the order by clause if you wish to ensure a fully deterministic ordering. David J.
Re: It is not clear from documentation when and how I should restore base backup
On Thu, Oct 5, 2023, 11:11 PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/continuous-archiving.html > Description: > > Hello. > > On page: > > https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY > Is is not clear should I do additional actions to restore basebackup first > and then WAL files (as described at p6) > You mean step 4? > This paragraph even more confusing: > > https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-STANDALONE > > ... and no special action is required to restore the backup. > Should I configure restore_command at least? > It is standalone, everything needed and possible exists in the backup file pg_basebackup created. David J.
Re: Typo in PL/pgSQL trigger Example 43.4?
On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker wrote: > > INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= > ARGUMENT IN QUESTION > The emp_audit table has a column named 'userid', which in actual usage > (next-to-last line quoted) is populated by 'user' which seems undefined in > the context. Was that intended to be 'current_user', or am I missing > something? > user is a valid pseudo-function: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION David J.
Re: 31.7.1. Initial Snapshot
On Wed, Oct 11, 2023 at 9:32 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: > https://www.postgresql.org/docs/16/logical-replication-architecture.html > Description: > > There are dublicated section named "31.7.1. Initial Snapshot" on > https://www.postgresql.org/docs/16/logical-replication-architecture.html What you are seeing is the first instance of 31.7.1 is a table of contents entry (hyperlink too) for the section. Then you have the chapter introductory material. Then you have section 31.7.1 itself. David J.
Re: Wrong article SET AUTOCOMMIT
On Wed, Oct 11, 2023 at 9:34 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/ecpg-sql-set-autocommit.html > Description: > > This article should be removed as this parameter does not exist since a > long > time version 9.4 > I suspect you are confusing the server setting for autocommit with client-side auto-commit. The server no longer implements auto-commit declaratively but almost every client out there does. In this case ECPG is a client environment and it indeed does implement auto-commit. David J.
Re: 31.7.1. Initial Snapshot
On Wed, Oct 11, 2023 at 9:41 AM Alvaro Herrera wrote: > Maybe a fix for this would be to style chapter TOCs in some way that > makes it clear that they are TOCs -- for example, add a (subtly) visible > bounding box, or something. Or maybe if a chapter has a single section, > just do not print the TOC at all. I have no idea how to implement such > a fix, or whether it'd be really acceptable after all. > Or move 4 paragraphs of introductory material into its own section so that there are two sections and a brief sentence for an intro. I don't see a special case for a single section to be a productive use of time. Improved formatting overall for the chapter ToC has merit. David J.
Clarify: default precision on timestamps is 6
On Friday, October 13, 2023, PG Doc comments form wrote: > both of them round any precision > beyond microseconds, and neither returns timestamps with greater precision > than the value that was inserted. > That is precisely what a no default with maximum of six means. If we say the default is six that would imply storage of less precise values pads significant zeros until there are six. David J.
Re: docs: set role permission checking, do I read this wrong?
On Wednesday, October 18, 2023, Daniel Westermann < daniel.westerm...@dbi-services.com> wrote: > > "After |SET ROLE|, permissions checking for SQL commands is carried out as > though the named role were the one that had logged in originally." > > Isn't it the other way around and permission checking is done as "a", or > do I read this wrong? > It is saying “a” is the current_user: When you set role to (named role) a the system behaves as if (named role) a had logged in originally (even though, in that example, postgres is the role that originally logged in) David J.
Re: docs: set role permission checking, do I read this wrong?
On Wednesday, October 18, 2023, Daniel Westermann < daniel.westerm...@dbi-services.com> wrote: > > Thank you, this is what I see in the small example. Maybe it is my > English, but this sentence sounds confusing. > How would you document that behavior? The sentence is correct; that doesn’t mean it can’t be improved. David J.
Re: opclass. See below for details. cannot found the "below".
On Sunday, October 22, 2023, jian he wrote: > https://www.postgresql.org/docs/current/sql-createindex.html > << > opclass > The name of an operator class. See below for details. > << > > there is no "below"? > Browser search, there are only four appearances of keyword "opclass". > > The notes on that discuss this a bit further then give you two links for more info, one being: https://www.postgresql.org/docs/current/indexes-opclass.html Why would you search for “opclass” and not “operator class” - the later is used in the notes paragraph that is being referred to? David J.
Re: pg_isready --dbname option is broken. So it should not be in the manual
On Thursday, October 26, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/app-pg-isready.html > Description: > > the --dbname option in pg_isready seems not to work propperly. the tool > returns 'ok' as long as the cluster itselft is running, no matter how wrong > the bdname might be. > > as this seems to be a ~10 year old misbehaviour as per the below thread I > think it should be removed from the manual. > > https://www.postgresql.org/message-id/flat/52840D38.9070604%40agliodbs.com > Read the notes section. David J.
Re: pg_isready --dbname option is broken. So it should not be in the manual
On Thursday, October 26, 2023, Daniel Gustafsson wrote: > > On 26 Oct 2023, at 14:20, David G. Johnston > wrote: > > > > On Thursday, October 26, 2023, PG Doc comments form < > nore...@postgresql.org <mailto:nore...@postgresql.org>> wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/app-pg-isready.html < > https://www.postgresql.org/docs/16/app-pg-isready.html> > > Description: > > > > the --dbname option in pg_isready seems not to work propperly. the tool > > returns 'ok' as long as the cluster itselft is running, no matter how > wrong > > the bdname might be. > > > > as this seems to be a ~10 year old misbehaviour as per the below thread I > > think it should be removed from the manual. > > > > https://www.postgresql.org/message-id/flat/52840D38. > 9070604%40agliodbs.com <https://www.postgresql.org/ > message-id/flat/52840D38.9070604%40agliodbs.com> > > > > Read the notes section. > > The notes section is pretty hidden though, I can sympathize with anyone > missing > it and maybe making the info a bit more visible would be good? > Add a “see notes” link to those two parameters. But we put lots of stuff like this into the notes everywhere; DBAs are expected to look for and read them on the man pages they consume. This doesn’t seem to fit into the description nor warrants a callout. David J.
Re: Full Text Search
On Sun, Oct 29, 2023, 13:58 PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/textsearch-limitations.html > Description: > > Hello, > > In the FTS/Limitations part of the documentation, it says : > > "Another example — the PostgreSQL mailing list archives contained 910,989 > unique words with 57,491,343 lexemes in 461,020 messages." > > How could the number of lexemes be greater than unique words ? > https://www.postgresql.org/docs/14/textsearch-parsers.html Note the part with the hyphenated word example. David J. >
Re: 'pg_global' cannot be used as default_tablespace.
On Wednesday, November 1, 2023, Bruce Momjian wrote: > On Tue, Nov 10, 2020 at 08:28:08AM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/13/bug-reporting.html > > Description: > > > > Tablespace 'pg_global' is one of the two auto-generated tablespace by > > initdb, and 'pg_global' should not be used as the default_tablespace, > since > > it is used for shared system catalogs. However, none of these > information is > > recorded in the doc > > https://www.postgresql.org/docs/11/runtime-config-client.html and > > https://www.postgresql.org/docs/11/manage-ag-tablespaces.html. In case > of > > some mishandling to use 'pg_global' as a default_tablespace value, it is > > better to record it in the doc of > > https://www.postgresql.org/docs/11/manage-ag-tablespaces.html. > > I know this is three years old, but I am now looking at this email can > can't see the problem: > > SET default_tablespace = 'pg_global'; > > CREATE TABLE test (x int); > ERROR: only shared relations can be placed in pg_global tablespace > > Did you want an error from the SET command? > That would probably be a decent addition but the request was for us to add “it is not permissible to specify the pg_global tablespace for either default_tablespace or temp_tablespace”. In the tablespace section per the request but maybe also within the settings definition section. David J.
Re: jsonb array accessors
On Saturday, November 4, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/datatype-json.html > Description: > > https://dbfiddle.uk/Dr-c-nqL > > multiple subscripts for jsonb arrays do not behave as described on Table > 8.25 in Postgres 16 docs / 8.14.7 jsonpath > That table describes jsonpath operations. Your example doesn’t involve jsonpath at all; it uses an SQL-scoped operation/syntax. You will need to use an operator or function that accepts jsonpath as its argument if you want to avail yourself of jsonpath functionality. David J.
Re: Example for Unique Partial Indexes
On Tue, Nov 7, 2023, 09:25 PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/indexes-unique.html > Description: > > I would like to request an improvement to the PostgreSQL documentation, > specifically in the sections "11.6. Unique Indexes" and "11.8. Partial > Indexes." > > Currently, the documentation does not provide information on how to create > unique partial indexes. Unique partial indexes are valuable for enforcing > uniqueness of a column's value over a subset of a table > 11.8 discusses this in detail including an example. Maybe should add a forward reference from 11.6 though. David J.
Re: Example for Unique Partial Indexes
On Tue, Nov 7, 2023 at 10:08 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Nov 7, 2023, 09:25 PG Doc comments form > > wrote: > >> Currently, the documentation does not provide information on how to > create > >> unique partial indexes. Unique partial indexes are valuable for > enforcing > >> uniqueness of a column's value over a subset of a table > > > 11.8 discusses this in detail including an example. Maybe should add a > > forward reference from 11.6 though. > > Yeah, AFAICS 11.8's coverage of this point is perfectly adequate. > > I'm disinclined to add a forward reference, because 11.8 is the > first section that mentions partial indexes at all. Somebody > reading the chapter in order would have no idea what we were > talking about. > > I do understand that argument but given that uniqueness comes up considerably more often than partial indexes for me it makes sense to inform the reader learning about unique constraints about what else exists in the world and not just presume they are reading the documentation serially (or will take the time to really dive into 11.8). "Sometimes you only care about uniqueness for data within a particular state (say an active state) and do not care if (say, inactive) data exists as well, possibly multiple times. A unique index by itself cannot handle this situation but you can combine it with a partial index for the active state data (as described in 11.8) to achieve this limited uniqueness requirement." David J.
Another user complaint regarding visibility of pg_catalog data
Hey, This comes up every so often (including today on Discord) and I keep having trouble figuring out where to point people for our official assertion and explanation for why anyone with a login can view routine bodies, view specifications, and comments. Apparently I griped about this a while ago and it fell on deaf ears: https://www.postgresql.org/message-id/1424231867994-5838367.post%40n5.nabble.com Is this something we just don't want to go into detail within our documentation, or just no one has cared enough to write something up (beyond my first draft back then) and form it into a patch? David J.
Re: Another user complaint regarding visibility of pg_catalog data
On Tue, Nov 7, 2023 at 12:28 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > Is this something we just don't want to go into detail within our > documentation, or just no one has cared enough to write something up > (beyond my first draft back then) and form it into a patch? > I've gone and added an FAQ entry for this at least so I can post a link to that in the future. https://wiki.postgresql.org/wiki/FAQ#How_do_I_prevent_regular_users_from_seeing_my_trade_secrets.3F I'm not married to the title or content but wanted to get something out there while my mind was engaged on the topic. David J.
Re: Another user complaint regarding visibility of pg_catalog data
On Wednesday, November 8, 2023, Laurenz Albe wrote: > > > When people ask my "why?", I tend to answer "why not?". It is not a > security > problem, in my opinion. Every user is allowed to know that I have a table > "purchase" with a column "credit_card_nr". As long as the permissions are > set > correctly, that is no problem. Any attempt to hide that information is at > best > "security by obscurity". > The typical answer is some variant of trade secrets. Though wanting to store private info in a comment has some merit too. David J.
Re: CREATE SUBSCRIPTION issue
On Wed, Nov 8, 2023 at 5:42 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: > https://www.postgresql.org/docs/15/logical-replication-row-filter.html > Description: > > Hey I'm using create subscription to subscribe to a table. I have two > different hosts and I want to setup replication between them. > In host 1 > I have one schema public and a table domain. > > In host 2 > I have multiple schemas schema1, schema2, schema3. > I want to copy the data from the host1 to host2 into schema1 and 2 and 3. > > While setting up the subscription it's giving me error saying 'relation > "public.domain" does not exist'. because in host2 the table is created in > schema1. So table would look like "schema1.domain". Any idea on how we can > setup such subscription such that schema name shouldn't be an issue. > The schema an object is located in is a fundamental component of its name/identity. The system has no provisions for mapping names on the publisher to different names on the subscriber. David J.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Fri, Nov 17, 2023 at 3:13 PM Bruce Momjian wrote: > On Fri, Apr 27, 2018 at 01:47:49PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/9.5/static/sql-select.html > > Description: > > > > In the SELECT statement page the argument type of the (FOR SHARE/UPDATE) > OF > > clause is listed to be a table_name. This is not *quite* accurate - it > > should reference the *alias* assigned to the table if one was given. The > > distinction is subtly important, as without this information the > > documentation implies that the choice of rows to lock can only be done > > per-table (i.e. that in a query mentioning the same table twice, *any* > > tuples being pulled from that table would be given the same treatment). > > > > But in fact postgres supports specifying the locking behaviour per-alias, > > which is a really powerful ability. And actually, trying to specify it by > > actual "table name" where an alias has been assigned won't work either. > > The attached patch documents this. > > I don't like this particular solution to the stated complaint. When a FROM entry has an alias it must be referenced via that alias anywhere it is referenced in the query - and indeed it is an error to not write the alias in your example. It is not an improvement to write [ table_name | alias ] in our syntax to try and demonstrate this requirement. If we do want to not say "table_name" I suggest we say instead "from_reference" and then just define what that means (i.e., an unaliased table name or an alias in the sibling FROM clause attached to this level of the query). I like this better anyway on the grounds that the thing being referenced can be a subquery or a view as well as a table. David J.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Mon, Nov 20, 2023 at 7:04 PM Bruce Momjian wrote: > On Fri, Nov 17, 2023 at 03:44:04PM -0700, David G. Johnston wrote: > > I don't like this particular solution to the stated complaint. When a > FROM > > entry has an alias it must be referenced via that alias anywhere it is > > referenced in the query - and indeed it is an error to not write the > alias in > > your example. It is not an improvement to write [ table_name | alias ] > in our > > syntax to try and demonstrate this requirement. If we do want to not say > > "table_name" I suggest we say instead "from_reference" and then just > define > > what that means (i.e., an unaliased table name or an alias in the > sibling FROM > > clause attached to this level of the query). I like this better anyway > on the > > grounds that the thing being referenced can be a subquery or a view as > well as > > a table. > > Okay, how is the attached patch? > > The placement in the numbered listing section feels wrong, I am OK with the wording. It should be down in the clause details. FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] -- need to change this spot to match where lock_strength can be one of [...] + and from_reference must be a table alias or non-hidden table_name referenced in the FROM clause. For more information on each [...] David J.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Mon, Nov 20, 2023 at 8:16 PM Bruce Momjian wrote: > On Mon, Nov 20, 2023 at 07:19:39PM -0700, David G. Johnston wrote: > > The placement in the numbered listing section feels wrong, I am OK with > > the wording. It should be down in the clause details. > > > > FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED > ] -- > > need to change this spot to match > > > > where lock_strength can be one of > > > > [...] > > > > + and from_reference must be a table alias or non-hidden table_name > referenced > > in the FROM clause. > > > > For more information on each [...] > > Ah, good point. I was searching for "FOR UPDATE" so I missed that > section; updated patch attached. > > WFM. Thanks! David J.
Re: [DOCS] Add example about date ISO format
On Wed, Nov 22, 2023 at 12:26 PM Bruce Momjian wrote: > On Wed, Nov 22, 2023 at 06:26:45PM +0100, Álvaro Herrera wrote: > > On 2023-Nov-22, Laurenz Albe wrote: > > > > > I think the example had best be at "8.5.2. Date/Time Output", in > > > doc/src/sgml/datatype.sgml around line 2552. > > > > Actually, isn't that a strange location? Chapter 8.5.2 is about the > > datatype itself, and there's already a cross-link to Section 9.8 for > > to_char() stuff. Since this is to_char() that the example wants to add, > > I think the to_char reference is a more appropriate place -- probably > > table "9.31 to_char Examples". > > I originally thought it belonged in section 9.8 too, but I think the > value of this example is ISO 8601 and I don't see how we can cleanly > mention that in table 9.31. > > Most of our tables have description columns, we could add one here. Or I've seen us use footnote superscripts before in a table then add the footnote text after the end of the table. I'm against incorporating this material into the data types in Chapter 8. David J.
Re: [DOCS] Confusing Trigger Docs.
On Wed, Nov 22, 2023 at 2:13 PM Bruce Momjian wrote: > On Wed, Nov 22, 2023 at 10:31:25AM +0100, Laurenz Albe wrote: > > I agree that the paragraph you are trying to improve needs it. > > > > I am not sure about that last sentence you added: > > > > The modification of > > EXCLUDED columns has similar interactions. > > > > How do you modify an EXCLUDED column? Are you talking about a BEFORE > > INSERT trigger? Reading the original text, I get the impression that > > it means "the behavior is obvious if you modify a column that is used > > with EXCLUDED in the DO UPDATE clause, but it can also happen if that > > column is not user with EXCLUDED". > > > > Perhaps you should omit that sentence for clarity. > > I think I found out what it trying to say by looking at the INSERT > manual page: > > Note that the effects of all per-row BEFORE > INSERT triggers are reflected in > excluded values, since those effects may > have contributed to the row being excluded from insertion. > > I modified the attached patch to explain this since it is not really the > same as modifying the actual row. Does that add any value? If not, > let's remove it. > > There is too much exposition drowning out the main purpose here which is to explain how the dual trigger situation introduced with on conflict gets handled. The following is a more direct approach. If an insert command contains an on conflict do update clause, before insert row triggers will be applied to the proposed row before conflict detection. If the update branch is taken, before update row triggers will also be applied. Either an insert or an update after row trigger will fire for each row. Before statement triggers fire for insertions first and then for updates, while after statement triggers fire in the reverse order, updates and then inserts. Statement triggers fire regardless if any rows were actually inserted or updated. Tangentially, having the partition table content between this and the merge content seems odd. There also seems to be room to integrate this and merge a bit better but that is beyond what I want to try right now. David J.
Re: Mention that there will be no feedback.
On Thursday, November 23, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/tutorial-createdb.html > Description: > > You might want to mention that createdb will give no feedback after > creating > database. Neither will dropdb. > But it does, like all commands it produces an exit code, which is 0 for success. David J.
Re: Table-space documentation
On Sunday, November 26, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/manage-ag-tablespaces.html > Description: > > While reading the table-space documentation > https://www.postgresql.org/docs/16/manage-ag-tablespaces.html the > information I was looking for was not included. The page invites feedback, > so I though I would share. I was specifically looking for information on > maximum table-space size and whether there is a use-case in which you would > create several table-spaces to split your tables / indexes logically. I am > from an Oracle world and it is normal for me to think about creating > multiple table-spaces for groups of tables and placing a size on the > table-space so that space does not extend and fill the o/s disk-space. It > would be nice for the documentation to include information about this > topic. > Even if it is says that these factors I should not worry about as postgres > handles them. > We tend to avoid documenting things that don’t exist. So the lack of a documented limit or any syntax to define one means no limit exists. As for usage patterns, there is a note I thought (maybe wiki FAQ?) that since the only thing you get with a tablespace is another root level storage place having more than one per disk really serves no purpose. David J.
Re: Clarification regarding CREATE TABLE LIKE and FOREIGN KEYS
On Fri, Dec 1, 2023 at 7:07 AM David Virebayre wrote: > "INCLUDING ALL" leads people to believe *everything* is copied. > > The fact that it doesn't seem counter intuitive, at least to me. > > Therefore, it doesn't seem absurd to me to warn people about what is not > copied. > > I wrote the original message hoping to help improve the documentation. I > could write a draft if I'm allowed to. > I do believe that clarifying this point improves the documentation. If > there's a consensus that it doesn't, my apologies for the annoyance. > > You are welcome to suggest improvements. But "all" means inclusive of every individual item listed on that page. You don't get stuff in "all" that you cannot specify individually and you cannot specify references clauses individually. David J.
Re: Missing "CONSTRAINT" keyword in ADD CONSTRAINT form?
On Monday, December 4, 2023, Tim Needham wrote: > Bah, scratch that, sorry again. It's because "CONSTRAINT" is implied by > the reference to CREATE DOMAIN, isn't it? > Yes, we require the reader to go to the create domain page to read the “domain_constraint” portion of the syntax. David J.
Re: Where is using a table name as a "row value" documented?
On Friday, December 8, 2023, Gulyás Attila wrote: > > Unfortunately, neither the row constructor docs[7] nor the pages > referenced there mention this usage anywhere (unless I missed it somehow). > I also found this answer[8] helpful when trying to find the relevant docs. > I found this fairly,quickly when looking for what can be used as a column reference. But I agree that this might need to be documented elsewhere as well. https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE David J.
Re: unclear wording re: spoofing prevention on network connections
On Saturday, December 9, 2023, Stephen Frost wrote: > > > The idea is that you can use both TLS and GSSAPI-with-encryption at the > same time within a given cluster for connections but you wouldn’t use them > on the same connection. Certainly would welcome suggestions as to the best > way to phrase that. > It isn’t really connection driven though - or even specific to these two options. The pg_hba.conf file can contain any number of different authentication methods that are usable simultaneously (from the perspective of the cluster). But a given login request is only going to match a single one of those lines; so it isn’t like the client somehow decides during each login using the same machine and user name which way they are going to verify who they say they are. We don’t call out being able to use password and peer simultaneously, the description and specification of the pg_hba.conf file itself imparts that information. I’m unclear why these two would warrant a special calling out. David J.
Re: Documentation does not describes format for access privileges: =Tc/user
On Friday, December 22, 2023, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/ddl-priv.html > Description: > > Hello. > The page https://www.postgresql.org/docs/current/ddl-priv.html does not > describe what =Tc/user means. Also I did not find a link to appropriate > page > which describes this. > Specifically I do not understand how 'user=Tc/user' differs from > '=Tc/user'. > > It would be nice if documentation will be extended. > > The paragraph immediately following table 5.2 describes all of this. David J.
Re: Documentation does not describes format for access privileges: =Tc/user
On Monday, December 25, 2023, Eugen Konkov wrote: > No, it does not. If you refer to `An empty grantee field in an aclitem > stands for PUBLIC.` then "grantee field" was never described. What is > this? > > It would be very clear if it was described in this way: > The access privileges has the following format: "grantee=privileges/who > grants". > > Yes, it requires a bit of mental gymnastics to read. The description says Calvin is the role being granted the privileges which makes that the grantee and Calvin is listed before the equal sign in the reference. “Who grants” is the “grantor”. I’ll accept that this can be improved but aside from a dictionary definition of grantee, which we don’t usually do, everything is shown. David J.
Re: Documentation does not describes format for access privileges: =Tc/user
On Monday, December 25, 2023, David G. Johnston
wrote:
> On Monday, December 25, 2023, Eugen Konkov wrote:
>
>> No, it does not. If you refer to `An empty grantee field in an aclitem
>> stands for PUBLIC.` then "grantee field" was never described. What is
>> this?
>>
>> It would be very clear if it was described in this way:
>> The access privileges has the following format: "grantee=privileges/who
>> grants".
>>
>>
> Yes, it requires a bit of mental gymnastics to read. The description says
> Calvin is the role being granted the privileges which makes that the
> grantee and Calvin is listed before the equal sign in the reference.
>
> “Who grants” is the “grantor”.
>
> I’ll accept that this can be improved but aside from a dictionary
> definition of grantee, which we don’t usually do, everything is shown.
>
We probably should write the syntax like we do everywhere else:
[grantee]={privilege[*]}[…]/grantor
Then define the placeholders in the subsequent paragraph.
David J.
Re: string_agg
On Wednesday, December 27, 2023, PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/functions-aggregate.html > Description: > > Please add the argument "distinct" to the documentation of the > aggregate-function "string_agg". It's used to omit any duplicates in the > result string. > string_agg isn’t special in this regard so instead of cluttering up every function with distinct we instead choose to document it as a syntax feature of aggregate functions in general. https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-AGGREGATES David J.
Re: Transition relation clarification
On Mon, Jan 1, 2024 at 9:35 AM Dull Bananas wrote: > The docs should mention that transition relations aren't automatically > made available in functions called by the trigger function. This unknown > behavior caused a lot of frustration for me. Seems to fall into the "if it isn't documented it doesn't happen" category of things that we simply choose not to clutter the documentation with. Called functions don't inherit stuff from the caller. It is like documenting a variable in the calling function isn't visible to the called function. Admittedly at least with variables you can pass them via arguments. The special trigger state is indeed available in the trigger returning function when it is the function directly invoked by the trigger. David J.
Re: initdb username doc bug
On Saturday, January 6, 2024, Bruce Momjian wrote: > This commit: > > commit 910cab820d > Author: Alvaro Herrera > Date: Fri Nov 18 11:59:26 2022 +0100 > > Add glossary entries related to superusers > > Extracted from a more ambitious patch. > > Author: David G. Johnston > Discussion: https://postgr.es/m/CAKFQuwZC4K0XYBm0bwBMDOZySBqhO > sekdhluaw4vpi+ozi8...@mail.gmail.com > > used this text for the --username option: > >-U username >--username=username >Selects the user name of the bootstrap superuser. This defaults >to the name of the cluster owner. > > This seems wrong since the cluster owner doesn't exist until the cluster > is created. The text exists in PG 16 and master. The attached patch > fixes this. > The glossary defines cluster owner as the pre-existing operating system user. There may be an argument that installation user is a better term but the existing choice isn’t wrong. If you are going to change it you need to update the glossary as well. The description in initdb uses cluster owner as well. I agree we presently use a mix of set and specify in these definitions so the word choice of selects is out-of-place. David J.
Re: initdb username doc bug
On Saturday, January 6, 2024, Bruce Momjian wrote: > On Sat, Jan 6, 2024 at 08:11:14PM -0700, David G. Johnston wrote: > > On Saturday, January 6, 2024, Bruce Momjian wrote: > > > > This commit: > > > > commit 910cab820d > > Author: Alvaro Herrera > > Date: Fri Nov 18 11:59:26 2022 +0100 > > > > Add glossary entries related to superusers > > > > Extracted from a more ambitious patch. > > > > Author: David G. Johnston > > Discussion: https://postgr.es/m/ > CAKFQuwZC4K0XYBm0bwBMDOZySBqhO > > sekdhluaw4vpi+ozi8...@mail.gmail.com > > > > used this text for the --username option: > > > >-U username > >--username=username > >Selects the user name of the bootstrap superuser. This > defaults > >to the name of the cluster owner. > > > > This seems wrong since the cluster owner doesn't exist until the > cluster > > is created. The text exists in PG 16 and master. The attached patch > > fixes this. > > > > > > The glossary defines cluster owner as the pre-existing operating system > user. > > No, it does not. > > > There may be an argument that installation user is a better term but the > > existing choice isn’t wrong. If you are going to change it you need to > update > > the glossary as well. The description in initdb uses cluster owner as > well. > > > > I agree we presently use a mix of set and specify in these definitions > so the > > word choice of selects is out-of-place. > > The patch says: > > + > + Cluster owner > + > + > --> + The operating system user that owns the > --> + data > directory > + and under which the postgres process is > run. > + It is required that this user exist prior to creating a new > + database > cluster. > + > + > + On operating systems with a root user, > + said user is not allowed to be the cluster owner. > + > + > + > > How can you default to be the owner of something that doesn't exist before > it is created? > > Also, the initdb code gets the default username from geteuid(), the > effective process owner; man geteuid says: > > geteuid() returns the effective user ID of the calling process. > > so it is not the owner of the data dirctory, but the installation user. > > I am open to saying that it is the owner of the data directory but we > would then need to change initdb to do that, and we aren't going to > backpatch that. > Ok, I see your point. Given that the installation users becomes the cluster owner by virtue of doing the only job they have, running initdb, defining both seemed excessive but it is indeed more precise. I would probably avoid install user though because of the probable confusion with the user that installed the distribution package as opposed to the one initializing the cluster (we don’t call it installing a cluster). So maybe a new defined term “cluster initializer”? David J.
Re: initdb username doc bug
On Saturday, January 6, 2024, David G. Johnston wrote: > On Saturday, January 6, 2024, Bruce Momjian wrote: > >> >> geteuid() returns the effective user ID of the calling process. >> >> so it is not the owner of the data dirctory, but the installation user. >> >> I am open to saying that it is the owner of the data directory but we >> would then need to change initdb to do that, and we aren't going to >> backpatch that. >> > > So maybe a new defined term “cluster initializer”? > Or just the bit more verbose “user running the initdb command” and don’t bother giving it a label, which is basically the approach used in the description for initdb anyway. David J.
Re: Grammar suggestion
On Monday, January 8, 2024, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/ddl-constraints.html > Description: > > Dear postgres Team, > > I happened to have stumbled across a tricky to read sentence in Ch. 5.45 > 'Foreign Keys' (Postgres Version 15). > > The original sentence: "Normally, a referencing row need not satisfy the > foreign key constraint if any of its referencing columns are null". > > Here's the revised version: "Normally, a referencing row does not need to > satisfy the foreign key constraint if any of its referencing columns are > null." > > A bit restructuring in beginning of this sentence can it make it > grammatically complete and clear for the reader. > I see where you are coming from but I think the word “need” is actual problem and it has to go. We use the phrase “escapes satisfying” in the subsequent sentence and should use it here too. Normally, a referencing row escapes satisfying the foreign key constraint if any of its referencing columns are null. [add: This is what is meant by match simple]. If match full is … Also, we go to the trouble of accepting “match partial”. Maybe add a final sentence in this paragraph nothing that we do so and explaining what partial is defined to mean in the standard? Also, we only note the not implemented in the syntax parameters area, not the compatibility area…this seems like an oversight. David J.
Re: initdb username doc bug
On Mon, Jan 8, 2024 at 2:26 PM Bruce Momjian wrote: > On Sat, Jan 6, 2024 at 11:27:14PM -0500, Tom Lane wrote: > > "David G. Johnston" writes: > > > Or just the bit more verbose “user running the initdb command” and > don’t > > > bother giving it a label, which is basically the approach used in the > > > description for initdb anyway. > > > > Or if you want a few more words, "name of the operating-system user > > running initdb". I don't like "installation user", that's just about > > as vague as could be. > > Agreed, updated patch attached. > > +1 Thanks! David J.
Re: Question on doc for RETURNING clause
On Thu, Jan 11, 2024 at 11:55 AM Russell, John wrote: > ``` > postgres=> insert into generatedfields (x) values (0), (10), (100) order > by 2 desc returning id, x; > ERROR: ORDER BY position 2 is not in select list > LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret... > ^ > ``` > > Is the acceptance of ORDER BY documented anywhere? VALUES, like SELECT, is an SQL Command in its own right. https://www.postgresql.org/docs/current/sql-values.html That is what you are ordering, before attempting insertion. Hence why it only sees one column. > I didn’t see that anywhere in the INSERT syntax. Does it have any > practical effect if there’s no RETURNING clause, e.g. do the rows get > physically inserted in the ORDER BY order, which could have implications > for columns like SERIAL? > At present, the order of rows presented to the insert does in no way compel the insert command to act on the provided rows in order; even though in practice it will seem to do so. David J.
Re: CREATE ROLE inheritance details
On Wed, Jan 17, 2024 at 9:38 AM Bruce Momjian wrote: > On Wed, Jan 17, 2024 at 09:28:38AM +0100, Laurenz Albe wrote: > > On Tue, 2024-01-16 at 17:03 -0500, Bruce Momjian wrote: > > > I am unhappy with the documentation adjustments made to CREATE ROLE in > > > Postgres 16 by this commit: > > > > > > commit e3ce2de09d > > > > > > I have attached a patch to re-add this information, and clarify it. I > > > would like to apply this to PG 16 and master. > > > > I had to read the text twice before I understood it, but I cannot think > > of a simpler way to write it. > > Yeah, I had the same feeling. A bullet list would be nice but overkill > for a manual page. > > I had a go at this. I went with a more "bullet item" approach with my wording for INHERIT/NOINHERIT. The entire paragraph regarding how the INHERIT "option" works, as opposed to the attribute, seems out of place where it was and the material is already covered in the GRANT page. We should either improve that page or extract this level of detail somewhere else, not try to clutter up the CREATE ROLE page with it. We consistently say what the default is for these attribute pairs, do so here as well. Turn the parenthetical in the IN ROLE section into actual assertive documentation of what the clause does. Tweak ROLE and ADMIN as well to fit in better. Reword the discussion regarding non-inheritance to be more direct. I added mention of the grantee aspect of privileges as a soft way of further pointing out that the IN ROLE, ROLE, ADMIN clauses are limited in what they can control in the resulting membership grants. I choose to use the phrasing "giving ... roles" for both parts of the sentence instead of switching to "... roles are given" for the second half. More of a style choice but I didn't think switching really added much and just makes it a bit wordier and possibly a bit more effort to mentally parse. Patch built on top of Bruce's role.diff, not HEAD David J. From ce4605354f1360662ffed6f0e239b2e06d107a85 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 17 Jan 2024 14:19:54 -0700 Subject: [PATCH] v2 --- doc/src/sgml/ref/create_role.sgml | 81 +-- 1 file changed, 45 insertions(+), 36 deletions(-) diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 5c2b71bf69..f134feea27 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,29 +133,29 @@ in sync when changing the above synopsis! NOINHERIT -This controls the membership inheritance status when a new role -is added as a member using the IN ROLE clause, -when this role is later added as a member of a new role with the -ROLE clause, and the default inheritance when -adding this role as a member using the GRANT -statement. In such GRANT statements, the -role's inheritance status will be used unless overridden by the -GRANT WITH INHERIT clause. +This controls whether a newly established membership of this role in +another has the INHERIT option. +Specifically, when this role is created with an IN ROLE +clause, is specified in the ROLE clause +of a future CREATE ROLE command, or when this +role is the "TO" role in a GRANT command +that does not specify INHERIT in the +WITH clause. -Role membership with the inherit attribute can automatically -use whatever database privileges have been granted to all -roles it is directly or indirectly a member of, though the -chain stops at memberships without the inherit attribute. -Without role inheritance, the only other value of membership -is via SET ROLE, assuming the membership has -the SET attribute. +See "GRANT on Roles" on the GRANT reference page +for more information on what the INHERIT option +on a membership grant does. + + + +If not specified, INHERIT is the default. In PostgreSQL versions before 16, -the GRANT statement did not support +the GRANT command did not support WITH INHERIT. Therefore, changing this role-level property would also change the behavior of already-existing grants. This is no longer the case. @@ -293,9 +293,12 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing -roles. (Note that there is no option to add the new role as an -administrator or to disallow SET ROLE; use a -separate GRANT command to do that.) +roles. The ne
Re: Creating the scripts and compiling the C files containing user-defined functions and types.
On Thursday, January 18, 2024, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/tutorial-sql-intro.html > Description: > > " Directory of C:\postgresql-16.1\src\tutorial > > 11/06/2023 11:17 PM . > 11/06/2023 11:17 PM .. > 11/06/2023 11:04 PM62 .gitignore > 11/06/2023 11:04 PM 1,579 advanced.source > 11/06/2023 11:04 PM 6,091 basics.source > 11/06/2023 11:04 PM 5,169 complex.c > 11/06/2023 11:04 PM 7,580 complex.source > 11/06/2023 11:04 PM 3,078 funcs.c > 11/06/2023 11:04 PM 4,531 funcs.source > 11/06/2023 11:04 PM 900 Makefile > 11/06/2023 11:04 PM 460 README > 11/06/2023 11:04 PM 5,506 syscat.source > 10 File(s) 34,956 bytes >2 Dir(s) 153,064,722,432 bytes free" > > it shows > > C:\postgresql-16.1\src\tutorial>make > 'make' is not recognized as an internal or external command, > operable program or batch file. Yeah, the “prior chapter” needed to create the database assumes you are able to compile PostgreSQL from source in which case make would be installed. > > and this also. > > C:\postgresql-16.1\src\tutorial>Makefile > 'Makefile' is not recognized as an internal or external command, > operable program or batch file. > The docs don’t tell you to try and execute the plain text Makefile. David J.
Re: create table explicitly mention that unique|primary key constraint will create an
On Thu, Jan 18, 2024 at 7:54 AM Peter Eisentraut wrote: > > I find the specific change > proposal for ddl.sgml a bit weird, though, because this is a very > introductory section, and you are referring people to pg_class (what is > that?!?) for details. If we want to put something there, it should > respect the order in which that chapter introduces concepts. > > I started looking at this specific item and immediately got the idea to actually document in user-facing (i.e., not system catalogs) what these object categories are in which object types share the schema namespace. The "Other Object Types" section already in the DDL chapter seems to provide a near-perfect place to put this (not sure I like the word "other" there being my only complaint). The attached patch replaces Laurenz's v1, leaving the create_table changes as-is but presenting an alternative approach to introducing namespacing when we explain why schemas exist. David J. From 4ba026d1a42e074df103a769e0f6b71629631c87 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Thu, 18 Jan 2024 14:15:33 -0700 Subject: [PATCH] Doc-objects-in-pg_class-share-a-namespace --- doc/src/sgml/ddl.sgml | 168 +++-- doc/src/sgml/ref/create_table.sgml | 9 +- 2 files changed, 163 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..86f793f724 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3002,11 +3002,18 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE; A database contains one or more named schemas, which - in turn contain tables. Schemas also contain other kinds of named - objects, including data types, functions, and operators. The same - object name can be used in different schemas without conflict; for - example, both schema1 and myschema can - contain tables named mytable. Unlike databases, + in turn contain all other database-specific objects (most importantly, tables). + The schema name, combined with an object's type category (i.e., tables are relationas), + forms a namespace in which any given object's name exists. + When writing an object's name the object type category component is inferred from + context. The schema name component can be explicitly prepended to the name separated + by a period (e.g., schema.object_name). If the schema name is not specified, the system + will search for the name in the schemas named in the search_path setting. + See ... for a listing of object types grouped by category. + + + + Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so. @@ -5274,31 +5281,172 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; that exist in a database. Many other kinds of objects can be created to make the use and management of the data more efficient or convenient. They are not discussed in this chapter, but we give - you a list here so that you are aware of what is possible: + you a partial list here so that you are aware of what is possible. + The first tier of labels are the object type categories that combine + with the schema name to form a namespace. If multiple specific + object types share the same category they will be listed in the + second tier of labels. - Views + Relations + + + + + +Tables + + + + +Indexes + + + + +Views + + + + +Materialized Views + + + + +Foreign Tables + + + + +Composite Types + + + + +Sequences + + + + +Partitioned Tables + + + + +Partitioned Indexes + + + + + + + + + Routines + + + + + +Functions + + + + +Aggregate Functions + + + + +Window Functions + + + + +Procedures + + + + + + + + + Data Types + + + + + +Base Types + + + + +Composite Types + + + + +Range Types + + + + +Multi-Range Types + + + + +Domains + + + + +Enums + + + + + + + + + Operators + + + + + + Triggers + + + + + + Row-Level Security (RLS) Policies
Re: About COPY
On Friday, January 19, 2024, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/tutorial-populate.html > Description: > > COPY weather FROM '/home/user/weather.txt'; is not working it requires > additional commands for me. > And your point is? The table has to have already been created and the file path needs to be modified to your personal circumstance. What is it exactly that you want to have changed? David J.
Re: Missing information on '-X' in section 26.3.6.1.
On Tue, Jan 23, 2024 at 1:30 PM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/continuous-archiving.html > Description: > > I noticed, that in section 26.3.6.1. it's not specified, what the -X > parameter should be set to (stream or fetch, or whether it even matters). I > could continue with trial and error, but it confused me a bit. > > The -X parameter is documented to have a default; but since both fetch and stream are documented to give you the same end result it doesn't matter. Of course you cannot specify the none method. David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe wrote: > On Wed, 2024-01-24 at 15:40 +0100, gp...@free.fr wrote: > > maybe a misunderstanding of my part, but your proposed modification > doesn't matched > > with the current behaviour of the command as precisely the object > privileges of the old owner are **NOT** transferred > > to the new owner along with the ownership > > But that is what happens. > > The permissions are transferred to the new owner, so the old owner doesn't > have any privileges on the object (and, in your case, cannot connect to > the database any more). > > I dislike this change, ownership of an object is completely independent of the grant system of privileges. The granted privileges of the old row do not transfer to the new owner when alter ... owner to is executed. The separate object attribute "owner" is the only thing that changes. If the old owner doesn't have any granted privileges on the modified object then they will be left with no ability to interact with that object. In the case of Database the applicable interactions are Create and Connect. The permissions the old owner may have on any other objects in the database are also left unaffected - such as those on a schema. But if they have lost the ability to Connect then actually exercising schema privileges becomes impossible. It really isn't any different than removing their login attribute. Note that since PUBLIC gets connect privileges on all databases by default... David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe wrote: > On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote: > > I dislike this change, ownership of an object is completely independent > of > > the grant system of privileges. The granted privileges of the old row do > > not transfer to the new owner when alter ... owner to is executed. > > CREATE TABLE mytab (); > > REVOKE ALL ON mytab FROM PUBLIC; > > \z mytab > Access privileges > Schema │ Name │ Type │ Access privileges │ Column privileges │ > Policies > > ╪═══╪═══╪═══╪═══╪══ > public │ mytab │ table │ postgres=arwdDxt/postgres │ │ > (1 row) > > ALTER TABLE mytab OWNER TO laurenz; > > \z mytab > Access privileges > Schema │ Name │ Type │Access privileges│ Column privileges │ > Policies > > ╪═══╪═══╪═╪═══╪══ > public │ mytab │ table │ laurenz=arwdDxt/laurenz │ │ > (1 row) > > > You need to actually revoke something to make the point stand out. postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+---+---+-- public | tt1 | table | davidj=arwdDxt/davidj | | (1 row) postgres=# revoke update on tt1 from davidj; REVOKE postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+--+---+-- public | tt1 | table | davidj=ardDxt/davidj | | (1 row) postgres=# alter table tt1 owner to testowner; ALTER TABLE postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---++---+-- public | tt1 | table | testowner=ardDxt/testowner | | (1 row) The new owner, testowner, is missing the same update privilege that davidj removed from himself. In short, setting owner does indeed cause explicit grants to appear in the system, grants that can be revoked. And so, yes, transferring ownership transfers the set of grants currently in effect for the existing owner. I can see making this detail more clear in the DDL chapter. It is unrelated to the confusion behind the topic of this thread though. David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 9:23 AM wrote:- > [postgres] $ psql > psql (14.10) > > You really should add commentary, especially since you never demonstrated the tst role (I advise picking different names for all of the objects in the future) being unable to login. Which they should be able to since public is shown to have "c" connect privileges (=Tc/tst) > [postgres@PGDEV14] postgres=# create user tst password 'tst'; > CREATE ROLE > [postgres@PGDEV14] postgres=# create database tst owner = tst; > CREATE DATABASE > This next command is pointless, it is a no-op, as soon as you made them owner of the tst database they already had all privileges to it, granted by the same user that created the database. And only it, that command is not recursing through the database into schemas and tables and adding more permissions. That isn't how this all works, a database is an object. While it is also a concept that encompasses the entire schema within it the permissions system only cares about the first definition. [postgres@PGDEV14] postgres=# grant all on database tst to tst; > GRANT > [postgres@PGDEV14] postgres=# \l+ tst > Liste des bases de données > Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits > d'accès | Taille | Tablespace | Description > > -+--+--+-+--++-++- > tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst > +| 9809 kB | pg_default | > | | | | | > tst=CTc/tst| || > (1 ligne) > > What are you trying to demonstrate here? > [postgres@PGDEV14] tst=# \dn+ tst > Liste des schémas > Nom | Propriétaire | Droits d'accès | Description > -+--++- > tst | tst || > (1 ligne) > > David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 9:56 AM Alvaro Herrera wrote: > On 2024-Jan-24, Laurenz Albe wrote: > > > The permissions are transferred to the new owner, so the old owner > doesn't > > have any privileges on the object (and, in your case, cannot connect to > > the database any more). > > However, if the old owner had a pg_hba.conf line that allowed them in, > and the new owner doesn't, then they're now both locked out of the > database with no recourse. > > The OP doesn't actually care about inherited permissions, just the stated ones. That said, I do think there is a problem here: postgres=# select current_user; -[ RECORD 1 ]+--- current_user | davidj postgres=# revoke all on database newdb2 from public; REVOKE postgres=# \l newdb2 List of databases -[ RECORD 1 ]-+ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale| ICU Rules | Access privileges | testowner=CTc/testowner postgres=# grant all on database newdb2 to testowner; -- as I am logged in as davidj this grant should actually happen, with davidj as the grantor -- the grants that materialize from ownership has the owning role as the grantor -- it is only those that should be removed upon reassigning ownership GRANT postgres=# \l newdb2 List of databases -[ RECORD 1 ]-+ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale| ICU Rules | Access privileges | testowner=CTc/testowner -- I expect to see "testowner=CTc/davidj" here as well David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 10:13 AM Tom Lane wrote: > "David G. Johnston" writes: > > postgres=# grant all on database newdb2 to testowner; > > -- as I am logged in as davidj this grant should actually happen, with > > davidj as the grantor > > -- the grants that materialize from ownership has the owning role as the > > grantor > > Yes. The FM points out somewhere that if a superuser does a GRANT, > it's executed as though by the object owner. That provision predates > when we supported explicit GRANTED BY clauses in GRANT. I'm not sure > we'd have made it work like that if we had GRANTED BY already, but > I'm afraid of the compatibility implications if we change it now. > > Agreed, and I do recall that - it is documented on the GRANT page. Also noted is I can "inherit ownership" if I exercise that inherited ability the resultant grant still comes from the owner. This unifies two of three ways for these grants to be established. If I give out the ability via a grant option only then does the grantor become the grant optioned role. This is the expected behavior and doesn't require documentation explicitly. The following testing of this behavior surprises me though: List of databases -[ RECORD 1 ]-+ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale| ICU Rules | Access privileges | testowner=CTc/testowner+ | to3=C*T*c*/testowner + | to4=CTc/to3+ | testowner=CTc/to3 postgres=> reset role; RESET postgres=# alter database newdb2 owner to davidj; ALTER DATABASE postgres=# \l newdb2 List of databases -[ RECORD 1 ]-+-- Name | newdb2 Owner | davidj Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale| ICU Rules | Access privileges | davidj=CTc/davidj+ | to3=C*T*c*/davidj+ | to4=CTc/to3 + | davidj=CTc/to3 I was expecting the privileges given to me by to3 to remain in place even after I lost my ownership grants. As you've noted it seems unlikely this is something we are willing to change at this point. So, in short, it seems impossible for an owner of an object to be left with any direct permissions on said object after having their ownership reassigned. The role which gets the new assignment assumes all of the explicit grants that exist for the old role. postgres=# alter database newdb2 owner to to3; ALTER DATABASE postgres=# \l newdb2 List of databases -[ RECORD 1 ]-+--- Name | newdb2 Owner | to3 Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale| ICU Rules | Access privileges | to3=C*T*c*/to3+ | to4=CTc/to3 This makes sense since the three grants that to3 would have after merging are consolidated into a single one - in an additive sense and the grant options being retained if present. David J.
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 7:23 AM Laurenz Albe wrote: > On Wed, 2024-01-24 at 11:08 +0100, gp...@free.fr wrote: > > for this "ALTER DATABASE" form, it should be mentioned that after > execution of the command, > > the old database owner loses all his privileges on it (even connection) > although it might > > still owns schemas or objects (tables, index,...) inside it. > > > > Thanks in advance to add this important precision. > > How about this: > > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index 4044f0908f..44042f863c 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -1891,6 +1891,8 @@ ALTER TABLE table_name > OWNER TO new_owne > Superusers can always do this; ordinary roles can only do it if they > are > both the current owner of the object (or inherit the privileges of the > owning role) and able to SET ROLE to the new owning > role. > + All object privileges of the old owner are transferred to the new owner > + along with the ownership. > > > > > Here's a slightly more detailed patch to consider to cover both the transference of ownership as well as documenting precisely what ownership means. diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..c8866ee9c7 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items; When an object is created, it is assigned an owner. The - owner is normally the role that executed the creation statement. - For most kinds of objects, the initial state is that only the owner - (or a superuser) can do anything with the object. To allow - other roles to use it, privileges must be - granted. + owner is the role that executed the creation statement + unless the statement itself specifies an owner. - There are different kinds of privileges: SELECT, + There are different kinds of grantable privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, @@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items; these privileges are used. + + Upon object creation the owner is granted all grantable privileges + on the object. Additionally, the built-in PUBLIC privileges of + the associated object type are granted. Lastly, if any have been defined, + the system grants the default privileges for the object type to the defined roles. + All of these privileges can be revoked. + + The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself. - (However, like all privileges, that right can be inherited by + (However, like the grantable privileges, that right can be inherited by members of the owning role; see .) + + Another inherent right the owner of an object has is to grant all + grantable privileges on that object to any database role, including + their own. + + An object can be assigned to a new owner with an ALTER command of the appropriate kind for the object, for example @@ -1893,6 +1904,11 @@ ALTER TABLE table_name OWNER TO new_owne Superusers can always do this; ordinary roles can only do it if they are both the current owner of the object (or inherit the privileges of the owning role) and able to SET ROLE to the new owning role. + The reassignment process involves changing the recorded owner of the object in + the appropriate system catalog, as well as changing all references + (grantor and grantee) to the old role in the Access Control List (ACL, see below) + column to the new role; leaving the old role without any direct privileges on the object. + Multiple privilege entries with the same grantor and grantee are consolidated into a single entry. Laurenz has already commented to my accidentally off-list initial post that this seems to be too much detail for this section. But it is the language specification Chapter, not the Tutorial, and I disagree. It doesn't seem like an internals topic and there just isn't anywhere else to define this stuff. The man pages I suppose work, and this does have some repetition of the material there, but personally this feels like the more correct spot. Some of the "where" language probably can be removed without loss of clarity but I haven't added anything here that isn't already described in even more detail at the end of this section. I just used that material in context. David J.
Re: SQL command : ALTER DATABASE OWNER TO
Thank you for the feedback. I've taken it into account and come up with a partial rewrite of my first pass, and posted it to -hackers so we can close this thread out. https://www.postgresql.org/message-id/cakfquwyy3ei05kpggk2del_pe5yjkdybxb362xuthovpqjf...@mail.gmail.com Some replies below. On Thu, Jan 25, 2024 at 7:35 AM Laurenz Albe wrote: > On Wed, 2024-01-24 at 16:04 -0700, David G. Johnston wrote: > > Here's a slightly more detailed patch to consider to cover both the > transference of ownership as well as documenting precisely what ownership > means. > > Ok, you want to describe that in more detail. But you should preserve > the when the term is used for the first time. > Yeah, I need to get better at looking for and applying semantic markup. Note that the documentation is careful to avoid the term "privilege" > when speaking about the latter: below, it says "The *right* to modify or > destroy an object is inherent in being the object's owner". > > We should leave that as it is. > Agreed. I was going for "first encounter clarification" only, not intending to define the term fully. I've taken a different approach with the new patch - not redefining the main term but adding appropriate qualifications for limited use in learning how things work. > I think we should say "owner" instead of "recorded owner". Also, is it > necessary > to detail to the level of system catalog columns? > > Agreed, with the overall flow of the content and context improved that specific sub-paragraph can be made much simpler without loss of clarity. David J.
Re: create table explicitly mention that unique|primary key constraint will create an
On Fri, Jan 19, 2024 at 3:46 AM Laurenz Albe wrote: > In the attached patch, I have copied the enumeration of relations from > the CREATE INDEX page. I think this small redundance is alright, but I > wouldn't mind if this gets removed from CREATE INDEX. > > Tweaking the main paragraph a little. We use examples elsewhere, it seems one for this makes the point very clear with less description. I removed it altogether but namespace is a word unto itself, not "name space". diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index e103eddd40..25db985a56 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3025,10 +3025,11 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE; A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. Within one schema, - two objects of the same type cannot have the same name. All relations - (tables, sequences, indexes, views, materialized views, and foreign tables) - share one name space, so they need to have different names if they are in - a single schema. The same + two objects of the same type cannot have the same name. The object type + of relations encompasses all of the following: + tables, sequences, indexes, views, materialized views, and foreign tables. + Thus, for example, an index and a table must have different names if they + are in the same schema. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, David J.
Re: CREATE ROLE inheritance details
On Fri, Jan 26, 2024 at 5:18 PM Bruce Momjian wrote: > > Just a reminder, this is for PG 16 and master. > > +Role membership with the inherit attribute can automatically use +whatever database privileges have been granted to all roles it +is directly or indirectly a member of, though the chain stops +at memberships lacking the inherit attribute. Without role +inheritance, the only other value of membership is the use of +SET ROLE, assuming the membership chain has +SET attributes. I really think it is clearer if we consistently call the property attached to the membership an "option" (the grant command calls them options) and restrict the word attribute to only when talking about the role property. The following refers to the option. I dislike burying this description about how the option works within create role like this. It is already in chapter 22 and this attribute description should point the reader there, not repeat the information. In PostgreSQL versions before 16, -the GRANT statement did not support -WITH INHERIT. Therefore, changing this role-level -property would also change the behavior of already-existing grants. -This is no longer the case. +inheritance was a role-level attribute. It could not be specified +during role addition with GRANT, and changing +this role-level property would also change the inheritance behavior +of all existing memberships. This is no longer the case. My first reaction to the wording here is negative. I agree that the pre-v16 behavior dynamic should be documented but maybe leave a note with a bit more detail in chapter 22 and leave the following in place here: Prior to version 16 this attribute directly controlled runtime privilege inheritance instead of now only providing a default for when role membership is established. @@ -285,9 +294,13 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing -roles. (Note that there is no option to add the new role as an -administrator; use a separate GRANT command -to do that.) +roles. The new membership will have the SET +option enabled and the ADMIN option disabled. +The INHERIT option will be enabled unless the +NOINHERIT attribute is specified. See the command, which has additional attribute +control during membership creation and to modify these options +after the new role is created. additional attribute control s/b additional option control @@ -307,10 +324,10 @@ in sync when changing the above synopsis! ADMIN role_name -The ADMIN clause is like ROLE, -but the named roles are added to the new role WITH ADMIN -OPTION, giving them the right to grant membership in this role -to others. +The ADMIN clause is similar to +ROLE, but the named roles are added as members +of the new role with ADMIN enabled, giving +them the right to grant membership in this role to others. I was trying to be explicitly clear that the ADMIN clause is effectively additive to what ROLE does. "similar to + but" makes it easier to interpret as something that only controls ADMIN, not SET or INHERIT. The ADMIN clause behaves like ROLE but the ADMIN option is enabled. And modify ROLE as such: +INHERIT enabled in the new membership. New +memberships will have the ADMIN option disabled. + Use the ADMIN clause instead if you want the admin option enabled. This variant of the GRANT command grants membership - in a role to one or more other roles. Membership in a role is significant + in a role to one or more other roles, and the modification of + membership attributes. Membership in a role is significant and (allows) the modification of three membership options, set, inherit, and admin, described in chapter 22. Membership in a role is significant... To modify that attributes of + an existing membership, simply specify the membership with updated + attribute values. attribute s/b option @@ -275,15 +278,13 @@ GRANT role_name [, ...] TO + The INHERIT option controls the inheritance status + of the new membership; see for + details on inheritance. If it is set to TRUE, + it causes the new member to inherit from the granted role. If + set to FALSE, the new member does not inherit. + If unspecified, it defaults to the inheritance status of the role + being added. Suggest linking to chapter 22, not create role. Unspecified has two outcomes: if the grant is establishing a new membership the inheritance "attribute" value of the role being added is used, if the grant is altering an existing membership the current v
Re: CREATE ROLE inheritance details
Almost there I think. The comment regarding seeing GRANT really applies to IN ROLE, ROLE, and ADMIN. Repeating it seemed a poor choice and upon thinking further it really makes sense to consider how this command and GRANT work together part of the description of create role. So I moved that commentary to a new description paragraph. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index d0d3d7ed64..f3b89e7239 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -66,6 +66,17 @@ in sync when changing the above synopsis! Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. + + + During role creation it is possible to immediately assign the newly created + role to be a member of an existing role, and also assign existing roles + to be members of the newly created role. The rules for which initial + role membership options are enabled are described below in the + IN ROLE, ROLE, and + ADMIN clauses. Alternatively, the + command has fine-grained option control during membership creation, + and also the ability to modify these options after the new role is created. + @@ -285,10 +296,7 @@ in sync when changing the above synopsis! roles. The new membership will have the SET option enabled and the ADMIN option disabled. The INHERIT option will be enabled unless the -NOINHERIT option is specified. See the command, which has additional option -control during membership creation and to modify these options -after the new role is created. +NOINHERIT option is specified. And then fixed up one last instance of attribute/option specification. I left the term " membership inheritance status" alone in the attribute description since that really does refer to the concept and neither the specific attribute here nor the membership option. All of the other references seemed better of being specific to the feature and not the behavior. @@ -301,9 +309,9 @@ in sync when changing the above synopsis! existing roles to be automatically added as members, with the SET option enabled. This in effect makes the new role a group. Roles named in this clause -with role-level INHERIT options will have -INHERIT enabled in the new membership. New -memberships will have the ADMIN option disabled. +with role-level the INHERIT attribute will have +the INHERIT option enabled in the new membership. +New memberships will have the ADMIN option disabled. The omission of an option during membership modification results in no change, not applying the default, applies to all three, not just inherit. Again, instead of documenting all three with this it seemed preferable to add it once somewhere - in this case the paragraph that talks about the option mode values seems appropriate. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index ee53871713..9d27b7fcde 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -266,7 +266,9 @@ GRANT role_name [, ...] TO TRUE or FALSE. The keyword OPTION is accepted as a synonym for TRUE, so that WITH ADMIN OPTION - is a synonym for WITH ADMIN TRUE. + is a synonym for WITH ADMIN TRUE. When altering + an existing membership the omission of an option results in the current + value being retained. And some final touch-ups; including removing the now redundant omission sentence for inherit. @@ -280,14 +282,13 @@ GRANT role_name [, ...] TO - The INHERIT attribute controls the inheritance status + The INHERIT option controls the inheritance status of the new membership; see for details on inheritance. If it is set to TRUE, it causes the new member to inherit from the granted role. If set to FALSE, the new member does not inherit. - If unspecified, new role membership defaults to the inheritance status - of the role being added. If an existing membership, the inheritance - option is unchanged. + If unspecified when creating a new role membership this defaults to + the inheritance attribute of the role being added. I've attached a revised role.diff patch as well as just the changes from the original. David J. From 1c483856c6e98a9f6bbd627bf13a950ab3e95871 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Tue, 30 Jan 2024 13:17:47 -0700 Subject: [PATCH] base rev description addition and tweaks grant tweaks --- doc/src/sgml/ref/create_role.sgml | 87 +++ doc/src/sgml/ref/grant.sgml | 29 ++- doc/src/sgml/user-manag.sgml | 8 +-- 3 files changed, 74 insertions(+), 50 deletions(-) diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml ind
Re: Missed information about clientname=CN option
On Wednesday, January 31, 2024, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/auth-cert.html > Description: > > Hello. > > This page missed information about supported clientname=CN/DN option and > describes only `map` option. > Also `clientcert` is described not in format. I expect it was documented > under `map` as next list item of supported options > The description for pg_hba.conf auth-options covers this. Since those two options do not only apply to cert but any hostssl entry it was chosen to document the ssl related options on the pg_hba.conf page. I do see value in pointing the reader back to that location from the cert page though. Or maybe move the wording to the cert page and replace the content in pg_hba.conf with a link to there. Leaning toward the later ATM. David J.
Re: Missed information about clientname=CN option
On Thu, Feb 1, 2024 at 3:16 AM Daniel Gustafsson wrote: > > On 1 Feb 2024, at 08:35, David G. Johnston > wrote: > > > maybe move the wording to the cert page and replace the content in > pg_hba.conf with a link to there. Leaning toward the later ATM. > > That sounds like the best option IMHO, care to propose a patch? > > Done here: https://www.postgresql.org/message-id/CAKFQuwa%3DiY13UkH2K4-Srut9iaXBi2FkLzWRxbok%2BmdSMPEDuA%40mail.gmail.com The material here needed some attention too, both on its own and to fit in with the changes to the client authentication section. https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES David J.
Re: Missing information on '-X' in section 26.3.6.1.
On Wed, Jan 24, 2024 at 2:19 AM Daniel Gustafsson wrote: > > On 23 Jan 2024, at 21:43, David G. Johnston > wrote: > > > > On Tue, Jan 23, 2024 at 1:30 PM PG Doc comments form < > nore...@postgresql.org <mailto:nore...@postgresql.org>> wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/continuous-archiving.html < > https://www.postgresql.org/docs/16/continuous-archiving.html> > > Description: > > > > I noticed, that in section 26.3.6.1. it's not specified, what the -X > > parameter should be set to (stream or fetch, or whether it even > matters). I > > could continue with trial and error, but it confused me a bit. > > > > The -X parameter is documented to have a default; but since both fetch > and stream are documented to give you the same end result it doesn't > matter. Of course you cannot specify the none method. > > Agreed. Still, it doesn't hurt to spell out what we take for granted but a > newcomer have to figure out in order to make the documentation easy to > follow > for new users. Something like the attached would be enough I think. > > So I once again find a larger issue here, mostly unrelated to the complaint at hand. This entire paragraph is in the Continuous Archiving & PITR section but the entire standalone concept is in opposition to that. It is also in a "Tips" section but doesn't really read as a tip. Thinking on it further, and as the tip talks about, what we are really doing here is describing a standalone physical file system backup in contrast to a pg_dump backup. We already have a chapter that does this - the previous one named "File System Level Backup". The attached patch moves this paragraph there. I distilled the paragraph down to its essence, but am open to being a bit more wordy, and consider more how this fits into the existing content of that page. I'm only really married to two things - mentioning the -X argument to pg_basebackup here is a bad idea and the content does not fit in the existing Tip area of continuous archiving section. David J. From 0512ec27c52401b9ed7b468e7b68ff9ebec1584e Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Fri, 2 Feb 2024 12:32:51 -0700 Subject: [PATCH] docs: move standalone pg_basebackup docs to file system section --- doc/src/sgml/backup.sgml | 47 1 file changed, 24 insertions(+), 23 deletions(-) diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index b3468eea3c..1c5e48d6da 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -351,6 +351,29 @@ pg_dump -j num -F d -f File System Level Backup + + In constrast to the logical backup that pg_dump performs there is + also the option to perform a physical backup. + PostgreSQL provides a tool, + pg_basebackup, that can produce a similar standalone + backup to the one produced by pg_dump, though the restoration point + of the physical backup is as of when the backup ended instead of when + it began. You may also choose to roll your own solution using the tools + available in your operating system. This section describes both options. + + + +Built-In Standalone Backups + + If all you want is a simple standalone backup of your cluster at some point in time + (specifically at roughly the moment the backup ended) you can just save the archive + produced by the command. It handles, by default, + saving the write-ahead log (WAL) files produced during the backup to the archive. + + + + + Custom Physical Backups An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database; @@ -462,6 +485,7 @@ tar -cf backup.tar /usr/local/pgsql/data the contents of indexes for example, just the commands to recreate them.) However, taking a file system backup might be faster. + @@ -1442,29 +1466,6 @@ restore_command = 'cp /mnt/server/archivedir/%f %p' Some tips for configuring continuous archiving are given here. - - - Standalone Hot Backups - - - It is possible to use PostgreSQL's backup facilities to - produce standalone hot backups. These are backups that cannot be used - for point-in-time recovery, yet are typically much faster to backup and - restore than pg_dump dumps. (They are also much larger - than pg_dump dumps, so in some cases the speed advantage - might be negated.) - - - - As with base backups, the easiest way to produce a standalone - hot backup is to use the - tool. If you include the -X parameter when calling - it, all the write-ahead log required to use the backup will be - included in the backup automatically, and no special action is - required to restore the backup. - - - Compressed Archive Logs -- 2.34.1
Re: system column
On Wednesday, February 7, 2024, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/ddl-system-columns.html > Description: > > Will be clear to have a picture of how they are in the database. > Do you have a specific question you were trying to answer that you were unable to using this documentation? The internal implementation details of these fields is not published intentionally. The user needs only to know that these column exist and can be retrieved in queries which ks what we documented here. David J.
