Tag: postgresql

Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

On 17th of January 2021, Magnus Hagander committed patch:

Add pg_stat_database counters for sessions and session time
 
This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.
 
Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.
 
Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at

Continue reading Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

How to run some tasks without user intervention, at specific times?

Every now and then someone complains (me included) that PostgreSQL doesn't have job scheduler.

This is true, to some extent. I'll try to show you couple of approaches to solving this particular problem.

Continue reading How to run some tasks without user intervention, at specific times?

Waiting for PostgreSQL 14 – Add idle_session_timeout.

On 6th of January 2021, Tom Lane committed patch:

Add idle_session_timeout.
 
This GUC variable works much like idle_in_transaction_session_timeout,
in that it kills sessions that have waited too long for a new client
query.  But it applies when we're not in a transaction, rather than
when we are.
 
Li Japin, reviewed by David Johnston and Hayato Kuroda, some
fixes by me
 
Discussion: https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com

Continue reading Waiting for PostgreSQL 14 – Add idle_session_timeout.

Waiting for PostgreSQL 14 – Report progress of COPY commands

On 6th of January 2021, Tomas Vondra committed patch:

Report progress of COPY commands
 
This commit introduces a view pg_stat_progress_copy, reporting progress
of COPY commands.  This allows rough estimates how far a running COPY
progressed, with the caveat that the total number of bytes may not be
available in some cases (e.g. when the input comes from the client).
 
Author: Josef Šimánek
Reviewed-by: Fujii Masao, Bharath Rupireddy, Vignesh C, Matthias van de Meent
Discussion: https://postgr.es/m/CAFp7QwqMGEi4OyyaLEK9DR0+E+oK3UtA4bEjDVCa4bNkwUY2PQ@mail.gmail.com
Discussion: https://postgr.es/m/CAFp7Qwr6_FmRM6pCO0x_a0mymOfX_Gg+FEKet4XaTGSW=LitKQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 14 – Report progress of COPY commands

How to limit rows to at most N per category – fix

A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.

Unfortunately, I overlooked a problem related to concurrency.

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

Fixes on why-upgrade.depesz.com

In case you're not familiar with this site, why-upgrade.depesz.com shows you aggregated changelog between any two releases, with optionally searching for some keywords.

Yesterday azeem on irc pointed me towards a problem on why-upgrade.depesz.com. Specifically, when displaying changes from 9.5.24 to 13.1 site showed 30 security fixes. But in reality there should be only 10.

Continue reading Fixes on why-upgrade.depesz.com

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

In previous post I showed how I'd install PostgreSQL for developer.

But that's not all. Now we need to add some configuration. What, how, where?

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

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