Re: rotate records

Michael,

Thank you very much for your response.

I tried your solutions but still it looks like it doesn't work when I
delete random records.

select * from foo;
id | val
----+-----
1 | 13
2 | 14
3 | 15
(3 rows)

delete from foo where val = '13';
DELETE 1
delete from foo where val = '15';
DELETE 1

select * from foo;
id | val
----+-----
2 | 14
(1 row)

insert into foo (val) values ('16');
INSERT 34533 1
psimc_db=# select * from foo;
id | val
----+-----
2 | 14
1 | 16
(2 rows)

insert into foo (val) values ('17');
INSERT 34534 1
psimc_db=#
psimc_db=# select * from foo;
id | val
----+-----
1 | 16
2 | 17

As you can see the record with value '14' is gone without explicit
delete, which is not what I expected. Do you have any ideas on how to
avoid this?

Natasha Galkina.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Michael Fuhr
Sent: Saturday, February 25, 2006 10:39 AM
To: Natasha Galkina
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] rotate records

On Sat, Feb 25, 2006 at 10:12:38AM -0700, Michael Fuhr wrote:
> > 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.

I should mention that with the example I posted you can still get
duplicate key violations if enough concurrent transactions insert
into the table at the same time. I'll have to think a little more
about the best way to avoid that.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster