Re: BUG #16202: Cannot restore database that has materialized view using crosstab (tablefunc)

--0000000000006d56ba059bcd53ab
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Indeed, that was it. Thank you so much!


Em sex., 10 de jan. de 2020 =C3=A0s 15:04, Tom Lane <[email protected]> esc=
reveu:

> PG Bug reporting form <[email protected]> writes:
> > If I build a materizalized view that uses the crosstab() function, a
> > dump/restore cycle will not restore the database.
>
> > CREATE VIEW v AS
> > SELECT * FROM crosstab (
> >       'SELECT id FROM t as t1',
> >       'SELECT id FROM t as t2'
> > ) ct (a integer, b integer);
>
> This view is unsafe because it doesn't schema-qualify its reference
> to table t.  It will fail if run with a restrictive search_path,
> which is what pg_dump does for security reasons.
>
> An ordinary view reference to "t" would be all right, because the
> system understands what that is and can adjust the schema qualification;
> but here you've just got a string that happens to contain "t" in it.
>
>                         regards, tom lane
>

--0000000000006d56ba059bcd53ab
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Indeed, that was it. Thank you so much!<div><br></div></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">Em s=
ex., 10 de jan. de 2020 =C3=A0s 15:04, Tom Lane &lt;<a href=3D"mailto:tgl@s=
ss.pgh.pa.us">[email protected]</a>&gt; escreveu:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">PG Bug reporting form &lt;<a href=3D"mail=
to:[email protected]" target=3D"_blank">[email protected]</a>&gt;=
 writes:<br>
&gt; If I build a materizalized view that uses the crosstab() function, a<b=
r>
&gt; dump/restore cycle will not restore the database.<br>
<br>
&gt; CREATE VIEW v AS<br>
&gt; SELECT * FROM crosstab (<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;SELECT id FROM t as t1&#39;,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;SELECT id FROM t as t2&#39;<br>
&gt; ) ct (a integer, b integer);<br>
<br>
This view is unsafe because it doesn&#39;t schema-qualify its reference<br>
to table t.=C2=A0 It will fail if run with a restrictive search_path,<br>
which is what pg_dump does for security reasons.<br>
<br>
An ordinary view reference to &quot;t&quot; would be all right, because the=
<br>
system understands what that is and can adjust the schema qualification;<br=
>
but here you&#39;ve just got a string that happens to contain &quot;t&quot;=
 in it.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--0000000000006d56ba059bcd53ab--