support overwrite with load_labels_from_file

[wangyong86]

My customers report:

1205=# select load_labels_from_file('owg', 'Person', 'overwrite_load/file2.csv');
ERROR:  Cannot insert duplicate vertex id: 957014890901079
HINT:  Entry id 112589960769111 is already used

They expect overwrite the existing vertex, which is simliar to INSERT ... ON CONFLICT SET ...

Although load into a temp table and using insert .. on conflict is viable, but customer can't accept it for complex operation.

So, I'll try to implement it check existence first and then update the conflicted tuple just like INSERT .. ON CONFLICT. Is it a reasonable approach?

[MuhammadTahaNaveed]

@wangyong86 The feature requested can be added to age csv loader, however, it won't be able to land in the upcoming release, since the release work has already started.

I'll update when I have a PR ready for this request.

[wangyong86]

I check the implementation of Insert .. on conflict. It's coupled with Executor tightly. A possbile approach is constructing plan and ExecUpdate when necessary. So, do you have better idea?

[MuhammadTahaNaveed]

@wangyong86 The most natural approach given the existing bulk load flow is to delete the existing tuple when a conflict is detected and let the insertion continue uninterrupted. This preserves the batch insert performance that the csv loader is designed for, while adding the overwrite capability. The main tradeoff is that this can get costly if the index has bloat, since we need to deal with tuple visibility checks to find the actual conflicting tuple among potentially stale index entries. But for typical use cases (well maintained indexes), it should work well.

Note that this can only be supported for load_labels_from_file (vertices) and specifically for id conflicts.

[wangyong86]

For the below case and data

1205=# select load_labels_from_file('owg', 'Personx', 'overwrite_load/file1.csv');
ERROR:  Cannot insert duplicate vertex id: 957014890901079
HINT:  Entry id 112589960769111 is already used
1205=# \d+ "owg"."Personx";
                                                                                            Table "owg.Personx"
   Column   |  Type   | Collation | Nullable |                                                Default                                                | Storage  | Compression | Stats target | Description
------------+---------+-----------+----------+-------------------------------------------------------------------------------------------------------+----------+-------------+--------------+-------------
 id         | graphid |           | not null | _graphid(_label_id('owg'::name, 'Personx'::name)::integer, nextval('owg."Personx_id_seq"'::regclass)) | plain    |             |              |
 properties | agtype  |           | not null | agtype_build_map()                                                                                    | extended |             |              |
Indexes:
    "Personx_pkey" PRIMARY KEY, btree (id)
Inherits: owg._ag_label_vertex

112589960769111,tom,25
112589960769112,jonh,30
112589960769113,michale,28

load_labels_from_file check conflict using index of "Person_pkey", but the real conflicted column is the sub-field of properties.

So, it's a coincidence or a special design? It's reasonable to force a unique index on properties.id?

[MuhammadTahaNaveed]

@wangyong86 Entity ids encode label information and are generated at runtime from the label and entry_id (when the id_field_exists parameter is enabled, which it is by default). For a given label, the resulting ids are reproducible.