Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17

So I tried one more thing. I executed the raw query on version 17 with a
LIMIT 1 clause and it returned 1 record. So I increased that to LIMIT 100
and it returned 100 records. I increased to LIMIT 1000 and it returned
1000 records. I increased to 10000 and it returned 10000 records. I
increased to 100000 and it returned 19959 records as that is all there are,
and it only took a few seconds to return. However, I then removed the
LIMIT clause, and once again it was hung and never returned.

Why would it return with a LIMIT clause, but not without the LIMIT clause?

On Mon, Jun 9, 2025 at 6:35 PM Lowell Hought <lowell(dot)hought(at)gmail(dot)com>
wrote:

> 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
>
>
>
>
> On Sat, Jun 7, 2025 at 10:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Lowell Hought <lowell(dot)hought(at)gmail(dot)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
>>
>