Open Microscopy Environment

Hi, I'm attempting an upgrade of our Omero instance from 5.0.8 to 5.1.2. I'm running Postgres 9.3.6, client and server, on CentOS 6, and when I run the upgrade script like this as the omero user:

/usr/pgsql-9.3/bin/psql -h localhost -U omero omero < sql/psql/OMERO5.1__1/OMERO5.0__0.sql

The script starts and chugs along for a bit, then dies with:

CREATE FUNCTION
ERROR: must be owner of function annotation_link_delete_trigger
ERROR: current transaction is aborted, commands ignored until end of transaction block


Any help is appreciated, and I'm happy to provide any additional info.

Thanks,
Rick

Hi Rick

It sounds like some (or all) of your tables might be owned by a different user. Can you paste the first few lines of:

Code: Select all

psql -h localhost -U omero omero -c '\df+ annotation_link_delete_trigger'


The first result row should tell you who owns that object.

Simon

Thanks, looks like it's owned by the postgres user:

-bash-4.1$ psql -h localhost -U omero omero -c '\df+ annotation_link_delete_trigger'
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language |
Source code | Description
--------+--------------------------------+------------------+---------------------+---------+------------+----------+----------+------
--------------------------------------------------------------------------------------------+-------------
public | annotation_link_delete_trigger | trigger | | trigger | volatile | postgres | plpgsql


Should I be connecting to the DB as postgres instead of Omero? Or just change the owner of that object to omero? Thanks,
Rick

Hi Rick,

were you previously accessing this database as `postgres` or did you perhaps restore the DB recently? You'll either need to use the `postgres` user everywhere, or change all the objects to belong to the `omero` user. (It may be easier to restore the database as `omero` rather than trying to change all the objects)

Cheers,
~Josh

Thanks, when we went from v 4.x to 5.0, we dumped the DB and restored it on a new server - Likely as the postgres user. I'll try running the upgrade script as the postgres user 1st. Thanks,
Rich