diff options
| -rw-r--r-- | doc/build/changelog/changelog_10.rst | 17 | ||||
| -rw-r--r-- | doc/build/changelog/migration_10.rst | 58 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/suite/test_reflection.py | 4 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_reflection.py | 12 |
5 files changed, 93 insertions, 1 deletions
diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index f2bd43a76..ad9eefa09 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -22,6 +22,23 @@ 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 c4157266b..e148e7d70 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 baa640eaa..034ee9076 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 08b858b47..e58b6f068 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 8de71216e..0dda1fa45 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -323,6 +323,18 @@ class ReflectionTest(fixtures.TestBase): 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): meta1 = self.metadata |