Postgres 9.6 - ltree extension - (re)build a tree on a table

[lordl...@gmail.com]

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 |
comp_user_id | character varying(150) | | not null |
comp_username | character varying(150) | | not null |
comp_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:
"corp_companyeuserprofile_pkey" PRIMARY KEY, btree (id)
"corp_companyeuserprofile_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
"corp_companyeuserprofile_comp_email_6f7503d7" btree (comp_email)
"corp_companyeuserprofile_comp_email_6f7503d7_like" btree (comp_email varchar_pattern_ops)
"corp_companyeuserprofile_comp_user_id_328cb82e" btree (comp_user_id)
"corp_companyeuserprofile_comp_user_id_328cb82e_like" btree (comp_user_id varchar_pattern_ops)
"corp_companyeuserprofile_comp_username_9eec69b2" btree (comp_username)
"corp_companyeuserprofile_comp_username_9eec69b2_like" btree (comp_username varchar_pattern_ops)
"corp_companyeuserprofile_guid_e3160b25" btree (guid)
"corp_companyeuserprofile_manager_id_2491a6e2" btree (manager_id)
"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:
"corp_comp_manager_id_2491a6e2_fk_cornersto" FOREIGN KEY (manager_id) REFERENCES corp_companyeuserprofile(id) DEFERRABLE INITIALLY DEFERRED
"corp_comp_user_id_39765502_fk_users_use" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "corp_companyeuserprofile" CONSTRAINT "corp_comp_manager_id_2491a6e2_fk_cornersto" FOREIGN KEY (manager_id) REFERENCES corp_companyeuserprofile(id) DEFERRABLE INITIALLY DEFERRED
Triggers:
cup_path_after_trg AFTER UPDATE ON corp_companyeuserprofile FOR EACH ROW WHEN (new.path IS DISTINCT FROM old.path) EXECUTE PROCEDURE _update_descendants_manager_path()
cup_path_insert_trg BEFORE INSERT ON corp_companyeuserprofile FOR EACH ROW EXECUTE PROCEDURE _update_manager_path()
cup_path_update_trg_two BEFORE UPDATE ON corp_companyeuserprofile FOR EACH ROW EXECUTE PROCEDURE _update_manager_path()


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

[Dimitri Fontaine]

lordl...@gmail.com writes:
> 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.

Have a look at WITH RECURSIVE:

https://www.postgresql.org/docs/current/static/queries-with.html
https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/

Regards,
--
Dimitri Fontaine

[lordl...@gmail.com]

Thanks Dimitri (and congrats for the book!)

I modified the table and this solution came up:

http://dpaste.com/2QXFP0M

What I'm trying to do is fetchig for each id the one with max level.

However, I'm messing up something since despite using DISTINCT there are still duplicated rows.

Any hints about that?

Thanks