In 20150101 and 20241201 Dump:Import of release_artist.csv failing because of a release with null artist value

[ijabz]

Importing data from /home/ubuntu/code/discogs-xml2db/csv-dir/release_artist.csv
Traceback (most recent call last):
  File "postgresql/importcsv.py", line 42, in <module>
    load_csv(os.path.abspath(filename), db)
  File "postgresql/importcsv.py", line 33, in load_csv
    cursor.copy_expert(q, fp)
psycopg2.errors.NotNullViolation: null value in column "artist_id" violates not-null constraint
DETAIL:  Failing row contains (32, 7, null, Koibito & Boku, 1, null, 2, null, Other [Spirits Lifted By], null).
CONTEXT:  COPY release_artist, line 33: "7,,Koibito & Boku,1,,2,,Other [Spirits Lifted By],"

[ijabz]

This is the related album https://www.discogs.com/release/7-Moonchildren-Moonchildren-EP?redirected=true

Koibito & Boku are listed as other credit and not clickable, the release artist is MoonChildren.

[ijabz]

All seem problematic because not release artist for any of these releases.

grep -G "Koibito & Boku" release_artist.csv
7,,Koibito & Boku,1,,2,,Other [Spirits Lifted By],
13,,Koibito & Boku,1,,2,,Other [Spirituality],
5121801,,Koibito & Boku,1,,2,,Other,

Update no that is not isssue they are extra_artists and this is recorded by column 4 =1

[ijabz]

From original xml

<release id="7" status="Accepted"><artists><artist><id>28</id><name>Moonchildren</name></artist></artists><title>Moonchildren EP</title><labels><label name="Deep4Life" catno="D4L12002" id="9"/></labels><extraartists><artist><id>387624</id><name>Kevin Hodge</name><anv>Ki</anv><role>Lacquer Cut By</role></artist><artist><name>Koibito &amp; Boku</name><role>Other [Spirits Lifted By]</role></artist><artist><id>921599</id><name>Kory And The Gang</name><role>Vocals</role></artist><artist><id>57</id><name>Chris Gray</name><role>Written-By, Producer, Mixed By</role></artist></extraartists><formats><format name="Vinyl" qty="1" text="Clear"><descriptions><description>12"</description><description>33 ⅓ RPM</description><description>EP</description></descriptions></format></formats><genres><genre>Electronic</genre></genres><styles><style>Deep House</style></styles><country>US</country><released>2000-02-00</released><notes>Written, produced and mixed for Mind Massage Music (BMI).

[ijabz]

grep -E "^[0-9]+,," release_artist.csv |wc -l
731946

So a huge number so I cant work if something broken in this file or not.

[ijabz]

Compared with 20240601

<release id="7" status="Accepted">
....
<extraartists>
<artist>
<id>387624</id><name>Kevin Hodge</name><anv>Ki</anv><join></join><role>Lacquer Cut By</role><tracks></tracks></artist>
<artist><id>0</id><name>Koibito &amp; Boku</name><anv></anv><join></join><role>Other [Spirits Lifted By]</role><tracks></tracks></artist>
<artist><id>921599</id><name>Kory And The Gang</name><anv></anv><join></join><role>Vocals</role><tracks></tracks></artist><artist><id>57</id><name>Chris Gray</name><anv></anv><join></join>
<role>Written-By, Producer, Mixed By</role><tracks></tracks></artist>
</extraartists>

So the difference is when no have an artist id it used to just set it to zero

`<id>0</id><name>Koibito &amp; Boku</name>`

Now it doesnt set it at all

`<name>Koibito &amp; Boku</name>`

So because the id was nonsense we ignored these rows anyway so personally dont release lose any functionality by removing them from the csv file as being ignored anyway, but woul be better  if did not ignore and could use them somehow.

[ijabz]

`
 grep -E "^[0-9]+,," -v release_artist.csv > release_artist.tmp; rm release_artist.csv; mv release_artist.tmp release_artist.csv`

[tomduncalf]

FWIW I asked Claude to modify the import script to temporarily remove the non-null constraints, do the import, delete the null rows, and re-add the constraints instead. I'm not sure if there's a better way to do this, or if this approach is correct – I don't know much about Postgres, but figured I'd share in case this problem is on-going! I couldn't see a way to just say "ignore the constraints on import".

#!/usr/bin/env python
import bz2
import sys
import os
import pathlib
from psycopg2 import sql
from dbconfig import connect_db, Config
# since we run this as a script, we need to add the parent folder
# so we can import discogsxml2db from it
parent_path = str(pathlib.Path(__file__).absolute().parent.parent)
sys.path.insert(1, parent_path)
from discogsxml2db.exporter import csv_headers  # noqa

def load_csv(filename, db):
    print("Importing data from {}".format(filename))
    base, fname = os.path.split(filename)
    table, ext = fname.split('.', 1)
    
    cursor = db.cursor()
    
    # Get columns that are not nullable from the target table
    cursor.execute(sql.SQL("""
        SELECT column_name, column_default
        FROM information_schema.columns 
        WHERE table_name = %s 
        AND is_nullable = 'NO'
        AND column_default IS NULL
    """), [table])
    
    non_nullable_columns = [(row[0], row[1]) for row in cursor.fetchall()]
    
    # Store constraints for later restoration
    constraint_changes = []
    
    # Temporarily drop NOT NULL constraints
    for column, default in non_nullable_columns:
        constraint_changes.append((column, default))
        cursor.execute(
            sql.SQL("ALTER TABLE {} ALTER COLUMN {} DROP NOT NULL").format(
                sql.Identifier(table),
                sql.Identifier(column)
            )
        )
    
    # Load data directly into the table
    if ext.startswith('csv'):
        q = sql.SQL("COPY {} ({}) FROM STDIN WITH CSV HEADER").format(
                sql.Identifier(table),
                sql.SQL(', ').join(map(sql.Identifier, csv_headers[table])))
    
    if ext == 'csv':
        fp = open(filename, encoding='utf-8')
    elif ext == 'csv.bz2':
        fp = bz2.BZ2File(filename)
    
    cursor.copy_expert(q, fp)
    fp.close()
    
    # Count rows with NULL values that will be deleted
    count_nulls_query = []
    for column, _ in non_nullable_columns:
        count_nulls_query.append(
            sql.SQL("{} IS NULL").format(sql.Identifier(column))
        )
    
    if count_nulls_query:
        null_check = sql.SQL(" OR ").join(count_nulls_query)
        cursor.execute(
            sql.SQL("SELECT COUNT(*) FROM {} WHERE {}").format(
                sql.Identifier(table),
                null_check
            )
        )
        invalid_rows = cursor.fetchone()[0]
    else:
        invalid_rows = 0
    
    # Delete rows with NULL values in non-nullable columns
    if non_nullable_columns:
        conditions = []
        for column, _ in non_nullable_columns:
            conditions.append(
                sql.SQL("{} IS NULL").format(sql.Identifier(column))
            )
        
        where_clause = sql.SQL(" OR ").join(conditions)
        
        cursor.execute(
            sql.SQL("DELETE FROM {} WHERE {}").format(
                sql.Identifier(table),
                where_clause
            )
        )
    
    # Restore NOT NULL constraints
    for column, default in constraint_changes:
        cursor.execute(
            sql.SQL("ALTER TABLE {} ALTER COLUMN {} SET NOT NULL").format(
                sql.Identifier(table),
                sql.Identifier(column)
            )
        )
    
    # Count total rows imported
    cursor.execute(
        sql.SQL("SELECT COUNT(*) FROM {}").format(
            sql.Identifier(table)
        )
    )
    current_count = cursor.fetchone()[0]
    
    print(f"Successfully imported {current_count} rows. Skipped {invalid_rows} rows with NULL values in non-nullable columns.")
    
    db.commit()

root = os.path.realpath(os.path.dirname(__file__))
config = Config(os.path.join(root, 'postgresql.conf'))
db = connect_db(config)
for filename in sys.argv[1:]:
    load_csv(os.path.abspath(filename), db)

[philipmat]

Thank you, @tomduncalf -- would you mind submitting a PR?

[hugs-and-kisses]

The issue is still not resolved. When I use importcsv.py from @tomduncalf, I get the following output from the April 2025 dumps:

Importing data from /app/csv/master_artist.csv.bz2
Successfully imported 2962968 rows. Skipped 131 rows with NULL values in non-nullable columns.
Importing data from /app/csv/release_artist.csv.bz2
Successfully imported 87273197 rows. Skipped 749721 rows with NULL values in non-nullable columns.
Importing data from /app/csv/release_company.csv.bz2
Successfully imported 31702283 rows. Skipped 2812 rows with NULL values in non-nullable columns.
Importing data from /app/csv/release_track_artist.csv.bz2
Successfully imported 117360389 rows. Skipped 1007135 rows with NULL values in non-nullable columns.

All other files imported without any skipped rows.

Using PostgreSQL, the primary key constraints were applied successfully. However, the following errors occurred when adding foreign key constraints with CreateFKConstraints.sql:

ERROR:  insert or update on table "release_artist" violates foreign key constraint "release_artist_fk_artist"
DETAIL:  Key (artist_id)=(194) is not present in table "artist".
ERROR:  insert or update on table "release_label" violates foreign key constraint "release_label_fk_label"
DETAIL:  Key (label_id)=(4010514) is not present in table "label".
ERROR:  insert or update on table "release_track_artist" violates foreign key constraint "release_track_artist_fk_artist"
DETAIL:  Key (artist_id)=(118760) is not present in table "artist".

When running CreateIndexes.sql I got the following error:

ERROR:  index row size 3224 exceeds btree version 4 maximum 2704 for index "release_track_idx_title"
DETAIL:  Index row references tuple (348870,48) in relation "release_track".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

I'll take a look over the next few days and see if I can come up with a proper fix. If so, I'll open a PR.

[hugs-and-kisses]

So the difference is when no have an artist id it used to just set it to zero

<id>0</id><name>Koibito &amp; Boku</name>

Now it doesnt set it at all

So because the id was nonsense we ignored these rows anyway so personally dont release lose any functionality by removing them from the csv file as being ignored anyway
@ijabz

I haven't verified if the same applies to all tables that cause problems, but this is likely the case.

So basically the script from @tomduncalf but instead of deleting we would set to zero..

They would remain in the DB, and the FK constraints would probably work again (?). Need to test that out.

Not really a clean fix, but I don't see a much better one, especially not without reworking the DB Schema...

[hugs-and-kisses]

I made a script that now has following output and seems to work quite well:

(.venv) PS D:\discogs-xml2db> python postgresql/importcsv.py .\csvdir\
[22:21:52] Input path is a directory: .\csvdir\. Scanning for CSV files...
[22:21:52]   + Added artist.csv.bz2
... (truncated)
[22:21:52]   + Added master_artist.csv.bz2
[22:21:52]   + Added master_genre.csv.bz2
[22:21:52]   + Added master_style.csv.bz2
[22:21:52]   + Added master_video.csv.bz2
[22:21:52] Found 12 valid CSV files in directory.
... (truncated)
[22:24:04] Processing file 9/12: master_artist.csv.bz2 (master_artist)...
[22:24:16]     • Updated 131 NULL rows in master_artist.artist_id to 0.
[22:24:16]   • Updated 131 total NULL rows across 1 column(s).
[22:24:17]   • Processed master_artist.csv.bz2 successfully.
[22:24:17]   • Time: 10.72s, Rows: 2963099 (276434 rows/s)
[22:24:17] Processing file 10/12: master_genre.csv.bz2 (master_genre)...
[22:24:23]   • No NULL values found requiring updates in non-nullable columns.
[22:24:23]   • Processed master_genre.csv.bz2 successfully.
[22:24:23]   • Time: 4.91s, Rows: 3204108 (653099 rows/s)
... (truncated)

--- Import Summary ---
Total files found: 12
Files attempted: 12
Files successfully committed: 12
Total rows imported: 42783207
Files failed: 0

  Status by table:
    Table                 Status     NULLs Updated   Total Rows
    --------------------  --------   -------------   ----------
    artist                Committed              0      9185936
    artist_alias          Committed              0      4421129
    artist_namevariation  Committed              0      4544402
    artist_url            Committed              0      1901464
    group_member          Committed              0      2126680
    label                 Committed              0      2198897
    label_url             Committed              0       358443
    master                Committed              0      2418416
    master_artist         Committed            131      2963099
    master_genre          Committed              0      3204108
    master_style          Committed              0      3738058
    master_video          Committed              0      5722575

  Detailed NULL Updates by Table:
    master_artist:
      Column     Default Value        Count
      ---------  -------------------  ---------
      artist_id  0                          131

Status:
  Processing finished SUCCESSFULLY.
Total duration: 309.81 seconds

It now also accepts folders, not just files. Previously, especially on Windows, patterns like /csvdir/* didn't work, so users needed to do something like (Get-ChildItem .\csvdir\*.csv.bz2).FullName or list all file paths separately.

I will run some final tests and probably make the PR tomorrow.