BUG #7515: DROP TABLE IF EXISTS fails if schema does not exist
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.
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. +
Attachments:
drop_table.difftext/x-diff; charset=us-asciiDownload+109-109
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 TABLEPatch 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
--
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 TABLEPatch 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 TABLEPatch 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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 TABLEPatch 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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers