Getting value from dynamic column in pl/PgSQL triggers?

Every so often, on irc, someone asks how to get value from column that is passed as argument.

This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.

We can work around it, though. Are the workarounds usable, in terms of performance?

Continue reading Getting value from dynamic column in pl/PgSQL triggers?

Changes on explain.depesz.com – extracted query from auto-explain plans

Some time ago James Courtney reported missing functionality.

Specifically, when one uses auto-explain, logged explains contain query text. So, when such explain is then pasted on explain.depesz.com, it stands to reason that it should be able to extract the query on its own, without having to manually extract it and put it in query box.

It took me a while, but finally, got it working today. And you can see it in all four explain format:

Also, while I'm writing – it seems that somewhere next month, there will be 1 millionth plan uploaded to the site 🙂 Hope you all find it useful 🙂

Waiting for PostgreSQL 14 – Add unistr function

On 29th of March 2021, Peter Eisentraut committed patch:

Add unistr function
 
This allows decoding a string with Unicode escape sequences.  It is
similar to Unicode escape strings, but offers some more flexibility.
 
Author: Pavel Stehule <pavel.stehule@gmail.com>
Reviewed-by: Asif Rehman <asifr.rehman@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAFj8pRA5GnKT+gDVwbVRH2ep451H_myBt+NTz8RkYUARE9+qOQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 14 – Add unistr function

Waiting for PostgreSQL 14 – Add “pg_database_owner” default role.

On 26th of March 2021, Noah Misch committed patch:

Add "pg_database_owner" default role.
 
Membership consists, implicitly, of the current database owner.  Expect
use in template databases.  Once pg_database_owner has rights within a
template, each owner of a database instantiated from that template will
exercise those rights.
 
Reviewed by John Naylor.
 
Discussion: https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com

Continue reading Waiting for PostgreSQL 14 – Add “pg_database_owner" default role.

Waiting for PostgreSQL 14 – Add date_bin function

On 24th of March 2021, Peter Eisentraut committed patch:

Add date_bin function
 
Similar to date_trunc, but allows binning by an arbitrary interval
rather than just full units.
 
Author: John Naylor <john.naylor@enterprisedb.com>
Reviewed-by: David Fetter <david@fetter.org>
Reviewed-by: Isaac Morland <isaac.morland@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Artur Zakirov <zaartur@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg@mail.gmail.com

Continue reading Waiting for PostgreSQL 14 – Add date_bin function

Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression.

On 19th of March 2021, Robert Haas committed patch:

Allow configurable LZ4 TOAST compression.
 
There is now a per-column COMPRESSION option which can be set to pglz
(the default, and the only option in up until now) or lz4. Or, if you
like, you can set the new default_toast_compression GUC to lz4, and
then that will be the default for new table columns for which no value
is specified. We don't have lz4 support in the PostgreSQL code, so
to use lz4 compression, PostgreSQL must be built --with-lz4.
 
In general, TOAST compression means compression of individual column
values, not the whole tuple, and those values can either be compressed
inline within the tuple or compressed and then stored externally in
the TOAST table, so those properties also apply to this feature.
 
Prior to this commit, a TOAST pointer has two unused bits as part of
the va_extsize field, and a compessed datum has two unused bits as
part of the va_rawsize field. These bits are unused because the length
of a varlena is limited to 1GB; we now use them to indicate the
compression type that was used. This means we only have bit space for
2 more built-in compresison types, but we could work around that
problem, if necessary, by introducing a new vartag_external value for
any further types we end up wanting to add. Hopefully, it won't be
too important to offer a wide selection of algorithms here, since
each one we add not only takes more coding but also adds a build
dependency for every packager. Nevertheless, it seems worth doing
at least this much, because LZ4 gets better compression than PGLZ
with less CPU usage.
 
It's possible for LZ4-compressed datums to leak into composite type
values stored on disk, just as it is for PGLZ. It's also possible for
LZ4-compressed attributes to be copied into a different table via SQL
commands such as CREATE TABLE AS or INSERT .. SELECT.  It would be
expensive to force such values to be decompressed, so PostgreSQL has
never done so. For the same reasons, we also don't force recompression
of already-compressed values even if the target table prefers a
different compression method than was used for the source data.  These
architectural decisions are perhaps arguable but revisiting them is
well beyond the scope of what seemed possible to do as part of this
project.  However, it's relatively cheap to recompress as part of
VACUUM FULL or CLUSTER, so this commit adjusts those commands to do
so, if the configured compression method of the table happens not to
match what was used for some column value stored therein.
 
Dilip Kumar. The original patches on which this work was based were
written by Ildus Kurbangaliev, and those were patches were based on
even earlier work by Nikita Glukhov, but the design has since changed
very substantially, since allow a potentially large number of
compression methods that could be added and dropped on a running
system proved too problematic given some of the architectural issues
mentioned above; the choice of which specific compression method to
add first is now different; and a lot of the code has been heavily
refactored.  More recently, Justin Przyby helped quite a bit with
testing and reviewing and this version also includes some code
contributions from him. Other design input and review from Tomas
Vondra, Álvaro Herrera, Andres Freund, Oleg Bartunov, Alexander
Korotkov, and me.
 
Discussion: http://postgr.es/m/20170907194236.4cefce96%40wp.localdomain
Discussion: http://postgr.es/m/CAFiTN-uUpX3ck%3DK0mLEk-G_kUQY%3DSNOTeqdaNRR9FMdQrHKebw%40mail.gmail.com

Continue reading Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression.

Starting with Pg – where/how can I set configuration parameters?

Previously I wrote about locating config files.

The thing is – postgresql.conf is not the only place you can set your configuration in.

In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.

Continue reading Starting with Pg – where/how can I set configuration parameters?

explain.depesz.com – now with JITs

Ever since PostgreSQL 11 we have JIT (Just-In-Time compilation).

Information about JIT is displayed in explain analyze plans, but up to this moment, explain.depesz.com didn't display it properly. But not anymore. Thanks to release 1.05 of Pg::Explain and subsequent change in templates for the site, you can now see nice JIT info.

Hope you'll find it useful 🙂