transaction is too large to complete results in SAVEPOINT not supported except for COCKROACH_RESTART

[nstewart]

When using a batch size of 1000 for rides, I see the following error, but only after hundreds of thousands of rides have been added. Reducing the batch size to 800 seems to fix this, but I wouldn't have expected to see the savepoint not supported error in either case.

Exception in thread Thread-6:
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 683, in do_executemany
    cursor.executemany(statement, parameters)
psycopg2.InternalError: transaction is too large to complete; try splitting into pieces


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2680, in _bulk_save_mappings
    isstates, return_defaults, render_nulls)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 69, in _bulk_insert
    bookkeeping=return_defaults)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 836, in _emit_insert_statements
    execute(statement, multiparams)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 683, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) transaction is too large to complete; try splitting into pieces
 [SQL: 'INSERT INTO rides (id, city, vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time, revenue) VALUES (%(id)s, %(city)s, %(vehicle_city)s, %(rider_id)s, %(vehicle_id)s, %(start_address)s, %(end_address)s, %(start_time)s, %(end_time)s, %(revenue)s)'] [parameters: ({'id': UUID('b1ceeac5-d98d-4f03-8193-4dde79b8c755'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('5432297f-ff06-497d-8a94-46b8c35a1f16'), 'vehicle_id': UUID('c8f2ecb7-7d7a-4a6f-9c9f-db7665bc2bfd'), 'start_address': '93706 Donna Ferry\nEricfort, LA 94137', 'end_address': '71658 Crystal Fields Apt. 004\nLake Gilbertmouth, LA 16285', 'start_time': datetime.datetime(2018, 11, 19, 1, 35, 7, 8671), 'end_time': datetime.datetime(2018, 11, 19, 2, 6, 7, 8671), 'revenue': 83.39400159224459}, {'id': UUID('1bba2174-bb79-4466-a645-dc481a51f07b'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('9826e406-b2b5-48c9-8dc5-08f511fec08c'), 'vehicle_id': UUID('2f779974-d9cb-4ac4-b344-569ca3f22055'), 'start_address': '5395 Regina Circles\nTravisville, MI 30044', 'end_address': 'PSC 0290, Box 5873\nAPO AP 98942', 'start_time': datetime.datetime(2018, 11, 14, 1, 35, 7, 9590), 'end_time': datetime.datetime(2018, 11, 14, 2, 1, 7, 9590), 'revenue': 72.75741594154309}, {'id': UUID('abb39aa2-bda0-4248-bcf0-a8cf18f793eb'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('7a6ccd2e-7d2b-491b-8a55-3738b21b9bf6'), 'vehicle_id': UUID('9d71016f-cbe0-42f2-b0a9-922e67c5a384'), 'start_address': '1459 Debra Trail Suite 886\nSouth Gary, OR 12079', 'end_address': '6432 Foster Ports Suite 794\nWoodland, IN 60262', 'start_time': datetime.datetime(2018, 11, 9, 1, 35, 7, 10096), 'end_time': datetime.datetime(2018, 11, 9, 2, 0, 7, 10096), 'revenue': 4.928096931811055}, {'id': UUID('db8815e5-6817-45c9-8738-1d8d2c0f0332'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('bce3d5cc-8e5d-445d-9a52-8194121f0461'), 'vehicle_id': UUID('e826acca-6d82-44a6-be10-4c060148d17c'), 'start_address': '19320 Miller Vista\nWest Luke, RI 71264', 'end_address': '837 Mitchell Trafficway\nSouth Jacobview, KY 07400', 'start_time': datetime.datetime(2018, 11, 24, 1, 35, 7, 11014), 'end_time': datetime.datetime(2018, 11, 24, 2, 23, 7, 11014), 'revenue': 87.46155642323491}, {'id': UUID('1300ac17-76e4-4db5-a862-8514fd086d7f'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('e016801f-b81d-4a1f-a13b-dd23a5467d1d'), 'vehicle_id': UUID('b4c60915-2dc8-468e-965b-d6e3e4f409ef'), 'start_address': '0563 Susan Summit\nNorth Joseph, FL 25587', 'end_address': '249 Alicia Plains Suite 949\nSotofurt, IL 89243', 'start_time': datetime.datetime(2018, 11, 22, 1, 35, 7, 11855), 'end_time': datetime.datetime(2018, 11, 22, 1, 58, 7, 11855), 'revenue': 71.01851025379284}, {'id': UUID('2c184d9c-d845-47cc-88d6-9a53bd7abc5d'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('d45fcedc-b142-40e8-9e24-a4b386850fdf'), 'vehicle_id': UUID('8d760fe5-843f-4a8b-8bb9-77d101986e9c'), 'start_address': '6683 Mann Throughway Suite 103\nPort Brittneyland, LA 38258', 'end_address': '1406 Melissa Oval Apt. 918\nShanemouth, ME 40859', 'start_time': datetime.datetime(2018, 11, 20, 1, 35, 7, 12668), 'end_time': datetime.datetime(2018, 11, 20, 1, 49, 7, 12668), 'revenue': 70.9785536519068}, {'id': UUID('849e1d4e-e3cc-4278-9ad2-c38b6cae79ce'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('90a61edb-0dab-4527-9094-bb005304d787'), 'vehicle_id': UUID('2c3dee5e-436c-4549-b37d-b344cb485773'), 'start_address': '6527 Candice Avenue\nLaurenfurt, MI 71119', 'end_address': 'USNS Ramirez\nFPO AP 86140', 'start_time': datetime.datetime(2018, 12, 3, 1, 35, 7, 13534), 'end_time': datetime.datetime(2018, 12, 3, 1, 53, 7, 13534), 'revenue': 17.166666065526396}, {'id': UUID('463b8a6e-b1db-4cce-8d71-b1d2f5cca837'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('1049315f-57b3-4453-916d-c37bee91659d'), 'vehicle_id': UUID('8f34324c-3cee-4cf3-96ed-3d44c1751e8b'), 'start_address': '1069 Khan Ramp\nHooverfort, DE 82789', 'end_address': '6088 Moore Expressway Suite 345\nLake Scott, AL 58143', 'start_time': datetime.datetime(2018, 11, 16, 1, 35, 7, 71348), 'end_time': datetime.datetime(2018, 11, 16, 1, 45, 7, 71348), 'revenue': 50.226038418146416}  ... displaying 10 of 1000 total bound parameter sets ...  {'id': UUID('9e3d939b-6471-48e5-917d-d452d44443f9'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('aab5b954-c6b2-468c-aa4c-68caa5c9692f'), 'vehicle_id': UUID('5b501723-1ecf-498f-9429-826271378f23'), 'start_address': '98768 Wilson Islands\nParkerberg, NJ 38482', 'end_address': '7145 Joseph Land Apt. 942\nPort Teresa, WA 71572', 'start_time': datetime.datetime(2018, 12, 7, 1, 35, 13, 939556), 'end_time': datetime.datetime(2018, 12, 7, 1, 36, 13, 939556), 'revenue': 81.15410017346142}, {'id': UUID('782fd350-b255-42d1-af13-3f0b41ed3877'), 'city': 'los angeles', 'vehicle_city': 'los angeles', 'rider_id': UUID('7e8ca3f9-5086-4dab-8ae9-770f78367703'), 'vehicle_id': UUID('000b424f-6b0c-4b14-adde-6ac3ece3708d'), 'start_address': '4690 Lopez Rapids\nLake Karachester, SD 52029', 'end_address': '21139 Crane Prairie Suite 142\nNorth Brandi, CT 41299', 'start_time': datetime.datetime(2018, 12, 4, 1, 35, 13, 940545), 'end_time': datetime.datetime(2018, 12, 4, 2, 14, 13, 940545), 'revenue': 26.44774427480407})] (Background on this error at: http://sqlalche.me/e/2j85)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
psycopg2.NotSupportedError: SAVEPOINT not supported except for COCKROACH_RESTART


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/threading.py", line 917, in _bootstrap_inner
    self.run()
  File "/usr/local/lib/python3.7/threading.py", line 865, in run
    self._target(*self._args, **self._kwargs)
  File "./loadmovr.py", line 61, in load_movr_data
    add_rides(engine, num_rides, city)
  File "./loadmovr.py", line 214, in add_rides
    lambda s: add_rides_helper(s, chunk, min(chunk + chunk_size, num_rides)))
  File "/usr/local/lib/python3.7/site-packages/cockroachdb/sqlalchemy/transaction.py", line 31, in run_transaction
    return _txn_retry_loop(session, callback)
  File "/usr/local/lib/python3.7/site-packages/cockroachdb/sqlalchemy/transaction.py", line 75, in _txn_retry_loop
    ret = callback(conn)
  File "./loadmovr.py", line 214, in <lambda>
    lambda s: add_rides_helper(s, chunk, min(chunk + chunk_size, num_rides)))
  File "./loadmovr.py", line 210, in add_rides_helper
    sess.bulk_save_objects(rides)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2521, in bulk_save_objects
    return_defaults, update_changed_only, False)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2685, in _bulk_save_mappings
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 76, in __exit__
    compat.reraise(type_, value, traceback)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2685, in _bulk_save_mappings
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 532, in rollback
    util.reraise(*rollback_err)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 497, in rollback
    t[1].rollback()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback
    self._do_rollback()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1694, in _do_rollback
    self._savepoint, self._parent)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 753, in _rollback_to_savepoint_impl
    self.engine.dialect.do_rollback_to_savepoint(self, name)
  File "/usr/local/lib/python3.7/site-packages/cockroachdb/sqlalchemy/dialect.py", line 383, in do_rollback_to_savepoint
    super(CockroachDBDialect, self).do_rollback_to_savepoint(connection, name)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 500, in do_rollback_to_savepoint
    connection.execute(expression.RollbackToSavepointClause(name))
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (psycopg2.NotSupportedError) SAVEPOINT not supported except for COCKROACH_RESTART
 [SQL: 'ROLLBACK TO SAVEPOINT sa_savepoint_1'] (Background on this error at: http://sqlalche.me/e/tw8g)