Обсуждение: Change from 9.6 to 11?

Hello, this seems as if some data was missing on a joined table ...

could you compare the result of
EXPLAIN ANALYZE
for that statement
between both databases ?

and maybe share them ?

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Chuck Martin <clmartin@theombudsman.com> writes:
> 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!

Maybe you've found a bug.  Can you extract a self-contained case
exhibiting this behavior?  Is this 11.0 or 11.1?

            regards, tom lane

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?



> 
> SELECT 
> event.Event_pkey,ombcase.CaseName,status.StatusID,eventtype.EventTypeID,event.datetime 
> AT TIME ZONE 'US/Eastern',event.endtime AT TIME ZONE 
>
'US/Eastern',event.EventDeadline,eventtype.EventIsDeadline,eventtype.OnCalendar,event.EventConfirmed,event.Description,event.PrivateNote,primaryresp.PrimaryrespID,event.IsPrivate,event.Hours,event.EventDone,event.EventDoneDate,event.Importance,event.Urgency,event.InCourt,event.LeadTime,doc.DocID,doctype.DocTypeID,project.ProjectName,usr.UserID,eventtype.Hours,event.Active,event.EditNum,event.InsBy,event.InsDateTime,event.ModBy,event.ModDateTime,event.Case_fkey,event.Eventtype_fkey,event.Project_fkey,event.Doc_fkey,event.Primaryresp_fkey,project.Project_pkey,primaryresp.PrimaryrespHolidayGroup,primaryresp.Usr_pkey,backup.Usr_pkey,doctype.Doctype_pkey,primaryresp.Primaryrespbackup_fkey,ombcase.Status_fkey,event.Parentevent_fkey,event.EventPostRemand

> 
> FROM 
> event,ombcase,status,eventtype,primaryresp,doc,doctype,project,usr,backup
> WHERE event.Case_fkey = Case_pkey
> AND event.Eventtype_fkey = Eventtype_pkey
> AND event.Project_fkey = Project_pkey
> AND event.Primaryresp_fkey = primaryresp.Usr_pkey
> AND event.Doc_fkey = Doc_pkey
> AND Doctype_fkey = Doctype_pkey
> AND usr.Backup_fkey = backup.Usr_pkey
> AND ombcase.Status_fkey = status.Status_pkey
> AND event.InsBy::int = usr.Usr_pkey
> AND event.Event_pkey = 1060071
> ORDER BY EventDone, DateTime DESC
> 
> Chuck Martin
> Avondale Software


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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