-
-
Notifications
You must be signed in to change notification settings - Fork 4k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
PostgreSQL: violation of constraints #366
Comments
What's the transaction isolation level you are using? |
As far as the PostgreSQL server is concerned the default "read committed" was not changed. |
Read commited is what we need, so that seems to be fine. |
I'm getting this behaviour on Nextcloud 10.0 with PostgreSQL 9.5.4:
Over at owncloud @nickvergessen seems to have found the underlying issue already (correct me if I'm wrong here): There is a workaround by @mobstef here to silence the logoutput: Although it needs to be modified for the Reference: Then again UPSERT is only avaible on Postgres 9.5 and above, but there are equivalents for MySQL. |
I had this error for both oc_preferences and oc_credentials with several log entries like this per second :
Based upon owncloud/core#23213 (comment) , the following rules silence the log messages:
Just in case that helps someone :-) |
The rule mentioned above is incomplete, since it doesn't handle the update case. Simply fixed: But this is just a poor patch. With user_ldap active, every access will try to insert and then (after ConstraintViolation) update displayName, uid and lastFeatureRefresh, resulting in 6 database roundtrips. This appears very inefficient to me. In addition, PostgreSQL will have the preferences table bloated since each update (replacing the same value over and over again) will create an additional row. On my test system, preferences has 106 rows but >74000 estimated rows (including outdated rows) consuming 5MB, indicating unhealtly access patterns. |
Some of this will hopefully be gone with #2147 |
is there anyone working on this? Although everything seems to work, running occ files:scan --all throws an exception, trying to update displayName. It is somewhat weird though, that the exception is about the user 3aee88e4-3657-1036-85e2-8bb479b1dffc, but occurs after scanning his files, during scanning the files of the user 3ca7f660-3b12-1036-9932-a142a160eb37. Extract from the output of the occ command:
It goes through with users 6 through 10 without problems after that though and I don't miss any files online, so it still seems to work. Also, every few seconds, Nextcloud tries to insert homePath, which already exists.
I observed that every one of these issues appears for the same user though. So, if there is anyone working on this, do you need any logs? I don't know php at all so I can not help with that unfortunately. |
Running b5990b7 on the same system except the updates which took place in the meantime (Apache 2.4.25, PHP 7.1.1, PostgreSQL 9.6.1) I cannot see the initial problem any longer. Also, I cannot confirm the trouble caused by running Running Nextcloud in virtual machines for testing purposes only so far. So in particular new database for each test and only few users and files, which I guess may have some impact e. g. on the findings with |
Is this supposed to be fixed? Just upgraded my PostgreSQL to 9.6 running 12.0.0 and got this:
EDIT Cleared my Redis cache with EDIT2 Hmm, I forgot to login and logout again, error still appears. :( |
Still fails --^ :( |
Hello Everyone,
|
Similar issue: #9305. It seems that Nextcloud has serious problem with concurrency… |
Any change regarding this? As I am using ldap auth, my postgres logs are full with the error messages In my opinion the best solution would be to use UPSERT (as mentioned earlier), maybe in an opt-in way to only enable it on systems using postgresql-9.5 or later. |
please reopen the issue, I'm still seeing this as well (also with LDAP auth) |
I actually implemented the suggestion from above
Considering that this only affects INSERT, any UPDATE should still work as expected. (not sure if the current INSERTs are supposed to also update (or if there is a separate update in case the INSERT fails) |
Just migrated from Mysql to PostgreSQL (version 11) and noticed the exact same error:
Happens (at least, maybe also in other situations) everytime when logging in.. |
same here with PostgreSQL 11, FreeBSD 12, and Nextcloud 15:
|
I also experienced on Ubuntu 16.04, Nextcloud 15, PostgreSQL 11. |
Just to be clear: this is actually wanted behavior. The SQL statements we used before where causing deadlocks in certain situations. So we went for the approach of inserting the column and let the DB handle this case with either an exception or an insertion without issues. In case of the exception we catch it in the application and know that the entry was already there. Then we handle it in the application logic. Unfortunately this is not the perfect way to do it, but the most robust one when it comes to handling this for the different DBs that we support. |
Good to know. I hope you can add this to the manual (https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/linux_database_configuration.html), so that people installing Nextcloud with PosgreSQL know this in advance. Otherwise it makes sense to think it's a bug in Nextcloud. |
While having a procedure throw an exception and act accordingly is standard programming technique for today's languages, this is not the case for SQL. It will flood the error log, eventually burying the real errors, so this isn't really administrator friendly and should be avoided. There's probably no simple rdbms-agnostic way to achieve this behaviour (insert .. on conflict and stored procedures are alternatives to rules for pgsql, none of them portable) |
Exactly that was the problem :/ And it also caused some weird corner cases as well that were then even hard to debug because they were super difficult to reproduce. |
@MorrisJobke, I certainly appreciate the problem when trying to support multiple DBMSs, thank you for that insight. I think @andreas-p hit the nail on the head, though, with:
In general, numbing the admin to a big error file will mask true errors and it is in general bad practice (imo) to encourage that behavior. I'm hoping that if a fix is found that is general-enough for multiple DBMSs, you would consider using it. With a closed issue, though, this is difficult as (1) out of sight, out of mind; and (2) users that are considering working on code for PRs are more likely to look at the " |
There are a lot of this postgres issues around here. #12729 is another one and there is already someone working on a potential fix. |
@danielkesselberg thanks for the perspective. Admittedly it's difficult to keep track of the issues, so I haven't seen many of them. That one you linked to, however, was closed over a month before the PR was first submitted, and even there MorrisJobke is saying the same thing as here: it's on purpose and there is no stated intent to fix or even track the underlying issue. Is there any indication that this direction is going to be accepted? (It's failing the build at the moment ...) |
@MorrisJobke Unfortunately, That behaviour makes generates too many logs every transaction, approx. 10GB. That's makes disk storage consumption, so I annoying every day to manage the PostgreSQL system. |
Using PostgreSQL some database constraints are by default violated.
From the second time users log in onwards there are messages
and
The first time a user is browsing the sample "Photos" in section "All files" there's
Seen running 9.0.52 on Arch Linux (PostgreSQL 9.5.3, PHP 7.0.8, Apache 2.4.23 using the PHP module).
The text was updated successfully, but these errors were encountered: