Re: simplifying foreign key/RI checks - Mailing list pgsql-hackers

On Tue, Jan 19, 2021 at 2:47 AM Zhihong Yu <zyu@yugabyte.com> wrote:
>
> Hi,
> I was looking at this statement:
>
> insert into f select generate_series(1, 2000000, 2);
>
> Since certain generated values (the second half) are not in table p, wouldn't insertion for those values fail ?
> I tried a scaled down version (1000th) of your example:
>
> yugabyte=# insert into f select generate_series(1, 2000, 2);
> ERROR:  insert or update on table "f" violates foreign key constraint "f_a_fkey"
> DETAIL:  Key (a)=(1001) is not present in table "p".

Sorry, a wrong copy-paste by me.  Try this:

create table p (a numeric primary key);
insert into p select generate_series(1, 2000000);
create table f (a bigint references p);

-- Unpatched
insert into f select generate_series(1, 2000000, 2);
INSERT 0 1000000
Time: 6527.652 ms (00:06.528)

update f set a = a + 1;
UPDATE 1000000
Time: 8108.310 ms (00:08.108)

-- Patched:
insert into f select generate_series(1, 2000000, 2);
INSERT 0 1000000
Time: 3312.193 ms (00:03.312)

update f set a = a + 1;
UPDATE 1000000
Time: 4292.807 ms (00:04.293)

> For v1-0002-Avoid-using-SPI-for-some-RI-checks.patch :
>
> +        * Collect partition key values from the unique key.
>
> At the end of the nested loop, should there be an assertion that partkey->partnatts partition key values have been found ?
> This can be done by using a counter (initialized to 0) which is incremented when a match is found by the inner loop.

I've updated the patch to add the Assert.  Thanks for taking a look.

--
Amit Langote
EDB: http://www.enterprisedb.com