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

On Thu, Dec 20, 2018 at 10:12 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.

Thanks, Adrian. This led me to the problem. The data in InsBy was invalid.
That is to say, a join wasn’t possible because no record exists with that
primary key. Not sure how that occurred, but now I know why. Had I
anticipated this might happen, I would have used an outer join.

I appreciate your help solving this minor, but annoying, issue.

--
Adrian Klaver
adrian.klaver@aklaver.com

--

Chuck Martin
Avondale Software