pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert in table with UNIQUE index
6+ messages / 6 participants
[nested] [flat]
* Insert in table with UNIQUE index
@ 2016-01-27 13:30 Artem Tomyuk <admin@leboutique.com>
0 siblings, 2 replies; 6+ messages in thread
From: Artem Tomyuk @ 2016-01-27 13:30 UTC (permalink / raw)
To: pgsql-admin
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);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert in table with UNIQUE index
@ 2016-01-27 14:12 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Artem Tomyuk <admin@leboutique.com>
1 sibling, 2 replies; 6+ messages in thread
From: Albe Laurenz @ 2016-01-27 14:12 UTC (permalink / raw)
To: 'Artem Tomyuk *EXTERN*' <admin@leboutique.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert in table with UNIQUE index
@ 2016-01-27 15:00 Samed YILDIRIM <samed@reddoc.net>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 1 reply; 6+ messages in thread
From: Samed YILDIRIM @ 2016-01-27 15:00 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; Artem Tomyuk *EXTERN* <admin@leboutique.com>; pgsql-admin
<div>Hi Artem,</div><div> </div><div>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.</div><div> </div><div>For example:</div><div>Create inherited table</div><div><span style="font-family:terminal,monaco;font-size:x-small;">CREATE TABLE public._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 CONSTRAINTS INCLUDING INDEXES</span><br /><span style="font-family:terminal,monaco;font-size:x-small;">) </span><br /><span style="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>Insert same records into these two table. (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><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 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><blockquote type="cite"><p>Artem Tomyuk wrote:</p><blockquote> I have a table with unique index 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 /></p><span>-- <br />Sent via pgsql-admin mailing list (<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-admi... /></span></blockquote>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert in table with UNIQUE index
@ 2016-01-27 15:49 David G. Johnston <david.g.johnston@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2016-01-27 15:49 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; Artem Tomyuk *EXTERN* <admin@leboutique.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert in table with UNIQUE index
@ 2016-01-27 16:06 bricklen <bricklen@gmail.com>
parent: Artem Tomyuk <admin@leboutique.com>
1 sibling, 0 replies; 6+ messages in thread
From: bricklen @ 2016-01-27 16:06 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert in table with UNIQUE index
@ 2016-02-01 12:55 jaime soler <jaime.soler@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 0 replies; 6+ messages in thread
From: jaime soler @ 2016-02-01 12:55 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; Artem Tomyuk *EXTERN* <admin@leboutique.com>; pgsql-admin
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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-02-01 12:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-27 13:30 Insert in table with UNIQUE index Artem Tomyuk <admin@leboutique.com>
2016-01-27 14:12 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2016-01-27 15:00 ` Samed YILDIRIM <samed@reddoc.net>
2016-01-27 15:49 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-01 12:55 ` jaime soler <jaime.soler@gmail.com>
2016-01-27 16:06 ` bricklen <bricklen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox