PostgreSQL ALTER TABLE ... SET LOGGED / UNLOGGED

PostgreSQL allows one to create tables which aren't written to the Write Ahead Log, meaning they aren't replicated or crash-safe, but also don't have the associated overhead, so are good for data that doesn't need the guarantees of regular tables. But if you decided an unlogged table should now be replicated, or a regular table should no longer be logged, you'd previously have to create a new copy of the table and copy the data across. But in 9.5, you can switch between logged and unlogged using a new command:

Set an unlogged table to logged:

 ALTER TABLE  SET LOGGED;

Set a logged table to unlogged:

 ALTER TABLE  SET UNLOGGED;

For example:

 # CREATE UNLOGGED TABLE messages (id int PRIMARY KEY, message text);

 # SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged'  WHEN 'p' then 'logged'  ELSE 'unknown' END AS table_type FROM pg_class WHERE relname  'messages*';  relname| table_type ---------------+------------ messages  | unlogged messages_pkey | unlogged  (2 rows)

Note that setting an unlogged table to logged will generate WAL which will contain all data in the table, so this would cause a spike in replication traffic for large tables. And now we change it to a logged table:

 # ALTER TABLE messages SET LOGGED;

And the result of the previous query is now:

  relname| table_type ---------------+------------ messages  | logged messages_pkey | logged  (2 rows)

PostgreSQl 9.5 – IMPLEMENT SKIP LOCKED FOR ROW-LEVEL LOCKS

This clause changes the behavior of SELECT locking clauses in thepresence of locked rows: instead of causing a process to block waitingfor the locks held by other processes (or raise an error, with NOWAIT),SKIP LOCKED makes the new reader skip over such rows.  While this is notappropriate behavior for general purposes, there are some cases in whichit is useful, such as queue-like tables. Catalog version bumped because this patch changes the representation ofstored rules.

This is great. Some time ago I wrote about picking tasks to work on, from a queue. To make it work sensibly, we had to use advisory locks. This is not a big problem, but it's something that most people don't use a lot, so it's very likely that it will get skipped when searching for solutions.
We have ability to lock rows, but never had the ability to just ignore whatever is locked and proceeding.
Now, finally, we can write simple queuing system without advisory locks, and one that performs great.
Let's see a test case:
create table jobs ( id serial primary key, priority int4 not null, payload text);CREATE TABLE insert into jobs (priority, payload) select random() * 100, 'payload #' || i from generate_series(1,100) i;INSERT 0 100
Data looks like:
select * from jobs limit 10; id | priority | payload ----+----------+-------------  1 | 51 | payload #1  2 | 88 | payload #2  3 | 65 | payload #3  4 | 76 | payload #4  5 | 87 | payload #5  6 | 90 | payload #6  7 | 91 | payload #7  8 | 65 | payload #8  9 | 72 | payload #9 10 | 48 | payload #10(10 rows)
Assuming we want to process tasks based on their priority (higher priority = first to process), getting single task would be:
select * from jobs order by priority desc, id asc limit 1; id | priority | payload ----+----------+------------- 67 | 99 | payload #67(1 row)
Since we don't want to have the same job handled by many workers, we should lock it. For example like this:
psql-1: $ begin;BEGIN psql-1: *$ select * from jobs order by priority desc, id asc limit 1 for update; id | priority | payload ----+----------+------------- 67 | 99 | payload #67(1 row)
But now, other psql sessions can't get any row, as the select … for update, will lock – waiting for psql-1 to finish its transaction.
Of course, we could have added “NOWAIT", but this is not really a solution:
psql-2: $ begin;BEGINpsql-2: *$ select * from jobs order by priority desc, id asc limit 1 for update nowait;ERROR:  could not obtain lock on row in relation "jobs"
We got error, transaction got rolled back, not good.
But, with the new, great patch, we can:
psql-2: $ begin;BEGIN psql-2: *$ select * from jobs order by priority desc, id asc limit 1 for update skip locked; id | priority | payload----+----------+------------- 94 | 99 | payload #94(1 row)
and even more:
psql-3: $ begin;BEGIN psql-3: *$ select * from jobs order by priority desc, id asc limit 1 for update skip locked; id | priority | payload ----+----------+------------- 91 | 98 | payload #91(1 row)
This is great. Of course this leads to simple question – what will happen if there are no unlocked rows?
psql-4: $ with x as (select id from jobs for update skip locked) delete from jobs where id in (select id from x);DELETE 97 psql-4: $ begin;BEGIN psql-4: *$ select * from jobs order by priority desc, id asc limit 1 for update skip locked; id | priority | payload ----+----------+---------(0 rows)
All great. No error, no problem, just empty result set – after all, all jobs are being worked on.