Re: Performance issues during pg_restore -j with big partitioned table

by process 465409. --> 44383 is test_runs_raw (the master partitioned table) Process 465409: ALTER TABLE ONLY public.test_runs_raw ADD CONSTRAINT test_runs_raw_partitioned_pkey PRIMARY KEY (workitem_n, run_n); Process 465408: COPY public.test_runs_raw__part_max10120k(...) FROM stdi

Re: sequence on daily log partitioned table

On Monday, April 21, 2025, senor wrote: > > I'm mainly interested in understanding how this works or why it can't > Partition routing happens on fully-formed tuples. They thus must be formed initially using only context, like defaults, attached to the partitioned table.

sequence on daily log partitioned table

log table receiving input from sensor equipment. The partitions would cover some time range like an hour or day. In all cases I want an ID column to default to a nextval from a sequence but starting over for each day. If I set the default on the partitioned table, I would need to alter it at midni

Re: Performance issues during pg_restore -j with big partitioned table

ght get moved across partition * boundaries, risking deadlock and/or loss of previously * loaded data. (We assume that all partitions of a * partitioned table will be treated the same way.) Thanks in advance, Dimitris -- Adrian Klaver adrian.kla...@aklaver.com

Performance issues during pg_restore -j with big partitioned table

Hello list. My database includes one table with 1000 partitions, all of them rather sizeable. I run: pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error --no-owner --no-privileges -n public -d newdb custom_format_dump.pgdump Right now after 24h of restore, I notice weird beha

Re: Performance issues during pg_restore -j with big partitioned table

of database 44090; blocked by process 465409. --> 44383 is test_runs_raw (the master partitioned table) Process 465409: ALTER TABLE ONLY public.test_runs_raw ADD CONSTRAINT test_runs_raw_partitioned_pkey PRIMARY KEY (workitem_n, run_n); Process 465408: COPY public.test_runs_raw__pa

Re: Performance issues during pg_restore -j with big partitioned table

On 4/4/25 9:45 AM, Ron Johnson wrote: TRUNCATE statements inside of "toc.dat" files?  I'm skeptical. See my post here: https://www.postgresql.org/message-id/7be2dcc6-3ba4-4e3f-a154-8d13d816aa9b%40aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com

Re: Performance issues during pg_restore -j with big partitioned table

On Wed, Apr 2, 2025 at 1:32 PM Dimitrios Apostolou wrote: > Hello list. > > My database includes one table with 1000 partitions, all of them rather > sizeable. I run: > >pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error > --no-owner --no-privileges -n public -d newdb custom_

Re: Performance issues during pg_restore -j with big partitioned table

On 4/4/25 08:09, Dimitrios Apostolou wrote: On Fri, 4 Apr 2025, Adrian Klaver wrote: On 4/4/25 06:13, Dimitrios Apostolou wrote:  On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:  Bug? This happened on a postgres compiled from last week's master branch. Are you talking about the dev versio

Re: Performance issues during pg_restore -j with big partitioned table

On Fri, 4 Apr 2025, Adrian Klaver wrote: On 4/4/25 06:13, Dimitrios Apostolou wrote: On Wed, 2 Apr 2025, Dimitrios Apostolou wrote: Bug? This happened on a postgres compiled from last week's master branch. Are you talking about the dev version? In this thread, yes. My mistake I didn't

Re: Performance issues during pg_restore -j with big partitioned table

On 4/4/25 06:13, Dimitrios Apostolou wrote: On Wed, 2 Apr 2025, Dimitrios Apostolou wrote: Bug? This happened on a postgres compiled from last week's master branch. Are you talking about the dev version? The dump I'm trying to restore is from postgres 17.4. Thanks Dimitris -- Adria

Re: Performance issues during pg_restore -j with big partitioned table

On Wed, 2 Apr 2025, Adrian Klaver wrote: On 4/2/25 10:39 AM, Adrian Klaver wrote: --clean will drop the object entirely not TRUNCATE. I'm guessing that this is being done by you per: https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net "After each fa

Re: Performance issues during pg_restore -j with big partitioned table

On Wed, 2 Apr 2025, Adrian Klaver wrote: On 4/2/25 10:32 AM, Dimitrios Apostolou wrote: Hello list. My database includes one table with 1000 partitions, all of them rather sizeable. I run:   pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error --no-owner --no-privileges

Re: Performance issues during pg_restore -j with big partitioned table

On 4/2/25 10:32 AM, Dimitrios Apostolou wrote: Hello list. My database includes one table with 1000 partitions, all of them rather sizeable. I run:   pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error --no-owner --no-privileges -n public -d newdb custom_format_dump.pgdump

Re: Regarding use of single column as primary key on partitioned table

On Sat, Sep 28, 2024 at 12:55 AM Tom Lane wrote: > Ron Johnson writes: > > On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < > > david.g.johns...@gmail.com> wrote: > >> On Friday, September 27, 2024, Durgamahesh Manne < > >>> Can't we use

Re: Regarding use of single column as primary key on partitioned table

Ron Johnson writes: > On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: >> On Friday, September 27, 2024, Durgamahesh Manne < >>> Can't we use primary key on singal column(betid) on partitioned table >>> rat

Re: Regarding use of single column as primary key on partitioned table

On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, September 27, 2024, Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> >> ERROR: unique constraint on partitioned table must include all >> parti

Re: Regarding use of single column as primary key on partitioned table

> On Sep 27, 2024, at 21:25, Durgamahesh Manne > wrote: > > Can't we use primary key on singal column(betid) on partitioned table rather > than using composite key (placedon,betid)? No. Any unique constraint on a partitioned table must include the partition key, inc

Re: Regarding use of single column as primary key on partitioned table

On Friday, September 27, 2024, Durgamahesh Manne wrote: > > ERROR: unique constraint on partitioned table must include all > partitioning columns > DETAIL: PRIMARY KEY constraint on table "bet" lacks column "placedon" > which is part of the partition key. &g

Regarding use of single column as primary key on partitioned table

rimary key (betid); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "bet" lacks column "placedon" which is part of the partition key. test=> Can't we use primary key on singal column(betid) on p

Re: Query on partitioned table needs memory n_partitions * work_mem

Ok I reproduced the OOM, with only 200 partitions as opposed to 2K that I mentioned before. The keys to reproduce it I believe are: 1. Write millions of rows to *all* partitions 2. Run ANALYSE so that the planner knows about the sizes Here are the correct steps now. Let me know if you manage to

Re: Query on partitioned table needs memory n_partitions * work_mem

On Thu, 18 Jul 2024, David Rowley wrote: On Fri, 12 Jul 2024 at 02:08, Dimitrios Apostolou wrote: * The memory is unnecessarily allocated early on, before any partitions are actually aggregated. I know this because I/O is slow on this device and the table sizes are huge, it's simply not

Re: Query on partitioned table needs memory n_partitions * work_mem

On Fri, 12 Jul 2024 at 02:08, Dimitrios Apostolou wrote: > I can't help but see this as a bug. I see many issues: > > * postgres is not reading from partitions in parallel, but one after the >other. It shouldn't need all this memory simultaneously. I don't know for Hash Aggregate, but for nod

Re: Query on partitioned table needs memory n_partitions * work_mem

On Thursday, July 11, 2024, Dimitrios Apostolou wrote:I wonder how the postgres development community is > > tracking all these issues, I've even started forgetting the ones I have > found, and I'm sure I have previously reported (on this list) a couple of > should-be-easy issues that would be ide

Re: Query on partitioned table needs memory n_partitions * work_mem

On Thu, 11 Jul 2024, Tom Lane wrote: Dimitrios Apostolou writes: The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). So don't do that. Adding partitions is not cost-free. I understand that, they also add an administrative cost that I'd rather avoid. But I ended up adding al

Re: Query on partitioned table needs memory n_partitions * work_mem

Dimitrios Apostolou writes: > The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). So don't do that. Adding partitions is not cost-free. regards, tom lane

Re: Query on partitioned table needs memory n_partitions * work_mem

Thank you for the feedback. So I've managed to reduce the query to a rather simple one: SELECT workitem_n, test_executable_n, bool_or(test_resulttype_n IN (2,3)) FROM test_runs_raw GROUP BY workitem_n, test_executable_n LIMIT 10; The TABLE test_runs_raw has 1000 partitions on R

Re: Query on partitioned table needs memory n_partitions * work_mem

On Thu, 11 Jul 2024 at 13:19, Dimitrios Apostolou wrote: > I have a table with 1000 partitions on PostgreSQL 16. > I notice that a fairly complicated query of the form: > > SELECT ... GROUP BY ... LIMIT ... > > causes the postgres backend process to grow insanely very fast, and the > kernel OOM ki

Query on partitioned table needs memory n_partitions * work_mem

Hello list, I have a table with 1000 partitions on PostgreSQL 16. I notice that a fairly complicated query of the form: SELECT ... GROUP BY ... LIMIT ... causes the postgres backend process to grow insanely very fast, and the kernel OOM killer to kill it rather soon. It seems it tries to alloca

Add primary key to existing, partitioned table

Hi, We have an application currently running on PostgreSQL 12, partitioned as shown below. Database size is approx. 1.4TB. We are now in the process of upgrading the application, using PostgreSQL 15. In the new version, there is an optional step to implement 'primary key' on history table. In the

Re: Hash Index on Partitioned Table

t I'm ok now :-) > > > > Thanks again, > > Peter > > > > > > -Original Message- > From: "Tom Lane" > Sent: Wednesday, May 31, 2023 10:07am > To: "peter.boris...@kartographia.com" > Cc: pgsql-general@lists.postgresq

Re: Hash Index on Partitioned Table

day, May 31, 2023 10:07am To: "peter.boris...@kartographia.com" Cc: pgsql-general@lists.postgresql.org Subject: Re: Hash Index on Partitioned Table "peter.boris...@kartographia.com" writes: > I have a rather large database with ~250 billion records in a partitioned

Re: Hash Index on Partitioned Table

"peter.boris...@kartographia.com" writes: > I have a rather large database with ~250 billion records in a partitioned > table. The database has been running and ingesting data continuously for > about 3 years. > I had a "regular" BTree index on one of th

Hash Index on Partitioned Table

Dear PostgreSQL Community, I have a rather large database with ~250 billion records in a partitioned table. The database has been running and ingesting data continuously for about 3 years. I had a "regular" BTree index on one of the fields (a unique bigint column) but it was g

Re: Cannot convert partitioned table to a view

> > The fact that creating a _RETURN rule for a table causes it to be > changed into a view. That's ugly and arguably dangerous. > > Right, thank you. Cheers, -- Kouber Saparev

Re: Cannot convert partitioned table to a view

er). I think maybe you and Tom are talking about different things. Why do you need rules and views, when a regular old partitioned table can have foreign tables in it? -- Angular momentum makes the world go 'round.

Re: Cannot convert partitioned table to a view

Kouber Saparev writes: > Excuse me, I didn't understand what was about to be ripped out then. The fact that creating a _RETURN rule for a table causes it to be changed into a view. That's ugly and arguably dangerous. regards, tom lane

Re: Cannot convert partitioned table to a view

> > No ... what has that got to do with this? We don't particularly care > what a foreign table is referencing, as long as it acts like a table. > Thank you. > We're more likely to rip it out as no-longer-needed than to expand what it > does. Excuse me, I didn't understand what was about to

Re: Cannot convert partitioned table to a view

Kouber Saparev writes: > Does that mean that the ability to point remote partitions to views (on the > remote side) will be forbidden in the future? No ... what has that got to do with this? We don't particularly care what a foreign table is referencing, as long as it acts like a table.

Re: Cannot convert partitioned table to a view

На чт, 6.10.2022 г. в 17:02 ч. Tom Lane написа: > That is an ancient backwards-compatibility hack that you should not > rely on, and most certainly shouldn't try to make use of from user > code. It exists because very ancient versions of pg_dump didn't > know how to dump views as views. We're m

Re: Cannot convert partitioned table to a view

Kouber Saparev writes: > I noticed that attaching the "_RETURN" rule to a table converts it to a > view, and the operation is irreversible, i.e. once attached, I cannot drop > the rule and put the relation back to a table one. That is an ancient backwards-compatibility hack that you should not re

Cannot convert partitioned table to a view

Is there a special reason why masking views behind table partitions is not allowed? Trying to do so raises an error: CREATE RULE "_RETURN" AS ON SELECT TO xxx_20220715 DO INSTEAD SELECT * FROM yyy WHERE ...; ERROR: cannot convert partitioned table "xxx_20220715" to a view

Upgrade 13 to 14 with replication of partitioned table

ed", "id"); ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING INDEX "closed_sessions_closed_id_idx"; --- closed_sessions is partitioned table and column closed is used for replica identity index. Cluster version 13 works fine... How can I upgrade the cluster? Sergey Belyashov.

CREATE INDEX ONLY on a partitioned table?

Postgresql 12.5. https://www.postgresql.org/docs/12/ddl-partitioning.html mentions creating indices on children, and then attaching them to a table which previously had CREATE INDEX ... ONLY ON executed. Can I do something similar on an existing table (for example, on a heavily partitioned

Re: Partitioned Table Index Column Order

On Thu, 24 Jun 2021 at 11:56, Tom Lane wrote: > > David Rowley writes: > > On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera > > wrote: > >> It is not relevant from the partitioning point of view. Other factors > >> can be used to decide the column order. > > > I'm not so sure that's really 100% tr

Re: Partitioned Table Index Column Order

David Rowley writes: > On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera wrote: >> It is not relevant from the partitioning point of view. Other factors >> can be used to decide the column order. > I'm not so sure that's really 100% true. There is at least one > partitioning feature that will work

Re: Partitioned Table Index Column Order

On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera wrote: > > On 2021-Jun-23, Rumpi Gravenstein wrote: > > > As a best practice is it better to create the primary key starting or > > ending with the partition column? > > It is not relevant from the partitioning point of view. Other factors > can be use

Re: Partitioned Table Index Column Order

Alvaro Herrera writes: > On 2021-Jun-23, Rumpi Gravenstein wrote: >> As a best practice is it better to create the primary key starting or >> ending with the partition column? > It is not relevant from the partitioning point of view. Other factors > can be used to decide the column order. See i

Re: Partitioned Table Index Column Order

On 2021-Jun-23, Rumpi Gravenstein wrote: > As a best practice is it better to create the primary key starting or > ending with the partition column? It is not relevant from the partitioning point of view. Other factors can be used to decide the column order. -- Álvaro Herrera Valdivia, C

Partitioned Table Index Column Order

All, I'm on PostgreSQL 13 and have a partitioned table with a primary key. create table t( a integer, b integer, c varchar, d .. ) partitioned by range( a ); As a best practice is it better to create the primary key starting or ending with the partition column? e.g. 1) t_pkey primary k

What is the expected difference in performance between querying a all partitions of a partitioned table and non partitioned one?

Hi there, my question is, shouldn't a query that spans all partitions in a partionend table be roughly the same in performance as one on a non partionend table? I'm getting roughly 50% slower performance on a partioned table. I have tried this on a macbook and ubuntu server. More details are belo

Re: Unable to execute Query in parallel for partitioned table

Hi Albe, We have checked as per your suggestion and we are good now. Thank you !!! On Thu, 11 Feb, 2021, 8:49 PM Brajendra Pratap, < brajendra.pratap...@gmail.com> wrote: > Hi Albe, > > Thank you so much for information, will check this and get back to you if > any help required. > > I have a

Re: Unable to execute Query in parallel for partitioned table

Hi Albe, Thank you so much for information, will check this and get back to you if any help required. I have a doubt why didn't the parallelism works here ,could u plz guide me? Thank you so much again. On Thu, 11 Feb, 2021, 1:23 PM Laurenz Albe, wrote: > On Thu, 2021-02-11 at 05:09 +0530, Br

Re: Unable to execute Query in parallel for partitioned table

On Thu, 2021-02-11 at 05:09 +0530, Brajendra Pratap wrote: > I am unable to execute the below in parallel plz suggest how can I achieve > parallelism here. > > select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER() OVER (order by > trn_transaction_date desc ) AS RowNumber from ( > select *

Unable to execute Query in parallel for partitioned table

Hi, I am unable to execute the below in parallel plz suggest how can I achieve parallelism here. select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER() OVER (order by trn_transaction_date desc ) AS RowNumber from ( select * from transactions where trn_store_date_id=20201202) abc; Query plan

Re: Partitioned Table conversion to Non-Partition table in postgresql open source

lun, 28 de dic. de 2020 a la(s) 04:38, Brajendra Pratap Singh ( singh.bpratap...@gmail.com) escribió: > Hi, > > Currently we are running on open-postgresql 9.6 version with some > partitioned Table which we need to convert from Inheritance partitioning to > declarative partitioning d

Partitioned Table conversion to Non-Partition table in postgresql open source

Hi, Currently we are running on open-postgresql 9.6 version with some partitioned Table which we need to convert from Inheritance partitioning to declarative partitioning during migration to 12.3 open-postgresql version. Is there any safest and faster way to fulfill this requirement, Please

Re: Unexpected result count from update statement on partitioned table

Michael Lewis writes: > On Fri, Dec 18, 2020 at 12:16 PM Tom Lane wrote: >> Yeah, this is a fairly fundamental shortcoming in inheritance_planner(): >> it supposes that it can duplicate the whole query for each target table. > Are there other examples of gotchas with this? Would it be any volati

Re: Unexpected result count from update statement on partitioned table

On Fri, Dec 18, 2020 at 12:16 PM Tom Lane wrote: > Laurenz Albe writes: > > The subquery is executed twice, and the two executions obviously don't > > return the same results. I am at a loss for an explanation ... > > Yeah, this is a fairly fundamental shortcoming in inheritance_planner(): > it

Re: Unexpected result count from update statement on partitioned table

Laurenz Albe writes: > The subquery is executed twice, and the two executions obviously don't > return the same results. I am at a loss for an explanation ... Yeah, this is a fairly fundamental shortcoming in inheritance_planner(): it supposes that it can duplicate the whole query for each targe

Re: Unexpected result count from update statement on partitioned table

Would (task_type in (1,2)) make any logical difference? On 12/18/20 6:11 AM, Craig McIlwee wrote: Despite looking at this query on and off for a couple of days, it wasn't until seeing it in Lauenz's reply that I noticed  a logical issue with the query that changes things a bit.  There should be

Re: Unexpected result count from update statement on partitioned table

Despite looking at this query on and off for a couple of days, it wasn't until seeing it in Lauenz's reply that I noticed a logical issue with the query that changes things a bit. There should be parenthesis around the task_type predicates, otherwise you end up getting reserved rows in the result

Re: Unexpected result count from update statement on partitioned table

On Thu, 2020-12-17 at 12:21 -0500, Craig McIlwee wrote: > Our application uses a queue-like table to assign tasks to users and this has > worked well for us for a few years. Now we are in the process of adding some > restrictions to which tasks a user can > work on and that is based on an attrib

Unexpected result count from update statement on partitioned table

Hello, Our application uses a queue-like table to assign tasks to users and this has worked well for us for a few years. Now we are in the process of adding some restrictions to which tasks a user can work on and that is based on an attribute of each task that does not change for the task's lifes

Re: pg_dump of partitioned table not working.

On 12/2/20 6:14 PM, Ron wrote: On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.  Maybe we should add it. Or actually: that syntax does do

Re: pg_dump of partitioned table not working.

On 12/2/20 6:54 PM, Adrian Klaver wrote: On 12/2/20 4:38 PM, Ron wrote: On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing app

Re: pg_dump of partitioned table not working.

On 12/2/20 4:38 PM, Ron wrote: On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the d

Re: pg_dump of partitioned table not working.

ention to a possible typo-related problem, as opposed to the true "partitioned table" problem. The fact that you didn't ask a better (more specific question), or otherwise state your expectations (which I presume would have pointed out the partitioned table dynamic) didn't help.

Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the database you were dumping was emp

Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:14 PM Ron > wrote: > It does seem like there might be reason to have a switch along > the lines of "--include-child-tables". That would be great, but won't help me in v12. I'd pr

Re: pg_dump of partitioned table not working.

On Wed, Dec 2, 2020 at 5:14 PM Ron wrote: > > > It does seem like there might be reason to have a switch along > > the lines of "--include-child-tables". > > That would be great, but won't help me in v12. > > I'd probably just relocate the table to a separate schema and require that all partition

Re: pg_dump of partitioned table not working.

On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the database you were dumping was empty but the one you were checking was not.

Re: pg_dump of partitioned table not working.

On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature. Maybe we should add it. Or actually: that syntax does do something, but it selects tables

Re: pg_dump of partitioned table not working.

On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron mailto:ronljohnso...@gmail.com>> wrote: What am I missing? (Specify

Re: pg_dump of partitioned table not working.

On 12/2/20 5:49 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Tom Lane > wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.

Re: pg_dump of partitioned table not working.

On Wed, Dec 2, 2020 at 5:06 PM Ron wrote: > On 12/2/20 5:35 PM, David G. Johnston wrote: > > On Wednesday, December 2, 2020, Ron wrote: > >> What am I missing? >> >> (Specifying the whole file name because multiple versions are installed.) >> >> $ /usr/lib/postgresql/12/bin/pg_dump --version >>

Re: pg_dump of partitioned table not working.

On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron > wrote: What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version pg_dump (Postgre

Re: pg_dump of partitioned table not working.

On 12/2/20 5:42 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron <mailto:ronljohnso...@gmail.com>> wrote: What am I missing? postgres=# \d+ measurement    Partitioned table "public.measurement" $ /usr/lib/postgr

Re: pg_dump of partitioned table not working.

I wrote: > What you need here is something like "pg_dump -t measurement*" > to indicate that you want measurement's child tables too, but > AFAIR pg_dump has no such feature. Maybe we should add it. Or actually: that syntax does do something, but it selects tables by pattern matching not hierarch

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Tom Lane wrote: > > > What you need here is something like "pg_dump -t measurement*" > to indicate that you want measurement's child tables too, but > AFAIR pg_dump has no such feature. -t accepts a pattern in pg_dump. But that requires the user to adhere to a n

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Ron wrote: > What am I missing? > > postgres=# \d+ measurement > Partitioned table "public.measurement" > > > $ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement > --data-only > I

Re: pg_dump of partitioned table not working.

Ron writes: > What am I missing? There's no data in a partitioned table per se, so the result is not surprising. What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such featur

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Ron wrote: > What am I missing? > > (Specifying the whole file name because multiple versions are installed.) > > $ /usr/lib/postgresql/12/bin/pg_dump --version > pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1) > > postgres=# \d+ measurement > > psql? on (de

pg_dump of partitioned table not working.

What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1) postgres=# \d+ measurement    Partitioned table "public.measur

Re: Rows removed on child table when updating parent partitioned table.

On Fri, Oct 2, 2020 at 9:11 AM Eduard Català wrote: > If no one else gives an opinion I will open a bug for at least, force an > update of the documentation. > It's been seen and begun to be discussed over on -hackers [1]. [1] https://www.postgresql.org/message-id/flat/CA%2BHiwqFvkBCmfwkQX_yBqv

Re: Rows removed on child table when updating parent partitioned table.

On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston wrote: > The convention on these lists is to inline or bottom-post, please do not > top-post. > > On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong > wrote: > >> I've been away from coding for several years, but dusting off my chops >> and getting b

Re: Rows removed on child table when updating parent partitioned table.

On Thu, Oct 1, 2020 at 2:02 PM David G. Johnston wrote: > The convention on these lists is to inline or bottom-post, please do not > top-post. > > On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong > wrote: > >> I've been away from coding for several years, but dusting off my chops >> and getting b

Re: Rows removed on child table when updating parent partitioned table.

The convention on these lists is to inline or bottom-post, please do not top-post. On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong wrote: > I've been away from coding for several years, but dusting off my chops and > getting back up to speed with PostgreSQL (love it!). So please forgive me > if

Re: Rows removed on child table when updating parent partitioned table.

I've been away from coding for several years, but dusting off my chops and getting back up to speed with PostgreSQL (love it!). So please forgive me if my early answers here come off as naive. But my understanding of this suggests that you shouldn't be using "update" on a serial field. I'm guessing

Rows removed on child table when updating parent partitioned table.

Hi developers, We have a strange case where some rows are removed. I think it is a bug, but before notifying it I prefer to ask here where I am wrong. *Postgres 12* Given the following structure: create table parent ( id serial, constraint parent_pkey primary key (id)) partition by range (id); cr

RE: PG13 partitioned table logical replication

di 10 septembre 2020 17:04 À : Nicolas Sornin ; pgsql-gene...@postgresql.org Objet : Re: PG13 partitioned table logical replication On 2020-09-10 14:13, Nicolas Sornin wrote: > I just made some test of upcoming version of porstgresql, especially > the ability to publish partitioned table via r

Re: PG13 partitioned table logical replication

On 2020-09-10 14:13, Nicolas Sornin wrote: I just made some test of upcoming version of porstgresql, especially the ability to publish partitioned table via root partition. I tried to setup PG13 to PG12 replication with different partitioning schemes. My first attempt was to replicate time

PG13 partitioned table logical replication

Dear community, I just made some test of upcoming version of porstgresql, especially the ability to publish partitioned table via root partition. I tried to setup PG13 to PG12 replication with different partitioning schemes. My first attempt was to replicate time partitioned table in PG13

Partitioned table migration strategy

Hello all, Are there any migration strategies/best practices when migrating from PG11 partitioned tables to PG12 partitioned tables that don't involve extended downtime? We have about 900 partitions (hash partitions) in PG11 with millions of rows that we need to migrate to PG12 and (ideally) would

Re: [MASSMAIL]Re: partitioned table

I think you might create a block "Begin-Commit", before procedure call. El 2020-01-09 12:15, Michael Lewis escribió: when I manually mocking the data into both tables are fine and when I run the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion constraint matching on the CON

Re: partitioned table

> > when I manually mocking the data into both tables are fine and when I run > the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion > constraint matching on the CONFLICT specification > > the procedure is > ... > INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, >

partitioned table

I have table and partitioned for year year like this: CREATE TABLE ecisdrdm.bnft_curr_fact (bnft_fact_id numeric(38), bene_cntry_of_brth_id  numeric(38), bene_cntry_of_rsdc_id numeric(38), bene_cntry_of_ctznshp_id numeric(38), frm_id numeric(38), svc_ctr_id numeric(38), actn_dt_in_id numer

Re: deadlock on declarative partitioned table (11.3)

disregard. problem found. i was locking the wrong table. On 9/16/2019 11:10 AM, Kevin Wilkinson wrote: on linux, pg11.3, i have a (declarative) partitioned table with a deadlock that i do not understand. one process does a copy to the partitioned table. another process is executing a jdbc

deadlock on declarative partitioned table (11.3)

on linux, pg11.3, i have a (declarative) partitioned table with a deadlock that i do not understand. one process does a copy to the partitioned table. another process is executing a jdbc batch of commands to "atomically" replace one of the table partitions. it has the followin

Re: pg_stat_user_tables.n_tup_ins empty for partitioned table

_reset --- 2019-03-28 14:40:01.945332+01 Since the partitioned table of that month has an n_tup_ins that is an order lower than n_live_tup I suspect this could be the cause: # select schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup from pg_stat_user_tables where relname = &#x

  1   2   >