www.depesz.com Open in urlscan Pro
88.198.49.178  Public Scan

Form analysis
1 forms found in the DOM

GET https://www.depesz.com/

<form role="search" method="get" class="search-form" action="https://www.depesz.com/">
  <label>
    <span class="screen-reader-text"> Search for: </span>
    <input type="search" class="search-field" placeholder="Search …" value="" name="s">
  </label>
  <button type="submit" class="search-submit"><span class="screen-reader-text"> Search </span></button>
</form>

Text Content

Skip to content


=$

|

Menu
 * Why upgrade PG?
 * explain.D.C
 * paste.D.C
 * Waiting for …expand child menu
   * … PostgreSQL 18
   * … PostgreSQL 17
   * … PostgreSQL 16
   * … PostgreSQL 15
   * … PostgreSQL 14
   * … PostgreSQL 13
   * … PostgreSQL 12
   * … PostgreSQL 11
   * … PostgreSQL 10
   * … PostgreSQL 9.6
   * … PostgreSQL 9.5
   * … PostgreSQL 9.4
   * … PostgreSQL 9.3
   * … PostgreSQL 9.2
   * … PostgreSQL 9.1
   * … PostgreSQL 9.0
   * … PostgreSQL 8.5
   * … PostgreSQL 8.4
 * Projects
 * Contact


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")

Posted on 2024-10-112024-10-11|Tags json, jsonb, json_query, json_table, pg17,
postgresql, sql/json, waiting|Leave a comment on SQL/JSON is here! (kinda
“Waiting for Pg 17”)


WAITING FOR POSTGRESQL 18 – ADD TEMPORAL FOREIGN KEY CONTRAINTS

On 17th of September 2024, Peter Eisentraut committed patch:

Add temporal FOREIGN KEY contraints
 
Add PERIOD clause to foreign key constraint definitions.  This is
supported for range and multirange types.  Temporal foreign keys check
for range containment instead of equality.
 
This feature matches the behavior of the SQL standard temporal foreign
keys, but it works on PostgreSQL's native ranges instead of SQL's
"periods", which don't exist in PostgreSQL (yet).
 
Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
are not supported yet.
 
(previously committed as 34768ee3616, reverted by 8aee330af55; this is
essentially unchanged from those)
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints

Posted on 2024-10-032024-10-03|Tags foreign key, period, pg18, postgresql,
range, temporal, waiting|Leave a comment on Waiting for PostgreSQL 18 – Add
temporal FOREIGN KEY contraints


WAITING FOR POSTGRESQL 18 – ADD TEMPORAL PRIMARY KEY AND UNIQUE CONSTRAINTS

On 17th of September 2024, Peter Eisentraut committed patch:

Add temporal PRIMARY KEY and UNIQUE constraints
 
Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints.
These are backed by GiST indexes instead of B-tree indexes, since they
are essentially exclusion constraints with = for the scalar parts of
the key and && for the temporal part.
 
(previously committed as 46a0cd4cefb, reverted by 46a0cd4cefb; the new
part is this:)
 
Because 'empty' && 'empty' is false, the temporal PK/UQ constraint
allowed duplicates, which is confusing to users and breaks internal
expectations.  For instance, when GROUP BY checks functional
dependencies on the PK, it allows selecting other columns from the
table, but in the presence of duplicate keys you could get the value
from any of their rows.  So we need to forbid empties.
 
This all means that at the moment we can only support ranges and
multiranges for temporal PK/UQs, unlike the original patch (above).
Documentation and tests for this are added.  But this could
conceivably be extended by introducing some more general support for
the notion of "empty" for other types.
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE
constraints

Posted on 2024-09-30|Tags period, pg18, postgresql, primary key, range,
temporal, waiting|4 Comments on Waiting for PostgreSQL 18 – Add temporal PRIMARY
KEY and UNIQUE constraints


WHAT TABLES WERE TOUCHED WITHIN GIVEN RANGE OF WAL LSN?

We had a case recently where one of our DBs failed over to a new primary. To
this server (old primary, and then new primary) we had connection from some kind
of CDC tool (Debezium, I think).

The thing is that while there was failover, this tool read (using logical
decoding) changes on old primary to location 1F1F/4ADC3348, and on new db, it
connected from location 1F1F/4ADC4038. Which means that there was some WAL that
was not decoded and sent to Debezium.

This is fixable, but we need to know what tables were touched in the WAL stream,
that Debezium didn't see, if any. Perhaps we could also get ids/location of the
rows that were there?

Continue reading What tables were touched within given range of wal LSN?

Posted on 2024-07-19|Tags cdc, decoding, failover, logical, postgresql,
tutorial, wal, waldump|Leave a comment on What tables were touched within given
range of wal LSN?


HOW MUCH SPEED YOU’RE LEAVING AT THE TABLE IF YOU USE DEFAULT LOCALE?

I've been to PGConf.dev recently, and one of the talks was about collations.

The whole talk was interesting (to put it mildly), but the thing that stuck with
me is that we really shouldn't be using default collation provider (libc with
locale based collation), unless it's really needed, because we're wasting
performance. But how much of a hit is it?

Continue reading How much speed you're leaving at the table if you use default
locale?

Posted on 2024-06-112024-06-11|Tags benchmark, collation, performance, pg17,
postgresql|3 Comments on How much speed you’re leaving at the table if you use
default locale?


WAITING FOR POSTGRESQL 17 – MERGE / SPLIT PARTITIONS

I thought about it for quite some time, whether I should write about it, and
how. That's why there is delay since:

On 6th of April 2024, Alexander Korotkov committed patch:

Implement ALTER TABLE ... MERGE PARTITIONS ... command
 
This new DDL command merges several partitions into the one partition of the
target table.  The target partition is created using new
createPartitionTable() function with parent partition as the template.
 
This commit comprises quite naive implementation which works in single process
and holds the ACCESS EXCLUSIVE LOCK on the parent table during all the
operations including the tuple routing.  This is why this new DDL command
can't be recommended for large partitioned tables under a high load.  However,
this implementation come in handy in certain cases even as is.
Also, it could be used as a foundation for future implementations with lesser
locking and possibly parallel.
 
Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru
Author: Dmitry Koval
Reviewed-by: Matthias van de Meent, Laurenz Albe, Zhihong Yu, Justin Pryzby
Reviewed-by: Alvaro Herrera, Robert Haas, Stephane Tachoires

and, also on 6th of April 2024, Alexander Korotkov committed patch:

Implement ALTER TABLE ... SPLIT PARTITION ... command
 
This new DDL command splits a single partition into several parititions.
Just like ALTER TABLE ... MERGE PARTITIONS ... command, new patitions are
created using createPartitionTable() function with parent partition as the
template.
 
This commit comprises quite naive implementation which works in single process
and holds the ACCESS EXCLUSIVE LOCK on the parent table during all the
operations including the tuple routing.  This is why this new DDL command
can't be recommended for large partitioned tables under a high load.  However,
this implementation come in handy in certain cases even as is.
Also, it could be used as a foundation for future implementations with lesser
locking and possibly parallel.
 
Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru
Author: Dmitry Koval
Reviewed-by: Matthias van de Meent, Laurenz Albe, Zhihong Yu, Justin Pryzby
Reviewed-by: Alvaro Herrera, Robert Haas, Stephane Tachoires

Continue reading Waiting for PostgreSQL 17 – MERGE / SPLIT partitions

Posted on 2024-05-22|Tags merge, partitioning, partitions, pg17, postgresql,
split, waiting|2 Comments on Waiting for PostgreSQL 17 – MERGE / SPLIT
partitions


WAITING FOR …: SQL/JSON IS COMING BACK. HOPEFULLY.

This is not the usual Waiting for post, but something should be said.

Back in March/April of 2022 Andrew Dunstan committed a series of patches that
added support for lots of really interesting features from SQL/JSON standard.

While I'm not avid user of json in database, I was very, very happy. Wrote
couple of blogposts about it.

Then, around six month later they got reverted.

Lately, since last year, actually, these re-appeared again:

 1. Commit by Alvaro Herrera, from March 29th, 2023: SQL/JSON: add standard JSON
    constructor functions
 2. Commit by Alvaro Herrera, from March 31st, 2023: SQL/JSON: support the IS
    JSON predicate
 3. Commit by Amit Langote, from July 20th, 2023: Add more SQL/JSON constructor
    functions
 4. Commit by Amit Langote, from March 21st, 2024: Add SQL/JSON query functions
 5. Commit by Amit Langote, from April 4th, 2024: Add basic JSON_TABLE()
    functionality

Since they re-appeared I was asked (twice) to write about them in the Waiting
for series.

So, I just want to say that while I did notice these changes, and am very happy
that they are there, I don't plan on writing Waiting for about them.

The reason is simple: I kinda have the feeling that I already wrote about
waiting for them.

What I can say though, is that as soon as PostgreSQL version (be it 17, or any
other) will get released with these in there, I will reblog about SQL/JSON, with
updated examples, so that this huge functionality, and astounding amount of work
by developers and testers, will get as much publicity as it can.

For now: I hope it will make it to Pg 17 release, and even before that I would
like to thank everyone involved. By my quick count we have at least nine
separate authors, and fifteen reviewers, and this is just across these five
commits I mentioned.

THANK YOU – can't wait till I will be able to write about it properly

Posted on 2024-04-122024-04-18|Tags pg17, postgresql, sql/json, teaser, trailer,
waiting|4 Comments on Waiting for …: SQL/JSON is coming back. Hopefully.


WAITING FOR POSTGRESQL 17 – INVENT SERIALIZE OPTION FOR EXPLAIN.

On 3rd of April 2024, Tom Lane committed patch:

Invent SERIALIZE option for EXPLAIN.
 
EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about
the volume of data emitted by a query, as well as the time taken
to convert the data to the on-the-wire format.  Previously there
was no way to investigate this without actually sending the data
to the client, in which case network transmission costs might
swamp what you wanted to see.  In particular this feature allows
investigating the costs of de-TOASTing compressed or out-of-line
data during formatting.
 
Stepan Rutz and Matthias van de Meent,
reviewed by Tomas Vondra and myself
 
Discussion: https://postgr.es/m/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de

Continue reading Waiting for PostgreSQL 17 – Invent SERIALIZE option for
EXPLAIN.

Posted on 2024-04-11|Tags analyze, explain, pg17, postgresql, serialize, toast,
waiting|Leave a comment on Waiting for PostgreSQL 17 – Invent SERIALIZE option
for EXPLAIN.


WAITING FOR POSTGRESQL 17 – ADD NEW COPY OPTION LOG_VERBOSITY.

On 1st of April 2024, Masahiko Sawada committed patch:

Add new COPY option LOG_VERBOSITY.
 
This commit adds a new COPY option LOG_VERBOSITY, which controls the
amount of messages emitted during processing. Valid values are
'default' and 'verbose'.
 
This is currently used in COPY FROM when ON_ERROR option is set to
ignore. If 'verbose' is specified, a NOTICE message is emitted for
each discarded row, providing additional information such as line
number, column name, and the malformed value. This helps users to
identify problematic rows that failed to load.
 
Author: Bharath Rupireddy
Reviewed-by: Michael Paquier, Atsushi Torikoshi, Masahiko Sawada
Discussion: https://www.postgresql.org/message-id/CALj2ACUk700cYhx1ATRQyRw-fBM%2BaRo6auRAitKGff7XNmYfqQ%40mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add new COPY option LOG_VERBOSITY.

Posted on 2024-04-11|Tags copy, log_verbosity, on_error, pg17, postgresql,
waiting|Leave a comment on Waiting for PostgreSQL 17 – Add new COPY option
LOG_VERBOSITY.


WAITING FOR POSTGRESQL 17 – ADD SUPPORT FOR MERGE … WHEN NOT MATCHED BY SOURCE.

On 30th of March 2024, Dean Rasheed committed patch:

Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
 
This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE
actions, which operate on rows that exist in the target relation, but
not in the data source. These actions can execute UPDATE, DELETE, or
DO NOTHING sub-commands.
 
This is in contrast to already-supported WHEN NOT MATCHED actions,
which operate on rows that exist in the data source, but not in the
target relation. To make this distinction clearer, such actions may
now be written as WHEN NOT MATCHED BY TARGET.
 
Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is
equivalent to writing WHEN NOT MATCHED BY TARGET.
 
Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing.
 
Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add support for MERGE … WHEN NOT
MATCHED BY SOURCE.

Posted on 2024-04-11|Tags delete, matched, merge, not matched, pg17, postgresql,
source, waiting|Leave a comment on Waiting for PostgreSQL 17 – Add support for
MERGE … WHEN NOT MATCHED BY SOURCE.


POSTS NAVIGATION

Page 1 Page 2 … Page 162 Next page


SEARCH

Search for: Search


FOLLOW ME

 * Comments RSS
 * Posts RSS


POSTGRESQL

 * Documentation
 * Explain Analyze analyzer
 * IRC help channel
 * Mailing Lists search
 * PG Planet
 * PostgreSQL Home Page


ABOUT ME

 * CPAN
 * GitLab
 * Linked In

 * Why upgrade PG?
 * explain.D.C
 * paste.D.C
 * Waiting for …expand child menu
   * … PostgreSQL 18
   * … PostgreSQL 17
   * … PostgreSQL 16
   * … PostgreSQL 15
   * … PostgreSQL 14
   * … PostgreSQL 13
   * … PostgreSQL 12
   * … PostgreSQL 11
   * … PostgreSQL 10
   * … PostgreSQL 9.6
   * … PostgreSQL 9.5
   * … PostgreSQL 9.4
   * … PostgreSQL 9.3
   * … PostgreSQL 9.2
   * … PostgreSQL 9.1
   * … PostgreSQL 9.0
   * … PostgreSQL 8.5
   * … PostgreSQL 8.4
 * Projects
 * Contact