[pgpool-general: 2493] Re: doubts concerning pgpool's configuration: black_function_lists and delay_threshold

[Tatsuo Ishii]

> Hi, first i really appreaciate the work you have been taking to develop pgpool.
> 
> Im using pgpool 3.1.3, and 2 backends in Master -> Slave Mode. It is
> working greate, but ofcourse im worried about WRITE queries being sent
> to the slave, so to avoid it im thinking in adding a large list of
> function names to the black_function_lists.
> 
> I shall say also that this Master -> Slave Mode is running Streaming
> Replication feature of postgresql 9.1.
> 
> I run a query to list all volatile function names:
> select proname from pg_proc where provolatile = 'v';
> 
> the above query lists all volatile triggers, and functions in the
> database, including those in pg_catalog. the total amount of functions
> is round 500.
> 
> i know that i can make use of regex to make the list shorter, also it
> is good practice to make a standard when naming functions, like all
> functions that do WRITES should be preceeded with a "write_" prefix or
> something like that.
> 
> My question are:
> 1) is this the right way to go to enforce WRITE queries being sent to
> the Master ONLY?

No. A function being volatile does not neccessarily mean it is a
writing function (obvious example is timeofday). So a
black_function_lists created like this will include a large number of
false positives. If you are not concerned about performance, it's
ok. But I will not recommend that way...

An interesting question is, whether ALL writing functions has the
property "volatile". I think the answer *should* be yes, but
considering that the property is given by human and there's no
systematic way to verify it, I tend not to trust it.

> 2) is there any kind of limit of function names on the
> black_function_lists? is there any problem seting black_function_lists
> to like 10 thousand function names?

No. Since the memory used for black_function_lists is dynamically
allocated using malloc(), there's no theoretical limit.

> 3) What should be a reasonable value of delay_threshold in
> pgpool.conf? i've set it to 5 bytes..

Depends on the application you are using. If it's really sensible for
replication delay, delay_threshold should be as small as possible. If
it's your case, 5 bytes would be a good starter.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp