[Pgpool-general] INSERTs in FUCTIONS are not replicating

[Tatsuo Ishii]

> I'm new to pgpool. I currently have pgpool II 2.2.2 installed. the
> underlying PostgreSQL database is 8.4.
> i'm not doing parallel queries, but i am using replication and pooling. i've
> successfully tested it up to 3 nodes, and i am able to detach, recover, and
> attach nodes with no problems.
> 
> when i issue inserts, updates, deletes, selects - i get the expected results
> on all attached nodes.
> 
> however when i do inserts from within functions, the insert only occurs on
> the master node.

Since pgpool does not analyze codes inside a function, it cannot know
if you actually issue INSERT or not. A workaround for this is, add a
bogus comment to the SELECT statement:

/* NO LOAD BALANCE */SELECT your_function();

This will let pgpool beleive it's not a load balancable SELECT query.

> the table has a SERIAL primary key, and i expect that this can be an issue
> with synchronizing, but shouldn't the insert at least happen on all nodes?
> 
> i have also tried adding things like:
> LOCK TABLE table_name IN SHARE ROW EXCLUSIVE MODE;
> INSERT....
> 
> to no avail.

Can you tell me more details on what happend?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> is this a known issue, or by design? or am i screwing up?
> 
> any help would be appreciated
> 
> -janos