MIME-Version: 1.0
References: <CAM9Bftx6fu1-_qNXTS6T6+VH1F69YzfsMggXGF7JeOzwrOCWLQ@mail.gmail.com>
In-Reply-To: <CAM9Bftx6fu1-_qNXTS6T6+VH1F69YzfsMggXGF7JeOzwrOCWLQ@mail.gmail.com>
From: =?UTF-8?B?Vmluw61jaXVzIEFicmFow6Nv?= <vinnix.bsd@gmail.com>
Date: Thu, 3 Oct 2024 12:29:50 +0100
Message-ID: <CAM9BftyjEyMyy6qHfMUTzaKH6XcD2aQB4Wcqiz0TBz3k=DUDag@mail.gmail.com>
Subject: Re: Userland copy of pg_statistic - is there a solution?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b770da062390e100"
Archived-At: <https://www.postgresql.org/message-id/CAM9BftyjEyMyy6qHfMUTzaKH6XcD2aQB4Wcqiz0TBz3k%3DDUDag%40mail.gmail.com>
Precedence: bulk

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

On Mon, Sep 30, 2024 at 8:31=E2=80=AFAM Vin=C3=ADcius Abrah=C3=A3o <vinnix.=
bsd@gmail.com>
wrote:

>
> Morning,
>
> postgres=3D# create table backup_pg_statistic as select * from pg_statist=
ic;
> 2024-09-30 08:25:56 BST [7400]:
> user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] ERROR:  column "s=
tavalues1"
> has pseudo-type anyarray
> 2024-09-30 08:25:56 BST [7400]:
> user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] STATEMENT:  creat=
e table
> backup_pg_statistic as select * from pg_statistic;
> ERROR:  column "stavalues1" has pseudo-type anyarray
> Time: 9.544 ms
> postgres=3D# create table test_array(a anyarray);
> 2024-09-30 08:26:40 BST [7400]:
> user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] ERROR:  column "a=
" has
> pseudo-type anyarray
> 2024-09-30 08:26:40 BST [7400]:
> user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] STATEMENT:  creat=
e table
> test_array(a anyarray);
> ERROR:  column "a" has pseudo-type anyarray
> Time: 9.137 ms
> Is there a solution for this simple problem?
>
> Cheers,
> Vin=C3=ADcius
>

Following up my own question - I could overcome this limitation observing
the parameter allow_system_table_mods

postgres=3D# set allow_system_table_mods to on;
SET
Time: 5.190 ms
postgres=3D# create table a(a anyarray) ;
CREATE TABLE
Time: 132.959 ms

This is verified at heap_create_with_catalog(...). PostgreSQL code
https://github.com/postgres/postgres/blob/master/src/backend/catalog/heap.c=
#L1151


If someone needs a patch let me know to make it work for other users. We
can work on this together.

--
vinnix
aka: Vin=C3=ADcius Abrah=C3=A3o Bazana Schmidt
twitter.com/vischmidt

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Sep 30, 2024 at 8:31=E2=80=AFAM V=
in=C3=ADcius Abrah=C3=A3o &lt;<a href=3D"mailto:vinnix.bsd@gmail.com">vinni=
x.bsd@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>
<table cellpadding=3D"0"><tbody><tr><td colspan=3D"2"><span></span><br></td=
><td colspan=3D"2">Morning,<br><br>postgres=3D# create table backup_pg_stat=
istic as select * from pg_statistic;<br>2024-09-30 08:25:56 BST [7400]: use=
r=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] ERROR: =C2=A0column &q=
uot;stavalues1&quot; has pseudo-type anyarray<br>2024-09-30 08:25:56 BST [7=
400]: user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[local] STATEMENT: =
=C2=A0create table backup_pg_statistic as select * from pg_statistic;<br>ER=
ROR: =C2=A0column &quot;stavalues1&quot; has pseudo-type anyarray<br>Time: =
9.544 ms<br>postgres=3D# create table test_array(a anyarray);<br>2024-09-30=
 08:26:40 BST [7400]: user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[loca=
l] ERROR: =C2=A0column &quot;a&quot; has pseudo-type anyarray<br>2024-09-30=
 08:26:40 BST [7400]: user=3Dvinnix,db=3Dpostgres,app=3Dpsql,client=3D[loca=
l] STATEMENT: =C2=A0create table test_array(a anyarray);<br>ERROR: =C2=A0co=
lumn &quot;a&quot; has pseudo-type anyarray<br>Time: 9.137 ms</td></tr></tb=
ody></table>

<br clear=3D"all"></div><div>Is there a solution for this simple problem?</=
div><div><br></div><div>Cheers,</div><div>Vin=C3=ADcius<br></div></div>
</blockquote></div><br clear=3D"all"><div>Following up my own question - I =
could overcome this limitation observing the parameter
<span style=3D"font-family:monospace">allow_system_table_mods </span>

</div><div><br></div><div>
<div><span style=3D"font-family:monospace">postgres=3D# set allow_system_ta=
ble_mods to on;<br>SET<br>Time: 5.190 ms<br>postgres=3D# create table a(a a=
nyarray) ;<br>CREATE TABLE<br>Time: 132.959 ms</span></div><div><span style=
=3D"font-family:monospace"><br></span></div><div><span style=3D"font-family=
:arial,sans-serif">This is verified at</span><span style=3D"font-family:mon=
ospace"> heap_create_with_catalog(...)</span>. PostgreSQL code <a href=3D"h=
ttps://github.com/postgres/postgres/blob/master/src/backend/catalog/heap.c#=
L1151">https://github.com/postgres/postgres/blob/master/src/backend/catalog=
/heap.c#L1151 </a><br></div>

</div><div><br></div><div>If someone needs a patch let me know to make it w=
ork for other users. We can work on this together.<br></div><div><span clas=
s=3D"gmail_signature_prefix">=C2=A0</span><br></div><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=
=3D"ltr">--<br>vinnix<br>aka: Vin=C3=ADcius Abrah=C3=A3o Bazana Schmidt<br>=
<a href=3D"http://twitter.com/vischmidt" target=3D"_blank">twitter.com/visc=
hmidt</a><br></div></div></div></div></div></div></div>

--000000000000b770da062390e100--