Re: [EXTERNAL] Re: Çok veriyle çalışırken, veritabanı yapılan işlemi kesiyor.

Segmentation fault a sebep olan kod parcasinin nerede oldugunu anlamak icin core dump alip backtrace e bakabilirsiniz. Boylece sorunun kaynaginin bir eklenti olup olmadigini da gorme sansiniz olabilir.

Eger sorun pg_cron kaynakli ise suradan bir issue acabilirsiniz: https://github.com/citusdata/pg_cron/issues
________________________________
From: Samed YILDIRIM <samed(at)reddoc(dot)net>
Sent: Wednesday, August 12, 2020 1:22 PM
To: Ali Kemal DEMIRCI <demirci(dot)alikemal(at)gmail(dot)com>; PostgreSQL Türkiye <pgsql-tr-genel(at)postgresql(dot)org>
Subject: [EXTERNAL] Re: Çok veriyle çalışırken, veritabanı yapılan işlemi kesiyor.

Merhabalar,

1. Hangi PostgreSQL versiyonunu kullanıyorsunuz?
2. Sistem kaynakları nedir?
3. Yapılandırma ayalarınızı paylaşır mısınız.
4. Veritabanında hangi eklentileri kullanıyorsunuz?
5. Explain ile planı alarak paylaşır mısınız.

En önemli soru 4. soru. Veritabanı "Segmentation fault" ile kapanıyor loglarınıza göre. Veritabanı bellek alamadım diye sonlanmıyor, sistem kaynaklarının yetersizliği ile alakalı olduğunu düşünmüyorum. Veritabanı süreçlerinden birisi, kendisine ait olmayan bir bellek alanını okumaya ya da yazmaya çalışıyor. Bu da en sık C ile yazılmış bir fonksiyonun/eklentinin içerisindeki hatalı bellek yönetiminden veya kodun içerisindeki buglardan kaynaklanır.

Loglardan ilk gözüme çarpan pg_cron kullandığınız. Onu kaldırmayı deneyebilirsiniz. local_preload_libraries, session_preload_libraries ve shared_preload_libraries parametrelerinin değerlerini de kontrol ederseniz iyi olur.

show session_preload_libraries;
show shared_preload_libraries;
show local_preload_libraries;
Best regards.
Samed YILDIRIM

12.08.2020, 12:17, "Ali Kemal DEMIRCI" <demirci(dot)alikemal(at)gmail(dot)com>:
Merhaba arkadaşlar,

Create table xxx as
Select ....
Limit 80000;

Yukardaki SQL komutunu çalıştırdığımda aşağıdaki hatayı alıyorum;

SQL Error [08006]: An I/O error occurred while sending to the backend.

Fakat "Limit 70000" yaptığımda başarılı şekilde select ifadesinden dönen kayıtlarla tablo oluşturulabiliyor.

Hat oluştuğunda log dosyasında oluşan kayıtlar aşağıdaki gibidir.
Bu konuda yardımcı olabilirmisiniz?

2020-08-12 11:08:58.037 +03 [94157] LOG: server process (PID 6072) was terminated by signal 11: Segmentation fault
2020-08-12 11:08:58.037 +03 [94157] DETAIL: Failed process was running: create table cs_subscriber_ as
select r.id_abone id, 1::numeric corporation_id, r.id_abone subscriber_number, 1::numeric area_id, r.abone_tipi subscriber_type_id, r.id_abone_adres_uavt building_flat_id,
r.creuser created_user_id, r.credate created_timestamp, 0::numeric "version", r.address address_description
from (select a.id_abone, a.abone_no, nvl(x.id_abone_tipi, 1) abone_tipi, a.id_abone_adres_uavt, bf.building_door_id,
nvl(a.creuser,1::numeric) creuser , nvl(a.credate, '01/01/1900 00:00:00'::timestamp) credate, (a.a4_adr1 ||a.a4_adr2 ||a.a4_adr3)::varchar address,
mmp.id<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmmp.id%2F&data=02%7C01%7Chanefi.onaldi%40microsoft.com%7Ce61e9891b2974a58c86e08d83ea99c2e%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637328245487467749&sdata=dEKa1gLMz0iJ9UpgeaCgW%2FcKi0z%2BpPvpQCBw%2B7kq9dc%3D&reserved=0> meter_model, a.sayac_no, a.sayac_konum, a.sayac_takma_tarihi, a.sayac_uretim_tarihi, to_char(a.sayac_uretim_tarihi, 'yyyy')::numeric uretim_yili,
a.sayac_karttipi_adi, a.kart_sayac_mrk , a.kart_sayac_no , a.kart_sayac_seri, a.sayac_konum, a.kayit_tarihi, a.aciklama, a.iptal_tarih,
('1'||lpad(a.defter_no::tex
2020-08-12 11:08:58.037 +03 [94157] LOG: terminating any other active server processes
2020-08-12 11:08:58.038 +03 [6074] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6074] 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.
2020-08-12 11:08:58.038 +03 [6074] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.038 +03 [6030] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6030] 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.
2020-08-12 11:08:58.038 +03 [6030] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.038 +03 [6073] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6073] 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.
2020-08-12 11:08:58.038 +03 [6073] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.038 +03 [6075] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6075] 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.
2020-08-12 11:08:58.038 +03 [6075] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.038 +03 [6056] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6056] 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.
2020-08-12 11:08:58.038 +03 [6056] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.038 +03 [6022] WARNING: terminating connection because of crash of another server process
2020-08-12 11:08:58.038 +03 [6022] 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.
2020-08-12 11:08:58.038 +03 [6022] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2020-08-12 11:08:58.041 +03 [94157] LOG: all server processes terminated; reinitializing
2020-08-12 11:08:58.066 +03 [6086] LOG: database system was interrupted; last known up at 2020-08-12 11:08:46 +03
2020-08-12 11:08:58.093 +03 [6087] FATAL: the database system is in recovery mode
2020-08-12 11:08:58.373 +03 [6088] FATAL: the database system is in recovery mode
2020-08-12 11:08:58.415 +03 [6089] FATAL: the database system is in recovery mode
2020-08-12 11:08:58.449 +03 [6090] FATAL: the database system is in recovery mode
2020-08-12 11:08:58.493 +03 [6086] LOG: database system was not properly shut down; automatic recovery in progress
2020-08-12 11:08:58.504 +03 [6086] LOG: redo starts at EC/8764F088
2020-08-12 11:08:58.600 +03 [6086] LOG: invalid record length at EC/88476D80: wanted 24, got 0
2020-08-12 11:08:58.600 +03 [6086] LOG: redo done at EC/88476CB8
2020-08-12 11:08:58.675 +03 [94157] LOG: database system is ready to accept connections
2020-08-12 11:08:58.679 +03 [6101] LOG: pg_cron scheduler started

--
Saygılarımla,

Ali Kemal DEMİRCİ