Re: Generating unique values for TEXT columns - Mailing list pgsql-general

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

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