Обсуждение: [GENERAL] CREATE TABLE LIKE including all not including storage parameters?
[GENERAL] CREATE TABLE LIKE including all not including storage parameters?
От
Milen Blagojevic
Дата:
Hi all,
I am using CREATE TABLE LIKE for creating partitions :
Regards,
I am using CREATE TABLE LIKE for creating partitions :
Lets say this is my main table:
\d+ test_tabl
Table "public.test_tabl"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------- --------------+-----------+--- -------+--------------+------- ------
id | integer | not null | plain | |
test_name | character varying(10) | | extended | |
test_value | numeric(19,3) | | main | |
time_created | timestamp without time zone | | plain | |
Indexes:
"test_tabl_pkey" PRIMARY KEY, btree (id)
"ix_test_tabl_time_created" btree (time_created)
Child tables: test_tabl_20170905
Options: fillfactor=75
I am creating new partitions with following query:
create table test_tabl_20170906 (like test_tabl INCLUDING ALL) inherits (test_tabl);
\d+ test_tabl_20170906
Table "public.test_tabl_20170906"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------- --------------+-----------+--- -------+--------------+------- ------
id | integer | not null | plain | |
test_name | character varying(10) | | extended | |
test_value | numeric(19,3) | | main | |
time_created | timestamp without time zone | | plain | |
Indexes:
"test_tabl_20170906_pkey" PRIMARY KEY, btree (id)
"test_tabl_20170906_time_creat ed_idx" btree (time_created)
Inherits: test_tabl
According to PostgreSQL documentation:
INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.
But in this case child table didn't inherit filfactor (behaviour is the same for autovacuum parameters)
Version is 9.4.13:
version
------------------------------ ------------------------------ ------------------------------ ----------------------
PostgreSQL 9.4.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Am I doing something wrong here?
Thanks in advance.
Regards,
Milen Blagojevic
Re: [GENERAL] CREATE TABLE LIKE including all not including storageparameters?
От
Achilleas Mantzios
Дата:
On 05/09/2017 11:56, Milen Blagojevic wrote:
Hi all,
I am using CREATE TABLE LIKE for creating partitions :Lets say this is my main table:\d+ test_tablTable "public.test_tabl"Column | Type | Modifiers | Storage | Stats target | Description--------------+-----------------------------+-----------+--- -------+--------------+------- ------ id | integer | not null | plain | |test_name | character varying(10) | | extended | |test_value | numeric(19,3) | | main | |time_created | timestamp without time zone | | plain | |Indexes:"test_tabl_pkey" PRIMARY KEY, btree (id)"ix_test_tabl_time_created" btree (time_created)Child tables: test_tabl_20170905Options: fillfactor=75I am creating new partitions with following query:create table test_tabl_20170906 (like test_tabl INCLUDING ALL) inherits (test_tabl);\d+ test_tabl_20170906Table "public.test_tabl_20170906"Column | Type | Modifiers | Storage | Stats target | Description--------------+-----------------------------+-----------+--- -------+--------------+------- ------ id | integer | not null | plain | |test_name | character varying(10) | | extended | |test_value | numeric(19,3) | | main | |time_created | timestamp without time zone | | plain | |Indexes:"test_tabl_20170906_pkey" PRIMARY KEY, btree (id)"test_tabl_20170906_time_created_idx" btree (time_created) Inherits: test_tablAccording to PostgreSQL documentation:INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.But in this case child table didn't inherit filfactor (behaviour is the same for autovacuum parameters)Version is 9.4.13:version------------------------------------------------------------ ------------------------------ ---------------------- PostgreSQL 9.4.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Same in PostgreSQL 10beta3 on x86_64-pc-linux-gnu.
testdb=# create table dad(foo text) WITH (fillfactor=99, autovacuum_freeze_max_age=20000000);
CREATE TABLE
testdb=# create table dadkid1 (like dad INCLUDING STORAGE);
CREATE TABLE
testdb=# \d+ dadkid1
Table "public.dadkid1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
foo | text | | | | extended | |
Am I doing something wrong here?Thanks in advance.
Regards,Milen Blagojevic
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] CREATE TABLE LIKE including all not including storage parameters?
От
Michael Paquier
Дата:
On Tue, Sep 5, 2017 at 6:45 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > Am I doing something wrong here? From the documentation: https://www.postgresql.org/docs/devel/static/sql-createtable.html STORAGE settings for the copied column definitions will be copied only if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings, resulting in the copied columns in the new table having type-specific default settings. For more on STORAGE settings, see Section 66.2. And in this case storage parameters refer to column-specific settings, not table-level storage parameters, which are defined here by toast: https://www.postgresql.org/docs/devel/static/storage-toast.html -- Michael
Re: [GENERAL] CREATE TABLE LIKE including all not including storageparameters?
От
Achilleas Mantzios
Дата:
On 05/09/2017 13:26, Michael Paquier wrote: > On Tue, Sep 5, 2017 at 6:45 PM, Achilleas Mantzios > <achill@matrix.gatewaynet.com> wrote: >> Am I doing something wrong here? I didn't sat that :) but you are right, STORAGE settings for copied col defs != tables's storage parameters. The key hereis that LIKE deals with columns only, not other parameters of the table. > From the documentation: > https://www.postgresql.org/docs/devel/static/sql-createtable.html > STORAGE settings for the copied column definitions will be copied only > if INCLUDING STORAGE is specified. The default behavior is to exclude > STORAGE settings, resulting in the copied columns in the new table > having type-specific default settings. For more on STORAGE settings, > see Section 66.2. > > And in this case storage parameters refer to column-specific settings, > not table-level storage parameters, which are defined here by toast: > https://www.postgresql.org/docs/devel/static/storage-toast.html -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt