Re: Two 'identical' DB's not acting identically - Mailing list pgsql-general

Shridhar Daithankar wrote:
>
> If the databases were created with exactly same schema, even those should not
> differ. But that does not look like cause of your problem.

Really? So this means I might also have another problem?

> I suggest you try running some of your subqueries by hand both of these and
> see which of these return more than one row. I hope that helps.

Here is the output from running your three test queries on both dbs. The
last two return more than one row on both machines, and the last one
returns twice the number of error messages on the test db than on the
live db.

I find it strange that the last query produces 1 error message on the
live db but 2 on the test db ... I can't see any reason for this.


> select id from del_methods where dsc='YAMATO';

TAL=# select id from del_methods where dsc='YAMATO';
  id
----
   1
(1 row)


TEST=# select id from del_methods where dsc='YAMATO';
  id
----
   1
(1 row)


> select limit_id from rel_del_limits_wards where ward_id in (select
> id from wards where wards.ward=invoices.ward)


TAL=# select limit_id from rel_del_limits_wards where ward_id in (select
TAL(# id from wards where wards.ward=invoices.ward);
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
  limit_id
----------
         1
         1
         1
         1
(4 rows)

TEST=# select limit_id from rel_del_limits_wards where ward_id in (select
TEST(# id from wards where wards.ward=invoices.ward);
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
  limit_id
----------
         1
         1
         1
         1
(4 rows)


> select(next_day::text || 'd')::interval from delivery_limit_types where
> id=(select limit_id from rel_del_limits_wards where ward_id in (select
> id from wards where wards.ward=invoices.ward))

TAL=# select(next_day::text || 'd')::interval from delivery_limit_types
where
TAL-# id=(select limit_id from rel_del_limits_wards where ward_id in
(select
TAL(# id from wards where wards.ward=invoices.ward));
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
ERROR:  More than one tuple returned by a subselect used as an expression.

TEST=# select(next_day::text || 'd')::interval from delivery_limit_types
where
TEST-# id=(select limit_id from rel_del_limits_wards where ward_id in
(select
TEST(# id from wards where wards.ward=invoices.ward));
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
ERROR:  More than one tuple returned by a subselect used as an expression.
NOTICE:  Adding missing FROM-clause entry in subquery for table "invoices"
ERROR:  More than one tuple returned by a subselect used as an expression.