Re: [SQL] two queryes in a single tablescan

On Oct 20, 2007, at 12:19 PM, Andreas Kretschmer wrote:

> Markus Schaber <schabi(at)logix-tt(dot)com> schrieb:
>>> is there any way to get both results in a single query,
>>> eventually through stored procedure?
>>> The retrieved [count(*),A] ; [count(*),B)] data couldnt fit
>>> on a single table, of course.
>>>
>>> The main goal would be to get multiple results while scanning the
>>> table[s] once only
>>> thus getting results in a faster way.
>>
>> PostgreSQL 8.3 contains great improvements in this area, you can
>> simply
>> start the selects from concurrent connections, and the backend will
>> synchronize the scans.
>
> works this right across different transactions? I mean, for
> instance, TX
> a insert rows and TX b insert other rows and both clients (with
> different transactions) starts a seq-scan?

If you are in read-committed mode and both backends start their scans
after the other has made its insert, then yes. Note Markus's point
that both queries must be initiated by concurrent connections. Since
Postgres doesn't have any kind of shared transaction mechanism across
connections then this is inherent.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com