Re: pg_upgrade doc uses inconsistent versions within the doc.
> On 26 Sep 2023, at 22:26, Bruce Momjian wrote: > On Tue, Sep 26, 2023 at 10:56:27AM -0700, David G. Johnston wrote: >> I would get rid of any mentions of our old pre-v10 versioning scheme in the >> current documentation. For content such as this, a very big +1. > Good point, how is this attached patch? LGTM. -- Daniel Gustafsson
Re: Hyperlinks for source file references
On 25.09.23 13:09, Daniel Gustafsson wrote: Commit b73c3a11963 introduced xyz hyperlinks for files in the postgres source tree by linking to the gitweb interface at git.postgresql.org. This, IMO, makes the content referred to more accessible, and is especially interesting for README files. The links in question can be found here: https://www.postgresql.org/docs/devel/tableam.html Right now we have ~85 references to source files using in the docs, should we convert them to hyperlinks like the ones in the Table AM docs? I would be happy to make a patch that implements this, but I wanted to check here before making the effort to make sure it's not immediately nayed. A slightly fancier variant that avoids having to hardcode the git web URL pattern in a bunch of places: You mark it up like and then write a bit of XSL to process that into whatever form you want.
Is CREATE INDEX dependent on the session?
Hi, Would it warrant a clarification in the documentation: - Whether CREATE INDEX is aborted if the session is disconnected, assuming it's not in a transaction? - Ditto for CREATE INDEX CONCURRENTLY? - Whether CREATE INDEX CONCURRENTLY "returns" immediately or blocks the session until it's created? Given how these operations may take substantial time, and have no output beyond success, this might be a relevant topic.
Re: Certificate authentication docs in multiple places
On Tue, Mar 28, 2023 at 04:28:24PM +0200, Peter Eisentraut wrote: > On 23.03.23 14:33, Steve Atkins wrote: > > A couple of times recently I’ve been chatting with someone about using > > certificate authentication, and the docs they’ve found on it are the ones > > in section 21.12 (Client Authentication -> Certificate Authentication). > > > > But the useful documentation about how to set it up and use it is in > > section 19.9.3 (Secure TCP/IP Connections with SSL -> Using Client > > Certificates), where you’re less likely to find it while thinking about > > authentication. > > > > Should we add a link from the former to the latter? > > Sure, some cross-linking between those two sections seems sensible. Attached is a patch which accomplishes this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index a72f80f033..82bfd25fd1 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -2165,7 +2165,8 @@ host ... radius radiusservers="server1,server2" radiussecrets="""secret one"","" This authentication method uses SSL client certificates to perform -authentication. It is therefore only available for SSL connections. +authentication. It is therefore only available for SSL connections; +see for SSL configuration instructions. When using this authentication method, the server will require that the client provide a valid, trusted certificate. No password prompt will be sent to the client. The cn (Common Name)
Re: Seeming contradiction in 22.1
On Thu, Mar 23, 2023 at 05:45:03PM +0100, maja zaloznik wrote: > Thanks David, this is very helpful! > > I would have to agree with Tom, that while technically true, the text could be > reworded. namely it was not obvious to me that i had used a 'packager' to > install the clusters and therefore was doing something outside the purview of > the postgres documentation. > > To me the last sentence would be clearer if it read something like: > > "Most packagers will name this role `postgres` by default, but this is not > required". > > That way it avoids the passive tense which leaves some ambiguity as to who or > what and when is naming this superuser. I think the paragraph was trying to do too much so I simplified it, patch attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml new file mode 100644 index 27c1f3d..92a299d *** a/doc/src/sgml/user-manag.sgml --- b/doc/src/sgml/user-manag.sgml *** SELECT rolname FROM pg_roles WHERE rolca *** 103,113 In order to bootstrap the database system, a freshly initialized system always contains one predefined login-capable role. This role !is always a superuser, and by default it will have the same name as the operating system user that initialized the !database cluster, unless another name is specified while !running initdb. !It is common, but not required, to arrange for this role to be named postgres. In order to create more roles you first have to connect as this initial role. --- 103,112 In order to bootstrap the database system, a freshly initialized system always contains one predefined login-capable role. This role !is always a superuser, and it will have the same name as the operating system user that initialized the !database cluster with initdb unless a different name !is specified. This role is often named postgres. In order to create more roles you first have to connect as this initial role.
Re: Make SSPI documentation clearer
On Sun, Mar 12, 2023 at 08:36:53PM -0400, Stephen Frost wrote: > > When the server is on a non-Windows platform then the server must use GSSAPI > > if it wants to authenticate the client either via Kerberos or via Active > > Directory. A client on a Windows platform that connects to a non-Windows > > Postgresql server can either use SSPI (strongly encouraged) or GSS (much > > more difficult to set up) if it wants to authenticate via Kerberos or Active > > Directory. A client from a non-Windows platform must use GSS if it wants to > > authenticate via Kerberos or Active Directory." > > Rather than work in negative, I feel like it might make more sense to > work in positives? That is, perhaps this instead: > > On Windows platforms, SSPI is the default and most commonly used > mechanism. Note that an SSPI client can authenticate to a server which > is using either SSPI or GSSAPI, and a GSSAPI client can authenticate to > a server which is using either SSPI or GSSAPI. Generally speaking, > clients and servers on Windows are recommended to use SSPI while clients > and servers on Unix (non-Windows) platforms use GSSAPI. I developed the attached patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml new file mode 100644 index a72f80f..fa53a0f *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** omicron bryanh *** 1505,1514 negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. ! SSPI authentication only works when both ! server and client are running Windows, ! or, on non-Windows platforms, when GSSAPI ! is available. --- 1505,1514 negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. ! SSPI and GSSAPI ! interoperate as clients and servers. It is recommended to use ! SSPI on Windows clients and servers and ! GSSAPI on non-Windows platforms.
Re: Mistake in statement example
On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote: > 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. I developed the attached patch. I explained the example, I mentioned a "second" transaciton, I changed the account number so I can talk about the second statement, because read committed changes the row visibility of the non-first statements, and I changed "transaction" to "statement". -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index f8f83d4..189cab0 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 413,420 does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler ! cases. For example, consider updating bank balances with transactions ! like: BEGIN; --- 413,420 does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler ! cases. For example, consider transferring $100 from one account ! to another: BEGIN; *** UPDATE accounts SET balance = balance - *** 423,430 COMMIT; ! If two such transactions concurrently try to change the balance of account ! 12345, we clearly want the second transaction to start with the updated version of the account's row. Because each command is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency. --- 423,430 COMMIT; ! If another transactions concurrently tries to change the balance of account ! 7534, we clearly want the second statement to start with the updated version of the account's row. Because each command is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency.
Re: MERGE examples not clear
On Tue, Feb 21, 2023 at 08:56:50AM -0700, David G. Johnston wrote:
> 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).
The attached patch removes the second example, which doesn't seem to add
much.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index 0995fe0c04..4544ce92b3 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -582,23 +582,6 @@ WHEN NOT MATCHED THEN
-
- Notice that this would be exactly equivalent to the following
- statement because the MATCHED result does not change
- during execution.
-
-
-MERGE INTO customer_account ca
-USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
-ON t.customer_id = ca.customer_id
-WHEN MATCHED THEN
- UPDATE SET balance = balance + transaction_value
-WHEN NOT MATCHED THEN
- INSERT (customer_id, balance)
- VALUES (t.customer_id, t.transaction_value);
-
-
-
Attempt to insert a new stock item along with the quantity of stock. If
the item already exists, instead update the stock count of the existing
Re: Suggestion for deprecated spellings
On Mon, Jan 30, 2023 at 04:17:14PM -0500, Bruce Momjian wrote: > On Mon, Jan 30, 2023 at 04:07:46PM -0500, Tom Lane wrote: > > Bruce Momjian writes: > > > On Thu, Jan 26, 2023 at 12:19:29PM +, PG Doc comments form wrote: > > >> From time to time some spelling for given command gets obsolete, yet it > > >> is > > >> shown in the syntax on "equal rights" as other valid clauses. > > > > > We don't need to show all _supported_ syntaxes in the "Synopsis" > > > section, so we could just remove them. > > > > IIRC, there is precedent in COPY for moving obsolete alternatives > > to a separate part of the man page. I'd prefer that to just > > removing them, because then there is no documentation to help > > someone understand what an old SQL script is doing. > > Yeah, I remember that with COPY. [email protected], please us that > as a guide. Thanks. I developed the attached patch to move the deprecated clauses to the bottom. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 7249fc7432..6a3cd6d808 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -36,10 +36,8 @@ CREATE ROLE name [ [ WITH ] password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] -| IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] -| USER role_name [, ...] | SYSID uid @@ -294,15 +292,6 @@ in sync when changing the above synopsis! - - IN GROUP role_name - - IN GROUP is an obsolete spelling of -IN ROLE. - - - - ROLE role_name @@ -326,16 +315,6 @@ in sync when changing the above synopsis! - - USER role_name - - -The USER clause is an obsolete spelling of -the ROLE clause. - - - - SYSID uid @@ -484,6 +463,22 @@ CREATE ROLE name [ WITH ADMIN NOINHERIT attribute, while roles are given the INHERIT attribute. + + + The USER clause has the same behavior as + ROLE but has been deprecated: + +USER role_name [, ...] + + + + + The IN GROUP clause has the same behavior as IN + ROLE but has been deprecated: + +IN GROUP role_name [, ...] + +
Re: Is CREATE INDEX dependent on the session?
On Wed, 2023-09-27 at 11:23 -0400, Ilya Priven wrote: > Would it warrant a clarification in the documentation: > - Whether CREATE INDEX is aborted if the session is disconnected, assuming > it's not in a transaction? It is aborted as soon as the server realizes that the client is gone, which may take a while (see the keepalive parameters and "client_connection_check_interval"). > - Ditto for CREATE INDEX CONCURRENTLY? The answer is the same. Note that if CREATE INDEX CONCURRENTLY is interrupted, you are left with an INVALID index that you have to delete later. > - Whether CREATE INDEX CONCURRENTLY "returns" immediately or blocks the > session until it's created? It blocks until the index has been created. Do you want to propose a patch? Yours, Laurenz Albe
