From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com> |
Subject: | Re: Inheriting table AMs for partitioned tables |
Date: | 2019-03-05 17:59:40 |
Message-ID: | 20190305175940.xyoal5hqrlefwcul@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2019-03-04 22:08:04 -0800, Andres Freund wrote:
> Hi,
>
> On 2019-03-05 16:01:50 +1300, David Rowley wrote:
> > On Tue, 5 Mar 2019 at 12:47, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
> > >
> > > SET default_table_access_method = 'heap';
> > > CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
> >
> >
> > > But for tableam_parted_a_heap2 tableam_parted_b_heap2 the answer isn't
> > > quite as clear. I think it'd both be sensible for new partitions to
> > > inherit the AM from the root, but it'd also be sensible to use the
> > > current default.
> >
> > I'd suggest it's made to work the same way as ca4103025dfe26 made
> > tablespaces work.
>
> Hm, is that actually correct? Because as far as I can tell that doesn't
> have the necessary pg_dump code to make this behaviour persistent:
>
> CREATE TABLESPACE frak LOCATION '/tmp/frak';
> CREATE TABLE test_tablespace (a text, b int) PARTITION BY list (a) TABLESPACE frak ;
> CREATE TABLE test_tablespace_1 PARTITION OF test_tablespace FOR VALUES in ('a');
> CREATE TABLE test_tablespace_2 PARTITION OF test_tablespace FOR VALUES in ('b') TABLESPACE pg_default;
> CREATE TABLE test_tablespace_3 PARTITION OF test_tablespace FOR VALUES in ('c') TABLESPACE frak;
>
> SELECT relname, relkind, reltablespace FROM pg_class WHERE relname LIKE 'test_tablespace%' ORDER BY 1;
> ┌───────────────────┬─────────┬───────────────┐
> │ relname │ relkind │ reltablespace │
> ├───────────────────┼─────────┼───────────────┤
> │ test_tablespace │ p │ 16384 │
> │ test_tablespace_1 │ r │ 16384 │
> │ test_tablespace_2 │ r │ 0 │
> │ test_tablespace_3 │ r │ 16384 │
> └───────────────────┴─────────┴───────────────┘
> (4 rows)
>
> but a dump outputs (abbreviated)
>
> SET default_tablespace = frak;
> CREATE TABLE public.test_tablespace (
> a text,
> b integer
> )
> PARTITION BY LIST (a);
> CREATE TABLE public.test_tablespace_1 PARTITION OF public.test_tablespace
> FOR VALUES IN ('a');
> SET default_tablespace = '';
> CREATE TABLE public.test_tablespace_2 PARTITION OF public.test_tablespace
> FOR VALUES IN ('b');
> SET default_tablespace = frak;
> CREATE TABLE public.test_tablespace_3 PARTITION OF public.test_tablespace
> FOR VALUES IN ('c');
>
> which restores to:
>
> postgres[32125][1]=# SELECT relname, relkind, reltablespace FROM pg_class WHERE relname LIKE 'test_tablespace%' ORDER BY 1;
> ┌───────────────────┬─────────┬───────────────┐
> │ relname │ relkind │ reltablespace │
> ├───────────────────┼─────────┼───────────────┤
> │ test_tablespace │ p │ 16384 │
> │ test_tablespace_1 │ r │ 16384 │
> │ test_tablespace_2 │ r │ 16384 │
> │ test_tablespace_3 │ r │ 16384 │
> └───────────────────┴─────────┴───────────────┘
> (4 rows)
>
> because public.test_tablespace_2 assumes it's ought to inherit the
> tablespace from the partitioned table.
>
>
> I also find it far from clear that:
> <listitem>
> <para>
> The <replaceable class="parameter">tablespace_name</replaceable> is the name
> of the tablespace in which the new table is to be created.
> If not specified,
> <xref linkend="guc-default-tablespace"/> is consulted, or
> <xref linkend="guc-temp-tablespaces"/> if the table is temporary. For
> partitioned tables, since no storage is required for the table itself,
> the tablespace specified here only serves to mark the default tablespace
> for any newly created partitions when no other tablespace is explicitly
> specified.
> </para>
> </listitem>
> is handled correctly. The above says that the *specified* tablespaces -
> which seems to exclude the default tablespace - is what's going to
> determine what partitions use as their default tablespace. But in fact
> that's not true, the partitioned table's pg_class.retablespace is set to
> what default_tablespaces was at the time of the creation.
>
>
> > i.e. if they specify the storage type when creating
> > the partition, then always use that, unless they mention otherwise. If
> > nothing was mentioned when they created the partition, then use
> > default_table_access_method.
>
> Hm. That'd be doable, but given the above ambiguities I'm not convinced
> that's the best approach. As far as I can see that'd require:
>
> 1) At relation creation, for partitioned tables only, do not take
> default_table_access_method into account.
>
> 2) At partition creation, if the AM is not specified and if the
> partitioned table's relam is 0, use the default_table_access_method.
>
> 3) At pg_dump, for partitioned tables only, explicitly emit a USING
> ... rather than use the method of manipulating default_table_access_method.
>
> As far as I can tell, the necessary steps are also what'd need to be
> done to actually implement the described behaviour for TABLESPACE (with
> s/default_table_access_method/default_tablespace/ and s/USING/TABLESPACE
> of course).
Based on this mail I'm currently planning to simply forbid specifying
USING for partitioned tables. Then we can argue about this later.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2019-03-05 18:05:55 | Re: Rare SSL failures on eelpout |
Previous Message | Daniel Verite | 2019-03-05 17:48:59 | Re: insensitive collations |