Re: SELECT FOR UPDATE returns zero rows

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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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
>>>
>>
>>
>