Postgres 9.6 - ltree extension - (re)build a tree on a table - Mailing list pgsql-sql
| From | Luca |
|---|---|
| Subject | Postgres 9.6 - ltree extension - (re)build a tree on a table |
| Date | |
| Msg-id | CAKLQ8zJaLaLgZGVxvt0+w9QFfrOmRTf3e6jS57z9kNwqgOeoAQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: Postgres 9.6 - ltree extension - (re)build a tree on a table
(Steve Midgley <science@misuse.org>)
Re: Postgres 9.6 - ltree extension - (re)build a tree on a table ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-sql |
On the following table (mapped by a Django model) I'm trying to write a function to build a tree using the `ltree` extension:
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
created | timestamp with time zone | | not null |
modified | timestamp with time zone | | not null |
guid | uuid | | not null |
csod_user_id | character varying(150) | | not null |
csod_username | character varying(150) | | not null |
csod_email | character varying(254) | | |
id | integer | | not null |
path | ltree | | |
first_name | character varying(150) | | |
last_name | character varying(150) | | |
manager_id | integer | | |
user_id | integer | | |
Indexes:
-- various idexes
"cup_path_btree_idx" btree (path)
"cup_path_gist_idx" gist (path)
Check constraints:
"check_no_recursion" CHECK (index(path, id::text::ltree) = (nlevel(path) - 1))
Foreign-key constraints:
"cornerstone_cornerst_manager_id_" FOREIGN KEY (manager_id) REFERENCES cornerstone_cornerstoneuserprofile(id) DEFERRABLE INITIALLY DEFERRED
"cornerstone_cornerst_user_id_" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "cornerstone_cornerstoneuserprofile" CONSTRAINT "cornerstone_cornerst_manager_id_" FOREIGN KEY (manager_id) REFERENCES cornerstone_cornerstoneuserprofile(id) DEFERRABLE INITIALLY DEFERRED
I first found and example on github that I adapted which uses triggers before/after inserts and updates:
-- function to calculate the path of any given manager
CREATE OR REPLACE FUNCTION _update_manager_path() RETURNS TRIGGER AS
$$
BEGIN
IF NEW.manager_id IS NULL THEN
NEW.path = NEW.id::text::ltree;
ELSE
SELECT path || NEW.id::text
FROM corp_companyuserprofile
WHERE NEW.manager_id IS NULL or id = NEW.manager_id
INTO NEW.path;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- function to update the path of the descendants of a c.u.p.
CREATE OR REPLACE FUNCTION _update_descendants_manager_path() RETURNS TRIGGER AS
$$
BEGIN
UPDATE corp_companyuserprofile
SET path = NEW.path || subpath(corp_companyuserprofile.path, nlevel(OLD.path))
WHERE corp_companyuserprofile.path <@ OLD.path AND id != NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- calculate the path every time we insert a new c.u.p.
DROP TRIGGER IF EXISTS cup_path_insert_trg ON corp_companyuserprofile;
CREATE TRIGGER cup_path_insert_trg
BEFORE INSERT ON corp_companyuserprofile
FOR EACH ROW
EXECUTE PROCEDURE _update_manager_path();
-- calculate the path when updating the manager or the comp_user_id
DROP TRIGGER IF EXISTS cup_path_update_trg ON corp_companyuserprofile;
CREATE TRIGGER cup_path_update_trg
BEFORE UPDATE ON corp_companyuserprofile
FOR EACH ROW
WHEN (OLD.manager_id IS DISTINCT FROM NEW.manager_id
OR OLD.comp_user_id IS DISTINCT FROM NEW.comp_user_id)
EXECUTE PROCEDURE _update_descendants_manager_path();
-- if the path was updated, update the path of the descendants
DROP TRIGGER IF EXISTS cup_path_after_trg ON corp_companyuserprofile;
CREATE TRIGGER cup_path_after_trg
AFTER UPDATE ON corp_companyuserprofile
FOR EACH ROW
WHEN (NEW.path IS DISTINCT FROM OLD.path)
EXECUTE PROCEDURE _update_descendants_manager_path();
However this is not working since the path column is updated just with the 'id' value of the same record.
Also those triggers are slowing down really much (as expected to be honest) the upsert operation.
Since this table should be updated after a data import, so after a massive upsert, ideally I would i'd like to have a function
called after that is completed, in order to (re)build the 'path' column for each record, which means rebuild the tree.
Any suggestion to achieve that or snippets about how to do that? havent foud any valid example for 'rebuild a tree in postgres' in the official docs.
Thanks