Jeff Davis [Fri, 6 Mar 2026 16:27:56 +0000 (08:27 -0800)]
CREATE SUBSCRIPTION ... SERVER.
Allow CREATE SUBSCRIPTION to accept a foreign server using the SERVER
clause instead of a raw connection string using the CONNECTION clause.
* Enables a user with sufficient privileges to create a subscription
using a foreign server by name without specifying the connection
details.
* Integrates with user mappings (and other FDW infrastructure) using
the subscription owner.
* Provides a layer of indirection to manage multiple subscriptions
to the same remote server more easily.
Also add CREATE FOREIGN DATA WRAPPER ... CONNECTION clause to specify
a connection_function. To be eligible for a subscription, the foreign
server's foreign data wrapper must specify a connection_function.
Add connection_function support to postgres_fdw, and bump postgres_fdw
version to 1.3.
Álvaro Herrera [Fri, 6 Mar 2026 15:24:58 +0000 (16:24 +0100)]
Don't include wait_event.h in pgstat.h
wait_event.h itself includes wait_event_types.h, which is a generated
file, so it's nice that we can avoid compiling >10% of the tree just
because that file is regenerated.
To avoid breaking too many third-party modules, we now #include
utils/wait_classes.h in storage/latch.h. Then, the very common case
of doing
WaitLatch(..., PG_WAIT_EXTENSION)
continues to work by including just storage/latch.h. (I didn't try to
determine how many modules would actually break if we don't do this, but
this seems a convenient and low-impact measure.)
Reviewed-by: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/202602181214.gcmhx2vhlxzp@alvherre.pgsql
Make unconstify and unvolatize use StaticAssertVariableIsOfTypeMacro
The unconstify and unvolatize macros had an almost identical assertion
as was already defined in StaticAssertVariableIsOfTypeMacro, only it
had a less useful error message and didn't have a sizeof fallback.
Use typeof everywhere instead of compiler specific spellings
We define typeof ourselves as __typeof__ if it does not exist. So
let's actually use that for consistency. The meson/autoconf checks
for __builtin_types_compatible_p still use __typeof__ though, because
there we have not redefined it.
Fujii Masao [Fri, 6 Mar 2026 07:43:40 +0000 (16:43 +0900)]
Fix publisher shutdown hang caused by logical walsender busy loop.
Previously, when logical replication was running, shutting down
the publisher could cause the logical walsender to enter a busy loop
and prevent the publisher from completing shutdown.
During shutdown, the logical walsender waits for all pending WAL
to be written out. However, some WAL records could remain unflushed,
causing the walsender to wait indefinitely.
The issue occurred because the walsender used XLogBackgroundFlush() to
flush pending WAL. This function does not guarantee that all WAL is written.
For example, WAL generated by a transaction without an assigned
transaction ID that aborts might not be flushed.
This commit fixes the bug by making the logical walsender call XLogFlush()
instead, ensuring that all pending WAL is written and preventing
the busy loop during shutdown.
Fujii Masao [Fri, 6 Mar 2026 07:02:09 +0000 (16:02 +0900)]
Improve tests for recovery_target_timeline GUC.
Commit fd7d7b71913 added regression tests to verify recovery_target_timeline
settings. To confirm that invalid values are rejected, those tests started the
server with an invalid setting and then verified that startup failed. While
functionally correct, this approach was expensive because it required
setting up and starting the server for each test case.
This commit updates the tests for recovery_target_timeline to use
the simpler approach introduced by commit bffd7130 for recovery_target_xid,
using ALTER SYSTEM SET to verify that invalid settings are rejected.
This avoids the need to set up and start the server when checking invalid
recovery_target_timeline values.
Author: David Steele <david@pgbackrest.org> Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwG44vZbSoBmg076G+xkR6n=Tj2=q+fVkfP7yEsyF1daFA@mail.gmail.com
Michael Paquier [Fri, 6 Mar 2026 05:49:00 +0000 (14:49 +0900)]
Fix inconsistency with HeapTuple freeing in extended_stats_funcs.c
heap_freetuple() is a thin wrapper doing a pfree(), and the function
import_pg_statistic(), introduced by ba97bf9cb7b4, had the idea to call
directly pfree() rather than the "dedicated" heap tuple routine.
upsert_pg_statistic_ext_data already uses heap_freetuple(). This code
is harmless as-is, but let's be consistent across the board.
Reported-by: Yonghao Lee <yonghao_lee@qq.com>
Discussion: https://postgr.es/m/tencent_CA1315EE8FB9C62F742C71E95FAD72214205@qq.com
Michael Paquier [Fri, 6 Mar 2026 05:41:41 +0000 (14:41 +0900)]
Fix order of columns in pg_stat_recovery
recovery_last_xact_time is listed before current_chunk_start_time in the
documentation, the function definition and the view definition, but
their order was reversed in the code.
Amit Kapila [Fri, 6 Mar 2026 05:21:32 +0000 (10:51 +0530)]
Fix inconsistent elevel in pg_sync_replication_slots() retry logic.
The commit 0d2d4a0ec3 allowed pg_sync_replication_slots() to retry sync
attempts, but missed a case, when WAL prior to a slot's
confirmed_flush_lsn is not yet flushed locally.
By changing the elevel from ERROR to LOG, we allow the sync loop to
continue. This provides the opportunity for the slot to be synchronized
once the standby catches up with the necessary WAL.
Author: Zhijie Hou <houzj.fnst@fujitsu.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Discussion: https://postgr.es/m/CAFPTHDZAA+gWDntpa5ucqKKba41=tXmoXqN3q4rpjO9cdxgQrw@mail.gmail.com
Michael Paquier [Fri, 6 Mar 2026 03:37:40 +0000 (12:37 +0900)]
Add system view pg_stat_recovery
This commit introduces pg_stat_recovery, that exposes at SQL level the
state of recovery as tracked by XLogRecoveryCtlData in shared memory,
maintained by the startup process. This new view includes the following
fields, that are useful for monitoring purposes on a standby, once it
has reached a consistent state (making the execution of the SQL function
possible):
- Last-successfully replayed WAL record LSN boundaries and its timeline.
- Currently replaying WAL record end LSN and its timeline.
- Current WAL chunk start time.
- Promotion trigger state.
- Timestamp of latest processed commit/abort.
- Recovery pause state.
Some of this data can already be recovered from different system
functions, but not all of it. See pg_get_wal_replay_pause_state or
pg_last_xact_replay_timestamp. This new view offers a stronger
consistency guarantee, by grabbing the recovery state for all fields
through one spinlock acquisition.
The system view relies on a new function, called pg_stat_get_recovery().
Querying this data requires the pg_read_all_stats privilege. The view
returns no rows if the node is not in recovery.
This feature originates from a suggestion I have made while discussion
the addition of a CONNECTING state to the WAL receiver's shared memory
state, because we lacked access to some of the state data. The author
has taken the time to implement it, so thanks for that.
Michael Paquier [Fri, 6 Mar 2026 02:53:23 +0000 (11:53 +0900)]
Refactor code retrieving string for RecoveryPauseState
This refactoring is going to be useful in an upcoming commit, to avoid
some code duplication with the function pg_get_wal_replay_pause_state(),
that returns a string for the recovery pause state.
Refactoring opportunity noticed while hacking on a different patch.
Tom Lane [Thu, 5 Mar 2026 22:43:09 +0000 (17:43 -0500)]
Simplify creation of built-in functions with non-default ACLs.
Up to now, to create such a function, one had to make a pg_proc.dat
entry and then modify it with GRANT/REVOKE commands, which we put in
system_functions.sql. That seems a little ugly though, because it
violates the idea of having a single source of truth about the initial
contents of pg_proc, and it results in leaving dead rows in the
initial contents of pg_proc.
This patch improves matters by allowing aclitemin to work during early
bootstrap, before pg_authid has been loaded. On the same principle
that we use for early access to pg_type details, put a table of known
built-in role names into bootstrap.c, and use that in bootstrap mode.
To create a built-in function with a non-default ACL, one should write
the desired ACL list in its pg_proc.dat entry, using a simplified
version of aclitemout's notation: omit the grantor (if it is the
bootstrap superuser, which it pretty much always should be) and spell
the bootstrap superuser's name as POSTGRES, similarly to the notation
used elsewhere in src/include/catalog. This results in entries like
proacl => '{POSTGRES=X,pg_monitor=X}'
which shows that we've revoked public execute permissions and instead
granted that to pg_monitor.
In addition to fixing up pg_proc.dat entries, I got rid of some
role grants that had been stuck into system_functions.sql,
and instead put them into a new file pg_auth_members.dat;
that seems like a far less random place to put the information.
The correctness of the data changes can be verified by comparing the
initial contents of pg_proc and pg_auth_members before and after.
pg_proc should match exactly, but the OID column of pg_auth_members
will probably be different because those OIDs now get assigned a
little earlier in bootstrap. (I forced a catversion bump out of
caution, but it wasn't really necessary.)
Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/183292bb-4891-4c96-a3ca-e78b5e0e1358@dunslane.net
Tom Lane [Thu, 5 Mar 2026 22:22:31 +0000 (17:22 -0500)]
Be more wary of false matches in initdb's replace_token().
Do not replace the target string unless the occurrence is surrounded
by whitespace or line start/end. This avoids potential false match
to a substring of a field. While we've not had trouble with that
up to now, the next patch creates hazards of false matches to
POSTGRES within an ACL field.
There is one call site that needs adjustment, as it was presuming
it could write "::1" and have that match "::1/128". For all the
others, this restriction is okay and strictly safer.
Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/183292bb-4891-4c96-a3ca-e78b5e0e1358@dunslane.net
The PruneState had members called "all_visible" and "all_frozen" which
reflect not the current state of the page but the state it could be in
once pruning and freezing have been executed. These are then saved in
the PruneFreezeResult so the caller can set the VM accordingly.
Prefix the PruneState members as well as the corresponsding
PruneFreezeResult members with "set_" to clarify that they represent the
proposed state of the all-visible and all-frozen bits for a heap page in
the visibility map, not the current state.
Author: Melanie Plageman <melanieplageman@gmail.com> Suggested-by: Andres Freund <andres@anarazel.de> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/bqc4kh5midfn44gnjiqez3bjqv4zogydguvdn446riw45jcf3y%404ez66il7ebvk
Move commonly used context into PruneState and simplify helpers
heap_page_prune_and_freeze() and many of its helpers use the heap
buffer, block number, and page. Other helpers took the heap page and
didn't use it. Initializing these values once during
prune_freeze_setup() simplifies the helpers' interfaces and avoids any
repeated calls to BufferGetBlockNumber() and BufferGetPage().
While updating PruneState, also reorganize its fields to make the layout
and member documentation more consistent.
Author: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/BD8B69E7-26D8-4706-9164-597C6AE57812%40gmail.com
Tom Lane [Thu, 5 Mar 2026 19:43:21 +0000 (14:43 -0500)]
Exit after fatal errors in client-side compression code.
It looks like whoever wrote the astreamer (nee bbstreamer) code
thought that pg_log_error() is equivalent to elog(ERROR), but
it's not; it just prints a message. So all these places tried to
continue on after a compression or decompression error return,
with the inevitable result being garbage output and possibly
cascading error messages. We should use pg_fatal() instead.
These error conditions are probably pretty unlikely in practice,
which no doubt accounts for the lack of field complaints.
Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/1531718.1772644615@sss.pgh.pa.us
Backpatch-through: 15
Jacob Champion [Thu, 5 Mar 2026 18:04:53 +0000 (10:04 -0800)]
oauth: Add TLS support for oauth_validator tests
The oauth_validator tests don't currently support HTTPS, which makes
testing PGOAUTHCAFILE difficult. Add a localhost certificate to
src/test/ssl and make use of it in oauth_server.py.
In passing, explain the hardcoded use of IPv4 in our issuer identifier,
after intermittent failures on NetBSD led to commit 8d9d5843b. (The new
certificate is still set up for IPv6, to make it easier to improve that
behavior in the future.)
Patch by Jonathan Gonzalez V., with some additional tests and tweaks by
me.
Jacob Champion [Thu, 5 Mar 2026 18:04:48 +0000 (10:04 -0800)]
libpq: Add PQgetThreadLock() to mirror PQregisterThreadLock()
Allow libpq clients to retrieve the current pg_g_threadlock pointer with
PQgetThreadLock(). Single-threaded applications could already do this in
a convoluted way:
pgthreadlock_t tlock;
tlock = PQregisterThreadLock(NULL);
PQregisterThreadLock(tlock); /* re-register the callback */
/* use tlock */
But a generic library can't do that without potentially breaking
concurrent libpq connections.
The motivation for doing this now is the libpq-oauth plugin, which
currently relies on direct injection of pg_g_threadlock, and should
ideally not.
Jacob Champion [Thu, 5 Mar 2026 18:04:36 +0000 (10:04 -0800)]
oauth: Report cleanup errors as warnings on stderr
Using conn->errorMessage for these "shouldn't-happen" cases will only
work if the connection itself fails. Our SSL and password callbacks
print WARNINGs when they find themselves in similar situations, so
follow their lead.
Fix handling of updated tuples in the MERGE statement
This branch missed the IsolationUsesXactSnapshot() check. That led to EPQ on
repeatable read and serializable isolation levels. This commit fixes the
issue and provides a simple isolation check for that. Backpatch through v15
where MERGE statement was introduced.
Reported-by: Tender Wang <tndrwang@gmail.com>
Discussion: https://postgr.es/m/CAPpHfdvzZSaNYdj5ac-tYRi6MuuZnYHiUkZ3D-AoY-ny8v%2BS%2Bw%40mail.gmail.com
Author: Tender Wang <tndrwang@gmail.com> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Backpatch-through: 15
Fujii Masao [Thu, 5 Mar 2026 12:40:32 +0000 (21:40 +0900)]
Improve validation of recovery_target_xid GUC values.
Previously, the recovery_target_xid GUC values were not sufficiently validated.
As a result, clearly invalid inputs such as the string "bogus", a decimal value
like "1.1", or 0 (a transaction ID smaller than the minimum valid value of 3)
were unexpectedly accepted. In these cases, the value was interpreted as
transaction ID 0, which could cause recovery to behave unexpectedly.
This commit improves validation of recovery_target_xid GUC so that invalid
values are rejected with an error. This prevents recovery from proceeding
with misconfigured recovery_target_xid settings.
Also this commit updates the documentation to clarify the allowed values
for recovery_target_xid GUC.
Author: David Steele <david@pgbackrest.org> Reviewed-by: Hüseyin Demir <huseyin.d3r@gmail.com> Reviewed-by: Fujii Masao <masao.fujii@gmail.com> Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/f14463ab-990b-4ae9-a177-998d2677aae0@pgbackrest.org
Fujii Masao [Thu, 5 Mar 2026 03:55:52 +0000 (12:55 +0900)]
doc: Clarify that COLUMN is optional in ALTER TABLE ... ADD/DROP COLUMN.
In ALTER TABLE ... ADD/DROP COLUMN, the COLUMN keyword is optional. However,
part of the documentation could be read as if COLUMN were required, which may
mislead users about the command syntax.
This commit updates the ALTER TABLE documentation to clearly state that
COLUMN is optional for ADD and DROP.
Also this commit adds regression tests covering ALTER TABLE ... ADD/DROP
without the COLUMN keyword.
Backpatch to all supported versions.
Author: Chao Li <lic@highgo.com> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/CAEoWx2n6ShLMOnjOtf63TjjgGbgiTVT5OMsSOFmbjGb6Xue1Bw@mail.gmail.com
Backpatch-through: 14
Michael Paquier [Thu, 5 Mar 2026 03:17:47 +0000 (12:17 +0900)]
Move definition of XLogRecoveryCtlData to xlogrecovery.h
XLogRecoveryCtlData is the structure that stores the shared-memory state
of WAL recovery, including information such as promotion requests, the
timeline ID (TLI), and the LSNs of replayed records.
This refactoring is independently useful because it allows code outside
of core to access the recovery state in live. It will be used by an
upcoming patch that introduces a SQL function for querying this
information, that can be accessed on a standby once a consistent state
has been reached. This only moves code around, changing nothing
functionally.
Michael Paquier [Thu, 5 Mar 2026 01:05:44 +0000 (10:05 +0900)]
Fix rare instability in recovery TAP test 004_timeline_switch
This fixes a problem similar to ad8c86d22cbd. In this case, the test
could fail under the following circumstances:
- The primary is stopped with teardown_node(), meaning that it may not
be able to send all its WAL records to standby_1 and standby_2.
- If standby_2 receives more records than standby_1, attempting to
reconnect standby_2 to the promoted standby_1 would fail because of a
timeline fork.
This race condition is fixed with a simple trick: instead of tearing
down the primary, it is stopped cleanly so as all the WAL records of the
primary are received and flushed by both standby_1 and standby_2. Once
we do that, there is no need for a wait_for_catchup() before stopping
the node. The test wants to check that a timeline jump can be achieved
when reconnecting a standby to a promoted standby in the same cluster,
hence an immediate stop of the primary is not required.
This failure is harder to reach than the previous instability of
009_twophase, still the buildfarm has been able to detect this failure
at least once. I have tried Alexander Lakhin's test trick with the
bgwriter and very aggressive standby snapshots, but I could not
reproduce it directly. It is reachable, as the buildfarm has proved.
Backpatch down to all supported branches, and this problem can lead to
spurious failures in the buildfarm.
Michael Paquier [Thu, 5 Mar 2026 00:24:35 +0000 (09:24 +0900)]
Change default value of default_toast_compression to "lz4", take two
The default value for default_toast_compression was "pglz". The main
reason for this choice is that this option is always available, pglz
code being embedded in Postgres. However, it is known that LZ4 is more
efficient than pglz: less CPU required, more compression on average. As
of this commit, the default value of default_toast_compression becomes
"lz4", if available. By switching to LZ4 as the default, users should
see natural speedups on TOAST data reads and/or writes.
Support for LZ4 in TOAST compression was added in Postgres v14, or 5
releases ago. This should be long enough to consider this feature as
stable.
While at it, quotes are removed from default_toast_compression in
postgresql.conf.sample. Quotes are not required in this case. The
in-place value replacement done by initdb if the build supports LZ4
would not use them in the postgresql.conf file added to a
freshly-initialized cluster.
Note that this is a version lighter than 7c1849311e49, that included a
replacement of --with-lz4 by --without-lz4 in configure builds, forcing
a requirement for LZ4 in all environments. The buildfarm did not like
it, at all. This commit switches default_toast_compression to lz4 as
default only when --with-lz4 is defined, which should keep the buildfarm
at bay while still allowing users to benefit from LZ4 compression in
TOAST as long as the code is compiled with it.
Author: Euler Taveira <euler@eulerto.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Aleksander Alekseev <aleksander@tigerdata.com>
Discussion: https://posgr.es/m/435df33a-129e-4f0c-a803-f3935c5a5ecb@eisentraut.org
Michael Paquier [Wed, 4 Mar 2026 23:25:35 +0000 (08:25 +0900)]
Revert "Change default value of default_toast_compression to "lz4""
This reverts commit 7c1849311e49, due to the fact that more than 60% of
the buildfarm members do not have lz4 installed. As we are in the last
commit fest of the development cycle, and that it could take a couple
of weeks to stabilize things, this change is reverted for now.
This commit will be reworked in a lighter version, as
default_toast_compression's default can be changed to "lz4" without the
switch from --with-lz4 to --without-lz4. This approach will keep the
buildfarm at bay, and still allow builds to take advantage of LZ4 in
TOAST by default, as long as the code is compiled with LZ4 support.
A harder requirement based on LZ4 should be achievable at some point,
but it is going to require some work from the buildfarm owners first.
Perhaps this part could be revisited at the beginning of the next
development cycle.
Andrew Dunstan [Fri, 27 Feb 2026 13:07:10 +0000 (08:07 -0500)]
pg_restore: add --no-globals option to skip globals
This is a followup to commit 763aaa06f03 Add non-text output formats to
pg_dumpall.
Add a --no-globals option to pg_restore that skips restoring global
objects (roles and tablespaces) when restoring from a pg_dumpall
archive. When -C/--create is not specified, databases that do not
already exist on the target server are also skipped.
This is useful when restoring only specific databases from a pg_dumpall
archive without needing the global objects to be restored first.
Tom Lane [Wed, 4 Mar 2026 20:33:15 +0000 (15:33 -0500)]
Fix estimate_hash_bucket_stats's correction for skewed data.
The previous idea was "scale up the bucketsize estimate by the ratio
of the MCV's frequency to the average value's frequency". But we
should have been suspicious of that plan, since it frequently led to
impossible (> 1) values which we had to apply an ad-hoc clamp to.
Joel Jacobson demonstrated that it sometimes leads to making the
wrong choice about which side of the hash join should be inner.
Instead, drop the whole business of estimating average frequency, and
just clamp the bucketsize estimate to be at least the MCV's frequency.
This corresponds to the bucket size we'd get if only the MCV appears
in a bucket, and the MCV's frequency is not affected by the
WHERE-clause filters. (We were already making the latter assumption.)
This also matches the coding used since 4867d7f62 in the case where
only a default ndistinct estimate is available.
Interestingly, this change affects no existing regression test cases.
Add one to demonstrate that it helps pick the smaller table to be
hashed when the MCV is common enough to affect the results.
This leaves estimate_hash_bucket_stats not considering the effects of
null join keys at all, which we should probably improve. However,
I have a different patch in the queue that will change the executor's
handling of null join keys, so it seems appropriate to wait till
that's in before doing anything more here.
Reported-by: Joel Jacobson <joel@compiler.org>
Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Joel Jacobson <joel@compiler.org>
Discussion: https://postgr.es/m/341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com
Tom Lane [Wed, 4 Mar 2026 17:08:37 +0000 (12:08 -0500)]
Fix yet another bug in archive streamer with LZ4 decompression.
The code path in astreamer_lz4_decompressor_content() that updated
the output pointers when the output buffer isn't full was wrong.
It advanced next_out by bytes_written, which could include previous
decompression output not just that of the current cycle. The
correct amount to advance is out_size. While at it, make the
output pointer updates look more like the input pointer updates.
This bug is pretty hard to reach, as it requires consecutive
compression frames that are too small to fill the output buffer.
pg_dump could have produced such data before 66ec01dc4, but
I'm unsure whether any files we use astreamer with would be
likely to contain problematic data.
Author: Chao Li <lic@highgo.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/0594CC79-1544-45DD-8AA4-26270DE777A7@gmail.com
Backpatch-through: 15
Amit Kapila [Wed, 4 Mar 2026 10:25:01 +0000 (15:55 +0530)]
Allow table exclusions in publications via EXCEPT TABLE.
Extend CREATE PUBLICATION ... FOR ALL TABLES to support the EXCEPT TABLE
syntax. This allows one or more tables to be excluded. The publisher will
not send the data of excluded tables to the subscriber.
To support this, pg_publication_rel now includes a prexcept column to flag
excluded relations. For partitioned tables, the exclusion is applied at
the root level; specifying a root table excludes all current and future
partitions in that tree.
Follow-up work will implement ALTER PUBLICATION support for managing these
exclusions.
Author: vignesh C <vignesh21@gmail.com>
Author: Shlok Kyal <shlok.kyal.oss@gmail.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Zhijie Hou <houzj.fnst@fujitsu.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Reviewed-by: Ashutosh Sharma <ashu.coek88@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://postgr.es/m/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
Add test for row-locking and multixids with prepared transactions
This is a repro for the issue fixed in commit ccae90abdb. Backpatch to
v17 like that commit, although that's a little arbitrary as this test
would work on older versions too.
Author: Sami Imseih <samimseih@gmail.com>
Discussion: https://www.postgresql.org/message-id/CAA5RZ0twq5bNMq0r0QNoopQnAEv+J3qJNCrLs7HVqTEntBhJ=g@mail.gmail.com
Backpatch-through: 17
Michael Paquier [Wed, 4 Mar 2026 07:30:51 +0000 (16:30 +0900)]
Fix rare instability in recovery TAP test 009_twophase
The phase of the test where we want to check that 2PC transactions
prepared on a primary can be committed on a promoted standby relied on
an immediate stop of the primary. This logic has a race condition: it
could be possible that some records (most likely standby snapshot
records) are generated on the primary before it finishes its shutdown,
without the promoted standby know about them. When the primary is
recycled as new standby, the test could fail because of a timeline fork
as an effect of these extra records.
This fix takes care of the instability by doing a clean stop of the
primary instead of a teardown (aka immediate stop), so as all records
generated on the primary are sent to the promoted standby and flushed
there. There is no need for a teardown of the primary in this test
scenario: the commit of 2PC transactions on a promoted standby do not
care about the state of the primary, only of the standby.
This race is very hard to hit in practice, even slow buildfarm members
like skink have a very low rate of reproduction. Alexander Lakhin has
come up with a recipe to improve the reproduction rate a lot:
- Enable -DWAL_DEBUG.
- Patch the bgwriter so as standby snapshots are generated every
milliseconds.
- Run 009_twophase tests under heavy parallelism.
With this method, the failure appears after a couple of iterations.
With the fix in place, I have been able to run more than 50 iterations
of the parallel test sequence, without seeing a failure.
Issue introduced in 30820982b295, due to a copy-pasto coming from the
surrounding tests. Thanks also to Hayato Kuroda for digging into the
details of the failure. He has proposed a fix different than the one of
this commit. Unfortunately, it relied on injection points, feature only
available in v17. The solution of this commit is simpler, and can be
applied to v14~v16.
Reported-by: Alexander Lakhin <exclusion@gmail.com>
Discussion: https://postgr.es/m/b0102688-6d6c-c86a-db79-e0e91d245b1a@gmail.com
Backpatch-through: 14
Michael Paquier [Wed, 4 Mar 2026 04:05:31 +0000 (13:05 +0900)]
Change default value of default_toast_compression to "lz4", when available
The default value for default_toast_compression was "pglz". The main
reason for this choice is that this option is always available, pglz
code being embedded in Postgres. However, it is known that LZ4 is more
efficient than pglz: less CPU required, more compression on average. As
of this commit, the default value of default_toast_compression becomes
"lz4", if available. By switching to LZ4 as the default, users should
see natural speedups on TOAST data reads and/or writes.
Support for LZ4 in TOAST compression was added in Postgres v14, or 5
releases ago. This should be long enough to consider this feature as
stable.
--with-lz4 is removed, replaced by a --without-lz4 to disable LZ4 in the
builds on an option-basis, following a practice similar to readline or
ICU. References to --with-lz4 are removed from the documentation.
While at it, quotes are removed from default_toast_compression in
postgresql.conf.sample. Quotes are not required in this case. The
in-place value replacement done by initdb if the build supports LZ4
would not use them in the postgresql.conf file added to a
freshly-initialized cluster.
For the reference, a similar switch has been done with ICU in fcb21b3acdcb. Some of the changes done in this commit are consistent
with that.
Note: this is going to create some disturbance in the buildfarm, in
environments where lz4 is not installed.
Author: Euler Taveira <euler@eulerto.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Aleksander Alekseev <aleksander@tigerdata.com>
Discussion: https://posgr.es/m/435df33a-129e-4f0c-a803-f3935c5a5ecb@eisentraut.org
Richard Guo [Wed, 4 Mar 2026 01:57:43 +0000 (10:57 +0900)]
Remove redundant restriction checks in apply_child_basequals
In apply_child_basequals, after translating a parent relation's
restriction quals for a child relation, we simplify each child qual by
calling eval_const_expressions. Historically, the code then called
restriction_is_always_false and restriction_is_always_true to reduce
NullTest quals that are provably false or true.
However, since commit e2debb643, the planner natively performs
NullTest deduction during constant folding. Therefore, calling
restriction_is_always_false and restriction_is_always_true immediately
afterward is redundant and wastes CPU cycles. We can safely remove
them and simply rely on the constant folding to handle the deduction.
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAMbWs4-vLmGXaUEZyOMacN0BVfqWCt2tM-eDVWdDfJnOQaauGg@mail.gmail.com
Richard Guo [Wed, 4 Mar 2026 01:56:06 +0000 (10:56 +0900)]
Remove obsolete SAMESIGN macro
The SAMESIGN macro was historically used as a helper for manual
integer overflow checks. However, since commit 4d6ad3125 introduced
overflow-aware integer operations, this manual sign-checking logic is
no longer necessary.
The macro remains defined in brin_minmax_multi.c and timestamp.c, but
is not used in either file. This patch removes these definitions to
clean things up.
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAMbWs4-NL3J3hQ3LzrwV-YUkQC18P+jM7ZiegQyAHzgdZev2qg@mail.gmail.com
Michael Paquier [Wed, 4 Mar 2026 00:55:58 +0000 (09:55 +0900)]
Add some tests for CREATE OR REPLACE VIEW with column additions
When working on an already-defined view with matching attributes, CREATE
OR REPLACE VIEW would internally generate an ALTER TABLE command with a
set of AT_AddColumnToView sub-commands, one for each attribute added.
Such a command is stored in event triggers twice:
- Once as a simple command.
- Once as an ALTER TABLE command, as it has sub-commands.
There was no test coverage to track this command pattern in terms of
event triggers and DDL deparsing:
- For the test module test_ddl_deparse, two command notices are issued.
- For event triggers, a CREATE VIEW command is logged twice, which may
look a bit weird first, but again this maps with the internal behavior
of how the commands are built, and how the event trigger code reacts in
terms of commands gathered.
While on it, this adds a test for CREATE SCHEMA with a CREATE VIEW
command embedded in it, case supported by the grammar but not covered
yet.
This hole in the test coverage has been found while digging into what
would be a similar behavior for sequences if adding attributes to them
with ALTER TABLE variants, after the initial relation creation.
Read stream users can now pause lookahead when no blocks are currently
available. After resuming, subsequent read_stream_next_buffer() calls
continue lookahead with the previous lookahead distance.
This is especially useful for read stream users with self-referential
access patterns (where consuming already-read buffers can produce
additional block numbers).
Author: Thomas Munro <thomas.munro@gmail.com> Reviewed-by: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Nazir Bilal Yavuz <byavuz81@gmail.com>
Discussion: https://postgr.es/m/CA%2BhUKGJLT2JvWLEiBXMbkSSc5so_Y7%3DN%2BS2ce7npjLw8QL3d5w%40mail.gmail.com
doc: Add restart on failure to example systemd file
The documentation previously had a systemd unit file that would not
attempt to recover from process failures such as OOM's, segfaults,
etc. This commit adds "Restart=on-failure",` which tells systemd to
attempt to restart the process after failure. This is the recommended
configuration per the systemd documentation: "Setting this to
on-failure is the recommended choice for long-running services". Many
PostgreSQL users will simply copy/paste what the PostgreSQL
documentation recommends and will probably do their own research and
change the service file to restart on failure, so might as well set
this as the default in the PostgreSQL documentation.
Author: Andrew Jackson <andrewjackson947@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAKK5BkFfMpAQnv8CLs%3Di%3DrZwurtCV_gmfRb0uZi-V%2Bd6wcryqg%40mail.gmail.com
Álvaro Herrera [Tue, 3 Mar 2026 10:19:23 +0000 (11:19 +0100)]
Reduce scope of for-loop-local variables to avoid shadowing
Adjust a couple of for-loops where a local variable was shadowed by
another in the same scope, by renaming it as well as reducing its scope
to the containing for-loop.
Author: Chao Li <lic@highgo.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CAEoWx2kQ2x5gMaj8tHLJ3=jfC+p5YXHkJyHrDTiQw2nn2FJTmQ@mail.gmail.com
Commit c66a7d75e652 introduced a new "cast discards ‘volatile’"
warning (-Wcast-qual) in vac_truncate_clog().
Instead of making use of unvolatize(), remove the warning by reducing the
scope of the volatile qualifier (added in commit 2d2e40e3bef) to only
2 fields.
Also do the same for vac_update_datfrozenxid(), since the intent of
commit f65ab862e3b was to prevent the same kind of race condition that
commit 2d2e40e3bef was fixing.
If ON_ERROR SET_NULL is specified during COPY FROM, any data type
conversion errors will result in the affected column being set to a
null value. A column's not-null constraints are still enforced, and
attempting to set a null value in such columns will raise a constraint
violation error. This applies to a column whose data type is a domain
with a NOT NULL constraint.
Author: Jian He <jian.universality@gmail.com>
Author: Kirill Reshke <reshkekirill@gmail.com> Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com> Reviewed-by: Jim Jones <jim.jones@uni-muenster.de> Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com> Reviewed-by: Yugo NAGATA <nagata@sraoss.co.jp> Reviewed-by: torikoshia <torikoshia@oss.nttdata.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Atsushi Torikoshi <torikoshia@oss.nttdata.com> Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/CAKFQuwawy1e6YR4S%3Dj%2By7pXqg_Dw1WBVrgvf%3DBP3d1_aSfe_%2BQ%40mail.gmail.com
Michael Paquier [Tue, 3 Mar 2026 06:27:50 +0000 (15:27 +0900)]
doc: Fix sentence of pg_walsummary page
Author: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Reviewed-by: Robert Treat <rob@xzilla.net>
Discussion: https://postgr.es/m/CAHut+PvfYBL-ppX-i8DPeRu7cakYCZz+QYBhrmQzicx7z_Tj5w@mail.gmail.com
Backpatch-through: 17
Michael Paquier [Tue, 3 Mar 2026 05:19:54 +0000 (14:19 +0900)]
Add support for "exprs" in pg_restore_extended_stats()
This commit adds support for the restore of extended statistics of the
kind "exprs", counting for the statistics data computed for expressions.
The input format consists of a jsonb object which must be an array of
objects which are keyed by statistics parameter names, like this:
[{"stat_type1": "...", "stat_type2": "...", ...},
{"stat_type1": "...", "stat_type2": "...", ...}, ...]
The outer array must have as many elements as there are expressions
defined in the statistics object, mapping with the way extended
statistics are built with one pg_statistic tuple stored for each
expression whose statistics have been computed. The elements of the
array must be either objects or null values (equivalent of invalid data,
case also supported by the stats computations when its data is inserted
in the catalogs).
The keys of the inner objects are names of the statistical columns in
pg_stats_ext_exprs (i.e. everything after "inherited"). Not all
parameter keys need to be provided, those omitted being silently
ignored. Key values that do not match a statistical column name will
cause a warning to be issued, but do not otherwise fail the expression
or the import as a whole.
The expected value type for all parameters is jbvString, which allows
us to validate the values using the input function specific to that
parameter. Any parameters with a null value are silently ignored, same
as if they were not provided in the first place.
This commit includes a battery of test cases:
- Sanity checks for what-should-be-all the failures in restore code
paths, including parsing errors, parameter sanity checks depending on
the extended stats object definition, etc.
- Value injection, for scalar, array, range, multi-range cases.
- Stats data cloning, with differential checks between the source
relation and its target. The source and the target should hold the same
stats data after restore.
- While expressions are supported in extended statistics since v14,
range_length_histogram, range_empty_frac, and range_bounds_histogram
have been added to pg_stat_ext_exprs only in v19. A test case has been
added to emulate a dump taken from v18, with expression stats restored
for a range data type where these three fields are NULL.
Support for pg_dump is included, with expressions supported since v14,
inherited since v15, and data for range types in expressions in v19.
pg_upgrade is the main use-case of this feature; it is also possible to
inject statistics, same as for the other extstat kinds.
As of this commit, ANALYZE should not be required after pg_upgrade when
the cluster upgrading from uses extended statistics, as MCV,
dependencies, expressions and ndistinct stats are all covered. The
stats data related to range types used in expressions requires v19,
whose support has also been added.
Author: Corey Huinker <corey.huinker@gmail.com> Co-authored-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CADkLM=fPcci6oPyuyEZ0F4bWqAA7HzaWO+ZPptufuX5_uWt6kw@mail.gmail.com
Tom Lane [Mon, 2 Mar 2026 19:40:29 +0000 (14:40 -0500)]
Fix local-variable shadowing in pg_trgm's printSourceNFA().
We hadn't noticed this violation of -Wshadow=compatible-local
because this function isn't compiled without -DTRGM_REGEXP_DEBUG.
As long as we have to clean it up, let's do so by converting all
this function's loops to use C99 loop-local control variables.
Reported-by: Sergei Kornilov <sk@zsrv.org>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/3009911772478436@08341ecb-668d-43a9-af4d-b45f00c72521
Nathan Bossart [Mon, 2 Mar 2026 19:12:25 +0000 (13:12 -0600)]
basic_archive: Allow archive directory to be missing at startup.
Presently, the GUC check hook for basic_archive.archive_directory
checks that the specified directory exists. Consequently, if the
directory does not exist at server startup, archiving will be stuck
indefinitely, even if it appears later. To fix, remove this check
from the hook so that archiving will resume automatically once the
directory is present. basic_archive must already be prepared to
deal with the directory disappearing at any time, so no additional
special handling is required.
Reported-by: Олег Самойлов <splarv@ya.ru> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Fujii Masao <masao.fujii@gmail.com> Reviewed-by: Sergei Kornilov <sk@zsrv.org>
Discussion: https://postgr.es/m/73271769675212%40mail.yandex.ru
Backpatch-through: 15
Fix OldestMemberMXactId and OldestVisibleMXactId array usage
Commit ab355e3a88 changed how the OldestMemberMXactId array is
indexed. It's no longer indexed by synthetic dummyBackendId, but with
ProcNumber. The PGPROC entries for prepared xacts come after auxiliary
processes in the allProcs array, which rendered the calculation for
MaxOldestSlot and the indexes into the array incorrect. (The
OldestVisibleMXactId array is not used for prepared xacts, and thus
never accessed with ProcNumber's greater than MaxBackends, so this
only affects the OldestMemberMXactId array.)
As a result, a prepared xact would store its value past the end of the
OldestMemberMXactId array, overflowing into the OldestVisibleMXactId
array. That could cause a transaction's row lock to appear invisible
to other backends, or other such visibility issues. With a very small
max_connections setting, the store could even go beyond the
OldestVisibleMXactId array, stomping over the first element in the
BufferDescriptor array.
To fix, calculate the array sizes more precisely, and introduce helper
functions to calculate the array indexes correctly.
Author: Yura Sokolov <y.sokolov@postgrespro.ru> Reviewed-by: Sami Imseih <samimseih@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://www.postgresql.org/message-id/7acc94b0-ea82-4657-b1b0-77842cb7a60c@postgrespro.ru
Backpatch-through: 17
Tom Lane [Mon, 2 Mar 2026 16:14:58 +0000 (11:14 -0500)]
In pg_dumpall, don't skip role GRANTs with dangling grantor OIDs.
In commits 29d75b25b et al, I made pg_dumpall's dumpRoleMembership
logic treat a dangling grantor OID the same as dangling role and
member OIDs: print a warning and skip emitting the GRANT. This wasn't
terribly well thought out; instead, we should handle the case by
emitting the GRANT without the GRANTED BY clause. When the source
database is pre-v16, such cases are somewhat expected because those
versions didn't prevent dropping the grantor role; so don't even
print a warning that we did this. (This change therefore restores
pg_dumpall's pre-v16 behavior for these cases.) The case is not
expected in >= v16, so then we do print a warning, but soldiering on
with no GRANTED BY clause still seems like a reasonable strategy.
Per complaint from Robert Haas that we were now dropping GRANTs
altogether in easily-reachable scenarios.
Reported-by: Robert Haas <robertmhaas@gmail.com>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CA+TgmoauoiW4ydDhdrseg+DD4Kwha=+TSZp18BrJeHKx3o1Fdw@mail.gmail.com
Backpatch-through: 16
Save prune cycles by consistently clearing prune hints on all-visible pages
All-visible pages can't contain prunable tuples. We already clear the
prune hint (pd_prune_xid) during pruning of all-visible pages, but we
were not doing so in vacuum phase three, nor initializing it for
all-frozen pages created by COPY FREEZE, and we were not clearing it on
standbys.
Because page hints are not WAL-logged, pages on a standby carry stale
pd_prune_xid values. After promotion, that stale hint triggers
unnecessary on-access pruning.
Fix this by clearing the prune hint everywhere we currently mark a heap
page all-visible. Clearing it when setting PD_ALL_VISIBLE ensures no
extra overhead.
Author: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/flat/CAAKRu_b-BMOyu0X-0jc_8bWNSbQ5K6JTEueayEhcQuw-OkCSKg%40mail.gmail.com
Calling copyObject in C++ without GNU extensions (e.g. when using
-std=c++11 instead of -std=gnu++11) fails with an error like this:
error: use of undeclared identifier 'typeof'; did you mean 'typeid'
This is due to the C compiler used to compile PostgreSQL supporting
typeof, but that function actually not being present in the C++
compiler. This fixes that by explicitely checking for typeof support
in C++, and then either use that or define typeof ourselves as:
std::remove_reference_t<decltype(x)>
According to the paper that led to adding typeof to the C standard,
that's the C++ equivalent of the C typeof:
https://www.open-std.org/jtc1/sc22/wg14/www/docs/n2927.htm#existing-decltype
Michael Paquier [Mon, 2 Mar 2026 05:13:05 +0000 (14:13 +0900)]
Remove WAL page header flag XLP_BKP_REMOVABLE
There are no known users of this flag. The last supposed user was
pglesslog, which is the reason why this flag has been introduced in
core, based on an historical search pointing at a8d539f12498.
I have mentioned that we may want to remove this flag back in 2018, due
to zero users of it in core. More recently, Noah has pointed out that
this flag is not safe to use: XLP_BKP_REMOVABLE can be set by the WAL
writer in a lock-free fashion with runningBackups > 0, meaning that some
full-page images could be required but not logged, ultimately corrupting
backups.
Bump XLOG_PAGE_MAGIC.
Author: Matthias van de Meent <boekewurm+postgres@gmail.com>
Discussion: https://postgr.es/m/20250705001628.c3.nmisch@google.com
Discussion: https://postgr.es/m/CAEze2WhiwKSoAvfUggjDeoeY0-rz9cTpfrHcqvBMmJxv-K_5DA@mail.gmail.com
Michael Paquier [Mon, 2 Mar 2026 04:14:15 +0000 (13:14 +0900)]
Fix memory allocation size in RegisterExtensionExplainOption()
The allocations used for the static array ExplainExtensionOptionArray,
that tracks a set of ExplainExtensionOption, used "char *" instead of
ExplainExtensionOption as the memory size consumed by one element,
underestimating the memory required by half.
The initial allocation of ExplainExtensionNameArray wants to hold 16
elements before being reallocated, and with "char *" it meant that there
was enough space only for 8 ExplainExtensionOption elements, 16 bytes
required for each element. The backend would crash once one tries to
register a 9th EXPLAIN option.
As far as I can see, the allocation formulas of GetExplainExtensionId()
have been copy-pasted to RegisterExtensionExplainOption(), but the
internal maths of the copy were not adjusted accordingly.
Michael Paquier [Mon, 2 Mar 2026 02:10:31 +0000 (11:10 +0900)]
test_custom_types: Test module with fancy custom data types
This commit adds a new test module called "test_custom_types", that can
be used to stress code paths related to custom data type
implementations.
Currently, this is used as a test suite to validate the set of fixes
done in 3b7a6fa15720, that requires some typanalyze callbacks that can
force very specific backend behaviors, as of:
- typanalyze callback that returns "false" as status, to mark a failure
in computing statistics.
- typanalyze callback that returns "true" but let's the backend know
that no interesting stats could be computed, with stats_valid set to
"false".
This could be extended more in the future if more problems are found.
For simplicity, the module uses a fake int4 data type, that requires a
btree operator class to be usable with extended statistics. The type is
created by the extension, and its properties are altered in the test.
Like 3b7a6fa15720, this module is backpatched down to v14, for coverage
purposes.
Author: Michael Paquier <michael@paquier.xyz> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/aaDrJsE1I5mrE-QF@paquier.xyz
Backpatch-through: 14
Fujii Masao [Mon, 2 Mar 2026 02:07:42 +0000 (11:07 +0900)]
psql: Add tab completion for DELETE ... USING.
This implements the tab completion that was marked as XXX TODO in the
source code. The following completion is now supported:
DELETE FROM <table> USING <TAB> -> list of relations supporting SELECT
This uses Query_for_list_of_selectables (instead of Query_for_list_of_tables)
because the USING clause can reference not only tables but also views and
other selectable objects, following the same syntax as the FROM clause
of a SELECT statement.
Michael Paquier [Mon, 2 Mar 2026 00:38:37 +0000 (09:38 +0900)]
Fix set of issues with extended statistics on expressions
This commit addresses two defects regarding extended statistics on
expressions:
- When building extended statistics in lookup_var_attr_stats(), the call
to examine_attribute() did not account for the possibility of a NULL
return value. This can happen depending on the behavior of a typanalyze
callback — for example, if the callback returns false, if no rows are
sampled, or if no statistics are computed. In such cases, the code
attempted to build MCV, dependency, and ndistinct statistics using a
NULL pointer, incorrectly assuming valid statistics were available,
which could lead to a server crash.
- When loading extended statistics for expressions,
statext_expressions_load() did not account for NULL entries in the
pg_statistic array storing expression statistics. Such NULL entries can
be generated when statistics collection fails for an expression, as may
occur during the final step of serialize_expr_stats(). An extended
statistics object defining N expressions requires N corresponding
elements in the pg_statistic array stored for the expressions, and some
of these elements can be NULL. This situation is reachable when a
typanalyze callback returns true, but sets stats_valid to indicate that
no useful statistics could be computed.
While these scenarios cannot occur with in-core typanalyze callbacks, as
far as I have analyzed, they can be triggered by custom data types with
custom typanalyze implementations, at least.
No tests are added in this commit. A follow-up commit will introduce a
test module that can be extended to cover similar edge cases if
additional issues are discovered. This takes care of the core of the
problem.
Attribute and relation statistics already offer similar protections:
- ANALYZE detects and skips the build of invalid statistics.
- Invalid catalog data is handled defensively when loading statistics.
This issue exists since the support for extended statistics on
expressions has been added, down to v14 as of a4d75c86bf15. Backpatch
to all supported stable branches.
Author: Michael Paquier <michael@paquier.xyz> Reviewed-by: Corey Huinker <corey.huinker@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/aaDrJsE1I5mrE-QF@paquier.xyz
Backpatch-through: 14
Tom Lane [Sun, 1 Mar 2026 17:56:55 +0000 (12:56 -0500)]
Correctly calculate "MCV frequency" for a unique column.
In commit bd3e3e9e5, I over-hastily used 1 / rel->rows as the assumed
frequency of entries in a column that ANALYZE has found to be unique.
However, rel->rows is the number of table rows that are estimated to
pass the query's restriction conditions, so that we got a too-large
result if the query has selective restrictions. What I should have
used is 1 / rel->tuples, since that is the estimated total number of
table rows. The pre-existing code path that digs a frequency out of
the histogram produces a frequency relative to the whole table, so
surely this new alternative code path must do so as well. Any
correction needed on the basis of selectivity must be done by the
user of the mcv_freq value.
Fixing this causes all the regression test plans changed by bd3e3e9e5
to revert to what they had been, except for the first change in
join.out. As I correctly argued in bd3e3e9e5, in that test case we
have no stats and should not risk a hash join. Evidently I was less
correct to argue that the other changes were improvements.
Reported-by: Joel Jacobson <joel@compiler.org> Diagnosed-by: Tender Wang <tndrwang@gmail.com>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com
Fujii Masao [Sat, 28 Feb 2026 14:56:46 +0000 (23:56 +0900)]
psql: Show comments in \dRp+, \dRs+, and \dX+ psql meta-commands.
Previously, the psql meta-commands that list publications, subscriptions,
and extended statistics did not display their associated comments,
whereas other \d meta-commands did. This made it inconvenient for users
to view these objects together with their descriptions.
This commit improves \dRp+ and \dRs+ to include comments for publications
and subscriptions. It also extends the \dX meta-command to accept the + option,
allowing comments for extended statistics to be shown when requested.
Author: Fujii Masao <masao.fujii@gmail.com> Co-authored-by: Jim Jones <jim.jones@uni-muenster.de> Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwGL4JqiKA26fnGx-cTM=VzoTs_uzqejvj4Fawyr4uLUUw@mail.gmail.com
Tom Lane [Fri, 27 Feb 2026 20:20:16 +0000 (15:20 -0500)]
Doc: improve user docs and code comments about EXISTS(SELECT * ...).
Point out that Postgres automatically optimizes away the target list
of an EXISTS' subquery, except in weird cases such as target lists
containing set-returning functions. Thus, both common conventions
EXISTS(SELECT * FROM ...) and EXISTS(SELECT 1 FROM ...) are
overhead-free and there's little reason to prefer one over the other.
In the code comments, mention that the SQL spec says that
EXISTS(SELECT * FROM ...) should be interpreted as EXISTS(SELECT
some-literal FROM ...), but we don't choose to do it exactly that way.
Author: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/9b301c70-3909-4f0f-98ca-9e3c4d142f3e@eisentraut.org
Tom Lane [Fri, 27 Feb 2026 17:54:02 +0000 (12:54 -0500)]
Don't flatten join alias Vars that are stored within a GROUP RTE.
The RTE's groupexprs list is used for deparsing views, and for that
usage it must contain the original alias Vars; else we can get
incorrect SQL output. But since commit 247dea89f,
parseCheckAggregates put the GROUP BY expressions through
flatten_join_alias_vars before building the RTE_GROUP RTE.
Changing the order of operations there is enough to fix it.
This patch unfortunately can do nothing for already-created views:
if they use a coding pattern that is subject to the bug, they will
deparse incorrectly and hence present a dump/reload hazard in the
future. The only fix is to recreate the view from the original SQL.
But the trouble cases seem to be quite narrow. AFAICT the output
was only wrong for "SELECT ... t1 LEFT JOIN t2 USING (x) GROUP BY x"
where t1.x and t2.x were not of identical data types and t1.x was
the side that required an implicit coercion. If there was no hidden
coercion, or if the join was plain, RIGHT, or FULL, the deparsed
output was uglier than intended but not functionally wrong.
Reported-by: Swirl Smog Dowry <swirl-smog-dowry@duck.com>
Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com
Backpatch-through: 18
John Naylor [Fri, 27 Feb 2026 13:30:41 +0000 (20:30 +0700)]
Centralize detection of x86 CPU features
We now maintain an array of booleans that indicate which features were
detected at runtime. When code wants to check for a given feature,
the array is automatically checked if it has been initialized and if
not, a single function checks all features at once.
Move all x86 feature detection to pg_cpu_x86.c, and move the CRC
function choosing logic to the file where the hardware-specific
functions are defined, consistent with more recent hardware-specific
files in src/port.
Andrew Dunstan [Fri, 27 Feb 2026 12:14:06 +0000 (07:14 -0500)]
Clean up nodes that are no longer of use in 007_pgdumpall.pl
Oversight in commit 763aaa06f03. When nodes are going out of scope, we
should stop the underlying postmasters rather than waiting for the
script to end.
Michael Paquier [Fri, 27 Feb 2026 09:59:41 +0000 (18:59 +0900)]
Use pg_malloc_object() and pg_alloc_array() variants in frontend code
This commit updates the frontend tools (src/bin/, contrib/ and
src/test/) to use the memory allocation variants based on
pg_malloc_object() and pg_malloc_array() in various code paths. This
does not cover all the allocations, but a good chunk of them.
Like all the changes of this kind (31d3847a37be, etc.), this should
encourage any future code to use this new style.
Author: Andreas Karlsson <andreas@proxel.se>
Discussion: https://postgr.es/m/cfb645da-6b3a-4f22-9bcc-5bc46b0e9c61@proxel.se
heapam_scan_analyze_next_tuple() doesn't distinguish between dead and
recently dead tuples when counting them, so it doesn't need OldestXmin.
GetOldestNonRemovableTransactionId() isn't free, so removing it is a
win.
Looking at other table AMs implementing table_scan_analyze_next_tuple(),
we couldn't find one using OldestXmin either, so remove it from the
callback.
Melanie Plageman [Thu, 26 Feb 2026 20:33:36 +0000 (15:33 -0500)]
Simplify visibility check in heap_page_would_be_all_visible()
heap_page_would_be_all_visible() does not need to distinguish between
HEAPTUPLE_RECENTLY_DEAD and HEAPTUPLE_DEAD tuples: any tuple in a state
other than HEAPTUPLE_LIVE means the page is not all-visible and
heap_page_would_be_all_visible() returns false.
Given that, calling HeapTupleSatisfiesVacuum() is unnecessary, since it
performs extra work to distinguish between dead and recently dead tuples
using OldestXmin. Replace it with the more minimal
HeapTupleSatisfiesVacuumHorizon().
Author: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CALdSSPjvhGXihT_9f-GJabYU%3D_PjrFDUxYaURuTbfLyQM6TErg%40mail.gmail.com
Jeff Davis [Thu, 26 Feb 2026 20:23:22 +0000 (12:23 -0800)]
Fix more multibyte issues in ltree.
Commit 84d5efa7e3 missed some multibyte issues caused by short-circuit
logic in the callers. The callers assumed that if the predicate string
is longer than the label string, then it couldn't possibly be a match,
but it can be when using case-insensitive matching (LVAR_INCASE) if
casefolding changes the byte length.
Fix by refactoring to get rid of the short-circuit logic as well as
the function pointer, and consolidate the logic in a replacement
function ltree_label_match().
Melanie Plageman [Thu, 26 Feb 2026 20:04:49 +0000 (15:04 -0500)]
Rename LVRelState VM-related logging counters
The LVRelState fields that track newly all-visible/all-frozen pages were
previously named vm_new_visible_pages, vm_new_frozen_pages, and
vm_new_visible_frozen_pages. The correct terminology is all-visible and
all-frozen; omitting “all” was open to misinterpretation, as the page
isn't visible or invisible, rather all the tuples on the page are
visible to all running and future transactions. Rename the members
accordingly.
Author: Melanie Plageman <melanieplageman@gmail.com> Suggested-by: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/bqc4kh5midfn44gnjiqez3bjqv4zogydguvdn446riw45jcf3y%404ez66il7ebvk
Andres Freund [Thu, 26 Feb 2026 15:19:23 +0000 (10:19 -0500)]
instrumentation: Rename INSTR_TIME_LT macro to INSTR_TIME_GT
This was incorrectly named "LT" for "larger than" in e5a5e0a90750d66, but
that is against existing conventions, where "LT" means "less than".
Clarify by using "GT" for "greater than" in macro name, and add a missing
comment at the top of instr_time.h to note the macro's existence.
Reported by: Peter Smith <smithpb2250@gmail.com>
Author: Lukas Fittl <lukas@fittl.com> Reviewed-by: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CAHut%2BPut94CTpjQsqOJHdHkgJ2ZXq%2BqVSfMEcmDKLiWLW-hPfA%40mail.gmail.com
Andrew Dunstan [Mon, 23 Feb 2026 20:08:55 +0000 (15:08 -0500)]
Add non-text output formats to pg_dumpall
pg_dumpall can now produce output in custom, directory, or tar formats
in addition to plain text SQL scripts. When using non-text formats,
pg_dumpall creates a directory containing:
- toc.glo: global data (roles and tablespaces) in custom format
- map.dat: mapping between database OIDs and names
- databases/: subdirectory with per-database archives named by OID
pg_restore is extended to handle these pg_dumpall archives, restoring
globals and then each database. The --globals-only option can be used
to restore only the global objects.
This enables parallel restore of pg_dumpall output and selective
restoration of individual databases from a cluster-wide backup.
Noah Misch [Thu, 26 Feb 2026 02:13:22 +0000 (18:13 -0800)]
EUC_CN, EUC_JP, EUC_KR, EUC_TW: Skip U+00A0 tests instead of failing.
Settings that ran the new test euc_kr.sql to completion would fail these
older src/pl tests. Use alternative expected outputs, for which psql
\gset and \if have reduced the maintenance burden. This fixes
"LANG=ko_KR.euckr LC_MESSAGES=C make check-world". (LC_MESSAGES=C fixes
IO::Pty usage in tests 010_tab_completion and 001_password.) That file
is new in commit c67bef3f3252a3a38bf347f9f119944176a796ce. Back-patch
to v14, like that commit.
Fujii Masao [Thu, 26 Feb 2026 00:01:52 +0000 (09:01 +0900)]
doc: Clarify INCLUDING COMMENTS behavior in CREATE TABLE LIKE.
The documentation for the INCLUDING COMMENTS option of the LIKE clause
in CREATE TABLE was inaccurate and incomplete. It stated that comments for
copied columns, constraints, and indexes are copied, but regarding comments
on constraints in reality only comments on CHECK and NOT NULL constraints
are copied; comments on other constraints (such as primary keys) are not.
In addition, comments on extended statistics are copied, but this was not
documented.
The CREATE FOREIGN TABLE documentation had a similar omission: comments
on extended statistics are also copied, but this was not mentioned.
This commit updates the documentation to clarify the actual behavior.
The CREATE TABLE reference now specifies that comments on copied columns,
CHECK constraints, NOT NULL constraints, indexes, and extended statistics are
copied. The CREATE FOREIGN TABLE reference now notes that comments on
extended statistics are copied as well.
Backpatch to all supported versions. Documentation updates related to
CREATE FOREIGN TABLE LIKE and NOT NULL constraint comment copying are
not applied to v17 and earlier, since those features were introduced in v18.
Fujii Masao [Wed, 25 Feb 2026 23:46:12 +0000 (08:46 +0900)]
Fix ProcWakeup() resetting wrong waitStart field.
Previously, when one process woke another that was waiting on a lock,
ProcWakeup() incorrectly cleared its own waitStart field (i.e.,
MyProc->waitStart) instead of that of the process being awakened.
As a result, the awakened process retained a stale lock-wait start timestamp.
This did not cause user-visible issues. pg_locks.waitstart was reported as
NULL for the awakened process (i.e., when pg_locks.granted is true),
regardless of the waitStart value.
Tom Lane [Wed, 25 Feb 2026 15:51:42 +0000 (10:51 -0500)]
Stabilize output of new isolation test insert-conflict-do-update-4.
The test added by commit 4b760a181 assumed that a table's physical
row order would be predictable after an UPDATE. But a non-heap table
AM might produce some other order. Even with heap AM, the assumption
seems risky; compare a3fd53bab for instance. Adding an ORDER BY is
cheap insurance and doesn't break any goal of the test.
Author: Pavel Borisov <pashkin.elfe@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CALT9ZEHcE6tpvumScYPO6pGk_ASjTjWojLkodHnk33dvRPHXVw@mail.gmail.com
Backpatch-through: 14
Richard Guo [Wed, 25 Feb 2026 02:13:21 +0000 (11:13 +0900)]
Fix unsafe RTE_GROUP removal in simplify_EXISTS_query
When simplify_EXISTS_query removes the GROUP BY clauses from an EXISTS
subquery, it previously deleted the RTE_GROUP RTE directly from the
subquery's range table.
This approach is dangerous because deleting an RTE from the middle of
the rtable list shifts the index of any subsequent RTE, which can
silently corrupt any Var nodes in the query tree that reference those
later relations. (Currently, this direct removal has not caused
problems because the RTE_GROUP RTE happens to always be the last entry
in the rtable list. However, relying on that is extremely fragile and
seems like trouble waiting to happen.)
Instead of deleting the RTE_GROUP RTE, this patch converts it in-place
to be RTE_RESULT type and clears its groupexprs list. This preserves
the length and indexing of the rtable list, ensuring all Var
references remain intact.
Reported-by: Tom Lane <tgl@sss.pgh.pa.us>
Author: Richard Guo <guofenglinux@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/3472344.1771858107@sss.pgh.pa.us
Backpatch-through: 18
John Naylor [Wed, 25 Feb 2026 01:44:59 +0000 (08:44 +0700)]
Fix USE_SLICING_BY_8_CRC32C builds on x86
A future commit will move the CRC function choosing logic to the
file where the hardware-specific functions are defined, but until
then add guards for builds without those functions. Oversight in
commit b9278871f.
Per buildfarm animal rhinoceros
Reported-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/4014992.1771963187@sss.pgh.pa.us
Jacob Champion [Tue, 24 Feb 2026 22:01:37 +0000 (14:01 -0800)]
pg_upgrade: Use max_protocol_version=3.0 for older servers
The grease patch in 4966bd3ed found its first problem: prior to the
February 2018 patch releases, no server knew how to negotiate protocol
versions, so pg_upgrade needs to take that into account when speaking to
those older servers.
This will be true even after the grease feature is reverted; we don't
need anyone to trip over this again in the future. Backpatch so that all
supported versions of pg_upgrade can gracefully handle an update to the
default protocol version. (This is needed for any distributions that
link older binaries against newer libpqs, such as Debian.) Branches
prior to 18 need an additional version check, for the existence of
max_protocol_version.
Per buildfarm member crake.
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAOYmi%2B%3D4QhCjssfNEoZVK8LPtWxnfkwT5p-PAeoxtG9gpNjqOQ%40mail.gmail.com
Backpatch-through: 14
Álvaro Herrera [Tue, 24 Feb 2026 16:34:56 +0000 (17:34 +0100)]
Add backtrace support for Windows using DbgHelp API
Previously, backtrace generation on Windows would return an "unsupported"
message. With this commit, we rely on CaptureStackBackTrace() to capture
the call stack and the DbgHelp API (SymFromAddrW, SymGetLineFromAddrW64)
for symbol resolution.
Symbol handler initialization (SymInitialize) is performed once per
process and cached. If initialization fails, the report for it is
returned as the backtrace output. The symbol handler is cleaned up via
on_proc_exit() to release DbgHelp resources.
The implementation provides symbol names, offsets, and addresses. When
PDB files are available, it also includes source file names and line
numbers. Symbol names and file paths are converted from UTF-16 to the
database encoding using wchar2char(), which properly handles both UTF-8
and non-UTF-8 databases on Windows. When symbol information is
unavailable or encoding conversion fails, it falls back to displaying raw
addresses.
The implementation uses the explicit UTF16 versions of the DbgHelp
functions (SYMBOL_INFOW, SymFromAddrW, IMAGEHLP_LINEW64,
SymGetLineFromAddrW64) rather than the generic versions. This allows us
to rely on predictable encoding conversion, rather than using the
haphazard ANSI codepage that we'd get otherwise.
DbgHelp is apparently available on all Windows platforms we support, so
there are no version number checks.
Author: Bryan Green <dbryan.green@gmail.com> Reviewed-by: Euler Taveira <euler@eulerto.com> Reviewed-by: Jakub Wartak <jakub.wartak@enterprisedb.com> Reviewed-by: Greg Burd <greg@burd.me>
Discussion: https://postgr.es/m/a692c0fe-caca-4c08-9c5d-debfd0ef2504@gmail.com
Peter Eisentraut [Tue, 24 Feb 2026 09:56:17 +0000 (10:56 +0100)]
Make ALTER DOMAIN VALIDATE CONSTRAINT no-op when constraint is already validated
Currently, AlterDomainValidateConstraint will re-validate a constraint
that has already been validated, which would just waste cycles. This
operation should be a no-op when the constraint is already validated.
This also aligns with ATExecValidateConstraint.
Author: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG=-Dv9fPJHqkA9c-wGZ2dDOWOXSp-X-0K_G7r-DgaASw@mail.gmail.com
Peter Eisentraut [Tue, 24 Feb 2026 09:30:50 +0000 (10:30 +0100)]
Allow ALTER COLUMN SET EXPRESSION on virtual columns with CHECK constraints
Previously, changing the generation expression of a virtual column was
prohibited if the column was referenced by a CHECK constraint. This
lifts that restriction.
RememberAllDependentForRebuilding within ATExecSetExpression will
rebuild all the dependent constraints, later ATPostAlterTypeCleanup
queues the required AlterTableStmt operations for ALTER TABLE Phase 3
execution.
Overall, ALTER COLUMN SET EXPRESSION on virtual columns may require
scanning the table to re-verify any associated CHECK constraints, but
it does not require a table rewrite in ALTER TABLE Phase 3.
Author: jian he <jian.universality@gmail.com> Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com>
Discussion: https://postgr.es/m/CACJufxH3VETr7orF5rW29GnDk3n1wWbOE3WdkHYd3iPGrQ9E_A@mail.gmail.com
projects / thirdparty / postgresql.git / log
