Repository navigation
Update broken on PGSQL #5909
Description
Activity
psql (PostgreSQL) 9.6.3 (latest Debian stable)
No clue why the query builder is fucking up the query
This is $computedPath at https://github.com/nextcloud/server/blob/stable12/lib/private/Repair/NC13/RepairInvalidPaths.php#L57
`p`.`path` || :dcValue1 || `f`.`name`That query won't work, you will need to expand it.
owncloud=# select name from oc_news_folders where name <> 'open' || 'source'; ERROR: argument of WHERE must be type boolean, not type text LINE 1: select name from oc_news_folders where name <> 'open' || 'so...owncloud=# select name from oc_news_folders where name <> ('open' || 'source'); name ------------- Open Source Media IT (3 rows)The error is the missing parentheses
return new QueryFunction($this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y));
should be
return new QueryFunction('(' . $this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y) . ')');
No idea why you overwrite this method because postgres has CONCAT(). Also I have no idea why you are not reusing doctrine's query builder
Reacted by enoch85 and Helmut K. C. TessarekA little bit off topic here, but I wish PostgreSQL in general was better supported. In MariaDB/MySQL you have to mess around with UTF8mb4 if you want 4-byte support, which is default in PostgreSQL UTF8.
PostgreSQL feels like a better DB in general. Would be nice if Nextcloud decided to go down that path instead of recommending a less advanced (and IMHO) worse DB.
I'm writing this after I have been messing with MariaDB for hours to activate 4 byte support, which only took a few minutes in PostgreSQL (aka
apt install postgresql). According to the IRC channel (#posgresql) UTF8mb4 is just something that MariaDB/MySQL came up with becuase they messed up the original code and had to make a quick fix to solve it. That doesn't sound good to me. Also UTFmb4 is considered "experimental" according to docs which isn't the case with PostgreSQL as it just works out of the box.I think it was @MorrisJobke that said that MariaDB/MySQL was tested in a larger (EDIT: clustered enviroments, nextcloud/vm#277 (comment)) environment and proved to be better, but my guess is that that test was some time ago. Maybe you should consider making PostgreSQL the recomended DB after all?
Also, this is not just about UTF8mb4, I'm talking generally. There are a lot to gain in using PSQL any other DB IMHO. According to several hours of reading I did comparing and testing the alternatives.
Reacted by Benedikt Geißler, r2evans, Keith Fiske, e-alfred and Bert GeensHi @enoch85
Please use a new topic ;) short answer: I think postgres and mysql are both equally well supported and there is no bias.
Also I have no idea why you are not reusing doctrine's query builder
We are, just have 2 manipulations in it:
- automatic adding of
*PREFIX*on table names - automatic quoting of table/column names for oracle support
However that doctrine query builder has no "FunctionBuilder" like we have, so we are not overwriting a doctrine method with some wrong.
Also as per https://www.postgresql.org/docs/9.1/static/functions-string.html
||is also valid...But thanks for finding the issue and feel free to send a PR? ;)
- automatic adding of
From @BernhardPosselt on IRC:
@icewind1991 This seems like your repair step. Can you look into this? This is blocking 12.0.1. THX!