[Pgpool-general] working with serial data -- will currval work?

[Bill Eaton]

In the README files, it says that if you want SERIAL values
in a pool to be identical, you have to do table locking. I
understand why this is true -- sooner or later, the
sequences will get out of synch.

In my application, I don't really care if sequences in the
pool get out of synch. However, I do care to preserve
primary/foreign key relationships within a database. If you
are writing to two tables that are related by a foreign key
that depends on a SERIAL value, you can get into trouble if
you're not careful. So I want to be careful. I can think of
two ways to achieve what I want, but I'm preferring the
first way:
  1. use currval:
       INSERT into table1 . . . <table that has the
sequence>
       INSERT into table2 (fkey, field1)
values(currval('myseq'), 'test')
  2. make a stored procedure that does the inserts for me,
and gets the
     value of the the sequence
I'm leaning towards 1, because it seems simpler and easier
to implement. Is there any reason why currval shouldn't
work?

I suppose I should set
  connection_cache=false

This will prevent me from potentially crossing data between
different connections. Is there a big performance hit for
turning caching off?

Bill Eaton