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

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

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

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

 

@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