Thread: More efficient DELETE ... ?
This looks painful ...
DELETE FROM webhit_referer_raw WHERE oid IN ( SELECT w.oid FROM webhit_referer_raw w,
referrer_datar 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
> > 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
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
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, Pennsylvania19026