Query started showing wrong result after Ctrl+c
Hi,
Steps to reproduce -
\\ PG HEAD / PG v10 sources . Connect to psql terminal - create these
following object
create table tv(n int,n1 char(100));
insert into tv values (generate_series(1,1000000),'aaa');
insert into tv values (generate_series(10000,1000000),'a');
analyze tv;
vacuum tv;
\\1st query
postgres=# SELECT * FROM ( SELECT n from tv where n= (select *
from (select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
\\2nd query
postgres=# SELECT * FROM ( SELECT n from tv where n!=ALL (select *
from (select n from tv) c)) as c ; [query was taking time so pressed
CTRL-C)
^C2017-10-12 10:54:49.004 BST [9073] ERROR: canceling statement due to
user request
2017-10-12 10:54:49.004 BST [9073] STATEMENT: SELECT * FROM ( SELECT
n from tv where n!=ALL (select * from (select n from tv) c)) as c ;
2017-10-12 10:54:49.004 BST [9129] FATAL: terminating connection due to
administrator command
2017-10-12 10:54:49.004 BST [9129] STATEMENT: SELECT * FROM ( SELECT
n from tv where n!=ALL (select * from (select n from tv) c)) as c ;
2017-10-12 10:54:49.004 BST [9130] FATAL: terminating connection due to
administrator command
2017-10-12 10:54:49.004 BST [9130] STATEMENT: SELECT * FROM ( SELECT
n from tv where n!=ALL (select * from (select n from tv) c)) as c ;
Cancel request sent
2017-10-12 10:54:49.005 BST [9058] LOG: background worker "parallel
worker" (PID 9129) exited with exit code 1
2017-10-12 10:54:49.005 BST [9058] LOG: background worker "parallel
worker" (PID 9130) exited with exit code 1
ERROR: canceling statement due to user request
\\again fired 1st query
postgres=# vacuum ANALYZE tv;
VACUUM
postgres=# SELECT * FROM ( SELECT n from tv where n= (select *
from (select n from tv limit 1) c)) as c ;
n
------
3713
(1 row)
This time , query is started showing wrong result. Is this an expected
behavior and if yes -then how to get the correct result ?
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Oct 12, 2017 at 12:03 PM, tushar <tushar.ahuja@enterprisedb.com>
wrote:
postgres=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
------
3713
(1 row)This time , query is started showing wrong result. Is this an expected
behavior and if yes -then how to get the correct result ?
The subquery:
select n from tv limit 1
could in theory return any row due to the lack of ORDER BY. What I'm
guessing happened is that you're seeing a synchronized sequential scan in
follow-up queries. Add an ORDER BY.
.m
On 10/12/2017 03:46 PM, Marko Tiikkaja wrote:
The subquery:
select n from tv limit 1
could in theory return any row due to the lack of ORDER BY. What I'm
guessing happened is that you're seeing a synchronized sequential scan
in follow-up queries. Add an ORDER BY.
Bang on . After adding order by clause - i am getting same result
consistently. but why i got the different result after canceling the
query only?
test=# \c f2
You are now connected to database "f2" as user "centos".
f2=# create table tv(n int,n1 char(100));
CREATE TABLE
f2=# insert into tv values (generate_series(1,1000000),'aaa');
INSERT 0 1000000
f2=# insert into tv values (generate_series(10000,1000000),'a');
INSERT 0 990001
f2=# analyze tv;
ANALYZE
f2=# vacuum tv;
VACUUM
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=#
even after restarting the server , i am getting the same result.
now after canceling the operation , next time - result is coming different ?
f2=# SELECT * FROM ( SELECT n from tv where n!=ALL (select * from
(select n from tv) c)) as c ;
^CCancel request sent
ERROR: canceling statement due to user request
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
------
3713
(1 row)
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
tushar <tushar.ahuja@enterprisedb.com> writes:
On 10/12/2017 03:46 PM, Marko Tiikkaja wrote:
The subquery:
select n from tv limit 1
could in theory return any row due to the lack of ORDER BY. What I'm
guessing happened is that you're seeing a synchronized sequential scan
in follow-up queries. Add an ORDER BY.
Bang on . After adding order by clause - i am getting same result
consistently. but why i got the different result after canceling the
query only?
If you let the query run to completion, the syncscan start pointer will
return to the start of the table. Cancelling it partway through allows
the syncscan pointer to be left pointing somewhere in the middle of the
table.
If you turn off synchronize_seqscans, you should find that you always
get the physically-first table row from that subselect.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers