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

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 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İ

Server(lerinizin) kaynagi yetersiz kaliyor query yi calistirmak icin. ram,
islemci, ve hdd kontrol edin query calisirken. su an da hdd yeterli yer
olabilir ama query calisirken olurusan temp e yazilan gecici datalar icin
hdd yeterli gelmiyordur ayni sekilde ram de yetmiyordur yada her ikisi...

12 Ağu 2020 Çar 12:17 tarihinde Ali Kemal DEMIRCI <
demirci(dot)alikemal(at)gmail(dot)com> şunu yazdı:

> 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 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İ
>

Merhaba Ali Bey,

Loglardan sunucunun kaynaklarının yetmediğini anlıyorum. Memory ayarlarını değiştirilirse problemin düzeleceğini tahmin ediyorum. Ama büyük veri ile çalıştığınızdan dolayı kayıt sayısı mesela 100K olunca aynı durumu yaşarsınız. Uygun çözüm ctas ile yaptığınız işlemi optimize etmek olacaktır.

Kolay gelsin,
Murat

May the data be with you.

>> On 12 Aug 2020, at 12:26, Mustafa Kahraman <barzo(dot)mk(at)gmail(dot)com> wrote:
> 
> Server(lerinizin) kaynagi yetersiz kaliyor query yi calistirmak icin. ram, islemci, ve hdd kontrol edin query calisirken. su an da hdd yeterli yer olabilir ama query calisirken olurusan temp e yazilan gecici datalar icin hdd yeterli gelmiyordur ayni sekilde ram de yetmiyordur yada her ikisi...
>
> 12 Ağu 2020 Çar 12:17 tarihinde Ali Kemal DEMIRCI <demirci(dot)alikemal(at)gmail(dot)com> şunu yazdı:
>> 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 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İ

<div>Merhabalar,</div><div> </div><div>1. Hangi PostgreSQL versiyonunu kullanıyorsunuz?</div><div>2. Sistem kaynakları nedir?</div><div>3. Yapılandırma ayalarınızı paylaşır mısınız.</div><div>4. Veritabanında hangi eklentileri kullanıyorsunuz?</div><div>5. Explain ile planı alarak paylaşır mısınız.</div><div> </div><div>En önemli soru 4. soru. Veritabanı <font face="monospace">"Segmentation fault"<span style="font-family:'arial' , sans-serif"> 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.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">Loglardan ilk gözüme çarpan pg_cron kullandığınız. Onu kaldırmayı deneyebilirsiniz. </span></font>local_preload_libraries, session_preload_libraries ve shared_preload_libraries<font face="monospace"><span style="font-family:'arial' , sans-serif"> parametrelerinin değerlerini de kontrol ederseniz iyi olur.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">show </span></font>session_preload_libraries;</div><div>show shared_preload_libraries;</div><div>show local_preload_libraries;</div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 12:17, "Ali Kemal DEMIRCI" &lt;demirci(dot)alikemal(at)gmail(dot)com&gt;:</div><blockquote><div><div><font face="monospace">Merhaba arkadaşlar,</font></div><div> </div><font face="monospace">Create table xxx as</font><div><font face="monospace">Select ....</font></div><div><font face="monospace">Limit 80000;</font></div><div> </div><div>Yukardaki SQL komutunu çalıştırdığımda aşağıdaki hatayı alıyorum;</div><div> </div><div><font face="monospace">SQL Error [08006]: An I/O error occurred while sending to the backend.</font></div><div> </div><div>Fakat "Limit 70000" yaptığımda başarılı şekilde select ifadesinden dönen kayıtlarla tablo oluşturulabiliyor.</div><div> </div><div>Hat oluştuğunda log dosyasında oluşan kayıtlar aşağıdaki gibidir.</div><div>Bu konuda yardımcı olabilirmisiniz?</div><div> </div><div><font face="monospace">2020-08-12 11:08:58.037 +03 [94157] LOG:  server process (PID 6072) was terminated by signal 11: Segmentation fault<br />2020-08-12 11:08:58.037 +03 [94157] DETAIL:  Failed process was running: create table cs_subscriber_ as<br />        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, <br />              r.creuser created_user_id, r.credate created_timestamp, 0::numeric "version", r.address address_description<br />        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,<br />                      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,<br />                      <a href="http://mmp.id/">mmp.id</a> 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,<br />                      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,<br />                      ('1'||lpad(a.defter_no::tex<br />2020-08-12 11:08:58.037 +03 [94157] LOG:  terminating any other active server processes<br />2020-08-12 11:08:58.038 +03 [6074] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.038 +03 [6030] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.038 +03 [6073] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.038 +03 [6075] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.038 +03 [6056] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.038 +03 [6022] WARNING:  terminating connection because of crash of another server process<br />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.<br />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.<br />2020-08-12 11:08:58.041 +03 [94157] LOG:  all server processes terminated; reinitializing<br />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<br />2020-08-12 11:08:58.093 +03 [6087] FATAL:  the database system is in recovery mode<br />2020-08-12 11:08:58.373 +03 [6088] FATAL:  the database system is in recovery mode<br />2020-08-12 11:08:58.415 +03 [6089] FATAL:  the database system is in recovery mode<br />2020-08-12 11:08:58.449 +03 [6090] FATAL:  the database system is in recovery mode<br />2020-08-12 11:08:58.493 +03 [6086] LOG:  database system was not properly shut down; automatic recovery in progress<br />2020-08-12 11:08:58.504 +03 [6086] LOG:  redo starts at EC/8764F088<br />2020-08-12 11:08:58.600 +03 [6086] LOG:  invalid record length at EC/88476D80: wanted 24, got 0<br />2020-08-12 11:08:58.600 +03 [6086] LOG:  redo done at EC/88476CB8<br />2020-08-12 11:08:58.675 +03 [94157] LOG:  database system is ready to accept connections<br />2020-08-12 11:08:58.679 +03 [6101] LOG:  pg_cron scheduler started</font><div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></div></div></blockquote>

Merhaba Samed bey,

Öncelikle pg_cron eklentisini kaldırdım ve yine aynı hatayı alıyorum.

Söylediğiniz parametrelerin değerleri;

session_preload_libraries|
-------------------------|
|

shared_preload_libraries|
------------------------|
|

local_preload_libraries|
-----------------------|
|

Sorularınızın cevapları;

1- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1
20191121 (Red Hat 8.3.1-5), 64-bit

2- 30 GB Ram (28 GB Free),
16 Core CPU (% 95 Idle),
2.5 TB disk (1.4 TB Free)

3- postgresql.conf
shared_buffers = 128MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB

4- Kurulu olan extension'lar
postgis
orafce

5- Subquery Scan on r (cost=463362.37..515099.44 rows=80000 width=282)

|
-> Limit (cost=463362.37..514299.44 rows=80000 width=534)

|
-> Hash Join (cost=463362.37..774820.91 rows=489166 width=534)

|
Hash Cond: (a.id_abone_adres_uavt = bf.id)

|
-> Hash Left Join (cost=439755.24..715093.91 rows=664869
width=129)

|
Hash Cond: ((((((((((nvl(a.id_sayac_marka,
'-9'::numeric))::text || '~'::text) || (nvl(a.sayac_capi,
'-9'::integer))::text) || '~'::text) || (nvl(a.sayac_devirrakami,
'-9'::numeric))::text) || '~'::text) || nvl(btrim(upper(a.sayac_tipi|
-> Merge Right Join (cost=439714.19..687627.01
rows=664869 width=147)

|
Merge Cond: (x.id_abone = a.id_abone)

|
-> GroupAggregate (cost=439713.76..460925.11
rows=729055 width=86)

|
Group Key: x.id_abone, x.id_abone_tipi,
x.bitis_tarihi, x.abone_durum

|
-> Sort (cost=439713.76..442497.92
rows=1113664 width=78)

|
Sort Key: x.id_abone,
x.id_abone_tipi, x.bitis_tarihi, x.abone_durum

|
-> Subquery Scan on x
(cost=189912.33..228890.57 rows=1113664 width=78)

|
-> WindowAgg
(cost=189912.33..217753.93 rows=1113664 width=84)

|
-> Sort
(cost=189912.33..192696.49 rows=1113664 width=30)

|
Sort Key:
sas.id_abone, sas.id_abone_sicil

|
-> Seq Scan on
su_abone_sicil sas (cost=0.00..51416.64 rows=1113664 width=30)

|
-> Index Scan using sql121209005336850 on
su_abone a (cost=0.42..210562.64 rows=664869 width=115)

|
Filter: (id_abone_adres_uavt IS NOT NULL)

|
-> Hash (cost=27.69..27.69 rows=1069 width=16)

|
-> Seq Scan on cs_meter_model_prm mmp
(cost=0.00..27.69 rows=1069 width=16)

|
-> Hash (cost=13999.06..13999.06 rows=585606 width=6)

|
-> Seq Scan on gis_building_flat bf
(cost=0.00..13999.06 rows=585606 width=6)

|

On Wed, Aug 12, 2020 at 1:22 PM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:

> 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 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İ
>
>

--
Saygılarımla,

Ali Kemal DEMİRCİ

<div>Merhabalar Ali Bey,</div><div> </div><div>Gördüğüm kadarıyla Postgres'i varsayılan ayarları ile kullanıyorsunuz. Bu durum alabileceğiniz verimin altında kalmanıza neden olur. Sunucuyu yalnızca veritabanına koşturmak için kullanacaksanız, yapılandırmasını sistem kaynağına göre düzenlemenizi önemle tavsiye ederim. :)  </div><div> </div><div>Postgis'den kaynaklı olarak segmentation fault alıyor olma ihtimaliniz bence düşük. Daha çok orafce'den şüphe ediyorum. Tavsiyem, bu sorgunuz özelinde orafce'ye bağımlı olan kısımları Postgres'in kendisinde bulunan alternatifler ile değiştirerek (NVL -&gt; COALESCE gibi) sorgunuzu yeniden yazmanız, daha sonra da orafce'yi kaldırarak bu işlemi tekrar denemeniz olacak.</div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 15:40, "Ali Kemal DEMIRCI" &lt;demirci(dot)alikemal(at)gmail(dot)com&gt;:</div><blockquote><div>Merhaba Samed bey,<div> </div><div>Öncelikle pg_cron eklentisini kaldırdım ve yine aynı hatayı alıyorum.</div><div> </div><div>Söylediğiniz parametrelerin değerleri;</div><div> </div><div><font face="monospace">session_preload_libraries|<br />-------------------------|<br />                         |</font></div><div> </div><div><font face="monospace">shared_preload_libraries|<br />------------------------|<br />                        |</font></div><div> </div><div><font face="monospace">local_preload_libraries|<br />-----------------------|<br />                       |</font></div><div> </div><div>Sorularınızın cevapları;</div><div> </div><div><font face="monospace">1- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit</font></div><div> </div><div><font face="monospace">2- 30 GB Ram (28 GB Free), </font></div><div><font face="monospace">   16 Core CPU (% 95 Idle), </font></div><div><font face="monospace">   2.5 TB disk (1.4 TB Free)</font></div><div> </div><div><font face="monospace">3- postgresql.conf</font></div><div><font face="monospace">   shared_buffers = 128MB</font></div><div><font face="monospace">   dynamic_shared_memory_type = posix</font></div><div><font face="monospace">   max_wal_size = 1GB</font></div><div><font face="monospace">   min_wal_size = 80MB</font></div><div> </div><div><font face="monospace">4- Kurulu olan extension'lar</font></div><div><font face="monospace">postgis<br />orafce</font></div><div> </div><div><font face="monospace">5- Subquery Scan on r  (cost=463362.37..515099.44 rows=80000 width=282)                                                                                                                                                                                        |</font></div><font face="monospace">  -&gt;  Limit  (cost=463362.37..514299.44 rows=80000 width=534)                                                                                                                                                                                                  |<br />        -&gt;  Hash Join  (cost=463362.37..774820.91 rows=489166 width=534)                                                                                                                                                                                       |<br />              Hash Cond: (a.id_abone_adres_uavt = <a href="http://bf.id/">bf.id</a>)                                                                                                                                                                                                       |<br />              -&gt;  Hash Left Join  (cost=439755.24..715093.91 rows=664869 width=129)                                                                                                                                                                            |<br />                    Hash Cond: ((((((((((nvl(a.id_sayac_marka, '-9'::numeric))::text || '~'::text) || (nvl(a.sayac_capi, '-9'::integer))::text) || '~'::text) || (nvl(a.sayac_devirrakami, '-9'::numeric))::text) || '~'::text) || nvl(btrim(upper(a.sayac_tipi|<br />                    -&gt;  Merge Right Join  (cost=439714.19..687627.01 rows=664869 width=147)                                                                                                                                                                    |<br />                          Merge Cond: (x.id_abone = a.id_abone)                                                                                                                                                                                                |<br />                          -&gt;  GroupAggregate  (cost=439713.76..460925.11 rows=729055 width=86)                                                                                                                                                                 |<br />                                Group Key: x.id_abone, x.id_abone_tipi, x.bitis_tarihi, x.abone_durum                                                                                                                                                          |<br />                                -&gt;  Sort  (cost=439713.76..442497.92 rows=1113664 width=78)                                                                                                                                                                    |<br />                                      Sort Key: x.id_abone, x.id_abone_tipi, x.bitis_tarihi, x.abone_durum                                                                                                                                                     |<br />                                      -&gt;  Subquery Scan on x  (cost=189912.33..228890.57 rows=1113664 width=78)                                                                                                                                                |<br />                                            -&gt;  WindowAgg  (cost=189912.33..217753.93 rows=1113664 width=84)                                                                                                                                                   |<br />                                                  -&gt;  Sort  (cost=189912.33..192696.49 rows=1113664 width=30)                                                                                                                                                  |<br />                                                        Sort Key: sas.id_abone, sas.id_abone_sicil                                                                                                                                                             |<br />                                                        -&gt;  Seq Scan on su_abone_sicil sas  (cost=0.00..51416.64 rows=1113664 width=30)                                                                                                                        |<br />                          -&gt;  Index Scan using sql121209005336850 on su_abone a  (cost=0.42..210562.64 rows=664869 width=115)                                                                                                                                  |<br />                                Filter: (id_abone_adres_uavt IS NOT NULL)                                                                                                                                                                                      |<br />                    -&gt;  Hash  (cost=27.69..27.69 rows=1069 width=16)                                                                                                                                                                                           |<br />                          -&gt;  Seq Scan on cs_meter_model_prm mmp  (cost=0.00..27.69 rows=1069 width=16)                                                                                                                                                        |<br />              -&gt;  Hash  (cost=13999.06..13999.06 rows=585606 width=6)                                                                                                                                                                                          |<br />                    -&gt;  Seq Scan on gis_building_flat bf  (cost=0.00..13999.06 rows=585606 width=6)                                                                                                                                                            |</font><div> <div> </div><div> </div></div></div> <div><div>On Wed, Aug 12, 2020 at 1:22 PM Samed YILDIRIM &lt;<a href="mailto:samed(at)reddoc(dot)net">samed(at)reddoc(dot)net</a>&gt; wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div>Merhabalar,</div><div> </div><div>1. Hangi PostgreSQL versiyonunu kullanıyorsunuz?</div><div>2. Sistem kaynakları nedir?</div><div>3. Yapılandırma ayalarınızı paylaşır mısınız.</div><div>4. Veritabanında hangi eklentileri kullanıyorsunuz?</div><div>5. Explain ile planı alarak paylaşır mısınız.</div><div> </div><div>En önemli soru 4. soru. Veritabanı <font face="monospace">"Segmentation fault"<span style="font-family:'arial' , sans-serif"> 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.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">Loglardan ilk gözüme çarpan pg_cron kullandığınız. Onu kaldırmayı deneyebilirsiniz. </span></font>local_preload_libraries, session_preload_libraries ve shared_preload_libraries<font face="monospace"><span style="font-family:'arial' , sans-serif"> parametrelerinin değerlerini de kontrol ederseniz iyi olur.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">show </span></font>session_preload_libraries;</div><div>show shared_preload_libraries;</div><div>show local_preload_libraries;</div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 12:17, "Ali Kemal DEMIRCI" &lt;<a href="mailto:demirci(dot)alikemal(at)gmail(dot)com">demirci(dot)alikemal(at)gmail(dot)com</a>&gt;:</div><blockquote><div><div><font face="monospace">Merhaba arkadaşlar,</font></div><div> </div><font face="monospace">Create table xxx as</font><div><font face="monospace">Select ....</font></div><div><font face="monospace">Limit 80000;</font></div><div> </div><div>Yukardaki SQL komutunu çalıştırdığımda aşağıdaki hatayı alıyorum;</div><div> </div><div><font face="monospace">SQL Error [08006]: An I/O error occurred while sending to the backend.</font></div><div> </div><div>Fakat "Limit 70000" yaptığımda başarılı şekilde select ifadesinden dönen kayıtlarla tablo oluşturulabiliyor.</div><div> </div><div>Hat oluştuğunda log dosyasında oluşan kayıtlar aşağıdaki gibidir.</div><div>Bu konuda yardımcı olabilirmisiniz?</div><div> </div><div><font face="monospace"><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] LOG:  server process (PID 6072) was terminated by signal 11: Segmentation fault<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] DETAIL:  Failed process was running: create table cs_subscriber_ as<br />        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, <br />              r.creuser created_user_id, r.credate created_timestamp, 0::numeric "version", r.address address_description<br />        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,<br />                      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,<br />                      <a href="http://mmp.id/">mmp.id</a> 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,<br />                      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,<br />                      ('1'||lpad(a.defter_no::tex<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] LOG:  terminating any other active server processes<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6074] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6074] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6030] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6030] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6073] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6073] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6075] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6075] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6056] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6056] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6022] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6022] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.041 +03 [94157] LOG:  all server processes terminated; reinitializing<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.066 +03 [6086] LOG:  database system was interrupted; last known up at <span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:46 +03<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.093 +03 [6087] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.373 +03 [6088] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.415 +03 [6089] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.449 +03 [6090] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.493 +03 [6086] LOG:  database system was not properly shut down; automatic recovery in progress<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.504 +03 [6086] LOG:  redo starts at EC/8764F088<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.600 +03 [6086] LOG:  invalid record length at EC/88476D80: wanted 24, got 0<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.600 +03 [6086] LOG:  redo done at EC/88476CB8<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.675 +03 [94157] LOG:  database system is ready to accept connections<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.679 +03 [6101] LOG:  pg_cron scheduler started</font><div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></div></div></blockquote></blockquote></div> <div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></blockquote>

Merhaba Samed bey,

Problem NVL komutundan kaynaklanıyormuş, NVL yerine COALESCE kullanınca
problem ortadan kalktı.
Orafce ile gelen NVL komutunu SELECT ifadesi içerisinde kullanırken problem
olmuyor, fakat CREATE TABLE ... AS SELECT ... şeklinde kullandığınızda bu
hata oluşuyor.
Aynı şekilde fonksiyon içerisinde NVL kullanırken de aynı hatayı verip
veritabanını restart yapıyor.

O zaman Orafce'nin kullanılması sakıncalı mıdır?
Yoksa başka birşeyler mi yapmak gerekiyor?

On Wed, Aug 12, 2020 at 4:00 PM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:

> Merhabalar Ali Bey,
>
> Gördüğüm kadarıyla Postgres'i varsayılan ayarları ile kullanıyorsunuz. Bu
> durum alabileceğiniz verimin altında kalmanıza neden olur. Sunucuyu
> yalnızca veritabanına koşturmak için kullanacaksanız, yapılandırmasını
> sistem kaynağına göre düzenlemenizi önemle tavsiye ederim. :)
>
> Postgis'den kaynaklı olarak segmentation fault alıyor olma ihtimaliniz
> bence düşük. Daha çok orafce'den şüphe ediyorum. Tavsiyem, bu sorgunuz
> özelinde orafce'ye bağımlı olan kısımları Postgres'in kendisinde bulunan
> alternatifler ile değiştirerek (NVL -> COALESCE gibi) sorgunuzu yeniden
> yazmanız, daha sonra da orafce'yi kaldırarak bu işlemi tekrar denemeniz
> olacak.
> Best regards.
> Samed YILDIRIM
>
>
>
> 12.08.2020, 15:40, "Ali Kemal DEMIRCI" <demirci(dot)alikemal(at)gmail(dot)com>:
>
> Merhaba Samed bey,
>
> Öncelikle pg_cron eklentisini kaldırdım ve yine aynı hatayı alıyorum.
>
> Söylediğiniz parametrelerin değerleri;
>
> session_preload_libraries|
> -------------------------|
> |
>
> shared_preload_libraries|
> ------------------------|
> |
>
> local_preload_libraries|
> -----------------------|
> |
>
> Sorularınızın cevapları;
>
> 1- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1
> 20191121 (Red Hat 8.3.1-5), 64-bit
>
> 2- 30 GB Ram (28 GB Free),
> 16 Core CPU (% 95 Idle),
> 2.5 TB disk (1.4 TB Free)
>
> 3- postgresql.conf
> shared_buffers = 128MB
> dynamic_shared_memory_type = posix
> max_wal_size = 1GB
> min_wal_size = 80MB
>
> 4- Kurulu olan extension'lar
> postgis
> orafce
>
> 5- Subquery Scan on r (cost=463362.37..515099.44 rows=80000 width=282)
>
>
> |
> -> Limit (cost=463362.37..514299.44 rows=80000 width=534)
>
>
> |
> -> Hash Join (cost=463362.37..774820.91 rows=489166 width=534)
>
>
> |
> Hash Cond: (a.id_abone_adres_uavt = bf.id)
>
>
> |
> -> Hash Left Join (cost=439755.24..715093.91 rows=664869
> width=129)
>
> |
> Hash Cond: ((((((((((nvl(a.id_sayac_marka,
> '-9'::numeric))::text || '~'::text) || (nvl(a.sayac_capi,
> '-9'::integer))::text) || '~'::text) || (nvl(a.sayac_devirrakami,
> '-9'::numeric))::text) || '~'::text) || nvl(btrim(upper(a.sayac_tipi|
> -> Merge Right Join (cost=439714.19..687627.01
> rows=664869 width=147)
>
> |
> Merge Cond: (x.id_abone = a.id_abone)
>
>
> |
> -> GroupAggregate (cost=439713.76..460925.11
> rows=729055 width=86)
>
> |
> Group Key: x.id_abone, x.id_abone_tipi,
> x.bitis_tarihi, x.abone_durum
>
> |
> -> Sort (cost=439713.76..442497.92
> rows=1113664 width=78)
>
> |
> Sort Key: x.id_abone,
> x.id_abone_tipi, x.bitis_tarihi, x.abone_durum
>
> |
> -> Subquery Scan on x
> (cost=189912.33..228890.57 rows=1113664 width=78)
>
> |
> -> WindowAgg
> (cost=189912.33..217753.93 rows=1113664 width=84)
>
> |
> -> Sort
> (cost=189912.33..192696.49 rows=1113664 width=30)
>
> |
> Sort Key:
> sas.id_abone, sas.id_abone_sicil
>
> |
> -> Seq Scan on
> su_abone_sicil sas (cost=0.00..51416.64 rows=1113664 width=30)
>
> |
> -> Index Scan using sql121209005336850 on
> su_abone a (cost=0.42..210562.64 rows=664869 width=115)
>
> |
> Filter: (id_abone_adres_uavt IS NOT NULL)
>
>
> |
> -> Hash (cost=27.69..27.69 rows=1069 width=16)
>
>
> |
> -> Seq Scan on cs_meter_model_prm mmp
> (cost=0.00..27.69 rows=1069 width=16)
>
> |
> -> Hash (cost=13999.06..13999.06 rows=585606 width=6)
>
>
> |
> -> Seq Scan on gis_building_flat bf
> (cost=0.00..13999.06 rows=585606 width=6)
>
> |
>
>
>
>
> On Wed, Aug 12, 2020 at 1:22 PM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:
>
> 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 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İ
>
>
>
> --
> Saygılarımla,
>
> Ali Kemal DEMİRCİ
>
>

--
Saygılarımla,

Ali Kemal DEMİRCİ

<div>Selamlar Ali Bey,</div><div> </div><div>Sakıncalı demek doğru olmaz. Görünüşe göre sizin bir Oracle uyumluluk ihtiyacınız var, çözümlerden bir tanesi de orafce. Yaşadığınız sorun eklenti içerisindeki bir bug'a işaret ediyor. Sizin yerinizde olsam, olabildiğince bu tarz ara katman kullanmaktan kaçınır, mümkün olduğunca uygulamamı ve sorgularımı Oracle spesifik komutlardan/fonksiyonlardan arındırıp Postgres alternatiflerini kullanırdım. Bu tarzda hataların oluşabileceğini aklımda tutar, o dikkatle kullanmaya devam edecek isem orafce'yi kullanmaya devam ederim. Diğer taraftan da orafce projesine bug raporlaması yapardım :)</div><div> </div><div>Eğer ki Oracle bağımlığından kurtulmak sizin için hem zaman hem de parasal anlamda maliyetli olacak ise EnterpriseDB'yi (Postgres Advanced Server) bir incelemenizi tavsiye ederim. Türkiye'de distribütörleri de var, Profelis. Hoşunuza gider ise onlar ile de iletişime geçebilirsiniz.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 16:57, "Ali Kemal DEMIRCI" &lt;demirci(dot)alikemal(at)gmail(dot)com&gt;:</div><blockquote><div>Merhaba Samed bey,<div> </div><div>Problem NVL komutundan kaynaklanıyormuş, NVL yerine COALESCE kullanınca problem ortadan kalktı.</div><div>Orafce ile gelen NVL komutunu SELECT ifadesi içerisinde kullanırken problem olmuyor, fakat CREATE TABLE ... AS SELECT ... şeklinde kullandığınızda bu hata oluşuyor.</div><div>Aynı şekilde fonksiyon içerisinde NVL kullanırken de aynı hatayı verip veritabanını restart yapıyor.</div><div> </div><div>O zaman Orafce'nin kullanılması sakıncalı mıdır? </div><div>Yoksa başka birşeyler mi yapmak gerekiyor?</div><div> </div></div> <div><div>On Wed, Aug 12, 2020 at 4:00 PM Samed YILDIRIM &lt;<a href="mailto:samed(at)reddoc(dot)net">samed(at)reddoc(dot)net</a>&gt; wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div>Merhabalar Ali Bey,</div><div> </div><div>Gördüğüm kadarıyla Postgres'i varsayılan ayarları ile kullanıyorsunuz. Bu durum alabileceğiniz verimin altında kalmanıza neden olur. Sunucuyu yalnızca veritabanına koşturmak için kullanacaksanız, yapılandırmasını sistem kaynağına göre düzenlemenizi önemle tavsiye ederim. :)  </div><div> </div><div>Postgis'den kaynaklı olarak segmentation fault alıyor olma ihtimaliniz bence düşük. Daha çok orafce'den şüphe ediyorum. Tavsiyem, bu sorgunuz özelinde orafce'ye bağımlı olan kısımları Postgres'in kendisinde bulunan alternatifler ile değiştirerek (NVL -&gt; COALESCE gibi) sorgunuzu yeniden yazmanız, daha sonra da orafce'yi kaldırarak bu işlemi tekrar denemeniz olacak.</div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 15:40, "Ali Kemal DEMIRCI" &lt;<a href="mailto:demirci(dot)alikemal(at)gmail(dot)com">demirci(dot)alikemal(at)gmail(dot)com</a>&gt;:</div><blockquote><div>Merhaba Samed bey,<div> </div><div>Öncelikle pg_cron eklentisini kaldırdım ve yine aynı hatayı alıyorum.</div><div> </div><div>Söylediğiniz parametrelerin değerleri;</div><div> </div><div><font face="monospace">session_preload_libraries|<br />-------------------------|<br />                         |</font></div><div> </div><div><font face="monospace">shared_preload_libraries|<br />------------------------|<br />                        |</font></div><div> </div><div><font face="monospace">local_preload_libraries|<br />-----------------------|<br />                       |</font></div><div> </div><div>Sorularınızın cevapları;</div><div> </div><div><font face="monospace">1- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit</font></div><div> </div><div><font face="monospace">2- 30 GB Ram (28 GB Free), </font></div><div><font face="monospace">   16 Core CPU (% 95 Idle), </font></div><div><font face="monospace">   2.5 TB disk (1.4 TB Free)</font></div><div> </div><div><font face="monospace">3- postgresql.conf</font></div><div><font face="monospace">   shared_buffers = 128MB</font></div><div><font face="monospace">   dynamic_shared_memory_type = posix</font></div><div><font face="monospace">   max_wal_size = 1GB</font></div><div><font face="monospace">   min_wal_size = 80MB</font></div><div> </div><div><font face="monospace">4- Kurulu olan extension'lar</font></div><div><font face="monospace">postgis<br />orafce</font></div><div> </div><div><font face="monospace">5- Subquery Scan on r  (cost=463362.37..515099.44 rows=80000 width=282)                                                                                                                                                                                        |</font></div><font face="monospace">  -&gt;  Limit  (cost=463362.37..514299.44 rows=80000 width=534)                                                                                                                                                                                                  |<br />        -&gt;  Hash Join  (cost=463362.37..774820.91 rows=489166 width=534)                                                                                                                                                                                       |<br />              Hash Cond: (a.id_abone_adres_uavt = <a href="http://bf.id/">bf.id</a>)                                                                                                                                                                                                       |<br />              -&gt;  Hash Left Join  (cost=439755.24..715093.91 rows=664869 width=129)                                                                                                                                                                            |<br />                    Hash Cond: ((((((((((nvl(a.id_sayac_marka, '-9'::numeric))::text || '~'::text) || (nvl(a.sayac_capi, '-9'::integer))::text) || '~'::text) || (nvl(a.sayac_devirrakami, '-9'::numeric))::text) || '~'::text) || nvl(btrim(upper(a.sayac_tipi|<br />                    -&gt;  Merge Right Join  (cost=439714.19..687627.01 rows=664869 width=147)                                                                                                                                                                    |<br />                          Merge Cond: (x.id_abone = a.id_abone)                                                                                                                                                                                                |<br />                          -&gt;  GroupAggregate  (cost=439713.76..460925.11 rows=729055 width=86)                                                                                                                                                                 |<br />                                Group Key: x.id_abone, x.id_abone_tipi, x.bitis_tarihi, x.abone_durum                                                                                                                                                          |<br />                                -&gt;  Sort  (cost=439713.76..442497.92 rows=1113664 width=78)                                                                                                                                                                    |<br />                                      Sort Key: x.id_abone, x.id_abone_tipi, x.bitis_tarihi, x.abone_durum                                                                                                                                                     |<br />                                      -&gt;  Subquery Scan on x  (cost=189912.33..228890.57 rows=1113664 width=78)                                                                                                                                                |<br />                                            -&gt;  WindowAgg  (cost=189912.33..217753.93 rows=1113664 width=84)                                                                                                                                                   |<br />                                                  -&gt;  Sort  (cost=189912.33..192696.49 rows=1113664 width=30)                                                                                                                                                  |<br />                                                        Sort Key: sas.id_abone, sas.id_abone_sicil                                                                                                                                                             |<br />                                                        -&gt;  Seq Scan on su_abone_sicil sas  (cost=0.00..51416.64 rows=1113664 width=30)                                                                                                                        |<br />                          -&gt;  Index Scan using sql121209005336850 on su_abone a  (cost=0.42..210562.64 rows=664869 width=115)                                                                                                                                  |<br />                                Filter: (id_abone_adres_uavt IS NOT NULL)                                                                                                                                                                                      |<br />                    -&gt;  Hash  (cost=27.69..27.69 rows=1069 width=16)                                                                                                                                                                                           |<br />                          -&gt;  Seq Scan on cs_meter_model_prm mmp  (cost=0.00..27.69 rows=1069 width=16)                                                                                                                                                        |<br />              -&gt;  Hash  (cost=13999.06..13999.06 rows=585606 width=6)                                                                                                                                                                                          |<br />                    -&gt;  Seq Scan on gis_building_flat bf  (cost=0.00..13999.06 rows=585606 width=6)                                                                                                                                                            |</font><div> <div> </div><div> </div></div></div> <div><div>On Wed, Aug 12, 2020 at 1:22 PM Samed YILDIRIM &lt;<a href="mailto:samed(at)reddoc(dot)net">samed(at)reddoc(dot)net</a>&gt; wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div>Merhabalar,</div><div> </div><div>1. Hangi PostgreSQL versiyonunu kullanıyorsunuz?</div><div>2. Sistem kaynakları nedir?</div><div>3. Yapılandırma ayalarınızı paylaşır mısınız.</div><div>4. Veritabanında hangi eklentileri kullanıyorsunuz?</div><div>5. Explain ile planı alarak paylaşır mısınız.</div><div> </div><div>En önemli soru 4. soru. Veritabanı <font face="monospace">"Segmentation fault"<span style="font-family:'arial' , sans-serif"> 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.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">Loglardan ilk gözüme çarpan pg_cron kullandığınız. Onu kaldırmayı deneyebilirsiniz. </span></font>local_preload_libraries, session_preload_libraries ve shared_preload_libraries<font face="monospace"><span style="font-family:'arial' , sans-serif"> parametrelerinin değerlerini de kontrol ederseniz iyi olur.</span></font></div><div> </div><div><font face="monospace"><span style="font-family:'arial' , sans-serif">show </span></font>session_preload_libraries;</div><div>show shared_preload_libraries;</div><div>show local_preload_libraries;</div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>12.08.2020, 12:17, "Ali Kemal DEMIRCI" &lt;<a href="mailto:demirci(dot)alikemal(at)gmail(dot)com">demirci(dot)alikemal(at)gmail(dot)com</a>&gt;:</div><blockquote><div><div><font face="monospace">Merhaba arkadaşlar,</font></div><div> </div><font face="monospace">Create table xxx as</font><div><font face="monospace">Select ....</font></div><div><font face="monospace">Limit 80000;</font></div><div> </div><div>Yukardaki SQL komutunu çalıştırdığımda aşağıdaki hatayı alıyorum;</div><div> </div><div><font face="monospace">SQL Error [08006]: An I/O error occurred while sending to the backend.</font></div><div> </div><div>Fakat "Limit 70000" yaptığımda başarılı şekilde select ifadesinden dönen kayıtlarla tablo oluşturulabiliyor.</div><div> </div><div>Hat oluştuğunda log dosyasında oluşan kayıtlar aşağıdaki gibidir.</div><div>Bu konuda yardımcı olabilirmisiniz?</div><div> </div><div><font face="monospace"><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] LOG:  server process (PID 6072) was terminated by signal 11: Segmentation fault<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] DETAIL:  Failed process was running: create table cs_subscriber_ as<br />        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, <br />              r.creuser created_user_id, r.credate created_timestamp, 0::numeric "version", r.address address_description<br />        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,<br />                      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,<br />                      <a href="http://mmp.id/">mmp.id</a> 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,<br />                      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,<br />                      ('1'||lpad(a.defter_no::tex<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.037 +03 [94157] LOG:  terminating any other active server processes<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6074] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6074] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6030] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6030] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6073] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6073] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6075] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6075] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6056] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6056] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6022] WARNING:  terminating connection because of crash of another server process<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>: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.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.038 +03 [6022] HINT:  In a moment you should be able to reconnect to the database and repeat your command.<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.041 +03 [94157] LOG:  all server processes terminated; reinitializing<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.066 +03 [6086] LOG:  database system was interrupted; last known up at <span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:46 +03<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.093 +03 [6087] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.373 +03 [6088] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.415 +03 [6089] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.449 +03 [6090] FATAL:  the database system is in recovery mode<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.493 +03 [6086] LOG:  database system was not properly shut down; automatic recovery in progress<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.504 +03 [6086] LOG:  redo starts at EC/8764F088<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.600 +03 [6086] LOG:  invalid record length at EC/88476D80: wanted 24, got 0<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.600 +03 [6086] LOG:  redo done at EC/88476CB8<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.675 +03 [94157] LOG:  database system is ready to accept connections<br /><span class="1f1ea193f6735cf0wmi-callto">2020-08-12 11</span>:08:58.679 +03 [6101] LOG:  pg_cron scheduler started</font><div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></div></div></blockquote></blockquote></div> <div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></blockquote></blockquote></div> <div> </div>--<div>Saygılarımla,<br /><br />Ali Kemal DEMİRCİ</div></blockquote>

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İ