The SQL for checking inventory worked sometimes and not others — turned out two functions in the WHERE clause were fighting each other

Last week I was pulled in to investigate an issue with an inventory batch interface. The feedback was that the same code and the same data would sometimes return results and sometimes not. My first reaction was that connection state wasn't being cleaned up when connections were reused from the pool. Following that lead, I discovered it really was related to "connection state" — just not at the connection pool layer, but in the SQL itself.

That SQL had two functions stuffed into its WHERE clause: f_set_batch records the current batch number to be queried into the session, and f_get_batch reads that value back out to filter. The person who wrote it probably intended to set first, then get, but in practice, this order is completely unreliable.

The environment is KES V009R001C010:

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

Setting up the scenario: one inventory table, two functions

Connect as a regular user, create the table and insert data:

create table app_schema.t_seq_stock (
  stock_id integer primary key,
  batch_no integer not null,
  sku_no varchar(20) not null,
  qty integer not null
);

insert into app_schema.t_seq_stock(stock_id, batch_no, sku_no, qty) values
  (1, 10, 'SKU-A01', 100),
  (2, 10, 'SKU-A02', 50),
  (3, 20, 'SKU-B01', 200),
  (4, 30, 'SKU-C01', 80);

Then create two functions to replicate the interface's "remember state first, then use state to query" pattern: f_set_batch writes a batch number to the session, f_get_batch reads it back:

create or replace function app_schema.f_set_batch(p_batch integer)
returns integer language plpgsql as $$
begin
  perform set_config('app.current_batch', p_batch::text, false);
  return 1;
end;
$$;

create or replace function app_schema.f_get_batch()
returns integer language plpgsql as $$
begin
  return current_setting('app.current_batch', true)::integer;
end;
$$;

The value written by set_config is only valid for the lifetime of the current database connection — it disappears when you switch connections. This characteristic is the core of the pitfall.

Running immediately on a new connection returns an empty set

Open a new connection, simulating the interface just getting a never-used connection from the pool, and run this query directly:

select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where s.batch_no = app_schema.f_get_batch()
  and app_schema.f_set_batch(10) = 1;

0 rows. The person who wrote this SQL probably thought: first use f_set_batch(10) to set the batch to 10, then use f_get_batch() to read that 10 and filter batch_no. But the order is reversed — f_get_batch() is written first and executes first, at which point f_set_batch hasn't been called at all. The variable in the session is empty, so batch_no = empty naturally returns nothing.

In the same connection, compare correct and incorrect order side by side

Without disconnecting, first manually run the "correct order" version:

select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where app_schema.f_set_batch(10) = 1
  and s.batch_no = app_schema.f_get_batch();

Immediately after, in the same connection, reverse the order and run the original "wrong order" version again:

select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where s.batch_no = app_schema.f_get_batch()
  and app_schema.f_set_batch(10) = 1;

The first query returns 2 rows, no problem — f_set_batch executes first, the batch is set to 10, and f_get_batch reads 10. The strange part is the second query — the "wrong order" version that clearly returned 0 rows in a new connection now also returns 2 rows.

This is the most deceptive part of the pitfall. The session variable was already set to 10 after the first query executed, and nothing clears it. The second query, even though f_get_batch() is written first, reads the stale value left over from the previous query, not the state this query itself should have. Getting results doesn't mean the SQL is correct — it just got lucky that someone before it happened to fill in the variable.

Disconnect and reconnect, the "wrong order" immediately shows its true colors

Disconnect this connection, reconnect, and run the same "wrong order" SQL:

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

set search_path to app_schema, public;

select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where s.batch_no = app_schema.f_get_batch()
  and app_schema.f_set_batch(10) = 1;

0 rows again. Exactly the same as the very beginning. This proves the 2 rows in the previous step were a coincidence, not evidence that this SQL itself is reliable — connections in the pool are reused. A connection might be used by request A, leaving state behind, and then immediately used by request B to run this "wrong order" SQL. Whether B gets results is entirely a matter of luck, depending on whether the previous user of this connection happened to set the same batch. This is the real reason the interface "sometimes finds results and sometimes doesn't" — it has nothing to do with data or caching.

The execution plan shows these two conditions are evaluated together

Run the execution plan for the "correct order" SQL:

explain analyze
select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where app_schema.f_set_batch(10) = 1
  and s.batch_no = app_schema.f_get_batch();

Filter: ((f_set_batch(10) = 1) AND (batch_no = f_get_batch())), the two conditions are merged into a single Filter expression and evaluated together, in the same order as written in the SQL. This only shows that the plan generated this time didn't reverse the condition order — it doesn't mean KES guarantees strict left-to-right execution order in all scenarios. SQL is a declarative language, and the order of conditions in a WHERE clause is never something the database promises to uphold. Whether the optimizer has the right to reorder them, and under what circumstances it might, cannot be concluded just from this one execution plan. The truly reliable basis for judgment is the connection experiments in the previous steps, not this execution plan.

The fix: completely separate state setting from querying

Move f_set_batch outside the query. In the application code, explicitly call it once first, then issue a separate read-only query:

select app_schema.f_set_batch(10);

select
  s.stock_id, s.batch_no, s.sku_no, s.qty
from app_schema.t_seq_stock s
where s.batch_no = app_schema.f_get_batch();

2 rows. Regardless of whether this connection was previously used by someone else or has leftover state, splitting these two statements apart makes the result stable. To go further, the more thorough approach is that this batch number shouldn't rely on session variables at all. The interface receives the parameter and can just pass where batch_no = 10 directly. The set_config/current_setting session state approach is itself just a transitional measure — avoid it if possible.

A few notes to jot down

• Don't put functions with side effects — modifying data, modifying session state, writing logs — in the WHERE clause. The criterion is simple: if the function does anything else besides returning a value, it shouldn't appear in WHERE.
• When you genuinely need "process state first, then query," split these two steps into two independent statements in application code. Don't expect the database to guarantee execution order for you.
• The approach to troubleshooting this kind of "sometimes right, sometimes wrong" problem is the route this article took: run repeatedly on the same connection vs. disconnect and reconnect, compare whether results are consistent. This is far more useful than staring at the SQL text and guessing.
• For functions that only read and don't modify any state, in KES you can declare them as IMMUTABLE or STABLE. This gives the optimizer an accurate signal and gives anyone reading the code later a clear hint: this function has no side effects and is safe to use.