Dear PostgresQL experts,
I thought I understood transactions until I found this problem. I'm
sure it must be a common situation. Can someone point me in the right
direction?
I have a simple table that records which flags are set for various
objects:
create table obj_flags (
obj_id integer,
flag text,
primary key (obj_id, flag)
);
In my application, I have a function that wants to set flag F for a set
of objects. First pseudo-code looks like this:
set_flag ( set<obj> s ) {
SQL "begin;"
foreach obj in s {
SQL "insert into obj_flags values (" obj.id ", 'F');"
}
SQL "end;"
}
But the flag may already be set for some of the objects. This is fine
as far as the application is concerned, but I get database errors
objecting to the duplicates. Any error causes the entire transaction to
fail.
I thought this would be simple to fix, but I'm almost stuck. Things
I've tried:
- I looked for some sort of "INSERT OR IGNORE" command that will
silently do nothing if the row already exists.
- I looked for a way of detecting and then clearing the error condition,
but there isn't one.
- I looked for a way of changing the transaction semantics so that it
would commit the results of those commands that did succeeded unless I
told it to ROLLBACK, but this seems impossible.
- I considered starting a new transaction after each error:
set_flag ( set<obj> s ) {
start:
SQL "BEGIN;"
foreach obj in s {
SQL "insert into obj_flags values (" obj.id ", 'F');"
if previous command failed {
SQL "ROLLBACK;"
s.remove obj
goto start
}
}
SQL "end;"
}
but it looks like it will increase the computational complexity from
O(sizeof(s)) to O(sizeof(s)^2), which is not great.
- I considered doing an explicit test for existence before each insert:
set_flag ( set<obj> s ) {
SQL "begin;"
foreach obj in s {
SQL "select * from obj_flags where obj_id = " obj.id " and flag='F'"
if tuples returned = 0 {
SQL "insert into obj_flags values (" obj.id ", 'F');"
}
}
SQL "end;"
}
but this requires TRANSACTION ISOLATION LEVEL SERIALIZEABLE in case
another connection adds the same flag between my SELECT and INSERT.
- I can't now see a difficulty with DELETEing any existing flag and then
inserting unconditionally, but I may have found an objection to this
previously:
set_flag ( set<obj> s ) {
SQL "begin;"
foreach obj in s {
SQL "delete from obj_flags where obj_id = " obj.id " and flag='F'"
SQL "insert into obj_flags values (" obj.id ", 'F');"
}
SQL "end;"
}
Surely this should be straightforward. Can someone please point out the
error of my ways?
Many thanks in advance for your help.
Regards,
--Phil Endecott.
