pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddeleting rows in specific order
6+ messages / 4 participants
[nested] [flat]
* deleting rows in specific order
@ 2006-10-09 20:14 Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Drotos @ 2006-10-09 20:14 UTC (permalink / raw)
To: pgsql-sql
Hi,
What is the best way to do something like:
delete from tablename where something order by somefield...
Daniel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deleting rows in specific order
@ 2006-10-09 22:00 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Frank Bax @ 2006-10-09 22:00 UTC (permalink / raw)
To: pgsql-sql
At 04:14 PM 10/9/06, Daniel Drotos wrote:
>What is the best way to do something like:
>
>delete from tablename where something order by somefield...
You cannot, because it doesn't make sense. The "order by" clause is not
valid on delete statement. Queries from other processes that start while
your delete is running will either see all the deleted rows or (perhaps)
they will see none of them.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deleting rows in specific order
@ 2006-10-11 12:48 Markus Schaber <schabi@logix-tt.com>
parent: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2006-10-11 12:48 UTC (permalink / raw)
To: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>; +Cc: pgsql-sql
Hi, Daniel,
Daniel Drotos wrote:
> What is the best way to do something like:
>
> delete from tablename where something order by somefield...
There should be no need for this, because a delete is atomic due to the
transaction system.
What is it that you're trying to achieve with the ordered delete? Maybe
there's a better way to solve your underlying problem.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deleting rows in specific order
@ 2006-10-11 19:31 Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 6+ messages in thread
From: Daniel Drotos @ 2006-10-11 19:31 UTC (permalink / raw)
To: pgsql-sql
On Wed, 11 Oct 2006, Markus Schaber wrote:
> What is it that you're trying to achieve with the ordered delete? Maybe
> there's a better way to solve your underlying problem.
I've a table which has a field for some info (number) and a date field
(insertion date) and others.
After inserting new rows into this table, very old rows are going to
be deleted. But value of deleted info should be remembered somehow, so
there is a trigger on delete which keeps sum of the deleted info
values in a separate table. This makes the application happy.
Trigger's algorithm is a little bit more dificult which I would be
able to simplify if rows are deleted in date order.
Now I'm doing it in a plpgsql function using a `for in select loop' to
delete rows one by one.
Daniel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deleting rows in specific order
@ 2006-10-13 11:48 Markus Schaber <schabi@logix-tt.com>
parent: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
1 sibling, 0 replies; 6+ messages in thread
From: Markus Schaber @ 2006-10-13 11:48 UTC (permalink / raw)
To: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>; +Cc: pgsql-sql
Hi, Daniel,
Daniel Drotos wrote:
>> What is it that you're trying to achieve with the ordered delete? Maybe
>> there's a better way to solve your underlying problem.
>
> I've a table which has a field for some info (number) and a date field
> (insertion date) and others.
>
> After inserting new rows into this table, very old rows are going to be
> deleted. But value of deleted info should be remembered somehow, so
> there is a trigger on delete which keeps sum of the deleted info values
> in a separate table. This makes the application happy.
>
> Trigger's algorithm is a little bit more dificult which I would be able
> to simplify if rows are deleted in date order.
Ah, I see.
I don't know exactly what you're doing here, but at least for a sum, the
order of summed values is not relevant.
And due to the transaction system, other applications won't see
inconsistent intermediate states, as the whole DELETE run and all
trigger calls are part of the same transaction.
> Now I'm doing it in a plpgsql function using a `for in select loop' to
> delete rows one by one.
I'm afraid that this is the only solution if you can't simplify the
trigger by other means.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deleting rows in specific order
@ 2006-10-16 17:51 Bruno Wolff III <bruno@wolff.to>
parent: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-10-16 17:51 UTC (permalink / raw)
To: Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>; +Cc: pgsql-sql
On Wed, Oct 11, 2006 at 21:31:37 +0200,
Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu> wrote:
> On Wed, 11 Oct 2006, Markus Schaber wrote:
>
> After inserting new rows into this table, very old rows are going to
> be deleted. But value of deleted info should be remembered somehow, so
> there is a trigger on delete which keeps sum of the deleted info
> values in a separate table. This makes the application happy.
You need to be careful if two of these can run at the same time if you
are adding the values to prexisting sums otherwise you might end up summing
just the values from one of the delete sets.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-10-16 17:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-09 20:14 deleting rows in specific order Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
2006-10-11 12:48 ` Markus Schaber <schabi@logix-tt.com>
2006-10-11 19:31 ` Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
2006-10-13 11:48 ` Markus Schaber <schabi@logix-tt.com>
2006-10-16 17:51 ` Bruno Wolff III <bruno@wolff.to>
2006-10-09 22:00 Re: deleting rows in specific order Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox