pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMore efficient DELETE ... ?
7+ messages / 3 participants
[nested] [flat]
* More efficient DELETE ... ?
@ 2000-01-22 17:20 The Hermit Hacker <scrappy@hub.org>
2000-01-22 17:56 ` Re: [SQL] More efficient DELETE ... ? Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 18:07 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: The Hermit Hacker @ 2000-01-22 17:20 UTC (permalink / raw)
To: pgsql-sql
This looks painful ...
DELETE FROM webhit_referer_raw
WHERE oid IN ( SELECT w.oid
FROM webhit_referer_raw w, referrer_data r
WHERE stat_date < 'Jan 17 2000 15:05:00'
AND w.referrer_url = r.referrer );
But, reading through the man page, it looks like this about the only way
of doing it? Or am I missing something as obvious as the UPDATE I asked
about the other day? :(
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
@ 2000-01-22 17:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2000-01-22 17:56 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql
>
> This looks painful ...
>
> DELETE FROM webhit_referer_raw
> WHERE oid IN ( SELECT w.oid
> FROM webhit_referer_raw w, referrer_data r
> WHERE stat_date < 'Jan 17 2000 15:05:00'
> AND w.referrer_url = r.referrer );
>
> But, reading through the man page, it looks like this about the only way
> of doing it? Or am I missing something as obvious as the UPDATE I asked
> about the other day? :(
Read chapter 8 from my book. That is the only way for DELETE. You
could to EXISTS and that may be faster.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
@ 2000-01-22 18:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-22 19:37 ` Re: [SQL] More efficient DELETE ... ? Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 23:18 ` Re: [SQL] More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
1 sibling, 2 replies; 7+ messages in thread
From: Tom Lane @ 2000-01-22 18:07 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql
The Hermit Hacker <scrappy@hub.org> writes:
> This looks painful ...
> DELETE FROM webhit_referer_raw
> WHERE oid IN ( SELECT w.oid
> FROM webhit_referer_raw w, referrer_data r
> WHERE stat_date < 'Jan 17 2000 15:05:00'
> AND w.referrer_url = r.referrer );
I believe you can do
DELETE FROM webhit_referer_raw
WHERE referrer_url = referrer_data.referrer
AND referrer_data.stat_date < 'Jan 17 2000 15:05:00';
(I assume stat_date is in referrer_data, otherwise this'd be easy.)
This is an example where Postgres' willingness to create implicit FROM
clause entries is a win ... AFAIK you couldn't do it in standard SQL,
since for some reason DELETE doesn't take a FROM clause.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
2000-01-22 18:07 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-22 19:37 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 21:43 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2000-01-22 19:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
> The Hermit Hacker <scrappy@hub.org> writes:
> > This looks painful ...
>
> > DELETE FROM webhit_referer_raw
> > WHERE oid IN ( SELECT w.oid
> > FROM webhit_referer_raw w, referrer_data r
> > WHERE stat_date < 'Jan 17 2000 15:05:00'
> > AND w.referrer_url = r.referrer );
>
> I believe you can do
>
> DELETE FROM webhit_referer_raw
> WHERE referrer_url = referrer_data.referrer
> AND referrer_data.stat_date < 'Jan 17 2000 15:05:00';
>
> (I assume stat_date is in referrer_data, otherwise this'd be easy.)
>
> This is an example where Postgres' willingness to create implicit FROM
> clause entries is a win ... AFAIK you couldn't do it in standard SQL,
> since for some reason DELETE doesn't take a FROM clause.
Good point. I had forgotten all about that.
Should I cover that in my book?
What about the NOTICE we were going to add when this happens. You will
see a WARN_FROM define in parser/*.c. I had to disable the message
because the regression tests were failing. They have to be regenerated
after I do this, but I can't do that here. Do people still want the
message? Can someone regenerate the regression output after I am done?
In fact, someone can just remove the #ifdef WARN_FROM defines around the
code and regenerate the regression tests and all will stay in sync.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
2000-01-22 18:07 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
2000-01-22 19:37 ` Re: [SQL] More efficient DELETE ... ? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-01-22 21:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-30 02:24 ` Re: More efficient DELETE ... ? Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-01-22 21:43 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In fact, someone can just remove the #ifdef WARN_FROM defines around the
> code and regenerate the regression tests and all will stay in sync.
IIRC, Thomas felt that his outer join changes were going to break those
warnings anyway --- that's why we had them commented out. No point in
trying to turn them on until the outer-join dust settles.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
2000-01-22 18:07 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
2000-01-22 19:37 ` Re: [SQL] More efficient DELETE ... ? Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 21:43 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-09-30 02:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2000-09-30 02:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
Just a reminder that this is taken care of. It only WARNs in cases
where we suspect they really made a mistake.
test=> select * from pg_class s where pg_class.oid =34;
NOTICE: Adding missing FROM-clause entry for table pg_class
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In fact, someone can just remove the #ifdef WARN_FROM defines around the
> > code and regenerate the regression tests and all will stay in sync.
>
> IIRC, Thomas felt that his outer join changes were going to break those
> warnings anyway --- that's why we had them commented out. No point in
> trying to turn them on until the outer-join dust settles.
>
> regards, tom lane
>
> ************
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] More efficient DELETE ... ?
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
2000-01-22 18:07 ` Re: [SQL] More efficient DELETE ... ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-22 23:18 ` The Hermit Hacker <scrappy@hub.org>
1 sibling, 0 replies; 7+ messages in thread
From: The Hermit Hacker @ 2000-01-22 23:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Sat, 22 Jan 2000, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> > This looks painful ...
>
> > DELETE FROM webhit_referer_raw
> > WHERE oid IN ( SELECT w.oid
> > FROM webhit_referer_raw w, referrer_data r
> > WHERE stat_date < 'Jan 17 2000 15:05:00'
> > AND w.referrer_url = r.referrer );
>
> I believe you can do
>
> DELETE FROM webhit_referer_raw
> WHERE referrer_url = referrer_data.referrer
> AND referrer_data.stat_date < 'Jan 17 2000 15:05:00';
>
> (I assume stat_date is in referrer_data, otherwise this'd be easy.)
nope, stat_date is in webhit_referer_raw, but it works like a charm,
thanks :)
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-09-30 02:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-22 17:20 More efficient DELETE ... ? The Hermit Hacker <scrappy@hub.org>
2000-01-22 17:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 18:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-22 19:37 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 21:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-30 02:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-22 23:18 ` The Hermit Hacker <scrappy@hub.org>
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