Re: DISCARD ALL ; stored procedures

* Robert Haas (robertmhaas(at)gmail(dot)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