Обсуждение: Sequence

Hello.

Each user has a lot of guests. Each guest only has one user.

1. I create a table users:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name varchar(256)
);

2. I create a table guests:

CREATE TABLE guests (
  user_id integer,
  guest_id SERIAL,
  PRIMARY KEY (user_id, guest_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)

3. I add two new users:

insert into users (name) values ('alex2008');
insert into users (name) values ('jack2008');

select * from users;
 user_id |   name
---------+----------
       1 | alex2008
       2 | jack2008
(2 rows)

4. I add two new guests to the user alex2008 and one new guest to the
user jack2008:

insert into guests (user_id, name) values (1, 'Mark Twain');
insert into guests (user_id, name) values (1, 'Anna Black');
insert into guests (user_id, name) values (2, 'John Black');

select * from guests;
 user_id | guest_id |    name
---------+----------+------------
       1 |        1 | Mark Twain
       1 |        2 | Anna Black
       2 |        3 | John Black
(3 rows)

I want to have a different result of insert command:

 user_id | guest_id |    name
---------+----------+------------
       1 |        1 | Mark Twain
       1 |        2 | Anna Black
       2 |        1 | John Black

Sequence guests_guest_id_seq is not connected with the field user_id. It
increases in spite of values of user_id. How can I solve this problem?

> select * from guests;
> user_id | guest_id |    name
> ---------+----------+------------
>       1 |        1 | Mark Twain
>       1 |        2 | Anna Black
>       2 |        3 | John Black
> (3 rows)
>
> I want to have a different result of insert command:
>
> user_id | guest_id |    name
> ---------+----------+------------
>       1 |        1 | Mark Twain
>       1 |        2 | Anna Black
>       2 |        1 | John Black
>
> Sequence guests_guest_id_seq is not connected with the field user_id. It
> increases in spite of values of user_id. How can I solve this problem?

If possible, design your application to be happy with the way it is
already. Those keys shouldn't really be user visible anyway.

If you really have to have per-user guest IDs (and, I'm guessing,
contiguous sequences of guest IDs) you'll have to do a fair bit of work.
The usual approach seems to be using a trigger function to trap inserts
and deletes and rewrite the guest_id field appropriately.

If you don't need contiguous guest IDs - ie you're happy with a sequence
like "1 3 4 5 8" after IDs 2, 6 and 7 have been DELETEd - then you can
emulate a sequence with a per-user counter. Eg:

CREATE TABLE user (
   user_id SERIAL PRIMARY KEY,
   guest_id_ctr INTEGER
);

CREATE TABLE guest (
   user_id INTEGER,
   guest_id INTEGER,
   PRIMARY KEY(user_id, guest_id)
);

then do inserts into guest with a sequence of operations like this
(assuming the user_id of interest is "111"):

UPDATE user
SET guest_id_ctr = guest_id_ctr + 1
WHERE user_id = 111
RETURNING guest_id_ctr;

-- Now, using the value obtained with the previous statement, say "4":

INSERT INTO guest (user_id, guest_id)
VALUES (111, 4);

That assumes you're using a version of PostgreSQL new enough to support
UPDATE ... RETURNING. If not, you need to use SELECT FOR UPDATE to
obtain the value, followed by a separate UPDATE statement to actually
increment it.

If you do need contiguous values of guest_ids within a given user_id
then you'll need to use a different approach, most likely PL/PgSQL
triggers or the use of function wrappers for DML operations on the
table. However, in most cases an application requirement of contiguous
IDs is a design fault that should be fixed, rather than hacked around in
the database.

--
Craig Ringer