Обсуждение: is it possible to create partitioned tables using tables fromdifferent schemas
I have a schema with a generated table with information coming from batch processes.
I would like to store in that table manually generated information. Since those rows are inserted by hand, they will be lost when the table will be reimported.
So I was thinking of creating a partitioned table with a column "origin" to select if the data is batch inserted or inserted by hand. Current generated by batch sub-table will live in its schema.
I want the other sub-table containing manually inserted information living in another schema.
Is this possible? Do I have to do something else (create some trigger, or something)?
Something like:
CREATE TABLE automatic.measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int origin int ) PARTITION BY RANGE (origin);
CREATE TABLE automatic.measurement_automatic PARTITION OF automatic.measurement FOR VALUES FROM (1) TO (1) PARTITION BY RANGE (origin);
CREATE TABLE manual.measurement_manual PARTITION OF automatic.measurement FOR VALUES FROM (2) TO (2) PARTITION BY RANGE (origin);
Re: is it possible to create partitioned tables using tables fromdifferent schemas
От
Adrian Klaver
Дата:
On 4/17/19 2:21 PM, pabloa98 wrote: > I have a schema with a generated table with information coming from > batch processes. > > I would like to store in that table manually generated information. > Since those rows are inserted by hand, they will be lost when the table > will be reimported. > > So I was thinking of creating a partitioned table with a column "origin" > to select if the data is batch inserted or inserted by hand. Current > generated by batch sub-table will live in its schema. > > I want the other sub-table containing manually inserted information > living in another schema. > > Is this possible? Do I have to do something else (create some trigger, > or something)? > > Something like: > > CREATE TABLE*automatic.*measurement ( > city_id int not null, > logdate date not null, > peaktemp int, > unitsales int > origin int > ) PARTITION BY RANGE (origin); > > > CREATE TABLE*automatic.*measurement_automatic PARTITION OF*automatic.*measurement > FOR VALUES FROM (1) TO (1) > PARTITION BY RANGE (origin); > > CREATE TABLE*manual.*measurement_manual PARTITION OF*automatic.*measurement > FOR VALUES FROM (2) TO (2) > PARTITION BY RANGE (origin); > > It would seem so(with a caveat): CREATE TABLE automatic.measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int, origin int ) PARTITION BY RANGE (origin); CREATE TABLE CREATE TABLE automatic.measurement_automatic PARTITION OF automatic.measurement test-# FOR VALUES FROM (1) TO (1) test-# PARTITION BY RANGE (origin); ERROR: empty range bound specified for partition "measurement_automatic" DETAIL: Specified lower bound (1) is greater than or equal to upper bound (1). OOPS, so lets cheat: <NOTE> Removed PARTITION BY RANGE (origin) as it is redundant and causes problems later. CREATE TABLE automatic.measurement_automatic PARTITION OF automatic.measurement FOR VALUES FROM (1) TO (2) CREATE TABLE CREATE TABLE manual.measurement_manual PARTITION OF automatic.measurement FOR VALUES FROM (3) TO (4) CREATE TABLE The above assumes that manual origin will be in (1,2) and automatic in (3,4) insert into automatic.measurement values(1, '04/16/19', 25, 100, 1); INSERT 0 1 insert into automatic.measurement values(1, '04/16/19', 25, 100, 3); INSERT 0 1 select * from automatic.measurement_automatic ; city_id | logdate | peaktemp | unitsales | origin ---------+------------+----------+-----------+-------- 1 | 2019-04-16 | 25 | 100 | 1 (1 row) select * from manual.measurement_manual; city_id | logdate | peaktemp | unitsales | origin ---------+------------+----------+-----------+-------- 1 | 2019-04-16 | 25 | 100 | 3 (1 row) -- Adrian Klaver adrian.klaver@aklaver.com
Thank you! This is exactly was I was looking for.
The range thing is good enough for me.
Pablo
On Wed, Apr 17, 2019 at 3:19 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/17/19 2:21 PM, pabloa98 wrote:
> I have a schema with a generated table with information coming from
> batch processes.
>
> I would like to store in that table manually generated information.
> Since those rows are inserted by hand, they will be lost when the table
> will be reimported.
>
> So I was thinking of creating a partitioned table with a column "origin"
> to select if the data is batch inserted or inserted by hand. Current
> generated by batch sub-table will live in its schema.
>
> I want the other sub-table containing manually inserted information
> living in another schema.
>
> Is this possible? Do I have to do something else (create some trigger,
> or something)?
>
> Something like:
>
> CREATE TABLE*automatic.*measurement (
> city_id int not null,
> logdate date not null,
> peaktemp int,
> unitsales int
> origin int
> ) PARTITION BY RANGE (origin);
>
>
> CREATE TABLE*automatic.*measurement_automatic PARTITION OF*automatic.*measurement
> FOR VALUES FROM (1) TO (1)
> PARTITION BY RANGE (origin);
>
> CREATE TABLE*manual.*measurement_manual PARTITION OF*automatic.*measurement
> FOR VALUES FROM (2) TO (2)
> PARTITION BY RANGE (origin);
>
>
It would seem so(with a caveat):
CREATE TABLE automatic.measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int,
origin int
) PARTITION BY RANGE (origin);
CREATE TABLE
CREATE TABLE automatic.measurement_automatic PARTITION OF
automatic.measurement
test-# FOR VALUES FROM (1) TO (1)
test-# PARTITION BY RANGE (origin);
ERROR: empty range bound specified for partition "measurement_automatic"
DETAIL: Specified lower bound (1) is greater than or equal to upper
bound (1).
OOPS, so lets cheat:
<NOTE> Removed PARTITION BY RANGE (origin) as it is redundant and causes
problems later.
CREATE TABLE automatic.measurement_automatic PARTITION OF
automatic.measurement
FOR VALUES FROM (1) TO (2)
CREATE TABLE
CREATE TABLE manual.measurement_manual PARTITION OF automatic.measurement
FOR VALUES FROM (3) TO (4)
CREATE TABLE
The above assumes that manual origin will be in (1,2) and automatic in (3,4)
insert into automatic.measurement values(1, '04/16/19', 25, 100, 1);
INSERT 0 1
insert into automatic.measurement values(1, '04/16/19', 25, 100, 3);
INSERT 0 1
select * from automatic.measurement_automatic ;
city_id | logdate | peaktemp | unitsales | origin
---------+------------+----------+-----------+--------
1 | 2019-04-16 | 25 | 100 | 1
(1 row)
select * from manual.measurement_manual;
city_id | logdate | peaktemp | unitsales | origin
---------+------------+----------+-----------+--------
1 | 2019-04-16 | 25 | 100 | 3
(1 row)
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: is it possible to create partitioned tables using tables fromdifferent schemas
От
Alvaro Herrera
Дата:
Note that unless you regularly query for only-manually-inserted or only-automatically-inserted data, this will be useless and will make queries more expensive, with no upside. Generally speaking, it's not a problem to put partitions in different schemas. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: is it possible to create partitioned tables using tables fromdifferent schemas
От
Adrian Klaver
Дата:
On 4/17/19 3:56 PM, Alvaro Herrera wrote: > Note that unless you regularly query for only-manually-inserted or > only-automatically-inserted data, this will be useless and will make > queries more expensive, with no upside. Well the OP's use case is: "I would like to store in that table manually generated information. Since those rows are inserted by hand, they will be lost when the table will be reimported." This got me to thinking an INSERT trigger would work also. > > Generally speaking, it's not a problem to put partitions in different > schemas. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: is it possible to create partitioned tables using tables fromdifferent schemas
От
David Rowley
Дата:
On Thu, 18 Apr 2019 at 10:56, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > Note that unless you regularly query for only-manually-inserted or > only-automatically-inserted data, this will be useless and will make > queries more expensive, with no upside. Going by "Since those rows are inserted by hand, they will be lost when the table will be reimported.", I just imagined there must be some sort of TRUNCATE + INSERT/COPY operation going on, in which case there would be a good upside of not having to perform a DELETE of all automatic records instead of the TRUNCATE. I could be wrong to assume that though. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: is it possible to create partitioned tables using tables fromdifferent schemas
От
David Rowley
Дата:
On Thu, 18 Apr 2019 at 10:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > CREATE TABLE automatic.measurement_automatic PARTITION OF > automatic.measurement > test-# FOR VALUES FROM (1) TO (1) > test-# PARTITION BY RANGE (origin); > ERROR: empty range bound specified for partition "measurement_automatic" > DETAIL: Specified lower bound (1) is greater than or equal to upper > bound (1). > > OOPS, so lets cheat: Or you could partition by LIST. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
you are right. What it happens is that the batch importing process drops the schema and recreates it. I would like some solution that is compatible with that.
I am sure partitioned tables will have an impact but on the other hand, it will solve the problem I have now without touching the legacy code.
On Wed, Apr 17, 2019 at 6:41 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
On Thu, 18 Apr 2019 at 10:56, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> Note that unless you regularly query for only-manually-inserted or
> only-automatically-inserted data, this will be useless and will make
> queries more expensive, with no upside.
Going by "Since those rows are inserted by hand, they will be lost
when the table will be reimported.", I just imagined there must be
some sort of TRUNCATE + INSERT/COPY operation going on, in which case
there would be a good upside of not having to perform a DELETE of all
automatic records instead of the TRUNCATE.
I could be wrong to assume that though.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Thank you David, I will use list.
On Wed, Apr 17, 2019 at 6:42 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
On Thu, 18 Apr 2019 at 10:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> CREATE TABLE automatic.measurement_automatic PARTITION OF
> automatic.measurement
> test-# FOR VALUES FROM (1) TO (1)
> test-# PARTITION BY RANGE (origin);
> ERROR: empty range bound specified for partition "measurement_automatic"
> DETAIL: Specified lower bound (1) is greater than or equal to upper
> bound (1).
>
> OOPS, so lets cheat:
Or you could partition by LIST.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services