Horton
Menu

Thread

  1. Chatter on DROP SOMETHING IF EXISTS

    Peter Eisentraut <peter_e@gmx.net> — 2007-02-07T13:13:48Z

    What is the practical purpose of the notices emitted by DROP SOMETHING IF 
EXISTS when the object in fact does not exist?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  2. Re: Chatter on DROP SOMETHING IF EXISTS

    Andrew Dunstan <andrew@dunslane.net> — 2007-02-07T14:29:09Z

    Peter Eisentraut wrote:
> What is the practical purpose of the notices emitted by DROP SOMETHING IF 
> EXISTS when the object in fact does not exist?
>
>   
It was asked for ...

http://archives.postgresql.org/pgsql-patches/2005-11/msg00072.php

I realise that doesn't quite answer your question.

cheers

andrew

  3. Re: Chatter on DROP SOMETHING IF EXISTS

    David Fetter <david@fetter.org> — 2007-02-07T18:53:34Z

    On Wed, Feb 07, 2007 at 02:13:48PM +0100, Peter Eisentraut wrote:
> What is the practical purpose of the notices emitted by DROP
> SOMETHING IF EXISTS when the object in fact does not exist?

DROP ... IF EXISTS is guaranteed not to throw an error.  This lets
people write idempotent scripts which run in a transaction :)

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

  4. Re: Chatter on DROP SOMETHING IF EXISTS

    Bruno Wolff III <bruno@wolff.to> — 2007-02-07T19:23:20Z

    On Wed, Feb 07, 2007 at 10:53:34 -0800,
  David Fetter <david@fetter.org> wrote:
> On Wed, Feb 07, 2007 at 02:13:48PM +0100, Peter Eisentraut wrote:
> > What is the practical purpose of the notices emitted by DROP
> > SOMETHING IF EXISTS when the object in fact does not exist?
> 
> DROP ... IF EXISTS is guaranteed not to throw an error.  This lets
> people write idempotent scripts which run in a transaction :)

I don't think that's what his question was. I think it was more along the
lines of why don't we get rid of the notices that are just cluttering things
up.

  5. Re: Chatter on DROP SOMETHING IF EXISTS

    Peter Eisentraut <peter_e@gmx.net> — 2007-02-08T17:54:33Z

    Andrew Dunstan wrote:
> Peter Eisentraut wrote:
> > What is the practical purpose of the notices emitted by DROP
> > SOMETHING IF EXISTS when the object in fact does not exist?
>
> It was asked for ...

The argument was that MySQL does the same.  Which is valid but not 
overriding.

I'm honestly looking for some practical use of this.  We have debated 
other NOTICE messages over the years, but they at least tell you 
something you can use after the command.  In this case, it just tells 
you that the object which you wanted removed no matter what didn't 
exist in the first place, but the state after the command (which is the 
interesting side) is always the same: "gone".  The only use case I see 
is informing about typos, but the system generally doesn't cater to 
that.

The downside is that while I wanted to use the IF EXISTS form to reduce 
the chatter at the beginning of schema loading scripts, this just gives 
me a different spelling of that same chatter.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  6. Re: Chatter on DROP SOMETHING IF EXISTS

    Andrew Dunstan <andrew@dunslane.net> — 2007-02-08T18:00:05Z

    Peter Eisentraut wrote:
> The downside is that while I wanted to use the IF EXISTS form to reduce 
> the chatter at the beginning of schema loading scripts, this just gives 
> me a different spelling of that same chatter.
>
>   

There is possibly a good case for dropping the message level.

cheers

andrew

  7. Re: Chatter on DROP SOMETHING IF EXISTS

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-02-08T18:54:13Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> I'm honestly looking for some practical use of this.  We have debated 
> other NOTICE messages over the years, but they at least tell you 
> something you can use after the command.

The objection I had to the original patch (which didn't return a notice)
was that this seemed actively misleading:

	foo=> DROP TABLE IF EXISTS not_there;
	DROP TABLE
	foo=>

I would be satisfied if the returned command tag were something else,
maybe "NO OPERATION".

			regards, tom lane

  8. Re: Chatter on DROP SOMETHING IF EXISTS

    Jim Nasby <jim@nasby.net> — 2007-02-15T21:10:41Z

    On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I'm honestly looking for some practical use of this.  We have debated 
> > other NOTICE messages over the years, but they at least tell you 
> > something you can use after the command.
> 
> The objection I had to the original patch (which didn't return a notice)
> was that this seemed actively misleading:
> 
> 	foo=> DROP TABLE IF EXISTS not_there;
> 	DROP TABLE
> 	foo=>
> 
> I would be satisfied if the returned command tag were something else,
> maybe "NO OPERATION".

"TABLE blah DID NOT EXIST" might be less confusing...
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

  9. Re: Chatter on DROP SOMETHING IF EXISTS

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-02-16T06:01:14Z

    "Jim C. Nasby" <jim@nasby.net> writes:
> On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote:
>> I would be satisfied if the returned command tag were something else,
>> maybe "NO OPERATION".

> "TABLE blah DID NOT EXIST" might be less confusing...

You're confusing a command tag with a notice.  In the first place,
we shouldn't assume that applications are ready to deal with
indefinitely long command tags (the backend itself doesn't think they
can be longer than 64 bytes); in the second place, they should be
constant strings for the most part so that simple strcmp()s suffice
to see what happened.  Command tags are meant for programs to deal
with, more than humans.

			regards, tom lane

  10. Re: Chatter on DROP SOMETHING IF EXISTS

    Bruce Momjian <bruce@momjian.us> — 2007-02-18T02:02:33Z

    Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote:
> >> I would be satisfied if the returned command tag were something else,
> >> maybe "NO OPERATION".
> 
> > "TABLE blah DID NOT EXIST" might be less confusing...
> 
> You're confusing a command tag with a notice.  In the first place,
> we shouldn't assume that applications are ready to deal with
> indefinitely long command tags (the backend itself doesn't think they
> can be longer than 64 bytes); in the second place, they should be
> constant strings for the most part so that simple strcmp()s suffice
> to see what happened.  Command tags are meant for programs to deal
> with, more than humans.

Yep.  Because IF EXISTS is in a lot of object destruction commands,
adding a modified tag seems very confusing, because in fact the DROP
TABLE did succeed, so to give any other tag seems incorrect.

I think the only option would be to use INFO instead of NOTICE, but
because the output is optional based on whether the object exists, you
might say NOTICE is the right level.

I am afraid we might just need to live with the current behavior.

-- 
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  11. Re: Chatter on DROP SOMETHING IF EXISTS

    Alvaro Herrera <alvherre@commandprompt.com> — 2007-02-19T12:12:18Z

    Bruce Momjian wrote:
> Tom Lane wrote:
> > "Jim C. Nasby" <jim@nasby.net> writes:
> > > On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote:
> > >> I would be satisfied if the returned command tag were something else,
> > >> maybe "NO OPERATION".
> > 
> > > "TABLE blah DID NOT EXIST" might be less confusing...
> > 
> > You're confusing a command tag with a notice.  In the first place,
> > we shouldn't assume that applications are ready to deal with
> > indefinitely long command tags (the backend itself doesn't think they
> > can be longer than 64 bytes); in the second place, they should be
> > constant strings for the most part so that simple strcmp()s suffice
> > to see what happened.  Command tags are meant for programs to deal
> > with, more than humans.
> 
> Yep.  Because IF EXISTS is in a lot of object destruction commands,
> adding a modified tag seems very confusing, because in fact the DROP
> TABLE did succeed, so to give any other tag seems incorrect.

I don't understand -- what problem you got with "NO OPERATION"?  It
seemed a sound idea to me.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  12. Re: Chatter on DROP SOMETHING IF EXISTS

    Peter Eisentraut <peter_e@gmx.net> — 2007-02-19T13:34:32Z

    Am Montag, 19. Februar 2007 13:12 schrieb Alvaro Herrera:
> I don't understand -- what problem you got with "NO OPERATION"?  It
> seemed a sound idea to me.

It seems nonorthogonal.  What if only some of the tables you mentioned did not 
exist?  Do you get "SOME OPERATION"?

There are also other cases where commands don't have an effect but we don't 
explicitly point that out.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  13. Re: Chatter on DROP SOMETHING IF EXISTS

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-02-19T14:57:32Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> Am Montag, 19. Februar 2007 13:12 schrieb Alvaro Herrera:
>> I don't understand -- what problem you got with "NO OPERATION"?  It
>> seemed a sound idea to me.

> It seems nonorthogonal.  What if only some of the tables you mentioned did not 
> exist?  Do you get "SOME OPERATION"?

I'd say you get DROP TABLE as long as at least one table was dropped.

> There are also other cases where commands don't have an effect but we don't 
> explicitly point that out.

The precedent that I'm thinking about is that the command tag for COMMIT
varies depending on what it actually did.

regression=# begin;
BEGIN
regression=# select 1/0;
ERROR:  division by zero
regression=# commit;
ROLLBACK
regression=#

			regards, tom lane

  14. Re: Chatter on DROP SOMETHING IF EXISTS

    Pavan Deolasee <pavan.deolasee@gmail.com> — 2007-02-19T15:14:54Z

    On 2/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Am Montag, 19. Februar 2007 13:12 schrieb Alvaro Herrera:
> >> I don't understand -- what problem you got with "NO OPERATION"?  It
> >> seemed a sound idea to me.
>
> > It seems nonorthogonal.  What if only some of the tables you mentioned
> did not
> > exist?  Do you get "SOME OPERATION"?
>
> I'd say you get DROP TABLE as long as at least one table was dropped.
>
>
How about DROP TABLE <cnt> where 'cnt' is the number of tables dropped ?

Thanks,
Pavan

-- 

EnterpriseDB     http://www.enterprisedb.com

  15. Re: Chatter on DROP SOMETHING IF EXISTS

    Peter Eisentraut <peter_e@gmx.net> — 2007-02-19T15:43:32Z

    Am Montag, 19. Februar 2007 15:57 schrieb Tom Lane:
> The precedent that I'm thinking about is that the command tag for COMMIT
> varies depending on what it actually did.

Some have also argued against that in the past, so I guess we just have 
different ideas of how it should work.  Not a problem.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  16. Re: Chatter on DROP SOMETHING IF EXISTS

    Bruce Momjian <bruce@momjian.us> — 2007-02-19T16:52:28Z

    Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Am Montag, 19. Februar 2007 13:12 schrieb Alvaro Herrera:
> >> I don't understand -- what problem you got with "NO OPERATION"?  It
> >> seemed a sound idea to me.
> 
> > It seems nonorthogonal.  What if only some of the tables you mentioned did not 
> > exist?  Do you get "SOME OPERATION"?
> 
> I'd say you get DROP TABLE as long as at least one table was dropped.

If we went with DROP TABLE if any table was dropped, and NO OPERATION
for none, I am fine with that.  What I didn't want was a different NO
OPERATION-type of message for every object type.

-- 
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +