Replica string comparsion issue

From: Andrey Lizenko <lizenko79(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Replica string comparsion issue
Date: 2018-06-13 20:10:18
Message-ID: CADKuZZD=N6P11z0YXOSJ+JtrJhMOg7nMB6DRmcz_ZURE09_xow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I'm observing strange behaviour on comparing ::text field with string while
quering replica.

Here is the table structure:

=# \dS+ raw.symbols_aggregates
> Table
> "raw.symbols_aggregates"
> Column | Type | Collation | Nullable |
> Default | Storage | Stats target | Description
>
> --------------------+---------+-----------+----------+------------------------------------------------+----------+--------------+-------------
> id | bigint | | not null |
> nextval('symbols_aggregates_id_seq'::regclass) | plain | |
> symbol | text | | not null |
> | extended | |
> id_type | bigint | | not null |
> | plain | |
> id_aggregates_list | integer | | not null |
> | plain | |
> id_regionals_list | integer | | not null |
> | plain | |
> date_started | date | | not null |
> | plain | |
> Indexes:
> "symbols_aggregates_pkey" PRIMARY KEY, btree (id)
> "symbols_aggregates_uniq" UNIQUE CONSTRAINT, btree (symbol, id_type,
> id_aggregates_list, id_regionals_list)
> Foreign-key constraints:
> "symbols_aggregates_id_aggregates_list_fkey" FOREIGN KEY
> (id_aggregates_list) REFERENCES aggregates_list(id)
> "symbols_aggregates_id_regionals_list_fkey" FOREIGN KEY
> (id_regionals_list) REFERENCES regionals_list(id)
> "symbols_aggregates_id_type_fkey" FOREIGN KEY (id_type) REFERENCES
> types_list(id)

Simple query on master works as expected:

> =# select symbol from raw.symbols_aggregates where symbol='RUT';
> symbol
> --------
> RUT
> RUT
> RUT
> (3 rows)

The same query on replica works only after casting to varchar, using
trim(), or something else:
(3 rows expected here)

> =# select symbol from raw.symbols_aggregates where symbol='RUT';
> symbol
> --------
> (0 rows)

with ::varchar(50) result looks correct:

=# select symbol from raw.symbols_aggregates where
> symbol::varchar(50)='RUT';
> symbol
> --------
> RUT
> RUT
> RUT

There is no hidden characters, it looks identical with ::bytea

=# select symbol::bytea, 'RUT'::bytea, (symbol::varchar(50))::bytea from
> raw.symbols_aggregates where symbol::varchar(50)='RUT';
> symbol | bytea | symbol
> ----------+----------+----------
> \x525554 | \x525554 | \x525554
> \x525554 | \x525554 | \x525554
> \x525554 | \x525554 | \x525554

PostgreSQL server version is 10.3. It might be important, that master is
running on Ubuntu 16.04.4 LTS and replica on Solaris 11.3.
PostgreSQL installed from binaries, not from sources. All locales
are en_US.UTF-8

My suggestion it is a kind of collation issue, but I've no idea why :text
is not working in this case.

--
Regards, Andrei Lizenko

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2018-06-13 20:24:16 Re: Replica string comparsion issue
Previous Message Adrian Klaver 2018-06-13 19:09:10 Re: Using GIT to caught delta between database versions