Repository navigation
Issue with removing videos off of playlist #2814
Description
Activity
I'm also getting this same error on the piped.projectsegfau.lt instance:
org.hibernate.exception.ConstraintViolationException: JDBC exception executing SQL [UPDATE playlists_videos_ids SET videos_order = videos_order - 1 WHERE playlist_id = ? AND videos_order > ?] [ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey" Detail: Key (playlist_id, videos_order)=(5359, 63) already exists.] [n/a] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:95) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:96) at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:76) at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:834) at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:637) at me.kavin.piped.server.handlers.auth.AuthPlaylistHandlers.removeFromPlaylistResponse(AuthPlaylistHandlers.java:350) at me.kavin.piped.server.ServerLauncher.lambda$mainServlet$50(ServerLauncher.java:484) at io.activej.http.AsyncServlet.lambda$ofBlocking$0(AsyncServlet.java:43) at io.activej.promise.Promise.lambda$ofBlocking$12(Promise.java:249) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey" Detail: Key (playlist_id, videos_order)=(5359, 63) already exists. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:83) ... 10 moreThe error says something about a duplicate key value, but for reference, the video(s) I try to remove are only shown once in the playlist. Or maybe the 'duplicate key value' has something to do with the playlist ID itself? I'm not sure.
I also only started seeing this error since a couple days ago as @sk471onn mentioned.
This seems to be a backend error, but just in case it matters:
Browser: LibreWolf 116.0.2-1
OS: EndeavourOS Linux x86_64 (Kernel: 6.1.45-1-lts)Same issue on my personal instance
I had a look at this today and I think I found the issue.
It is caused by TeamPiped/Piped-Backend#672 and more specifically by the logic that is intended to decrease the playlist positioning for every element in the playlist that was positioned after the removed video:
The problem is that PostgreSQL does not seem to allow this kind of query as the uniqueness of the primary key constraint is checked after each updated row, not after the completion of the statement.
Depending on how the update statement is processed, this can lead to the above error.I solved that in my instance by defining the primary key as
DEFERRABLE INITIALLY IMMEDIATEwhich causes the constraint check to happen after the statement was completed:More information:
- https://www.depesz.com/2009/08/11/waiting-for-8-5-deferrable-uniqueness/
- https://www.alibabacloud.com/blog/postgresql-deferrable-constraints-unique-primary-key-foreign-key-and-exclude_597717#:~:text=If%20a%20constraint%20is%20deferrable,the%20end%20of%20the%20transaction.
Executing the following Statement against my Piped DB solved the issue:
ALTER TABLE "playlists_videos_ids" DROP CONSTRAINT "playlists_videos_ids_pkey"; ALTER TABLE "playlists_videos_ids" ADD PRIMARY KEY ("playlist_id", "videos_order") DEFERRABLE INITIALLY IMMEDIATE;
Note: I do not know whether this has unintended side effects and it may cause your DB to not being updateable in the future with the official DB migration scripts - so use with caution.
@FireMasterK: Maybe you can double-check my analysis/solution and see if it makes sense to include this change?
Reacted by Kavin, Dan Stewart, astro, Myned and Andrey 0xdc09Reacted by Chev, astro, Myned and Benjamin BouvierReacted by astro and MynedIm dealing with this issue too. After updating the database container to
mysqlpostgres15 did not solved it.
The way that I was able to remove the problematic items were removing starting from last added (bottom of list) to older ones (top of list). I was able to reproduce it twice, but not more times:- add 3 videos to a playlist
- then try to remove the first added and it shows the bug.
- Try to remove the last added, the middle and the first, then all is removed.
Could you please check what version of the postgres image is used? We used to use the Alpine Linux images previously, and they had data consistency issues in regard to unique constraints.
Could you please check what version of the postgres image is used? We used to use the Alpine Linux images previously, and they had data consistency issues in regard to unique constraints.
sorry, it was postgres13 alpine, and after update to postgres15 (not alpine tag) the bug came back next day.
Could you please check what version of the postgres image is used? We used to use the Alpine Linux images previously, and they had data consistency issues in regard to unique constraints.
I ran into this issue with the 13-alpine version, then migrated to 15 (no alpine), error contributed to occur. After troubleshooting I arrived at the conclusion described above.
Maybe the 15 version does by default organize the pages on disk differently or there is another reason why it is not occurring more often, but from my research into the inner workings of postgres regarding update statements and primary key constraints the current form of the update statement is expected to fail in cases where postgres does not perform the order decrement in exactly the ascending order.
Reacted by KavinWe should add a test for this first in https://github.com/TeamPiped/Piped-Backend/blob/master/testing/api-test.sh 🤔
We should add a test for this first in https://github.com/TeamPiped/Piped-Backend/blob/master/testing/api-test.sh 🤔
I tried this in TeamPiped/Piped-Backend#698, but the test seems to pass. Perhaps we have something else wrong?
We should add a test for this first in https://github.com/TeamPiped/Piped-Backend/blob/master/testing/api-test.sh 🤔
I tried this in TeamPiped/Piped-Backend#698, but the test seems to pass. Perhaps we have something else wrong?
I also have this issue. I've been looking at it and I don't think it's as simple as having a playlist and removing a single video - I tried that on the official instance and it seems to work.
I can say that for small playlists, this hasn't been an issue for me, but when I have larger ones, this problem shows up. I'd like to say around 20-30+ videos on a playlist. And yes, the problem becomes deleting the older ones. You can do it, but you have to delete the newer ones first, and work your way backwards.
I'm using Postgres 15 (not the alpine version)
I've tried upgrading from 13-alpine to 15 (non alpine) but no luck.
I think it's something to do with having the same video multiple times in the one playlist but can't figure out how to replicate it.
Full Error
org.hibernate.exception.ConstraintViolationException: JDBC exception executing SQL [UPDATE playlists_videos_ids SET videos_order = videos_order - 1 WHERE playlist_id = ? AND videos_order > ?] [ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey" Detail: Key (playlist_id, videos_order)=(28, 29) already exists.] [n/a] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:97) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:96) at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:76) at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:835) at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:650) at me.kavin.piped.server.handlers.auth.AuthPlaylistHandlers.removeFromPlaylistResponse(AuthPlaylistHandlers.java:350) at me.kavin.piped.server.ServerLauncher.lambda$mainServlet$47(ServerLauncher.java:438) at io.activej.http.AsyncServlet.lambda$ofBlocking$0(AsyncServlet.java:43) at io.activej.promise.Promise.lambda$ofBlocking$12(Promise.java:249) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey" Detail: Key (playlist_id, videos_order)=(28, 29) already exists. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:83) ... 10 moreI‘m experience the same issue on a hosted instance.
I exported the playlists and reimported them.
Afterwards I was able to remove the first two videos (the once I had watched) but the third failed again.I think it's something to do with having the same video multiple times in the one playlist but can't figure out how to replicate it.
In my testing duplicate videos are also what triggers the issue.
In my opinion duplicate videos in playlists should not even be a thing and a request to add a video to a playlist a second time should be rejected.5 remaining items
I don’t think it has. Fwiw it is still working (for me) with the most recent version.
Ok thx, seems I was the lucky one!
To anyone else having this issue, my "workaround" to resolve it was to:
- Export my playlists JSON
- Modify the JSON export file so that it contained only the affected playlist
AND remove the duplicated video from the affected playlist. - Rename the affected playlist in Piped to something else
(e.g. " - BROKEN") - Import JSON file to restore the original playlist
(which should now allow delete operations to work again, since offending/duplicate video ID was removed)
If anyone knows a quicker workaround (in case this happens again), I'm all ears 🤓
Sigh, I spoke too soon.
Now having the issue again with a completely separate playlist.This one is weirder, because there is no duplication of videos that I can see (I also double-checked the export for dupes), but still when I try to remove some videos, I get...
org.hibernate.exception.ConstraintViolationException: JDBC exception executing SQL [UPDATE playlists_videos_ids SET videos_order = videos_order - 1 WHERE playlist_id = ? AND videos_order > ?] [ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey" Detail: Key (playlist_id, videos_order)=(606, 21) already exists.] [n/a] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:97) at ...@FireMasterK @Bnyro maybe you could take another look at the analysis/suggestion I posted here: #2814 (comment)
It seems clear that this is a widespread issue and no valid use of postgresql prone to intermittent failures
Reacted by Paul Nicholas@FireMasterK @Bnyro maybe you could take another look at the analysis/suggestion I posted here: #2814 (comment)
It seems clear that this is a widespread issue and no valid use of postgresql prone to intermittent failures
I feel it may simply be caused by an incorrect connection handling setting in https://github.com/TeamPiped/Piped-Backend/blob/c746794d7434d0cda99f8b586a431ef69e1fb7b5/src/main/resources/hibernate.cfg.xml#L13
The valid settings are https://docs.jboss.org/hibernate/orm/6.4/javadocs/org/hibernate/resource/jdbc/spi/PhysicalConnectionHandlingMode.html
I feel
DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTIONmight fix the issue.I just haven't had enough time to look into that, unfortunately. I'll take a look over the weekend if I can.
Thanks!
I did some further research and wanted to add the results here, too.
The following SO post describes the same situation/type of query:This comment even provides a dbfiddle where the issue can be reproduced:
https://stackoverflow.com/questions/72082427/error-duplicate-key-value-violates-unique-constraint-while-updating-multiple-r#comment127364069_72082427Reacted by Paul NicholasIs this not a widespread issue?
Or is it just a few people (like us) that are affected by this?
Coz sadly, no matter how "clean" I try to keep my playlists - it seems to be at random when this issue will happen and then I suddenly can't remove any videos again, unless I can determine the "broken" one and remove that first.
Thx in advanceALTER TABLE "playlists_videos_ids" DROP CONSTRAINT "playlists_videos_ids_pkey"; ALTER TABLE "playlists_videos_ids" ADD PRIMARY KEY ("playlist_id", "videos_order") DEFERRABLE INITIALLY IMMEDIATE;
Thanks for that solution! I'm not that much of a nerd of doing stuff like this around here but where do I put this statement? Is it in like some sort of pat of the code or do i have to put it else where?
This is an SQL statement that needs to be executed against the postgresql database that is running in the db container.
I don’t have any exact extractions at hand at the moment, sorry.ALTER TABLE "playlists_videos_ids" DROP CONSTRAINT "playlists_videos_ids_pkey"; ALTER TABLE "playlists_videos_ids" ADD PRIMARY KEY ("playlist_id", "videos_order") DEFERRABLE INITIALLY IMMEDIATE;
Thanks for that solution! I'm not that much of a nerd of doing stuff like this around here but where do I put this statement? Is it in like some sort of pat of the code or do i have to put it else where?
Hey there, unsure if you've figured it out by now but allow me to document my process for implementing this (I literally only just got this fix running so it's worked for a lengthy 10 minutes now).
- Connect to the console/terminal of the "postgres" container in the piped stack (I used Portainer but you can do this however you're most comfortable)
- Confirm you're connected to the right place:
psql -U piped -lThis should output a list of databases, the important one in this case is named "piped" by default.
- Connect to the "piped" database interactive psql session to run the commands.
psql -U piped -d piped piped=#- Run both commands (note the "piped=#" preceding the commands indicating you're inside the psql session). I ran one command at a time. Then, exit the psql interactive session.
piped=# ALTER TABLE "playlists_videos_ids" DROP CONSTRAINT "playlists_videos_ids_pkey"; piped=# ALTER TABLE "playlists_videos_ids" ADD PRIMARY KEY ("playlist_id", "videos_order") DEFERRABLE INITIALLY IMMEDIATE; piped=# \q- Enjoy!
So far, so good. Hoping this works long term.
This happens due to Postgres not executing the updates in expected order.
The query that causes the error
UPDATE playlists_videos_ids SET videos_order = videos_order - 1 WHERE playlist_id = :playlistId AND videos_order > :indexexpects that the updates will be executed in the order as if the rows were ordered byvideos_ordercolumn, but Postgres (if any database engine) gives no such guarantee.The easiest way to work around the problem is to make the constraint that causes the error deferrable (@leepfrog-ger 's solution). I'd only use
INITIALLY DEFERREDinstead ofINITIALLY IMMEDIATE. That would move the constraint checks even further down the line - to the end of the transaction. This would make all sorts of playlist reorderings possible without worrying about the order of updates.I can create a PR with a Liquibase changeset that recreates the constraint just for Postgres, if everyone is OK with that.
Reacted by leepfrog- added 2 commits that reference this issue
on Dec 10, 2024 - added a commit that references this issue
on Jan 8, 2025
Official Instance
Describe the bug
I have a private instance and have a playlist with 30 videos or so on my account. The videos are all distinct (no duplicates). On this playlist, attempting to remove some videos fails with a database error (see logs below).
Some videos, however, do delete just fine. Seemingly, it's the videos that were added last to the playlist (the last couple of them) that don't display this behavior, while the videos that were added earlier do.
I suspect the amount of videos on the playlist might also play a part on this as I did a test where I deleted the videos that never displayed an error, and then attempted to delete the videos that previously erred, and they deleted fine.
For some context on timelines, I start seeing the error on videos added about 2 days ago.
I did a quick sanity test with the below reproduction steps on the official instance and wasn't able to replicate the issue, unfortunately.
To Reproduce
As mentioned, you might not see the error specifically with these reproduction steps. Playlist video count and time since added may play into this problem.
Expected behavior
No error, video is removed from the playlist
Logs/Errors
Here's the full error as displayed in the backend server:
I am running the backend on commit
69757fd.Browser, and OS with Version.
Brave Version 1.56.20
Windows 11 22H2 22621.2134
Additional context
No response