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

Additional information on the issue - 'Equal' and 'between' operators
return different results, see below.

>psql -h pgsqltest -U billing -d billing

billing=# select jobid, name from job where jobid = 2;
jobid | name
-------+-------------------
2 | Payment Processor
(1 row)

billing=# select jobid, name from job where jobid between 2 and 2;
jobid | name
-------+-------------------
2 | Payment Processor
2 | Payment Processor
2 | Payment Processor
(3 rows)

Dump file also contains duplicates, see attached file.

>pg_dump.exe --host pgsqltest ... --format plain --file
"job-plain.backup" --table "public.job" "billing"

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(at)mvscusa(dot)com <mailto: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 <mailto: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
> <mailto:pgsql-bugs(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>