From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning existing table issue - Help needed!
Date: Fri, 5 Feb 2021 14:08:37 +0100
Message-ID: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com> (raw)
In-Reply-To: <CAEz7P_vuTsSzo=RK58hkvjCaU=vXedf-VGC8Ox9f1x0D+SXQRw@mail.gmail.com>
References: <CAEz7P_v93GF4_irRugWvz7SrCNVHFEihEbuotaDJGu=WGyWbpQ@mail.gmail.com>
<CABUevExYmH6pHajagD5DD8Rm9cHXiWP+YtRMcOSbrQTBej1NkQ@mail.gmail.com>
<CAEz7P_sEfNPq=Xj4nd6WhXiyZwWFZ=BqaTrpYuO3z759z+Vu=w@mail.gmail.com>
<CAEz7P_vuTsSzo=RK58hkvjCaU=vXedf-VGC8Ox9f1x0D+SXQRw@mail.gmail.com>
Am 05.02.21 um 14:02 schrieb dbatoCloud Solution:
>
> Dear All,
> I want to increase this value in postgreSQL but it is not allowing do it .
>
> alter system set max_stack_depth='12288kb';
> SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM
> command
>
> Thanks & Best Wishes,
>
> Ashok
>
> ---------------------------------------------------------------------------------------------------------------------
>
> Ashokkumar Mani /(OCP12c/11g/10g/9i, AWS SAA, M103)/
>
> *Dubai**, UAE**| BLR , INDIA*
>
> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :*+91 81975 99922*
>
> W: https://dbatocloudsolution.blogspot.in/
> <http://dbatocloudsolution.blogspot.in/; | E: dbatocloud17@gmail.com
> <mailto:dbatocloud17@gmail.com>
>
>
>
> On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution
> <dbatocloud17@gmail.com <mailto:dbatocloud17@gmail.com>> wrote:
>
> Thanks Magnus,
>
> I was able to create trigger for each partition now successfully.
>
> Now I tried to insert records from the old tableto newly created
> partition table but I am receiving the below error.
> Comand # insert into
> core.contact_transaction_history_Parent_PART_BKP_AM select * from
> core.contact_transaction_history;
>
> *SQL Error [54001]: ERROR: stack depth limit exceeded
> Hint: Increase the configuration parameter "max_stack_depth"
> (currently 6144kB), after ensuring the platform's stack depth
> limit is adequate.*
> Where: SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
> SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
> SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
> SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
> SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
> SQL statement "INSERT INTO
> core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function
> core.contact_transaction_history_parent_part_bkp_am_() line 14 at
> SQL statement
>
> Thanks & Best Wishes,
>
> Ashok
>
> ---------------------------------------------------------------------------------------------------------------------
>
> Ashokkumar Mani /(OCP12c/11g/10g/9i, AWS SAA, M103)/
>
> *Dubai**, UAE**| BLR , INDIA*
>
> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :*+91 81975 99922*
>
> W: https://dbatocloudsolution.blogspot.in/
> <http://dbatocloudsolution.blogspot.in/; | E:
> dbatocloud17@gmail.com <mailto:dbatocloud17@gmail.com>
>
>
>
> On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander
> <magnus@hagander.net <mailto:magnus@hagander.net>> wrote:
>
> On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
> <dbatocloud17@gmail.com <mailto:dbatocloud17@gmail.com>> wrote:
> >
> > Dear All,
> >
> > I am partitioning the large table using declarative
> partitioning method in PostgreSQL 10.13.
> >
> >
> >
> > The below steps which I am using are :
> >
> >
> >
> > created parent table
> > create sub-partitioned table
> > created index for each partition
> > created function
> > creating trigger but end with below error:-
> >
> > SQL Error [42809]: ERROR:
> "contact_transaction_history_parent_part_bkp_am" is a
> partitioned table
> >
> > Detail: Partitioned tables cannot have ROW triggers.
> >
> >
> >
> > Please let me know what is the issue with this?
>
> The ability to add row triggers on partitioned tables was added in
> PostgreSQL 11, see the release notes at
> https://www.postgresql.org/docs/11/release-11.html
> <https://www.postgresql.org/docs/11/release-11.html;.
>
> In v10 you have to create the triggers individually for each
> partition.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <https://www.hagander.net/;
> Work: https://www.redpill-linpro.com/
> <https://www.redpill-linpro.com/;
>
You must be or become superuser in order to use this command. Check
which roles are superuser using \du in psql. Maybe you can switch to a
superuser role using
SET ROLE to abcdef;
if abcdef is a superuser role.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning existing table issue - Help needed!
In-Reply-To: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox