Thread: Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist
On Sun, Sep 2, 2012 at 05:40:54PM +0000, james@illusorystudios.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7515
> Logged by: James Bellinger
> Email address: james@illusorystudios.com
> PostgreSQL version: 9.1.5
> Operating system: Ubuntu Linux 12.04 Server
> Description:
>
> If the table being referenced has a schema in its name, and the schema does
> not exist, DROP TABLE IF EXISTS will have an ERROR instead of a NOTICE.
>
> So for instance,
> DROP TABLE IF EXISTS bar;
> This is a NOTICE if bar does not exist.
>
> DROP TABLE IF EXISTS foo.bar;
> This is an ERROR if foo does not exist, even though that implies bar does
> not exist which means it should be a NOTICE.
>
> Saw this because it was making a drop/recreate transaction fail on me, after
> I changed some code to use a schema.
I looked at this bug report from September. The problem is that
LookupExplicitNamespace() doesn't have a missing_ok parameter, even
though get_namespace_oid(), which it calls, does. By adding a
missing_ok parameter and passing it cleanly, I fixed the problem:
test=> DROP TABLE IF EXISTS foo.bar;
NOTICE: table "bar" does not exist, skipping
DROP TABLE
Patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachment
Re: Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist
From
Pavel Stehule
Date:
2013/1/25 Bruce Momjian <bruce@momjian.us>: > On Sun, Sep 2, 2012 at 05:40:54PM +0000, james@illusorystudios.com wrote: >> The following bug has been logged on the website: >> >> Bug reference: 7515 >> Logged by: James Bellinger >> Email address: james@illusorystudios.com >> PostgreSQL version: 9.1.5 >> Operating system: Ubuntu Linux 12.04 Server >> Description: >> >> If the table being referenced has a schema in its name, and the schema does >> not exist, DROP TABLE IF EXISTS will have an ERROR instead of a NOTICE. >> >> So for instance, >> DROP TABLE IF EXISTS bar; >> This is a NOTICE if bar does not exist. >> >> DROP TABLE IF EXISTS foo.bar; >> This is an ERROR if foo does not exist, even though that implies bar does >> not exist which means it should be a NOTICE. >> >> Saw this because it was making a drop/recreate transaction fail on me, after >> I changed some code to use a schema. > > I looked at this bug report from September. The problem is that > LookupExplicitNamespace() doesn't have a missing_ok parameter, even > though get_namespace_oid(), which it calls, does. By adding a > missing_ok parameter and passing it cleanly, I fixed the problem: > > test=> DROP TABLE IF EXISTS foo.bar; > NOTICE: table "bar" does not exist, skipping > DROP TABLE > > Patch attached. > +1 Pavel > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On 2013-01-24 20:45:20 -0500, Bruce Momjian wrote: > On Sun, Sep 2, 2012 at 05:40:54PM +0000, james@illusorystudios.com wrote: > > The following bug has been logged on the website: > > > > Bug reference: 7515 > > Logged by: James Bellinger > > Email address: james@illusorystudios.com > > PostgreSQL version: 9.1.5 > > Operating system: Ubuntu Linux 12.04 Server > > Description: > > > > If the table being referenced has a schema in its name, and the schema does > > not exist, DROP TABLE IF EXISTS will have an ERROR instead of a NOTICE. > > > > So for instance, > > DROP TABLE IF EXISTS bar; > > This is a NOTICE if bar does not exist. > > > > DROP TABLE IF EXISTS foo.bar; > > This is an ERROR if foo does not exist, even though that implies bar does > > not exist which means it should be a NOTICE. > > > > Saw this because it was making a drop/recreate transaction fail on me, after > > I changed some code to use a schema. > > I looked at this bug report from September. The problem is that > LookupExplicitNamespace() doesn't have a missing_ok parameter, even > though get_namespace_oid(), which it calls, does. By adding a > missing_ok parameter and passing it cleanly, I fixed the problem: > > test=> DROP TABLE IF EXISTS foo.bar; > NOTICE: table "bar" does not exist, skipping > DROP TABLE > > Patch attached. That makes it a HEAD only fix though. I personally don't have a problem with that, but others might... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Jan 25, 2013 at 02:22:40PM +0100, Andres Freund wrote: > On 2013-01-24 20:45:20 -0500, Bruce Momjian wrote: > > On Sun, Sep 2, 2012 at 05:40:54PM +0000, james@illusorystudios.com wrote: > > > The following bug has been logged on the website: > > > > > > Bug reference: 7515 > > > Logged by: James Bellinger > > > Email address: james@illusorystudios.com > > > PostgreSQL version: 9.1.5 > > > Operating system: Ubuntu Linux 12.04 Server > > > Description: > > > > > > If the table being referenced has a schema in its name, and the schema does > > > not exist, DROP TABLE IF EXISTS will have an ERROR instead of a NOTICE. > > > > > > So for instance, > > > DROP TABLE IF EXISTS bar; > > > This is a NOTICE if bar does not exist. > > > > > > DROP TABLE IF EXISTS foo.bar; > > > This is an ERROR if foo does not exist, even though that implies bar does > > > not exist which means it should be a NOTICE. > > > > > > Saw this because it was making a drop/recreate transaction fail on me, after > > > I changed some code to use a schema. > > > > I looked at this bug report from September. The problem is that > > LookupExplicitNamespace() doesn't have a missing_ok parameter, even > > though get_namespace_oid(), which it calls, does. By adding a > > missing_ok parameter and passing it cleanly, I fixed the problem: > > > > test=> DROP TABLE IF EXISTS foo.bar; > > NOTICE: table "bar" does not exist, skipping > > DROP TABLE > > > > Patch attached. > > That makes it a HEAD only fix though. I personally don't have a problem > with that, but others might... This turns an error into a notice/success. I don't think we would ever backpatch such a behavioral change. I realize that nothing changes either way in the database, but applications checking for return status might care about it. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist
From
Bruce Momjian
Date:
On Thu, Jan 24, 2013 at 08:45:20PM -0500, Bruce Momjian wrote: > On Sun, Sep 2, 2012 at 05:40:54PM +0000, james@illusorystudios.com wrote: > > The following bug has been logged on the website: > > > > Bug reference: 7515 > > Logged by: James Bellinger > > Email address: james@illusorystudios.com > > PostgreSQL version: 9.1.5 > > Operating system: Ubuntu Linux 12.04 Server > > Description: > > > > If the table being referenced has a schema in its name, and the schema does > > not exist, DROP TABLE IF EXISTS will have an ERROR instead of a NOTICE. > > > > So for instance, > > DROP TABLE IF EXISTS bar; > > This is a NOTICE if bar does not exist. > > > > DROP TABLE IF EXISTS foo.bar; > > This is an ERROR if foo does not exist, even though that implies bar does > > not exist which means it should be a NOTICE. > > > > Saw this because it was making a drop/recreate transaction fail on me, after > > I changed some code to use a schema. > > I looked at this bug report from September. The problem is that > LookupExplicitNamespace() doesn't have a missing_ok parameter, even > though get_namespace_oid(), which it calls, does. By adding a > missing_ok parameter and passing it cleanly, I fixed the problem: > > test=> DROP TABLE IF EXISTS foo.bar; > NOTICE: table "bar" does not exist, skipping > DROP TABLE > > Patch attached. Patch applied to git head. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +