Skip site navigation (1) Skip section navigation (2)

Re: DISCARD ALL ; stored procedures

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: DISCARD ALL ; stored procedures
Date: 2011-01-07 13:43:55
Message-ID: 20110107134355.GO4933@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackers
* 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

In response to

Responses

pgsql-hackers by date

Next:From: Itagaki TakahiroDate: 2011-01-07 13:47:37
Subject: Re: [COMMITTERS] pgsql: New system view pg_stat_replication displays activity of wal sen
Previous:From: ZotovDate: 2011-01-07 13:34:28
Subject: Re: join functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group