Unexpected update count received (Actual: 0, Expected: 1). - Unable to update view by using sql rules 关注
I am a happy user of your Intellij IDE. I am working on PostgreSQL database right now and I like the idea to be able to update views. Since I am using a View of multiple tables, I use rules to do a proper update.
However, underlying jdbc on in the Intellij seems to be a bit strick regarding returned rows.
E.g. if I wanted to create the following rule:
create or replace rule my_rule
AS ON UPDATE to public.my_view
DO INSTEAD NOTHING;
I would get an error:
Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back.
psql runs completely fine on it. Even if I run a query from the query console, it all works fine. E.g.
update public.my_view set category_name = 'new_name' where category_pk = 1;
It seems that the view graphical interface has a problem with it.
Thank you for help,
Thanks for reporting this issue.
I am trying to reproduce this issue with a SQL like your provided one but failed to reproduce it.
Would it be possible to share a sample DDL of your `public.my_view` for checking?
Also, what's your current IDE version and the PostgreSQL driver version: https://www.jetbrains.com/help/datagrip/configuring-database-connections.html#using-jdbc-drivers
here is the code that does not work.
First, create two simple tables and connect them together through a separate table:
Then you create a view for those two tables:
You also insert some values:
Connection table looks like this:
Finally, you create one of these rules, I just want to show you that it does not work on various scenarios.)
Now, if you open full_name view in the IDE, it will look like this.
If you try to update any row, you will get an error:
But if you run an update from the console, it just works fine:
Thank you and best regards,
One more thing, please. If you fail to reproduce it, please send me your version of the tables and views and I will try your code.
My PostgreSQL driver is 42.5.0 and IntelliJ IDEA 2022.2.4 (Ultimate Edition)
@Vlcek Vv Really thanks for the detailed steps and that's really helpful.
I could reproduce this.
I have created a bug report here: https://youtrack.jetbrains.com/issue/DBE-17423/Unable-to-update-view-by-using-sql-rules-when-using-PostgreSQL-due-to-Unexpected-update-count-received-Actual-0-Expected-1-error