Postgres对索引的唯一约束不起作用 [英] Postgres unique constraint on index does not work

问题描述

在一个相当简单的表上

  CREATE TABLE collectionmaster 
（
 id字符变化（32） NOT NULL，
版本整数，
 publisherid字符变化（12），
标题字符变化（1024），
字幕字符变化（1024），
字符变化（255）NOT NULL，
 CONSTRAINT collectionmaster_pkey PRIMARY KEY（id）
）
 WITH（
 OIDS = FALSE 
）; 
 CREATE INDEX idx_coll_title 
 ON collectionmaster 
 USING btree 
（title COLLATE pg_catalog。default）; 
  

我尝试通过唯一索引添加唯一检查

  CREATE UNIQUE INDEX idx_uni_coll_title 
 ON collectionmaster 
 USING btree 
（publisherid COLLATE pg_catalog。default，title COLLATE pg_catalog。default ，（COALESCE（subtitle，'no_sub':: character varying））COLLATE pg_catalog。default）; 
  

或通过唯一约束

  ALTER TABLE collectionmaster 
 ADD CONSTRAINT uni_coll_title UNIQUE（publisherid，title，subtitle）; 
  

可以截取多个线程中执行的java服务（spring-jpa on hibernate）但奇怪的是，索引和约束都没有按预期那样工作。八条记录由八条线程添加，根据单独事务中的约束或索引，这些线程不是唯一的，但不会引发唯一的违规，并且所有记录都会插入到表中。没有一个值是空的，就像其他问题中的问题一样。
这个constaint不是延迟的，索引作为约束在哪里有效。
Postgres版本是9.2。

我在这里相当无知。

编辑：这些是在这张表中查询的结果，从pgAdmin中提取出来（很难在这里更好地格式化）：

 id;version ;publisherid;title;subtitle;type
53b3df625baf40bf885b48daa366fbc8; 1;5109138;Titel;Untertitel;set
2673ef9a33f84289995d6f7288f07b46 1;5109138;Titel;Untertitel;set
ef7c385205034fdc89fe39e3eca48408; 1;5109138;Titel;Untertitel;set
527922f2f3464f91826dbae2e2b67caf; 1;5109138;Titel;Untertitel;set
794638a725324319852d10b828257df7; 1;5109138;Titel;Untertitel;set
dbe2201058974d63a2107131f0080233 1;5109138;Titel;Untertitel;set
cbb77c7c1adb415db006853a6f6244ac; 1;5109138;Titel;Untertitel;set
0b6606fe015040fbbc85444361ab414c 1;5109138;Titel;Untertitel;set
  

可以执行

 <$ （'aaa'，1，'5109138'，'Titel'，'Untertitel'，'set'）
插入到collectionmaster（id，版本，publisherid，title，subtitle， code> 

没有违反约束。我也不敢相信，但这是问题所在......

确保你的spring-jpa config < a href =http://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html =nofollow> spring.jpa。 hibernate.ddl-auto 未设置为 create-drop 。

<这样，休眠将永远放弃&重新创建您的整个模式，它不包含您的自定义唯一约束，也不包含您的索引。

On a quite simple table

CREATE TABLE collectionmaster
(
  id character varying(32) NOT NULL,
  version integer,
  publisherid character varying(12),
  title character varying(1024),
  subtitle character varying(1024),
  type character varying(255) NOT NULL,
  CONSTRAINT collectionmaster_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_coll_title
  ON collectionmaster
  USING btree
  (title COLLATE pg_catalog."default");

I tried to add a unique check either via unique index

CREATE UNIQUE INDEX idx_uni_coll_title
  ON collectionmaster
  USING btree
  (publisherid COLLATE pg_catalog."default", title COLLATE pg_catalog."default", (COALESCE(subtitle, 'no_sub'::character varying)) COLLATE pg_catalog."default");

or via unique constraint

ALTER TABLE collectionmaster
  ADD CONSTRAINT uni_coll_title UNIQUE(publisherid, title, subtitle);

to intercept accidentally multiple creation by a java service (spring-jpa on hibernate) executed in several threads. But strangely neither of index and constraint works as expected. Eight records are added by eight threads which are not unique according to either constraint or index in separate transaction, but no unique violations are thrown, and all records are inserted into the table. And none of the values are null, as was the problem in other questions here. The constaint was not deferred, index as constraint where valid. Postgres version is 9.2.

I am quite clueless here.

EDIT: These are the results of a query in this table, extracted from pgAdmin (hard to format it nicer here):

"id";"version";"publisherid";"title";"subtitle";"type"
"53b3df625baf40bf885b48daa366fbc8";1;"5109138";"Titel";"Untertitel";"set"
"2673ef9a33f84289995d6f7288f07b46";1;"5109138";"Titel";"Untertitel";"set"
"ef7c385205034fdc89fe39e3eca48408";1;"5109138";"Titel";"Untertitel";"set"
"527922f2f3464f91826dbae2e2b67caf";1;"5109138";"Titel";"Untertitel";"set"
"794638a725324319852d10b828257df7";1;"5109138";"Titel";"Untertitel";"set"
"dbe2201058974d63a2107131f0080233";1;"5109138";"Titel";"Untertitel";"set"
"cbb77c7c1adb415db006853a6f6244ac";1;"5109138";"Titel";"Untertitel";"set"
"0b6606fe015040fbbc85444361ab414c";1;"5109138";"Titel";"Untertitel";"set"

Even on these I can execute

insert into collectionmaster(id, version, publisherid, title, subtitle, type) values('aaa',1,'5109138','Titel','Untertitel','set')

without getting a constraint violation. I can't believe it, too, but this is the problem...

解决方案

Make sure your spring-jpa config spring.jpa.hibernate.ddl-auto is not set to create-drop.

That way, hibernate will always drop & re-create your whole schema, which won't contain your custom unique constraints, nor your indexes.

这篇关于Postgres对索引的唯一约束不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！