Обсуждение: [HACKERS] 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 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