I wrote a script to create all of the tables, views, and function in an effort to recreate the issue. I ran the script on both version 16 and version 17 and executed the function on each. On both servers, the function returned results, so the attempt to recreate the problem failed. I then ran both versions of the server simultaneously on different ports and attempted a dump from 16 to version 17. I used the pg_dump from version 17. Once again the restore to version 17 got hung up and did not finish. It hangs at the point where it attempts to REFRESH MATERIALIZED view. The materialized view in question uses the function report.GetReportPoolTrainees that we have been discussing. I deleted the materialized view in the version 16 database and then did a dump/restore to the version 17 database, ran ANALYZE, and attempted to execute the query that the function calls. No luck, it would not return.
What is so puzzling to me is that if I do a fresh install of version 16, everything works as it should. But not when I do the exact same thing on version 17.
Lowell
Lowell Hought <lowell.hought@gmail.com> writes:
> I can try. I am not sure how to go about that. I did not see on the bug
> report page where I could upload files, and I am afraid the file size of
> the tables needed might be too large for email.
No, uploading stuff to that webform doesn't work. But at this point
we're just conversing on the pgsql-bugs mailing list, so anything you
can squeeze into email is fine. Having said that, nobody likes
multi-gigabyte emails.
> The entire database when
> written to an sql dump file is about 20 GB, so not terribly large. I could
> attempt to dump the schema definition in one file and then the underlying
> tables in another. Would that work? Or would you also need the files for
> the function and any views the query relies upon?
Yeah, we'd need all the moving parts.
Usually people with this kind of problem don't want to expose their
data anyway, for privacy and/or legal reasons. So what I'd suggest
is trying to create some little script that generates fake data
that's close enough to trigger the problem. Then you just need to
provide that script and the DDL and function definitions.
regards, tom lane
