Csaba Nagy wrote:
> First of all, thanks for all the suggestions.
>
>> put a SERIAL primary key on the table
> Or:
>> Maybe add OIDs to the table, and delete based on the OID number?
>
> No, this is not acceptable, it adds overhead to the insertions. Normally
> the overhead will be small enough, but on occasions it is noticeable.
How about using the following?
delete from <table>
where ctid in (select ctid from <table> limit <num>);
Here's a live example:
db=> select count(*) from sometable;
count
-------
381
(1 row)
db=> delete from sometable where ctid in (select ctid from sometable
limit 5);
DELETE 5
db=> select count(*) from sometable;
count
-------
376
(1 row)
Does anyone see problems with the above delete?
---
Anyway, do you have figures of the overhead you mentioned? How fast is
PG (with OIDs) and how does it compare with the alternatives you're using?
In your original post you wrote:
> On other databases, it is possible to limit the delete to a maximum
> number of rows to be deleted.
I don't know what "other databases" you're referring to, but are you
sure they don't have anything similar to PG OIDs, without even you
knowing it, and without any option to disable them? It's even possible
that in the "other databases" you're already paying that overhead, and
that makes it quite acceptable in PG, too. Or maybe there's some other
kind of overhead, much bigger than the OIDs one?
For example, you're using a high overhead mechanism to consume rows
(triggers on delete, insering into another table), are you sure that in
the "other databases" this doesn't slow all the inserts down much more
than adding OIDs on PG would do? PG has MVCC, I guess that makes deletes
and inserts on the same table play nice to each other, but how about the
other databases? Do they need to acquire a lock on inserts/deletes? That
would make your concurrent inserts/deletes much slower that just adding
a column to the table. Maybe you could even add an index, and still be
faster thanks to MVCC.
Also, the trigger is fired once for each deleted row. Have you
considered a single stored procedure that loops over the rows to be
processed, instead of relaying on deletes and triggers?
.TM.