Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l80ui-0004lv-Im for pgsql-admin@arkaria.postgresql.org; Fri, 05 Feb 2021 13:12:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l80uh-0000Ht-8g for pgsql-admin@arkaria.postgresql.org; Fri, 05 Feb 2021 13:12:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l80ug-0000Hl-SY for pgsql-admin@lists.postgresql.org; Fri, 05 Feb 2021 13:12:43 +0000 Received: from mail-qk1-x734.google.com ([2607:f8b0:4864:20::734]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l80ue-0000yZ-50 for pgsql-admin@lists.postgresql.org; Fri, 05 Feb 2021 13:12:42 +0000 Received: by mail-qk1-x734.google.com with SMTP id k193so6734209qke.6 for ; Fri, 05 Feb 2021 05:12:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=7VhIrWFvDvXTtnrFf2oBtyIPRpp8Dkp/RQW7UYHgZe4=; b=vDfe897K2d+Rc2tP0RNIGDu6PAed1Ooy4WQg+59nUkuhgBk2DfgAJY90Wt3PszDRG0 5l2Kye/GVYdf4MSfmJ9h6Kdr57AtnrA/uygWuPzuSJlLbZne10TS+Lm5fjcOKLbbkfbi VuuJw2MLZtRYOVHQ3iOfZcn1zMNR8UoVnsIisF7LXT5it+THIAvSpN/GFScxDYp+IagY Av2FrC0Hhy3tVHx29rdOiQb3DN8ByXedj5MVW59aH+SpdivwcrK3Dkh005YOGynTyN/2 WlBSgZH8xiekdR7QeqHcBEyjoGRGJKhjUy7CAnZLFS5XQEj7wHKQhIGN5taAwb24poVy nLHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=7VhIrWFvDvXTtnrFf2oBtyIPRpp8Dkp/RQW7UYHgZe4=; b=phPcMD6TbFRsWasQC8AhRW1YPw6CLC6bEVsgi1lrI2ssJNNKxnLjjmZWUDr8Pp8UE3 c+AhR18FNJ+Hp/ti1C1zmlv94DXCwvlMIwIWhogUh61CBQDKhBtueq04taZCWPuIr4Bz 1ZR55mDUeNiju6Tz+8EzarG0fOx+i21Cca+X4xWkBEmfeg1Wc+nqEKOk9/SFbvKRF62f 7Ce9jmrnIXa4RXzdxKaZ0AODdEVYe1zdT99kqQnDCvEJ1CKMVJmI5laUjft36LnSqlq5 vn+sCpzfjQ1eeDHZYI3/NrYwugBoA/dRZeUv3/3sW/rbHbf0UrY3G5GH4Ira3bBwrutB QDbg== X-Gm-Message-State: AOAM531gFKQ6s/RYv9zK9u08ZWN4TyzkJ+skHHSPGVUTkQWPTeuWKDI0 ERoq99H+iQ1S9NStWdIJbr/AUeUH4NfB6w== X-Google-Smtp-Source: ABdhPJwWasYfs3ByVa+y8BCIPYryU4mInJN+YJDgljBDwEjs+zJHaGb5BqIH5ogP9ujqtgn14zlgTQ== X-Received: by 2002:a37:9a0d:: with SMTP id c13mr4115867qke.27.1612530758223; Fri, 05 Feb 2021 05:12:38 -0800 (PST) Received: from [192.168.254.67] (h178.165.133.40.static.ip.windstream.net. [40.133.165.178]) by smtp.gmail.com with ESMTPSA id b16sm7750151qtx.85.2021.02.05.05.12.37 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 05 Feb 2021 05:12:37 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-309B88E4-3A9D-4417-8FA2-B856F85EED8B Content-Transfer-Encoding: 7bit From: John Scalia Mime-Version: 1.0 (1.0) Subject: Re: Partitioning existing table issue - Help needed! Date: Fri, 5 Feb 2021 08:12:35 -0500 Message-Id: <9BC7DF8D-F984-4B6A-BB41-450E686B9C20@gmail.com> References: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com> Cc: pgsql-admin@lists.postgresql.org In-Reply-To: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com> To: Holger Jakobs X-Mailer: iPad Mail (18C66) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-309B88E4-3A9D-4417-8FA2-B856F85EED8B Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Holler is correct, do you have the rds_superuser credentials? Or can you do= as Holger specifies? Sent from my iPad > On Feb 5, 2021, at 8:08 AM, Holger Jakobs wrote: >=20 > =EF=BB=BF >=20 >=20 > Am 05.02.21 um 14:02 schrieb dbatoCloud Solution: >>=20 >> Dear All, >> I want to increase this value in postgreSQL but it is not allowing do it .= >>=20 >> alter system set max_stack_depth=3D'12288kb'; >> SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM comma= nd >>=20 >> =20 >>=20 >> Thanks & Best Wishes, >>=20 >> Ashok >>=20 >> =20 >>=20 >> -------------------------------------------------------------------------= -------------------------------------------- >>=20 >> Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103) >>=20 >> Dubai , UAE | BLR , INDIA >>=20 >> M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922 >>=20 >> W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com=20= >>=20 >>=20 >>=20 >> On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution wrote: >>> Thanks Magnus, >>>=20 >>> I was able to create trigger for each partition now successfully.=20 >>>=20 >>> Now I tried to insert records from the old tableto newly created partiti= on 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;=20 >>>=20 >>> SQL Error [54001]: ERROR: stack depth limit exceeded >>> Hint: Increase the configuration parameter "max_stack_depth" (currentl= y 6144kB), after ensuring the platform's stack depth limit is adequate. >>> Where: SQL statement "INSERT INTO core.contact_transaction_history_P_P= ART_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_Y= 2020_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_Y= 2020_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_Y= 2020_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_Y= 2020_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_Y= 2020_AM VALUES (NEW.*)" >>> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_()= line 14 at SQL statement >>> =20 >>>=20 >>> =20 >>>=20 >>> Thanks & Best Wishes, >>>=20 >>> Ashok >>>=20 >>> =20 >>>=20 >>> ------------------------------------------------------------------------= --------------------------------------------- >>>=20 >>> Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103) >>>=20 >>> Dubai , UAE | BLR , INDIA >>>=20 >>> M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922 >>>=20 >>> W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com=20= >>>=20 >>>=20 >>>=20 >>> On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander wro= te: >>>> On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution >>>> wrote: >>>> > >>>> > Dear All, >>>> > >>>> > I am partitioning the large table using declarative partitioning meth= od 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_bk= p_am" is a partitioned table >>>> > >>>> > Detail: Partitioned tables cannot have ROW triggers. >>>> > >>>> > >>>> > >>>> > Please let me know what is the issue with this? >>>>=20 >>>> 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. >>>>=20 >>>> In v10 you have to create the triggers individually for each partition.= >>>>=20 >>>> --=20 >>>> Magnus Hagander >>>> Me: https://www.hagander.net/ >>>> Work: https://www.redpill-linpro.com/ > You must be or become superuser in order to use this command. Check which r= oles are superuser using \du in psql. Maybe you can switch to a superuser ro= le using=20 >=20 > SET ROLE to abcdef;=20 > if abcdef is a superuser role. >=20 >=20 > -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --Apple-Mail-309B88E4-3A9D-4417-8FA2-B856F85EED8B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Holler is correct, do you have the rds_supe= ruser credentials?  Or can you do as Holger specifies?

Sent from my iPad

= On Feb 5, 2021, at 8:08 AM, Holger Jakobs <holger@jakobs.com> wrote:
=EF=BB=BF= =20 =20 =20


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=3D'12288kb';
SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM command

 

Tha= nks & Best Wishes,

Ash= ok

 

---------------------------------------= ----------------------------------------------------------------------------= --

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://dbatoclo= udsolution.blogspot.in/ | E: dbatocloud17@gmail.com



On Fri, Feb 5, 2021 at 5:34 PM= dbatoCloud Solution <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/ | E: dbatocloud17@gmail.com



On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander <magnus@hagander.net> wrote:
On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<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.p= ostgresql.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/
 Work: 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 =20
= --Apple-Mail-309B88E4-3A9D-4417-8FA2-B856F85EED8B--