Query started showing wrong result after Ctrl+c

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Query started showing wrong result after Ctrl+c

tushar1
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Query started showing wrong result after Ctrl+c

Marko Tiikkaja-4
On Thu, Oct 12, 2017 at 12:03 PM, tushar <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Query started showing wrong result after Ctrl+c

tushar1
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Query started showing wrong result after Ctrl+c

Tom Lane-2
tushar <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Previous Thread Next Thread