Thread

1. Userland copy of pg_statistic - is there a solution?

   Vinícius Abrahão <vinnix.bsd@gmail.com> — 2024-09-30T07:31:06Z

   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
   

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

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-09-30T10:05:52Z

   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-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?
   
   Use a text[] for your copy of the stavalues* columns.
   
   Yours,
   Laurenz Albe
   
   
   
   

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

   Vinícius Abrahão <vinnix.bsd@gmail.com> — 2024-10-03T11:29:50Z

   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