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

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

From

vadim@mvscusa.com

Date:

25 October 2013, 17:03:04

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

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

From

Pavel Stehule

Date:

25 October 2013, 17:14:19

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
>

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

From

Pavel Stehule

Date:

25 October 2013, 20:27:58

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

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

From

Vadim Yevsyukov

Date:

26 October 2013, 00:10:21

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
>

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

From

Vadim Yevsyukov

Date:

26 October 2013, 06:42:54

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

Attachment

job-plain.backup