Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT FOR UPDATE returns zero rows

SELECT FOR UPDATE returns zero rows

From
Dima Pavlov
Date:
Why SELECT FOR UPDATE returns 0 rows in scenario below? But if I just execute sql query from second transaction it always returns 1 row.

TRANSACTION 1:
--------------------------

BEGIN;
UPDATE t1 SET t1c3 = "string_value_1" WHERE t1c1 = 123456789;

-- Query returned successfully: one row affected, 51 msec execution time.


TRANSACTION 2:
--------------------------

WITH 
    cte1 AS (
        SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
    ),

    cte2 AS (
        SELECT * FROM t1
        WHERE 
             t1c1 = 123456789 
            AND t1c2 = (SELECT t2c2 FROM cte1)
        FOR UPDATE
    ) 

SELECT * FROM cte2

-- Waiting


TRANSACTION 1:
--------------------------

COMMIT;

-- Query returned successfully with no result in 41 msec.


TRANSACTION 2:
--------------------------

-- Returned 0 rows

:

From
"Levenson, Elliott I."
Date:
I have had no luck connecting to a database connecting to an Amazon EC2 database on Heroku.

No matter how I change the pg_hba.conf file it rejects my IP.

I've been looking it up online but none of the suggestions work.

Thanks
Elliott

Re: SELECT FOR UPDATE returns zero rows

From
amul sul
Date:
What is output of following queries:

1. SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
2. SELECT * FROM t1 WHERE t1c1 = 123456789 AND t1c2 IN (SELECT t2c2
FROM t2 WHERE t2c1 = "string_value_2")

Regards,
Amul

Re: :

From
amul sul
Date:
I hope you have tried  listen_addresses = '*' postgresql.conf setting too.

Regards,
Amul

On Sun, Sep 18, 2016 at 3:58 AM, Levenson, Elliott I. <eleven@pitt.edu> wrote:
I have had no luck connecting to a database connecting to an Amazon EC2 database on Heroku.

No matter how I change the pg_hba.conf file it rejects my IP.

I've been looking it up online but none of the suggestions work.

Thanks
Elliott


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: :

From
"Amit S."
Date:
Can you check the log files to of the os as well as postgres to see whether the request is reaching at all. 

Also, if this is just a test system, you can try turning off the firewall.

On Sun, Sep 18, 2016 at 11:17 AM, amul sul <sulamul@gmail.com> wrote:
I hope you have tried  listen_addresses = '*' postgresql.conf setting too.

Regards,
Amul

On Sun, Sep 18, 2016 at 3:58 AM, Levenson, Elliott I. <eleven@pitt.edu> wrote:
I have had no luck connecting to a database connecting to an Amazon EC2 database on Heroku.

No matter how I change the pg_hba.conf file it rejects my IP.

I've been looking it up online but none of the suggestions work.

Thanks
Elliott


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice




--

Amit Sharma

Re: SELECT FOR UPDATE returns zero rows

From
Dima Pavlov
Date:
1. 1 row. 
    t2c2: 100


2. 1 row. 
_pk:1
t1c1: 123456789
t1c2: 100
t1c3: "string_value_1"

Example:

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);

On Sun, Sep 18, 2016 at 9:41 AM, amul sul <sulamul@gmail.com> wrote:
What is output of following queries:

1. SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
2. SELECT * FROM t1 WHERE t1c1 = 123456789 AND t1c2 IN (SELECT t2c2
FROM t2 WHERE t2c1 = "string_value_2")

Regards,
Amul

Re: SELECT FOR UPDATE returns zero rows

From
amul sul
Date:
On my environment it working fine, see below:

postgres=# WITH 
    cte1 AS (
        SELECT t2c2 FROM t2 WHERE t2c1 = 'string_value_2'
    ),
    cte2 AS (
        SELECT * FROM t1
        WHERE 
             t1c1 = 123456789 
            AND t1c2 = (SELECT t2c2 FROM cte1)
        FOR UPDATE
    ) 
SELECT * FROM cte2;

 _pk |   t1c1    | t1c2 |      t1c3      
-----+-----------+------+----------------
   1 | 123456789 |  100 | string_value_1
(1 row)


Are you sure, user executing these SQLs have appropriate permissions on respective table? Is there any error or warnings (check server log too)?


Regards,
Amul

On Sun, Sep 18, 2016 at 7:20 PM, Dima Pavlov <imyfess@gmail.com> wrote:
1. 1 row. 
    t2c2: 100


2. 1 row. 
_pk:1
t1c1: 123456789
t1c2: 100
t1c3: "string_value_1"

Example:

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);

On Sun, Sep 18, 2016 at 9:41 AM, amul sul <sulamul@gmail.com> wrote:
What is output of following queries:

1. SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
2. SELECT * FROM t1 WHERE t1c1 = 123456789 AND t1c2 IN (SELECT t2c2
FROM t2 WHERE t2c1 = "string_value_2")

Regards,
Amul


Re: SELECT FOR UPDATE returns zero rows

From
Dima Pavlov
Date:
Did u perform all 4 steps from my scenarion with 2 parallel transactions? If i didn't have enough permissions then i guess I would not get right result when execute sql query from the second transaction separately.

On Mon, Sep 19, 2016 at 10:09 AM, amul sul <sulamul@gmail.com> wrote:
On my environment it working fine, see below:

postgres=# WITH 
    cte1 AS (
        SELECT t2c2 FROM t2 WHERE t2c1 = 'string_value_2'
    ),
    cte2 AS (
        SELECT * FROM t1
        WHERE 
             t1c1 = 123456789 
            AND t1c2 = (SELECT t2c2 FROM cte1)
        FOR UPDATE
    ) 
SELECT * FROM cte2;

 _pk |   t1c1    | t1c2 |      t1c3      
-----+-----------+------+----------------
   1 | 123456789 |  100 | string_value_1
(1 row)


Are you sure, user executing these SQLs have appropriate permissions on respective table? Is there any error or warnings (check server log too)?


Regards,
Amul

On Sun, Sep 18, 2016 at 7:20 PM, Dima Pavlov <imyfess@gmail.com> wrote:
1. 1 row. 
    t2c2: 100


2. 1 row. 
_pk:1
t1c1: 123456789
t1c2: 100
t1c3: "string_value_1"

Example:

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);

On Sun, Sep 18, 2016 at 9:41 AM, amul sul <sulamul@gmail.com> wrote:
What is output of following queries:

1. SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
2. SELECT * FROM t1 WHERE t1c1 = 123456789 AND t1c2 IN (SELECT t2c2
FROM t2 WHERE t2c1 = "string_value_2")

Regards,
Amul



Re: SELECT FOR UPDATE returns zero rows

From
amul sul
Date:
On Mon, Sep 19, 2016 at 9:28 PM, Dima Pavlov <imyfess@gmail.com> wrote:
>
> Did u perform all 4 steps from my scenarion with 2 parallel transactions? If i didn't have enough permissions then i
guessI would not get right result when execute sql query from the second transaction separately. 
>
Ohh I see, understood.

If simple query executed instead of CTE works as expected, looks like
a bug in CTE code.

Lets wait for other responses.
In case of no response in couple of days, we might need to write to
-hackers about this discovery. Thanks.

Regards,
Amul.

Re: :

From
"Levenson, Elliott I."
Date:

Amul,


There was a space after my username.


Shame on me for six weeks!


Thanks,
Elliott

From: Amit S. <comeonamit@gmail.com>
Sent: Sunday, September 18, 2016 3:09 AM
To: amul sul
Cc: Levenson, Elliott I.; Dima Pavlov; pgsql-novice@postgresql.org
Subject: Re: : [NOVICE]
 
Can you check the log files to of the os as well as postgres to see whether the request is reaching at all. 

Also, if this is just a test system, you can try turning off the firewall.

On Sun, Sep 18, 2016 at 11:17 AM, amul sul <sulamul@gmail.com> wrote:
I hope you have tried  listen_addresses = '*' postgresql.conf setting too.

Regards,
Amul

On Sun, Sep 18, 2016 at 3:58 AM, Levenson, Elliott I. <eleven@pitt.edu> wrote:
I have had no luck connecting to a database connecting to an Amazon EC2 database on Heroku.

No matter how I change the pg_hba.conf file it rejects my IP.

I've been looking it up online but none of the suggestions work.

Thanks
Elliott


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice




--

Amit Sharma