Re: SQL:2011 application time

On Sun, Nov 19, 2023 at 1:24 PM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)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 PM jian he <jian(dot)universality(at)gmail(dot)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 != numpks) before if (is_temporal) {numfks +=
> > 1;}, So I changed the code order to make the error report more
> > consistent.
>
> Since we do numfks +=1 and numpks +=1, 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 = '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 = 'lb_to_up';
return;
end
$$ language plpgsql;
select * from demo1();