aboutsummaryrefslogtreecommitdiff
path: root/doc/src
Commit message (Collapse)AuthorAge
...
* Add delay time to VACUUM/ANALYZE (VERBOSE) and autovacuum logs.Nathan Bossart2025-02-14
| | | | | | | | | | | Commit bb8dff9995 added this information to the pg_stat_progress_vacuum and pg_stat_progress_analyze system views. This commit adds the same information to the output of VACUUM and ANALYZE with the VERBOSE option and to the autovacuum logs. Suggested-by: Masahiro Ikeda <ikedamsh@oss.nttdata.com> Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com> Discussion: https://postgr.es/m/ZmaXmWDL829fzAVX%40ip-10-97-1-34.eu-west-3.compute.internal
* pgcrypto: Add support for CFB mode in AES encryptionDaniel Gustafsson2025-02-14
| | | | | | | | | | | | | | | | Cipher Feedback Mode, CFB, is a self-synchronizing stream cipher which is very similar to CBC performed in reverse. Since OpenSSL supports it, we can easily plug it into the existing cipher selection code without any need for infrastructure changes. This patch was simultaneously submitted by Umar Hayat and Vladyslav Nebozhyn, the latter whom suggested the feauture. The committed patch is Umar's version. Author: Umar Hayat <postgresql.wizard@gmail.com> Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/CAPBGcbxo9ASzq14VTpQp3mnUJ5omdgTWUJOvWV0L6nNigWE5jw@mail.gmail.com
* Remove unnecessary (char *) casts [string]Peter Eisentraut2025-02-12
| | | | | | | | | | Remove (char *) casts around string functions where the arguments or result already have the right type and the cast is useless (or worse, potentially casts away a qualifier, but this doesn't appear to be the case here). Reviewed-by: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> Discussion: https://www.postgresql.org/message-id/flat/fd1fcedb-3492-4fc8-9e3e-74b97f2db6c7%40eisentraut.org
* Doc: Fix punctuation errorsJohn Naylor2025-02-12
| | | | | | | Author: 斉藤登 <noborusai@gmail.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/CAAM3qnL6i-BSu5rB2+KiHLjMCOXiQEiPMBvEj7F1CgUzZMooLA@mail.gmail.com Backpatch-through: 13
* Add cost-based vacuum delay time to progress views.Nathan Bossart2025-02-11
| | | | | | | | | | | | | | | | | | | | | | | This commit adds the amount of time spent sleeping due to cost-based delay to the pg_stat_progress_vacuum and pg_stat_progress_analyze system views. A new configuration parameter named track_cost_delay_timing, which is off by default, controls whether this information is gathered. For vacuum, the reported value includes the sleep time of any associated parallel workers. However, parallel workers only report their sleep time once per second to avoid overloading the leader process. Bumps catversion. Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com> Co-authored-by: Nathan Bossart <nathandbossart@gmail.com> Reviewed-by: Sami Imseih <samimseih@gmail.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Masahiro Ikeda <ikedamsh@oss.nttdata.com> Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Sergei Kornilov <sk@zsrv.org> Discussion: https://postgr.es/m/ZmaXmWDL829fzAVX%40ip-10-97-1-34.eu-west-3.compute.internal
* Limit pgbench COPY FREEZE to ordinary relationsMelanie Plageman2025-02-11
| | | | | | | | | | | | | | | | | | pgbench client-side data generation uses COPY FREEZE to load data for most tables. COPY FREEZE isn't supported for partitioned tables and since pgbench only supports partitioning pgbench_accounts, pgbench used a hard-coded check to skip COPY FREEZE and use plain COPY for a partitioned pgbench_accounts. If the user has manually partitioned one of the other pgbench tables, this causes client-side data generation to error out with: ERROR: cannot perform COPY FREEZE on a partitioned table Fix this by limiting COPY FREEZE to ordinary tables (RELKIND_RELATION). Author: Sergey Tatarintsev <s.tatarintsev@postgrespro.ru> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/flat/97f55fca-8a7b-4da8-b413-7d1c57010676%40postgrespro.ru
* Eagerly scan all-visible pages to amortize aggressive vacuumMelanie Plageman2025-02-11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Aggressive vacuums must scan every unfrozen tuple in order to advance the relfrozenxid/relminmxid. Because data is often vacuumed before it is old enough to require freezing, relations may build up a large backlog of pages that are set all-visible but not all-frozen in the visibility map. When an aggressive vacuum is triggered, all of these pages must be scanned. These pages have often been evicted from shared buffers and even from the kernel buffer cache. Thus, aggressive vacuums often incur large amounts of extra I/O at the expense of foreground workloads. To amortize the cost of aggressive vacuums, eagerly scan some all-visible but not all-frozen pages during normal vacuums. All-visible pages that are eagerly scanned and set all-frozen in the visibility map are counted as successful eager freezes and those not frozen are counted as failed eager freezes. If too many eager scans fail in a row, eager scanning is temporarily suspended until a later portion of the relation. The number of failures tolerated is configurable globally and per table. To effectively amortize aggressive vacuums, we cap the number of successes as well. Capping eager freeze successes also limits the amount of potentially wasted work if these pages are modified again before the next aggressive vacuum. Once we reach the maximum number of blocks successfully eager frozen, eager scanning is disabled for the remainder of the vacuum of the relation. Original design idea from Robert Haas, with enhancements from Andres Freund, Tomas Vondra, and me Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Bilal Yavuz <byavuz81@gmail.com> Discussion: https://postgr.es/m/flat/CAAKRu_ZF_KCzZuOrPrOqjGVe8iRVWEAJSpzMgRQs%3D5-v84cXUg%40mail.gmail.com
* config: Rename "Asynchronous Behavior" to "I/O"Andres Freund2025-02-11
| | | | | | | | | | | "I/O" seems more descriptive than "Asynchronous Behavior", given that some of the GUCs in the section don't relate to anything asynchronous. Most other abbreviations in the config sections are un-abbreviated, but "Input/Output" seems less likely to be helpful than just IO or I/O. Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/x3tlw2jk5gm3r3mv47hwrshffyw7halpczkfbk3peksxds7bvc@lguk43z3bsyq
* config: Split "Worker Processes" out of "Asynchronous Behavior"Andres Freund2025-02-11
| | | | | | | | Having all the worker related GUCs in the same section as IO controlling GUCs doesn't really make sense. Create a separate section for them. Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/x3tlw2jk5gm3r3mv47hwrshffyw7halpczkfbk3peksxds7bvc@lguk43z3bsyq
* docs: EUC_TW can be up to four bytes wide, not threeAndres Freund2025-02-10
| | | | | Backpatch-through: 13 Security: CVE-2025-1094
* PDF docs build: avoid spurious "warn" in build logs.Tom Lane2025-02-07
| | | | | | | Improve on e4c886519 so that the string "warn" appears in the output when there's a problem, and not when there isn't. This should silence noise I've been seeing in my buildfarm warning scraper.
* Doc: clarify behavior of timestamptz input some more.Tom Lane2025-02-07
| | | | | | | | | | | | Try to make it absolutely plain that we don't retain the originally specified time zone, only the UTC timestamp. While at it, make glossary entries for "UTC" and "GMT". Author: Robert Treat <rob@xzilla.net> Co-authored-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/173796426022.1064.9135167366862649513@wrigleys.postgresql.org Backpatch-through: 13
* Virtual generated columnsPeter Eisentraut2025-02-07
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This adds a new variant of generated columns that are computed on read (like a view, unlike the existing stored generated columns, which are computed on write, like a materialized view). The syntax for the column definition is ... GENERATED ALWAYS AS (...) VIRTUAL and VIRTUAL is also optional. VIRTUAL is the default rather than STORED to match various other SQL products. (The SQL standard makes no specification about this, but it also doesn't know about VIRTUAL or STORED.) (Also, virtual views are the default, rather than materialized views.) Virtual generated columns are stored in tuples as null values. (A very early version of this patch had the ambition to not store them at all. But so much stuff breaks or gets confused if you have tuples where a column in the middle is completely missing. This is a compromise, and it still saves space over being forced to use stored generated columns. If we ever find a way to improve this, a bit of pg_upgrade cleverness could allow for upgrades to a newer scheme.) The capabilities and restrictions of virtual generated columns are mostly the same as for stored generated columns. In some cases, this patch keeps virtual generated columns more restricted than they might technically need to be, to keep the two kinds consistent. Some of that could maybe be relaxed later after separate careful considerations. Some functionality that is currently not supported, but could possibly be added as incremental features, some easier than others: - index on or using a virtual column - hence also no unique constraints on virtual columns - extended statistics on virtual columns - foreign-key constraints on virtual columns - not-null constraints on virtual columns (check constraints are supported) - ALTER TABLE / DROP EXPRESSION - virtual column cannot have domain type - virtual columns are not supported in logical replication The tests in generated_virtual.sql have been copied over from generated_stored.sql with the keyword replaced. This way we can make sure the behavior is mostly aligned, and the differences can be visible. Some tests for currently not supported features are currently commented out. Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Tested-by: Shlok Kyal <shlok.kyal.oss@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
* Disallow COPY FREEZE on foreign tables.Nathan Bossart2025-02-06
| | | | | | | | | | | | | | This didn't actually work: the COPY succeeds, but the FREEZE optimization isn't applied. There doesn't seem to be an easy way to support FREEZE on foreign tables, so let's follow the precedent established by commit 5c9a5513a3 by raising an error early. This is arguably a bug fix, but due to the lack of reports, the minimal discussion on the mailing list, and the potential to break existing scripts, I am not back-patching it for now. Author: Sami Imseih <samimseih@gmail.com> Reviewed-by: Zhang Mingli <zmlpostgres@gmail.com> Discussion: https://postgr.es/m/CAA5RZ0ujeNgKpE3OrLtR%3DeJGa5LkGMekFzQTwjgw%3DrzaLufQLQ%40mail.gmail.com
* Introduce autovacuum_vacuum_max_threshold.Nathan Bossart2025-02-05
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | One way autovacuum chooses tables to vacuum is by comparing the number of updated or deleted tuples with a value calculated using autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The threshold specifies the base value for comparison, and the scale factor specifies the fraction of the table size to add to it. This strategy ensures that smaller tables are vacuumed after fewer updates/deletes than larger tables, which is reasonable in many cases but can result in infrequent vacuums on very large tables. This is undesirable for a couple of reasons, such as very large tables incurring a huge amount of bloat between vacuums. This new parameter provides a way to set a limit on the value calculated with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor so that very large tables are vacuumed more frequently. By default, it is set to 100,000,000 tuples, but it can be disabled by setting it to -1. It can also be adjusted for individual tables by changing storage parameters. Author: Nathan Bossart <nathandbossart@gmail.com> Co-authored-by: Frédéric Yhuel <frederic.yhuel@dalibo.com> Reviewed-by: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Michael Banck <mbanck@gmx.net> Reviewed-by: Joe Conway <mail@joeconway.com> Reviewed-by: Sami Imseih <samimseih@gmail.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com> Reviewed-by: Vinícius Abrahão <vinnix.bsd@gmail.com> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Alena Rybakina <a.rybakina@postgrespro.ru> Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
* doc: Update links which returned 404Daniel Gustafsson2025-02-05
| | | | | | | | | | | | | | | | | Two links in the isn module documentation were pointing to tools which had been moved, resulting in 404 error responses. Update to the new URLs for the tools. The link to the Sequoia 2000 page in the history section was no longer working, and since the page is no longer available online update our link to point at the paper instead which is on a stable URL. These links exist in all versions of the documentation so backpatch to all supported branches. Author: Daniel Gustafsson <daniel@yesql.se> Reported-by: charukiewicz@protonmail.com Discussion: https://postgr.es/m/173679670185.705.8565555804465055355@wrigleys.postgresql.org Backpatch-through: 13
* Avoid updating inactive_since for invalid replication slots.Amit Kapila2025-02-05
| | | | | | | | | | | | | | | | | | | | | | | It is possible for the inactive_since value of an invalid replication slot to be updated multiple times, which is unexpected behavior like during the release of the slot or at the time of restart. This is harmless because invalid slots are not allowed to be accessed but it is not prudent to update invalid slots. We are planning to invalidate slots due to other reasons like idle time and it will look odd that the slot's inactive_since displays the recent time in this field after invalidated due to idle time. So, this patch ensures that the inactive_since field of slots is not updated for invalid slots. In the passing, ensure to use the same inactive_since time for all the slots at restart while restoring them from the disk. Author: Nisha Moond <nisha.moond412@gmail.com> Author: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> Reviewed-by: Vignesh C <vignesh21@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Hou Zhijie <houzj.fnst@fujitsu.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/CABdArM7QdifQ_MHmMA=Cc4v8+MeckkwKncm2Nn6tX9wSCQ-+iw@mail.gmail.com
* Add data for WAL in pg_stat_io and backend statisticsMichael Paquier2025-02-04
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This commit adds WAL IO stats to both pg_stat_io view and per-backend IO statistics (pg_stat_get_backend_io()). This change is possible since f92c854cf406, as WAL IO is not counted in blocks in some code paths where its stats data is measured (like WAL read in xlogreader.c). IOContext gains IOCONTEXT_INIT and IOObject IOOBJECT_WAL, with the following combinations allowed: - IOOBJECT_WAL/IOCONTEXT_NORMAL is used to track I/O operations done on already-created WAL segments. - IOOBJECT_WAL/IOCONTEXT_INIT is used for tracking I/O operations done when initializing WAL segments. The core changes are done in pg_stat_io.c, backend statistics inherit them. Backend statistics and pg_stat_io are now available for the WAL writer, the WAL receiver and the WAL summarizer processes. I/O timing data is controlled by the GUC track_io_timing, like the existing data of pg_stat_io for consistency. The timings related to IOOBJECT_WAL show up if the GUC is enabled (disabled by default). Bump pgstats file version, due to the additions in IOObject and IOContext, impacting the amount of data written for the fixed-numbered IO stats kind in the pgstats file. Author: Nazir Bilal Yavuz Reviewed-by: Bertrand Drouvot, Nitin Jadhav, Amit Kapila, Michael Paquier, Melanie Plageman, Bharath Rupireddy Discussion: https://postgr.es/m/CAN55FZ3AiQ+ZMxUuXnBpd0Rrh1YhwJ5FudkHg=JU0P+-W8T4Vg@mail.gmail.com
* Convert strategies to and from compare typesPeter Eisentraut2025-02-02
| | | | | | | | | | | | | | | For each Index AM, provide a mapping between operator strategies and the system-wide generic concept of a comparison type. For example, for btree, BTLessStrategyNumber maps to and from COMPARE_LT. Numerous places in the planner and executor think directly in terms of btree strategy numbers (and a few in terms of hash strategy numbers.) These should be converted over subsequent commits to think in terms of CompareType instead. (This commit doesn't make any use of this API yet.) Author: Mark Dilger <mark.dilger@enterprisedb.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://www.postgresql.org/message-id/flat/E72EAA49-354D-4C2E-8EB9-255197F55330@enterprisedb.com
* doc: Fix pg_buffercache_evict() titleDaniel Gustafsson2025-01-31
| | | | | | | | | | | | | Use <function> rather than <structname> in the <title> to be consistent with how other functions in this module are documented. Also suffix the function name with () for consistency. Backpatch to v17 where pg_buffercache_evict was introduced. Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Discussion: https://postgr.es/m/CAExHW5uKWH8CuZc9NCb8XxSQc6uzvACV0cScebm54kF763ERAw@mail.gmail.com Backpatch-through: 17
* Doc: Generated column replication.Amit Kapila2025-01-30
| | | | | | | | | | | | Commit 7054186c4e added the support to publish generated stored columns. This patch adds detailed documentation for that feature. Author: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Vignesh C <vignesh21@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E%40gmail.com Discussion: https://postgr.es/m/CAHut+PsYmAvKhUjA1AaR1rxLdeSBKiBko8wKyf4_H8nEEqDuOg@mail.gmail.com
* Rename pubgencols_type to pubgencols in pg_publication.Amit Kapila2025-01-28
| | | | | | | | | | | The column added in commit e65dbc9927, pubgencols_type, was inconsistent with the naming conventions of other columns in the pg_publication catalog. Author: Vignesh C <vignesh21@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Discussion: https://postgr.es/m/CALDaNm1u-ufVOW-RUsXSooqzkpohxfZYy=z78fbcr_9Pq5hbCg@mail.gmail.com
* Track per-relation cumulative time spent in [auto]vacuum and [auto]analyzeMichael Paquier2025-01-28
| | | | | | | | | | | | | | | | | | | This commit adds four fields to the statistics of relations, aggregating the amount of time spent for each operation on a relation: - total_vacuum_time, for manual vacuum. - total_autovacuum_time, for vacuum done by the autovacuum daemon. - total_analyze_time, for manual analyze. - total_autoanalyze_time, for analyze done by the autovacuum daemon. This gives users the option to derive the average time spent for these operations with the help of the related "count" fields. Bump catalog version (for the catalog changes) and PGSTAT_FILE_FORMAT_ID (for the additions in PgStat_StatTabEntry). Author: Sami Imseih Reviewed-by: Bertrand Drouvot, Michael Paquier Discussion: https://postgr.es/m/CAA5RZ0uVOGBYmPEeGF2d1B_67tgNjKx_bKDuL+oUftuoz+=Y1g@mail.gmail.com
* doc: Meson is not experimental on WindowsPeter Eisentraut2025-01-27
| | | | | | | | | | | | | The installation documentation stated that using Meson is experimental. But since this is the only way to build using Visual Studio on Windows, this would imply that that whole build procedure is experimental, which isn't true. So qualify this statement a bit more. We keep the statement that Meson is experimental on other platforms, since it doesn't have full, confirmed feature parity with the make build system. Author: Aleksander Alekseev <aleksander@timescale.com> Discussion: https://www.postgresql.org/message-id/flat/a3e76618-4cb5-4d54-a71c-da4fb8ba571b@eisentraut.org
* Doc: recommend "psql -X" for restoring pg_dump scripts.Tom Lane2025-01-25
| | | | | | | | | | This practice avoids possible problems caused by non-default psql options, such as disabling AUTOCOMMIT. Author: Shinya Kato <Shinya11.Kato@oss.nttdata.com> Reviewed-by: Robert Treat <rob@xzilla.net> Discussion: https://postgr.es/m/96ff23a5d858ff72ca8e823a014d16fe@oss.nttdata.com Backpatch-through: 13
* Fix PDF doc build.Jeff Davis2025-01-25
| | | | | Reported-by: Tom Lane Discussion: https://postgr.es/m/608525.1737781222@sss.pgh.pa.us
* Add SQL function CASEFOLD().Jeff Davis2025-01-24
| | | | | | | | | | | | | Useful for caseless matching. Similar to LOWER(), but avoids edge-case problems with using LOWER() for caseless matching. For collations that support it, CASEFOLD() handles characters with more than two case variations or multi-character case variations. Some characters may fold to uppercase. The results of case folding are also more stable across Unicode versions than LOWER() or UPPER(). Discussion: https://postgr.es/m/a1886ddfcd8f60cb3e905c93009b646b4cfb74c5.camel%40j-davis.com Reviewed-by: Ian Lawrence Barwick
* pgcrypto: Make it possible to disable built-in cryptoDaniel Gustafsson2025-01-24
| | | | | | | | | | | | | | | | | When using OpenSSL and/or the underlying operating system in FIPS mode no non-FIPS certified crypto implementations should be used. While that is already possible by just not invoking the built-in crypto in pgcrypto, this adds a GUC which prohibit the code from being called. This doesn't change the FIPS status of PostgreSQL but can make it easier for sites which target FIPS compliance to ensure that violations cannot occur. Author: Daniel Gustafsson <daniel@yesql.se> Author: Joe Conway <mail@joeconway.com> Reviewed-by: Joe Conway <mail@joeconway.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Discussion: https://postgr.es/m/16b4a157-9ea1-44d0-b7b3-4c85df5de97b@joeconway.com
* pgcrypto: Add function to check FIPS modeDaniel Gustafsson2025-01-24
| | | | | | | | | | | This adds a SQL callable function for reading and returning the status of FIPS configuration of OpenSSL. If OpenSSL is operating with FIPS enabled it will return true, otherwise false. As this adds a function to the SQL file, bump the extension version to 1.4. Author: Daniel Gustafsson <daniel@yesql.se> Reviewed-by: Joe Conway <mail@joeconway.com> Discussion: https://postgr.es/m/8f979145-e206-475a-a31b-73c977a4134c@joeconway.com
* Convert sepgsql tests to TAPPeter Eisentraut2025-01-24
| | | | | | | | | | | | | | Add a TAP test for sepgsql. This automates the previously required manual setup before the test. The actual tests are still run by pg_regress, as before, but now called from within the TAP Perl script. The previous manual test script (test_sepgsql) is left in place, since its purpose is (also) to test whether a running instance was properly initialized for sepgsql. But it has been changed to call pg_regress directly and no longer require make. Reviewed-by: Andreas Karlsson <andreas@proxel.se> Discussion: https://www.postgresql.org/message-id/flat/651a5baf-5c45-4a5a-a202-0c8453a4ebf8@eisentraut.org
* Doc: Fix a typo introduced in 4a0e7314f1.Amit Kapila2025-01-24
| | | | | Author: Erik Rijkers <er@xs4all.nl> Discussion: https://postgr.es/m/6e625c81-968e-42d0-802d-edfaf9cfac11@xs4all.nl
* Doc: Fix column name in pg_publication catalog.Amit Kapila2025-01-24
| | | | | | | | | | Commit e65dbc9927 incorrectly spelled the column name in the pg_publication catalog. In passing make the order of columns in the doc match the actual catalog. Author: Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Discussion: https://postgr.es/m/DM4PR84MB1734F8F140E4477580761F93EEE02@DM4PR84MB1734.NAMPRD84.PROD.OUTLOOK.COM
* Ensure that AFTER triggers run as the instigating user.Tom Lane2025-01-23
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | With deferred triggers, it is possible that the current role changes between the time when the trigger is queued and the time it is executed (for example, the triggering data modification could have been executed in a SECURITY DEFINER function). Up to now, deferred trigger functions would run with the current role set to whatever was active at commit time. That does not matter for foreign-key constraints, whose correctness doesn't depend on the current role. But for user-written triggers, the current role certainly can matter. Hence, fix things so that AFTER triggers are fired under the role that was active when they were queued, matching the behavior of BEFORE triggers which would have actually fired at that time. (If the trigger function is marked SECURITY DEFINER, that of course overrides this, as it always has.) This does not create any new security exposure: if you do DML on a table owned by a hostile user, that user has always had various ways to exploit your permissions, such as the aforementioned BEFORE triggers, default expressions, etc. It might remove some security exposure, because the old behavior could potentially expose some other role besides the one directly modifying the table. There was discussion of making a larger change, such as running as the trigger's owner. However, that would break the common idiom of capturing the value of CURRENT_USER in a trigger for auditing/logging purposes. This change will make no difference in the typical scenario where the current role doesn't change before commit. Arguably this is a bug fix, but it seems too big a semantic change to consider for back-patching. Author: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Joseph Koshakow <koshy44@gmail.com> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://postgr.es/m/77ee784cf248e842f74588418f55c2931e47bd78.camel@cybertec.at
* Allow NOT VALID foreign key constraints on partitioned tablesÁlvaro Herrera2025-01-23
| | | | | | | | | | | | | | | | This feature was intentionally omitted when FKs were first implemented for partitioned tables, and had been requested a few times; the usefulness is clear. Validation can happen for each partition individually, which is useful to contain the number of locks held and the duration; or it can be executed for the partitioning hierarchy as a single command, which validates all child constraints that haven't been validated already. This is also useful to implement NOT ENFORCED constraints on top. Author: Amul Sul <sulamul@gmail.com> Discussion: https://postgr.es/m/CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com
* Change publication's publish_generated_columns option type to enum.Amit Kapila2025-01-23
| | | | | | | | | | | | | | | The current boolean publish_generated_columns option only supports a binary choice, which is insufficient for future enhancements where generated columns can be of different types (e.g., stored or virtual). The supported values for the publish_generated_columns option are 'none' and 'stored'. Author: Vignesh C <vignesh21@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/d718d219-dd47-4a33-bb97-56e8fc4da994@eisentraut.org Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com
* Support RN (roman-numeral format) in to_number().Tom Lane2025-01-22
| | | | | | | | | | | We've long had roman-numeral output support in to_char(), but lacked the reverse conversion. Here it is. Author: Hunaid Sohail <hunaidpgml@gmail.com> Reviewed-by: Maciek Sakrejda <m.sakrejda@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Tomas Vondra <tomas@vondra.me> Discussion: https://postgr.es/m/CAMWA6ybh4M1VQqpmnu2tfSwO+3gAPeA8YKnMHVADeB=XDEvT_A@mail.gmail.com
* Doc: Update the interaction of tablesync with wal_retrieve_retry_interval.Amit Kapila2025-01-22
| | | | | | | | | | In passing, update the documentation that explains the process of initial data replication to explicitly state that it uses a table synchronization worker. Author: Vignesh C Reviewed-by: Peter Smith, Shlok Kyal, Amit Kapila Discussion: https://postgr.es/m/CALDaNm3RxGcD4cDAV5Q0_A4n06F3+AAMpxiyND9Zn0dB86hFmg@mail.gmail.com
* Doc: simplify the tutorial's window-function examples.Tom Lane2025-01-21
| | | | | | | | | | | | | | | | | | For the purposes of this discussion, row_number() is just as good as rank(), and its behavior is easier to understand and describe. So let's switch the examples to using row_number(). Along the way to checking the results given in the tutorial, I found it helpful to extract the empsalary table we use in the regression tests, which is evidently the same data that was used to make these results. So I shoved that into advanced.source to improve the coverage of that file a little. (There's still several pages of the tutorial that are not included in it, but at least now 3.5 Window Functions is covered.) Suggested-by: "David G. Johnston" <david.g.johnston@gmail.com> Author: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/173737973383.1070.1832752929070067441@wrigleys.postgresql.org
* Support PG_UNICODE_FAST locale in the builtin collation provider.Jeff Davis2025-01-17
| | | | | | | | | | | | | | | | | | | | | | The PG_UNICODE_FAST locale uses code point sort order (fast, memcmp-based) combined with Unicode character semantics. The character semantics are based on Unicode full case mapping. Full case mapping can map a single codepoint to multiple codepoints, such as "ß" uppercasing to "SS". Additionally, it handles context-sensitive mappings like the "final sigma", and it uses titlecase mappings such as "Dž" when titlecasing (rather than plain uppercase mappings). Importantly, the uppercasing of "ß" as "SS" is specifically mentioned by the SQL standard. In Postgres, UCS_BASIC uses plain ASCII semantics for case mapping and pattern matching, so if we changed it to use the PG_UNICODE_FAST locale, it would offer better compliance with the standard. For now, though, do not change the behavior of UCS_BASIC. Discussion: https://postgr.es/m/ddfd67928818f138f51635712529bc5e1d25e4e7.camel@j-davis.com Discussion: https://postgr.es/m/27bb0e52-801d-4f73-a0a4-02cfdd4a9ada@eisentraut.org Reviewed-by: Peter Eisentraut, Daniel Verite
* Add documentation about calling version-1 C functions from C.Tom Lane2025-01-17
| | | | | | | | | This topic wasn't really covered before, so fill in some details. Author: Florents Tselai <florents.tselai@gmail.com> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/90853055-5BBD-493D-91E5-721677C7C59B@gmail.com
* Remove flex version checksPeter Eisentraut2025-01-17
| | | | | | | | | | | | | | | | | | | | | | | | | | | Remove the flex version checks from configure and meson. The cutoff versions are all so ancient that this is no longer relevant, and what the actual cutoff should be is a bit fuzzy. This also removes the ancient behavior that configure would also accept a "lex" program if it is actuall flex. This aligns the check with meson in this respect. For future reference, as of this commit, these are relevant flex versions: - The hard required minimum is flex 2.5.34 as of commit b1ef48980dd, but this has not actually been tested. - Prior to this, the minimum enforced by configure/meson was flex 2.5.35, which is the oldest present in the buildfarm right now. - As of commit 6fdd5d95634, the oldest version that will compile without warnings due to flex-generated code is flex 2.5.36. - The oldest version that probably still has some practical relevance is flex 2.5.37, which ships with CentOS/RHEL 7. Discussion: https://www.postgresql.org/message-id/1a204ccd-7ae6-478c-a431-407b5c48ccc6@eisentraut.org
* Seek zone abbreviations in the IANA data before timezone_abbreviations.Tom Lane2025-01-16
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | If a time zone abbreviation used in datetime input is defined in the currently active timezone, use that definition in preference to looking in the timezone_abbreviations list. That allows us to correctly handle abbreviations that have different meanings in different timezones. Also, it eliminates an inconsistency between datetime input and datetime output: the non-ISO datestyles for timestamptz have always printed abbreviations taken from the IANA data, not from timezone_abbreviations. Before this fix, it was possible to demonstrate cases where casting a timestamp to text and back fails or changes the value significantly because of that inconsistency. While this change removes the ability to override the IANA data about an abbreviation known in the current zone, it's not clear that there's any real use-case for doing so. But it is clear that this makes life a lot easier for dealing with abbreviations that have conflicts across different time zones. Also update the pg_timezone_abbrevs view to report abbreviations that are recognized via the IANA data, and *not* report any timezone_abbreviations entries that are thereby overridden. Under the hood, there are now two SRFs, one that pulls the IANA data and one that pulls timezone_abbreviations entries. They're combined by logic in the view. This approach was useful for debugging (since the functions can be called on their own). While I don't intend to document the functions explicitly, they might be useful to call directly. Also improve DecodeTimezoneAbbrev's caching logic so that it can cache zone abbreviations found in the IANA data. Without that, this patch would have caused a noticeable degradation of the runtime of timestamptz_in. Per report from Aleksander Alekseev and additional investigation. Discussion: https://postgr.es/m/CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com
* Add OLD/NEW support to RETURNING in DML queries.Dean Rasheed2025-01-16
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries to explicitly return old and new values by using the special aliases "old" and "new", which are automatically added to the query (if not already defined) while parsing its RETURNING list, allowing things like: RETURNING old.colname, new.colname, ... RETURNING old.*, new.* Additionally, a new syntax is supported, allowing the names "old" and "new" to be changed to user-supplied alias names, e.g.: RETURNING WITH (OLD AS o, NEW AS n) o.colname, n.colname, ... This is useful when the names "old" and "new" are already defined, such as inside trigger functions, allowing backwards compatibility to be maintained -- the interpretation of any existing queries that happen to already refer to relations called "old" or "new", or use those as aliases for other relations, is not changed. For an INSERT, old values will generally be NULL, and for a DELETE, new values will generally be NULL, but that may change for an INSERT with an ON CONFLICT ... DO UPDATE clause, or if a query rewrite rule changes the command type. Therefore, we put no restrictions on the use of old and new in any DML queries. Dean Rasheed, reviewed by Jian He and Jeff Davis. Discussion: https://postgr.es/m/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com
* Doc: Improve the Replica Identity information.Amit Kapila2025-01-16
| | | | | | | | | | | This commit improves the Replica Identity information and clarifies its related restrictions. Reported-by: James Coleman Author: Peter Smith Co-authored-by: Robert Treat Reviewed-by: Laurenz Albe, Amit Kapila Discussion: https://postgr.es/m/CAAaqYe_=7qFSqW7qavvhVy58mmzk1uSQ0RReRiUHyKO5znvr7g@mail.gmail.com
* postgres_fdw: SCRAM authentication pass-throughPeter Eisentraut2025-01-15
| | | | | | | | | | | | | | | This enables SCRAM authentication for postgres_fdw when connecting to a foreign server without having to store a plain-text password on user mapping options. This is done by saving the SCRAM ClientKey and ServeryKey from the client authentication and using those instead of the plain-text password for the server-side SCRAM exchange. The new foreign-server or user-mapping option "use_scram_passthrough" enables this. Co-authored-by: Matheus Alcantara <mths.dev@pm.me> Co-authored-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://www.postgresql.org/message-id/flat/27b29a35-9b96-46a9-bc1a-914140869dac@gmail.com
* Change gist stratnum function to use CompareTypePeter Eisentraut2025-01-15
| | | | | | | | | | | | | This changes commit 7406ab623fe in that the gist strategy number mapping support function is changed to use the CompareType enum as input, instead of the "well-known" RT*StrategyNumber strategy numbers. This is a bit cleaner, since you are not dealing with two sets of strategy numbers. Also, this will enable us to subsume this system into a more general system of using CompareType to define operator semantics across index methods. Discussion: https://www.postgresql.org/message-id/flat/E72EAA49-354D-4C2E-8EB9-255197F55330@enterprisedb.com
* psql: Add option to use expanded mode to all list commands.Dean Rasheed2025-01-14
| | | | | | | | | | | | | | | | This allows "x" to be appended to any psql list-like meta-command, forcing its output to be displayed in expanded mode. This improves readability in cases where the output is very wide. For example, "\dfx+" (or equivalently "\df+x") will produce a list of functions, with additional details, in expanded mode. This works with all \d* meta-commands, plus \l, \z, and \lo_list, with the one exception that the expanded mode option "x" cannot be appended to "\d" by itself, since "\dx" already means something else. Dean Rasheed, reviewed by Greg Sabino Mullane. Discussion: https://postgr.es/m/CAEZATCVXJk3KsmCncf7PAVbxdDAUDm3QzDgGT7mBYySWikuOYw@mail.gmail.com
* Consistently spell "leakproof" without a hyphen.Dean Rasheed2025-01-14
| | | | | | | | | The overwhelming majority of places already did this, but a small handful of places had a hyphen. Yugo Nagata. Discussion: https://postgr.es/m/CAEZATCXnnuORE2BoGwHw2zbtVvsPOLhbfVmEk9GxRzK%2Bx3OW-Q%40mail.gmail.com
* psql: Add leakproof indicator to \df+, \do+, \dAo+, and \dC+ output.Dean Rasheed2025-01-14
| | | | | | | | | | | | This allows users to determine whether particular functions are leakproof, and whether the underlying functions used by operators and casts are leakproof. This is useful to determine whether indexes can be used in queries on security barrier views or tables with row-level security policies. Yugo Nagata, reviewed by Erik Wienhold and Dean Rasheed. Discussion: https://postgr.es/m/20240701220817.483f9b645b95611f8b1f65da%40sranhm.sraoss.co.jp
* Make pg_stat_io count IOs as bytes instead of blocks for some operationsMichael Paquier2025-01-14
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Currently in pg_stat_io view, IOs are counted as blocks of size BLCKSZ. There are two limitations with this design: * The actual number of I/O requests sent to the kernel is lower because I/O requests may be merged before being sent. Additionally, it gives the impression that all I/Os are done in block size, which shadows the benefits of merging I/O requests. * Some patches are under work to extend pg_stat_io for the tracking of operations that may not be linked to the block size. For example, WAL read IOs are done in variable bytes and it is not possible to correctly show these IOs in pg_stat_io view, and we want to keep all this data in a single system view rather than spread it across multiple relations to ease monitoring. WaitReadBuffers() can now be tracked as a single read operation worth N blocks. Same for ExtendBufferedRelShared() and ExtendBufferedRelLocal() for extensions. Three columns are added to pg_stat_io for reads, writes and extensions for the byte calculations. op_bytes, which was always hardcoded to BLCKSZ, is removed. IO backend statistics are updated to reflect these changes. Bump catalog version. Author: Nazir Bilal Yavuz Reviewed-by: Bertrand Drouvot, Melanie Plageman Discussion: https://postgr.es/m/CAN55FZ0oqxBaaHAEsj=xFqkzE3n5P=3RA1V_igXwL-RV7QRzyw@mail.gmail.com
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 09:29:40 +0000