DISCARD PLANS;
with tmp as
(
	select tid
	from transport.transaction_specdata
	where state_code != transport.get_state_code_by_name('processing')
)
select *
from main.transaction t
	join tmp on t.id = tmp.tid;

CREATE OR REPLACE FUNCTION transport.get_state_code_by_name(_state_name text)
 RETURNS smallint AS ...
 LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION transport.garbage_collector(_max_store_time interval)
 RETURNS void AS
$BODY$
declare
	_cur_tid bigint;
	_processing_code smallint := transport.get_state_code_by_name('processing');
BEGIN
	for _cur_tid in 
		with tmp as
		(
			select tid, regtime::date as pdate, test_mode, state_code
			from transport.transaction_specdata
			where regtime < (now() - _max_store_time) and
				(test_mode > 0 or state_code != _processing_code)
		),
		del_trn as
		(
			-- delete test-mode transactions
			delete from main.transaction t
			using tmp
			where t.id = tmp.tid and t.pdate = tmp.pdate and tmp.test_mode > 0
		)
		delete from transport.transaction_specdata s
		using tmp
		where s.tid = tmp.tid and tmp.state_code != _processing_code
		returning s.tid
	loop
		raise notice '[Garbage collector][tid=%] The transaction was deleted', _cur_tid;
	end loop;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;