Thread: Problem with insert related to different schemas
Hi Everyone,
I've come along with a problem that appeared with latest version of
Postgresql 8.4.2.
I'm trying to insert a row in the analysis schema:
INSERT INTO
"analisys"."response_quality"
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
VALUES('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01 00:00:00.000000 +01:00:00')
But it fails because:
ERROR: permiso denegado al esquema public
LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su... ^
CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
"id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
********** Error **********
ERROR: permiso denegado al esquema public
Estado SQL:42501
Surely it may have something to do with foreign keys.
But the user who executes this query has access to all foreign keys
tables...
Then why I'm receiving this error?
Thank you in advance!
NOTE:
-- Table: analisys.response_quality
-- DROP TABLE analisys.response_quality;
CREATE TABLE analisys.response_quality
( uuid uuid NOT NULL, id_survey_question integer NOT NULL, id_survey integer NOT NULL, id_survey_status integer NOT
NULL,id_shop integer NOT NULL, survey_question_response integer, id_survey_answer integer NOT NULL, date_survey_answer
dateNOT NULL, CONSTRAINT "primary" PRIMARY KEY (uuid, id_survey_answer,
id_survey_question, date_survey_answer, id_shop, id_survey), CONSTRAINT question FOREIGN KEY (id_survey_question)
REFERENCESsurvey_question (id_survey_question) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
surveyFOREIGN KEY (id_survey) REFERENCES survey_surveys (id_survey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE
NOACTION
)
WITH ( OIDS=FALSE
);
On Wed, Apr 14, 2010 at 2:23 AM, Gonzalo Aguilar Delgado
<gaguilar@level2crm.com> wrote:
> Hi Everyone,
>
> I've come along with a problem that appeared with latest version of
> Postgresql 8.4.2.
>
> I'm trying to insert a row in the analysis schema:
>
This is an insert query:
> INSERT INTO
> "analisys"."response_quality"
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
VALUES('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01 00:00:00.000000 +01:00:00')
>
>
> But it fails because:
This is a select query. I don't think that's the right error message.
>
> ERROR: permiso denegado al esquema public
> LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su...
> ^
> CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
> "id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
<br /><blockquote type="CITE"><pre>
> Hi Everyone,
>
> I've come along with a problem that appeared with latest version of
> Postgresql 8.4.2.
>
> I'm trying to insert a row in the analysis schema:
>
This is an insert query:
</pre></blockquote><br /> Yes it is...<br /><br /><blockquote type="CITE"><pre>
> INSERT INTO
> "analisys"."response_quality"
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
VALUES('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01 00:00:00.000000 +01:00:00')
>
>
> But it fails because:
This is a select query. I don't think that's the right error message.
</pre></blockquote><br /> Yes, but IS the correct error message. It appeared just after upgrading to 8.4 as 8.3 had no
problemprocessing this query.<br /><br /> I think that something internally is done with foreign key validation and
it'sthe root cause of this error. <br /><br /> I can confirm this because removing the foreign key constraint solved
theproblem (but will not maintain reference integrity).<br /><br /> That's why I see this strange...<br /><br /> I can
tryto isolate the problem if you want but it should be redoable by using inserts with foreign keys in different
schemas...<br/><br /><br /> What do you think?<br /><br /> Thank you for your answer<br /><br /><br /><br /><br
/><blockquotetype="CITE"><pre>
>
> ERROR: permiso denegado al esquema public
> LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su...
> ^
> CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
> "id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
</pre></blockquote>
On Tue, Apr 20, 2010 at 10:32 AM, Gonzalo Aguilar Delgado
<gaguilar@aguilardelgado.com> wrote:
>
>> Hi Everyone,
>>
>> I've come along with a problem that appeared with latest version of
>> Postgresql 8.4.2.
>>
>> I'm trying to insert a row in the analysis schema:
>>
>
> This is an insert query:
>
> Yes it is...
>
>
>> INSERT INTO
>> "analisys"."response_quality"
>>
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
>> VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
>> 00:00:00.000000 +01:00:00')
>>
>>
>> But it fails because:
>
> This is a select query. I don't think that's the right error message.
>
> Yes, but IS the correct error message. It appeared just after upgrading to
> 8.4 as 8.3 had no problem processing this query.
A self-contained example would be helpful.
Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com> writes:
>> This is a select query. I don't think that's the right error message.
> Yes, but IS the correct error message.
The query being complained of appears to be a generated foreign key
checking query. It's not surprising it would appear in the context
of an insert.
> It appeared just after upgrading
> to 8.4 as 8.3 had no problem processing this query.
You've either changed the permissions on schema public from what they
were in the old installation, or linked an FK constraint to the wrong
table. I see no reason to think there is either a bug or a version
difference here.
regards, tom lane
El mar, 20-04-2010 a las 10:34 -0600, Scott Marlowe escribió:
> On Tue, Apr 20, 2010 at 10:32 AM, Gonzalo Aguilar Delgado
> <gaguilar@aguilardelgado.com> wrote:
> >
> >> Hi Everyone,
> >>
> >> I've come along with a problem that appeared with latest version of
> >> Postgresql 8.4.2.
> >>
> >> I'm trying to insert a row in the analysis schema:
> >>
> >
> > This is an insert query:
> >
> > Yes it is...
> >
> >
> >> INSERT INTO
> >> "analisys"."response_quality"
> >>
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
> >> VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
> >> 00:00:00.000000 +01:00:00')
> >>
> >>
> >> But it fails because:
> >
> > This is a select query. I don't think that's the right error message.
> >
> > Yes, but IS the correct error message. It appeared just after upgrading to
> > 8.4 as 8.3 had no problem processing this query.
>
> A self-contained example would be helpful.
>
Ok. I will try to reproduce this in a small example...
Give me some time...
Thank you!
Hi Tom, > >> This is a select query. I don't think that's the right error message. > > > Yes, but IS the correct error message. > > The query being complained of appears to be a generated foreign key > checking query. It's not surprising it would appear in the context > of an insert. Yes, that's what I think too... > > > It appeared just after upgrading > > to 8.4 as 8.3 had no problem processing this query. > > You've either changed the permissions on schema public from what they > were in the old installation, or linked an FK constraint to the wrong > table. I see no reason to think there is either a bug or a version > difference here. Maybe but I ran even grant all on schema public for this user to check if this was the problem. About linking the wrong FK... Everything is posible. I have to take a look to them again and try isolate the problem. But I thought that it was a restriction of the jdbc driver used with postgresql. It looks like when you specify a schema for the query no other schema references are allowed... But I tried directly through the psql client and returned same error so it must be isolated at database level. Anyway, let me provide a test case. Thank you > > regards, tom lane >
On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com> wrote: > Hi Tom, > > > >> >> This is a select query. I don't think that's the right error message. >> >> > Yes, but IS the correct error message. >> >> The query being complained of appears to be a generated foreign key >> checking query. It's not surprising it would appear in the context >> of an insert. > > Yes, that's what I think too... > >> >> > It appeared just after upgrading >> > to 8.4 as 8.3 had no problem processing this query. >> >> You've either changed the permissions on schema public from what they >> were in the old installation, or linked an FK constraint to the wrong >> table. I see no reason to think there is either a bug or a version >> difference here. > > Maybe but I ran even grant all on schema public for this user to check > if this was the problem. That doesn't do what you think it does. You need to grant on the actual object.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado
> <gaguilar@aguilardelgado.com> wrote:
>> Maybe but I ran even grant all on schema public for this user to check
>> if this was the problem.
> That doesn't do what you think it does. You need to grant on the actual object.
The error he was getting was about permissions for the schema, though.
One thought: if you're running any moderately recent version of PG,
the FK check query will be run as though by the owner of the table,
not whoever issued the INSERT. Maybe that user doesn't have the right
permissions?
regards, tom lane
Hi Tom, This may be a clue... Will check El mié, 21-04-2010 a las 11:23 -0400, Tom Lane escribió: > Scott Marlowe <scott.marlowe@gmail.com> writes: > > On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado > > <gaguilar@aguilardelgado.com> wrote: > >> Maybe but I ran even grant all on schema public for this user to check > >> if this was the problem. > > > That doesn't do what you think it does. You need to grant on the actual object. > > The error he was getting was about permissions for the schema, though. > > One thought: if you're running any moderately recent version of PG, > the FK check query will be run as though by the owner of the table, > not whoever issued the INSERT. Maybe that user doesn't have the right > permissions? > > regards, tom lane >