pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of memory error
11+ messages / 4 participants
[nested] [flat]
* Out of memory error
@ 2021-11-24 05:27 aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: aditya desai @ 2021-11-24 05:27 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
In a trigger function I am creating a temp table . When an update on a
table is executed for say 10k rows. I get the below error.
ERROR: out of shared memory
HINT:You might need to increase max_locks_per_transaction
CONTEXT: SQL Statement "created temp table changedinfo(colName
varchar(100), oldValue varchar(4000), newValue varchar(4000)
Current value of max_locks_per_transaction is 64. Do I have to increase
this?
Regards,
Aditya.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 05:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-11-24 05:52 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
aditya desai <admad123@gmail.com> writes:
> In a trigger function I am creating a temp table . When an update on a
> table is executed for say 10k rows. I get the below error.
> ERROR: out of shared memory
> HINT:You might need to increase max_locks_per_transaction
> CONTEXT: SQL Statement "created temp table changedinfo(colName
> varchar(100), oldValue varchar(4000), newValue varchar(4000)
[ raised eyebrow ... ] If you are concerned about performance,
I'd start by not creating a temp table per row of the outer update.
That's costing probably 100x to 1000x as much as the row update itself.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 06:25 aditya desai <admad123@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: aditya desai @ 2021-11-24 06:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks Tom. However I could not find any solution to achieve the given
requirement. I have to take all values in the temp table and assign it to
an array variable to pass it to the audit procedure as shown below. Can you
please advise ?
CREATE OR REPLACE FUNCTION call_insert_info(
) RETURNS void AS $$
DECLARE
v_message r_log_message[];
OLDVALUE1 varchar(4000);
BEGIN
drop table if exists changedinfo
create temp table changedinfo(colName varchar(100), oldValue
varchar(4000), newValue varchar(4000));
insert into changed infot select 'empName', OLD.empName,
NEW.empName from employee;
insert into changed infot select 'location', OLD.location,
NEW.location from employee;
v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
newvalue ||')' from changedinfo);
perform insert_info(v_message);
raise notice '%',v_message;
END;
$$ LANGUAGE plpgsql;
Regards,
AD.
On Wed, Nov 24, 2021 at 11:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> aditya desai <admad123@gmail.com> writes:
> > In a trigger function I am creating a temp table . When an update on a
> > table is executed for say 10k rows. I get the below error.
>
> > ERROR: out of shared memory
> > HINT:You might need to increase max_locks_per_transaction
> > CONTEXT: SQL Statement "created temp table changedinfo(colName
> > varchar(100), oldValue varchar(4000), newValue varchar(4000)
>
> [ raised eyebrow ... ] If you are concerned about performance,
> I'd start by not creating a temp table per row of the outer update.
> That's costing probably 100x to 1000x as much as the row update itself.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 06:31 Thomas Kellerer <shammat@gmx.net>
parent: aditya desai <admad123@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Thomas Kellerer @ 2021-11-24 06:31 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
aditya desai schrieb am 24.11.2021 um 07:25:
> Thanks Tom. However I could not find any solution to achieve the given requirement. I have to take all values in the temp table and assign it to an array variable to pass it to the audit procedure as shown below. Can you please advise ?
>
> CREATE OR REPLACE FUNCTION call_insert_info(
>
> ) RETURNS void AS $$
> DECLARE
> v_message r_log_message[];
> OLDVALUE1 varchar(4000);
> BEGIN
> drop table if exists changedinfo
> create temp table changedinfo(colName varchar(100), oldValue varchar(4000), newValue varchar(4000));
> insert into changed infot select 'empName', OLD.empName, NEW.empName from employee;
> insert into changed infot select 'location', OLD.location, NEW.location from employee;
>
>
> v_message:= array(select '(' || columname || ',' || oldvalue || ',' || newvalue ||')' from changedinfo);
> perform insert_info(v_message);
> raise notice '%',v_message;
> END;
> $$ LANGUAGE plpgsql;
You don't need a temp table for that. You can create the array directly from the new and old records:
v_message := array[concat_ws(',', 'empName', old.empname, new.empname), concat_ws(',', 'location', old.location, new.location)];
Although nowadays I would probably pass such an "structure" as JSON though, not as a comma separated list.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 06:36 aditya desai <admad123@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 11+ messages in thread
From: aditya desai @ 2021-11-24 06:36 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Ok. Let me try this. Thanks!!
On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer <shammat@gmx.net> wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to
> an array variable to pass it to the audit procedure as shown below. Can you
> please advise ?
> >
> > CREATE OR REPLACE FUNCTION call_insert_info(
> >
> > ) RETURNS void AS $$
> > DECLARE
> > v_message r_log_message[];
> > OLDVALUE1 varchar(4000);
> > BEGIN
> > drop table if exists changedinfo
> > create temp table changedinfo(colName varchar(100), oldValue
> varchar(4000), newValue varchar(4000));
> > insert into changed infot select 'empName', OLD.empName,
> NEW.empName from employee;
> > insert into changed infot select 'location', OLD.location,
> NEW.location from employee;
> >
> >
> > v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
> newvalue ||')' from changedinfo);
> > perform insert_info(v_message);
> > raise notice '%',v_message;
> > END;
> > $$ LANGUAGE plpgsql;
>
>
> You don't need a temp table for that. You can create the array directly
> from the new and old records:
>
> v_message := array[concat_ws(',', 'empName', old.empname,
> new.empname), concat_ws(',', 'location', old.location, new.location)];
>
> Although nowadays I would probably pass such an "structure" as JSON
> though, not as a comma separated list.
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 06:45 Michael Lewis <mlewis@entrata.com>
parent: aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Lewis @ 2021-11-24 06:45 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
It seems like that function has some syntax errors, and also doesn't do
what you want since I presume the "from employee" bit would mean you get
many rows inserted into that temp table for all the existing data and not
the one row you are operating on at the moment the trigger fires.
It is worth noting also that if bulk operations are at all common for this
table then writing this as an after statement trigger will likely be
helpful for performance.
For full context, we'd need to see how the function insert_info is defined.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 07:31 aditya desai <admad123@gmail.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 11+ messages in thread
From: aditya desai @ 2021-11-24 07:31 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
H Michael,
Please see insert_info function below. Also r_log_message is composite data
type and it's definition is also given below.
CREATE OR REPLACE FUNCTION insert_info(
info_array r_log_message[]
) RETURNS varchar AS $$
DECLARE
info_element r_log_message;
BEGIN
FOREACH info_element IN ARRAY info_array
LOOP
INSERT INTO testaditya(
columname,
oldvalue,
newvalue
) VALUES(
info_element.column_name,
info_element.oldvalue,
info_element.newvalue
);
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql;
postgres=# \d r_log_message;
Composite type "public.r_log_message"
Column | Type | Collation | Nullable | Default
-------------+-------------------------+-----------+----------+---------
column_name | character varying(30) | | |
oldvalue | character varying(4000) | | |
newvalue | character varying(4000) | | |
Regards,
Aditya.
On Wed, Nov 24, 2021 at 12:16 PM Michael Lewis <mlewis@entrata.com> wrote:
> It seems like that function has some syntax errors, and also doesn't do
> what you want since I presume the "from employee" bit would mean you get
> many rows inserted into that temp table for all the existing data and not
> the one row you are operating on at the moment the trigger fires.
>
> It is worth noting also that if bulk operations are at all common for this
> table then writing this as an after statement trigger will likely be
> helpful for performance.
>
> For full context, we'd need to see how the function insert_info is defined.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 07:35 aditya desai <admad123@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 11+ messages in thread
From: aditya desai @ 2021-11-24 07:35 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Thomas,
v_message is of composite data type r_log_message and it's definition is as
shown below.
postgres=# \d r_log_message;
Composite type "public.r_log_message"
Column | Type | Collation | Nullable | Default
-------------+-------------------------+-----------+----------+---------
column_name | character varying(30) | | |
oldvalue | character varying(4000) | | |
newvalue | character varying(4000) | | |
Regards,
Aditya.
On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer <shammat@gmx.net> wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to
> an array variable to pass it to the audit procedure as shown below. Can you
> please advise ?
> >
> > CREATE OR REPLACE FUNCTION call_insert_info(
> >
> > ) RETURNS void AS $$
> > DECLARE
> > v_message r_log_message[];
> > OLDVALUE1 varchar(4000);
> > BEGIN
> > drop table if exists changedinfo
> > create temp table changedinfo(colName varchar(100), oldValue
> varchar(4000), newValue varchar(4000));
> > insert into changed infot select 'empName', OLD.empName,
> NEW.empName from employee;
> > insert into changed infot select 'location', OLD.location,
> NEW.location from employee;
> >
> >
> > v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
> newvalue ||')' from changedinfo);
> > perform insert_info(v_message);
> > raise notice '%',v_message;
> > END;
> > $$ LANGUAGE plpgsql;
>
>
> You don't need a temp table for that. You can create the array directly
> from the new and old records:
>
> v_message := array[concat_ws(',', 'empName', old.empname,
> new.empname), concat_ws(',', 'location', old.location, new.location)];
>
> Although nowadays I would probably pass such an "structure" as JSON
> though, not as a comma separated list.
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 12:10 Thomas Kellerer <shammat@gmx.net>
parent: aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Thomas Kellerer @ 2021-11-24 12:10 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
aditya desai schrieb am 24.11.2021 um 08:35:
> Hi Thomas,
> v_message is of composite data type r_log_message and it's definition is as shown below.
>
> postgres=# \d r_log_message;
> Composite type "public.r_log_message"
> Column | Type | Collation | Nullable | Default
> -------------+-------------------------+-----------+----------+---------
> column_name | character varying(30) | | |
> oldvalue | character varying(4000) | | |
> newvalue | character varying(4000) | | |
>
> Regards,
> Aditya.
Sorry, didn't see that.
Then you need to create records of that type in the array:
v_message := array[('empName', old.empname, new.empname)::r_log_message, ('location', old.location, new.location)::r_log_message];
or an array of that type:
v_message := array[('empName', old.empname, new.empname), ('location', old.location, new.location)]::r_log_message[];
Btw: why don't you use `text` instead of varchar(4000).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 12:12 Thomas Kellerer <shammat@gmx.net>
parent: aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Thomas Kellerer @ 2021-11-24 12:12 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
aditya desai schrieb am 24.11.2021 um 08:31:
> H Michael,
> Please see insert_info function below. Also r_log_message is composite data type and it's definition is also given below.
>
> CREATE OR REPLACE FUNCTION insert_info(
> info_array r_log_message[]
> ) RETURNS varchar AS $$
> DECLARE
> info_element r_log_message;
> BEGIN
> FOREACH info_element IN ARRAY info_array
> LOOP
> INSERT INTO testaditya(
> columname,
> oldvalue,
> newvalue
> ) VALUES(
> info_element.column_name,
> info_element.oldvalue,
> info_element.newvalue
> );
> END LOOP;
> RETURN 'OK';
> END;
> $$ LANGUAGE plpgsql;
You don't need a loop for that. This can be done more efficiently using unnest()
INSERT INTO testaditya(columname,oldvalue,newvalue)
select u.*
from unnest(info_array) as u;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Out of memory error
@ 2021-11-24 14:32 aditya desai <admad123@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 11+ messages in thread
From: aditya desai @ 2021-11-24 14:32 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks Thomas! Sorry to say this but ,this was migrated from Oracle to PG
:) and the app team just wants to keep the data type as it is :(
On Wed, Nov 24, 2021 at 5:40 PM Thomas Kellerer <shammat@gmx.net> wrote:
> aditya desai schrieb am 24.11.2021 um 08:35:
> > Hi Thomas,
> > v_message is of composite data type r_log_message and it's definition is
> as shown below.
> >
> > postgres=# \d r_log_message;
> > Composite type "public.r_log_message"
> > Column | Type | Collation | Nullable | Default
> > -------------+-------------------------+-----------+----------+---------
> > column_name | character varying(30) | | |
> > oldvalue | character varying(4000) | | |
> > newvalue | character varying(4000) | | |
> >
> > Regards,
> > Aditya.
>
> Sorry, didn't see that.
>
> Then you need to create records of that type in the array:
>
> v_message := array[('empName', old.empname,
> new.empname)::r_log_message, ('location', old.location,
> new.location)::r_log_message];
>
> or an array of that type:
>
> v_message := array[('empName', old.empname, new.empname), ('location',
> old.location, new.location)]::r_log_message[];
>
>
> Btw: why don't you use `text` instead of varchar(4000).
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2021-11-24 14:32 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-24 05:27 Out of memory error aditya desai <admad123@gmail.com>
2021-11-24 05:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-24 06:25 ` aditya desai <admad123@gmail.com>
2021-11-24 06:31 ` Thomas Kellerer <shammat@gmx.net>
2021-11-24 06:36 ` aditya desai <admad123@gmail.com>
2021-11-24 06:45 ` Michael Lewis <mlewis@entrata.com>
2021-11-24 07:31 ` aditya desai <admad123@gmail.com>
2021-11-24 12:12 ` Thomas Kellerer <shammat@gmx.net>
2021-11-24 07:35 ` aditya desai <admad123@gmail.com>
2021-11-24 12:10 ` Thomas Kellerer <shammat@gmx.net>
2021-11-24 14:32 ` aditya desai <admad123@gmail.com>
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