X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 17F04D1B4FC for ; Fri, 26 Sep 2003 14:06:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 20104-05 for ; Fri, 26 Sep 2003 11:05:56 -0300 (ADT) Received: from colsweeper.cranel.com (newmail.cranel.com [12.32.71.147]) by svr1.postgresql.org (Postfix) with ESMTP id B1D51D1B507 for ; Fri, 26 Sep 2003 11:05:51 -0300 (ADT) Received: from colmail01.cranel.com (colmail01.cranel.com) by colsweeper.cranel.com (Content Technologies SMTPRS 4.2.5) with ESMTP id for ; Fri, 26 Sep 2003 10:08:12 -0400 Received: from cranel.com (gspiegelberg.cranel.com [192.168.11.134]) by colmail01.cranel.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TM0YCDNY; Fri, 26 Sep 2003 10:08:42 -0400 Message-ID: <3F7447BE.9060200@cranel.com> Date: Fri, 26 Sep 2003 10:05:50 -0400 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020408 X-Accept-Language: en-us, en MIME-Version: 1.0 To: PGSQL PHP Subject: Same query difference results Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/79 X-Sequence-Number: 2356 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.