New ‘grazy’ procedure in PostgreSQL 11

The latest update to PostgreSQL has once again added a number of fine new features to the popular open-source database. In addition to a number of nice features that make the DBA’s job easier, the hearts of database users will especially beat faster because of one particular update. The update that has been awaited for years. *Trumpet*: PostgreSQL 11 features a real stored procedure!

PostgreSQL adds new useful features in every update, which make the DBA’s work easier and clearer. For example, PostgreSQL 9.6 already added the role pg_signal_backend, which you could grant to users so they could cancel or terminate their (too) long-running queries. In PostgreSQL 10, the roles pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables and pg_monitor were added, and in PostgreSQL 11, the number of roles was expanded again. Combined with these already existing roles, the new roles make it possible for a DBA to give controlled application users or other users structured access to “your” database.

Gijsjan Baars

DBA Consultant en Senior Database Reliability Engineer

Telefoonnummer E-mailadres

At last: a real stored-procedure

Yes, there are again a lot of improvements in PostgreSQL 11. In addition to the access control improvements mentioned above, partitioning, parallelism and WAL files have been addressed. But the real progress that all database users were waiting for is, of course, the stored procedure. You read correctly: we can now finally use real stored procedures in PostgreSQL!

Previously, a stored procedure in PostgreSQL was nothing more than a function, but from version 11 on, we can call the stored procedure with CALL {function name} instead of SELECT * FROM … and that offers great advantages. For example, you can now use transaction control within a stored procedure, so we can now perform a commit and a rollback within a procedure. The languages supported here are: PL/pgSQL, PL/Perl, PL/Python, PL/Tcl and SPI.

Exit!

And there are other cool new features, such as JIT Compiled Queries. Indeed, compiling some queries to machine code can improve performance, according to benchmarks by CitusData and others.

Finally, another handy feature has been implemented in PostgreSQL 11. Previously, you could only get out of the psql command line by typing \q or via the key combination CTRL+D. But like me, how often have you typed ‘exit’ when you switch between the Linux prompt and the psql prompt a lot?

However, typing ‘exit’ or ‘quit’ didn’t accomplish anything until recently, apart from an oh yes annoyance. That’s a thing of the past from now on, because in PostgreSQL 11 you can now exit the psql prompt with ‘exit’ and ‘quit’. It’s not a big change, but I’m happy with it!

Want to know more about PostgreSQL 11?

The Netherlands has a growing and vibrant PostgreSQL community. For example, the PostgreSQL Usergroup NL regularly organizes meetups in the Netherlands. To meet, strengthen and share knowledge.

Would you like to know more about the possibilities and/or new features of PostgreSQL 11?

Join the meetup group and visit our meetups.

Or take a look via the links below to previous blogs and information page about the PostgreSQL platform, its management and support or feel free to contact us.