Thread: Out of memory error

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

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.

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.

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.

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).