"ownership" of sequences, pseudo random unique id

Ivan Sergio Borgonovo wrote:

I've

create table pr(
code varchar(16) primary key,
...
);
create sequence pr_code_seq owned by pr.code; -- uh!

pr.code will *mostly* be obtained as

to_hex(feistel_encrypt(nextval('pr_code')))
and sometimes 'manually' inserting unique codes.

actually stuff like:
alter table pr drop column code;
or just
drop table pr

seems to work as expected (they drop the sequence too).

Should I be concerned of anything since it looks like a hack?

You need to ensure you have a retry loop in your insertion code, because
if the generated code conflicts with a manually inserted code, it will
cause an error. Other than that, seems like it should work ...

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, 20 Aug 2009 14:31:02 -0400
Alvaro Herrera <alvherre@commandprompt.com> wrote:

Ivan Sergio Borgonovo wrote:

I've

create table pr(
code varchar(16) primary key,
...
);
create sequence pr_code_seq owned by pr.code; -- uh!

actually stuff like:
alter table pr drop column code;
or just
drop table pr

seems to work as expected (they drop the sequence too).

Should I be concerned of anything since it looks like a hack?

You need to ensure you have a retry loop in your insertion code,
because if the generated code conflicts with a manually inserted
code, it will cause an error. Other than that, seems like it
should work ...

I was mainly concerned about assigning ownership of a sequence to a
column that is not an int.
This looks like an hack ;) but it looks to work as expected:
dropping the column or the table drop the sequence.
Assigning ownership just avoid me to remember that if I drop the
column I don't need the sequence.
So owned by just mean "drop if" regardless of type or anything else.

I could even define the table as
create table pr(
code varchar(16) primary key,
...
);
create sequence pr_code_seq owned by pr.code;
alter table pr
alter column code
set default
to_hex(feistel_encrypt(nextval('pr_code_seq')));
That will make more explicit the relationship between the sequence
and the column.

I think I can avoid conflict between auto and manually generated
codes imposing a different format on input in client code.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:

I was mainly concerned about assigning ownership of a sequence to a
column that is not an int.

I think you can get away with that. It's already the case within the
standard usage that the owning column could be either int or bigint.

FWIW, I agree with the idea of adding a default expression so that it
behaves even more like a regular serial column.

regards, tom lane