Bad concurrency checking on field charvar null?
Posted: Mon 17 Aug 2009 14:26
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
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