)]}'
{"ceilometer/storage/impl_sqlalchemy.py":[{"author":{"_account_id":6676,"name":"zqfan","email":"aji.zqfan@gmail.com","username":"aji-zqfan"},"change_message_id":"e102e3e9d3dc8c3c3aa9afcb5e01d055158de5c8","unresolved":false,"context_lines":[{"line_number":373,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":374,"context_line":""},{"line_number":375,"context_line":"            # remove metadata of cleaned resources"},{"line_number":376,"context_line":"            for table in [models.MetaText, models.MetaBigInt,"},{"line_number":377,"context_line":"                          models.MetaFloat, models.MetaBool]:"},{"line_number":378,"context_line":"                session \u003d self._engine_facade.get_session()"},{"line_number":379,"context_line":"                with session.begin():"}],"source_content_type":"text/x-python","patch_set":2,"id":"9a80dd14_d4c77d10","line":376,"updated":"2015-03-19 04:18:52.000000000","message":"clear MetaXXX table first, then clear resource table","commit_id":"a8bd730f098fbed2b48f793243155d0028534b11"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"9821d1bd19bc775a4f3390b2bd405fe82b6140da","unresolved":false,"context_lines":[{"line_number":358,"context_line":"            LOG.info(_(\"%d samples removed from database\"), rows)"},{"line_number":359,"context_line":""},{"line_number":360,"context_line":"        if not cfg.CONF.sql_expire_samples_only:"},{"line_number":361,"context_line":"            session \u003d self._engine_facade.get_session()"},{"line_number":362,"context_line":"            with session.begin():"},{"line_number":363,"context_line":"                # remove Meter definitions with no matching samples"},{"line_number":364,"context_line":"                (session.query(models.Meter)"}],"source_content_type":"text/x-python","patch_set":4,"id":"9a80dd14_36ee0a8c","line":361,"updated":"2015-03-19 13:28:51.000000000","message":"i don\u0027t think we need to grab a new session each time.. just break it down into multiple transactions","commit_id":"01e86d67611e28c3d4001b73092ae66726b855fa"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"7f8cfc08e0136af02f5641fbd64ed398508ff88c","unresolved":false,"context_lines":[{"line_number":357,"context_line":"            rows \u003d sample_q.delete()"},{"line_number":358,"context_line":"            LOG.info(_(\"%d samples removed from database\"), rows)"},{"line_number":359,"context_line":""},{"line_number":360,"context_line":"        if not cfg.CONF.sql_expire_samples_only:"},{"line_number":361,"context_line":"            with session.begin():"},{"line_number":362,"context_line":"                # remove Meter definitions with no matching samples"},{"line_number":363,"context_line":"                (session.query(models.Meter)"}],"source_content_type":"text/x-python","patch_set":5,"id":"9a80dd14_55544bc5","line":360,"updated":"2015-03-19 15:56:03.000000000","message":"Removed multiple get_session calls","commit_id":"e6115c47d8f0d265f6288655ace7a1398a18c870"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"0e6a3acf3be22f986aa8e23c797d564cf07075f8","unresolved":false,"context_lines":[{"line_number":350,"context_line":"        :param ttl: Number of seconds to keep records for."},{"line_number":351,"context_line":"        \"\"\""},{"line_number":352,"context_line":"        # Reduces possibility of database deadlocks from"},{"line_number":353,"context_line":"        # occuring by using separate transaction for each delete"},{"line_number":354,"context_line":"        session \u003d self._engine_facade.get_session()"},{"line_number":355,"context_line":"        with session.begin():"},{"line_number":356,"context_line":"            end \u003d timeutils.utcnow() - datetime.timedelta(seconds\u003dttl)"}],"source_content_type":"text/x-python","patch_set":6,"id":"9a80dd14_d061fb39","line":353,"updated":"2015-03-20 14:47:48.000000000","message":"Added comment indicating that this change reduces possibility of database deadlocks from occuring","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"b4f660b959d1f8a6385bf33806d3040308c16d16","unresolved":false,"context_lines":[{"line_number":377,"context_line":"                     .filter(table.id.in_(resource_subq))"},{"line_number":378,"context_line":"                     .delete(synchronize_session\u003dFalse))"},{"line_number":379,"context_line":""},{"line_number":380,"context_line":"            with session.begin():"},{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"}],"source_content_type":"text/x-python","patch_set":6,"id":"1a6ced46_23be4b00","line":380,"updated":"2015-03-23 13:27:02.000000000","message":"sorry, i just realised this but i think we should group the metaXXX and resource tables cleanup in a single transaction... if we do it this way, there\u0027s a chance we remove meta data but not the resource so we effectively have a resource that is not queryable.\n\nthat said, i\u0027m assuming the resource cleanup is the part of the transaction that is taking up the most time so it might make this patch less effective.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":9562,"name":"Ildiko Vancsa","email":"ildiko.vancsa@gmail.com","username":"ildikov"},"change_message_id":"f8240b4969fe6ef373faa22cb3222a9c7af3fab0","unresolved":false,"context_lines":[{"line_number":377,"context_line":"                     .filter(table.id.in_(resource_subq))"},{"line_number":378,"context_line":"                     .delete(synchronize_session\u003dFalse))"},{"line_number":379,"context_line":""},{"line_number":380,"context_line":"            with session.begin():"},{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"}],"source_content_type":"text/x-python","patch_set":6,"id":"1a6ced46_8309bf97","line":380,"in_reply_to":"1a6ced46_23be4b00","updated":"2015-03-23 13:36:13.000000000","message":"I guess it would be nice to test this somehow, to avoid this situation for sure. Would that be possible?","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"b388e84e216fcce776b8e62dbd5f1c785451ea4a","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"1a6ced46_f35dd7ea","line":384,"updated":"2015-03-23 16:18:41.000000000","message":"If we delete from metadataXXX for say resource A, but the delete on resource A fails, the next time the expirer runs it should delete resource A. Since we want to clean up the resource anyway it should not matter if the resource is not queryable based on metadata values for short amount of time.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":4491,"name":"Lianhao Lu","email":"llh_misc@outlook.com","username":"lianhao-lu"},"change_message_id":"a131a6f1395b547aa56bd058d8fccfc3c87d11e8","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"1a6ced46_399c2e0f","line":384,"in_reply_to":"1a6ced46_136d1333","updated":"2015-03-26 08:55:03.000000000","message":"Could we just clean the Resource here? And let the cascade take action to delete those in Meta tables?","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"57de28a759d482d5b28c6f9f0f66ae15ae8dde6e","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"fa963961_338f5725","line":384,"in_reply_to":"1a6ced46_399c2e0f","updated":"2015-03-26 18:04:18.000000000","message":"llu, i assume you mean this? http://docs.sqlalchemy.org/en/rel_0_9/orm/cascades.html#delete\n\nwe can give it a try, the reason i didn\u0027t suggest it is because i\u0027ve generally had terrible experience with it. (see all the disclaimers in doc)","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"b848c0b1e4af1a0b46d4cb48fa6ad0f3e6679325","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"1a6ced46_136d1333","line":384,"in_reply_to":"1a6ced46_f35dd7ea","updated":"2015-03-23 16:25:40.000000000","message":"so i think the scenario i was thinking was:\n\n- resourceA is cleaned from metaxxx table\n- sample for resourceA is received and is written to data but resource/meta tables aren\u0027t updated because resourceA already exists.\n- resourceA won\u0027t be cleaned because it now has a sample but it has no metadata anymore so it can\u0027t be queried.\n\nit\u0027s an edge case but i get it\u0027s a debate about increasing chance of deadlock vs risk of consistency issues.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"d9555e1ddd41a45f78d123fd4f071105067cc519","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"dac4157f_5c1048a8","line":384,"in_reply_to":"9aa53dc9_4cbb23e6","updated":"2015-04-22 01:25:20.000000000","message":"@gordon chung @lianhao lu Sorry I wasnt able to work on this till today. I was able to try option 2 (using database on delete cascade) on our test system, updated the mysql schema, added foreign key constraint added \"on delete cascade\" option, updated the sqlalchemy model to add ondelete\u003d\"cascade\" to all metadata tables foreign key constraint, let the ceilometer expirer run very often, database does seem to cleanup from metadata_* tables but I get intermittent error (10 in about 10000 inserts) where sample insert fails because resource no longer exists (deleted by the expirer) I think this is much like the  case which you highlighted above where when a sample is being inserted, it finds a resource row using metadata_hash, but the resource row soon after gets deleted by the expirer, before the sample can be inserted. I think at this point option 4 setting metadata_hash to \"delete_\" + id to mark resource rows for delete, seems to be the only option because its separating delete from sample, resource, metadata tables into into separate transactions (thus eliminating database deadlock) and at the same time handling the case new sample will always have an associated resource added. I will upload a new patch soon.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"437a8a29dc25f4808e79de0f1f1ba48edf57e9a7","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"da9b358b_82aa3d6f","line":384,"in_reply_to":"da9b358b_442b2aec","updated":"2015-03-30 09:06:37.000000000","message":"@gordon chung thanks for pointing me to the unique index which is currently commented out. I currently have a patch working internally which sets the metadata_hash with a prefix \u0027delete_\u0027 + internal_id instead of setting it to \u00270\u0027 (so that its still unique) and using the same logic as before (changing \u003d\u003d \u00270\u0027 to like \u0027delete_%\u0027)\n\n@gordon chung, @Lianhao Lu wanted to summarize the options we have to eliminate database deadlock by not grouping delete from resource and metadata_* tables in a single transaction.\n\noption 1: Use sqlalchemy cascades http://docs.sqlalchemy.org/en/rel_0_9/orm/cascades.html#delete\nAccording to this if we want to use bulk delete operation as we currently are using according to http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.delete, we will have to perform delete operation one at a time, which we dont want to do.\n\noption 2: Use database cascade http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#on-update-on-delete\nThis is not available on sqllite and when using myisam tables in mysql. Regarding @Lianhao Lu\u0027s comment above whether we can just get rid of sqllite tests would be worth pursuing this approach. Which are other databases that we have to support? so we can check if we can rely on this feature.\n\noption 3: Delete in chunks\nThis again will mean we wont be able to do bulk delete operations and would go against the principle letting the database do the heavy lifting which I think is important in our case where we want to deal with potentially large amount of data. Also defining chunks might be tricky as it could depend on the load of the database etc.\n\noption 4: Using the metadata_hash column in the resource to some unique value\nWith this approach I think we cover all cases except that the code is not elegant as we may want it to be but this will get me (and probably someone else) in the community over this problem in the short term. Its implemented (see my comment above) to take into account @gordon chungs earlier concerns as well as wont be impacted when the unique index on metadata_hash gets turned on in the future. We are testing this currently and will go with this approach if we can.\n\noption 5: Add a new column to all tables called \"is_active\"\nThis is a more elegant way to delete rows is by introducing a column called is_active in resource table. A delete would flip the bit of the colun from 1 to 0 without physically deleting the row. Any inserts into resource tables would now have to look for this extra flag in addition to metadata_hash to figure out if resource exists or not. Now we can delete physically all rows from metadata_* tables whose resource has is_active set to 0 , and finally clean up resource themselves in separate transaction.\nProbably we need such a column in all the tables meter, sample, resource so that we can employ such a strategy of flipping the flag in a bulk update operation and perform the delete or purge operations independently. I assume there are more things that can be impacted by this change like API etc which now have to check if the resource is acive or not etc. so this would be a big change.\n\nIn the short term option 4 makes sense to us, But option 2 may also work for the community (I can run a test to see if it works without causing deadlocks in our test env). Please let me know what you all think of the options above. Thanks Ashwin","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"0dab354a891bb69bf5382bdaec8240508e50cd85","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"9aa53dc9_4cbb23e6","line":384,"in_reply_to":"da9b358b_82aa3d6f","updated":"2015-04-07 20:31:53.000000000","message":"ah yes! option 1 flaw was the reason i didn\u0027t bother with cascades\n\nthe prepending of \u0027delete\u0027 might be the easiest solution to be honest... i think we could look at dropping sqlite and MYISAM mysql doesn\u0027t really matter because we enforce usage InnoDB already... the change might be more sizeable.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"0e83fe9775bdaee317be5787fdc161bb4d8ace37","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"dac4157f_9ee1229d","line":384,"in_reply_to":"dac4157f_5c1048a8","updated":"2015-04-22 13:02:18.000000000","message":"@gordon chung @lianhao lu ah found a problem, still evaluating/testing option 2 (using database on delete cascade). Please ignore my earlier comment","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":4491,"name":"Lianhao Lu","email":"llh_misc@outlook.com","username":"lianhao-lu"},"change_message_id":"cf3d39f1b69868f64173d6f931ac06a62fbbc97c","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"da9b358b_442b2aec","line":384,"in_reply_to":"fa963961_07785ff1","updated":"2015-03-30 02:32:36.000000000","message":"If the unsupported DB for cascade on delete is only sqlite, i\u0027m wondering whether we can remove the sqlite in tests, because I don\u0027t think we officially claim to support sqlite.","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":4491,"name":"Lianhao Lu","email":"llh_misc@outlook.com","username":"lianhao-lu"},"change_message_id":"d2c0c72ff40be10c227d883e05d927f85bc3592e","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"fa963961_8bf93c50","line":384,"in_reply_to":"fa963961_338f5725","updated":"2015-03-27 02:28:09.000000000","message":"Then how about trying this http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#on-update-on-delete","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"83d4ed4f4e6a5827790c1b2fd299fe3678336e48","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"fa963961_07785ff1","line":384,"in_reply_to":"fa963961_4c6b391f","updated":"2015-03-27 20:19:09.000000000","message":"@Ashwin, i just realised your only solution works because there\u0027s a missing unique constraint that should really be there. \n\nhttps://github.com/openstack/ceilometer/blob/master/ceilometer/storage/sqlalchemy/models.py#L170-L175\n\nthe constaint isn\u0027t completely necessary but it does lead to this:https://bugs.launchpad.net/ceilometer/+bug/1419271","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"c73e0943cdd038009f0f630466e1a2aff8e45e48","unresolved":false,"context_lines":[{"line_number":381,"context_line":"                # remove resources with no matching samples"},{"line_number":382,"context_line":"                resource_q \u003d (session.query(models.Resource.internal_id)"},{"line_number":383,"context_line":"                              .filter(~models.Resource.samples.any()))"},{"line_number":384,"context_line":"                resource_q.delete(synchronize_session\u003dFalse)"},{"line_number":385,"context_line":"            LOG.info(_(\"Expired residual resource and\""},{"line_number":386,"context_line":"                       \" meter definition data\"))"},{"line_number":387,"context_line":""}],"source_content_type":"text/x-python","patch_set":6,"id":"fa963961_4c6b391f","line":384,"in_reply_to":"fa963961_8bf93c50","updated":"2015-03-27 15:08:08.000000000","message":"Looks like from the doc that on delete cascade is not supported in all databases including sqllite. I can give it a try and let you know. What do you think about the approach of setting metadata_hash on resource to \u00270\u0027 (in other words marking it as to be deleted) that way we can still clean up from each of metadata_* tables and resource tables in separate transactions and would cover the case that gordon highlighted above","commit_id":"bda58cca522aeda6f3e5a1d3c3f96dc59766abd0"},{"author":{"_account_id":6537,"name":"gordon chung","email":"gord@live.ca","username":"chungg"},"change_message_id":"289612e9261a899c9f996698efdeba9ceedd94df","unresolved":false,"context_lines":[{"line_number":376,"context_line":"                # mark resource with no matching samples for delete"},{"line_number":377,"context_line":"                resource_q.update({models.Resource.metadata_hash: \"delete_\""},{"line_number":378,"context_line":"                                  + cast(models.Resource.internal_id,"},{"line_number":379,"context_line":"                                         sa.String)},"},{"line_number":380,"context_line":"                                  synchronize_session\u003dFalse)"},{"line_number":381,"context_line":""},{"line_number":382,"context_line":"            # remove metadata of resources marked for delete"}],"source_content_type":"text/x-python","patch_set":9,"id":"da20952f_709ead08","line":379,"updated":"2015-09-10 14:06:34.000000000","message":"do we actually need to cast/append id if we never use it?","commit_id":"7bdd0bf2c5ea97f5dd500b0e4fdaaadf6ab00889"},{"author":{"_account_id":11580,"name":"Ashwin Agate","email":"agateaaa@gmail.com","username":"agateaaa"},"change_message_id":"c1f8a5d64e125ffd04fd0db121fd0adb4995ece8","unresolved":false,"context_lines":[{"line_number":376,"context_line":"                # mark resource with no matching samples for delete"},{"line_number":377,"context_line":"                resource_q.update({models.Resource.metadata_hash: \"delete_\""},{"line_number":378,"context_line":"                                  + cast(models.Resource.internal_id,"},{"line_number":379,"context_line":"                                         sa.String)},"},{"line_number":380,"context_line":"                                  synchronize_session\u003dFalse)"},{"line_number":381,"context_line":""},{"line_number":382,"context_line":"            # remove metadata of resources marked for delete"}],"source_content_type":"text/x-python","patch_set":9,"id":"ba15a1d1_573b8deb","line":379,"in_reply_to":"ba15a1d1_a6bc416d","updated":"2015-09-15 03:59:26.000000000","message":"@gordon adding internal id will keep the row unique and maintain the unique constraint when its added in the future.","commit_id":"7bdd0bf2c5ea97f5dd500b0e4fdaaadf6ab00889"},{"author":{"_account_id":6676,"name":"zqfan","email":"aji.zqfan@gmail.com","username":"aji-zqfan"},"change_message_id":"7acb7da9822107b7f58461a9733e4cfba74f9b89","unresolved":false,"context_lines":[{"line_number":376,"context_line":"                # mark resource with no matching samples for delete"},{"line_number":377,"context_line":"                resource_q.update({models.Resource.metadata_hash: \"delete_\""},{"line_number":378,"context_line":"                                  + cast(models.Resource.internal_id,"},{"line_number":379,"context_line":"                                         sa.String)},"},{"line_number":380,"context_line":"                                  synchronize_session\u003dFalse)"},{"line_number":381,"context_line":""},{"line_number":382,"context_line":"            # remove metadata of resources marked for delete"}],"source_content_type":"text/x-python","patch_set":9,"id":"ba15a1d1_a6bc416d","line":379,"in_reply_to":"da20952f_709ead08","updated":"2015-09-15 01:16:11.000000000","message":"IMHO it requires because there may have (res1, hash1), (res1, hash2) entries, and if in future, we need the unique constraint (actually there is a bug for this problem mentioned in previously comment), then the (res1, \u0027delete\u0027), (res1, \u0027delete\u0027) will fail to update. Using the internal id (resource id can be duplicate so it is not suitable) can unique the pairs.","commit_id":"7bdd0bf2c5ea97f5dd500b0e4fdaaadf6ab00889"}]}