Обсуждение: Constraint using a SQL function executed during SELECT

Hello,

Is there a way to execute a SELECT on a table having a constraint that
uses a non-working SQL function?

I know the easiest way would be to fix the function, but I'm the
database administrator, not the owner, and I need to do a COUNT(*) on
each table.

Here's a minimal use case:

CREATE TABLE t1 (
     id integer
);

CREATE SCHEMA rename_me;

CREATE TABLE rename_me.t2 (
     id integer
);

CREATE FUNCTION f(id integer) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
SELECT true
FROM rename_me.t2

$_$;

ALTER TABLE ONLY t1 ADD CONSTRAINT c EXCLUDE (id WITH =) WHERE ((f(id)
IS NOT TRUE));

ALTER SCHEMA rename_me RENAME TO renamed;

SELECT * FROM "t1";

returns:

ERROR:  relation "rename_me.t2" does not exist
LINE 3: FROM rename_me.t2
              ^
QUERY:
SELECT true
FROM rename_me.t2


CONTEXT:  SQL function "f" during inlining

Tested on 9.5.2 (and an older 9.0).

--
Cyril B.

On 7/19/16 7:43 AM, Cyril B. wrote:
> Hello,
>
> Is there a way to execute a SELECT on a table having a constraint that
> uses a non-working SQL function?
...
> ALTER TABLE ONLY t1 ADD CONSTRAINT c EXCLUDE (id WITH =) WHERE ((f(id)
> IS NOT TRUE));
...
> ERROR:  relation "rename_me.t2" does not exist
...
> CONTEXT:  SQL function "f" during inlining

In this example, you should be able to avoid that by setting
constraint_exclusion=off.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 07/19/2016 03:51 PM, Jim Nasby wrote:
> In this example, you should be able to avoid that by setting
> constraint_exclusion=off.

Sorry I forgot to mention I had already tried that, to no avail.

--
Cyril B.

"Cyril B." <cbay@excellency.fr> writes:
> On 07/19/2016 03:51 PM, Jim Nasby wrote:
>> In this example, you should be able to avoid that by setting
>> constraint_exclusion=off.

> Sorry I forgot to mention I had already tried that, to no avail.

Yeah, constraint_exclusion won't help, because this function isn't
in a CHECK constraint.  It's in an index definition, and the planner
will always examine those.

My first reaction on looking at the example was that this particular
function shouldn't be a candidate for inlining, because it's not just
"SELECT expression".  But I see that inline_function() runs parse analysis
before it checks for presence of a FROM clause, and that's when the
failure happens.  Seems like it might be worthwhile to apply some of those
checks on the raw parsetree so that we could skip parse analysis when
there are obvious showstoppers like a FROM clause.  This wouldn't
constitute a general solution to your problem, of course, but it would
save some useless cycles in planning.

            regards, tom lane