1

I have a table which is

event_id BIGINT NOT NULL,
data BYTEA

and a column in this table is

 event_id |        data
----------+--------------------
        2 | \x0000000000000000

Now, I want to update a slice of data like

UPDATE "bin_data" SET "data"=SET_BYTE("data", 0, 1) where "event_id"=2;

But, I'm not sure this option can concurrently or not.

If those two options

UPDATE "bin_data" SET "data"=SET_BYTE("data", 0, 1) where "event_id"=2;
UPDATE "bin_data" SET "data"=SET_BYTE("data", 1, 1) where "event_id"=2;

are executed parallelly.

In my test, the result is \x0101000000000000, but I can't guarantee that my test is absolute parallelly.

I have an idea of how to solve it which is lock the row like this

UPDATE "bin_data" SET "data"=SET_BYTE((SELECT "data" FROM "data" WHERE "event_id"=2 FOR UPDATE), 0, 1) where "event_id"=2;

Is there any way to update a slice of bytea field parallelly without lock the row?

fuxiuyin
  • 151
  • 1
  • 10
  • Unrelated, but: the `select` inside the function call is completely useless. You can simply use `set data = set_byte(data)`. And yes: every update is atomic. –  Mar 19 '18 at 07:28
  • @a_horse_with_no_name You are right, every update is atomic, I think I should edit my question to "Can I update a slice of bytea field parallel". – fuxiuyin Mar 19 '18 at 08:07
  • 1
    A single column is a single value regardless on how your application treats it. You can not update the same column of the same row concurrently from two different transactions. If you need to do that, you might have the wrong database design. –  Mar 19 '18 at 08:09
  • The row lock (using `FOR UPDATE) in the sub-select is unnecessary because it locks the row that the UPDATE will lock anyway. –  Mar 19 '18 at 08:27
  • @a_horse_with_no_name I'm doing a monitor application which monitors many hosts, one host has many panels like CPU, memory. I need to store one point per minute for every panel, and in the webpage a user can check each panel in minute, hour or day. The number of point is too large, so I think I should store multi points in one field. – fuxiuyin Mar 19 '18 at 08:36

0 Answers0