Re: Problem with UPDATE and UNIQUE - Mailing list pgsql-general

Michael Glaesemann wrote:
>
> On Aug 22, 2007, at 1:02 , Frank Millman wrote:
>
> > I want to store data in a 'tree' form, with a fixed number
> of levels,
> > so that each level has a defined role.
>

Thanks very much for the in-depth response, Michael. Plenty for the little
grey cells to work on.

> First thought: fixed, predetermined levels, separate tables
> for each level. If a more general approach is desired, your
> options are generally adjacency list, nested sets, or
> contrib/ltree. Each has their own strengths and weaknesses.
>

I am writing a general-purpose business/accounting application. If
successful, I hope to have a number of different companies using it. I want
to provide the ability for the end-user to to define their own,
multi-dimensional, views of various core tables (general ledger, products,
etc). I foresee that it will only be used for reporting purposes
(particularly WHERE, ORDER BY and GROUP BY). Therefore I do need a general
approach.

> > I have the following (simplified) table -
> >
> > CREATE TABLE treedata (
> >   rowid serial primary key,
> >   levelno int not null,
> >   parentid int references treedata,
> >   seq int not null,
> >   code varchar not null,
> >   description varchar not null
> >   );
>
> rowid + parentid looks like adjacency list to me. Note that
> you're storing redundant data (the levelno, which can be
> derived from the rowid/parentid relationships), which you may
> want to do for performance reasons, but does make things more
> complicated: you're essentially caching data which brings
> with it problems of cache invalidation. In this case, you
> need to make sure you're updating levelno whenever it needs
> to be updated. (Which I'm sure you've already thought of.)
>

I read up on 'adjency list' and 'nested sets', and I agree, the scheme I
have come up with is an adjency list. It had not occurred to me that levelno
is redundant, but I can see that this is so. I will have to check to see if
there are any implications if I remove it.

> > To describe each of the levels in the tree, I have the
> following table
> > -
> >
> > CREATE TABLE treelevels (
> >   levelno int primary key,
> >   code varchar unique not null,
> >   description varchar not null
> >   );
>
> Having each level as its own table would make this redundant,
> but again, that might not fit with what you're modeling.
>
> > Typical values for this table could be -
> >   (0,'Prod','Product code')
> >   (1,'Cat','Product category')
> >   (2,'*','All products')
>
> This makes me think you'll want to rethink your schema a bit,
> as you're mixing different types of data: categories and
> products. I'd at least separate this out into a products
> table and a categories table. The categories table may in
> fact still require some kind of tree structure, but I don't
> think products belongs as part of it.
>

Very good point. I will give this some serious thought.

[...]

> >
> > Say I want to insert a level between 'code' and 'category' called
> > 'group' -
> >
> > INSERT INTO treelevels VALUES (1,'Group','Product group');
>
> It's a good habit to *always* explicitly list your columns:
> it's self- documenting and more robust in the face of schema changes.
>
> > Obviously this will fail with a duplicate levelno. Therefore before
> > the insert statement I want to do this -
> >
> > UPDATE treelevels SET levelno = (levelno+1) WHERE levelno >= 1;
> >
> > The problem is that if there are a number of levels, and
> they are in
> > indeterminate order, I can get duplicate level numbers while the
> > command is being executed.
> >
> > My workaround at present is the following -
> >
> > UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
> > UPDATE treelevels SET levelno = (levelno-10000) WHERE levelno >= 1;
>
> This is a general problem with nested sets and your situation
> where you're caching the levelno, and you're work around is
> similar to the two generally recommended solutions. One is to
> make updates using an offset such as what you're doing, and
> the other is to utilize negative levels. I'm keen on the
> latter, as I feel it's a bit more
> flexible: you don't need to make sure your offset is large enough.

I also like the idea of 'negating' the level. It is neat and effective.
Thanks for the tip, I will use it.

One trivial point. I use 'negating' quite a bit, and instead of -
    SET levelno = -1 * (levelno + 1)

I have adopted the habit of using -
    SET levelno = -(levelno + 1)

It just feels a bit neater.

[...]

>
> Anyway, hope this gives you something to think about.
>

It certainly does. Thanks again for all the valuable advice.

Frank