BUG #8553: CRITICAL - delete from doesn't delete rows, select returns them anyway
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
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):
Show quoted text
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
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
Show quoted text
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
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
Show quoted text
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
Attachments:
job-plain.backuptext/plain; charset=windows-1252; name=job-plain.backupDownload
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
Show quoted text
Vadim Yevsyukov
Motor Vehicle Software Corporationhttp://www.mvscusa.comOn 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