has_table() in Postgresql doesnt work for temporary

[Mike Bayer]

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')
user_tmp.create(e, checkfirst=True)

user_tmp.create(e, checkfirst=True)

[Mike Bayer]

apparently we've been using pg_table_is_visible for many years in get_table_oid(), so this should be safe for 0.9.

[Mike Bayer]

mmmm, scratch that, apparently PG allows a real table to overwrite the name of an existing temp table silently, so this is a behavior change:

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_tmp = Table(
        "user_tmp", m2,
        Column("id", INT, primary_key=True),
        Column('name', VARCHAR(50)),
    )

    user_tmp.create(conn, checkfirst=True)

needs a migration doc.

[Mike Bayer]

• 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

→ <<cset 87bfcf91e965>>