Problem upgrading DB from 67 to 68
Hi, Trying to upgrade DB schema ('sh manage_db.sh upgrade') from 67 to 68 fails with the following message: === sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "RENAME" LINE 1: ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_i... ^ 'ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_id_seq' {} === Full back-trace attached at the end of this email. My postgres version is 8.1.18 on CentOS 5.4. I suspect the error is that "ALTER SEQUENCE RENAME" is new to Postgres 9: http://www.postgresql.org/docs/9.0/static/sql-altersequence.html Where as in Postgres 8.1, there"s no "ALTER SEQUENCE RENAME": http://www.postgresql.org/docs/8.1/interactive/sql-altersequence.html The bottom notes on the 8.1 help page say: " Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. ". Any recommendations on how to proceed will be appreciated, otherwise I'll just change: lib/galaxy/model/migrate/versions/0068_rename_sequencer_to_external_services.py Thanks, -gordon ---------------------- sh manage_db.sh upgrade 67 -> 68... This migration script renames the sequencer table to 'external_service' table and creates a association table, 'request_type_external_service_association' and populates it. The 'sequencer_id' foreign_key from the 'request_type' table is removed. The 'sequencer_type_id' column is renamed to 'external_service_type_id' in the renamed table 'external_service'. Finally, adds a foreign key to the external_service table in the sample_dataset table and populates it. Traceback (most recent call last): File "./scripts/manage_db.py", line 55, in <module> main( repository=repo, url=db_url ) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0068_rename_sequencer_to_external_services.py", line 106, in upgrade db_session.execute( cmd ) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "RENAME" LINE 1: ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_i... ^ 'ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_id_seq' {}
Hello Assaf, As you have rightly pointed out, the script is failing as ALTER SEQUENCE .. RENAME TO is not supported in Postgres 8.1. It works on 8.3 onwards which we have here. I will post a patch for this soon. Thanks rc On Feb 1, 2011, at 5:36 PM, Assaf Gordon <gordon@cshl.edu> wrote:
Hi,
Trying to upgrade DB schema ('sh manage_db.sh upgrade') from 67 to 68 fails with the following message: === sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "RENAME" LINE 1: ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_i... ^ 'ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_id_seq' {} === Full back-trace attached at the end of this email.
My postgres version is 8.1.18 on CentOS 5.4.
I suspect the error is that "ALTER SEQUENCE RENAME" is new to Postgres 9: http://www.postgresql.org/docs/9.0/static/sql-altersequence.html Where as in Postgres 8.1, there"s no "ALTER SEQUENCE RENAME": http://www.postgresql.org/docs/8.1/interactive/sql-altersequence.html
The bottom notes on the 8.1 help page say: " Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. ".
Any recommendations on how to proceed will be appreciated, otherwise I'll just change: lib/galaxy/model/migrate/versions/0068_rename_sequencer_to_external_services.py
Thanks, -gordon
----------------------
sh manage_db.sh upgrade 67 -> 68...
This migration script renames the sequencer table to 'external_service' table and creates a association table, 'request_type_external_service_association' and populates it. The 'sequencer_id' foreign_key from the 'request_type' table is removed. The 'sequencer_type_id' column is renamed to 'external_service_type_id' in the renamed table 'external_service'. Finally, adds a foreign key to the external_service table in the sample_dataset table and populates it.
Traceback (most recent call last): File "./scripts/manage_db.py", line 55, in <module> main( repository=repo, url=db_url ) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/gordon/projects/galaxy_devel/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0068_rename_sequencer_to_external_services.py", line 106, in upgrade db_session.execute( cmd ) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/gordon/projects/galaxy_devel/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "RENAME" LINE 1: ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_i... ^ 'ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_id_seq' {} _______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
participants (2)
-
Assaf Gordon -
Ramkrishna Chakrabarty