Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
Actually, what works is set search_path='crabdata', 'public' ; On 2/23/12 1:10 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; >> > > Well search_path is a string, so have you tried?; > search_path='crabdata,public' > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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[]: ERROR: . (where 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 pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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. 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
crabby=# \dv
List of relations
Schema | Name| Type | Owner
--+---+--+--
crabdata | Total XXX X 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. 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.kla...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@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
Re: [GENERAL] Upgrade to 9.1 causing function problem
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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
