Обсуждение: when to use "execute" in plpgsql?
Hello,
I'm having some troubles with the correct use of the execute plpgsql
statement. Where I work, we have a postgresql db hosting a set of
schemas all with the same tables and, from time to time, we upgrade
the schemas to a new version coding a stored procedure like the
following (pseudocode):
-------> example use case <--------
-- upgrade function, gets a schema name as input and upgrades it
create function upgrade_to_new_version(schema_name name)
returns void as $$
begin
-- become the schema owner
execute 'set role to ' || schema_name;
/* perform DDL and data transformations work here */
/* body here */
end;
language plpgsql volatile strict;
-- schemas_to_upgrade contains a column sname with the names of
-- the schemas needing an upgrade
select upgrade_to_new_version(sname) from schemas_to_upgrade;
--------->example end<-------------
the strange thing is that from time to time the function doesn't work.
Or, even worst, It works for a database but doesn't on another. The
issue usually goes away if we substitute the statement into /* body
here */ prepending those with an execute and submitting those via
execute; apparently the behaviour is reproducibile given a database
instance (i.e. it is not random), but it is impossible (at least for
us) to tell in advance if it will happen on another database (with the
same schemas and postgresql version number which, by the way, is the
official 8.3.5 on centos5/x86_64). The "safe" way to do things (it
never breaks) is to pass every statement via executes but we would
like to dig on this.
What are we missing?
Thanks in advance for your help,
enrico
Hi, check this out: http://archives.postgresql.org/pgsql-general/2008-05/msg00938.php I would say that execute is the only way to achieve some things related to schemas and temp tables.
On Fri, Feb 27, 2009 at 12:00 PM, Enrico Sirola <enrico.sirola@gmail.com> wrote: > Hello, > > I'm having some troubles with the correct use of the execute plpgsql > statement. Where I work, we have a postgresql db hosting a set of schemas > all with the same tables and, from time to time, we upgrade the schemas to a > new version coding a stored procedure like the following (pseudocode): > > -------> example use case <-------- > > -- upgrade function, gets a schema name as input and upgrades it > create function upgrade_to_new_version(schema_name name) > returns void as $$ > begin I'm not completely sure what the problem is because this is light on detail, but here's a what I bet the problem is. static (that is, not EXECUTE-ed) queries in pl/pgsql functions convert table references in the function body to fixed 'pointers' to actual tables that are always schema qualified. Once the function is run the first time and the plan generated, changing the schema will have no bearing on which tables are used. Thus, the function will not 'float' with the current schema search path setting. dynamic sql functions however will always re-look up the tables based on the search path because the plan is not saved off for the query. If this is your problem, you have a couple of options: *) always use dynamic sql in functions that are meant to apply to multiple schemas in the same session *) duplicate your function for each schema and make your tables fully schema qualified *) DISCARD your plans before running your function merlin
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Merlin Moncure wrote: > I'm not completely sure what the problem is because this is light on > detail, but here's a what I bet the problem is. static (that is, not > EXECUTE-ed) queries in pl/pgsql functions convert table references in > the function body to fixed 'pointers' to actual tables that are always > schema qualified. Once the function is run the first time and the > plan generated, changing the schema will have no bearing on which > tables are used. Thus, the function will not 'float' with the current > schema search path setting. We had similar problems to this when using temp tables in a function. Because the temp tables disappear and pg stores the oid, the second time it is run we were getting an error message. I posted about it on Oct 10. 2005, subject: strange error. My experience in this is just from 8.0 so I don't know if anything has changed. In general, if tables are created or deleted within the function then you want to use execute, as well as if the tables are recreated in the course of a normal workflow. For example, if you have a table that you regenerate once a day (by drop and create) you will not want to reference that table in a view or function. Obviously if you want to run dynamic code you also need to use execute. Sim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmqnSkACgkQjDX6szCBa+o77gCgjB7W+4tIYZVPtEvaF1Uj3QBC fPcAoOubAAC9dr5opTRyFsyUfLq6ojQF =q5NU -----END PGP SIGNATURE-----
Hello > > My experience in this is just from 8.0 so I don't know if anything has > changed. In general, if tables are created or deleted within the > function then you want to use execute, as well as if the tables are > recreated in the course of a normal workflow. For example, if you have a > table that you regenerate once a day (by drop and create) you will not > want to reference that table in a view or function. this bug is solved in 8.3 regards Pavel Stehule > > Obviously if you want to run dynamic code you also need to use execute. > > Sim > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (MingW32) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkmqnSkACgkQjDX6szCBa+o77gCgjB7W+4tIYZVPtEvaF1Uj3QBC > fPcAoOubAAC9dr5opTRyFsyUfLq6ojQF > =q5NU > -----END PGP SIGNATURE----- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >