openSUSE admin

mb_opensuse2=# \d filearr
                                    Table "public.filearr"
 Column  |          Type          | Collation | Nullable |               Default               
---------+------------------------+-----------+----------+-------------------------------------
 id      | integer                |           | not null | nextval('filearr_id_seq'::regclass)
 path    | character varying(512) |           | not null | 
 mirrors | smallint[]             |           |          | 

So the table format was changed, but the code was not updated to match? (or the other way around)

/usr/lib64/python2.7/site-packages/mb/files.py, line 162:

def dir_filelist(conn, path):
    """Returns tuples of (id, name) for all files that reside in a directory

    The returned filenames include their path."""

    query = """SELECT filearr.path, hash.file_id
                   FROM filearr 
               LEFT JOIN hash 
                   ON hash.file_id = filearr.id 
               WHERE filearr.dirname = '%s/'""" % util.pgsql_regexp_esc(path)

Currently installed mirrorbrain is 'python-mb-2.19.3-lp152.12.1.x86_64'.

pjessen wrote:

Andrii, I see you have been doing some work on mirrorbrain, I was just wondering if this is a left-over from some of that?

It looks this patch wasn't executed , which should be part of 2.19.3
https://github.com/openSUSE/mirrorbrain/blob/2.19.3/sql/migrations/schema-postgresql-add-filearr_split_path_trigger.sql

I didn't do anything in production, except some troubleshooting in May iirc

pjessen wrote:

Before I go and apply it, any reason it may have been deliberately left out? Darix, Andrii?

It should be safe to apply. I don't think that it is part of installer script, so probably it should be applied manually and just wasn't during update or something similar.

I ran the updates, but the final "UPDATE filearr SET id=id where dirname is null" to trigger updates of all the filename/dirnames got killed:

ERROR:  deadlock detected
DETAIL:  Process 23433 waits for ShareLock on transaction 2358030461; blocked by process 31890.
Process 31890 waits for ShareLock on transaction 2357996271; blocked by process 23433.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (8233,68) in relation "filearr"

pjessen wrote:

Okay, another few deadlocks and restarts, but it looks like I'm getting there. Only 4512606 rows to go.

2021-03-18 19:00 Uh, correction - 4512606 is the number of rows done ..... 112209127 to go.
2021-03-19 11:00 9450577 done.
2021-03-21 09:47 18606065 done (not running continually since last count).

I'll have to come up with a script for this, but why is it so slow?

Still working on this. Small steps at a time.

Okay, I have manually updated everything that isn't tumbleweed and isn't repositories. Now 27'388'500 of 117'642'160 rows updated, approx 23%.