Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 46 Minuten

Selvakumar Arumugam: A Tool to Compare PostgreSQL Database Schema Versions

11. Februar 2020 - 1:00

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.

Thomas Spoelstra: My First Postgres Day experience at FosDEM 2020

10. Februar 2020 - 12:17
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.

Pavel Stehule: psql and gnuplot

9. Februar 2020 - 8:48
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 *******-+

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

9. Februar 2020 - 1:00

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:

Pavel Stehule: Moscow PgConf.Russia 2020

7. Februar 2020 - 20:03
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.

Keith Fiske: How To Migrate From Trigger-Based Partitioning To Native in PostgreSQL

7. Februar 2020 - 15:53

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

6. Februar 2020 - 17:45

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.

Julien Rouhaud: New in pg12: New leader_pid column in pg_stat_activity

6. Februar 2020 - 13:59
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:

Stefan Fercot: Monitor pgBackRest backups with check_pgbackrest 1.7

6. Februar 2020 - 1:00

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

The main features are:

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.