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

2013/10/25 Vadim Yevsyukov <vadim@mvscusa.com>

>  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.
>

so it is little bit worse:

a) delete a duplicate rows:

set enable_indexscan to off; -- now indexes will not be used
select jobid from job group by 1 having count(*) > 1; -- print duplicates
delete from job where jobid in (2, ...)

reindex job;

regards

Pavel

p.s. you should to find a reason why indexes are broken - not enough
space, ...

and check all indexes


>
> Vadim Yevsyukov
> Motor Vehicle Software Corporationhttp://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> napsal(a):
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      8553
>> Logged by:          Vadim Yevsyukov
>> Email address:      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)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>