Neues vom PostgreSQL Planet

Photo by @kelvyn on Unsplash

The End Point development team has completed a major application migration from one stack to another. Many years ago, the vendor maintaining the old stack abandoned support and development. This led to a stack evolution riddled with independent custom changes and new features in the following years.

My first experience of a Postgres day, where people talk postgreSQL all day. My day started very early – I left home at 05:15 and arrived in Brussels at 09:10. Unfortunately, due to the train schedules (where trains seem to wake up later than I do) I arrived a bit late for the opening talk by Magnus Hagander. Magnus is one of the core members of the PostgreSQL infrastructure team, developer and comitter in the Global development group. And then, straight into the sessions in a packed room, fortunately there was a seat available.

psql from PostgreSQL 12 can produces CSV format. This format is well readable by gnuplot. So anybody can use it together:

\pset format csv
select i, sin(i) from generate_series(0, 6.3, 0.5) g(i) \g |gnuplot -p -e "set datafile separator ','; set key autotitle columnhead; plot '-'with boxes"

1 +-+---------+-*******--+-----------+----------+-----------+--------+-+
+ + * ******* + + + +
0.8 +-+ ****** * * sin *******-+

You may have noticed how dropping a column is fast in PostgreSQL, haven’t you?

Why Dropping a Column does not Reclaim Disk Space? (or better, why is it so fast?)

Simple answer: because PostgreSQL knows how to do its job at best!

Let’s create a dummy table to test this behavior against:

Last week I was on Moscow's PgConf.Russia 2020 (Thanks for Oleg Bartunov for invitation). It was interesting and very good event, where I can meet interesting people, where I can see interesting places - like famous building of Moscow Lomonosov State University - with nice guide from PostgresPro.

PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+.

2020 February 20 Meeting 6pm-8pm

Location:

PSU Business Accelerator
2828 SW Corbett Ave · Portland, OR
Parking is open after 5pm.

Speakers: Gabrielle Roth & Michelle Franz

In February, long-term PDXPUGers Michelle Franz & Gabrielle Roth return to discuss DBLINK and FDW, two methods of accessing data that’s outside your database. We’ll briefly mention SQL/MED on the way, and tell a couple of bad (Dad) jokes.

New leader_pid column in pg_stat_activity view

Surprisingly, since parallel query was introduced in PostgreSQL 9.6, it was impossible to know wich backend a parallel worker was related to. So, as Guillaume pointed out, it makes it quite difficult to build simple tools that can sample the wait events related to all process involved in a query. A simple solution to that problem is to export the lock group leader information available in the backend at the SQL level:

check_pgbackrest is designed to monitor pgBackRest backups, relying on the status information given by the info command.

The main features are:

VACUUM needs to be run by the object owner!

Executing VACUUM by non-owner user

The documentation about VACUUM clearly states it:

PostgreSQL supports many procedural languages, which can be used to write user defined functions or stored procedures.  There are four that are readily available as part of the standard PostgreSQL distribution: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python.  Yet procedural languages don’t have to be created as part of the core project.  There are a number more that […]

Unable to clean HTML

Crunchy Data recently released its latest version of the open source PostgreSQL Operator for Kubernetes, version 4.2. Among the various enhancements included within this release is support for Synchronous Replication within deployed PostgreSQL clusters.

This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In the article, we’ll use an example of Animals to predict whether they are mammals, Birds, Fish or Insects. Software versions We’re going to use 2UDA version 11.6-1 to implement the Machine Learning model. 2UDA version 11.6-1 combines: PostgreSQL 11.6 […]

The JTS TestBuilder is a great tool for creating geometry, processing it with JTS spatial functions, and visualizing the results.  It has powerful capabilities for inspecting the fine details of geometry (such as the Reveal Topology mode).  I've often thought it would be handy if there was a similar tool for PostGIS.  Of course QGIS excels at visualizing the results of PostGIS queries.  But it doesn't offer the same simplicity for creating geometry and passing it into P

Unlike other database management systems that have their own built-in scheduler (like Oracle, MSSQL or MySQL), PostgreSQL still doesn’t have this kind of feature.

In order to provide scheduling functionality in PostgreSQL you will need to use an external tool like...

When in a pl/pgsql function there is an exception then the function stops the execution and returns an error. When this happens all the changes made are rolled back.

It’s always possible to manage the error at application level, however there are some cases where managing the exception inside the function it may be a sensible choice. And pl/pgsql have a nice way to do that. The EXCEPTION block.

However handling the exception inside a function is not just a cosmetic thing. The way the excepion is handled have implications that may cause issues.

The PostGIS Team is pleased to release the first alpha of upcoming PostGIS 3.1.0 release.

Best served with PostgreSQL 12.1, GEOS 3.8.0.

Continue Reading by clicking title hyperlink ..

PostgreSQL provides a built-in function for character wise string replacement:
select translate('abcdefghijkl', 'ace', '123');
translate
--------------
1b2d3fghijkl

Recently, I was asked for advice on how to reasonably implement a common task of table change tracking – meaning a so-called “audit trail” for all row changes over time was required. The keyword “compliance” might also ring a bell in this context, here for those who work in finance or for government institutions. But as always with technology, there are a couple of different approaches with different benefits / drawbacks to choose from; let’s lay it out for the Internet Citizens! There’s a summary of pros and cons down below if you’re in a hurry.