Addition of Github link to odument
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-sql-intro.html Description: I feel, for the viewers or learners of Postgresql will be benetial to add the link to the /src/tutorial on PostgreSQL's GitHub page will be easier to download the tutorial if they used binary installation and missing the /src/tutorial. Please consider adding this link in the document -> https://github.com/postgres/postgres
tar command example for backup file
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/backup-file.html Description: It would be nice to change the tar command example to: tar -cf backup.tar -C /usr/local/pgsql/data . instead. Otherwise, the /usr/local/pgsql path gets stored in the tar archive which can be annoying to extract to another location (especially since not all versions of tar support the newer --strip-components parameter).
21.12. Certificate Authentication, terminology
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/auth-cert.html Description: Re: https://www.postgresql.org/docs/current/auth-cert.html and "21.12. Certificate Authentication" "Certificate Authentication" as used in this context is typically called "Mutual Authentication." The term "Certificate Authentication" does not make a lot of sense since a user agent is almost always authenticating the server using a X.509 certificate. When only the server authenticates itself, its usually referred to as "Server Authentication" or less frequently "One-way Authentication." The use of Pre-Shared Keys (PSK) and Secure Remote Passwords (SRP) are kind of rare. The cipher suites perform Mutual Authentication without the need for certificates. Ironically, PSK and SRP provide channel binding, which improves security of the channel by binding authentication to channel setup. The parties mutually authenticate or the channel setup fails. Channel binding via PSK and SRP should be used more frequently then it is. From RFC 8446, Section 1: 1. Introduction The primary goal of TLS is to provide a secure channel between two communicating peers; the only requirement from the underlying transport is a reliable, in-order data stream. Specifically, the secure channel should provide the following properties: - Authentication: The server side of the channel is always authenticated; the client side is optionally authenticated. Authentication can happen via asymmetric cryptography (e.g., RSA [RSA], the Elliptic Curve Digital Signature Algorithm (ECDSA) [ECDSA], or the Edwards-Curve Digital Signature Algorithm (EdDSA) [RFC8032]) or a symmetric pre-shared key (PSK). From RFC 8446, Appendix E: E.1. Handshake The TLS handshake is an Authenticated Key Exchange (AKE) protocol which is intended to provide both one-way authenticated (server-only) and mutually authenticated (client and server) functionality. At the completion of the handshake, each side outputs its view of the following values: ... [1] https://www.rfc-editor.org/rfc/rfc8446
Tablespace with multiple locations
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-createtablespace.html
Description:
Hello,
I m kindly requesting a clarification regarding a debatable subject in one
of my projects, that if it's possible to create one global tablespace and
assign many locations like the following example:
create tablespace TBS1 location ('/pgdata1','/pgdata2');
Which will be helpful on database migration, once the new database to be
restored needs further available space than the created tablespace ( if the
new DB is larger than the existing tablespace ), so it can be automatically
distributed on the assigned locations ( filesystems ) with no need for
manual intervention to distribute the schema objects between many
tablespaces that can't be done automatically.
And if a tablespace needs to be extended on a physical partitioned disk
device that is not configured as a logical volume.
Thank you,
Hussein Younes
Tutorial make command comment
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/tutorial-sql-intro.html Description: It would be valuable for someone who only just clones the repo, to mention that before running make to get the tutorial build, ./configure must be run at the root of the repo for other Makefiles to be populated. Kind regards, Efren
random-page-cost
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/runtime-config-query.html Description: Two comments on random-page-cost parameter here https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST 1) Adding a paragraph reminding us what random-access really mean in postgres would be extremely valuable. I find the first paragraph from the index - only scan section perfect. "This means that in an ordinary index scan, each row retrieval requires fetching data from both the index and the heap. Furthermore, while the index entries that match a given indexable WHERE condition are usually close together in the index, the table rows they reference might be anywhere in the heap. The heap-access portion of an index scan thus involves a lot of random access into the heap, which can be slow, particularly on traditional rotating media. " 2) A question, based on the definition of what random-access mean in postgres (hits to the heap) does the random-page-cost affect index-only and covering index scans at all? (assume zero heap fetches from hints etc.)? if no can we also add a note there. thank you! Hussein
There is no command pg_ctl reload in Postgresql 13 clusters
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/config-setting.html Description: Could you provide additional information about pg_ctlcluster command? On postgrsql 13 clusters there is no pg_ctl reload. So we should not use `pg_ctl reload` instead eg `pg_ctlcluster 13 main reload` (after listing clusters with `pg_lsclusters`).
Postgres Partitions Limitations (5.11.2.3)
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! Thanks, Bryce
8.5.2 "integral" - "integer"
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-datetime.html Description: I think, the "integer" will be good choice instead "integral'": 8.5.2 ... "The offset will be shown as hh (hours only) if it is an integral number of hours, else as hh:mm if it is an integral number of minutes, else as hh:mm:ss. (The third case is not possible with any modern"
The documentation for storage type 'plain' actually allows single byte header
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/index.html
Description:
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.
Background worker
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/bgworker.html Description: Hi Team, The glossary is a really nice summary of what each term means. The background worker gives a nice summary as to what it does "Background worker (process) Process within an instance, which runs system- or user-supplied code. Serves as infrastructure for several features in PostgreSQL, such as logical replication and parallel queries. In addition, Extensions can add custom background worker processes." Then it links to Chapter 48 For more information, see Chapter 48. to learn more but I can't find any more information about logical replication and parallel queries in relation with background workers aside from user extensions. Would like to learn more about that thanks. thanks and keep up the good work Hussein
Typo in 2.7 Aggregate Functions
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-agg.html Description: Near the end of Chapter 2.7 Aggregate Functions of the documentation, the command FILTER is introduced. The full query is SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30) FROM weather GROUP BY city HAVING max(temp_lo) < 40; and the output shows a count value of 5. This is an error. In total we only have 3 rows in the table so far. Furthermore, none of the rows have temp_lo < 30. If I'm not mistaken, the count value should be 0. Thanks, Jimmy
broken link
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/index.html Description: The FAQ page https://wiki.postgresql.org/wiki/FAQ has a broken link at the very bottom: "Bruce Momjian's presentation" http://momjian.us/main/presentations/features.html#securing
Adjustment to pg_stat_statements F.32
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/docguide.html Description: The default limit of 5000 statements applied by the pg_stat_statements.max parameter is not mentioned until farther down in the article at F.32.4 . For ease of reading and use by those of us that don't do configuration only support, I recommend making a note either in F.32.1 or the main description of F.32 to something of the degree: """ Note This table/view is not an exhaustive list of all distinct queries. Only the most commonly executed queries are saved up to a certain limit after which the less executed queries are discarded. That limit is defined by pg_stat_statements.max as described in section F.32.4 Configuration Parameters. """
gen_random_uuid is only available with pgcrypto enabled
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/functions-uuid.html Description: Regarding the documentation on this page: https://www.postgresql.org/docs/15/functions-uuid.html It think the documentation should state clearly, that postgres natively does not have any UUID generation functions. You can use the `pgcrypto` or `uuid-ossp` extensions though: CREATE EXTENSION "pgcrypto"; /* enable pgcrypto extension */ SELECT public.gen_random_uuid(); /* -> uuid */ See https://www.postgresql.org/docs/current/pgcrypto.html#id-1.11.7.37.11 CREATE EXTENSION "uuid-ossp"; /* enable uuid-ossp extension */ SELECT public.uuid_generate_v4(); /* -> uuid */ See https://www.postgresql.org/docs/current/uuid-ossp.html
https://www.postgresql.org/account/comments/new/15/sql-alterdatabase.html/
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-alterdatabase.html Description: Would love to see more information about each of the ALTER DATABASE parameters... a specific... "connlimit"... -1 means no connections, but what does 0 mean? Are there users that connect even though the connection limit is exceeded. Is there special allowances for other database connections, ie backups?
Naming of network_ops vs. inet_ops for SP-GIST
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html Description: I wanted to add an SP-GIST index for an inet field ip_address In https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html network_ops is stated as the built-in opclass for (inet, inet) On this basis, on this version of pgsql: psql (15.1 (Debian 15.1-1.pgdg110+1), server 14.6 (Debian 14.6-1.pgdg110+1)) I entered the following command: # create index concurrently ip_address_spgist_ban_by_ip on ban_by_ip using spgist (ip_address network_ops); ERROR: operator class "network_ops" does not exist for access method "spgist" However, this worked: # create index concurrently ip_address_spgist_ban_by_ip on ban_by_ip using spgist (ip_address inet_ops); CREATE INDEX This created the index: "ip_address_spgist_ban_by_ip" spgist (ip_address) which worked as expected: # EXPLAIN ANALYZE select * from ban_by_ip where ip_address >>= '1.2.3.4' and now() < banuntil_datetime; QUERY PLAN Index Scan using ip_address_spgist_ban_by_ip on ban_by_ip (cost=0.14..2.57 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1) Index Cond: (ip_address >>= '1.2.3.4'::inet) Filter: (now() < banuntil_datetime) Planning Time: 0.149 ms Execution Time: 0.027 ms (5 rows) I notice inet_ops, not network_ops, is mentioned in the docs for 13. Perhaps it was renamed to network_ops in 15 but not 14?
Not an error but a difficult wording
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"
Suggestion for deprecated spellings
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/sql-createrole.html Description: From time to time some spelling for given command gets obsolete, yet it is shown in the syntax on "equal rights" as other valid clauses. For instance see `CREATE ROLE` with deprecated spellings like `IN GROUP` or `USER`. I guess it would be useful to see those spellings visually marked as deprecated in Synopsis section (with e.g. strike-through or whatever suits better). Otherwise, when consulting documentation, it often requires jumping from the synopsis to detailed description and back to check if given spelling is still applicable. Just a thought. :-)
1.3 Creating a database - windows 10x64
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-createdb.html Description: I installed pgsql with binary for windows from official website. Going through the tutorial I found that I have no createdb command and for me to create database I need to use sql statement CREATE DATABASE
documentation clarification of tsvector length limitation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/textsearch-limitations.html Description: I'm suggesting a documentation clarification to the statement "The length of a tsvector (lexemes + positions) must be less than 1 megabyte" from the page https://www.postgresql.org/docs/current/textsearch-limitations.html. It's not clear what "length" here means, especially in light of the parenthetical "(lexemes + positions)". Only lexemes and positions but not weights? The length() function on a tsvector gives the lexeme count, is that the limit here? Or would the storage space returned with the pg_column_size function give the length to which this limitation applies? (Trying this out, it gives numbers somewhat larger that what I get when looking at the text representations I see when querying the tsvector column.)
Getting out of copy out mode
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/protocol-flow.html Description: In copy out mode it is mentioned "Otherwise, any message type other than CopyData or CopyDone may be treated as terminating copy-out mode.". This is confusing to me. Does that mean database might return anything other than Async messages, ErrorResponse, CopyData, CopyDone, or CommandComplete?
unclear syntax explanation for multiple window definitions in a SELECT query
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-select.html Description: The syntax documentation for SELECT in SQL COMMANDS section is unclear in explaining how to use multiple window definitions. It's easy to read it and believe that multiple WINDOW keywords, one for each definition, are correct, which is not the case. See this stackoverflow question and answer on point: https://stackoverflow.com/questions/59651529/multiple-named-windows-in-a-postgres-query Thanks.
Transaction wraparound and read committed isolation level
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. 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.
MERGE examples not clear
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-merge.html
Description:
On this page: https://www.postgresql.org/docs/15/sql-merge.html
the first and second examples seems to be contrasted (by "this would be
exactly equivalent to the following statement"), however the difference does
not seem to related to the stated reason ("the MATCHED result does not
change"). It seems like the difference should involve the order of WHEN
clauses?
Of course, it might be that I don't understand the point, in which case
maybe the point could be stated more clearly?
Row Level Security Execution within the SQL Evaluation Pipeline
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-createpolicy.html Description: RLS documentation seems to say the user's predicate is evaluated AFTER the policy is evaluated. This presents to me an issue that I can't wrap my head around, and seems to confuse others as well. Setup: 1. We have a policy-enabled table that has millions of rows, with ID as a primary key. 2. There exists a row in this table whose ID column is 10. 3. User submits a query: SELECT * FROM MYTABLE WHERE id = 10. According to the documentation, the WHERE clause is not evaluated until AFTER the policy is evaluated, thus there is initially a full table scan of MYTABLE that returns only the set of rows that the user has access to (via the policy), THEN the WHERE clause is activated that reduces the row count to 1 or 0. This sounds non-performant, and if this is or is not the case, I think it should be more clearly explained. In addition, a link to a "best practices using the policy effectively" would be useful, as from reviewing stack overflow, there is lots of concern over performance of RLS.
Modifying Arrays
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/arrays.html
Description:
According to the official document of PostgreSQL 15, in the section 8.15.4.
Modifying Arrays there is a statement like:
The slice syntaxes with omitted lower-bound and/or upper-bound can be used
too, but only when updating an array value that is not NULL or
zero-dimensional (otherwise, there is no existing subscript limit to
substitute).
This statement is not true for the following statements or I am missing
something?
CREATE TABLE sal_emp (
nametext,
pay_by_quarter integer[],
scheduletext[][]
);
INSERT INTO sal_emp
VALUES ('Bill',
'{1, 1, 1, 1}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{2, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
INSERT INTO sal_emp
VALUES ('Carolx',
'{2, 25001, 25002, 25003}',
'{{"breakfastx", "consultingx"}, {"meetingx", "lunchx"}, {"meetingy",
"lunchy"}}');
INSERT INTO sal_emp
VALUES ('Sam',
'{2, 25001, 25002, 25003}',
'{}');
INSERT INTO sal_emp
VALUES ('Pam',
'{2, 25001, 25002, 25003}');
SELECT * FROM sal_emp;
UPDATE sal_emp SET schedule[1:2] = '{"asd", "asa"}'
WHERE name = 'Sam';
UPDATE sal_emp SET schedule[1:2] = '{"x", "y"}'
WHERE name = 'Pam';
SELECT * FROM sal_emp;
In order to test it: https://dbfiddle.uk/pHKY32u0
Thanks!
incorrect info in dblink examples
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/contrib-dblink-function.html Description: In your examples, AS t1(proname name, prosrc text) should actually be AS t1(proname text, prosrc text) This occurs frequently in the documentation at the following link: https://www.postgresql.org/docs/current/contrib-dblink-function.html Thanks, Doug
Mistake in statement example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/transaction-iso.html Description: I believe there is a mistake in an example on https://www.postgresql.org/docs/current/transaction-iso.html section 13.2.1: BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; COMMIT; The acctnum is expected to be 12345 in both cases.
I think that the transaction tutorial document (3.4) should mention transaction isolation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-transactions.html Description: Hi, I just discovered that by default, transactions are not entirely isolated. I think that the tutorial should mention this. The tutorial gives the example of a bank account, where you make two commands, one to increase the balance and one to decrease the balance, explaining why you need a transaction. The example, in which the command is "UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'", would indeed be correct with the default transaction isolation. However, if the transaction included getting the balance by a program, adding 100 to it, and then setting the balance, consistency would not always be kept, since another transaction could have modified the balance in between. By just reading the tutorial, the reader would have no idea that this may happen. I think that a notice saying that for absolute transaction isolation you should set the default transaction isolation to serializable, and be prepared to have transactions fail, and referring to the more details document, would help users prevent consistency bugs. Thanks, Noam
Make SSPI documentation clearer
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sspi-auth.html Description: The [current SSPI documentation](https://www.postgresql.org/docs/current/sspi-auth.html) reads: "SSPI authentication only works when both server and client are running Windows, or, on non-Windows platforms, when GSSAPI is available." I interpret that phrase like this: * there's a case where both server and client are running Windows * there's a case where both are running non-Windows What about mixed cases? When the client is non-Windows, then can it use SSPI? No, AFAIK not. So I'd suggest to make that phrase above clearer and completely explicit: "SSPI authentication works when both server and client are running Windows. When the server is on a non-Windows platform then the server must use GSSAPI if it wants to authenticate the client either via Kerberos or via Active Directory. A client on a Windows platform that connects to a non-Windows Postgresql server can either use SSPI (strongly encouraged) or GSS (much more difficult to set up) if it wants to authenticate via Kerberos or Active Directory. A client from a non-Windows platform must use GSS if it wants to authenticate via Kerberos or Active Directory." Thanks a lot for everything Postgres to you who is reading a considering this change!
bytea documentation error
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-binary.html Description: I believe there is a documentation error in the example for "8.4.1 bytea Hex Format" (https://www.postgresql.org/docs/current/datatype-binary.html#id-1.5.7.12.9). The example is `SELECT '\xDEADBEEF';` However, when I try that on my system [psql (13.9 (Ubuntu 13.9-1.pgdg20.04+1), server 13.10 (Ubuntu 13.10-1.pgdg20.04+1))], it works *but* the result is type `text` per `pg_typeof()`. I believe the example needs to be corrected with an explicit cast to `bytea`.
Seeming contradiction in 22.1
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/database-roles.html Description: In chapter 22.1 the following text seems to imply the role will have "the same name as the user that initialised" it and also the "role will be named postgres". Which cannot both be true, or perhaps the text is trying to say something else, but in doing so is not clear. "This role is always a “superuser”, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. "
Mistake in documentation (PG15+)
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-numeric.html Description: Greetings. Just found a mistake in documentation: https://www.postgresql.org/docs/15/datatype-numeric.html $$ Thus, a column declared as NUMERIC(2, -3) will round values to the nearest thousand and can store values between -99000 and 99000, inclusive. $$ But the correct range here would be from -99499 to 99499: select 99499::NUMERIC(2, -3);
Misleading "For more information..." placement
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/datatype-character.html
Description:
In the paragraph
> The characters that can be stored in any of these data types are
determined by the database character set, which is selected when the
database is
> created. Regardless of the specific character set, the character with code
zero (sometimes called NUL) cannot be stored. For more information refer to
Section 24.3.
The final sentence ("For more information refer to Section 24.3") is easily
read to refer to more information being available about character code zero
(NUL). However, section 24.3 has no mention of NUL, but rather the
available database character sets. I'd suggest moving that last sentence to
immediately after the first sentence, which ends with "...when the database
is created."
A really minor point, but it caught me off-guard.
Best,
-Randall
List Tables
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-table.html Description: Would be nice to add a paragraph after create table and before delete table with list tables.
Split_Part w/negative integer does not work
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/functions-string.html
Description:
When n is negative, an error occurs.
Documentation
split_part ( string text, delimiter text, n integer ) → text
Splits string at occurrences of delimiter and returns the n'th field
(counting from one), or when n is negative, returns the |n|'th-from-last
field.
split_part('abc~@~def~@~ghi', '~@~', 2) → def
split_part('abc,def,ghi,jkl', ',', -2) → ghi
Observation
SELECT split_part('abc,def,ghi,jkl', ',', -1);
generates
ERROR: field position must be greater than zero
SQL state: 22023
Or in code
DO $$
DECLARE
mytxt text;
BEGIN
mytxt = split_part('abc,def,ghi,jkl', ',', -1);
RAISE NOTICE '%', mytxt;
END $$
ERROR: field position must be greater than zero
CONTEXT: SQL statement "SELECT split_part('abc,def,ghi,jkl', ',', -1)"
PL/pgSQL function inline_code_block line 6 at assignment
SQL state: 22023
Minor typo in 13.3.5. Advisory Locks
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/explicit-locking.html Description: After the code snippet in the 6th paragraph of 13.3.5. Advisory Locks (https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS) I believe there is a mistake in this sentence (I've surrounded it with asterisks): "In the above queries, the second *form* is dangerous because the LIMIT...". I believe that "form" in the above sentence is actually meant to be "from", referencing the second line of code and its FROM clause in the snippet.
Confusion between psql options and pg_service file config
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/libpq-connect.html Description: Hi folks, I feel like I'm going in circles trying to square `psql` options (15: psql) with connection service file parameters (15: 34.17). I've lost exactly which page I started on, but it was something like this... - I start at https://www.postgresql.org/docs/15/app-psql.html - Under "Options" -c _command_ shows that I can run an arbitrary command on connection. Great! - But I'd like to specify something similar in the pg_service file, so I look up I look up the Connection Service File, section 34.17 - https://www.postgresql.org/docs/15/libpq-pgservice.html - So far, so good. The "parameters are connection parameters; see [Section 34.1.2](https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-PARAMKEYWORDS) for a list." - Following that link, I scroll down to the key word "options" which "Specifies command line options" presumably like those for psql. Then it says "For a detailed discussion of the available options, consult [Chapter 20](https://www.postgresql.org/docs/15/runtime-config.html)." - Suddenly, I don't know why I'm in "Server Configuration" (Chapter 20). Sure, it is a TOC with sections that document options, but mostly only relevant to a superuser. - I thought I'd get a list of options valid as arguments to the 'options' keyword. What am I suddenly doing here? It seems an odd landing point for the link. What I have found particularly confusing is some of the keywords for the pg_service file clearly duplicate the options for `psql` but some of them are equivalent, but not identical keywords (eg, `psql --username` vs pg_service.conf keyword 'user'). Are both valid, or are they restricted to their respective realm? (That's a question I'm asking the documentation to answer, not a question I'm directly asking you.) There is also no particularly clear connection between psql options and the connection service file; it's even hard to find where the app-psql.html page indicates that using a service name in conjunction with a connection service file can make invoking psql so much easier :- ) Additionally, since the concepts are so similar to each other, and the pages are reasonable long, it's easy to lose track of exactly which piece of documentation I am looking at for which feature. (not an easy problem to solve there, just an additional curveball) Hopefully, I've kept this all objective, and not let any of my frustration spill out; if I did, that certainly wasn't my intention! Thanks for all your work, -Randall
Cannot update the generation expression for a generated column / make the limitation explicit
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-altertable.html Description: In the many forms of ALTER TABLE, described in https://www.postgresql.org/docs/current/sql-altertable.html, it appears that a generated column, defined with generated always as (expression) stored, cannot have its generation expression updated. There is a DROP EXPRESSION option that can remove the generation expression, but no option to add back the generation expression with a different definition. My documentation recommendation is to be explicit about this limitation in both the ALTER TABLE web page and the page that discusses generated columns, https://www.postgresql.org/docs/15/ddl-generated-columns.html. Dropping the column and recreating it (1) to preserve column order would also require, I believe, dropping and recreating subsequent columns in the same table; (2) often requires dropping and recreating objects that reference the column unless there is a technique I have overlooked; (3) which can, for the PG database I support, require dropping and recreating a stack of dozens of SQL objects including materialized views that contain much of our content. So my related functionality recommendation is to add the option to ALTER TABLE to assign the generation expression. (The term generated can also involve identity columns or sequence references; I am not suggesting any changes to that functionality.)
Correction: Postgres emum documentation 8.7.4 should read 63 characters (instead of bytes)
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-enum.html Description: Postgres documentation, section 8.7.4 gives the limit on enum labels as 63 bytes. Testing this with an oversized string gives the following error: SQL Error [42602]: ERROR: invalid enum label Detail: Labels must be 63 characters or less.
recovery from continuous-archiving
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/continuous-archiving.html Description: As of version 14, the server will continue restore from the WAL files. It stops when the last requested file does not exist, which is true since the system does not know which WAL is the last one. So the server will not remove recovery.signal file in the end. It has to be removed manually which is contradicted to the document.
Bug in the documentation - to_tsquery( 'postgres:*' );
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/index.html Description: There is a bug in the documentation on the link https://www.postgresql.org/docs/current/datatype-textsearch.html postgres does not get stemmed to postgrby :to_tsquery( 'postgres:*' ); and SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ); returns f. I am using PostgreSQL 12.14 but the documentation for PostgreSQL 12 is not correct either. Here is the output from my system: SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' ); to_tsvector| to_tsquery --+-- 'postgraduate':1 | 'postgres':* (1 row) So the below mentioned citation seems to be wrong. Citation: Note that to_tsquery will process prefixes in the same way as other words, which means this comparison returns true: SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ); ?column? -- t because postgres gets stemmed to postgr: SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' ); to_tsvector | to_tsquery ---+ 'postgradu':1 | 'postgr':* which will match the stemmed form of postgraduate.
Packaging Related Objects into an Extension
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/extend-extensions.html Description: This needs a section on how to remove Extensions. My specific case is I have an old extension with no make file and i have to remove it manually. I have deleted all the files by looking at a sample make install log. But even then something remains because pg_upgrade still flags a c-extension that is already obsolete.
Information on setting the message level is too hard to find if you search for "debug"
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/runtime-config-client.html Description: It took me a good bit of searching to find the page for remembering that client_min_messages was the variable for the debug level. I think my basic stumbling block was that the syntax for RAISE uses DEBUG as the keyword, but "message level" is the term used elsewhere; so, I was looking for how to set the debug level rather than the message level. I'd just forgotten the lingo, but knew I'd changed it a long time ago in a galaxy far away, and would find it eventually. I'd searched for 'debug' and got these results: https://www.postgresql.org/search/?u=%2Fdocs%2F10%2F&q=DEBUG I also searched for 'debug-level' and get even fewer results: https://www.postgresql.org/search/?u=%2Fdocs%2F10%2F&q=debug-level Trying 'debug set' gets https://www.postgresql.org/search/?u=%2Fdocs%2F15%2F&q=DEBUG+set The result that looks most promising for 'debug' (and the only result for 'debug-level' is > 9. PostgreSQL: Documentation: 10: postgres [0.02] > ...debug-level Sets the debug level. The higher this value is set, the more debugging... > https://www.postgresql.org/docs/10/app-postgres.html and the app-postgres.html page indeed says "-d debug-levelSets the debug level..." A Google search finally got me to: https://www.google.com/search?q=postgres+set+debug-level&rlz=1C5GCEM_enUS1015US1016&oq=postgres+set+debug-level&aqs=chrome..69i57j0i22i30l2j69i64.5046j0j4&sourceid=chrome&ie=UTF-8 Google made it easy (doh!) but I'd been staying in the postgres.org ecosystem and using the internal search. So, a couple of suggestions: - I think that on https://www.postgresql.org/docs/15/app-postgres.html, under the `-d` description, it would be helpful to include a link to https://www.postgresql.org/docs/10/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES and perhaps https://www.postgresql.org/docs/10/runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES as ways to override the value initially specified by -d (disclaimer: I don't know if '-d' can be overwritten; I don't run postgres servers) - Perhaps under client_min_messages on https://www.postgresql.org/docs/10/runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES make a reference something "this sets the debug level for the current session" - Perhaps under log_min_messages on https://www.postgresql.org/docs/10/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES a similar statement that "this is a [distinct | related | overriding] setting for the '-d debug-level' when [starting Postgres](https://www.postgresql.org/docs/15/app-postgres.html)" - The page https://www.postgresql.org/docs/11/plpgsql-errors-and-messages.html has links to both 'client_min_messages' and 'log_min_messages' and it's probably my fault for skimming the page too fast. It still strikes me odd, though, that searching for 'set DEBUG level' doesn't pop this page to the top, but lets it sit down at #10 with a relevance(?) of [0.00] Some of these ideas may be better suited for a "Note" box on the page; basically, having some way to improve the search results to bring these pages to the top is the central concern here and I had found it personally frustrating enough to take the time to write this tome :- )
Perhaps an issue on the collation page?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/collation.html Description: 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.
Misleading description for IPC wait events in PostgreSQL documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/monitoring-stats.html Description: While preparing for my presentation on PostgreSQL Wait Events at PgConf India, I was trying to understand *IPC:XactGroupUpdate* in more detail. PostgreSQL documentation [1] mentions: > A process is waiting for the group leader to update the transaction status at the end of a _parallel operation_. I was looking at `TransactionGroupUpdateXidStatus` in PostgreSQL code (`clog.c`) Line `481` [2] sets this wait event. And after reading the code - my understanding is it does not necessarily need to be a "_parallel operation_". Or maybe I am just misinterpreting "parallel operation" in this context. But it is possible for other users to confuse it with the parallel query feature. **My understanding is** In order to avoid `XactSLRULock` being passed between backends, backends waiting for it will add themselves to the queue [3]. The first backend in the queue (also the leader) will be the only one to acquire `XactSLRULock` and update the XID status for all those pids which are in the queue. This wait event `IPC:XactGroupUpdate` is observed in other backened processes in queue who are waiting for the group leader to update the XID status. I recommend making a change to PostgreSQL docs to clearly indicate what this wait event means: > When concurrent processes are trying to update transaction status in the same page of commit logs, a group leader is assigned to perform the update and other processes wanting to perform the update wait on this IPC wait event. OR > When multiple backend processes wait on a group leader to update their transaction status, so that PostgreSQL can avoid passing around the LwLock `XactSLRULock` between multiple backend processes. A similar change should be done for `ProcArrayGroupUpdate` to indicate that the wait event is a result of concurrent backend processes trying to clear the transaction id (instead of saying "parallel operation"). Thoughts? [1] https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-IPC-TABLE [2] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/clog.c#L481 [3] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/clog.c#L399 Thanks, Sameer DB Specialist, Amazon Web Services
Incorrect link tohttps://www.postgresql.org/docs/current/indexes-functional.html ?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/indexes-expressional.html Description: Hi, nice to meet you. I found incorrect link to https://www.postgresql.org/docs/current/indexes-functional.html on this page https://www.postgresql.org/docs/7.3/indexes-functional.html. The text is "...the same page for the current version...". Is "functional index" in version 7.3 (or earlier) equivalent to the "Indexes on Expressions" in the current version? If so, is correct URL https://www.postgresql.org/docs/current/indexes-expressional.html ? Would you check and correct it? Thank you in advance.
CREATE TABLE ... PARTITION OF ...
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-createtable.html Description: Documentation does not state that CREATE TABLE ... PARTITION OF ... requires an ACCESS EXCLUSIVE lock on the parent table (it only mentions that about DROP TABLE ... which is a partition of another table)
pg_basebackup application does not dump all database objects
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/app-pgbasebackup.html
Description:
Hello!
It would be good to point out on the pg_basebackup documentation that it
behaves differently from
SELECT * FROM pg_start_backup('label', true, false);
cp -a xxx yyy
SELECT * FROM pg_stop_backup(false, true);
method. One thing discovered was that the latter retains replication slot
information on the clone while pg_basebackup discards all replication slot
information. Not sure if it is a feature or a bug (replication slots
retention is a good thing).
And perhaps there are more such subtle differences?
With best regards,
Kristjan Mustkivi
Postgres 14 documentation suggests range_agg is available, but it's not until Postgres 15
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/functions-aggregate.html Description: Hello - I noticed that range_agg is listed in the Postgres 14 documentation as an available aggregate function here: https://www.postgresql.org/docs/14/functions-aggregate.html However, my understanding is that this feature isn't available until Postgres 15, per the feature matrix here: https://www.postgresql.org/about/featurematrix/ Can the Postgres 14 documentation be updated to reflect this? Thanks! Michael Carlin
Incorrect/confusing information about timetz
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-datetime.html Description: This statement in section 8.5.3 states "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client." Is not correct for timetz
DROP INDEX - dropping index of a table in a named schema
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-dropindex.html Description: I had to find out (as it is not in the given documentation) that to drop an index for a table in a named schema (later "schemaname.tablename") you have to prefix the schemaname for the index inte DROP statement. Consider CREATE INDEX indexname ON schemaname.tablename () To drop it you will have to perform DROP INDEX schemaname.indexname (as I have read that indices are tied to schemas) The following statement will fail (which was not clear from documentation) DROP INDEX indexname
Typo
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/history.html Description: Hi Folks, thank you for maintaining this great technical resource, which I've only recently started to use. 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. Thanks, Peter Spung | Raleigh, NC, USA
5.11.1 Table Partitioning Overview - "...the size of the table should exceed the physical memory.."
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/ddl-partitioning.html Description: Is this rule of thumb a typo? "The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server." I believe it should say the "a rule of thumb is that the size of the table should NOT exceed the physical memory of the database server." Thanks, MV
pass open cursor via USING in execute staement
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
Description:
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;
pg_class.relpages documentation does not mention relpages corner case
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/catalog-pg-class.html Description: In the pg_class documentation [1], the relpages column is described as >Size of the on-disk representation of this table in pages (of size BLCKSZ). This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. However, it looks like this is -1 for partitioned tables. At least for ones that have children and have been analyze since adding a child partition, it seems. I'm not sure how to word that concisely, but should the documentation note this case? Or can this just be zero for partitioned tales? I'm not sure if it's too late for that, but they don't actually have any relpages, so it seems odd that they need a sentinel value. Thanks, Maciek [1]: https://www.postgresql.org/docs/current/catalog-pg-class.html
Pattern matching also includes Full-text search, Trigram and Fuzzysearch
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/functions-matching.html Description: Hi, The tip states: "If you have pattern matching needs that go beyond this, consider writing a user-defined function in Perl or Tcl." But there's a Full-text search, Trigram and Fuzzysearch extensions. Shouldn't they be listed in the tip? Regards, B Banchev
Further clarification in documentation: No deletion of unreferenced large objects
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-binary.html Description: 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. Kind regards Oliver Marienfeld
streaming replication depends on matching glibc versions / LOCALE sort order
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/warm-standby.html Description: After suffering a database corruption, I went looking for mention of the cause or "prevention tips" in the official docs. tldr; The following section might make a great place to mention the dependency on identical glibc versions: https://www.postgresql.org/docs/15/warm-standby.html#STANDBY-PLANNING There is information in a blog post: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html and some in the wiki: https://wiki.postgresql.org/wiki/Locale_data_changes and some talk on the postgresql developer mailing list such as "I have been expecting to hear about such breakage, and am surprised we hear about it so rarely." https://www.postgresql.org/message-id/flat/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E%40tripadvisor.com In Slack, it seems clear that this is a "well known issue" to some people on the project, but it continues to burn people in the wild. This seems important enough that it should be included in the official docs. Can I do anything to help get the documentation updated?
Change "two" to "three" for decades of development in history
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/history.html Description: Hi, In https://www.postgresql.org/docs/current/history.html it is written "With over two decades of development behind it". But since "The implementation of POSTGRES began in 1986" according to the same document, it should now be "With over three decades of development behind it". Regards, Pierre
bad choice of the word in sentence
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/transaction-iso.html Description: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED Quote: "<...>When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. <...>" "... changes committed during ..." Don't you think this is bad choice of the word, especially while speaking about "commiting transactions" in very same sentence? Wouldn't it be better to say something like <...> it never sees either uncommitted data or changes produced during query execution by concurrent transactions. <...> I mean, with read committed isolation level SELECT clearly can see changes from concurrent transactions, if these transactions COMMITED. In fact, you can read about that in few lines later: "<...>Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts. <...>" The sentences seem to contradict each other because of " it never sees ... changes committed during ..."
group by can use alias from select list
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-select.html Description: hi 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: " Description SELECT retrieves rows from zero or more tables. The general processing of SELECT is as follows: 1.) All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. (See WITH Clause below.) 2.) All elements in the FROM list are computed. (Each element in the FROM list is a real or virtual table.) If more than one element is specified in the FROM list, they are cross-joined together. (See FROM Clause below.) 3.) If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output. (See WHERE Clause below.) 4.) If the GROUP BY clause is specified, or if there are aggregate function calls, the output is combined into groups of rows that match on one or more values, and the results of aggregate functions are computed. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition. (See GROUP BY Clause and HAVING Clause below.) 5.) The actual output rows are computed using the SELECT output expressions for each selected row or row group. (See SELECT List below.) " 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 the SQL for that: with tbase(id) as (select 1 union all select 2) select id otto from tbase group by otto order by otto what do you think about that? hape
transaction example just pathetic
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.
Confusing reference to MERGE use inside WITH
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/queries-with.html Description: Hi. In the queries WITH documentation https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing that MERGE is referenced in several places as being something you can use inside with, even though it can't. If you search for MERGE on the page, you will find the 2 first results is a reference saying MERGE can be used inside WITH, the same as SELECT, INSERT, UPDATE, or DELETE. The 3rd result then correctly states that MERGE cannot be used inside WITH. If I'm misunderstanding the meaning of the first 2 results, then I apologise :) Greetings Rasmus Porsager
Improvement of clarity in pg_ctl command docummentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/app-pg-ctl.html Description: I was reading the documentation about pg_ctl and there everything was well written about the usage and option that can be used with the pg_ctl command but there is not mentioned that we can not run pg_ctl command as root and why we cant run it as root.
IN for records
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/functions-comparisons.html
Description:
In section #FUNCTIONS-COMPARISONS-IN-SCALAR operator IN is described as a
expression for scalars, and as a shorthand notation for a combination of
operators || and =
In section #COMPOSITE-TYPE-COMPARISON described extension, to use operators
with a records, like "record operator record" for uncertain set of
operators, and noted "Composite type comparisons are allowed when the
operator is =, <>, <, <=, > or >=, or has semantics similar to one of
these." and with a some difficult to understand note about B-trees.
And as result, I can't see a direct description for allowance to use a
syntax like a "(key_part_1, key_part_2) IN ( ('B',1), ('C',2) )"
So I propose to mention record types in section
#FUNCTIONS-COMPARISONS-IN-SCALAR. Phrase "The right-hand side is a
parenthesized list of scalar expressions." can be replaced to phrases "The
right-hand side is a parenthesized list of scalar or record expressions.
About records [see further](#COMPOSITE-TYPE-COMPARISON)"
Also I propose make similar adjustments in other sections of this page, and
make cross-links here and in a
https://www.postgresql.org/docs/current/functions-subquery.html page near
the text mentions of each others.
CREATEROLE Inheritance
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/role-membership.html Description: Regarding this paragraph: "The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing SET ROLE admin." I have checked this for CREATEROLE and this role attribute is definitely inheritable. I've created a new user with CREATE ROLE and no additional options. I've tried to create a role and could not. Then I made this user a member of a role that have the CREATEROLE attribute set to TRUE (with GRANT user_with_createrole TO new_user). With the same new user I could now create new roles. This has to mean inheritance works for this attribute. I am using DataGrip IDE and made sure in the output window that the program does not issue a 'SET ROLE' command before attempting to create a role. So creating a new role worked with the new user (by inheritance) without using 'SET ROLE' before.
Uniquness of ctid
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/ddl-system-columns.html Description: Hi, Looking at the explanation abour ctid, it is "The physical location of the row version within its table. " From that line, I think ctid is unique in the table. And I also think ctid might be unique across the database since it is the physical location. But I was not sure about this. Please confirm if my assumption is right or not. Thanks Brian
to_char(numeric type, text) rounding instead of truncating
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.
word incorrectly on document
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/executor.html Description: Document location: 52.6. Executor fed up -> feed up
3.6 Inheritance Documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/tutorial-inheritance.html Description: The documentation for inheritance in PostgreSQL is not entirely accurate in the given example. In the provided example, the cities table and the capitals table have a parent-child relationship through inheritance. However, querying the cities table directly (e.g., SELECT name, elevation FROM cities WHERE elevation > 500;) will not automatically include data from the child table, capitals. Inheritance in PostgreSQL does not implicitly combine data from parent and child tables in a single query. To include data from both the parent and child tables, a UNION or other join operations must be used, as I previously explained. The documentation should be corrected to clarify that inheritance does not automatically combine data from parent and child tables when querying the parent table directly. It might be updated to provide a better example demonstrating the use of UNION or JOIN operations to retrieve data from both parent and child tables.
With recusive miss syntax definition
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/queries-with.html Description: In this page seems to miss link to with sintax. Specially for with recursive. I'm looking for an more complicated example like that: with first_query as ( ), recusive_second_query as ( select fields from first_query union all select fields from second_query join other query ) But it seems "recusive" could be used just for first query. But this information should be explained
U+200B ZERO WIDTH SPACE (0xe2 0x80 0x8b) in PostgreSQL documentation
The following documentation comment has been logged on the website:
Page:
https://www.postgresql.org/docs/15/infoschema-administrable-role-authorizations.html
Description:
In official html documentation there are issue U+200B ZERO WIDTH SPACE (0xe2
0x80 0x8b) in text of some keywords, some identificators and some literals.
When copy-pasting to the SQL command, an error occurs because of this.
For example in page infoschema-administrable-role-authorizations.html on
site
(https://www.postgresql.org/account/comments/new/15/infoschema-administrable-role-authorizations.html/)
and in linux doc
{/usr/share/doc/postgresql-doc-13/html/infoschema-administrable-role-authorizations.html}.
See below excerpt text from the abovementioned page contains:
administrable_role_<200b>authorizations
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>http://www.w3.org/1999/xhtml";>36.4. administrable_role_<200b>authorizationshttp://www.w3.org/TR/xhtml1/transitional"; class="navheader">36.4. http://www.w3.org/1999/xhtml";
class="literal">administrable_role_<200b>authorizationsPrev UpChapter 36. The Information SchemaHome Next36.4. administrable_role_<200b>authorizations
The view administrable_role_authorizations
identifies all roles that the current user has the admin option
for.
Include PostgresNIO Swift client in the Documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/external-interfaces.html Description: Hi, I'm reaching out to ask if the Swift Postgres could be included in the listing here: https://www.postgresql.org/docs/current/external-interfaces.html Name: PostgresNIO Language: Swift Comments: Non-blocking, event-driven client built with SwiftNIO Website: https://github.com/vapor/postgres-nio Thanks for your time and consideration, Fabian Fett
Include rust as an externally maintained procedural language
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/external-pl.html Description: The documentation [1] contains a list of independently maintained procedural languages. Recently, PL/Rust [2] has reached its version 1.0 [3] and appears to be stable. Major companies have expressed interest in the extension. Kiwi Copple, the founder of Supabase, has stated his company is researching the language for his platform [4]. Meanwhile, Amazon's RDS team has already announced support for PL/Rust as a trusted language extension [5] on their PostgreSQL instances [6]. Considering the increasing support and stability for PL/Rust, it should be referenced within Postgresql's docs [1]. [1] https://www.postgresql.org/docs/current/external-pl.html [2] https://tcdi.github.io/plrust/plrust.html [3] https://github.com/tcdi/plrust/pull/277/commits/a10352b2461276cffdc2d6bf1fe0340143203125 [4] https://news.ycombinator.com/item?id=35501065 [5] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html [6] https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-rds-postgresql-rust/
ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-altertable.html Description: Hi, When the folloing SQL is executed: ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error then an error is returned: > ERROR: sequence option "restart" not supported here According to the documention for Postgres 15 on ALTER TABLE, the provided command seems to be correct. Omitting 'WITH' did not help. A similar ALTER SEQUENCE table_id_seq RESTART WITH nnn works fine. The involved Postgres version is: PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit I also found a very similar error in an old mail: https://www.postgresql.org/message-id/ed41b243-63b2-f287-e7b0-8b2ac7266d66%402ndquadrant.com Please fix the documentation or the functionality.
In docs there is no "Installation from Binaries" section
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/install-binaries.html Description: Installing software from ZIP archive is a common practice, pgsql provides such archive, but doesn't provide instructions on what to do with it. These pages don't have such instruction or links to such instruction even though it would be natural for them to belong there: https://www.postgresql.org/download/ https://www.postgresql.org/download/windows/ https://www.enterprisedb.com/download-postgresql-binaries https://www.postgresql.org/docs/current/install-binaries.html https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql/ https://www.postgresqltutorial.com/?s=zip+archive (nothing found) https://www.postgresqltutorial.com/?s=binaries (nothing found) In actual archive: .\pgsql\doc\installation-notes.html .\pgsql Please provide an instruction on this matter and add links to it to reasonable places across your web and documentation pages. Expected use case is: 1. download zip archive 2. Unzip it 3. ??? 4. Execute SQL, like: `select now();`
Wrong statement on the materialized views page
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/rules-materializedviews.html Description: There might be an issue on this page: https://www.postgresql.org/docs/current/rules-materializedviews.html I think the statement CREATE TABLE mymatview AS SELECT * FROM mytab; should actuall be CREATE VIEW mymatview AS SELECT * FROM mytab; kind regards, Mark
Create Role Documentation - Not Clear for new users
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/database-roles.html Description: Hi! Hope you are doing great! I'm trying to learn about PostgreSQL, I'm totally new, and I think that for people like me, the documentation is not clear, for example, you are stating that we need to run the ` CREATE ROLE name; `, but you are not specifying if that is directly on the terminal? In my case, I'm trying to run that on my terminal and I'm getting: `CREATE: not found`, I think would be great if that part could be improved! I'm reading that in chapter 22 because in chapter 1.4 I'm being referenced to chapter 22 Thanks for letting me share :D my thoughts!
There is no description about "Logical Replication also use or occupy max_wal_senders ",
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/runtime-config-replication.html Description: hello ,everyone, in the url https://www.postgresql.org/docs/15/runtime-config-replication.html, there are following description: max_wal_senders (integer) Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is 10. The value 0 means replication is disabled. Abrupt disconnection of a streaming client might leave an orphaned connection slot behind until a timeout is reached, so this parameter should be set slightly higher than the maximum number of expected clients so disconnected clients can immediately reconnect. This parameter can only be set at server start. Also, wal_level must be set to replica or higher to allow connections from standby servers. When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server. In the above description， there is no description about "Logical Replication also use or occupy max_wal_senders ", so, I suggest the following modification: Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients and Logical Replication (i.e., the maximum number of simultaneously running WAL sender processes). ---please note ,"and Logical Replication" is newly added。
CREATE TRIGGER documentation inconsistensies
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/sql-createtrigger.html Description: In PostgreSQL 14 and 15 (previous versions I've not checked) is stated that CREATE TRIGGER statement is as follows: CREATE [OR REPLACE] [CONSTRAINT] TRIGGER however while CREATE TRIGGER works correctly the CREATE OR REPLACE TRIGGER construction leads to SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "TRIGGER" looks like CREATE TRIGGER doesn't support OR REPLACE, but documentation is then incorrect.
General Feedback
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/sql-createschema.html Description: "To create a schema, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)" - maybe you can show how to check if the user has the CREATE privilege? Or is this omitted on purpose so readers will take their own initiative to find that out themselves :-)
Error in Example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/indexes-partial.html Description: The IP addresses used to exemplify which one will be covered by the partial index are inverted.
Clarification of deadlock possibilities in section 13.3.5. Advisory Locks
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/explicit-locking.html Description: Hi, An example from section 13.3.5. Advisory Locks (I included the ORDER BY for deterministic results): SELECT pg_advisory_lock(q.id) FROM ( SELECT id FROM foo WHERE id > 12345 ORDER BY id ASC LIMIT 100 ) q; -- ok Can you clarify the potential for deadlocks occurring when two transactions execute this at the same time. Assuming no concurrent inserts or updates the inner SELECT should return the same set of ids. However, my question is, whether the ORDER BY guarantees the order in which the advisory locks are acquired? I would want the guarantee that the locks are acquired in ascending order (or at least the same order) instead of random, which would prevent the two transactions from deadlocking. In case the ordering is not deterministic and deadlocks are possible I think this should be added to the documentation. Regards, Marius Lichtblau
Kernel settings shmall
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/kernel-resources.html Description: Hi. The kernel parameter shmall is only in pages in Linux but your documentation says it can also be in bytes. Maybe it's in bytes with some OS other than Linux but the way it is presented in table 18.1 is quite misleading.. Could you add "depending on the OS" or something similar ? Many colleagues using Linux think it can be in bytes with Linux. Could you rewrite that part ? Table 18.1 in chapter 18.4.1 Regards. Patrick BUNINO
index access method documentation light on details on ii_AmCache
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/index-functions.html Description: So, if I cache something in ii_AmCache during a call to my aminsert callback... When, if ever, does it get freed? Having looked at example code, I don't actually see anything doing this in insert paths, so presumably there's some point at which this happens automatically, possibly as part of the Memory Context thing, maybe related to the ii_Context which seems to be getting used, but I can't find anything anywhere documenting that. This may well be completely obvious, or intended to be implied by "it can allocate space in indexInfo->ii_Context", but it's not exceptionally obvious to me as a newcomer to the code. (By contrast, the ambuild docs say to palloc a data structure, but don't mention a context for it; no idea whether it should be in a particular context.) Actually, in full generality, I have not been able to find a section of the documentation which explains the memory-context stuff at all. I found a blog post elsewhere suggesting that it's just "the memory context will be freed and thus everything associated with it". This implies that there's no straightforward way for an index to do end-of-insert maintenance after all the inserts from a given query are complete, except to do it after every tuple just in case it's the last tuple, I guess?
Ambiguous statement about order
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-json.html Description: The following statement in the page doesn't speak about arrays which created some confusion with developers in our company: "By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept." The reason that caused confusion was some developers argued object keys include arrays. What added to the ambiguity is another line (a footnote): "For this purpose, the term “value” includes array elements, though JSON terminology sometimes considers array elements distinct from values within objects." I understand that it is only related to containment test. But it was causing confusion with others. I observed that arrays inside JSONB preserved order of insertion. An explicit line about whether the array order will be preserved or not would be helpful.
Documentation does not mention that basebackup could not be used on newer major version
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.
pg_upgrade doc uses inconsistent versions within the doc.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/pgupgrade.html Description: I feel like for readability the pg_upgrade doc should use the same old_version and new_version examples when showing examples throughout the doc page. as an exmaple I'm looking at : https://www.postgresql.org/docs/current/pgupgrade.html in some places the example commands use v9.6 and v16. (see step 8) but in other places it mixes versions v9.5 and v9.6 (see step 11 sub step 8)
SSI retry strategy
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/index.html Description: Dear Sir / Madam, according the https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE a transaction must be retried if its commit failed. Quotes: " using Serializable transactions will allow one transaction to commit and will roll the other back". Later: "While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect" Indeed, when the commit is rejected the error is "could not serialize access due to read/write dependencies among transactions" and the reason code is "Canceled on identification as a pivot, during commit attempt.". Basic test case: check if a room is available at given dates (select), insert a new reservation if it is and throw a business exception otherwise. However, sometime the error is thrown when insert is executed. The message is still "could not serialize access due to read/write dependencies among transactions" but the reason code is "Canceled on identification as a pivot, during write.". From a developer point of view it has impact on the retry strategy because I should consider that any insert/update/delete during the transaction can result in a 40001 (and not only at commit). This page https://wiki.postgresql.org/wiki/SSI does dot mention "Canceled on identification as a pivot, during write." either. According the this message https://stackoverflow.com/a/9984640/10938834 from Kevin Grittner "When using SERIALIZABLE transactions in 9.1, your application should be prepared for serialization failures on any statement except for ROLLBACK ". "any statement" is the key point, I think it could me clearer in the documentation. I acknowledge that 13.2.2 section mention serialization failure in a more general way (i.e. does not emphasis on commit time). Regards, Alaric Hermant
Typo/wording on https://www.postgresql.org/docs/current/catalog-pg-class.html
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/catalog-pg-class.html Description: I've just read this: "catalogs tables and most everything else that has columns or is otherwise similar to a table" It seems that it should be: "catalogs tables and almost everything else that has columns or is otherwise similar to a table" So, "most" becomes "almost". Clicking back through versions it changed from "mostly" to "most" at version 7.3. Martin
ALTER POLICY etc locks
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-alterpolicy.html Description: Hi folks, just wanted to drop a documentation suggestion: we document that e.g. ALTER TABLE acquires an ACCESS EXCLUSIVE lock most of the time, but there's currently no documentation that shows that ALTER POLICY does the same. This seems to apply to the other POLICY commands too. It makes sense as a behaviour, but could probably use writing down for clarity! Source reference: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/policy.c;hb=HEAD#l363 Cheers, Curtis
correct documentation in set role
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-set-role.html Description: it is good to mention that: The specified role_name must be a role that the current session user is a member of "and has SET True option on it." The second part missed in documentation for set role command.
`pg_restore --if-exists` clarification
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/app-pgrestore.html Description: Good Morning, In the `pg_restore` docs (https://www.postgresql.org/docs/current/app-pgrestore.html), `--if-exists` states that it is to > Use conditional commands (i.e., add an IF EXISTS clause) > to drop database objects. This option is not valid unless > --clean is also specified.", but not being a SQL expert, > I'm having a hard time deciphering this. Suggestion: Would you consider adding the sentence (or something similar)? > "--clean makes pg_restore drop all objects first, > and --if-exists prevents that non-existent objects > cause a failure." Source: https://stackoverflow.com/a/75136163/1498178 This is succinct, and tells exactly what one needs to know. Thank you and have a great day! Appreciatively, Attila
Not clear spelling about transaction
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/continuous-archiving.html Description: >No committed transactions will be lost Hello. It is not clear to me. Did you mean: Not committed transactions will be lost Or None of committed transactions will be lost ? It would be simpler if you write that explicitly: All committed transactions will not be lost.
missing GRANTED BY explanations
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-revoke.html Description: Hi, In the documentation for revoke command, there is no description for GRANTED BY clause. please explain that what will happen when using this clause in revoke command. Best regards.
unnest multirange, returned order
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/functions-range.html
Description:
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".
Some context: I ask because we have some seemingly random (and impossible to
repro in tests up to now) errors in our code. The code assumes that this
returns things in range order and as the doc is unclear to me on this point,
I cannot exclude this to be our culprit.
Thank you
INT4RANGE Upper bound always includes a higher number
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/rangetypes.html Description: Hi, First I just wanted to say, the documentation is overall excellent, it has been such a pleasure learning postgres. In the RANGE types where it says the square bracket indicates upper bound inclusive ], But the issue with this is when I do upper bound inclusive it always goes one number above. This is contrary to your documentation says and it is also counter intuitive. According to your example (copied from your docs): -- includes 3, does not include 7, and does include all points in between SELECT '[3,7)'::int4range; But this is not true, it shows 3 and 7 And if i do: SELECT '(3,7]'::INT4RANGE; It shows: [4,8) (1 row) Please can someone explain ? Thank you
Corresponding documentation page does not mention about `spread` mode
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/app-pgbasebackup.html
Description:
Hello.
On [this
page](https://www.postgresql.org/docs/current/app-pgbasebackup.html)
`spread` is mentioned:
-c {fast|spread}
--checkpoint={fast|spread}
Sets checkpoint mode to fast (immediate) or spread (the default) (see
Section 26.3.3).
But mentioned section 26.3.3 does not describe `spread` mode:
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
It seems https://www.postgresql.org/docs/current/app-pgbasebackup.html
should be updated.
