This is a multi-part message in MIME format.
--------------050904020602050504040904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I'm sorry, but I attached the incorrect file the first time!

--------------050904020602050504040904
Content-Type: text/plain;
name="Luces_Importing_Problems.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="Luces_Importing_Problems.txt"

We are using MS Access as a frontend to import many water quality records into postgresql v. 8.0.2 on Red Hat 3 Enterprise.
When importing a table of 40,000+ records something is going wrong with the timestamp field only for certain records.
The records are listed below.


MS Access Details:

DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table:
ID original_deployment_code date_time water_temp depth_m salinity sp_cond do_per do_mg_l ph
1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352 30.1 46.299999 80.400002 6.42 7.64
2 MLM20010327 2001-04-01 02:30:00 17.700001 1.61544 30.200001 46.400002 78.699997 6.3 7.64
3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592 30.200001 46.400002 72.800003 5.84 7.62
4 MLM20010327 2001-04-01 03:30:00 17.639999 1.524 30.1 46.299999 79.300003 6.36 7.61

Here's the SQL run from Access:
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m )
SELECT original_deployment_code, c.id, lwq.date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m
FROM DD5 AS lwq INNER JOIN public_collection AS c ON lwq.original_deployment_code=c.original_collection _code;

I get a uniqueness constraint error.

Here are the Postgresql table details:

CREATE TABLE "public"."wq" (
"id" SERIAL,
"collection_id" INTEGER NOT NULL,
"date_time" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"original_deployment_code" VARCHAR(20),
"water_temp" NUMERIC(28,6),
"depth_m" NUMERIC(28,6),
"salinity" NUMERIC(28,6),
"sp_cond" NUMERIC(28,6),
"do_per" NUMERIC(28,6),
"do_mg_l" NUMERIC(28,6),
"ph" NUMERIC(28,6),
"turbidity" NUMERIC(28,6),
"chlorophyll_ug_l" NUMERIC(28,6),
"orp_mv" NUMERIC(28,6),
"reviewed" BOOLEAN DEFAULT false NOT NULL,
"date_entered" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"date_updated" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"entered_by" VARCHAR(50) NOT NULL,
"updated_by" VARCHAR(50) NOT NULL,
CONSTRAINT "wq_pkey" PRIMARY KEY("id"),
CONSTRAINT "fk_collection" FOREIGN KEY ("collection_id")
REFERENCES "public"."collection"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;

COMMENT ON TABLE "public"."wq"
IS 'This is continuous wq. Unique constraint is on collection and date_time. Do we need fields for raw and interpolated values???';

COMMENT ON COLUMN "public"."wq"."collection_id"
IS 'fk to collection table. ';

COMMENT ON COLUMN "public"."wq"."date_time"
IS 'Date that the sample was taken. This is usually in 30 minute increments per collection.';

COMMENT ON COLUMN "public"."wq"."original_deployment_code"
IS '???';

COMMENT ON COLUMN "public"."wq"."do_per"
IS 'Calculate this in a trigger?';

COMMENT ON COLUMN "public"."wq"."reviewed"
IS 'This defaults to false and must be set manually in order to verify entries. Possibly setup a view to filter these out.';

COMMENT ON COLUMN "public"."wq"."date_entered"
IS 'Triggered value upon insert. Will use current_timestamp unless specified.';

COMMENT ON COLUMN "public"."wq"."date_updated"
IS 'Triggered value upon update. Will use current_timestamp.';

COMMENT ON COLUMN "public"."wq"."entered_by"
IS 'Triggered value upon insert. Will use current_user unless otherwise specified.';

COMMENT ON COLUMN "public"."wq"."updated_by"
IS 'Triggered value upon update. Will use current_user.';

CREATE UNIQUE INDEX "wq_unique_key" ON "public"."wq"
USING btree ("collection_id", "date_time");

CREATE TRIGGER "new_wq_trig" BEFORE INSERT
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."new_record_logger"();

CREATE TRIGGER "update_wq_trig" BEFORE UPDATE
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."update_record_logger"();


****new_record_logger trigger****
BEGIN
if new.date_entered is null then
new.date_entered := current_timestamp;
end if;
if new.entered_by is null then
new.entered_by := current_user;
end if;
if new.date_updated is null then
new.date_updated := current_timestamp;
end if;
if new.updated_by is null then
new.updated_by := current_user;
end if;


return new;
END;


RESULTS:

Taking the uniqueness constraint off allows import and the above inserted data is below (Notice that 2:00 and 2:30 changed to 3:00 and 3:30 during the insert):

id collection_id date_time original_deployment_code water_temp depth_m salinity sp_cond do_per do_mg_l ph turbidity chlorophyll_ug_l orp_mv reviewed date_entered date_updated entered_by updated_by
204414 1982 2001-04-01 03:30:00 MLM20010327 17.639999 1.524 30.1 46.299999 79.300003 6.36 7.61 0 2005-07-27 14:28:39 2005-07-27 14:28:39 ddabney ddabney
204413 1982 2001-04-01 03:00:00 MLM20010327 17.67 1.64592 30.200001 46.400002 72.800003 5.84 7.62 0 2005-07-27 14:28:39 2005-07-27 14:28:39 ddabney ddabney
204412 1982 2001-04-01 03:30:00 MLM20010327 17.700001 1.61544 30.200001 46.400002 78.699997 6.3 7.64 0 2005-07-27 14:28:39 2005-07-27 14:28:39 ddabney ddabney
204411 1982 2001-04-01 03:00:00 MLM20010327 17.860001 1.49352 30.1 46.299999 80.400002 6.42 7.64 0 2005-07-27 14:28:39 2005-07-27 14:28:39 ddabney ddabney

I'm assuming this is a bug.




--------------050904020602050504040904
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------050904020602050504040904--

"David Dabney" <David.Dabney (AT) noaa (DOT) gov> writes:
Perhaps, but there is absolutely no chance of anyone reproducing the
problem from the information you've offered. You say "it fails when
I try to insert data from table DD5", but you give us no clue about
what data is in DD5.

There is some generic advice about how to create a useful bug report
here:
http://www.postgresql.org/docs/8.0/s...reporting.html

The short and sweet version of it is "give us a SQL script that
delivers a wrong result" ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom,

Thanks for the quick reply and I'm sorry I wasn't explicit enough in my
description. At the top of the attachment there is this section which
is the piece of data from DD5 that is causing the problem:

****************
MS Access Details:

DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table:
ID original_deployment_code date_time water_temp
depth_m salinity sp_cond do_per do_mg_l ph
1 MLM20010327 2001-04-01 02:00:00 17.860001
1.49352 30.1 46.299999 80.400002 6.42 7.64
2 MLM20010327 2001-04-01 02:30:00 17.700001
1.61544 30.200001 46.400002 78.699997 6.3 7.64
3 MLM20010327 2001-04-01 03:00:00 17.67
1.64592 30.200001 46.400002 72.800003 5.84 7.62
4 MLM20010327 2001-04-01 03:30:00 17.639999
1.524 30.1 46.299999 79.300003 6.36 7.61
********************

I needed to get this data in, so I tried to manually correct the invalid
date_time field after inserting and then put the uniqueness constraint
back on. From EMS I tried to change the times from 3:30 and 3:00 to
2:30 and 2:00. When I refresh the data it now shows 1:30 and 1:00 for
these records! I then tried to change 1:30 and 1:00 to 2:30 and 2:00
and it went back to 3:30 and 3:00.

So I backed up and tried to import directly from psql. The same results
happen as when I did it from MS Access.

I was driving home and realized 4/1/2001 was probably when daylight
savings time changed.....and it was. So I'm not sure if this is a bug
or not.

Regards,

Dave

Tom Lane wrote:

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

The more I thought about this I realized it's not a bug and I'm sorry to
have perstered the list. The fact that the data logger was deployed
before and after a time change and it was not setup to update itself
created this problem. I just have to push forward all the times after
the change and then import the data. We've been trying to get the
scientists to use UTC.....maybe this will help them understand why they
should be doing that.

Thanks,

Dave

PS Postgres rocks. Please keep up the good work!

David Dabney wrote:

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

David Dabney wrote:
The other thing you should do is use "timestamp with time zone" to store
your timestamps. Otherwise, you aren't storing an absolute time at all.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster