| General Bits is a column based on the PostgreSQL mailing list pgsql-general. To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org. |
|---|
|
|---|
|
| |
|
|
|
There was an editing mistake in the Issue 30 article about finding and fixing duplicate rows. (Why didn't someone else find this and tell me?)
The function fixdupes() had an incorrect targetlist for the selection in the function. The corrected function in full is:
create or replace function fixdupes()
returns void as '
DECLARE
d record;
BEGIN
for d in select *
from rawlog
group by ldate, ltime, doc, ip, method, qs, code, dsize
having count(*) > 1 limit 1
loop
delete from rawlog
where ldate=d.ldate and ltime=d.ltime and
doc=d.doc and ip=d.ip and method=d.method and
qs=d.qs and code=d.code and dsize=d.dsize;
insert into rawlog values (d.ip, d.ldate, d.ltime,
d.method, d.doc, d.qs, d.code, d.dsize);
end loop;
RETURN;
END;
' language 'plpgsql';
|
| |
|
|
|
PostgreSQL supports trusted and untrusted languages for server side functions. As a general rule (except for C) untrusted languages have a u suffix to their name. For example, plperl is trusted but plperlu is an untrusted.
A function written in a trusted language, a trusted function, is safe for general usage. A function written in an untrusted language, an untrusted function, can also be written safely, but the possibility exists for abuse.
Tom Lane writes, "[a] trusted-PL language should be able to define arbitrary self-contained computations (arithmetic, pattern-matching, or what have you), and it should be able to access the database at the same level as regular SQL commands. It should not be able to bypass the SQL abstractions nor execute any OS-level operations using the postgres user's privileges." Trusted languages explicitly limit their functionality via permissions and by language interpreters to prevent invoking "dangerous" functionality.
So an untrusted language is not, as one might think, a buggy or bad implementation of the language, but rather one which allows expanded functionality to be performed in a server function under the permissions of the postgres superuser.
That "expanded" or "dangerous" functionality is widely defined as anything that operates outside of the database on the understanding that anything outside of the database could be exploited to cause damage. File system I/O, many specific OS calls, memory management, process creating and interprocess communication are some of the things that are classified as dangerous.
Untrusted languages must be explicitly installed by the database superuser and permission to create functions and triggers using untrusted languages is limited to users with superuser priviledge. The responsibility for good citizenship is placed on the superuser(s).
After understanding all of the warnings, untrusted functions can be extremely important to an application. There are many legitimate and valuable cases where untrusted languages and operations are extremely useful, for example, sending email, safe file system reads and writes and informational OS calls. It is also possible to write a function in an untrusted language that does not use any of the untrusted features, for example most C functions available in contrib fall into this category.
Untrusted does not mean that the language is not to be used. It means that it must be used carefully. An untrusted language can be used very beneficially by someone who understands exactly the repercussions of their code.
|
| |
|
|
|
In 7.4, plpython will move from a trusted language to an untrusted language and its name will change to plpythonu. The name change emphasizes that the language is now untrusted. (See the above article in this issue for an explanation of trusted and untrusted languages.)
Guido Van Rossum, et al, the Python team, have determined that rexec (restricted execution) was fundamentally insecure in python and so it was removed from language as of version 2.3.
The earlier version of plpython enabled a limited set of python calls via rexec. With the new understanding of the frailities of rexec, plpython was then insecure, not having a safe, restricted execution environment and was also incompatible with python 2.3.
Kevin Jacobs submitted a patch to plpython removing the rexec references and marked the language as untrusted. The patch was applied to the development branch targeted for 7.4.
People using plpython will probably need to reload their functions with the language changed to plpythonu in 7.4. Check the release notes for proper handling when the 7.4 version comes out. Also only people with superuser priviledges will be able to define functions using plpythonu.
|
| |
|
|
|
ROWTYPEs of tables with dropped columns can be frustrating and confusing. If you declare a variable to be the rowtype of your table and select into it, the selection set and the target rowtype will be different in that the row type will still contain any dropped columns.
A row type with dropped columns can viewed using \d and the dropped column is not shown. However, by selecting from the pg_attribute table you can see where the dropped column was. It would look like this:
attnum | attname | typname | case
--------+------------------------------+-----------+----------------
-7 | tableoid | oid | '-7'::oid
-6 | cmax | cid | '-6'::cid
-5 | xmax | xid | '-5'::xid
-4 | cmin | cid | '-4'::cid
-3 | xmin | xid | '-3'::xid
-2 | oid | oid | '-2'::oid
-1 | ctid | tid | '-1'::tid
1 | col_int | int4 | '1'::int4
2 | col_timestamp | timestamp | '2'::timestamp
3 | col_float | float8 | '3'::float8
4 | col_text | text | '4'::text
5 | ........pg.dropped.5........ | text |
6 | mod_timestamp | timestamp | '6'::timestamp
(13 rows)
A function like this one will not work as expected. The * will select attributes 1, 2, 3, 4, and 6. However, the rowtype will have attributes 1, 2, 3, 4, 5 and 6, causing a mismatch on the return types.
CREATE OR REPLACE FUNCTION testrows ( integer )
RETURNS foo
AS '
DECLARE
myrec foo%ROWTYPE;
BEGIN
SELECT INTO myrec *
FROM foo
WHERE foo.col_int = $1;
RETURN myrec;
END;
'
LANGUAGE 'plpgsql';
select * from do_testrows(2);
WARNING: Error occurred while executing PL/pgSQL function testrows
WARNING: line 5 at select into variables
ERROR: Bad timestamp external representation '2.3'
If you do not get an
error on mismatched returned types, you will get the following error:
ERROR: Function bartest() does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
This behaviour is a known problem. The workaround for this problem is to use a RECORD type instead of a ROWTYPE or possible designate your target list explicitly, including values for the "missing" columns or define a ROWTYPE which matches your altered table and use it instead of the table's ROWTYPE.
The fix to this bug would require changes to all places in the code where rowtypes are accessed. This is a lot of places. No one has yet determined a way to correct the problem as a universal change.
In the meanwhile, use caution when using ROWTYPES with altered tables.
|
| |
|
|
|
plpgsql functions create internal dependencies on objects used it its queries. Objects include indexes, other functions, tables and views.
When a plpgsql function is first run a plan is created and stored for queries within the function. This enable plpgsql to be fairly speedy.
If the plan has dependent objects the function will not run. You might think that dropping an index should not affect the function, however, due to the stored query plan it does.
If a query in the plpgsql function stores a plan that uses an index AND that index is dropped then running the function will cause an error about a missing relation:
ERROR: Relation 3912941 does not exist
Dropping and recreating the function will repair the problem. The plan is dropped when the function is dropped and a new plan is saved when it is run again.
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!.
Copyright A. Elein Mustain 2003