Thread: Same query difference results

Same query difference results

From
Greg Spiegelberg
Date:
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@Cranel.com
Cranel. Technology. Integrity. Focus.