Re: Chunk Delete

On Thu, Nov 15, 2007 at 04:18:27PM +0200, Abraham, Danny wrote:
> The temporary sequence works perfectly for me.

You may want to read my other message and test again. You are well
into implementation details here and the trick that was suggested will
(with the current version of PG delete the first x rows that appear
in the table. I've got no idea what 8.3 will do with its new seqscan
piggybacking code, it may be clever enough to start deleting out of the
middle of the table if it can.

When you VACUUM a table, these rows will be marked as unused and your
new data will be inserted into them. i.e. your FIFO will only act like a
FIFO until you VACUUM it, then all hell will break loose.

Sam

p.s. the test I did, was to run this once:

CREATE TABLE foo (
id serial,
value INTEGER
);

and then run several iterations of:

INSERT INTO foo (value)
SELECT v FROM generate_series(1,10000) x(v);

CREATE TEMPORARY SEQUENCE foo_seq;
DELETE FROM foo WHERE nextval('foo_seq') < 5000;
DROP SEQUENCE foo_seq;
VACUUM foo;

Checking how the FIFO was working at various points with:

SELECT MIN(id), MAX(id) FROM foo;