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 1l80qt-0004fV-Q6 for pgsql-admin@arkaria.postgresql.org; Fri, 05 Feb 2021 13:08:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l80qs-00043I-Iw for pgsql-admin@arkaria.postgresql.org; Fri, 05 Feb 2021 13:08:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l80qs-00043B-2k for pgsql-admin@lists.postgresql.org; Fri, 05 Feb 2021 13:08:46 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l80qo-0006uw-0Z for pgsql-admin@lists.postgresql.org; Fri, 05 Feb 2021 13:08:45 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 7734C380572; Fri, 5 Feb 2021 14:08:39 +0100 (CET) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id aXbG3GnyG_SJ; Fri, 5 Feb 2021 14:08:39 +0100 (CET) Received: from [192.168.178.197] (ip-37-201-154-46.hsi13.unitymediagroup.de [37.201.154.46]) by rs.plausibolo.de (Postfix) with ESMTPSA id F25E6380531; Fri, 5 Feb 2021 14:08:38 +0100 (CET) Subject: Re: Partitioning existing table issue - Help needed! To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Message-ID: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com> Date: Fri, 5 Feb 2021 14:08:37 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="sdXEAP51Sf0ZvCK2rYilkdryh1Ndj23fi" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --sdXEAP51Sf0ZvCK2rYilkdryh1Ndj23fi Content-Type: multipart/mixed; boundary="36LPjI9siyXsv3AzB2rydhf2okeFC1327"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com> Subject: Re: Partitioning existing table issue - Help needed! References: In-Reply-To: --36LPjI9siyXsv3AzB2rydhf2okeFC1327 Content-Type: multipart/alternative; boundary="------------F48F23725C49A3925CCDDC49" Content-Language: de-DE This is a multi-part message in MIME format. --------------F48F23725C49A3925CCDDC49 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable 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 i= t . > > alter system set max_stack_depth=3D'12288kb'; > SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM=20 > 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=C2=A0| *WApp :*+91 81975 99922= * > > W: https://dbatocloudsolution.blogspot.in/=20 > | E: dbatocloud17@gmail.com=20 > > > > > On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution=20 > > wrote: > > Thanks Magnus, > > I was able to create trigger for each partition now successfully. > > Now I tried to insert records from the old=C2=A0tableto newly creat= ed > partition table but I am receiving the below error. > Comand=C2=A0# insert into > core.contact_transaction_history_Parent_PART_BKP_AM =C2=A0select * = from > core.contact_transaction_history; > > *SQL Error [54001]: ERROR: stack depth limit exceeded > =C2=A0 Hint: Increase the configuration parameter "max_stack_depth"= > (currently 6144kB), after ensuring the platform's stack depth > limit is adequate.* > =C2=A0 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=C2=A0| *WApp :*+91 81975 9= 9922* > > W: https://dbatocloudsolution.blogspot.in/ > | E: > dbatocloud17@gmail.com > > > > On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander > > wrote: > > On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution > > 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=C2=A0 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 > > > >=C2=A0 =C2=A0Detail: Partitioned tables cannot have ROW trigge= rs. > > > > > > > > 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 > . > > In v10 you have to create the triggers individually for each > partition. > > --=20 > =C2=A0Magnus Hagander > =C2=A0Me: https://www.hagander.net/ = > =C2=A0Work: https://www.redpill-linpro.com/ > > You must be or become superuser in order to use this command. Check=20 which roles are superuser using \du in psql. Maybe you can switch to a=20 superuser role using SET ROLE to abcdef; if abcdef is a superuser role. -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------F48F23725C49A3925CCDDC49 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable


Am 05.02.21 um 14:02 schrieb dbatoClou= d 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

=C2=A0

= Thanks & Best Wishes,

= Ashok

=C2=A0

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

Ashokkuma= r Mani=C2=A0 (OCP12c/= 11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971= 54 723 0075 | +91 90086 70302=C2=A0| WApp : +91 81975 99922<= /span>

W: = https://db= atocloudsolution.blogspot.in/ | E: dbatocloud= 17@gmail.com



On Fri, Feb 5, 2021 at 5:34= PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote:
=
Th= anks Magnus,
I was able to create trigger for each partition now successfully.=C2=A0
No= w I tried to insert records from the old=C2=A0tableto newly created partition table but I am receiving the below error.
Co= mand=C2=A0# insert into core.contact_transaction_history_Parent_PART_BKP_AM =C2=A0select * from core.contact_transaction_history;=C2=A0=
SQL Error [54001]: ERROR: stack depth limit exceeded
= =C2=A0 Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
=C2=A0 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

=C2=A0

=C2=A0

Thanks & Best Wishes,

Ashok

=C2=A0

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

Ashok= kumar Mani=C2=A0 (OCP= 12c/11g/10g/9i, AWS SAA, M103)

Duba= i , UAE | BLR , INDIA

M: = +971 54 723 0075 | +91 90086 70302=C2=A0| WApp : +91 81975 99922<= /b>

W: https:= //dbatocloudsolution.blogspot.in/ | E: <= a href=3D"mailto:dbatocloud17@gmail.com" target=3D"_blank" moz-do-not-send=3D"true">dbatoc= loud17@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@g= mail.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=C2=A0 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
>
>=C2=A0 =C2=A0Detail: 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.

In v10 you have to create the triggers individually for each partition.

--
=C2=A0Magnus Hagander
=C2=A0Me: https://www.ha= gander.net/
=C2=A0Work: https://www.redpill-linpro.com/

You must be or become superuse= r 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 --------------F48F23725C49A3925CCDDC49-- --36LPjI9siyXsv3AzB2rydhf2okeFC1327-- --sdXEAP51Sf0ZvCK2rYilkdryh1Ndj23fi Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYB1DVgUDAAAAAAAKCRCKV5yTsxlhugHj AJwKnRKaNLsNMcHeZ6ZZ/Etvor4h0QCffrkBlrcXCo7tUA82J4B4yPtsl/M= =/dia -----END PGP SIGNATURE----- --sdXEAP51Sf0ZvCK2rYilkdryh1Ndj23fi--