是否可以创建一个包含ltree字段的整个路径的唯一约束

Question

我在Postgres中使用ltree扩展来管理树的路径。我想确保名称字段对于树上的任何给定路径都是唯一的。我可以使用约束来实现这一点吗？还是必须将其构建到查询中？

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

如果我必须将其构建到查询中，那么如果我使用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;

Answer 1

另一种解决方案可以是在触发函数或规则中测试新路径(更好的性能(：

触发功能：

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 () ;

规则

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 ;

Answer 2

您可以创建一个单独的表，其中存储名称和路径

-- 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,
);

并创建一个并行安全函数，您可以在插入原始表时调用该函数

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));

如果要维护单独的索引，则必须实现类似的触发器来更新和删除项。

Answer 3

如果使用读提交，则存在竞争条件。

原则上，您可以使用EXCLUDE约束，只是没有一个可以使用的自交换运算符。这意味着你必须发明一个新的运算符，可能被称为<@>，并将其添加到ltree中。我认为这是可能的，但这不是你可能期待的事情。

你能用序列化吗？