aboutsummaryrefslogtreecommitdiff
path: root/doc/src
Commit message (Collapse)AuthorAge
...
* Return actual error code from FOP failure in PDF buildBruce Momjian2024-12-04
| | | | | | Previously we returned "1" on error. Improvement on 77c189cdafe. Backpatch-through: master
* Ensure stored generated columns must be published when required.Amit Kapila2024-12-04
| | | | | | | | | | | | | | | | | | | | | | | Ensure stored generated columns that are part of REPLICA IDENTITY must be published explicitly for UPDATE and DELETE operations to be published. We can publish generated columns by listing them in the column list or by enabling the publish_generated_columns option. This commit changes the behavior of the test added in commit adedf54e65 by giving an ERROR for the UPDATE operation in such cases. There is no way to trigger the bug reported in commit adedf54e65 but we didn't remove the corresponding code change because it is still relevant when replicating changes from a publisher with version less than 18. We decided not to backpatch this behavior change to avoid the risk of breaking existing output plugins that may be sending generated columns by default although we are not aware of any such plugin. Also, we didn't see any reports related to this on STABLE branches which is another reason not to backpatch this change. Author: Shlok Kyal, Hou Zhijie Reviewed-by: Vignesh C, Amit Kapila Discussion: https://postgr.es/m/CANhcyEVw4V2Awe2AB6i0E5AJLNdASShGfdBLbUd1XtWDboymCA@mail.gmail.com
* Properly use $(AWK) in Makefile, not 'awk'Bruce Momjian2024-12-03
| | | | | | Fix for commit 498f1307569. Backpatch-through: master
* Fix Makefile so invalid characters warning preserves error codeBruce Momjian2024-12-03
| | | | | | | | | | Fix for commit e4c8865196f. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/88cb6ecf-22bb-431e-974b-1cd236a80364@eisentraut.org Backpatch-through: master
* Now that we have non-Latin1 SGML detection, restore Latin1 charsBruce Momjian2024-12-03
| | | | | | | | | | | This reverts the change in commit 641a5b7a144 that converted them to HTML entities. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/Z05ssoVheWI-rqax@momjian.us Backpatch-through: master
* doc: Clarify some terms for pg_createsubscriberMichael Paquier2024-12-03
| | | | | | | | | | | | | | | | The last section of pg_createsubscriber used the terms "publication-name", "replication-slot-name", and "subscription-name". These terms are not defined on the page, which was confusing, and the intention is clearly to refer to the values one would give to the options --publication, --subscription and --replication-slot. Let's simplify the documentation by mentioning the option switches, instead of these terms. Reported-by: Christophe Courtois Author: Shubham Khanna Reviewed-by: Vignesh C, Peter Smith Discussion: https://postgr.es/m/173288198026.714.15127074046508836738@wrigleys.postgresql.org Backpatch-through: 17
* doc Makefile: issue warning about chars that cannot be outputBruce Momjian2024-12-02
| | | | | | | | | | A follow-up improvement to commit 641a5b7a144. Reported-by: Tatsuo Ishii, Tom Lane Discussion: https://postgr.es/m/20241126.182513.1752581942460106099.ishii@postgresql.org Backpatch-through: master
* Deprecate MD5 passwords.Nathan Bossart2024-12-02
| | | | | | | | | | | | | | | | | | | MD5 has been considered to be unsuitable for use as a cryptographic hash algorithm for some time. Furthermore, MD5 password hashes in PostgreSQL are vulnerable to pass-the-hash attacks, i.e., knowing the username and hashed password is sufficient to authenticate. The SCRAM-SHA-256 method added in v10 is not subject to these problems and is considered to be superior to MD5. This commit marks MD5 password support in PostgreSQL as deprecated and to be removed in a future release. The documentation now contains several deprecation notices, and CREATE ROLE and ALTER ROLE now emit deprecation warnings when setting MD5 passwords. The warnings can be disabled by setting the md5_password_warnings parameter to "off". Reviewed-by: Greg Sabino Mullane, Jim Nasby Discussion: https://postgr.es/m/ZwbfpJJol7lDWajL%40nathan
* doc: Fix typoPeter Eisentraut2024-11-30
| | | | | | for commit 1e08905842f Reported-by: Marcos Pegoraro <marcos@f10.com.br>
* doc: Improve description of referential actionsPeter Eisentraut2024-11-29
| | | | | | | Some of the differences between NO ACTION and RESTRICT were not explained fully. Discussion: https://www.postgresql.org/message-id/ea5b2777-266a-46fa-852f-6fca6ec480ad@eisentraut.org
* Require ucrt if using MinGW.Thomas Munro2024-11-27
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Historically we tolerated the absence of various C runtime library features for the benefit of the MinGW tool chain, because it used ancient msvcrt.dll for a long period of time. It now uses ucrt by default (like Windows 10+, Visual Studio 2015+), and that's the only configuration we're testing. In practice, we effectively required ucrt already in PostgreSQL 17, when commit 8d9a9f03 required _create_locale etc, first available in msvcr120.dll (Visual Studio 2013, the last of the pre-ucrt series of runtimes), and for MinGW users that practically meant ucrt because it was difficult or impossible to use msvcr120.dll. That may even not have been the first such case, but old MinGW configurations had already dropped off our testing radar so we weren't paying much attention. This commit formalizes the requirement. It also removes a couple of obsolete comments that discussed msvcrt.dll limitations, and some tests of !defined(_MSC_VER) to imply msvcrt.dll. There are many more anachronisms, but it'll take some time to figure out how to remove them all. APIs affected relate to locales, UTF-8, threads, large files and more. Thanks to Peter Eisentraut for the documentation change. It's not really necessary to talk about ucrt explicitly in such a short section, since it's the default for MinGW-w64 and MSYS2. It's enough to prune references and broken links to much older tools. Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://postgr.es/m/d9e7731c-ca1b-477c-9298-fa51e135574a%40eisentraut.org
* Support LIKE with nondeterministic collationsPeter Eisentraut2024-11-27
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This allows for example using LIKE with case-insensitive collations. There was previously no internal implementation of this, so it was met with a not-supported error. This adds the internal implementation and removes the error. The implementation follows the specification of the SQL standard for this. Unlike with deterministic collations, the LIKE matching cannot go character by character but has to go substring by substring. For example, if we are matching against LIKE 'foo%bar', we can't start by looking for an 'f', then an 'o', but instead with have to find something that matches 'foo'. This is because the collation could consider substrings of different lengths to be equal. This is all internal to MatchText() in like_match.c. The changes in GenericMatchText() in like.c just pass through the locale information to MatchText(), which was previously not needed. This matches exactly Generic_Text_IC_like() below. ILIKE is not affected. (It's unclear whether ILIKE makes sense under nondeterministic collations.) This also updates match_pattern_prefix() in like_support.c to support optimizing the case of an exact pattern with nondeterministic collations. This was already alluded to in the previous code. (includes documentation examples from Daniel Vérité and test cases from Paul A Jungwirth) Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/700d2e86-bf75-4607-9cf2-f5b7802f6e88@eisentraut.org
* Handle better implicit transaction state of pipeline modeMichael Paquier2024-11-27
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | When using a pipeline, a transaction starts from the first command and is committed with a Sync message or when the pipeline ends. Functions like IsInTransactionBlock() or PreventInTransactionBlock() were already able to understand a pipeline as being in a transaction block, but it was not the case of CheckTransactionBlock(). This function is called for example to generate a WARNING for SET LOCAL, complaining that it is used outside of a transaction block. The current state of the code caused multiple problems, like: - SET LOCAL executed at any stage of a pipeline issued a WARNING, even if the command was at least second in line where the pipeline is in a transaction state. - LOCK TABLE failed when invoked at any step of a pipeline, even if it should be able to work within a transaction block. The pipeline protocol assumes that the first command of a pipeline is not part of a transaction block, and that any follow-up commands is considered as within a transaction block. This commit changes the backend so as an implicit transaction block is started each time the first Execute message of a pipeline has finished processing, with this implicit transaction block ended once a sync is processed. The checks based on XACT_FLAGS_PIPELINING in the routines checking if we are in a transaction block are not necessary: it is enough to rely on the existing ones. Some tests are added to pgbench, that can be backpatched down to v17 when \syncpipeline is involved and down to v14 where \startpipeline and \endpipeline are available. This is unfortunately limited regarding the error patterns that can be checked, but it provides coverage for various pipeline combinations to check if these succeed or fail. These tests are able to capture the case of SET LOCAL's WARNING. The author has proposed a different feature to improve the coverage by adding similar meta-commands to psql where error messages could be checked, something more useful for the cases where commands cannot be used in transaction blocks, like REINDEX CONCURRENTLY or VACUUM. This is considered as future work for v18~. Author: Anthonin Bonnefoy Reviewed-by: Jelte Fennema-Nio, Michael Paquier Discussion: https://postgr.es/m/CAO6_XqrWO8uNBQrSu5r6jh+vTGi5Oiyk4y8yXDORdE2jbzw8xw@mail.gmail.com Backpatch-through: 13
* Fix commit 641a5b7a144 for "nbsp" output in SVG filesBruce Momjian2024-11-26
| | | | | | | | | | | | | | In commit 641a5b7a144, I removed "nbsp" characters from SVG files, not realizing the SVG files were generated from GV files and that the "nbsp" characters were caused by trailing ASCII spaces in GV files. This commit restores the "nbsp" SVG characters and adds a GV comment about how the trailing spaces cause the "nbsp" output. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/2c5dd601-b245-4092-9c27-6d1ad51609df%40eisentraut.org Backpatch-through: master
* Reordering DISTINCT keys to match input path's pathkeysRichard Guo2024-11-26
| | | | | | | | | | | | | | | | | | | | | | | | The ordering of DISTINCT items is semantically insignificant, so we can reorder them as needed. In fact, in the parser, we absorb the sorting semantics of the sortClause as much as possible into the distinctClause, ensuring that one clause is a prefix of the other. This can help avoid a possible need to re-sort. In this commit, we attempt to adjust the DISTINCT keys to match the input path's pathkeys. This can likewise help avoid re-sorting, or allow us to use incremental-sort to save efforts. For DISTINCT ON expressions, the parser already ensures that they match the initial ORDER BY expressions. When reordering the DISTINCT keys, we must ensure that the resulting pathkey list matches the initial distinctClause pathkeys. This introduces a new GUC, enable_distinct_reordering, which allows the optimization to be disabled if needed. Author: Richard Guo Reviewed-by: Andrei Lepikhov Discussion: https://postgr.es/m/CAMbWs48dR26cCcX0f=8bja2JKQPcU64136kHk=xekHT9xschiQ@mail.gmail.com
* Doc: Clarify the `inactive_since` field description.Amit Kapila2024-11-25
| | | | | | | | | | | Updated to specify that it represents the exact time a slot became inactive, rather than the period of inactivity. Reported-by: Peter Smith Author: Bruce Momjian, Nisha Moond Reviewed-by: Amit Kapila, Peter Smith Backpatch-through: 17 Discussion: https://postgr.es/m/CAHut+PuvsyA5v8y7rYoY9mkDQzUhwaESM05yCByTMaDoRh30tA@mail.gmail.com
* doc: Fix example with __next__() in PL/Python functionMichael Paquier2024-11-25
| | | | | | | | | | | Per PEP 3114, iterator.next() has been renamed to iterator.__next__(), and one example in the documentation still used next(). This caused the example provided to fail the function creation since Python 2 is not supported anymore since 19252e8ec93. Author: Erik Wienhold Discussion: https://postgr.es/m/173209043143.2092749.13692266486972491694@wrigleys.postgresql.org Backpatch-through: 15
* doc: clarify how logical replication takes its initial snapshotBruce Momjian2024-11-21
| | | | | | | | Reported-by: Koen De Groote Discussion: https://postgr.es/m/171606613152.686.7693963105919927503@wrigleys.postgresql.org Backpatch-through: master
* doc: clarify that jsonb_path_match() returns an SQL booleanBruce Momjian2024-11-20
| | | | | | | | | | | Not a JSON boolean. Also clarify that other predicate check expressions functions return a JSON boolean, not an SQL boolean. Reported-by: jian he Discussion: https://postgr.es/m/CACJufxH7tP1NXCHN1bUBXcEB=dv7-qE+ZjB3UxwK6Em+9Qzb9Q@mail.gmail.com Backpatch-through: 17
* clarify --no-comments option in --help and SGML filesBruce Momjian2024-11-20
| | | | | | | | | | The previous commit, b38bac26e20, missed these cases for dump/restore. Reported-by: Tom Lane Discussion: https://postgr.es/m/3495698.1731968093@sss.pgh.pa.us Backpatch-through: master
* file_fdw: Add REJECT_LIMIT option to file_fdw.Fujii Masao2024-11-20
| | | | | | | | | | | | | | | | | | | | Commit 4ac2a9bece introduced the REJECT_LIMIT option for the COPY command. This commit extends the support for this option to file_fdw. As well as REJECT_LIMIT option for COPY, this option limits the maximum number of erroneous rows that can be skipped. If the number of data type conversion errors exceeds this limit, accessing the file_fdw foreign table will fail with an error, even when on_error = 'ignore' is specified. Since the CREATE/ALTER FOREIGN TABLE commands require foreign table options to be single-quoted, this commit updates defGetCopyRejectLimitOption() to handle also string value for them, in addition to int64 value for COPY command option. Author: Atsushi Torikoshi Reviewed-by: Fujii Masao, Yugo Nagata, Kirill Reshke Discussion: https://postgr.es/m/bab68a9fc502b12693f0755b6f35f327@oss.nttdata.com
* doc: Fix section of functions age(xid) and mxid_age(xid)Michael Paquier2024-11-20
| | | | | | | | | | | | | | | | In 17~, age(xid) and mxid_age(xid) were listed as deprecated. Based on the discussion that led to 48b5aa3143, this is not intentional as this could break many existing monitoring queries. Note that vacuumdb also uses both of them. In 16, both functions were listed under "Control Data Functions", which is incorrect, so let's move them to the list of functions related to transaction IDs and snapshots. Author: Bertrand Drouvot Discussion: https://postgr.es/m/Zzr2zZFyeFKXWe8a@ip-10-97-1-34.eu-west-3.compute.internal Discussion: https://postgr.es/m/20231114013224.4z6oxa6p6va33rxr@awork3.anarazel.de Backpatch-through: 16
* Improve error message for database object stats manipulation functions.Fujii Masao2024-11-20
| | | | | | | | | | | | | | | | | | | | | | | Previously, database object statistics manipulation functions like pg_set_relation_stats() reported unclear error and hint messages when executed during recovery. These messages were "internal", making it difficult for users to understand the issue: ERROR: cannot acquire lock mode ShareUpdateExclusiveLock on database objects while recovery is in progress HINT: Only RowExclusiveLock or less can be acquired on database objects during recovery. This commit updates the error handling so that, if these functions are called during recovery, they produce clearer messages: ERROR: recovery is in progress HINT: Statistics cannot be modified during recovery. The related documentation has also been updated to explicitly clarify that these functions are not available during recovery. Author: Fujii Masao Reviewed-by: Heikki Linnakangas, Maxim Orlov Discussion: https://postgr.es/m/6d313829-5f56-4a28-ae4b-bd01bf1ae791@oss.nttdata.com
* doc: clarify pg_dump --no-comments meaning as SQL commentsBruce Momjian2024-11-18
| | | | | | Discussion: https://postgr.es/m/ZyjdAjEsXbFPkD3t@momjian.us Backpatch-through: master
* doc: clarify text about combining row-level policiesBruce Momjian2024-11-18
| | | | | | | | Reported-by: splarv@ya.ru Discussion: https://postgr.es/m/173045909386.700.9231055113418242392@wrigleys.postgresql.org Backpatch-through: master
* Fix collation handling for foreign keysPeter Eisentraut2024-11-15
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Allowing foreign keys where the referenced and the referencing columns have collations with different notions of equality is problematic. This can only happen when using nondeterministic collations, for example, if the referencing column is case-insensitive and the referenced column is not, or vice versa. It does not happen if both collations are deterministic. To show one example: CREATE COLLATION case_insensitive (provider = icu, deterministic = false, locale = 'und-u-ks-level2'); CREATE TABLE pktable (x text COLLATE "C" PRIMARY KEY); CREATE TABLE fktable (x text COLLATE case_insensitive REFERENCES pktable ON UPDATE CASCADE ON DELETE CASCADE); INSERT INTO pktable VALUES ('A'), ('a'); INSERT INTO fktable VALUES ('A'); BEGIN; DELETE FROM pktable WHERE x = 'a'; TABLE fktable; ROLLBACK; BEGIN; DELETE FROM pktable WHERE x = 'A'; TABLE fktable; ROLLBACK; Both of these DELETE statements delete the one row from fktable. So this means that one row from fktable references two rows in pktable, which should not happen. (That's why a primary key or unique constraint is required on pktable.) When nondeterministic collations were implemented, the SQL standard available to yours truly said that referential integrity checks should be performed with the collation of the referenced column, and so that's how we implemented it. But this turned out to be a mistake in the SQL standard, for the same reasons as above, that was later (SQL:2016) fixed to require both collations to be the same. So that's what we are aiming for here. We don't have to be quite so strict. We can allow different collations if they are both deterministic. This is also good for backward compatibility. So the new rule is that the collations either have to be the same or both deterministic. Or in other words, if one of them is nondeterministic, then both have to be the same. Users upgrading from before that have affected setups will need to make changes to their schemas (i.e., change one or both collations in affected foreign-key relationships) before the upgrade will succeed. Some of the nice test cases for the previous situation in collate.icu.utf8.sql are now obsolete. They are changed to just check the error checking of the new rule. Note that collate.sql already contained a test for foreign keys with different deterministic collations. A bunch of code in ri_triggers.c that added a COLLATE clause to enforce the referenced column's collation can be removed, because both columns now have to have the same notion of equality, so it doesn't matter which one to use. Reported-by: Paul Jungwirth <pj@illuminatedcomputing.com> Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/78d824e0-b21e-480d-a252-e4b84bc2c24b@illuminatedcomputing.com
* doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPSPeter Eisentraut2024-11-13
| | | | | | | Fixup for commit fc0438b4e80. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Discussion: https://www.postgresql.org/message-id/57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com
* doc: Add PERIOD to ALTER TABLE reference docsPeter Eisentraut2024-11-13
| | | | | | | | | Commit 89f908a6d0a documented foreign keys with PERIOD in the CREATE TABLE docs, but not in ALTER TABLE. This commit adds the new syntax to the ALTER TABLE docs. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Discussion: https://www.postgresql.org/message-id/57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com
* doc: Small improvement in CREATE TABLE / PERIOD documentationPeter Eisentraut2024-11-13
| | | | | Use placeholders that are more consistent and match the description better. Fixup for commit 89f908a6d0a.
* doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docsPeter Eisentraut2024-11-13
| | | | | | | | | Commit fc0438b4e80 documented WITHOUT OVERLAPS in the CREATE TABLE docs, but not in ALTER TABLE. This commit adds the new syntax to the ALTER TABLE docs. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Discussion: https://www.postgresql.org/message-id/57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com
* Block environment variable mutations from trusted PL/Perl.Noah Misch2024-11-11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Many process environment variables (e.g. PATH), bypass the containment expected of a trusted PL. Hence, trusted PLs must not offer features that achieve setenv(). Otherwise, an attacker having USAGE privilege on the language often can achieve arbitrary code execution, even if the attacker lacks a database server operating system user. To fix PL/Perl, replace trusted PL/Perl %ENV with a tied hash that just replaces each modification attempt with a warning. Sites that reach these warnings should evaluate the application-specific implications of proceeding without the environment modification: Can the application reasonably proceed without the modification? If no, switch to plperlu or another approach. If yes, the application should change the code to stop attempting environment modifications. If that's too difficult, add "untie %main::ENV" in any code executed before the warning. For example, one might add it to the start of the affected function or even to the plperl.on_plperl_init setting. In passing, link to Perl's guidance about the Perl features behind the security posture of PL/Perl. Back-patch to v12 (all supported versions). Andrew Dunstan and Noah Misch Security: CVE-2024-10979
* Doc: Add links to clarify the max_replication_slots.Amit Kapila2024-11-11
| | | | | | | | The GUC max_replication_slots has a different meaning for sending servers and subscribers. Add cross-links in each section for easy reference. Author: Tristan Partin Discussion: https://postgr.es/m/D5FNEPMMFHFX.1OQBCML0TU5AH@partin.io
* Add two attributes to pg_stat_database for parallel workers activityMichael Paquier2024-11-11
| | | | | | | | | | | | | | | | | | | | | | Two attributes are added to pg_stat_database: * parallel_workers_to_launch, counting the total number of parallel workers that were planned to be launched. * parallel_workers_launched, counting the total number of parallel workers actually launched. The ratio of both fields can provide hints that there are not enough slots available when launching parallel workers, also useful when pg_stat_statements is not deployed on an instance (i.e. cf54a2c00254). This commit relies on de3a2ea3b264, that has added two fields to EState, that get incremented when executing Gather or GatherMerge nodes. A test is added in select_parallel, where parallel workers are spawned. Bump catalog version. Author: Benoit Lobréau Discussion: https://postgr.es/m/783bc7f7-659a-42fa-99dd-ee0565644e25@dalibo.com
* libpq: Bail out during SSL/GSS negotiation errorsMichael Paquier2024-11-11
| | | | | | | | | | | | | | | | | | | | | | | This commit changes libpq so that errors reported by the backend during the protocol negotiation for SSL and GSS are discarded by the client, as these may include bytes that could be consumed by the client and write arbitrary bytes to a client's terminal. A failure with the SSL negotiation now leads to an error immediately reported, without a retry on any other methods allowed, like a fallback to a plaintext connection. A failure with GSS discards the error message received, and we allow a fallback as it may be possible that the error is caused by a connection attempt with a pre-11 server, GSS encryption having been introduced in v12. This was a problem only with v17 and newer versions; older versions discard the error message already in this case, assuming a failure caused by a lack of support for GSS encryption. Author: Jacob Champion Reviewed-by: Peter Eisentraut, Heikki Linnakangas, Michael Paquier Security: CVE-2024-10977 Backpatch-through: 12
* Add pg_constraint rows for not-null constraintsÁlvaro Herrera2024-11-08
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | We now create contype='n' pg_constraint rows for not-null constraints on user tables. Only one such constraint is allowed for a column. We propagate these constraints to other tables during operations such as adding inheritance relationships, creating and attaching partitions and creating tables LIKE other tables. These related constraints mostly follow the well-known rules of conislocal and coninhcount that we have for CHECK constraints, with some adaptations: for example, as opposed to CHECK constraints, we don't match not-null ones by name when descending a hierarchy to alter or remove it, instead matching by the name of the column that they apply to. This means we don't require the constraint names to be identical across a hierarchy. The inheritance status of these constraints can be controlled: now we can be sure that if a parent table has one, then all children will have it as well. They can optionally be marked NO INHERIT, and then children are free not to have one. (There's currently no support for altering a NO INHERIT constraint into inheriting down the hierarchy, but that's a desirable future feature.) This also opens the door for having these constraints be marked NOT VALID, as well as allowing UNIQUE+NOT NULL to be used for functional dependency determination, as envisioned by commit e49ae8d3bc58. It's likely possible to allow DEFERRABLE constraints as followup work, as well. psql shows these constraints in \d+, though we may want to reconsider if this turns out to be too noisy. Earlier versions of this patch hid constraints that were on the same columns of the primary key, but I'm not sure that that's very useful. If clutter is a problem, we might be better off inventing a new \d++ command and not showing the constraints in \d+. For now, we omit these constraints on system catalog columns, because they're unlikely to achieve anything. The main difference to the previous attempt at this (b0e96f311985) is that we now require that such a constraint always exists when a primary key is in the column; we didn't require this previously which had a number of unpalatable consequences. With this requirement, the code is easier to reason about. For example: - We no longer have "throwaway constraints" during pg_dump. We needed those for the case where a table had a PK without a not-null underneath, to prevent a slow scan of the data during restore of the PK creation, which was particularly problematic for pg_upgrade. - We no longer have to cope with attnotnull being set spuriously in case a primary key is dropped indirectly (e.g., via DROP COLUMN). Some bits of code in this patch were authored by Jian He. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Author: Bernd Helmle <mailings@oopsware.de> Reviewed-by: 何建 (jian he) <jian.universality@gmail.com> Reviewed-by: 王刚 (Tender Wang) <tndrwang@gmail.com> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Discussion: https://postgr.es/m/202408310358.sdhumtyuy2ht@alvherre.pgsql
* doc: Reword ALTER TABLE ATTACH restriction on NO INHERIT constraintsÁlvaro Herrera2024-11-07
| | | | | | | | | | The previous wording is easy to read incorrectly; this change makes it simpler, less ambiguous, and less prominent. Backpatch to all live branches. Reviewed-by: Amit Langote <amitlangote09@gmail.com> Discussion: https://postgr.es/m/202411051201.zody6mld7vkw@alvherre.pgsql
* Replicate generated columns when 'publish_generated_columns' is set.Amit Kapila2024-11-07
| | | | | | | | | | | | | | | | | | | | This patch builds on the work done in commit 745217a051 by enabling the replication of generated columns alongside regular column changes through a new publication parameter: publish_generated_columns. Example usage: CREATE PUBLICATION pub1 FOR TABLE tab_gencol WITH (publish_generated_columns = true); The column list takes precedence. If the generated columns are specified in the column list, they will be replicated even if 'publish_generated_columns' is set to false. Conversely, if generated columns are not included in the column list (assuming the user specifies a column list), they will not be replicated even if 'publish_generated_columns' is true. Author: Vignesh C, Shubham Khanna Reviewed-by: Peter Smith, Amit Kapila, Hayato Kuroda, Shlok Kyal, Ajin Cherian, Hou Zhijie, Masahiko Sawada Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com
* doc: Remove event trigger firing matrixPeter Eisentraut2024-11-06
| | | | | | | | | | | | | | | | This is difficult to maintain accurately, and it was probably already somewhat incorrect, especially in the sql_drop and table_rewrite categories. The prior section already documented which DDL commands are *not* supported (which was also slightly outdated), so let's expand that a bit and just rely on that instead of listing out each command in full detail. Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Reviewed-by: Michael Paquier <michael@paquier.xyz> Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CACJufxE_UAuxcM08BW5oVsg34v0cFWoEt8yBa5xSAoKLmL6LTQ%40mail.gmail.com
* Revert pg_wal_replay_wait() stored procedureAlexander Korotkov2024-11-04
| | | | | | | | | | | | | | | | This commit reverts 3c5db1d6b0, and subsequent improvements and fixes including 8036d73ae3, 867d396ccd, 3ac3ec580c, 0868d7ae70, 85b98b8d5a, 2520226c95, 014f9f34d2, e658038772, e1555645d7, 5035172e4a, 6cfebfe88b, 73da6b8d1b, and e546989a26. The reason for reverting is a set of remaining issues. Most notably, the stored procedure appears to need more effort than the utility statement to turn the backend into a "snapshot-less" state. This makes an approach to use stored procedures questionable. Catversion is bumped. Discussion: https://postgr.es/m/Zyhj2anOPRKtb0xW%40paquier.xyz
* doc: use more accurate URL for bug reportingBruce Momjian2024-11-04
| | | | | | | | Reported-by: nat@makarevitch.org Discussion: https://postgr.es/m/172947609746.699.14488791149769110078@wrigleys.postgresql.org Backpatch-through: master
* pg_basebackup, pg_receivewal: fix failure to find password in ~/.pgpass.Tom Lane2024-11-04
| | | | | | | | | | | | | | | | | | | | | | | Sloppy refactoring in commit cca97ce6a caused these programs to pass dbname = NULL to libpq if there was no "--dbname" switch on the command line, where before "replication" would be passed. This didn't break things completely, because the source server doesn't care about the dbname specified for a physical replication connection. However, it did cause libpq to fail to match a ~/.pgpass entry that has "replication" in the dbname field. Restore the previous behavior of passing "replication". Also, closer inspection shows that if you do specify a dbname in the connection string, that is what will be matched to ~/.pgpass, not "replication". This was the pre-existing behavior so we should not change it, but the SGML docs were pretty misleading about it. Improve that. Per bug #18685 from Toshi Harada. Back-patch to v17 where the error crept in. Discussion: https://postgr.es/m/18685-fee2dd142b9688f1@postgresql.org Discussion: https://postgr.es/m/2702546.1730740456@sss.pgh.pa.us
* doc: remove check of SVG files, since they are derivedBruce Momjian2024-11-04
| | | | | | | | | | revert of change from commit 641a5b7a144 Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/2c5dd601-b245-4092-9c27-6d1ad51609df@eisentraut.org Backpatch-through: master
* docs: Consistently use <optional> to indicate optional parametersHeikki Linnakangas2024-11-04
| | | | | | | | | Some functions were using square brackets instead, replace them all with <optional>. Author: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> Reviewed-by: jian he <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CACJufxFfUbSph5UUSsZbL4SitbuPuW%3DEccpKgEaZrjtrPPuadQ@mail.gmail.com
* doc: fix typo in mvcc clarification in commit 2fa255ce9b9Bruce Momjian2024-11-04
| | | | | | Reported-by: Erik Rijkers (private email) Backpatch-through: master
* Make PG_TEST_EXTRA env var override the "meson setup" optionHeikki Linnakangas2024-11-04
| | | | | | | | | | | | | | | | | | | | | | "meson test" used to ignore the PG_TEST_EXTRA environment variable, which meant that in order to run additional tests, you had to run "meson setup -DPG_TEST_EXTRA=...". That's somewhat expensive, and not consistent with autoconf builds. Allow PG_TEST_EXTRA environment variable to override the setup-time option at run time, so that you can do "PG_TEST_EXTRA=... meson test". To implement this, the configuration time value is passed as an extra "--pg-test-extra" argument to testwrap instead of adding it to the test environment. If the environment variable is set at the time of running test, testwrap uses the value from the environment variable and ignores the --pg-test-extra option. Now that "meson test" obeys the environment variable, we can remove it from the "meson setup" steps in the CI script. It will now be picked up from the environment variable like with "make check". Author: Nazir Bilal Yavuzk, Ashutosh Bapat Reviewed-by: Ashutosh Bapat with inputs from Tom Lane and Andrew Dunstan
* Doc: Update the behavior of generated columns in Logical Replication.Amit Kapila2024-11-04
| | | | | | | | | | Commit 745217a051 misses updating the new behavior of generated columns in logical replication at a few places. Reported-by: Peter Smith, Ajin Cherian Author: Vignesh C Discussion: https://postgr.es/m/CALDaNm2JOO7szz9+uaQbjmgZOfzbM_9tAQdFF8H5BjkQeaJs0A@mail.gmail.com Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com
* doc: clarify text around MVCC example queryBruce Momjian2024-11-01
| | | | | | | | Reported-by: marlene.brandstaetter@cargonet.software Discussion: https://postgr.es/m/167765529052.987840.12345375075704447735@wrigleys.postgresql.org Backpatch-through: master
* doc: remove useless MERGE exampleBruce Momjian2024-11-01
| | | | | | | | Reported-by: dwayne.towell@gmail.com Discussion: https://postgr.es/m/167699245721.1902146.6479762301617101634@wrigleys.postgresql.org Backpatch-through: master
* doc: improve tablespace example query and link to helper funcs.Bruce Momjian2024-11-01
| | | | | | | | Reported-by: Agustín Discussion: https://postgr.es/m/172609721070.1128084.6724666076293146476@wrigleys.postgresql.org Backpatch-through: master
* doc: fix ALTER DOMAIN domain_constraint to spell out optionsBruce Momjian2024-11-01
| | | | | | | | | | | It used to refer to CREATE DOMAIN, but CREATE DOMAIN allows NULL, while ALTER DOMAIN does not. Reported-by: elionescu@yahoo.com Discussion: https://postgr.es/m/172225092461.915373.6103973717483380183@wrigleys.postgresql.org Backpatch-through: 12