Re: table with sort_key without gaps - Mailing list pgsql-general

From Bruno Wolff III
Subject Re: table with sort_key without gaps
Date
Msg-id 20041211190647.GB1613@wolff.to
Whole thread Raw
In response to table with sort_key without gaps  (Janning Vygen <vygen@gmx.de>)
Responses Re: table with sort_key without gaps  (Janning Vygen <vygen@gmx.de>)
List pgsql-general
On Thu, Dec 09, 2004 at 18:32:19 +0100,
  Janning Vygen <vygen@gmx.de> wrote:
>
> "id" should be positive
> "id" should not have gaps within the same account
> "id" should start counting by 1 for each account
>
> i cant use sequences because they are producing gaps and doesn't start
> counting by 1 for each account and i dont want to use postgresql array type
> for various reasons.
>
> for this model to function you need a lot of sophisticated plpgsql function to
> insert, move or delete entries to keep

I doubt you want to use this model if you are going to be deleting records.

> - did anyone implemented a table like this and wrote some custom
> functions/triggers for inserting, deleting, moving and so on? If yes it would
> be nice if he/she is willing to sahre the code with me.

If you aren't deleting records and you don't have a lot of concurrent requests,
you can lock the table and select the current max id for an account and add
1 to get the next id for for that account.

> - did anyone implemented a table like this and came to the conclusion that
> this shouldn't be done for any reasons out of my sight? (i don't bother about
> updating a primary key)

Why are you doing this? Normally uniqness of an ID is good enough. If you
don't need to worry about gaps, you could use one sequence for the entire
table to generate IDs.

pgsql-general by date:

Previous
From: Julian Legeny
Date:
Subject: Select after insert to the unique column
Next
From: Timothy Perrigo
Date:
Subject: Re: Regarding Postgres installation and administration on linux suse 9.0