Re: Change from 9.6 to 11? - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Change from 9.6 to 11?
Date
Msg-id 37b96fd6-c5f5-e1d3-6284-6f1521a02638@aklaver.com
Whole thread Raw
In response to Change from 9.6 to 11?  (Chuck Martin <clmartin@theombudsman.com>)
Responses Re: Change from 9.6 to 11?
List pgsql-general
On 12/20/18 5:51 PM, Chuck Martin wrote:

Please reply to list also.
Ccing list.

> 
> 
> On Thu, Dec 20, 2018 at 7:56 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 12/20/18 12:35 PM, Chuck Martin wrote:
>      > I hope someone here can see something that eludes me. I've recently
>      > moved a database from PostgreSQL 9.6 to 11, and there are a few
>      > oddities. The following select statement returns zero rows when it
>      > should return one. This is one of a small number of records that
>     exist,
>      > but are not returned by the query. When I include the main table,
>     event,
>      > and any one of the associated tables, the record is returned, but no
>      > record is returned with the entire statement. All the primary keys
>      > (_pkey) and foreign keys (_fkey) are integers. The field I
>     suspect as
>      > the possible culprit, event.InsBy, is a character column I'm
>     converting
>      > to do a lookup on a primary key (integer): event.InsBy::int =
>      > usr.Usr_pkey. Maybe PG 11 doesn't recognize the same syntax for
>     cast as
>      > PG 9.6? Or maybe I'm overlooking something else basic. Thanks for
>     reading!
> 
>     So if in the WHERE you leave out the:
> 
>     AND event.InsBy::int = usr.Usr_pkey
> 
>     and in the SELECT you add:
> 
>     event.InsBy, event.InsBy::int AS InsByInt
> 
>     what do you see?
> 
> 
> I get 91 copies of the record. One for each record in the usr table.

But do the event.InsBy, event.InsBy::int AS InsByInt values match each 
other?

Just had a thought, what if you join just the event and usr tables on:

event.InsBy::int = usr.Usr_pkey

Trying to determine whether your suspected culprit really is the culprit.


-- 
Adrian Klaver
adrian.klaver@aklaver.com


pgsql-general by date:

Previous
From: George Neuner
Date:
Subject: Re: Watching for view changes
Next
From: Rijo Roy
Date:
Subject: Re: Wals not recycling