From: John Scalia <jayknowsunix@gmail.com>
To: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org
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> (raw)
In-Reply-To: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com>
References: <759d3823-0773-346b-4b78-5ece7011736d@jakobs.com>
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 <holger@jakobs.com> wrote:
>
>
>
>
> 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/ | 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.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/
>>>> 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
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: jayknowsunix@gmail.com, holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning existing table issue - Help needed!
In-Reply-To: <9BC7DF8D-F984-4B6A-BB41-450E686B9C20@gmail.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