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.phpAnd 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