POSTGRESSQL 9.3: NEW FUNCTIONALITY

We recently released PostgreSQL 9.3, and we have already heard a lot of good things about it. The recent accomplishments have left a lot of people in awe, and they are already eager to use the new features. As usual, the new release includes a ton of brand-new features and numerous enhancements.

Each person has a favorite new feature. For me, these standout qualities are:

• decreased use of shared memory
• fabricated viewpoints
• automatic updating view
• assistance with event triggers
• Typed FDWs

decreased use of shared memory

In recent years, kernel-side shared memory settings have caused problems for many new users of PostgreSQL, Linux, and UNIX. All of the I/O cache was housed in shared memory prior to PostgreSQL 9.3. The issue here was that most Linux distributions (and most other UNIX systems) only allowed a small amount of RAM to be used for shared memory.

The following message might be well-known to a lot of users:

FATAL: Could not create a 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 a 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’m happy to report that those messages are no longer being sent. This will greatly lessen the discomfort and make learning PostgreSQL easier for beginners.

fabricated viewpoints

A request that has been made frequently is for views that can be materialized. Actually, having a materialized view is cool. It enables the pre-processing of data storage. In reality, a “materialized view” is an object in the database that stores the results of a query.

Here’s an illustration:

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 by storing the result of the query:

test=# CREATE MATERIALIZED VIEW mat_test AS SELECT sum(x) FROM t_test; SELECT 1

Now, you can get to the materialized view in your PostgreSQL database the same way you would get to any other table:

test=# SELECT * FROM mat_test; sum ----- 55 (1 row)

Just remember that a materialized view is read-only. It is impossible to genuinely add a row to it. If you try: PostgreSQL will produce an error.

test=# INSERT INTO mat_test VALUES (10); ERROR: cannot change materialized view "mat_test"

The materialized view will remain unchanged even if the underlying table is changed, of course:

test=# INSERT INTO t_test VALUES (100); INSERT 0 1 test=# SELECT * FROM mat_test; sum ----- 55 (1 row)

The materialized view will be updated if REFRESH is called.

test=# \h REFRESH Command: REFRESH MATERIALIZED VIEW Description: Replace the contents of a materialized view. Syntax: "Refresh Materialized View" [ WITH [ NO ] DATA ] test=# REFRESH MATERIALIZED VIEW mat_test WITH DATA; REFRESH MATERIALIZED VIEW

PostgreSQL will execute the query once more, updating the data in the materialized view:

test=# SELECT * FROM mat_test; sum ----- 155 (1 row)

Materialized views allow for the reasonably comfortable storage of pre-aggregated data.

automatic updating views

Not all of PostgreSQL’s new features are related to materialized views. “Normal” views also received a very nice makeover. Simple views can be directly updated in PostgreSQL. Previously, in order for a view to modify the underlying tables, a trigger had to be created. It took a lot of work and discomfort to complete this, even if it were done for many viewers. “Simple” views can now be directly updated in PostgreSQL 9.3.

The process is as follows:

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)

Through the view, our data is directly entered into t_test. Once more, this lessens the workload for developers in many situations.

assistance with event triggers

So-called “event triggers” are another feature that a lot of people have been waiting for. Up until this point, only triggers firing on INSERT, UPDATE, DELETE, and TRUNCATE could be written. You can write a trigger firing on any event, such as CREATE TABLE, ALTER TABLE, and so on, using event triggers. Because newly created tables can be added to replication automatically for the first time without having to add them manually, event triggers will be a REAL relief for many logical replication solutions like Slony and Londiste.

The syntax for CREATE EVENT TRIGGER is as follows:

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()

Event triggers must be written in C as of version 9.3, but I anticipate that will change in the future.

Writing FDWs

The capability to ACTUALLY INSERT DATA into a foreign table is another significant improvement. Only reading a foreign table was possible prior to PostgreSQL 9.2. Many users found this to be a serious issue because it restricted their ability to use a foreign table similarly to a regular table.

Numerous additional enhancements

Of course, the features I’ve listed here are just a quick rundown of the most significant updates in PostgreSQL 9.3. There are countless additional changes to the code that make PostgreSQL much easier to use.

Keep checking back for more PostgreSQL updates.