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