Transaction control in PL procedures
A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018.
With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate:
CREATE TABLE test1 (a int);
CREATE PROCEDURE transaction_test1()
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
Having a unified view when combining data from different sources is essential for every business. Whether you want to connect to a cloud application, import/export data to/from a text file, or sync data between your datasource and a 3rd party platform - having a database platform that provides a seamless and effective data integration process is critical.
This week on Tuesday, 13 February, 2ndQuadrant hosted the second webinar in their PostgreSQL webinar series. Principal Consultant, Shaun Thomas, conducted the webinar, covering various topics around integration of PostgreSQL with other data sources and database technologies.
The recording of the Webinar is now available. Those who couldn’t make it to the live session can now view Data Integration with PostgreSQL here.
OmniDB was designed to allow users to manage multiple databases from different RDMBS within an unified workspace. The philosophy is to use the same familiar web interface (same structures, icons, context menu and handy features such as SQL Autocomplete, Create/Edit Table Form and Edit Data) so the user feels comfortable and confident to manage different systems as if they are just different connections from the same system.
OmniDB 2.5.0 introduces basic support to Oracle databases. Users can connect and interact with Oracle databases the same way the tool already does for PostgreSQL databases. Oracle versions 11g and 12c are supported.
Let's connect to Oracle databases
As you can see below, I already have some OmniDB connections. Now let's connect to Oracle right away, click on New
OmniDB 2.4.0 introduces a new cool feature called Monitoring Dashboard. We know a picture is worth a thousand words, so please take a look:
As you can see, this is a new kind of inner tab showing some charts and grids. This Monitoring inner tab is automatically opened once you expand the tree root node (the PostgreSQL node). You can keep it open or close it at any time. To open it again, right-click the root node and click on Dashboard.
The dashboard is composed of handy information rectangles called Monitoring Units. Currently there are 3 types of Monitoring Units:
Grid: The most simple kind, just executes a query from time to time and shows the results in a data grid.
Chart: Every time it refreshes, it renders a new complete chart. The old set of values is
The PostgreSQL Developer Meeting went well with attendees from UK, Czechia, US, Russia and Germany. The meeting is in Brussels, near the FOSDEM conference.
Andreas Seltenreich was the new face at the meeting. Andreas' sqlsmith tool has found many PostgreSQL bugs, so we discussed extending that for the latest features in PostgreSQL 11.
We discussed all of the patches in the queue and moved a few forwards quickly. With more than 200 patches in the queue there is much work still to do and the last Commitfest hasn't even started yet.
Bruce was able to commit a patch to improve the usability of psql with commands exit and quit. Finally the usability annoyance of people not remembering "\q" command ends in 2018!
I've taken on shepherding the new WAIT FOR command which should allow us
Unless you were in Sydney for linux.conf.au 2018 last week you probably didn't see my talk Geographically distributed multi-master replication with PostgreSQL and BDR. Luckily for you it's on archive.org and YouTube.
If you're interested in multi-master replication of any sort, even non-PostgreSQL-based multi-master, it should be worth taking a look. The talk could've been better titled "Understand Multi-Master and if it's right for you" or "Physics is Mean".
Comments and questions are welcome - @craig2ndq.
You can also just
My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It's worth explaining what these are rather than trying to piece together what is happening from reading commit messages.
The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.
To achieve that, we need to understand the structure of features in PostgreSQL.
Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.
Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I'll write about partitioned indexes
Backups are a critical component to a fully covered Postgres database infrastructure. In some ways, it's fair to say a database without a backup is no database at all---sometimes literally. 2ndQuadrant's Barman tool is aptly named as a Backup And Recovery Manager for Postgres, and it exists primarily for encouraging a stable and robust backup process.
Backing up a database and then restoring from that backup often has an army of associated scripts and utilities. Some of these components are probably the Postgres default tools of pg_dump, pg_restore, and pg_basebackup. The rest are often shell scripts, homegrown or otherwise copied from blogs or git repositories. Let's just assume the best case scenario and assume all of these work just fine; I used to think the same thing myself.
Spectre and Meltdown have caused severe alarm in recent days. You may have read about up to 30% impact on PostgreSQL databases, which I believe to be overstated because of misunderstandings in the media. Let's dig into this in more detail.
TL;DR Summary: no PostgreSQL patch required, -7% performance hit
In response to these new security threats various OS patches have been released. Various authors have published benchmarks around these and they have, in some cases, stated worst-case measurements as impact measurements. For example: stating a 30% hit when, in fact, we are seeing a 7% hit on a busy server. Regrettably, it looks to me like some people outside the PostgreSQL community have spread this news as a problem for PostgreSQL, without clearly stating the workload measured, or
The annual DBEngines ranking have just been updated, with PostgreSQL winning the "DBMS of the Year 2017" award.
PostgreSQL was a runner up last year.
So across the year, we have made more gains in popularity than all other databases. Looking at the detail we see that all of the other 4 databases that make up the Top 5 have reduced in popularity from last year. PostgreSQL has gone up, others have gone down.
What's even more interesting, is that PostgreSQL is the only database in the top 5 systems that has increased steadily over the last 4 years.
Slow, steady progress. The word is out!
Why is that? PostgreSQL is multi-talented, offering relational features when needed, as well as JSON features for