Issue with removing videos off of playlist

Official Instance

• The bug is reproducible on the official hosted instance, or is API-related.

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.

1. Create playlist
2. Add videos (presumably, a fair few of them)
3. Attempt to delete ones at the top of the playlist (the earliest videos added to the playlist)
4. Error as above

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.

ERROR: ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey"
  Detail: Key (playlist_id, videos_order)=(6, 21) already exists.
An error occoured in the path: /user/playlists/remove
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)=(6, 21) 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(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "playlists_videos_ids_pkey"
  Detail: Key (playlist_id, videos_order)=(6, 21) 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 more

Browser, and OS with Version.

Brave Version 1.56.20
Windows 11 22H2 22621.2134

Additional context

No response

[chev2]

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 more

The 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)

[leepfrog-ger]

Same issue on my personal instance

[leepfrog-ger]

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:

https://github.com/TeamPiped/Piped-Backend/blob/7b1f7dfb2030ef9ec46cd9e64e586e3af6d9e156/src/main/java/me/kavin/piped/server/handlers/auth/AuthPlaylistHandlers.java#L347

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 IMMEDIATE which 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?

[luckkmaxx]

Im dealing with this issue too. After updating the database container to mysql postgres15 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:

1. add 3 videos to a playlist
2. then try to remove the first added and it shows the bug.
3. Try to remove the last added, the middle and the first, then all is removed.

[FireMasterK]

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.

[luckkmaxx]

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.

[leepfrog-ger]

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.

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'm using Postgres version 12.16 from Docker's postgres:12 image

[FireMasterK]

We should add a test for this first in https://github.com/TeamPiped/Piped-Backend/blob/master/testing/api-test.sh 🤔

[FireMasterK]

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?

[hugoghx]

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)

[danstewart]

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 more

[Nebucatnetzer]

I‘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.