CODEWITHSUNDEEP
postgresqlltree
Travis
Travis

Reputation: 51

Is it possible to create a unique constraint that includes the entire path of an ltree field?

I'm using the ltree extension in Postgres to manage paths for a tree. I want to ensure that the name field is unique for any given path on the tree. Can I accomplish this using a constraint or do I have to build this into the query?

  demo table
------------------------------
| name (text) | path (ltree) |
------------------------------
| a           | 1            |
------------------------------
| b           | 1.2          |
------------------------------
| b           | 1.3          |
------------------------------
| b           | 1.2.4        | <-- this should fail on insert
------------------------------

If I have to build it into the query, does this potentially have a race condition if I use READ COMMITTED?

CREATE TABLE demo (
    name text NOT null,
    path ltree NOT null
);
CREATE INDEX path_gist_idx ON demo USING GIST (path);
INSERT INTO demo (name, path)
SELECT 'a', '1'
WHERE NOT EXISTS (
    SELECT * FROM demo WHERE name = 'a' AND (path <@ '1' OR path @> '1')
);
INSERT INTO demo (name, path)
SELECT 'b', '1.2'
WHERE NOT EXISTS (
    SELECT * FROM demo WHERE name = 'b' AND (path <@ '1.2' OR path @> '1.2')
);
INSERT INTO demo (name, path)
SELECT 'b', '1.3'
WHERE NOT EXISTS (
    SELECT * FROM demo WHERE name = 'b' AND (path <@ '1.3' OR path @> '1.3')
);
INSERT INTO demo (name, path)
SELECT 'b', '1.2.4'
WHERE NOT EXISTS (
    SELECT * FROM demo WHERE name = 'b' AND (path <@ '1.2.4' OR path @> '1.2.4')
);
SELECT * FROM demo;

Upvotes: 5

Views: 569

Answers (3)

Remco Goyvaerts
Remco Goyvaerts

Reputation: 101

You could create a separate table with stores the name and a path

-- this table is equivalent to the demo table
CREATE TABLE record_table (
    title text NOT null
);
-- this table holds the titles
CREATE TABLE title_table (
    name text NOT null,
    path ltree NOT null,
);

and create a parallel safe function which you can call while inserting into the original table

CREATE OR REPLACE FUNCTION verify_unique_title(title text, tree_path ltree) 
RETURNS TEXT AS '
  DECLARE
      duplicate_path ltree;
  BEGIN
      SELECT path 
       FROM title_table 
       INTO duplicate_path 
       WHERE name=title AND path <@> tree_path;
      IF duplicate_path IS NOT NULL 
       THEN raise exception ''title was not unique''; 
       END IF;
      INSERT INTO title_table (name, path) VALUES (title, tree_path);
      RETURN title;
  END;
' LANGUAGE plpgsql PARALLEl UNSAFE;

-- these inserts should work
INSERT INTO record_table (title) VALUES (verify_unique_title('a', '1'::ltree));
INSERT INTO record_table (title) VALUES (verify_unique_title('b', '1.2'::ltree));
INSERT INTO record_table (title) VALUES (verify_unique_title('b', '1.3'::ltree));

-- the following should fail
INSERT INTO record_table (title) VALUES (verify_unique_title('b', '1.2.3'::ltree));

You'd have to implement similar triggers for updating and deleting items if you want to maintain the separate index.

Upvotes: 2

Edouard
Edouard

Reputation: 7065

An other solution could be to test the new path in a trigger function or in a rule (better performance) :

Trigger function :

CREATE OR REPLACE FUNCTION path_test ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
IF EXISTS (SELECT *
             FROM demo
            WHERE name = NEW.name
              AND (path <@ NEW.pah OR path @> NEW.path))
THEN RETURN NULL ;
ELSE RETURN NEW ;
END ;
$$

CREATE OR REPLACE TRIGGER before_insert_update_demo BEFORE INSERT OR UPDATE OF name, path ON demo
FOR EACH ROW EXECUTE FUNCTION path_test () ;

Rule

CREATE OR REPLACE RULE path_test_insert AS ON INSERT TO demo
WHERE EXISTS (SELECT *
                 FROM demo
                WHERE name = NEW.name
                  AND (path <@ NEW.pah OR path @> NEW.path))
DO NOTHING ;

CREATE OR REPLACE RULE path_test_update AS ON UPDATE TO demo
WHERE EXISTS (SELECT *
                 FROM demo
                WHERE name = NEW.name
                  AND (path <@ NEW.pah OR path @> NEW.path))
DO NOTHING ;

Upvotes: 3

jjanes
jjanes

Reputation: 44363

There is a race condition if you use read committed.

You could in principle use an EXCLUDE constraint, except that there is no single self-commutative operator you can use. That means you would have to invent a new operator, maybe called <@>, and add it to ltree. I think that this would be possible, but not something you would probably look forward to doing.

Could you just use serializable?

Upvotes: 1

Related Questions