Repository navigation
Spanner - Insert_Or_Update does not work as documented ? #2425
Description
Hi,
I have a use case that requires to update just one column of a table. To do that, i tried to use insert_or_update as documentation says it will update some columns and preserve others' values. But i get an error below, says i am trying to insert new row without not null columns? Why does not it checks row already exist ?
What am i missing here ? Here is my mutation build code too. I get primary keys and map them to mutation as is. First 4 fields are all part of primary key.
Mutation.newInsertOrUpdateBuilder("orders")
.set("appkey").to(result.getString("appkey"))
.set("GlobalId").to(result.getString("globalid"))
.set("Id").to(result.getLong("id"))
.set("TicketId").to(result.getLong("TicketId"))
.set("finalprice").to(result.getDouble("price"))
.build();
public static final Mutation.Op INSERT_OR_UPDATE
Like INSERT, except that if the row already exists, then its column values are overwritten with the ones provided. Any column values not explicitly written are preserved.
io.grpc.StatusRuntimeException: FAILED_PRECONDITION: A potentially-new row in table Orders does not specify a non-null value for these NOT NULL columns: AccountTransactionTypeId, CalculatePrice, CreatedDateTime, DecreaseInventory, DepartmentId, etc..
at io.grpc.Status.asRuntimeException(Status.java:543) ~[grpc-core-1.4.0.jar:1.4.0]
at io.grpc.stub.ClientCalls$UnaryStreamToFuture.onClose(ClientCalls.java:442) ~[grpc-stub-1.4.0.jar:1.4.0]
at io.grpc.ForwardingClientCallListener.onClose(ForwardingClientCallListener.java:56) ~[grpc-core-1.4.0.jar:1.4.0]
at com.google.cloud.spanner.spi.v1.SpannerErrorInterceptor$1$1.onClose(SpannerErrorInterceptor.java:100) ~[google-cloud-spanner-0.22.0-beta.jar:0.22.0-beta]
Activity
shinfan commentedon Sep 14, 2017
The error message is complaining that the following columns are required but not set:
AccountTransactionTypeId, CalculatePrice, CreatedDateTime, DecreaseInventory, DepartmentId etc...
halilduygulu commentedon Sep 14, 2017
shinfan commentedon Sep 14, 2017
@vkedia This looks like a potential bug. Can you take a look? (since you implemented the Spanner client)
vkedia commentedon Sep 14, 2017
This error message indicates that the row does not exist with the given primary keys so it is trying to insert a new row. Are you sure this row exists?
Can you try just using update and see what error do you get?
halilduygulu commentedon Sep 15, 2017
Hey vkedia,
I tried again now. If I just change newInsertOrUpdateBuilder to newUpdateBuilder it works. So the row exists in the database and finalprice column is updated correctly. But i do not understand why insert_or_update does not work on the same row.
Using insert_or_update gives same error.
vkedia commentedon Sep 16, 2017
@halilduygulu This is actually working as intended. InsertOrUpdate mutation must satisfy the constraint of Insert therefore you need to provide values for all non null columns. I will update the documentation.
halilduygulu commentedon Sep 18, 2017
Thank you.
Then i should use "update" instead of "insert_or_update" to update only one or two columns right ?
https://cloud.google.com/spanner/docs/reference/rpc/google.spanner.v1#google.spanner.v1.Mutation
vkedia commentedon Sep 18, 2017
That's correct.
Update documentation of INSERT_OR_UPDATE mutation
Update documentation of INSERT_OR_UPDATE mutation (#2447)
abhishek-sehgal954 commentedon Sep 11, 2019
Suppose I want to insert new rows if they are not already present. I was using upsert. but upsert will update the column values for rows already inserted. I want to preserve the column values if they are already present in the DB
Is there any workaround for that?
@vkedia
StuAtGit commentedon Dec 3, 2019
@abhishek-sehgal954
What you're looking for is essentially
ON DUPLICATE KEY UPDATE....Google's api makes it kinda ugly, but that's what read write transactions are for:
https://stackoverflow.com/questions/54100837/automatic-created-on-not-updated-on-timestamp-on-insert-not-update-in-google
deps: update dependency com.google.cloud:sdk-platform-java-config to …
deps: update dependency net.jqwik:jqwik to v1.8.3 (#2425)
chore(deps): update dependency com.google.cloud:libraries-bom to v26.…
chore(deps): update dependency com.google.cloud:libraries-bom to v26.…
test: add test to ensure that feature flags are sent during a ping an…