Re: SELECT FOR UPDATE returns zero rows

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