Polling projections showing idle in transaction in PostgreSQL

[lifinsky]

Hi!
We have a question regarding the behavior of polling projections and database transactions.

We observe that each polling projection appears in PostgreSQL (pg_stat_activity) with state: idle in transaction between polling iterations.

Example:

pid=795   state=idle in transaction   query:
SELECT * FROM "_0d40978da2e32dab9b38fa91af861016c7499b28"
WHERE no >= $1
ORDER BY no ASC
LIMIT $2;

pid=1977  state=idle in transaction   query:
SELECT * FROM "_06972625743d9ae5c325835ad183c1e9f2d5b037"
WHERE no >= $1
ORDER BY no ASC
LIMIT $2;

So it looks like:

• a transaction is opened,
• a SELECT is executed,
• and then the connection stays idle in transaction until the next polling cycle.

Is this the expected behavior for polling projections in Ecotone,
or should transactions normally be closed between polling iterations?

We just want to understand whether this is by design or if we might be misinterpreting what we see.
Additionally, could having polling projections in idle in transaction state for a longer time affect VACUUM or autovacuum behavior in PostgreSQL?

Thanks in advance 🙏

[lifinsky]

@dgafka hope it’s okay to tag you here 🙏
We have a question about polling projections and their transaction lifecycle and would really appreciate your input if you have a moment.

[dgafka]

I do think that's flaw in the design, because Prooph Projections never release the control and transaction are started before that.
This as a result may create situation you've described.

That shouldn't be like this, therefore some to cover new with projecting system, as we are no longer improving integration with Prooph (it's meant to be dropped in Ecotone 2.0)

[lifinsky]

@dgafka Is it possible to disable deduplication for a polling projection in Ecotone? We would like to know if this is configurable per projection or if deduplication is always enforced by design. Additionally, from our perspective, deduplication for polling projections seems unnecessary. Since polling projections already read events sequentially from the event store and control their own positions, it feels like deduplication adds extra complexity without much benefit in this case.

19:53:08 INFO      [ecotone] Message with id `e5c217e9-eee0-4624-89b9-3f2e05332c31` received from Message Channel `unknown`
[
  "message_id" => "e5c217e9-eee0-4624-89b9-3f2e05332c31",
  "correlation_id" => "89946ee1-a1dd-4933-abca-63bb1bcaa0e9",
  "parent_id" => "f3cfccd2-a0b2-4167-926a-a363cb20a087"
]
19:53:08 INFO      [ecotone] Database Transaction started
[
  "message_id" => "e5c217e9-eee0-4624-89b9-3f2e05332c31",
  "correlation_id" => "89946ee1-a1dd-4933-abca-63bb1bcaa0e9",
  "parent_id" => "f3cfccd2-a0b2-4167-926a-a363cb20a087"
]
19:53:09 INFO      [ecotone] Message was stored in deduplication table.
[
  "message_id" => "e5c217e9-eee0-4624-89b9-3f2e05332c31",
  "consumer_endpoint_id" => "projection_name",
  "routing_slip" => ""
]
19:53:09 INFO      [ecotone] Flushed and cleared doctrine object managers
[
  "message_id" => "e5c217e9-eee0-4624-89b9-3f2e05332c31",
  "correlation_id" => "89946ee1-a1dd-4933-abca-63bb1bcaa0e9",
  "parent_id" => "f3cfccd2-a0b2-4167-926a-a363cb20a087"
]
19:53:09 INFO      [ecotone] Database Transaction committed
[
  "message_id" => "e5c217e9-eee0-4624-89b9-3f2e05332c31",
  "correlation_id" => "89946ee1-a1dd-4933-abca-63bb1bcaa0e9",
  "parent_id" => "f3cfccd2-a0b2-4167-926a-a363cb20a087"
]

[dgafka]

It can be done for other features, but it wasn't implemented for deduplication

[lifinsky]

It can be done for other features, but it wasn't implemented for deduplication

Thank you for the clarification.

For my use case, it is very important that deduplication is not used for polling projections when a projection does not rely on a message queue and works by polling the event stream directly. Ideally, this should be optional or disabled by default for polling projections.