Issue with removing videos off of playlist

ghost commented 1 year ago

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.

Create playlist
Add videos (presumably, a fair few of them)
Attempt to delete ones at the top of the playlist (the earliest videos added to the playlist)
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 commented 1 year ago

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 commented 1 year ago

Same issue on my personal instance

leepfrog-ger commented 1 year ago

I had a look at this today and I think I found the issue.

It is caused by https://github.com/TeamPiped/Piped-Backend/pull/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:

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 commented 1 year ago

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:

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.

FireMasterK commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.

ghost commented 1 year ago

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 commented 1 year ago

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

FireMasterK commented 1 year ago

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 https://github.com/TeamPiped/Piped-Backend/pull/698, but the test seems to pass. Perhaps we have something else wrong?

hugoghx commented 1 year ago

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 commented 11 months ago

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 commented 11 months ago

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.

JonathanTreffler commented 10 months ago

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.

Nebucatnetzer commented 10 months ago

That wasn’t the case for my playlists. It only contained each video once and also not in another playlist. On 29 Oct 2023, at 17:18, Jonathan Treffler @.***> wrote:

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.

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

hugoghx commented 10 months ago

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!

danstewart commented 10 months ago

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 :tada:

samiralam commented 10 months ago

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!

Liquidream commented 9 months ago

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.

leepfrog-ger commented 8 months ago

I don’t think it has. Fwiw it is still working (for me) with the most recent version.

Liquidream commented 8 months ago

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 🤓

Liquidream commented 8 months ago

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

leepfrog-ger commented 8 months ago

@FireMasterK @Bnyro maybe you could take another look at the analysis/suggestion I posted here: https://github.com/TeamPiped/Piped/issues/2814#issuecomment-1703845716

It seems clear that this is a widespread issue and no valid use of postgresql prone to intermittent failures

FireMasterK commented 8 months ago

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

leepfrog-ger commented 8 months ago

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

Liquidream commented 7 months ago

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

Animelove1105 commented 1 month ago

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?

leepfrog-ger commented 1 month ago

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.

Lwfrancisco commented 1 month ago

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 -l
```
This 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.

TeamPiped / Piped