From: Greg Spiegelberg <gspiegelberg@cranel.com>
To: PGSQL PHP <pgsql-php@postgresql.org>
Subject: Same query difference results
Date: Fri, 26 Sep 2003 10:05:50 -0400
Message-ID: <3F7447BE.9060200@cranel.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: gspiegelberg@cranel.com
Subject: Re: Same query difference results
In-Reply-To: <3F7447BE.9060200@cranel.com>
References: <3F7447BE.9060200@cranel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox