Thread: Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist

Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist

From
Bruce Momjian
Date:
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
>



Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist

From
Andres Freund
Date:
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



Re: [BUGS] BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist

From
Bruce Momjian
Date:
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. +