Re: [GENERAL] using ID as a key - Mailing list pgsql-general

> sounds intriguing. although it still use db, but because it
> does not need any special db feature (table-locking is
> common), it qualifys as "programmatical" solution.
>
> however, not totally understood yet, let's see:
>
> comparing to file locking (e.g. perl's flock)
> 1) locking is enforced. safer than flock, which is just cooperative;
>    however, it need extra db session. seems flock is fast esp. if
>    each app connection session only need to get one id.
> 2) it gets a block of id's to the client to reduce traffic to the
> "central control".
>    how about for each app connection session, usually only increase one?
>    overhead?
> 3) because 2) may create a lot of holes, to make it better, somehow (the
> algorithm) it can return id's to the pool. (then, the id's assigned are
> not monotonous) -- unless the client crashes.
>
> is that understanding correct?
>
> also, how many columns in the "central control" table, 2 or 3?

1)

 The creation statement for the table is:

 CREATE TABLE TX (FX1 int4, FX2 int4 not null)
 CREATE UNIQUE INDEX FX2IND ON TX (FX2)


 The table is locked via the following command in the start-up phase
of the application:

  BEGIN
  INSERT INTO TX (FX2) VALUES(-1)
  COMMIT

 If this statement does not produce an error, no other application
will get access to this table. Actually the application will try
to "lock" the table several times during start-up in case another
client has blocked it.


2) What happens for the first client.

 No client has prior made a connection to the table. Therefore the client
get the internal value pair (session=1, id=0) and writes the following
value pair to the table (session=2,id=0). The client "unlocks" the
table via "delete ... where fx2=-1"

 Now the client creates objects:

 1  0 class-id
 1  1 class-id
 1  2 class-id
 1  3 class-id

 Now the application shuts down and writes back:

 insert into ..... ( ) VALUES(1,4)

 What happens with the second client ?

 He now finds two rows with value pairs (1,4) and (2,0). He "locks"
the table and uses (1,4) for further work, remove the choosen value
pair from table and creates objects with:

 1 4 class-id
 1 5 class-id

 Now the third client is coming.

 He locks table, finds only (2,0), uses them, remove this pair from
table and insert (3,0), unlock the table and creates objects:

 2 0 class-id
 2 1 class-id
 2 2 class-id

 Now the second client terminates, writes back their value pair, then the
third client writes pack their value pair and we have the following pairs
within the table:

 1 6
 2 3
 3 0

 waiting for further clients to ask for values ...

3) Some further considerations about the work which has to be done on
the client. With the values above (6 digits to base 36) we can create
up to (36^6)-1 with a "fresh" value pair (x,0) without any further
communication to the database. (Therefore you may have up to
2.176.782.335 id for internal usage).

 On the other side you may have up to (2^36)-1 sessions.

 The client has to check for an overflow for each new id he creates

 if newid >=2176782335 then
   "get new session pair from database and forget your old one"

4) With the values below you may recognise  46.656 different classes
(or tables) which should be enough.

5) You may change these values (6/6/3) to other values which seems
to be better.

6) You may calculate how long it will take before you have no ids
available any longer ... it's quite a long time.

 The idea behind this has been mentioned in several papers on the
Internet mentioned as "high-low" algorithm. Actually I've seen this
code in one wrapper product.

 Marten