Re: [GENERAL] looking for a globally unique row ID

On Sep 14, Rafal Pietrak modulated:

> My main objective is the "guarantee". Which I've tried to indicate
> referring to "future UPDATEs".
> 

With a well-behaved application, it is sufficient to define each
ID column as:
  id int8 UNIQUE NOT NULL DEFAULT nextval('sharedsequence')

and ensure that you always use defaults for INSERT and never
set the column during UPDATE.

You can also use a BEFORE/FOR EACH ROW trigger to enforce these
conventions, if you are worried about accidental violations in your
SQL.  In that case, leave the DEFAULT as NULL in the table definition,
but make the trigger do this for INSERT:
  NEW.id := nextval('sharedsequence');

and this enforcement check for UPDATE:
  IF OLD.id != NEW.id THEN RAISE EXCEPTION 'id is immutable';

If that's not a strong enough guarantee, I'm not sure I understand
your problem statement...


Karl


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general