Ошибка more than one owned sequence found

Update: This bug has been fixed in PostgreSQL v12 with commit 19781729f78.
The rest of the answer is relevant for older versions.

A serial column has a sequence that is owned by the column and a DEFAULT value that gets the net sequence value.

If you try to change that column into an identity column, you’ll get an error that there is already a default value for the column.

Now you must have dropped the default value, but not the sequence that belongs to the serial column. Then when you converted the column into an identity column, a second sequence owned by the column was created.

Now when you try to insert a row, PostgreSQL tries to find and use the sequence owned by the column, but there are two, hence the error message.

I’d argue that this is a bug in PostgreSQL: in my opinion, it should either have repurposed the existing sequence for the identity column or given you an error that there is already a sequence owned by the column, and you should drop it. I’ll try to get this bug fixed.

Meanwhile, you should manually drop the sequence left behind from the serial column.
Run the following query:

SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

That should give you the name of the sequence left behind from the serial column. Drop it, and the identity column should behave as desired.

ALTER TABLE Patient ALTER PatientId
   ADD GENERATED ALWAYS AS IDENTITY (START WITH 1);

more than one owned sequence found

ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

2 ответа

Лучший ответ

SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

alter TABLE db.generic_items alter column generic_item_id drop default;
alter TABLE db.generic_items alter column generic_item_id add generated by default as identity;

CREATE TABLE db.generic_items
(
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_generic_items PRIMARY KEY (generic_item_id),
)

Обновлять: Эта ошибка была исправлена ​​в PostgreSQL v12 с фиксацией 19781729f78.
Остальная часть ответа актуальна для более старых версий.

Столбец serial имеет последовательность, которой владеет столбец, и значение DEFAULT, которое получает чистое значение последовательности.

Если вы попытаетесь изменить этот столбец на столбец идентификаторов, вы получите сообщение об ошибке, что для столбца уже есть значение по умолчанию.

Теперь вы, должно быть, отбросили значение по умолчанию, но не последовательность, принадлежащую столбцу serial. Затем, когда вы преобразовали столбец в столбец идентификаторов, была создана вторая последовательность, принадлежащая столбцу.

Теперь, когда вы пытаетесь вставить строку, PostgreSQL пытается найти и использовать последовательность в, принадлежащую столбцу, но их две, отсюда и сообщение об ошибке.

Я бы сказал, что это ошибка в PostgreSQL: на мой взгляд, он должен был либо переназначить существующую последовательность для столбца идентификаторов, либо дать вам ошибку, что уже существует последовательность, принадлежащая столбцу, и вы должны ее удалить. попробую исправить этот баг.

Между тем, вы должны вручную удалить последовательность, оставшуюся позади, из столбца serial.
Запустите следующий запрос:

SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

Это должно дать вам название последовательности, оставшейся после столбца serial. Отбросьте его, и столбец идентификаторов должен вести себя так, как хотелось бы.

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as idname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as seqname,
            columns.table_schema as schamename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   ) loop
    execute format('select last_value from %I.%s',_r.schamename,_r.seqname) into _i;
    execute format('select max(%I) from %I.%I',_r.idname,_r.schamename,_r.tablename) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.tablename||'_Id_seq',' from:',_i,' to:',_m+1);
      execute format('alter sequence %I.%s restart with %s',_r.schamename,_r.seqname,_m+1);
    end if;
  end loop;

end;
$$
;

FlyWay + Spring Boot 2.x for vendor specific scripts error: found more than one migration with version 1.x

@Bean(initMethod = "migrate")
    Flyway flyway() {
        Flyway flyway = Flyway
                .configure()
                .dataSource(dataSource(dataSourceProperties()))
                .locations("classpath:db/migration/MySQL", "classpath:db/migration/H2")
                .load();
        return flyway;
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource")
    public HikariDataSource dataSource(DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
    }

spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://localhost:3306/messages
    username: username
    password: password

Caused by: org.flywaydb.core.api.FlywayException: Found more than one migration with version 1.1
Offenders:
-> C:Projectsmy-projectoutproductionresourcesdbspecificMySQLV1_1__Create_table_message.sql (SQL)
-> C:Projectsmy-projectoutproductionresourcesdbspecificH2V1_1__Create_table_message.sql (SQL)

.locations("classpath:db/migration/MySQL", "classpath:db/migration/H2")

String driverVendor = ..; // something to decide h2 or mysql
Flyway flyway = Flyway
        .configure()
        .locations("classpath:db/migration/" + driverVendor)

spring.flyway.locations=classpath:db/migration/{vendor}