In PostgreSQL 9.6 I have a table
T like this
category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | whatcomesafterfoobarbaz
There is a view
V giving me the data for
T, so it has columns
category, id, data.
T is essentially the materialized view for
V, except that I need to refresh it with more granularity than "refresh everything".
So I will select from
V for example
SELECT * FROM V WHERE category = 'A';
SELECT * FROM V WHERE category = 'A' AND id = 2;
And replace the relevant rows in
T with whatever
V gives me. Unfortunately I cannot do a simple
V eg. for
WHERE category = 'A' might give me a totally different set of rows than before. Therefore I need to do this sequence:
DELETE FROM T WHERE <condition>; INSERT INTO T (SELECT FROM V WHERE <condition>);
<condition> is either
WHERE category = ? or
WHERE category = ? AND id = ?.
How do I do this so that the following conditions hold?
- Reads from rows not satisfying
<condition>should be unaffected.
- The change should be atomic, meaning reads from rows satisfying
<condition>should either see the the old row set or the new row set, not a mix.
Note: unlike this question, I don't want to replace the whole table at once - only the rows affected.
There are more reads than writes, on the order of 10-100 times more. After each write there will be a read to the adjacent categories. The application is looking at a set of
dataand updates the
datafor one or more
categoriesat a time. Right after it will re-fetch those
categoriesand display them, and it must see the fresh
data. All the
ids are always fetched with "their"
categorywill have something like 1-10
ids, there will be tens of thousands of
More details after first answer
Transactions can run concurrently. There can definitely be a case when two transactions start with
DELETE FROM T WHERE category = 'A';.
There is a table
categorieswhere it's possible to lock rows
FOR UPDATE. There also is a table where
ids can be locked
RETURNINGmakes not much sense here as I need to fetch more than just the rows changed. Thus all is simpler with a separate