Thread: Best way to create unique primary keys across schemas?
Hi,
If I'd like to have primary keys generated ("numeric" style, no UUIDs) that
are unique across schemas is the best option to allocate a fixed sequence
range (min,max) to the sequences of all schemas?
Thanks
panam
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5165043.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sun, Jan 22, 2012 at 5:19 PM, panam <panam@gmx.net> wrote:
> Hi,
>
> If I'd like to have primary keys generated ("numeric" style, no UUIDs) that
> are unique across schemas is the best option to allocate a fixed sequence
> range (min,max) to the sequences of all schemas?
That's how I do it. If you use a bigserial / bigint for it, then the
space is usually plenty big for each schema.
On Mon, Jan 23, 2012 at 11:19 AM, panam <panam@gmx.net> wrote:
> Hi,
>
> If I'd like to have primary keys generated ("numeric" style, no UUIDs) that
> are unique across schemas is the best option to allocate a fixed sequence
> range (min,max) to the sequences of all schemas?
You can "share" a sequence object between several tables. This can
happen somewhat unexpectedly, as I found out to my surprise a while
ago:
CREATE TABLE tbl1 (ID serial primary key,foo varchar,bar varchar);
INSERT INTO tbl1 (foo,bar) VALUES ('asdf','qwer');
CREATE TABLE tbl2 LIKE tbl1 INCLUDING ALL;
INSERT INTO tbl2 (foo,bar) VALUES ('hello','world');
Both tables will be drawing IDs from the same sequence object, because
"create table like" copies the default value, not the "serial"
shorthand. (It makes perfect sense, it just surprised me that the IDs
were looking a little odd.)
Chris Angelico
Chris Angelico wrote
>
>
> You can "share" a sequence object between several tables. This can
> happen somewhat unexpectedly, as I found out to my surprise a while
> ago:
>
> CREATE TABLE tbl1 (ID serial primary key,foo varchar,bar varchar);
> INSERT INTO tbl1 (foo,bar) VALUES ('asdf','qwer');
> CREATE TABLE tbl2 LIKE tbl1 INCLUDING ALL;
> INSERT INTO tbl2 (foo,bar) VALUES ('hello','world');
>
> Both tables will be drawing IDs from the same sequence object, because
> "create table like" copies the default value, not the "serial"
> shorthand. (It makes perfect sense, it just surprised me that the IDs
> were looking a little odd.)
>
Wow, this is pretty useful. Just to fit it more to my original use case, I
used this:
CREATE schema schema1;
CREATE schema schema2;
CREATE TABLE tbl (ID serial primary key,foo varchar,bar varchar); --in
public schema
CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
Thanks, I now consider this my best practice. This way, I don't have to
allocate ranges any more a priori :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5281409.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Jan 24, 2012 at 10:23 PM, panam <panam@gmx.net> wrote: > Wow, this is pretty useful. Just to fit it more to my original use case, I > used this: > > CREATE TABLE tbl (ID serial primary key,foo varchar,bar varchar); --in > public schema > CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from > sequence in public schema > CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from > sequence in public schema > > Thanks, I now consider this my best practice. This way, I don't have to > allocate ranges any more a priori :) I would recommend using an explicit sequence object rather than relying on odd behavior like this; for instance, if you now drop public.tbl, the sequence will be dropped too. However, what you have there is going to be pretty close to the same result anyway. As someone who's moved from DB2 to MySQL (hey, it's all open source!) to Postgres (hey, it's all the features of DB2 and it's _still_ open source!), I've been pretty pleased with Postgres sequences. Instead of special-casing the primary key (as MySQL does with auto_increment), Postgres allows you to have any sequences you like, going any direction you like, and have multiple in the same table if you so desire. Incidentally - I've yet to need it, but I think it's possible to reset a sequence object to start producing lower numbers again, while your table still has some higher numbers in it (of course being careful not to get pkey collisions). ChrisA
Chris Angelico wrote > > I would recommend using an explicit sequence object rather than > relying on odd behavior like this; for instance, if you now drop > public.tbl, the sequence will be dropped too. However, what you have > there is going to be pretty close to the same result anyway. > Oops, thanks for the warning. Any means to prevent accidently dropping the sequence by deleting the corresponding "root"-table? What do you mean with "explicit sequence object"? An own sequence for each table per schema? Chris Angelico wrote > > I think it's possible > to reset a sequence object to start producing lower numbers again, > while your table still has some higher numbers in it (of course being > careful not to get pkey collisions). > Yes, this is definitely possible (http://www.postgresql.org/docs/9.1/static/sql-altersequence.html) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5428997.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Jan 24, 2012 at 5:23 AM, panam <panam@gmx.net> wrote:
> Wow, this is pretty useful. Just to fit it more to my original use case, I
> used this:
>
> CREATE schema schema1;
> CREATE schema schema2;
> CREATE TABLE tbl (ID serial primary key,foo varchar,bar varchar); --in
> public schema
> CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
> CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
> INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
> INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
> INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
> INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
>
> Thanks, I now consider this my best practice. This way, I don't have to
> allocate ranges any more a priori :)
Another quirky way to do it is with domains;
create sequence global_seq;
create domain gid bigint default nextval('global_seq');
create table foo (gid gid, f1 text);
create table bar (gid gid, f2 int);
etc.
This looks very appealing on the surface but domains have some quirks
that should give pause. In particular, you can't make arrays of them,
although you can make arrays of rowtypes that have a domain in them.
Barring domains, you can just manually apply the default instead of
using a serial type:
create table foo (gid bigint default nextval('global_seq'));
merlin
On 01/24/2012 04:23 PM, Merlin Moncure wrote:
> On Tue, Jan 24, 2012 at 5:23 AM, panam <panam@gmx.net> wrote:
>> Wow, this is pretty useful. Just to fit it more to my original use case, I
>> used this:
>>
>> CREATE schema schema1;
>> CREATE schema schema2;
>> CREATE TABLE tbl (ID serial primary key,foo varchar,bar varchar); --in
>> public schema
>> CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
>> sequence in public schema
>> CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
>> sequence in public schema
>> INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
>> INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
>> INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
>> INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
>>
>> Thanks, I now consider this my best practice. This way, I don't have to
>> allocate ranges any more a priori :)
> Another quirky way to do it is with domains;
>
> create sequence global_seq;
> create domain gid bigint default nextval('global_seq');
> create table foo (gid gid, f1 text);
> create table bar (gid gid, f2 int);
> etc.
>
> This looks very appealing on the surface but domains have some quirks
> that should give pause. In particular, you can't make arrays of them,
> although you can make arrays of rowtypes that have a domain in them.
>
> Barring domains, you can just manually apply the default instead of
> using a serial type:
>
> create table foo (gid bigint default nextval('global_seq'));
>
> merlin
>
And UUIDs don't work because....?
On Wed, Jan 25, 2012 at 9:54 AM, panam <panam@gmx.net> wrote:
> What do you mean with "explicit sequence object"? An own sequence for each
> table per schema?
This:
On Wed, Jan 25, 2012 at 10:23 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> Barring domains, you can just manually apply the default instead of
> using a serial type:
>
> create table foo (gid bigint default nextval('global_seq'));
http://www.postgresql.org/docs/9.1/static/sql-createsequence.html
When you create a 'serial' column, Postgres creates a sequence and
makes the column as 'int' with a default that pulls from the sequence.
(Similarly for 'bigserial' and 'bigint'.) If you create the sequence
yourself, you get a bit more control over it (eg setting
min/max/step), and can name it appropriately.
Note the OWNED BY clause (as documented in the above link). That's
what I was saying about the sequence being owned by or linked to the
creating table.
ChrisA
OK, thanks for replys. To sum up, this is what I now consider best practice:
CREATE schema schema1;
CREATE schema schema2;
CREATE SEQUENCE global_seq; --in public schema
CREATE TABLE tbl (ID bigint default nextval('global_seq') primary key,foo
varchar,bar varchar); --in public schema
CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
P.S.: This is to figure out good strategies to make pg work with Hibernate
4's multi tenancy feature and I personally dislike handling large UUIDs,
especially during debugging. Plus I see no particular reason to prefer them
over bigint sequences in the general case (but this is a different topic).
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5430441.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Jan 26, 2012 at 2:12 AM, panam <panam@gmx.net> wrote:
> CREATE TABLE tbl (ID bigint default nextval('global_seq') primary key,foo
> varchar,bar varchar); --in public schema
> CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
> CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
Yep, but you can do this more simply:
CREATE TABLE schema1.tbl (ID bigint default nextval('global_seq')
primary key,foo varchar,bar varchar)
CREATE TABLE schema2.tbl (ID bigint default nextval('global_seq')
primary key,foo varchar,bar varchar)
No need for the dummy table.
Obviously you would want better names than these ("global_seq" is a
really dumb name for a production environment!), but you knew that
already :)
ChrisA
Thanks, yeah, but the dummy tables are needed anyway in my case for those entities that are shared among the tenants :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5433562.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jan 27, 2012 at 4:56 AM, panam <panam@gmx.net> wrote: > Thanks, yeah, but the dummy tables are needed anyway in my case for those > entities that are shared among the tenants :) Ah! Then that's easy :) ChrisA
On 2012-01-23, panam <panam@gmx.net> wrote:
> Hi,
>
> If I'd like to have primary keys generated ("numeric" style, no UUIDs) that
> are unique across schemas is the best option to allocate a fixed sequence
> range (min,max) to the sequences of all schemas?
given that challenge the easiest solution is to just create one
sequence and hang everything off it.
--
⚂⚃ 100% natural