Re: BUG #8553: CRITICAL - delete from doesn't delete rows, select returns them anyway

REINDEX cannot be done. Getting "ERROR:  could not create unique index
"pk_job" DETAIL:  Key (jobid)=(2) is duplicated". And as I mentioned
earlier - I cannot delete these rows as well.

Vadim Yevsyukov
Motor Vehicle Software Corporation
http://www.mvscusa.com

On 10/25/2013 7:14 AM, Pavel Stehule wrote:
>
> It looks like broken index. I dont know what is reason of this error,
> but REINDEX should helps.
>
> Dne 25.10.2013 16:03 <vadim@mvscusa.com <mailto:vadim@mvscusa.com>>
> napsal(a):
>
>     The following bug has been logged on the website:
>
>     Bug reference:      8553
>     Logged by:          Vadim Yevsyukov
>     Email address: vadim@mvscusa.com <mailto:vadim@mvscusa.com>
>     PostgreSQL version: 9.3.1
>     Operating system:   CentOS 6.4
>     Description:
>
>     After upgrading to PostgreSQL 9.3.1.
>
>
>     Schema:
>
>
>     create sequence JobSeq start 1;
>     create table Job
>     (
>             jobId int not null default nextval('JobSeq'),
>             name varchar(50) not null,
>             className varchar(50) not null,
>             timeoutSeconds int null,
>             enabled boolean not null,
>             nextRun timestamp null,
>             running boolean not null,
>             dayMask int null,
>             timeOfDaySeconds int null,
>             dayOfMonth int null,
>             nextRunForce boolean not null,
>             constraint PK_Job primary key (jobId)
>     );
>
>
>     I noticed a weird exceptions in my billing system that's processing
>     payments. After some research, I found that my internal job
>     scheduler's
>     table in PostgreSQL has two records for 'Payment Processor' job
>     (jobId = 2),
>     which should not be possible, since jobId is the primary key.
>
>
>     I tried to delete records: delete from Job where jobId = 2; But
>     instead of
>     reporting '2 records affected', PostgreSQL has reported 'one record
>     affected'. I ran 'select from Job where jobId = 2' and it returned
>     no rows,
>     however when I ran query without 'where' clause 'select * from
>     Job' - it did
>     returned two records with jobId = 2.
>
>
>     Query 'select count(*) from Job where jobId = 2' also reporting 0,
>     despite
>     two records returned in 'select * from Job'.
>
>
>     Does look like a CRITICAL bug to me. Let me know please if additional
>     information is needed.
>
>
>     Thank you,
>     Vadim
>
>
>
>     --
>     Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org
>     <mailto:pgsql-bugs@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-bugs
>