Neues vom PostgreSQL Planet
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.
\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"
+ + * ******* + + + +
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:
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+.
Mark Wong: PDXPUG February 2020 Meetup: DBlink and SQL/MED and FDW, oh my! External Data Access tricks
2020 February 20 Meeting 6pm-8pm
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.
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
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.
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.
PostgreSQL provides a built-in function for character wise string replacement:
select translate('abcdefghijkl', 'ace', '123');
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.