From: Samed YILDIRIM <samed@reddoc.net>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Artem Tomyuk *EXTERN* <admin@leboutique.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Insert in table with UNIQUE index
Date: Wed, 27 Jan 2016 17:00:53 +0200
Message-ID: <37051453906853@web20o.yandex.ru> (raw)
In-Reply-To: <A737B7A37273E048B164557ADEF4A58B537D9AF2@ntex2010i.host.magwien.gv.at>
References: <CANYYVqKwbPTj8JHS1MqYVnXzVtAr25bO+eEBWceCz2npoW0Vbg@mail.gmail.com>
<A737B7A37273E048B164557ADEF4A58B537D9AF2@ntex2010i.host.magwien.gv.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: samed@reddoc.net, laurenz.albe@wien.gv.at, admin@leboutique.com
Subject: Re: Insert in table with UNIQUE index
In-Reply-To: <37051453906853@web20o.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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