SELECT FOR UPDATE returns zero rows with CTE

[Roman Guryanov]

Hello, could you check my problem.

Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution
in transaction)

If delete 'FOR UPDATE', 1 row returned
Test case:

DROP TABLE IF EXISTS t1;DROP TABLE IF EXISTS t2;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);

WITH
cte1 AS (
UPDATE t1SET t1c3 = 'string_value_1'WHERE t1c1 = 123456789
returning t1c1, t1c2
),
cte2 AS (
SELECT * FROM t1
WHERE
 t1c1 = 123456789
AND t1c2 = (SELECT t1c2 FROM cte1)
FOR UPDATE
)
SELECT * FROM cte2;

https://stackoverflow.com/questions/69217940/select-for-update-returns-zero-rows-with-cte

Re: SELECT FOR UPDATE returns zero rows with CTE

[Alban Hertroys]

> On 17 Sep 2021, at 8:32, Roman Guryanov  wrote:
> 
> Hello, could you check my problem.
> Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution in 
> transaction)
> 
> If delete 'FOR UPDATE', 1 row returned
> 
> Test case:
> DROP TABLE IF EXISTS
>  t1;
> 
> CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer
> , t1c3 text);
> 
> insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1'
> );

(…cut everything related to unused t2…)

> 
> WITH
> cte1 
> AS
>  (
> UPDATE
>  t1
> SET t1c3 = 'string_value_1'
> WHERE t1c1 = 123456789
> returning t1c1, t1c2
> ),
> cte2 
> AS
>  (
> SELECT * FROM
>  t1  
> WHERE t1c1 = 123456789  
> AND t1c2 = (SELECT t1c2 FROM cte1)
> FOR UPDATE
> )
> 
> SELECT * FROM cte2;


Most likely the outer select returns 0 rows because you locked the rows you 
expected in cte2 and didn’t perform an update on those locked rows yet.

I suspect your intention for this query is to first lock the rows, then update 
them and then select them, but instead you start with updating them, then lock 
those rows after the fact and then you try to select those locked rows.

Also, selecting the updated rows by t1c2 in cte2 seems rather risky, as that is 
a rather different selection criterium than you use for the actual update. It’s 
okay for this single-row example, but if you had a table full of data, you 
would now have locked all rows with the value t1c2 = 100 for update. If that 
update never happens (or the locking doesn’t get rolled back), well…

Regards,

Alban Hertroys
--
There is always an exception to always.

Re: The tragedy of SQL

[Raymond Brinzer]

On Wed, Sep 15, 2021 at 2:46 AM Julien Rouhaud  wrote:
> I agree, and actually sent a patch some time ago to allow usage of
> third-party parser(s).  They can coexist with the core one, meaning
> that you can (if you write your parser this way) use both languages,
> even in a multi-query string.  See
> https://commitfest.postgresql.org/34/3100/ for more details.

Bravo!  I look forward to reading the code when I have time; seems
like it will be a nice cheat sheet for the relevant internals,
particularly as you say that you commented thoroughly.

--
Ray Brinzer

Re: The tragedy of SQL

[Raymond Brinzer]

On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure  wrote:
> I've long thought that there is more algebraic type syntax sitting
> underneath SQL yearning to get out.

I wanted to come back to this, because I've been looking to take a
single problem (from my perspective) and explain it concisely.  Your
intuition is right on the mark.

Shell syntax is a pretty good lingua franca, so let's use it.  Say you
were working at the command line, and you said something like:

cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"

And the shell responded with something like:  ERROR: syntax error at
or near "sort".  After a little tinkering, you discover:  that's
because the grep has to come before the sort.  But why?

The database is not going to evaluate relational operations in order,
passing the output of one into the next as a shell pipe does.
Nevertheless, they are logically independent.  Each should take in a
relation and either a second relation or a predicate, and return a
relation.  Or, to put it mathily, relations are closed under
relational operations.  So:

Operation 1 | Operation 2
and
Operation 2 | Operation 1

should both be valid, whether or not they're semantically equivalent
(though they often are).  The operations are inherently atomic, and
can be understood in isolation.  That's not the case here:

SELECT col_3, col_1, col_5 FROM sometable WHERE col_3 LIKE 'Robert%'
ORDER BY col_3, col_1, col_5;

Now, if this sort of thing suits the way you think, I say, "Great!"
I'm glad you have a language which suits you.  For me, it's too rigid;
it assumes too much about what I might want to say.  I wouldn't
program in a language like this, or use a shell like this.  I don't
want to write database queries like this.  I do, because it's how I
get to talk to the awesome toy in the background, but it always
chafes.

-- 
Ray Brinzer

Re: The tragedy of SQL

[Raymond Brinzer]

On Fri, Sep 17, 2021 at 7:49 AM Raymond Brinzer  wrote:
> Now, if this sort of thing suits the way you think, I say, "Great!"
> I'm glad you have a language which suits you.

Reading this over, I realized I should have been more clear:  I mean
"you" generally.  I liked your comment about algebraic syntax; just
giving my take on it, not attributing anything to you.

-- 
Ray Brinzer

Re: The tragedy of SQL

[Tom Browder]

On Fri, Sep 17, 2021 at 06:49 Raymond Brinzer  wrote:
On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure  wrote:
> > I've long thought that there is more algebraic type syntax sitting
> > underneath SQL yearning to get out.
...
> Now, if this sort of thing suits the way you think, I say, "Great!"
> I'm glad you have a language which suits you.  For me, it's too rigid;
> it assumes too much about what I might want to say.  I wouldn't
> program in a language like this, or use a shell like this.  I don't
> want to write database queries like this.  I do, because it's how I
> get to talk to the awesome toy in the background, but it always
> chafes.

This thread strikes home because I've long used my favorite language,
Raku (and Perl before that) as a powerful glue language to generate
code in several languages I was forced to use and maintain during my
working years including FORTRAN, C, C++, PostScript, and SQL. I still
generate a lot of PostScript, but Raku has made it *much* easier.

Most recently I've used Raku modules for both ORM and procedural
interfaces to PostgreSQL, but with Raku's powerful grammar capability
a dedicated user can write his own language interface if he wishes.
In addition, Raku has a native C and C++ interface to ease using
PostgreSQL compiled code when necessary.

Best regards,

-Tom

P.S. See  as a starting place for Raku.

jsonb Indexing

[ramikvl]

Hello,

I'm new to jsonb feature of postgres. I have PostgreSQL 13.2 running 
locally in docker.


I've been playing a little bit with jsonb indexes described 
https://www.postgresql.org/docs/13/datatype-json.html#JSON-INDEXING and 
I'm not able make the GIN index work on 'tags'.


I was wondering what I'm doing wrong. There are steps what I've tried:

CREATE TABLE api (
    jdoc jsonb
);

INSERT INTO api (jdoc)
    VALUES ('{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
    "enim",
    "aliquip",
    "qui"
    ]
}');

CREATE INDEX idxgintags ON api USING GIN ((jdoc->'tags'));

EXPLAIN ANALYZE SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 
'tags' ? 'qui';


And the result is

Seq Scan on api  (cost=0.00..1.02 rows=1 width=64) (actual 
time=0.019..0.021 rows=1 loops=1)

  Filter: ((jdoc -> 'tags'::text) ? 'qui'::text)

Planning Time: 0.115 ms

Execution Time: 0.047 ms

Do you know why Index Scan on idxgintag is not used?

Thank you,

Vlasta

Re: SELECT FOR UPDATE returns zero rows with CTE

[Tom Lane]

Alban Hertroys  writes:
>> On 17 Sep 2021, at 8:32, Roman Guryanov  
>> wrote:
>> Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution 
>> in transaction)

> Most likely the outer select returns 0 rows because you locked the rows you 
> expected in cte2 and didn’t perform an update on those locked rows yet.

I might be wrong (ENOCAFFEINE), but I think what is happening is that the
UPDATE updates the row and then the FOR UPDATE filter skips the row on the
grounds that the row is already-updated-by-self.  In an ordinary UPDATE,
there's a hard restriction not to update a row already updated in the same
command, to avoid possibly-infinite loops if the same row is visited more
than once due to join behavior or the like.  I think that we use the same
semantics in FOR UPDATE, and I'm pretty sure that the two WITH clauses
would be treated as all one command.

I'd have to say that overall this example is one of the worst bits of
SQL I've seen lately.  Aside from the issues Alban noted, the "t1c2 =
(SELECT t1c2 FROM cte1)" part will fail outright if cte1 returns more
than one row, because that's a scalar sub-select not a join.  And
there's a real question of which WITH clause acts first: yeah, cte2
can't *complete* without running cte1, but it might act partially,
including performing the other half of its WHERE.  If cte1 were
updating t1c1 then I think it'd be pretty close to undefined what
results you get.  What's the point of doing it like this, rather than
just having cte1 return all the columns needed?

regards, tom lane

Re: jsonb Indexing

[Julien Rouhaud]

Hi,

On Fri, Sep 17, 2021 at 9:55 PM  wrote:
>
> I was wondering what I'm doing wrong. There are steps what I've tried:
>
> CREATE TABLE api (
>  jdoc jsonb
> );
>
> INSERT INTO api (jdoc)
>  VALUES ('{
>  "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
>  "name": "Angela Barton",
>  "is_active": true,
>  "company": "Magnafone",
>  "address": "178 Howard Place, Gulf, Washington, 702",
>  "registered": "2009-11-07T08:53:22 +08:00",
>  "latitude": 19.793713,
>  "longitude": 86.513373,
>  "tags": [
>  "enim",
>  "aliquip",
>  "qui"
>  ]
> }');
>
> CREATE INDEX idxgintags ON api USING GIN ((jdoc->'tags'));
>
> EXPLAIN ANALYZE SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc ->
> 'tags' ? 'qui';
>
> And the result is
>
> Seq Scan on api  (cost=0.00..1.02 rows=1 width=64) (actual
> time=0.019..0.021 rows=1 loops=1)
>Filter: ((jdoc -> 'tags'::text) ? 'qui'::text)
>
> Planning Time: 0.115 ms
>
> Execution Time: 0.047 ms
>
> Do you know why Index Scan on idxgintag is not used?

Yes, because doing an index scan on a table containing a single row is
an order or magnitude less efficient than simply doing a sequential
scan.  You should try to simulate something close to your production
data to see something interesting.

Re: pg_upgrade problem as locale difference in data centers

[Tom Lane]

Yi Sun  writes:
> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
> where datname in ('postgres','template1','template0') and
> (datcollate<>'en_US.UTF-8' or datctype<>'en_US.UTF-8')

This seems like a pretty bad idea.  You might get away with it if you've
not added any user-defined indexes in any of those databases, but it's
definitely a case where if things go wrong you'll be told it's your
own fault.

The context of "moving to a new datacenter and also updating across four
Postgres versions" suggests strongly to me that you are also planning a
move to a new OS version, in which case you had better read

https://wiki.postgresql.org/wiki/Locale_data_changes

In short, it seems to me that you are at very great risk of ending
up with corrupt (incorrectly ordered) indexes on textual columns.
If you don't mind reindexing all of those after the update, you
could proceed with this plan.  Otherwise, pg_dump-and-restore might
be a safer idea.

regards, tom lane

Re: Fwd: autocommit for multi call store procedure

[Adrian Klaver]

On 9/16/21 6:32 PM, Trang Le wrote:

Hi Adrian,

I am processing this issue with Ninad.

Could you double check it?


I have no idea what the forwarded message was showing. Too many changes 
in code. As David suggested reduce this down to a simple test case that 
demonstrates your issue.




Regards,
Trang





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: The tragedy of SQL

[Gavin Flower]

On 17/09/21 23:49, Raymond Brinzer wrote:

On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure  wrote:

I've long thought that there is more algebraic type syntax sitting
underneath SQL yearning to get out.

I wanted to come back to this, because I've been looking to take a
single problem (from my perspective) and explain it concisely.  Your
intuition is right on the mark.

Shell syntax is a pretty good lingua franca, so let's use it.  Say you
were working at the command line, and you said something like:

cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"

And the shell responded with something like:  ERROR: syntax error at
or near "sort".  After a little tinkering, you discover:  that's
because the grep has to come before the sort.  But why?

The database is not going to evaluate relational operations in order,
passing the output of one into the next as a shell pipe does.
Nevertheless, they are logically independent.  Each should take in a
relation and either a second relation or a predicate, and return a
relation.  Or, to put it mathily, relations are closed under
relational operations.  So:

Operation 1 | Operation 2
and
Operation 2 | Operation 1

should both be valid, whether or not they're semantically equivalent
(though they often are).  The operations are inherently atomic, and
can be understood in isolation.


[...]

In Mathematics which way round you do things may be important. For 
numbers in the Real & Complex domains then this does not matter.  
However, in the Quaternions it does matter, here A * B is not always the 
same as B * A.  And amongst the Octonions it is even worse, as there the 
order in which you do things may lead to different results, so A * (B * 
C) is not necessarily the same as (A * B) * C.


Another example is rotating things in 3 dimensions.  Hold a book with 
its front facing you.  Rotate the book towards you so it is now flat, 
them rotate the book along the vertical access so it is now edge on.  
When you do the operations in the reverse order, then you get a 
different result! Yes, you can blame the Quaternions.


In PostgreSQL, if the operations are 'not idempotent' (relies on at 
least one function that has varying output for the same input 
parameters) then the order in which you do things could lead to 
different results.


For the optimizer to be effective then it must be allowed to do 
operations in the best order it sees fit -- this is documented. Just as 
you must not rely on the order in which results are returned, unless you 
explicitly have an ORDER BY -- as the system will extract results in the 
fastest way it knows, which may not necessarily be in the same order as 
the values where inserted. This would be true, even if you had a totally 
different query language.



Cheers,
Gavin

Re: The tragedy of SQL

[Benedict Holland]

I love how we would admonish sql but love lisp. There isn't a perfect
language. SQL is fine. C is damn good. C++ is impossible, Java is C++ but
simple, Python is a C wrapper. God help us if we settled on Fortran. We
would still have single core processors. Lisp at least allowed
multithreading but is very hard to debug.

But back to the issue at hand, SQL is hard. It falls into the trap that C++
did where it is very hard and time consuming to make good schemas and
organize data correctly but it is very easy to do it badly. Then we try and
fix bad design in places where the fixes don't belong like the view or God
forbid the controller. That leads to horrible code and even more bugs.

I make a career of fixing broken schemas and teaching good design. I am 15
years in and learn how people screw up designs every day. Sometimes they
are beyond repair and I end up creating new designs and migrate the data.
Who knew that you should hire experts to teach novices but experts are
expensive and most of the time the code is throwaway anyway.

I don't get why there are so many programming languages out there. C is
virtually perfect. Python is C with benefits. Everything else appears to
just be offshoots of Python or Lisp and no one uses Lisp as far as I can
tell. SQL isn't really a programming language. It is just a layer on top of
data.

On Fri, Sep 17, 2021, 3:44 PM Gavin Flower 
wrote:

> On 17/09/21 23:49, Raymond Brinzer wrote:
> > On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure 
> wrote:
> >> I've long thought that there is more algebraic type syntax sitting
> >> underneath SQL yearning to get out.
> > I wanted to come back to this, because I've been looking to take a
> > single problem (from my perspective) and explain it concisely.  Your
> > intuition is right on the mark.
> >
> > Shell syntax is a pretty good lingua franca, so let's use it.  Say you
> > were working at the command line, and you said something like:
> >
> > cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"
> >
> > And the shell responded with something like:  ERROR: syntax error at
> > or near "sort".  After a little tinkering, you discover:  that's
> > because the grep has to come before the sort.  But why?
> >
> > The database is not going to evaluate relational operations in order,
> > passing the output of one into the next as a shell pipe does.
> > Nevertheless, they are logically independent.  Each should take in a
> > relation and either a second relation or a predicate, and return a
> > relation.  Or, to put it mathily, relations are closed under
> > relational operations.  So:
> >
> > Operation 1 | Operation 2
> > and
> > Operation 2 | Operation 1
> >
> > should both be valid, whether or not they're semantically equivalent
> > (though they often are).  The operations are inherently atomic, and
> > can be understood in isolation.
>
> [...]
>
> In Mathematics which way round you do things may be important. For
> numbers in the Real & Complex domains then this does not matter.
> However, in the Quaternions it does matter, here A * B is not always the
> same as B * A.  And amongst the Octonions it is even worse, as there the
> order in which you do things may lead to different results, so A * (B *
> C) is not necessarily the same as (A * B) * C.
>
> Another example is rotating things in 3 dimensions.  Hold a book with
> its front facing you.  Rotate the book towards you so it is now flat,
> them rotate the book along the vertical access so it is now edge on.
> When you do the operations in the reverse order, then you get a
> different result! Yes, you can blame the Quaternions.
>
> In PostgreSQL, if the operations are 'not idempotent' (relies on at
> least one function that has varying output for the same input
> parameters) then the order in which you do things could lead to
> different results.
>
> For the optimizer to be effective then it must be allowed to do
> operations in the best order it sees fit -- this is documented. Just as
> you must not rely on the order in which results are returned, unless you
> explicitly have an ORDER BY -- as the system will extract results in the
> fastest way it knows, which may not necessarily be in the same order as
> the values where inserted. This would be true, even if you had a totally
> different query language.
>
>
> Cheers,
> Gavin
>
>
>
>
>