Re: tracking commit timestamps

On 15/11/14 13:36, Simon Riggs wrote:
> On 15 November 2014 04:32, Steve Singer <steve(at)ssinger(dot)info> wrote:
>
>> The use cases I'm talking about aren't really replication related. Often I
>> have come across systems that want to do something such as 'select * from
>> orders where X > the_last_row_I_saw order by X' and then do further
>> processing on the order.
>
> Yes, existing facilities provide mechanisms for different types of
> application change queues.
>
> If you want to write a processing queue in SQL, that isn't the best
> way. You'll need some way to keep track of whether or not its been
> successfully processed. That's either a column in the table, or a
> column in a queue table maintained by triggers, with the row write
> locked on read. You can then have multiple readers from this queue
> using the new SKIP LOCKED feature, which was specifically designed to
> facilitate that.
>
> Logical decoding was intended for much more than just replication. It
> provides commit order access to changed data in a form that is both
> usable and efficient for high volume applicatiion needs.
>
> I don't see any reason to add LSN into a SLRU updated at commit to
> support those application needs.
>

I am still on the fence about the LSN issue, I don't mind it from code
perspective, it's already written anyway, but I am not sure if we really
want it in the SLRU as Simon says.

Mainly because of three things:
One, this patch is not really feature patch, as you can do most of what
it does via tables already, but more a performance improvement and we
should try to make it perform as good as possible then, adding more
things does not really improve performance (according to my benchmarks
the performance difference with/without LSN is under 1% so it's not
terrible, but it's there), not to mention additional disk space.

Two, the LSN use-cases seem to still be only theoretical to me, while
the timestamp use-case has been production problem for at least a decade.

Three, even if we add LSN, I am still not convinced that the use-cases
presented here wouldn't be better served by putting that info into
actual table instead of SLRU - as people want to use it as filter in
WHERE clause, somebody mentioned exporting to different db, etc.

Maybe we need better explanation of the LSN use-case(s) to understand
why it should be stored here and why the other solutions are
significantly worse.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services