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

On Tue, Apr 17, 2018 at 3:11 AM, Luca <[email protected]> wrote:

> On the following table (mapped by a Django model) I'm trying to write a
> function to build a tree using the `ltree` extension:
>
[...]

> 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
>

If you're using Django, couldn't you just manage the tree element updates
in the model there? That might simplify the problem a bit.. You could
probably issue a recursive update statement just after your create
statement, to create all the necessary tree elements? Not exactly an answer
to your question, but maybe an alternative approach.