PostgreSQL lock table not supported yet

[mckeeh3]

Jira Link: DB-2468
Execute statement lock table blah in exclusive mode fails: Exception "ERROR: EXCLUSIVE not supported yet"

[rkarthik007]

Hi @mckeeh3, thanks for opening this issue, this is something we've been discussing as well.

Just wanted to understand your usage scenario for table level locks - could you please describe why/when you're looking to lock some tables? This is more for us to understand.

[Bessonov]

@rkarthik007 run in the same issue. I've same intent like described here:

        -- prevent concurrent inserts from multiple transactions
        LOCK TABLE photos IN EXCLUSIVE MODE;

I'm exploring hasura with yugabyte and the example use case is to limit the count of wheels an every car can have. I've put transactions in serializable isolation level, but not sure that helps to avoid need of exclusive lock mode.

[Bessonov]

Bessonov unassigned ndeodhar now

I'm not sure what happens here 8o

[rkarthik007]

I'm exploring hasura with yugabyte and the example use case is to limit the count of wheels an every car can have. I've put transactions in serializable isolation level, but not sure that helps to avoid need of exclusive lock mode.

@jaki @m-iancu - can @Bessonov achieve the above with something like:
BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE

Bessonov unassigned ndeodhar now

I'm not sure what happens here 8o

Haha no issues, we've changed the process a bit to leave issues we're not actively working on unassigned.

[jaki]

@rkarthik007, DEFERRABLE is useful for selecting lots of data, and I don't
think that's the case here.

@Bessonov, the link you had uses LOCK TABLE, and that still may not be the
best solution since you're preventing other concurrent writes to photos whose
owners are different from the owner we're checking. If it's not a critical
workload, then the LOCK TABLE should be fine.

Since we don't support it, what you can do instead is SELECT * FROM photos FOR UPDATE. This can be costly depending on how big the photos table is.

I have come up with this. It takes advantage of the fact that we select count
by row-locking them. This consequently takes a weak lock on the table, so it
conflicts with inserts to unrelated owners, but that would have been the
behavior anyway if you used LOCK TABLE.

CREATE TABLE photos (data int, owner oid); -- data is int for demo convenience

CREATE FUNCTION check_photos() RETURNS trigger AS $$
  DECLARE
    max_num_photos int := 10;
    must_check boolean := false;
    num_photos int;
  BEGIN
    IF TG_OP = 'INSERT' THEN
      must_check := true;
    END IF;
    IF TG_OP = 'UPDATE' THEN
      IF (NEW.owner != OLD.owner) THEN
          must_check := true;
      END IF;
    END IF;
    IF must_check THEN
      SELECT COUNT(*) FROM (SELECT *
                            FROM photos
                            WHERE owner = NEW.owner
                            FOR UPDATE NOWAIT) AS owner_photos INTO num_photos;
      IF (num_photos > max_num_photos) THEN
        RAISE EXCEPTION 'Too many photos % for owner %', num_photos, NEW.owner;
      END IF;
    END IF;
--  PERFORM pg_sleep(4); -- debug concurrency
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_photos_trigger
  AFTER INSERT OR UPDATE ON photos
  FOR EACH ROW EXECUTE FUNCTION check_photos();

INSERT INTO photos VALUES (generate_series(1, 9), 13);
INSERT INTO photos VALUES (generate_series(10, 11), 13); -- error
INSERT INTO photos VALUES (100, 13);
INSERT INTO photos VALUES (101, 13); -- error

You should be able to modify this to fit you cars + wheels example.

The other idea is to take this into your own hands and manually check the
counts inside a transaction, something like

• BEGIN
• INSERT
• SELECT COUNT and verify it
• COMMIT

But you'd need to find every insert and wrap it like this.

[Bessonov]

Wow @jaki @rkarthik007 thank you very much for the great support! You're right, FOR UPDATE is a better solution.

BTW, it was a great event today.

[rkarthik007]

BTW, it was a great event today.
Glad you liked it @Bessonov!

[ilourt]

Any news about when SHARE UPDATE EXCLUSIVE mode for lock will be supported? Without this yugabyte can not be used with Phoenix/Ecto (Elixir), it would be sad to not use Yugabyte just because of this.

Thanks for all your work.

[dmagda]

The issue still exists for Elixir + Phoenix. Presently, as a workaround we have to use the migration_lock: false setting.
https://docs.yugabyte.com/preview/drivers-orms/elixir/phoenix/