waiting – select * from depesz;

Waiting for PostgreSQL 18 – Add cost-based vacuum delay time to progress views.

On 11st of February 2025, Nathan Bossart committed patch:

Add cost-based vacuum delay time to progress views.
 
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

Continue reading Waiting for PostgreSQL 18 – Add cost-based vacuum delay time to progress views.

Waiting for PostgreSQL 18 – Support RN (roman-numeral format) in to_number().

On 22nd of January 2025, Tom Lane committed patch:

Support RN (roman-numeral format) in to_number().
 
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

Continue reading Waiting for PostgreSQL 18 – Support RN (roman-numeral format) in to_number().

Waiting for PostgreSQL 18 – Add OLD/NEW support to RETURNING in DML queries.

On 16th of January 2025, Dean Rasheed committed patch:

Add OLD/NEW support to RETURNING in DML queries.
 
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

Continue reading Waiting for PostgreSQL 18 – Add OLD/NEW support to RETURNING in DML queries.

Waiting for PostgreSQL 18 – Allow changing autovacuum_max_workers without restarting.

On 6th of January 2025, Nathan Bossart committed patch:

Allow changing autovacuum_max_workers without restarting.
 
This commit introduces a new parameter named
autovacuum_worker_slots that controls how many autovacuum worker
slots to reserve during server startup.  Modifying this new
parameter's value does require a server restart, but it should
typically be set to the upper bound of what you might realistically
need to set autovacuum_max_workers.  With that new parameter in
place, autovacuum_max_workers can now be changed with a SIGHUP
(e.g., pg_ctl reload).
 
If autovacuum_max_workers is set higher than
autovacuum_worker_slots, a WARNING is emitted, and the server will
only start up to autovacuum_worker_slots workers at a given time.
If autovacuum_max_workers is set to a value less than the number of
currently-running autovacuum workers, the existing workers will
continue running, but no new workers will be started until the
number of running autovacuum workers drops below
autovacuum_max_workers.
 
Reviewed-by: Sami Imseih, Justin Pryzby, Robert Haas, Andres Freund, Yogesh Sharma
Discussion: https://postgr.es/m/20240410212344.GA1824549%40nathanxps13

Continue reading Waiting for PostgreSQL 18 – Allow changing autovacuum_max_workers without restarting.

Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default

On 11st of December 2024, David Rowley committed patch:

Enable BUFFERS with EXPLAIN ANALYZE by default
The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE option
has come up a few times over the past few years. In many ways, doing this
seems like a good idea as it may be more obvious to users why a given
query is running more slowly than they might expect. Also, from my own
(David's) personal experience, I've seen users posting to the mailing
lists with two identical plans, one slow and one fast asking why their
query is sometimes slow. In many cases, this is due to additional reads.
Having BUFFERS on by default may help reduce some of these questions, and
if not, make it more obvious to the user before they post, or save a
round-trip to the mailing list when additional I/O effort is the cause of
the slowness.
The general consensus is that we want BUFFERS on by default with
ANALYZE. However, there were more than zero concerns raised with doing
so. The primary reason against is the additional verbosity, making it
harder to read large plans. Another concern was that buffer information
isn't always useful so may not make sense to have it on by default.
It's currently December, so let's commit this to see if anyone comes
forward with a strong objection against making this change. We have over
half a year remaining in the v18 cycle where we could still easily consider
reverting this if someone were to come forward with a convincing enough
reason as to why doing this is a bad idea.
There were two patches independently submitted to achieve this goal, one
by me and the other by Guillaume. This commit is a mix of both of these
patches with some additional work done by me to adjust various
additional places in the documentation which include EXPLAIN ANALYZE
output.
Author: Guillaume Lelarge, David Rowley
Reviewed-by: Robert Haas, Greg Sabino Mullane, Michael Christofides
Discussion: https://postgr.es/m/CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default

Waiting for PostgreSQL 18 – Support LIKE with nondeterministic collations

On 27th of November 2024, Peter Eisentraut committed patch:

Support LIKE with nondeterministic collations
This allows for example using LIKE with case-insensitive collations.
There was previously no internal implementation of this, so it was met
with a not-supported error. This adds the internal implementation and
removes the error. The implementation follows the specification of
the SQL standard for this.
Unlike with deterministic collations, the LIKE matching cannot go
character by character but has to go substring by substring. For
example, if we are matching against LIKE 'foo%bar', we can't start by
looking for an 'f', then an 'o', but instead with have to find
something that matches 'foo'. This is because the collation could
consider substrings of different lengths to be equal. This is all
internal to MatchText() in like_match.c.
The changes in GenericMatchText() in like.c just pass through the
locale information to MatchText(), which was previously not needed.
This matches exactly Generic_Text_IC_like() below.
ILIKE is not affected. (It's unclear whether ILIKE makes sense under
nondeterministic collations.)
This also updates match_pattern_prefix() in like_support.c to support
optimizing the case of an exact pattern with nondeterministic
collations. This was already alluded to in the previous code.
(includes documentation examples from Daniel Vérité and test cases
from Paul A Jungwirth)
Reviewed-by: Jian He <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/700d2e86-bf75-4607-9cf2-f5b7802f6e88@eisentraut.org

Continue reading Waiting for PostgreSQL 18 – Support LIKE with nondeterministic collations

Waiting for PostgreSQL 18 – psql: Add more information about service name

On 18th of December 2024, Michael Paquier committed patch:

psql: Add more information about service name
 
This commit adds support for the following items in psql, able to show a
service name, when available:
- Variable SERVICE.
- Substitution %s in PROMPT{1,2,3}.
 
This relies on 4b99fed7541e, that has made the service name available in
PGconn for libpq.
 
Author: Michael Banck
Reviewed-by: Greg Sabino Mullane
Discussion: https://postgr.es/m/6723c612.050a0220.1567f4.b94a@mx.google.com

Continue reading Waiting for PostgreSQL 18 – psql: Add more information about service name

Waiting for PostgreSQL 18 – Add UUID version 7 generation function.

On 11st of December 2024, Masahiko Sawada committed patch:

Add UUID version 7 generation function.
 
This commit introduces the uuidv7() SQL function, which generates UUID
version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp
in milliseconds and random bits, offering both uniqueness and
sortability.
 
In our implementation, the 12-bit sub-millisecond timestamp fraction
is stored immediately after the timestamp, in the space referred to as
"rand_a" in the RFC. This ensures additional monotonicity within a
millisecond. The rand_a bits also function as a counter. We select a
sub-millisecond timestamp so that it monotonically increases for
generated UUIDs within the same backend, even when the system clock
goes backward or when generating UUIDs at very high
frequency. Therefore, the monotonicity of generated UUIDs is ensured
within the same backend.
 
This commit also expands the uuid_extract_timestamp() function to
support UUID version 7.
 
Additionally, an alias uuidv4() is added for the existing
gen_random_uuid() SQL function to maintain consistency.
 
Bump catalog version.
 
Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Przemysław Sztoch, Nikolay Samokhvalov
Reviewed-by: Peter Eisentraut, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Masahiko Sawada, Lukas Fittl, Michael Paquier, Japin Li
Reviewed-by: Marcos Pegoraro, Junwang Zhao, Stepan Neretin
Reviewed-by: Daniel Vérité
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add UUID version 7 generation function.

Waiting for PostgreSQL 18 – Add SQL function array_reverse()

On 1st of November 2024, Michael Paquier committed patch:

Add SQL function array_reverse()
 
This function takes in input an array, and reverses the position of all
its elements.  This operation only affects the first dimension of the
array, like array_shuffle().
 
The implementation structure is inspired by array_shuffle(), with a
subroutine called array_reverse_n() that may come in handy in the
future, should more functions able to reverse portions of arrays be
introduced.
 
Bump catalog version.
 
Author: Aleksander Alekseev
Reviewed-by: Ashutosh Bapat, Tom Lane, Vladlen Popolitov
Discussion: https://postgr.es/m/CAJ7c6TMpeO_ke+QGOaAx9xdJuxa7r=49-anMh3G5476e3CX1CA@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add SQL function array_reverse()

SQL/JSON is here! (kinda “Waiting for Pg 17”)

Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do?

Let's see if I can shed some light on it…

Continue reading SQL/JSON is here! (kinda “Waiting for Pg 17")

=$
|

Tag: waiting