Non PK UUID column and `returning`

[andyatkinson]

Hello. I wanted to take advantage of RETURNING to save a round-trip to the database for an area that is particularly costly. However activerecord-import which we're using (just bumped to latest) in the Rails app (Rails 5.x) seems to take over the import method that Active Record provides that would let me specify a returning clause which I initially intended to use, then stumbled into activerecord-import.

1. Is there a way around activerecord-import definition of import of I want to try the Active Record one? Am I missing something on that?

2. The other problem is that I see activerecord-import supports returning but I'm only seeing examples with a primary key in past issues for the project. We're using the uuid-oosp extension to generate a UUID. Are non-PK columns supported or different in some way? We have this UUID generation happening as the column default. When I insert one record and query it again I see it there in the DB. I see use activerecord-import to insert a single record in a list, it says it is inserted but the in-memory instance does not have the value set, it's not in the returning results, and if I query again I see the column appears null.

e.g. Successful insert of single row where we have external_id as a UUID non-PK column. But the results are a single nil item and I expected it to be the value for external_id.

My::Model.import([instance], returning: :external_id)

=> #<struct ActiveRecord::Import::Result failed_instances=[], num_inserts=1, ids=[1], results=[nil]>

[jkowens]

1. Is there a way around activerecord-import definition of import of I want to try the Active Record one? Am I missing something on that?

I'm not sure I understand. ActiveRecord doesn't have an import method of its own.

2. The other problem is that I see activerecord-import supports returning but I'm only seeing examples with a primary key in past issues for the project. We're using the uuid-oosp extension to generate a UUID. Are non-PK columns supported or different in some way? We have this UUID generation happening as the column default. When I insert one record and query it again I see it there in the DB. I see use activerecord-import to insert a single record in a list, it says it is inserted but the in-memory instance does not have the value set, it's not in the returning results, and if I query again I see the column appears null.

Can you show the values for instance before it is imported? I want to confirm external_id is actually populated. Also can you check your Rails log for the generated SQL that is executed?

[andyatkinson]

Pardon, coffee has not kicked in. I agree Active Record does not have an import method so that was my bad. The method I was trying to use instead of creating an object normally was insert but now I'm realizing it's defined on Active Record Relation objects. https://apidock.com/rails/v5.0.0.1/ActiveRecord/Relation/insert

What I wanted was to do in Ruby what I'd do with a SQL statement where I specified returning external_id on the end so that I could carry on with making sure that value set by the DB is present on the instance, since we pass it on to additional code.

It matches up pretty close to here: https://dev.to/jbranchaud/a-few-methods-for-returning-default-values-when-creating-activerecord-objects-1457

I think activerecord-import might be better than using a manual SQL insert statement here. But I'm looking at the SQL that is generated and it does look like NULL is being set on the VALUES portion of the insert statement. Since our column allows null maybe this is why? I changed the model to "Foo" below and removed extraneous stuff.

This be like: Foo.import([instance], returning: :external_id)

D, [2021-12-09T11:37:02.924023 #70152] DEBUG -- :  Foo Create Many (1.0ms)  INSERT INTO "foos" ("id","external_id","created_at","updated_at") VALUES (nextval('public.foos_id_seq'),NULL,'2021-12-09 17:37:02.922245','2021-12-09 17:37:02.922245') RETURNING "id", "external_id"

Here is a SQL statement I'd run manually where I'm not specifying the external_id column at all. This is the non-PK UUID column that gets a default UUID value:

insert into foos (id, created_at) values (1, CURRENT_TIMESTAMP) returning external_id;

-[ RECORD 1 ]-------------------------------------
external_id | 8f335a8d-0902-4936-91db-847ec0ea1733

So maybe I'm just doing this wrong but for a column that has a generated UUID value, should I be able to use import to insert 1 or more of them and expect the generated value to be accessible via the returning option?

Many thanks!

[andyatkinson]

Our use case here is that we're inserting into a partitioned table that is heavily written to, and the immediate lookup afterwards to fetch the value because normal Active Record create doesn't populate it, is actually pretty costly as it involves scanning all the partitions. So my idea was to simply try and return it using returning after the insert so that we didn't need to look it up again.

[jkowens]

Ohh I understand now, so external_id is a generated UUID value. I guess I didn't follow that part at first. So I think the problem is that since you're using models to insert, the external_id column is being included with a NULL value in the insert statement.

To work around that you could try using an array or arrays or array of hashes to control which columns get included in the insert SQL.

[andyatkinson]

Thank you for taking a look and thinking on this @jkowens - as a workaround for now I'm setting the UUID in code instead of the DB. But I'll give what you're saying a shot. Your explanation that the instances are already created in a list and have null/nil values makes sense.

[Amnesthesia]

Possibly fixed by this #802