PostgreSQL 9.3 has just been released and we have already received a lot of positive feedback for the new release. Many people are impressed by what has been achieved recently and are already eager to enjoy those new features. As always, the new release brings a great deal of new functionality and many improvements.
Everybody has their favorite new functionality. In my case these top features are:
Reduced shared memory consumption
In recent years many newbies to PostgreSQL and Linux / UNIX have suffered from kernel side shared memory settings. Before PostgreSQL 9.3 all the I/O cache resided in shared memory. The problem here was that most Linux distros (and most other UNIX systems) restricted the use of shared memory to a fairly small amount of RAM.
Many users might be familiar with the following message:
|
1 2 3 4 5 6 7 8 9 |
FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5433001, size=35233792, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 35233792 bytes), reduce PostgreSQL's shared_buffers parameter (currently 4096) and/or its max_connections parameter (currently 13). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. [fail] |
I am glad to say that those messages are a thing of the past. This will significantly reduce the pain and newbies will have a better time getting started with PostgreSQL.
Materialized views
One thing many people have been asking for is materialized views. A materialized view is actually a cool thing to have. It allows people to store data in a pre-processed way. In fact, a materialized view is a database object storing the result of a query.
Here is an example:
|
1 2 |
test=# CREATE TABLE t_test AS SELECT * FROM generate_series(1, 10) AS x; SELECT 10 |
We can turn this into a shiny materialized view storing the result of the query:
|
1 2 |
test=# CREATE MATERIALIZED VIEW mat_test AS SELECT sum(x) FROM t_test; SELECT 1 |
The materialized view can now be accessed inside your PostgreSQL database just like you would access any normal table:
|
1 2 3 4 5 |
test=# SELECT * FROM mat_test; sum ----- 55 (1 row) |
Keep one thing in mind: A materialized view is read-only. There is no way to actually add a row to it. PostgreSQL will issue an error in case you try:
|
1 2 |
test=# INSERT INTO mat_test VALUES (10); ERROR: cannot change materialized view "mat_test" |
You can of course modify the underlying table – the materialized view will be unchanged, however:
|
1 2 3 4 5 6 7 8 |
test=# INSERT INTO t_test VALUES (100); INSERT 0 1 test=# SELECT * FROM mat_test; sum ----- 55 (1 row) |
To make sure that the materialized view is updated, you have to call REFRESH:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# h REFRESH Command: REFRESH MATERIALIZED VIEW Description: replace the contents of a materialized view Syntax: REFRESH MATERIALIZED VIEW name [ WITH [ NO ] DATA ] test=# REFRESH MATERIALIZED VIEW mat_test WITH DATA; REFRESH MATERIALIZED VIEW |
PostgreSQL will execute the query once again and update the content of the materialized view:
|
1 2 3 4 5 |
test=# SELECT * FROM mat_test; sum ----- 155 (1 row) |
Materialized views are a pretty comfortable way to store pre-aggregated data.
Auto-Updatable views
Materialized views are not the only area in which PostgreSQL got some new features. “Normal” views also got a pretty nice facelift. PostgreSQL allows simple views to be updated directly. Previously you had to write a trigger so that a view could change its underlying tables. This was painful and if it was done for many views, it was even a lot of work to get this done. In PostgreSQL 9.3 “simple” views can be updated directly.
Here is how it works:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# CREATE VIEW v AS SELECT * FROM t_test; CREATE VIEW test=# INSERT INTO v VALUES (0); INSERT 0 1 test=# SELECT * FROM t_test WHERE x = 0; x --- 0 (1 row) |
Our data is inserted into t_test directly through the view. Again, this reduces the amount of work for developers in many cases.
Support for event triggers
One more feature many people have been waiting for are so called “event triggers”. Up to now it was only possible to write triggers firing on INSERT / UPDATE / DELETE / TRUNCATE. With event triggers you can write a trigger firing on any event such as CREATE TABLE, ALTER TABLE and so on. Event triggers will be a REAL relief for many logical replication solutions such as Slony and Londiste because it will be possible to add newly created tables to replication automatically for the first time without having to add them manually.
Here is the syntax of CREATE EVENT TRIGGER:
|
1 2 3 4 5 6 7 8 9 |
test=# h CREATE EVENT TRIGGER Command: CREATE EVENT TRIGGER Description: define a new event trigger Syntax: CREATE EVENT TRIGGER name ON event [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ] EXECUTE PROCEDURE function_name() |
As of 9.3 there is just one issue: Event triggers must be written in C, but I assume that this will change in the future.
Writable FDWs
One more major enhancement is the ability to actually INSERT data into a foreign table. Up to PostgreSQL 9.2 it was only possible to read a foreign table. To many users this was a serious issue because it limited the ability to use a foreign table just like a normal table.
Countless other improvements
Of course the features I have presented in this post are just a brief summary of the most important new features of PostgreSQL 9.3. There are countless more improvements in the code which help people to use PostgreSQL a lot more efficiently.
There is still more to come from PostgreSQL – stay tuned.
+43 (0) 2622 93022-0
office@cybertec.at