Wow! Thank you!
I guess this is a near-light-speed support in practice! :-)
I stumbled upon this issue while I was trying to figure out how to work with
temporary tables in SQLAlchemy. Final version of my code does not use the
checkfirst flag at all, because I know when to create and when to drop the temp
table.
However it is nice to know that it will work correctly in all circumstances.
Please, keep up the excellent work, because SQLAlchemy is probably the best
library I have ever worked with! :-)
Thank you,
Ladislav Lenart
On 4.12.2014 18:03, Michael Bayer wrote:
>
>> On Dec 4, 2014, at 9:36 AM, Ladislav Lenart <lenart...@volny.cz> wrote:
>>
>> Hello.
>>
>> The following code crashes:
>>
>> # db init...
>> meta = MetaData()
>> foo = Table('tmp_foo' meta,
>> Column('id', Integer, primary_key=True),
>> prefixes=['TEMPORARY'],
>> )
>> conn = session.connection()
>> foo.create(conn, checkfirst=True)
>> foo.create(conn, checkfirst=True)
>>
>>
>> This is because the 'check-first' logic emmits the following SQL:
>>
>> SELECT relname
>> FROM
>> pg_class c
>> JOIN pg_namespace n ON n.oid=c.relnamespace
>> WHERE
>> n.nspname=CURRENT_SCHEMA()
>> AND relname='tmp_foo'
>>
>>
>> The culrpit is in a call to CURRENT_SCHEMA() because temporary tables in
>> postgres reside in a special schema.
>>
>> Is there a fix/workaround for this?
>
> issue
> https://bitbucket.org/zzzeek/sqlalchemy/issue/3264/has_table-in-postgresql-doesnt-work-for
> has been created and fixed for 1.0.
>
> For now, I’d avoid using “checkfirst” for a temporary table. As this is only
> local to a transaction it should be straightforward just to make sure the
> code is only calling create() once. If this is unavoidable, then place the
> call to create() within a conditional that runs the new query:
>
> "select relname from pg_class c join pg_namespace n on "
> "n.oid=c.relnamespace where "
> "pg_catalog.pg_table_is_visible(c.oid) "
> "and relname=:name”,
>
> PG’s behavior unfortunately allows a non-temporary table to silently
> overwrite a temporary one, so this change is a significant behavioral change
> to the checkfirst flag.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
