Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 9 Minuten 11 Sekunden

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

17. Februar 2020 - 15:24
On 6th of February 2020, Michael Paquier committed patch: Add leader_pid to pg_stat_activity   This new field tracks the PID of the group leader used with parallel query. For parallel workers and the leader, the value is set to the PID of the group leader. So, for the group leader, the value is the same … Continue reading "Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity"

Daniel Vérité: Isolation Repeatable Read in PostgreSQL versus MySQL

14. Februar 2020 - 19:14

To avoid having concurrent transactions interfere with each other, SQL engines implement isolation as a feature. This property corresponds to the I letter in the well known ACID acronym, the other properties being Atomicity, Consistency and Durability.

Isolation happens to be configurable, with different levels that correspond to different behaviors when executing concurrent transactions.

Pavel Stehule: plpgsql_check 1.9 calculates coverage metrics

14. Februar 2020 - 19:03
Small note - I finished support of statement and branch coverage metrics calculations for plpgsql_check

Ernst-Georg Schmid: Excel and ODF support for cloudfs_fdw

14. Februar 2020 - 11:53
cloudfs_fdw now supports .xls (Excel 97-2003), .xlsx, and .ods (Open Document Format) Spreadsheets via pandas, xlrd, and odfpy. It requires pandas >= 1.0.1, so Multicorn must be compiled against Python 3.

Robert Haas: Useless Vacuuming

13. Februar 2020 - 20:13
In previous blog posts that I've written about VACUUM, and I seem to be accumulating an uncomfortable number of those, I've talked about various things that can go wrong with vacuum, but one that I haven't really covered is when autovacuum seems to be running totally normally but you still have a VACUUM problem. In this blog post, I'd like to talk about how to recognize that situation, how to figure out what has caused it, how to avoid it via good monitoring, and how to recover if it happens.

Jobin Augustine: Compression of PostgreSQL WAL Archives Becoming More Important

13. Februar 2020 - 15:54

As hardware and software evolve, the bottlenecks in a database system also shift. Many old problems might disappear and new types of problems pop-up.

Old Limitations

There were days when CPU and Memory was a limitation. More than a decade back, servers with 4 cores were “High End” and as a DBA, my biggest worry was managing the available resources. And for an old DBA like me, Oracle’s attempt to pool CPU and Memory from multiple host machines for a single database using RAC architecture was a great attempt to solve it.

Luca Ferrari: Take advantage of pg_settings when dealing with your configuration

13. Februar 2020 - 1:00

The right way to get the current PostgreSQL configuration is by means of pg_settings.

Take advantage of pg_settings when dealing with your configuration

I often see messages on PostgreSQL related mailing list where the configuration is assumed by a Unix-style approach. For example, imagine you have been asked to provide your autovacuum configuration in order to see if there’s something wrong with it; one approach I often is the copy and paste of the following:

Dave Conlin: Configuring work_mem in Postgres

12. Februar 2020 - 15:38

One of the worst performance hits a Postgres query can take is having to perform a sort or hash operation on disk. When these space-intensive operations require more memory than is available, Postgres uses disk space instead. Disk space is much slower to read and write than RAM, so this generally takes significantly longer.

The best solution to this problem is to avoid having to perform the operation entirely, for example by adding a judicious index.

Hans-Juergen Schoenig: Migrating from MS SQL to PostgreSQL: Uppercase vs. Lowercase

12. Februar 2020 - 9:00

When migrating from MS SQL to PostgreSQL, one of the first things people notice is that in MS SQL, object names such as tables and columns all appear in uppercase. While that is possible on the PostgreSQL side as well it is not really that common. The question therefore is: How can we rename all those things to lowercase – easily and fast?



Mark Wong: Creating a PostgreSQL procedural language – Part 2 – Embedding Julia

12. Februar 2020 - 1:51
Julia provides an API so that Julia functions can be called from C.  PL/Julia will use this C API to execute Julia code from its user defined functions and stored procedures. Julia’s documentation provides an example C program that starts up the Julia environment, evaluates the expression sqrt(2.0), displays the resulting value to the standard […]

Sadequl Hussain: How to Automate PostgreSQL 12 Replication and Failover with repmgr – Part 2

11. Februar 2020 - 11:38
This is the second installment of a two-part series on 2ndQuadrant’s repmgr, an open-source high-availability tool for PostgreSQL. In the first part, we set up a three-node PostgreSQL 12 cluster along with a “witness” node. The cluster consisted of a primary node and two standby nodes. The cluster and the witness node were hosted in […]

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: