Re: Synchronized scans

Tom Lane wrote:
> Heikki Linnakangas  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