Digest news from the world of PostgreSQL. Issue number 4

We continue to introduce you to the most interesting news on PostgreSQL.

Releases

PostgreSQL 10.3 Released
')
In this release, a security hole is closed: an uncontrolled path for finding objects in database schemas in pg_dump and other applications. Among other fixes: logical replication now will not attempt to transfer changes if tables are prohibited from being published. Also updated versions 9.x.

Postgres Pro Standard 10.3.1 was released the same day as PostgreSQL 10.3, as it was necessary to patch the hole as quickly as possible. Postgres Pro Standard 10.3.2 and Postgres Pro Enterprise 10.3.2 are now available. They also include TOAST support for INCLUDED attributes in index-B-trees. The pg_probackup utility has been significantly improved (now it is version 2.0.16).

pglogical 2.2

The new version of this extension, which was developed in the 2ndQuadrant , fixed the errors of the previous version;
Version 2.2 is able to re-create the replication slot when restarting replication after an error;
eliminated competition, which sometimes led to a table synchronization error;
There are some other possibilities.

You can download it here .

dbForge Studio

It is a tool for developing and managing PostgreSQL with a developed data editor, auto-completion function and user interface reminiscent of MS Visual Studio . The announcement of dbForge Studio in Postgresso # 2 has already aroused the desire of readers to try. Now available for download free version of Express . And work is already underway on the paid version - Standard .

pg_badplan

Claes Jakobsson (Claes Jakobsson) offered to try out a demo of the new extension he created, extravagantly named pg_badplan.
When executing requests, this extension monitors the work of the scheduler and compares its estimate with the number of actually delivered records. If the ratio exceeds the specified threshold, pg_badplan reports this to the log.

The commit fest continues - the most important this year, since the patches that have not passed it will no longer fall into version 11. The accepted patches can be tracked here . On 23.03.2018, 69 out of 252 were accepted. Among those that fall into version 11:

• In pgbench , the \ if construct and a general-purpose hash function appeared.
• When partitioning, UPDATE of the partitioning key column is now possible; You can create unique indexes. Aggregation and grouping in partitioned tables is likely to appear in version 11, but for now, for review;
• INOUT parameters in procedures.

Courses

DBA in Tver

On March 26, an open educational course for developers of server applications DEV1 started in Tver at the Research and Development Institute of Information Technologies . Development of server-side applications PostgreSQL 9.6. Basic course .
The course is conducted by the managers of Postgres Professional educational programs, Egor Rogov and Pavel Luzanov . Last lesson is March 29th.

Conferences and meetings

in Russia:

DevConf 2018

This year's conference will be held May 18-19 in Moscow in Digital October.

Highload ++ Siberia
This year, Highload ++ will be held at the Novosibirsk Expo Center on June 25 and 26.

Rit
The Russian Internet Technologies Festival 2018 and all its conferences will be held in Moscow on May 28 and 29 at the Skolkovo School of Management

Ya.Subbotnik . Mitap
It will be March 31 in St. Petersburg.

outside Russia:

PGConf APAC 2018 . It took place in Singapore on March 22-23.

German-speaking PostgreSQL. The conference will be held in Berlin on April 13.

PGConfNepal 2018 is scheduled for May 4-5 in Nepal at the University of Kathmandu.

PGCon 2018 in Ottawa will be held May 29 - June 1.

The 2018 Swiss PGDay will be held in Rapperswil, near Zurich on June 29th. Applications are accepted until April 14, registration until June 28.

PGConf.Brazil 2018 will be held in San Paolo on August 3-4.

Articles and blogs

The 2018 StackOverflow survey for PostgreSQL.
More than 100,000 SO respondents surveyed their feelings for various DBMSs. PostgreSQL is your favorite! More precisely, one of the two most beloved ones: Redis (for 64.5% of respondents) and PostgreSQL (62%), but since Redis is certainly not a universal DBMS, PostgreSQL is definitely the favorite DBMS among those with whom it competes. IBM DB2 and Oracle lead the Most Dreaded category.

SQL / JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL
Oleg Bartunov writes in his LiveJournal blog that in terms of compliance with Oracle 18c, MS SQL Server 2017 and MySQL 8.0.4 to SQL / JSON Standard-2016, PostgreSQL is undoubtedly ahead. The proof is a correspondence matrix. PostgreSQL 11 in this matrix is ​​supplemented with 3 patches, which were started by Nikita Glukhov and Oleg Bartunov a couple of months after the publication of the standard: SQL / JSON: jsonpath, SQL / JSON: functions and SQL / JSON: JSON_TABLE

Using EclipseLink with PostgreSQL
The 2ndQuadrant blog has two articles on ORM in Java. The first is about EclipseLink , the second is called
Using Java ORMs with PostgreSQL - MyBatis
and explains the fundamental difference in the approaches of these two ORMs.

Three reasons why VACUUM won't remove dead rows from a table
In this article by Lorenz Alba , there are examples in the Cybertech blog. For each problem situation, an exit is proposed:
for prolonged transactions, use the pg_terminate_backend () function to terminate a session that blocks VACUUM;
- unused replication slots - you can get rid of unnecessary replication slots with the pg_drop_replication_slot () function;
Prepared Orphan Transactions — Use ROLLBACK PREPARED SQL to remove these prepared transactions.

What PostgreSQL Full-Text-Search has to do with VACUUM
In his blog, Hans-Jürgen Schönig , also from Cybertch, writes about how GIN indices work, how to measure the impact of VACUUM on performance, how VACUUM can speed up full-text search (FTS) in PostgreSQL. Also with examples.

Send your ideas and wishes to email: news_channel@postgrespro.ru
Previous issues: # 3 , # 2 , # 1