MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <831f17ba-ff36-1380-a475-a7cd2c65a89c@enterprisedb.com> <ab4cf49c-9839-3426-5114-f596fa9ee76b@illuminatedcomputing.com>
 <6f010a6e-8e20-658b-dc05-dc9033a694da@eisentraut.org> <7d44dade-ff31-5eef-80ac-78ed838066f6@postgresfriends.org>
 <88dadcc1-6652-ff7d-264d-73906a53dee9@illuminatedcomputing.com>
 <CACJufxHD2kv6hCy8yjVk486+d5rcuJS2XxkNH80g9Ui6Rz81Cg@mail.gmail.com>
 <CACJufxFTqiag-4fDPWJLxwwX+2gcZZWJPM9GyhPYD0tH=uM+jQ@mail.gmail.com>
 <CA+renyV4RxkWx57Vi5k_k0d7mkT7+F2kHYuKkwNPBaSOCaotFA@mail.gmail.com>
 <CACJufxG7DKLhyJgBaFttdXZe-4dOVu3NVED+4sdegdGs6R7JdQ@mail.gmail.com>
 <CACJufxFxQH5NxD9UDvbcRCPtaSga+y0+Wwt54PA4KahLwhahOA@mail.gmail.com>
 <5157e85a-6007-27a7-853a-5601d9c3722f@illuminatedcomputing.com>
 <CACJufxGCWDSK2pYMwNdsyDGk6skbP_L5yvSBK67L6pxbGUF8OQ@mail.gmail.com>
 <5a16a59a-d497-7d72-07aa-0afc09d5193a@illuminatedcomputing.com>
 <CACJufxG_iZc8hbdcfjkU6q81kLf4R2Fp=VyDZbyhocuTbMNVEw@mail.gmail.com>
 <b8ef855d-216b-5499-6a6e-ec883a32bd96@illuminatedcomputing.com>
 <CACJufxGnN6-513copYkpgJia6Qq7vKtO6q7cigUpA=4Evozppg@mail.gmail.com>
 <CACJufxGsjGXbKT-qbPYtspLxN74B8Q8dBXRZV+HfgqC0hh-ZNQ@mail.gmail.com>
 <CACJufxGt4oYfa7UN2eAXymgOKH1irbhC5eY_oNkkdj30w-fefw@mail.gmail.com>
 <CACJufxExyO_mN8-OkgaTdgqmE5OfE=RBoQXizf=8uRj4c-pAYw@mail.gmail.com>
 <CACJufxGeUsmxOhnd2760kBv-hUWRwCy7A0s45pGSPTe8DJVTGw@mail.gmail.com> <CA+renyWWMWOMgUiCV6zod+ZCaqUwNM=8PiD+iW7ogLjkwQaLJw@mail.gmail.com>
In-Reply-To: <CA+renyWWMWOMgUiCV6zod+ZCaqUwNM=8PiD+iW7ogLjkwQaLJw@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Mon, 20 Nov 2023 14:57:37 +0800
Message-ID: <CACJufxErS01_WRY1Wf35mquPcVZm1P1jyBbu+wsWG_bWxbUNtQ@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxErS01_WRY1Wf35mquPcVZm1P1jyBbu%2BwsWG_bWxbUNtQ%40mail.gmail.com>
Precedence: bulk

On Sun, Nov 19, 2023 at 1:24=E2=80=AFPM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> Thank you for continuing to review this submission! My changes are in
> the v18 patch I sent a few days ago. Details below.
>
> On Sun, Oct 29, 2023 at 5:01=E2=80=AFPM jian he <jian.universality@gmail.=
com> wrote:
> > * The attached patch makes foreign keys with PERIOD fail if any of the
> > foreign key columns is "generated columns".
>
> I don't see anything like that included in your attachment. I do see
> the restriction on `ON DELETE SET NULL/DEFAULT (columnlist)`, which I
> included. But you are referring to something else I take it? Why do
> you think FKs should fail if the referred column is GENERATED? Is that
> a restriction you think should apply to all FKs or only temporal ones?
>

I believe the following part should fail. Similar tests on
src/test/regress/sql/generated.sql. line begin 347.

drop table if exists gtest23a,gtest23x cascade;
CREATE TABLE gtest23a (x int4range, y int4range,
CONSTRAINT gtest23a_pk PRIMARY KEY (x, y WITHOUT OVERLAPS));
CREATE TABLE gtest23x (a int4range, b int4range GENERATED ALWAYS AS
('empty') STORED,
FOREIGN KEY (a, PERIOD b ) REFERENCES gtest23a(x, PERIOD y) ON UPDATE
CASCADE);  -- should be error?
-------

>
> > * you did if (numfks !=3D numpks) before if (is_temporal) {numfks +=3D
> > 1;}, So I changed the code order to make the error report more
> > consistent.
>
> Since we do numfks +=3D1 and numpks +=3D1, I don't see any inconsistency
> here. Also you are making things now happen before a permissions
> check, which may be important (I'm not sure). Can you explain what
> improvement is intended here? Your changes don't seem to cause any
> changes in the tests, so what is the goal? Perhaps I'm
> misunderstanding what you mean by "more consistent."
>

begin;
drop table if exists fk, pk cascade;
CREATE TABLE pk (id int4range, valid_at int4range,
CONSTRAINT pk_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
CREATE TABLE fk (
id int4range,valid_at tsrange, parent_id int4range,
CONSTRAINT fk FOREIGN KEY (parent_id, valid_at)
    REFERENCES pk
);
rollback;
--
the above query will return an error: number of referencing and
referenced columns for foreign key disagree.
but if you look at it closely, primary key and foreign key columns both are=
 two!
The error should be saying valid_at should be specified with "PERIOD".

begin;
drop table if exists fk, pk cascade;
CREATE TABLE pk (id int4range, valid_at int4range,
CONSTRAINT pk_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
CREATE TABLE fk (
id int4range,valid_at int4range, parent_id int4range,
CONSTRAINT fk FOREIGN KEY (parent_id, period valid_at)
REFERENCES pk
);
select conname,array_length(conkey,1),array_length(confkey,1)
from pg_constraint where conname =3D 'fk';
rollback;
------------
I found out other issues in v18.
I first do `git apply` then  `git diff --check`, there is a white
space error in v18-0005.

You also need to change update.sgml and delete.sgml <title>Outputs</title> =
part.
Since at most, it can return 'UPDATE 3' or 'DELETE 3'.

--the following query should work?
drop table pk;
CREATE table pk(a numrange PRIMARY key,b text);
insert into pk values('[1,10]');
create or replace function demo1() returns void as $$
declare lb numeric default 1; up numeric default 3;
begin
    update pk for portion of a from lb to up set b =3D 'lb_to_up';
    return;
end
$$ language plpgsql;
select * from demo1();