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 TABLE
foo=>
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. +