[pgpool-general: 1032] Maybe add currval to default black_function_list?

Philip Hofstetter phofstetter at sensational.ch
Fri Sep 21 04:54:42 JST 2012


Hi,

The application I'm dealing with has multiple occurrences of using an
"insert" followed by a "select currval(...)" to get to the id of the
just inserted row.

To make matters worse, the application very often doesn't do this
inside a transaction.

Now while really bad design, it's fine when working with a single
database and connection exclusive to one application process as
currval() will only return a sequence value if nextval() has been
called in the same connection.

Of course, when you add pgpool to the mix, the first query (insert)
will go to the master and the second query (currval()) will probably
go to the slave where postgres, rightfully so, blows up.

Now what I'm doing is of course hunting those usages of currval in our
application (for a long time we were oblivious to the RETURNING clause
which is *so much* better for this), but as a quick remedy, I have
added currval to "black_function_list" and the more I think about it,
the more I think that it makes sense for that to be there by default
as I see no single usage that's not inside a transaction (where a
previous write would make it go to master only anyways) where
currval() would ever make sense in a pgpool setup.

Hence my suggestion to maybe add currval to black_function_list by default.

Philip


More information about the pgpool-general mailing list