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.
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.
postgres=# CREATE TABLE awl (
postgres(# username varchar(100) NOT NULL default '',
postgres(# email varchar(255) NOT NULL default '',
postgres(# ip varchar(40) NOT NULL default '',
postgres(# msgcount bigint NOT NULL default '0',
postgres(# totscore float NOT NULL default '0',
postgres(# signedby varchar(255) NOT NULL default '',
postgres(# last_hit timestamp NOT NULL default CURRENT_TIMESTAMP,
postgres(# PRIMARY KEY (username,email,signedby,ip)
postgres(# );
CREATE TABLE
postgres=# select * from awl;
username | email | ip | msgcount | totscore | signedby | last_hit
----------+-------+----+----------+----------+----------+----------
(0 rows)
postgres=# insert into awl (username, email, ip, signedby) values ('john',
'jsm...@example.com', '127.0.0.1', '-');
INSERT 0 1
postgres=# select * from awl;
username | email | ip | msgcount | totscore | signedby |
last_hit
----------+--------------------+-----------+----------+----------+----------+----------------------------
john | jsm...@example.com | 127.0.0.1 | 0 | 0 | - |
2023-01-04 02:45:24.769152
(1 row)
postgres=# insert into awl (username, email, ip, signedby) values ('john',
'jsm...@example.com', '127.0.0.1', '-') ON CONFLICT (username, email, signedby,
ip) DO UPDATE set msgcount = 5, totscore=totscore + 10;
ERROR: column reference "totscore" is ambiguous
LINE 1: ...ignedby, ip) DO UPDATE set msgcount = 5, totscore=totscore +...
^
