Re: Insert in table with UNIQUE index

El mié, 27-01-2016 a las 14:12 +0000, Albe Laurenz escribió:
> Artem Tomyuk wrote:
> > I have a table with unique index with 2 exactly the same rows.
> > How it can be possible?
> >
> >
> > CREATE TABLE _inforgchngr6716_test
> > (
> > _nodetref bytea NOT NULL,
> > _noderref bytea NOT NULL,
> > _messageno numeric(10,0)
> > )
> > WITH (
> > OIDS=FALSE
> > );
> > ALTER TABLE _inforgchngr6716_test
> > OWNER TO postgres;
> >
> > -- Index: _inforg6716_bynodemsg_rn_test
> >
> > -- DROP INDEX _inforg6716_bynodemsg_rn_test;
> >
> > CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_test
> > ON _inforgchngr6716_test
> > USING btree
> > (_nodetref, _noderref, _messageno);
>
> Maybe index corruption.
> Did you have any crashes?

To discard a index corruption problem, try to select duplicates row
setting enable_indexscan and enable_bitmapscan to false and check if
you get 1 or 2 rows, if you get 2 -> I'll follow Albe advide and
reindex
>
> Do you get an error when you
> REINDEX INDEX _inforg6716_bynodemsg_rn_test;
>
> Yours,
> Laurenz Albe
>