diff options
Diffstat (limited to 'doc/build/changelog/migration_14.rst')
| -rw-r--r-- | doc/build/changelog/migration_14.rst | 98 |
1 files changed, 98 insertions, 0 deletions
diff --git a/doc/build/changelog/migration_14.rst b/doc/build/changelog/migration_14.rst index 97b94087f..1ee52b86a 100644 --- a/doc/build/changelog/migration_14.rst +++ b/doc/build/changelog/migration_14.rst @@ -1576,6 +1576,104 @@ The new behavior is described in the errors page at :ref:`error_8s2a`. Dialect Changes =============== +psycopg2 version 2.7 or higher is required for the PostgreSQL psycopg2 dialect +------------------------------------------------------------------------------ + +The psycopg2 dialect relies upon many features of psycopg2 released +in the past few years. To simplify the dialect, version 2.7, released +in March, 2017 is now the minimum version required. + +.. _change_5401: + +psycopg2 dialect features "execute_values" with RETURNING for INSERT statements by default +------------------------------------------------------------------------------------------ + +The first half of a significant performance enhancement for PostgreSQL when +using both Core and ORM, the psycopg2 dialect now uses +``psycopg2.extras.execute_values()`` by default for compiled INSERT statements +and also implements RETURNING support in this mode. + +This extension method allows many rows to be INSERTed within a single +statement, using an extended VALUES clause for the statement. While +SQLAlchemy's :func:`_sql.insert` construct already supports this syntax via +the :meth:`_sql.Insert.values` method, the extension method allows the +construction of the VALUES clause to occur dynamically when the statement +is executed as an "executemany" execution, which is what occurs when one +passes a list of parameter dictionaries to :meth:`_engine.Connection.execute`. +It also occurs beyond the cache boundary so that the INSERT statement may +be cached before the VALUES are rendered. + +A quick test of the ``execute_values()`` approach using the +``bulk_inserts.py`` script in the :ref:`examples_performance` example +suite reveals an approximate **fivefold performance increase**:: + + $ python -m examples.performance bulk_inserts --test test_core_insert --num 100000 --dburl postgresql://scott:tiger@localhost/test + + # 1.3 + test_core_insert : A single Core INSERT construct inserting mappings in bulk. (100000 iterations); total time 5.229326 sec + + # 1.4 + test_core_insert : A single Core INSERT construct inserting mappings in bulk. (100000 iterations); total time 0.944007 sec + +Support for the "batch" extension was added in version 1.2 in +:ref:`change_4109`, and enhanced to include support for the ``execute_values`` +extension in 1.3 in :ticket:`4623`. In 1.4 the ``execute_values`` extension is +now being turned on by default for INSERT statements; the "batch" extension +for UPDATE and DELETE remains off by default. + +In addition, the ``execute_values`` extension function supports returning the +rows that are generated by RETURNING as an aggregated list. The psycopg2 +dialect will now retrieve this list if the given :func:`_sql.insert` construct +requests returning via the :meth:`.Insert.returning` method or similar methods +intended to return generated defaults; the rows are then installed in the +result so that they are retreieved as though they came from the cursor +directly. This allows tools like the ORM to use batched inserts in all cases, +which is expected to provide a dramatic performance improvement. + + +The ``executemany_mode`` feature of the psycopg2 dialect has been revised +with the following changes: + +* A new mode ``"values_only"`` is added. This mode uses the very performant + ``psycopg2.extras.execute_values()`` extension method for compiled INSERT + statements run with executemany(), but does not use ``execute_batch()`` for + UPDATE and DELETE statements. This new mode is now the default setting for + the psycopg2 dialect. + +* The existing ``"values"`` mode is now named ``"values_plus_batch"``. This mode + will use ``execute_values`` for INSERT statements and ``execute_batch`` + for UPDATE and DELETE statements. The mode is not enabled by default + because it disables the proper functioning of ``cursor.rowcount`` with + UPDATE and DELETE statements executed with ``executemany()``. + +* RETURNING support is enabled for ``"values_only"`` and ``"values"`` for + INSERT statements. The psycopg2 dialect will receive the rows back + from psycopg2 using the fetch=True flag and install them into the result + set as though they came directly from the cursor (which they ulimately did, + however psycopg2's extension function has aggregated multiple batches into + one list). + +* The default "page_size" setting for ``execute_values`` has been increased + from 100 to 1000. The default remains at 100 for the ``execute_batch`` + function. These parameters may both be modified as was the case before. + +* The ``use_batch_mode`` flag that was part of the 1.2 version of the feature + is removed; the behavior remains controllable via the ``executemany_mode`` + flag added in 1.3. + +* The Core engine and dialect has been enhanced to support executemany + plus returning mode, currently only available with psycopg2, by providing + new :attr:`_engine.CursorResult.inserted_primary_key_rows` and + :attr:`_engine.CursorResult.returned_default_rows` accessors. + +.. seealso:: + + :ref:`psycopg2_executemany_mode` + + +:ticket:`5401` + + .. _change_4895: Removed "join rewriting" logic from SQLite dialect; updated imports |