starred/shiori

Fork 0

mirror of https://github.com/go-shiori/shiori.git synced 2026-04-25 22:45:55 +03:00

[GH-ISSUE #215] "failed to save bookmark" #157

New issue

Closed

opened 2026-02-25 23:33:35 +03:00 by kerem · 11 comments

kerem commented

2026-02-25 23:33:35 +03:00

Owner

Originally created by @watson387 on GitHub (Nov 22, 2019).
Original GitHub issue: https://github.com/go-shiori/shiori/issues/215

Shiori Docker with Postgresql database, on Ubuntu Server 19.10.

My compose:

shiori:
    image: radhifadlillah/shiori
    container_name: shiori
    depends_on:
      - shioridb
    restart: unless-stopped
    ports:
      - "8989:8080"
    volumes:
     - ${USERDIR}/docker/shiori:~/.local/share/shiori
    environment:
      - PUID=${PUID}
      - PGID=${PGID}
      - TZ=${TZ}
      # - SHIORI_DIR=${USERDIR}/docker/shiori
      - SHIORI_DBMS=postgresql
      - SHIORI_PG_USER=shiori
      - SHIORI_PG_PASS=shioripass
      - SHIORI_PG_NAME=shioridb
      - SHIORI_PG_HOST=shioridb
      - SHIORI_PG_PORT=5432
  shioridb:
    image: postgres:12-alpine
    container_name: shioridb
    restart: always
    environment:
      - POSTGRES_USER=shiori
      - POSTGRES_PASSWORD=shioripass
      - POSTGRES_DB=shioridb
    volumes:
      - ${USERDIR}/docker/shiori/db:/var/lib/postgresql/data

It was working fine at first but now, while trying to add bookmarks through gui I'm getting this error:

failed to save bookmark: pq: current transaction is aborted, commands ignored until end of transaction block (500)

I'm also seeing this in the shioridb logs:


2019-11-22 12:52:27.773 UTC [589] ERROR:  insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"


2019-11-22 12:52:27.773 UTC [589] DETAIL:  Key (bookmark_id)=(5) is not present in table "bookmark".


2019-11-22 12:52:27.773 UTC [589] STATEMENT:  INSERT INTO bookmark_tag


			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING


2019-11-22 13:15:00.325 UTC [589] ERROR:  insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"


2019-11-22 13:15:00.325 UTC [589] DETAIL:  Key (bookmark_id)=(15) is not present in table "bookmark".


2019-11-22 13:15:00.325 UTC [589] STATEMENT:  INSERT INTO bookmark_tag


			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING


2019-11-22 13:15:00.326 UTC [589] ERROR:  current transaction is aborted, commands ignored until end of transaction block


2019-11-22 13:15:00.326 UTC [589] STATEMENT:  SELECT id FROM tag WHERE name = $1


2019-11-22 13:17:25.315 UTC [589] ERROR:  insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"


2019-11-22 13:17:25.315 UTC [589] DETAIL:  Key (bookmark_id)=(22) is not present in table "bookmark".


2019-11-22 13:17:25.315 UTC [589] STATEMENT:  INSERT INTO bookmark_tag


			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING


2019-11-22 13:17:25.316 UTC [589] ERROR:  current transaction is aborted, commands ignored until end of transaction block


2019-11-22 13:17:25.316 UTC [589] STATEMENT:  SELECT id FROM tag WHERE name = $1


2019-11-22 13:18:19.518 UTC [589] ERROR:  insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"


2019-11-22 13:18:19.518 UTC [589] DETAIL:  Key (bookmark_id)=(23) is not present in table "bookmark".


2019-11-22 13:18:19.518 UTC [589] STATEMENT:  INSERT INTO bookmark_tag


			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING


2019-11-22 13:18:19.519 UTC [589] ERROR:  current transaction is aborted, commands ignored until end of transaction block


2019-11-22 13:18:19.519 UTC [589] STATEMENT:  SELECT id FROM tag WHERE name = $1


2019-11-22 13:19:51.411 UTC [589] ERROR:  insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"


2019-11-22 13:19:51.411 UTC [589] DETAIL:  Key (bookmark_id)=(24) is not present in table "bookmark".


2019-11-22 13:19:51.411 UTC [589] STATEMENT:  INSERT INTO bookmark_tag


			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING


2019-11-22 13:19:51.412 UTC [589] ERROR:  current transaction is aborted, commands ignored until end of transaction block


2019-11-22 13:19:51.412 UTC [589] STATEMENT:  SELECT id FROM tag WHERE name = $1

What am I doing wrong?

Originally created by @watson387 on GitHub (Nov 22, 2019). Original GitHub issue: https://github.com/go-shiori/shiori/issues/215 Shiori Docker with Postgresql database, on Ubuntu Server 19.10. My compose: ``` shiori: image: radhifadlillah/shiori container_name: shiori depends_on: - shioridb restart: unless-stopped ports: - "8989:8080" volumes: - ${USERDIR}/docker/shiori:~/.local/share/shiori environment: - PUID=${PUID} - PGID=${PGID} - TZ=${TZ} # - SHIORI_DIR=${USERDIR}/docker/shiori - SHIORI_DBMS=postgresql - SHIORI_PG_USER=shiori - SHIORI_PG_PASS=shioripass - SHIORI_PG_NAME=shioridb - SHIORI_PG_HOST=shioridb - SHIORI_PG_PORT=5432 shioridb: image: postgres:12-alpine container_name: shioridb restart: always environment: - POSTGRES_USER=shiori - POSTGRES_PASSWORD=shioripass - POSTGRES_DB=shioridb volumes: - ${USERDIR}/docker/shiori/db:/var/lib/postgresql/data ``` It was working fine at first but now, while trying to add bookmarks through gui I'm getting this error: ``` failed to save bookmark: pq: current transaction is aborted, commands ignored until end of transaction block (500) ``` I'm also seeing this in the shioridb logs: ``` 2019-11-22 12:52:27.773 UTC [589] ERROR: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" 2019-11-22 12:52:27.773 UTC [589] DETAIL: Key (bookmark_id)=(5) is not present in table "bookmark". 2019-11-22 12:52:27.773 UTC [589] STATEMENT: INSERT INTO bookmark_tag (tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING 2019-11-22 13:15:00.325 UTC [589] ERROR: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" 2019-11-22 13:15:00.325 UTC [589] DETAIL: Key (bookmark_id)=(15) is not present in table "bookmark". 2019-11-22 13:15:00.325 UTC [589] STATEMENT: INSERT INTO bookmark_tag (tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING 2019-11-22 13:15:00.326 UTC [589] ERROR: current transaction is aborted, commands ignored until end of transaction block 2019-11-22 13:15:00.326 UTC [589] STATEMENT: SELECT id FROM tag WHERE name = $1 2019-11-22 13:17:25.315 UTC [589] ERROR: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" 2019-11-22 13:17:25.315 UTC [589] DETAIL: Key (bookmark_id)=(22) is not present in table "bookmark". 2019-11-22 13:17:25.315 UTC [589] STATEMENT: INSERT INTO bookmark_tag (tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING 2019-11-22 13:17:25.316 UTC [589] ERROR: current transaction is aborted, commands ignored until end of transaction block 2019-11-22 13:17:25.316 UTC [589] STATEMENT: SELECT id FROM tag WHERE name = $1 2019-11-22 13:18:19.518 UTC [589] ERROR: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" 2019-11-22 13:18:19.518 UTC [589] DETAIL: Key (bookmark_id)=(23) is not present in table "bookmark". 2019-11-22 13:18:19.518 UTC [589] STATEMENT: INSERT INTO bookmark_tag (tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING 2019-11-22 13:18:19.519 UTC [589] ERROR: current transaction is aborted, commands ignored until end of transaction block 2019-11-22 13:18:19.519 UTC [589] STATEMENT: SELECT id FROM tag WHERE name = $1 2019-11-22 13:19:51.411 UTC [589] ERROR: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" 2019-11-22 13:19:51.411 UTC [589] DETAIL: Key (bookmark_id)=(24) is not present in table "bookmark". 2019-11-22 13:19:51.411 UTC [589] STATEMENT: INSERT INTO bookmark_tag (tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING 2019-11-22 13:19:51.412 UTC [589] ERROR: current transaction is aborted, commands ignored until end of transaction block 2019-11-22 13:19:51.412 UTC [589] STATEMENT: SELECT id FROM tag WHERE name = $1 ``` What am I doing wrong?

kerem

2026-02-25 23:33:35 +03:00

closed this issue
added the
type:bug

component:backend

database:sqlite
labels

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@deanishe commented on GitHub (Aug 6, 2020):

I think this issue is related to the problems described in PR #250.

I plan to rejigger the way database IDs are handled in an upcoming update.

@deanishe commented on GitHub (Aug 6, 2020): I think this issue is related to the problems described in PR #250. I plan to rejigger the way database IDs are handled in an upcoming update.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@Mati20187 commented on GitHub (Mar 27, 2022):

Sorry for necrobumping but this is still relevant. I have shiori installed from the precompiled binary on my raspberry pi (postgres db) and when I try to add a new bookmark and attach a tag to it, I get insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk".
Is there a fix for this issue yet? I would greatly appreciate any help!

@Mati20187 commented on GitHub (Mar 27, 2022): Sorry for necrobumping but this is still relevant. I have shiori installed from the precompiled binary on my raspberry pi (postgres db) and when I try to add a new bookmark and attach a tag to it, I get `insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk"`. Is there a fix for this issue yet? I would greatly appreciate any help!

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@Mati20187 commented on GitHub (Mar 30, 2022):

What I did:

Click "+" to add a bookmark
Paste a link
Add a tag
Confirm

What I got on the screen:

Relevant logs from journalctl -xe:

Mar 30 09:53:59 raspberrypi shiori[1621]: 2022/03/30 09:53:59 error during insert: pq: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" Mar 30 09:53:59 raspberrypi shiori[1621]: 2022/03/30 09:53:59 error during rollback: sql: transaction has already been committed or rolled back Mar 30 09:53:59 raspberrypi shiori[1621]: time="2022-03-30T09:53:59+02:00" level=warning msg="POST /api/bookmarks" proto=HTTP/1.1 remote="127.0.0.1:53046" reqlen=195 size=82 status=500

@Mati20187 commented on GitHub (Mar 30, 2022): What I did: 1. Click "+" to add a bookmark 2. Paste a link 3. Add a tag 4. Confirm What I got on the screen: ![pq-error](https://user-images.githubusercontent.com/31066931/160782022-d04230d6-6217-41f4-8db4-179b81199abc.jpg) Relevant logs from `journalctl -xe`: ` Mar 30 09:53:59 raspberrypi shiori[1621]: 2022/03/30 09:53:59 error during insert: pq: insert or update on table "bookmark_tag" violates foreign key constraint "bookmark_tag_bookmark_id_fk" Mar 30 09:53:59 raspberrypi shiori[1621]: 2022/03/30 09:53:59 error during rollback: sql: transaction has already been committed or rolled back Mar 30 09:53:59 raspberrypi shiori[1621]: time="2022-03-30T09:53:59+02:00" level=warning msg="POST /api/bookmarks" proto=HTTP/1.1 remote="127.0.0.1:53046" reqlen=195 size=82 status=500 `

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@fmartingr commented on GitHub (May 3, 2022):

Hey @Mati20187, sorry for being a little absent. Are you still experiencing this? I use SQLite in my personal Shiori server so I would need to test this properly once I have some time.

@fmartingr commented on GitHub (May 3, 2022): Hey @Mati20187, sorry for being a little absent. Are you still experiencing this? I use SQLite in my personal Shiori server so I would need to test this properly once I have some time.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@Mati20187 commented on GitHub (May 6, 2022):

@fmartingr yes, this is still an issue for me. Also, if you use SQLite, have you noticed a significant performance drop discussed in #408 ?

@Mati20187 commented on GitHub (May 6, 2022): @fmartingr yes, this is still an issue for me. Also, if you use SQLite, have you noticed a significant performance drop discussed in #408 ?

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@fmartingr commented on GitHub (May 26, 2022):

@fmartingr yes, this is still an issue for me.

I will check this once I have some time. I have some migrations pending on my local infrastructure and was wondering if migrating to PSql myself.

Also, if you use SQLite, have you noticed a significant performance drop discussed in #408 ?

I did not, actually. But my bookmark database is not as big as others have pointed out in the issue. Hopefully #425 will fix that.

@fmartingr commented on GitHub (May 26, 2022): > @fmartingr yes, this is still an issue for me. I will check this once I have some time. I have some migrations pending on my local infrastructure and was wondering if migrating to PSql myself. > Also, if you use SQLite, have you noticed a significant performance drop discussed in #408 ? I did not, actually. But my bookmark database is not as big as others have pointed out in the issue. Hopefully #425 will fix that.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@hrshadhin commented on GitHub (Jun 24, 2022):

i'm also facing this issue like @Mati20187
Can't save bookmarks with tags. i'm using PostgreSQL.

@hrshadhin commented on GitHub (Jun 24, 2022): i'm also facing this issue like @Mati20187 Can't save bookmarks with tags. i'm using PostgreSQL.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@kolaente commented on GitHub (Aug 1, 2022):

I'm getting a similar error every time I'm trying to save a bookmark with tags:

failed to save bookmark: pq: current transaction is aborted, commands ignored until end of transaction block (500)

Saving without tags or adding tags after saving works fine.

@kolaente commented on GitHub (Aug 1, 2022): I'm getting a similar error every time I'm trying to save a bookmark with tags: ``` failed to save bookmark: pq: current transaction is aborted, commands ignored until end of transaction block (500) ``` Saving without tags or adding tags after saving works fine.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@sardaukar commented on GitHub (Sep 5, 2022):

I could only add a bookmark via the API after creating a tag in the Web UI by hand, and then using this payload (imported is the tag I created):

{
  "url": "https://article.com",
  "tags": [
    {
	"id": 10,
	"name": "imported"
    }
  ],
  "title": "Cool Interesting Article",
  "excerpt": "An interesting and cool article indeed!"
}

@sardaukar commented on GitHub (Sep 5, 2022): I could only add a bookmark via the API after creating a tag in the Web UI by hand, and then using this payload (`imported` is the tag I created): ``` { "url": "https://article.com", "tags": [ { "id": 10, "name": "imported" } ], "title": "Cool Interesting Article", "excerpt": "An interesting and cool article indeed!" } ```

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@thelazyoxymoron commented on GitHub (Oct 4, 2022):

+1 for this. Not able to add a tag with Postgres. I was, however, able to add (multiple) tag(s) when trying to edit an existing bookmark. Looks like this is a problem only when you try to add a tag during the "add a bookmark" screen.

@thelazyoxymoron commented on GitHub (Oct 4, 2022): +1 for this. Not able to add a tag with Postgres. I was, however, able to add (multiple) tag(s) when trying to edit an existing bookmark. Looks like this is a problem only when you try to add a tag during the "add a bookmark" screen.

kerem commented

2026-02-25 23:33:35 +03:00

Author

Owner

@fmartingr commented on GitHub (Oct 4, 2022):

I've tested this locally and #484 should fix the psql problem. I'm sure there are more issues underneath the database wrapper, but we'll try to fix them as they come. Please take a look.

Note: There's still a problem with archives unless we properly use returning or #480 is tested and merged.

@fmartingr commented on GitHub (Oct 4, 2022): I've tested this locally and #484 should fix the psql problem. I'm sure there are more issues underneath the database wrapper, but we'll try to fix them as they come. Please take a look. Note: There's still a problem with archives unless we properly use returning or #480 is tested and merged.