#2843 SQLAlchemy transaction error when jobs missing in Meltano database Issue created by MeltyBot Migrated from GitLab: https://gitlab.com/meltano/meltano/-/issues/2902 Originally created by @al.whatmough on 2021-08-19 093626 --- I'm using a Postgres database for the Meltano internal database, and I manually truncated the rows in the

job

table. After doing this, I'm getting an error when running `meltano elt`: ``` [2021-08-19 091724,754] [17009|MainThread|meltano.core.job.job] [INFO] This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: UPDATE statement on table 'job' expected to update 1 row(s); 0 were matched. (Background on this error at: https://sqlalche.me/e/14/7s2a) [2021-08-19 091724,755] [17009|MainThread|meltano.cli.utils] [DEBUG] This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: UPDATE statement on table 'job' expected to update 1 row(s); 0 were matched. (Background on this error at: https://sqlalche.me/e/14/7s2a) Traceback (most recent call last): File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/job/job.py", line 147, in run self.success() File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/job/job.py", line 174, in success self.transit(State.SUCCESS) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/job/job.py", line 120, in transit transition = (self.state, state) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/attributes.py", line 481, in get return self.impl.get(state, dict_) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/attributes.py", line 926, in get value = self._fire_loader_callables(state, key, passive) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/attributes.py", line 957, in _fire_loader_callables return state._load_expired(state, passive) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/state.py", line 710, in _load_expired self.manager.expired_attribute_loader(self, toload, passive) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/loading.py", line 1453, in load_scalar_attributes no_autoflush=no_autoflush, File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/loading.py", line 412, in load_on_ident execution_options=execution_options, File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/loading.py", line 528, in load_on_pk_identity bind_arguments=bind_arguments, File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1643, in execute _parent_execute_state is not None, File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/context.py", line 292, in orm_pre_session_exec session._autoflush() File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2193, in _autoflush self.flush() File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 3298, in flush self._flush(objects) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 3394, in _flush flush_context.transaction = transaction = self.begin(_subtrans=True) File "<string>", line 2, in begin File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/util/deprecations.py", line 298, in warned return fn(*args, **kwargs) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1320, in begin trans = self._transaction._begin(nested=nested) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 629, in _begin self._assert_active() File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/session.py", line 608, in _assert_active code="7s2a", sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: UPDATE statement on table 'job' expected to update 1 row(s); 0 were matched. (Background on this error at: https://sqlalche.me/e/14/7s2a) During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/cli/__init__.py", line 44, in main cli(obj={"project": None}) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/click/core.py", line 764, in call return self.main(*args, **kwargs) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/click/core.py", line 717, in main rv = self.invoke(ctx) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/click/core.py", line 1137, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/click/core.py", line 956, in invoke return ctx.invoke(self.callback, **ctx.params) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/click/core.py", line 555, in invoke return callback(*args, **kwargs) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/cli/params.py", line 23, in decorate return func(*args, **kwargs) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/cli/params.py", line 56, in decorate func(project, *args, **kwargs) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/cli/elt.py", line 137, in elt run_async(_run_job(project, job, session, context_builder, force=force)) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/utils/__init__.py", line 47, in run_async loop.run_until_complete(future) File "/usr/lib64/python3.6/asyncio/base_events.py", line 484, in run_until_complete return future.result() File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/cli/elt.py", line 214, in _run_job await _run_elt(project, context_builder, output_logger) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/async_generator/_util.py", line 42, in aexit await self._agen.asend(None) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/job/job.py", line 153, in run if not self.is_running(): File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/meltano/core/job/job.py", line 83, in is_running return self.state is State.RUNNING File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/attributes.py", line 481, in get return self.impl.get(state, dict_) File "/home/ec2-user/environment/meltano_docker/project/lib64/python3.6/dist-packages/sqlalchemy/orm/attributes.py", line 926, in get value = self._fire_loader_callab… meltano/meltano