Re: No details on how to use Hash partitions: no example, no syntax
On Tue, 2024-09-17 at 22:32 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/ddl-partitioning.html > Description: > > I would like the documentation to explain how to use Hash partitions. It > says one needs to provide a modulus and remainder, but the exact syntax is > not stated in the relevant documentation page 5.11 All that is documented with CREATE TABLE: https://www.postgresql.org/docs/current/sql-createtable.html "How to use" is material for a tutorial rather than for the documentation. Yours, Laurenz Albe
Re: Table rewrite supporting functions for event triggers
On Tue, 2024-09-03 at 11:54 -0400, Greg Sabino Mullane wrote: > How about something like this? This patch looks good to me. Yours, Laurenz Albe
Re: Table rewrite supporting functions for event triggers
On Mon, 2024-09-02 at 17:15 -0400, Greg Sabino Mullane wrote: > While looking over the event trigger docs, I noticed that the sample code > references > the two special table rewrite functions (returning oid and reason for the > rewrite), > but the event trigger page itself does not mention them, although it does > mention > the functions available for the other types of event triggers (e.g. > pg_event_trigger_ddl_commands). > Please find attached a patch to remedy this, including the meaning of the int > values > (which, while subject to change, seems worth documenting here rather than > hand-waving > it away as func.sgml does) I think that it would be better to add a reference to https://www.postgresql.org/docs/16/functions-event-triggers.html#FUNCTIONS-EVENT-TRIGGER-TABLE-REWRITE than to repeat that information. If you feel that "The exact meaning of the codes is release dependent" is unnecessarily vague, that sentence should be changed. Your proposed description leaves me a bit clueless: +To find the OID of the table that was rewritten, use the function +pg_event_trigger_table_rewrite_oid(). To discover the +reason for the rewrite, use the function +pg_event_trigger_table_rewrite_reason(). This function returns +an integer representing a bitmap of reasons for the rewrite. The current values +are 1 (the table has changed persistence), 2 (a column has changed a default value), +3 (a column has a new data type), and 4 (the table access method has changed). A "bitmap of reasons" to me would mean that each reason is a bit, and if two reasons apply at the same time, both bits are set. But that's clearly not what you mean, because "a column has a new data type" is not the same as "the table has changed persistence" and at the same time "a column has changed a default value". Perhaps "a bitmap of reasons" should simply become "the reason". Yours, Laurenz Albe
Re: Incorrect grammar in 15.2
On Wed, 2024-07-24 at 05:11 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/when-can-parallel-query-be-used.html > Description: > > Unless I'm misunderstanding the sentence structure here, I believe the word > "a" is missing before "parallel query plan" in the following sentence from > section 15.2: > > > Even when parallel query plan is generated for a particular query, there > are several circumstances under which it will be impossible to execute that > plan in parallel at execution time. > > This should say: > > > Even when a parallel query plan is generated for a particular query, there > are several circumstances under which it will be impossible to execute that > plan in parallel at execution time. +1 Here is a patch for that; it may be the smallest patch I've ever written for PostgreSQL. Yours, Laurenz Albe From 68eedd01c754ffd2dfbb11cd29f568841d7db7a6 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Wed, 24 Jul 2024 12:50:49 +0200 Subject: [PATCH v1] Fix a missing article in the documentation Per complaint from Grant Gryczan. --- doc/src/sgml/parallel.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml index dae9dd7f2f..590cb385dd 100644 --- a/doc/src/sgml/parallel.sgml +++ b/doc/src/sgml/parallel.sgml @@ -202,7 +202,7 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%'; -Even when parallel query plan is generated for a particular query, there +Even when a parallel query plan is generated for a particular query, there are several circumstances under which it will be impossible to execute that plan in parallel at execution time. If this occurs, the leader will execute the portion of the plan below the Gather -- 2.45.2
Re: 13.2.1. Read Committed Isolation Level
On Sun, 2024-07-14 at 06:17 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/transaction-iso.html > Description: > > I don't understend this text. > > [five paragraphs from the documentation] > > Could you please describe this behavior in more detail? It is difficult to help you if you are that unspecific about what exactly you fail to understand. Sure, there are some complicated concepts involved. If you can't understand *anything* about that text, perhaps you should start reading the whole chapter about concurrency. Yours, Laurenz Albe
Re: Add small detail to RAISE statement descripton
On Mon, 2024-07-15 at 21:30 +0300, Igor Gnatyuk wrote: > I tried to take your comments into account: > > - added a missing comma > - still left a verbose version ;) > - made the last sentence more complete (according to the 2nd option). > > Attached you will find new patch version fix_doc_raise_v4.patch, > please check it out. Thanks. I have marked the patch as "ready for committer". Yours, Laurenz Albe
Re: Add small detail to RAISE statement descripton
On Wed, 2024-05-22 at 18:34 +0300, Igor Gnatyuk wrote: > fix_doc_raise_v3-bios.patch. Please check it out. I think the patch is fine. > + > +condition_name and > +sqlstate specify > +error condition name and the five-character SQLSTATE code respectively. > +See for more information. > + There should be a comma before "respectively". > You can attach additional information to the error report by writing > USING followed by class="parameter">option = -class="parameter">expression items. Each > +class="parameter">expression or > +option := > +expression > +items, where > expression can be any > string-valued expression. I think that is unnecessarily verbose. The original wording was fine; everybody can see from the syntax diagram that you can also use := But I won't fight over it. > +In the RAISE command syntax with > +condition_name or > +sqlstate you can > +additionally use the USING clause too. > +A variation of the example above: I think that the final sentence should be more complete. Suggestions: Here is a variation of the above example: A variation of the above example is: Yours, Laurenz Albe
Re: COALESCE documentation
On Wed, 2024-07-03 at 11:42 +0200, Navrátil, Ondřej wrote: > On a side note, I tested similar behavior in Oracle databases, and for them, > something like > select testtype(null, null) is null; -- returns 0 (false) > select testtype(null, null) is not null; -- returns 1 (true) > ...and as far as I could test, in Oracle the IS NULL and IS NOT NULL > operators are truly dual That only goes to say that Oracle is not very standard compliant, but I wouldn't expect anything else from a system where '' IS NULL. Yours, Laurenz Albe
Re: COALESCE documentation
On Wed, 2024-07-03 at 11:00 +0200, Peter Eisentraut wrote: > On 02.07.24 12:45, Navrátil, Ondřej wrote: > > as per documentation > > <https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL> > > > The |COALESCE| function returns the first of its arguments that is > > not null. Null is returned only if all arguments are null. > > > > This is not exactly true. In fact: > > The |COALESCE| function returns the first of its arguments that *is > > distinct* *from *null. Null is returned only if all arguments *are not > > distinct from* null. > > > > See my stack overflow question here > > <https://stackoverflow.com/questions/78691097/postgres-null-on-composite-types>. > > > > Long story short > > > > > select coalesce((null, null), (10, 20)) as magic; | > > > > returns > > > > > magic --- (,) (1 row)| > > > > However, this is true: > > > > > select (null, null) is null;| > > I think this is actually a bug in the implementation, not in the > documentation. That is, the implementation should behave like the > documentation suggests. You are right. I find this in the standard: COALESCE (V1, V2) is equivalent to the following : CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END That would mean that coalesce(ROW(1,NULL), ROW(2,1)) should return the second argument. Blech. I am worried about the compatibility pain such a bugfix would cause... Yours, Laurenz Albe
Re: COALESCE documentation
On Tue, 2024-07-02 at 12:45 +0200, Navrátil, Ondřej wrote: > as per documentation > > The COALESCE function returns the first of its arguments that is not null. > > Null is returned only if all arguments are null. > > This is not exactly true. In fact: > The COALESCE function returns the first of its arguments that is distinct > from null. Null is returned only if all arguments are not distinct from null. +1 Do you want to write a documentation patch? Yours, Laurenz Albe
Re: 8.12. UUID Type
On Thu, 2024-06-13 at 08:59 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/datatype-uuid.html > Description: > > Is this the right sentence? "use of upper-case digits" > > > PostgreSQL also accepts the following alternative forms for input: use of > > > upper-case digits Hexadecimal digits, also known as letters. Yours, Laurenz Albe
Re: Suggestion about tcp_keepalives_idle parameter in the document
On Sat, 2024-06-08 at 05:37 +0800, yanliang lei wrote: > there is the following description in the > https://www.postgresql.org/docs/current/runtime-config-connection.html： > > A value of 0 (the default) selects the operating system's default. > but there is no description about“ the operating system's default > value in the linux is tcp_keepalive_time kernel parameter” Documenting the defaults for each supported operating system is not PostgreSQL's task. In this article[1], I figured out the defaults for Linux, MacOS and Windows. Yours, Laurenz Albe [1]: https://www.cybertec-postgresql.com/en/tcp-keepalive-for-a-better-postgresql-experience/#defaults
Re: Need clarification on "field"
On Tue, 2024-06-04 at 19:54 +, PG Doc comments form wrote: > Under page "https://www.postgresql.org/docs/current/limits.html";, below > limitations are mentioned: > > field size - 1 GB > identifier length - 63 bytes > > I understand "identifier" as the name we provide for tables, columns etc. > > By the way, what is "field" in Postgresql? I don't see any official > page/explanation for this. The PostgreSQL term would be "attribute". Perhaps we should use that. Alternatively, what about "column value"? It is perhaps not accurate, because a Datum need not be stored in a column, but it might be readily understandable. Yours, Laurenz Albe
Re: Avoid too prominent use of "backup" on pg_dump man page
On Thu, 2024-05-30 at 08:21 +0200, Daniel Gustafsson wrote: > No objections to using export over backup, but it does make the use of > "restore" feel awkward as that's generally an operation on a backup and not an > export. > > - least one schema/table in the backup file. > + least one schema/table in the file to be restored. > > Would it make sense to use "import" in some cases instead? What about calling it "dump file" instead of "file to be restored"? Yours, Laurenz Albe
Re: Ambiguous description on new columns
On Wed, 2024-05-22 at 12:47 +1000, Peter Smith wrote: > I think the following small change will remove any ambiguity: > > BEFORE > If no column list is specified, any columns added later are > automatically replicated. > > SUGGESTION > If no column list is specified, any columns added to the table later > are automatically replicated. > > ~~ > > I attached a small patch to make the above change. +1 on that change. Yours, Laurenz Albe
Re: 52.38 pg_proc (postgresql version 14)
On Mon, 2024-05-20 at 21:07 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/14/catalog-pg-proc.html > Description: > > I don't know if this is a documentation problem or an error in pg_proc. > > The document, for the "prokind" column state the following: > f for a normal function, p for a procedure, a for an aggregate > function, or w for a window function. > > However, there "f" is used for *both* functions and procedures. I cannot reproduce that: CREATE PROCEDURE noop() LANGUAGE plpgsql AS 'BEGIN NULL; END;'; SELECT prokind FROM pg_proc WHERE proname = 'noop'; prokind ═ p (1 row) Yours, Laurenz Albe
Re: Connection Info
On Sun, 2024-05-05 at 20:25 +, PG Doc comments form wrote: > On this page: > https://www.postgresql.org/docs/14/server-start.html#CLIENT-CONNECTION-PROBLEMS > > It notes: " A common mistake is to forget to configure the server to > allow TCP/IP connections." > > I would expect a hyperlink to a section explaining exactly HOW to "configure > the server to allow TCP/IP connections" - from that note text. I agree. Perhaps it should say: A common mistake is to forget to configure "listen_addresses" so that the server accepts remote TCP connections. Then "listen_addresses" could hyperlink to the parameter's documentation. Yours, Laurenz Albe
Re: roles that have the CREATEROLE privilege can no longer GRANT predefined roles
On Wed, 2024-05-01 at 16:09 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/predefined-roles.html > > roles that have the CREATEROLE privilege can no longer GRANT predefined > roles unless they are part of it having the WITH ADMIN option. this needs to > be corrected in the documentation I see what you mean. This text: Administrators (including roles that have the CREATEROLE privilege) can GRANT these roles to users and/or other roles ... should probably become Administrators (including roles that have the CREATEROLE privilege and have been granted the predefined role with the ADMIN option) can GRANT these roles to users and/or other roles ... Yours, Laurenz Albe
Re: 8.14.5 jsonb subscripting
On Tue, 2024-04-09 at 09:31 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/datatype-json.html
>
> Using subscripts with square brackets is not supported, one must use -> or
> #> operators
> The given examples result in error:
>
> SELECT ('{"a": 1}'::jsonb)['a'];
>
> -- Extract nested object value by key path
> SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
>
> -- Extract array element by index
> SELECT ('[1, "2", null]'::jsonb)[1];
You must be using an old PostgreSQL version where that is not yet supported.
Yours,
Laurenz Albe
Re: LOCK docs and permissions
On Fri, 2024-04-05 at 10:25 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/explicit-locking.html > Description: > > It appears that LOCK on a schema.table requires particular permissions but I > can’t find which ones need to be granted in a schema.table in order to lock > said table. > > I think it would help to add a short blurb on that? https://www.postgresql.org/docs/current/sql-lock.html#id-1.9.3.155.7 "To lock a table, the user must have the right privilege for the specified lockmode, or be the table's owner or a superuser. If the user has UPDATE, DELETE, or TRUNCATE privileges on the table, any lockmode is permitted. If the user has INSERT privileges on the table, ROW EXCLUSIVE MODE (or a less-conflicting mode as described in Section 13.3) is permitted. If a user has SELECT privileges on the table, ACCESS SHARE MODE is permitted." Yours, Laurenz Albe
Re: Discourage splitting pg_wal directory
On Tue, 2024-03-19 at 11:04 -0400, Greg Sabino Mullane wrote: > On Mon, Mar 18, 2024 at 11:58 AM Laurenz Albe > wrote: > > I think it is still a good idea to put data files and WAL on different file > > systems. Perhaps not so much with the intention of distributing I/O across > > different disks, but to prevent the data files from filling the WAL disk. > > Cannot say I understand this - if the data files are crowding out the WAL, > surely they are moments away from filling the disk on their own. Disk is cheap > these days, so I'm not a fan of encouraging complexity if not needed. If you run out of disk on the data files, you get an error and life goes on. If WAL runs out of disk space, PostgreSQL will crash. Yours, Laurenz Albe
Re: Duplicates being removed from intarray on subtraction of another intarray
On Mon, 2024-03-18 at 08:21 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/intarray.html
>
> Hi,
> I recently ran into an unusual issue with the intarray extension where if
> you subtract one array from another the result is *also* sorted and
> de-duplicated. The documentation does not seem to imply that this should be
> the case, stating only that the operator "removes elements of the right
> array from the left array" and not that it also de-duplicates and sorts the
> result... It seems to only occur when subtracting an array. Is this the
> intended behavior?
>
> SELECT '{3,1,1,2,2,2}'::int[] - 1; --> {3,2,2,2} as you would expect
> SELECT '{3,1,1,2,2,2}'::int[] - '{1}'::int[]; --> {2,3} instead of
> {3,2,2,2}
There is no harm in documenting that; I propose the attached patch.
Yours,
Laurenz Albe
From 96a7fed7be768c047f8a74a4431716da0beb0e28 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Mon, 18 Mar 2024 16:59:43 +0100
Subject: [PATCH v1] Documentation fix for intarray's "-" operator
Document that int[] - int[] removes duplicates as well as
elements of the right array.
Discussion: https://postgr.es/m/171075007381.7104.7931589808177869854%40wrigleys.postgresql.org
---
doc/src/sgml/intarray.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/intarray.sgml b/doc/src/sgml/intarray.sgml
index c72d49b01d..ec1d525042 100644
--- a/doc/src/sgml/intarray.sgml
+++ b/doc/src/sgml/intarray.sgml
@@ -312,7 +312,7 @@
integer[]
-Removes elements of the right array from the left array.
+Removes elements of the right array and duplicate entries from the left array.
--
2.44.0
Re: Discourage splitting pg_wal directory
On Mon, 2024-03-18 at 11:32 -0400, Greg Sabino Mullane wrote: > "It is advantageous if the WAL is located on a different disk from the > main database files. This can be achieved by moving the pg_wal directory > to another location (while the server is shut down, of course) and > creating a symbolic link from the original location in the main data > directory to the new location." > > This is not as important as it used to be, and I would even hazard to > say that we should not be encouraging it. There are still use cases for > doing so, yes, but "advantageous" is too much. I played with some wording, > but at the end of the day, I am thinking that we simply remove this > paragraph entirely. I think it is still a good idea to put data files and WAL on different file systems. Perhaps not so much with the intention of distributing I/O across different disks, but to prevent the data files from filling the WAL disk. Yours, Laurenz Albe
Re: Locking
On Sun, 2024-03-17 at 15:11 +1000, Anthony Berglas wrote: > I would write something iff there is interest in posting it. I cannot promise that the patch would be accepted; I personally think that briefly mentioning transaction anomalies and isolation levels would be ok. > Probably an overview in the Introduction to Locking section. I don't know exactly which section you mean, but it should definitely be part of the tutorial, in chapter 3.4. Appendix J contains some information about building the documentation from source. The patch should be against the current development version. Yours, Laurenz Albe
Re: Locking
On Sat, 2024-03-16 at 10:52 +1000, Anthony Berglas wrote: > The code is wrong by any normal definition. We lose one of the updates. > It is a very common error. It is a very common way to write code, > especially if using an ORM, which is very common. > > [example of a lost update] > > The problem is that this fundamental problem is lost in all the escoteric > details of locking. > > Sure, a database expert that carefully studies the docs might figure it > out if they did not already know it. But the other 99.9% of users will > just consider Postgresql to be buggy because balances do not add up. > > So I think something in the docs is very much necessary. Yes, the "lost update" is a common and well-known transaction anomaly, and every developer should know about it. What you are looking for is a tutorial about database transactions. There are fundamental differences between a tutorial and documentation. A tutorial is an example-based introduction aimed at beginners, while the documentation describes the behavior in greater detail, aiming for rigorourness and completeness. Now there is a tutorial inside the PostgreSQL documentation, and it even has a chapter about transactions: https://www.postgresql.org/docs/current/tutorial-transactions.html It even talks some about transaction isolation, but doesn't go as far as mentioning anomalies and the individual isolation levels. Perhaps you feel inspired to write a patch for that page that demonstrates the lost update and shows how to avoid it using the REPEATABLE READ isolation level? Yours, Laurenz Albe
Re: Locking
On Thu, 2024-03-14 at 00:16 +, PG Doc comments form wrote: > There really needs to be an explicit warning that the following is invalid > in normal read committed mode:- > > select foo into f from bar where id=1; > f = f + 123; > update bar set foo = f where id =1; > commit; > > This is a very common and serious mistake and extremely difficult to > understand from the current documentation. There is nothing invalid in the code sample you are showing. If you are talking about lost updates, that is described on https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client. Yours, Laurenz Albe
Re: Definition of random_page_cost seems to invert correlation of that setting and seq_page_cost
On Mon, 2024-03-11 at 12:30 +, PG Doc comments form wrote: > While reading the following section in the documentation > https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST > I found something which looks incorrect to me. > > If I understand this setting correctly, the following sentence: > > "Reducing this value relative to seq_page_cost will cause the system to > prefer index scans; raising it will make index scans look relatively more > expensive." > > should rather be: > > "Raising this value relative to seq_page_cost will cause the system to > prefer index scans; reducing it will make index scans look relatively more > expensive." The documentation is correct. If the cost of a random I/O operation is higher, index scans look more expensive, because they perform random I/O. Yours, Laurenz Albe
Re: Issue with interval calculation when adding 1 year to a non-leap year
On Fri, 2024-03-08 at 07:57 +, PG Doc comments form wrote:
> I have encountered a potential issue with the interval calculation when
> adding 1 year to a non-leap year. The behavior observed is that when adding
> 1 year to a date in a non-leap year such as 2023, the resulting date is
> incorrectly set to February 28th instead of February 29th in the following
> leap year, which should be 2024.
>
> This behavior is inconsistent with the expected behavior, as it does not
> account for leap years when performing date arithmetic using intervals.
>
> Please find below a sample query illustrating the issue:
> SELECT ('2023-02-28 23:59:59')::timestamp + INTERVAL '1 year';
>
> Expected result: '2024-02-29 23:59:59'
> Actual result: '2024-02-28 23:59:59'
If somebody tells me to meet again on the same day one year from
now on Feb 28, I would show up on Feb 28 the next year.
I understand that you are thinking of "the last day of the month",
but with that reasoning you could say that
2023-02-27 00:00:00 + 1 year = 2024-02-28 00:00:00
or indeed
2023-02-01 00:00:00 + 1 year = 2024-02-02 00:00:00
Somewhat in favor of your interpretation is
SELECT '2024-02-29 12:00:00'::timestamp - '1 year'::interval;
?column?
═
2023-02-28 12:00:00
(1 row)
So we have
2024-02-29 12:00:00 - 1 year + 1 year != 2024-02-29 12:00:00
and indeed
2024-02-29 12:00:00 - 1 year = 2024-02-28 12:00:00 - 1 year
I'd say that there is simply no way to make all this consistent,
and the current implementation is what I would intuitively expect.
Yours,
Laurenz Albe
Re: Fix analyze_sampling docs in postgres-fdw.sgml
On Mon, 2024-03-04 at 14:27 +0900, Shinya Kato wrote: > I fixed analyze_sampling docs in postgres-fdw.sgml. > - Changed analyze_sampling type 'text' to 'string' +1 > - Add a statement 'The option specified on a table overrides an option > specified for the server.' I think that applies to all options. It would be better to have a general statement to that effect at the beginning of the "Options" section. Yours, Laurenz Albe
Re: Missing | ?
On Mon, 2024-02-19 at 09:37 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/sql-security-label.html > > FOREIGN TABLE object_name > > ...probably should have a following "|", I think? Absolutely! Yours, Laurenz Albe
Re: 20.5.1
On Wed, 2024-02-07 at 09:59 -0700, David G. Johnston wrote: > 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/runtime-config-wal.html > > Description: > > > > The sentence in commit_siblings "A larger value makes it more probable that > > at least one other transaction will become ready to commit during the delay > > interval." seems to belong in commit_delay instead. > > > > That sentence in that location is correct. See “birthday paradox”. To be more precise: if 15 other transactions are currently running, there is a bigger chance that at least one of them will want to flush WAL before "commit_delay" has expired than if there are only 3 other transactions. Yours, Laurenz Albe
Re: create table explicitly mention that unique|primary key constraint will create an
On Wed, 2024-01-31 at 12:11 +0100, Peter Eisentraut wrote: > Sprinkled in some of David's suggestions, and pushed. Thanks; your text is great. Yours, Laurenz Albe
Re: SET ROLE versus SET SESSION AUTHORIZATION
On Tue, 2024-01-30 at 17:25 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/sql-set-role.html > Description: > > "SET ROLE has effects comparable to SET SESSION AUTHORIZATION, but the > privilege checks involved are quite different." > > What are the differences in privilege checks? > > How are the two commands the same and different, semantically? The difference is revealed by "SELECT session_user". I think that is pretty clearly described in the SET SESSION AUTHORIZATION documentation page. Yours, Laurenz Albe
Re: create table explicitly mention that unique|primary key constraint will create an
On Fri, 2024-01-26 at 19:01 +0530, vignesh C wrote: > CFBot shows that the patch does not apply anymore as in [1]: There was a conflict with 46a0cd4cefb. Updated version attached. Yours, Laurenz Albe From 193d6d6c20f0c2976e0b63f1896978545def3fe8 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 26 Jan 2024 16:50:11 +0100 Subject: [PATCH v4] Doc: All relations share a namespace This was already documented in the CREATE INDEX reference, but not in the introductory "Data Definition" chapter. Also, document that the index that implements a constraint has the same name as the constraint. Author: Laurenz Albe Reviewed-by: Jian He, Peter Eisentraut, David G. Johnston Discussion: https://postgr.es/m/CACJufxFG682tYcP9aH_F-jrqq5End8MHZR77zcp1%3DDUrEsSu1Q%40mail.gmail.com --- doc/src/sgml/ddl.sgml | 6 +- doc/src/sgml/ref/create_table.sgml | 8 +--- 2 files changed, 10 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..434e074df1 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3003,7 +3003,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. The same + 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 object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 079af9126a..bdc3541930 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1027,7 +1027,8 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns used in the constraint. But if the constraint includes a WITHOUT OVERLAPS clause, it - will use a GiST index. + will use a GiST index. The created index has the same name as the unique + constraint. @@ -1082,6 +1083,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY constraint will automatically create a unique B-tree index, or GiST if WITHOUT OVERLAPS was specified, on the column or group of columns used in the constraint. + That index has the same name as the primary key constraint. @@ -1118,8 +1120,8 @@ WITH ( MODULUS numeric_literal, REM Exclusion constraints are implemented using - an index, so each specified operator must be associated with an - appropriate operator class + an index that has the same name as the constraint, so each specified + operator must be associated with an appropriate operator class (see ) for the index access method index_method. The operators are required to be commutative. -- 2.43.0
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, 2024-01-24 at 15:26 +0100, Daniel Gustafsson wrote: > > On 24 Jan 2024, at 15:23, 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. > > > > Doesn't seem unreasonable to me, it won't make the docs harder to read and use > for experienced users while it may make them easier to follow for new users. Here is a patch for this change. Yours, Laurenz Albe From 3685b2ce9d921857d629bd20d49b1acfd5f01576 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 26 Jan 2024 12:01:37 +0100 Subject: [PATCH v1] Document effects of ownership change on privileges Privileges have always been transferred along with the ownership, but it is a good idea to document that. Per complaint by Gilles Parc. Author: Laurenz Albe Reviewed-by: Daniel Gustafsson, David G. Johnston Discussion: https://postgr.es/m/2023185982.281851219.1646733038464.JavaMail.root%40zimbra15-e2.priv.proxad.net --- doc/src/sgml/ddl.sgml | 2 ++ 1 file changed, 2 insertions(+) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..835ebd5a67 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1893,6 +1893,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. -- 2.43.0
Re: It would be nice to clarify is there any point in select queries pipelining
On Thu, 2024-01-25 at 04:46 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/protocol-flow.html > > Please consider the following exchange with a PG database (Kotlin + Reactor+ > r2dbc-postgresql): > > [trace of a pipelined message flow] > > The application code is the following (for the sake of clarity): > > [some Java code] > > I’m aware that Postgres doesn’t support cursor(portal) multiplexing so its > impossible to fetch data for two selects simultaneously but should I make > use of selects pipelining when I want to make the most of the DB? I mean to > say does a single Postgres backend employs concurrent processing and its > possible for a DB to transmit results for a query #2 (see the traffic) and > concurrently parse/rewrite/plan/optimize the subsequent (pipelined) queries > (issued from the same transaction). Neither chapter 55 nor 52 expand on it > (or it’s hard to spot at least). No, PostgreSQL is single-threaded (with the exception of parallel query execution). Yours, Laurenz Albe
Re: SQL command : ALTER DATABASE OWNER TO
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. > > 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. > Ok, you want to describe that in more detail. But you should preserve the when the term is used for the first time. > > - There are different kinds of privileges: SELECT, > + There are different kinds of grantable privileges: > SELECT, > INSERT, UPDATE, > DELETE, > TRUNCATE, REFERENCES, > TRIGGER, > CREATE, CONNECT, > TEMPORARY, We use "privilege" for the rights you can grant everywhere else, so you'd have to change that all over the documentation. I don't think we should randomly change our terminology. I see that you didn't change the . I understand that you want to disambiguate between "grantable privileges" and "inherent privileges" like the right to drop an object. 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. > @@ -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. > + > + Perhaps it would be better to say "has the privileges" than "is granted the privileges", because there is nothing specifically granted (the ACL is NULL). Also, I don't think we should burden the user with ALTER DEFAULT PRIVILEGES right here, where we speak about ownership. Default privileges are discribed in some detail further down, which I believe is sufficient. In the same vein, the page describes further down that "An object's owner can choose to revoke their own ordinary privileges, for example ...". Again, no need to mention it another time (before it is discussed). > > 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 .) > See above. > + > + 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. > + > + Again, that's mostly a repetition: "Ordinarily, only the object's owner (or a superuser) can grant or revoke privileges on an object." > @@ -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. > This change is fundamentally OK, although I doubt that we need to get as detailed as to how multiple access control items get consolidated. I think we should say "owner" instead of "recorded owner". Also, is it necessary to detail to the level of system catalog columns? Yours, Laurenz Albe
Re: SQL command : ALTER DATABASE OWNER TO
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) Yours, Laurenz Albe
Re: SQL command : ALTER DATABASE OWNER TO
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). Yours, Laurenz Albe
Spam complaint
For months now, I have received this automated reply whenever I post to this list: Forwarded Message From: 471266196 <471266...@qq.com> To: Laurenz Albe Subject: 自动回复: Re: SQL command : ALTER DATABASE OWNER TO Date: 01/24/2024 03:23:31 PM 邮件已收到，我会尽快处理。谢谢！ --- Seems like that person will never return to the office. Could someone please unsubscribe that address? Yours, Laurenz Albe
Re: SQL command : ALTER DATABASE OWNER TO
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. Yours, Laurenz Albe
Re: create table explicitly mention that unique|primary key constraint will create an
On Thu, 2024-01-18 at 15:54 +0100, Peter Eisentraut wrote: > On 27.11.23 03:30, Laurenz Albe wrote: > > True; I don't find it documented that all objects in pg_class share a > > namespace and that constraints are implemented by indexes of the same > > name. But I think that the first part is a property of schemas and had > > better be documented there. > > It is documented prominently on the CREATE INDEX reference page. We > could document it in more places, of course. 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. > > The changes on create_table.sgml seem ok. Although I had actually > expected that the system applies the find-a-unique-name routine rather > than taking the constraint name for the index name unaltered. > > Perhaps taking the create_table.sgml changes and combination with the > existing text on CREATE INDEX is sufficient. Ah, I didn't see the CREATE INDEX page. (As an aside: too much conceptual stuff is documented in our reference pages, but that's a different issue.) For me, the intuitive place to look for information like that is the "Data Definition" chapter, so I think we should mention it there. I agree that "pg_class" is too advanced for that chapter, even though there is an earlier reference to it under "System Columns". 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. The rest is unmodified. Yours, Laurenz Albe From b85ccf59ad0575e5040b17bbed5d2e152217df47 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 19 Jan 2024 11:37:32 +0100 Subject: [PATCH v3] Doc: All relations share a namespace This was already documented in the CREATE INDEX reference, but not in the introductory "Data Definition" chapter. Also, document that the index that implements a constraint has the same name as the constraint. Author: Laurenz Albe Reviewed-by: Jian He, Peter Eisentraut, David G. Johnston Discussion: https://postgr.es/m/CACJufxFG682tYcP9aH_F-jrqq5End8MHZR77zcp1%3DDUrEsSu1Q%40mail.gmail.com --- doc/src/sgml/ddl.sgml | 6 +- doc/src/sgml/ref/create_table.sgml | 9 + 2 files changed, 10 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4044f0908f..13831667fe 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3001,7 +3001,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. The same + 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 object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e04a0692c4..e334b17672 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1001,7 +1001,8 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. + index on the column or group of columns used in the constraint. That + index has the same name as the unique constraint. @@ -1054,7 +1055,7 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in the - constraint. + constraint. That index has the same name as the primary key constraint. @@ -1091,8 +1092,8 @@ WITH ( MODULUS numeric_literal, REM Exclusion constraints are implemented using - an index, so each specified operator must be associated with an - appropriate operator class + an index that has the same name as the constraint, so each specified + operator must be associated with an appropriate operator class (see ) for the index access method index_method. The operators are required to be commutative. -- 2.43.0
Re: CREATE ROLE inheritance details
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. Yours, Laurenz Albe
Re: Table-space documentation
On Sun, 2023-11-26 at 21:41 +, 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. There is no maximum tablespace size, other than the size of the filesystem the tablespace is on. In 99% of all cases, don't create a tablespace in PostgreSQL. Just use the default tablespace. In a virtualized environment, don't ever create a tablespace. Tablespaces were more important back what people had physical boxes with physical disks of limited size. There are still some use cases, but they are extremely rare. Yours, Laurenz Albe
Re: create table explicitly mention that unique|primary key constraint will create an
On Mon, 2023-11-27 at 08:00 +0800, jian he wrote: > Hi. minor doc issue. > create table s1(a int, constraint s2 PRIMARY key (a)); > create table s2(a int); > ERROR: relation "s2" already exists > > https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE > maybe for the following 2 sentence > "Adding a unique constraint will automatically create a unique btree > index on the column or group of columns used in the constraint." > "Adding a PRIMARY KEY constraint will automatically create a unique > btree index on the column or group of columns used in the constraint." > > maybe we can mention that: the unique btree index name will be the > constraint name. > also is "a unique" or "an unique"? It would be "a unique", because "unique" is pronounced "juneek", which does not start with a vowel. > I personally thought this part is obscure. True; I don't find it documented that all objects in pg_class share a namespace and that constraints are implemented by indexes of the same name. But I think that the first part is a property of schemas and had better be documented there. What do you think of the attached patch? Yours, Laurenz Albe From dc38339a9348ab4855600f1bbcd79e7712c21780 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Mon, 27 Nov 2023 03:25:41 +0100 Subject: [PATCH] Doc: objects in pg_class share a namespace Explicitly state that tables, indexes, sequences and other relations share a single namespace. Also, document that the index that implements a constraint has the same name as the constraint. --- doc/src/sgml/ddl.sgml | 7 ++- doc/src/sgml/ref/create_table.sgml | 9 + 2 files changed, 11 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4490e82aa5..46df1dd344 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2884,7 +2884,12 @@ 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 + objects, including data types, functions, and operators. Within one schema, + two objects of the same type cannot have the same name. Tables, indexes, + sequences (and all other objects stored in + pg_class) + share one name space, so they need to have different names if they are in + a single 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, diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e04a0692c4..e334b17672 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1001,7 +1001,8 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. + index on the column or group of columns used in the constraint. That + index has the same name as the unique constraint. @@ -1054,7 +1055,7 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in the - constraint. + constraint. That index has the same name as the primary key constraint. @@ -1091,8 +1092,8 @@ WITH ( MODULUS numeric_literal, REM Exclusion constraints are implemented using - an index, so each specified operator must be associated with an - appropriate operator class + an index that has the same name as the constraint, so each specified + operator must be associated with an appropriate operator class (see ) for the index access method index_method. The operators are required to be commutative. -- 2.43.0
Re: [DOCS] Confusing Trigger Docs.
On Fri, 2023-11-24 at 13:14 -0500, Bruce Momjian wrote: > On Thu, Nov 23, 2023 at 08:36:34AM +0100, Laurenz Albe wrote: > > On Wed, 2023-11-22 at 14:49 -0800, Peter Geoghegan wrote: > > > I don't think that your proposed wording for this is an improvement. > > > > Well, the existing wording is impenetrable even for someone with some > > PostgreSQL knowledge, like me. > > I moved the parition pagagraph to a more logical location and tried to > clarify the new paragraph to be more targeted on the goal, patch > attached. I cannot tell if that covers everything that the original text (which I failed to understand) did, but your wording makes sense to me. Yours, Laurenz Albe
Re: [DOCS] Add example about date ISO format
On Fri, 2023-11-24 at 12:29 -0500, Bruce Momjian wrote: > On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote: > > On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote: > > > Okay, I moved it into the "Note" section that talked about ISO 8601 > > > output with "T", in the attached patch. > > > > Fine by me, except that I would rather have "returns" or "produces" > > instead of the questionable verb "outputs". > > The majority of people seem to want it in table 9.31, so I have moved it > there. It does almost double the width of the displayed table though. > You can see the new output here: > > > https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE > > Patch attached. Looks good to me. Yours, Laurenz Albe
Re: [DOCS] Add example about date ISO format
On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote: > Okay, I moved it into the "Note" section that talked about ISO 8601 > output with "T", in the attached patch. Fine by me, except that I would rather have "returns" or "produces" instead of the questionable verb "outputs". Yours, Laurenz Albe
Re: [DOCS] Confusing Trigger Docs.
On Wed, 2023-11-22 at 14:49 -0800, Peter Geoghegan wrote: > I don't think that your proposed wording for this is an improvement. Well, the existing wording is impenetrable even for someone with some PostgreSQL knowledge, like me. Yours, Laurenz Albe
Re: [DOCS] Confusing Trigger Docs.
On Tue, 2023-11-21 at 21:01 -0500, Bruce Momjian wrote: > On Thu, Aug 31, 2017 at 09:22:22AM -0700, Peter Geoghegan wrote: > > On Thu, Aug 31, 2017 at 6:25 AM, Bruce Momjian wrote: > > > On Mon, Jul 3, 2017 at 08:07:10PM +, n...@fairwindsoft.com wrote: > > > > The following documentation comment has been logged on the website: > > > > > > > > Page: https://www.postgresql.org/docs/9.6/static/trigger-definition.html > > > > Description: > > > > > > > > https://www.postgresql.org/docs/devel/static/trigger-definition.html > > > > > > > > This sentence: > > > > > > > > "If an INSERT contains an ON CONFLICT DO UPDATE clause, it is > > > > possible that > > > > the effects of all row-level BEFORE INSERT triggers and all row-level > > > > BEFORE > > > > UPDATE triggers can both be applied in a way that is apparent from the > > > > final > > > > state of the updated row, if an EXCLUDED column is referenced." > > > > > > > > is very hard to digest. > > > > EXCLUDED.* is exactly what the name suggests -- the tuple that was not > > inserted because of a conflict. So, naturally it has the effects of > > any before insert trigger, and carries them forward. But you still > > have before triggers on the update side. > > > > Typically, this won't matter at all, because before triggers tend to > > be written in an idempotent fashion -- something gets filled in. But I > > can imagine cases where it is not idempotent, and apply a before > > update trigger modifies the row in a way that is surprising. Just > > because ON CONFLICT DO UPDATE was used rather than UPDATE. That's what > > the documentation warns about. > > I know this thread is six years old, but I still found it confusing, so > the attached patch tries to simplify it. 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. Yours, Laurenz Albe
Re: [DOCS] intagg.sgml: example wrongly named and does not compile
On Tue, 2023-11-21 at 22:27 -0500, Bruce Momjian wrote: > I like this six year old patch so would like to apply it to master, > attached. +1, since it is arguably a bug fix. Yours, Laurenz Albe
Re: [DOCS] Add example about date ISO format
On Tue, 2023-11-21 at 23:33 -0500, Bruce Momjian wrote: > On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@iki.fi wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html > > Description: > > > > The documentation should include an example how to format datetime entry > > into most commonly known ISO format. This is a bit tricky as literal > > character needs to included with quotes: > > > > to_char(NOW(), '-MM-DD"T"HH24:MI:SSZ') > > I know this is a six-year-old idea, but it is still a good one. I have > developed the attached patch I would like to apply to master. +1 on the idea, but from the context it looks like you added that example at the regular expression matching functions. I think the example had best be at "8.5.2. Date/Time Output", in doc/src/sgml/datatype.sgml around line 2552. Yours, Laurenz Albe
Re: Additional Notes
On Wed, 2023-11-15 at 17:38 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/sql-notify.html > Description: > > It would be good to add to the notes section that use of NOTIFY especially > within a TRIGGER requires an AccessExclusiveLock which may cause performance > issues. Old thread for reference: > https://www.postgresql.org/message-id/3598.1363354686%40sss.pgh.pa.us I don't see what this has to do with triggers. Even deferred triggers run *before* this notify lock is taken. The only possibility I see for such a lock to be held for a long time is if COMMIT spends a long time waiting for a reply from a synchronous standby server. Is that your problem? I don't think that would require special documentation, because if your synchronous standby does not respond in time, you normally have worse problems than NOTIFY performance. Yours, Laurenz Albe
Re: Documentation of return values of range functions lower and upper
On Mon, 2023-11-13 at 16:08 -0500, Bruce Momjian wrote: > Backpatched to PG 16. Thanks! Yours, Laurenz Albe
Re: CREATE SUBSCRIPTION issue
On Wed, 2023-11-08 at 12:26 +, PG Doc comments form wrote: > 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. That is not supported yet. You cannot do that. Yours, Laurenz Albe
Re: Another user complaint regarding visibility of pg_catalog data
On Wed, 2023-11-08 at 05:31 -0700, David G. Johnston wrote: > 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. Don't keep your trade secrets in database identifiers or database function code. But if somebody is nervous about that, they can have their own database. Why share a database with users you don't trust? Yours, Laurenz Albe
Re: Another user complaint regarding visibility of pg_catalog data
On Tue, 2023-11-07 at 12:28 -0700, David G. Johnston wrote: > 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. > > 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 am not sure if we can and want to document the "why" (this does not really belong into the technical documentation), but the fact that most catalog tables can be read by PUBLIC is worth documenting. Perhaps here: https://www.postgresql.org/docs/current/catalogs.html 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". Yours, Laurenz Albe
Re: Document target_role param of ALTER DEFAULT PRIVILEGES
On Fri, 2023-11-03 at 09:53 -0400, Bruce Momjian wrote: > On Thu, Nov 2, 2023 at 09:51:58PM -0400, Bruce Momjian wrote: > > On Wed, Nov 1, 2023 at 09:31:52PM +0100, Laurenz Albe wrote: > > > There is another patch in the current commitfest that addresses a similar > > > problem, triggered by another complaint: > > > https://commitfest.postgresql.org/45/4633/ > > > > > > That patch does not cover role membership. Perhaps you could adopt that > > > patch and merge the information from your patch into it. > > > > Agreed. I found this doc report from 2020 with the same confusion about > > what target_role means: > > > > > > https://www.postgresql.org/message-id/flat/160003085417.31338.6391977653235501273%40wrigleys.postgresql.org > > > > I will apply my patch tomorrow/Friday and then work on the commitfest > > one. > > Okay, patch applied to all supported version. I will now work on the > similar commitfest item. Thanks! Laurenz Albe
Re: Documentation of return values of range functions lower and upper
On Thu, 2023-11-02 at 10:14 -0400, Bruce Momjian wrote:
> Better, though "Is the range's upper bound unbounded?" makes me cringe.
>
> Oh, yeah, totally cringe, me too. :-)
>
> > It is not the bound that is bounded or not, but the range.
> >
> > How about "Is the range unbounded at the upper end?" or "Does the range
> > have no upper bound?"
>
> I used your "end" idea to modify the patch, attached.
There are still some loose ends:
- you lost the specification whether it is the upper or the lower bound
- "Infinity" is a literal
- "-Infinity" is a very unlikely value for an upper bound
How about the attached version?
Yours,
Laurenz Albe
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76ec52c55..c2f266ea24 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19905,7 +19905,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the range (NULL if the
-range is empty or the lower bound is infinite).
+range is empty or has no lower bound).
lower(numrange(1.1,2.2))
@@ -19923,7 +19923,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the range (NULL if the
-range is empty or the upper bound is infinite).
+range is empty or has no upper bound).
upper(numrange(1.1,2.2))
@@ -19991,7 +19991,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's lower bound infinite?
+Does the range have no lower bound? (A lower bound of
+-Infinity returns false.)
lower_inf('(,)'::daterange)
@@ -20008,7 +20009,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's upper bound infinite?
+Does the range have no upper bound? (An upper bound of
+Infinity returns false.)
upper_inf('(,)'::daterange)
@@ -20063,7 +20065,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the multirange (NULL if the
-multirange is empty or the lower bound is infinite).
+multirange is empty has no lower bound).
lower('{[1.1,2.2)}'::nummultirange)
@@ -20081,7 +20083,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the multirange (NULL if the
-multirange is empty or the upper bound is infinite).
+multirange is empty or has no upper bound).
upper('{[1.1,2.2)}'::nummultirange)
@@ -20149,7 +20151,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's lower bound infinite?
+Does the multirange have no lower bound? (A lower bound of
+-Infinity returns false.)
lower_inf('{(,)}'::datemultirange)
@@ -20166,7 +20169,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's upper bound infinite?
+Does the multirange have no upper bound? (An upper bound of
+Infinity returns false.)
upper_inf('{(,)}'::datemultirange)
Re: Documentation of return values of range functions lower and upper
On Wed, 2023-11-01 at 18:03 -0400, Bruce Momjian wrote: > On Wed, Nov 1, 2023 at 09:40:43PM +0100, Laurenz Albe wrote: > > > Yes, I agree this documentation needs help. > > > > > > For upper/lower(), it is clear that the documentation is better saying > > > "unspecified" rather than infinite. The fact that upper/lower_inf() > > > returns false for +/-Infinity is quite odd, but should at least be > > > documented. > > > > > > Patch attached. It is odd that +Infinity (vs. Infinity) wasn't > > > supported for datetime input until PG 16, but I think we have to say > > > +/-infinity vs (blank)/-Infinity. > > > > > > Patch attached. > > > > I am unhappy with "unspecified". A NULL value as upper or lower bound has > > a very > > specific meaning, namely that the range is unbounded in that direction. > > This is > > a bit confusing, since NULL is typically used for unknown or undefined > > values. > > > > I think it would be better to say "returns NULL if the range is empty or > > unbounded" > > and "is the range unbounded on the upper end?". > > I had to go with "Is the multirange's lower bound unbounded?" because > the surrounding items use that sentence structure. Patch attached. Better, though "Is the range's upper bound unbounded?" makes me cringe. It is not the bound that is bounded or not, but the range. How about "Is the range unbounded at the upper end?" or "Does the range have no upper bound?" Yours, Laurenz Albe
Re: Documentation of return values of range functions lower and upper
On Wed, 2023-11-01 at 16:28 -0400, Bruce Momjian wrote: > On Wed, Nov 18, 2020 at 05:28:44PM +0100, Laurenz Albe wrote: > > On Wed, 2020-11-18 at 22:49 +0900, Fujii Masao wrote: > > > On 2020/11/12 17:14, Laurenz Albe wrote: > > > > On Wed, 2020-11-11 at 18:19 +0100, Laurenz Albe wrote: > > > > > > Table 9.54 in page > > > > > > https://www.postgresql.org/docs/current/functions-range.html states > > > > > > that the > > > > > > functions lower and upper return NULL if the requested bound is > > > > > > infinite. If > > > > > > the element type of the range contains the special values infinity > > > > > > and > > > > > > -infinity, this is not correct, as those values are returned if > > > > > > explicitly > > > > > > used as either bound. > > > > > +1 > > > > > Perhaps it would be better to say > > > > > NULL if the range is empty or has no lower/upper bound > > > > > > I agree this description looks a bit confusing. But according to the > > > section > > > "Infinite (Unbounded) Ranges" (*1), we already call "lower/upper bound > > > omitted" just infinite. So I don't think the current description is > > > incorrect. > > > > > > (*1) > > > https://www.postgresql.org/docs/devel/rangetypes.html#RANGETYPES-INFINITE > > > > That is correct, but I'd argue that it would be better to clarify the > > paragraph too, > > in particular: > > > > The functions lower_inf and upper_inf test for infinite lower and upper > > bounds of a range, respectively. > > > > should better read > > > > The functions lower_inf and upper_inf test for omitted lower and upper > > bounds of a range, respectively. > > > > The rest of the paragraph is pretty unambiguous. > > > > > > Independent of this, I think that my patch for "upper" and "lower" would > > make the > > documentation clearer. > > Yes, I agree this documentation needs help. > > For upper/lower(), it is clear that the documentation is better saying > "unspecified" rather than infinite. The fact that upper/lower_inf() > returns false for +/-Infinity is quite odd, but should at least be > documented. > > Patch attached. It is odd that +Infinity (vs. Infinity) wasn't > supported for datetime input until PG 16, but I think we have to say > +/-infinity vs (blank)/-Infinity. > > Patch attached. I am unhappy with "unspecified". A NULL value as upper or lower bound has a very specific meaning, namely that the range is unbounded in that direction. This is a bit confusing, since NULL is typically used for unknown or undefined values. I think it would be better to say "returns NULL if the range is empty or unbounded" and "is the range unbounded on the upper end?". Yours, Laurenz Albe
Re: Document target_role param of ALTER DEFAULT PRIVILEGES
On Wed, 2023-11-01 at 13:09 -0400, Bruce Momjian wrote: > On Thu, Feb 18, 2021 at 08:16:13AM +0100, Laurenz Albe wrote: > > On Wed, 2021-02-17 at 15:08 -0500, Jordi Gutiérrez Hermoso wrote: > > > I just had a confusing moment trying to figure out why my roles > > > weren't creating tables the way I was expecting them to. I didn't > > > understand what the `target_role` parameter did. No matter how I try > > > to read this page, I can't see an explanation for that parameter: > > > > > > https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html > > > > > > I propose an explanation like the following be added to the > > > `target_role` parameter: > > > > > > This is the role that will create objects with altered default > > > privileges. Note that changes in default privileges aren't > > > inherited, so altering the default privileges of created objects > > > for a parent role will not change the default privileges of > > > objects created by inherited roles. > > > > +1. I cannot count the number of times I have seen people confused by that. > > > > Suggested patch attached. > > I see how people can be confused. I wrote this patch, which I think is > simpler than the previously posted one. Oh, I had forgotten about this patch. I like your version better. There is another patch in the current commitfest that addresses a similar problem, triggered by another complaint: https://commitfest.postgresql.org/45/4633/ That patch does not cover role membership. Perhaps you could adopt that patch and merge the information from your patch into it. Yours, Laurenz Albe
Re: Missing word? https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL
On Mon, 2023-10-30 at 13:15 +, Daniel Westermann (DWE) wrote: > "Another way to archive is to use a custom archive module as the > archive_library" > > shouldn't this read: > "Another way to archive this is to use a custom archive module as the > archive_library" That would be worse than the original. What is "this"? I'd suggest "Another way to archive WAL ...". Yours, Laurenz Albe
Re: "20.16. Customized Options" – cannot be set by `ALTER SYSTEM`
On Mon, 2023-10-16 at 16:21 -0400, Tom Lane wrote: > So maybe we should allow ALTER SYSTEM for unrecognized parameters, > as long as the parameter name is syntactically legit and you're a > superuser. That seems more consistent than the current behavior, so +1. Yours, Laurenz Albe
Re: "20.16. Customized Options" – cannot be set by `ALTER SYSTEM`
On Mon, 2023-10-16 at 12:29 -0400, Tom Lane wrote: > I do see an issue here: > > regression=# ALTER SYSTEM SET foo.bar TO 'baz'; > ERROR: unrecognized configuration parameter "foo.bar" > regression=# SET foo.bar TO 'baz'; > SET > regression=# ALTER SYSTEM SET foo.bar TO 'baz'; > ALTER SYSTEM > > and now we have > > $ cat $PGDATA/postgresql.auto.conf > # Do not edit this file manually! > # It will be overwritten by the ALTER SYSTEM command. > foo.bar = 'baz' > > So that feels like a bug: we should not allow ALTER SYSTEM to execute > against a placeholder GUC definition, because the placeholder can't > tell us whether the value is valid. I wonder though if forbidding > this would break any legitimate usage patterns. I feel the same. However, the lack of any "variables" in SQL (as proposed in [1]) leads a lot of people to abuse placeholder parameters as variables to hold application state. I am sure that that is where this complaint comes from. We maintain that doing so is not a valid use case, but that claim sounds increasingly like a grammarian declaring that sentences should not end with a preposition, when everybody does it all the time. Yours, Laurenz Albe [1]: https://postgr.es/m/CAFj8pRDY%2Bm9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ%40mail.gmail.com
Re: Wrong article SET AUTOCOMMIT
On Wed, 2023-10-11 at 14:03 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/15/ecpg-sql-set-autocommit.html > > This article should be removed as this parameter does not exist since a long > time version 9.4 It is not a parameter, it is a client setting. It has nothing to do with the server parameter that was removed in 7.4. Yours, Laurenz Albe
Re: unnest multirange, returned order
On Wed, 2023-10-04 at 20:12 -0400, Daniel Fredouille wrote:
> unnest ( anymultirange ) → setof anyrange
> Expands a multirange into a set of ranges. The ranges are read out in storage
> order (ascending) and therefore order cannot be relied upon.
That's not true. The order is deterministic and can be relied on.
How about the attached patch, which does away with the confusing
mention of "storage order"?
Yours,
Laurenz Albe
From e39d1b5760d6fa0ed143c13589f717846cc82574 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Thu, 5 Oct 2023 08:48:21 +0200
Subject: [PATCH] Clarify the result order of unnest(multirange)
It is best not to mention the storage order, because that is
an implementation detail and has confused at least one user,
who assumed that the storage order is the order in which the
constituent ranges were written in SQL.
Since the sorting order is explained at the beginning of the
page, it should be sufficient to say that the ranges are
returned in ascending order.
Discussion: https://postgr.es/m/169627213477.3727338.17653654241633692682%40wrigleys.postgresql.org
---
doc/src/sgml/func.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 24ad87f910..bbb6e878ab 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20121,7 +20121,7 @@ SELECT NULLIF(value, '(none)') ...
Expands a multirange into a set of ranges.
-The ranges are read out in storage order (ascending).
+The ranges are read out in ascending order.
unnest('{[1,2), [3,4)}'::int4multirange)
--
2.41.0
Re: unnest multirange, returned order
On Tue, 2023-10-03 at 20:40 -0400, Daniel Fredouille wrote: > > I'd say that the storag order is the order in which PostgreSQL stores > > multiranges internally: > > Right, I believe that you are right but then this information is not useful > for the developer. > If storage order is always ascending by range order then let's make it clear, > if order cannot be counted upon as it may evolve from postgres version to > version, > then let's make it clear as well. WDYT ? I personally think that it is clear as it is written now. If you have a good suggestion for an improvement, you could send it; perhaps someone will pick it up. Yours, Laurenz Albe
Re: unnest multirange, returned order
On Mon, 2023-10-02 at 18:42 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/functions-range.html
>
> The doc says:
> * unnest ( anymultirange ) → setof anyrange
> * Expands a multirange into a set of ranges. The ranges are read out in
> storage order (ascending).
>
> What is storage order ?
>
> At first I thought that it was the order in which the different ranges are
> inserted in the internal data structure. However, the following sort of
> shows that it is not:
> ```
> postgres=# select unnest('{[1,4), [8,10)}'::int4multirange + '{[-5,-3)}' -
> '{[2,3)}') ;
> unnest
> -
> [-5,-3)
> [1,2)
> [3,4)
> [8,10)
> (4 lignes)
> ```
> Whatever I try, it always return in range order instead of "storage order".
I'd say that the storag order is the order in which PostgreSQL stores
multiranges internally:
SELECT '{[100,200),[-100,-50),[-1,2)}'::int4multirange;
int4multirange
═══
{[-100,-50),[-1,2),[100,200)}
(1 row)
Yours,
Laurenz Albe
Re: The documentation for storage type 'plain' actually allows single byte header
On Fri, 2023-09-29 at 18:19 -0400, Bruce Momjian wrote:
> On Thu, Jan 12, 2023 at 03:43:57PM +0100, Laurenz Albe wrote:
> > On Tue, 2023-01-10 at 15:53 +, PG Doc comments form wrote:
> > > https://www.postgresql.org/docs/devel/storage-toast.html - This is the
> > > development version.
> > >
> > > > PLAIN prevents either compression or out-of-line storage; furthermore it
> > > > disables use of single-byte headers for varlena types. This is the only
> > > > possible strategy for columns of non-TOAST-able data types.
> > >
> > > However, it does allow "single byte" headers. How to verify this?
> > >
> > > CREATE EXTENSION pageinspect;
> > > CREATE TABLE test(a VARCHAR(1) STORAGE PLAIN);
> > > INSERT INTO test VALUES (repeat('A',10));
> > >
> > > Now peek into the page with pageinspect functions
> > >
> > > SELECT left(encode(t_data, 'hex'), 40) FROM
> > > heap_page_items(get_raw_page('test', 0));
> > >
> > > This returned value of "1741414141414141414141".
> > > Here the first byte 0x17 = 0001 0111 in binary.
> > > Length + 1 is stored in the length bits (1-7). So Len = 0001011-1 = (11-1)
> > > [base-10] = 10 [base-10]
> > > which exactly matches the expected length. Further the data "41" repeated
> > > 10
> > > times also indicates character A (65 or 0x41 in ASCII) repeated 10 times.
> > >
> > > SoThis does **not** disable 1-B header. That sentence should be
> > > removed
> > > from the documentation unless this is a bug.
> >
> > I think that the documentation is wrong. The attached patch removes the
> > offending half-sentence.
> >
> > Yours,
> > Laurenz Albe
>
> > From 5bf0b43fe73384a21f59d9ad1f7a8d7cbc81f8c4 Mon Sep 17 00:00:00 2001
> > From: Laurenz Albe
> > Date: Thu, 12 Jan 2023 15:41:56 +0100
> > Subject: [PATCH] Fix documentation for STORAGE PLAIN
> >
> > Commit 3e23b68dac0, which introduced single-byte varlena headers,
> > added documentation that STORAGE PLAIN would prevent such single-byte
> > headers. This has never been true.
> > ---
> > doc/src/sgml/storage.sgml | 4 +---
> > 1 file changed, 1 insertion(+), 3 deletions(-)
> >
> > diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
> > index e5b9f3f1ff..4795a485d0 100644
> > --- a/doc/src/sgml/storage.sgml
> > +++ b/doc/src/sgml/storage.sgml
> > @@ -456,9 +456,7 @@ for storing TOAST-able columns on
> > disk:
> >
> >
> > PLAIN prevents either compression or
> > - out-of-line storage; furthermore it disables use of single-byte
> > headers
> > - for varlena types.
> > - This is the only possible strategy for
> > + out-of-line storage. This is the only possible strategy for
> > columns of non-TOAST-able data types.
> >
> >
> > --
> > 2.39.0
> >
>
> Where did we end with this? Is a doc patch the solution?
I don't think this went anywhere, and a doc patch is not the solution.
Tom has argued convincingly that single-byte headers are an effect of the TOAST
system, and that STORAGE PLAIN should disable all effects of TOAST.
So this would need a code patch.
Yours,
Laurenz Albe
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
Re: Documentation does not mention that basebackup could not be used on newer major version
On Mon, 2023-09-18 at 15:29 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/app-pgbasebackup.html > Description: > > https://www.postgresql.org/docs/current/logical-replication.html > Logical replication has excellent paragraph about usecases. > > These pages does not have them. > https://www.postgresql.org/docs/current/app-pgdump.html > https://www.postgresql.org/docs/current/app-pgbasebackup.html > > My main issue is that `pg_basebackup` page does not mention that this backup > is compatible only with current version of database. I can not do basebackup > on v11 and restore that on v15, for example. I think that that is amply documented by the fact that pg_basebackup is *not* mentioned in https://www.postgresql.org/docs/current/upgrading.html . You can't expect us to enumerate every tool that is not suitable for upgrading. Yours, Laurenz Albe
Re: Remove obsolete mention of backslashes as escapes
On Fri, 2023-09-08 at 17:25 -0400, Bruce Momjian wrote: > Thanks, patch applied back to PG 11. Thank you! Laurenz Albe
Re: to_char(numeric type, text) rounding instead of truncating
On Wed, 2023-09-06 at 16:53 -0400, Bruce Momjian wrote: > I slightly modified your patch and applied it back to PG 11 since all > supported Postgres versions have the same behavior. Thanks. Thanks for picking it up. Yours, Laurenz Albe
Re: clarifying trigger/rule behavior on logical replication subscribers
On Thu, 2023-07-27 at 12:16 +1000, Peter Smith wrote: > I created patches for this over a month ago, but then the thread went > quiet. Is there something more needed, or was it just accidentally > overlooked? > > Should this be added to the commitfest? Yes, please add it to the commitfest. That increases the likelyhood of you patch not being forgotten. Yours, Laurenz Albe
Re: to_char(numeric type, text) rounding instead of truncating
On Fri, 2023-07-21 at 04:56 +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/functions-formatting.html
> Description:
>
> I've encountered an issue with to_char(numeric type, text)
> Apparently, it uses rounding instead of truncation which leads to
> unpexpected results. This is not reflected in the documentation.
> My specific example:
> select to_Char(1235::real/(2::real+1235::real)*100, '99%')
> The expected output was 99%. The output was ##% - since the number was
> rounded to 100, there wasn't enough digits in the format.
> If I add a digit after the decimal point
> select to_char(1235::real/(2::real+1235::real)*100,'90D0%')
> The output becomes 99.8%
>
> It would be nice to have this behavior explained in the documentation so
> people get more predictable results.
+1
How about the following:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..9421ace77e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8505,6 +8505,14 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
+
+
+ If the format provides for fewer fractional digits than the number being
+ formatted has, to_char() will round the number to
+ the specified number of fractional digits.
+
+
+
The pattern characters S, L,
D,
Yours,
Laurenz Albe
Re: transaction example just pathetic
On Thu, 2023-07-13 at 18:59 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/tutorial-transactions.html > Description: > > The explanation of transactions is pathetic. Why are you showing bad > design...then saying it doesn't matter. And then in your transaction > block, you only show one update statement. Well, you just could have shown > the complete example if you didn't have the two stupid branch updates to > begin with. And you want people to believe postgres is good, get its > documentation is so poor. The documentation is not half as bad as this unspecific problem report. But I guess you are out to rant, not to get anything improved. Yours, Laurenz Albe
Re: group by can use alias from select list
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: > An expression used inside a grouping_element can be an input column name, > or the name or ordinal number of an output column (SELECT list item), > or an arbitrary expression formed from input-column values. An alias in this case would be an output column. Perhaps we can mention the alias explicitly. Yours, Laurenz Albe
Re: [PATCH] Attempt to clarify example of serialization anomaly
On Tue, 2023-06-20 at 20:01 -0700, Will Mortensen wrote:
> Rebased on master and updated the wording in several places. All
> feedback is appreciated. :-)
>
> I now see how to link to the wiki from the docs. I guess I numbered
> the transactions differently than the wiki's version though; I can
> rework it to more closely match the wiki if linking seems
> desirable.
I agree that the current wording in the documentation is too terse,
so your patch is an improvement.
There is still the potential for confusion. Perhaps an example with
explicit SQL statements (as in the other sections) might be even better.
Perhaps something simple like
If two concurrent sessions run:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM tab WHERE name = 'alice';
/* if the result is <> 0, rollback */
INSERT INTO table (name) VALUES ('alice');
COMMIT;
you could end up with two rows with the same name, which could not
happen in a serial execution of the transactions.
Yours,
Laurenz Albe
Re: Further clarification in documentation: No deletion of unreferenced large objects
On Tue, 2023-06-13 at 10:57 +, Oliver Marienfeld wrote: > I would understand if you decided against extending the docs. I don't get to decide that. If you come up with a patch for the documentation, that would increase the chances that the documentation actually gets changed. Yours, Laurenz Albe
Re: Further clarification in documentation: No deletion of unreferenced large objects
On Mon, 2023-06-12 at 13:48 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/15/datatype-binary.html > > 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. It's > good to know that PostgreSQL handles this differently and that one must > periodically use vacuumlo to avoid BLOB data piling up. At least, that's > what I understood. > > I think a small paragraph in the documentation about this would help many > people. I can help with writing, if needed. This is documented here: https://www.postgresql.org/docs/current/lo.html Admittedly, that is not the best place. Perhaps the introductory chapter in https://www.postgresql.org/docs/current/lo-intro.html would be a good place to mention that there is no referential integrity to large objects. Yours, Laurenz Albe
Re: bpchar datatype is not equal to character(1) data type
On Tue, 2023-06-06 at 22:30 +0800, yanliang lei wrote:
> in the documents
> (https://www.postgresql.org/docs/current/typeconv-query.html),there is the
> following descrition:
> “blank-padded char”, the internal name of the character data type.
> ===>>so bpchar datatype is equal to character data type
>
> in the documents
> (https://www.postgresql.org/docs/15/datatype-character.html),there is the
> following descrition:
> character without length specifier is equivalent to character(1).
> ===>>so character data type is equal to character(1) data type
>
> Based on the above description, there is a deduction as follows:
> bpchar datatype is equal to character(1) data type
>
> but the following test tells us that: bpchar datatype is not equal to
> character(1) data type.
> I want to know why? Maybe the document has error?
>
> [...]
>
> postgres=# create table xxx3(c1 bpchar,c2 int);
> CREATE TABLE
> postgres=# insert into xxx3 values('a',1);
> INSERT 0 1
> postgres=# \d+ xxx3;
> Table "public.xxx3"
> Column | Type | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> +-+---+--+-+--+-+--+-
> c1 | bpchar | | | | extended | |
> |
> c2 | integer | | | | plain | |
> |
> Access method: heap
>
> postgres=# select * from xxx3; <<<<please note this result.
> c1 | c2
> ---+
> a | 1
> (1 row)
There is some special case code in "gram.y" that treats column definitions of
CHAR or BIT special, since the standard requires that that means a length of 1.
See the following source comment:
/* We have a separate ConstTypename to allow defaulting fixed-length
* types such as CHAR() and BIT() to an unspecified length.
* SQL9x requires that these default to a length of one, but this
* makes no sense for constructs like CHAR 'hi' and BIT '0101',
* where there is an obvious better choice to make.
* Note that ConstInterval is not included here since it must
* be pushed up higher in the rules to accommodate the postfix
* options (e.g. INTERVAL '1' YEAR). Likewise, we have to handle
* the generic-type-name case in AexprConst to avoid premature
* reduce/reduce conflicts against function names.
*/
So the standard demands that behavior for CHAR or BIT, but it does not
define the behavior of "bpchar", so we have no special case code for that.
If you read the documentation carefully, it says that "bpchar is the internal
name of the character data type", but it doesn't say that the behave identical.
"bpchar" is not even mentioned in
https://www.postgresql.org/docs/current/datatype-character.html ,
so I think it is OK to consider it an internal thing that should not be used
by users directly. I think that there is no need to document that small
behavior difference.
Yours,
Laurenz Albe
Re: pass open cursor via USING in execute staement
On Wed, 2023-05-31 at 22:57 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
>
> Should be documented if this statement is allowed or not (if not, maybe set
> as desired feature :-) )
>
> EXECUTE format('update %I set col1=$1, col2=now() WHERE current of $3',
> tabname)
> USING myVal, myOpenCursor;
That is not supported. Parameters can only be used in a place where constants
(SQL literals) might appear, not instead of identifiers (names of tables,
columns etc.)
I looked, and that is really not documented anywhere.
One of PREPARE or the extended query protocol might be good places.
Yours,
Laurenz Albe
Re: Cross-Product JOIN?
On Thu, 2023-05-25 at 08:59 +0200, Erik Wienhold wrote:
> > Since we are talking about Cartesian products: is the term "Cartesian join"
> > used anywhere?
>
> Not in the docs:
I was trying to be funny. The Cartesian product of {Cartesian,cross} and
{product,join} would be {Cartesian product,Cartesian join,cross product,cross
join}.
Yours,
Laurenz Albe
Re: Cross-Product JOIN?
On Wed, 2023-05-24 at 21:10 +0200, Erik Wienhold wrote: > > On 24/05/2023 15:46 CEST Erik Wienhold wrote: > > > > Personally, I think it should read cartesian product because cross product > > is > > an overloaded term and cartesian product is used more often in the > > documentation > > overall. > > > > But the same page [0] also uses cross product when talking about grouping > > sets. > > > > [0] https://www.postgresql.org/docs/15/queries-table-expressions.html > > Here's a patch that fixes those two places. +1 "Cross product" seems to be a misbegotten hybrid of "cross join" and "Cartesian product". Since we are talking about Cartesian products: is the term "Cartesian join" used anywhere? Yours, Laurenz Albe
Re: Typo
On Wed, 2023-05-24 at 07:32 +0900, 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%. 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). How about: "... was written completely in ANSI C ..." Yours, Laurenz Albe
Re: Perhaps an issue on the collation page?
On Wed, 2023-04-26 at 21:20 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/15/collation.html > > This page states: > > Observe how in the traditional ICU locale naming system, the root locale > is selected by an empty string. > > CREATE COLLATION latinlast (provider = icu, locale = > 'en-u-kr-grek-latn'); > CREATE COLLATION latinlast (provider = icu, locale = > 'en@colReorder=grek-latn'); > > Neither of the locales are empty strings. The sentence with the "empty string" refers to the example that was before: CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji'); There is nothing, that is an ampty string, before the "@". Yours, Laurenz Albe
Remove obsolete mention of backslashes as escapes
The documentation in https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING says that "While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or backslashes, since each of those must be doubled." But this has been obsolete ever since version 9.1, when "standard_conforming_strings" started to default to "on". It has confused at least one reader: https://dba.stackexchange.com/q/325850/176905 So I propose to remove the mention of backslashes there. Yours, Laurenz Albe From 282d2dae27524aef37cdafe02e4833894bf5892c Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Tue, 11 Apr 2023 09:48:20 +0200 Subject: [PATCH] Remove obsolete mention of backslashes as escapes Since version 9.1, standard_conforming_strings defaults to "on". So the remark that backslashes must be doubled has been obsolete for a long time and should be removed. It is more confusing than helpful by now. --- doc/src/sgml/syntax.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 5668ab0143..3ba844057f 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -555,7 +555,7 @@ U&'d!0061t!+61' UESCAPE '!' While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string - contains many single quotes or backslashes, since each of those must + contains many single quotes, since each of those must be doubled. To allow more readable queries in such situations, PostgreSQL provides another way, called dollar quoting, to write string constants. -- 2.39.2
Re: doc build error on Fedora 38
On Thu, 2023-04-06 at 11:41 +0100, Devrim Gündüz wrote: > I'm getting the following errors while building PDF docs on Fedora 38 > (and works fine on Fedora 37). This is how I build them: > > ./configure > cd doc/src/sgml/ > make postgres-A4.pdf > > and getting the following errors. Google did not help me that much. This > is libxml2-2.10.3 and libxslt 1.1.37 (same on Fedora 37). > > [...] > I/O error : Attempt to load network entity > http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd > [...] Not sure if that helps, but could it simply be a problem accessing the network? Yours, Laurenz Albe
Re: Nulls Not Distinct in Unique Indexes secton
On Tue, 2023-03-14 at 08:24 -0700, Kirk Parker wrote: > The Unique Indexes section > (https://www.postgresql.org/docs/15/indexes-unique.html) > does not mention the new NULLS [ NOT ] DISTINCT capability of indexes > (https://www.postgresql.org/docs/15/sql-createindex.html), and it probably > should. > > Specifically, it has the exact wording from previous versions (emphasis > added): > > When an index is declared unique, multiple table > rows with equal indexed values are not allowed. > *Null values are not considered equal*. > > We should consider adding "unless the NULLS NOT DISTINCT clause is used when > creating the index", or something to that effect. +1 Here is a patch for that. Yours, Laurenz Albe From b02fbf9e11c6953269428dd8572e79349d61d646 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Thu, 16 Mar 2023 20:47:20 +0100 Subject: [PATCH] Mention NULLS NOT DISTINCT in the SQL documentation Commit 94aa7cc5f7 forgot one place in the documentation that still claimed unconditionally that NULLs are considered equal. Per report by Kirk Parker. --- doc/src/sgml/indices.sgml | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 0c3fcfd62f..f1c15fcc0b 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -672,8 +672,9 @@ CREATE UNIQUE INDEX name ON table When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered - equal. A multicolumn unique index will only reject cases where all - indexed columns are equal in multiple rows. + equal, unless the index is created with the NULLS NOT + DISTINCT clause. A multicolumn unique index will only + reject cases where all indexed columns are equal in multiple rows. -- 2.39.2
Re: Transaction wraparound and read committed isolation level
On Sun, 2023-02-19 at 03:10 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/routine-vacuuming.html > Description: > > hey guys > > thanks for the work you do we really appreciate it. > > In the transaction wraparound section this statement is misleading and got > me really confused. > > “a row version with an insertion XID greater than the current transaction's > XID is “in the future” and should not be visible to the current > transaction“ > > If the current transaction isolation level is read committed it absolutely > can see rows committed by future transactions with higher XIDs. You are right, in combination with PlanQualEval you can. > Would be cool to add a note. > > this also bear the question that the wraparound isn’t really a problem with > default isolation level but more for higher levels such as repeatable read > and such. > > please correct me if my understanding is incorrect. Wraparound can be a problem on all isolation levels. It has to do with transaction IDs and visibility. Yours, Laurenz Albe
Re: Not an error but a difficult wording
On Wed, 2023-01-25 at 20:39 -0500, Tom Lane wrote: > Laurenz Albe writes: > > On Wed, 2023-01-25 at 08:22 +, PG Doc comments form wrote: > > > Maybe this would be better? (I don't know the comma rules) > > > "because the files(,?) that are generated/processed by these tools(,?) are > > > already included in the tarball" > > > +1 > > > Correct English would be: > > > These tools are not needed to build from a distribution tarball, because > > the files generated by these tools are included in the tarball. > > The existing wording is not incorrect AFAICS, but I agree it's a bit > awkward. I meant "a correct version of what was suggested in the mail", not that the released text was incorrect. > I'd modify one word in your version: > > These tools are not needed to build from a distribution tarball, because > the files generated using these tools are included in the tarball. > > Or possibly "with" instead of "using"? Both are better; I'd lean towards "with". Yours, Laurenz Albe
Re: Not an error but a difficult wording
On Wed, 2023-01-25 at 08:22 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sourcerepo.html > Description: > > https://www.postgresql.org/docs/current/sourcerepo.html > > I was convinced that there was a missing word or something in > "because the files that these tools are used to build are included in the > tarball" > I had to read this several times, until I saw that it was actually > correct. > > Maybe this would be better? (I don't know the comma rules) > "because the files(,?) that are generated/processed by these tools(,?) are > already included in the tarball" +1 Correct English would be: These tools are not needed to build from a distribution tarball, because the files generated by these tools are included in the tarball. Yours, Laurenz Albe
Re: The documentation for storage type 'plain' actually allows single byte header
On Mon, 2023-01-16 at 11:50 -0500, Tom Lane wrote: > Laurenz Albe writes: > > On Sun, 2023-01-15 at 16:40 -0500, Tom Lane wrote: > > > The documentation is correct, what is broken is the code. > > > I see. But what is the reason for that anyway? Why not allow short varlena > > headers if TOAST storage is set to PLAIN? > > The original motivation for that whole mechanism was to protect data > types for which the C functions haven't been upgraded to support > non-traditional varlena headers. So I was worried that this behavior > would somehow break those cases (which still exist, eg oidvector and > int2vector). However, the thing that actually marks such a datatype > is that pg_type.typstorage is PLAIN, and as far as I can find we do > still honor that case in full. If that's the case then every tupdesc > we ever create for such a column will say PLAIN, so there's no > opportunity for the wrong thing to happen. > > So maybe it's okay to move the goalposts and acknowledge that setting > attstorage to PLAIN isn't a complete block on applying toast-related > transformations. I wonder though whether short-header is the only > case that can slide through. In particular, for "INSERT ... SELECT > FROM othertable", I suspect it's possible for a compressed-in-line > datum to slide through without decompression. (We certainly must > fix out-of-line datums, but that doesn't necessarily mean we undo > compression.) So I'm not convinced that the proposed wording is > fully correct yet. I see, thanks for the explanation. Since the only storage format I have ever had use for are EXTENDED and EXTERNAL, it is not very important for me if PLAIN supports short headers or not. Since single-byte headers are part of the TOAST mechanism (and documented as such), it makes sense to disable them in PLAIN. Then the documentation could describe PLAIN as "skip all TOAST processing". So we should probably go with the simplest fix that restores consistency. Yours, Laurenz Albe
Re: The documentation for storage type 'plain' actually allows single byte header
On Sun, 2023-01-15 at 16:40 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > On Tue, 2023-01-10 at 15:53 +, PG Doc comments form wrote:
> > > > PLAIN prevents either compression or out-of-line storage; furthermore it
> > > > disables use of single-byte headers for varlena types. This is the only
> > > > possible strategy for columns of non-TOAST-able data types.
>
> > > However, it does allow "single byte" headers. How to verify this?
> > > CREATE EXTENSION pageinspect;
> > > CREATE TABLE test(a VARCHAR(1) STORAGE PLAIN);
> > > INSERT INTO test VALUES (repeat('A',10));
> > >
> > > Now peek into the page with pageinspect functions
> > >
> > > SELECT left(encode(t_data, 'hex'), 40) FROM
> > > heap_page_items(get_raw_page('test', 0));
> > >
> > > This returned value of "1741414141414141414141".
>
> > I think that the documentation is wrong. The attached patch removes the
> > offending half-sentence.
>
> The documentation is correct, what is broken is the code.
I see. But what is the reason for that anyway? Why not allow short varlena
headers if TOAST storage is set to PLAIN?
Yours,
Laurenz Albe
Re: The documentation for storage type 'plain' actually allows single byte header
On Tue, 2023-01-10 at 15:53 +, PG Doc comments form wrote:
> https://www.postgresql.org/docs/devel/storage-toast.html - This is the
> development version.
>
> > PLAIN prevents either compression or out-of-line storage; furthermore it
> > disables use of single-byte headers for varlena types. This is the only
> > possible strategy for columns of non-TOAST-able data types.
>
> However, it does allow "single byte" headers. How to verify this?
>
> CREATE EXTENSION pageinspect;
> CREATE TABLE test(a VARCHAR(1) STORAGE PLAIN);
> INSERT INTO test VALUES (repeat('A',10));
>
> Now peek into the page with pageinspect functions
>
> SELECT left(encode(t_data, 'hex'), 40) FROM
> heap_page_items(get_raw_page('test', 0));
>
> This returned value of "1741414141414141414141".
> Here the first byte 0x17 = 0001 0111 in binary.
> Length + 1 is stored in the length bits (1-7). So Len = 0001011-1 = (11-1)
> [base-10] = 10 [base-10]
> which exactly matches the expected length. Further the data "41" repeated 10
> times also indicates character A (65 or 0x41 in ASCII) repeated 10 times.
>
> SoThis does **not** disable 1-B header. That sentence should be removed
> from the documentation unless this is a bug.
I think that the documentation is wrong. The attached patch removes the
offending half-sentence.
Yours,
Laurenz Albe
From 5bf0b43fe73384a21f59d9ad1f7a8d7cbc81f8c4 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Thu, 12 Jan 2023 15:41:56 +0100
Subject: [PATCH] Fix documentation for STORAGE PLAIN
Commit 3e23b68dac0, which introduced single-byte varlena headers,
added documentation that STORAGE PLAIN would prevent such single-byte
headers. This has never been true.
---
doc/src/sgml/storage.sgml | 4 +---
1 file changed, 1 insertion(+), 3 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index e5b9f3f1ff..4795a485d0 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -456,9 +456,7 @@ for storing TOAST-able columns on disk:
PLAIN prevents either compression or
- out-of-line storage; furthermore it disables use of single-byte headers
- for varlena types.
- This is the only possible strategy for
+ out-of-line storage. This is the only possible strategy for
columns of non-TOAST-able data types.
--
2.39.0
Re: Postgres Partitions Limitations (5.11.2.3)
On Fri, 2023-01-06 at 08:28 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/ddl-partitioning.html > Description: > > Link: > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE > > "Using ONLY to add or drop a constraint on only the partitioned table is > supported as long as there are no partitions. Once partitions exist, using > ONLY will result in an error. Instead, constraints on the partitions > themselves can be added and (if they are not present in the parent table) > dropped." This seems in contradiction to the example involving adding a > unique constraint while minimizing locking at the bottom of "5.11.2.2. > Partition Maintenance", which seems to run fine on my local Pg instance: > > " > This technique can be used with UNIQUE and PRIMARY KEY constraints too; the > indexes are created implicitly when the constraint is created. Example: > > ```ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate); > > ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate); > ALTER INDEX measurement_city_id_logdate_key > ATTACH PARTITION measurement_y2006m02_city_id_logdate_key; > ... > ``` > " > > I might be misinterpreting something. Sorry if that's the case! No, that is actually an omission in the documentation. The attached patch tries to improve that. Yours, Laurenz Albe From ecdce740586e33eeb394d47564b10f813896ff11 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Mon, 9 Jan 2023 16:38:58 +0100 Subject: [PATCH] Fix omission in partitioning limitation documentation UNIQUE and PRIMARY KEY constraints can be created on ONLY the partitioned table. We already had an example demonstrating that, but forgot to mention it in the documentation of the limits of partitioning. Author: Laurenz Albe Discussion: https://postgr.es/m/167299368731.659.16130012959616771...@wrigleys.postgresql.org --- doc/src/sgml/ddl.sgml | 5 - 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 6e92bbddd2..b4a75f9c8f 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4282,7 +4282,10 @@ ALTER INDEX measurement_city_id_logdate_key Using ONLY to add or drop a constraint on only the partitioned table is supported as long as there are no partitions. Once partitions exist, using ONLY -will result in an error. Instead, constraints on the partitions +will result in an error (the exception to this are +UNIQUE and PRIMARY KEY +constraints, which will be created with an invalid index, as shown in +the example above). Instead, constraints on the partitions themselves can be added and (if they are not present in the parent table) dropped. -- 2.39.0
Re: Fix broken event trigger example
On Fri, 2022-12-23 at 13:23 +0100, Alvaro Herrera wrote: > > The example in > > https://www.postgresql.org/docs/current/event-trigger-example.html > > stopped compiling since commit 2f9661311b. > > > > Here is a patch to fix that. > > Right, thanks. Applied to all the relevant branches. Thanks for the quick work! Yours, Laurenz Albe
Fix broken event trigger example
The example in
https://www.postgresql.org/docs/current/event-trigger-example.html
stopped compiling since commit 2f9661311b.
Here is a patch to fix that.
Yours,
Laurenz Albe
From ae0aa8d01df0626a0417c6c339f59ee50de5c2b3 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Fri, 23 Dec 2022 13:07:25 +0100
Subject: [PATCH] Fix event trigger example
Commit 2f9661311b changed command tags from strings to numbers,
but forgot to adjust the code in the event trigger example,
which consequently failed to compile.
While fixing that, improve the indentation to adhere to pgindent
style.
Backpatch to v13, where the change was introduced.
Author: Laurenz Albe
---
doc/src/sgml/event-trigger.sgml | 5 +++--
1 file changed, 3 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index f1235a2c9f..3b6a5361b3 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1194,8 +1194,9 @@ noddl(PG_FUNCTION_ARGS)
trigdata = (EventTriggerData *) fcinfo->context;
ereport(ERROR,
-(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("command \"%s\" denied", trigdata->tag)));
+(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("command \"%s\" denied",
+GetCommandTagName(trigdata->tag;
PG_RETURN_NULL();
}
--
2.38.1
Re: Getting started
On Sat, 2022-12-10 at 18:46 +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/user-manag.html > Description: > > https://www.postgresql.org/docs/current/tutorial-createdb.html > > The "getting started" page skips right from the installing to already having > started the program, but on linux machines, you cannot start psql without > inputting a special command, "sudo -u postgres psql". Typing psql will make > the program tell you that your username doesn't exist, and you can't create > a username for psql without starting psql first. I had to search somewhere > else to learn that I had to start psql with that special command. Yes, the "initdb" step is missing. If we had that, it would be clear that the rest of the chapter assumes that you are running shell commands with an operating system user whose name is identical to the bootstrap superuser. Yours, Laurenz Albe
