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

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İ