pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning existing table issue - Help needed!
10+ messages / 5 participants
[nested] [flat]
* Partitioning existing table issue - Help needed!
@ 2021-02-04 13:47 dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: dbatoCloud Solution @ 2021-02-04 13:47 UTC (permalink / raw)
To: pgsql-admin
Dear All,
I am partitioning the large table using declarative partitioning method in
PostgreSQL 10.13.
The below steps which I am using are :
1. created parent table
2. create sub-partitioned table
3. created index for each partition
4. created function
5. 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?
Kindly advise.
Regards,
AShok
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-04 14:10 Magnus Hagander <magnus@hagander.net>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Magnus Hagander @ 2021-02-04 14:10 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 12:04 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 2 replies; 10+ messages in thread
From: dbatoCloud Solution @ 2021-02-05 12:04 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: pgsql-admin
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
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/
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 13:02 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: dbatoCloud Solution @ 2021-02-05 13:02 UTC (permalink / raw)
To: pgsql-admin
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
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/
> <http://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/
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 13:08 Holger Jakobs <holger@jakobs.com>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Holger Jakobs @ 2021-02-05 13:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 13:12 John Scalia <jayknowsunix@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 10+ messages in thread
From: John Scalia @ 2021-02-05 13:12 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 13:21 Magnus Hagander <magnus@hagander.net>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Magnus Hagander @ 2021-02-05 13:21 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; +Cc: pgsql-admin
(please avoid top-posting, it makes the thread really hard to read)
On Fri, Feb 5, 2021 at 1:04 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
This looks a lot like you may have created a trigger that actually
fires recursively. What does your trigger actually do that would
require that much stack? Can you post the code of the trigger?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 13:55 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 10+ messages in thread
From: dbatoCloud Solution @ 2021-02-05 13:55 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: pgsql-admin
My Apologies!
The below trigger which I created for after partitioning for each
partitions
CREATE trigger insert_contact_transaction_history_P_PART_BKP_Y2017_AM
BEFORE INSERT ON core.contact_transaction_history_P_PART_BKP_Y2017_AM
FOR EACH ROW EXECUTE PROCEDURE
core.contact_transaction_history_Parent_PART_BKP_AM_();
--
---
---
---
CREATE trigger insert_contact_transaction_history_P_PART_BKP_Y2025_AM
BEFORE INSERT ON core.contact_transaction_history_P_PART_BKP_Y2025_AM
FOR EACH ROW EXECUTE PROCEDURE
core.contact_transaction_history_Parent_PART_BKP_AM_();
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
On Fri, Feb 5, 2021 at 6:52 PM Magnus Hagander <magnus@hagander.net> wrote:
> (please avoid top-posting, it makes the thread really hard to read)
>
>
> On Fri, Feb 5, 2021 at 1:04 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
>
> This looks a lot like you may have created a trigger that actually
> fires recursively. What does your trigger actually do that would
> require that much stack? Can you post the code of the trigger?
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/
> Work: https://www.redpill-linpro.com/
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 14:01 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: dbatoCloud Solution @ 2021-02-05 14:01 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org
Dear John,
Unfortunately, I don't have RDS_SUPERUSER credentials and I'm not 100% sure
that AWS will provide that please
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
On Fri, Feb 5, 2021 at 6:42 PM John Scalia <jayknowsunix@gmail.com> wrote:
> 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/
> <http://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/
>> <http://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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning existing table issue - Help needed!
@ 2021-02-05 14:50 Thomas Kellerer <shammat@gmx.net>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2021-02-05 14:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
dbatoCloud Solution schrieb am 04.02.2021 um 14:47:
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
> 1. created parent table
> 2. create sub-partitioned table
> 3. created index for each partition
> 4. created function
> 5. 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.
What do you need the trigger for?
And what does the trigger do?
If you insert into the parent table, Postgres will automatically store
the row in the appropriate partition.
Apart from the need to upgrade because row triggers on a partitioned table require Postgres 11,
you might want to consider upgrading to at least Postgres 12 anyway, as there were
substantial partitioning improvements in 11 and 12 (performance and usability wise)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-02-05 14:50 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-04 13:47 Partitioning existing table issue - Help needed! dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-04 14:10 ` Magnus Hagander <magnus@hagander.net>
2021-02-05 12:04 ` dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-05 13:02 ` dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-05 13:08 ` Holger Jakobs <holger@jakobs.com>
2021-02-05 13:12 ` John Scalia <jayknowsunix@gmail.com>
2021-02-05 14:01 ` dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-05 13:21 ` Magnus Hagander <magnus@hagander.net>
2021-02-05 13:55 ` dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-05 14:50 ` Thomas Kellerer <shammat@gmx.net>
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