[pgpool-general: 5515] Re: schema name in black_function_list pgpool version 3.4.3
Madhusudan, Dhanya
dhanya.madhusudan at hpe.com
Fri May 19 19:35:10 JST 2017
You are right Tatsuo, my bad.
Thanks for the suggestion.
Regards,
Dhanya
-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
Sent: Friday, May 19, 2017 2:40 PM
To: Madhusudan, Dhanya <dhanya.madhusudan at hpe.com>
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 5447] schema name in black_function_list pgpool version 3.4.3
Dhanya,
I'm confused. Trigger functions are used in backend and Pgpool-II cannot see the function called. Or you just call these functions using SELECT?(then they are not trigger functions by definition). If so, use /*NO LOAD BALANCE*/ comment is the way to go.
http://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-SETTINGS
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> Hi Tatsuo,
>
> We use these functions to create partitions as the load increases.
> Is there any config that will avoid such trigger functions being routed to the standby?
> Do you have any other suggestions?
>
> Regards,
> Dhanya
>
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
> Sent: Monday, May 15, 2017 6:32 AM
> To: Madhusudan, Dhanya <dhanya.madhusudan at hpe.com>
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 5447] schema name in black_function_list
> pgpool version 3.4.3
>
>> Thanks for confirming Tatsuo.
>> We have a trigger function iot.request_part_trig_func(), which in-turn uses the partman functions.
>> I tried with both 'request_part_trig_func' and 'iot.request_part_trig_func' in the black_function_list, but it still complains about the partman functions.
>> Any suggestions? Am I adding the function name correctly? Are trigger functions not supported?
>
> No. Pgpool-II only recognizes functions explicitly used in a query.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
>> Regards,
>> Dhanya
>>
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>> Sent: Friday, May 12, 2017 2:29 PM
>> To: Madhusudan, Dhanya <dhanya.madhusudan at hpe.com>
>> Cc: pgpool-general at pgpool.net
>> Subject: Re: [pgpool-general: 5447] schema name in
>> black_function_list pgpool version 3.4.3
>>
>> Unfortunately no. "partman.* will be not recognized as a schema qualification.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>>> Is it possible to black_list all the functions under a schema?
>>> For eg., We have update functions under the partman schema. To avoid them from being load-balanced, I added the below config, but I still get the below error in logs:
>>> black_function_list = 'partman.*,currval, iot.contentinstance, ........'
>>>
>>> 2017-05-10 02:06:10 UTC edbrepuser postgres 15.213.55.240(39504)LOG:
>>> disconnection: session time: 0:00:00.005 user=edbrepuser
>>> database=postgres host=15.213.55.240 port=39504
>>> 2017-05-10 02:06:11 UTC enterprisedb hp_iot 15.213.55.240(38428)ERROR:
>>> cannot execute INSERT in a read-only transaction
>>> 2017-05-10 02:06:11 UTC enterprisedb hp_iot 15.213.55.240(38428)CONTEXT: SQL statement "WITH partition_data AS (
>>> DELETE FROM ONLY iot.contentinstance WHERE container_serial_id >= 3000 AND container_serial_id < 4000 RETURNING *)
>>> INSERT INTO iot.contentinstance_p3000 SELECT * FROM partition_data"
>>> PL/pgSQL function
>>> partman.partition_data_id(text,integer,integer,numeric,text) line
>>> 104 at EXECUTE
>>> 2017-05-10 02:06:11 UTC enterprisedb hp_iot
>>> 15.213.55.240(38428)STATEMENT: select
>>> partman.partition_data_id('iot.contentinstance', 100) as r
>>> 2017-05-10 02:06:11 UTC enterprisedb hp_iot 15.213.55.240(38428)ERROR: cannot execute CREATE FUNCTION in a read-only transaction
>>> CONTEXT: SQL statement "CREATE OR REPLACE FUNCTION iot.request_part_trig_func() RETURNS trigger LANGUAGE plpgsql AS $t$
>>> DECLARE
>>> v_count int;
>>> v_partition_name text;
>>> v_partition_timestamp timestamptz;
>>> BEGIN
>>> IF TG_OP = 'INSERT' THEN
>>> v_partition_timestamp := date_trunc('day', NEW.originating_timestamp);
>>> IF NEW.originating_timestamp >= '2017-05-10 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-11 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_10 VALUES (NEW.*);
>>> ELSIF NEW.originating_timestamp >= '2017-05-09 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-10 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_09 VALUES (NEW.*);
>>> ELSIF NEW.originating_timestamp >= '2017-05-11 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-12 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_11 VALUES (NEW.*);
>>> ELSIF NEW.originating_timestamp >= '2017-05-08 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-09 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_08 VALUES (NEW.*);
>>> ELSIF NEW.originating_timestamp >= '2017-05-12 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-13 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_12 VALUES (NEW.*);
>>> ELSIF NEW.originating_timestamp >= '2017-05-13 00:00:00+05:30' AND NEW.originating_timestamp < '2017-05-14 00:00:00+05:30' THEN
>>> INSERT INTO iot.request_p2017_05_13 VALUES (NEW.*);
>>> ELSE
>>> v_partition_name := partman.check_name_length('request', to_char(v_partition_timestamp, 'YYYY_MM_DD'), TRUE);
>>> SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = 'iot'::name AND tablename = v_partition_name::name;
>>> IF v_count > 0 THEN
>>> EXECUTE format('INSERT INTO %I.%I VALUES($1.*)', 'iot', v_partition_name) USING NEW;
>>> ELSE
>>> RETURN NEW;
>>> END IF;
>>> END IF;
>>> END IF;
>>> RETURN NULL;
>>> EXCEPTION WHEN OTHERS THEN
>>> RAISE WARNING 'pg_partman insert into child table failed, row inserted into parent (%.%). ERROR: %', TG_TABLE_SCHEMA, TG_TABLE_NAME, COALESCE(SQLERRM, 'unknown');
>>> RETURN NEW;
>>> END $t$;"
>>> PL/pgSQL function partman.create_function_time(text,bigint) line 269 at EXECUTE
>>> SQL statement "SELECT partman.create_function_time(p_parent_table)"
>>> PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text) line 226 at PERFORM
>>> DETAIL:
>>> HINT:
>>> 2017-05-10 02:06:11 UTC enterprisedb hp_iot 15.213.55.240(38428)CONTEXT: SQL statement "SELECT partman.create_function_time(p_parent_table)"
>>> PL/pgSQL function
>>> partman.partition_data_time(text,integer,interval,numeric,text) line
>>> 226 at PERFORM
>>>
>>>
>>> Regards,
>>> Dhanya
More information about the pgpool-general
mailing list