Upgrade to 9.1 causing function problem

On Thursday, February 23, 2012 11:18:46 am Willem Buitendyk wrote:

I have recently upgraded my database from 8.4 to 9.1. In the process I
have moved everything to a different schema. Postgis is occupying the
public schema. Everything is working fine except for some of my own
functions. Here is a small function and table that will not update when I
perform the following code: select _me_set_process_month('2012-01-01');
It will run but the resulting table will not update. Any ideas?

What does the Postgres log say?
What is your search_path set to?
Is there more than one activity_month?

My guess is that your search_path is limiting the visibility of the table.
Without an explicit schema qualification of the table, Postgres will use the
search_path to locate a table. The logs should say something or you can change
the function to point to a schema qualified table name, if you do want to change
the search_path.

--
Adrian Klaver
adrian.klaver@gmail.com

Here are the log returns:

2012-02-23 11:31:44 PST WARNING invalid value for parameter "search_path": "crabdata"
2012-02-23 11:31:44 PST DETAIL schema "crabdata" does not exist

Bizarre because I did set my search path to the schema crabdata but now it is saying that crabdata doesn't exist. But it clearly does. I'm at a loss.

Willem

On 2012-02-23, at 11:41 AM, Adrian Klaver wrote:

On 02/23/2012 12:49 PM, Willem Buitendyk wrote:

Here are the log returns:

2012-02-23 11:31:44 PST WARNING invalid value for parameter "search_path": "crabdata"
2012-02-23 11:31:44 PST DETAIL schema "crabdata" does not exist

Bizarre because I did set my search path to the schema crabdata but now it is saying that crabdata doesn't exist. But it clearly does. I'm at a loss.

Willem

On 2012-02-23, at 11:41 AM, Adrian Klaver wrote:

On Thursday, February 23, 2012 11:18:46 am Willem Buitendyk wrote:

I have recently upgraded my database from 8.4 to 9.1. In the process I
have moved everything to a different schema. Postgis is occupying the
public schema. Everything is working fine except for some of my own
functions. Here is a small function and table that will not update when I
perform the following code: select _me_set_process_month('2012-01-01');
It will run but the resulting table will not update. Any ideas?

What does the Postgres log say?
What is your search_path set to?
Is there more than one activity_month?

My guess is that your search_path is limiting the visibility of the table.
Without an explicit schema qualification of the table, Postgres will use the
search_path to locate a table. The logs should say something or you can change
the function to point to a schema qualified table name, if you do want to change
the search_path.

--
Adrian Klaver
adrian.klaver@gmail.com

Who owes/owned "crabdata" schema?

crabby=# \dv
List of relations
Schema | Name | Type | Owner
----------+-------------------------------------------+------+----------
crabdata | Total XXX XXXXX by XXX | view | postgres

also;

crabby=# show search_path;
search_path
-------------
crabdata
(1 row)

On 2012-02-23, at 12:16 PM, Rob Sargent wrote:

On 02/23/2012 11:49 AM, Willem Buitendyk wrote:

Here are the log returns:

2012-02-23 11:31:44 PST WARNING invalid value for parameter "search_path": "crabdata"
2012-02-23 11:31:44 PST DETAIL schema "crabdata" does not exist

Bizarre because I did set my search path to the schema crabdata but now it is saying that crabdata doesn't exist. But it clearly does. I'm at a loss.

Where did you set the search_path, in postgressql.conf of from a SET
command?

Willem

--
Adrian Klaver
adrian.klaver@gmail.com

I have it set in postgresql.conf and I've also used:

alter user postgres set search_path = crabdata,public;

looking at the logs after issuing the above alter user command I get:

2012-02-23 13:03:09 PST WARNING invalid value for parameter "search_path": "crabdata, public"
2012-02-23 13:03:09 PST DETAIL schema "crabdata" does not exist

If i look in the schemata table in the catalogs I see crabdata schema is there:

"crabby";"pg_toast";"postgres";"";"";"";""
"crabby";"pg_temp_1";"postgres";"";"";"";""
"crabby";"pg_toast_temp_1";"postgres";"";"";"";""
"crabby";"pg_catalog";"postgres";"";"";"";""
"crabby";"public";"postgres";"";"";"";""
"crabby";"information_schema";"postgres";"";"";"";""
"crabby";"crabdata";"postgres";"";"";"";""

I should note this is on Windows 7 64 bit - using the 32 bit installation of postgresql 9.1

On 2012-02-23, at 12:57 PM, Adrian Klaver wrote:

On 02/23/12 11:49 AM, Willem Buitendyk wrote:

2012-02-23 11:31:44 PST WARNING invalid value for parameter "search_path": "crabdata"
2012-02-23 11:31:44 PST DETAIL schema "crabdata" does not exist

Bizarre because I did set my search path to the schema crabdata but now it is saying that crabdata doesn't exist. But it clearly does. I'm at a loss.

add the database name to your log_line_prefix ... I use something like...

log_line_prefix = '%m %u@%d[%p]: '

so my log lines look like...

2012-02-21 14:04:21.595 PST user@dbname[8888]: ERROR: .....

(where 8888 is the pid)... I'm suggesting this so you can confirm
that you are getting this error on the database you think it is, and not
something else.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 02/23/2012 01:08 PM, Willem Buitendyk wrote:

I have it set in postgresql.conf and I've also used:

alter user postgres set search_path = crabdata,public;

Well search_path is a string, so have you tried?;
search_path='crabdata,public'

--
Adrian Klaver
adrian.klaver@gmail.com

Actually, what works is
set search_path='crabdata', 'public' ;

On 2/23/12 1:10 PM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:

On 02/23/2012 01:15 PM, Igor Polishchuk wrote:

Actually, what works is
set search_path='crabdata', 'public' ;

Yea, I was not clear here. I was referring to postgresql.conf setting.

--
Adrian Klaver
adrian.klaver@gmail.com

On 02/23/2012 01:08 PM, Willem Buitendyk wrote:

I have it set in postgresql.conf and I've also used:

alter user postgres set search_path = crabdata,public;

looking at the logs after issuing the above alter user command I get:

2012-02-23 13:03:09 PST WARNING invalid value for parameter "search_path": "crabdata, public"
2012-02-23 13:03:09 PST DETAIL schema "crabdata" does not exist

I should note this is on Windows 7 64 bit - using the 32 bit installation of postgresql 9.1

How are you doing these changes, via psql or PgAdmin or both?
Is the previous 8.4 instance of Postgres still up and running?
Are you sure you are only working with one database cluster?

--
Adrian Klaver
adrian.klaver@gmail.com

Both via psql and PgAdmin.

Yes only one database cluster.

On 2012-02-23, at 1:32 PM, Adrian Klaver wrote:

On 02/23/2012 01:53 PM, Willem Buitendyk wrote:

Both via psql and PgAdmin.

Yes only one database cluster.

1)
Did you try Johns recommendation, just to be sure?:
"
add the database name to your log_line_prefix ... I use something like...

log_line_prefix = '%m %u@%d[%p]: '
"

2)
So what happens if you change your function to have an explicit schema
reference to the table?:

update crabdata.activity_month set action_month = $1;

--
Adrian Klaver
adrian.klaver@gmail.com

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

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:

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

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 Friday, February 24, 2012 10:31:44 am Willem Buitendyk wrote:

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.

At this point my guess is it is a GRANT issue, where the user that creates the
SCHEMA is different from the one using the function and the function user does
not have USAGE on the schema.

See:

http://www.postgresql.org/docs/9.0/interactive/sql-grant.html

In particular:
"
USAGE

...
For schemas, allows access to objects contained in the specified schema (assuming
that the objects' own privilege requirements are also met). Essentially this
allows the grantee to "look up" objects within the schema. Without this
permission, it is still possible to see the object names, e.g. by querying the
system tables. Also, after revoking this permission, existing backends might
have statements that have previously performed this lookup, so this is not a
completely secure way to prevent object access.
...

"

--
Adrian Klaver
adrian.klaver@gmail.com