Обсуждение: table with sort_key without gaps

Hi,

i have a table like this:

create table array (
  account text NOT NULL,
  id      int4 NOT NULL,
  value   text NOT NULL,
  PRIMARY KEY (account, id)
);

values like this:

acc1,1,'hi'
acc1,2,'ho'
acc1,3,'ha'
acc2,1,'ho'
acc3,1,'he'
acc3,2,'hu'

"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

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

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

kind regards,
janning

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.

Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
> 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.

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

Hi,

On Mon, 2004-12-13 at 10:58 +0100, Janning Vygen wrote:
> Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
> > 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.
> ...

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

Well, to get an idea on what you want to do here, maybe you use a sheet
of paper, a red and a blue pen (to simulate two concurrent requests)
and do step by step your inserts but do each step for every color:

1 blue
2 red
3 blue
4 red

and so on.
See to where it leads if you have to maintain the gaplessness.

Sometimes you can design the application differently to get what you
want at the end. Maybe you can expand a bit on your requirements
and what the reason behind these is?

Regards
Tino

On Mon, Dec 13, 2004 at 10:58:25 +0100,
  Janning Vygen <vygen@gmx.de> wrote:
> Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
>
> 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?

Sequences should really only be used to obtain unique values. It is dangerous
to assume any other semantics other than that within a session the values
returned by nextval TO THAT SESSION will monotonically increase.

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

You still haven't told us why you want to remove the gaps in the id.
Unless you have some business reason for doing that, you shouldn't be
doing that. If you told us what the business reason for doing that is,
then we may be able to give you some better suggestions.

Am Montag, 13. Dezember 2004 17:37 schrieb Bruno Wolff III:
> On Mon, Dec 13, 2004 at 10:58:25 +0100,
>
>   Janning Vygen <vygen@gmx.de> wrote:
> > Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
> >
> > 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?
>
> Sequences should really only be used to obtain unique values. It is
> dangerous to assume any other semantics other than that within a session
> the values returned by nextval TO THAT SESSION will monotonically increase.
>
> > 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?
>
> You still haven't told us why you want to remove the gaps in the id.
> Unless you have some business reason for doing that, you shouldn't be
> doing that. If you told us what the business reason for doing that is,
> then we may be able to give you some better suggestions.

ok, i have users which wants to manage their sporting competitions which
(simplified) has games and fixtures (in german "Spieltage", i hope the word
fixtures is understandable). Like German "Bundesliga" has 9 games on
"Spieltag 1", 7 on saturday and two on sunday.

So i have a table:

CREATE TABLE spieltage (
  account  text NOT NULL,
  sort int4 NOT NULL,
  name text NOT NULL
  PRIMARY KEY (account, sort),
  UNIQUE (account, name)
)

and another table (which is not interesting here) with games having a foreign
key referencing spieltage(account, sort). Of course every "spieltag" has a
unique name but needs more important a sort column.

I need to have sort as a primary key or at least a unique key (which is nearly
the same) because many other tables should reference the (primary or
candidate) key (account, sort) for the main reason that i can easily sort
other tables according to the sort column without the need to make a join.

updating/inserting/deleting to the table spieltage takes happen very seldom,
but it should be possible.

When i have three rows and i want to insert one row between sort "1" and sort
"2" i have to move all columns by one.

sample data when using one sequence for sort column

account | sort
--------------
acc1    | 1
acc1    | 2
acc2    | 3
acc2    | 4
acc1    | 5


now i insert VALUES ('acc1', 2) i need to move all existing rows out of the
way.

ah, as i am writing i understand my problem:

i CAN say:

SELECT nextval('spieltage_sort_seq'); -- i might move a column to currval
UPDATE spieltage SET sort = -(sort + 1) WHERE account = 'acc1' and sort >= 2;
UPDATE spieltage SET sort = -(sort)     WHERE account = 'acc1'  and sort < 0;
INSERT INTO spieltage VALUES ('acc1', 3);

right?

because the duplicate sort column value '3' after moving isnt a problem
because of the two-column primary key which only enforces uniquness of
(account, sort)

the other reason why i wanted gapless sequences was that i would love to use
the id in an URL. But this is easy to manage to translate a positional id in
an URL to the database id.

ok. I think i am going to use sequences. But after all i am wondering to find
so little stuff for this common problem. Lots of people have tables which
have a sort column (example: top ten lists) but i guess normally the sort
column is NOT the primary key.

kind regards
janning

On Mon, Dec 13, 2004 at 19:37:41 +0100,
  Janning Vygen <vygen@gmx.de> wrote:
>
> ok, i have users which wants to manage their sporting competitions which
> (simplified) has games and fixtures (in german "Spieltage", i hope the word
> fixtures is understandable). Like German "Bundesliga" has 9 games on
> "Spieltag 1", 7 on saturday and two on sunday.
>
> So i have a table:
>
> CREATE TABLE spieltage (
>   account  text NOT NULL,
>   sort int4 NOT NULL,
>   name text NOT NULL
>   PRIMARY KEY (account, sort),
>   UNIQUE (account, name)
> )
>
> and another table (which is not interesting here) with games having a foreign
> key referencing spieltage(account, sort). Of course every "spieltag" has a
> unique name but needs more important a sort column.
>
> I need to have sort as a primary key or at least a unique key (which is nearly
> the same) because many other tables should reference the (primary or
> candidate) key (account, sort) for the main reason that i can easily sort
> other tables according to the sort column without the need to make a join.
>
> updating/inserting/deleting to the table spieltage takes happen very seldom,
> but it should be possible.

For this emaxmple, I suggest considering using a numeric column for doing
the sorting. You can initial load it with integer values in a number of
ways. When you need to insert a new row with a value between two existing
rows you can use the fractional part of the sort value to give you an
apropiate value without having to modify existing rows.
It doesn't sound like you need to worry about renumbering after deletions,
since gaps shouldn't cause a problem in the sort order. For the actual
reports, the application can number the records consecutively as they
are returned rather than displaying the sort column values.

On Mon, Dec 13, 2004 at 19:37:41 +0100,
  Janning Vygen <vygen@gmx.de> wrote:
>
> the other reason why i wanted gapless sequences was that i would love to use
> the id in an URL. But this is easy to manage to translate a positional id in
> an URL to the database id.

For this you probably shouldn't be using the sort value (even if you use
numeric to avoid having to renumber). This should use a real primary key.
This you definitely can use a sequence for if there isn't a natural
primary key.

Yeah, that suggestion sounds good as long as you ensure that the sort
column has sufficient precision to handle the in-between values.  I
would suggest checking for value-above and value-below when inserting,
then using their midpoint.  In the event that there is no value-above,
add some integer number to the last used value, preferably > 1 (maybe
4, for example), to help avoid the possibility of running out of
precision.

You might have a "maintenance" query which could go through and
renumber the sort order.  In other words,

SELECT * FROM spieltage ORDER BY sort;

then for each row in the result, re-insert it with a new value for the
sort order, increasing by integer values of 4, or whatever.  This could
be run "once-in-a-while" to help avoid precision problems, assuming
that you will actually have enough updates to consider this an issue.

Note: You should probably copy the table into a temp table, delete from
the original, then read the data from the temp while inserting into the
original, then drop the temp table -- all of this within a single
transaction, of course...

On Dec 13, 2004, at 2:08 PM, Bruno Wolff III wrote:

> On Mon, Dec 13, 2004 at 19:37:41 +0100,
>   Janning Vygen <vygen@gmx.de> wrote:
>>
>> ok, i have users which wants to manage their sporting competitions
>> which
>> (simplified) has games and fixtures (in german "Spieltage", i hope
>> the word
>> fixtures is understandable). Like German "Bundesliga" has 9 games on
>> "Spieltag 1", 7 on saturday and two on sunday.
>>
>> So i have a table:
>>
>> CREATE TABLE spieltage (
>>   account  text NOT NULL,
>>   sort int4 NOT NULL,
>>   name text NOT NULL
>>   PRIMARY KEY (account, sort),
>>   UNIQUE (account, name)
>> )
>>
>> and another table (which is not interesting here) with games having a
>> foreign
>> key referencing spieltage(account, sort). Of course every "spieltag"
>> has a
>> unique name but needs more important a sort column.
>>
>> I need to have sort as a primary key or at least a unique key (which
>> is nearly
>> the same) because many other tables should reference the (primary or
>> candidate) key (account, sort) for the main reason that i can easily
>> sort
>> other tables according to the sort column without the need to make a
>> join.
>>
>> updating/inserting/deleting to the table spieltage takes happen very
>> seldom,
>> but it should be possible.
>
> For this emaxmple, I suggest considering using a numeric column for
> doing
> the sorting. You can initial load it with integer values in a number of
> ways. When you need to insert a new row with a value between two
> existing
> rows you can use the fractional part of the sort value to give you an
> apropiate value without having to modify existing rows.
> It doesn't sound like you need to worry about renumbering after
> deletions,
> since gaps shouldn't cause a problem in the sort order. For the actual
> reports, the application can number the records consecutively as they
> are returned rather than displaying the sort column values.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com