Unexpected update count received (Actual: 0, Expected: 1). - Unable to update view by using sql rules

Answered

Hi, 

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, 

Vaclav

0
4 comments
Hi,

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
0

Hi Lejia,

here is the code that does not work. 

First, create two simple tables and connect them together through a separate table:

create table _testing.first_name (
id bigserial primary key,
first_name varchar(255)
);

create table _testing.last_name (
id bigserial primary key,
second_name varchar(255)
);

create table _testing.first_to_last_name(
first_id bigint,
last_id bigint,
constraint fk_first_id foreign key (first_id) references _testing.first_name(id),
constraint fk_last_id foreign key (last_id) references _testing.last_name(id),
primary key (first_id, last_id)
);

Then you create a view for those two tables:

create view _testing.full_name as
select
first.id as first_id,
first.first_name,
last.id as last_id,
last.second_name
from _testing.first_name first
left join _testing.first_to_last_name ftln on first.id = ftln.first_id
left join _testing.last_name last on ftln.last_id = last.id;

You also insert some values:

insert into _testing.first_name(first_name) VALUES ('Thomas');
insert into _testing.first_name(first_name) VALUES ('Peter');
insert into _testing.first_name(first_name) VALUES ('George');
insert into _testing.first_name(first_name) VALUES ('John');

insert into _testing.last_name(second_name) VALUES ('Jefferson');
insert into _testing.last_name(second_name) VALUES ('Pan');
insert into _testing.last_name(second_name) VALUES ('Lukas');
insert into _testing.last_name(second_name) VALUES ('Doe');

Connection table looks like this:

first_id | last_id
---------+------
   1     |    1
   2     |    2
3     | 3
4     | 4

Finally, you create one of these rules, I just want to show you that it does not work on various scenarios.)

create or replace rule rule1
AS ON UPDATE to _testing.full_name
DO INSTEAD
insert into _testing.last_name(second_name) VALUES ('BBB');

create or replace rule rule2
AS ON UPDATE to _testing.full_name
DO INSTEAD NOTHING;

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:

update _testing.full_name set first_name = 'a' where first_id = 1;

Thank you and best regards, 

Vaclav

0

Hey Lejia,

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)

Best regards, 

Vaclav

 

0

@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

Please watch/vote for it so you can receive notifications about this issue when there are any updates.
0

Please sign in to leave a comment.