Allow only one reader to a table

Question

I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again.

However due to multiple worker/threads they many times read simultaneously. I want that only one reader should be allowed at a time and then update the column value.

select port from socketport where available = 't'
update socketport set available = 'f' where port = port (from above)

Can above select statement be locked and then updated without releasing lock?

Answer 1

Edited: look at the last example is a better way

Yes, but you must send the two sentences within a transaction and add the "for update" clause in the select sentence.

Python

If you send then sentences one by one you can write something like this (in Python the connection handles transactions)

conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
cur = conn.cursor()
cur.execute("select port from socketport where available = 't' limit 1 for update")
row = cur.fetchone()
port = row[0]
cur.close();
cur = conn.cursor()
cur.execute("update socketport set available = 'f' where port = %s", (port))
cur.close();
conn.commit();

node.js

If your language does not support transaction you must include manually:

const { Client } = require('pg')
const client = new Client(connOpts)
try{
  await client.query("BEGIN TRANSACTION");
  var result = await client.query("select port from socketport where available = 't' limit 1 for update");
  var port = result.rows[0].port;
  await client.query("update socketport set available = 'f' where port = $1", [port]);
  await client.query("commit");
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}    

pure postgresql

You can use direct postgres. Any block is within transaction

do $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
end;
$SQL$;

looking for a better solution

using a function

You can do something like this. A postgres function that does the job and returns the port, then you can call the function from python (or whatever you want).

create or replace function get_avaialbe_port() returns integer
  language plpgsql
as $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
  return v_port;
end;
$SQL$;

then in your backend you can call:

select get_avaialbe_port()

All code inside a function runs inside a transaction. The "for update" is stil needed, because without it two functions can run simultaneously and can take the same value of v_port prior to the update statement.

only one sentence

Also you can update the selected value in one sentence

update socketport 
  set available = 'f'
  where port in (select port from socketport where available = 't' limit 1)
  returning port;

You can call it directly from python or node.js. You can see a postgres running example at: http://rextester.com/OKMC68108

UPDATE sentence with RETURNING clause acts as SELECT you can query it and fetch the values. UPDATE ... RETURNING is like a SELECT that updates.

NOTES

Watch at LIMIT 1 in the examples. In the postgres version it is mandatory because of SELECT INTO. In python and node.js version it is recommended because in that way only one row is locked. Then the table can be updated with releasing ports or taking others using some kind of "order by random"

Answer 2

I found the proper way in python is through transaction. For that commit needs to be made after both select and update also for update in select is necessary, otherwise it won't work. conn.commit() or cursor.execute('commit') any can be called to commit the transaction.

try:
    conn = psycopg2.connect(database='tanmay_db', user='xx', host='xx', port='5432', password='xx')
    cur = conn.cursor()
    cur.execute("""select port from ports where is_available = 't' for update""")
    row  = cur.fetchone()
    port = row[0]
    cur.execute("""update ports set is_available='f' where port = %s"""%port)
    cur.execute('commit')
    #conn.commit()
    cur.close()
except psycopg2.DatabaseError as e:
    print(e)
finally:
    if conn is not None:
        conn.close()

You can also close the cursor after select statement and re-open it again. but commit necessarily needs to be made after both the statement, If commit is ignored select would always return the same port.