Thread: jdbc high memory consumption for RAISE NOTICE output using function-call in Java

jdbc high memory consumption for RAISE NOTICE output using function-call in Java

From

Georg Brünsing

Date:

08 December 2015, 14:43:58

The function creates ~16MB output. The VM needs ~200MB over statement.executeQuery(...).

CREATE OR REPLACE FUNCTION Test_Grosse_Logdatei() returns INTEGER AS
$$
DECLARE
BEGIN
    FOR i IN 1..150000 LOOP
        RAISE notice '123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 ';
    END LOOP;

    RETURN 0;
END;
$$
language plpgsql;

How can we reduce this?

Georg Brünsing

postgresql-9.4-1206-jdbc4.jar + postgresql-9.1-902.jdbc4.jar
psql (PostgreSQL) 9.3.1
Java(TM) SE Runtime Environment (build 1.8.0_66-b18) + Java(TM) SE Runtime Environment (build 1.7.0_75-b13) + Java(TM) SE Runtime Environment (build pxa6460sr7-20091215_02(SR7))
JDK 6-8
Windows/Linux

Re: jdbc high memory consumption for RAISE NOTICE output using function-call in Java

From

Daniel Blanch Bataller

Date:

08 December 2015, 15:02:25

Hi Georg:

Could you elaborate a bit more why do you want to RAISE NOTICE 150000 times?

Regards

- Daniel.

El 8 dic 2015, a las 11:37, Georg Brünsing <georg.bruensing@clickware.de> escribió:

The function creates ~16MB output. The VM needs ~200MB over statement.executeQuery(...).

CREATE OR REPLACE FUNCTION Test_Grosse_Logdatei() returns INTEGER AS
$$
DECLARE
BEGIN
    FOR i IN 1..150000 LOOP
        RAISE notice '123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 ';
    END LOOP;

    RETURN 0;
END;
$$
language plpgsql;

How can we reduce this?

Georg Brünsing

postgresql-9.4-1206-jdbc4.jar + postgresql-9.1-902.jdbc4.jar
psql (PostgreSQL) 9.3.1
Java(TM) SE Runtime Environment (build 1.8.0_66-b18) + Java(TM) SE Runtime Environment (build 1.7.0_75-b13) + Java(TM) SE Runtime Environment (build pxa6460sr7-20091215_02(SR7))
JDK 6-8
Windows/Linux

Re: jdbc high memory consumption for RAISE NOTICE output using function-call in Java

From

Vladimir Sitnikov

Date:

08 December 2015, 16:48:41

>How can we reduce this?

It would be better not to abuse "raise ...".

However, if you cannot alter the software to remove that "raise", use
client_min_messages=WARNING:
http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html#GUC-CLIENT-MIN-MESSAGES

Regards,
Vladimir Sitnikov