Jain commented

Your setup

From source

Extra details

compiling things from a custom dockerfile which is similiar to the original one in the repo

Version

d782140e2b

PostgreSQL version

15

What were you trying to do?

When i restore the db from postgres 12 to 15 i get the following issue:

ERROR:  function activity_visibility(character varying, character varying[], jsonb) does not exist
LINE 1: visibility_new := activity_visibility(NEW.actor, NEW.recipie...
                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data)
CONTEXT:  PL/pgSQL function public.update_status_visibility_counter_cache() line 15 at assignment

I checked the db, the function is the same as in
https://git.pleroma.social/pleroma/pleroma/-/blob/develop/priv/repo/migrations/20200508092434_update_counter_cache_table.exs
https://akkoma.dev/AkkomaGang/akkoma/src/branch/develop/priv/repo/migrations/20200508092434_update_counter_cache_table.exs

Also asked FloatingGhost about this:

11:46:37 h. hm that's unusual
11:46:50 but it's probably fine - provided it restored all of your rows it's probably fine though
11:47:18 just run a quick count(*) over activities and objects, if the function exists and the count is right you're probably good
11:53:58 yes it did, it restored all the things
11:54:14 just a bit worried that the trigger wont work anymore
11:58:01 the counter isn't very important, even if it were to break you wouldn't notice

What did you expect to happen?

not giving that error on restoring backups

What actually happened?

it restores everything except that it also throws this error

Logs

Severity

I can manage

Have you searched for this issue?

I have double-checked and have not found this issue mentioned anywhere.

### Your setup From source ### Extra details compiling things from a custom dockerfile which is similiar to the original one in the repo ### Version d782140e2b1a7cedaaafa6c55713928d7117bddc ### PostgreSQL version 15 ### What were you trying to do? When i restore the db from postgres 12 to 15 i get the following issue: ``` ERROR: function activity_visibility(character varying, character varying[], jsonb) does not exist LINE 1: visibility_new := activity_visibility(NEW.actor, NEW.recipie... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data) CONTEXT: PL/pgSQL function public.update_status_visibility_counter_cache() line 15 at assignment ``` I checked the db, the function is the same as in https://git.pleroma.social/pleroma/pleroma/-/blob/develop/priv/repo/migrations/20200508092434_update_counter_cache_table.exs https://akkoma.dev/AkkomaGang/akkoma/src/branch/develop/priv/repo/migrations/20200508092434_update_counter_cache_table.exs Also asked FloatingGhost about this: 11:46:37 <FloatingGhost> h. hm that's unusual 11:46:50 <FloatingGhost> but it's probably fine - provided it restored all of your rows it's probably fine though 11:47:18 <FloatingGhost> just run a quick count(*) over activities and objects, if the function exists and the count is right you're probably good 11:53:58 <Jain> yes it did, it restored all the things 11:54:14 <Jain> just a bit worried that the trigger wont work anymore 11:58:01 <FloatingGhost> the counter isn't very important, even if it were to break you wouldn't notice ### What did you expect to happen? not giving that error on restoring backups ### What actually happened? it restores everything except that it also throws this error ### Logs ```shell - ``` ### Severity I can manage ### Have you searched for this issue? - [x] I have double-checked and have not found this issue mentioned anywhere.

Jain added the

bug

label 2023-01-19 11:11:26 +00:00

Jain commented

2023-01-19 11:23:54 +00:00

Author

to document the issue properly and in case others are unsure:

12:19:45 if you're interested, all the counter does is like... fill in an admin endpoint
12:19:59 which i don't think is used anywhere

to document the issue properly and in case others are unsure: 12:19:45 <FloatingGhost> if you're interested, all the counter does is like... fill in an admin endpoint 12:19:59 <FloatingGhost> which i don't think is used anywhere

Jain commented

2023-01-19 11:32:55 +00:00

Author

shit, i checked the wrong db, no it didnt import activities

Jain commented

2023-01-19 12:02:03 +00:00

Author

this happens if someone uses plain pg_dump and psql, gona test out https://docs.akkoma.dev/develop/administration/backup/#backup now...

Jain commented

2023-01-20 08:34:34 +00:00

Author

Ok, here is the deal and why it is important

According to the official Documentation (https://docs.akkoma.dev/develop/administration/backup/#backup) one should restore the backup at once.
But this way has some issues to big instances:
https://blog.freespeechextremist.com/blog/activities-visibility-index-slowness.html
TLDR: because it doesnt restore a specific index, the creation of the activities-visibility index is really really slow.

If you restore it, as in the blog recommended (schema first then data) you will get this issue which i documented here (the format of the dump doesnt matter).

So for larger instances there is basically only one way to restore their data:

Start the restore as official recommended
Add this Index while restoring, do that before its creating the activities-visibility index:
CREATE INDEX CONCURRENTLY tmp_apid_follower_address ON users USING btree (ap_id, COALESCE(follower_address, ''::character varying));

# Ok, here is the deal and why it is important According to the official Documentation (https://docs.akkoma.dev/develop/administration/backup/#backup) one should restore the backup at once. But this way has some issues to big instances: https://blog.freespeechextremist.com/blog/activities-visibility-index-slowness.html TLDR: because it doesnt restore a specific index, the creation of the activities-visibility index is really really slow. If you restore it, as in the blog recommended (schema first then data) you will get this issue which i documented here (the format of the dump doesnt matter). So for larger instances there is basically only one way to restore their data: 1. Start the restore as official recommended 2. Add this Index while restoring, do that before its creating the activities-visibility index: CREATE INDEX CONCURRENTLY tmp_apid_follower_address ON users USING btree (ap_id, COALESCE(follower_address, ''::character varying));

floatingghost commented

2023-01-20 13:01:47 +00:00

Owner

note 3 on the restore options note that

ilja commented

2023-01-20 19:37:55 +00:00

Contributor

y'know... Maybe it's a bit dirty, but what if we rename an index so it's not even an issue any more? If I'm not mistaken, the indexes are listed alphabetically, so changing the name could change the order of the indexes. Even with the note in the docs, I'm still seeing people struggle with it. I assume you normally let Ecto handle the naming, so I'm unsure if there'll be unintended side effects, but otherwise maybe worth a shot.