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

On Thu, 20 Aug 2009 14:31:02 -0400
Alvaro Herrera <alvherre(at)commandprompt(dot)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