Neues vom PostgreSQL Planet

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.

Debian-Integration of Patroni and vip-manager Patroni is a clustering solution for PostgreSQL that is getting more and more popular in the cloud and Kubernetes sector due to its operator pattern and integration with Etcd or Consul. Some time ago we wrote a blog post about the integration of Patroni into Debian. Recently, the vip-manager... 30-01 Michael Banck

Unable to clean HTML

Introduction

I recently did some work with a customer who had some strange behavior happening in his database. When I asked for his logs, I found that each line had a message, and just one timestamp prefixed to it. In other words, he had log_line_prefix = '%t '. This made it hard for me to figure out who did what, especially as his database was serving many clients and applications. It got me thinking, and I scoured through our other clients’ postgresql.conf files that had been shared with me over the years, and in ~130 conf files, I found the following:

As a PostgreSQL developer, I occasionally need to make my code work on Windows. As I don’t normally use Windows and don’t have a permanent Windows installation around, this has always been a bit cumbersome. I have developed a few techniques to make this easier, and I figure they are worth sharing. And actually this […]

Recently I was in a situation where autovacuum couldn't keep up with changes. To solve the problem I finally decided to manually vacuum analyze all tables (manual vacuum/analyze is faster than one ran by autovacuum daemon). But it irritated me that I didn't have ready way to check which tables are waiting for autovacuum to … Continue reading "Which tables should be auto vacuumed or auto analyzed?"

In 2018 Lluad created wiki page with couple of rules about what not to do. Since then there have been 30 changes, done by nine more people. Based on some conversation on irc I figured I can write a tool to automatically check these rules. Or at least – most of them. Currently wiki lists … Continue reading "Don’t do these things in PostgreSQL"

On 25th of January 2020, Dean Rasheed committed patch: Add functions gcd() and lcm() for integer and numeric types.   These compute the greatest common divisor and least common multiple of a pair of numbers using the Euclidean algorithm.   Vik Fearing, reviewed by Fabien Coelho.

It is important (understatement) that you take regularly scheduled backups of your PostgreSQL system as well as manage how many backups you have, which is known as "backup retention." These best practices ensure that you always have a recent backup of your database system to recover from in the event of a disaster (or use to

Hans-Juergen Schoenig: pg_timetable: Advanced PostgreSQL scheduling