Обсуждение: SELECT Query returns empty

hi,

have a look at transaction isolation in docs

/tm

am  Wed, dem 09.07.2008, um 16:32:11 +0800 mailte Bright D.L. folgendes:
> The scenario:
>             There are two separate processes ? one (P1) inserting (not
> updating) data to a table at a high rate (around one record in 10ms) and
> another (P2) selecting the data from the same table for further processing. P1
> and P2 use separate connection to the Database and P1 sends a trigger to P2
> after inserting the data to the PostgreSQL.

What do you mean with 'P1 sends a TRIGGER to P2'? You can't send a
TRIGGER to another process.

>
>
>
> The issue:
>
>             P2 tries to query the data from the table, and the result set is
> empty. I modified the logic to send the trigger only after making sure that P1
> can successfully query the last record it inserted.
>
>
>
> I would like to know why P1 can retrieve the data from the table while P2
> can?t.

Wild guess (maybe i misunderstand you): You insert the Data with P1
within a TRANSACTION and you haven't commited this insert. In this case,
P2 can't see the uncommited data.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Bright D.L. wrote:

> I would like to know why P1 can retrieve the data from the table while
> P2 can't.

At a guess: transactional visibility. P1 will have not yet committed its
transaction, so the data isn't visible to P2 yet. Remember, PostgreSQL
defaults to the READ COMMITTED isolation level and does not offer READ
UNCOMMITTED for those rare situations where you might want it.

By "send a trigger" I'm assuming you mean that you're using RAISE NOTICE
and LISTEN.

If P1 commits, then raises a notice to tell P2 about the changes, P2
should be able to see the changes immediately. Even if P2's transaction
began before it receives the notice it should still see the changes
(because of READ COMMITTED isolation). However, if a function or
statement is running in P2 when P1 commits, the changes made by P1 will
only become visible after that function or statement finishes.

If I'm mistaken in my interpretation and P1 does in fact commit before
notifying P2 of the changes, then there must be something else going on.
Perhaps a bit more detail might help.

--
Craig Ringer

Thank you Craig,

>At a guess: transactional visibility. P1 will have not yet committed
its
>transaction, so the data isn't visible to P2 yet. Remember, PostgreSQL
>defaults to the READ COMMITTED isolation level and does not offer READ
>UNCOMMITTED for those rare situations where you might want it.

P1 did commit its insertion; even it commits its every transaction.

>By "send a trigger" I'm assuming you mean that you're using RAISE
NOTICE
>and LISTEN.

By 'send a trigger' what I meant was, P1 sends a TCP packet to P2 (It is
the preferred IPC in our application) asking it to start querying for
data.

>If P1 commits, then raises a notice to tell P2 about the changes, P2
>should be able to see the changes immediately. Even if P2's transaction
>began before it receives the notice it should still see the changes
>(because of READ COMMITTED isolation). However, if a function or
>statement is running in P2 when P1 commits, the changes made by P1 will
>only become visible after that function or statement finishes.

>If I'm mistaken in my interpretation and P1 does in fact commit before
>notifying P2 of the changes, then there must be something else going
on.
>Perhaps a bit more detail might help.

Processes P1 and P2 are executables developed in VC++. These are the
steps performed by P1 before sending the TCP packet (which acts as a
trigger) to P2.

1) Create an insertion query
2) Execute the query
3) Execute a 'Commit' command
4) Repeat 2 and 3 how many ever times needed
5) 'Select' part of the last 'insert'ed data to verify whether it is
accessible
6) Repeat 6 till the data is available
7) Send a TCP packet to P2 to start its 'Select' query

>> The scenario:
>>             There are two separate processes ? one (P1) inserting
(not
>> updating) data to a table at a high rate (around one record in 10ms)
and
>> another (P2) selecting the data from the same table for further
>>processing. P1
>> and P2 use separate connection to the Database and P1 sends a trigger
to >>P2
>> after inserting the data to the PostgreSQL.

>What do you mean with 'P1 sends a TRIGGER to P2'? You can't send a
>TRIGGER to another process.

By 'send a trigger' what I meant was, P1 sends a TCP packet to P2 (It is
the preferred IPC in our application) asking it to start querying for
data.

>> The issue:
>>
>>  P2 tries to query the data from the table, and the result set is
>> empty. I modified the logic to send the trigger only after making
sure >>that P1
>> can successfully query the last record it inserted.


>> I would like to know why P1 can retrieve the data from the table
while P2
>> can?t.

>Wild guess (maybe i misunderstand you): You insert the Data with P1
>within a TRANSACTION and you haven't commited this insert. In this
case,
>P2 can't see the uncommited data.

P1 did commit its insertion and verified it by successfully querying the
last inserted data, before sending the TCP packet - the trigger - to P2

>hi,

>have a look at transaction isolation in docs

Thanks TM. I checked the isolation level of the DB and it is "read
committed". May be I will change it to 'Serializable' and check whether
that helps.

>/tm

Bright D.L. wrote:
> Processes P1 and P2 are executables developed in VC++. These are the
> steps performed by P1 before sending the TCP packet (which acts as a
> trigger) to P2.
>
> 1) Create an insertion query
> 2) Execute the query
> 3) Execute a 'Commit' command
> 4) Repeat 2 and 3 how many ever times needed
> 5) 'Select' part of the last 'insert'ed data to verify whether it is
> accessible
> 6) Repeat 6 till the data is available
> 7) Send a TCP packet to P2 to start its 'Select' query
>

Is there any chance that P2 is still in transaction from its last query?

Once step 6 completes, can psql see the data?

Does "select * from pg_stat_activity" show anything unexpected?

Are you directly using libpq or some other connection method?

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

>> Processes P1 and P2 are executables developed in VC++. These are the
>> steps performed by P1 before sending the TCP packet (which acts as a
>> trigger) to P2.
>>
>> 1) Create an insertion query
>> 2) Execute the query
>> 3) Execute a 'Commit' command
> 4) Repeat 2 and 3 how many ever times needed
>> 5) 'Select' part of the last 'insert'ed data to verify whether it is
>> accessible
>> 6) Repeat 6 till the data is available

Sorry a typo - step 6 should be 'Repeat 5 till the data is available'

>> 7) Send a TCP packet to P2 to start its 'Select' query
>>

>Is there any chance that P2 is still in transaction from its last
query?

P2 already completed its transaction before proceeding with the next
query

>Once step 6 completes, can psql see the data?

Yes, P1 makes sure and is able to see the data before sending Packet to
P2.

>Does "select * from pg_stat_activity" show anything unexpected?

I have to investigate on that side.

>Are you directly using libpq or some other connection method?

Yes, I am directly using the libpq library.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

"Bright D.L." <dl_bright@star-quest.com> writes:
> P1 did commit its insertion and verified it by successfully querying the
> last inserted data, before sending the TCP packet - the trigger - to P2

The fact that P1 can see data it inserted is no proof at all that it's
committed its transaction.  I think you've somehow messed up the commit
step.

            regards, tom lane

Bright D.L. wrote:
> >Once step 6 completes, can psql see the data?
>
> Yes, P1 makes sure and is able to see the data before sending Packet to
> P2.
>

Not P1, but psql.  If you can see the data from psql, then your problem
has to be in P2.  If you can't see the data from psql, then P1 is the
problem.


> >Does "select * from pg_stat_activity" show anything unexpected?
>
> I have to investigate on that side.
>

If it's as everyone thinks, then one of them will probably be idle in
transaction when queried from psql between step 6 and 7.  Running it
from P1 or P2 will just tell you that its running "select * from
pg_stat_activity", not if it's still in transaction.

You could try setting log_statement='all' and check that the logs look
like you would expect.  Especially the commits from both sides.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au