Re: Using sequence name depending on other column - Mailing list pgsql-general

On Wed, Mar 23, 2005 at 20:47:36 +0200,
  Andrus <noeetasoftspam@online.ee> wrote:
>
> I thought about this.
>
> 1. It seems that user prefer to see separate numbers for each sequence.
>
> First invoice has number 1 , second invoice has number  2

This suggests that invoices for different categories can have the same
number. That sounds like a really bad idea. You should talk them out
of this idea.

> First order has number 1, second order has number 2 etc.

This has similar problems to the above, but might not be as bad, depending
on how you use the numbers.

>
> It seems that this is more convenient

Why do you think it will be more convenient? It looks to me like it will
be less convenient becuase you won't be able to use invoice or order numbers
without qualifying them. This is likely to cause some mixups.

>
> 2. Users may have not acces to all documents. He/she may even not to know
> about existence of other categories . If it sees sequence numbers leving big
> random gaps for unknown reasons this can be very confusing.

Why are gaps confusing? Are you sure they are even going to see them?
If you are talking about row numbers here, that will probably just be
used to order rows for output within a document. Most people probably
won't see the row numbers.

>
> 3. This is also a security leak: by analyzing sequence numbers, user can get
> information about the number and insertion frequency of unauthorized
> documents. This is the information which should be hidden from user.

They will be able to analyze sequence numbers in any case. But presumably
here you are concerned about people with valid access to some of your
documents being able to deduce information about documents to which they
don't have access. For row numbers within a document you can just not
show them the row numbers. If you are also concerned about document IDs
you can use encryption to convert a sequence number into an id. However,
you won't be able to easily change the key after the fact since that
will effectively renumber all documents and will make it hard to talk
to people who have copies from before the change.


> So it seems that the best way is for mass document insertion:

This seems like a mess. Why not go the lock table and select the max+1
value within a category or document? I doubt that you are producing
documents at a rate where locking the table is an issue. Having a simpler
solution will be easier to maintain.

> 1. Create separate (20!) sequences for each category.
> 2. Use a trigger suggested by Russell Smith for each document insertion:
>
> CREATE FUNCTION seq_trig() RETURNS "trigger"
>     AS $$BEGIN
> NEW.sequence = nextval(NEW.category);
> RETURN NEW;
> END$$
>     LANGUAGE plpgsql STRICT;
>
> 3. Grab the inserted document sequence number using curval(). Update
> temporary table document rows with this number.
> 4. Repeat p.3 for each document separately .  It seems that this cannot be
> done is a SQL way, it requires the scanning of insertable document header
> database one by one.
>
> In this case sequence number acts as registration number and as part of
> primary key.
>
> The problem is that this requires calling curval() function after inserting
> each document header. This doubles
> the client-server traffic compared to the solution where sequence numbers
> are allocated one time from
> separate lookup table.

Why do you think this is a problem? Have you actually done performance tests
and found the system couldn't keep up? Even if it can't, buying more or
better hardware might be a better solution than making the software part
more complicated.

>
> Is this solution best or should I still use separate table for storing
> numbers ?
>
> > Though it looks like your description of the rows table is odd. My guess
> > is
> > that the sequence for the row is not supposed to be the same one used in
> > the FK reference to the document. Assuming this is the case, again you
> > can use one sequence for the entire rows table.
>
> Sorry, I don't understand this.
> The fields (category, sequence) make relation between document headers and
> document rows.
> They are same for same document. Sequnece numbers are generated by document
> header insertion trigger.
> There is no primary key required in row table.

All tables should have a primary key. If you don't you are asking for
trouble down the road. If you get two identical rows, you are going to
have trouble deleting or updating them later. Also it seems very odd
that rows that are part of a document don't have an order. Are you
relying on them being displayed in the same order they were added
to the database instead of using an ORDER BY clause?