Server: Update broken on PGSQL

[LukasReschke]

From @BernhardPosselt on IRC:

"\OC\Updater::failure: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT \"f\".\"fileid\", \"f\".\"path\", \"p\".\"path\" as \"parent_path\", \"f\".\"name\", \"f\".\"parent\", \"f\".\"storage\", \"p\".\"storage\" as \"parent_storage\" FROM \"oc_filecache\" f INNER JOIN \"oc_filecache\" p ON (\"f\".\"parent\" = \"p\".\"fileid\") AND (\"p\".\"name\" <> ?) WHERE \"f\".\"path\"
18:01:55 \" || ? || \"f\".\"name\" LIMIT 1000' with params [\"\", \"\\/\"]:\n\nSQLSTATE[42804]: Datatype mismatch: 7 ERROR: argument of WHERE must be type boolean, not type text\nLINE 1: ...ent\" = \"p\".\"fileid\") AND (\"p\".\"name\" <> $1) WHERE \"f\".\"path\"...\n

@icewind1991 This seems like your repair step. Can you look into this? This is blocking 12.0.1. THX!

[BernhardPosselt]

psql (PostgreSQL) 9.6.3 (latest Debian stable)

[icewind1991]

No clue why the query builder is fucking up the query

https://github.com/nextcloud/server/blob/13dd0b0ebe9cdce7ea5c9287a1e6c6547f8a6809/lib/private/Repair/NC13/RepairInvalidPaths.php#L58

[BernhardPosselt]

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.

[BernhardPosselt]

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)

[BernhardPosselt]

The error is the missing parentheses

[BernhardPosselt]

https://github.com/nextcloud/server/blob/master/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php#L28

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

[enoch85]

A 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, https://github.com/nextcloud/vm/pull/277#issuecomment-310527005) 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.

[BernhardPosselt]

Hi @enoch85

Please use a new topic ;) short answer: I think postgres and mysql are both equally well supported and there is no bias.

[nickvergessen]

@BernhardPosselt

Also I have no idea why you are not reusing doctrine's query builder

We are, just have 2 manipulations in it:

1. automatic adding of *PREFIX* on table names
2. 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? ;)