Generating unique values for TEXT columns

Hi Frank

I use the following constructs to generate an objectid's in my database:

CREATE SEQUENCE public.tsfraction MAXVALUE 999999;

CREATE FUNCTION getobjectid() RETURNS text
AS '
select((select(to_char(current_timestamp, \'yyyy-mm-dd-hh-mm-ss\'))) || (select(to_char((nextval(\'tsfraction\')),\'-FM000000MI\')))) as return;
'
LANGUAGE 'sql';

CREATE TABLE public.object
(
objectid text NOT NULL DEFAULT getobjectid(),
-- other columns omited
CONSTRAINT pk_object PRIMARY KEY (objectid)
) WITH OIDS;

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Frank,

El 03/01/2005 10:53 AM, Frank D. Engel, Jr. en su mensaje escribio:

Is there any "convenient" way to generate (on request) a unique value
for a TEXT column? I have a situation in which I want users of my
front-end program to be able to manually enter values for this column,
but if they leave it blank (in the front-end), to have the database
automatically fill in a unique value. I would like to restrict the
unique values to (for example) digits and uppercase letters (this is
flexible, but the uniqueness of the values should be visually
discernible, and all characters should be printable).

I know how to do this with a numeric column (I can just SELECT MAX on
the column and add one, for example), but how can this be done with a
TEXT column?

I have plpgsql function to generate random character ids:

CREATE OR REPLACE FUNCTION "public"."basex" (integer, varchar) RETURNS
varchar AS'
DECLARE
lnval ALIAS for $1;
tcdom alias for $2;
lndomsiz integer;
lndig integer;
lcret varchar;
lnval2 integer ;
lnpos integer;
lcdig varchar;
BEGIN
lndomsiz := char_length(tcdom) ;
lnVal2 := lnVal;
lcret :='''';
while lnVal2 <> 0 loop
lndig := lnVal2 % lnDomSiz ;
lnval2 := trunc ( lnVal2/lnDomSiz ) ;
lnpos := lnDig+1 ;
lcdig := substr(tcdom,lnpos,1);
lcret := lcdig || lcret ;
end loop;
return lcret;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Usually I call it this way:

select lpad(basex(nextval('sqrefno')::int,'12456789CFHRWY'),6,'0');

--
Sinceramente,
Josu� Maldonado.

"Que se me den seis l�neas escritas de pu�o y letra del hombre m�s
honrado del mundo, y hallar� en ellas motivos para hacerle ahorcar."
--cardenal Richelieu (Cardenal y pol�tico franc�s. 1.585 - 1.642)

On Mon, 2005-01-03 at 10:53, Frank D. Engel, Jr. wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is there any "convenient" way to generate (on request) a unique value
for a TEXT column? I have a situation in which I want users of my
front-end program to be able to manually enter values for this column,
but if they leave it blank (in the front-end), to have the database
automatically fill in a unique value. I would like to restrict the
unique values to (for example) digits and uppercase letters (this is
flexible, but the uniqueness of the values should be visually
discernible, and all characters should be printable).

I know how to do this with a numeric column (I can just SELECT MAX on
the column and add one, for example), but how can this be done with a
TEXT column?

You might want to try creating a sequence for this, then doing something
like:

select upper(md5(nextval('lll')));

Then checking to see if that string is already used to be sure and use
that. If md5 strings are too long, then just substr() the function
above to get a small bit. Chances of failing uniqueness test will
increase, but you have to test for that anyway, so...

SELECT max, then treat the string as a sequence of characters and
increment the last character, rippling the carry if there is one :

carry = 1
l = len(s)-1
while carry and l>=0:
c = s[l]
c += carry
if c>max_allowed_char:
c = min_allowed_char
carry = 1
else:
carry = 0
s[l] = c

if carry:
s = min_allowed_char + s

If two transactions do the same at the same time, you're out of luck
though !

*** Better solution :

if the value was human-entered, prefix it with 'H',
if it's auto generated, use 'A' concatenated with the value from a sequence

thus the user-entered values can't clahs with the sequence values ; the
sequence values are by definition unique ; and all is well.

or something like that...

On Mon, 3 Jan 2005 11:53:44 -0500, Frank D. Engel, Jr.
<fde101@fjrhome.net> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That sounds promising, but I was hoping to avoid a custom function. Oh
well, I'll get to work on it when I get back from lunch.

If this is done as a stored procedure, won't transaction semantics
prevent it from being a problem when two transactions attempt this
simultaneously? I'd thought that the output of one would become the
input of the other (in essence, in terms of database state)?

On Jan 3, 2005, at 12:25 PM, Pierre-Frédéric Caillaud wrote:

SELECT max, then treat the string as a sequence of characters and
increment the last character, rippling the carry if there is one :

carry = 1
l = len(s)-1
while carry and l>=0:
c = s[l]
c += carry
if c>max_allowed_char:
c = min_allowed_char
carry = 1
else:
carry = 0
s[l] = c

if carry:
s = min_allowed_char + s

If two transactions do the same at the same time, you're out of luck
though !

*** Better solution :

if the value was human-entered, prefix it with 'H',
if it's auto generated, use 'A' concatenated with the value from a
sequence

thus the user-entered values can't clahs with the sequence values ;
the sequence values are by definition unique ; and all is well.

or something like that...

On Mon, 3 Jan 2005 11:53:44 -0500, Frank D. Engel, Jr.
<fde101@fjrhome.net> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is there any "convenient" way to generate (on request) a unique value
for a TEXT column? I have a situation in which I want users of my
front-end program to be able to manually enter values for this
column, but if they leave it blank (in the front-end), to have the
database automatically fill in a unique value. I would like to
restrict the unique values to (for example) digits and uppercase
letters (this is flexible, but the uniqueness of the values should be
visually discernible, and all characters should be printable).

I know how to do this with a numeric column (I can just SELECT MAX on
the column and add one, for example), but how can this be done with a
TEXT column?

Thank you!

- -----------------------------------------------------------
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.
$ -----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB2XiY7aqtWrR9cZoRAqATAJ46xJ3bwsZVpIda0iMSSdcm3/8YKACePSC3
2JAngWmFOlkzC5fNE6HKYMU=
=pblY
-----END PGP SIGNATURE-----

___________________________________________________________
$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

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

- -----------------------------------------------------------
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.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB2YZX7aqtWrR9cZoRAktzAJ0edjYBm7wS/fNtPUt7VIytdAcymACfWO2i
arL1gXIctDZKeqjq6RoILOg=
=BUlk
-----END PGP SIGNATURE-----

___________________________________________________________
$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

If this is done as a stored procedure, won't transaction semantics
prevent it from being a problem when two transactions attempt this
simultaneously? I'd thought that the output of one would become the
input of the other (in essence, in terms of database state)?

Well, for this you need to use a sequence somewhere, or be prepared to
retry on error.

Same thing for :
- read key entered from user
- check if it's in the db
- if it's not, then insert

Is not safe because somebody else may have entered the same key between
the check and the insert. So in a sense the check is useless : just insert
and see if it fails or not. However, the 'max' thingy is guaranteed to
step on other's toes if used concurrently. Which is a lot worse.

Generally, when you design for concurrent systems, you have two choices :
- use a system primitive that supports concurrency and does almost what
you want, and add a thin layer of code on it (like, putting auto and user
generated keys in separate keyspaces by using different prefixes)
- try to design a primitive yourself ; which is generally complex because
you'll have to lock, unlock, and manage concurrency yourself. So unless
there is no other way, this is a slippy slope !

That's why the 'max' solution looks nice, but in fact does not work ;
while the 'prefix' solution looks a bit uglier, but it works, and in fact
it's nicer because it also memorizes whereas the key was generated or not.