pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
More efficient DELETE ... ?
7+ messages / 3 participants
[nested] [flat]

* More efficient DELETE ... ?
@ 2000-01-22 17:20  The Hermit Hacker <scrappy@hub.org>
  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:56  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: The Hermit Hacker <scrappy@hub.org>
  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 18:07  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 19:37  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: 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 21:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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: [SQL] More efficient DELETE ... ?
@ 2000-01-22 23:18  The Hermit Hacker <scrappy@hub.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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

* Re: More efficient DELETE ... ?
@ 2000-09-30 02:24  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.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


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