Ángel wrote on 4/01/23 2:59 pm:
On 2023-01-04 at 10:24 +1300, Sidney Markowitz wrote:
If anyone else reading this is using 4.0.0 and postgres for AWL, are
you seeing or not seeing this problem?
I can easily reproduce this with a quick install and manually providing
the SQL from the code (output included below). Postgresql (tested on
version 11) seem to be choking on the self-referential totscore in the
update clause.
It seems it can't assign a value from the row inside the DO UPDATE but
only a constant.
Thanks, I found it easy to reproduce your results by pasting that into
the online sandbox at https://www.db-fiddle.com/ and running with every
version of postgres that has ON CONFLICT (added in version 9.5).
Martin, that proves that it doesn't have anything to do with the awl and
txrep tables having the same column names, it is something postgres is
picky about even with no other tables in the schema. SQLite, which uses
the same ON CONFLICT syntax for upsert doesn't sem to have a problem
with it.
As a quick fix, removing the string “pg|” from the lines 309, 325, 341
and 358 will probably make the plugin work by making it use the
fallback code with postgres.
> A += doesn't (neccesarily) fix it. Perhaps it works on an higher
> version.
The correct syntax is =+ Benny's += was probably a typo.
I verified in the sandbox that it works for every version of postgres
from 9.5 through 15, and also the SQLite versions there.
Making the expression totscore = awl.totscore + 10 also works, but =+
seems cleaner.
I'll open a bug report, post a patch that gets all of the similar sql
statements, and see what the devs with more SQL experience have to say.
-- Sidney
