The examples given in Chapter 14 are not true.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/performance-tips.html Description: 1) How did you create the `tenk1` table? If you will claim that from the source code of version 9.3, the question arises how CREATE TABLE tenk1 (...) WITH OIDS; in v16? https://github.com/postgres/postgres/blob/524440579f838b30579f35d2045cc01b6f5070d8/src/test/regress/sql/create_table.sql#L37 Additionally, you have to spend time searching for additional and completely unnecessary information and old versions of the code, although you have just installed server version 16 from the source code, which has passed all checks - but as it turns out is not compatible with the documentation. After `make installcheck` v16 - All 215 tests passed. But 1) relpages for table `tenk1` - different in version 16. Some calculations will be incorrect accordingly 2) you will not be able to reproduce examples for which there are no indexes in table `tenk1` - for example index `index_tenk1_on_four`. 3) How do you see this familiarization with the product through documentation that points to older versions that work very differently in the new version?
ltree docs imprecise about sorting order
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ltree.html Description: The ltree docs available at https://www.postgresql.org/docs/current/ltree.html state "Comparison sorts in the order of a tree traversal" without specifying the strategy implemented to walk the tree. A quick experiment suggests that the implemented solution is pre-ordered depth-first search. I suggest the ltree docs be amended to "Comparison sorts in the order of a pre-ordered depth-first tree traversal". Best regards, Pierre Vennin
Building from source using clean Ubuntu docker container
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/install-make.html Description: Hello there, Below are complete steps for building Postgres from source in a clean docker ubuntu container. Maybe this is useful for your docs? I noticed the 'Chapter 17. Installation from Source Code' doesn't discuss many common dependency issues like ICU and Zlib. If you would like a write up, I can contribute. Thanks, Sundip STEPS: docker pull ubuntu docker run --rm -it --entrypoint bash ubuntu apt update && apt upgrade apt install build-essential apt install sudo apt install git apt install pkg-config apt install libicu-dev apt install bison apt install flex apt install libreadline-dev apt install zlib1g-dev adduser postgres usermod -aG sudo postgres git clone https://git.postgresql.org/git/postgresql.git ./configure make sudo make install sudo mkdir -p /usr/local/pgsql/data sudo chown postgres /usr/local/pgsql/data /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test
Ambiguous description on new columns
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/logical-replication-col-lists.html Description: The documentation on this page mentions: "If no column list is specified, any columns added later are automatically replicated." It feels ambiguous what this could mean. Does it mean: 1/ That if you alter the table on the publisher and add a new column, it will be replicated 2/ If you add a column list later and add a column to it, it will be replicated In both cases, does the subscriber automatically create this column if it wasn't there before? I recall reading that the initial data synchronization requires the schema of the publisher database to be created on the subscriber first. But then later updates sync newly created columns? I don't recall any pages on logical replication mentioning this, up to this point. Regards, Koen De Groote
52.38 pg_proc (postgresql version 14)
The following documentation comment has been logged on the website: 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.
Fundamental Architecture of PostgreSQL
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-arch.html Description: PostgreSQL uses a client/server architecture. As is typical of client/server applications, the client and the server can be on different hosts. In that case, they communicate over a TCP/IP network connection. A PostgreSQL database, in a broader sense, consists of the following cooperating processes (programs): • A PostgreSQL instance process that manages a collection of databases also known as Postgres Cluster. A Postgres Cluster is stored at a common file system location (the “data area”). More than one PostgreSQL instance can be initiated on a single system as long as they use different data areas and different communication ports. • A Client Process initiates a connection (or session) for the PostgreSQL instance. Client applications can be very diverse in nature. A Postgres Client could be a text-oriented tool, a graphical application, a web server that accesses the PostgreSQL as a backend database, or a specialized database maintenance tool. • A Server Process initiated by the PostgreSQL instance as soon as a Postgres Client sends a connection request. The PostgreSQL instance can handle multiple such connection requests concurrently, originating from various Postgres Client processes that are running on a single or multiple hosts. To achieve this, the PostgreSQL instance initiates (forks) a new Server Process for every new connection request coming from a Postgres Client. The Server Process accepts the connection request and after authentication, performs database actions on behalf of the corresponding Postgres Client while interacting with the PostgreSQL instance. In other words, after establishing the connection with one of the Server Processes, the Postgres Client sends requests to the Server Process while the Server Process listens to these requests and returns responses accordingly. Once a connection is established, the Postgres Client and the Server Process can communicate with each other without directly intervening with the PostgreSQL instance. Note that, the PostgreSQL instance is always running and waiting to accept any new client connection requests coming from Postgres Clients, whereas the Postgres Client and associated Server Process come and go.
Logical replication - initial data synchronization
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/logical-replication-subscription.html Description: I'm reading up on Logical Replication and have been reading the pages in order. The first 2 pages: https://www.postgresql.org/docs/current/logical-replication.html and https://www.postgresql.org/docs/current/logical-replication-publication.html both speak of the requirement to set up a snapshot and explain that publication will then send further updates as they happen to subscribers. But the 3rd page, https://www.postgresql.org/docs/current/logical-replication-subscription.html now mentions this: "Additional replication slots may be required for the initial data synchronization of pre-existing table data and those will be dropped at the end of data synchronization." For me, reading the first 2 pages implied that I would have to perform some manual command that starts the creation of a snapshot of pre-existing table data, and unpack this on the subscriber node somehow. The text on the "Subscription" page sounds to me like this is actually something the publisher<-> subscriber model of the postgres software can manage on its own. As opposed to a snapshot, which feels more like the concept of a basebackup. Regardless of that being correct or not, my current impression is that the description isn't consistent across pages. Maybe the text is obvious for people who've performed setup of logical replication before, but I have never done this. To me, the description on the first 2 pages seems inconsistent with the description I just encountered on the 3rd page. I was under the impression there was no such thing as "initial data synchronization of pre-existing table data" in terms of postgres doing this by itself. Am I missing something extremely simple, or can the description of the involved operations be made more consistent across documentation pages? Regards, Koen De Groote
Unclear on Publication documentation page
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/logical-replication-publication.html Description: The page in question: https://www.postgresql.org/docs/current/logical-replication-publication.html It says: "If a table without a replica identity is added to a publication that replicates UPDATE or DELETE operations then subsequent UPDATE or DELETE operations will cause an error on the publisher. INSERT operations can proceed regardless of any replica identity." The text is ambiguous as to what is meant by "the publisher" in "will cause an error on the publisher". I've personally never set up logical replication, which is why I'm reading the documentation now. The question for me is: Does "the publisher" refer to a logical entity, created by the "CREATE PUBLICATION " command? And further publication in the publication<->subscriber model will not work anymore? Or does it refer to the actual database itself, and will the functionality of the database be impacted? As in: your live production database can now no longer process UPDATE and DELETE statements so long as that publication exists/so long as that publication is configured to publish that particular table? Regards, Koen De Groote
Explanations not clear
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/collation.html Description: I created a collation specifying the ks-level3 setting and with deterministic set to false. But when I compare "a_b" to "a-b" with this collation I get false. According to the table 24.1 it should yield true. Only after adding ka-shifted this comparison becomes true. The interactions of the different options are not very clear. What is the difference for each level when using ks without ka and with ka? The explanations for the options kc and kb are also not very helpful. Also the role of deterministic = false is not very good explained. It seems that the settings ks, ka and kc loose any meaning with this setting. Thank you very much
Connection Info
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/server-start.html Description: Hello! 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.
Missing examples for hash partitioning
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ddl-partitioning.html Description: There is no example showing the syntax of Hash Partitioning in this section of the documentation, it's missing.
The PostgreSQL Select Syntax
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-select.html Description: Dear PostgreSQL Team, I'm writing to seek clarification regarding the scope of the formal syntax outlined in the documentation for the SELECT statement (https://www.postgresql.org/account/comments/new/16/sql-select.html/). I understand that this syntax defines the core structure and rules for constructing valid queries. However, I'm interested in building an Object-Relational Mapper (ORM) for PostgreSQL. My primary question is: Does the documented formal syntax represent the absolute limit of what PostgreSQL can understand and process, or are there additional functionalities beyond these core rules? I'm particularly interested in how ORMs typically handle features like data type mapping, object-relational mapping, and support for advanced functionalities like subqueries and window functions. I'm asking this for clarity to avoid dedicating time to exploring the internal C parser code of PostgreSQL. I'd like to ensure the documented syntax serves as a reliable foundation for building an ORM, with the understanding that there might be additional functionalities beyond the core structure. Any insights you can provide on this topic would be greatly appreciated. Additionally, if there are resources beyond the formal syntax documentation that would be helpful for building a PostgreSQL ORM, I'd be grateful if you could point me in the right direction. Thank you for your time and assistance.
roles that have the CREATEROLE privilege can no longer GRANT predefined roles
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/predefined-roles.html Description: 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 https://www.postgresql.org/docs/current/predefined-roles.html
No documentation on how pg_ctl is installed
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/app-pg-ctl.html Description: I have used postgresql@14 for several years and have always used pg_ctl to start the postgres server, check status etc. I tried upgrading to postgresql@15 with the command brew install postgresql@15 on a Mac from Terminal and pg_ctl is apparently not installed. The Documentation page for pg_ctl goes into all kinds of detail about the utility, the options to use, etc. but NOTHING about how it is installed, troubleshooting when it doesn't work or when it is not found. Google searches yield very little information also. Fairly new to postgres - apologies for the newbie comments but a little more documentation is needed here.
Documentation Chapter 16: 8.21
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/datatype-pseudo.html Description: I tryed to implement a safe_cast function with 2 parameters and a return of type "any" but it failed. I get a syntax error, if I use "any" as a data type, because it is a sql function (similar to array contains) and not a data type. And if there is a data type "any" in postgres too, how should it work as parameter and wheres the difference to "anyelement"?
The prompt is not displayed correctly in the example on psql page
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-psql.html Description: On this page https://www.postgresql.org/docs/current/app-psql.html where is? >> `( if there is an unmatched left parenthesis` ```sql testdb=> SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB", testdb(> row_number() over(order by t2.first) AS ord testdb(> FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC testdb(> \crosstabview "A" "B" "AxB" ord ```
Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/postgres-fdw.html Description: Hi, The https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION section mentions for `extensions` configuration option: > Functions and operators that are immutable and belong to a listed extension will be considered shippable to the remote server. But doesn't mention that extensions' custom data types will be pushed down as well! I feel it's important and worth mentioning. I noticed that the lookup_shippable() function in shippable.c file does mention that: > Returns true if given object (operator/function/type) is shippable according to the server options. Src: https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/shippable.c#L108 Can you please update the official docs to call out about types? Thank you! :)
Mysteries of the future
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/functions-formatting.html
Description:
I'm not sure that there will be 31 days in 11 months; although postgresql
probably knows better what will happen in 2
For example (with the year 2): to_date('21131', 'MMDD') will be
interpreted as a 4-digit year; instead use a non-digit separator after the
year, like to_date('2-1131', '-MMDD') or to_date('2Nov31',
'MonDD').
https://www.postgresql.org/docs/current/functions-formatting.html
```sql
SELECT to_date('2-1131', '-MMDD');
ERROR: 22008: date/time field value out of range: "2-1131"
LOCATION: DateTimeParseError, datetime.c:4021
```
psql option
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-psql.html Description: option -X, the specified comma after the letter X https://www.postgresql.org/docs/current/app-psql.html
8.14.5 jsonb subscripting
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/datatype-json.html
Description:
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];
A typo?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/plpgsql-declarations.html Description: Under 43.3.1, "Notice that we omitted RETURNS real — we could have included it, but it would be redundant." Should that be "RETURNS tax" instead of "RETURNS real"?
RE: ts_headline performance note
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/textsearch-controls.html Description: This is a followup to https://www.postgresql.org/message-id/171175476214.7104.6233899851600749789%40wrigleys.postgresql.org I am a developer on the team that fixed the performance issues with our usage of ts_headline. I want to give a little bit of context on how we noticed the issue and fixed it. We first noticed the issue when we began indexing large numbers of multi-megabyte text content in our database. It was causing our search endpoint to slow down significantly and, in many cases, time out with query times greater than 10 seconds. We assumed it was a performance issue with search (maybe with our indexing or vector implementation), but in a process of elimination we happened to remove our ts_headline annotations and that fixed the issue. We realized that adding ts_headline back and slowly increasing our result set showed that for some results in the set, ts_headline came back quickly (less than 10ms) and in other cases it added hundreds (occasionally thousands) of milliseconds for a single result. From there we computed that in our particular use case, ts_headline seems to have a maximum processing speed of between 3-5 MB/s, and processing entire pages of results with some results containing multiple megabytes of text was not feasible. We solved this issue in three ways: First, we are ensuring that our frontend is fully utilizing pagination to reduce the number of results ts_headline must process per request. Second, we are forcing our Django application to paginate the data before generating headlines, thereby ensuring that ts_headline is not running for the entire result set of the search operation, but only a subset. This was not immediately obvious as Django's ORM seems to attempt to optimize to the smallest number of queries for a given operation by default, instead of optimizing toward least time. We accomplished this by searching, then paginating, then extracting the primary keys of the pagination result into a list which we feed into another query for ts_headline to work on. Finally, we are annotating a substring (currently the first 50,000 characters) of the field that often contains large amounts of text and running ts_headline on that instead. We found that if we stick to a maximum per result text field size of 50k, ts_headline consistently takes 10ms or less per result in our use case, and we find that acceptable for the page sizes that we are returning. The tradeoff with this solution is that ts_headline will not always find text to highlight in the substring, but we find that acceptable as well due to our need for better performance. It may be helpful to include something like this in the docs for ts_headline: > ts_headline uses the original document, not a tsvector summary, so it can be slow and should be used with care. For example: > * Consider using pagination when searching through large data sets. > * If you're using pagination, ensure your application is paginating the data before generating headlines, so that ts_headline is not running for the entire result set of the search operation, but only a subset. > * Consider limiting ts_headline to running on a limited amount of text from each document, instead of the entire document. > * Consider setting up a timeout for the ts_headline operation, so that if it takes too long, search results are delivered without headlines.
LOCK docs and permissions
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/explicit-locking.html Description: Hello, 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? Thank you! Jens
pg_crypto reference
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/pgcrypto.html Description: Hi Regarding the page for pg_crypto: https://www.postgresql.org/docs/current/pgcrypto.html A worked example would really help with this, especially for those of us who don't know much about encryption. Thanks Matthew
Need a PostgreSQL 16 developer edition for enabling vector support
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/bug-reporting.html Description: Need a PostgreSQL 16 developer edition for enabling vector support. We want to compile a vector extension in the postgres 16 dev edition and use the vector feature in the postgresql
Wording for using the lock timeout setting
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/runtime-config-client.html Description: Thanks for the cool product. One comment concerning the lock_timeout parameter: The documentation states here: https://www.postgresql.org/docs/16/runtime-config-client.html "Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions." From my perspective this statement is too strong. There are complex transaction scenarios where multiple sessions can block the whole system and a finite value would make sense. When you state "not recommended" this property is not available in AWS Aurora for example for this reason. I would assume as you and a number of other database vendors offer such a property, it should also be usable? Just a proposal (you might find a better wording): "Be aware that setting lock_timeout in postgresql.conf would affect all sessions." Thanks for consideration.
ALTER TABLE atomicity with sub-commands
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-altertable.html Description: Hello dear PostgreSQL family, It is not entirely clear (to me) that ALTER TABLE statements with comma-separated sub-commands are atomic. Despite of saying "perform X actions in one operation" in one of the examples, it is not explicitly said that the operation will be rolled back if one of the sub-commands fails. From the examples, we have: ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100); Will the `address` column type change rollback if the `ALTER COLUMN name TYPE varchar(100)` subcommand fails? Currently reading the docs for version 15. Many thanks :)
ts_headline performance note
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/textsearch-controls.html Description: My team recently ran into a performance issue that turned out to involve ts_headline, so I've been looking at the documentation. In 12.3.4. Highlighting Results, the last line says: "ts_headline uses the original document, not a tsvector summary, so it can be slow and should be used with care." This sentence was easy for me to miss at first because it's a small line between a long example block and the footer material. If a person needs to use this with care, should that note be incorporated into the introduction to the section? Also, I see at https://github.com/postgres/postgres/commit/d5d8a0b7e54ca09d0b5fdfc6afcb307450f33215 (discussed at https://www.postgresql.org/message-id/6700.1478986625%40sss.pgh.pa.us) that this section previously provided an example that became obsolete. "Used with care" is a bit vague on its own, and future readers could benefit from a more specific statement or a new example of how to use it with care. Thank you very much! I appreciate the work of documentation editors.
CREATE ROLE
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-createrole.html Description: https://www.postgresql.org/docs/16/sql-createrole.html ROLE role_name The ROLE clause causes one or more specified existing roles to be automatically added as members, with the SET option enabled. This in effect makes the new role a “group”. Roles named in this clause with role-level the INHERIT attribute will have the INHERIT option enabled in the new membership. New memberships will have the ADMIN option disabled. The following sentence is badly worded: Roles named in this clause with role-level the INHERIT attribute will have the INHERIT option enabled in the new membership
Explain Format output specification
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-explain.html Description: Hello Regarding the Explain command's documentation: https://www.postgresql.org/docs/current/sql-explain.html For non-text format options (JSON, YAML, XML) there is no mention of the expected output. How stable are the fields inside these ? Does the name of some field liable to change from version to version ? Could it be possible to have at least a high level specifications of some of the fields ? I have a script I'm relying on that takes the JSON output of Explain with Verbose set to true. It then find the "Output" fields deeply (in each Plans recursively), use "Relation Name", "Alias" and "Schema" to get the full name of the columns and collects them all. How susceptible is this to break in the future ? Thanks in advance
Monetary Data Types Improvement
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/datatype-money.html Description: It's not explicitly obvious that money doesn't behave like a normal numeric type in that executing a procedure with a negative numeric value for money causes an error. The solution to this is to pass the value as a string. For example, -15.99 for money should be expressed as '-15.99'.
Duplicates being removed from intarray on subtraction of another intarray
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/intarray.html
Description:
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}
I have confirmed that I get the same result when using PostgreSQL 9 through
16 on DBFiddle. See here: https://dbfiddle.uk/i-eXKhFR
I am using the official PostgreSQL 16.2 Docker image for Debian (PostgreSQL
16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc
(Debian 12.2.0-14) 12.2.0, 64-bit).
Cheers
Tom
Locking
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/mvcc-intro.html Description: 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.
What are the minimum required permissions for pg_isready
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-pg-isready.html Description: Hello, The documentation does not specify what are the minimum required permissions for setting up pg_isready . I was wondering if this could be added so devops teams know how to create a user that is used only for monitoring postgresql. I assume this user will need to connect and nothing else ? I also assume we would need to drop public access for that user ? Thanks, Eugen
Incomplete sentence in the description for most_common_freqs
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/view-pg-stats.html Description: https://www.postgresql.org/docs/current/view-pg-stats.html#VIEW-PG-STATS It seems the ending clarifying sentence: "(Null when most_common_vals is.)" should rather be: "(Null when most_common_vals is null.)" Thanks, Nacho
Definition of random_page_cost seems to invert correlation of that setting and seq_page_cost
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/runtime-config-query.html Description: 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." Thanks, Nacho
Minor typo in Section, 27.2.8.4.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/warm-standby.html Description: Section, 27.2.8.4. Planning For High Availability is missing the word "the" as shown below .. "Such transaction commits may never be completed if any one of THE synchronous standbys should crash."
Issue with interval calculation when adding 1 year to a non-leap year
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/functions-datetime.html
Description:
Dear PostgreSQL Developers,
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'
This issue could potentially impact applications relying on accurate date
arithmetic, particularly in scenarios involving financial calculations or
date projections.
Thank you for your attention to this matter.
Sincerely,
Mohamed Riyazath
mdriyazath...@gmail.com
Non-blocking synchronization in libpq using pipeline mode
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/libpq-pipeline-mode.html Description: The calls to PQpipelineSync and PQsendFlushRequest may either report failure or success, but not that data could not be written as of yet because the request would block. Does this mean that 1. these functions will always block when invoked and the socket is not ready to accept the number of bytes that need to be written (assuming the number of bytes required to be written is greater than one byte) or 2. the synchronization or flush request need to be flushed manually with successive PQflush calls or 3. the functions will return an error condition when the connection is non-blocking and no data could be written. Any clarification in the documentation would be appreciated.
Clarify pg_rewind behaviour in case of no divergence
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-pgrewind.html Description: After the sentence "all other files, including new relation files, configuration files, and WAL segments, are copied in full." we should add "However, no files are copied if there is no divergence, i.e. if the source data directory is found to be compatible with the target data directory: in that case pg_rewind exits successfully without performing the synchronization."
documentation issue: listen_addresses does NOT take a comma-separated list of host names
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/runtime-config-connection.html Description: In the documentation, I read: 20.3.1. Connection Settings listen_addresses (string) Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. (...) I tried different versions of names and IPs, quoted in single, double and no quotes at all, comma separated between quotes and inside, like listen_addresses = '192.168.32.3','127.0.0.1' # or listen_addresses = '192.168.32.3,127.0.0.1' but `sudo journalctl -eb -u postgresql@14-main.service -o cat` gives me: Reloading PostgreSQL Cluster 14-main... Error: invalid line 61 in /etc/postgresql/14/main/postgresql.conf: listen_addresses = "192.168.32.3","127.0.0.1" postgresql@14-main.service: Control process exited, code=exited, status=1/FAILURE Reload failed for PostgreSQL Cluster 14-main. Reloading PostgreSQL Cluster 14-main... Error: invalid line 61 in /etc/postgresql/14/main/postgresql.conf: listen_addresses = "192.168.32.3,127.0.0.1" postgresql@14-main.service: Control process exited, code=exited, status=1/FAILURE Reload failed for PostgreSQL Cluster 14-main. Reloading PostgreSQL Cluster 14-main... Error: invalid line 61 in /etc/postgresql/14/main/postgresql.conf: listen_addresses = '192.168.32.3','127.0.0.1' postgresql@14-main.service: Control process exited, code=exited, status=1/FAILURE Reload failed for PostgreSQL Cluster 14-main. So I tried to follow the otherwise seen pattern and wrote into postgresql.conf: listen_addresses = '192.168.32.3' listen_addresses = '127.0.0.1' which worked. Note, that in the file itself, the comment repeats the (imho) misinformation. #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses;
Documentation for installing Python for PL/Python
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/plpython.html Description: Hi everyone, I am currently trying to install PL/Python on Windows and noticed that no documentation for that currently exists (I hope I didn't miss anything). Unfortunately, it's not so straightforward. The only information is currently available on some Stackoverflow posts, and I found the Stackbuilder documentation for enabling it, which unfortunately is only available for Postgres 14 (and therefore doesn't mention the correct Python version for newer Postgres versions): https://www.enterprisedb.com/docs/epas/14/language_pack/02_installing_language_pack/ I think the Postgres documentation should mention: - what Python one needs (So 3.11 for PG 16) - if only the Stackbuilder Python is supported or also a normal Python install. And if normal Python installs must be installed to some special path. The Stackbuilder version seems to differ from the normal one, it for example doesn't have the pip command, only pip3 (which might be confusing for beginners). It also seems to install itself in a location that is not writable for users, which the normal Python installer doesn't. - what needs to be added to PATH on Windows (and if it needs to be the System or user path.) - other environment variables like PYTHONPATH or PYTHONHOME, which might need to be set. (For standard Python installs one doesn't really need them, but Stackoverflow/the Stackbuilder docs say that one needs them for PL/Python.) - if any attention needs to be paid when installing custom libraries (I don't know, the fact that the stackbuilder installer installs Python installs the files into a non-user-writable directory causes the following warning: https://stackoverflow.com/questions/59997065/pip-python-normal-site-packages-is-not-writeable . But I'm not sure about the specific effects.) PS: Thanks a lot for maintaining postgres!
Ubuntu and Debian Download/Installation instructions enhancement
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/bug-reporting.html Description: Dear PostgreSQL Documentation Team, I hope this message finds you well. As a user and admirer of PostgreSQL, I greatly appreciate the effort and detail that goes into maintaining the project documentation and would like to take this opportunity to thank you for curating such a well crafted, valuable resource. This week I installed PostgreSQL 16 into an Ubuntu 22.04 LXD container, per these instructions https://www.postgresql.org/download/linux/ubuntu/ and observed a deprecation warning for apt-key on the following command: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)). I subsequently referred to the documentation which notes: DEPRECATION Except for using apt-key del in maintainer scripts, the use of apt-key is deprecated. This section shows how to replace existing use of apt-key. If your existing use of apt-key add looks like this: wget -qO- https://myrepo.example/myrepo.asc | sudo apt-key add - Then you can directly replace this with (though note the recommendation below): wget -qO- https://myrepo.example/myrepo.asc | sudo tee /etc/apt/trusted.gpg.d/myrepo.asc Make sure to use the "asc" extension for ASCII armored keys and the "gpg" extension for the binary OpenPGP format (also known as "GPG key public ring"). The binary OpenPGP format works for all apt versions, while the ASCII armored format works for apt version >= 1.4. Recommended: Instead of placing keys into the /etc/apt/trusted.gpg.d directory, you can place them anywhere on your filesystem by using the Signed-By option in your sources.list and pointing to the filename of the key. See sources.list(5) for details. Since APT 2.4, /etc/apt/keyrings is provided as the recommended location for keys not managed by packages. When using a deb822-style sources.list, and with apt version >= 2.4, the Signed-By option can also be used to include the full ASCII armored keyring directly in the sources.list without an additional file. This deprecation notice was introduced in Ubuntu Groovy Gorilla (20.10) which was released in October 2020. I therefore propose a documentation change for the Ubuntu instructions, altering the line command from: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - to: wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc as per the apt-key manual recommendation. This approach has been successful in my testing and has yielded no warnings or errors. The documentation proposes that an alternative location could be specified for the key, of which I do not have an opinion of at this time, therefore I would appreciate input on this location. Since Ubuntu tracks the upstream Debian repository, I propose the Debian instructions (https://www.postgresql.org/download/linux/debian/) are also updated with this change. Whilst this is not yet strictly a bug it will become one if/ when the deprecated apt-key functionality is removed. Resolving the warning would also improve the experience for new/ inexperienced users who are potentially installing PostgreSQL for the first time and do not understand the message. I therefore think it prudent to review and update these instructions at the earliest convenience. Thank you very much for considering my request. I look forward to your guidance on how best to proceed with this enhancement. Best regards, Daniel Dewberry
substring start position behavior
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/functions-string.html Description: Hey, I was confused by substring behavior today, when giving 0 as a start position. I understand now that string indices are 1-based, have a certain flexibility about where to start (allowing negative start positions), and that this is defined in the standard SQL spec. I'm comfy with all this, but I think it'd be nice to have a hint in the pg substring docs for nonpositive start positions, so that users don't have to have paid for the standard SQL spec to get past this. To me, substring seems like a relatively common function with relatively surprising behavior. I'd be happy to write up a patch as a next step -- I'm not sure this would count as a "trivial patch that serves an obvious need" (https://wiki.postgresql.org/wiki/Submitting_a_Patch). Only thing is, I can't seem to find the source repo for the docs. https://www.postgresql.org/docs/current/docguide-toolsets.html mentions a configure script, but I don't know which one. Best, Dan
Typos in dectoint() and dectolong() function's descriptions
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ecpg-informix-compat.html Description: Chapter 36. ECPG — Embedded SQL in C 36.15. Informix Compatibility Mode The first sentence in dectoint() and dectolong() function's descriptions contains part with typo: "Convert a variable to type decimal to ...". Should use "of" preposition here: "Convert a variable of type decimal to ...". Thank you!
incorrect order? in "D.1. Supported Features" of PostgreSQL 16 manual
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/features-sql-standard.html Description: In "D.1. Supported Features"(https://www.postgresql.org/docs/current/features-sql-standard.html), identifiers appear as following: F302INTERSECT table operator F303INTERSECT DISTINCT table operator F302-02 INTERSECT ALL table operator F304EXCEPT ALL table operator The correct result would be as follows: F302INTERSECT table operator F302-02 INTERSECT ALL table operator F303INTERSECT DISTINCT table operator F304EXCEPT ALL table operator
return type marked optional but isn't
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/sql-createfunction.html Description: the documentation for creating a function shows [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] but returns is not optional. At least when specifying LANGUAGE plpgsql. All the online advice suggests specifying RETURNS void as the solution.
Missing | ?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-security-label.html Description: Hi, FOREIGN TABLE object_name ...probably should have a following "|", I think? Cheers Tim
Stats and the query results on examples seem wrong
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/bloom.html
Description:
Created the table with exact scripts provided and the index size was;
SELECT pg_size_pretty(pg_relation_size('btreeidx'));
386 MB
SELECT pg_size_pretty(pg_relation_size('bloomidx'));
153 MB
So data used for examples were possibly from a different dataset.
Broken link in pgcrypto documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/pgcrypto.html Description: I was going through the links in pgcrypto documentation and I realized that one of the links at Useful Reading section do not work. interhack.net seems to be down for a while whereas interhack.com contains a portion of the contents of the former website in a different hierarchy. I did some digging and I believe we can update that link to the same set of information on the other website. Relevant page: https://www.postgresql.org/docs/current/pgcrypto.html#PGCRYPTO-NOTES-USEFUL-READING Current link that does not work: http://www.interhack.net/people/cmcurtin/snake-oil-faq.html Last capture at Web Archive: https://web.archive.org/web/2024090731/http://www.interhack.net/people/cmcurtin/snake-oil-faq.html My suggestion for updating the link: http://web.interhack.com/publications/snake-oil-faq Thanks, Hanefi
bgwriter_delay
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/runtime-config-resource.html Description: Would be nice to add the limits to the doc, according to my pg15 instance I can't set bgwriter_delay more than 1 ms. (I set it to 60 s and got a FATAL upon startup)
text and varchar are not equivalent
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/datatype-character.html
Description:
The documentation implies that the data types text and varchar are
equivalent, but this is not the case with this test in Postgresql version
16.
CREATE TEMPORARY TABLE test(ch char, vc varchar, txt text, txt0 text);
INSERT INTO test VALUES (' ', ' ', ' ','');
SELECT ch = vc AS ch_vc, ch = txt AS ch_txt, ch = txt0 AS ch_txt0,
vc = ch AS vc_ch, vc = txt AS vc_txt, vc = txt0 AS vc_txt0,
txt = ch AS txt_ch, txt = vc AS txt_vc, txt = txt0 AS txt_txt0,
txt0 = ch AS txt0_ch, txt0 = vc AS txt0_vc, txt0 = txt AS txt0_txt
FROM test;
ch_vc ch_txt ch_txt0 vc_ch vc_txt vc_txt0 txt_ch txt_vc txt_txt0
txt0_ch txt0_vc txt0_txt
TRUEFALSE TRUETRUETRUEFALSE FALSE TRUEFALSE TRUE
FALSE FALSE
The tests are showing that the space character is treated differently in a
one character string. Whilst varchar = text, the comparison with char is
treated differently with text and varchar
Pathetic pedantry
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-createdatabase.html Description: Hi, [ STRATEGY [=] strategy ] ] ...has a superfluous ], I think? Tim
Add a different archive_command example for Linux / Unix
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/bug-reporting.html Description: Hello, in https://www.postgresql.org/docs/16/continuous-archiving.html#BACKUP-ARCHIVING-WAL the example given could be improved for Linux / Unix environment. As cp command is buffered it means it could return success although the data didn't reach the disk which puts backups at risk. I propose to use dd command with its fsync option. So the actual equivalent example would be : archive_command = 'dd if=%p of=/mnt/server/archivedir/%f bs=1M conv=fsync,excl status=none' # Unix What do you think ? Regards Gilles
20.5.1
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.
system column
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ddl-system-columns.html Description: Will be clear to have a picture of how they are in the database.
Broken link
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/recovery-config.html Description: This page has a link that says "See the release notes for PostgreSQL 12 for details on this change." https://www.postgresql.org/docs/current/recovery-config.html The link does not go to the release notes though.
Possible spelling errors
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/auth-username-maps.html Description: Hello. I am not sure, but 'can be include directives' looks very strange to me: As for pg_hba.conf, the lines in this file can be include directives, following the same rules. I suppose it should be 'can include directives' or similar. Thanks.
Missed information about clientname=CN option
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/auth-cert.html Description: Hello. This page missed information about supported clientname=CN/DN option and describes only `map` option. Also `clientcert` is described not in format. I expect it was documented under `map` as next list item of supported options. Thank you.
unknown option --subject
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/auth-pg-hba-conf.html Description: Hello. On this page this command does not work `openssl x509 -in myclient.crt -noout --subject -nameopt RFC2253` unknown option --subject We should use only one dash `-subject`
SET ROLE versus SET SESSION AUTHORIZATION
The following documentation comment has been logged on the website: 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?
It would be nice to clarify is there any point in select queries pipelining
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/protocol-flow.html Description: Greeting! Please consider the following exchange with a PG database (Kotlin + Reactor+ r2dbc-postgresql): ``` 2951.063166127.0.0.1 50591 127.0.0.1 32797 PGSQL 111 >Q ---> BEGIN ISOLATION LEVEL REPEATABLE READ, READ WRITE 2961.063219127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=1 Ack=56 Win=6373 Len=0 TSval=3266177882 TSecr=3728690767 3011.069912127.0.0.1 32797 127.0.0.1 50591 PGSQL 73 Q --> select * from ... (#2) 7131.099858127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=551 Win=6365 Len=0 TSval=3266177919 TSecr=3728690804 7151.099985127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q --> select * from ... (#4) 7171.19127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=1046 Win=6358 Len=0 TSval=3266177919 TSecr=3728690804 7191.100082127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q --> select * from ... (#1) 7201.100106127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=1541 Win=6350 Len=0 TSval=3266177919 TSecr=3728690804 7221.100164127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q --> select * from ... (#3) 7231.100192127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=2036 Win=6342 Len=0 TSval=3266177919 TSecr=3728690804 7351.114695127.0.0.1 32797 127.0.0.1 50591 PGSQL 424 Results for #2 7371.114741127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=386 Win=6364 Len=0 TSval=3728690818 TSecr=3266177933 7731.121732127.0.0.1 32797 127.0.0.1 50591 PGSQL 1468 Results for #4 7741.121757127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=1798 Win=6342 Len=0 TSval=3728690826 TSecr=3266177941 7851.126793127.0.0.1 32797 127.0.0.1 50591 PGSQL 594 Results for #1 7861.126820127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=2336 Win=6334 Len=0 TSval=3728690831 TSecr=3266177946 8051.135197127.0.0.1 32797 127.0.0.1 50591 PGSQL 497 Results for #3 8061.135222127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=2777 Win=6327 Len=0 TSval=3728690839 TSecr=3266177954 8471.138848127.0.0.1 50591 127.0.0.1 32797 PGSQL 68 >Q --> COMMIT 8481.138876127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=2777 Ack=2048 Win=6342 Len=0 TSval=3266177958 TSecr=3728690843 8531.144624127.0.0.1 32797 127.0.0.1 50591 PGSQL 74 EntityConverter().convert(row) } .all() .reduce(EntityReducer()) } }.`as`(transactionalOperator::transactional) ``` 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). Thanks!
ERROR: plpython3u
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-join.html Description: CREATE EXTENSION plpython3u; [2024-01-25 05:56:50] [58P01] ERROR: could not load library "C:/Program Files/PostgreSQL/16/lib/plpython3.dll": The specified module could not be found.
Missing information on '-X' in section 26.3.6.1.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/continuous-archiving.html Description: Hi! I'm reading through the documentation and so far I have to say this is the best documentation I have ever encountered, thank you! I noticed, that in section 26.3.6.1. it's not specified, what the -X parameter should be set to (stream or fetch, or whether it even matters). I could continue with trial and error, but it confused me a bit. Thank you and have a nice day! Lukas
About COPY
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-populate.html Description: COPY weather FROM '/home/user/weather.txt'; is not working it requires additional commands for me.
Creating the scripts and compiling the C files containing user-defined functions and types.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-sql-intro.html Description: " Directory of C:\postgresql-16.1\src\tutorial 11/06/2023 11:17 PM . 11/06/2023 11:17 PM .. 11/06/2023 11:04 PM62 .gitignore 11/06/2023 11:04 PM 1,579 advanced.source 11/06/2023 11:04 PM 6,091 basics.source 11/06/2023 11:04 PM 5,169 complex.c 11/06/2023 11:04 PM 7,580 complex.source 11/06/2023 11:04 PM 3,078 funcs.c 11/06/2023 11:04 PM 4,531 funcs.source 11/06/2023 11:04 PM 900 Makefile 11/06/2023 11:04 PM 460 README 11/06/2023 11:04 PM 5,506 syscat.source 10 File(s) 34,956 bytes 2 Dir(s) 153,064,722,432 bytes free" it shows C:\postgresql-16.1\src\tutorial>make 'make' is not recognized as an internal or external command, operable program or batch file. and this also. C:\postgresql-16.1\src\tutorial>Makefile 'Makefile' is not recognized as an internal or external command, operable program or batch file.
Supplement of procedure suggested
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/routine-vacuuming.html Description: In https://www.postgresql.org/docs/current/routine-vacuuming.html section "25.1.5. Preventing Transaction ID Wraparound Failures" there is a 5-step procedure to handle the "database is not accepting commands" situation, see "Instead, follow these steps:" Please, add a notice about temporary tables - they can also prevent vacuuming! This is hinted in section "25.1.6. The Autovacuum Daemon", but only indirectly: "Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via session SQL commands." Just today on a production, I performed (in a single-user mode) vacuum, even vacuum freeze, and after ending it, DB still didn't accept users. The useful detection SELECT was SELECT oid, oid::regclass, relkind, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relfrozenxid <> 0 ORDER BY age(relfrozenxid); Then I realized these were the temporary tables and dropped all the temporary schemas ... once in a single-user mode :(
Grammar suggestion
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/ddl-constraints.html Description: Dear postgres Team, I happened to have stumbled across a tricky to read sentence in Ch. 5.45 'Foreign Keys' (Postgres Version 15). The original sentence: "Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null". Here's the revised version: "Normally, a referencing row does not need to satisfy the foreign key constraint if any of its referencing columns are null." A bit restructuring in beginning of this sentence can it make it grammatically complete and clear for the reader. Kindly yours, Sayed Dileri.
incorrect description of Python try / except
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/plpython-subtransaction.html Description: On page https://www.postgresql.org/docs/current/plpython-subtransaction.html, Note that the use of try/catch is still required. should read Note that the use of try/except is still required.
Problems when using unaccent
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/unaccent.html
Description:
I'm trying to query a database with unaccent, but PostgreSQL gives me
exceptions:
ERROR: function unaccent(character varying) does not exist at character
139
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
STATEMENT: SELECT "cidades"."id", "cidades"."nome", "cidades"."populacao",
"cidades"."estado_id", "cidades"."complemento" FROM "cidades" WHERE
UPPER(UNACCENT("cidades"."nome")::text) LIKE '%' ||
UPPER(REPLACE(REPLACE(REPLACE((UNACCENT('ara')), E'\\', E''), E'%',
E'\\%'), E'_', E'\\_')) || '%' ORDER BY "cidades"."nome" ASC LIMIT 21
This query is being generated according to parameters passed by Django.
Example:
cidades.filter(**{'nome__unaccent__icontains': termo_selecao_cidade})
Postgres compilation instructions do not work on modern debian or ubuntu
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/install-make.html Description: Hello, I Hope you've enjoyed a festive period. Thanks for all your hard work, postgres is amazing. Small suggestion for installation instructions on debian / ubuntu boxes: apt install -yqq build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache git libicu-dev pkg-config works, what is listed misses git, libicu-dev and pkg-config git may be optional for those downloading from a tarball, but something to untar would then be required, as would gzip most-likely. Even with the above, make world and make world-bin and make check, do not succeed on arm64, so I've no idea if the build is reporting success, but silently failing (I get return code zero, so it should pass a compile in CI) For testing this, I use docker as it's fast-enough to burn down the world and check my instructions work (see below): Compiling postgres 16 docker run --rm -it ubuntu:22.04 Or docker run --rm -it debian Then within container install system-build-dependencies apt update -yqq apt install -yqq build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache git libicu-dev pkg-config Now time to clone Postgres git clone https://github.com/postgres/postgres.git cd postgres git checkout REL_16_1 git checkout -b release/16.1 Now we can build Postgres ./configure make -j4
string_agg
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/functions-aggregate.html Description: Please add the argument "distinct" to the documentation of the aggregate-function "string_agg". It's used to omit any duplicates in the result string.
Documentation does not describes format for access privileges: =Tc/user
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ddl-priv.html Description: Hello. The page https://www.postgresql.org/docs/current/ddl-priv.html does not describe what =Tc/user means. Also I did not find a link to appropriate page which describes this. Specifically I do not understand how 'user=Tc/user' differs from '=Tc/user'. It would be nice if documentation will be extended. Thank you.
Inconsistency in: 2.3. Creating a New Table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-table.html Description: The text in: 2.3. Creating a New Table, states: "... you can type the command aligned differently than above, or even all on one line. Two dashes (“--”) introduce comments. Whatever follows them is ignored up to the end of the line ..." The last phrase contradicts the first one. If there is a comment on a column definition you cannot continue to define a new column on the same line. It looks like commas were column separators before comments were allowed to appear. They rather should have become column terminators appearing after an optional comment?
'bar' shouldn't be a string in example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-alterforeigndatawrapper.html Description: Hi, Re. this example: ALTER FOREIGN DATA WRAPPER dbi OPTIONS (ADD foo '1', DROP 'bar'); I think "bar" is akin to "foo" and therefore should be similarly an option? (i.e. not a string value)? Many thanks Tim
Section 4.1.2.3 repeats information presented in section 4.1.1
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-syntax-lexical.html Description: it just threw me off to read the exact same thing twice in quick succession, I'd suggest linking to section 4.1.2.3 in section 4.1.1 instead.
unclear wording re: spoofing prevention on network connections
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/preventing-server-spoofing.html Description: When I read: To prevent spoofing on TCP connections, either use SSL certificates and make sure that clients check the server's certificate, or use GSSAPI encryption (or both, if they're on separate connections). It takes some thought to figure out what "separate connections" are being referred to. Does it mean separate TLS connection and non-tls-with-gssapi-encryption?
pg_stats entries visibility with RLS enabled
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/view-pg-stats.html Description: First paragraph for the pg_stats view says: "This view allows access only to rows of pg_statistic that correspond to tables the user has permission to read, and therefore it is safe to allow public read access to this view." I think it would be worth to mention that with RLS enabled for the table the rows will also be hidden to not reveal values from the rows not visible to the user. Wojtek
Inconsistent information on default ordering for ORDER BY clause
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-select.html Description: At the top of the sql-select page it says "If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce." but later it says "Optionally one can add the key word ASC (ascending) or DESC (descending) after any expression in the ORDER BY clause. If not specified, ASC is assumed by default." The first statement implies that you cannot rely on the default ordering, but the second implies that you can (and it will be ASC).
Missing "CONSTRAINT" keyword in ADD CONSTRAINT form?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-alterdomain.html Description: (apologies, I managed to send this too soon, just now, somehow) The example for ALTER DOMAIN shows: ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5); ...yet the syntax suggests no CONSTRAINT-keyword (I'm guessing?) should be used: ALTER DOMAIN name ADD domain_constraint [ NOT VALID ] When it should, I think?
libpq-ssl suggestion:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/libpq-ssl.html Description: I noticed that it would be helpful to list the connection parameters and environment variables for the various SSL files in the table "Table 34.2. Libpq/Client SSL File Usage": https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-FILE-USAGE In particular, I think the first column could be renamed from "File" to "Default File Location", and two new columns added with these contents: Connection parameter - sslcert - sslkey - sslrootcert - sslcrl Environment variable - PGSSLCERT - PGSSLKEY - PGSSLROOTCERT - PGSSLCRL It might also be useful to reiterate near the table the differences on Windows, e.g.: On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key.
Restore to a new database from a backup (.tar) generated from
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/app-pgrestore.html Description: After creating a new database in postgreSQL : CREATE DATABASE x; I dump it in a .tar file: pg_dump -p 5433 -U postgres -W -F t x > C:\pgBackups\x.tar Then I restore it with the restore command: pg_restore -p 5433 -U postgres -W -d x -C --verbose C:\pgBackups\x.tar RESULT : pg_restore: création de DATABASE « x » pg_restore: pendant le traitement de la TOC (« PROCESSING TOC ») : pg_restore: de l'entrée TOC 3315 ; 1262 29865 DATABASE x postgres pg_restore: erreur : could not execute query: ERREUR: la base de données « x » existe déjà La commande était : CREATE DATABASE x WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'French_Canada.1252'; pg_restore: connexion à la nouvelle base de données « x » pg_restore: attention : erreurs ignorées lors de la restauration : 1 ' PROBLEM WITH CODE PAGE 1252 and the fact that the (empty) db already exists if I remove the database (x) from PostgreSQL (Version 15.5)DROP DATABASE x; and I try the same restore command: pg_restore -p 5433 -U postgres -W -d x -C --verbose C:\pgBackups\x.tar RESULT : pg_restore: connexion à la base de données pour la restauration Mot de passe : pg_restore: erreur : la connexion au serveur sur « localhost » (::1), port 5433 a échoué : FATAL: la base de données « x » n'existe pas ' The problem is that the database does not exist So, I can I create a database from scratch, in PostgreSQL, from a backup (.tar) file generated from pg_dump ?
Typo in '8.5. Date/Time Types' v16
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/datatype-datetime.html Description: Hello, In the interval example outputs, for postgres and postgres_verbose. It says mons instead of months.
Aggregate functions description page should link to Aggregate Functions list page
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-agg.html Description: There are two documentation pages with the same title "Aggregate Functions". One describes what they are and one lists all of them. The two pages should provide links to each other. https://www.postgresql.org/docs/current/tutorial-agg.html and https://www.postgresql.org/docs/current/functions-aggregate.html
Clarification regarding CREATE TABLE LIKE and FOREIGN KEYS
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-createtable.html Description: It is not clear to me that foreign keys are copied or not when duplicating a table using CREATE TABLE ... LIKE ... INCLUDING ALL. I had to actually test to check what it does.
Table-space documentation
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.
"name" vs "alias" in datatype table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/datatype.html Description: I think there could be some clarification of what is a "name" vs. "alias" on the datatypes table. Right now, what's in the "Aliases" column is sometimes postgres's internal type (e.g. `pg_catalog.int4`), and sometimes the "pretty name", (e.g. `integer`). How about if this table was laid out still as one row per type, but the columns were "Names" (plural) and "Internal Name" (singular). Names would contain both "int" and "integer" (neither of which appear in `pg_type`) and "Internal Name" would contain "pg_catalog.int4", the "real" name. Then you could also include arrays here, so that `text[]` would appear in the names column and `pg_catalog._text` would appear in the "Internal Name" column.
Mention that there will be no feedback.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/tutorial-createdb.html Description: You might want to mention that createdb will give no feedback after creating database. Neither will dropdb.
Additional Notes
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
T is a mandatory date time separator in RFC3339 but documentation states differently
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/git.html Description: >ISO 8601 specifies the use of uppercase letter T to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems. https://www.postgresql.org/docs/current/datatype-datetime.html Short answer: T (or t as discouraged alternative). After reading on this as much as I could, it turns out the time separator must be a T or t. What has made think this way is first of all this thread in the GNU lists where F. Alexander Njemz contacted the authors of RFC3339 Graham Klyne and Chris Newman asking if T is mandatory and got this response from Mr. Klyne: > In short: "yes" > > Per section 5.5, the intent in this draft was to specify a timestamp format using elements from and compatible with 8601, but eliminating as far as reasonable any variations that could make timestamp data harder to process. This includes making the 'T' mandatory in date+time values. Just for clarity's sake, this is stated in the section 5.5: > Simplicity is achieved by making most fields and punctuation mandatory. This clearly clashes with a non-mandatory T and strongly makes me think that the this syntax in that problematic passage refers to ISO8601 and not RFC3339. https://stackoverflow.com/questions/63783868/what-are-valid-date-time-separators-in-rfc3339-strings/63882162#63882162
Missing ;
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-select.html Description: Hi, To be consistent, the "with" example should end in a ";". WITH t AS ( SELECT random() as x FROM generate_series(1, 3) ) SELECT * FROM t UNION ALL SELECT * FROM t; Possibly the most pathetic contribution ever? Apologies. Tim
add new acronym "AM"
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/acronyms.html Description: while reading the progres codebase, i could find you're using the acronym "AM" which denotes "Access Method". it's be nice to add it to the list of acronyms
CREATE SUBSCRIPTION issue
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/logical-replication-row-filter.html Description: Hey I'm using create subscription to subscribe to a table. I have two different hosts and I want to setup replication between them. In host 1 I have one schema public and a table domain. In host 2 I have multiple schemas schema1, schema2, schema3. I want to copy the data from the host1 to host2 into schema1 and 2 and 3. While setting up the subscription it's giving me error saying 'relation "public.domain" does not exist'. because in host2 the table is created in schema1. So table would look like "schema1.domain". Any idea on how we can setup such subscription such that schema name shouldn't be an issue.
Example for Unique Partial Indexes
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/indexes-unique.html Description: I would like to request an improvement to the PostgreSQL documentation, specifically in the sections "11.6. Unique Indexes" and "11.8. Partial Indexes." Currently, the documentation does not provide information on how to create unique partial indexes. Unique partial indexes are valuable for enforcing uniqueness of a column's value over a subset of a table. To enhance the documentation's completeness and to provide a clear example for users, I propose adding an entry in one of these sections with an example as follows: ```sql Copy code -- Example of Creating a Unique Partial Index -- This index enforces uniqueness of the 'column' over a subset of the table CREATE UNIQUE INDEX name ON table (column [, ...]) WHERE (condition); This addition will assist users in understanding how to create unique partial indexes and help them enforce uniqueness over specific subsets of their tables. Please consider incorporating this addition into the documentation. ``` Thank you for your attention to this request, and I believe it will contribute to the clarity and comprehensiveness of the PostgreSQL documentation.
jsonb array accessors
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/datatype-json.html Description: https://dbfiddle.uk/Dr-c-nqL multiple subscripts for jsonb arrays do not behave as described on Table 8.25 in Postgres 16 docs / 8.14.7 jsonpath
Please make a note regarding the PL/pgSQL FOUND variable
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-createtableas.html Description: Since I was recently bitten by the fact that CREATE TABLE AS doesn't set the PL/pgSQL variable FOUND, it would be nice if that were explicitly noted in the documentation. Alternatively, if it COULD set the FOUND variable, that would be great, although I realize the difficulty of that when coupled with IF NOT EXISTS.
^@ operator vs start_with function
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/functions-string.html Description: https://www.postgresql.org/docs/16/functions-string.html Said text ^@ text → boolean Returns true if the first string starts with the second string (equivalent to the starts_with() function). 'alphabet' ^@ 'alph' → t This is not true equivalent, because ^@ can be accelerated by special SP-GiST index https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html while function starts_with() perhaps no. Thus need to mention in the description of ^@ it can be accelerated by the special index with link to it. And in the description on start_with() need to mention that alternative ^@ is better, because it can be accelerated by index, while the function is not.
Full Text Search
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/textsearch-limitations.html Description: Hello, In the FTS/Limitations part of the documentation, it says : "Another example — the PostgreSQL mailing list archives contained 910,989 unique words with 57,491,343 lexemes in 461,020 messages." How could the number of lexemes be greater than unique words ? Thank you Julien
pg_isready --dbname option is broken. So it should not be in the manual
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-pg-isready.html Description: the --dbname option in pg_isready seems not to work propperly. the tool returns 'ok' as long as the cluster itselft is running, no matter how wrong the bdname might be. as this seems to be a ~10 year old misbehaviour as per the below thread I think it should be removed from the manual. https://www.postgresql.org/message-id/flat/52840D38.9070604%40agliodbs.com
19.9. Secure TCP/IP Connections with SSL
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ssl-tcp.html Description: There is no mention of key lengths on the manual page about SSL/TLS connections even though there are restrictions. It probably depends on the build which is why it's been omitted, but I think to help new people it would be great to have a Note box that covers key lengths restrictions. It came up on reddit today and while the following is in the error log.. FATAL: could not load server certificate file "/etc/postgresql/16/main/server.crt": ee key too small It is amongst a chain of other messages and has a long line such that it could be missed as it's truncated (though it should still have been spotted of course). Regardless, I like the idea of all the things you need to do/be mindful of being in the manual. It could be something like.. Note Some builds of PostgreSQL specify a minimum key length for certificates to enforce best-practices. If the key you use is does not meet or exceed this minimum length PostgreSQL will fail to start. It's common practice to require a key of at least length 2048.
