Category: Uncategorized

Waiting for PostgreSQL 17 – Generate new LOG for “trust” connections under log_connections

On 26th of August 2023, Michael Paquier committed patch:

Generate new LOG for "trust" connections under log_connections
 
Adding an extra LOG for connections that have not set an authn ID, like
when the "trust" authentication method is used, is useful for audit
purposes.
 
A couple of TAP tests for SSL and authentication need to be tweaked to
adapt to this new LOG generated, as some scenarios expected no logs but
they now get a hit.
 
Reported-by: Shaun Thomas
Author: Jacob Champion
Reviewed-by: Robert Haas, Michael Paquier
Discussion: https://postgr.es/m/CAFdbL1N7-GF-ZXKaB3XuGA+CkSmnjFvqb8hgjMnDfd+uhL2u-A@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Generate new LOG for “trust" connections under log_connections

Waiting for PostgreSQL 17 – Add to_bin() and to_oct().

On 23rd of August 2023, Nathan Bossart committed patch:

Add to_bin() and to_oct().
 
This commit introduces functions for converting numbers to their
equivalent binary and octal representations.  Also, the base
conversion code for these functions and to_hex() has been moved to
a common helper function.
 
Co-authored-by: Eric Radman
Reviewed-by: Ian Barwick, Dag Lem, Vignesh C, Tom Lane, Peter Eisentraut, Kirk Wolak, Vik Fearing, John Naylor, Dean Rasheed
Discussion: https://postgr.es/m/Y6IyTQQ/TsD5wnsH%40vm3.eradman.com

Continue reading Waiting for PostgreSQL 17 – Add to_bin() and to_oct().

Human/version sort in PostgreSQL

Ever been in situation where you had to sort data that is partially text, and partially numerical?

Like invoice numbers: prefix-9, prefix-10, prefix-11, other-5, other-20 ? Normally you can't do order by as you will get them in wrong order: other-20 ⇒ other-5 ⇒ prefix-10 ⇒ prefix-11 ⇒ prefix-9. Can something be done with it?

Continue reading Human/version sort in PostgreSQL

A tale about (incomplete) upgrade from PostgreSQL 12 to 14

This might not interest many of you, but I recently heard about at least two people that stumbled upon the problems I did, so I figured I can write about problems we discovered, and how we solved them (or not).

When we began our journey, the latest Pg was 14.x, that's why we're upgrading to 14, not 15. But I suspect upgrading to 15 wouldn't change much …

Continue reading A tale about (incomplete) upgrade from PostgreSQL 12 to 14

Waiting for PostgreSQL 16 – Add array_sample() and array_shuffle() functions.

On 7th of April 2023, Tom Lane committed patch:

Add array_sample() and array_shuffle() functions.
 
These are useful in Monte Carlo applications.
 
Martin Kalcher, reviewed/adjusted by Daniel Gustafsson and myself
 
Discussion: https://postgr.es/m/9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net

Continue reading Waiting for PostgreSQL 16 – Add array_sample() and array_shuffle() functions.

System roles – what, why, how?

Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles.

Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_.

Continue reading System roles – what, why, how?

Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

On 11st of February 2023, Andres Freund committed patch:

Add pg_stat_io view, providing more detailed IO statistics
 
Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation.
 
Rows of the view show IO operations for a particular backend type, IO target
object, IO context combination (e.g. a client backend's operations on
permanent relations in shared buffers) and each column in the view is the
total number of IO Operations done (e.g. writes). So a cell in the view would
be, for example, the number of blocks of relation data written from shared
buffers by client backends since the last stats reset.
 
In anticipation of tracking WAL IO and non-block-oriented IO (such as
temporary file IO), the "op_bytes" column specifies the unit of the "reads",
"writes", and "extends" columns for a given row.
 
Rows for combinations of IO operation, backend type, target object and context
that never occur, are ommitted entirely. For example, checkpointer will never
operate on temporary relations.
 
Similarly, if an IO operation never occurs for such a combination, the IO
operation's cell will be null, to distinguish from 0 observed IO
operations. For example, bgwriter should not perform reads.
 
Note that some of the cells in the view are redundant with fields in
pg_stat_bgwriter (e.g. buffers_backend). For now, these have been kept for
backwards compatibility.
 
Bumps catversion.
 
Author: Melanie Plageman <melanieplageman@gmail.com>
Author: Samay Sharma <smilingsamay@gmail.com>
Reviewed-by: Maciek Sakrejda <m.sakrejda@gmail.com>
Reviewed-by: Lukas Fittl <lukas@fittl.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de

Continue reading Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

How to get a row, and all of it’s dependencies?

This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?

Decided to take a look at this task.

Continue reading How to get a row, and all of it's dependencies?