Projet

Général

Profil

0002-sql-use-iterate-on-server-only-for-iterators.patch

Benjamin Dauvergne, 06 octobre 2021 09:51

Télécharger (3,89 ko)

Voir les différences: 

Subject: [PATCH 2/3] sql: use iterate on server only for iterators

Iteration on server is useless when loading all objects in memory and
increase the number of needed connections to the SQL server, at least by
a factor 2 (you need the current one and the one needed to populate the
returned list).

 tests/conftest.py          |  3 ++-
 tests/workflow/test_all.py |  2 +-
 wcs/sql.py                 | 15 +++++++++++----
 3 files changed, 14 insertions(+), 6 deletions(-)
tests/conftest.py
96 96 

  		




  97
  97
  
    
        def cursor(*args, **kwargs):

  		




  98
  98
  
    
            cur = old_cursor(*args, **kwargs)

  		




  99
  
  
    
            mocked_cur = mock.Mock(wraps=cur)

  		




  
  99
  
    
            mocked_cur = mock.MagicMock(wraps=cur, spec=cur)

  		




  100
  100
  
    
            old_execute = cur.execute

  		




  101
  101
  
    

  		




  102
  102
  
    
            def execute(*args, **kwargs):

  		




  ......




  104
  104
  
    
                return old_execute(*args, **kwargs)

  		




  105
  105
  
    

  		




  106
  106
  
    
            mocked_cur.execute = execute

  		




  
  107
  
    
            mocked_cur.__iter__ = lambda *args, **kwargs: cur.__iter__()

  		




  107
  108
  
    
            return mocked_cur

  		




  108
  109
  
    

  		




  109
  110
  
    
        mocked_conn.cursor = cursor

  		












  

    
      tests/workflow/test_all.py
    
  





  3183
  3183
  
    
        assert formdata.geolocations == {}

  		




  3184
  3184
  
    
    if two_pubs.is_using_postgresql():

  		




  3185
  3185
  
    
        assert two_pubs.loggederror_class.count() == 2

  		




  3186
  
  
    
        logged_error = two_pubs.loggederror_class.select()[1]

  		




  
  3186
  
    
        logged_error = two_pubs.loggederror_class.select(order_by='id')[1]

  		




  3187
  3187
  
    
        assert logged_error.summary == 'error calling geocoding service [some error]'

  		




  3188
  3188
  
    
        assert logged_error.formdata_id == str(formdata.id)

  		




  3189
  3189
  
    
        assert logged_error.exception_class == 'ConnectionError'

  		












  

    
      wcs/sql.py
    
  





  1630
  1630
  
    

  		




  1631
  1631
  
    
    @classmethod

  		




  1632
  1632
  
    
    @guard_postgres

  		




  1633
  
  
    
    def select_iterator(cls, clause=None, order_by=None, ignore_errors=False, limit=None, offset=None):

  		




  
  1633
  
    
    def select_iterator(

  		




  
  1634
  
    
        cls, clause=None, order_by=None, ignore_errors=False, limit=None, offset=None, iterator=True

  		




  
  1635
  
    
    ):

  		




  1634
  1636
  
    
        table_static_fields = [

  		




  1635
  1637
  
    
            x[0] if x[0] not in cls._table_select_skipped_fields else 'NULL AS %s' % x[0]

  		




  1636
  1638
  
    
            for x in cls._table_static_fields

  		




  ......




  1654
  1656
  
    
                sql_statement += ' OFFSET %(offset)s'

  		




  1655
  1657
  
    
                parameters['offset'] = offset

  		




  1656
  1658
  
    

  		




  1657
  
  
    
        if cls._iterate_on_server:

  		




  
  1659
  
    
        if cls._iterate_on_server and iterator:

  		




  1658
  1660
  
    
            conn = get_connection(isolate=True)

  		




  1659
  1661
  
    
            cur = conn.cursor(name='select_iterator_%s' % uuid.uuid4())

  		




  1660
  1662
  
    
            cur.itersize = 1

  		




  ......




  1671
  1673
  
    
        finally:

  		




  1672
  1674
  
    
            cur.close()

  		




  1673
  1675
  
    
            conn.commit()

  		




  1674
  
  
    
            if cls._iterate_on_server:

  		




  
  1676
  
    
            if cls._iterate_on_server and iterator:

  		




  1675
  1677
  
    
                # close isolated connection

  		




  1676
  1678
  
    
                conn.close()

  		




  1677
  1679
  
    

  		




  ......




  1679
  1681
  
    
    @guard_postgres

  		




  1680
  1682
  
    
    def select(cls, clause=None, order_by=None, ignore_errors=False, limit=None, offset=None, iterator=False):

  		




  1681
  1683
  
    
        objects = cls.select_iterator(

  		




  1682
  
  
    
            clause=clause, order_by=order_by, ignore_errors=ignore_errors, limit=limit, offset=offset

  		




  
  1684
  
    
            clause=clause,

  		




  
  1685
  
    
            order_by=order_by,

  		




  
  1686
  
    
            ignore_errors=ignore_errors,

  		




  
  1687
  
    
            limit=limit,

  		




  
  1688
  
    
            offset=offset,

  		




  
  1689
  
    
            iterator=iterator,

  		




  1683
  1690
  
    
        )

  		




  1684
  1691
  
    
        func_clause = parse_clause(clause)[2]

  		




  1685
  1692
  
    
        if func_clause and (limit or offset):

  		




  1686
  
  
    
-