pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #5272: PL/Python SELECT: return composite fields as dict, not str
19+ messages / 11 participants
[nested] [flat]
Commits from this thread:
2023-07-02 98640f960e 9ae7b5d1f3 0c5fe4ff6b c1affa38c7 3ce761d5ca Fix memory leak in Incremental Sort rescans
* BUG #5272: PL/Python SELECT: return composite fields as dict, not str
@ 2010-01-11 16:41 Steve White <swhite@aip.de>
2010-01-12 02:31 ` Re: BUG #5272: PL/Python SELECT: return composite fields as dict, not str Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Steve White @ 2010-01-11 16:41 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged online:
Bug reference: 5272
Logged by: Steve White
Email address: swhite@aip.de
PostgreSQL version: 8.4.2
Operating system: Linux
Description: PL/Python SELECT: return composite fields as dict, not
str
Details:
Hi,
As recently posted in the mailing list,
http://archives.postgresql.org/pgsql-general/2010-01/msg00389.php
(where you will find a test file, not reproduced here.):
The plpy.execute() command on a SELECT returns a list of nice dictionaries
keyed on field names, containing the fields. For numeric types, the type of
the dictionary values are as expected. Unfortunately however, if a field
contains a composite type, it is flattened to a string.
I would have expected a composite type field to be returned as a dictionary
of values of the proper types, keyed on the names of the elements of the
composite type. After all, it's a DB system that supports hierarchies, and
a programming language that beautifully supports them.
In my case, I was able to work around this problem in an ugly way, but I can
imagine cases where this would render PL/Python unsuitable.
I see nothing in the documentation about this
http://www.postgresql.org/docs/8.4/static/plpython.html
It only talks about passing composite types into and out of functions.
This is an unpleasant discovery for those working to a deadline. Perhaps a
"limitations" section for the doc would be in order. (You could also list
the limitations of PL/Python regarding returning RECORD types.)
But the best thing to do would be to fix it.
For backward compatibility, you might implement a switch that turns on the
hierarchical interpretation of fields, and leave the current behaviour as
the default.
Cheers!
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: BUG #5272: PL/Python SELECT: return composite fields as dict, not str
2010-01-11 16:41 BUG #5272: PL/Python SELECT: return composite fields as dict, not str Steve White <swhite@aip.de>
@ 2010-01-12 02:31 ` Robert Haas <robertmhaas@gmail.com>
2010-01-31 12:20 ` Re: BUG #5272: PL/Python SELECT: return composite fields as dict, not str Steve White <swhite@aip.de>
0 siblings, 1 reply; 19+ messages in thread
From: Robert Haas @ 2010-01-12 02:31 UTC (permalink / raw)
To: Steve White <swhite@aip.de>; +Cc: pgsql-bugs
On Mon, Jan 11, 2010 at 11:41 AM, Steve White <swhite@aip.de> wrote:
> I see nothing in the documentation about this
> http://www.postgresql.org/docs/8.4/static/plpython.html
> It only talks about passing composite types into and out of functions.
>
> This is an unpleasant discovery for those working to a deadline. Perhaps a
> "limitations" section for the doc would be in order. (You could also list
> the limitations of PL/Python regarding returning RECORD types.)
Maybe you could suggest some text, perhaps in the form of a context
diff against the current sgml files?
> But the best thing to do would be to fix it.
Doubtless. Of course it will depend on whether anyone finds enough
round tuits. Peter Eisentraut made some improvements to PL/python
array handling which will be in the next major release, but I am not
aware of anyone working on record types. This is an all-volunteer
effort, so people tend to scratch their own itches.
> For backward compatibility, you might implement a switch that turns on the
> hierarchical interpretation of fields, and leave the current behaviour as
> the default.
I suspect we wouldn't bother with that.
...Robert
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: BUG #5272: PL/Python SELECT: return composite fields as dict, not str
2010-01-11 16:41 BUG #5272: PL/Python SELECT: return composite fields as dict, not str Steve White <swhite@aip.de>
2010-01-12 02:31 ` Re: BUG #5272: PL/Python SELECT: return composite fields as dict, not str Robert Haas <robertmhaas@gmail.com>
@ 2010-01-31 12:20 ` Steve White <swhite@aip.de>
0 siblings, 0 replies; 19+ messages in thread
From: Steve White @ 2010-01-31 12:20 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-bugs
Hi all,
Robert suggested that I start some documentation about limitations of
PL/Python. Find attached.
I think it would also be good to explain the meaning of "trusted" regarding
PL/Python, and how it might impact the user. There is already some
commented-out doc, and also something in the PL/Perl section.
Specifically, a user might want to know if it is actually dangerous to
use PL/Python, etc. If it is dangerous, how, and for whom? Where should
one be careful?
Cheers!
On 11.01.10, Robert Haas wrote:
> On Mon, Jan 11, 2010 at 11:41 AM, Steve White <swhite@aip.de> wrote:
> > I see nothing in the documentation about this
> > http://www.postgresql.org/docs/8.4/static/plpython.html
> > It only talks about passing composite types into and out of functions.
> >
> > This is an unpleasant discovery for those working to a deadline. Perhaps a
> > "limitations" section for the doc would be in order. (You could also list
> > the limitations of PL/Python regarding returning RECORD types.)
>
> Maybe you could suggest some text, perhaps in the form of a context
> diff against the current sgml files?
>
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| e-Science / AstroGrid-D Zi. 35 Bg. 20
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Attachments:
plpython.sgml.diff (text/plain, 1.5K) download
--- plpython.sgml 2009-12-19 23:23:21.000000000 +0100
+++ plpython-new.sgml 2010-01-31 13:17:20.000000000 +0100
@@ -693,6 +693,41 @@
</para>
</sect1>
+ <sect1 id="plpython-database">
+ <title>Limitations and Work-arounds</title>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The result of <function>execute()</function> on a <command>SELECT</command>
+ is a list of dictionary-like objects whose values corresponding to simple
+ database types are of the corresponding Python type. Unfortunately
+ however, values corresponding to composite types are returned as string
+ representations of the composite value.
+ That is, <application>PL/Python</application> flattens all composite type
+ values to strings.
+ </para>
+ <para>
+ Work-arounds: (1) Do a <command>SELECT</command> whose result is the
+ values of the composite entry. (2) Parse up the result strings.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <application>PL/Python</application> functions cannot return either type
+ <type>RECORD</type> or <type>SETOF RECORD</type>.
+ </para>
+ <para>
+ One work-around is to write a <application>PL/pgSQL</application> function
+ that creates a temporary table, have it call the
+ <application>PL/Python</application> function to fill the table,
+ then have function <application>PL/pgSQL</application> function return the
+ generic <type>RECORD</type> from the temporary table.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect1>
<![IGNORE[
<!-- NOT CURRENTLY SUPPORTED -->
* Re: “server closed the connection unexpectedly”
@ 2014-06-18 18:45 Heikki Linnakangas <hlinnakangas@vmware.com>
2014-06-18 18:51 ` Re: “server closed the connection unexpectedly” Felipe Gasper <felipe@felipegasper.com>
0 siblings, 1 reply; 19+ messages in thread
From: Heikki Linnakangas @ 2014-06-18 18:45 UTC (permalink / raw)
To: Felipe Gasper <felipe@felipegasper.com>; pgsql-bugs
On 06/18/2014 09:30 PM, Felipe Gasper wrote:
> The query:
>
> SELECT schemaname, tablename
> FROM pg_tables
> WHERE tableowner IN (E'njvue5kiy1c') OR schemaname='public';
>
> --------
> The error:
>
> DBD::Pg::db selectall_arrayref failed: server closed the connection
> unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> --------
>
> It happens every time. The “njvue5kiy1c” is a random generated name.
Is there any more information in the server log?
- Heikki
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: “server closed the connection unexpectedly”
2014-06-18 18:45 Re: “server closed the connection unexpectedly” Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2014-06-18 18:51 ` Felipe Gasper <felipe@felipegasper.com>
2014-06-18 20:09 ` Re: “server closed the connection unexpectedly” Felipe Gasper <felipe@felipegasper.com>
0 siblings, 1 reply; 19+ messages in thread
From: Felipe Gasper @ 2014-06-18 18:51 UTC (permalink / raw)
To: pgsql-bugs
On 6/18/14 1:45 PM, Heikki Linnakangas wrote:
> On 06/18/2014 09:30 PM, Felipe Gasper wrote:
>> The query:
>>
>> SELECT schemaname, tablename
>> FROM pg_tables
>> WHERE tableowner IN (E'njvue5kiy1c') OR schemaname='public';
>>
>> --------
>> The error:
>>
>> DBD::Pg::db selectall_arrayref failed: server closed the connection
>> unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>>
>> --------
>>
>> It happens every time. The “njvue5kiy1c” is a random generated name.
>
> Is there any more information in the server log?
>
setting log_min_messages to DEBUG5 shows this as the last query and after:
==============
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOG: 00000:
statement: GRANT ALL PRIVILEGES on DATABASE "sameownerdb" TO "sameownerdb";
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
exec_simple_query, postgres.c:842
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
ProcessUtility
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
PortalRunUtility, pquery.c:1163
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
CommitTransactionCommand
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
finish_xact_command, postgres.c:2369
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
CommitTransaction
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
ShowTransactionState, xact.c:4116
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000: name:
unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid:
694/1/1, nestlvl: 1, children:
“postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
ShowTransactionStateRec, xact.c:4154
==============
(Curiously, setting any DEBUG* log_min_messages value also makes the log
messages spit to STDERR as well as my log file.)
-F
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: “server closed the connection unexpectedly”
2014-06-18 18:45 Re: “server closed the connection unexpectedly” Heikki Linnakangas <hlinnakangas@vmware.com>
2014-06-18 18:51 ` Re: “server closed the connection unexpectedly” Felipe Gasper <felipe@felipegasper.com>
@ 2014-06-18 20:09 ` Felipe Gasper <felipe@felipegasper.com>
0 siblings, 0 replies; 19+ messages in thread
From: Felipe Gasper @ 2014-06-18 20:09 UTC (permalink / raw)
To: pgsql-bugs
My bad - bug in my own logic.
Sorry for the spam!
-FG
On 6/18/14 1:51 PM, Felipe Gasper wrote:
> On 6/18/14 1:45 PM, Heikki Linnakangas wrote:
>> On 06/18/2014 09:30 PM, Felipe Gasper wrote:
>>> The query:
>>>
>>> SELECT schemaname, tablename
>>> FROM pg_tables
>>> WHERE tableowner IN (E'njvue5kiy1c') OR schemaname='public';
>>>
>>> --------
>>> The error:
>>>
>>> DBD::Pg::db selectall_arrayref failed: server closed the connection
>>> unexpectedly
>>> This probably means the server terminated abnormally
>>> before or while processing the request.
>>>
>>> --------
>>>
>>> It happens every time. The “njvue5kiy1c” is a random generated name.
>>
>> Is there any more information in the server log?
>>
>
> setting log_min_messages to DEBUG5 shows this as the last query and after:
>
> ==============
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOG: 00000:
> statement: GRANT ALL PRIVILEGES on DATABASE "sameownerdb" TO "sameownerdb";
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
> exec_simple_query, postgres.c:842
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
> ProcessUtility
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
> PortalRunUtility, pquery.c:1163
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
> CommitTransactionCommand
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
> finish_xact_command, postgres.c:2369
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000:
> CommitTransaction
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
> ShowTransactionState, xact.c:4116
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: DEBUG: 00000: name:
> unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid:
> 694/1/1, nestlvl: 1, children:
> “postgres” in “postgres” @ 2014-06-18 13:47:15 CDT: LOCATION:
> ShowTransactionStateRec, xact.c:4154
> ==============
>
> (Curiously, setting any DEBUG* log_min_messages value also makes the log
> messages spit to STDERR as well as my log file.)
>
> -F
>
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 19+ messages in thread
* server closed the connection unexpectedly during upgrade
@ 2015-12-16 07:34 Yang, Leo <leo.yang@hp.com>
2015-12-16 07:57 ` Re: server closed the connection unexpectedly during upgrade Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Yang, Leo @ 2015-12-16 07:34 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: pgsql-bugs
Hi all,
Thank you for your explain for previous question. But I met another error log when running pg_upgrade check from PostgreSQL 8.3 to PostgreSQL 9.4.5:
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "name" user columns connection to database failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
This issue don't reproduces regularly on windows Server 2012. The error log only reproduces in a few cases, but I need to fix this issue. Can you tell me the reasons for this error.
Best Regards,
Leo
-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
Sent: 2015年12月16日 14:28
To: Yang, Leo
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] "pg_upgrade" cannot write to log file pg_upgrade_internal.log
On Tue, Dec 15, 2015 at 6:54 PM, Yang, Leo <leo.yang@hp.com> wrote:
> I run “pg_upgrade -xx” fromPostgreSQL8.3 to PostgreSQL9.4.5 on window
> server 2008. It will show some wrong information:
>
> cannot write to log file pg_upgrade_internal.log
>
> Failure, exiting.
> It seems that the upgrade can be successful on window 7. It seems to
> be a permissions problem. I get some information about restricting
> privileges from source code, and I find the changes “Run pg_upgrade
> and pg_resetxlog with restricted privileges on Windows, so that they
> don't fail when run by an administrator (refer to
> http://www.postgresql.org/docs/9.4/static/release-9-4-2.html )” for
> release 9.4.2. Why did you do this?
The original commit regarding the use of restricted permissions is that:
commit: 2366761bf9eb8e9aa3820b9f3491bcfc48b8188f
author: Andrew Dunstan <andrew@dunslane.net>
date: Mon, 30 Mar 2015 17:16:57 -0400
Run pg_upgrade and pg_resetxlog with restricted token on Windows
And that's a problem that has been discussed here to correct the fact that pg_upgrade cannot run with a user that has administrator rights on Windows:
http://www.postgresql.org/message-id/CAEB4t-NpXGiP5Bqvv3P+d+x=V4BqE+Awg+G7ennBn8icPXep_g@mail.gmail....
Note that pg_upgrade_internal.log gets opened *before* the restricted token is taken for the process running pg_upgrade when the options are parsed, so it seems hard to believe that your problem and this fix are related.
> How can I solve this problem? I hope you can help me.
I think that you should first check if the user under which you are running the upgrade has sufficient permissions to write those log files. As far as I can see, there is nothing to blame from the code.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: server closed the connection unexpectedly during upgrade
2015-12-16 07:34 server closed the connection unexpectedly during upgrade Yang, Leo <leo.yang@hp.com>
@ 2015-12-16 07:57 ` Michael Paquier <michael.paquier@gmail.com>
2015-12-16 08:26 ` Re: server closed the connection unexpectedly during upgrade Yang, Leo <leo.yang@hp.com>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2015-12-16 07:57 UTC (permalink / raw)
To: Yang, Leo <leo.yang@hp.com>; +Cc: pgsql-bugs
On Wed, Dec 16, 2015 at 4:34 PM, Yang, Leo <leo.yang@hp.com> wrote:
> Hi all,
>
> Thank you for your explain for previous question. But I met another error log when running pg_upgrade check from PostgreSQL 8.3 to PostgreSQL 9.4.5:
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
> Checking database user is a superuser ok
> Checking database connection settings ok
> Checking for prepared transactions ok
> Checking for reg* system OID user data types ok
> Checking for contrib/isn with bigint-passing mismatch ok
> Checking for invalid "name" user columns connection to database failed: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> This issue don't reproduces regularly on windows Server 2012. The error log only reproduces in a few cases, but I need to fix this issue. Can you tell me the reasons for this error.
There is no way to know what actually happened without the server
logs, that's where the problem visibly happened..
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: server closed the connection unexpectedly during upgrade
2015-12-16 07:34 server closed the connection unexpectedly during upgrade Yang, Leo <leo.yang@hp.com>
2015-12-16 07:57 ` Re: server closed the connection unexpectedly during upgrade Michael Paquier <michael.paquier@gmail.com>
@ 2015-12-16 08:26 ` Yang, Leo <leo.yang@hp.com>
0 siblings, 0 replies; 19+ messages in thread
From: Yang, Leo @ 2015-12-16 08:26 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: pgsql-bugs
But the issue not reproducing regularly can affect our upgrade project. The attached log files shows log error. Sometimes the issue reproduces when run pg_upgrade check from PostgreSQL 8.3 to PostgreSQL 9.4.5, but it will succeed sometimes.
Regards,
Leo
-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
Sent: 2015年12月16日 15:58
To: Yang, Leo
Cc: pgsql-bugs@postgresql.org
Subject: Re: server closed the connection unexpectedly during upgrade
On Wed, Dec 16, 2015 at 4:34 PM, Yang, Leo <leo.yang@hp.com> wrote:
> Hi all,
>
> Thank you for your explain for previous question. But I met another error log when running pg_upgrade check from PostgreSQL 8.3 to PostgreSQL 9.4.5:
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
> Checking database user is a superuser ok
> Checking database connection settings ok
> Checking for prepared transactions ok
> Checking for reg* system OID user data types ok
> Checking for contrib/isn with bigint-passing mismatch ok
> Checking for invalid "name" user columns connection to database failed: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> This issue don't reproduces regularly on windows Server 2012. The error log only reproduces in a few cases, but I need to fix this issue. Can you tell me the reasons for this error.
There is no way to know what actually happened without the server logs, that's where the problem visibly happened..
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Attachments:
log file.rar (application/octet-stream, 1.5K) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* server closed the connection unexpectedly - bug report
@ 2022-06-25 16:00 Jiří Ledvinka <ledvinkaj@sci.muni.cz>
2022-06-25 21:11 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jiří Ledvinka @ 2022-06-25 16:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello everyone. I have problem with opening DB in pgAdmin. But the DB works
normally. See Screenshots and logs.
After update from version 11.10 to 11.16 it didn't help. pgAdmin also the
newest, and other version work either. I cant upgrade to higher version
because the SW using this DB isn't compatible with higher version. Do you
thing the solution is to make dump, remove the DB and import DB from the
dump.SQL? But there is also TimeScaleDB addon.
Thank you
regards
Jiri
Mgr. Jiří Ledvinka
Head of IT Department
Masaryk University | Faculty of Science
Office for Information and Communication Technologies
A: Kotlářská 267/2, 611 37 Brno, Czech Republic
T: <tel:+420549494060> +420 549 49 4060 | M: <tel:+420602825735> +420 602
825 735
E: <mailto:ledvinkaj@sci.muni.cz> ledvinkaj@sci.muni.cz | W:
<http://www.sci.muni.cz/; www.sci.muni.cz
Attachments:
image001.png (image/png, 77.3K) download
view image
image002.png (image/png, 60.6K) download
view image
image003.png (image/png, 666B) download
view image
image004.png (image/png, 8.8K) download
view image
log.txt (text/plain, 8.2K) download
2022-06-15 10:40:44.341 CEST [7572] LOG: server process (PID 12228) was terminated by exception 0xC0000005
2022-06-15 10:40:44.341 CEST [7572] DETAIL: Failed process was running: SET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='UNICODE';
2022-06-15 10:40:44.341 CEST [7572] HINT: See C include file "ntstatus.h" for a description of the hexadecimal value.
2022-06-15 10:40:44.341 CEST [7572] LOG: terminating any other active server processes
2022-06-15 10:40:44.343 CEST [17876] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.343 CEST [17876] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.343 CEST [17876] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.344 CEST [14020] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.344 CEST [14020] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.344 CEST [14020] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.344 CEST [4812] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.344 CEST [4812] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.344 CEST [4812] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.344 CEST [9256] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.344 CEST [9256] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.344 CEST [9256] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.345 CEST [14688] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.345 CEST [14688] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.345 CEST [14688] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.346 CEST [5592] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.346 CEST [5592] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.346 CEST [5592] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.346 CEST [3384] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.346 CEST [3384] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.346 CEST [3384] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.347 CEST [17356] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.347 CEST [17356] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.347 CEST [17356] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.347 CEST [15644] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.347 CEST [15644] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.347 CEST [15644] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.348 CEST [16100] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.348 CEST [16100] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.348 CEST [16100] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.348 CEST [8096] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.348 CEST [8096] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.348 CEST [8096] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.351 CEST [16380] WARNING: terminating connection because of crash of another server process
2022-06-15 10:40:44.351 CEST [16380] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-06-15 10:40:44.351 CEST [16380] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-06-15 10:40:44.374 CEST [16836] FATAL: the database system is in recovery mode
2022-06-15 10:40:44.386 CEST [7572] LOG: all server processes terminated; reinitializing
2022-06-15 10:40:44.419 CEST [17684] LOG: database system was interrupted; last known up at 2022-06-15 10:39:46 CEST
2022-06-15 10:40:45.689 CEST [17300] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.708 CEST [12076] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.826 CEST [7332] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.948 CEST [9472] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.960 CEST [2784] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.973 CEST [5704] FATAL: the database system is in recovery mode
2022-06-15 10:40:45.987 CEST [17736] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.004 CEST [17912] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.029 CEST [17948] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.061 CEST [11684] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.532 CEST [15716] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.549 CEST [6184] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.566 CEST [8288] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.582 CEST [17984] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.599 CEST [6640] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.615 CEST [18008] FATAL: the database system is in recovery mode
2022-06-15 10:40:46.617 CEST [17684] LOG: database system was not properly shut down; automatic recovery in progress
2022-06-15 10:40:46.620 CEST [17684] LOG: redo starts at CB/3179C3C0
2022-06-15 10:40:46.689 CEST [17684] LOG: redo done at CB/321AFFB0
2022-06-15 10:40:46.689 CEST [17684] LOG: last completed transaction was at log time 2022-06-15 10:40:38.367048+02
2022-06-15 10:40:46.891 CEST [7572] LOG: database system is ready to accept connections
2022-06-15 10:40:46.915 CEST [17264] LOG: TimescaleDB background worker launcher connected to shared catalogs
* Re: server closed the connection unexpectedly - bug report
2022-06-25 16:00 server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
@ 2022-06-25 21:11 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-25 21:22 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2022-06-25 21:11 UTC (permalink / raw)
To: Jiří Ledvinka <ledvinkaj@sci.muni.cz>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Saturday, June 25, 2022, Jiří Ledvinka <ledvinkaj@sci.muni.cz> wrote:
> Hello everyone. I have problem with opening DB in pgAdmin. But the DB
> works normally. See Screenshots and logs.
>
> After update from version 11.10 to 11.16 it didn’t help. pgAdmin also the
> newest, and other version work either. I cant upgrade to higher version
> because the SW using this DB isn’t compatible with higher version. Do you
> thing the solution is to make dump, remove the DB and import DB from the
> dump.SQL? But there is also TimeScaleDB addon.
>
>
Check logs and try just using psql to connect.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: server closed the connection unexpectedly - bug report
2022-06-25 16:00 server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
2022-06-25 21:11 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
@ 2022-06-25 21:22 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-11 08:05 ` RE: server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2022-06-25 21:22 UTC (permalink / raw)
To: Jiří Ledvinka <ledvinkaj@sci.muni.cz>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sat, Jun 25, 2022 at 2:11 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Saturday, June 25, 2022, Jiří Ledvinka <ledvinkaj@sci.muni.cz> wrote:
>
>> Hello everyone. I have problem with opening DB in pgAdmin. But the DB
>> works normally. See Screenshots and logs.
>>
>> After update from version 11.10 to 11.16 it didn’t help. pgAdmin also the
>> newest, and other version work either. I cant upgrade to higher version
>> because the SW using this DB isn’t compatible with higher version. Do you
>> thing the solution is to make dump, remove the DB and import DB from the
>> dump.SQL? But there is also TimeScaleDB addon.
>>
>>
> Check logs and try just using psql to connect.
>
>
So what was process 12228? Any log messages related to it? Last server up
was 5 minutes before the logs you are showing here.
If indeed it is an attempt by pgAdmin to connect and issue that set of SQL
commands causing an access violation does the same violation occur if you
emulate it but using psql?
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: server closed the connection unexpectedly - bug report
2022-06-25 16:00 server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
2022-06-25 21:11 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
2022-06-25 21:22 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
@ 2022-07-11 08:05 ` Jiří Ledvinka <ledvinkaj@sci.muni.cz>
0 siblings, 0 replies; 19+ messages in thread
From: Jiří Ledvinka @ 2022-07-11 08:05 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I dont know what the process 12228 was. Because the OS is Windows Server. Do you want me to send the verbose log file? The SQL commands doesnt not cause the access violation.
Regards
Jiri
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Saturday, June 25, 2022 11:23 PM
To: Jiří Ledvinka <ledvinkaj@sci.muni.cz>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: server closed the connection unexpectedly - bug report
On Sat, Jun 25, 2022 at 2:11 PM David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>> wrote:
On Saturday, June 25, 2022, Jiří Ledvinka <ledvinkaj@sci.muni.cz<mailto:ledvinkaj@sci.muni.cz>> wrote:
Hello everyone. I have problem with opening DB in pgAdmin. But the DB works normally. See Screenshots and logs.
After update from version 11.10 to 11.16 it didn’t help. pgAdmin also the newest, and other version work either. I cant upgrade to higher version because the SW using this DB isn’t compatible with higher version. Do you thing the solution is to make dump, remove the DB and import DB from the dump.SQL? But there is also TimeScaleDB addon.
Check logs and try just using psql to connect.
So what was process 12228? Any log messages related to it? Last server up was 5 minutes before the logs you are showing here.
If indeed it is an attempt by pgAdmin to connect and issue that set of SQL commands causing an access violation does the same violation occur if you emulate it but using psql?
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Server closed the connection unexpectedly
@ 2023-06-15 16:26 Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-15 20:12 ` Re: Server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Zu-Ming Jiang @ 2023-06-15 16:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
My fuzzer finds a bug in Postgres, which makes the connection closed
unexpectedly.
--- Steps to reproduce the bug ---
1. /usr/local/pgsql/bin/psql -d postgres -c "drop database redb";
2. /usr/local/pgsql/bin/psql -d postgres -c "create database redb";
3. /usr/local/pgsql/bin/psql -d redb -f pg_testdb10_bk.sql;
4. /usr/local/pgsql/bin/psql -d redb -f unexpected.sql
pg_testdb10_bk.sql and unexpected.sql are attached.
--- Expected behavior ---
No error triggered.
--- Actual behavior ---
The test case makes the connection closed unexpectedly:
psql:unexpected.sql:39: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:unexpected.sql:39: error: connection to server was lost
--- Postgres version ---
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
Attachments:
pg_testdb10_bk.sql (text/plain, 10.1K) download
--
-- PostgreSQL database dump
--
-- Dumped from database version 16beta1
-- Dumped by pg_dump version 16beta1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: t0; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t0 (
vkey integer,
pkey integer,
c0 text,
c1 integer NOT NULL,
c2 text,
c3 text
);
ALTER TABLE public.t0 OWNER TO zuming;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t1 (
vkey integer,
pkey integer NOT NULL,
c4 text,
c5 text
);
ALTER TABLE public.t1 OWNER TO zuming;
--
-- Name: t2; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t2 (
vkey integer,
pkey integer,
c6 integer,
c7 timestamp without time zone,
c8 timestamp without time zone,
c9 double precision,
c10 double precision,
c11 integer,
c12 integer,
c13 integer,
c14 integer
);
ALTER TABLE public.t2 OWNER TO zuming;
--
-- Name: t3; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t3 (
vkey integer,
pkey integer,
c15 timestamp without time zone,
c16 integer,
c17 integer,
c18 timestamp without time zone,
c19 double precision
);
ALTER TABLE public.t3 OWNER TO zuming;
--
-- Name: t4; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t4 AS
SELECT c_0
FROM ( SELECT (true AND ((ref_0.c11 IS NOT NULL) AND (( SELECT t0.c0
FROM public.t0
ORDER BY t0.c0
OFFSET 3
LIMIT 1) < ( SELECT t0.c0
FROM public.t0
ORDER BY t0.c0
OFFSET 4
LIMIT 1)))) AS c_0
FROM (public.t2 ref_0
CROSS JOIN public.t2 ref_1)
WHERE (ref_1.c8 >= ( SELECT t2.c7
FROM public.t2
ORDER BY t2.c7
OFFSET 4
LIMIT 1))) subq_0
WHERE (COALESCE(
CASE
WHEN (NOT NULL::boolean) THEN ( SELECT var_pop(t3.c19) AS var_pop
FROM public.t3)
ELSE ((log(57.49))::double precision * ( SELECT t3.c19
FROM public.t3
ORDER BY t3.c19
OFFSET 4
LIMIT 1))
END, ( SELECT t2.c9
FROM public.t2
ORDER BY t2.c9
OFFSET 4
LIMIT 1)) = NULLIF(NULLIF(( SELECT t2.c9
FROM public.t2
ORDER BY t2.c9
OFFSET 1
LIMIT 1), (NULL::double precision ^ date_part(( SELECT t0.c0
FROM public.t0
ORDER BY t0.c0
OFFSET 97
LIMIT 1), ( SELECT t2.c7
FROM public.t2
ORDER BY t2.c7
OFFSET 4
LIMIT 1)))), power(( SELECT t3.c19
FROM public.t3
ORDER BY t3.c19
OFFSET 3
LIMIT 1), NULL::double precision)));
ALTER VIEW public.t4 OWNER TO zuming;
--
-- Name: t5; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t5 (
vkey integer,
pkey integer,
c20 double precision NOT NULL,
c21 integer,
c22 double precision,
c23 integer,
c24 timestamp without time zone,
c25 integer
);
ALTER TABLE public.t5 OWNER TO zuming;
--
-- Name: t6; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t6 (
vkey integer,
pkey integer,
c26 integer,
c27 double precision,
c28 integer,
c29 double precision,
c30 timestamp without time zone,
c31 double precision,
c32 double precision,
c33 text,
c34 timestamp without time zone
);
ALTER TABLE public.t6 OWNER TO zuming;
--
-- Name: t7; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t7 AS
SELECT ( SELECT min(t6.vkey) AS min
FROM public.t6) AS c_0,
length(ref_0.c4) AS c_1
FROM ((public.t1 ref_0
FULL JOIN public.t0 ref_1 ON ((ref_0.c4 = ref_1.c0)))
FULL JOIN public.t0 ref_2 ON ((ref_0.c5 = ref_2.c0)))
WHERE (ref_1.c0 > ( SELECT ref_1.c2 AS c_0
FROM (public.t3 ref_3
RIGHT JOIN public.t0 ref_4 ON ((ref_3.pkey = ref_4.vkey)))
WHERE false
ORDER BY ref_1.c2
LIMIT 1))
ORDER BY ( SELECT min(t6.vkey) AS min
FROM public.t6), (length(ref_0.c4))
LIMIT 85;
ALTER VIEW public.t7 OWNER TO zuming;
--
-- Data for Name: t0; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t0 (vkey, pkey, c0, c1, c2, c3) FROM stdin;
16 26000 $O 0 9#MQ Jzs&,
23 33000 V*+ -32 4IO gYGUY
27 37000 45 (u #gg.=
28 38000 j7$> -74 \N
50 60000 XE 74 5# Nh&Lb
52 62000 \N -48 sH Z!j3
55 65000 ai` 47 0g WK}C
81 91000 \N -15 ^kOT ?
\.
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t1 (vkey, pkey, c4, c5) FROM stdin;
1 11000 AG \N
11 21000 \N
15 25000 }O0L9 KI4
18 28000 cX+=} ,@XF
21 31000 iB L]
30 40000 ]@=$ $
32 42000 SKLx Y3
33 43000 5IQ ~9?A~
34 44000
35 45000 \N 7
37 47000 QvY[ c
41 51000 \N RL
53 63000 LWP` X@KkT
58 68000 I \N
59 69000 -] E(c4R
63 73000 (&- rai
66 76000 T^ZwG 8
67 77000 !QoE <do{
69 79000 -Jp |!1
70 80000 C.E . B
76 86000 2 (8
91 101000 BI X
98 108000 DC7u ~-
99 109000 9 \N
\.
--
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t2 (vkey, pkey, c6, c7, c8, c9, c10, c11, c12, c13, c14) FROM stdin;
2 12000 0 2055-01-04 23:40:00 2024-07-22 23:03:49 0 \N 27 60 56 -92
5 15000 0 2000-09-25 22:52:05 2049-02-21 17:01:31 -34.56 -99.55 47 -50 \N -80
12 22000 -55 2009-02-16 04:56:57 2023-10-07 13:12:09 0 0 -68 0 \N 61
13 23000 50 2002-11-27 03:01:00 1987-09-12 20:23:02 -68.28 23.91 42 -78 0 97
14 24000 \N 2045-01-03 10:13:13 2086-08-07 23:03:18 0 -26.9 \N \N -70 \N
19 29000 \N 2024-07-15 01:22:12 2054-04-05 00:02:07 \N 24.59 46 0 -29 -47
24 34000 94 2096-01-24 18:22:12 1989-02-19 20:43:45 85.71 0 \N 8 0 -100
68 78000 0 2062-11-28 18:06:53 \N -13.4 58.2 67 \N 89 \N
71 81000 0 2104-04-20 20:42:40 2075-10-12 21:04:23 \N 60.78 -16 -10 8 -17
77 87000 0 \N 2003-03-11 19:11:41 0 -73.81 -89 11 \N 0
78 88000 0 2105-05-24 10:18:51 1973-08-18 23:43:14 \N 82.3 -56 94 5 0
86 96000 -18 2078-11-15 07:35:55 2046-08-18 19:00:30 -28.27 35.58 0 -59 -90 45
89 99000 \N 2065-06-06 21:53:15 2093-06-05 06:29:18 65.81 0 0 0 \N 22
\.
--
-- Data for Name: t3; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t3 (vkey, pkey, c15, c16, c17, c18, c19) FROM stdin;
4 14000 1982-02-26 16:53:15 0 62 2106-09-10 05:52:42 -29.87
7 17000 2028-05-24 10:45:17 \N 0 \N 0
38 48000 2078-02-07 17:37:16 3 -78 1989-02-27 23:23:21 59.12
39 49000 2038-09-10 23:10:56 32 \N 1983-04-15 17:59:48 \N
40 50000 2070-07-08 09:44:33 80 0 2073-05-28 16:31:27 0
42 52000 2058-12-18 04:59:10 \N 25 2007-03-03 13:32:55 \N
46 56000 2031-03-20 18:32:25 \N 0 2072-07-09 02:19:24 9.84
49 59000 2018-05-22 06:33:15 -10 44 2018-05-21 11:31:35 79.29
54 64000 2020-05-28 06:17:15 22 0 \N 56.46
61 71000 1983-12-04 01:45:11 -3 96 1996-03-23 08:46:46 72.64
62 72000 2042-03-26 06:33:23 0 0 2051-09-26 05:13:19 -5.84
79 89000 1981-03-28 22:42:10 0 -48 1995-12-24 23:20:55 \N
87 97000 2103-12-25 22:55:37 -90 0 2072-05-08 01:39:50 86.5
90 100000 2072-02-22 19:32:06 31 \N 2100-04-04 23:44:49 0
\.
--
-- Data for Name: t5; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t5 (vkey, pkey, c20, c21, c22, c23, c24, c25) FROM stdin;
25 35000 14.56 18 0 \N 1992-10-18 00:48:30 -24
26 36000 0 \N 0 0 2034-11-11 17:32:56 -61
29 39000 -5.47 52 -92.49 3 2042-03-24 10:37:48 5
36 46000 -85.74 -53 -10.11 -22 1997-05-19 21:43:48 0
43 53000 -87.93 \N -12.49 41 2012-10-25 14:59:07 45
45 55000 -95.1 -4 0 0 2011-12-22 06:53:17 -76
57 67000 95.98 2 \N 90 2081-09-26 15:39:20 9
65 75000 -58.32 -94 0 \N 2028-01-13 00:50:12 10
94 104000 -28.88 0 0 -7 2041-07-10 16:25:41 -64
\.
--
-- Data for Name: t6; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t6 (vkey, pkey, c26, c27, c28, c29, c30, c31, c32, c33, c34) FROM stdin;
3 13000 \N 0 \N 0 1995-01-22 18:28:53 5.53 -40.69 @VH 2013-06-02 04:35:10
6 16000 \N 0 0 33.18 2106-07-04 08:04:50 5.73 54.95 V@t2 2065-07-08 03:21:15
8 18000 \N -82.34 0 \N 2021-10-26 10:06:51 6.71 0 \N 1989-04-03 08:00:41
9 19000 -2 0 0 0 2014-12-12 17:00:30 -9.63 -48.16 2016-05-08 03:46:39
10 20000 -46 \N -30 -94.3 2026-11-23 23:51:09 83.21 0 T 2010-03-02 20:40:54
17 27000 \N 32.64 19 0 2031-02-11 21:25:48 \N -48.5 f+|/Z 2101-01-14 18:52:03
31 41000 \N \N 7 0 2059-10-27 22:05:06 -29.16 -87.85 9 2019-04-18 15:43:37
47 57000 \N \N 0 -4.24 2016-02-16 10:57:41 0 0 D!~; 1994-09-27 14:34:08
51 61000 96 63.53 0 90.64 2054-02-28 23:38:34 0 22.86 , 2040-08-25 15:10:16
56 66000 -100 10.4 87 44.7 1990-03-25 07:19:35 -49.14 \N -6f) 1970-08-10 07:17:07
64 74000 0 \N 0 0 \N \N \N Z( 2065-12-14 02:23:00
74 84000 0 0 39 31.71 \N -96.61 0 \N
75 85000 10 \N 35 -38.66 2081-12-24 18:08:39 0 \N \N 1998-03-15 09:57:04
80 90000 0 96.9 73 -68.4 1982-07-26 05:02:29 0 98.26 /y 2011-02-20 15:24:54
82 92000 0 0 -96 -63.82 1998-09-26 06:21:35 0 0 Ar:Ip 2095-12-22 00:37:54
85 95000 0 0 55 -59.6 \N -75.9 40.69 2045-12-12 08:24:11
88 98000 0 -34.35 54 -25.68 2079-02-19 23:04:47 0 29.61 LLcI4 \N
92 102000 77 70.63 -38 64.83 2020-04-13 10:28:37 -97.84 -45.78 2100-11-17 20:00:20
95 105000 -39 0 -50 0 2021-11-11 01:50:56 0 96.73 cbv 1996-01-26 14:03:50
96 106000 100 0 0 29.69 2030-03-08 11:05:09 \N 61.6 7`9) 2105-10-24 10:32:26
\.
--
-- Name: t0 t0_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t0
ADD CONSTRAINT t0_pkey PRIMARY KEY (c1);
--
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t1
ADD CONSTRAINT t1_pkey PRIMARY KEY (pkey);
--
-- Name: t5 t5_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t5
ADD CONSTRAINT t5_pkey PRIMARY KEY (c20);
--
-- Name: i0; Type: INDEX; Schema: public; Owner: zuming
--
CREATE INDEX i0 ON public.t6 USING btree (vkey DESC, c28 DESC, c33);
--
-- PostgreSQL database dump complete
--
unexpected.sql (text/plain, 4.6K) download
delete from t1
where (((case when ((t1.vkey in (
select 1
from
((t2 as ref_2 cross join t6 as ref_3)
cross join (t2 as ref_4
right outer join t6 as ref_5
on (ref_4.vkey = ref_5.vkey)))
where (case when ((ref_5.c26 in (
select ref_5.vkey
as c_0
from
t6 as ref_6
where ((case when (((exists (
select
pg_catalog.stddev_pop(ref_3.c29) over w0 as c_0,
ref_21.c32 as c_1
from
t6 as ref_21
window w0 as (partition by ref_4.c7 order by t1.vkey asc, t1.pkey asc, t1.c4 desc, t1.c5 asc, ref_2.vkey desc, ref_2.pkey desc, ref_2.c6 asc, ref_2.c7 asc, ref_2.c8 desc, ref_2.c9 desc, ref_2.c10 asc, ref_2.c11 asc, ref_2.c12 asc, ref_2.c13 desc, ref_2.c14 asc, ref_3.vkey asc, ref_3.pkey desc, ref_3.c26 asc, ref_3.c27 desc, ref_3.c28 asc, ref_3.c29 asc, ref_3.c30 asc, ref_3.c31 asc, ref_3.c32 asc, ref_3.c33 asc, ref_3.c34 asc, ref_4.vkey asc, ref_4.pkey desc, ref_4.c6 asc, ref_4.c7 desc, ref_4.c8 desc, ref_4.c9 desc, ref_4.c10 asc, ref_4.c11 asc, ref_4.c12 asc, ref_4.c13 asc, ref_4.c14 desc, ref_5.vkey asc, ref_5.pkey desc, ref_5.c26 asc, ref_5.c27 desc, ref_5.c28 asc, ref_5.c29 desc, ref_5.c30 desc, ref_5.c31 asc, ref_5.c32 desc, ref_5.c33 desc, ref_5.c34 desc, ref_6.vkey asc, ref_6.pkey desc, ref_6.c26 asc, ref_6.c27 asc, ref_6.c28 asc, ref_6.c29 asc, ref_6.c30 asc, ref_6.c31 asc, ref_6.c32 asc, ref_6.c33 desc, ref_6.c34 asc, ref_21.vkey asc, ref_21.pkey asc, ref_21.c26 desc, ref_21.c27 asc, ref_21.c28 desc, ref_21.c29 asc, ref_21.c30 asc, ref_21.c31 desc, ref_21.c32 desc, ref_21.c33 desc, ref_21.c34 asc)))
and (not (exists (
select
pg_catalog.stddev_pop(ref_3.c29) over w0 as c_0,
ref_21.c32 as c_1
from
t6 as ref_21
window w0 as (partition by ref_4.c7 order by t1.vkey asc, t1.pkey asc, t1.c4 desc, t1.c5 asc, ref_2.vkey desc, ref_2.pkey desc, ref_2.c6 asc, ref_2.c7 asc, ref_2.c8 desc, ref_2.c9 desc, ref_2.c10 asc, ref_2.c11 asc, ref_2.c12 asc, ref_2.c13 desc, ref_2.c14 asc, ref_3.vkey asc, ref_3.pkey desc, ref_3.c26 asc, ref_3.c27 desc, ref_3.c28 asc, ref_3.c29 asc, ref_3.c30 asc, ref_3.c31 asc, ref_3.c32 asc, ref_3.c33 asc, ref_3.c34 asc, ref_4.vkey asc, ref_4.pkey desc, ref_4.c6 asc, ref_4.c7 desc, ref_4.c8 desc, ref_4.c9 desc, ref_4.c10 asc, ref_4.c11 asc, ref_4.c12 asc, ref_4.c13 asc, ref_4.c14 desc, ref_5.vkey asc, ref_5.pkey desc, ref_5.c26 asc, ref_5.c27 desc, ref_5.c28 asc, ref_5.c29 desc, ref_5.c30 desc, ref_5.c31 asc, ref_5.c32 desc, ref_5.c33 desc, ref_5.c34 desc, ref_6.vkey asc, ref_6.pkey desc, ref_6.c26 asc, ref_6.c27 asc, ref_6.c28 asc, ref_6.c29 asc, ref_6.c30 asc, ref_6.c31 asc, ref_6.c32 asc, ref_6.c33 desc, ref_6.c34 asc, ref_21.vkey asc, ref_21.pkey asc, ref_21.c26 desc, ref_21.c27 asc, ref_21.c28 desc, ref_21.c29 asc, ref_21.c30 asc, ref_21.c31 desc, ref_21.c32 desc, ref_21.c33 desc, ref_21.c34 asc)))))
and ((exists (
select
pg_catalog.stddev_pop(ref_3.c29) over w0 as c_0,
ref_21.c32 as c_1
from
t6 as ref_21
window w0 as (partition by ref_4.c7 order by t1.vkey asc, t1.pkey asc, t1.c4 desc, t1.c5 asc, ref_2.vkey desc, ref_2.pkey desc, ref_2.c6 asc, ref_2.c7 asc, ref_2.c8 desc, ref_2.c9 desc, ref_2.c10 asc, ref_2.c11 asc, ref_2.c12 asc, ref_2.c13 desc, ref_2.c14 asc, ref_3.vkey asc, ref_3.pkey desc, ref_3.c26 asc, ref_3.c27 desc, ref_3.c28 asc, ref_3.c29 asc, ref_3.c30 asc, ref_3.c31 asc, ref_3.c32 asc, ref_3.c33 asc, ref_3.c34 asc, ref_4.vkey asc, ref_4.pkey desc, ref_4.c6 asc, ref_4.c7 desc, ref_4.c8 desc, ref_4.c9 desc, ref_4.c10 asc, ref_4.c11 asc, ref_4.c12 asc, ref_4.c13 asc, ref_4.c14 desc, ref_5.vkey asc, ref_5.pkey desc, ref_5.c26 asc, ref_5.c27 desc, ref_5.c28 asc, ref_5.c29 desc, ref_5.c30 desc, ref_5.c31 asc, ref_5.c32 desc, ref_5.c33 desc, ref_5.c34 desc, ref_6.vkey asc, ref_6.pkey desc, ref_6.c26 asc, ref_6.c27 asc, ref_6.c28 asc, ref_6.c29 asc, ref_6.c30 asc, ref_6.c31 asc, ref_6.c32 asc, ref_6.c33 desc, ref_6.c34 asc, ref_21.vkey asc, ref_21.pkey asc, ref_21.c26 desc, ref_21.c27 asc, ref_21.c28 desc, ref_21.c29 asc, ref_21.c30 asc, ref_21.c31 desc, ref_21.c32 desc, ref_21.c33 desc, ref_21.c34 asc))) is not null)
) then (ref_4.c10) else (ref_3.c27) end
) = ref_6.c32)
order by c_0 asc)) is not null) then true else (null::bool) end
))) is not null) then true else (null::bool) end
)));
* Re: Server closed the connection unexpectedly
2023-06-15 16:26 Server closed the connection unexpectedly Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
@ 2023-06-15 20:12 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 2023-06-15 20:12 UTC (permalink / raw)
To: Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>; +Cc: pgsql-bugs@lists.postgresql.org
Zu-Ming Jiang <zuming.jiang@inf.ethz.ch> writes:
> My fuzzer finds a bug in Postgres, which makes the connection closed
> unexpectedly.
Thanks for the report. This seems to be the same memory-leakage
issue under discussion here:
https://www.postgresql.org/message-id/flat/b2bd02dff61af15e3526293e2771f874cf2a3be7.camel%40cybertec...
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Server closed the connection unexpectedly (memory leak)
@ 2023-06-23 16:06 Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-23 16:20 ` Fwd: Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-24 16:56 ` Re: Server closed the connection unexpectedly (memory leak) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Zu-Ming Jiang @ 2023-06-23 16:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
My fuzzer finds a bug in Postgres, which makes the connection closed
unexpectedly. This bug still can be reproduced even after applying the
patch for
https://www.postgresql.org/message-id/flat/b2bd02dff61af15e3526293e2771f874cf2a3be7.camel@cybertec.a...
--- Steps to reproduce the bug ---
1. /usr/local/pgsql/bin/psql -d postgres -c "drop database redb";
2. /usr/local/pgsql/bin/psql -d postgres -c "create database redb";
3. /usr/local/pgsql/bin/psql -d redb -f pg_testdb55_bk.sql;
4. /usr/local/pgsql/bin/psql -d redb -f unexpected.sql
pg_testdb55_bk.sql and unexpected.sql are attached.
--- Expected behavior ---
No error triggered.
--- Actual behavior ---
The test case makes the connection closed unexpectedly:
psql:unexpected.sql:695: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:unexpected.sql:695: error: connection to server was lost
--- Postgres version ---
Github commit: f5c446e3367527f9db1506d7c38d2f56e20950b6
Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
Attachments:
pg_testdb55_bk.sql (text/plain, 13.4K) download
--
-- PostgreSQL database dump
--
-- Dumped from database version 16beta1
-- Dumped by pg_dump version 16beta1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: t0; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t0 (
vkey integer NOT NULL,
pkey integer,
c0 integer,
c1 double precision,
c2 text,
c3 timestamp without time zone
);
ALTER TABLE public.t0 OWNER TO zuming;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t1 (
vkey integer NOT NULL,
pkey integer,
c4 text,
c5 integer
);
ALTER TABLE public.t1 OWNER TO zuming;
--
-- Name: t2; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t2 (
vkey integer,
pkey integer,
c6 double precision
);
ALTER TABLE public.t2 OWNER TO zuming;
--
-- Name: t3; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t3 AS
SELECT
CASE
WHEN (c4 !~~ '%%_'::text) THEN ( SELECT t0.c2
FROM public.t0
ORDER BY t0.c2
OFFSET 1
LIMIT 1)
ELSE c4
END AS c_0,
vkey AS c_1,
vkey AS c_2,
c4 AS c_3,
( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 4
LIMIT 1) AS c_4,
CASE
WHEN ((vkey >= ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 4
LIMIT 1)) AND (vkey <= int4inc(vkey))) THEN pkey
ELSE (vkey | ( SELECT t0.vkey
FROM public.t0
ORDER BY t0.vkey
OFFSET 64
LIMIT 1))
END AS c_5,
regexp_count(c4, c4, c5, c4) AS c_6,
COALESCE(c4, c4) AS c_7,
pkey AS c_8,
dpow(var_samp(float8up(( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 6
LIMIT 1))) OVER (PARTITION BY c5 ORDER BY vkey DESC, pkey, c4 DESC, c5 DESC), NULL::double precision) AS c_9,
dround(cos(
CASE
WHEN (( SELECT bit_or(t1.vkey) AS bit_or
FROM public.t1) <> vkey) THEN ( SELECT t2.c6
FROM public.t2
ORDER BY t2.c6
OFFSET 43
LIMIT 1)
ELSE atanh(( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 51
LIMIT 1))
END)) AS c_10,
c4 AS c_11,
vkey AS c_12,
"substring"(md5(c4), pkey) AS c_13,
vkey AS c_14,
(((EXISTS ( SELECT ref_2.c2 AS c_0,
( SELECT min(t0.c3) AS min
FROM public.t0) AS c_1,
ref_2.c2 AS c_2,
ref_2.vkey AS c_3,
( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 3
LIMIT 1) AS c_4,
ref_2.vkey AS c_5,
( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 6
LIMIT 1) AS c_6,
( SELECT max(t2.vkey) AS max
FROM public.t2) AS c_7,
ref_2.vkey AS c_8
FROM public.t0 ref_2
WHERE (NULL::boolean AND true))) AND false) OR true OR (EXISTS ( SELECT DISTINCT (ref_0.c4 = ref_0.c4) AS c_0,
false AS c_1
FROM public.t2 ref_3
WHERE (true OR (NOT true))
UNION
SELECT false AS c_0,
(ref_4.c2 = ref_0.c4) AS c_1
FROM public.t0 ref_4
WHERE ((( SELECT t0.vkey
FROM public.t0
ORDER BY t0.vkey
OFFSET 5
LIMIT 1) <> ref_0.vkey) AND NULL::boolean))) OR (c4 ~>~ c4) OR (c4 ~~ 'mjo'::text)) AS c_15,
CASE
WHEN (c4 <= ( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 5
LIMIT 1)) THEN ((vkey >= ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 6
LIMIT 1)) AND (vkey <= c5))
ELSE ((( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 4
LIMIT 1) !~~ 'L_%'::text) AND (false OR (( SELECT max(t0.c2) AS max
FROM public.t0) !~ c4)))
END AS c_16,
bit_and(pkey) OVER (PARTITION BY c5 ORDER BY vkey, pkey, c4 DESC, c5 DESC) AS c_17,
(vkey + ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 3
LIMIT 1)) AS c_18,
( SELECT t1.pkey
FROM public.t1
ORDER BY t1.pkey
OFFSET 4
LIMIT 1) AS c_19,
pkey AS c_20,
similar_to_escape(c4, c4) AS c_21
FROM public.t1 ref_0
WHERE (bool((( SELECT max(t0.vkey) AS max
FROM public.t0) >> c5)) IN ( SELECT (NOT false) AS c_0
FROM public.t1 ref_1
WHERE ((0.0 < '-55.30'::numeric) AND NULL::boolean AND (true OR (ref_0.c5 <> ref_1.pkey)))));
ALTER VIEW public.t3 OWNER TO zuming;
--
-- Name: t4; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t4 AS
SELECT DISTINCT stddev(ref_0.c_9) OVER (PARTITION BY ref_0.c_18, ref_2.c_20 ORDER BY ref_0.c_0 DESC, ref_0.c_1 DESC, ref_0.c_2 DESC, ref_0.c_3, ref_0.c_4, ref_0.c_5, ref_0.c_6 DESC, ref_0.c_7 DESC, ref_0.c_8, ref_0.c_9, ref_0.c_10 DESC, ref_0.c_11 DESC, ref_0.c_12 DESC, ref_0.c_13 DESC, ref_0.c_14 DESC, ref_0.c_15 DESC, ref_0.c_16, ref_0.c_17 DESC, ref_0.c_18 DESC, ref_0.c_19, ref_0.c_20 DESC, ref_0.c_21, ref_1.c_0, ref_1.c_1, ref_1.c_2 DESC, ref_1.c_3 DESC, ref_1.c_4, ref_1.c_5, ref_1.c_6, ref_1.c_7 DESC, ref_1.c_8, ref_1.c_9, ref_1.c_10 DESC, ref_1.c_11, ref_1.c_12, ref_1.c_13, ref_1.c_14 DESC, ref_1.c_15 DESC, ref_1.c_16, ref_1.c_17, ref_1.c_18 DESC, ref_1.c_19 DESC, ref_1.c_20 DESC, ref_1.c_21 DESC, ref_2.c_0 DESC, ref_2.c_1 DESC, ref_2.c_2, ref_2.c_3, ref_2.c_4 DESC, ref_2.c_5, ref_2.c_6, ref_2.c_7 DESC, ref_2.c_8, ref_2.c_9 DESC, ref_2.c_10 DESC, ref_2.c_11 DESC, ref_2.c_12 DESC, ref_2.c_13, ref_2.c_14 DESC, ref_2.c_15 DESC, ref_2.c_16 DESC, ref_2.c_17, ref_2.c_18, ref_2.c_19, ref_2.c_20, ref_2.c_21 DESC) AS c_0,
ref_0.c_8 AS c_1,
ref_0.c_3 AS c_2,
CASE
WHEN NULL::boolean THEN ( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 33
LIMIT 1)
ELSE "left"('O'::text, ref_1.c_5)
END AS c_3,
COALESCE(( SELECT var_pop(t0.c1) AS var_pop
FROM public.t0), ref_0.c_10) AS c_4,
(ref_1.c_18 <= ref_2.c_19) AS c_5,
acosh(min(ref_2.c_9) OVER (PARTITION BY ref_1.c_4 ORDER BY ref_0.c_0, ref_0.c_1 DESC, ref_0.c_2 DESC, ref_0.c_3 DESC, ref_0.c_4, ref_0.c_5 DESC, ref_0.c_6, ref_0.c_7 DESC, ref_0.c_8 DESC, ref_0.c_9 DESC, ref_0.c_10 DESC, ref_0.c_11 DESC, ref_0.c_12 DESC, ref_0.c_13 DESC, ref_0.c_14 DESC, ref_0.c_15, ref_0.c_16, ref_0.c_17, ref_0.c_18 DESC, ref_0.c_19 DESC, ref_0.c_20 DESC, ref_0.c_21, ref_1.c_0, ref_1.c_1 DESC, ref_1.c_2 DESC, ref_1.c_3 DESC, ref_1.c_4, ref_1.c_5, ref_1.c_6 DESC, ref_1.c_7, ref_1.c_8, ref_1.c_9, ref_1.c_10, ref_1.c_11 DESC, ref_1.c_12, ref_1.c_13, ref_1.c_14, ref_1.c_15 DESC, ref_1.c_16 DESC, ref_1.c_17 DESC, ref_1.c_18 DESC, ref_1.c_19, ref_1.c_20 DESC, ref_1.c_21, ref_2.c_0 DESC, ref_2.c_1 DESC, ref_2.c_2 DESC, ref_2.c_3 DESC, ref_2.c_4, ref_2.c_5 DESC, ref_2.c_6 DESC, ref_2.c_7, ref_2.c_8 DESC, ref_2.c_9 DESC, ref_2.c_10 DESC, ref_2.c_11, ref_2.c_12, ref_2.c_13, ref_2.c_14 DESC, ref_2.c_15 DESC, ref_2.c_16, ref_2.c_17, ref_2.c_18 DESC, ref_2.c_19 DESC, ref_2.c_20 DESC, ref_2.c_21)) AS c_6
FROM ((public.t3 ref_0
CROSS JOIN public.t3 ref_1)
RIGHT JOIN public.t3 ref_2 ON ((ref_1.c_11 = ref_2.c_0)))
WHERE ((( SELECT t0.c3
FROM public.t0
ORDER BY t0.c3
OFFSET 4
LIMIT 1) < ( SELECT t0.c3
FROM public.t0
ORDER BY t0.c3
OFFSET 6
LIMIT 1)) OR NULL::boolean);
ALTER VIEW public.t4 OWNER TO zuming;
--
-- Name: t5; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t5 (
vkey integer,
pkey integer,
c7 text,
c8 integer,
c9 integer,
c10 text,
c11 double precision,
c12 text,
c13 timestamp without time zone,
c14 text,
c15 timestamp without time zone
);
ALTER TABLE public.t5 OWNER TO zuming;
--
-- Data for Name: t0; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t0 (vkey, pkey, c0, c1, c2, c3) FROM stdin;
4 14000 0 \N \N
7 17000 0 97.99 i7N+ 2032-03-24 19:42:31
17 27000 0 \N 2046-07-04 04:03:09
20 30000 -20 -20.82 #}!BA 2020-03-28 07:00:32
21 31000 0 0 2071-12-27 18:48:27
22 32000 25 0 c~xm 2059-09-16 07:48:14
23 33000 -75 -41.97 2m7 2098-10-14 08:31:51
28 38000 18 20.97 \N \N
43 53000 0 -33.25 I) \N
45 55000 45 49.7 N? 2006-02-08 17:46:12
47 57000 0 4.1 \N
58 68000 -14 6.94 1978-11-07 17:18:49
60 70000 0 70.93 :y 1978-05-25 17:30:52
78 88000 -43 38.11 1975-03-20 01:01:10
88 98000 0 19.31 Q,JuM 2046-03-01 07:33:12
92 102000 -17 87.62 8e2$9 2098-03-17 16:01:47
93 103000 0 59.35 2021-02-23 14:24:24
99 109000 \N -93.55 e 2003-12-08 22:21:34
100 110000 \N 0 $|xxP 2086-10-19 01:16:23
105 115000 0 0 2042-04-09 08:01:32
110 120000 -48 8.29 \N \N
112 122000 19 70.3 S \N
119 129000 \N \N 2002-07-19 09:47:53
\.
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t1 (vkey, pkey, c4, c5) FROM stdin;
9 19000 6#)8@ -12
15 25000 0
24 34000 ;p,,[ -4
30 40000 0
36 46000 h2 -2
38 48000 \N -89
41 51000 IR,V 91
42 52000 77N 35
50 60000 a+[e> 22
53 63000 \N
57 67000 `:5 99
62 72000 \N -15
67 77000 6Jl -25
68 78000 ?O 40
69 79000 ;z -36
71 81000 q/8nz -82
77 87000 \N -11
82 92000 \N 0
83 93000 g \N
85 95000 OV -22
91 101000 : \N
94 104000 Q@R 0
96 106000 [IY- 43
97 107000 @ 0
103 113000 \N -38
111 121000 ,Pk 70
114 124000 S7-8Y 42
115 125000 hc 49
120 130000 \N \N
\.
--
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t2 (vkey, pkey, c6) FROM stdin;
2 12000 28.4
3 13000 52.1
5 15000 30.99
8 18000 75.9
10 20000 -79.4
11 21000 0
16 26000 -41.54
19 29000 43.68
25 35000 77.37
27 37000 57.78
29 39000 \N
32 42000 3.64
33 43000 -2.83
37 47000 98.44
40 50000 -82.3
48 58000 64.4
54 64000 -5.71
55 65000 6.65
56 66000 -63.49
59 69000 0
64 74000 4.8
65 75000 -34.9
70 80000 \N
73 83000 -42.3
74 84000 53.83
76 86000 0
79 89000 0
80 90000 45.84
81 91000 0
86 96000 0
95 105000 92.13
98 108000 -7.24
101 111000 -99.86
106 116000 0
108 118000 91.4
113 123000 -18.98
117 127000 \N
118 128000 1.6
121 131000 0
\.
--
-- Data for Name: t5; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t5 (vkey, pkey, c7, c8, c9, c10, c11, c12, c13, c14, c15) FROM stdin;
1 11000 V? -17 -75 X \N WTF \N TJ \N
6 16000 mc -57 76 \N 0 ]5tk) 1993-05-09 13:13:22 \N 2082-12-19 19:30:03
12 22000 0 0 vnX! 0 iRx]U 2011-05-07 00:55:30 Ay1U8 2001-04-17 04:34:26
13 23000 9q -33 \N Z:/wH -57.57 \N 2035-12-11 02:06:16 2087-01-07 18:54:42
14 24000 ] -63 -85 a3 -68.57 tr \N ``-n 2094-02-27 16:51:46
18 28000 hEa 0 5 + 0 $4:` 2067-12-01 07:43:42 PPca 1985-09-20 13:24:48
26 36000 Ylz; 73 -92 z9U 3.36 OgWn@ 2101-08-11 03:10:29 X+ \N
31 41000 87 0 Zd \N 2051-08-02 05:56:02 2101-07-16 06:13:24
34 44000 =?=p \N -12 XZ 0 \N \N 2023-01-06 20:39:23
35 45000 \N -43 -96 1 -43.29 1998-05-03 18:44:20 (X] 1984-01-06 21:37:58
39 49000 tB`h 63 6 ]w&H -14.4 M)^-& 2039-02-10 20:47:53 I 3T9 2030-03-22 03:29:02
44 54000 77l -98 69 5q^c 77.93 9y/W 2061-01-26 15:36:42 3SeJQ 2102-06-14 16:03:19
46 56000 B 0 0 \N 70.84 J<!Q~ 2014-06-05 00:08:59 = $ 2007-07-18 13:22:56
49 59000 S -91 65 KQa3! -93.5 \N 1988-12-20 20:29:30 \N 2080-01-09 12:41:05
51 61000 2;}s -27 \N 0cD@4 -87.74 \N 2074-07-05 08:21:05 O?] 2035-07-25 16:19:40
52 62000 kII -80 64 \N wTYQ 2037-02-01 18:40:59 +R* 2058-11-23 22:28:18
61 71000 @d 0 -75 `Q<DE 0 .+M \N \N 2069-02-22 22:01:44
63 73000 \N \N 21 g Gg{ -60.66 PXI 1990-11-28 18:55:49 Ud \N
66 76000 f02 -14 23 -78.58 ij 1999-08-07 15:51:08 m 2046-01-04 02:31:56
72 82000 \N \N 86 Vw 0 MZ[B 2104-05-21 10:57:41 \N 2071-12-17 07:06:45
75 85000 R8dp -89 79 w; \N \N 1975-10-13 17:28:33 vV^ 2028-07-19 15:55:30
84 94000 } 0 63 mR 66.83 P#80p 2076-05-24 23:06:30 Rnrm 1970-11-02 06:35:18
87 97000 mXL2 0 0 <eQey 77.46 \N 2103-10-28 01:50:49 #X 1981-08-17 11:20:32
89 99000 SGk 0 89 \N 0 WRbI1 1974-02-20 21:22:43 @@ 1995-05-28 06:28:48
90 100000 pXB -71 -20 0 v 1974-10-03 02:59:12 \N 2062-04-28 15:11:49
102 112000 $i 86 0 \N 36.76 At 1972-03-26 03:39:46 4G 1989-12-24 21:45:53
104 114000 -E(0 2 -78 \N 76.9 S+ 1998-10-02 13:14:36 fIoP 2054-04-23 07:57:51
107 117000 7 -94 -45 H* 27.27 2010-11-03 06:40:41 2028-10-15 19:57:22
109 119000 Y1ekT -53 -3 7V -94.42 2009-07-05 06:13:42 p( 2044-06-04 02:32:49
116 126000 Ma -21 59 @KSc -72.57 |B=o9 2018-03-20 03:05:09 !6 2025-12-11 22:19:13
\.
--
-- Name: t0 t0_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t0
ADD CONSTRAINT t0_pkey PRIMARY KEY (vkey);
--
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t1
ADD CONSTRAINT t1_pkey PRIMARY KEY (vkey);
--
-- Name: i0; Type: INDEX; Schema: public; Owner: zuming
--
CREATE INDEX i0 ON public.t0 USING btree (c3 DESC, vkey, c0, c1);
--
-- PostgreSQL database dump complete
--
unexpected.sql (text/plain, 33.9K) download
with
cte_2 AS (select distinct
case when true then ((case when false then (ref_14.c_1) else (ref_14.c_1) end
) & (case when true then (ref_14.c_1) else (ref_14.c_1) end
)) else ((ref_14.c_1) << (ref_14.c_1)) end
as c_0,
case when ((((ref_14.c_2) is not null)
or (not ((ref_14.c_2) is not null)))
or (((ref_14.c_2) is not null) is null)) then (ref_14.c_2) else (case when ((((((ref_14.c_6) in (select
ref_14.c_4 as c_0
from
t3 as ref_81
where null::bool
window w0 as (partition by ref_14.c_4 order by ref_14.c_0 desc, ref_14.c_1 desc, ref_14.c_2 desc, ref_14.c_3 asc, ref_14.c_4 desc, ref_14.c_5 asc, ref_14.c_6 desc, ref_81.c_0 desc, ref_81.c_1 desc, ref_81.c_2 desc, ref_81.c_3 desc, ref_81.c_4 desc, ref_81.c_5 desc, ref_81.c_6 asc, ref_81.c_7 asc, ref_81.c_8 desc, ref_81.c_9 desc, ref_81.c_10 asc, ref_81.c_11 desc, ref_81.c_12 asc, ref_81.c_13 desc, ref_81.c_14 desc, ref_81.c_15 desc, ref_81.c_16 desc, ref_81.c_17 asc, ref_81.c_18 desc, ref_81.c_19 desc, ref_81.c_20 asc, ref_81.c_21 asc)
order by c_0 desc))
or ((null::bool)
or (null::bool)))
or ((ref_14.c_1) < (ref_14.c_1)))
or ((']da') > (ref_14.c_2)))
and (not ((null::bool)
and ((ref_14.c_2) not like 'H_b')))) then (case when ((ref_14.c_1) is not null) then (ref_14.c_2) else (ref_14.c_2) end
) else (ref_14.c_3) end
) end
as c_1,
case when ((((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1))
and (not ((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1))))
and (((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1)) is not null)) then (case when (null::bool) then (ref_14.c_1) else (nullif(ref_14.c_1, 0)) end
) else (ref_14.c_1) end
as c_2,
case when false then (ref_14.c_0) else (ref_14.c_4) end
as c_3,
case when true then (ref_14.c_5) else ((ref_14.c_3) <= (ref_14.c_3)) end
as c_4,
case when false then (ref_14.c_1) else (null::int4) end
as c_5,
case when true then (ref_14.c_5) else (((ref_14.c_4) is null)
and ((pg_catalog.btrim(ref_14.c_3,
ref_14.c_3)) ~~* (ref_14.c_3))) end
as c_6,
case when false then ((pg_catalog.timestamp(null::timestamp,
ref_14.c_1)) <> (pg_catalog.timestamp_smaller(make_timestamp(2029, 11, 19, 20, 38, 38),
make_timestamp(2029, 3, 7, 10, 28, 28)))) else (ref_14.c_5) end
as c_7,
case when false then (ref_14.c_6) else (ref_14.c_4) end
as c_8,
case when false then ((coalesce(ref_14.c_2, pg_catalog.rpad(ref_14.c_3,
ref_14.c_1,
ref_14.c_3))) not like '_%_') else ((case when true then (case when (((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_14.c_2) else (ref_14.c_2) end
) not like 'Rn/')) then (case when (((false::bool)
and (not (false::bool)))
and ((false::bool) is not null)) then (ref_14.c_2) else (null::text) end
) else (case when ((((ref_14.c_3) > (ref_14.c_3))
and (not ((ref_14.c_3) > (ref_14.c_3))))
and (((ref_14.c_3) > (ref_14.c_3)) is not null)) then (ref_14.c_3) else (ref_14.c_2) end
) end
) else (ref_14.c_2) end
) ~* (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_14.c_2) else (ref_14.c_3) end
)) end
as c_9
from
t4 as ref_14
),
cte_3 AS (select
case when true then (subq_1.c_0) else (subq_1.c_9) end
as c_0,
case when true then (case when (((case when true then (case when ((((case when true then (subq_1.c_3) else (subq_1.c_9) end
) >= (case when false then (17) else (-25) end
))
and ((case when true then (subq_1.c_3) else (subq_1.c_9) end
) <= (case when ((((subq_1.c_5) not like '_>_')
and (not ((subq_1.c_5) not like '_>_')))
and (((subq_1.c_5) not like '_>_') is not null)) then (subq_1.c_3) else (subq_1.c_3) end
)))) then (case when ((((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0)))
or (not ((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0)))))
or (((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0))) is null)) then (subq_1.c_6) else (subq_1.c_6) end
) else (case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_6) else (subq_1.c_1) end
) end
) else (case when ((((subq_1.c_1) not like 'Y.m')
and ((null::bool)
or (not ((subq_1.c_6) > (subq_1.c_5)))))
or ((subq_1.c_5) <> (subq_1.c_5))) then (subq_1.c_1) else (subq_1.c_6) end
) end
) <> (case when ((((subq_1.c_9) > (subq_1.c_9))
or (not ((subq_1.c_9) > (subq_1.c_9))))
or (((subq_1.c_9) > (subq_1.c_9)) is null)) then (subq_1.c_5) else (subq_1.c_5) end
))) then (case when true then (case when ((not ((not (((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3))
or (not ((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3))))
or (((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3)) is null))
and ((case when (((((subq_1.c_0) > (subq_1.c_0))
or (null::bool))
and (not (((subq_1.c_0) > (subq_1.c_0))
or (null::bool))))
and ((((subq_1.c_0) > (subq_1.c_0))
or (null::bool)) is not null)) then (subq_1.c_7) else (subq_1.c_4) end
) <> (case when (((((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6)))
or (not (((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6)))))
or ((((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6))) is null)) then (subq_1.c_2) else (subq_1.c_7) end
))))
and (not (((((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp))
or (not ((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp))))
or (((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp)) is null))
and (not ((not ((((true::bool)
and (not (true::bool)))
and ((true::bool) is not null))
or (not (((((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7)))
or (not ((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7)))))
or (((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7))) is null))
and ((case when ((((0) is not null)
and (not ((0) is not null)))
and (((0) is not null) is not null)) then (subq_1.c_6) else (subq_1.c_6) end
) = (case when (((null::bool)
or (not (null::bool)))
or ((null::bool) is null)) then (subq_1.c_1) else (subq_1.c_1) end
))))))
or (not (((((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54)))
or (not ((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54)))))
or (((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54))) is null))
and (((case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_3) else (subq_1.c_9) end
) >= (case when ((((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m')))
and (not ((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m')))))
and (((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m'))) is not null)) then (subq_1.c_9) else (subq_1.c_3) end
))
and ((case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_3) else (subq_1.c_9) end
) <= (case when (((((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool))
and (not (((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool))))
and ((((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool)) is not null)) then (subq_1.c_0) else (subq_1.c_9) end
)))))))))))) then (case when (((not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5))))
and (not (not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5))))))
and ((not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5)))) is not null)) then (subq_1.c_5) else (subq_1.c_6) end
) else (case when (((not (false::bool))
and (not (not (false::bool))))
and ((not (false::bool)) is not null)) then (case when (false::bool) then (subq_1.c_1) else (subq_1.c_1) end
) else (subq_1.c_6) end
) end
) else ((subq_1.c_6) || (subq_1.c_5)) end
) else (case when ((((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6)))
and (not ((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6)))))
and (((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6))) is not null)) then (subq_1.c_5) else (subq_1.c_1) end
) end
) else (case when (null::bool) then (subq_1.c_6) else (subq_1.c_6) end
) end
as c_1
from
(select
case when ((((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30)))
or (not ((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30)))))
or (((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30))) is null)) then (-0) else (ref_16.c_1) end
as c_0,
case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_15.c_2) else (ref_15.c_3) end
as c_1,
case when ((((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool)))
or (not ((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool)))))
or (((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool))) is null)) then (ref_16.c_4) else (ref_16.c_4) end
as c_2,
case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (ref_15.c_1) else (ref_15.c_1) end
as c_3,
case when ((((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1)))
or (not ((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1)))))
or (((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1))) is null)) then (ref_16.c_4) else (0.0) end
as c_4,
case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (ref_16.c_2) else (ref_16.c_2) end
as c_5,
case when ((((('/zI,') < (ref_16.c_3)) = (not (false::bool)))
or (not ((('/zI,') < (ref_16.c_3)) = (not (false::bool)))))
or (((('/zI,') < (ref_16.c_3)) = (not (false::bool))) is null)) then (ref_15.c_2) else (ref_15.c_2) end
as c_6,
case when ((((ref_15.c_3) <= (ref_15.c_3))
and (not ((ref_15.c_3) <= (ref_15.c_3))))
and (((ref_15.c_3) <= (ref_15.c_3)) is not null)) then (ref_15.c_4) else (ref_16.c_6) end
as c_7,
case when ((((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1))
or (not ((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1))))
or (((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1)) is null)) then (ref_15.c_5) else (false::bool) end
as c_8,
case when (((exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool))
or (not (exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool))))
or ((exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool)) is null)) then (ref_15.c_1) else (ref_16.c_1) end
as c_9
from
(t4 as ref_15
inner join t4 as ref_16
on (ref_15.c_0 = ref_16.c_0 ))
where not ((false::bool)
or (not ((true::bool)
and (((((null::int4) between (ref_16.c_1) and (ref_16.c_1))
or (not ((null::int4) between (ref_16.c_1) and (ref_16.c_1))))
or (((null::int4) between (ref_16.c_1) and (ref_16.c_1)) is null))
and ((case when ((((ref_15.c_3) not like '__S')
and (not ((ref_15.c_3) not like '__S')))
and (((ref_15.c_3) not like '__S') is not null)) then (ref_15.c_1) else (ref_16.c_1) end
) > (case when ((((ref_15.c_2) !~~* (ref_15.c_3))
or (not ((ref_15.c_2) !~~* (ref_15.c_3))))
or (((ref_15.c_2) !~~* (ref_15.c_3)) is null)) then (ref_15.c_1) else (ref_15.c_1) end
))))))) as subq_1
)
select
case when ((((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null))
or (not ((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null))))
or (((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null)) is null)) then (case when (((((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool))
and (not ((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool))))
and (((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool)) is not null))
or ((true::bool) in (select
case when (((((true::bool)
or (not (true::bool)))
or ((true::bool) is null))
and ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (subq_2.c_9) else (ref_29.c_11) end
) like 'u6_')) is null) then (false::bool) else ((((true::bool)
or (not (true::bool)))
or ((true::bool) is null))
and ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (subq_2.c_9) else (ref_29.c_11) end
) like 'u6_')) end
as c0
from
t3 as ref_29
where true::bool))) then (((ref_22.c1) = (ref_22.c1))
or (true::bool)) else (null::bool) end
) else (true::bool) end
as c_2,
((((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2)))
and (not ((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2)))))
and (((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2))) is not null))
or (not ((not (((((ref_22.c2) > (subq_2.c_9))
and (not ((ref_22.c2) > (subq_2.c_9))))
and (((ref_22.c2) > (subq_2.c_9)) is not null))
or ((true::bool) in (select
case when ((null::bool) is null) then (false::bool) else (null::bool) end
as c0
from
t3 as ref_30
where true::bool))))
or (not (((((subq_2.c_9) <= (subq_2.c_9))
and (not ((subq_2.c_9) <= (subq_2.c_9))))
and (((subq_2.c_9) <= (subq_2.c_9)) is not null))
or ((case when ((((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1))
or (not ((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1))))
or (((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1)) is null)) then (case when (((((ref_22.c0) is null)
or (not ((ref_22.c0) is null)))
or (((ref_22.c0) is null) is null))
and ((case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else (ref_22.c1) end
) >= (case when (((((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3)))
and (not (((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3)))))
and ((((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3))) is not null)) then (ref_22.c1) else (ref_22.c1) end
))) then (case when (((((subq_2.c_5) = (subq_2.c_4))
and (null::bool))
and (not (((subq_2.c_5) = (subq_2.c_4))
and (null::bool))))
and ((((subq_2.c_5) = (subq_2.c_4))
and (null::bool)) is not null)) then (subq_2.c_11) else (subq_2.c_2) end
) else (case when (((null::bool)
or (not (null::bool)))
or ((null::bool) is null)) then (subq_2.c_11) else (subq_2.c_9) end
) end
) else (subq_2.c_2) end
) ^@ (case when ((((subq_2.c_2) ~* (subq_2.c_11))
and (not ((subq_2.c_2) ~* (subq_2.c_11))))
and (((subq_2.c_2) ~* (subq_2.c_11)) is not null)) then (pg_catalog.rpad(subq_2.c_11,
subq_2.c_10)) else (subq_2.c_8) end
)))))) as c_3,
((((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8))
or (not ((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8))))
or (((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8)) is null))
and (not ((not (((((ref_22.c3) <= (ref_22.c3))
and (not ((ref_22.c3) <= (ref_22.c3))))
and (((ref_22.c3) <= (ref_22.c3)) is not null))
or ((case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (subq_2.c_2) else (subq_2.c_2) end
) not like '_%%')))
and (not (((((subq_2.c_9) = (subq_2.c_9))
or (not ((subq_2.c_9) = (subq_2.c_9))))
or (((subq_2.c_9) = (subq_2.c_9)) is null))
and (not ((not (((((ref_22.c2) !~ (subq_2.c_8))
and (not ((ref_22.c2) !~ (subq_2.c_8))))
and (((ref_22.c2) !~ (subq_2.c_8)) is not null))
or (not ((not (null::bool))
and (not (null::bool))))))
or (not ((((null::bool)
and (not (null::bool)))
and ((null::bool) is not null))
or ((case when ((((subq_2.c_9) !~* (subq_2.c_9))
and (not ((subq_2.c_9) !~* (subq_2.c_9))))
and (((subq_2.c_9) !~* (subq_2.c_9)) is not null)) then (subq_2.c_5) else (subq_2.c_1) end
) < (case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (96) else (pg_catalog.gin_compare_jsonb(case when (((false::bool)
and (not (false::bool)))
and ((false::bool) is not null)) then (subq_2.c_2) else (null::text) end
,
case when ((((false::bool) is null)
and (not ((false::bool) is null)))
and (((false::bool) is null) is not null)) then (subq_2.c_9) else (subq_2.c_2) end
)) end
)))))))))) as c_4,
case when ((((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3)))
or (not ((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3)))))
or (((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3))) is null)) then ((case when ((((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null))
and (not ((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null))))
and (((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null)) is not null)) then (nullif(ref_22.c1, ref_22.c1)) else (pg_catalog.float8smaller(case when ((((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4))
and (not ((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4))))
and (((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4)) is not null)) then (ref_22.c1) else (ref_22.c1) end
,
case when ((((subq_2.c_1) = (ref_22.vkey))
or (not ((subq_2.c_1) = (ref_22.vkey))))
or (((subq_2.c_1) = (ref_22.vkey)) is null)) then (ref_22.c1) else (ref_22.c1) end
)) end
) / (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_22.c1) else (ref_22.c1) end
) / (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else (ref_22.c1) end
)) end
)) else (pg_catalog.cotd(ref_22.c1)) end
as c_5
from
((select
case when false then (ref_17.c5) else (ref_17.pkey) end
as c_0,
case when false then (ref_18.c_0) else (ref_18.c_0) end
as c_1,
case when true then (ref_18.c_1) else (ref_18.c_1) end
as c_2,
case when false then (ref_17.c5) else (ref_17.vkey) end
as c_3,
case when true then (ref_17.c5) else (ref_17.c5) end
as c_4,
case when false then (ref_18.c_0) else (ref_18.c_0) end
as c_5,
case when false then (ref_17.vkey) else (ref_17.pkey) end
as c_6,
case when true then (ref_18.c_0) else (ref_17.c5) end
as c_7,
case when false then (ref_18.c_1) else (ref_18.c_1) end
as c_8,
case when true then (ref_18.c_1) else (ref_18.c_1) end
as c_9,
case when false then (ref_17.pkey) else (ref_17.vkey) end
as c_10,
case when false then (ref_18.c_1) else (ref_18.c_1) end
as c_11
from
(t1 as ref_17
cross join cte_3 as ref_18
)
where not ((false::bool)
or (not ((true::bool)
and (null::bool))))) as subq_2
left outer join (t0 as ref_22
left outer join (select
case when ((((ref_23.c_8) > (ref_23.c_3))
and (not ((ref_23.c_8) > (ref_23.c_3))))
and (((ref_23.c_8) > (ref_23.c_3)) is not null)) then (ref_23.c_2) else (ref_23.c_2) end
as c_0,
case when ((((2) between (ref_23.c_2) and (ref_23.c_0))
or (not ((2) between (ref_23.c_2) and (ref_23.c_0))))
or (((2) between (ref_23.c_2) and (ref_23.c_0)) is null)) then (ref_23.c_6) else (null::bool) end
as c_1,
case when ((((ref_23.c_0) <= (ref_23.c_0))
and (not ((ref_23.c_0) <= (ref_23.c_0))))
and (((ref_23.c_0) <= (ref_23.c_0)) is not null)) then (true::bool) else (ref_23.c_9) end
as c_2,
((case when ((((ref_23.c_1) <= (ref_23.c_1))
or (not ((ref_23.c_1) <= (ref_23.c_1))))
or (((ref_23.c_1) <= (ref_23.c_1)) is null)) then (ref_23.c_3) else (ref_23.c_3) end
) <= (case when (((((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool))
or (not (((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool))))
or ((((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool)) is null)) then (ref_23.c_8) else (ref_23.c_8) end
)) as c_3
from
cte_2 as ref_23
where not ((false::bool)
or (not ((true::bool)
and ((((false::bool)
and (not (false::bool)))
and ((false::bool) is not null))
or ((case when (((((true::bool)
and (null::bool))
and (false::bool))
and (not (((true::bool)
and (null::bool))
and (false::bool))))
and ((((true::bool)
and (null::bool))
and (false::bool)) is not null)) then (ref_23.c_0) else (ref_23.c_5) end
) is not null)))))) as subq_3
on (ref_22.pkey = subq_3.c_0 ))
on (subq_2.c_0 = ref_22.vkey ))
* Fwd: Server closed the connection unexpectedly (memory leak)
2023-06-23 16:06 Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
@ 2023-06-23 16:20 ` Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
1 sibling, 0 replies; 19+ messages in thread
From: Zu-Ming Jiang @ 2023-06-23 16:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Sorry that the test cases is a bit large. It is difficult for me to
reduce it to the minimal, because when I removed some parts, the bug
easily disappears.
Best wishes,
Zuming Jiang
------------------------------------------------------------------------
*From:* Zu-Ming Jiang [mailto:zuming.jiang@inf.ethz.ch]
*Sent:* Friday, June 23, 2023 at 6:06 PM
*To:* pgsql-bugs@lists.postgresql.org
*Subject:* Server closed the connection unexpectedly (memory leak)
My fuzzer finds a bug in Postgres, which makes the connection closed
unexpectedly. This bug still can be reproduced even after applying the
patch for
https://www.postgresql.org/message-id/flat/b2bd02dff61af15e3526293e2771f874cf2a3be7.camel@cybertec.a...
--- Steps to reproduce the bug ---
1. /usr/local/pgsql/bin/psql -d postgres -c "drop database redb";
2. /usr/local/pgsql/bin/psql -d postgres -c "create database redb";
3. /usr/local/pgsql/bin/psql -d redb -f pg_testdb55_bk.sql;
4. /usr/local/pgsql/bin/psql -d redb -f unexpected.sql
pg_testdb55_bk.sql and unexpected.sql are attached.
--- Expected behavior ---
No error triggered.
--- Actual behavior ---
The test case makes the connection closed unexpectedly:
psql:unexpected.sql:695: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:unexpected.sql:695: error: connection to server was lost
--- Postgres version ---
Github commit: f5c446e3367527f9db1506d7c38d2f56e20950b6
Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
Attachments:
pg_testdb55_bk.sql (text/plain, 13.4K) download
--
-- PostgreSQL database dump
--
-- Dumped from database version 16beta1
-- Dumped by pg_dump version 16beta1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: t0; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t0 (
vkey integer NOT NULL,
pkey integer,
c0 integer,
c1 double precision,
c2 text,
c3 timestamp without time zone
);
ALTER TABLE public.t0 OWNER TO zuming;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t1 (
vkey integer NOT NULL,
pkey integer,
c4 text,
c5 integer
);
ALTER TABLE public.t1 OWNER TO zuming;
--
-- Name: t2; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t2 (
vkey integer,
pkey integer,
c6 double precision
);
ALTER TABLE public.t2 OWNER TO zuming;
--
-- Name: t3; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t3 AS
SELECT
CASE
WHEN (c4 !~~ '%%_'::text) THEN ( SELECT t0.c2
FROM public.t0
ORDER BY t0.c2
OFFSET 1
LIMIT 1)
ELSE c4
END AS c_0,
vkey AS c_1,
vkey AS c_2,
c4 AS c_3,
( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 4
LIMIT 1) AS c_4,
CASE
WHEN ((vkey >= ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 4
LIMIT 1)) AND (vkey <= int4inc(vkey))) THEN pkey
ELSE (vkey | ( SELECT t0.vkey
FROM public.t0
ORDER BY t0.vkey
OFFSET 64
LIMIT 1))
END AS c_5,
regexp_count(c4, c4, c5, c4) AS c_6,
COALESCE(c4, c4) AS c_7,
pkey AS c_8,
dpow(var_samp(float8up(( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 6
LIMIT 1))) OVER (PARTITION BY c5 ORDER BY vkey DESC, pkey, c4 DESC, c5 DESC), NULL::double precision) AS c_9,
dround(cos(
CASE
WHEN (( SELECT bit_or(t1.vkey) AS bit_or
FROM public.t1) <> vkey) THEN ( SELECT t2.c6
FROM public.t2
ORDER BY t2.c6
OFFSET 43
LIMIT 1)
ELSE atanh(( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 51
LIMIT 1))
END)) AS c_10,
c4 AS c_11,
vkey AS c_12,
"substring"(md5(c4), pkey) AS c_13,
vkey AS c_14,
(((EXISTS ( SELECT ref_2.c2 AS c_0,
( SELECT min(t0.c3) AS min
FROM public.t0) AS c_1,
ref_2.c2 AS c_2,
ref_2.vkey AS c_3,
( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 3
LIMIT 1) AS c_4,
ref_2.vkey AS c_5,
( SELECT t0.c1
FROM public.t0
ORDER BY t0.c1
OFFSET 6
LIMIT 1) AS c_6,
( SELECT max(t2.vkey) AS max
FROM public.t2) AS c_7,
ref_2.vkey AS c_8
FROM public.t0 ref_2
WHERE (NULL::boolean AND true))) AND false) OR true OR (EXISTS ( SELECT DISTINCT (ref_0.c4 = ref_0.c4) AS c_0,
false AS c_1
FROM public.t2 ref_3
WHERE (true OR (NOT true))
UNION
SELECT false AS c_0,
(ref_4.c2 = ref_0.c4) AS c_1
FROM public.t0 ref_4
WHERE ((( SELECT t0.vkey
FROM public.t0
ORDER BY t0.vkey
OFFSET 5
LIMIT 1) <> ref_0.vkey) AND NULL::boolean))) OR (c4 ~>~ c4) OR (c4 ~~ 'mjo'::text)) AS c_15,
CASE
WHEN (c4 <= ( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 5
LIMIT 1)) THEN ((vkey >= ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 6
LIMIT 1)) AND (vkey <= c5))
ELSE ((( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 4
LIMIT 1) !~~ 'L_%'::text) AND (false OR (( SELECT max(t0.c2) AS max
FROM public.t0) !~ c4)))
END AS c_16,
bit_and(pkey) OVER (PARTITION BY c5 ORDER BY vkey, pkey, c4 DESC, c5 DESC) AS c_17,
(vkey + ( SELECT t1.vkey
FROM public.t1
ORDER BY t1.vkey
OFFSET 3
LIMIT 1)) AS c_18,
( SELECT t1.pkey
FROM public.t1
ORDER BY t1.pkey
OFFSET 4
LIMIT 1) AS c_19,
pkey AS c_20,
similar_to_escape(c4, c4) AS c_21
FROM public.t1 ref_0
WHERE (bool((( SELECT max(t0.vkey) AS max
FROM public.t0) >> c5)) IN ( SELECT (NOT false) AS c_0
FROM public.t1 ref_1
WHERE ((0.0 < '-55.30'::numeric) AND NULL::boolean AND (true OR (ref_0.c5 <> ref_1.pkey)))));
ALTER VIEW public.t3 OWNER TO zuming;
--
-- Name: t4; Type: VIEW; Schema: public; Owner: zuming
--
CREATE VIEW public.t4 AS
SELECT DISTINCT stddev(ref_0.c_9) OVER (PARTITION BY ref_0.c_18, ref_2.c_20 ORDER BY ref_0.c_0 DESC, ref_0.c_1 DESC, ref_0.c_2 DESC, ref_0.c_3, ref_0.c_4, ref_0.c_5, ref_0.c_6 DESC, ref_0.c_7 DESC, ref_0.c_8, ref_0.c_9, ref_0.c_10 DESC, ref_0.c_11 DESC, ref_0.c_12 DESC, ref_0.c_13 DESC, ref_0.c_14 DESC, ref_0.c_15 DESC, ref_0.c_16, ref_0.c_17 DESC, ref_0.c_18 DESC, ref_0.c_19, ref_0.c_20 DESC, ref_0.c_21, ref_1.c_0, ref_1.c_1, ref_1.c_2 DESC, ref_1.c_3 DESC, ref_1.c_4, ref_1.c_5, ref_1.c_6, ref_1.c_7 DESC, ref_1.c_8, ref_1.c_9, ref_1.c_10 DESC, ref_1.c_11, ref_1.c_12, ref_1.c_13, ref_1.c_14 DESC, ref_1.c_15 DESC, ref_1.c_16, ref_1.c_17, ref_1.c_18 DESC, ref_1.c_19 DESC, ref_1.c_20 DESC, ref_1.c_21 DESC, ref_2.c_0 DESC, ref_2.c_1 DESC, ref_2.c_2, ref_2.c_3, ref_2.c_4 DESC, ref_2.c_5, ref_2.c_6, ref_2.c_7 DESC, ref_2.c_8, ref_2.c_9 DESC, ref_2.c_10 DESC, ref_2.c_11 DESC, ref_2.c_12 DESC, ref_2.c_13, ref_2.c_14 DESC, ref_2.c_15 DESC, ref_2.c_16 DESC, ref_2.c_17, ref_2.c_18, ref_2.c_19, ref_2.c_20, ref_2.c_21 DESC) AS c_0,
ref_0.c_8 AS c_1,
ref_0.c_3 AS c_2,
CASE
WHEN NULL::boolean THEN ( SELECT t1.c4
FROM public.t1
ORDER BY t1.c4
OFFSET 33
LIMIT 1)
ELSE "left"('O'::text, ref_1.c_5)
END AS c_3,
COALESCE(( SELECT var_pop(t0.c1) AS var_pop
FROM public.t0), ref_0.c_10) AS c_4,
(ref_1.c_18 <= ref_2.c_19) AS c_5,
acosh(min(ref_2.c_9) OVER (PARTITION BY ref_1.c_4 ORDER BY ref_0.c_0, ref_0.c_1 DESC, ref_0.c_2 DESC, ref_0.c_3 DESC, ref_0.c_4, ref_0.c_5 DESC, ref_0.c_6, ref_0.c_7 DESC, ref_0.c_8 DESC, ref_0.c_9 DESC, ref_0.c_10 DESC, ref_0.c_11 DESC, ref_0.c_12 DESC, ref_0.c_13 DESC, ref_0.c_14 DESC, ref_0.c_15, ref_0.c_16, ref_0.c_17, ref_0.c_18 DESC, ref_0.c_19 DESC, ref_0.c_20 DESC, ref_0.c_21, ref_1.c_0, ref_1.c_1 DESC, ref_1.c_2 DESC, ref_1.c_3 DESC, ref_1.c_4, ref_1.c_5, ref_1.c_6 DESC, ref_1.c_7, ref_1.c_8, ref_1.c_9, ref_1.c_10, ref_1.c_11 DESC, ref_1.c_12, ref_1.c_13, ref_1.c_14, ref_1.c_15 DESC, ref_1.c_16 DESC, ref_1.c_17 DESC, ref_1.c_18 DESC, ref_1.c_19, ref_1.c_20 DESC, ref_1.c_21, ref_2.c_0 DESC, ref_2.c_1 DESC, ref_2.c_2 DESC, ref_2.c_3 DESC, ref_2.c_4, ref_2.c_5 DESC, ref_2.c_6 DESC, ref_2.c_7, ref_2.c_8 DESC, ref_2.c_9 DESC, ref_2.c_10 DESC, ref_2.c_11, ref_2.c_12, ref_2.c_13, ref_2.c_14 DESC, ref_2.c_15 DESC, ref_2.c_16, ref_2.c_17, ref_2.c_18 DESC, ref_2.c_19 DESC, ref_2.c_20 DESC, ref_2.c_21)) AS c_6
FROM ((public.t3 ref_0
CROSS JOIN public.t3 ref_1)
RIGHT JOIN public.t3 ref_2 ON ((ref_1.c_11 = ref_2.c_0)))
WHERE ((( SELECT t0.c3
FROM public.t0
ORDER BY t0.c3
OFFSET 4
LIMIT 1) < ( SELECT t0.c3
FROM public.t0
ORDER BY t0.c3
OFFSET 6
LIMIT 1)) OR NULL::boolean);
ALTER VIEW public.t4 OWNER TO zuming;
--
-- Name: t5; Type: TABLE; Schema: public; Owner: zuming
--
CREATE TABLE public.t5 (
vkey integer,
pkey integer,
c7 text,
c8 integer,
c9 integer,
c10 text,
c11 double precision,
c12 text,
c13 timestamp without time zone,
c14 text,
c15 timestamp without time zone
);
ALTER TABLE public.t5 OWNER TO zuming;
--
-- Data for Name: t0; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t0 (vkey, pkey, c0, c1, c2, c3) FROM stdin;
4 14000 0 \N \N
7 17000 0 97.99 i7N+ 2032-03-24 19:42:31
17 27000 0 \N 2046-07-04 04:03:09
20 30000 -20 -20.82 #}!BA 2020-03-28 07:00:32
21 31000 0 0 2071-12-27 18:48:27
22 32000 25 0 c~xm 2059-09-16 07:48:14
23 33000 -75 -41.97 2m7 2098-10-14 08:31:51
28 38000 18 20.97 \N \N
43 53000 0 -33.25 I) \N
45 55000 45 49.7 N? 2006-02-08 17:46:12
47 57000 0 4.1 \N
58 68000 -14 6.94 1978-11-07 17:18:49
60 70000 0 70.93 :y 1978-05-25 17:30:52
78 88000 -43 38.11 1975-03-20 01:01:10
88 98000 0 19.31 Q,JuM 2046-03-01 07:33:12
92 102000 -17 87.62 8e2$9 2098-03-17 16:01:47
93 103000 0 59.35 2021-02-23 14:24:24
99 109000 \N -93.55 e 2003-12-08 22:21:34
100 110000 \N 0 $|xxP 2086-10-19 01:16:23
105 115000 0 0 2042-04-09 08:01:32
110 120000 -48 8.29 \N \N
112 122000 19 70.3 S \N
119 129000 \N \N 2002-07-19 09:47:53
\.
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t1 (vkey, pkey, c4, c5) FROM stdin;
9 19000 6#)8@ -12
15 25000 0
24 34000 ;p,,[ -4
30 40000 0
36 46000 h2 -2
38 48000 \N -89
41 51000 IR,V 91
42 52000 77N 35
50 60000 a+[e> 22
53 63000 \N
57 67000 `:5 99
62 72000 \N -15
67 77000 6Jl -25
68 78000 ?O 40
69 79000 ;z -36
71 81000 q/8nz -82
77 87000 \N -11
82 92000 \N 0
83 93000 g \N
85 95000 OV -22
91 101000 : \N
94 104000 Q@R 0
96 106000 [IY- 43
97 107000 @ 0
103 113000 \N -38
111 121000 ,Pk 70
114 124000 S7-8Y 42
115 125000 hc 49
120 130000 \N \N
\.
--
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t2 (vkey, pkey, c6) FROM stdin;
2 12000 28.4
3 13000 52.1
5 15000 30.99
8 18000 75.9
10 20000 -79.4
11 21000 0
16 26000 -41.54
19 29000 43.68
25 35000 77.37
27 37000 57.78
29 39000 \N
32 42000 3.64
33 43000 -2.83
37 47000 98.44
40 50000 -82.3
48 58000 64.4
54 64000 -5.71
55 65000 6.65
56 66000 -63.49
59 69000 0
64 74000 4.8
65 75000 -34.9
70 80000 \N
73 83000 -42.3
74 84000 53.83
76 86000 0
79 89000 0
80 90000 45.84
81 91000 0
86 96000 0
95 105000 92.13
98 108000 -7.24
101 111000 -99.86
106 116000 0
108 118000 91.4
113 123000 -18.98
117 127000 \N
118 128000 1.6
121 131000 0
\.
--
-- Data for Name: t5; Type: TABLE DATA; Schema: public; Owner: zuming
--
COPY public.t5 (vkey, pkey, c7, c8, c9, c10, c11, c12, c13, c14, c15) FROM stdin;
1 11000 V? -17 -75 X \N WTF \N TJ \N
6 16000 mc -57 76 \N 0 ]5tk) 1993-05-09 13:13:22 \N 2082-12-19 19:30:03
12 22000 0 0 vnX! 0 iRx]U 2011-05-07 00:55:30 Ay1U8 2001-04-17 04:34:26
13 23000 9q -33 \N Z:/wH -57.57 \N 2035-12-11 02:06:16 2087-01-07 18:54:42
14 24000 ] -63 -85 a3 -68.57 tr \N ``-n 2094-02-27 16:51:46
18 28000 hEa 0 5 + 0 $4:` 2067-12-01 07:43:42 PPca 1985-09-20 13:24:48
26 36000 Ylz; 73 -92 z9U 3.36 OgWn@ 2101-08-11 03:10:29 X+ \N
31 41000 87 0 Zd \N 2051-08-02 05:56:02 2101-07-16 06:13:24
34 44000 =?=p \N -12 XZ 0 \N \N 2023-01-06 20:39:23
35 45000 \N -43 -96 1 -43.29 1998-05-03 18:44:20 (X] 1984-01-06 21:37:58
39 49000 tB`h 63 6 ]w&H -14.4 M)^-& 2039-02-10 20:47:53 I 3T9 2030-03-22 03:29:02
44 54000 77l -98 69 5q^c 77.93 9y/W 2061-01-26 15:36:42 3SeJQ 2102-06-14 16:03:19
46 56000 B 0 0 \N 70.84 J<!Q~ 2014-06-05 00:08:59 = $ 2007-07-18 13:22:56
49 59000 S -91 65 KQa3! -93.5 \N 1988-12-20 20:29:30 \N 2080-01-09 12:41:05
51 61000 2;}s -27 \N 0cD@4 -87.74 \N 2074-07-05 08:21:05 O?] 2035-07-25 16:19:40
52 62000 kII -80 64 \N wTYQ 2037-02-01 18:40:59 +R* 2058-11-23 22:28:18
61 71000 @d 0 -75 `Q<DE 0 .+M \N \N 2069-02-22 22:01:44
63 73000 \N \N 21 g Gg{ -60.66 PXI 1990-11-28 18:55:49 Ud \N
66 76000 f02 -14 23 -78.58 ij 1999-08-07 15:51:08 m 2046-01-04 02:31:56
72 82000 \N \N 86 Vw 0 MZ[B 2104-05-21 10:57:41 \N 2071-12-17 07:06:45
75 85000 R8dp -89 79 w; \N \N 1975-10-13 17:28:33 vV^ 2028-07-19 15:55:30
84 94000 } 0 63 mR 66.83 P#80p 2076-05-24 23:06:30 Rnrm 1970-11-02 06:35:18
87 97000 mXL2 0 0 <eQey 77.46 \N 2103-10-28 01:50:49 #X 1981-08-17 11:20:32
89 99000 SGk 0 89 \N 0 WRbI1 1974-02-20 21:22:43 @@ 1995-05-28 06:28:48
90 100000 pXB -71 -20 0 v 1974-10-03 02:59:12 \N 2062-04-28 15:11:49
102 112000 $i 86 0 \N 36.76 At 1972-03-26 03:39:46 4G 1989-12-24 21:45:53
104 114000 -E(0 2 -78 \N 76.9 S+ 1998-10-02 13:14:36 fIoP 2054-04-23 07:57:51
107 117000 7 -94 -45 H* 27.27 2010-11-03 06:40:41 2028-10-15 19:57:22
109 119000 Y1ekT -53 -3 7V -94.42 2009-07-05 06:13:42 p( 2044-06-04 02:32:49
116 126000 Ma -21 59 @KSc -72.57 |B=o9 2018-03-20 03:05:09 !6 2025-12-11 22:19:13
\.
--
-- Name: t0 t0_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t0
ADD CONSTRAINT t0_pkey PRIMARY KEY (vkey);
--
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: zuming
--
ALTER TABLE ONLY public.t1
ADD CONSTRAINT t1_pkey PRIMARY KEY (vkey);
--
-- Name: i0; Type: INDEX; Schema: public; Owner: zuming
--
CREATE INDEX i0 ON public.t0 USING btree (c3 DESC, vkey, c0, c1);
--
-- PostgreSQL database dump complete
--
unexpected.sql (text/plain, 33.9K) download
with
cte_2 AS (select distinct
case when true then ((case when false then (ref_14.c_1) else (ref_14.c_1) end
) & (case when true then (ref_14.c_1) else (ref_14.c_1) end
)) else ((ref_14.c_1) << (ref_14.c_1)) end
as c_0,
case when ((((ref_14.c_2) is not null)
or (not ((ref_14.c_2) is not null)))
or (((ref_14.c_2) is not null) is null)) then (ref_14.c_2) else (case when ((((((ref_14.c_6) in (select
ref_14.c_4 as c_0
from
t3 as ref_81
where null::bool
window w0 as (partition by ref_14.c_4 order by ref_14.c_0 desc, ref_14.c_1 desc, ref_14.c_2 desc, ref_14.c_3 asc, ref_14.c_4 desc, ref_14.c_5 asc, ref_14.c_6 desc, ref_81.c_0 desc, ref_81.c_1 desc, ref_81.c_2 desc, ref_81.c_3 desc, ref_81.c_4 desc, ref_81.c_5 desc, ref_81.c_6 asc, ref_81.c_7 asc, ref_81.c_8 desc, ref_81.c_9 desc, ref_81.c_10 asc, ref_81.c_11 desc, ref_81.c_12 asc, ref_81.c_13 desc, ref_81.c_14 desc, ref_81.c_15 desc, ref_81.c_16 desc, ref_81.c_17 asc, ref_81.c_18 desc, ref_81.c_19 desc, ref_81.c_20 asc, ref_81.c_21 asc)
order by c_0 desc))
or ((null::bool)
or (null::bool)))
or ((ref_14.c_1) < (ref_14.c_1)))
or ((']da') > (ref_14.c_2)))
and (not ((null::bool)
and ((ref_14.c_2) not like 'H_b')))) then (case when ((ref_14.c_1) is not null) then (ref_14.c_2) else (ref_14.c_2) end
) else (ref_14.c_3) end
) end
as c_1,
case when ((((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1))
and (not ((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1))))
and (((ref_14.c_1) between (ref_14.c_1) and (ref_14.c_1)) is not null)) then (case when (null::bool) then (ref_14.c_1) else (nullif(ref_14.c_1, 0)) end
) else (ref_14.c_1) end
as c_2,
case when false then (ref_14.c_0) else (ref_14.c_4) end
as c_3,
case when true then (ref_14.c_5) else ((ref_14.c_3) <= (ref_14.c_3)) end
as c_4,
case when false then (ref_14.c_1) else (null::int4) end
as c_5,
case when true then (ref_14.c_5) else (((ref_14.c_4) is null)
and ((pg_catalog.btrim(ref_14.c_3,
ref_14.c_3)) ~~* (ref_14.c_3))) end
as c_6,
case when false then ((pg_catalog.timestamp(null::timestamp,
ref_14.c_1)) <> (pg_catalog.timestamp_smaller(make_timestamp(2029, 11, 19, 20, 38, 38),
make_timestamp(2029, 3, 7, 10, 28, 28)))) else (ref_14.c_5) end
as c_7,
case when false then (ref_14.c_6) else (ref_14.c_4) end
as c_8,
case when false then ((coalesce(ref_14.c_2, pg_catalog.rpad(ref_14.c_3,
ref_14.c_1,
ref_14.c_3))) not like '_%_') else ((case when true then (case when (((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_14.c_2) else (ref_14.c_2) end
) not like 'Rn/')) then (case when (((false::bool)
and (not (false::bool)))
and ((false::bool) is not null)) then (ref_14.c_2) else (null::text) end
) else (case when ((((ref_14.c_3) > (ref_14.c_3))
and (not ((ref_14.c_3) > (ref_14.c_3))))
and (((ref_14.c_3) > (ref_14.c_3)) is not null)) then (ref_14.c_3) else (ref_14.c_2) end
) end
) else (ref_14.c_2) end
) ~* (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_14.c_2) else (ref_14.c_3) end
)) end
as c_9
from
t4 as ref_14
),
cte_3 AS (select
case when true then (subq_1.c_0) else (subq_1.c_9) end
as c_0,
case when true then (case when (((case when true then (case when ((((case when true then (subq_1.c_3) else (subq_1.c_9) end
) >= (case when false then (17) else (-25) end
))
and ((case when true then (subq_1.c_3) else (subq_1.c_9) end
) <= (case when ((((subq_1.c_5) not like '_>_')
and (not ((subq_1.c_5) not like '_>_')))
and (((subq_1.c_5) not like '_>_') is not null)) then (subq_1.c_3) else (subq_1.c_3) end
)))) then (case when ((((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0)))
or (not ((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0)))))
or (((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_0))
or (null::bool))
or ((subq_1.c_9) <> (subq_1.c_0))) is null)) then (subq_1.c_6) else (subq_1.c_6) end
) else (case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_6) else (subq_1.c_1) end
) end
) else (case when ((((subq_1.c_1) not like 'Y.m')
and ((null::bool)
or (not ((subq_1.c_6) > (subq_1.c_5)))))
or ((subq_1.c_5) <> (subq_1.c_5))) then (subq_1.c_1) else (subq_1.c_6) end
) end
) <> (case when ((((subq_1.c_9) > (subq_1.c_9))
or (not ((subq_1.c_9) > (subq_1.c_9))))
or (((subq_1.c_9) > (subq_1.c_9)) is null)) then (subq_1.c_5) else (subq_1.c_5) end
))) then (case when true then (case when ((not ((not (((((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3))
or (not ((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3))))
or (((subq_1.c_9) between (subq_1.c_0) and (subq_1.c_3)) is null))
and ((case when (((((subq_1.c_0) > (subq_1.c_0))
or (null::bool))
and (not (((subq_1.c_0) > (subq_1.c_0))
or (null::bool))))
and ((((subq_1.c_0) > (subq_1.c_0))
or (null::bool)) is not null)) then (subq_1.c_7) else (subq_1.c_4) end
) <> (case when (((((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6)))
or (not (((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6)))))
or ((((subq_1.c_2) is null)
and ((subq_1.c_6) ~~* (subq_1.c_6))) is null)) then (subq_1.c_2) else (subq_1.c_7) end
))))
and (not (((((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp))
or (not ((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp))))
or (((make_timestamp(1996, 11, 24, 12, 12, 52)) <> (null::timestamp)) is null))
and (not ((not ((((true::bool)
and (not (true::bool)))
and ((true::bool) is not null))
or (not (((((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7)))
or (not ((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7)))))
or (((make_timestamp(2082, 8, 28, 23, 52, 10)) = (make_timestamp(2024, 11, 16, 2, 32, 7))) is null))
and ((case when ((((0) is not null)
and (not ((0) is not null)))
and (((0) is not null) is not null)) then (subq_1.c_6) else (subq_1.c_6) end
) = (case when (((null::bool)
or (not (null::bool)))
or ((null::bool) is null)) then (subq_1.c_1) else (subq_1.c_1) end
))))))
or (not (((((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54)))
or (not ((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54)))))
or (((make_timestamp(2062, 4, 13, 16, 25, 40)) < (make_timestamp(2032, 5, 20, 12, 29, 54))) is null))
and (((case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_3) else (subq_1.c_9) end
) >= (case when ((((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m')))
and (not ((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m')))))
and (((exists (
select
ref_104.vkey as c_0
from
t1 as ref_104
where (subq_1.c_3) >= (
select
ref_104.c5 as c_0
from
t4 as ref_105
where (subq_1.c_5) > (ref_105.c_2)
order by c_0 desc
limit 1)
intersect
select
subq_1.c_3 as c_0
from
t5 as ref_106
where false::bool))
or (((subq_1.c_0) <= (subq_1.c_0))
and ((subq_1.c_5) not like '%%m'))) is not null)) then (subq_1.c_9) else (subq_1.c_3) end
))
and ((case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (subq_1.c_3) else (subq_1.c_9) end
) <= (case when (((((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool))
and (not (((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool))))
and ((((subq_1.c_1) ~* (subq_1.c_6))
or (false::bool)) is not null)) then (subq_1.c_0) else (subq_1.c_9) end
)))))))))))) then (case when (((not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5))))
and (not (not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5))))))
and ((not (((subq_1.c_1) ^@ (subq_1.c_6))
and ((subq_1.c_5) < (subq_1.c_5)))) is not null)) then (subq_1.c_5) else (subq_1.c_6) end
) else (case when (((not (false::bool))
and (not (not (false::bool))))
and ((not (false::bool)) is not null)) then (case when (false::bool) then (subq_1.c_1) else (subq_1.c_1) end
) else (subq_1.c_6) end
) end
) else ((subq_1.c_6) || (subq_1.c_5)) end
) else (case when ((((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6)))
and (not ((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6)))))
and (((null::bool)
or ((subq_1.c_6) ~* (subq_1.c_6))) is not null)) then (subq_1.c_5) else (subq_1.c_1) end
) end
) else (case when (null::bool) then (subq_1.c_6) else (subq_1.c_6) end
) end
as c_1
from
(select
case when ((((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30)))
or (not ((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30)))))
or (((make_timestamp(2104, 11, 23, 14, 7, 42)) <> (make_timestamp(2106, 9, 20, 4, 43, 30))) is null)) then (-0) else (ref_16.c_1) end
as c_0,
case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_15.c_2) else (ref_15.c_3) end
as c_1,
case when ((((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool)))
or (not ((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool)))))
or (((((ref_15.c_1) is null)
or ((make_timestamp(2073, 8, 5, 19, 26, 3)) > (make_timestamp(2024, 11, 14, 6, 30, 27))))
or ((((make_timestamp(2100, 2, 12, 17, 12, 2)) = (make_timestamp(2037, 4, 6, 9, 26, 17)))
and (((not ((null::text) >= (ref_16.c_2)))
or (null::bool))
or (null::bool)))
and (false::bool))) is null)) then (ref_16.c_4) else (ref_16.c_4) end
as c_2,
case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (ref_15.c_1) else (ref_15.c_1) end
as c_3,
case when ((((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1)))
or (not ((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1)))))
or (((null::bool) < ((ref_16.c_1) between (ref_15.c_1) and (ref_15.c_1))) is null)) then (ref_16.c_4) else (0.0) end
as c_4,
case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (ref_16.c_2) else (ref_16.c_2) end
as c_5,
case when ((((('/zI,') < (ref_16.c_3)) = (not (false::bool)))
or (not ((('/zI,') < (ref_16.c_3)) = (not (false::bool)))))
or (((('/zI,') < (ref_16.c_3)) = (not (false::bool))) is null)) then (ref_15.c_2) else (ref_15.c_2) end
as c_6,
case when ((((ref_15.c_3) <= (ref_15.c_3))
and (not ((ref_15.c_3) <= (ref_15.c_3))))
and (((ref_15.c_3) <= (ref_15.c_3)) is not null)) then (ref_15.c_4) else (ref_16.c_6) end
as c_7,
case when ((((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1))
or (not ((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1))))
or (((ref_15.c_1) between (ref_15.c_1) and (ref_15.c_1)) is null)) then (ref_15.c_5) else (false::bool) end
as c_8,
case when (((exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool))
or (not (exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool))))
or ((exists (
select
(ref_16.c_3) ~<=~ (ref_16.c_2) as c_0,
ref_107.c_2 as c_1
from
t4 as ref_107
where (((ref_15.c_2) ~~* (ref_16.c_2))
or (false::bool))
or (((make_timestamp(2103, 12, 25, 1, 57, 36)) > (make_timestamp(1971, 9, 1, 9, 59, 47)))
or ((ref_107.c_2) in (select distinct
ref_16.c_3 as c_0
from
t0 as ref_108
where false::bool
order by c_0 asc)))
except
select
null::bool as c_0,
ref_15.c_2 as c_1
from
t4 as ref_109
where true::bool)) is null)) then (ref_15.c_1) else (ref_16.c_1) end
as c_9
from
(t4 as ref_15
inner join t4 as ref_16
on (ref_15.c_0 = ref_16.c_0 ))
where not ((false::bool)
or (not ((true::bool)
and (((((null::int4) between (ref_16.c_1) and (ref_16.c_1))
or (not ((null::int4) between (ref_16.c_1) and (ref_16.c_1))))
or (((null::int4) between (ref_16.c_1) and (ref_16.c_1)) is null))
and ((case when ((((ref_15.c_3) not like '__S')
and (not ((ref_15.c_3) not like '__S')))
and (((ref_15.c_3) not like '__S') is not null)) then (ref_15.c_1) else (ref_16.c_1) end
) > (case when ((((ref_15.c_2) !~~* (ref_15.c_3))
or (not ((ref_15.c_2) !~~* (ref_15.c_3))))
or (((ref_15.c_2) !~~* (ref_15.c_3)) is null)) then (ref_15.c_1) else (ref_15.c_1) end
))))))) as subq_1
)
select
case when ((((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null))
or (not ((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null))))
or (((((null::bool)
or (false::bool))
or ((((subq_2.c_8) !~~* (subq_2.c_8))
and ((subq_2.c_11) !~* (subq_2.c_8)))
and (null::bool)))
or ((ref_22.c0) is not null)) is null)) then (case when (((((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool))
and (not ((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool))))
and (((subq_3.c_2) in (select
(ref_22.vkey) between (subq_2.c_7) and (ref_22.c0) as c_0
from
(t4 as ref_125
right outer join t4 as ref_126
on (ref_125.c_6 = ref_126.c_0 ))
where (null::bool)
and (null::bool)
union
select
(ref_127.c5) in (select
subq_2.c_1 as c_0
from
cte_2 as ref_128
where true::bool) as c_0
from
t1 as ref_127
where true::bool)) is not null))
or ((true::bool) in (select
case when (((((true::bool)
or (not (true::bool)))
or ((true::bool) is null))
and ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (subq_2.c_9) else (ref_29.c_11) end
) like 'u6_')) is null) then (false::bool) else ((((true::bool)
or (not (true::bool)))
or ((true::bool) is null))
and ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (subq_2.c_9) else (ref_29.c_11) end
) like 'u6_')) end
as c0
from
t3 as ref_29
where true::bool))) then (((ref_22.c1) = (ref_22.c1))
or (true::bool)) else (null::bool) end
) else (true::bool) end
as c_2,
((((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2)))
and (not ((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2)))))
and (((pg_catalog.regexp_replace(subq_2.c_2,
ref_22.c2,
ref_22.c2)) !~~* ((subq_2.c_8) || (ref_22.c2))) is not null))
or (not ((not (((((ref_22.c2) > (subq_2.c_9))
and (not ((ref_22.c2) > (subq_2.c_9))))
and (((ref_22.c2) > (subq_2.c_9)) is not null))
or ((true::bool) in (select
case when ((null::bool) is null) then (false::bool) else (null::bool) end
as c0
from
t3 as ref_30
where true::bool))))
or (not (((((subq_2.c_9) <= (subq_2.c_9))
and (not ((subq_2.c_9) <= (subq_2.c_9))))
and (((subq_2.c_9) <= (subq_2.c_9)) is not null))
or ((case when ((((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1))
or (not ((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1))))
or (((-61) <> (
select
subq_3.c_0 as c_0
from
t4 as ref_130
where ((subq_2.c_9) <> (ref_22.c2))
or ((ref_130.c_2) not like '_6%')
order by c_0 desc
limit 1)) is null)) then (case when (((((ref_22.c0) is null)
or (not ((ref_22.c0) is null)))
or (((ref_22.c0) is null) is null))
and ((case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else (ref_22.c1) end
) >= (case when (((((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3)))
and (not (((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3)))))
and ((((subq_2.c_11) ~ (subq_2.c_9))
or ((ref_22.vkey) between (subq_2.c_6) and (subq_2.c_3))) is not null)) then (ref_22.c1) else (ref_22.c1) end
))) then (case when (((((subq_2.c_5) = (subq_2.c_4))
and (null::bool))
and (not (((subq_2.c_5) = (subq_2.c_4))
and (null::bool))))
and ((((subq_2.c_5) = (subq_2.c_4))
and (null::bool)) is not null)) then (subq_2.c_11) else (subq_2.c_2) end
) else (case when (((null::bool)
or (not (null::bool)))
or ((null::bool) is null)) then (subq_2.c_11) else (subq_2.c_9) end
) end
) else (subq_2.c_2) end
) ^@ (case when ((((subq_2.c_2) ~* (subq_2.c_11))
and (not ((subq_2.c_2) ~* (subq_2.c_11))))
and (((subq_2.c_2) ~* (subq_2.c_11)) is not null)) then (pg_catalog.rpad(subq_2.c_11,
subq_2.c_10)) else (subq_2.c_8) end
)))))) as c_3,
((((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8))
or (not ((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8))))
or (((pg_catalog.substring(subq_2.c_8,
-0,
subq_2.c_0)) ~~ (subq_2.c_8)) is null))
and (not ((not (((((ref_22.c3) <= (ref_22.c3))
and (not ((ref_22.c3) <= (ref_22.c3))))
and (((ref_22.c3) <= (ref_22.c3)) is not null))
or ((case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (subq_2.c_2) else (subq_2.c_2) end
) not like '_%%')))
and (not (((((subq_2.c_9) = (subq_2.c_9))
or (not ((subq_2.c_9) = (subq_2.c_9))))
or (((subq_2.c_9) = (subq_2.c_9)) is null))
and (not ((not (((((ref_22.c2) !~ (subq_2.c_8))
and (not ((ref_22.c2) !~ (subq_2.c_8))))
and (((ref_22.c2) !~ (subq_2.c_8)) is not null))
or (not ((not (null::bool))
and (not (null::bool))))))
or (not ((((null::bool)
and (not (null::bool)))
and ((null::bool) is not null))
or ((case when ((((subq_2.c_9) !~* (subq_2.c_9))
and (not ((subq_2.c_9) !~* (subq_2.c_9))))
and (((subq_2.c_9) !~* (subq_2.c_9)) is not null)) then (subq_2.c_5) else (subq_2.c_1) end
) < (case when (((null::bool)
and (not (null::bool)))
and ((null::bool) is not null)) then (96) else (pg_catalog.gin_compare_jsonb(case when (((false::bool)
and (not (false::bool)))
and ((false::bool) is not null)) then (subq_2.c_2) else (null::text) end
,
case when ((((false::bool) is null)
and (not ((false::bool) is null)))
and (((false::bool) is null) is not null)) then (subq_2.c_9) else (subq_2.c_2) end
)) end
)))))))))) as c_4,
case when ((((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3)))
or (not ((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3)))))
or (((ref_22.c1) >= (pg_catalog.tsrange_subdiff(make_timestamp(2091, 10, 11, 21, 41, 25),
ref_22.c3))) is null)) then ((case when ((((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null))
and (not ((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null))))
and (((((((subq_2.c_9) ^@ (subq_2.c_9))
or (null::bool))
and (null::bool))
or (null::bool))
and ((ref_22.pkey) is not null)) is not null)) then (nullif(ref_22.c1, ref_22.c1)) else (pg_catalog.float8smaller(case when ((((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4))
and (not ((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4))))
and (((subq_2.c_3) between (subq_2.c_7) and (subq_2.c_4)) is not null)) then (ref_22.c1) else (ref_22.c1) end
,
case when ((((subq_2.c_1) = (ref_22.vkey))
or (not ((subq_2.c_1) = (ref_22.vkey))))
or (((subq_2.c_1) = (ref_22.vkey)) is null)) then (ref_22.c1) else (ref_22.c1) end
)) end
) / (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else ((case when (((false::bool)
or (not (false::bool)))
or ((false::bool) is null)) then (ref_22.c1) else (ref_22.c1) end
) / (case when (((true::bool)
and (not (true::bool)))
and ((true::bool) is not null)) then (ref_22.c1) else (ref_22.c1) end
)) end
)) else (pg_catalog.cotd(ref_22.c1)) end
as c_5
from
((select
case when false then (ref_17.c5) else (ref_17.pkey) end
as c_0,
case when false then (ref_18.c_0) else (ref_18.c_0) end
as c_1,
case when true then (ref_18.c_1) else (ref_18.c_1) end
as c_2,
case when false then (ref_17.c5) else (ref_17.vkey) end
as c_3,
case when true then (ref_17.c5) else (ref_17.c5) end
as c_4,
case when false then (ref_18.c_0) else (ref_18.c_0) end
as c_5,
case when false then (ref_17.vkey) else (ref_17.pkey) end
as c_6,
case when true then (ref_18.c_0) else (ref_17.c5) end
as c_7,
case when false then (ref_18.c_1) else (ref_18.c_1) end
as c_8,
case when true then (ref_18.c_1) else (ref_18.c_1) end
as c_9,
case when false then (ref_17.pkey) else (ref_17.vkey) end
as c_10,
case when false then (ref_18.c_1) else (ref_18.c_1) end
as c_11
from
(t1 as ref_17
cross join cte_3 as ref_18
)
where not ((false::bool)
or (not ((true::bool)
and (null::bool))))) as subq_2
left outer join (t0 as ref_22
left outer join (select
case when ((((ref_23.c_8) > (ref_23.c_3))
and (not ((ref_23.c_8) > (ref_23.c_3))))
and (((ref_23.c_8) > (ref_23.c_3)) is not null)) then (ref_23.c_2) else (ref_23.c_2) end
as c_0,
case when ((((2) between (ref_23.c_2) and (ref_23.c_0))
or (not ((2) between (ref_23.c_2) and (ref_23.c_0))))
or (((2) between (ref_23.c_2) and (ref_23.c_0)) is null)) then (ref_23.c_6) else (null::bool) end
as c_1,
case when ((((ref_23.c_0) <= (ref_23.c_0))
and (not ((ref_23.c_0) <= (ref_23.c_0))))
and (((ref_23.c_0) <= (ref_23.c_0)) is not null)) then (true::bool) else (ref_23.c_9) end
as c_2,
((case when ((((ref_23.c_1) <= (ref_23.c_1))
or (not ((ref_23.c_1) <= (ref_23.c_1))))
or (((ref_23.c_1) <= (ref_23.c_1)) is null)) then (ref_23.c_3) else (ref_23.c_3) end
) <= (case when (((((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool))
or (not (((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool))))
or ((((ref_23.c_1) ~<=~ (ref_23.c_1))
or (null::bool)) is null)) then (ref_23.c_8) else (ref_23.c_8) end
)) as c_3
from
cte_2 as ref_23
where not ((false::bool)
or (not ((true::bool)
and ((((false::bool)
and (not (false::bool)))
and ((false::bool) is not null))
or ((case when (((((true::bool)
and (null::bool))
and (false::bool))
and (not (((true::bool)
and (null::bool))
and (false::bool))))
and ((((true::bool)
and (null::bool))
and (false::bool)) is not null)) then (ref_23.c_0) else (ref_23.c_5) end
) is not null)))))) as subq_3
on (ref_22.pkey = subq_3.c_0 ))
on (subq_2.c_0 = ref_22.vkey ))
* Re: Server closed the connection unexpectedly (memory leak)
2023-06-23 16:06 Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
@ 2023-06-24 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-01 02:56 ` Re: Server closed the connection unexpectedly (memory leak) Andres Freund <andres@anarazel.de>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2023-06-24 16:56 UTC (permalink / raw)
To: Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>; +Cc: pgsql-bugs@lists.postgresql.org
Zu-Ming Jiang <zuming.jiang@inf.ethz.ch> writes:
> My fuzzer finds a bug in Postgres, which makes the connection closed
> unexpectedly.
TBH, I think there's not much to be learned here, beyond "a
ridiculously complicated query takes a ridiculous amount of memory
to plan". The reason for the backend crash is presumably that the
OOM killer decided to zap it. If you run the postmaster under a
"ulimit -v" setting that's small enough to act before the OOM killer
does, then you get an unexciting "out of memory" error.
I did find that if you mark cte_3 as MATERIALIZED, the resource
consumption is a lot less --- but you get a plan that requires
41191 lines to EXPLAIN, so it's still way outside any bounds of
reasonability. Perhaps there's room there to argue that we
shouldn't flatten CTE subqueries that are "too big" ... but it's
hard to decide how to measure "too big".
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Server closed the connection unexpectedly (memory leak)
2023-06-23 16:06 Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-24 16:56 ` Re: Server closed the connection unexpectedly (memory leak) Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-07-01 02:56 ` Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 19+ messages in thread
From: Andres Freund @ 2023-07-01 02:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>; pgsql-bugs@lists.postgresql.org
Hi,
On 2023-06-24 12:56:06 -0400, Tom Lane wrote:
> Zu-Ming Jiang <zuming.jiang@inf.ethz.ch> writes:
> > My fuzzer finds a bug in Postgres, which makes the connection closed
> > unexpectedly.
>
> TBH, I think there's not much to be learned here, beyond "a
> ridiculously complicated query takes a ridiculous amount of memory
> to plan". The reason for the backend crash is presumably that the
> OOM killer decided to zap it. If you run the postmaster under a
> "ulimit -v" setting that's small enough to act before the OOM killer
> does, then you get an unexciting "out of memory" error.
One small thing I find is interesting is that it takes quite a while to
process cancel requests - which primarily appears to be because there's no CFI
anywhere in copyObject().
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2023-07-01 02:56 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-11 16:41 BUG #5272: PL/Python SELECT: return composite fields as dict, not str Steve White <swhite@aip.de>
2010-01-12 02:31 ` Robert Haas <robertmhaas@gmail.com>
2010-01-31 12:20 ` Steve White <swhite@aip.de>
2014-06-18 18:45 Re: “server closed the connection unexpectedly” Heikki Linnakangas <hlinnakangas@vmware.com>
2014-06-18 18:51 ` Re: “server closed the connection unexpectedly” Felipe Gasper <felipe@felipegasper.com>
2014-06-18 20:09 ` Re: “server closed the connection unexpectedly” Felipe Gasper <felipe@felipegasper.com>
2015-12-16 07:34 server closed the connection unexpectedly during upgrade Yang, Leo <leo.yang@hp.com>
2015-12-16 07:57 ` Re: server closed the connection unexpectedly during upgrade Michael Paquier <michael.paquier@gmail.com>
2015-12-16 08:26 ` Re: server closed the connection unexpectedly during upgrade Yang, Leo <leo.yang@hp.com>
2022-06-25 16:00 server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
2022-06-25 21:11 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
2022-06-25 21:22 ` Re: server closed the connection unexpectedly - bug report David G. Johnston <david.g.johnston@gmail.com>
2022-07-11 08:05 ` RE: server closed the connection unexpectedly - bug report Jiří Ledvinka <ledvinkaj@sci.muni.cz>
2023-06-15 16:26 Server closed the connection unexpectedly Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-15 20:12 ` Re: Server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
2023-06-23 16:06 Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-23 16:20 ` Fwd: Server closed the connection unexpectedly (memory leak) Zu-Ming Jiang <zuming.jiang@inf.ethz.ch>
2023-06-24 16:56 ` Re: Server closed the connection unexpectedly (memory leak) Tom Lane <tgl@sss.pgh.pa.us>
2023-07-01 02:56 ` Re: Server closed the connection unexpectedly (memory leak) Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox