Re: Synchronized scans

Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
>> I don't think anyone can reasonably expect to get the same ordering when
>> the same query issued twice in general, but within the same transaction
>> it wouldn't be that unreasonable. If we care about that, we could keep
>> track of starting locations per transaction, only do the synchronization
>> on the first scan in a transaction, and start subsequent scans from the
>> same page as the first one.
>
> I think the real problem here is that the first scan is leaving state
> behind that changes the behavior of the next scan. Which can have no
> positive benefit, since obviously the first scan is not still
> proceeding; the best you can hope for is that it's a no-op and the worst
> case is that it actively pessimizes things. Why doesn't the patch
> remove the shmem entry at scan termination?

Because there's no reason why it should, and it would require a lot more
bookkeeping. There can be many scanners on the same table, so we'd need
to implement some kind of reference counting, which means having to
reliably decrement the counter when a scan terminates.

In any case if there actually is a concurrent scan, you'd still see
different ordering. Removing the entry when a scan is over would just
make it harder to trigger.

>> I think the warning on LIMIT without ORDER BY is a good idea, regardless
>> of the synchronized scans patch.
>
> I seriously doubt that can be done in any way that doesn't both warn
> about perfectly-safe cases and fail to warn about other unsafe ones.
> Furthermore, it's not uncommon for people to do "SELECT * ... LIMIT 1"
> just to remind themselves of column names or whatever. Do we really
> want the thing to be so nannyish?

It really depends on how many false negatives and positives it gives. If
too many, it's just annoying, but if it's reasonably accurate I think it
would be OK to remind people running queries like that.

> I was envisioning simply a stronger warning in the SELECT reference page ...

I doubt the people that would be bitten by this read the SELECT
reference page.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com