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.hought@gmail.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@sss.pgh.pa.us> wrote:
>
>> 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
>>
>