Page 1 of 1

Bad concurrency checking on field charvar null?

Posted: Mon 17 Aug 2009 14:26
by Ariwibawa
Hello,

i'm using dcpostgresql450pro (4.50.33), postgresql 8.2

i found a problem when trying update field 'xx' that has allow null property.
i inspect on postgresql log, that update command sent to server was using "(xx is NULL)" and not the value store in db ('' double single quote), when i tried changing the where condition to = '' and run it from query analyse it was updated.

here is my test scenario:
1. insert detailname='aaa', detaildescription='bbbb'
save succesfully
2. update detaildescription='' (im not intepret in my code to store null if string is empty)
save successfully
3. update again detaildescription='ffff' (detailname is leave)
ChangeConflictException occurred
Row not found or changed

this is log from postgresql log_statement on update error
2009-08-17 07:16:52 LOG: execute PRSTMT192028063088642357/PORTAL192028063088642357: UPDATE public."SampleDetail" SET "DetailDescription" = $1, "ModifiedOn" = $2 WHERE "DetailId" = $3 AND "CreatedBy" = $4 AND (($5 = 1 AND "ModifiedBy" IS NULL) OR ("ModifiedBy" = $6)) AND "RowStatus" = $7 AND "DetailName" = $8 AND (($9 = 1 AND "DetailDescription" IS NULL) OR ("DetailDescription" = $10)) AND "MasterId" = $11 AND (($12 = 1 AND "CreatedOn" IS NULL) OR ("CreatedOn" = $13)) AND (($14 = 1 AND "ModifiedOn" IS NULL) OR ("ModifiedOn" = $15))
2009-08-17 07:16:52 DETAIL: parameters: $1 = 'gg', $2 = '2009-08-17 21:16:52.645', $3 = '3', $4 = 'Arie', $5 = '0', $6 = 'Arie', $7 = '0', $8 = 'wefsg534gg', $9 = '1', $10 = NULL, $11 = '2', $12 = '0', $13 = '2009-08-16 23:55:40', $14 = '0', $15 = '2009-08-17 20:49:08.902'

here is the table description:
-- Table: "ParentTable"

-- DROP TABLE "ParentTable";

CREATE TABLE "ParentTable"
(
"CreatedBy" character varying(10) NOT NULL, -- Store userid of current row
"ModifiedBy" character varying(10),
"RowStatus" smallint NOT NULL DEFAULT 1,
"CreatedOn" timestamp without time zone,
"ModifiedOn" timestamp without time zone
)
WITHOUT OIDS;
ALTER TABLE "ParentTable" OWNER TO postgres;
COMMENT ON COLUMN "ParentTable"."CreatedBy" IS 'Store userid of current row';

-- Table: "SampleMaster"

-- DROP TABLE "SampleMaster";

CREATE TABLE "SampleMaster"
(
-- Inherited: "CreatedBy" character varying(10) NOT NULL,
-- Inherited: "ModifiedBy" character varying(10),
-- Inherited: "RowStatus" smallint NOT NULL DEFAULT 1,
"MasterId" integer NOT NULL,
"MasterName" character varying(50) NOT NULL,
-- Inherited: "CreatedOn" timestamp without time zone,
-- Inherited: "ModifiedOn" timestamp without time zone,
CONSTRAINT "MasterId" PRIMARY KEY ("MasterId")
) INHERITS ("ParentTable")
WITHOUT OIDS;
ALTER TABLE "SampleMaster" OWNER TO postgres;

-- Table: "SampleDetail"

-- DROP TABLE "SampleDetail";

CREATE TABLE "SampleDetail"
(
-- Inherited: "CreatedBy" character varying(10) NOT NULL,
-- Inherited: "ModifiedBy" character varying(10),
-- Inherited: "RowStatus" smallint NOT NULL DEFAULT 1,
"DetailId" integer NOT NULL DEFAULT nextval('"SampleDetail_DetailId_seq"'::regclass),
"DetailName" character varying(50) NOT NULL,
"DetailDescription" character varying(255),
"MasterId" integer NOT NULL,
-- Inherited: "CreatedOn" timestamp without time zone,
-- Inherited: "ModifiedOn" timestamp without time zone,
CONSTRAINT "DetailId" PRIMARY KEY ("DetailId"),
CONSTRAINT "MasterId" FOREIGN KEY ("MasterId")
REFERENCES "SampleMaster" ("MasterId") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
) INHERITS ("ParentTable")
WITHOUT OIDS;
ALTER TABLE "SampleDetail" OWNER TO postgres;

Thanks & Regards,
Ariwibawa

Posted: Tue 18 Aug 2009 10:52
by AndreyR
I have tried to reproduce the described problem using the latest dotConnect for PostgreSQL 4.55.39
and everything went ok.
Please try to update to the latest build of dotConnect for PostgreSQL.
If it doesn't help, please send me the code you are using to update records.

Posted: Tue 18 Aug 2009 12:10
by Ariwibawa
Thanks Andrey
I will test it on latest built. I will update you soon.

Regards,
Ariwibawa

Posted: Wed 19 Aug 2009 14:51
by Ariwibawa
It's working now :D
Thanks for your support

Regards,
Ariwibawa