Author: depesz

How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 1

Recently I spent some time thinking about what can be improved when it comes to helping new users start using PostgreSQL.

One thing that almost immediately jumped to my mind is – how to install PostgreSQL? The task is theoretically simple. But there are always some caveats – which packages to use, what to configure in the beginning, where to find config files and logs.

With that in mind I decided to write a howto based on my ideas on what is right. These do not necessarily mean that these are the best for everybody, but I think this is a good start for anyone wanting to start their adventure with PostgreSQL.

Final note of warning – this post is for installing and setting PostgreSQL on developer workstation. As in: server where user can do anything, and we don't really care about security. Please do not configure production servers using this howto.

Continue reading How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 1

Waiting for PostgreSQL 14 – Multirange datatypes

On 20th of December 2020, Alexander Korotkov committed patch:

Multirange datatypes
 
Multiranges are basically sorted arrays of non-overlapping ranges with
set-theoretic operations defined over them.
 
Since v14, each range type automatically gets a corresponding multirange
datatype.  There are both manual and automatic mechanisms for naming multirange
types.  Once can specify multirange type name using multirange_type_name
attribute in CREATE TYPE.  Otherwise, a multirange type name is generated
automatically.  If the range type name contains "range" then we change that to
"multirange".  Otherwise, we add "_multirange" to the end.
 
Implementation of multiranges comes with a space-efficient internal
representation format, which evades extra paddings and duplicated storage of
oids.  Altogether this format allows fetching a particular range by its index
in O(n).
 
Statistic gathering and selectivity estimation are implemented for multiranges.
For this purpose, stored multirange is approximated as union range without gaps.
This field will likely need improvements in the future.
 
Catversion is bumped.
 
Discussion: https://postgr.es/m/CALNJ-vSUpQ_Y%3DjXvTxt1VYFztaBSsWVXeF1y6gTYQ4bOiWDLgQ%40mail.gmail.com
Discussion: https://postgr.es/m/a0b8026459d1e6167933be2104a6174e7d40d0ab.camel%40j-davis.com#fe7218c83b08068bfffb0c5293eceda0
Author: Paul Jungwirth, revised by me
Reviewed-by: David Fetter, Corey Huinker, Jeff Davis, Pavel Stehule
Reviewed-by: Alvaro Herrera, Tom Lane, Isaac Morland, David G. Johnston
Reviewed-by: Zhihong Yu, Alexander Korotkov

Continue reading Waiting for PostgreSQL 14 – Multirange datatypes

Waiting for PostgreSQL 14 – pg_stat_statements: Track time at which all statistics were last reset.

On 18th of December 2020, Fujii Masao committed patch:

pg_stat_statements: Track time at which all statistics were last reset.
 
This commit adds "stats_reset" column into the pg_stat_statements_info
view. This column indicates the time at which all statistics in the
pg_stat_statements view were last reset.
 
Per discussion, this commit also changes pg_stat_statements_info code
so that "dealloc" column is reset at the same time as "stats_reset" is reset,
i.e., whenever all pg_stat_statements entries are removed, for the sake
of consistency. Previously "dealloc" was reset only when
pg_stat_statements_reset(0, 0, 0) is called and was not reset when
pg_stat_statements_reset() with non-zero value argument discards all
entries. This was confusing.
 
Author: Naoki Nakamichi, Yuki Seino
Reviewed-by: Yuki Seino, Kyotaro Horiguchi, Li Japin, Fujii Masao
Discussion: https://postgr.es/m/c102cf3180d0ee73c1c5a0f7f8558322@oss.nttdata.com

Continue reading Waiting for PostgreSQL 14 – pg_stat_statements: Track time at which all statistics were last reset.

Waiting for PostgreSQL 14 – Allow subscripting of hstore values.

On 11st of December 2020, Tom Lane committed patch:

Allow subscripting of hstore values.
 
This is basically a finger exercise to prove that it's possible for
an extension module to add subscripting ability.  Subscripted fetch
from an hstore is not different from the existing "hstore -> text"
operator.  Subscripted update does seem to be a little easier to
use than the traditional update method using hstore concatenation,
but it's not a fundamentally new ability.
 
However, there may be some value in the code as sample code, since
it shows what's basically the minimum-complexity way to implement
subscripting when one needn't consider nested container objects.
 
Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us

Continue reading Waiting for PostgreSQL 14 – Allow subscripting of hstore values.

How to limit rows to at most N per category

The question was asked relatively recently on irc. And it proved to be non-trivial.

Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?

Let's see.

Continue reading How to limit rows to at most N per category

Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

On 14th of November 2020, Tom Lane committed patch:

Provide the OR REPLACE option for CREATE TRIGGER.
 
This is mostly straightforward.  However, we disallow replacing
constraint triggers or changing the is-constraint property; perhaps
that can be added later, but the complexity versus benefit tradeoff
doesn't look very good.
 
Also, no special thought is taken here for whether replacing an
existing trigger should result in changes to queued-but-not-fired
trigger actions.  We just document that if you're surprised by the
results, too bad, don't do that.  (Note that any such pending trigger
activity would have to be within the current session.)
 
Takamichi Osumi, reviewed at various times by Surafel Temesgen,
Peter Smith, and myself
 
Discussion: https://postgr.es/m/0DDF369B45A1B44B8A687ED43F06557C010BC362@G01JPEXMBYT03

Continue reading Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

Waiting for PostgreSQL 14 – Support negative indexes in split_part().

On 13rd of November 2020, Tom Lane committed patch:

Support negative indexes in split_part().
 
This provides a handy way to get, say, the last field of the string.
Use of a negative index in this way has precedent in the nearby
left() and right() functions.
 
The implementation scans the string twice when N < -1, but it seems
likely that N = -1 will be the huge majority of actual use cases,
so I'm not really excited about adding complexity to avoid that.
 
Nikhil Benesch, reviewed by Jacob Champion; cosmetic tweakage by me
 
Discussion: https://postgr.es/m/cbb7f861-6162-3a51-9823-97bc3aa0b638@gmail.com

Continue reading Waiting for PostgreSQL 14 – Support negative indexes in split_part().

New functionality on explain.depesz.com

Just committed some new changes to explain.depesz.com.

The less visible is that, in case of plans generated by plain EXPLAIN (without ANALYZE), site will no longer show “actual time=.. rows= loops=", which was clearly bogus info.

The more visible is that if you add query to your plan, you can also have it reformatted to (hopefully) nicer format, shown in “REFORMATTED QUERY" tab. For example, take a look at this example.

Reformatting is done using pgFormatter library by Gilles Darold.

I picked this library for the very simple reason – it's in Perl, just like explain.depesz.com site, which made using it a breeze.

Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

On 5th of October 2020, Peter Eisentraut committed patch:

Support for OUT parameters in procedures 
 
Unlike for functions, OUT parameters for procedures are part of the
signature.  Therefore, they have to be listed in pg_proc.proargtypes
as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.
 
Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com

Continue reading Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

On 11st of September 2020, Alvaro Herrera committed patch:

psql: Display stats target of extended statistics
 
The stats target can be set since commit d06215d03, but wasn't shown by
psql.
 
Author: Justin Pryzby <justin@telsasoft.com>
Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com
Reviewed-by: Georgios Kokolatos <gkokolatos@protonmail.com>
Reviewed-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>

Continue reading Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics