Re: rotate records

On Fri, Feb 24, 2006 at 04:31:48PM -0800, Natasha Galkina wrote:
> I created sequence
>
> CREATE SEQUENCE event_id_seq
>   INCREMENT 1
>   MINVALUE 1
>   MAXVALUE 5
>   START 1
>   CACHE 1
>   CYCLE;
[...]
> My question is how I can rotate the records in the table. I have maximum
> number of records in the table defined by sequence.

Sequences are just number generators; they don't impose constraints
on tables that use them.  The sequence's maximum value has no bearing
on the number of records that a table can hold.

> Every time when I try to insert a new record, I get an error about
> duplicate key.  Even if I manually delete a record somewhere from
> the middle I still might get this error. If I state CYCLE attribute
> in the sequence, doesn't it mean that while inserting new records
> into database if the maximum is met the old records should be deleted?

No, CYCLE affects only the values returned by the sequence.

> If it is not correct, how can I rotate the records in the table?

One way would be to use a trigger to delete records having the same
event_id as the record being inserted.  Here's an example:

CREATE SEQUENCE foo_seq MINVALUE 1 MAXVALUE 3 CYCLE;

CREATE TABLE foo (
    id   integer PRIMARY KEY DEFAULT nextval('foo_seq')
                 CHECK (id BETWEEN 1 AND 3),
    val  text NOT NULL
);

CREATE FUNCTION foo_rotate() RETURNS trigger AS '
BEGIN
    DELETE FROM foo WHERE id = NEW.id;
    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER foo_insert BEFORE INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE foo_rotate();

INSERT INTO foo (val) VALUES ('one');
INSERT INTO foo (val) VALUES ('two');
INSERT INTO foo (val) VALUES ('three');
SELECT * FROM foo;
 id |  val
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

INSERT INTO foo (val) VALUES ('four');
INSERT INTO foo (val) VALUES ('five');
SELECT * FROM foo;
 id |  val
----+-------
  3 | three
  1 | four
  2 | five
(3 rows)

The CHECK constraint on the primary key ensures that you can't
exceed the record limit by explicitly setting the primary key value.

If you do something like this then be sure to vacuum the table
frequently, as each insert that "rotates" creates a dead tuple due
to the delete.

--
Michael Fuhr