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

2013/10/25 Vadim Yevsyukov <vadim(at)mvscusa(dot)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(at)mvscusa(dot)com> napsal(a):
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 8553
>> Logged by: Vadim Yevsyukov
>> Email address: vadim(at)mvscusa(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>