From 87bfcf91e9659893f17adf307090bc0a4a8a8f23 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Dec 2014 12:01:19 -0500 Subject: [PATCH] - The :meth:`.PGDialect.has_table` method will now query against ``pg_catalog.pg_table_is_visible(c.oid)``, rather than testing for an exact schema match, when the schema name is None; this so that the method will also illustrate that temporary tables are present. Note that this is a behavioral change, as Postgresql allows a non-temporary table to silently overwrite an existing temporary table of the same name, so this changes the behavior of ``checkfirst`` in that unusual scenario. fixes #3264 --- doc/build/changelog/changelog_10.rst | 17 ++++++ doc/build/changelog/migration_10.rst | 58 +++++++++++++++++++ lib/sqlalchemy/dialects/postgresql/base.py | 3 +- .../testing/suite/test_reflection.py | 4 ++ test/dialect/postgresql/test_reflection.py | 12 ++++ 5 files changed, 93 insertions(+), 1 deletion(-) diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index f2bd43a76b..ad9eefa09b 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -21,6 +21,23 @@ series as well. For changes that are specific to 1.0 with an emphasis on compatibility concerns, see :doc:`/changelog/migration_10`. + .. change:: + :tags: bug, postgresql + :tickets: 3264 + + The :meth:`.PGDialect.has_table` method will now query against + ``pg_catalog.pg_table_is_visible(c.oid)``, rather than testing + for an exact schema match, when the schema name is None; this + so that the method will also illustrate that temporary tables + are present. Note that this is a behavioral change, as Postgresql + allows a non-temporary table to silently overwrite an existing + temporary table of the same name, so this changes the behavior + of ``checkfirst`` in that unusual scenario. + + .. seealso:: + + :ref:`change_3264` + .. change:: :tags: bug, sql :tickets: 3260 diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst index c4157266bb..e148e7d70c 100644 --- a/doc/build/changelog/migration_10.rst +++ b/doc/build/changelog/migration_10.rst @@ -276,6 +276,64 @@ running 0.9 in production. :ticket:`2891` +.. _change_3264: + +Postgresql ``has_table()`` now works for temporary tables +--------------------------------------------------------- + +This is a simple fix such that "has table" for temporary tables now works, +so that code like the following may proceed:: + + from sqlalchemy import * + + metadata = MetaData() + user_tmp = Table( + "user_tmp", metadata, + Column("id", INT, primary_key=True), + Column('name', VARCHAR(50)), + prefixes=['TEMPORARY'] + ) + + e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug') + with e.begin() as conn: + user_tmp.create(conn, checkfirst=True) + + # checkfirst will succeed + user_tmp.create(conn, checkfirst=True) + +The very unlikely case that this behavior will cause a non-failing application +to behave differently, is because Postgresql allows a non-temporary table +to silently overwrite a temporary table. So code like the following will +now act completely differently, no longer creating the real table following +the temporary table:: + + from sqlalchemy import * + + metadata = MetaData() + user_tmp = Table( + "user_tmp", metadata, + Column("id", INT, primary_key=True), + Column('name', VARCHAR(50)), + prefixes=['TEMPORARY'] + ) + + e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug') + with e.begin() as conn: + user_tmp.create(conn, checkfirst=True) + + m2 = MetaData() + user = Table( + "user_tmp", m2, + Column("id", INT, primary_key=True), + Column('name', VARCHAR(50)), + ) + + # in 0.9, *will create* the new table, overwriting the old one. + # in 1.0, *will not create* the new table + user.create(conn, checkfirst=True) + +:ticket:`3264` + .. _feature_gh134: Postgresql FILTER keyword diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index baa640eaad..034ee90768 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1942,7 +1942,8 @@ class PGDialect(default.DefaultDialect): cursor = connection.execute( sql.text( "select relname from pg_class c join pg_namespace n on " - "n.oid=c.relnamespace where n.nspname=current_schema() " + "n.oid=c.relnamespace where " + "pg_catalog.pg_table_is_visible(c.oid) " "and relname=:name", bindparams=[ sql.bindparam('name', util.text_type(table_name), diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index 08b858b472..e58b6f068d 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -128,6 +128,10 @@ class ComponentReflectionTest(fixtures.TablesTest): DDL("create temporary view user_tmp_v as " "select * from user_tmp") ) + event.listen( + user_tmp, "before_drop", + DDL("drop view user_tmp_v") + ) @classmethod def define_index(cls, metadata, users): diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 8de71216e8..0dda1fa452 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -322,6 +322,18 @@ class ReflectionTest(fixtures.TestBase): t2 = Table('t', m2, autoload=True) eq_([c.name for c in t2.primary_key], ['t_id']) + @testing.provide_metadata + def test_has_temporary_table(self): + assert not testing.db.has_table("some_temp_table") + user_tmp = Table( + "some_temp_table", self.metadata, + Column("id", Integer, primary_key=True), + Column('name', String(50)), + prefixes=['TEMPORARY'] + ) + user_tmp.create(testing.db) + assert testing.db.has_table("some_temp_table") + @testing.provide_metadata def test_cross_schema_reflection_one(self): -- 2.47.3