BulkCreate upsert not working
I'm using a
BulkCreate action but it seems not to upsert correctly:
69 Replies
I also tried disabling
Carbonite with same result.
Sorry for using screenshots but it was the easiest way to show the problem.When you say not working what do you mean?
It doesn’t return results by default, that’s an option you have to pass
I'm trying out the bulk creates with upserts and returned results for my staging seeds file, working well so far. 🙂
And I have to say, it's a fantastic API for that purpose. 🚀
I'm passing
return_records?: true and still it's not returning the upserted record and the product record is not updatedDo you have
stop_on_error?: true? Because otherwise it might not display an error that's causing a problem (that's been my experience).Oh interesting…that sounds like a bug
@frankdugan3 do you have a reproduction for the errors not appearing that you mentioned?
I don't remember the specific circumstance... I think it was on initial create when giving some bad fields as params... If I run into it again I'll create an issue now that I know it should error.
it should return the errors at least 🙂
Yeah, I may have had both those options off when that happened. lol
@frankdugan3 you are right with
stop_on_error?: true I have the error still there is something not working as I expect (but maybe as it should 😆 )
I have a :create action (also used for upsert) for a Product that needs a data_in and a category_id relationships.
there is an API call used only to update product quantities that returns a list of %{sku_code: "MAGP0.6.2", quantity: 14.0}
I have a :unique_sku_code identity in product
I add data_in_id and category_id making data become a list of [%{category_id: "b77fa8c6-330e-422f-8ee5-84277096821b", data_in_id: "221408ba-ff99-46ce-a01d-bccacc3579c7", quantity: 14.0, sku_code: "MAGP0.6.2"}]
I would expect this bulk_create to only update the product quantity but it gives me an error:
to update only the product quantity instead it gives me an error where it is trying to update all the fields (setting nil to some needed ones).
Do I need to pass also the Product id to make the upsert work even if I have the :unique_sku_code identity?
Or am I using the upsert in an unproper way?okay so I need to make sure that error shows up even if
stop_on_error? is falseapart from error rising am I using the upsert correctly and if so why isn't the poduct quantity updated?
I don't see
:unique_sku_code in your create data: How would it be able to compare it against the existing row if that's the identity field? :thinkies:
Oh, is that the name your identity for :sku_code? I wonder if it wants the field name instead.this is the
:unique_sku_code identity
I'm not sure because I generally name my single-key identities the same as the field.
ok I can try that easily
no I have the same error giving
:sku_code as the upsert_identityokay so I made some small fixes, commented about in the announcement for bulk creates
specifically we were actually not honoring the
return_errors? option, and it was supposed to default to false
which is technically a minor breaking change but this is a new-ish feature and I'd rather not go through the whole rigamarole of the breaking change process for it.
lemme look over this again and see if I can spot the issue
also @tommasop#2001 are you on the latest ash and ash_postgres?I am on main
in both
👌
Actually I don’t think I’ve seen the bulk insert generated sql statement yet
Is that in the logs? Did I just miss it?
If we can see why that’s wrong the fix should be pretty clear
I'm using a list with a single product to test the behavior and with a
LOG_LEVEL=debug this is what I see in iex
🤔
THat is pretty strange
Usually there is a log for the actual query
Try doing this
I'm wondering if the thrown error is causing a crash or somethign
and its not getting to log the initial query attempt
perhaps you can get query logs from the database somehow?
We have it working in
ash_postgres tests, producing queries like:
and to be 100% sure you've updated to latest ash and ash_postgres main with mix deps.update ash ash_postgres? I ask that question a lot, but its always worth asking 😆 Sorry if its repetetive.double checked it and I have the latest ash and ash_postgres
Should I be able to upsert with
:id as the identity?
IIRC that is the default behavior
@tommasop#2001 can you see if it works if you use the id to upsert (by not providing an
upsert_identity?)of course
it gives me the same error
this is the collection
I wonder
I wonder if postgres is validating the insert before trying to apply the on conflict?
like "this insert would fail, and might be an insert"
can you try filling in the required fields in your input?
And seeing if that creates a row
or if it updates
good idea
with all the needed fields it works but it updates all the fields:
the behavior is consistent if I use
id or another identity (:unique_sku_code)are you sure its updating all the fields and not just creating a new record?
if its updating more than the
upsert_fields then something very very very strange is happening
ohhhhh
😢
I think I found it
maybe
Yeah, this isn't your fault, sorry
actually...okay well, I found a place that is definitely problematic
and maybe we're looking somewhere along these lines
its not a manual create action is it?
what happens if you do this in iex
Do you get true?yes
😢
thought I had figured it out
I fixed something, but I don't think it will help you
but try pulling main anyway
ok thanks
If this fixes it then we have some more investigating to do as to how you even got into this case anyway. But its probably not it unfortunately
lets double check this: https://discord.com/channels/711271361523351632/1110978687203016764/1111312811247804457
i.e use a static id and do your upsert twice
can I see the
:create action?Seems pretty standard
it's not upserting but creating new records
even when the id matches?
Do you have a
default_accept?
Is sku_code writable?
is id writable?sku_code is writable, and no default_accept?, id is writeable🤔 🤔 🤔 🤔
okay, so I think I need to see like a complete script or something as well as the resource (in an ideal world, this would be reproduced in a test in
ash_postgres)I can try to write a minimal reproduction test in
ash_postgresthat would be amazing 🙂
will do my best 🙂
I'm trying to do a bulk insert w/ a managed relationship like this:
Which produces this error:
Is that supported, or should I be doing separate inserts and just directly manage the
:type_id?I think its fine, but what are you passing as opts?
it looks like something is getting
type instead of type_id perhaps your identity is wrong?
If I use
:type_id instead of :type, it doesn't upsert the types (vendors have already been created without type) and also doesn't create any errors.
This is the query w/ :type_id:
Are you on the latest ash? Just released recently?
add
return_errors?: trueerror_count: 0, errors: [], same otherwise.so its doing like...nothing?
Nothing w/ the type or type_id, but all the other attributes are creating/upserting. To clarify: the types have not been created first, attempting to leverage the
manage_relationship. Totally get it if that's not supported.It should be 🙂
Could you try to reproduce in a test on ash_postgres?
It was a simple test, you want it as a PR?
yes please ❤️
will take a look at it tomorrow
I'm setting up the test and it seems the problem arises when you have attributes with
allow_nil? false and without a default
I will set up a test with different resources because otherwise I'll break all other tests or let me know if there is a better wayLatest release includes multiple bulk create fixes
Updated to latest releases of everything, getting a new error now. This:
Results in this:
Here the error stays the same
I added a test with a
Manager resource which belongs to Organization the test passes for creation but fails for upsert
hope the test makes sense 🙂Will look into both tonight or tomorrow, thank you both for the tests and patience 🙂
@tommasop#2001 alright, so, here is the deal
that is actually not supported 🙂
INSERT INTO null constraints are checked first
So because it might actually do a create, this won't work
You'll need to use ecto to do your update statement and/or wait until bulk updates come for Ash
However, Ash should have called that invalid before you got there actually
instead of a postgres error
I've fixed that issue. It was a missing required check in the bulk create logic.
@frankdugan3 I don't know whats going on w/ that error....seems like the error_count is somehow leaking out?
Or maybe something in here...:
okay, found it
Something in your request is failing @frankdugan3 but the error message was being swallowed
So it might still be broken, but we should get more info 🙂OK, latest of of everything. This:
Results in this:
If I do this:
I actually get a proper error, and I can see I had a changeset validation problem. So I think we're making progress on the errors! 😄
So the upsert may be working once I fix my data input. Looks like it's just error handling that has a few edge cases.
Okay, I think I found a place that could cause errors to be something like
[nil]
which would yield that error
Just pushed a change up to main that ought to help with thatOK, making progress, but ran into a new error. 😄
When doing this variety of bulk create:
with this action:
I get this error:
^ This is on the latest released version.
Also, still getting this exact error for this kind of bulk create. ^
😢 I might need another test reproduction for this one
NP, will PR a few when I get a chance.
@frankdugan3 you're in luck, a client encountered this issue
have a fix incoming, at least for the specific issue raising on
[]Oh, awesome! Sorry I hadn't gotten around to a repro PR, was using this for optional improvements to staging seed, had some other priorities come up ahead of it. 😅
Sadly the updates to Ash/AshPostgres didn't change anything for my two cases. Will see if I can get to a repro PR today or tomorrow. 😢
Hmmmm