Re: tracking commit timestamps

On 15/11/14 13:36, Simon Riggs wrote:
> On 15 November 2014 04:32, Steve Singer <steve@ssinger.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