From: | aditya desai <admad123(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Out of memory error |
Date: | 2021-11-24 06:25:50 |
Message-ID: | CAN0SRDGZeRyxcB-LWKjNQSi0O3tcK_xKPZdhaLvj_8+sbNPoBA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> aditya desai <admad123(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2021-11-24 06:31:08 | Re: Out of memory error |
Previous Message | Tom Lane | 2021-11-24 05:52:38 | Re: Out of memory error |