search

chop-dbhi / omop_harvest

OMOP CDMV4 data model with OSIM2 data in Harvest
https://eig.research.chop.edu/omop/
Other
5 stars 3 forks source link

Gender.view.refresh() - materialized views failing when i try to refresh them once actual data in Person table. #92

Open rv816 opened 9 years ago

rv816 commented 9 years ago

(omop_harvest-env)~/D/new_omop_harvest ❯❯❯ ./bin/manage.py shell Python 2.7.5 (default, Mar 9 2014, 22:15:05) [GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin Type "help", "copyright", "credits" or "license" for more information. (InteractiveConsole)

from omop_harvest.models import Person Gender.view.refresh() Traceback (most recent call last): File "", line 1, in NameError: name 'Gender' is not defined from omop_harvest.models import * Gender.view.refresh() FATAL ERROR - The following SQL query failed: INSERT INTO "gender" ("concept_id", "concept_name", "concept_level", "concept_class", "concept_code", "valid_start_date", "valid_end_date", "invalid_reason", "vocabulary_id") SELECT "concept_id", "concept_name", "concept_level", "concept_class", "concept_code", "valid_start_date", "valid_end_date", "invalid_reason", "vocabulary_id" FROM "concept" JOIN "person" ON "concept"."concept_id" = "person"."gender_concept_id" The error was: duplicate key value violates unique constraint "gender_pkey" DETAIL: Key (concept_id)=(8507) already exists.

Traceback (most recent call last): File "", line 1, in File "./omop_harvest/models.py", line 30, in refresh db.refresh_view(self.model) File "./omop_harvest/dbops/generic.py", line 22, in refresh_view "%(join_field_name)s" % params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 282, in execute cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute return self.cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 56, in execute six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2]) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute return self.cursor.execute(query, args) IntegrityError: duplicate key value violates unique constraint "gender_pkey" DETAIL: Key (concept_id)=(8507) already exists.

Gender.view.refresh() FATAL ERROR - The following SQL query failed: DELETE FROM "gender"; The error was: current transaction is aborted, commands ignored until end of transaction block

Traceback (most recent call last): File "", line 1, in File "./omop_harvest/models.py", line 30, in refresh db.refresh_view(self.model) File "./omop_harvest/dbops/generic.py", line 7, in refresh_view self.clear_table(table_name) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 47, in _cache_clear return func(self, table, _args, _opts) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 398, in clear_table self.execute('DELETE FROM %s;' % params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 282, in execute cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute return self.cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 58, in execute six.reraise(utils.DatabaseError, utils.DatabaseError(tuple(e.args)), sys.exc_info()[2]) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute return self.cursor.execute(query, args) DatabaseError: current transaction is aborted, commands ignored until end of transaction block

Race.view.refresh() FATAL ERROR - The following SQL query failed: DELETE FROM "race"; The error was: current transaction is aborted, commands ignored until end of transaction block

Traceback (most recent call last): File "", line 1, in File "./omop_harvest/models.py", line 30, in refresh db.refresh_view(self.model) File "./omop_harvest/dbops/generic.py", line 7, in refresh_view self.clear_table(table_name) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 47, in _cache_clear return func(self, table, _args, _opts) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 398, in clear_table self.execute('DELETE FROM %s;' % params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 282, in execute cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute return self.cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 58, in execute six.reraise(utils.DatabaseError, utils.DatabaseError(tuple(e.args)), sys.exc_info()[2]) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute return self.cursor.execute(query, args) DatabaseError: current transaction is aborted, commands ignored until end of transaction block

quit() (omop_harvest-env)~/D/new_omop_harvest ❯❯❯ ./bin/manage.py runserver Validating models...

0 errors found November 21, 2014 - 01:20:10 Django version 1.5.11, using settings 'omop_harvest.conf.settings' Development server is running at http://127.0.0.1:8000/ Quit the server with CONTROL-C. ^C% (omop_harvest-env)~/D/new_omop_harvest ❯❯❯ ./bin/manage.py shell Python 2.7.5 (default, Mar 9 2014, 22:15:05) [GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin Type "help", "copyright", "credits" or "license" for more information. (InteractiveConsole)

from omop_harvest.models import Gender Gender.view.refresh() FATAL ERROR - The following SQL query failed: INSERT INTO "gender" ("concept_id", "concept_name", "concept_level", "concept_class", "concept_code", "valid_start_date", "valid_end_date", "invalid_reason", "vocabulary_id") SELECT "concept_id", "concept_name", "concept_level", "concept_class", "concept_code", "valid_start_date", "valid_end_date", "invalid_reason", "vocabulary_id" FROM "concept" JOIN "person" ON "concept"."concept_id" = "person"."gender_concept_id" The error was: duplicate key value violates unique constraint "gender_pkey" DETAIL: Key (concept_id)=(8507) already exists.

Traceback (most recent call last): File "", line 1, in File "./omop_harvest/models.py", line 30, in refresh db.refresh_view(self.model) File "./omop_harvest/dbops/generic.py", line 22, in refresh_view "%(join_field_name)s" % params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/south/db/generic.py", line 282, in execute cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute return self.cursor.execute(sql, params) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 56, in execute six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2]) File "/Users/vassr/Documents/omop_harvest-env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute return self.cursor.execute(query, args) IntegrityError: duplicate key value violates unique constraint "gender_pkey" DETAIL: Key (concept_id)=(8507) already exists.

gracebrownecodes commented 9 years ago

@rv816 Did the fix suggested over email resolve this issue?

Screenshot of fix in omop_harvest/dbops/generic.py: pastedgraphic-1