Documentation: What about unique constraints?

[raratiru]

I am trying to understand what happens during copy_from if a table already contains data and a unique constraint is triggered.

By using drop_constraints=False, if a value-to-be-saved already exists in the database will it be ignored or will an error be raised?

Searching the Postgresql-9.5+ docs, I found the ON CONFLICT clause which caters for this issue.

[palewire]

This is a good question. I do not know the answer. Shall we write a unit test?

[igncampa]

It apparently raises an IntegrityError:

On drop_constraints=True, the IntegrityError is raised by Django:

django.db.utils.IntegrityError: duplicate key value violates unique constraint "model_field_key"
DETAIL: Key (field)=(value) already exists.

On drop_constraints=False, the IntegrityError is raised by psycopg2:

psycopg2.IntegrityError: duplicate key value violates unique constraint "model_field_key"
DETAIL: Key (field)=(value) already exists.

It doesn't seem like the COPY command accepts ON CONFLICT or anything similar. I guess it may be possible to write a try - except block to catch the exception and simulate ON CONFLICT. How efficient that would be I don't really know.

[raratiru]

I am totally unaware of how the COPY command works, I just learnt about it yesterday.

During this research I bumped on this SO post which roughly says that the theoretical SQL layout of a solution would be:

CREATE TEMP TABLE tmp_table 
ON COMMIT DROP
AS
SELECT * 
FROM main_table
WITH NO DATA;

COPY tmp_table FROM 'full/file/name/here';

INSERT INTO main_table
SELECT *
FROM tmp_table
ON CONFLICT DO NOTHING;

[igncampa]

Oh. Well dajngo-postgres-copy does create a temporary table, copies from your csv file into the temp table using the COPY command and then does an INSERT INTO from the temp table.

So it would actually be possible to add in the ON CONFLICT parameter to the INSERT INTO command, however there currently is not a way of modifying the SQL statement to include it (without diving into the source code, of course).

[raratiru]

This is great news!

Currently I am working on preparing my project for this functionality. Nonetheless, I consider diving into the source code a great opportunity for making new friends!

[igncampa]

I think the easiest way to implement it would be

copy_from.py

        sql = """
            INSERT INTO "%(model_table)s" (%(model_fields)s) (
            SELECT %(temp_fields)s
            FROM "%(temp_table)s")
            %(on_conflict)s;"""

        if self.on_conflict:
            options['on_conflict'] = "ON CONFLICT {}".format(self.on_conflict)
            # the above is prone to string cocatenation, for example:
            # "ON CONFLICT DO NOTHING; DELETE FROM table *;" deletes all records in the table
            if ';' in options['on_conflict']:
                raise SyntaxError('Terminanting the SQL statement is forbidden!')
        else:
            options['on_conflict'] = ""

However a lot would be left to the user considering ON CONFLICT has almost infinite possibilities.

I'd like to hear @palewire's thoughts on this before moving on. It sounds reasonable to implement and particularly helpful for users who do a lot of mass updating, but it can be argued that it has nothing to do with the COPY command and thus beyond the scope of this library.

[raratiru]

As a user, I understand that the infinite possibilities of ON CONFLICT would be out of the scope of this library.

However, a default behavior of DO NOTHING which guarantees that the library will finish without "unexpected" errors its task, is a solution that guarantees a high level of quality for such a great tool.

[igncampa]

Yeah in my opinion the default behavior should always be to raise the IntegrityError but allow users to pass in an ON CONFLICT DO NOTHING argument since the latter might have unintended consequences (such a silently omitting rows).

Sounds reasonable to me.

[raratiru]

OK, sorry, I misunderstood the topic of the discussion and my expression was not accurate.

As a user, I would very much appreciate the existence of a keyword argument that will allow me to chose whether on conflict, the library will ignore the current entry or -by default- raise an IntegrittyError.

A third option for this argument would be a callable where a user could develop one of the infinite other possibilities available. However, this is not as crucial for the perceived quality of the library as the basic True/False option for an ON CONFLICT DO NOTHING argument is, according to my perception.