#124 Split migration.
Merged 6 years ago by ralph. Opened 6 years ago by ralph.

@@ -65,6 +65,7 @@ 

                  directives[:] = []

                  logger.info('No changes in schema detected.')

  

+     logger.info('Connecting...')

      engine = engine_from_config(config.get_section(config.config_ini_section),

                                  prefix='sqlalchemy.',

                                  poolclass=pool.NullPool)
@@ -73,12 +74,18 @@ 

      context.configure(connection=connection,

                        target_metadata=target_metadata,

                        process_revision_directives=process_revision_directives,

+                       transaction_per_migration=True,

                        **current_app.extensions['migrate'].configure_args)

  

      try:

+         logger.info('Beginning outermost transaction.')

          with context.begin_transaction():

+             logger.info('Starting migrations.')

              context.run_migrations()

+             logger.info('Done with migrations.')

+         logger.info('Outermost transaction released.')

      finally:

+         logger.info('Closing connection.')

          connection.close()

  

  if context.is_offline_mode():

@@ -0,0 +1,72 @@ 

+ """migrate records from old format to new.

+ 

+ Revision ID: 71b84ccc31bb

+ Revises: f2772c2c64a6

+ Create Date: 2018-02-14 12:04:34.688790

+ 

+ """

+ 

+ # revision identifiers, used by Alembic.

+ revision = '71b84ccc31bb'

+ down_revision = 'f2772c2c64a6'

+ 

+ from alembic import op

+ import sqlalchemy as sa

+ 

+ import requests

+ 

+ from waiverdb.api_v1 import get_resultsdb_result

+ from waiverdb.models import Waiver

+ 

+ 

+ def convert_id_to_subject_and_testcase(result_id):

+     try:

+         result = get_resultsdb_result(result_id)

+     except requests.HTTPError as e:

+         if e.response.status_code == 404:

+             raise RuntimeError('Result id %s not found in Resultsdb' % (result_id))

+         else:

+             raise RuntimeError('Failed looking up result in Resultsdb: %s' % e)

+     except Exception as e:

+         raise RuntimeError('Failed looking up result in Resultsdb: %s' % e)

+     if 'original_spec_nvr' in result['data']:

+         subject = {'original_spec_nvr': result['data']['original_spec_nvr'][0]}

+     else:

+         if result['data']['type'][0] == 'koji_build' or \

+            result['data']['type'][0] == 'bodhi_update':

+             SUBJECT_KEYS = ['item', 'type']

+             subject = dict([(k, v[0]) for k, v in result['data'].items()

+                             if k in SUBJECT_KEYS])

+         else:

+             raise RuntimeError('Unable to determine subject for result id %s' % (result_id))

+     testcase = result['testcase']['name']

+     return (subject, testcase)

+ 

+ 

+ def upgrade():

+     # Get a session asociated with the alembic upgrade operation.

+     connection = op.get_bind()

I think this could just be:

db.session.bind = op.get_bind()

and then use the normal db stuff below. But this is okay too.

+     Session = sa.orm.sessionmaker()

+     session = Session(bind=connection)

+ 

+     try:

+         # querying resultsdb for the corresponding subject/testcase.

+         waivers = session.query(Waiver).all()

+         for waiver in waivers:

+             subject, testcase = convert_id_to_subject_and_testcase(waiver.result_id)

+             waiver.subject = subject

+             waiver.testcase = testcase

+         session.commit()

+     except:

+         session.rollback()

+         raise

+     finally:

+         session.close()

+ 

+ 

+ def downgrade():

+     # It shouldn't be possible to downgrade this change.

+     # Because the result_id field will not be populated with data anymore.

+     # If the user tries to downgrade "result_id" should be not null once again

+     # like in the old version of the schema, but the value is not longer available

+     raise RuntimeError('Irreversible migration')

@@ -0,0 +1,30 @@ 

+ """set nullable on new and old fields.

+ 

+ Revision ID: ed43eb9b221c

+ Revises: 71b84ccc31bb

+ Create Date: 2018-02-14 12:09:42.877375

+ 

+ """

+ 

+ # revision identifiers, used by Alembic.

+ revision = 'ed43eb9b221c'

+ down_revision = '71b84ccc31bb'

+ 

+ from alembic import op

+ 

+ 

+ def upgrade():

+     # SQLite has some problem in dropping/altering columns.

+     # So in this way Alembic should do some behind the scenes

+     # with: make new table - copy data - drop old table - rename new table

+     with op.batch_alter_table('waiver') as batch_op:

+         batch_op.alter_column('subject', nullable=False)

+         batch_op.alter_column('testcase', nullable=False)

+         batch_op.alter_column('result_id', nullable=True)

+ 

+ 

+ def downgrade():

+     with op.batch_alter_table('waiver') as batch_op:

+         batch_op.alter_column('subject', nullable=True)

+         batch_op.alter_column('testcase', nullable=True)

+         batch_op.alter_column('result_id', nullable=False)

@@ -8,10 +8,6 @@ 

  

  from alembic import op

  import sqlalchemy as sa

- import requests

- 

- from waiverdb.api_v1 import get_resultsdb_result

- from waiverdb.models import db, Waiver

  

  

  # revision identifiers, used by Alembic.
@@ -19,54 +15,11 @@ 

  down_revision = '0a74cdab732a'

  

  

- def convert_id_to_subject_and_testcase(result_id):

-     try:

-         result = get_resultsdb_result(result_id)

-     except requests.HTTPError as e:

-         if e.response.status_code == 404:

-             raise RuntimeError('Result id %s not found in Resultsdb' % (result_id))

-         else:

-             raise RuntimeError('Failed looking up result in Resultsdb: %s' % e)

-     except Exception as e:

-         raise RuntimeError('Failed looking up result in Resultsdb: %s' % e)

-     if 'original_spec_nvr' in result['data']:

-         subject = {'original_spec_nvr': result['data']['original_spec_nvr'][0]}

-     else:

-         if result['data']['type'][0] == 'koji_build' or \

-            result['data']['type'][0] == 'bodhi_update':

-             SUBJECT_KEYS = ['item', 'type']

-             subject = dict([(k, v[0]) for k, v in result['data'].items()

-                             if k in SUBJECT_KEYS])

-         else:

-             raise RuntimeError('Unable to determine subject for result id %s' % (result_id))

-     testcase = result['testcase']['name']

-     return (subject, testcase)

- 

- 

  def upgrade():

      op.add_column('waiver', sa.Column('subject', sa.Text(), nullable=True, index=True))

      op.add_column('waiver', sa.Column('testcase', sa.Text(), nullable=True, index=True))

  

-     # querying resultsdb for the corresponding subject/testcase for each result_id

-     waivers = Waiver.query.all()

-     for waiver in waivers:

-         subject, testcase = convert_id_to_subject_and_testcase(waiver.result_id)

-         waiver.subject = subject

-         waiver.testcase = testcase

-         db.session.commit()

- 

-     # SQLite has some problem in dropping/altering columns.

-     # So in this way Alembic should do some behind the scenes

-     # with: make new table - copy data - drop old table - rename new table

-     with op.batch_alter_table('waiver') as batch_op:

-         batch_op.alter_column('subject', nullable=False)

-         batch_op.alter_column('testcase', nullable=False)

-         batch_op.alter_column('result_id', nullable=True)

- 

  

  def downgrade():

-     # It shouldn't be possible to downgrade this change.

-     # Because the result_id field will not be populated with data anymore.

-     # If the user tries to downgrade "result_id" should be not null once again

-     # like in the old version of the schema, but the value is not longer available

-     raise RuntimeError('Irreversible migration')

+     op.drop_column('waiver', 'subject')

+     op.drop_column('waiver', 'testcase')

Interesting. At least partial success here.

λ oc logs waiverdb-test-jenkins-waiverdb-rbean-1-web-1-deploy                 ~
--> pre: Running hook pod ...
sh: cannot set terminal process group (-1): Inappropriate ioctl for device
sh: no job control in this shell
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 0a27a8ad723a, Initializes migration
INFO  [alembic.runtime.migration] Running upgrade 0a27a8ad723a -> 0a74cdab732a, add proxyuser waiving support
INFO  [alembic.runtime.migration] Running upgrade 0a74cdab732a -> f2772c2c64a6, waive absence of result
INFO  [alembic.runtime.migration] Running upgrade f2772c2c64a6 -> 71b84ccc31bb, migrate records from old format to new.
INFO  [alembic.runtime.migration] Running upgrade 71b84ccc31bb -> ed43eb9b221c, set nullable on new and old fields.

It does seem to hang on that last upgrade though...

(waiverdb) waiverdb λ oc status                                                                                                                                                  devel/waiverdb splitting-migraine
In project WaiverDB test environments (waiverdb-test) on server https://open.paas.redhat.com:443

svc/waiverdb-test-jenkins-waiverdb-rbean-1-database - 172.30.242.203:5432
  dc/waiverdb-test-jenkins-waiverdb-rbean-1-database deploys registry.access.redhat.com/rhscl/postgresql-95-rhel7:latest 
    deployment #1 deployed 5 minutes ago - 1 pod

https://waiverdb-test-jenkins-waiverdb-rbean-1-web-waiverdb-test.int.open.paas.redhat.com (redirects) to pod port web (svc/waiverdb-test-jenkins-waiverdb-rbean-1-web)
  dc/waiverdb-test-jenkins-waiverdb-rbean-1-web deploys docker-registry.engineering.redhat.com/factory2/waiverdb:0.6.1.dev9-git.49e6947 
    deployment #1 running for 5 minutes

View details with 'oc describe <resource>/<name>' or list everything with 'oc get all'.
(waiverdb) waiverdb λ oc get pods                                                                                                                                                devel/waiverdb splitting-migraine
NAME                                                      READY     STATUS    RESTARTS   AGE
waiverdb-test-jenkins-waiverdb-rbean-1-database-1-dl8vv   1/1       Running   0          5m
waiverdb-test-jenkins-waiverdb-rbean-1-web-1-deploy       1/1       Running   0          5m
waiverdb-test-jenkins-waiverdb-rbean-1-web-1-hook-pre     1/1       Running   0          5m

Yeah, it's hung:

waiverdb=> SELECT * FROM pg_stat_activity WHERE waiting;
 datid | datname  | pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |        backend_start         |          xact_start           |         query_start          |         state_change         | waiting | state  | backend_xid | backend_xmin |                        query                         
-------+----------+-----+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+-------------------------------+------------------------------+------------------------------+---------+--------+-------------+--------------+------------------------------------------------------
 16385 | waiverdb | 121 |    16384 | waiverdb |                  | 10.1.24.1   |                 |       51756 | 2018-02-14 17:41:59.84921+00 | 2018-02-14 17:41:59.906088+00 | 2018-02-14 17:41:59.90625+00 | 2018-02-14 17:41:59.90625+00 | t       | active |             |          629 | ALTER TABLE waiver ALTER COLUMN subject SET NOT NULL
(1 row)

1 new commit added

  • Try dropping batch operation to get postgres upgrades working.
6 years ago

2 new commits added

  • Try dropping batch operation to get postgres upgrades working.
  • Split migration.
6 years ago

2 new commits added

  • Some log statements.
  • Split migration.
6 years ago

1 new commit added

  • Put this operation on a session associated with the alembic op.
6 years ago

3 new commits added

  • Put this operation on a session associated with the alembic op.
  • Some log statements.
  • Split migration.
6 years ago

3 new commits added

  • Put this operation on a session associated with the alembic op.
  • Some log statements.
  • Split migration.
6 years ago

Ah, that finally did it. See run number 9 of the waiverdb-rbean jenkins job on the rcm-tools-jenkins instance. :)

Output from the pre hook:

λ oc logs -f waiverdb-test-jenkins-waiverdb-rbean-9-web-1-hook-pre   devel/waiverdb splitting-migraine 
sh: cannot set terminal process group (-1): Inappropriate ioctl for device                             
sh: no job control in this shell                   
INFO  [alembic.env] Connecting...                  
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.                                         
INFO  [alembic.runtime.migration] Will assume transactional DDL.                                       
INFO  [alembic.env] Beginning outermost transaction.                                                   
INFO  [alembic.env] Starting migrations.           
INFO  [alembic.runtime.migration] Running upgrade  -> 0a27a8ad723a, Initializes migration              
INFO  [alembic.runtime.migration] Running upgrade 0a27a8ad723a -> 0a74cdab732a, add proxyuser waiving support
INFO  [alembic.runtime.migration] Running upgrade 0a74cdab732a -> f2772c2c64a6, waive absence of result
INFO  [alembic.runtime.migration] Running upgrade f2772c2c64a6 -> 71b84ccc31bb, migrate records from old format to new.
INFO  [alembic.runtime.migration] Running upgrade 71b84ccc31bb -> ed43eb9b221c, set nullable on new and old fields.
INFO  [alembic.env] Done with migrations.          
INFO  [alembic.env] Outermost transaction released.                                                    
INFO  [alembic.env] Closing connection.

I think this could just be:

db.session.bind = op.get_bind()

and then use the normal db stuff below. But this is okay too.

Pull-Request has been merged by ralph

6 years ago