Thread: Chatter on DROP SOMETHING IF EXISTS
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/
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
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!
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.
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/
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
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 TABLEfoo=>
I would be satisfied if the returned command tag were something else,
maybe "NO OPERATION".
regards, tom lane
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)
"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
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. +
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
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/
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
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
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/
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. +