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)

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:

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?

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.

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.

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

We 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.

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

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.

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.

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.

I also did not experience this.

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?

FWIW, I've been rocking this change for months and it seems to work great. Thank you @leepfrog-ger!

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;

Somehow missed that comment, it also looks to have sorted the issue for me 🎉

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;

Just want to echo that this worked for me. Thanks @leepfrog-ger!

Outta curiosity - has this "fix" been rolled into main version yet?

As it seems I'm now seeing this on ggtyler.dev, where I accidentally got the same video added twice to a playlist, and I now I cannot delete either of them without triggering the error.
Thx in advance.

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:

1. Export my playlists JSON
2. Modify the JSON export file so that it contained only the affected playlist
   AND remove the duplicated video from the affected playlist.
3. Rename the affected playlist in Piped to something else
   (e.g. " - BROKEN")
4. 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

@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_TRANSACTION might 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:

https://stackoverflow.com/questions/72082427/error-duplicate-key-value-violates-unique-constraint-while-updating-multiple-r

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_72082427

Is 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 advance

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?

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

1. 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)
2. Confirm you're connected to the right place:

psql -U piped -l

This should output a list of databases, the important one in this case is named "piped" by default.

3. Connect to the "piped" database interactive psql session to run the commands.

psql -U piped -d piped
piped=#

4. 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

5. 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 > :index expects that the updates will be executed in the order as if the rows were ordered by videos_order column, 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 DEFERRED instead of INITIALLY 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.