Re: Upgrade to 9.1 causing function problem

I tried as you suggested and my results are:

crabby=# SELECT length(schema_name), schema_name from information_schema.schemat
a;
length | schema_name
--------+--------------------
8 | pg_toast
9 | pg_temp_1
15 | pg_toast_temp_1
10 | pg_catalog
6 | public
18 | information_schema
8 | crabdata
(7 rows)

So it seems that crabdata schema is not with extra space character or such. Likewise I created another schema earlier in a test (called test) from psql and it exhibited the same behaviour.
I've spent a whole week migrating from 8.3 to 9.1 and am loathe to repeat the process because I might have a funky installation. In all other respects everything
seems to work ok. Failing all else I can try a re-installation. If I go down this road are there any suggestions to wipe the slate clean to give myself
the best fighting chance of having this work? Using windows 7 64 bit with postgresql 9.1 32 bit and postgis. I am also making sure to operate from the correct database.

Here are the two problems as such:

1) setting the search_path to another schema returns the error in the server log:

2012-02-24 11:32:59.456 PST @[3868]: WARNING: invalid value for parameter "search_path": "crabdata, public"
2012-02-24 11:32:59.456 PST @[3868]: DETAIL: schema "crabdata" does not exist

As noted at the beginning of this post - crabdata is clearly present and does not contain any extraneous characters.

2) using designated schema designation in functions and tables still fail to work correctly. Such as:

select crabdata._crab_set_report_month('2012-01-01');

CREATE OR REPLACE FUNCTION crabdata._crab_set_report_month(date)
RETURNS void AS
$BODY$

BEGIN

update activity_month set action_month = $1;
perform * from _crab_pop_tag_day_over();

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION crabdata._crab_set_report_month(date)
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION crabdata._crab_set_report_month(date) TO public;
GRANT EXECUTE ON FUNCTION crabdata._crab_set_report_month(date) TO postgres;

CREATE TABLE crabdata.activity_month
(
action_month date NOT NULL,
CONSTRAINT idkeymonth PRIMARY KEY (action_month )
)
WITH (
OIDS=FALSE
);
ALTER TABLE crabdata.activity_month
OWNER TO postgres;
GRANT ALL ON TABLE crabdata.activity_month TO postgres;
GRANT ALL ON TABLE crabdata.activity_month TO public;

On 2012-02-23, at 6:04 PM, Adrian Klaver wrote:

> On Thursday, February 23, 2012 1:53:42 pm Willem Buitendyk wrote:
>> Both via psql and PgAdmin.
>>
>> Yes only one database cluster.
>>
>
> Another thought.
> Did you CREATE the schema using PgAdmin and if so, might you have inadvertently
> put in a trailing or leading space ?
> I ask because if I remember correctly PgAdmin by default quotes object names and
> that would trap the space character.
>
> I know you showed this previously:
>
> "crabby";"crabdata";"postgres";"";"";"";""
>
> On the chance that spaces where trimmed out of the above what does the query
> below show?:
>
> SELECT length(schema_name), schema_name from information_schema.schemata;
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com