Home > mailing lists

Thread: Insert in table with UNIQUE index

Insert in table with UNIQUE index

From

Artem Tomyuk

Date:

27 January 2016, 16:31:14

Hi All!

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);

Re: Insert in table with UNIQUE index

From

Albe Laurenz

Date:

27 January 2016, 17:13:00

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?

Do you get an error when you
   REINDEX INDEX _inforg6716_bynodemsg_rn_test;

Yours,
Laurenz Albe

Re: Insert in table with UNIQUE index

From

Samed YILDIRIM

Date:

27 January 2016, 18:01:11

<div>Hi Artem,</div><div> </div><div>You can see exact same record in your select results from _infogcngr6716_test
tabledespite uniq index exists, if you create a table inherited from the table.</div><div> </div><div>For
example:</div><div>Createinherited table</div><div><span style="font-family:terminal,monaco;font-size:x-small;">CREATE
TABLEpublic._inforgcngr6716_test_child</span><br /><span
style="font-family:terminal,monaco;font-size:x-small;">(</span><br/><span
style="font-family:terminal,monaco;font-size:x-small;">  LIKE public._inforgchngr6716_test INCLUDING DEFAULTS INCLUDING
CONSTRAINTSINCLUDING INDEXES</span><br /><span style="font-family:terminal,monaco;font-size:x-small;">) </span><br
/><spanstyle="font-family:terminal,monaco;font-size:x-small;">INHERITS (_inforgchngr6716_test)</span><br /><span
style="font-family:terminal,monaco;font-size:x-small;">WITH(</span><br /><span
style="font-family:terminal,monaco;font-size:x-small;"> OIDS = FALSE</span><br /><span
style="font-family:terminal,monaco;font-size:x-small;">);</span></div><div> </div><div>Insertsame records into these
twotable. (No error returns)</div><div><span style="font-family:terminal,monaco;font-size:x-small;">INSERT INTO
_infogcngr6716_test_child(</span><br/><span style="font-family:terminal,monaco;font-size:x-small;">           
_nodetref,_noderref, _messageno)</span><br /><span style="font-family:terminal,monaco;font-size:x-small;">    VALUES
('asd','asd',10);</span><br /><br /><span style="font-family:terminal,monaco;font-size:x-small;">INSERT INTO
_infogcngr6716_test(</span><br/><span style="font-family:terminal,monaco;font-size:x-small;">            _nodetref,
_noderref,_messageno)</span><br /><span style="font-family:terminal,monaco;font-size:x-small;">    VALUES ('asd',
'asd',10);</span></div><div> </div><div>Make select query to parent table</div><div><span
style="font-family:terminal,monaco;font-size:x-small;">SELECT_nodetref, _noderref, _messageno</span><br /><span
style="font-family:terminal,monaco;font-size:x-small;"> FROM _inforgchngr6716_test;</span><br /><br
/></div><div>"asd";"asd";10<br/>"asd";"asd";10<br /><br /></div><div><div>Make select query to only parent
table</div><spanstyle="font-family:terminal,monaco;font-size:x-small;">SELECT _nodetref, _noderref,
_messageno</span><br/><span style="font-family:terminal,monaco;font-size:x-small;">  FROM ONLY
_inforgchngr6716_test;</span></div><div> </div><div>"asd";"asd";10</div><div> </div><div>İyiçalışmalar.</div><div>Samed
YILDIRIM</div><div> </div><div> </div><div> </div><div>27.01.2016,16:14, "Albe Laurenz"
<laurenz.albe@wien.gv.at>:</div><blockquotetype="cite"><p>Artem Tomyuk wrote:<blockquote> I have a table with
uniqueindex with 2 exactly the same rows.<br /> How it can be possible?<br /><br /><br /> CREATE TABLE
_inforgchngr6716_test<br/> (<br />   _nodetref bytea NOT NULL,<br />   _noderref bytea NOT NULL,<br />   _messageno
numeric(10,0)<br/> )<br /> WITH (<br />   OIDS=FALSE<br /> );<br /> ALTER TABLE _inforgchngr6716_test<br />   OWNER TO
postgres;<br/><br /> -- Index: _inforg6716_bynodemsg_rn_test<br /><br /> -- DROP INDEX
_inforg6716_bynodemsg_rn_test;<br/><br /> CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_test<br />   ON
_inforgchngr6716_test<br/>   USING btree<br />   (_nodetref, _noderref, _messageno);</blockquote><p><br />Maybe index
corruption.<br/>Did you have any crashes?<br /><br />Do you get an error when you<br />   REINDEX INDEX
_inforg6716_bynodemsg_rn_test;<br/><br />Yours,<br />Laurenz Albe<br /><br /><span>-- <br />Sent via pgsql-admin
mailinglist (<a href="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</a>)<br />To make changes to your
subscription:<br/><a
href="http://www.postgresql.org/mailpref/pgsql-admin">http://www.postgresql.org/mailpref/pgsql-admin</a><br
/></span></blockquote>

Re: Insert in table with UNIQUE index

From

"David G. Johnston"

Date:

27 January 2016, 18:49:50

On Wed, Jan 27, 2016 at 8:00 AM, Samed YILDIRIM <samed@reddoc.net> wrote:

Hi Artem,

You can see exact same record in your select results from _infogcngr6716_test table despite uniq index exists, if you create a table inherited from the table.

So what? That is not the problem presented.

David J.

Re: Insert in table with UNIQUE index

From

bricklen

Date:

27 January 2016, 19:06:10

On Wed, Jan 27, 2016 at 5:30 AM, Artem Tomyuk <admin@leboutique.com> 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)
)

CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_test
ON _inforgchngr6716_test
USING btree
(_nodetref, _noderref, _messageno);

In the duplicated entries, are any of the "_messageno" values NULL? If so, that would explain it. NULL is not bound by the constraint, you have a few options: make it NOT NULL, COALESCE() it to a known value, or create a couple partial indexes to enforce the uniqueness.

Re: Insert in table with UNIQUE index

From

jaime soler

Date:

01 February 2016, 15:55:49

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
>