Thread: Two 'identical' DB's not acting identically

I'm trying to set up a test database using data from a live one.

However I seem to have have somehow setup the test DB differently than
the live one because some queries don't work as they should. I think it
has something to do with localization but can't be sure ...

The live database is as follows:

    Name    |  Owner   | Encoding
-----------+----------+----------
  TAL       | postgres | EUC_JP
  template0 | postgres | EUC_JP
  template1 | postgres | EUC_JP
(3 rows)

The test DB is:

    Name    |  Owner   | Encoding
-----------+----------+----------
  TEST      | postgres | EUC_JP
  template0 | postgres | EUC_JP
  template1 | postgres | EUC_JP
(3 rows)


However this query works on the live DB but not on the test DB (data on
the two DBs is identical). (I tried to find which part of the query was
causing the problems but couldn't ... my SQL debugging skills are still
lacking)

TEST=# select count(distinct invoice_id) from invoices, invoice_li where
id=invoice_id and dist_invoice is NULL AND not payment_rcvd and NOT
invoices.cancelled and NOT invoice_li.cancelled and NOT shipped AND
payment_type=2 AND delivery_method=(select id from del_methods where
dsc='YAMATO') AND max(req_del_date1, req_del_date2, req_del_date3) -
(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))) >= current_timestamp;
ERROR:  More than one tuple returned by a subselect used as an expression.
ERROR:  More than one tuple returned by a subselect used as an expression.


TAL=# select count(distinct invoice_id) from invoices, invoice_li where
id=invoice_id and dist_invoice is NULL AND not payment_rcvd and NOT
invoices.cancelled and NOT invoice_li.cancelled and NOT shipped AND
payment_type=2 AND delivery_method=(select id from del_methods where
dsc='YAMATO') AND max(req_del_date1, req_del_date2, req_del_date3) -
(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))) >= current_timestamp;
  count
-------
      0
(1 row)


How can I find out what is different betweeb my live db and TEST db? I
had a look at the postgres.conf files but those were identical.


Thanks,

Jean-Christian Imbeault

On Tuesday 15 April 2003 15:13, you wrote:
> How can I find out what is different betweeb my live db and TEST db? I
> had a look at the postgres.conf files but those were identical.

Do a pg_dump -s on both the databases to produce their schemas. Then diff
those two schemas to find out anything is missing.

HTH

 Shridhar

Shridhar Daithankar wrote:
>
> Do a pg_dump -s on both the databases to produce their schemas. Then diff
> those two schemas to find out anything is missing.

I forgot to mention that the test db was created this way:

- on the live db do a pg_dump TAL > dump
- copy dump file to test database server
- on the test db do a createdb TEST; psql TEST < dump

This way the two dbs will have the schemas no?

Thanks,

Jean-Christian Imbeault

Shridhar Daithankar wrote:
>
> Do a pg_dump -s on both the databases to produce their schemas. Then diff
> those two schemas to find out anything is missing.

I did as you suggested just in case and found only TOC entry differneces
such as:

 > -- TOC entry 299 (OID 3953732)
3219c3219
< -- TOC entry 300 (OID 457305)

Jean-Christian Imbeault

On Tuesday 15 April 2003 15:29, you wrote:
> Shridhar Daithankar wrote:
> > Do a pg_dump -s on both the databases to produce their schemas. Then diff
> > those two schemas to find out anything is missing.
>
> I did as you suggested just in case and found only TOC entry differneces
>
> such as:
>  > -- TOC entry 299 (OID 3953732)
>
> 3219c3219
> < -- TOC entry 300 (OID 457305)

If the databases were created with exactly same schema, even those should not
differ. But that does not look like cause of your 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.

select id from del_methods where dsc='YAMATO';

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

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))

 Shridhar

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.

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I forgot to mention that the test db was created this way:
> - on the live db do a pg_dump TAL > dump
> - copy dump file to test database server
> - on the test db do a createdb TEST; psql TEST < dump
> This way the two dbs will have the schemas no?

You would think they'd be the same ...

Are both servers the same version of Postgres?  (And which version would
that be, exactly?)  Does EXPLAIN of the problem query show the same
results on both?

            regards, tom lane

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> 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.

You know, this could be a matter of a data discrepancy, like you somehow
loaded two copies of data into some table.  It'd be worth doing a
"select count(*)" on each table in each database to see if they agree.

            regards, tom lane

Tom Lane wrote:
>
> You know, this could be a matter of a data discrepancy, like you somehow
> loaded two copies of data into some table.  It'd be worth doing a
> "select count(*)" on each table in each database to see if they agree.

That's exactly what it looks like. I had this error message once before
on the live DB and it was caused by inserting data twice into a table.

I compared the counts of all the tables and they are identical. I
checked the counts of the the tables involved in the query that gives an
error (just to be sure) and they are identical.

The database versions for both are the same (both installed from source,
but the source files where downloaded at different times):

$ psql --version
psql (PostgreSQL) 7.3.2

An explain (after a vacuum full analyze) gave this difference:

$ diff tal.explain test.explain
22,23c22,23
<                                        ->  Materialize
(cost=63.99..63.99 rows=1 width=4)
<                                              ->  Seq Scan on wards
(cost=0.00..63.99 rows=1 width=4)
---
 >                                        ->  Materialize
(cost=63.99..63.99 rows=5 width=4)
 >                                              ->  Seq Scan on wards
(cost=0.00..63.99 rows=5 width=4)


This leaves me to believe that the problem is related to a character
encoding problem? invoices.ward and wards.ward are text fields
containing EUC_JP. Looks like the TEST db cannot properly do a text
comparison on EUC_JP text?

I did some more testing and:

TAL=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE:  Adding missing FROM-clause entry for table "invoices"
   ward  |  ward  |  id
--------+--------+------
  ?????| ?????| 1016
  ?????| ?????| 1023
  ?????| ?????| 1023
  ??????| ??????| 1026
  ?????? | ?????? | 1038
(5 rows)


TEST=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE:  Adding missing FROM-clause entry for table "invoices"
   ward  |  ward  |  id
--------+--------+------
  ?????| ?????| 1016
  ?????| ?????| 1023
  ?????| ?????| 1023
  ??????| ??????| 1026
  ?????? | ?????? | 1038
  ?????| ?????? | 2065
(6 rows)

The last row does *not* contain a match yet it is returned as a matching
row ....

But both databases have the same encoding:

    Name    |  Owner   | Encoding
-----------+----------+----------
  TAL       | postgres | EUC_JP


    Name    |  Owner   | Encoding
-----------+----------+----------
  TEST      | postgres | EUC_JP


Hope some of this helps in figuring out why the two databases are
different ...

Thanks!

Jean-Christian Imbeault