pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with insert related to different schemas
10+ messages / 4 participants
[nested] [flat]
* Problem with insert related to different schemas
@ 2010-04-14 08:23 Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Gonzalo Aguilar Delgado @ 2010-04-14 08:23 UTC (permalink / raw)
To: pgsql-sql
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 date NOT 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)
REFERENCES survey_question (id_survey_question) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT survey FOREIGN KEY (id_survey)
REFERENCES survey_surveys (id_survey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
@ 2010-04-20 16:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-04-20 16:04 UTC (permalink / raw)
To: Gonzalo Aguilar Delgado <gaguilar@level2crm.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-20 16:32 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 16:34 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Gonzalo Aguilar Delgado @ 2010-04-20 16:32 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
> > 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.
I think that something internally is done with foreign key validation
and it's the root cause of this error.
I can confirm this because removing the foreign key constraint solved
the problem (but will not maintain reference integrity).
That's why I see this strange...
I can try to isolate the problem if you want but it should be redoable
by using inserts with foreign keys in different schemas...
What do you think?
Thank you for your answer
>
> >
> > 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
@ 2010-04-20 16:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
1 sibling, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-04-20 16:34 UTC (permalink / raw)
To: Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 16:34 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-20 17:01 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gonzalo Aguilar Delgado @ 2010-04-20 17:01 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
@ 2010-04-20 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 13:25 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2010-04-20 17:01 UTC (permalink / raw)
To: Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-21 13:25 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-21 13:31 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Gonzalo Aguilar Delgado @ 2010-04-21 13:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 13:25 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
@ 2010-04-21 13:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-21 15:23 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-04-21 13:31 UTC (permalink / raw)
To: Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>; +Cc: pgsql-sql@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 13:25 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-21 13:31 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-21 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 17:36 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2010-04-21 15:23 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Problem with insert related to different schemas
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 17:01 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 13:25 ` Re: Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-21 13:31 ` Re: Problem with insert related to different schemas Scott Marlowe <scott.marlowe@gmail.com>
2010-04-21 15:23 ` Re: Problem with insert related to different schemas Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-21 17:36 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gonzalo Aguilar Delgado @ 2010-04-21 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-04-21 17:36 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-14 08:23 Problem with insert related to different schemas Gonzalo Aguilar Delgado <gaguilar@level2crm.com>
2010-04-20 16:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:32 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 16:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 17:01 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-20 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 13:25 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
2010-04-21 13:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-21 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-21 17:36 ` Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox