Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 56 Minuten 35 Sekunden

Luca Ferrari: Executing VACUUM by non-owner user

6. Februar 2020 - 1:00

VACUUM needs to be run by the object owner!

Executing VACUUM by non-owner user

The documentation about VACUUM clearly states it:

Mark Wong: Creating a PostgreSQL procedural language – Part 1 – Setup

5. Februar 2020 - 22:31
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 […]

Luca Ferrari: PL/PgSQL Exception and XIDs

5. Februar 2020 - 1:00
Unable to clean HTML

Andrew L'Ecuyer: Guard Against Transaction Loss with PostgreSQL Synchronous Replication

4. Februar 2020 - 16:44

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.

Semab Tariq: How to use Machine Learning with 2UDA – PostgreSQL and Orange

4. Februar 2020 - 14:19
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 […]

Martin Davis: Running commands in the JTS TestBuilder

4. Februar 2020 - 6:40
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

Hugo Dias: An Overview of Job Scheduling Tools for PostgreSQL

3. Februar 2020 - 21:01

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...

Federico Campoli: The strange case of the EXCEPTION block

3. Februar 2020 - 1:00

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.

Darafei Praliaskouski: PostGIS 3.1.0alpha1

2. Februar 2020 - 1:00

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 ..

Abdul Yadi: Extension for String Translation

1. Februar 2020 - 9:23

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

Kaarel Moppel: Row change auditing options for PostgreSQL

30. Januar 2020 - 11:00

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.

Michael Banck: Debian-Integration of Patroni and vip-manager

30. Januar 2020 - 9:31
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

Luca Ferrari: Checking catalogues for corruption with pg_catcheck

30. Januar 2020 - 1:00
Unable to clean HTML

Richard Yen: The Most Neglected Postgres Feature

29. Januar 2020 - 22:00

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:

Peter Eisentraut: Developing PostgreSQL for Windows, Part 1

29. Januar 2020 - 12:07
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 […]

Hubert 'depesz' Lubaczewski: Which tables should be auto vacuumed or auto analyzed?

29. Januar 2020 - 3:01
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?"

Hubert 'depesz' Lubaczewski: Don’t do these things in PostgreSQL

28. Januar 2020 - 21:17
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"

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Add functions gcd() and lcm() for integer and numeric types.

28. Januar 2020 - 13:46
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.

Jonathan Katz: Scheduled PostgreSQL Backups and Retention Policies with Kubernetes

28. Januar 2020 - 12:35

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

28. Januar 2020 - 9:30

pg_timetable is a brand-new job scheduler for PostgreSQL implemented completely from scratch (in Go). Traditional job scheduling is quite limited and does not cover all we needed in real life. Therefore, we thought about job execution in more detail and came up with a new concept that has not been seen in the PostgreSQL world before. pg_timetable does not simply execute some simple SQL statements periodically.