[HACKERS] planner issue with constraint exclusion

[Joshua D. Drake]

Hello,

I ran into this problem recently:

https://projects.commandprompt.com/public/replicator/pastebin?show=f1288d4d8%0D

Of the functions the only one that will use constraint_exclusion is the
one that explicitly passes the date value. I kind of get why except for
the one that uses EXECUTE. As EXECUTE has to replan the query, shouldn't
it be able to use constraint_exclusion?

(text also below for those that don't want to fire up a browser)

CREATE OR REPLACE FUNCTION test_search1() RETURNS integer AS $$
  DECLARE
    temp date;
    tmp integer;
  BEGIN
    SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
    SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE 
lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = date(timehit);
    RETURN tmp;
  END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search2() RETURNS integer AS $$
  DECLARE
    temp date;
    tmp integer;
  BEGIN
    SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
    SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE 
lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '2006-07-17';
    RETURN tmp;
  END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search3() RETURNS integer AS $$
  DECLARE
    temp date;
    tmp integer;
  BEGIN
    SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
    SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE 
lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = temp;
    RETURN tmp;
  END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search4() RETURNS integer AS $$
use strict;
use warnings;
my $sql = "SELECT date(timehit) AS timehit FROM foo51 WHERE unit_id = 1 LIMIT 
1";
my $rv = spi_exec_query($sql);
return undef if( ! defined $rv->{rows}[0]->{'timehit'} );
my $date = $rv->{rows}[0]->{'timehit'};

$sql = "SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = 
l.unit_id AND lsd.a_serv_id = 1 AND day = '$date'";
$rv = spi_exec_query($sql);

return undef if( ! defined $rv->{rows}[0]->{'unit_id'} );
my $unit_id = $rv->{rows}[0]->{'unit_id'};

return $unit_id;
$$ LANGUAGE 'plperlu' STABLE;

CREATE OR REPLACE FUNCTION test_search5() RETURNS integer AS $$
  DECLARE
    temp date;
    tmp integer;
  BEGIN
    SELECT timehit INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
    EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE 
lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || '''';
    RETURN 1;
  END
$$ LANGUAGE plpgsql STABLE;


-- 
PostgreSQL
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers