On 09/02/2016 04:32 AM, Jonas Tehler wrote:
Hi,
We’re running Postgresql 9.4.5 on Amazon RDS. One of our tables
looks something like this:
CREATE TABLE users
(
...
email character varying(128) NOT NULL,
...
CONSTRAINT users_email_key UNIQUE (email)
)
Despite this we have rows with very similar email values. I discovered
the problem when I tried to add a column and got the following error:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: could not
create unique index "users_email_key"
DETAIL: Key (email)=(xxx@xxxxxxx <mailto:xxx@xxxxxxx>) is duplicated.
: ALTER TABLE "users" ADD COLUMN "client_branch" character varying(255)
DEFAULT ‘beta'
Now look at this:
=> select email from users where email = 'xxx@xxxxxxx <mailto:xxx@xxxxxxx>';
email
---------------------------
xxx@xxxxxxx <mailto:xxx@xxxxxxx>
(1 row)
=> select email from users where email LIKE 'xxx@xxxxxxx
<mailto:xxx@xxxxxxx>';
email
---------------------------
xxx@xxxxxxx <mailto:xxx@xxxxxxx>
xxx@xxxxxxx <mailto:xxx@xxxxxxx>
(2 rows)
I have tried to compare the binary data in various ways, email::bytes,
md5(email), encode(email::bytea, 'hex’), char_length(email) and it all
looks the same for both rows.
Any suggestions how I can discover the difference between the values and
how they could have been added without triggering the constraint? I know
that the values were added after the constraint was added.
select ctid, email from users where email LIKE 'xxx@xxxxxxx';
https://www.postgresql.org/docs/9.5/static/ddl-system-columns.html
"ctid
The physical location of the row version within its table. Note
that although the ctid can be used to locate the row version very
quickly, a row's ctid will change if it is updated or moved by VACUUM
FULL. Therefore ctid is useless as a long-term row identifier. The OID,
or even better a user-defined serial number, should be used to identify
logical rows.
"
Best guess is the INDEX on the column is corrupted and needs to be
reindexed:
https://www.postgresql.org/docs/9.5/static/sql-reindex.html
The data was added from a Ruby on Rails app that also has unique
constraints on the email field and validation on the email format.
/ Jonas
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
