Re: table with sort_key without gaps

Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
> On Thu, Dec 09, 2004 at 18:32:19 +0100,
>
> Janning Vygen <vygen(at)gmx(dot)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.

Sometimes i am going to delete records. Then i would call a trigger ON DELETE
which moves all other entries to the right place.

> > - 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.

Updates and deletes are very seldom, but i still dont want to lock the table.

> > - 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.

maybe your are right. But with Sequences i thought to have problems when i do
inserts in the middle of the sorting array. I need to move all current rows
out of the way to insert a new one. Insert a row at id 3 i need to do

UPDATE mytable SET id = -(id + 1) WHERE id >= 3;
UPDATE mytable SET id = -(id) WHERE id < 0;
INSERT INTO mytable VALUES (3);

-- UPDATE mytable SET id = id + 1 WHERE id >= 3;
-- doesnt work in pgsql if id is a primary key

but with sequences i just have to push my sequence counter up, too. Right?

SELECT nextval('mytable_id_seq');

ok, it should work with sequences, too. I will try it. but isn't there a ready
to use model which explains and avoids problems like the one with the update
statement above?

kind regards
janning