Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #821

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

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

Postgres 9.6 - ltree extension - (re)build a tree on a table lordluke80@gmail.com - 2018-04-17 01:44 -0700
  Re: Postgres 9.6 - ltree extension - (re)build a tree on a table Dimitri Fontaine <dim@tapoueh.org> - 2018-04-17 23:31 +0200
    Re: Postgres 9.6 - ltree extension - (re)build a tree on a table lordluke80@gmail.com - 2018-04-19 14:45 -0700

csiph-web