I have a table that tracks changes over time and the automated process
which inserts the data as it comes in uses the same query as a week
ago but is behaving strangely. The automated process is saving all
rows whether there is a change or not however if I walk through it
manually cutting & pasting the same SQL in psql the results are as
needed.
Below is the table and the process we use to insert the changes. Any
help would be greatly appreciated.
Table:
create table samples (
ss_id int8 references snapshots(ss_id),
name text,
attr1 varchar(16),
attr2 int8,
attr3 int8,
primary key(ss_id,name)
);
create table snapshots (
ss_id serial8 unique,
s_id int8 references systems(s_id),
ss_time int8 not null,
ss_lock boolean default FALSE,
primary key (ss_id,s_id,ss_time)
);
Insert process for ss_id=290:
begin;
-- Create empty temp table, ss_id never equals -1
select * into temporary table samples_290 from samples where ss_id=-1;
-- Load data to tmep table
copy samples_290 from '/var/tmpfs/samples_290.dat';
-- Insert changes only
insert into samples (
select distinct on(news.name) news.* from
(select * from samples_290 ) as news
full outer join
(select distinct on (f.name) * from samples f
where exists (select * from snapshots s where s.ss_s_id=295 and
s.ss_id!=290 and s.ss_time<(select ss_time from snapshots
where ss_id=290) and s.ss_id=f.ss_id order by s.ss_time desc))
as olds
on news.name=olds.name
where
-- if "name" exists in both tables
news.name is not null and
((news.attr1, news.attr2, news.attr3)
<>
(olds.attr1, olds.attr2, olds.attr3)
or
olds.name is null -- if "name" doesn't exist in the table yet
)); -- END of insert
commit;
--
Greg Spiegelberg
Sr. Product Development Engineer
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: gspiegelberg@xxxxxxxxxx
Cranel. Technology. Integrity. Focus.
