Re: DISCARD ALL ; stored procedures - Mailing list pgsql-hackers

* Robert Haas (robertmhaas@gmail.com) wrote:
> If DISCARD ALL doesn't flush this stuff, I'd consider that an outright
> bug.  Does it?

No, it does not, based on my testing against 8.4.5:

Simple function:

----------------------------
CREATE OR REPLACE FUNCTION test_func() RETURNS boolean
AS $_$
DECLARE rec RECORD;
BEGIN
   SELECT INTO rec statefp FROM edges LIMIT 1;
   raise notice 'rec: %', rec;
   RETURN TRUE;
END;
$_$ LANGUAGE plpgsql;
----------------------------

Couple of tables, note the CHECK constraints on statefp,which is what the function pulls out:
----------------------------
gis*=> \d tiger_02.addrfn                                Table "tiger_01.addrfn" Column  |         Type          |
               Modifiers                        
----------+-----------------------+------------------------------------------------------gid      | integer
 | not null default nextval('addrfn_gid_seq'::regclass)arid     | character varying(22) | linearid | character
varying(22)| statefp  | character varying(2)  | not null default '01'::character varying 
Indexes:   "addrfn_pkey" PRIMARY KEY, btree (gid)
Check constraints:   "addrfn_statefp_check" CHECK (statefp::text = '01'::text)
Inherits: tiger_us.addrfn
----------------------------

----------------------------
gis*=> \d tiger_02.addrfn                                Table "tiger_02.addrfn" Column  |         Type          |
               Modifiers                        
----------+-----------------------+------------------------------------------------------gid      | integer
 | not null default nextval('addrfn_gid_seq'::regclass)arid     | character varying(22) | linearid | character
varying(22)| statefp  | character varying(2)  | not null default '02'::character varying 
Indexes:   "addrfn_pkey" PRIMARY KEY, btree (gid)
Check constraints:   "addrfn_statefp_check" CHECK (statefp::text = '02'::text)
Inherits: tiger_us.addrfn
----------------------------

See the results:
gis=> \i ./qs.sql
CREATE FUNCTION
gis*=> set search_path to tiger_01;
SET
gis*=> set search_path to tiger_01,sfrost;
SET
gis*=> select test_func();
NOTICE:  rec: (01)test_func
-----------t
(1 row)

gis*=> commit;
COMMIT
gis=> discard all;
DISCARD ALL
gis=> set search_path to tiger_02,sfrost;
SET
gis*=> select test_func();
NOTICE:  rec: (01)test_func
-----------t
(1 row)

The addrfn table in the tiger_02 schema certainly can not have a
statefp of 01 due to the CHECK constraint (and it believe me, it's
right).

To be honest, I agree it's a bug, and I would *love* to have it
back-patched, but I could see an argument for it to be something
explicit from DISCARD PLANS; and would hence require a grammar
change which isn't something we'd typically back-patch.

Making it part of DISCARD PLANS; and back-patching it to 8.3 where
DISCARD was introduced would be awesome for me. :)
Thanks,
    Stephen