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


WAITING FOR POSTGRESQL 17 – ADD RETURNING SUPPORT TO MERGE.

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

Add RETURNING support to MERGE.
 
This allows a RETURNING clause to be appended to a MERGE query, to
return values based on each row inserted, updated, or deleted. As with
plain INSERT, UPDATE, and DELETE commands, the returned values are
based on the new contents of the target table for INSERT and UPDATE
actions, and on its old contents for DELETE actions. Values from the
source relation may also be returned.
 
As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be
used as the source relation for other operations such as WITH queries
and COPY commands.
 
Additionally, a special function merge_action() is provided, which
returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action
executed for each row. The merge_action() function can be used
anywhere in the RETURNING list, including in arbitrary expressions and
subqueries, but it is an error to use it anywhere outside of a MERGE
query's RETURNING list.
 
Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera,
Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut,
and Wolfgang Walther.
 
Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add RETURNING support to MERGE.

Posted on 2024-03-19|Tags merge, merge_action, pg17, postgresql, returning,
waiting|Leave a comment on Waiting for PostgreSQL 17 – Add RETURNING support to
MERGE.


WHAT THE HELL IS TRANSACTION WRAPAROUND?

Recently someone asked on Slack about what is transaction wraparound. Full
answer is a bit too much for slack reply, but I can try to explain it in here.

Continue reading What the hell is transaction wraparound?

Posted on 2024-03-18|Tags freeze, mvcc, postgresql, slack, tutorial, vacuum,
wraparound|5 Comments on What the hell is transaction wraparound?


WAITING FOR POSTGRESQL 17 – ADD NEW COPY OPTION SAVE_ERROR_TO / RENAME COPY
OPTION FROM SAVE_ERROR_TO TO ON_ERROR

On 16th of January 2024, Alexander Korotkov committed patch:

Add new COPY option SAVE_ERROR_TO
 
Currently, when source data contains unexpected data regarding data type or
range, the entire COPY fails. However, in some cases, such data can be ignored
and just copying normal data is preferable.
 
This commit adds a new option SAVE_ERROR_TO, which specifies where to save the
error information. When this option is specified, COPY skips soft errors and
continues copying.
 
Currently, SAVE_ERROR_TO only supports "none". This indicates error information
is not saved and COPY just skips the unexpected data and continues running.
 
Later works are expected to add more choices, such as 'log' and 'table'.
 
Author: Damir Belyalov, Atsushi Torikoshi, Alex Shulgin, Jian He
Discussion: https://postgr.es/m/87k31ftoe0.fsf_-_%40commandprompt.com
Reviewed-by: Pavel Stehule, Andres Freund, Tom Lane, Daniel Gustafsson,
Reviewed-by: Alena Rybakina, Andy Fan, Andrei Lepikhov, Masahiko Sawada
Reviewed-by: Vignesh C, Atsushi Torikoshi

and then, three days later, he changed the syntax in next patch:

Rename COPY option from SAVE_ERROR_TO to ON_ERROR
 
The option names now are "stop" (default) and "ignore".  The future options
could be "file 'filename.log'" and "table 'tablename'".
 
Discussion: https://postgr.es/m/20240117.164859.2242646601795501168.horikyota.ntt%40gmail.com
Author: Jian He
Reviewed-by: Atsushi Torikoshi

Continue reading Waiting for PostgreSQL 17 – Add new COPY option SAVE_ERROR_TO /
Rename COPY option from SAVE_ERROR_TO to ON_ERROR

Posted on 2024-02-072024-02-07|Tags copy, error, ignore, on_error, pg17,
postgresql, save_error_to, waiting|3 Comments on Waiting for PostgreSQL 17 – Add
new COPY option SAVE_ERROR_TO / Rename COPY option from SAVE_ERROR_TO to
ON_ERROR


WAITING FOR POSTGRESQL 17 – SUPPORT IDENTITY COLUMNS IN PARTITIONED TABLES

On 16th of January 2024, Peter Eisentraut committed patch:

Support identity columns in partitioned tables
 
Previously, identity columns were disallowed on partitioned tables.
(The reason was mainly that no one had gotten around to working
through all the details to make it work.)  This makes it work now.
 
Some details on the behavior:
 
* A newly created partition inherits identity property
 
  The partitions of a partitioned table are integral part of the
  partitioned table.  A partition inherits identity columns from the
  partitioned table.  An identity column of a partition shares the
  identity space with the corresponding column of the partitioned
  table.  In other words, the same identity column across all
  partitions of a partitioned table share the same identity space.
  This is effected by sharing the same underlying sequence.
 
  When INSERTing directly into a partition, the sequence associated
  with the topmost partitioned table is used to calculate the value of
  the corresponding identity column.
 
  In regular inheritance, identity columns and their properties in a
  child table are independent of those in its parent tables.  A child
  table does not inherit identity columns or their properties
  automatically from the parent.  (This is unchanged.)
 
* Attached partition inherits identity column
 
  A table being attached as a partition inherits the identity property
  from the partitioned table.  This should be fine since we expect
  that the partition table's column has the same type as the
  partitioned table's corresponding column.  If the table being
  attached is a partitioned table, the identity properties are
  propagated down its partition hierarchy.
 
  An identity column in the partitioned table is also marked as NOT
  NULL.  The corresponding column in the partition needs to be marked
  as NOT NULL for the attach to succeed.
 
* Drop identity property when detaching partition
 
  A partition's identity column shares the identity space
  (i.e. underlying sequence) as the corresponding column of the
  partitioned table.  If a partition is detached it can longer share
  the identity space as before.  Hence the identity columns of the
  partition being detached loose their identity property.
 
  When identity of a column of a regular table is dropped it retains
  the NOT NULL constraint that came with the identity property.
  Similarly the columns of the partition being detached retain the NOT
  NULL constraints that came with identity property, even though the
  identity property itself is lost.
 
  The sequence associated with the identity property is linked to the
  partitioned table (and not the partition being detached).  That
  sequence is not dropped as part of detach operation.
 
* Partitions with their own identity columns are not allowed.
 
* The usual ALTER operations (add identity column, add identity
  property to existing column, alter properties of an indentity
  column, drop identity property) are supported for partitioned
  tables.  Changing a column only in a partitioned table or a
  partition is not allowed; the change needs to be applied to the
  whole partition hierarchy.
 
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/CAExHW5uOykuTC+C6R1yDSp=o8Q83jr8xJdZxgPkxfZ1Ue5RRGg@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Support identity columns in
partitioned tables

Posted on 2024-02-072024-02-07|Tags generated, identity, partitioning,
partitions, pg17, postgresql, waiting|Leave a comment on Waiting for PostgreSQL
17 – Support identity columns in partitioned tables


WAITING FOR POSTGRESQL 17 – IN PLPGSQL, ALLOW %TYPE AND %ROWTYPE TO BE FOLLOWED
BY ARRAY DECORATION.

On 4th of January 2024, Tom Lane committed patch:

In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.
 
This provides the useful ability to declare a variable that is an array
of the type of some other variable or some table column.
 
Quan Zongliang, Pavel Stehule
 
Discussion: https://postgr.es/m/ec4523e1-9e7e-f3ef-f9ce-bafd680ad6f6@yeah.net

Continue reading Waiting for PostgreSQL 17 – In plpgsql, allow %TYPE and
%ROWTYPE to be followed by array decoration.

Posted on 2024-01-22|Tags array, arrays, declaration, pg17, plpgsql, postgresql,
variable, waiting|Leave a comment on Waiting for PostgreSQL 17 – In plpgsql,
allow %TYPE and %ROWTYPE to be followed by array decoration.


WAITING FOR POSTGRESQL 17 – ALTER TABLE COMMAND TO CHANGE GENERATION EXPRESSION

On 4th of January 2024, Peter Eisentraut committed patch:

ALTER TABLE command to change generation expression
 
This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION
that changes the generation expression of a generated column.
 
The syntax is not standard but was adapted from other SQL
implementations.
 
This command causes a table rewrite, using the usual ALTER TABLE
mechanisms.  The implementation is similar to and makes use of some of
the infrastructure of the SET DATA TYPE subcommand (for example,
rebuilding constraints and indexes afterwards).  The new command
requires a new pass in AlterTablePass, and the ADD COLUMN pass had to
be moved earlier so that combinations of ADD COLUMN and SET EXPRESSION
can work.
 
Author: Amul Sul <sulamul@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b94yyJeGA-5M951_Lr+KfZokOp-2kXicpmEhi5FXhBeTog@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – ALTER TABLE command to change
generation expression

Posted on 2024-01-15|Tags alter, expression, generated, pg17, postgresql,
waiting|1 Comment on Waiting for PostgreSQL 17 – ALTER TABLE command to change
generation expression


WAITING FOR POSTGRESQL 17 – ADD SUPPORT FOR INCREMENTAL BACKUP.

On 20th of December 2023, Robert Haas committed patch:

Add support for incremental backup.
 
To take an incremental backup, you use the new replication command
UPLOAD_MANIFEST to upload the manifest for the prior backup. This
prior backup could either be a full backup or another incremental
backup.  You then use BASE_BACKUP with the INCREMENTAL option to take
the backup.  pg_basebackup now has an --incremental=PATH_TO_MANIFEST
option to trigger this behavior.
 
An incremental backup is like a regular full backup except that
some relation files are replaced with files with names like
INCREMENTAL.${ORIGINAL_NAME}, and the backup_label file contains
additional lines identifying it as an incremental backup. The new
pg_combinebackup tool can be used to reconstruct a data directory
from a full backup and a series of incremental backups.
 
Patch by me.  Reviewed by Matthias van de Meent, Dilip Kumar, Jakub
Wartak, Peter Eisentraut, and Álvaro Herrera. Thanks especially to
Jakub for incredibly helpful and extensive testing.
 
Discussion: http://postgr.es/m/CA+TgmoYOYZfMCyOXFyC-P+-mdrZqm5pP2N7S-r0z3_402h9rsA@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add support for incremental backup.

Posted on 2024-01-08|Tags backup, backups, incremental, pg17, pg_basebackup,
postgresql, waiting|1 Comment on Waiting for PostgreSQL 17 – Add support for
incremental backup.


PROPER SUPPORT FOR I/O TIMINGS ON EXPLAIN.DEPESZ.COM

Very long time ago (in PostgreSQL 9.2) we got ability to get, from EXPLAIN,
information how long Pg spent on I/O operations. Reads and writes.

Over the years situation has changed.

Continue reading Proper support for I/O Timings on explain.depesz.com

Posted on 2023-12-29|Tags announcements, explain, explain.depesz.com, perl,
postgresql|Leave a comment on Proper support for I/O Timings on
explain.depesz.com


HUMAN/VERSION SORT IN POSTGRESQL – REVISITED

Couple of months ago I wrote how to do human sort in Pg by using arrays, and
splitting string.

This works, but Matt mentioned in comments that it could be done with ICU
collations.

So I looked into it …

Continue reading Human/version sort in PostgreSQL – revisited

Posted on 2023-10-242023-10-24|Tags collation, compare, howto, human, icu,
postgresql, sort, unicode, version|Leave a comment on Human/version sort in
PostgreSQL – revisited


WAITING FOR POSTGRESQL 17 – ADD SUPPORT EVENT TRIGGERS ON AUTHENTICATED LOGIN

On 16th of October 2023, Alexander Korotkov committed patch:

Add support event triggers on authenticated login
 
This commit introduces trigger on login event, allowing to fire some actions
right on the user connection.  This can be useful for logging or connection
check purposes as well as for some personalization of environment.  Usage
details are described in the documentation included, but shortly usage is
the same as for other triggers: create function returning event_trigger and
then create event trigger on login event.
 
In order to prevent the connection time overhead when there are no triggers
the commit introduces pg_database.dathasloginevt flag, which indicates database
has active login triggers.  This flag is set by CREATE/ALTER EVENT TRIGGER
command, and unset at connection time when no active triggers found.
 
Author: Konstantin Knizhnik, Mikhail Gribkov
Discussion: https://postgr.es/m/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru
Reviewed-by: Pavel Stehule, Takayuki Tsunakawa, Greg Nancarrow, Ivan Panchenko
Reviewed-by: Daniel Gustafsson, Teodor Sigaev, Robert Haas, Andres Freund
Reviewed-by: Tom Lane, Andrey Sokolov, Zhihong Yu, Sergey Shinderuk
Reviewed-by: Gregory Stark, Nikita Malakhov, Ted Yu

Continue reading Waiting for PostgreSQL 17 – Add support event triggers on
authenticated login

Posted on 2023-10-24|Tags authentication, event, initialization, login, perl,
pg17, postgresql, trigger, waiting|3 Comments on Waiting for PostgreSQL 17 – Add
support event triggers on authenticated login


POSTS NAVIGATION

Page 1 Page 2 … Page 161 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 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