Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 03A406339A7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 20 Apr 2010 13:39:46 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 49503-03-7 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 20 Apr 2010 16:39:25 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from aguilardelgado.com (74.141.15.62.static.jazztel.es
	[62.15.141.74])
	by mail.postgresql.org (Postfix) with ESMTP id 49DD8633D7B
	for <pgsql-sql@postgresql.org>; Tue, 20 Apr 2010 13:32:48 -0300 (ADT)
Received: from [192.168.10.100] (135.228.16.95.dynamic.jazztel.es
	[95.16.228.135]) (authenticated bits=0)
	by aguilardelgado.com (8.14.3/8.14.3/Debian-9ubuntu1) with ESMTP id
	o3KGWaRF003503
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Tue, 20 Apr 2010 18:32:45 +0200
Subject: Re: Problem with insert related to different schemas
From: Gonzalo Aguilar Delgado <gaguilar@aguilardelgado.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <m2rdcc563d11004200904r22b7b34fhea678e04c930beb7@mail.gmail.com>
References: <1271233402.10390.5.camel@azul1>
	<m2rdcc563d11004200904r22b7b34fhea678e04c930beb7@mail.gmail.com>
Content-Type: multipart/alternative; boundary="=-LuPaK1JYi5To3M5wxzFp"
Date: Tue, 20 Apr 2010 18:32:35 +0200
Message-ID: <1271781155.6136.57.camel@azul1>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.3 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.431 tagged_above=-10 required=5 tests=AWL=0.591,
	BAYES_00=-1.9, FRT_ADOBE2=0.877, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201004/69
X-Sequence-Number: 33996


--=-LuPaK1JYi5To3M5wxzFp
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit



> > 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
> 

--=-LuPaK1JYi5To3M5wxzFp
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.28.3">
</HEAD>
<BODY>
<BR>
<BLOCKQUOTE TYPE=CITE>
<PRE>
&gt; Hi Everyone,
&gt;
&gt; I've come along with a problem that appeared with latest version of
&gt; Postgresql 8.4.2.
&gt;
&gt; I'm trying to insert a row in the analysis schema:
&gt;

This is an insert query:
</PRE>
</BLOCKQUOTE>
<BR>
Yes it is...<BR>
<BR>
<BLOCKQUOTE TYPE=CITE>
<PRE>

&gt; INSERT INTO
&gt; &quot;analisys&quot;.&quot;response_quality&quot; (&quot;uuid&quot;,&quot;id_survey_question&quot;,&quot;id_survey&quot;,&quot;id_survey_status&quot;,&quot;id_shop&quot;,&quot;survey_question_response&quot;,&quot;id_survey_answer&quot;,&quot;date_survey_answer&quot;) VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01 00:00:00.000000 +01:00:00')
&gt;
&gt;
&gt; But it fails because:

This is a select query.&nbsp; 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 problem processing this query.<BR>
<BR>
I think that something internally is done with foreign key validation and it's the root cause of this error. <BR>
<BR>
I can confirm this because removing the foreign key constraint solved the problem (but will not maintain reference integrity).<BR>
<BR>
That's why I see this strange...<BR>
<BR>
I can try to 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>
<BLOCKQUOTE TYPE=CITE>
<PRE>

&gt;
&gt; ERROR: &nbsp;permiso denegado al esquema public
&gt; L&#205;NEA 1: SELECT 1 FROM ONLY &quot;public&quot;.&quot;survey_question&quot; x WHERE &quot;id_su...
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;^
&gt; CONSULTA: &nbsp;SELECT 1 FROM ONLY &quot;public&quot;.&quot;survey_question&quot; x WHERE
&gt; &quot;id_survey_question&quot; OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

</PRE>
</BLOCKQUOTE>
</BODY>
</HTML>

--=-LuPaK1JYi5To3M5wxzFp--