0

I have a problem with Postgres that others seem to have found but the solutions that are given somehow do not work for me. I explain:

WHAT I USE

  • MacOS 10.15.6
  • Installed through Homebrew
  • PostgreSQL 12.2 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit

THE TRIGGER EVENT

I have been doing queries without problems but some of them crash my system, here is one of the examples

SELECT w2.id AS "id"
FROM weather w1
JOIN weather w2
    ON EXTRACT(DAY FROM CAST(w2.recorddate AS TIMESTAMP)-CAST(w1.recorddate AS TIMESTAMP)) = 1
        AND w2.temperature > w1.temperature;

THE ERROR

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I tried on PgAdmin and it also crashes.

THINGS I HAVE CHECKED

The pg_hba.conf file

  • I tried some of the suggestions in this thread without success

The postgresql.conf file

  • I tried adding listen_addresses = '*' to the end of the file without success

CURRENT SETTINGS

I went back to the defaults I had originally but now I am not sure if everything is in order

My pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

My postgresql.conf has the following settings

  • listen_addresses = 'localhost'
  • port = 5432

QUESTION

Any suggestion on where to look, what to reinstall, or what to do? It is a bit uncomfortable not being able to run certain queries.

Thanks!

EDIT POSTGRES LOG

Process:               postgres [11542]
Path:                  /Users/USER/*/postgres
Identifier:            postgres
Version:               ???
Code Type:             X86-64 (Native)
Parent Process:        postgres [123]
Responsible:           postgres [123]
User ID:               502

Date/Time:             2020-09-06 14:06:05.972 -0700
OS Version:            Mac OS X 10.15.6 (19G2021)
Report Version:        12
Anonymous UUID:        C82E0E26-CE4B-F615-A561-84E475FEB121

Sleep/Wake UUID:       4B9FB152-7EE9-4D77-B3B3-909D1AD8479A

Time Awake Since Boot: 7500 seconds
Time Since Wake:       740 seconds

System Integrity Protection: enabled

Crashed Thread:        0  Dispatch queue: com.apple.main-thread

Exception Type:        EXC_BAD_ACCESS (Code Signature Invalid)
Exception Codes:       0x0000000000000032, 0x000000010453f000
Exception Note:        EXC_CORPSE_NOTIFY

Termination Reason:    Namespace CODESIGNING, Code 0x2

kernel messages:

VM Regions Near 0x10453f000:
    __LINKEDIT             0000000104466000-000000010453f000 [  868K] r--/rw- SM=COW  /Users/USER/*/*.dylib
--> VM_ALLOCATE            000000010453f000-0000000104543000 [   16K] r-x/rwx SM=COW  
    __TEXT                 0000000104544000-0000000104562000 [  120K] r-x/rwx SM=COW  /Users/USER/*/*.dylib

Application Specific Information:
crashed on child side of fork pre-exec

Thread 0 Crashed:: Dispatch queue: com.apple.main-thread
0   ???                             0x000000010453f000 0 + 4367577088
1   postmaster                      0x00000001035841c6 ExecScan + 582 (execScan.c:239)
2   postmaster                      0x00000001035a5270 ExecSort + 240 (nodeSort.c:109)
3   postmaster                      0x000000010359e581 ExecMergeJoin + 241 (nodeMergejoin.c:657)
4   postmaster                      0x000000010359e581 ExecMergeJoin + 241 (nodeMergejoin.c:657)
5   postmaster                      0x00000001035a5270 ExecSort + 240 (nodeSort.c:109)
6   postmaster                      0x00000001035a654d ExecGroup + 109 (nodeGroup.c:70)
7   postmaster                      0x000000010357c3bf standard_ExecutorRun + 287 (execMain.c:364)
8   postmaster                      0x00000001036e1963 PortalRunSelect + 243 (pquery.c:931)
9   postmaster                      0x00000001036e153e PortalRun + 430 (pquery.c:770)
10  postmaster                      0x00000001036e0585 exec_simple_query + 1445 (postgres.c:1223)
11  postmaster                      0x00000001036de40d PostgresMain + 3917 (postgres.c:4249)
12  postmaster                      0x000000010365ee28 PostmasterMain + 7832 (postmaster.c:1377)
13  postmaster                      0x00000001035cbbce main + 1678
14  libdyld.dylib                   0x00007fff6b701cc9 start + 1

Thread 0 crashed with X86 Thread State (64-bit):
  rax: 0x000000010453f000  rbx: 0x00007fc79682d4c0  rcx: 0x0000000106f818d8  rdx: 0x00007ffeec82e5e7
  rdi: 0x00007fc79682d578  rsi: 0x00007fc79682d1a0  rbp: 0x00007ffeec82e610  rsp: 0x00007ffeec82e5b8
   r8: 0x0000000000016d3d   r9: 0xffffffff00000000  r10: 0x0000000106f818d0  r11: 0x00007fc799288730
  r12: 0x00007fc79682d578  r13: 0x00007fc79682be00  r14: 0x00007fc79682d1a0  r15: 0x0000000103a3c240
  rip: 0x000000010453f000  rfl: 0x0000000000010246  cr2: 0x000000010453f000
  
Logical CPU:     0
Error Code:      0x00000015 (invalid protections for user instruction read)
Trap Number:     14
JourneyDS
  • 113
  • 14
  • Look in the Postgresql server log as well as the system log. A rough first guess is some sort of out of memory issue that is causing the backend process to be terminated. – Adrian Klaver Sep 06 '20 at 20:03
  • Hi Adrian, thanks for the reply, I added what the crash report of Postgres says but I am too much of a rookie to understand it. Can you help me decipher where the problem came from? – JourneyDS Sep 06 '20 at 21:08
  • Looks like a bug on first glance. I would try a more recent version of PostgreSQL. Currently version 12.4 is shipped via brew. – Ulrich Thomas Gabor Sep 06 '20 at 21:21
  • Well that is the system log and it is useful. Could you also look in the Postgres server log? I don't know what the system log information is saying other then something bad happened. I would suggest taking this to to the [Postgres general list](https://lists.postgresql.org/) pgsql-general. There are folks there that could provide more information. – Adrian Klaver Sep 06 '20 at 21:32
  • Apologies, I thought I had shared the Postgres logs. This is what it shows `2020-09-06 15:01:26.478 PDT [19807] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2020-09-06 15:01:26.478 PDT [19807] WARNING: could not create listen socket for "localhost" 2020-09-06 15:01:26.479 PDT [19807] FATAL: could not create any TCP/IP sockets` – JourneyDS Sep 06 '20 at 21:58
  • GhostGambler, I upgraded to 12.4 and downgraded to 11.9 but it did not work unfortunately, but thanks for the suggestions! – JourneyDS Sep 06 '20 at 21:59
  • I do not know if it helps, I am looking at the ports (`sudo lsof -iTCP -sTCP:LISTEN -n -P`) and currently these are "listening" for postgres `postgres 22344 postgres 4u IPv6 0x54b8408e9dceb4f 0t0 TCP *:5432 (LISTEN) postgres 22344 postgres 5u IPv4 0x54b8408de1faf7f 0t0 TCP *:5432 (LISTEN)` – JourneyDS Sep 06 '20 at 22:31
  • Maybe your query just timed out. Use a simpler query to chech the connection. (and: avoid expressions in the where clause) – wildplasser Sep 06 '20 at 23:43
  • Thanks, wildplasser. Yes, simple queries work, my problem is with large ones but they are by no means complex since I am talking about tables with 20 rows at most (I am practicing SQL). Is there a way to check if it is what you suggest? – JourneyDS Sep 07 '20 at 00:01

1 Answers1

2

This looks like a known issue where JIT code is not signed properly and triggers security errors on MacOS. I thought a fix for this was already released, so I don't know why you would still be seeing it on 12.4, but in any event turning jit=off should fix the problem.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Awesome, worked well! Thanks. Rookie question, I turned it off at database level with `alter database name_db set jit=off;` do you recommend to do it at global level or leave it like this? – JourneyDS Sep 07 '20 at 00:53
  • 2
    I'd turn it off globally. If this is the only database in the cluster which large queries get run in, it probably doesn't matter. To me it is just easier to lose awareness that I did an `alter database` or `alter user`, whereas changes to postgresql.conf are self-documenting, and easy to add a commented-out line describing why I made the change. – jjanes Sep 07 '20 at 01:19
  • 1
    The issue referred to is referencing the EDB build of Postgres and the packaging thereof . @JourneyDS is using Homebrew and it may not have gotten the memo:) Might be worth filing an issue [here](https://github.com/Homebrew/formulae.brew.sh/issues) – Adrian Klaver Sep 08 '20 at 14:42
  • Hey Adrian, just to be sure of what you are referring to, are you suggesting I open a "new issue" on the Homebrew github page even if it has the solution? (I am asking because I am quite new in all of this and I am not sure if you are saying that, I am quite happy to do so if you think it might be helpful for the community) – JourneyDS Sep 08 '20 at 15:19