sqlalchemy-tickets — trac tickets and updates forwarded to this list

Re: [Sqlalchemy-tickets] [sqlalchemy] #2928: yield_per working with oursql but not with mysqldb

[sqlalchemy <mi...@zz...>]

#2928: yield_per working with oursql but not with mysqldb
-----------------------------------+------------------------------------
      Reporter:  postpawl          |      Owner:
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:
     Component:  mysql             |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => invalid
 * status_field:  awaiting triage => completed/closed


Comment:

 this is MySQLdb's behavior, not SQLAlchemy's.   The docs are pretty clear
 http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html?highlight=yield_per#sqlalchemy.orm.query.Query.yield_per:

     Also note that while yield_per() will set the stream_results execution
 option to True, currently this is only understood by psycopg2 dialect
 which will stream results using server side cursors instead of pre-buffer
 all rows for this query. Other DBAPIs pre-buffer all rows before making
 them available.

 OurSQL is another DBAPI that has the advantage of being able to stream
 rows.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2928#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2928: yield_per working with oursql but not with mysqldb

[sqlalchemy <mi...@zz...>]

#2928: yield_per working with oursql but not with mysqldb
----------------------+-----------------------------------------
 Reporter:  postpawl  |           Owner:
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:
Component:  mysql     |        Severity:  no triage selected yet
 Keywords:            |  Progress State:  awaiting triage
----------------------+-----------------------------------------
 I have a large query which uses all of my system's memory unless I use
 yield_per.

 While using the mysqldb library in the sqlalchemy connection string and
 watching TOP, the query will eat up all the systems memory (and get
 killed) with or without yield_per. However, when I use oursql - the memory
 usage will stay steady while I use yield_per (as expected).

 You can simulate this by using the mysqldb library + a query using
 query(table).yield_per(5) on an extremely large dataset on a server with a
 small amount of RAM.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2928>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by vrobin):

 After adding missing line:

 {{{
 reflection_options = ('postgresql_ignore_search_path', )
 }}}


 The value of postgresql_ignore_search_path remains coherent between all
 tables and the patch now also works with failing environment.

 '''AFAICT, the patch, once correctly applied, solves the problem.'''


 One last question about this patch... did you really meant


 {{{
        (schema.Table, {
             "ignore_search_path": False
 }}}


 And not

 {{{
        (schema.Table, {
             "postgresql_ignore_search_path": False
 }}}

 ?

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:15>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2927: coalesce compiling to "Annotatedcoalesce" in SQL

[sqlalchemy <mi...@zz...>]

#2927: coalesce compiling to "Annotatedcoalesce" in SQL
---------------------------------------+----------------------------------
      Reporter:  orutherfurd_carnegie  |      Owner:  zzzeek
          Type:  defect                |     Status:  closed
      Priority:  highest               |  Milestone:  0.9.xx
     Component:  orm                   |   Severity:  minor - half an hour
    Resolution:  fixed                 |   Keywords:
Progress State:  completed/closed      |
---------------------------------------+----------------------------------

Comment (by orutherfurd_carnegie):

 Great, thank you!

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2927#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2927: coalesce compiling to "Annotatedcoalesce" in SQL

[sqlalchemy <mi...@zz...>]

#2927: coalesce compiling to "Annotatedcoalesce" in SQL
---------------------------------------+----------------------------------
      Reporter:  orutherfurd_carnegie  |      Owner:  zzzeek
          Type:  defect                |     Status:  closed
      Priority:  highest               |  Milestone:  0.9.xx
     Component:  orm                   |   Severity:  minor - half an hour
    Resolution:  fixed                 |   Keywords:
Progress State:  completed/closed      |
---------------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => highest
 * status:  new => closed
 * resolution:   => fixed
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => completed/closed


Comment:

 nice catch, r8afb69ced876200c6bc40131089

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2927#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2927: coalesce compiling to "Annotatedcoalesce" in SQL

[sqlalchemy <mi...@zz...>]

#2927: coalesce compiling to "Annotatedcoalesce" in SQL
---------------------------------+-----------------------------------------
 Reporter:                       |           Owner:  zzzeek
  orutherfurd_carnegie           |          Status:  new
     Type:  defect               |       Milestone:  0.9.xx
 Priority:  medium               |        Severity:  no triage selected yet
Component:  orm                  |  Progress State:  awaiting triage
 Keywords:                       |
---------------------------------+-----------------------------------------
 I have a query that does
 {{{sqlalchemy.func.lower(sqlalchemy.func.coalesce(a, b))}}}.  In 0.9.x,
 the generated SQL is "ORDER BY lower(Annotatedcoalesce(" whereas in 0.8.4
 it's "ORDER BY lower(coalesce(".

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2927>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by zzzeek):

 patch is updated please try again

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:14>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by vrobin):

 With failing environment (pgsql 9.1 / linux), patch fails to correct the
 problem with the real database and application.

 Behavior is rather weird to me because the parameter
 "postgresql_ignore_search_path" changes during introspection.

 This can be seen after adding this trace in dialects/postgresql/base.py
 (line 1981):

 {{{
     @reflection.cache
     def get_foreign_keys(self, connection, table_name, schema=None,
 postgresql_ignore_search_path=False, **kw):
         print "XXXXXY", postgresql_ignore_search_path, table_name, schema
 }}}

 and calling this:

 {{{
 metadata = MetaData(bind=engine, schema=SCHEMA)
 metadata.bind.echo = True
 print sqlalchemy.__version__
 print "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
 metadata.reflect(postgresql_ignore_search_path=True)   ### <= Here
 print "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ"
 }}}

 The result is the following
 XXXXXY True table1 mymainschema
 XXXXXY True table2 mymainschema
 XXXXXY True table3 mymainschema
 XXXXXY True table4 mymainschema
 XXXXXY False table5 mymainschema
 [...]
 XXXXXY False tableX mymainschema

 Change from True to False, doesn't occur in a table having a dependency in
 secondary schema.

 If I change default value from
 def get_foreign_keys(self, connection, table_name, schema=None,
 postgresql_ignore_search_path=False, **kw):
 to
 def get_foreign_keys(self, connection, table_name, schema=None,
 postgresql_ignore_search_path=True, **kw):

 Things naturally works.

 PS: It is possible that 9222.patch is incomplete because when I manually
 patch my sqlalchemy sourcetree, It didn't have
 class PGDialect(default.DefaultDialect):
                     "using": False,
                     "where": None,
                     "ops": {}
 (that may have been since 0.9.1 release:
 http://www.sqlalchemy.org/trac/changeset/1af8e2491dcbed723d2cdafd44fd37f1a6908e91#file5
 )

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:13>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2924: RowProxy.__eq__() throws unexpected TypeError

[sqlalchemy <mi...@zz...>]

#2924: RowProxy.__eq__() throws unexpected TypeError
-----------------------------------+----------------------------------
      Reporter:  elic              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.2
     Component:  engine            |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  in queue => completed/closed


Comment:

 check out how I did this in rd4c908ded1e9a7923312f3b33583, I've added test
 coverage ensuring that all equality operators (==, !=, <, >, <=, >=)
 should behave identically to that of a tuple.  reopen if I got it wrong,
 thanks!

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2924#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2925: oracle nested table support

[sqlalchemy <mi...@zz...>]

#2925: oracle nested table support
-------------------------+--------------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  medium       |       Milestone:  0.9.xx
Component:  oracle       |        Severity:  very major - up to 2 days
 Keywords:               |  Progress State:  in queue
-------------------------+--------------------------------------------
 see:

 http://www.orafaq.com/wiki/NESTED_TABLE

 example:

 {{{
 #!python
 SQL> create or replace type str_list as table of varchar2(256 char) not
 null;
   2  /


 create table foo (id integer primary key, data str_list) nested table data
 store as data_tab;

 SQL> insert into foo (id, data) values (1, str_list('a', 'b', 'c'));

 1 row created.

 SQL> select * from foo;

         ID
 ----------
 DATA
 --------------------------------------------------------------------------------
          1
 STR_LIST('a', 'b', 'c')

 }}}

 steps needed:

 1. get the above sequence to work in Python - cx_oracle must have some way
 to get the data in and out.  If cx_oracle doesnt' support custom types
 directly, see if string formatting can be used.  however bound parameters
 must be used.

 2. build out an ARRAY type similar to that of Postgresql.   We can use
 SchemaType here in a similar fashion.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2925>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by vrobin):

 Wow, this is what I call premium service! You're impressive! :)

 I've applied your patch and I can confirm that the test case now works
 perfectly with postgresql_ignore_search_path=True parameter (and fails as
 before when parameter is set to false).

 Strangely, with the database at my hand (a pgsql 9.2 / windows 7 x64), the
 test case fails before the patch and works with the patch, but the orignal
 code with the original database... doesn't fail (even before patch is
 applied). I'll try tomorrow with the failing database (pgsql 9.1 / linux)
 so I can confirm the full resolution of the problem (I have little doubt
 about it)

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:12>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by zzzeek):

 the two test cases are:

 {{{
 # test one
 metadata.reflect(postgresql_ignore_search_path=True)

 # test two
 myfirsttable = Table('myfirsttable', metadata, autoload=True,
 postgresql_ignore_search_path=True)
 }}}

 tests will be specifically in test.dialect.postgresql.test_reflection->
 test_schema_reflection_multi_search_path_ignore / _dontignore.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:11>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by zzzeek):

 OK, you've convinced me, we have enough infrastructure in our reflection
 process now to make this configurable.   patch is attached.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: allow PG search path reflection behavior to be configurable (was: Metadata.reflect failed on different schema foreign key constraint (postgresql))

[sqlalchemy <mi...@zz...>]

#2922: allow PG search path reflection behavior to be configurable
------------------------------+-------------------------------
      Reporter:  vrobin       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.2
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => major - 1-3 hours
 * status_field:  needs questions answered => needs tests
 * milestone:   => 0.9.2


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:9>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
-------------------------------------------+-------------------------------
      Reporter:  vrobin                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  postgres                  |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 looking more closely, it seems like the issue is not as much that we don't
 know the schema, as it is actually in both results, but that we don't know
 how to interpret this schema compared to how the user has set up the Table
 defs.   That is, someone might have schema A, B, C in their search path,
 and they want to use `Table` in a schemaless fashion across all three.  So
 when we reflect a constraint from A to B, we want the correct "B" Table -
 e.g. the one with or without the schema.

 So i think the issue is more like we had to make a choice how to appease
 people using table reflection in different ways with different search
 paths.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:8>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
-------------------------------------------+-------------------------------
      Reporter:  vrobin                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  postgres                  |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 so this is what the issue is - there is probably a way to fix this, which
 would mean we'd have to no longer rely upon a particular PG function we're
 using.  if you use 'echo="debug"' you can see the rows coming back.
 Here's with the schemas in the search path:

 {{{
           SELECT r.conname,
                 pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                 n.nspname as conschema
           FROM  pg_catalog.pg_constraint r,
                 pg_namespace n,
                 pg_class c

           WHERE r.conrelid = %(table)s AND
                 r.contype = 'f' AND
                 c.oid = confrelid AND
                 n.oid = c.relnamespace
           ORDER BY 1

 {'table': 538795}
 Col ('conname', 'condef', 'conschema')
 Row (u'fkdc7e8de5969fc80', u'FOREIGN KEY (city_id) REFERENCES
 tableinsecondarychema(city_code)', u'secondaryschema')
 }}}

 here's with a plain search_path:

 {{{
           SELECT r.conname,
                 pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                 n.nspname as conschema
           FROM  pg_catalog.pg_constraint r,
                 pg_namespace n,
                 pg_class c

           WHERE r.conrelid = %(table)s AND
                 r.contype = 'f' AND
                 c.oid = confrelid AND
                 n.oid = c.relnamespace
           ORDER BY 1

 {'table': 538812}
 Col ('conname', 'condef', 'conschema')
 Row (u'fkdc7e8de5969fc80', u'FOREIGN KEY (city_id) REFERENCES
 secondaryschema.tableinsecondarychema(city_code)', u'secondaryschema')
 }}}

 so basically pg_get_constraintdef() takes the search path into account.
 We'd need to not use that function anymore.  I'm not sure why that hasn't
 been tried yet.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
-------------------------------------------+-------------------------------
      Reporter:  vrobin                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  postgres                  |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by vrobin):

 {{{
 Postgresql *does not tell us the schema of the constraint* if that schema
 is in the search path.
 }}}

 Ok, this is clear! As you said "game over". I could try to look around if
 I can find some mean to circumvent this limitation, but it would be a bit
 presomptuous of me to think that I can find a way where you didn't :).

 Would it be possible that backup/change/introspect/restore search_path
 could solve this issue? (maybe it would break many, many other things?)


 Anyway, thanks for digging into this "non issue". I will probably just
 redefine search_path at connection level. Maybe this ticket will be useful
 for other people that hit similar search_path problems (I've seen report
 like this one on debian mailing-list that were left unanswered).

 To conclude, many many thanks for the great job you're doing on
 SqlAlchemy.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:6>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] Batch modify: #2913, #2921, #2923, #2924

[sqlalchemy <mi...@zz...>]

Batch modification to #2913, #2921, #2923, #2924 by zzzeek:
milestone to 0.9.2

--
Tickets URL: <http://www.sqlalchemy.org/trac/query?id=2913%2C2921%2C2923%2C2924>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2924: RowProxy.__eq__() throws unexpected TypeError

[sqlalchemy <mi...@zz...>]

#2924: RowProxy.__eq__() throws unexpected TypeError
---------------------------+----------------------------------
      Reporter:  elic      |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.9.xx
     Component:  engine    |   Severity:  minor - half an hour
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => in queue


Comment:

 or just throwing an isinstance() in there.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2924#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
-------------------------------------------+-------------------------------
      Reporter:  vrobin                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  postgres                  |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 Replying to [comment:4 vrobin]:
 > There may be an intrinsic problem with this use of explicit schema (in
 DDL, in SQLAlchemy reflection) along with an implicit schema resolution
 (specific search_path) which is set at the db level, but I fail to see it.

 there is, and it is exactly what is being referred to in the document I
 linked:  http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html
 #remote-cross-schema-table-introspection

 Postgresql *does not tell us the schema of the constraint* if that schema
 is in the search path.   E.g., a particular query that normally tells us
 "refers to someotherschema.sometable.somecolumn" now tells us only
 "sometable.somecolumn".  That's it, game over, we are not told what the
 schema name is.    A lot of work went into figuring this out.  I'll go
 over your test later but pretty much this issue is a "wontfix".

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:5>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
-------------------------------------------+-------------------------------
      Reporter:  vrobin                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  postgres                  |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by vrobin):

 You were absolutely right, problem isn’t related to sqlalchemy upgrades
 (from 0.7.10 to 0.8.4, then 0.9.1). Both upgrade and search_path
 configuration were changed in a close period of time.

 Current search_path configuration is:
 '"$user", public, mymainschema, secondaryschema,

 I can't be sure, but I suspect that before, search_path looked like this:
 '"$user", mymainschema, public,


 I've run attached test case, it fails with search_path set to
         "$user", public, mymainschema, secondaryschema;

 But runs flawlesslywith search_path set to
         "$user", public


 As you can see in the test log below, it seems that the schema parameter
 override schema specified in foreign key declaration.

 There may be an intrinsic problem with this use of explicit schema (in
 DDL, in SQLAlchemy reflection) along with an implicit schema resolution
 (specific search_path) which is set at the db level, but I fail to see it.

 Initially, my goal was to reflect only tables in "mymainschema"
 (reflecting linked to "secondaryschema" is a good behaviour in my case).

 I hope this helps you understand/qualify the problem.


 {{{
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine select
 version()
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine select
 current_schema()
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine CREATE SCHEMA
 mymainschema
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine CREATE SCHEMA
 secondaryschema
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine
     CREATE TABLE secondaryschema.tableinsecondarychema (
         city_code VARCHAR(2) PRIMARY KEY
     )

 2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,447 INFO sqlalchemy.engine.base.Engine
 CREATE TABLE mymainschema.myfirsttable
 (
   name character varying(255),
   city_id character varying(255),
   CONSTRAINT myfirsttable_pkey PRIMARY KEY (name),
   CONSTRAINT fkdc7e8de5969fc80 FOREIGN KEY (city_id)
       REFERENCES secondaryschema.tableinsecondarychema (city_code) MATCH
 SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
 )

 2014-01-28 10:37:25,447 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine SELECT relname
 FROM pg_class c WHERE relkind = 'r' AND 'mymainschema' = (select nspname
 from pg_namespace n where n.oid = c.relnamespace)
 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine
             SELECT c.oid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE (n.nspname = %(schema)s)
             AND c.relname = %(table_name)s AND c.relkind in ('r','v')

 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {'table_name':
 u'myfirsttable', 'schema': u'mymainschema'}
 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine
             SELECT a.attname,
               pg_catalog.format_type(a.atttypid, a.atttypmod),
               (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                 FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
                AND a.atthasdef)
               AS DEFAULT,
               a.attnotnull, a.attnum, a.attrelid as table_oid
             FROM pg_catalog.pg_attribute a
             WHERE a.attrelid = %(table_oid)s
             AND a.attnum > 0 AND NOT a.attisdropped
             ORDER BY a.attnum

 2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {'table_oid':
 174750}
 2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine
             SELECT t.typname as "name",
                pg_catalog.format_type(t.typbasetype, t.typtypmod) as
 "attype",
                not t.typnotnull as "nullable",
                t.typdefault as "default",
                pg_catalog.pg_type_is_visible(t.oid) as "visible",
                n.nspname as "schema"
             FROM pg_catalog.pg_type t
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
 t.typnamespace
             WHERE t.typtype = 'd'

 2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine
             SELECT t.typname as "name",
                -- no enum defaults in 8.4 at least
                -- t.typdefault as "default",
                pg_catalog.pg_type_is_visible(t.oid) as "visible",
                n.nspname as "schema",
                e.enumlabel as "label"
             FROM pg_catalog.pg_type t
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
 t.typnamespace
                  LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
             WHERE t.typtype = 'e'
             ORDER BY "name", e.oid -- e.oid gives us label order

 2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine {}
 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine
                 SELECT a.attname
                 FROM pg_attribute a JOIN (
                     SELECT unnest(ix.indkey) attnum,
                            generate_subscripts(ix.indkey, 1) ord
                     FROM pg_index ix
                     WHERE ix.indrelid = %(table_oid)s AND ix.indisprimary
                     ) k ON a.attnum=k.attnum
                 WHERE a.attrelid = %(table_oid)s
                 ORDER BY k.ord

 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_oid':
 174750}
 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine
         SELECT conname
            FROM  pg_catalog.pg_constraint r
            WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
            ORDER BY 1

 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_oid':
 174750}
 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine
           SELECT r.conname,
                 pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                 n.nspname as conschema
           FROM  pg_catalog.pg_constraint r,
                 pg_namespace n,
                 pg_class c

           WHERE r.conrelid = %(table)s AND
                 r.contype = 'f' AND
                 c.oid = confrelid AND
                 n.oid = c.relnamespace
           ORDER BY 1

 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table':
 174750}
 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine
             SELECT c.oid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE (n.nspname = %(schema)s)
             AND c.relname = %(table_name)s AND c.relkind in ('r','v')

 2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_name':
 u'tableinsecondarychema', 'schema': u'mymainschema'}
 XXXX Failing Query
             SELECT c.oid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE (n.nspname = :schema)
             AND c.relname = :table_name AND c.relkind in ('r','v')
          tableinsecondarychema mymainschema
 XXXX
 Traceback (most recent call last):
   File "J:\project\loid\schema_reflect_test.py", line 43, in <module>
     metadata.reflect()
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 2809, in reflect
     Table(name, self, **reflect_opts)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 332, in __new__
     table._init(name, metadata, *args, **kw)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 396, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 413, in _autoload
     self, include_columns, exclude_columns
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\base.py", line 1118, in run_callable
     return callable_(self, *args, **kwargs)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\default.py", line 262, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\reflection.py", line 513, in reflecttable
     **reflection_options
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 332, in __new__
     table._init(name, metadata, *args, **kw)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 396, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\schema.py", line 413, in _autoload
     self, include_columns, exclude_columns
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\base.py", line 1118, in run_callable
     return callable_(self, *args, **kwargs)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\default.py", line 262, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\reflection.py", line 414, in reflecttable
     for col_d in self.get_columns(table_name, schema, **tblkw):
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\reflection.py", line 254, in get_columns
     **kw)
   File "<string>", line 1, in <lambda>
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\dialects\postgresql\base.py", line 1683, in
 get_columns
     info_cache=kw.get('info_cache'))
   File "<string>", line 1, in <lambda>
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\engine\reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "J:\project\python-venv\loid\lib\site-
 packages\sqlalchemy\dialects\postgresql\base.py", line 1598, in
 get_table_oid
     raise exc.NoSuchTableError(table_name)
 sqlalchemy.exc.NoSuchTableError: tableinsecondarychema

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2924: RowProxy.__eq__() throws unexpected TypeError

[sqlalchemy <mi...@zz...>]

#2924: RowProxy.__eq__() throws unexpected TypeError
--------------------+-----------------------------------------
 Reporter:  elic    |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:  0.9.xx
Component:  engine  |        Severity:  no triage selected yet
 Keywords:          |  Progress State:  awaiting triage
--------------------+-----------------------------------------
 As of 0.9, comparing a `sqlalchemy.engine.result.RowProxy` against any
 non-iterable (e.g. doing `RowProxy(...) == 123`) results in a !TypeError,
 rather 0.8's behavior where it would return False. This breaks the normal
 python behavior where different types can be compared for equality (e.g.
 `assert {} != 5`). I think this was caused by rev
 02f21ffcf366da406795334d2fc6908.

 A simple fix would probably be wrapping the existing `RowProxy.__eq__()`
 in a try/except that catches !TypeError, and returns False; though
 limiting try/except to just the `tuple(other)` call would prevent it from
 masking other unrelated !TypeErrors.

 That changeset also causes `RowProxy() < 5` return a !TypeError, but that
 part seems fine to me, since Python 3 made that an expected behavior of
 `__lt__()` and friends (e.g. `None < 1` now returns
 `TypeError("unorderable types")`).

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2924>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2923: schema naming convention

[sqlalchemy <mi...@zz...>]

#2923: schema naming convention
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  schema       |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by zzzeek):

 needs tests + docs, see also:

 https://bitbucket.org/zzzeek/alembic/issue/169/adding-an-anonymous-unique-
 constraint-to

 https://bitbucket.org/zzzeek/alembic/issue/147/default-naming

 https://bitbucket.org/zzzeek/alembic/issue/133/add-documentation-examples-
 for-constraint

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2923#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2923: schema naming convention

[sqlalchemy <mi...@zz...>]

#2923: schema naming convention
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  schema       |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------
Changes (by zzzeek):

 * severity:  no triage selected yet => major - 1-3 hours
 * component:  cextensions => schema
 * priority:  medium => high
 * milestone:   => 0.9.xx
 * status_field:  awaiting triage => needs tests
 * type:  defect => enhancement


Old description:

> see attached

New description:

 see attached

 {{{
 #!python

 convention = {
   "ix": 'ix_%(column_0_label)s',
   "uq": "uq_%(table_name)s_%(column_0_name)s",
   "ck": "ck_%(table_name)s_%(constraint_name)s",
   "fk":
 "fk_%(table_name)s_%(parent_column_0_name)s_%(referred_table_name)s",
   "pk": "pk_%(table_name)s"
 }

 metadata = MetaData(naming_convention=convention)
 }}}

--

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2923#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2923: schema naming convention

[sqlalchemy <mi...@zz...>]

#2923: schema naming convention
-------------------------+-----------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  medium       |       Milestone:
Component:  cextensions  |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 see attached

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2923>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python