Thread: possible bug
Hello,
id |code|regno|col_3|
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|
id |code|regno|col_3|
-----------+----+-----+-----+
CREATE TABLE kap.training_type (
id int8 NOT NULL,
code text NOT NULL,
-- many more fields here
CREATE TABLE kap.course_user (
id int8 NOT NULL,
is_active bool NOT NULL DEFAULT true,
course_id int8 NOT NULL,
sec_user_id int8 NOT NULL,
CONSTRAINT pk_course_user PRIMARY KEY (id),
CONSTRAINT fk_course_user_course_id FOREIGN KEY (course_id) REFERENCES kap.course(id) DEFERRABLE,
CONSTRAINT fk_course_user_sec_user_id FOREIGN KEY (sec_user_id) REFERENCES sys.sec_user(id) DEFERRABLE
CREATE TABLE wf.workflow (
id int8 NOT NULL,
wf_type_id int8 NOT NULL,
head_table_id int8 NOT NULL,
table_info_id int8 NOT NULL,
rec_id int8 NOT NULL,
station_id int8 NOT NULL,
We had a support request today, and we have narrowed down the problem to a query that behaves very strangely. The actual query was much more complicated, but I came up with this minimal example.
This is what we have seen inside our application:
select * from test where id in (26643094740, 26437091668);
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|
26643094740|PEG | 2905| 1|
select * from test where id = 26643094740;
-----------+----+-----+-----+
26643094740|PEG | 2905| 0|
The problem: value of col_3 changes for id=26643094740 if I query two rows vs. one row. This is without changing any data. The problem is 100% repeatable, if I query two rows from the same view, then I get different data for one of the rows.
I suspect that this is a bug. But I might be wrong. Please help me!
The actual test view looks like this:
create view test as
SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id = 25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;
SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id = 25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;
Below are some DDL s (simplified, the actual tables contain much more fields).
Do you think that this might be a bug? If not, then can somebody please explain how this can happen?
Laszlo
explain analyze select * from test where id in (26643094740, 26437091668);
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107 rows=2 loops=1) |
Hash Cond: (tt.id = c.training_type_id) |
-> Seq Scan on training_type tt (cost=0.00..12.71 rows=71 width=13) (actual time=0.004..0.033 rows=71 loops=1) |
-> Hash (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025 rows=2 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Bitmap Heap Scan on course c (cost=8.58..16.05 rows=2 width=24) (actual time=0.018..0.021 rows=2 loops=1) |
Recheck Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
Heap Blocks: exact=2 |
-> Bitmap Index Scan on pk_course (cost=0.00..8.58 rows=2 width=0) (actual time=0.012..0.012 rows=2 loops=1) |
Index Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=2) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.010..0.012 rows=1 loops=2) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1 loops=2) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2) |
Cache Key: w_1.rec_id |
Hits: 1 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Planning Time: 0.527 ms |
Execution Time: 0.175 ms |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107 rows=2 loops=1) |
Hash Cond: (tt.id = c.training_type_id) |
-> Seq Scan on training_type tt (cost=0.00..12.71 rows=71 width=13) (actual time=0.004..0.033 rows=71 loops=1) |
-> Hash (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025 rows=2 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Bitmap Heap Scan on course c (cost=8.58..16.05 rows=2 width=24) (actual time=0.018..0.021 rows=2 loops=1) |
Recheck Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
Heap Blocks: exact=2 |
-> Bitmap Index Scan on pk_course (cost=0.00..8.58 rows=2 width=0) (actual time=0.012..0.012 rows=2 loops=1) |
Index Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=2) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.010..0.012 rows=1 loops=2) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1 loops=2) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2) |
Cache Key: w_1.rec_id |
Hits: 1 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Planning Time: 0.527 ms |
Execution Time: 0.175 ms |
explain analyze select * from test where id in (26643094740)
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=0.42..64.60 rows=1 width=29) (actual time=0.033..0.035 rows=1 loops=1) |
-> Index Scan using pk_course on course c (cost=0.28..8.30 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=1) |
Index Cond: (id = '26643094740'::bigint) |
-> Index Scan using pk_training_type on training_type tt (cost=0.14..8.16 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1) |
Index Cond: (id = c.training_type_id) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.007..0.009 rows=1 loops=1) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1) |
Cache Key: w_1.rec_id |
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Rows Removed by Filter: 1 |
Planning Time: 0.528 ms |
Execution Time: 0.096 ms |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=0.42..64.60 rows=1 width=29) (actual time=0.033..0.035 rows=1 loops=1) |
-> Index Scan using pk_course on course c (cost=0.28..8.30 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=1) |
Index Cond: (id = '26643094740'::bigint) |
-> Index Scan using pk_training_type on training_type tt (cost=0.14..8.16 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1) |
Index Cond: (id = c.training_type_id) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.007..0.009 rows=1 loops=1) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1) |
Cache Key: w_1.rec_id |
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Rows Removed by Filter: 1 |
Planning Time: 0.528 ms |
Execution Time: 0.096 ms |
CREATE TABLE kap.course (
id int8 NOT NULL,
training_type_id int8 NOT NULL,
regno int8 NOT NULL,
id int8 NOT NULL,
training_type_id int8 NOT NULL,
regno int8 NOT NULL,
-- many more fields here...
CONSTRAINT pk_course PRIMARY KEY (id),
CONSTRAINT fk_course_training_type_id FOREIGN KEY (training_type_id) REFERENCES kap.training_type(id) DEFERRABLE
CONSTRAINT pk_course PRIMARY KEY (id),
CONSTRAINT fk_course_training_type_id FOREIGN KEY (training_type_id) REFERENCES kap.training_type(id) DEFERRABLE
-- many more constraints here...
);
);
CREATE TABLE kap.training_type (
id int8 NOT NULL,
code text NOT NULL,
-- many more fields here
CONSTRAINT pk_training_type PRIMARY KEY (id)
-- many more constraints here
);
CREATE UNIQUE INDEX training_type_uidx_code ON kap.training_type USING btree (code);
);
CREATE UNIQUE INDEX training_type_uidx_code ON kap.training_type USING btree (code);
CREATE TABLE kap.course_user (
id int8 NOT NULL,
is_active bool NOT NULL DEFAULT true,
course_id int8 NOT NULL,
sec_user_id int8 NOT NULL,
CONSTRAINT pk_course_user PRIMARY KEY (id),
CONSTRAINT fk_course_user_course_id FOREIGN KEY (course_id) REFERENCES kap.course(id) DEFERRABLE,
CONSTRAINT fk_course_user_sec_user_id FOREIGN KEY (sec_user_id) REFERENCES sys.sec_user(id) DEFERRABLE
-- many more constraints here...
);
CREATE UNIQUE INDEX course_user_uidx ON kap.course_user USING btree (course_id, sec_user_id);
);
CREATE UNIQUE INDEX course_user_uidx ON kap.course_user USING btree (course_id, sec_user_id);
CREATE TABLE wf.workflow (
id int8 NOT NULL,
wf_type_id int8 NOT NULL,
head_table_id int8 NOT NULL,
table_info_id int8 NOT NULL,
rec_id int8 NOT NULL,
station_id int8 NOT NULL,
-- many more fields here...
CONSTRAINT pk_workflow PRIMARY KEY (id),
CONSTRAINT fk_workflow_station_id FOREIGN KEY (station_id) REFERENCES wf.station(id) DEFERRABLE,
CONSTRAINT fk_workflow_table_info_id FOREIGN KEY (table_info_id) REFERENCES meta.table_info(id) DEFERRABLE,
CONSTRAINT fk_workflow_wf_type_id FOREIGN KEY (wf_type_id) REFERENCES wf.wf_type(id) DEFERRABLE
CONSTRAINT pk_workflow PRIMARY KEY (id),
CONSTRAINT fk_workflow_station_id FOREIGN KEY (station_id) REFERENCES wf.station(id) DEFERRABLE,
CONSTRAINT fk_workflow_table_info_id FOREIGN KEY (table_info_id) REFERENCES meta.table_info(id) DEFERRABLE,
CONSTRAINT fk_workflow_wf_type_id FOREIGN KEY (wf_type_id) REFERENCES wf.wf_type(id) DEFERRABLE
-- many more constraints here
);
CREATE UNIQUE INDEX uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
CREATE INDEX workflow_idx_station ON wf.workflow USING btree (station_id);
CREATE UNIQUE INDEX workflow_uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
);
CREATE UNIQUE INDEX uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
CREATE INDEX workflow_idx_station ON wf.workflow USING btree (station_id);
CREATE UNIQUE INDEX workflow_uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
On 10/21/22 10:50 AM, Les wrote: > Hello, > > We had a support request today, and we have narrowed down the problem to > a query that behaves very strangely. The actual query was much more > complicated, but I came up with this minimal example. > > This is what we have seen inside our application: > > select * from test where id in (26643094740, 26437091668); > > id |code|regno|col_3| > -----------+----+-----+-----+ > 26437091668|TA-T| 2632| 1| > 26643094740|PEG | 2905| 1| > > select * from test where id = 26643094740; > > id |code|regno|col_3| > -----------+----+-----+-----+ > 26643094740|PEG | 2905| 0| > > The problem: value of col_3 changes for id=26643094740 if I query two > rows vs. one row. This is without changing any data. The problem is 100% > repeatable, if I query two rows from the same view, then I get different > data for one of the rows. > > I suspect that this is a bug. But I might be wrong. Please help me! I suspect an index problem. Have you tried reindexing the source table, kap.course if I am following correctly. Have there been any issues with the database lately, e.g. crash or other significant event? > > The actual test view looks like this: > -- Adrian Klaver adrian.klaver@aklaver.com
One of my colleagues pointed out, that they query returns a different result, if I cast the head_table_id condition to float8 and then back to int8.
SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id::float8::int8 = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id = 25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id
where c.id in (26437091668, 26643094740)
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id::float8::int8 = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id = 25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id
where c.id in (26437091668, 26643094740)
Returns:
id |code|regno|col_3|
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|
26643094740|PEG | 2905| 0|
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|
26643094740|PEG | 2905| 0|
Although all identifier columns are defined as int8.
Les <nagylzs@gmail.com> ezt írta (időpont: 2022. okt. 21., P, 19:50):
Hello,We had a support request today, and we have narrowed down the problem to a query that behaves very strangely. The actual query was much more complicated, but I came up with this minimal example.This is what we have seen inside our application:select * from test where id in (26643094740, 26437091668);id |code|regno|col_3|
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|26643094740|PEG | 2905| 1|select * from test where id = 26643094740;id |code|regno|col_3|
-----------+----+-----+-----+26643094740|PEG | 2905| 0|The problem: value of col_3 changes for id=26643094740 if I query two rows vs. one row. This is without changing any data. The problem is 100% repeatable, if I query two rows from the same view, then I get different data for one of the rows.I suspect that this is a bug. But I might be wrong. Please help me!The actual test view looks like this:create view test as
SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id = 25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;Below are some DDL s (simplified, the actual tables contain much more fields).Do you think that this might be a bug? If not, then can somebody please explain how this can happen?Laszloexplain analyze select * from test where id in (26643094740, 26437091668);QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107 rows=2 loops=1) |
Hash Cond: (tt.id = c.training_type_id) |
-> Seq Scan on training_type tt (cost=0.00..12.71 rows=71 width=13) (actual time=0.004..0.033 rows=71 loops=1) |
-> Hash (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025 rows=2 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Bitmap Heap Scan on course c (cost=8.58..16.05 rows=2 width=24) (actual time=0.018..0.021 rows=2 loops=1) |
Recheck Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
Heap Blocks: exact=2 |
-> Bitmap Index Scan on pk_course (cost=0.00..8.58 rows=2 width=0) (actual time=0.012..0.012 rows=2 loops=1) |
Index Cond: (id = ANY ('{26643094740,26437091668}'::bigint[])) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=2) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.010..0.012 rows=1 loops=2) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1 loops=2) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2) |
Cache Key: w_1.rec_id |
Hits: 1 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Planning Time: 0.527 ms |
Execution Time: 0.175 ms |explain analyze select * from test where id in (26643094740)QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=0.42..64.60 rows=1 width=29) (actual time=0.033..0.035 rows=1 loops=1) |
-> Index Scan using pk_course on course c (cost=0.28..8.30 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=1) |
Index Cond: (id = '26643094740'::bigint) |
-> Index Scan using pk_training_type on training_type tt (cost=0.14..8.16 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1) |
Index Cond: (id = c.training_type_id) |
SubPlan 1 |
-> Aggregate (cost=47.91..47.92 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1) |
-> Nested Loop (cost=0.59..47.90 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1) |
-> Index Scan using workflow_idx_station on workflow w_1 (cost=0.29..22.90 rows=3 width=8) (actual time=0.007..0.009 rows=1 loops=1) |
Index Cond: (station_id = '25406740434'::bigint) |
Filter: (head_table_id = '25408438504'::bigint) |
-> Memoize (cost=0.30..8.32 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1) |
Cache Key: w_1.rec_id |
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
-> Index Scan using pk_course_user on course_user cu (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)|
Index Cond: (id = w_1.rec_id) |
Filter: (is_active AND (course_id = c.id)) |
Rows Removed by Filter: 1 |
Planning Time: 0.528 ms |
Execution Time: 0.096 ms |CREATE TABLE kap.course (
id int8 NOT NULL,
training_type_id int8 NOT NULL,
regno int8 NOT NULL,-- many more fields here...
CONSTRAINT pk_course PRIMARY KEY (id),
CONSTRAINT fk_course_training_type_id FOREIGN KEY (training_type_id) REFERENCES kap.training_type(id) DEFERRABLE-- many more constraints here...
);
CREATE TABLE kap.training_type (
id int8 NOT NULL,
code text NOT NULL,
-- many more fields hereCONSTRAINT pk_training_type PRIMARY KEY (id)-- many more constraints here
);
CREATE UNIQUE INDEX training_type_uidx_code ON kap.training_type USING btree (code);
CREATE TABLE kap.course_user (
id int8 NOT NULL,
is_active bool NOT NULL DEFAULT true,
course_id int8 NOT NULL,
sec_user_id int8 NOT NULL,
CONSTRAINT pk_course_user PRIMARY KEY (id),
CONSTRAINT fk_course_user_course_id FOREIGN KEY (course_id) REFERENCES kap.course(id) DEFERRABLE,
CONSTRAINT fk_course_user_sec_user_id FOREIGN KEY (sec_user_id) REFERENCES sys.sec_user(id) DEFERRABLE-- many more constraints here...
);
CREATE UNIQUE INDEX course_user_uidx ON kap.course_user USING btree (course_id, sec_user_id);
CREATE TABLE wf.workflow (
id int8 NOT NULL,
wf_type_id int8 NOT NULL,
head_table_id int8 NOT NULL,
table_info_id int8 NOT NULL,
rec_id int8 NOT NULL,
station_id int8 NOT NULL,-- many more fields here...
CONSTRAINT pk_workflow PRIMARY KEY (id),
CONSTRAINT fk_workflow_station_id FOREIGN KEY (station_id) REFERENCES wf.station(id) DEFERRABLE,
CONSTRAINT fk_workflow_table_info_id FOREIGN KEY (table_info_id) REFERENCES meta.table_info(id) DEFERRABLE,
CONSTRAINT fk_workflow_wf_type_id FOREIGN KEY (wf_type_id) REFERENCES wf.wf_type(id) DEFERRABLE-- many more constraints here
);
CREATE UNIQUE INDEX uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
CREATE INDEX workflow_idx_station ON wf.workflow USING btree (station_id);
CREATE UNIQUE INDEX workflow_uidx_ht ON wf.workflow USING btree (head_table_id, rec_id);
Not that I know of.
I just tried this:
reindex table kap.course;
reindex table kap.course_user;
reindex table wf.workflow;
reindex table kap.training_type;
reindex table kap.course_user;
reindex table wf.workflow;
reindex table kap.training_type;
But it is still wrong.
Adrian Klaver <adrian.klaver@aklaver.com> ezt írta (időpont: 2022. okt. 21., P, 19:57):
On 10/21/22 10:50 AM, Les wrote:
> Hello,
>
> We had a support request today, and we have narrowed down the problem to
> a query that behaves very strangely. The actual query was much more
> complicated, but I came up with this minimal example.
>
> This is what we have seen inside our application:
>
> select * from test where id in (26643094740, 26437091668);
>
> id |code|regno|col_3|
> -----------+----+-----+-----+
> 26437091668|TA-T| 2632| 1|
> 26643094740|PEG | 2905| 1|
>
> select * from test where id = 26643094740;
>
> id |code|regno|col_3|
> -----------+----+-----+-----+
> 26643094740|PEG | 2905| 0|
>
> The problem: value of col_3 changes for id=26643094740 if I query two
> rows vs. one row. This is without changing any data. The problem is 100%
> repeatable, if I query two rows from the same view, then I get different
> data for one of the rows.
>
> I suspect that this is a bug. But I might be wrong. Please help me!
I suspect an index problem. Have you tried reindexing the source table,
kap.course if I am following correctly.
Have there been any issues with the database lately, e.g. crash or other
significant event?
>
> The actual test view looks like this:
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/21/22 10:57 AM, Les wrote: > One of my colleagues pointed out, that they query returns a different > result, if I cast the head_table_id condition to float8 and then back to > int8. > > SELECT > c.id <http://c.id>, > tt.code, > c.regno, > ( > select count(*) > FROM kap.course_user cu > JOIN wf.workflow w_1 ON w_1.rec_id = cu.id <http://cu.id> AND > w_1.head_table_id::float8::int8 = 25408438504 > where cu.is_active AND cu.course_id = c.id <http://c.id> AND > w_1.station_id = 25406740434 > ) AS col_3 > FROM > kap.course c > INNER JOIN kap.training_type tt ON tt.id <http://tt.id> = > c.training_type_id > where c.id <http://c.id> in (26437091668, 26643094740) > > Returns: > > id |code|regno|col_3| > -----------+----+-----+-----+ > 26437091668|TA-T| 2632| 1| > 26643094740|PEG | 2905| 0| > > Although all identifier columns are defined as int8. So what happens if query the table directly?: select * from wf.workflow where head_table_id::float8::int8 = 25408438504; vs select * from wf.workflow where head_table_id = 25408438504; FYI, the convention on the list is to not top post, but instead to bottom or inline post. Also to trim out material which was covered in previous posts. -- Adrian Klaver adrian.klaver@aklaver.com
So what happens if query the table directly?:
select * from wf.workflow where head_table_id::float8::int8 = 25408438504;
vs
select * from wf.workflow where head_table_id = 25408438504;
Both return lots of rows. The same number of rows.
count|
-----+
62260|
select count(*) from wf.workflow where head_table_id = 25408438504;
count|-----+
62260|
Both of them use seq scan.
QUERY PLAN |
-------------------------------------------------------------------+
Aggregate (cost=2985.00..2985.01 rows=1 width=8) |
-> Seq Scan on workflow (cost=0.00..2829.07 rows=62369 width=0)|
Filter: (head_table_id = '25408438504'::bigint) |
-------------------------------------------------------------------+
Aggregate (cost=2985.00..2985.01 rows=1 width=8) |
-> Seq Scan on workflow (cost=0.00..2829.07 rows=62369 width=0)|
Filter: (head_table_id = '25408438504'::bigint) |
QUERY PLAN |
-------------------------------------------------------------------------------------+
Aggregate (cost=3289.86..3289.87 rows=1 width=8) |
-> Seq Scan on workflow (cost=0.00..3288.70 rows=460 width=0) |
Filter: (((head_table_id)::double precision)::bigint = '25408438504'::bigint)|
-------------------------------------------------------------------------------------+
Aggregate (cost=3289.86..3289.87 rows=1 width=8) |
-> Seq Scan on workflow (cost=0.00..3288.70 rows=460 width=0) |
Filter: (((head_table_id)::double precision)::bigint = '25408438504'::bigint)|
Les <nagylzs@gmail.com> writes: > We had a support request today, and we have narrowed down the problem to a > query that behaves very strangely. The actual query was much more > complicated, but I came up with this minimal example. Which PG version is this exactly? Given the Memoize node shown in your plan, I suppose 14.something, but is it up to date? There were Memoize-related bug fixes in 14.2 and 14.4, and the one in 14.2 looks particularly likely to be relevant. If you are on the current minor release, does "set enable_memoize = off" change the behavior? regards, tom lane
Which PG version is this exactly? Given the Memoize node shown
in your plan, I suppose 14.something, but is it up to date?
There were Memoize-related bug fixes in 14.2 and 14.4, and the
one in 14.2 looks particularly likely to be relevant.
If you are on the current minor release, does "set enable_memoize = off"
change the behavior?
As always, you hit the nail on the head. set enable_memoize = on fixes the problem!
Version is PostgreSQL 14.1, time to upgrade...
I'm sorry that I wasted your time.
Les <nagylzs@gmail.com> writes: > As always, you hit the nail on the head. set enable_memoize = on fixes the > problem! > Version is PostgreSQL 14.1, time to upgrade... Yup, very likely fixed by c2dc7b9e1 then. > I'm sorry that I wasted your time. No need to apologize, it was an actual bug. regards, tom lane
on a diff note, is the word memoize inspired from Perl Module memoize which use to
do the same thing.
On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna <s_ravikrishna@aol.com> wrote:
on a diff note, is the word memoize inspired from Perl Module memoize which use todo the same thing.
It is a general functional programming concept - not sure on the history but probably academic and thus Perl and others picked it up "from the source".
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna <s_ravikrishna@aol.com> wrote: >> on a diff note, is the word memoize inspired from Perl Module memoize >> which use to do the same thing. > It is a general functional programming concept - not sure on the history > but probably academic and thus Perl and others picked it up "from the > source". Looks to me like you suggested our use of the terminology: https://www.postgresql.org/message-id/flat/CAKFQuwZQmCNyS_Vv2Jf3TNe7wRTiptWNs7xkgU%3DAEdqthkQe9A%40mail.gmail.com#bbcd739c97e28b17ef2e111be8cf214b regards, tom lane
On Fri, Oct 21, 2022 at 6:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna <s_ravikrishna@aol.com> wrote:
>> on a diff note, is the word memoize inspired from Perl Module memoize
>> which use to do the same thing.
> It is a general functional programming concept - not sure on the history
> but probably academic and thus Perl and others picked it up "from the
> source".
Looks to me like you suggested our use of the terminology:
https://www.postgresql.org/message-id/flat/CAKFQuwZQmCNyS_Vv2Jf3TNe7wRTiptWNs7xkgU%3DAEdqthkQe9A%40mail.gmail.com#bbcd739c97e28b17ef2e111be8cf214b
Yeah, I just don't remember which book on functional programming (probably JavaScript oriented though) I learned about it in.
David J.
On 2022-Oct-21, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna <s_ravikrishna@aol.com> wrote: > >> on a diff note, is the word memoize inspired from Perl Module memoize > >> which use to do the same thing. > > > It is a general functional programming concept - not sure on the history > > but probably academic and thus Perl and others picked it up "from the > > source". > > Looks to me like you suggested our use of the terminology: > > https://www.postgresql.org/message-id/flat/CAKFQuwZQmCNyS_Vv2Jf3TNe7wRTiptWNs7xkgU%3DAEdqthkQe9A%40mail.gmail.com#bbcd739c97e28b17ef2e111be8cf214b The word itself has been used in the PG lists before. For example, Greg Stark used it in 2008 when discussing WITH RECURSIVE https://postgr.es/m/47C151F1.8050100@enterprisedb.com There are a few other hits over the years. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Digital and video cameras have this adjustment and film cameras don't for the same reason dogs and cats lick themselves: because they can." (Ken Rockwell)