sql: investigate circular dependency restrictions between udfs and relations

[rharding6373]

The following example in logic tests fails in CRDB due to circular dependency check:

> CREATE TABLE t_circle(a INT PRIMARY KEY, b INT);
> CREATE FUNCTION f_circle() RETURNS INT LANGUAGE SQL AS $$ SELECT a FROM t_circle $$;
> ALTER TABLE t_circle ADD CONSTRAINT ckb CHECK (b + f_circle() > 1);

ERROR: error executing StatementPhase stage 1 of 1 with 3 MutationType ops: *scop.AddTableConstraintBackReferencesInFunctions: &{{{}} 104 2 [105]}: cannot add dependency from descriptor 104 to function f_circle (105) because there will be a dependency cycle

However, this is inconsistent with postgres, which allows this behavior:

CREATE TABLE t_circle(a INT PRIMARY KEY, b INT);
CREATE FUNCTION f_circle() RETURNS INT LANGUAGE SQL BEGIN ATOMIC; SELECT a FROM t_circle; END;
ALTER TABLE t_circle ADD CONSTRAINT ckb CHECK (b + f_circle() > 1);
\d t_circle;
--              Table "public.t_circle"
--  Column |  Type   | Collation | Nullable | Default 
-- --------+---------+-----------+----------+---------
--  a      | integer |           | not null |  
--  b      | integer |           |          | 
-- Indexes:
--     "t_circle_pkey" PRIMARY KEY, btree (a)
-- Check constraints:
--     "ckb" CHECK ((b + f_circle()) > 1)
INSERT INTO t_circle VALUES (0,0);
INSERT INTO t_circle VALUES (1,0);
-- ERROR:  23514: new row for relation "t_circle" violates check constraint "ckb"

This came up as part of auditing CRDB's use of pg error codes against postgres #107369. If this discrepancy is expected, please add an error code to the test and a note.

Jira issue: CRDB-30368

[rafiss]

Although it's a divergence, we think it's better to show the error earlier, rather than waiting until the INSERT occurs. But we should improve the error message and add a PG Code.

[chettriyuvraj]

New here! Can I pick this up?

[zyf123123]

Hello @chettriyuvraj , If you are not working on this one im gonna to take up this and try :)

[rharding6373]

I don't think there is an open PR on this one. You're welcome to pick this up. Thank you for contributing!

[zyf123123]

I have already commit a PR for this issue, please review my code. Thank you!