Thread: Using sequence name depending on other column
I have table containing different types of documents (type A, B and C). Each document type must have separate sequential ID starting at 1 ID of first inserted record of type A must be set to 1 ID of first inserted record of type B must be also set to 1 ID of second record of type A must be set to 2 etc. I tried to implement this as CREATE SEQUENCE a_id_seq; CREATE SEQUENCE b_id_seq; CREATE SEQUENCE c_id_seq; CREATE TABLE documents ( doctype CHAR(1), id NUMERIC DEFAULT nextval(doctype ||'_dokumnr_seq'), documentcontents TEXT ); but got an error ERROR: cannot use column references in default expression Any idea how to implement this ? Andrus.
On Sat, Mar 12, 2005 at 23:05:41 +0200, Andrus Moor <eetasoft@online.ee> wrote: > I have table containing different types of documents (type A, B and C). > > Each document type must have separate sequential ID starting at 1 > > ID of first inserted record of type A must be set to 1 > ID of first inserted record of type B must be also set to 1 > ID of second record of type A must be set to 2 > etc. Sequences aren't designed for doing this. If you aren't doing lots of updates, just lock the table and assign the next id as the current max id of that type + 1.
On Sun, 13 Mar 2005 02:59 pm, Bruno Wolff III wrote: > On Sat, Mar 12, 2005 at 23:05:41 +0200, > Andrus Moor <eetasoft@online.ee> wrote: > > I have table containing different types of documents (type A, B and C). > > > > Each document type must have separate sequential ID starting at 1 > > > > ID of first inserted record of type A must be set to 1 > > ID of first inserted record of type B must be also set to 1 > > ID of second record of type A must be set to 2 > > etc. > If you are happy with the fact that a sequence may leave a whole in the numbers. You are probably best to no set a default value for an integer, or big integer. Then run a before trigger for each row. That trigger will assign a value to the column based on the value given for the type. Regards Russell Smith
>> I have table containing different types of documents (type A, B and C). >> >> Each document type must have separate sequential ID starting at 1 >> >> ID of first inserted record of type A must be set to 1 >> ID of first inserted record of type B must be also set to 1 >> ID of second record of type A must be set to 2 >> etc. > > Sequences aren't designed for doing this. If you aren't doing lots of > updates, just lock the table and assign the next id as the current max id > of that type + 1. Bruno, thank you for reply. Document IDs are almost never updated. There are about 25 document updates per minute in peak hours (they create a copy from document and this creation also writes reference to original document). The database can became quite large (500000 documents). Which indexes should I create for getting max ID's fast (total 25 different document types) ? 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. Locking the whole table causes delay for users wanting to insert other type of document. Is this reasonable? Is this delay noticeable in this case? Is it possible to get a number concecutive IDs from sequence ? Andrus.
On Sat, Mar 19, 2005 at 22:37:55 +0200, Andrus Moor <eetasoft@online.ee> wrote: > >> I have table containing different types of documents (type A, B and C). > >> > >> Each document type must have separate sequential ID starting at 1 > >> > >> ID of first inserted record of type A must be set to 1 > >> ID of first inserted record of type B must be also set to 1 > >> ID of second record of type A must be set to 2 > >> etc. > > > > Sequences aren't designed for doing this. If you aren't doing lots of > > updates, just lock the table and assign the next id as the current max id > > of that type + 1. > > Bruno, > > thank you for reply. > > Document IDs are almost never updated. There are about 25 document updates > per minute in peak hours (they create a copy from document and this creation > also writes reference to original document). The database can became quite > large (500000 documents). > > Which indexes should I create for getting max ID's fast (total 25 different > document types) ? You need to create an index on (category, sequence) so that order by category, sequence will be fast. > 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. > Locking the whole table causes delay for users wanting to insert other type > of document. > Is this reasonable? Is this delay noticeable in this case? At 25 inserts per minute locking the table shouldn't be a problem. You could also speed this up by using another table to store the highest value for each category. If you do it that way you can use UPDATE to do the update without locking the whole table. (In effect you only lock by category type.) > Is it possible to get a number concecutive IDs from sequence ? Not if transactions sometimes rollback. You also have to worry about clients requesting groups of sequence numbers at once and then not using them. Deleting records will leave holes. You also need a sequence per category type which will be a pain to maintain.
>> > I have table containing different types of documents (type A, B and C). >> > >> > Each document type must have separate sequential ID starting at 1 >> > >> > ID of first inserted record of type A must be set to 1 >> > ID of first inserted record of type B must be also set to 1 >> > ID of second record of type A must be set to 2 >> > etc. >> > If you are happy with the fact that a sequence may leave a whole in > the numbers. You are probably best to no set a default value for an > integer, or big integer. Then run a before trigger for each row. That > trigger will assign a value to the column based on the value given for > the type. Russell, thank you. I'm a new to Postgres. Is there any sample how to write such trigger ? Before inserting each row it should set document id from sequence corresponding to insertable document type. Andrus.
On Tue, 15 Mar 2005 08:39 pm, Andrus wrote:
> >> > I have table containing different types of documents (type A, B and C).
> >> >
> >> > Each document type must have separate sequential ID starting at 1
> >> >
> >> > ID of first inserted record of type A must be set to 1
> >> > ID of first inserted record of type B must be also set to 1
> >> > ID of second record of type A must be set to 2
> >> > etc.
> >>
> > If you are happy with the fact that a sequence may leave a whole in
> > the numbers. You are probably best to no set a default value for an
> > integer, or big integer. Then run a before trigger for each row. That
> > trigger will assign a value to the column based on the value given for
> > the type.
>
> Russell,
>
> thank you.
> I'm a new to Postgres.
> Is there any sample how to write such trigger ?
>
CREATE FUNCTION seq_trig() RETURNS "trigger"
AS $$BEGIN
IF NEW.type = 'A' THEN
NEW.sequence = nextval('a');
END IF;
IF NEW.type = 'B' THEN
NEW.sequence = nextval('b');
END IF;
RETURN NEW;
END$$
LANGUAGE plpgsql STRICT;
Something like that this may work.
> Before inserting each row it should set document id from sequence
> corresponding to insertable document type.
>
> Andrus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
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 ?
>> 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.
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
First order has number 1, second order has number 2 etc.
It seems that this is more convenient
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.
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.
So it seems that the best way is for mass document insertion:
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.
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.
Andrus.
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?