Обсуждение: 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