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 <gor...@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
_______________________________________________
galaxy-dev mailing list
galaxy-dev@lists.bx.psu.edu
http://lists.bx.psu.edu/listinfo/galaxy-dev
