Review: prepare plans of embedded sql on function start
Pavel, this patch:
https://commitfest.postgresql.org/action/patch_view?id=624
It applied clean and compiled ok, but I cannot get it to work at all.
$ psql
Timing is on.
psql (9.2devel)
Type "help" for help.
andy=# set plpgsql.prepare_plans to on_start;
ERROR: unrecognized configuration parameter "plpgsql.prepare_plans"
It was also really upset when I added it to my postgresql.conf file.
I hate to split hairs, but the GUC having option on_start and on_demand seems weird. Most everything else is a yes/no. How'd you feel about renaming it to: prepare_plans_on_start = yes/no
But really its not start (start might imply you call the function and it starts executing), its on create, so maybe: prepare_plans_on_create = yes/no
-Andy
On 09/05/2011 05:03 PM, Andy Colson wrote:
Pavel, this patch:
https://commitfest.postgresql.org/action/patch_view?id=624
It applied clean and compiled ok, but I cannot get it to work at all.
$ psql
Timing is on.
psql (9.2devel)
Type "help" for help.andy=# set plpgsql.prepare_plans to on_start;
ERROR: unrecognized configuration parameter "plpgsql.prepare_plans"
Did you add plpgsql to custom_variable_classes? It looks like you might
not have. (I'm not sure why plpgsql switch should require one, though,
especially since we now load plpgsql by default. It might be better just
to call it plpgsql_prepare_on_start.)
cheers
andrew
On 09/05/2011 05:04 PM, Andrew Dunstan wrote:
On 09/05/2011 05:03 PM, Andy Colson wrote:
Pavel, this patch:
https://commitfest.postgresql.org/action/patch_view?id=624
It applied clean and compiled ok, but I cannot get it to work at all.
$ psql
Timing is on.
psql (9.2devel)
Type "help" for help.andy=# set plpgsql.prepare_plans to on_start;
ERROR: unrecognized configuration parameter "plpgsql.prepare_plans"Did you add plpgsql to custom_variable_classes? It looks like you might not have. (I'm not sure why plpgsql switch should require one, though, especially since we now load plpgsql by default. It might be better just to call it plpgsql_prepare_on_start.)
cheers
andrew
Ah, yep, that was the problem, thank you.
-Andy
On 09/05/2011 05:27 PM, Andy Colson wrote:
On 09/05/2011 05:04 PM, Andrew Dunstan wrote:
On 09/05/2011 05:03 PM, Andy Colson wrote:
Pavel, this patch:
https://commitfest.postgresql.org/action/patch_view?id=624
It applied clean and compiled ok, but I cannot get it to work at all.
$ psql
Timing is on.
psql (9.2devel)
Type "help" for help.andy=# set plpgsql.prepare_plans to on_start;
ERROR: unrecognized configuration parameter "plpgsql.prepare_plans"Did you add plpgsql to custom_variable_classes? It looks like you might not have. (I'm not sure why plpgsql switch should require one, though, especially since we now load plpgsql by default. It might be better just to call it plpgsql_prepare_on_start.)
cheers
andrew
Ah, yep, that was the problem, thank you.
-Andy
However I still cannot get it to work.
andy=# set plpgsql.prepare_plans to on_start;
SET
Time: 0.123 ms
andy=# show plpgsql.prepare_plans;
plpgsql.prepare_plans
-----------------------
on_start
(1 row)
andy=# create or replace function test1(a integer) returns integer as $$
andy$# begin
andy$# return b+1;
andy$# end;
andy$# $$ language plpgsql;
CREATE FUNCTION
Time: 16.926 ms
andy=#
Oh... shoot, having gone back and read more closely I realize I didnt understand. I thought the sql would be checked on create. That's not the case.
This is what I'd hopped it was:
create table junk1 (
id serial,
code1 integer,
);
create or replace function test2() returns integer as $$
declare
x integer;
begin
select bob into x from junk1 where id = 4;
return x;
end;
$$ language plpgsql;
I was thinking the create function would immediately return saying, unknown column bob, and not create the function.
So now with the function above, this patch has not helped me at all. I wont get an error until I exec the function. Just like without the patch.
I'm not so sure how helpful that is. What is you use the "if false then ... end if" trick to comment out some old code? You're sill going to check the tables and fields on every exec?
Pavel, is there any way to move all that code to the create function? But, then that would create a dependency where there is not one now. So that would be bad.
How about a new "check function test2()" type of call? I think having the tables/fields checked just once would be better than checking them over and over on ever single execute.
-Andy
Hi Pavel,
I can get:
ERROR: permission denied to set parameter "plpgsql.prepare_plans"
with this script:
set plpgsql.prepare_plans to on_start;
create or replace function test1(a integer) returns integer as $$
begin
return a+1;
end;
$$ language plpgsql;
If test1() exists, then this script works fine:
select * from test1(1);
set plpgsql.prepare_plans to on_start;
create or replace function test1(a integer) returns integer as $$
begin
return a+1;
end;
$$ language plpgsql;
-Andy
Purpose
========
Better test coverage of functions. On first call of a function, all sql statements will be prepared, even those not directly called. Think:
create function test() returns void as $$
begin
if false then
select badcolumn from badtable;
end if;
end; $$ language plpgsql;
At first I thought this patch would check sql on create, but that would create a dependency, which would be bad.
Before, if you called this function, you'd get no error. With this patch, and with postgresql.conf settings enabled, you get:
select * from test();
ERROR: relation "badtable" does not exist
LINE 1: select badcolumn from badtable
^
QUERY: select badcolumn from badtable
CONTEXT: PL/pgSQL function "test" line 4 at SQL statement
The Patch
=========
Applied ok, compile and make check ran ok. It seems to add/edit regression tests, but no documentation.
I tried several different things I could think of, but it always found my bugs. Its disabled by default so wont cause unexpected changes. Its easy to enable, and to have individual functions exempt themselves.
Performance
===========
No penalty. At first I was concerned every function call would have overhead of extra preparing, but that is not the case. It's prepared on first call but not subsequent calls. But that made me worry that the prepare would exist too long and use old outdated stats, that as well is not a problem. I was able to setup a test where a bad index was chosen. I used two different psql sessions. In one I started a transaction, and selected from my function several times (and it was slow because it was using a bad index). In the other psql session I ran analyze on my table. Back in my first psql session, I just waited, running my function ever once and a while. Eventually it picked up the new stats and start running quick again.
Code Review
===========
I am not qualified
Problems
========
I like the idea of this patch. I think it will help catch more bugs in functions sooner. However, a function like:
create function test5() returns integer as $$
begin
create temp table junk(id integer);
insert into junk(id) values(100);
drop table temp;
return 1;
end;
$$ language plpgsql;
Will always throw an error because at prepare time, the temp junk table wont exist. This patch implements new syntax to disable the check:
create function test5() returns integer as $$
#prepare_plans on_demand
begin
...
Was it Tom Lane that said, "if we add new syntax, we have to support it forever"? As a helpful feature I can see people (myself included) enabling this system wide. So what happens to all the plpgsql on pgxn that this happens to break? Well it needs updated, no problem, but the fix will be to add "#prepare_plans on_demand" in the magic spot. That breaks it for prior versions of PG. Is this the syntax we want? What if we add more "compiler flags" in the future:
create function test5() returns integer as $$
#prepare_plans on_demand
#disable_xlog
#work_mem 10MB
begin
create temp table junk(id integer);
insert into junk(id) values(100);
drop table temp;
return 1;
end;
$$ language plpgsql;
I don't have an answer to that. Other sql implement via OPTION(...).
One option I'd thought about, was to extended ANALYZE to support functions. It would require no additional plpgsql syntax changes, no postgresql.conf settings. If you wanted to prepare (prepare for a testing purpose, not a performance purpose) all the sql inside your function, youd:
analyze test5();
I'd expect to get errors from that, because the junk table doesn't exist. I'd expect it, and just never analyze it.
Summary
=======
Its a tough one. I see benefit here. I can see myself using it. If I had to put my finger on it, I'm not 100% sold on the syntax. It is usable though, it does solve problems, so I'd use it. (I'm not 100% sure ANALYZE is better, either).
I'm going to leave this patch as "needs review", I think more eyes might be helpful.
-Andy
Andy Colson <andy@squeakycode.net> writes:
[ Andy's dubious about adding plpgsql syntax to control this feature ]
Yeah, that bothers me a lot too.
One option I'd thought about, was to extended ANALYZE to support functions.
That's actually quite a good idea, not least because the extra checking
happens only when you ask for it and not every time the function is
loaded into a new session.
I'm not that happy with overloading the ANALYZE keyword to mean this
(especially not since there is already meaning attached to the syntax
"ANALYZE x(y)"). But we could certainly use some other name --- I'm
inclined to suggest CHECK:
CHECK FUNCTION function_name(arglist);
People would want some sort of wild card capability; at the very least
"check all plpgsql functions owned by me". Not sure what that ought
to look like syntactically.
It might also be a good idea to make sure there's room in the syntax to
specify different checking options. We already would have reason to
want "just do the existing style of validation check" versus this more
intensive check. And it's not hard to foresee other sorts of checking
in future.
Also, this would force us to invent PL-independent infrastructure for
doing the checking. I'm envisioning an additional argument to the
existing PL validator function that tells it what checking options to
use.
regards, tom lane
On Sun, Sep 11, 2011 at 01:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not that happy with overloading the ANALYZE keyword to mean this
But we could certainly use some other name --- I'm
inclined to suggest CHECK:
CHECK FUNCTION function_name(arglist);
Just a thought: pg_check_function(oid)?
People would want some sort of wild card capability; at the very least
"check all plpgsql functions owned by me".
SELECT pg_check_function(p.oid) FROM pg_proc p
JOIN pg_user ON (usesysid=proowner) WHERE usename=current_user;
Regards,
Marti
Marti Raudsepp <marti@juffo.org> writes:
On Sun, Sep 11, 2011 at 01:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not that happy with overloading the ANALYZE keyword to mean this
But we could certainly use some other name --- I'm
inclined to suggest CHECK:
CHECK FUNCTION function_name(arglist);
Just a thought: pg_check_function(oid)?
People would want some sort of wild card capability; at the very least
"check all plpgsql functions owned by me".
SELECT pg_check_function(p.oid) FROM pg_proc p
JOIN pg_user ON (usesysid=proowner) WHERE usename=current_user;
Hmm, there's something in what you say --- it gets us out from under the
need to anticipate what wildcard rules people would want. I think it
loses something in ease-of-use, but we could have the utility command
too for the simple check-one-function case, and direct people to the
function as soon as they want something fancier.
What about check-strength options?
regards, tom lane
Hello
thank you very much for review
Will always throw an error because at prepare time, the temp junk table wont
exist. This patch implements new syntax to disable the check:create function test5() returns integer as $$
#prepare_plans on_demand
begin
...Was it Tom Lane that said, "if we add new syntax, we have to support it
forever"? As a helpful feature I can see people (myself included) enabling
this system wide. So what happens to all the plpgsql on pgxn that this
happens to break? Well it needs updated, no problem, but the fix will be to
add "#prepare_plans on_demand" in the magic spot. That breaks it for prior
versions of PG. Is this the syntax we want? What if we add more "compiler
flags" in the future:create function test5() returns integer as $$
#prepare_plans on_demand
#disable_xlog
#work_mem 10MB
begin
create temp table junk(id integer);
insert into junk(id) values(100);
drop table temp;
return 1;
end;
$$ language plpgsql;
I am sure, so we will support a plan based statements inside PL very
long time. But this is not only one way, how to change a behave. You
can use a plpgsql.prepare_plans variable too. Theoretically We can
live without prepare_plans option - it doesn't modify a function's
behave - so it must not be part of function body. But I think, so it
is more readable - and it stronger warning for developers -
"attention, this function was not checked deeply". I have no problem
to remove this option, and use only a custom GUC
I don't have an answer to that. Other sql implement via OPTION(...).
One option I'd thought about, was to extended ANALYZE to support functions.
It would require no additional plpgsql syntax changes, no postgresql.conf
settings. If you wanted to prepare (prepare for a testing purpose, not a
performance purpose) all the sql inside your function, youd:analyze test5();
I am not against to some analogy to what you mean - just dislike a use
of ANALYZE keyword. Just static check has a one significant
disadvantage - we should not to identify types of NEW and OLD
variables in triggers.
I'd expect to get errors from that, because the junk table doesn't exist.
I'd expect it, and just never analyze it.Summary
=======
Its a tough one. I see benefit here. I can see myself using it. If I had
to put my finger on it, I'm not 100% sold on the syntax. It is usable
though, it does solve problems, so I'd use it. (I'm not 100% sure ANALYZE
is better, either).I'm going to leave this patch as "needs review", I think more eyes might be
helpful.-Andy
Thank you very much
Pavel Stehule
Hello
2011/9/11 Tom Lane <tgl@sss.pgh.pa.us>:
Andy Colson <andy@squeakycode.net> writes:
[ Andy's dubious about adding plpgsql syntax to control this feature ]
Yeah, that bothers me a lot too.
I like to discussion about syntax - a name "prepare_plans" and
following list is just one (for me - practical) shot. I am sure so
preparing all plans on function start is one functionality what we
want - because it can to do early warnings when some in environments
is not well. And if I remember well, there was one tool that does it
too, but a goal was different - they wanted a faster function
execution in production usage - without "slower" first call. The
overhead of check walker is minimal.
One option I'd thought about, was to extended ANALYZE to support functions.
That's actually quite a good idea, not least because the extra checking
happens only when you ask for it and not every time the function is
loaded into a new session.I'm not that happy with overloading the ANALYZE keyword to mean this
(especially not since there is already meaning attached to the syntax
"ANALYZE x(y)"). But we could certainly use some other name --- I'm
inclined to suggest CHECK:CHECK FUNCTION function_name(arglist);
I proposed a stored procedure "check_function(name, arglist)", but
CHECK FUNCTION is ok for me too. Is easy implement it. Maybe there is
issue - "CHECK" will be a keyword :(
People would want some sort of wild card capability; at the very least
"check all plpgsql functions owned by me". Not sure what that ought
to look like syntactically.
I don't think. Now (when I looking around me) a owner of functions are
some abstract role. It is terrible to maintain a system where database
objects has a different roles. I can expect a request for check all
functions from some schema or all functions related to some PL.
It might also be a good idea to make sure there's room in the syntax to
specify different checking options. We already would have reason to
want "just do the existing style of validation check" versus this more
intensive check. And it's not hard to foresee other sorts of checking
in future.
+1
There is possible check of RAISE statement params and maybe similar.
Also, this would force us to invent PL-independent infrastructure for
doing the checking. I'm envisioning an additional argument to the
existing PL validator function that tells it what checking options to
use.
+1
yup. But there is query - need we a new special statement?
cannot we enhance a CREATE OR REPLACE FUNCTION statement? Cannot we
enhance of syntax or cannot we enhance of behave.
or has sense to have two statements CREATE FUNCTION and CHECK
FUNCTION? I see a sense. CHECK FUNCTION should be ??parametrized?? One
idea - CHECK FUNCTION can have a own independent hooks on PL hooks.
Resume:
* We want to deep check plans when function is started
* I like a CHECK FUNCTION statement - see a possibilities - but I am
not sure if PL developers will like it too. It means start two
statements - it just idea - what about CREATE OR REPLACE FUNCTION
blabla() ... IMMUTABLE STRICT >>CHECK<< - so CREATE STATEMENT can
optionally to call CHECK statement
regards, tom lane
there is still task - what with trigger's functions
Regards
Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
I like to discussion about syntax - a name "prepare_plans" and
following list is just one (for me - practical) shot. I am sure so
preparing all plans on function start is one functionality what we
want - because it can to do early warnings when some in environments
is not well.
I don't think it *is* functionality that we want. From a performance
standpoint it's certainly not a win: there is no savings from preparing
all the plans at the same time, and you can lose if there are code paths
that are never executed. The only argument for doing it is more
extensive checking.
And on the checking front, I like Andy's idea better than the original.
There is not value in repeating a checking effort in every single
process when nothing is changing. Furthermore, because of the problem
of the checks breaking functions that contain DDL, you have to have a
way of disabling it and you have to worry about setting up permission
mechanisms to restrict who can break whose functions, something that
will never work very smoothly IMO. (Certainly, having to be superuser
to enable checking is not a point in favor of your design.) Pushing the
checking out as a separately invokable operation neatly bypasses both of
those problems. Right offhand I'm not sure we need any permission
restrictions at all on a CHECK operation, but at the worst, permission
to call the function ought to be enough.
there is still task - what with trigger's functions
"CHECK TRIGGER name ON tablename" could be syntactic sugar for calling
the validator on the trigger's function and passing it sufficient
information to set up the trigger arguments properly.
regards, tom lane
CHECK FUNCTION function_name(arglist);
I proposed a stored procedure "check_function(name, arglist)", but
CHECK FUNCTION is ok for me too. Is easy implement it. Maybe there is
issue - "CHECK" will be a keyword :(
CHECK is reserved keyword now, so this is issue.
sorry for noise
Pavel
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'm not that happy with overloading the ANALYZE keyword to mean this
(especially not since there is already meaning attached to the syntax
"ANALYZE x(y)"). But we could certainly use some other name --- I'm
inclined to suggest CHECK:CHECK FUNCTION function_name(arglist);
This looks as good as it gets, but as we proposed some new behaviors for
ANALYZE in the past, I though I would bounce them here again for you to
decide about the overall picture.
The idea (that didn't get much traction at the time) was to support
ANALYZE on VIEWS so that we have statistics support for multi-columns or
any user given join. The very difficult part about that is to be able
to match those stats we would have against a user SQL query.
But such a matching has been talked about in other contexts, it seems to
me, so the day we have that capability we might want to add ANALYZE
support to VIEWS. ANALYZE could then support tables, indexes, views and
functions, and maybe some more database objects in the future.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hello
I started work on proposed check statement option and there are a few questions?
what is sense of this statement for others PL? When we solve a mainly
PL/pgSQL issue, has sense to implement new statement? Isn't a some
problem in our CREATE FUNCTION design? A separation to two steps
should has a little bit strange behave - we cannot to check a function
before their registration (we can, but we should to do a some game
with names) - there is necessary some a conditional CREATE FUNCTION
statement - some like "CREATE CHECKED FUNCTION " or CHECK FUNCTION
with function body.
comments?
Regards
Pavel Stehule
2011/9/11 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
Andy Colson <andy@squeakycode.net> writes:
[ Andy's dubious about adding plpgsql syntax to control this feature ]
Yeah, that bothers me a lot too.
One option I'd thought about, was to extended ANALYZE to support functions.
That's actually quite a good idea, not least because the extra checking
happens only when you ask for it and not every time the function is
loaded into a new session.I'm not that happy with overloading the ANALYZE keyword to mean this
(especially not since there is already meaning attached to the syntax
"ANALYZE x(y)"). But we could certainly use some other name --- I'm
inclined to suggest CHECK:CHECK FUNCTION function_name(arglist);
People would want some sort of wild card capability; at the very least
"check all plpgsql functions owned by me". Not sure what that ought
to look like syntactically.It might also be a good idea to make sure there's room in the syntax to
specify different checking options. We already would have reason to
want "just do the existing style of validation check" versus this more
intensive check. And it's not hard to foresee other sorts of checking
in future.Also, this would force us to invent PL-independent infrastructure for
doing the checking. I'm envisioning an additional argument to the
existing PL validator function that tells it what checking options to
use.regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
I started work on proposed check statement option and there are a few questions?
what is sense of this statement for others PL?
IMO you should design this as a call to the PL's validator function.
It's not necessary to make other PLs do anything more than their
existing validators do (at least for now).
regards, tom lane
Hello
this is initial version of CHECK FUNCTION | CHECK TRIGGER statement
usage is simple
postgres=# CHECK FUNCTION f();
CHECK FUNCTION
Time: 3,411 ms
postgres=# CHECK TRIGGER foo ON omega ;
NOTICE: checking function "trg()"
CHECK TRIGGER
Time: 73,139 ms
postgres=# select plpgsql_checker('f()'::regprocedure, 0);
plpgsql_checker
─────────────────
(1 row)
Time: 0,861 ms
second parameter of plpgsql_checker function is relation oid that is
used for trigger checking. A possibility batch checking is reason why
I used new PL function.
when function has a bug, then CHECK FUNCTION show it
postgres=# CHECK FUNCTION fx();
ERROR: column "z" does not exist
LINE 1: SELECT exists(select * from omega where z = 1)
^
QUERY: SELECT exists(select * from omega where z = 1)
CONTEXT: PL/pgSQL function "fx" line 4 at IF
postgres=#
autocomplete in psql is supported
Regards
Pavel Stehule
Attachments:
check_function.difftext/x-patch; charset=US-ASCII; name=check_function.diffDownload
*** ./doc/src/sgml/ref/allfiles.sgml.orig 2011-10-04 13:56:40.000000000 +0200
--- ./doc/src/sgml/ref/allfiles.sgml 2011-10-05 11:44:22.667571959 +0200
***************
*** 40,45 ****
--- 40,46 ----
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
+ <!ENTITY checkFunction SYSTEM "check_function.sgml">
<!ENTITY checkpoint SYSTEM "checkpoint.sgml">
<!ENTITY close SYSTEM "close.sgml">
<!ENTITY cluster SYSTEM "cluster.sgml">
*** ./doc/src/sgml/ref/create_language.sgml.orig 2011-10-04 13:58:46.580303751 +0200
--- ./doc/src/sgml/ref/create_language.sgml 2011-10-04 13:59:14.301344571 +0200
***************
*** 23,29 ****
<synopsis>
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
! HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ]
</synopsis>
</refsynopsisdiv>
--- 23,29 ----
<synopsis>
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
! HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ] [ CHECK <replaceable>checkfunction</replaceable> ]
</synopsis>
</refsynopsisdiv>
***************
*** 218,223 ****
--- 218,237 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>CHECK</literal> <replaceable class="parameter">checkfunction</replaceable></term>
+
+ <listitem>
+ <para><replaceable class="parameter">checkfunction</replaceable> is the
+ name of a previously registered function that will be called
+ when a new function in the language is created, to check the
+ function by statemnt <command>CHECK FUNCTION</command> or
+ <command>CHECK TRIGGER</command>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
<para>
*** ./doc/src/sgml/reference.sgml.orig 2011-10-04 13:56:40.000000000 +0200
--- ./doc/src/sgml/reference.sgml 2011-10-05 14:18:39.597956163 +0200
***************
*** 68,73 ****
--- 68,74 ----
&alterView;
&analyze;
&begin;
+ &checkFunction;
&checkpoint;
&close;
&cluster;
*** ./doc/src/sgml/ref/check_function.sgml.orig 2011-10-05 11:42:53.365345351 +0200
--- ./doc/src/sgml/ref/check_function.sgml 2011-10-05 13:00:41.331956767 +0200
***************
*** 0 ****
--- 1,37 ----
+ <!--
+ doc/src/sgml/ref/check_function.sgml
+ -->
+
+ <refentry id="SQL-CHECKFUNCTION">
+ <refmeta>
+ <refentrytitle>CHECK FUNCTION</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CHECK FUNCTION</refname>
+ <refpurpose>ensure a deep checking of existing function</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-checkfunction">
+ <primary>CHECK FUNCTION</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+ <synopsis>
+ CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] )
+ | CREATE TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">tablename</replaceable>
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1 id="sql-checkfunction-description">
+ <title>Description</title>
+
+ <para>
+ <command>CHECK FUNCTION</command> check a existing function.
+ <command>CHECK TRIGGER</command> check a trigger function.
+ </para>
+ </refsect1>
+
+ </refentry>
*** ./src/backend/catalog/pg_proc.c.orig 2011-10-04 13:56:40.742123353 +0200
--- ./src/backend/catalog/pg_proc.c 2011-10-04 13:59:14.302344572 +0200
***************
*** 1075,1077 ****
--- 1075,1078 ----
*newcursorpos = newcp;
return false;
}
+
*** ./src/backend/commands/functioncmds.c.orig 2011-10-04 13:58:46.587303763 +0200
--- ./src/backend/commands/functioncmds.c 2011-10-05 08:08:50.131216144 +0200
***************
*** 44,53 ****
--- 44,55 ----
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_proc_fn.h"
+ #include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "catalog/pg_type_fn.h"
#include "commands/defrem.h"
#include "commands/proclang.h"
+ #include "commands/trigger.h"
#include "miscadmin.h"
#include "optimizer/var.h"
#include "parser/parse_coerce.h"
***************
*** 1079,1084 ****
--- 1081,1209 ----
}
}
+ /*
+ * CheckFunction
+ * call a PL checker function when this function exists.
+ */
+ void
+ CheckFunction(CheckFunctionStmt *stmt)
+ {
+ List *functionName = stmt->funcname;
+ List *argTypes = stmt->args; /* list of TypeName nodes */
+ Oid funcOid;
+
+ HeapTuple tup;
+ Form_pg_proc proc;
+
+ HeapTuple languageTuple;
+ Form_pg_language languageStruct;
+ Oid languageChecker;
+ Oid trgOid = InvalidOid;
+ Oid relid = InvalidOid;
+
+ /* when we should to check trigger, then we should to find a trigger handler */
+ if (functionName == NULL)
+ {
+ HeapTuple ht_trig;
+ Form_pg_trigger trigrec;
+ ScanKeyData skey[1];
+ Relation tgrel;
+ SysScanDesc tgscan;
+ char *fname;
+
+ relid = RangeVarGetRelid(stmt->relation, ShareLock, false, false);
+ trgOid = get_trigger_oid(relid, stmt->trgname, false);
+
+ /*
+ * Fetch the pg_trigger tuple by the Oid of the trigger
+ */
+ tgrel = heap_open(TriggerRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(trgOid));
+
+ tgscan = systable_beginscan(tgrel, TriggerOidIndexId, true,
+ SnapshotNow, 1, skey);
+
+ ht_trig = systable_getnext(tgscan);
+
+ if (!HeapTupleIsValid(ht_trig))
+ elog(ERROR, "could not find tuple for trigger %u", trgOid);
+
+ trigrec = (Form_pg_trigger) GETSTRUCT(ht_trig);
+
+ /* we need to know trigger function to get PL checker function */
+ funcOid = trigrec->tgfoid;
+ fname = format_procedure(funcOid);
+ /* Clean up */
+ systable_endscan(tgscan);
+
+ elog(NOTICE, "checking function \"%s\"", fname);
+ pfree(fname);
+
+ heap_close(tgrel, AccessShareLock);
+ }
+ else
+ {
+ /*
+ * Find the function,
+ */
+ funcOid = LookupFuncNameTypeNames(functionName, argTypes, false);
+ }
+
+ tup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcOid));
+ if (!HeapTupleIsValid(tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for function %u", funcOid);
+
+ proc = (Form_pg_proc) GETSTRUCT(tup);
+
+ languageTuple = SearchSysCache1(LANGOID, ObjectIdGetDatum(proc->prolang));
+ Assert(HeapTupleIsValid(languageTuple));
+
+ languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
+ languageChecker = languageStruct->lanchecker;
+
+ /* Check a function body */
+ if (OidIsValid(languageChecker))
+ {
+ ArrayType *set_items = NULL;
+ int save_nestlevel;
+ Datum datum;
+ bool isnull;
+
+ datum = SysCacheGetAttr(PROCOID, tup, Anum_pg_proc_proconfig, &isnull);
+
+ if (!isnull)
+ {
+ /* Set per-function configuration parameters */
+ set_items = (ArrayType *) DatumGetPointer(datum);
+ if (set_items) /* Need a new GUC nesting level */
+ {
+ save_nestlevel = NewGUCNestLevel();
+ ProcessGUCArray(set_items,
+ (superuser() ? PGC_SUSET : PGC_USERSET),
+ PGC_S_SESSION,
+ GUC_ACTION_SAVE);
+ }
+ else
+ save_nestlevel = 0; /* keep compiler quiet */
+ }
+
+ OidFunctionCall2(languageChecker, ObjectIdGetDatum(funcOid),
+ ObjectIdGetDatum(relid));
+
+ if (set_items)
+ AtEOXact_GUC(true, save_nestlevel);
+ }
+ else
+ elog(WARNING, "language \"%s\" has no defined checker function",
+ NameStr(languageStruct->lanname));
+
+ ReleaseSysCache(languageTuple);
+ ReleaseSysCache(tup);
+ }
/*
* Rename function
*** ./src/backend/commands/proclang.c.orig 2011-10-04 13:58:46.590303767 +0200
--- ./src/backend/commands/proclang.c 2011-10-05 07:25:52.104765099 +0200
***************
*** 46,57 ****
char *tmplhandler; /* name of handler function */
char *tmplinline; /* name of anonymous-block handler, or NULL */
char *tmplvalidator; /* name of validator function, or NULL */
char *tmpllibrary; /* path of shared library */
} PLTemplate;
static void create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, bool trusted);
static PLTemplate *find_language_template(const char *languageName);
static void AlterLanguageOwner_internal(HeapTuple tup, Relation rel,
Oid newOwnerId);
--- 46,58 ----
char *tmplhandler; /* name of handler function */
char *tmplinline; /* name of anonymous-block handler, or NULL */
char *tmplvalidator; /* name of validator function, or NULL */
+ char *tmplchecker; /* name of checker function, or NULL */
char *tmpllibrary; /* path of shared library */
} PLTemplate;
static void create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, Oid checkerOid, bool trusted);
static PLTemplate *find_language_template(const char *languageName);
static void AlterLanguageOwner_internal(HeapTuple tup, Relation rel,
Oid newOwnerId);
***************
*** 68,76 ****
PLTemplate *pltemplate;
Oid handlerOid,
inlineOid,
! valOid;
Oid funcrettype;
! Oid funcargtypes[1];
/*
* Translate the language name to lower case
--- 69,78 ----
PLTemplate *pltemplate;
Oid handlerOid,
inlineOid,
! valOid,
! checkerOid;
Oid funcrettype;
! Oid funcargtypes[2];
/*
* Translate the language name to lower case
***************
*** 225,234 ****
else
valOid = InvalidOid;
/* ok, create it */
create_proc_lang(languageName, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, pltemplate->tmpltrusted);
}
else
{
--- 227,275 ----
else
valOid = InvalidOid;
+ /*
+ * Likewise for the checker, if required; but we don't care about
+ * its return type.
+ */
+ if (pltemplate->tmplchecker)
+ {
+ funcname = SystemFuncName(pltemplate->tmplchecker);
+ funcargtypes[0] = OIDOID;
+ funcargtypes[1] = REGCLASSOID;
+ checkerOid = LookupFuncName(funcname, 2, funcargtypes, true);
+ if (!OidIsValid(checkerOid))
+ {
+ checkerOid = ProcedureCreate(pltemplate->tmplchecker,
+ PG_CATALOG_NAMESPACE,
+ false, /* replace */
+ false, /* returnsSet */
+ VOIDOID,
+ ClanguageId,
+ F_FMGR_C_VALIDATOR,
+ pltemplate->tmplchecker,
+ pltemplate->tmpllibrary,
+ false, /* isAgg */
+ false, /* isWindowFunc */
+ false, /* security_definer */
+ true, /* isStrict */
+ PROVOLATILE_VOLATILE,
+ buildoidvector(funcargtypes, 2),
+ PointerGetDatum(NULL),
+ PointerGetDatum(NULL),
+ PointerGetDatum(NULL),
+ NIL,
+ PointerGetDatum(NULL),
+ 1,
+ 0);
+ }
+ }
+ else
+ checkerOid = InvalidOid;
+
/* ok, create it */
create_proc_lang(languageName, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, checkerOid, pltemplate->tmpltrusted);
}
else
{
***************
*** 300,309 ****
else
valOid = InvalidOid;
/* ok, create it */
create_proc_lang(languageName, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, stmt->pltrusted);
}
}
--- 341,362 ----
else
valOid = InvalidOid;
+ /* validate the validator function */
+ if (stmt->plchecker)
+ {
+ funcargtypes[0] = OIDOID;
+ funcargtypes[1] = REGCLASSOID;
+ checkerOid = LookupFuncName(stmt->plchecker, 2, funcargtypes, false);
+ /* return value is ignored, so we don't check the type */
+ }
+ else
+ checkerOid = InvalidOid;
+
+
/* ok, create it */
create_proc_lang(languageName, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, checkerOid, stmt->pltrusted);
}
}
***************
*** 313,319 ****
static void
create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, bool trusted)
{
Relation rel;
TupleDesc tupDesc;
--- 366,372 ----
static void
create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, Oid checkerOid, bool trusted)
{
Relation rel;
TupleDesc tupDesc;
***************
*** 343,348 ****
--- 396,402 ----
values[Anum_pg_language_lanplcallfoid - 1] = ObjectIdGetDatum(handlerOid);
values[Anum_pg_language_laninline - 1] = ObjectIdGetDatum(inlineOid);
values[Anum_pg_language_lanvalidator - 1] = ObjectIdGetDatum(valOid);
+ values[Anum_pg_language_lanchecker - 1] = ObjectIdGetDatum(checkerOid);
nulls[Anum_pg_language_lanacl - 1] = true;
/* Check for pre-existing definition */
***************
*** 429,434 ****
--- 483,497 ----
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
}
+ /* dependency on the checker function, if any */
+ if (OidIsValid(checkerOid))
+ {
+ referenced.classId = ProcedureRelationId;
+ referenced.objectId = checkerOid;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+
/* Post creation hook for new procedural language */
InvokeObjectAccessHook(OAT_POST_CREATE,
LanguageRelationId, myself.objectId, 0);
***************
*** 484,489 ****
--- 547,557 ----
if (!isnull)
result->tmplvalidator = TextDatumGetCString(datum);
+ datum = heap_getattr(tup, Anum_pg_pltemplate_tmplchecker,
+ RelationGetDescr(rel), &isnull);
+ if (!isnull)
+ result->tmplchecker = TextDatumGetCString(datum);
+
datum = heap_getattr(tup, Anum_pg_pltemplate_tmpllibrary,
RelationGetDescr(rel), &isnull);
if (!isnull)
*** ./src/backend/parser/gram.y.orig 2011-10-04 13:58:46.594303771 +0200
--- ./src/backend/parser/gram.y 2011-10-04 13:59:14.314344592 +0200
***************
*** 227,232 ****
--- 227,233 ----
DeallocateStmt PrepareStmt ExecuteStmt
DropOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
+ CheckFunctionStmt
%type <node> select_no_parens select_with_parens select_clause
simple_select values_clause
***************
*** 276,282 ****
%type <list> func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_inline_handler opt_validator validator_clause
! opt_collate
%type <range> qualified_name OptConstrFromTable
--- 277,283 ----
%type <list> func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_inline_handler opt_validator validator_clause
! opt_collate opt_checker
%type <range> qualified_name OptConstrFromTable
***************
*** 701,706 ****
--- 702,708 ----
| AlterUserSetStmt
| AlterUserStmt
| AnalyzeStmt
+ | CheckFunctionStmt
| CheckPointStmt
| ClosePortalStmt
| ClusterStmt
***************
*** 3197,3207 ****
n->plhandler = NIL;
n->plinline = NIL;
n->plvalidator = NIL;
n->pltrusted = false;
$$ = (Node *)n;
}
| CREATE opt_or_replace opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
! HANDLER handler_name opt_inline_handler opt_validator
{
CreatePLangStmt *n = makeNode(CreatePLangStmt);
n->replace = $2;
--- 3199,3210 ----
n->plhandler = NIL;
n->plinline = NIL;
n->plvalidator = NIL;
+ n->plchecker = NIL;
n->pltrusted = false;
$$ = (Node *)n;
}
| CREATE opt_or_replace opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
! HANDLER handler_name opt_inline_handler opt_validator opt_checker
{
CreatePLangStmt *n = makeNode(CreatePLangStmt);
n->replace = $2;
***************
*** 3209,3214 ****
--- 3212,3218 ----
n->plhandler = $8;
n->plinline = $9;
n->plvalidator = $10;
+ n->plchecker = $11;
n->pltrusted = $3;
$$ = (Node *)n;
}
***************
*** 3243,3248 ****
--- 3247,3257 ----
| /*EMPTY*/ { $$ = NIL; }
;
+ opt_checker:
+ CHECK handler_name { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
DropPLangStmt:
DROP opt_procedural LANGUAGE ColId_or_Sconst opt_drop_behavior
{
***************
*** 6249,6254 ****
--- 6258,6293 ----
/*****************************************************************************
*
+ * CHECK FUNCTION funcname(args)
+ * CHECK TRIGGER triggername ON table
+ *
+ *
+ *****************************************************************************/
+
+
+ CheckFunctionStmt:
+ CHECK FUNCTION func_name func_args
+ {
+ CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
+ n->funcname = $3;
+ n->args = extractArgTypes($4);
+ n->trgname = NULL;
+ n->relation = NULL;
+ $$ = (Node *) n;
+ }
+ | CHECK TRIGGER name ON qualified_name
+ {
+ CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
+ n->funcname = NULL;
+ n->args = NIL;
+ n->trgname = $3;
+ n->relation = $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+ /*****************************************************************************
+ *
* DO <anonymous code block> [ LANGUAGE language ]
*
* We use a DefElem list for future extensibility, and to allow flexibility
*** ./src/backend/tcop/utility.c.orig 2011-10-04 13:58:46.609303793 +0200
--- ./src/backend/tcop/utility.c 2011-10-05 08:11:46.129451749 +0200
***************
*** 936,941 ****
--- 936,945 ----
AlterFunction((AlterFunctionStmt *) parsetree);
break;
+ case T_CheckFunctionStmt:
+ CheckFunction((CheckFunctionStmt *) parsetree);
+ break;
+
case T_IndexStmt: /* CREATE INDEX */
{
IndexStmt *stmt = (IndexStmt *) parsetree;
***************
*** 2260,2265 ****
--- 2264,2276 ----
}
break;
+ case T_CheckFunctionStmt:
+ if (((CheckFunctionStmt *) parsetree)->funcname != NULL)
+ tag = "CHECK FUNCTION";
+ else
+ tag = "CHECK TRIGGER";
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
***************
*** 2722,2727 ****
--- 2733,2742 ----
}
break;
+ case T_CheckFunctionStmt:
+ lev = LOGSTMT_ALL;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
*** ./src/bin/psql/tab-complete.c.orig 2011-10-04 13:56:40.000000000 +0200
--- ./src/bin/psql/tab-complete.c 2011-10-05 08:51:23.603634388 +0200
***************
*** 1,4 ****
--- 1,5 ----
/*
+ *
* psql - the PostgreSQL interactive terminal
*
* Copyright (c) 2000-2011, PostgreSQL Global Development Group
***************
*** 719,725 ****
*prev6_wd;
static const char *const sql_commands[] = {
! "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE",
--- 720,726 ----
*prev6_wd;
static const char *const sql_commands[] = {
! "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE",
***************
*** 1521,1526 ****
--- 1522,1549 ----
COMPLETE_WITH_LIST(list_TRANS);
}
+
+ /* CHECK */
+ else if (pg_strcasecmp(prev_wd, "CHECK") == 0)
+ {
+ static const char *const list_CHECK[] =
+ {"FUNCTION", "TRIGGER", NULL};
+
+ COMPLETE_WITH_LIST(list_CHECK);
+ }
+ else if (pg_strcasecmp(prev3_wd, "CHECK") == 0 &&
+ pg_strcasecmp(prev2_wd, "TRIGGER") == 0)
+ {
+ COMPLETE_WITH_CONST("ON");
+ }
+ else if (pg_strcasecmp(prev4_wd, "CHECK") == 0 &&
+ pg_strcasecmp(prev3_wd, "TRIGGER") == 0 &&
+ pg_strcasecmp(prev_wd, "ON") == 0)
+ {
+ completion_info_charp = prev2_wd;
+ COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
+ }
+
/* CLUSTER */
/*
*** ./src/include/catalog/pg_language.h.orig 2011-10-04 13:58:46.610303794 +0200
--- ./src/include/catalog/pg_language.h 2011-10-04 13:59:14.317344596 +0200
***************
*** 37,42 ****
--- 37,43 ----
Oid lanplcallfoid; /* Call handler for PL */
Oid laninline; /* Optional anonymous-block handler function */
Oid lanvalidator; /* Optional validation function */
+ Oid lanchecker; /* Optional checker function */
aclitem lanacl[1]; /* Access privileges */
} FormData_pg_language;
***************
*** 51,57 ****
* compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language 8
#define Anum_pg_language_lanname 1
#define Anum_pg_language_lanowner 2
#define Anum_pg_language_lanispl 3
--- 52,58 ----
* compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language 9
#define Anum_pg_language_lanname 1
#define Anum_pg_language_lanowner 2
#define Anum_pg_language_lanispl 3
***************
*** 59,78 ****
#define Anum_pg_language_lanplcallfoid 5
#define Anum_pg_language_laninline 6
#define Anum_pg_language_lanvalidator 7
! #define Anum_pg_language_lanacl 8
/* ----------------
* initial contents of pg_language
* ----------------
*/
! DATA(insert OID = 12 ( "internal" PGUID f f 0 0 2246 _null_ ));
DESCR("built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 0 2247 _null_ ));
DESCR("dynamically-loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 0 2248 _null_ ));
DESCR("SQL-language functions");
#define SQLlanguageId 14
--- 60,80 ----
#define Anum_pg_language_lanplcallfoid 5
#define Anum_pg_language_laninline 6
#define Anum_pg_language_lanvalidator 7
! #define Anum_pg_language_lanchecker 8
! #define Anum_pg_language_lanacl 9
/* ----------------
* initial contents of pg_language
* ----------------
*/
! DATA(insert OID = 12 ( "internal" PGUID f f 0 0 2246 0 _null_ ));
DESCR("built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 0 2247 0 _null_ ));
DESCR("dynamically-loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 0 2248 0 _null_ ));
DESCR("SQL-language functions");
#define SQLlanguageId 14
*** ./src/include/catalog/pg_pltemplate.h.orig 2011-10-04 13:58:46.612303798 +0200
--- ./src/include/catalog/pg_pltemplate.h 2011-10-04 13:59:14.318344598 +0200
***************
*** 36,41 ****
--- 36,42 ----
text tmplhandler; /* name of call handler function */
text tmplinline; /* name of anonymous-block handler, or NULL */
text tmplvalidator; /* name of validator function, or NULL */
+ text tmplchecker; /* name of checker function, or NULL */
text tmpllibrary; /* path of shared library */
aclitem tmplacl[1]; /* access privileges for template */
} FormData_pg_pltemplate;
***************
*** 51,65 ****
* compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate 8
#define Anum_pg_pltemplate_tmplname 1
#define Anum_pg_pltemplate_tmpltrusted 2
#define Anum_pg_pltemplate_tmpldbacreate 3
#define Anum_pg_pltemplate_tmplhandler 4
#define Anum_pg_pltemplate_tmplinline 5
#define Anum_pg_pltemplate_tmplvalidator 6
! #define Anum_pg_pltemplate_tmpllibrary 7
! #define Anum_pg_pltemplate_tmplacl 8
/* ----------------
--- 52,67 ----
* compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate 9
#define Anum_pg_pltemplate_tmplname 1
#define Anum_pg_pltemplate_tmpltrusted 2
#define Anum_pg_pltemplate_tmpldbacreate 3
#define Anum_pg_pltemplate_tmplhandler 4
#define Anum_pg_pltemplate_tmplinline 5
#define Anum_pg_pltemplate_tmplvalidator 6
! #define Anum_pg_pltemplate_tmplchecker 7
! #define Anum_pg_pltemplate_tmpllibrary 8
! #define Anum_pg_pltemplate_tmplacl 9
/* ----------------
***************
*** 67,79 ****
* ----------------
*/
! DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperlu_call_handler" "plperlu_inline_handler" "plperlu_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython2u" f f "plpython2_call_handler" "plpython2_inline_handler" "plpython2_validator" "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" "plpython3_validator" "$libdir/plpython3" _null_ ));
#endif /* PG_PLTEMPLATE_H */
--- 69,81 ----
* ----------------
*/
! DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "plpgsql_checker" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" _null_ "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperlu_call_handler" "plperlu_inline_handler" "plperlu_validator" _null_ "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" _null_ "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython2u" f f "plpython2_call_handler" "plpython2_inline_handler" "plpython2_validator" _null_ "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" "plpython3_validator" _null_ "$libdir/plpython3" _null_ ));
#endif /* PG_PLTEMPLATE_H */
*** ./src/include/commands/defrem.h.orig 2011-10-04 13:58:46.614303802 +0200
--- ./src/include/commands/defrem.h 2011-10-04 13:59:14.318344598 +0200
***************
*** 61,66 ****
--- 61,67 ----
extern void CreateFunction(CreateFunctionStmt *stmt, const char *queryString);
extern void RemoveFunction(RemoveFuncStmt *stmt);
extern void RemoveFunctionById(Oid funcOid);
+ extern void CheckFunction(CheckFunctionStmt *stmt);
extern void SetFunctionReturnType(Oid funcOid, Oid newRetType);
extern void SetFunctionArgType(Oid funcOid, int argIndex, Oid newArgType);
extern void RenameFunction(List *name, List *argtypes, const char *newname);
*** ./src/include/nodes/nodes.h.orig 2011-10-04 13:58:46.616303805 +0200
--- ./src/include/nodes/nodes.h 2011-10-04 13:59:14.319344599 +0200
***************
*** 290,295 ****
--- 290,296 ----
T_CreateFunctionStmt,
T_AlterFunctionStmt,
T_RemoveFuncStmt,
+ T_CheckFunctionStmt,
T_DoStmt,
T_RenameStmt,
T_RuleStmt,
*** ./src/include/nodes/parsenodes.h.orig 2011-10-04 13:56:40.994123707 +0200
--- ./src/include/nodes/parsenodes.h 2011-10-04 13:59:14.320344601 +0200
***************
*** 1750,1755 ****
--- 1750,1756 ----
List *plhandler; /* PL call handler function (qual. name) */
List *plinline; /* optional inline function (qual. name) */
List *plvalidator; /* optional validator function (qual. name) */
+ List *plchecker; /* optional checker function (qual. name) */
bool pltrusted; /* PL is trusted */
} CreatePLangStmt;
***************
*** 2132,2137 ****
--- 2133,2151 ----
} RemoveFuncStmt;
/* ----------------------
+ * Check {Function|Trigger} Statement
+ * ----------------------
+ */
+ typedef struct CheckFunctionStmt
+ {
+ NodeTag type;
+ List *funcname; /* qualified name of checked object */
+ List *args; /* types of the arguments */
+ char *trgname; /* trigger's name */
+ RangeVar *relation; /* trigger's relation */
+ } CheckFunctionStmt;
+
+ /* ----------------------
* DO Statement
*
* DoStmt is the raw parser output, InlineCodeBlock is the execution-time API
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-10-04 13:56:41.051123786 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2011-10-05 07:45:35.615349404 +0200
***************
*** 211,216 ****
--- 211,220 ----
PLpgSQL_expr *dynquery, List *params,
const char *portalname, int cursorOptions);
+ static void
+ set_rectype_for_query(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt,
+ PLpgSQL_rec *rec,PLpgSQL_expr *query,
+ bool use_element_type);
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 6175,6177 ****
--- 6179,6837 ----
return portal;
}
+
+ /*
+ * Sometime we must initialize a unknown record variable with NULL
+ * of type that is derived from some plan. This is necessary for later
+ * using a rec variable. Last parameter 'use_element_type' is true, when
+ * we would to assign a element type of result array.
+ *
+ */
+ static void
+ set_rectype_for_query(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt *stmt,
+ PLpgSQL_rec *rec,
+ PLpgSQL_expr *query,
+ bool use_element_type)
+ {
+ bool *nulls;
+ HeapTuple tup;
+ CachedPlanSource *plansource = NULL;
+ const char *err_text = estate->err_text;
+
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+ if (rec->freetup)
+ heap_freetuple(rec->tup);
+
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+
+ if (query->plan != NULL)
+ {
+ SPIPlanPtr plan = query->plan;
+
+ if (plan == NULL || plan->magic != _SPI_PLAN_MAGIC)
+ elog(ERROR, "cached plan is not valid plan");
+
+ if (list_length(plan->plancache_list) != 1)
+ elog(ERROR, "plan is not single execution plan");
+
+ plansource = (CachedPlanSource *) linitial(plan->plancache_list);
+
+ rec->tupdesc = CreateTupleDescCopy(plansource->resultDesc);
+ rec->freetupdesc = true;
+ }
+ else
+ elog(ERROR, "there are no plan for query: \"%s\"",
+ query->query);
+
+ /*
+ * try to get a element type, when result is a array (used with FOREACH ARRAY stmt)
+ */
+ if (use_element_type)
+ {
+ Oid elemtype;
+ TupleDesc tupdesc;
+
+ /* result should be a array */
+ if (rec->tupdesc->natts != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg_plural("query \"%s\" returned %d column",
+ "query \"%s\" returned %d columns",
+ rec->tupdesc->natts,
+ query->query,
+ rec->tupdesc->natts)));
+
+ /* check the type of the expression - must be an array */
+ elemtype = get_element_type(rec->tupdesc->attrs[0]->atttypid);
+ if (!OidIsValid(elemtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("FOREACH expression must yield an array, not type %s",
+ format_type_be(rec->tupdesc->attrs[0]->atttypid))));
+
+ /* we can't know typmod now */
+ tupdesc = lookup_rowtype_tupdesc_noerror(elemtype, -1, true);
+ if (tupdesc != NULL)
+ {
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+ rec->tupdesc = CreateTupleDescCopy(tupdesc);
+ rec->freetupdesc = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ else
+ elog(ERROR, "cannot to identify real type for record type variable");
+ }
+
+ /*
+ * When record is assigned to composite type, then
+ * we should to unpack composite type.
+ */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ stmt->cmd_type == PLPGSQL_STMT_ASSIGN)
+ {
+ TupleDesc tupdesc;
+
+ tupdesc = lookup_rowtype_tupdesc_noerror(rec->tupdesc->attrs[0]->atttypid,
+ rec->tupdesc->attrs[0]->atttypmod,
+ true);
+ if (tupdesc != NULL)
+ {
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+ rec->tupdesc = CreateTupleDescCopy(tupdesc);
+ rec->freetupdesc = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ }
+
+ /*
+ * When returned tupdesc contains only
+ * unpined record: rec := func_with_out_parameters(). IN this case
+ * we must to dig more deep - we have to find oid of function and
+ * get their parameters,
+ *
+ * This is support for assign statement
+ * recvar := func_with_out_parameters(..)
+ */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ rec->tupdesc->attrs[0]->atttypid == RECORDOID &&
+ rec->tupdesc->attrs[0]->atttypmod == -1)
+ {
+ PlannedStmt *_stmt;
+ Plan *_plan;
+ TargetEntry *tle;
+ CachedPlan *cplan;
+
+ /*
+ * When tupdesc is related to unpined record, we will try
+ * to check plan if it is just function call and if it is
+ * then we can try to derive a tupledes from function's
+ * description.
+ */
+ cplan = GetCachedPlan(plansource, NULL, true);
+ _stmt = (PlannedStmt *) linitial(cplan->stmt_list);
+
+ if (IsA(_stmt, PlannedStmt) && _stmt->commandType == CMD_SELECT)
+ {
+ _plan = _stmt->planTree;
+ if (IsA(_plan, Result) && list_length(_plan->targetlist) == 1)
+ {
+ tle = (TargetEntry *) linitial(_plan->targetlist);
+ if (((Node *) tle->expr)->type == T_FuncExpr)
+ {
+ FuncExpr *fn = (FuncExpr *) tle->expr;
+ FmgrInfo flinfo;
+ FunctionCallInfoData fcinfo;
+ TupleDesc rd;
+ Oid rt;
+
+ fmgr_info(fn->funcid, &flinfo);
+ flinfo.fn_expr = (Node *) fn;
+ fcinfo.flinfo = &flinfo;
+
+ get_call_result_type(&fcinfo, &rt, &rd);
+ if (rd == NULL)
+ elog(ERROR, "function does not return composite type is not possible to identify composite type");
+
+ FreeTupleDesc(rec->tupdesc);
+ BlessTupleDesc(rd);
+
+ rec->tupdesc = rd;
+ }
+ }
+ }
+
+ ReleaseCachedPlan(cplan, true);
+ }
+
+ /* last recheck */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ rec->tupdesc->attrs[0]->atttypid == RECORDOID &&
+ rec->tupdesc->attrs[0]->atttypmod == -1)
+ elog(ERROR, "cannot to identify real type for record type variable");
+
+ /* initialize rec by NULLs - variable should be valid */
+ nulls = (bool *) palloc(rec->tupdesc->natts * sizeof(bool));
+ memset(nulls, true, rec->tupdesc->natts * sizeof(bool));
+
+ tup = heap_form_tuple(rec->tupdesc, NULL, nulls);
+ if (HeapTupleIsValid(tup))
+ {
+ rec->tup = tup;
+ rec->freetup = true;
+ }
+ else
+ {
+ rec->tup = NULL;
+ rec->freetup = false;
+ }
+
+ estate->err_text = err_text;
+ }
+
+ /*
+ * Prepare plans walker - this can be used for checking
+ *
+ */
+ bool
+ plpgsql_expr_prepare_plan(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context)
+ {
+ PLpgSQL_execstate *estate = (PLpgSQL_execstate *) context;
+ int cursorOptions = 0;
+ const char *err_text = estate->err_text;
+
+ /* overwrite a estate variables */
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+ if (expr == NULL)
+ return false;
+
+ switch (stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_OPEN:
+ {
+ PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_open->curvar];
+
+ cursorOptions = curvar->cursor_options;
+ }
+ break;
+
+ case PLPGSQL_STMT_FORC:
+ {
+ PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_forc->curvar];
+
+ /*
+ * change a cursorOption only whenn this call is related to
+ * curvar->cursor_explicit_expr
+ */
+ if (curvar->cursor_explicit_expr == expr)
+ cursorOptions = curvar->cursor_options;
+ }
+ break;
+ }
+
+ /*
+ * If first time through, create a plan for this expression.
+ */
+ if (expr->plan == NULL)
+ {
+ exec_prepare_plan(estate, expr, cursorOptions);
+ }
+
+ /*
+ * very common practic in PLpgSQL is is using a record type. But any using of
+ * untyped record breaks a check. A solution is an prediction of record type based
+ * on plans - a following switch covers all PLpgSQL statements where a record
+ * variable can be assigned.
+ *
+ * when record is target of dynamic SQL statement, then raise exception
+ *
+ */
+ switch (stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_ASSIGN:
+ {
+ PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt;
+ PLpgSQL_datum *target = (estate->datums[stmt_assign->varno]);
+
+ if (target->dtype == PLPGSQL_DTYPE_REC)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) target,
+ stmt_assign->expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_EXECSQL:
+ {
+ PLpgSQL_stmt_execsql *stmt_execsql = (PLpgSQL_stmt_execsql *) stmt;
+ ListCell *l;
+
+ stmt_execsql->mod_stmt = false;
+ foreach(l, expr->plan->plancache_list)
+ {
+ CachedPlanSource *plansource = (CachedPlanSource *) lfirst(l);
+ CachedPlan *cplan = GetCachedPlan(plansource, NULL, true);
+ ListCell *l2;
+
+ foreach(l2, cplan->stmt_list)
+ {
+ PlannedStmt *p = (PlannedStmt *) lfirst(l2);
+
+ if (IsA(p, PlannedStmt) &&
+ p->canSetTag)
+ {
+ if (p->commandType == CMD_INSERT ||
+ p->commandType == CMD_UPDATE ||
+ p->commandType == CMD_DELETE)
+ stmt_execsql->mod_stmt = true;
+ }
+ }
+
+ ReleaseCachedPlan(cplan, true);
+ }
+
+ if (stmt_execsql->rec != NULL)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) (estate->datums[stmt_execsql->rec->dno]),
+ stmt_execsql->sqlstmt,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FETCH:
+ {
+ PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt;
+
+ /* fetch can not determinate a record datatype for refcursors */
+ if (stmt_fetch->rec != NULL)
+ {
+ PLpgSQL_var *curvar = (PLpgSQL_var *)( estate->datums[stmt_fetch->curvar]);
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_fetch->rec->dno]);
+
+ if (curvar->cursor_explicit_expr == NULL)
+ elog(ERROR, "cannot to determinate record type for refcursor");
+
+ set_rectype_for_query(estate, stmt,
+ rec,
+ curvar->cursor_explicit_expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FORS:
+ {
+ PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
+
+ if (stmt_fors->rec != NULL)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) (estate->datums[stmt_fors->rec->dno]),
+ stmt_fors->query,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FORC:
+ {
+ PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) (estate->datums[stmt_forc->curvar]);
+
+ if (stmt_forc->rec != NULL && curvar->cursor_explicit_expr == expr)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_forc->rec->dno]);
+
+ set_rectype_for_query(estate, stmt,
+ rec,
+ curvar->cursor_explicit_expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FOREACH_A:
+ {
+ PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
+ PLpgSQL_datum *loop_var = estate->datums[stmt_foreach_a->varno];
+
+ if (loop_var->dtype == PLPGSQL_DTYPE_REC)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) loop_var,
+ stmt_foreach_a->expr,
+ true);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_CASE:
+ {
+ PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt;
+ TupleDesc tupdesc;
+ Oid result_oid;
+
+ /*
+ * this is special case - a result type of expression should to
+ * overwrite a expected int datatype.
+ */
+ if (stmt_case->t_expr == expr)
+ {
+ CachedPlanSource *plansource = NULL;
+ const char *err_text = estate->err_text;
+
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+
+ if (expr->plan != NULL)
+ {
+ SPIPlanPtr plan = expr->plan;
+ PLpgSQL_var *t_var = (PLpgSQL_var *) estate->datums[stmt_case->t_varno];
+
+ if (plan == NULL || plan->magic != _SPI_PLAN_MAGIC)
+ elog(ERROR, "cached plan is not valid plan");
+
+ if (list_length(plan->plancache_list) != 1)
+ elog(ERROR, "plan is not single execution plan");
+
+ plansource = (CachedPlanSource *) linitial(plan->plancache_list);
+ tupdesc = CreateTupleDescCopy(plansource->resultDesc);
+
+ if (tupdesc->natts != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg_plural("query \"%s\" returned %d column",
+ "query \"%s\" returned %d columns",
+ tupdesc->natts,
+ expr->query,
+ tupdesc->natts)));
+
+ result_oid = tupdesc->attrs[0]->atttypid;
+
+ /*
+ * When expected datatype is different from real, change it. Note that
+ * what we're modifying here is an execution copy of the datum, so
+ * this doesn't affect the originally stored function parse tree.
+ */
+ if (t_var->datatype->typoid != result_oid)
+ t_var->datatype = plpgsql_build_datatype(result_oid,
+ -1,
+ estate->func->fn_input_collation);
+ FreeTupleDesc(tupdesc);
+ }
+ else
+ elog(ERROR, "there are no plan for query: \"%s\"",
+ expr->query);
+
+ estate->err_text = err_text;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNEXECUTE:
+ {
+ PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
+
+ if (stmt_dynexecute->into && stmt_dynexecute->rec != NULL)
+ elog(ERROR, "cannot to determine a result of dynamic SQL");
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNFORS:
+ {
+ PLpgSQL_stmt_dynfors *stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
+
+ if (stmt_dynfors->rec != NULL)
+ elog(ERROR, "cannot to determinate a result of dynamic SQL");
+ }
+ break;
+ }
+
+ estate->err_text = err_text;
+
+ return false;
+ }
+
+
+ /*
+ * Initialize variable to NULL
+ */
+ static void
+ var_init_to_null(PLpgSQL_execstate *estate, int varno)
+ {
+ PLpgSQL_var *var = (PLpgSQL_var *) estate->datums[varno];
+ var->value = (Datum) 0;
+ var->isnull = true;
+ var->freeval = false;
+ }
+
+ /*
+ * Check function - it prepare variables and starts a prepare plan walker
+ * called by function checker
+ *
+ */
+ void
+ plpgsql_check_function(PLpgSQL_function *func, FunctionCallInfo fcinfo)
+ {
+ PLpgSQL_execstate estate;
+ ErrorContextCallback plerrcontext;
+ int i;
+
+ /*
+ * Setup the execution state
+ */
+ plpgsql_estate_setup(&estate, func, (ReturnSetInfo *) fcinfo->resultinfo);
+
+ /*
+ * Setup error traceback support for ereport()
+ */
+ plerrcontext.callback = plpgsql_exec_error_callback;
+ plerrcontext.arg = &estate;
+ plerrcontext.previous = error_context_stack;
+ error_context_stack = &plerrcontext;
+
+ /*
+ * Make local execution copies of all the datums
+ */
+ for (i = 0; i < estate.ndatums; i++)
+ estate.datums[i] = copy_plpgsql_datum(func->datums[i]);
+
+ /*
+ * Store the actual call argument values into the appropriate variables
+ */
+ for (i = 0; i < func->fn_nargs; i++)
+ {
+ int n = func->fn_argvarnos[i];
+
+ switch (estate.datums[n]->dtype)
+ {
+ case PLPGSQL_DTYPE_VAR:
+ {
+ var_init_to_null(&estate, n);
+ }
+ break;
+
+ case PLPGSQL_DTYPE_ROW:
+ {
+ PLpgSQL_row *row = (PLpgSQL_row *) estate.datums[n];
+
+ exec_move_row(&estate, NULL, row, NULL, NULL);
+ }
+ break;
+
+ default:
+ elog(ERROR, "unrecognized dtype: %d", func->datums[i]->dtype);
+ }
+ }
+
+ /*
+ * Now call the toplevel block of statements
+ */
+ estate.err_text = NULL;
+ estate.err_stmt = (PLpgSQL_stmt *) (func->action);
+
+ plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
+ plpgsql_expr_prepare_plan,
+ &estate);
+
+ estate.err_stmt = NULL;
+ estate.err_text = gettext_noop("while casting return value to function's return type");
+
+ /* Clean up any leftover temporary memory */
+ plpgsql_destroy_econtext(&estate);
+ exec_eval_cleanup(&estate);
+
+ /*
+ * Pop the error context stack
+ */
+ error_context_stack = plerrcontext.previous;
+ }
+
+ /*
+ * Check trigger - prepare fake environments for testing trigger
+ *
+ */
+ void
+ plpgsql_check_trigger(PLpgSQL_function *func,
+ TriggerData *trigdata)
+ {
+ PLpgSQL_execstate estate;
+ ErrorContextCallback plerrcontext;
+ int i;
+ PLpgSQL_rec *rec_new,
+ *rec_old;
+
+ /*
+ * Setup the execution state
+ */
+ plpgsql_estate_setup(&estate, func, NULL);
+
+ /*
+ * Setup error traceback support for ereport()
+ */
+ plerrcontext.callback = plpgsql_exec_error_callback;
+ plerrcontext.arg = &estate;
+ plerrcontext.previous = error_context_stack;
+ error_context_stack = &plerrcontext;
+
+ /*
+ * Make local execution copies of all the datums
+ */
+ estate.err_text = gettext_noop("during initialization of execution state");
+ for (i = 0; i < estate.ndatums; i++)
+ estate.datums[i] = copy_plpgsql_datum(func->datums[i]);
+
+ /*
+ * Put the OLD and NEW tuples into record variables
+ *
+ * We make the tupdescs available in both records even though only one may
+ * have a value. This allows parsing of record references to succeed in
+ * functions that are used for multiple trigger types. For example, we
+ * might have a test like "if (TG_OP = 'INSERT' and NEW.foo = 'xyz')",
+ * which should parse regardless of the current trigger type.
+ */
+ rec_new = (PLpgSQL_rec *) (estate.datums[func->new_varno]);
+ rec_new->freetup = false;
+ rec_new->tupdesc = trigdata->tg_relation->rd_att;
+ rec_new->freetupdesc = false;
+ rec_old = (PLpgSQL_rec *) (estate.datums[func->old_varno]);
+ rec_old->freetup = false;
+ rec_old->tupdesc = trigdata->tg_relation->rd_att;
+ rec_old->freetupdesc = false;
+
+ /*
+ * Assign the special tg_ variables
+ */
+ var_init_to_null(&estate, func->tg_op_varno);
+ var_init_to_null(&estate, func->tg_name_varno);
+ var_init_to_null(&estate, func->tg_when_varno);
+ var_init_to_null(&estate, func->tg_level_varno);
+ var_init_to_null(&estate, func->tg_relid_varno);
+ var_init_to_null(&estate, func->tg_relname_varno);
+ var_init_to_null(&estate, func->tg_table_name_varno);
+ var_init_to_null(&estate, func->tg_table_schema_varno);
+ var_init_to_null(&estate, func->tg_nargs_varno);
+ var_init_to_null(&estate, func->tg_argv_varno);
+
+ /*
+ * Now call the toplevel block of statements
+ */
+ estate.err_text = NULL;
+ estate.err_stmt = (PLpgSQL_stmt *) (func->action);
+
+ plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
+ plpgsql_expr_prepare_plan,
+ &estate);
+
+ estate.err_stmt = NULL;
+ estate.err_text = gettext_noop("while casting return value to function's return type");
+
+ /* Clean up any leftover temporary memory */
+ plpgsql_destroy_econtext(&estate);
+ exec_eval_cleanup(&estate);
+
+ /*
+ * Pop the error context stack
+ */
+ error_context_stack = plerrcontext.previous;
+ }
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2011-10-04 13:58:46.624303816 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2011-10-04 13:59:14.325344608 +0200
***************
*** 293,681 ****
}
! /**********************************************************************
! * Release memory when a PL/pgSQL function is no longer needed
*
! * The code for recursing through the function tree is really only
! * needed to locate PLpgSQL_expr nodes, which may contain references
! * to saved SPI Plans that must be freed. The function tree itself,
! * along with subsidiary data, is freed in one swoop by freeing the
! * function's permanent memory context.
! **********************************************************************/
! static void free_stmt(PLpgSQL_stmt *stmt);
! static void free_block(PLpgSQL_stmt_block *block);
! static void free_assign(PLpgSQL_stmt_assign *stmt);
! static void free_if(PLpgSQL_stmt_if *stmt);
! static void free_case(PLpgSQL_stmt_case *stmt);
! static void free_loop(PLpgSQL_stmt_loop *stmt);
! static void free_while(PLpgSQL_stmt_while *stmt);
! static void free_fori(PLpgSQL_stmt_fori *stmt);
! static void free_fors(PLpgSQL_stmt_fors *stmt);
! static void free_forc(PLpgSQL_stmt_forc *stmt);
! static void free_foreach_a(PLpgSQL_stmt_foreach_a *stmt);
! static void free_exit(PLpgSQL_stmt_exit *stmt);
! static void free_return(PLpgSQL_stmt_return *stmt);
! static void free_return_next(PLpgSQL_stmt_return_next *stmt);
! static void free_return_query(PLpgSQL_stmt_return_query *stmt);
! static void free_raise(PLpgSQL_stmt_raise *stmt);
! static void free_execsql(PLpgSQL_stmt_execsql *stmt);
! static void free_dynexecute(PLpgSQL_stmt_dynexecute *stmt);
! static void free_dynfors(PLpgSQL_stmt_dynfors *stmt);
! static void free_getdiag(PLpgSQL_stmt_getdiag *stmt);
! static void free_open(PLpgSQL_stmt_open *stmt);
! static void free_fetch(PLpgSQL_stmt_fetch *stmt);
! static void free_close(PLpgSQL_stmt_close *stmt);
! static void free_perform(PLpgSQL_stmt_perform *stmt);
! static void free_expr(PLpgSQL_expr *expr);
! static void
! free_stmt(PLpgSQL_stmt *stmt)
{
switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
{
case PLPGSQL_STMT_BLOCK:
! free_block((PLpgSQL_stmt_block *) stmt);
! break;
case PLPGSQL_STMT_ASSIGN:
! free_assign((PLpgSQL_stmt_assign *) stmt);
! break;
case PLPGSQL_STMT_IF:
! free_if((PLpgSQL_stmt_if *) stmt);
! break;
case PLPGSQL_STMT_CASE:
! free_case((PLpgSQL_stmt_case *) stmt);
! break;
case PLPGSQL_STMT_LOOP:
! free_loop((PLpgSQL_stmt_loop *) stmt);
! break;
case PLPGSQL_STMT_WHILE:
! free_while((PLpgSQL_stmt_while *) stmt);
! break;
case PLPGSQL_STMT_FORI:
! free_fori((PLpgSQL_stmt_fori *) stmt);
! break;
case PLPGSQL_STMT_FORS:
! free_fors((PLpgSQL_stmt_fors *) stmt);
! break;
case PLPGSQL_STMT_FORC:
! free_forc((PLpgSQL_stmt_forc *) stmt);
! break;
! case PLPGSQL_STMT_FOREACH_A:
! free_foreach_a((PLpgSQL_stmt_foreach_a *) stmt);
! break;
! case PLPGSQL_STMT_EXIT:
! free_exit((PLpgSQL_stmt_exit *) stmt);
! break;
! case PLPGSQL_STMT_RETURN:
! free_return((PLpgSQL_stmt_return *) stmt);
! break;
! case PLPGSQL_STMT_RETURN_NEXT:
! free_return_next((PLpgSQL_stmt_return_next *) stmt);
! break;
! case PLPGSQL_STMT_RETURN_QUERY:
! free_return_query((PLpgSQL_stmt_return_query *) stmt);
! break;
! case PLPGSQL_STMT_RAISE:
! free_raise((PLpgSQL_stmt_raise *) stmt);
! break;
! case PLPGSQL_STMT_EXECSQL:
! free_execsql((PLpgSQL_stmt_execsql *) stmt);
! break;
! case PLPGSQL_STMT_DYNEXECUTE:
! free_dynexecute((PLpgSQL_stmt_dynexecute *) stmt);
! break;
case PLPGSQL_STMT_DYNFORS:
! free_dynfors((PLpgSQL_stmt_dynfors *) stmt);
! break;
! case PLPGSQL_STMT_GETDIAG:
! free_getdiag((PLpgSQL_stmt_getdiag *) stmt);
! break;
! case PLPGSQL_STMT_OPEN:
! free_open((PLpgSQL_stmt_open *) stmt);
! break;
! case PLPGSQL_STMT_FETCH:
! free_fetch((PLpgSQL_stmt_fetch *) stmt);
! break;
! case PLPGSQL_STMT_CLOSE:
! free_close((PLpgSQL_stmt_close *) stmt);
! break;
! case PLPGSQL_STMT_PERFORM:
! free_perform((PLpgSQL_stmt_perform *) stmt);
! break;
! default:
! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
! break;
! }
! }
! static void
! free_stmts(List *stmts)
! {
! ListCell *s;
! foreach(s, stmts)
! {
! free_stmt((PLpgSQL_stmt *) lfirst(s));
! }
! }
! static void
! free_block(PLpgSQL_stmt_block *block)
! {
! free_stmts(block->body);
! if (block->exceptions)
! {
! ListCell *e;
! foreach(e, block->exceptions->exc_list)
! {
! PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
! free_stmts(exc->action);
! }
! }
! }
! static void
! free_assign(PLpgSQL_stmt_assign *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_if(PLpgSQL_stmt_if *stmt)
! {
! free_expr(stmt->cond);
! free_stmts(stmt->true_body);
! free_stmts(stmt->false_body);
! }
! static void
! free_case(PLpgSQL_stmt_case *stmt)
! {
! ListCell *l;
! free_expr(stmt->t_expr);
! foreach(l, stmt->case_when_list)
! {
! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
! free_expr(cwt->expr);
! free_stmts(cwt->stmts);
! }
! free_stmts(stmt->else_stmts);
! }
! static void
! free_loop(PLpgSQL_stmt_loop *stmt)
! {
! free_stmts(stmt->body);
! }
! static void
! free_while(PLpgSQL_stmt_while *stmt)
! {
! free_expr(stmt->cond);
! free_stmts(stmt->body);
! }
! static void
! free_fori(PLpgSQL_stmt_fori *stmt)
! {
! free_expr(stmt->lower);
! free_expr(stmt->upper);
! free_expr(stmt->step);
! free_stmts(stmt->body);
! }
! static void
! free_fors(PLpgSQL_stmt_fors *stmt)
! {
! free_stmts(stmt->body);
! free_expr(stmt->query);
! }
! static void
! free_forc(PLpgSQL_stmt_forc *stmt)
! {
! free_stmts(stmt->body);
! free_expr(stmt->argquery);
! }
! static void
! free_foreach_a(PLpgSQL_stmt_foreach_a *stmt)
! {
! free_expr(stmt->expr);
! free_stmts(stmt->body);
! }
! static void
! free_open(PLpgSQL_stmt_open *stmt)
! {
! ListCell *lc;
! free_expr(stmt->argquery);
! free_expr(stmt->query);
! free_expr(stmt->dynquery);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_fetch(PLpgSQL_stmt_fetch *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_close(PLpgSQL_stmt_close *stmt)
! {
! }
! static void
! free_perform(PLpgSQL_stmt_perform *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_exit(PLpgSQL_stmt_exit *stmt)
! {
! free_expr(stmt->cond);
! }
! static void
! free_return(PLpgSQL_stmt_return *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_return_next(PLpgSQL_stmt_return_next *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_return_query(PLpgSQL_stmt_return_query *stmt)
! {
! ListCell *lc;
! free_expr(stmt->query);
! free_expr(stmt->dynquery);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_raise(PLpgSQL_stmt_raise *stmt)
! {
! ListCell *lc;
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! foreach(lc, stmt->options)
! {
! PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(lc);
! free_expr(opt->expr);
! }
! }
! static void
! free_execsql(PLpgSQL_stmt_execsql *stmt)
! {
! free_expr(stmt->sqlstmt);
! }
! static void
! free_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
! {
! ListCell *lc;
! free_expr(stmt->query);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_dynfors(PLpgSQL_stmt_dynfors *stmt)
! {
! ListCell *lc;
! free_stmts(stmt->body);
! free_expr(stmt->query);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
}
}
! static void
! free_getdiag(PLpgSQL_stmt_getdiag *stmt)
! {
! }
! static void
! free_expr(PLpgSQL_expr *expr)
{
if (expr && expr->plan)
{
SPI_freeplan(expr->plan);
expr->plan = NULL;
}
}
void
plpgsql_free_function_memory(PLpgSQL_function *func)
{
- int i;
-
/* Better not call this on an in-use function */
Assert(func->use_count == 0);
- /* Release plans associated with variable declarations */
- for (i = 0; i < func->ndatums; i++)
- {
- PLpgSQL_datum *d = func->datums[i];
-
- switch (d->dtype)
- {
- case PLPGSQL_DTYPE_VAR:
- {
- PLpgSQL_var *var = (PLpgSQL_var *) d;
-
- free_expr(var->default_val);
- free_expr(var->cursor_explicit_expr);
- }
- break;
- case PLPGSQL_DTYPE_ROW:
- break;
- case PLPGSQL_DTYPE_REC:
- break;
- case PLPGSQL_DTYPE_RECFIELD:
- break;
- case PLPGSQL_DTYPE_ARRAYELEM:
- free_expr(((PLpgSQL_arrayelem *) d)->subscript);
- break;
- default:
- elog(ERROR, "unrecognized data type: %d", d->dtype);
- }
- }
- func->ndatums = 0;
-
/* Release plans in statement tree */
! if (func->action)
! free_block(func->action);
func->action = NULL;
/*
--- 293,653 ----
}
! /*
! * call a plpgsql_expr_walker for any statement in list
*
! */
! static bool
! plpgsql_expr_walker_list(PLpgSQL_function *func, List *stmts,
! bool (*expr_walker)(),
! void *context)
! {
! ListCell *lc;
+ foreach(lc, stmts)
+ {
+ PLpgSQL_stmt *stmt = (PLpgSQL_stmt *) lfirst(lc);
! if (plpgsql_expr_walker(func, stmt, expr_walker, context))
! return true;
! }
! return false;
! }
!
! /*
! * walk over all expressions inside statements tree
! *
! * stmt_walker is function called for every stmt and should be NULL
! *
! */
! bool
! plpgsql_expr_walker(PLpgSQL_function *func,
! PLpgSQL_stmt *stmt,
! bool (*expr_walker)(),
! void *context)
{
+ ListCell *l;
+
+ if (stmt == NULL)
+ return false;
+
switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
{
case PLPGSQL_STMT_BLOCK:
! {
! PLpgSQL_stmt_block *stmt_block = (PLpgSQL_stmt_block *) stmt;
! int i;
! PLpgSQL_datum *d;
!
! for (i = 0; i < stmt_block->n_initvars; i++)
! {
! d = func->datums[stmt_block->initvarnos[i]];
!
! switch(d->dtype)
! {
! case PLPGSQL_DTYPE_VAR:
! {
! PLpgSQL_var *var = (PLpgSQL_var *) d;
!
! if (expr_walker(stmt, var->default_val, context))
! return true;
!
! /*
! * theoretically there is place for walk over var->cursor_explicit_expr,
! * but we would not to call process too early. In this moment a some
! * record parameters should be unknown. So we will wait on better moment
! *
! * if (expr_walker(stmt, var->cursor_explicit_expr, context))
! * return true;
! */
! }
! break;
! case PLPGSQL_DTYPE_ROW:
! case PLPGSQL_DTYPE_REC:
! case PLPGSQL_DTYPE_RECFIELD:
! break;
! case PLPGSQL_DTYPE_ARRAYELEM:
! if (expr_walker(stmt, ((PLpgSQL_arrayelem *) d)->subscript, context))
! return true;
! default:
! elog(ERROR, "unrecognized data type: %d", d->dtype);
! }
! }
!
! if (plpgsql_expr_walker_list(func, stmt_block->body, expr_walker, context))
! return true;
!
! if (stmt_block->exceptions)
! {
! foreach(l, stmt_block->exceptions->exc_list)
! {
! if (plpgsql_expr_walker_list(func, ((PLpgSQL_exception *) lfirst(l))->action,
! expr_walker,
! context))
! return true;
! }
! }
!
! return false;
! }
!
case PLPGSQL_STMT_ASSIGN:
! return expr_walker(stmt, ((PLpgSQL_stmt_assign *) stmt)->expr, context);
!
case PLPGSQL_STMT_IF:
! {
! PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt;
!
! if (expr_walker(stmt, stmt_if->cond, context))
! return true;
!
! if (plpgsql_expr_walker_list(func, stmt_if->true_body, expr_walker, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_if->false_body, expr_walker, context);
! }
!
case PLPGSQL_STMT_CASE:
! {
! PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt;
!
! if (expr_walker(stmt, stmt_case->t_expr, context))
! return true;
!
! foreach(l, stmt_case->case_when_list)
! {
! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
!
! if (expr_walker(stmt, cwt->expr, context))
! return true;
!
! if (plpgsql_expr_walker_list(func, cwt->stmts, expr_walker, context))
! return true;
! }
!
! return plpgsql_expr_walker_list(func, stmt_case->else_stmts, expr_walker, context);
! }
!
case PLPGSQL_STMT_LOOP:
! return plpgsql_expr_walker_list(func, ((PLpgSQL_stmt_loop *) stmt)->body, expr_walker, context);
!
case PLPGSQL_STMT_WHILE:
! {
! PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt;
!
! if (expr_walker(stmt, stmt_while->cond, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_while->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORI:
! {
! PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt;
!
! if (expr_walker(stmt, stmt_fori->lower, context))
! return true;
!
! if (expr_walker(stmt, stmt_fori->upper, context))
! return true;
!
! if (expr_walker(stmt, stmt_fori->step, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_fori->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORS:
! {
! PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
!
! if (expr_walker(stmt, stmt_fors->query, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_fors->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORC:
! {
! PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar];
!
! if (expr_walker(stmt, stmt_forc->argquery, context))
! return true;
!
! if (expr_walker(stmt, var->cursor_explicit_expr, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_forc->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_DYNFORS:
! {
! PLpgSQL_stmt_dynfors * stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
! if (expr_walker(stmt, stmt_dynfors->query, context))
! return true;
! foreach(l, stmt_dynfors->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return plpgsql_expr_walker_list(func, stmt_dynfors->body, expr_walker, context);
! }
! case PLPGSQL_STMT_FOREACH_A:
! {
! PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
! if (expr_walker(stmt, stmt_foreach_a->expr, context))
! return true;
! return plpgsql_expr_walker_list(func, stmt_foreach_a->body, expr_walker, context);
! }
! case PLPGSQL_STMT_EXIT:
! return expr_walker(stmt, ((PLpgSQL_stmt_exit *) stmt)->cond, context);
! case PLPGSQL_STMT_PERFORM:
! return expr_walker(stmt, ((PLpgSQL_stmt_perform *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN:
! return expr_walker(stmt, ((PLpgSQL_stmt_return *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN_NEXT:
! return expr_walker(stmt, ((PLpgSQL_stmt_return_next *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN_QUERY:
! {
! PLpgSQL_stmt_return_query *stmt_rq = (PLpgSQL_stmt_return_query *) stmt;
! if (expr_walker(stmt, stmt_rq->query, context))
! return true;
! if (expr_walker(stmt, stmt_rq->dynquery, context))
! return true;
! foreach(l, stmt_rq->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_RAISE:
! {
! PLpgSQL_stmt_raise *stmt_raise = (PLpgSQL_stmt_raise *) stmt;
! foreach(l, stmt_raise->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! foreach(l, stmt_raise->options)
! {
! if (expr_walker(stmt, ((PLpgSQL_raise_option *) lfirst(l))->expr, context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_EXECSQL:
! return expr_walker(stmt, ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt, context);
! case PLPGSQL_STMT_DYNEXECUTE:
! {
! PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
! if (expr_walker(stmt, stmt_dynexecute->query, context))
! return true;
! foreach(l, stmt_dynexecute->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! break;
! case PLPGSQL_STMT_GETDIAG:
! return false;
! case PLPGSQL_STMT_OPEN:
! {
! PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt;
! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_open->curvar];
! if (expr_walker(stmt, var->cursor_explicit_expr, context))
! return true;
! if (expr_walker(stmt, stmt_open->query, context))
! return true;
! if (expr_walker(stmt, stmt_open->dynquery, context))
! return true;
! if (expr_walker(stmt, stmt_open->argquery, context))
! return true;
! foreach(l, stmt_open->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_FETCH:
! case PLPGSQL_STMT_CLOSE:
! return false;
! default:
! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
! return false; /* be compiler quite */
}
}
! /**********************************************************************
! * Release memory when a PL/pgSQL function is no longer needed
! *
! * The code for recursing through the function tree is really only
! * needed to locate PLpgSQL_expr nodes, which may contain references
! * to saved SPI Plans that must be freed. The function tree itself,
! * along with subsidiary data, is freed in one swoop by freeing the
! * function's permanent memory context.
! **********************************************************************/
! static bool
! free_expression(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context)
{
if (expr && expr->plan)
{
SPI_freeplan(expr->plan);
expr->plan = NULL;
}
+ return false;
}
void
plpgsql_free_function_memory(PLpgSQL_function *func)
{
/* Better not call this on an in-use function */
Assert(func->use_count == 0);
/* Release plans in statement tree */
! plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
! free_expression,
! NULL);
! func->ndatums = 0;
func->action = NULL;
/*
*** ./src/pl/plpgsql/src/pl_handler.c.orig 2011-10-04 13:58:46.626303819 +0200
--- ./src/pl/plpgsql/src/pl_handler.c 2011-10-05 09:02:51.295554897 +0200
***************
*** 312,314 ****
--- 312,429 ----
PG_RETURN_VOID();
}
+
+ /* ----------
+ * plpgsql_checker
+ *
+ * This function attempts to check a embeded SQL inside a PL/pgSQL function at
+ * CHECK FUNCTION time. It should to have one or two parameters. Second
+ * parameter is a relation (used when function is trigger).
+ * ----------
+ */
+ PG_FUNCTION_INFO_V1(plpgsql_checker);
+
+ Datum
+ plpgsql_checker(PG_FUNCTION_ARGS)
+ {
+ Oid funcoid = PG_GETARG_OID(0);
+ Oid relid = PG_GETARG_OID(1);
+ HeapTuple tuple;
+ FunctionCallInfoData fake_fcinfo;
+ FmgrInfo flinfo;
+ TriggerData trigdata;
+ int rc;
+ PLpgSQL_function *function;
+
+ Form_pg_proc proc;
+ char functyptype;
+ bool istrigger = false;
+ PLpgSQL_execstate *save_cur_estate;
+
+ /* we don't need to repair a check done by validator */
+
+ tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for function %u", funcoid);
+ proc = (Form_pg_proc) GETSTRUCT(tuple);
+
+ functyptype = get_typtype(proc->prorettype);
+
+ if (functyptype == TYPTYPE_PSEUDO)
+ {
+ /* we assume OPAQUE with no arguments means a trigger */
+ if (proc->prorettype == TRIGGEROID ||
+ (proc->prorettype == OPAQUEOID && proc->pronargs == 0))
+ {
+ istrigger = true;
+ if (!OidIsValid(relid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PL/pgSQL trigger functions cannot be checked directly"),
+ errhint("use CHECK TRIGGER statement instead")));
+ }
+ }
+
+ /*
+ * Connect to SPI manager
+ */
+ if ((rc = SPI_connect()) != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc));
+
+ /*
+ * Set up a fake fcinfo with just enough info to satisfy
+ * plpgsql_compile().
+ *
+ * there should be a different real argtypes for polymorphic params
+ */
+ MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo));
+ MemSet(&flinfo, 0, sizeof(flinfo));
+ fake_fcinfo.flinfo = &flinfo;
+ flinfo.fn_oid = funcoid;
+ flinfo.fn_mcxt = CurrentMemoryContext;
+ if (istrigger)
+ {
+ MemSet(&trigdata, 0, sizeof(trigdata));
+ trigdata.type = T_TriggerData;
+ trigdata.tg_relation = relation_open(relid, AccessShareLock);
+ fake_fcinfo.context = (Node *) &trigdata;
+ }
+
+ /* Get a compiled function */
+ function = plpgsql_compile(&fake_fcinfo, true);
+
+ /* Must save and restore prior value of cur_estate */
+ save_cur_estate = function->cur_estate;
+
+ /* Create a fake runtime environment and prepare plans */
+ PG_TRY();
+ {
+ if (!istrigger)
+ plpgsql_check_function(function, &fake_fcinfo);
+ else
+ plpgsql_check_trigger(function, &trigdata);
+ }
+ PG_CATCH();
+ {
+ if (istrigger)
+ relation_close(trigdata.tg_relation, AccessShareLock);
+ function->cur_estate = save_cur_estate;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ if (istrigger)
+ relation_close(trigdata.tg_relation, AccessShareLock);
+
+ function->cur_estate = save_cur_estate;
+
+ /*
+ * Disconnect from SPI manager
+ */
+ if ((rc = SPI_finish()) != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc));
+
+ ReleaseSysCache(tuple);
+
+ PG_RETURN_VOID();
+ }
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2011-10-04 13:56:41.052123789 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2011-10-05 07:43:53.217212322 +0200
***************
*** 903,908 ****
--- 903,909 ----
extern Datum plpgsql_call_handler(PG_FUNCTION_ARGS);
extern Datum plpgsql_inline_handler(PG_FUNCTION_ARGS);
extern Datum plpgsql_validator(PG_FUNCTION_ARGS);
+ extern Datum plpgsql_checker(PG_FUNCTION_ARGS);
/* ----------
* Functions in pl_exec.c
***************
*** 920,925 ****
--- 921,929 ----
extern void exec_get_datum_type_info(PLpgSQL_execstate *estate,
PLpgSQL_datum *datum,
Oid *typeid, int32 *typmod, Oid *collation);
+ extern bool plpgsql_expr_prepare_plan(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context);
+ extern void plpgsql_check_function(PLpgSQL_function *func, FunctionCallInfo fcinfo);
+ extern void plpgsql_check_trigger(PLpgSQL_function *func, TriggerData *trigdata);
/* ----------
* Functions for namespace handling in pl_funcs.c
***************
*** 944,949 ****
--- 948,956 ----
extern const char *plpgsql_getdiag_kindname(int kind);
extern void plpgsql_free_function_memory(PLpgSQL_function *func);
extern void plpgsql_dumptree(PLpgSQL_function *func);
+ extern bool plpgsql_expr_walker(PLpgSQL_function *func,
+ PLpgSQL_stmt *stmt,
+ bool (*expr_walker)(), void *context);
/* ----------
* Scanner functions in pl_scanner.c
*** ./src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql.orig 2011-10-04 13:58:46.628303823 +0200
--- ./src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql 2011-10-04 13:59:14.326344609 +0200
***************
*** 5,7 ****
--- 5,8 ----
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_call_handler();
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_inline_handler(internal);
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_validator(oid);
+ ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_checker(oid, regclass);