Thread: "ownership" of sequences, pseudo random unique id

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?

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

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