Gotcha postgres: `unique_index_on_selector?` does not work as expected

[KevinColemanInc]

Gotcha postgres: `unique_index_on_selector?` does not work as expected

[pnomolos]

I’ll change the name, as we want to look for a unique constraint (a unique index does not necessarily work) in order for INSERT ... ON CONFLICT ... work.

…

On Sat, Aug 18, 2018 at 9:55 PM Kevin Coleman ***@***.***> wrote: Current behavior The standard way of defining a unique index for table integrity in rails <https://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index> is to call add_index(..., unique: true). This calls CREATE UNIQUE INDEX in postgres to define the unique constraint. Gotcha! upsert is using information_schema.constraint_column_usage to determine if a table has a unique *constraint* which is different than a unique index in postgres. Considering the method name suggests your checking for unique index, maybe we should change the name? Related documentation https://www.postgresql.org/message-id/49E798A3.2020805%40fmed.uba.ar And the reply https://www.postgresql.org/message-id/3622.1239912596%40sss.pgh.pa.us Fix Quick fix you need to define a constraint in postgres like so: ALTER TABLE $1 ADD CONSTRAINT $2 UNIQUE USING INDEX $2 where $1 is the table name and $2 is the name of the unique index Fix the gem Use a combination of pg_class, pg_index, and pg_attribute to look for unique indexes. related SO post <https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql> — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#111>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AACNf2gloDaFRBLOV-gXIJB1r56q_nOxks5uSO-ngaJpZM4WC1Bk> .

[KevinColemanInc]

@pnomolos

If you're just changing the name, this will make your gem difficult to use with rails b/c the alter table would not be reflected in your schema.rb file which most rails projects use to define the schema.

[KevinColemanInc]

Why does the unique index not work?

[pnomolos]

Because Postgres requires a unique constraint, not a unique index, in order to use that syntax. Took me a couple days of banging my head against the wall to sort that one out.

…

On Sat, Aug 18, 2018 at 10:04 PM Kevin Coleman ***@***.***> wrote: Why does the unique index not work? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#111 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AACNf6UKUdtqtD-YRGtZWeX0T6RmzcIuks5uSPHqgaJpZM4WC1Bk> .

[pnomolos]

I can add a note that you need to ensure you’re using a constraint in order for the gem to use the native functionality instead of the generated function.

…

On Sat, Aug 18, 2018 at 10:06 PM Philip Schalm ***@***.***> wrote: Because Postgres requires a unique constraint, not a unique index, in order to use that syntax. Took me a couple days of banging my head against the wall to sort that one out. On Sat, Aug 18, 2018 at 10:04 PM Kevin Coleman ***@***.***> wrote: > Why does the unique index not work? > > — > You are receiving this because you were mentioned. > > > Reply to this email directly, view it on GitHub > <#111 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AACNf6UKUdtqtD-YRGtZWeX0T6RmzcIuks5uSPHqgaJpZM4WC1Bk> > . >

[KevinColemanInc]

that is so lame. Yes, please add a note. I spent 3 hours poking at this gem trying to figure out why inserting 60k rows took 30 minutes on my dev machine.

The only other thing that was slow was the batch not batching the network calls.

[pnomolos]

What’s your setup? It’s very curious that it’s taking that long - even on my 2011 MBP it’s faster than that. If every call is over the network, though, that would make sense.

[KevinColemanInc]

I misread the csv file. When I opened it up in "Apple Numbers", ignored the warning of "there are 1,400,000 rows, but we are only going to show 65k".

On my 2016 MBP with pg running locally and 16GB ram, 1.4m records with use_pg_native? as true takes 26 minutes, which is more understandable.

===

I made #112 to add a warning if use_pg_native? is false

[pnomolos]

Closed via #112