Thread: missing cache data for cache id 27
I'm getting the above error when i try to replace a function of mine. It
seems i have two problems: the latest dump (through phpPGAdmin) works
fine, except that a function that should return a record was replaced
without the column definition list, so calls on it are failing.
from pg_dump:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS
SETOF record AS $$
should be:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
So, i tried re-defining the function with the OUT params, and was hit
with the error in the subject line. I was able to DROP it first, then
re-create it. Now everything seems fine. But does anyone know what the
error means?
And why does the function definition in the db dump not reflect that OUT
params are called for?
Here's the entire function, fwiw:
-- snip --
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
DECLARE
rec record;
BEGIN
FOR rec IN
EXECUTE 'SELECT id, name, 1 AS total FROM service_type ORDER BY NAME ASC'
LOOP
name := rec.name;
id := rec.id;
SELECT INTO rec.total SUM(CASE sp.accepted WHEN TRUE THEN 1 ELSE 0 END)
FROM service_provider AS sp WHERE sp.id IN
(
SELECT spst.service_provider_id FROM service_provider_service_type AS
spst WHERE spst.service_type_id = rec.id
);
-- If none for this service type, give it a total of zero
IF rec.total IS NULL THEN
SELECT INTO total 0;
ELSE
total := rec.total;
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- snip --
brian
brian wrote: > I'm getting the above error when i try to replace a function of mine. It > seems i have two problems: the latest dump (through phpPGAdmin) works > fine, except that a function that should return a record was replaced > without the column definition list, so calls on it are failing. > > from pg_dump: > CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS > SETOF record AS $$ > > should be: > CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text, > OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ What version of PostgreSQL and what version of pg_dump are we talking about here? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > brian wrote: > >> I'm getting the above error when i try to replace a function of mine. >> It seems i have two problems: the latest dump (through phpPGAdmin) >> works fine, except that a function that should return a record was >> replaced without the column definition list, so calls on it are failing. >> >> from pg_dump: >> CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS >> SETOF record AS $$ >> >> should be: >> CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name >> text, OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ > > > What version of PostgreSQL and what version of pg_dump are we talking > about here? > Ach! 8.1.4, sorry. b
brian <brian@zijn-digital.com> writes: > I'm getting the above error when i try to replace a function of mine. In what PG version? http://archives.postgresql.org/pgsql-bugs/2006-10/msg00044.php http://archives.postgresql.org/pgsql-committers/2006-10/msg00084.php > And why does the function definition in the db dump not reflect that OUT > params are called for? Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code to fail to notice the OUT parameters, since it'd not know about the new columns in pg_proc ... regards, tom lane
Tom Lane wrote: > brian <brian@zijn-digital.com> writes: > >>I'm getting the above error when i try to replace a function of mine. > > > In what PG version? > http://archives.postgresql.org/pgsql-bugs/2006-10/msg00044.php > http://archives.postgresql.org/pgsql-committers/2006-10/msg00084.php > > >>And why does the function definition in the db dump not reflect that OUT >>params are called for? > > > Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code > to fail to notice the OUT parameters, since it'd not know about the new > columns in pg_proc ... > phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it simply makes a call to pg_dump, so wasn't expecting the problem lay in the front end. b
brian <brian@zijn-digital.com> writes:
> Tom Lane wrote:
>> Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code
>> to fail to notice the OUT parameters, since it'd not know about the new
>> columns in pg_proc ...
> phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it
> simply makes a call to pg_dump, so wasn't expecting the problem lay in
> the front end.
Um, but what pg_dump is it invoking? ISTR that phpPgAdmin uses the -i
option to pg_dump, so that you wouldn't find out if the pg_dump was too
old. In my book using that option by default verges on being a war
crime, but I'm sure they think it's a good idea.
regards, tom lane
Tom Lane wrote: > brian <brian@zijn-digital.com> writes: > >>Tom Lane wrote: >> >>>Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code >>>to fail to notice the OUT parameters, since it'd not know about the new >>>columns in pg_proc ... > > >>phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it >>simply makes a call to pg_dump, so wasn't expecting the problem lay in >>the front end. > > > Um, but what pg_dump is it invoking? ISTR that phpPgAdmin uses the -i > option to pg_dump, so that you wouldn't find out if the pg_dump was too > old. In my book using that option by default verges on being a war > crime, but I'm sure they think it's a good idea. > Right. I'd done: $ /usr/bin/pg_dump --version pg_dump (PostgreSQL) 8.1.4 but a quick glance at phpPGAdmin's config reminds me that it has its own version: $ /usr/bin/phpPgAdmin/pg_dump --version pg_dump (PostgreSQL) 8.0.4 I'll upgrade to 4.1, dump the db, and see how it recreates the function (whether it includes the OUT params). Thanks for the heads-up, Tom! b