Postgres Pro
Downloads
Home   >   mailing lists

Thread: Userland copy of pg_statistic - is there a solution?

Userland copy of pg_statistic - is there a solution?

From
Vinícius Abrahão
Date:

Morning,

postgres=# create table backup_pg_statistic as select * from pg_statistic;
2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "stavalues1" has pseudo-type anyarray
2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create table backup_pg_statistic as select * from pg_statistic;
ERROR:  column "stavalues1" has pseudo-type anyarray
Time: 9.544 ms
postgres=# create table test_array(a anyarray);
2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "a" has pseudo-type anyarray
2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create 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ícius

Re: Userland copy of pg_statistic - is there a solution?

From
Laurenz Albe
Date:
On Mon, 2024-09-30 at 08:31 +0100, Vinícius Abrahão wrote:
> postgres=# create table backup_pg_statistic as select * from pg_statistic;
> 2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "stavalues1" has
pseudo-typeanyarray 
> 2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create table
backup_pg_statisticas select * from pg_statistic; 
> ERROR:  column "stavalues1" has pseudo-type anyarray
> Time: 9.544 ms
> postgres=# create table test_array(a anyarray);
> 2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "a" has pseudo-type
anyarray
> 2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create table test_array(a
anyarray);
> ERROR:  column "a" has pseudo-type anyarray
> Time: 9.137 ms
>
>
> Is there a solution for this simple problem?

Use a text[] for your copy of the stavalues* columns.

Yours,
Laurenz Albe

Re: Userland copy of pg_statistic - is there a solution?

From
Vinícius Abrahão
Date:
On Mon, Sep 30, 2024 at 8:31 AM Vinícius Abrahão <vinnix.bsd@gmail.com> wrote:

Morning,

postgres=# create table backup_pg_statistic as select * from pg_statistic;
2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "stavalues1" has pseudo-type anyarray
2024-09-30 08:25:56 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create table backup_pg_statistic as select * from pg_statistic;
ERROR:  column "stavalues1" has pseudo-type anyarray
Time: 9.544 ms
postgres=# create table test_array(a anyarray);
2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] ERROR:  column "a" has pseudo-type anyarray
2024-09-30 08:26:40 BST [7400]: user=vinnix,db=postgres,app=psql,client=[local] STATEMENT:  create 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ícius

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

postgres=# set allow_system_table_mods to on;
SET
Time: 5.190 ms
postgres=# 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ícius Abrahão Bazana Schmidt
twitter.com/vischmidt