[pgpool-general: 5513] Re: schema name in black_function_list pgpool version 3.4.3
Madhusudan, Dhanya
dhanya.madhusudan at hpe.com
Fri May 19 17:49:17 JST 2017
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