Re: Upgrade to 9.1 causing function problem

Ok I must be doing something wrong. I tried the same test on my old server running 8.3 which has had no problem with anything up till now (but also only working within public schema). So I am obviously not working with schemas correctly. Will read the manual for hopefully a deeper understanding.

> I did create the schemas with PgAdmin. As a test I also created another schema in psql and it too has the same problems with the function not working. I also created the function this time without relying on search_path and even altered the function and tables names slightly just in case there was some kind of conflict. Consequently there was no log error with search_path anymore but again the function will not work even though it appears to go through the motions of working.
>
> select test2._crab_set_process_month_trial('2012-01-01');
>
>
> CREATE OR REPLACE FUNCTION test2._crab_set_process_month_trial(date)
> RETURNS void AS
> $BODY$
>
> BEGIN
>
> update test2.activity_trial set action_month = $1;
>
>
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
> ALTER FUNCTION test2._crab_set_process_month_trial(date)
> OWNER TO postgres;
>
>
> CREATE TABLE test2.activity_trial
> (
> action_month date NOT NULL,
> CONSTRAINT idkeymonth PRIMARY KEY (action_month )
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE test2.activity_trial
> OWNER TO postgres;
>
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