Empty SELECT result at simultaneous calls

Stefan Wild <wilds81@yahoo.de> writes:

I have a problem with my web application and postgres. I have several servlets on a page which results in severeal simultaneous data base calls kind of:

SELECT d.id, d.aa, d.ab, ts.ac, d.ad, d.af, d.ag, d.ah, d.ai, d.aj, d.ak, d.al, d.am, d.an, d.ao, d.ap, d.ar, d.as, d.at, d.au, d.av, d.ax, d.ay, d.az, d.ba, d.bb, d.bc FROM c_depots d INNER JOIN c_aa ts ON d.bd_id=ts.id INNER JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE d.id=13

Even though I have "d.id 13" the SELECT result is empty.

Um, well, that's not obviously wrong. If that d row doesn't have join
partners in all those other tables, you won't get anything out of the
inner joins. Maybe you want to left-join, instead?

regards, tom lane

Hello,

as already stated: "When I'm working with delays in the servlets, everything works fine." the same selects on the same id work fine if delayd. So the data should not be the problem.

regards, Stefan Wild

--- Tom Lane <tgl@sss.pgh.pa.us> schrieb am Mi, 8.9.2010:

Excerpts from Stefan Wild's message of mié sep 08 11:40:25 -0400 2010:

Hello,

as already stated: "When I'm working with delays in the servlets, everything works fine." the same selects on the same id work fine if delayd. So the data should not be the problem.

So why are you blaming Postgres?

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Excerpts from Stefan Wild's message
of mié sep 08 11:40:25 -0400 2010:

Hello,

as already stated: "When I'm working with delays in

the servlets, everything works fine." the same selects on
the same id work fine if delayd. So the data should not be
the problem.

So why are you blaming Postgres?

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development,
24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In my opinion this is a performance problem (HW + SW). So my question is: how can I improve the situation (beside using the delays) and I'm not just blaming postgres. Or do you assume other root causes?

On 8/09/2010 11:40 PM, Stefan Wild wrote:

Hello,

as already stated: "When I'm working with delays in the servlets, everything works fine." the same selects on the same id work fine if delayd. So the data should not be the problem.

Is this freshly inserted data you're having issues with?

If so, I suspect you're running into issues where the data has been
written in another transaction but that transaction has not yet
committed. The data is **NOT** visible to other transactions until the
writing transaction commits.

That's by design. PostgreSQL does not have a "READ UNCOMMITTED" mode
that would permit data that's been written but not yet committed to be
seen by other transactions.

If that's what's biting you, you need to adjust your code so it only
expects to find data in the database that's actually been committed
(maybe by adding appropriate locking/synchronization/queuing), or you
need to use the same connection for reading and writing the uncommitted
data.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Is this freshly inserted data you're having issues with?

If so, I suspect you're running into issues where the data
has been written in another transaction but that transaction
has not yet committed. The data is **NOT** visible to other
transactions until the writing transaction commits.

That's by design. PostgreSQL does not have a "READ
UNCOMMITTED" mode that would permit data that's been written
but not yet committed to be seen by other transactions.

If that's what's biting you, you need to adjust your code
so it only expects to find data in the database that's
actually been committed (maybe by adding appropriate
locking/synchronization/queuing), or you need to use the
same connection for reading and writing the uncommitted
data.

-- Craig Ringer

Thanks for the good Idea! Unfortunately it is old data (already inserted since some days) so this cannot be the reason...

On 09/10/2010 03:33 AM, Stefan Wild wrote:

Thanks for the good Idea! Unfortunately it is old data (already inserted since some days) so this cannot be the reason...

Is there any chance you can provide a self-contained example that
includes both a populated database and the complete, compilable servlet
code required to run it? If you can post a SQL dump of a test database
that demonstrates the problem, and some Java sources that can be
compiled to to a deployable .war, it'll be a lot easier to see what's
going on.

As for the old data: are you sure the inserting transactions have
committed? Nothing stops them from being open for hours or days.

If it's not that, my next best bet would be a concurrency bug in your
code. That's not a slur on your programming skill; I'd assume the same
thing if it was my code, because I know that bugs in released, tested
PostgreSQL code are uncommon and bugs in my in-progress code are very
common. In your case you're using a shared "DbManager" (the source code
for which you have not supplied) and you haven't shown how you get from
there to query execution and result processing. You also haven't shown
how your code concludes that the results are empty.

PostgreSQL's JDBC driver is supposed to provide a fair degree of
concurrency management, making sure that only one thread may use a given
connection at a time. That only protects the driver and its
connection(s) though; it can't protect your code. If you do out-of-spec
things involving sharing resultsets, for example, you will have problems.

--
Craig Ringer

Hey Stefan,

The sounds like you have a field "id" in you "c_transactions" without
default value (which usually should be nextval('some_sequence'::regclass).
Do you create a sequence for "c_transactions"."id" ?

Hi Dmitriy,

yes it's right, the id column does not have a default value and it shouldn't also. What do you mean with "Do you create a sequence for "c_transactions"."id" ?" ? When values are inserted into the table, I find out in my code the next higher value for the id and insert this with the data.

Thanks, Stefan

Ok I found the solution. I have to use the UPDATE command and not the INSERT:

UPDATE c_transactions SET timestamp = entrytimestamp

and than:

UPDATE c_transactions SET timestamp = exittimestamp WHERE exittimestamp IS NOT NULL

Hey Stefan,

For surrogate keys there is no reason to calculate values manually.
You should use sequences instead. Please, see
http://www.postgresql.org/docs/8.3/static/sql-createsequence.html
You may also use a SERIAL data type which creates a sequence
for you automatically upon its creation. Please, see
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL
Since you execute INSERT command without specifying an "id" column,
and there is no default value for it, then the default is NULL. So, you need
to define a default and the best solution to define it as next value of
sequence.

--
Regards,
Dmitriy