PostgreSQL news digest. Issue No. 4
We continue to introduce you to the most interesting PostgreSQL news.
PostgreSQL 10.3 has been released. A security hole has been closed
in this release : an uncontrolled path to search for objects in database schemas in pg_dump and other applications. Among other fixes: now logical replication will not attempt to transmit changes if tables are not allowed for publication. Also released updated versions 9.x. Version of Postgres Pro Standard 10.3.1 came out the same day as the PostgreSQL 10.3, as it was necessary to patch a 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 B-tree indexes. Utility seriously improved
pg_probackup (now this is version 2.0.16).
The new version of this extension, which was developed in 2ndQuadrant , fixes bugs of the previous version;
version 2.2 can re-create the replication slot when restarting replication after an error;
eliminated competition, which sometimes led to an error in the synchronization of tables;
there are some other possibilities.
Download here .
This is a PostgreSQL development and management tool with an advanced data editor, autocompletion function and user interface reminiscent of MS Visual Studio . Announcement of dbForge Studio in Postgresso # 2already aroused the desire of readers to try. Now a free version of Express is available for download . And work is already underway on the paid version - Standard .
Claes Jakobsson (Claes Jakobsson) offered to try out a demo of the new extension he created, extravagantly called pg_badplan.
When executing requests, this extension monitors the work of the scheduler and compares its estimate with the number of records actually delivered. If the ratio exceeds a predetermined threshold value, pg_badplan reports this to the log.
The Komitfest continues - the most important this year, since patches that did not pass it will no longer fall into version 11. Accepted patches can be tracked here. On March 23, 2018, 69 out of 252 were accepted. Among those that fall into version 11:
- In pgbench appeared design \ if and hash-function general purpose.
- When partitioning, UPDATE of the partition key column is now possible; You can create unique indexes. Aggregation and grouping in partitioned tables is likely to appear in version 11, but so far in review;
- INOUT parameters in procedures.
DBA in Tver
March 26 in Tver in the Research Institute of Information Technologies launched an open educational course for developers of server applications DEV1. Server-side development of PostgreSQL 9.6 applications. Basic course .
The course is taught by Postgres Professional program managers Egor Rogov and Pavel Luzanov . The last lesson is March 29th.
Conferences and meetings
This year, the conference will be held May 18-19 in Moscow at Digital October.
Highload ++ Siberia
This year Highload ++ will be held at the Expocenter of Novosibirsk on June 25 and 26.
Festival “Russian Internet Technologies” 2018 and all the conferences included in it will be held in Moscow on May 28 and 29 at the Skolkovo School of Management
Y. Subbotnik . Mitap.
Will be held March 31 in St. Petersburg.
outside of Russia:
PGConf APAC 2018 . Held in Singapore March 22-23.
German-speaking PostgreSQL. The conference will be held in Berlin on April 13th.
PGConfNepal 2018 is scheduled for May 4-5 in Nepal at Kathmandu University.
PGCon 2018 in Ottawa will be held May 29 - June 1.
Swiss PGDay 2018 will be held in Rapperswil, near Zurich on June 29. Applications are accepted until April 14, registration until June 28.
PGConf.Brazil 2018 will be held in Sao Paolo on August 3-4.
Articles and Blogs
The 2018 Stack Overflow survey has just been published, with good news for PostgreSQL.
Over 100,000 SO respondents interviewed for their feelings about various DBMSs. PostgreSQL is my favorite! More precisely, one of the two most beloved: Redis (for 64.5% of respondents) and PostgreSQL (62%), but since Redis is by no means a universal DBMS, PostgreSQL is certainly the most beloved DBMS among those with whom it competes. IBM DB2 and Oracle lead the Most dreaded nominations.
SQL / JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL
Oleg Bartunov in his LJ blog writes that regarding the compliance of Oracle 18c, MS SQL Server 2017 and MySQL 8.0.4 with SQL / JSON Standard-2016PostgreSQL is definitely ahead. The proof shows the correspondence matrix. PostgreSQL 11 in this matrix is supplemented with 3 patches that Nikita Glukhov and Oleg Bartunov started developing 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 Two appeared
on the 2ndQuadrant blog Java ORM articles . 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
There are examples in this Lorenz Albe article on the Cybertech blog. For each problematic situation, a solution is proposed:
for the case of protracted transactions, use the pg_terminate_backend () function to interrupt the session blocking VACUUM;
- unused replication slots - you can get rid of unnecessary replication slots by the pg_drop_replication_slot () function;
Prepared orphan transactions - Use ROLLBACK PREPARED SQL to delete these prepared transactions.
What PostgreSQL Full-Text-Search has to do with VACUUM
On his blog, Hans-Jürgen Schönig , also from Cybertch, writesabout how GIN indexes 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 ideas and suggestions to the mail: firstname.lastname@example.org
Previous issues: # 3 , # 2 , # 1