Re: Upgrade to 9.1 causing function problem - Mailing list pgsql-general

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
ina 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
wipethe 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
alsomaking 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.klaver@gmail.com