ltree demo adaptation- issues with path

[lboc80]

hello, thanks for the repo and the example. I tried to adapt it in this way: this is the 'employee' table from a django model:

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 also adapted the triggers:

-- 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 concat_ws('.', path::text, NEW.id::text)::ltree
          SELECT concat_ws('.', path::text, NEW.id::text)::ltree
          FROM comp_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 comp_companyuserprofile
       SET path = concat_ws('.', NEW.path::text, subpath(comp_companyuserprofile.path, nlevel(OLD.path))::text)::ltree
     WHERE comp_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 comp_companyuserprofile;
CREATE TRIGGER cup_path_insert_trg
               BEFORE INSERT ON comp_companyuserprofile
               FOR EACH ROW
               EXECUTE PROCEDURE _update_manager_path();

-- calculate the path when updating the manager or the csod_user_id
DROP TRIGGER IF EXISTS cup_path_update_trg ON comp_companyuserprofile;
CREATE TRIGGER cup_path_update_trg
               BEFORE UPDATE ON comp_companyuserprofile
               FOR EACH ROW
               WHEN (OLD.manager_id IS DISTINCT FROM NEW.manager_id
                     OR OLD.csod_user_id IS DISTINCT FROM NEW.csod_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 comp_companyuserprofile;
CREATE TRIGGER cup_path_after_trg
               AFTER UPDATE ON comp_companyuserprofile
               FOR EACH ROW
               WHEN (NEW.path IS DISTINCT FROM OLD.path)
               EXECUTE PROCEDURE _update_descendants_manager_path();

However path remains always empty after inserting or updating the entire row or the 'manager_id' column. Am I doing something wrong? I there a way to launch the update functions ath the end of the import process instead of implementing them as triggers?

[k4nar]

Even if you insert a new row with no manager_id you don't have a path? That's weird…

And yes, you could use regular SQL functions instead of triggers, and call them when you need to. But only the triggers can ensure data integrity.

[andreynovikov]

I do not know if this is the same issue but I also had empty paths when using get_or_create:

obj, created = Object.objects.get_or_create(slug=slug)  # obj is saved under the hood and path is set by triggers
obj.field = value
obj.save()  # obj is saved but we have an empty path in Django object, triggers are not run as slug is not modified,
#             path is overwritten by Django ORM

I fixed this by adding refresh_from_db:

obj, created = Object.objects.get_or_create(slug=slug)  # obj is saved under the hood and path is set by triggers
obj.refresh_from_db()  # read generated path from db into object
obj.field = value
obj.save()  # obj is saved but we have correct path now

Another (tested) option is to exclude path from saved fields:

fields = [f.name for f in Object._meta.get_fields() if f.name != 'path' and not f.auto_created and not f.primary_key]
obj.save(update_fields=fields)

This can be put in save() method for convenience.