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

Reply from Bruno Wolff III, re-posted from mail:

> >>I have 120 concurrent users inserting documents. Mostly they are using
> >>10
> >>different document types. Each type  should have separate numbering.
> >>They
> >>insert 30 documents per minute in peak hours.
> >
> >You really should think about this. What are you really using these
> >sequence numbers for.
>
> I'm trying to move existing ERP database to Postgres
>
> My database contains table of document headers:
>
> CREATE TABLE document (
> category CHAR,
> sequence NUMERIC(7),
> ... a lot of other columns ... ,
> PRIMARY KEY (category, sequence) ) ;
>
> and document rows:
>
> CREATE TABLE rows  (
> category CHAR,
> sequence NUMERIC(7),
> ... a lot of other columns ... ,
> FOREIGN KEY (category, sequence) REFERENCES document ON DELETE CASCADE );
>
> I need to insert always on category documents in one transaction.

From what I see above, I don't see any need to have separate sequences for
each category. It looks like you can just use one for the whole table.
That will work efficiently.

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.

>
> I think I should use the following algorithm:
>
> 1. Create temporary tables containing new documents headers and rows.
> 2. Allocate desired amount of sequence numbers.
> 3. Update temporary tables with new sequence numbers
> 4. Add updated temprary tables to document and rows tables

The normal way to do this if it is being done in one session that isn't
shared is to insert the document record, and then refer to its sequence
using currval while inserting the row records. In version 8.0 you can
use a function to get the name of a sequence associated with a serial
column (as opposed to manually entering the name).

>
> There are 3 recommendations for this in this thread:
>
> 1. Sequences + trigger using Russell Smith code.
>
> Pros: does not require programming
>
> Cons:  Impossible to implement. I need to assing same sequence number to
> rows create relation between document header and document rows. I seems
> that
> this is not posssible to implement this using sequences and trigger.
>
> 2. Lock document table, get sequence numbers.
>
> LOCK document
> SELECT MAX(sequence)+1 FROM document WHERE category=mycategory
>
> Update temporary tables with new numbers
>
> Cons: may cause delays for other users: a) locks whole document table for
> single category.
>  b) table remains locked until all rows and headers are added
>
> 3.  Use separate lookup table for sequence numbers. Lock this table row,
> update it.
>
> Cons: I don't know is it possible to lock single row in Postgres. Since it
> is impossible to unlock a row,
> row should remains locked during whole transaction and causes also delay
> if
> other user want to add document with same sequence number.
> For this I should use separate transaction to update lookup table.
>
>
> Which is the best way to do this is PostgreSQL ?