PostgreSQL and sequenceExists -- bug?

General Discussion
1

So as part of my OpenNMS transition to Liquibase, I’ve got a set of changeSets which essentially does “create the schema if it doesn’t exist; if it does, mark it as ran” so I can bootstrap from an existing database to one being tracked by liquibase.

One of the things I’m trying to create is a sequence, if it’s not already there:

On a database that already has the sequence, it’s failing:

    Exception in thread "main" org.opennms.core.schema.MigrationException: unable to update the database         at org.opennms.core.schema.Migrator.migrate(Migrator.java:72)         at org.opennms.install.Installer.install(Installer.java:284)         at org.opennms.install.Installer.main(Installer.java:860) Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set sequences.xml::opennmsnxtid::rangerrick:     Reason: liquibase.exception.JDBCException: Error executing SQL CREATE SEQUENCE opennmsnxtid:           Caused By: Error executing SQL CREATE SEQUENCE opennmsnxtid:           Caused By: ERROR: relation "opennmsnxtid" already exists         at liquibase.ChangeSet.execute(ChangeSet.java:238)         at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)         at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)         at liquibase.Liquibase.update(Liquibase.java:112)         at org.opennms.core.schema.Migrator.migrate(Migrator.java:69)         ... 2 more Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE SEQUENCE opennmsnxtid         at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:56)         at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:87)         at liquibase.change.AbstractChange.execute(AbstractChange.java:251)         at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:97)         at liquibase.ChangeSet.execute(ChangeSet.java:214)         ... 6 more Caused by: org.postgresql.util.PSQLException: ERROR: relation "opennmsnxtid" already exists         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)         at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:332)         at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:79)         at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:49)         ... 10 more

It’s definitely preexisting in the database, it’s in a pg_dump:

    –
    – Name: opennmsnxtid; Type: SEQUENCE; Schema: public; Owner: opennms

CREATE SEQUENCE opennmsnxtid
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public.opennmsnxtid OWNER TO opennms;

–
– Name: opennmsnxtid; Type: SEQUENCE SET; Schema: public; Owner: opennms

SELECT pg_catalog.setval(‘opennmsnxtid’, 1401, true);

Any ideas what’s going wrong?

2

So here’s something interesting.  We were digging around in the queries that liquibase makes to find the sequence, and the nextval not in stuff seems to filter the sequence out:

    opennms=# SELECT relname AS SEQUENCE_NAME FROM pg_class, pg_namespace WHERE relkind=‘S’ AND pg_class.relnamespace = pg_namespace.oid;  sequence_name 

opennmsnxtid
vulnnxtid
alarmsnxtid
catnxtid
demandpollnxtid
eventsnxtid
mapnxtid
nodenxtid
notifynxtid
outagenxtid
pollresultnxtid
reportnxtid
servicenxtid
usernotifnxtid
(14 rows)

opennms=# SELECT relname AS SEQUENCE_NAME FROM pg_class, pg_namespace WHERE relkind=‘S’ AND pg_class.relnamespace = pg_namespace.oid AND ‘nextval(’’’||relname||’’’::regclass)’ not in (select adsrc from pg_attrdef where adsrc is not null) ;
  sequence_name 

vulnnxtid
alarmsnxtid
catnxtid
demandpollnxtid
eventsnxtid
mapnxtid
nodenxtid
notifynxtid
outagenxtid
pollresultnxtid
reportnxtid
servicenxtid
usernotifnxtid
(13 rows)

Note how in the first one it shows up, and the second it doesn’t.  What is the significance of that?  I don’t know postgresql’s guts this well.

3

I think it was related to separating out stand alone sequences from serial column sequences.  I’ll have to look into it more, though.  Could you create an issue for it on liquibase.jira.com?

Nathan