Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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 <tablename> SET LOGGED;
Set a logged table to unlogged:
 ALTER TABLE <tablename> 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 the
presence of locked rows: instead of causing a process to block waiting
for 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 not
appropriate behavior for general purposes, there are some cases in which
it is useful, such as queue-like tables.
 
Catalog version bumped because this patch changes the representation of
stored 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;
BEGIN
psql-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.


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL