Обсуждение: Empty SELECT result at simultaneous calls

Hello guys,

I have a problem with my web application and postgres. I have several servlets on a page which results in severeal
simultaneousdata 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_depotscx 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. When I'm working with delays in the servlets, everything works
fine.The calling instance itself is realized as single instance: 

    public static DbManager getInstance() {
        if(instance == null) {
            instance = new DbManager();
        }
        return instance;
    }

So I'm wondering why there are such problems and how can I resolve them?

My posgres version is (still) 8.2.

Thanks.

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
simultaneousdata 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_depotscx 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
idwork 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:

> Von: Tom Lane <tgl@sss.pgh.pa.us>
> Betreff: Re: [GENERAL] Empty SELECT result at simultaneous calls
> An: "Stefan Wild" <wilds81@yahoo.de>
> CC: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Datum: Mittwoch, 8. September, 2010 01:27 Uhr
> 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
>

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
sameid 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
thedelays) 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
sameid 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
asequence for "c_transactions"."id" ?" ? When values are inserted into the table, I find out in my code the next higher
valuefor the id and insert this with the data. 

Thanks, Stefan

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