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

    From lordluke80@gmail.com@21:1/5 to All on Tue Apr 17 01:44:39 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dimitri Fontaine@21:1/5 to lordluke80@gmail.com on Tue Apr 17 23:31:00 2018
    lordluke80@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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From lordluke80@gmail.com@21:1/5 to All on Thu Apr 19 14:45:09 2018
    Il giorno martedì 17 aprile 2018 23:31:01 UTC+2, Dimitri Fontaine ha scritto:
    lordluke80@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

    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)