Обсуждение: Upgrade to 9.1 causing function problem
I have recently upgraded my database from 8.4 to 9.1. In the process I have moved everything to a different schema.
Postgisis occupying the public schema. Everything is working fine except for some of my own functions. Here is a small
functionand 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?
CREATE OR REPLACE FUNCTION _me_set_process_month(date)
RETURNS void AS
$BODY$
BEGIN
update activity_month set action_month = $1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION _me_set_process_month(date)
OWNER TO postgres;
CREATE TABLE activity_month
(
action_month date NOT NULL,
CONSTRAINT idkeymonth PRIMARY KEY (action_month )
)
WITH (
OIDS=FALSE
);
ALTER TABLE activity_month
OWNER TO postgres;
GRANT ALL ON TABLE activity_month TO public;
GRANT ALL ON TABLE activity_month TO postgres;
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
itclearly 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
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.
Butit 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 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.
Butit 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?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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. Butit 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/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. Butit 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
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.
Butit 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: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' >
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: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
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
problemswith the function not working. I also created the function this time without relying on search_path and even
alteredthe function and tables names slightly just in case there was some kind of conflict. Consequently there was no
logerror with search_path anymore but again the function will not work even though it appears to go through the motions
ofworking.
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
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
anythingup 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
problemswith the function not working. I also created the function this time without relying on search_path and even
alteredthe function and tables names slightly just in case there was some kind of conflict. Consequently there was no
logerror with search_path anymore but again the function will not work even though it appears to go through the motions
ofworking.
>
> 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
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
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