aboutsummaryrefslogtreecommitdiff
path: root/doc/src
Commit message (Collapse)AuthorAge
...
* Add pg_basetype() function to extract a domain's base type.Tom Lane2024-03-30
| | | | | | | | | | | | | This SQL-callable function behaves much like our internal utility function getBaseType(), except it returns NULL rather than failing for an invalid type OID. (That behavior is modeled on our experience with other catalog-inquiry functions such as the ACL checking functions.) The key advantage over doing a join to pg_type is that it will loop as needed to find the bottom base type of a nest of domains. Steve Chavez, reviewed by jian he and others Discussion: https://postgr.es/m/CAGRrpzZSX8j=MQcbCSEisFA=ic=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com
* doc: Improve "Partition Maintenance" sectionAlvaro Herrera2024-03-30
| | | | | | | | This adds some reference links and clarifies the wording a bit. Author: Robert Treat <rob@xzilla.net> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Discussion: https://postgr.es/m/CABV9wwNGn-pweak6_pvL5PJ1mivDNPKfg0Tck_1oTUETv5Y=dg@mail.gmail.com
* Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.Dean Rasheed2024-03-30
| | | | | | | | | | | | | | | | | | | This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or DO NOTHING sub-commands. This is in contrast to already-supported WHEN NOT MATCHED actions, which operate on rows that exist in the data source, but not in the target relation. To make this distinction clearer, such actions may now be written as WHEN NOT MATCHED BY TARGET. Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is equivalent to writing WHEN NOT MATCHED BY TARGET. Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing. Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com
* Add allow_alter_system GUC.Robert Haas2024-03-29
| | | | | | | | | | | | | | | | | | This is marked PGC_SIGHUP, so it can only be set in a configuration file, not anywhere else; and it is also marked GUC_DISALLOW_IN_AUTO_FILE, so it can't be set using ALTER SYSTEM. When set to false, the ALTER SYSTEM command is disallowed. There was considerable concern that this would be misinterpreted as a security feature, which it is not, because a determined superuser has various ways of bypassing it. Hence, a lot of work has gone into wordsmithing the documentation, in the hopes of avoiding any such confusion. Jelte Fennemia-Nio and Gabriele Bartolini, with wording suggestions for the documentation from many others. Discussion: http://postgr.es/m/CA%2BVUV5rEKt2%2BCdC_KUaPoihMu%2Bi5ChT4WVNTr4CD5-xXZUfuQw%40mail.gmail.com
* Allow "internal" subtransactions in parallel mode.Tom Lane2024-03-28
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Allow use of BeginInternalSubTransaction() in parallel mode, so long as the subtransaction doesn't attempt to acquire an XID or increment the command counter. Given those restrictions, the other parallel processes don't need to know about the subtransaction at all, so this should be safe. The benefit is that it allows subtransactions intended for error recovery, such as pl/pgsql exception blocks, to be used in PARALLEL SAFE functions. Another reason for doing this is that the API of BeginInternalSubTransaction() doesn't allow reporting failure. pl/python for one, and perhaps other PLs, copes very poorly with an error longjmp out of BeginInternalSubTransaction(). The headline feature of this patch removes the only easily-triggerable failure case within that function. There remain some resource-exhaustion and similar cases, which we now deal with by promoting them to FATAL errors, so that callers need not try to clean up. (It is likely that such errors would leave us with corrupted transaction state inside xact.c, making recovery difficult if not impossible anyway.) Although this work started because of a report of a pl/python crash, we're not going to do anything about that in the back branches. Back-patching this particular fix is obviously not very wise. While we could contemplate some narrower band-aid, pl/python is already an untrusted language, so it seems okay to classify this as a "so don't do that" case. Patch by me, per report from Hao Zhang. Thanks to Robert Haas for review. Discussion: https://postgr.es/m/CALY6Dr-2yLVeVPhNMhuBnRgOZo1UjoTETgtKBx1B2gUi8yy+3g@mail.gmail.com
* doc: fix CREATE ROLE typoBruce Momjian2024-03-27
| | | | | | | | | | This wording typo was added in PG 16. Reported-by: s.bailey@chorusintel.com Discussion: https://postgr.es/m/171150077554.7105.801523271545956671@wrigleys.postgresql.org Backpatch-through: 16
* Adjust documentation for syncfs().Nathan Bossart2024-03-27
| | | | | | | | | | | | | | | | Commit 8c16ad3b43 created a new appendix for syncfs(), which is excessive for such a small amount of content. This commit moves the description of the caveats to be aware of when using syncfs() back to the documentation for recovery_init_sync_method. The documentation for the other utilities with syncfs() support now directs readers to recovery_init_sync_method for information about these caveats. Reported-by: Peter Eisentraut, Robert Haas Suggested-by: Robert Haas Reviewed-by: Robert Haas Discussion: https://postgr.es/m/42804669-7063-1320-ed37-3226d5f1067d%40eisentraut.org Discussion: https://postgr.es/m/CA%2BTgmobUiqKr%2BZMCLc5Qap-sXBnjfGUU%2BZBmzYEjUuWyjsGr1g%40mail.gmail.com
* Add functions to generate random numbers in a specified range.Dean Rasheed2024-03-27
| | | | | | | | | | | | | | | | | | | | | | | | This adds 3 new variants of the random() function: random(min integer, max integer) returns integer random(min bigint, max bigint) returns bigint random(min numeric, max numeric) returns numeric Each returns a random number x in the range min <= x <= max. For the numeric function, the number of digits after the decimal point is equal to the number of digits that "min" or "max" has after the decimal point, whichever has more. The main entry points for these functions are in a new C source file. The existing random(), random_normal(), and setseed() functions are moved there too, so that they can all share the same PRNG state, which is kept private to that file. Dean Rasheed, reviewed by Jian He, David Zhang, Aleksander Alekseev, and Tomas Vondra. Discussion: https://postgr.es/m/CAEZATCV89Vxuq93xQdmc0t-0Y2zeeNQTdsjbmV7dyFBPykbV4Q@mail.gmail.com
* Change last_inactive_time to inactive_since in pg_replication_slots.Amit Kapila2024-03-27
| | | | | | | | | | | | | Commit a11f330b55 added last_inactive_time to show the last time the slot was inactive. But, it tells the last time that a currently-inactive slot previously *WAS* active. This could be unclear, so we changed the name to inactive_since. Reported-by: Robert Haas Author: Bharath Rupireddy Reviewed-by: Bertrand Drouvot, Shveta Malik, Amit Kapila Discussion: https://postgr.es/m/CA+Tgmob_Ta-t2ty8QrKHBGnNLrf4ZYcwhGHGFsuUoFrAEDw4sA@mail.gmail.com Discussion: https://postgr.es/m/CALj2ACUXS0SfbHzsX8bqo+7CZhocsV52Kiu7OWGb5HVPAmJqnA@mail.gmail.com
* Adjust pgbench option for debug mode.Nathan Bossart2024-03-25
| | | | | | | | | | | | | | Many other utilities use -d to specify the database to use, but pgbench uses it to enable debug mode. This is causing some users to accidentally enable it. This commit changes -d to accept the database name and introduces --dbname. Debug mode can still be enabled with --debug. This is a backward-incompatible change, but it has been judged to be worth the trade-off, i.e., some scripts that use pgbench will need to be updated. Author: Greg Sabino Mullane Reviewed-by: Tomas Vondra, Euler Taveira, Alvaro Herrera, David Christensen Discussion: https://postgr.es/m/CAKAnmmLjAzwVtb%3DVEaeuCtnmOLpzkJ1uJ_XiQ362YdD9B72HSg%40mail.gmail.com
* Allow specifying an access method for partitioned tablesAlvaro Herrera2024-03-25
| | | | | | | | | | | | | | | | | | | | | | | | | It's now possible to specify a table access method via CREATE TABLE ... USING for a partitioned table, as well change it with ALTER TABLE ... SET ACCESS METHOD. Specifying an AM for a partitioned table lets the value be used for all future partitions created under it, closely mirroring the behavior of the TABLESPACE option for partitioned tables. Existing partitions are not modified. For a partitioned table with no AM specified, any new partitions are created with the default_table_access_method. Also add ALTER TABLE ... SET ACCESS METHOD DEFAULT, which reverts to the original state of using the default for new partitions. The relcache of partitioned tables is not changed: rd_tableam is not set, even if a partitioned table has a relam set. Author: Justin Pryzby <pryzby@telsasoft.com> Author: Soumyadeep Chakraborty <soumyadeep2007@gmail.com> Author: Michaël Paquier <michael@paquier.xyz> Reviewed-by: The authors themselves Discussion: https://postgr.es/m/CAE-ML+9zM4wJCGCBGv01k96qQ3gFv4WFcFy=zqPHKeaEFwwv6A@mail.gmail.com Discussion: https://postgr.es/m/20210308010707.GA29832%40telsasoft.com
* doc: Document error handling in PGTYPESnumeric_to_longDaniel Gustafsson2024-03-25
| | | | | | | | | The documentation for PGTYPESnumeric_to_long only mentioned errno being set to indicate overflow but the code also sets errno when underflow happens. Reported-by: Aidar Imamov <a.imamov@postgrespro.ru> Discussion: https://postgr.es/m/eebf0ad50ad4321d65d2d64dd6b7f17d@postgrespro.ru
* pg_createsubscriber: creates a new logical replica from a standby serverPeter Eisentraut2024-03-25
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | It must be run on the target server and should be able to connect to the source server (publisher) and the target server (subscriber). All tables in the specified database(s) are included in the logical replication setup. A pair of publication and subscription objects are created for each database. The main advantage of pg_createsubscriber over the common logical replication setup is the initial data copy. It also reduces the catchup phase. Some prerequisites must be met to successfully run it. It is basically the logical replication requirements. It starts creating a publication using FOR ALL TABLES and a replication slot for each specified database. Write recovery parameters into the target data directory and start the target server. It specifies the LSN of the last replication slot (replication start point) up to which the recovery will proceed. Wait until the target server is promoted. Create one subscription per specified database (using publication and replication slot created in a previous step) on the target server. Set the replication progress to the replication start point for each subscription. Enable the subscription for each specified database on the target server. And finally, change the system identifier on the target server. Author: Euler Taveira <euler.taveira@enterprisedb.com> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Shlok Kyal <shlok.kyal.oss@gmail.com> Reviewed-by: Vignesh C <vignesh21@gmail.com> Reviewed-by: Shubham Khanna <khannashubham1197@gmail.com> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://www.postgresql.org/message-id/flat/5ac50071-f2ed-4ace-a8fd-b892cffd33eb@www.fastmail.com
* Track last_inactive_time in pg_replication_slots.Amit Kapila2024-03-25
| | | | | | | | | | | | | | | | | | | | | This commit adds a new property called last_inactive_time for slots. It is set to 0 whenever a slot is made active/acquired and set to the current timestamp whenever the slot is inactive/released or restored from the disk. Note that we don't set the last_inactive_time for the slots currently being synced from the primary to the standby because such slots are typically inactive as decoding is not allowed on those. The 'last_inactive_time' will be useful on production servers to debug and analyze inactive replication slots. It will also help to know the lifetime of a replication slot - one can know how long a streaming standby, logical subscriber, or replication slot consumer is down. The 'last_inactive_time' will also be useful to implement inactive timeout-based replication slot invalidation in a future commit. Author: Bharath Rupireddy Reviewed-by: Bertrand Drouvot, Amit Kapila, Shveta Malik Discussion: https://www.postgresql.org/message-id/CALj2ACW4aUe-_uFQOjdWCEN-xXoLGhmvRFnL8SNw_TZ5nJe+aw@mail.gmail.com
* reindexdb: Add the index-level REINDEX with multiple jobsAlexander Korotkov2024-03-25
| | | | | | | | | | | | | | | | | | Straight-forward index-level REINDEX is not supported with multiple jobs as we cannot control the concurrent processing of multiple indexes depending on the same relation. Instead, we dedicate the whole table to certain reindex job. Thus, if indexes in the lists belong to different tables, that gives us a fair level of parallelism. This commit teaches get_parallel_object_list() to fetch table names for indexes in the case of index-level REINDEX. The same tables are grouped together in the output order, and the list of indexes is also rebuilt to match that order. Later during processingof that list, we push indexes belonging to the same table into the same job. Discussion: https://postgr.es/m/CACG%3DezZU_VwDi-1PN8RUSE6mcYG%2BYx1NH_rJO4%2BKe-mKqLp%3DNw%40mail.gmail.com Author: Maxim Orlov, Svetlana Derevyanko, Alexander Korotkov Reviewed-by: Michael Paquier
* doc: Clarify requirements for SET ROLE.Nathan Bossart2024-03-24
| | | | | | | | | | | | Since commit 3d14e171e9, SET ROLE has required the current session user to have membership with the SET option in the target role, but the SET ROLE documentation only mentions the membership requirement. This commit adds this important detail to the SET ROLE page. Reviewed-by: Robert Haas Discussion: https://postgr.es/m/CA%2BRLCQysHtME0znk2KUMJN343ksboSRQSU-hCnOjesX6VK300Q%40mail.gmail.com Backpatch-through: 16
* Add temporal FOREIGN KEY contraintsPeter Eisentraut2024-03-24
| | | | | | | | | | | | | | | | | | Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality. This feature matches the behavior of the SQL standard temporal foreign keys, but it works on PostgreSQL's native ranges instead of SQL's "periods", which don't exist in PostgreSQL (yet). Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT} are not supported yet. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: jian he <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
* Revert "Add notBefore and notAfter to SSL cert info display"Daniel Gustafsson2024-03-22
| | | | | | | | | | This reverts commit 6acb0a628eccab8764e0306582c2b7e2a1441b9b since LibreSSL didn't support ASN1_TIME_diff until OpenBSD 7.1, leaving the older OpenBSD animals in the buildfarm complaining. Per plover in the buildfarm. Discussion: https://postgr.es/m/F0DF7102-192D-4C21-96AE-9A01AE153AD1@yesql.se
* Add notBefore and notAfter to SSL cert info displayDaniel Gustafsson2024-03-22
| | | | | | | | | | | | | | | | | | This adds the X509 attributes notBefore and notAfter to sslinfo as well as pg_stat_ssl to allow verifying and identifying the validity period of the current client certificate. OpenSSL has APIs for extracting notAfter and notBefore, but they are only supported in recent versions so we have to calculate the dates by hand in order to make this work for the older versions of OpenSSL that we still support. Original patch by Cary Huang with additional hacking by Jacob and myself. Author: Cary Huang <cary.huang@highgo.ca> Co-author: Jacob Champion <jacob.champion@enterprisedb.com> Co-author: Daniel Gustafsson <daniel@yesql.se> Discussion: https://postgr.es/m/182b8565486.10af1a86f158715.2387262617218380588@highgo.ca
* Track invalidation_reason in pg_replication_slots.Amit Kapila2024-03-22
| | | | | | | | | | | | | | | | | | | | | | | | Till now, the reason for replication slot invalidation is not tracked directly in pg_replication_slots. A recent commit 007693f2a3 added 'conflict_reason' to show the reasons for slot conflict/invalidation, but only for logical slots. This commit adds a new column 'invalidation_reason' to show invalidation reasons for both physical and logical slots. And, this commit also turns 'conflict_reason' text column to 'conflicting' boolean column (effectively reverting commit 007693f2a3). The 'conflicting' column is true for invalidation reasons 'rows_removed' and 'wal_level_insufficient' because those make the slot conflict with recovery. When 'conflicting' is true, one can now look at the new 'invalidation_reason' column for the reason for the logical slot's conflict with recovery. The new 'invalidation_reason' column will also be useful to track other invalidation reasons in the future commit. Author: Bharath Rupireddy Reviewed-by: Bertrand Drouvot, Amit Kapila, Shveta Malik Discussion: https://www.postgresql.org/message-id/ZfR7HuzFEswakt/a%40ip-10-97-1-34.eu-west-3.compute.internal Discussion: https://www.postgresql.org/message-id/CALj2ACW4aUe-_uFQOjdWCEN-xXoLGhmvRFnL8SNw_TZ5nJe+aw@mail.gmail.com
* Add hash support functions and hash opclass for contrib/ltree.Tom Lane2024-03-21
| | | | | | | | This also enables hash join and hash aggregation on ltree columns. Tommy Pavlicek, reviewed by jian he Discussion: https://postgr.es/m/CAEhP-W9ZEoHeaP_nKnPCVd_o1c3BAUvq1gWHrq8EbkNRiS9CvQ@mail.gmail.com
* docs: Make claims about the benefits of HOT updates more precise.Robert Haas2024-03-21
| | | | | | | | | | | | | | | | | | | | | The old text claims that HOT completely removes old row versions. It was unclear whether it just meant the tuples themselves, or the tuples together with their line pointers. If it meant the former, it was wrong because we can remove dead row versions even when no HOT updates have occurred, so it's not describing a benefit of HOT. If it meant the latter, it was wrong because HOT doesn't allow reclaiming the root tuple's line pointer. This section does seems like it's intended to be more of an informal introduction to HOT than a precise technical description of every detail of how it works, but we still don't want it to say things that are just not true, so update the text enough to avoid that. Patch by me, reviewed by James Coleman (although he would have preferred more extensive changes) and Shubham Khanna. Discussion: http://postgr.es/m/CA+TgmobH6DPmR-u--Xgeg8cYUwhDhypNsv38nDrAJyf_xno=TQ@mail.gmail.com
* Add SQL/JSON query functionsAmit Langote2024-03-21
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This introduces the following SQL/JSON functions for querying JSON data using jsonpath expressions: JSON_EXISTS(), which can be used to apply a jsonpath expression to a JSON value to check if it yields any values. JSON_QUERY(), which can be used to to apply a jsonpath expression to a JSON value to get a JSON object, an array, or a string. There are various options to control whether multi-value result uses array wrappers and whether the singleton scalar strings are quoted or not. JSON_VALUE(), which can be used to apply a jsonpath expression to a JSON value to return a single scalar value, producing an error if it multiple values are matched. Both JSON_VALUE() and JSON_QUERY() functions have options for handling EMPTY and ERROR conditions, which can be used to specify the behavior when no values are matched and when an error occurs during jsonpath evaluation, respectively. Author: Nikita Glukhov <n.gluhov@postgrespro.ru> Author: Teodor Sigaev <teodor@sigaev.ru> Author: Oleg Bartunov <obartunov@gmail.com> Author: Alexander Korotkov <aekorotkov@gmail.com> Author: Andrew Dunstan <andrew@dunslane.net> Author: Amit Langote <amitlangote09@gmail.com> Author: Peter Eisentraut <peter@eisentraut.org> Author: Jian He <jian.universality@gmail.com> Reviewers have included (in no particular order): Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, Jian He, Anton A. Melnikov, Nikita Malakhov, Peter Eisentraut, Tomas Vondra Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
* Allow dbname to be written as part of connstring via pg_basebackup's -R option.Amit Kapila2024-03-21
| | | | | | | | | | | | | | | | Commit cca97ce6a665 allowed dbname in pg_basebackup connstring and in this commit we allow it to be written in postgresql.auto.conf when -R option is used. The database name in the connection string will be used by the logical replication slot synchronization on standby. The dbname will be recorded only if specified explicitly in the connection string or environment variable. Masahiko Sawada hasn't reviewed the code in detail but endorsed the idea. Author: Vignesh C, Kuroda Hayato Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/CAB8KJ=hdKdg+UeXhReeHpHA6N6v3e0qFF+ZsPFHk9_ThWKf=2A@mail.gmail.com
* Add to_regtypemod function to extract typemod from a string type name.Tom Lane2024-03-20
| | | | | | | | | | | | | | | | | | | | | | | In combination with to_regtype, this allows converting a string to the "canonicalized" form emitted by format_type. That usage requires parsing the string twice, which is slightly annoying but not really too expensive. We considered alternatives such as returning a record type, but that way was notationally uglier than this, and possibly less flexible. Like to_regtype(), we'd rather that this return NULL for any bad input, but the underlying type-parsing logic isn't yet capable of not throwing syntax errors. Adjust the documentation for both functions to point that out. In passing, fix up a couple of nearby entries in the System Catalog Information Functions table that had not gotten the word about our since-v13 convention for displaying function usage examples. David Wheeler and Erik Wienhold, reviewed by Pavel Stehule, Jim Jones, and others. Discussion: https://postgr.es/m/DF2324CA-2673-4ABE-B382-26B5770B6AA3@justatheory.com
* Review wording on tablespaces w.r.t. partitioned tablesAlvaro Herrera2024-03-20
| | | | | | | | | | | Remove a redundant comment, and document pg_class.reltablespace properly in catalogs.sgml. After commits a36c84c3e4a9, 87259588d0ab and others. Backpatch to 12. Discussion: https://postgr.es/m/202403191013.w2kr7wqlamqz@alvherre.pgsql
* Add "--exclude-extension" to pg_dump's options.Dean Rasheed2024-03-20
| | | | | | | | | | | This option (or equivalently specifying "exclude extension pattern" in a filter file) allows extensions matching the specified pattern to be excluded from the dump. Ayush Vatsa, reviewed by Junwang Zhao, Dean Rasheed, and Daniel Gustafsson. Discussion: https://postgr.es/m/CACX+KaP=VgVy9h-EUh598DTu+-fNr1jyEmpghC8rRp9s=w33Kg@mail.gmail.com
* Support C.UTF-8 locale in the new builtin collation provider.Jeff Davis2024-03-19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | The builtin C.UTF-8 locale has similar semantics to the libc locale of the same name. That is, code point sort order (fast, memcmp-based) combined with Unicode semantics for character operations such as pattern matching, regular expressions, and LOWER()/INITCAP()/UPPER(). The character semantics are based on Unicode simple case mappings. The builtin provider's C.UTF-8 offers several important advantages over libc: * faster sorting -- benefits from additional optimizations such as abbreviated keys and varstrfastcmp_c * faster case conversion, e.g. LOWER(), at least compared with some libc implementations * available on all platforms with identical semantics, and the semantics are stable, testable, and documentable within a given Postgres major version Being based on memcmp, the builtin C.UTF-8 locale does not offer natural language sort order. But it is an improvement for most use cases that might otherwise use libc's "C.UTF-8" locale, as well as many use cases that use libc's "C" locale. Discussion: https://postgr.es/m/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com Reviewed-by: Daniel Vérité, Peter Eisentraut, Jeremy Schneider
* Improve EXPLAIN's display of SubPlan nodes and output parameters.Tom Lane2024-03-19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | Historically we've printed SubPlan expression nodes as "(SubPlan N)", which is pretty uninformative. Trying to reproduce the original SQL for the subquery is still as impractical as before, and would be mighty verbose as well. However, we can still do better than that. Displaying the "testexpr" when present, and adding a keyword to indicate the SubLinkType, goes a long way toward showing what's really going on. In addition, this patch gets rid of EXPLAIN's use of "$n" to represent subplan and initplan output Params. Instead we now print "(SubPlan N).colX" or "(InitPlan N).colX" to represent the X'th output column of that subplan. This eliminates confusion with the use of "$n" to represent PARAM_EXTERN Params, and it's useful for the first part of this change because it eliminates needing some other indication of which subplan is referenced by a SubPlan that has a testexpr. In passing, this adds simple regression test coverage of the ROWCOMPARE_SUBLINK code paths, which were entirely unburdened by testing before. Tom Lane and Dean Rasheed, reviewed by Aleksander Alekseev. Thanks to Chantal Keller for raising the question of whether this area couldn't be improved. Discussion: https://postgr.es/m/2838538.1705692747@sss.pgh.pa.us
* Add some UUID support functionsPeter Eisentraut2024-03-19
| | | | | | | | | | Add uuid_extract_timestamp() and uuid_extract_version(). Author: Andrey Borodin Reviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch Reviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev Reviewed-by: Peter Eisentraut, Chris Travers, Lukas Fittl Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
* Fix documentation oversights from 2d819a08a1.Jeff Davis2024-03-18
|
* Address more review comments on commit 2d819a08a1.Jeff Davis2024-03-18
| | | | | | | | | | | | | Based on comments from Peter Eisentraut. * Document CREATE DATABASE ... BUILTIN_LOCALE. * Determine required encoding based on locale name for CREATE COLLATION. Use -1 for "C" (requires catversion bump). * initdb output fixups. * Make ctype_is_c a constant true for now. * Fixups to ICU 010_create_database.pl test. Discussion: https://postgr.es/m/4135cf11-206d-40ed-96c0-9363c1232379@eisentraut.org
* Fix PDF doc generation.Dean Rasheed2024-03-17
| | | | | | | Commit c649fa24a4 broke PDF generation, due to a misplaced id attribute. Per buildfarm member crake.
* Add RETURNING support to MERGE.Dean Rasheed2024-03-17
| | | | | | | | | | | | | | | | | | | | | | | | | | This allows a RETURNING clause to be appended to a MERGE query, to return values based on each row inserted, updated, or deleted. As with plain INSERT, UPDATE, and DELETE commands, the returned values are based on the new contents of the target table for INSERT and UPDATE actions, and on its old contents for DELETE actions. Values from the source relation may also be returned. As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be used as the source relation for other operations such as WITH queries and COPY commands. Additionally, a special function merge_action() is provided, which returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action executed for each row. The merge_action() function can be used anywhere in the RETURNING list, including in arbitrary expressions and subqueries, but it is an error to use it anywhere outside of a MERGE query's RETURNING list. Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera, Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut, and Wolfgang Walther. Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com
* Make stxstattarget nullablePeter Eisentraut2024-03-17
| | | | | | | | | To match attstattarget change (commit 4f622503d6d). The logic inside CreateStatistics() is clarified a bit compared to that previous patch, and so here we also update ATExecSetStatistics() to match. Reviewed-by: Tomas Vondra <tomas.vondra@enterprisedb.com> Discussion: https://www.postgresql.org/message-id/flat/4da8d211-d54d-44b9-9847-f2a9f1184c76@eisentraut.org
* Login event trigger documentation wordsmithingDaniel Gustafsson2024-03-14
| | | | | | | | | Minor wordsmithing on the login trigger documentation and code comments to improve readability, as well as fixing a few small incorrect statements in the comments. Author: Robert Treat <rob@xzilla.net> Discussion: https://postgr.es/m/CAJSLCQ0aMWUh1m6E9YdjeqV61baQ=EhteJX8XOxXg8H_2Lcr0Q@mail.gmail.com
* Add pg_column_toast_chunk_id().Nathan Bossart2024-03-14
| | | | | | | | | | | | | This function returns the chunk_id of an on-disk TOASTed value. If the value is un-TOASTed or not on-disk, it returns NULL. This is useful for identifying which values are actually TOASTed and for investigating "unexpected chunk number" errors. Bumps catversion. Author: Yugo Nagata Reviewed-by: Jian He Discussion: https://postgr.es/m/20230329105507.d764497456eeac1ca491b5bd%40sraoss.co.jp
* Introduce "builtin" collation provider.Jeff Davis2024-03-13
| | | | | | | | | | | | | | | | | | | | | | | | | New provider for collations, like "libc" or "icu", but without any external dependency. Initially, the only locale supported by the builtin provider is "C", which is identical to the libc provider's "C" locale. The libc provider's "C" locale has always been treated as a special case that uses an internal implementation, without using libc at all -- so the new builtin provider uses the same implementation. The builtin provider's locale is independent of the server environment variables LC_COLLATE and LC_CTYPE. Using the builtin provider, the database collation locale can be "C" while LC_COLLATE and LC_CTYPE are set to "en_US", which is impossible with the libc provider. By offering a new builtin provider, it clarifies that the semantics of a collation using this provider will never depend on libc, and makes it easier to document the behavior. Discussion: https://postgr.es/m/ab925f69-5f9d-f85e-b87c-bd2a44798659@joeconway.com Discussion: https://postgr.es/m/dd9261f4-7a98-4565-93ec-336c1c110d90@manitou-mail.org Discussion: https://postgr.es/m/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com Reviewed-by: Daniel Vérité, Peter Eisentraut, Jeremy Schneider
* Improve documentation for pg_stat_checkpointer fieldsAlexander Korotkov2024-03-14
| | | | | | | | | | | | pg_stat_checkpointer contains statistics for checkpoints and restartpoints. Before 12915a58eec9 documentation said only about checkpoints implying that restartpoint is the variation of checkpoint. 12915a58eec9 introduced new separate statistics fields for restartpoints. This commit explicitly documents fields that are relevant for both checkpoints and restartpoints. Reported-by: Magnus Hagander Discussion: https://postgr.es/m/CABUevExav5-SR0x%2BG9kBUMV0G8XsvSUfuyyqmYBBJi6VHns6sw%40mail.gmail.com Reviewed-by: Anton A. Melnikov
* Reintroduce MAINTAIN privilege and pg_maintain predefined role.Nathan Bossart2024-03-13
| | | | | | | | | | | | | | | | | | Roles with MAINTAIN on a relation may run VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZE VIEW, CLUSTER, and LOCK TABLE on the relation. Roles with privileges of pg_maintain may run those same commands on all relations. This was previously committed for v16, but it was reverted in commit 151c22deee due to concerns about search_path tricks that could be used to escalate privileges to the table owner. Commits 2af07e2f74, 59825d1639, and c7ea3f4229 resolved these concerns by restricting search_path when running maintenance commands. Bumps catversion. Reviewed-by: Jeff Davis Discussion: https://postgr.es/m/20240305161235.GA3478007%40nathanxps13
* Add the system identifier to backup manifests.Robert Haas2024-03-13
| | | | | | | | | | | | | | | | | | Before this patch, if you took a full backup on server A and then tried to use the backup manifest to take an incremental backup on server B, it wouldn't know that the manifest was from a different server and so the incremental backup operation could potentially complete without error. When you later tried to run pg_combinebackup, you'd find out that your incremental backup was and always had been invalid. That's poor timing, because nobody likes finding out about backup problems only at restore time. With this patch, you'll get an error when trying to take the (invalid) incremental backup, which seems a lot nicer. Amul Sul, revised by me. Review by Michael Paquier. Discussion: http://postgr.es/m/CA+TgmoYLZzbSAMM3cAjV4Y+iCRZn-bR9H2+Mdz7NdaJFU1Zb5w@mail.gmail.com
* doc: Improve a couple of places in the MERGE docs.Dean Rasheed2024-03-13
| | | | | | | | | | | | | | | | | In the synopsis, make the syntax for merge_update consistent with the syntax for a plain UPDATE command. It was missing the optional "ROW" keyword that can be used in a multi-column assignment, and the option to assign from a multi-column subquery, both of which have been supported by MERGE since it was introduced. In the parameters section for the with_query parameter, mention that WITH RECURSIVE isn't supported, since this is different from plain INSERT, UPDATE, and DELETE commands. While at it, move that entry to the top of the list, for consistency with the other pages. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWoQyWkMFfu7JXXQr8dA6%3DgxjhYzgpuBP2oz0QoJTxGWw%40mail.gmail.com
* libpq: Add encrypted and non-blocking query cancellation routinesAlvaro Herrera2024-03-12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The existing PQcancel API uses blocking IO, which makes PQcancel impossible to use in an event loop based codebase without blocking the event loop until the call returns. It also doesn't encrypt the connection over which the cancel request is sent, even when the original connection required encryption. This commit adds a PQcancelConn struct and assorted functions, which provide a better mechanism of sending cancel requests; in particular all the encryption used in the original connection are also used in the cancel connection. The main entry points are: - PQcancelCreate creates the PQcancelConn based on the original connection (but does not establish an actual connection). - PQcancelStart can be used to initiate non-blocking cancel requests, using encryption if the original connection did so, which must be pumped using - PQcancelPoll. - PQcancelReset puts a PQcancelConn back in state so that it can be reused to send a new cancel request to the same connection. - PQcancelBlocking is a simpler-to-use blocking API that still uses encryption. Additional functions are - PQcancelStatus, mimicks PQstatus; - PQcancelSocket, mimicks PQcancelSocket; - PQcancelErrorMessage, mimicks PQerrorMessage; - PQcancelFinish, mimicks PQfinish. Author: Jelte Fennema-Nio <postgres@jeltef.nl> Reviewed-by: Denis Laxalde <denis.laxalde@dalibo.com> Discussion: https://postgr.es/m/AM5PR83MB0178D3B31CA1B6EC4A8ECC42F7529@AM5PR83MB0178.EURPRD83.prod.outlook.com
* reindexdb: Allow specifying objects to process in all databases.Nathan Bossart2024-03-11
| | | | | | | | | | | | | Presently, reindexdb's --table, --schema, --index, and --system options cannot be used together with --all, i.e., you cannot specify objects to process in all databases. This commit removes this unnecessary restriction. Furthermore, it removes the restriction that --system cannot be used with --table, --schema, and --index. There is no such restriction for the latter options, and there is no technical reason to disallow these combinations. Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13
* clusterdb: Allow specifying tables to process in all databases.Nathan Bossart2024-03-11
| | | | | | | | | | | | | Presently, clusterdb's --table option cannot be used together with --all, i.e., you cannot specify tables to process in all databases. This commit removes this unnecessary restriction. In passing, change the synopsis in the documentation to use "[option...]" instead of "[--verbose | -v]". There are other general-purpose options (e.g., --quiet and --echo), but the synopsis currently only lists --verbose. Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13
* doc: add missing word "the"Bruce Momjian2024-03-11
| | | | | | | | Reported-by: doughale@gmail.com Discussion: https://postgr.es/m/170993253510.640.5664117187431542912@wrigleys.postgresql.org Backpatch-through: 12
* Add missing connection statuses to docsAlvaro Herrera2024-03-11
| | | | | | | | | | | | | | | | | The list of connection statuses that PQstatus might return during an asynchronous connection attempt was outdated: 1. CONNECTION_SETENV is never returned anymore and is only part of the enum for backwards compatibility, so remove it from the docs. 2. CONNECTION_CHECK_STANDBY and CONNECTION_GSS_STARTUP were not listed, so add them. CONNECTION_NEEDED and CONNECTION_CHECK_TARGET are not listed in the docs on purpose, since these are internal states that can never be observed by a caller of PQstatus. Author: Jelte Fennema-Nio <postgres@jeltef.nl> Discussion: https://postgr.es/m/CAGECzQRb21spiiykQ48rzz8w+Hcykz+mB2_hxR65D9Qk6nnw=w@mail.gmail.com
* vacuumdb: Allow specifying objects to process in all databases.Nathan Bossart2024-03-11
| | | | | | | | | | | Presently, vacuumdb's --table, --schema, and --exclude-schema options cannot be used together with --all, i.e., you cannot specify tables or schemas to process in all databases. This commit removes this unnecessary restriction, thus enabling potentially useful commands like "vacuumdb --all --schema pg_catalog". Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13
* Doc: Warn about two_phase when altering a subscription's slot name.Amit Kapila2024-03-11
| | | | | | | | | | | We expect the 'two_phase' and 'failover' properties to match between the slot on the publisher and a subscription option on the subscriber. Otherwise, the slot on the publisher may behave differently from what the subscription's failover option says. Author: Bertrand Drouvot Reviewed-by: Peter Smith, Tristen Raab, Amit Kapila Discussion: https://postgr.es/m/ZbkYrLPhH+RxpZlW@ip-10-97-1-34.eu-west-3.compute.internal
* Catalog changes preparing for builtin collation provider.Jeff Davis2024-03-09
| | | | | | | | | | | | Rename pg_collation.colliculocale to colllocale, and pg_database.daticulocale to datlocale. These names reflects that the fields will be useful for the upcoming builtin provider as well, not just for ICU. This is purely a rename; no changes to the meaning of the fields. Discussion: https://postgr.es/m/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com Reviewed-by: Peter Eisentraut
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-07 13:26:11 +0000