Not able to update some rows in a table

[Marco Fochesato]

Dear all,
I have a table with 21 columns.
Primary key is done with 20 of these colums.

I have 3.313 records.
I don't know why, but I'm not able to update some of these records.
I don't understand, it seems that I'm not able to fetch.
It happens with Pgadmin, with Python Psycopg2 and also through Libreoffice
Base.

Looking to the properties of the table in Pgadmin, I can see only the
estimated number
of rows. Counted rows are 'not counted'.

-- 
Marco Fochesato

Re: Not able to update some rows in a table

[Marco Fochesato]

>
> postgres version
>
10, but I have loaded the database in older versions of Postgresql and the
problem remain.

> your table structure
>
CREATE TABLE public.forniture_ee
(
  id_cliente integer NOT NULL,
  pod character varying(14) NOT NULL,
  id_fornitore integer NOT NULL,
  mese integer NOT NULL,
  anno integer NOT NULL,
  imponibile_fornitura double precision NOT NULL,
  lettura character varying NOT NULL,
  f1 double precision NOT NULL,
  f2 double precision NOT NULL,
  f3 double precision NOT NULL,
  picco double precision NOT NULL,
  fuori_picco double precision NOT NULL,
  mono double precision NOT NULL,
  perdite_di_rete double precision NOT NULL,
  potenza_max double precision NOT NULL,
  potenza_disponibile double precision NOT NULL,
  costo_potenza_max double precision NOT NULL,
  energia_reattiva double precision NOT NULL,
  imponibile_energia double precision NOT NULL,
  report_inviato boolean,
  energia_e_perdite double precision NOT NULL,
  CONSTRAINT forniture_ee_pkey PRIMARY KEY (id_cliente, pod, id_fornitore,
mese, anno, imponibile_fornitura, lettura, f1, f2, f3, picco, fuori_picco,
mono, perdite_di_rete, potenza_max, potenza_disponibile, costo_potenza_max,
energia_reattiva, imponibile_energia, energia_e_perdite),
  CONSTRAINT forniture_ee_id_cliente_fkey FOREIGN KEY (id_cliente)
  REFERENCES public.anagrafica_clienti (id_cliente) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT forniture_ee_id_fornitore_fkey FOREIGN KEY (id_fornitore)
  REFERENCES public.anagrafica_fornitori (id_fornitore) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT forniture_ee_pod_fkey FOREIGN KEY (pod)
  REFERENCES public.pod (pod) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.forniture_ee
  OWNER TO postgres;

> how you are updating the records
>
Also by opening the table on PGADMIN.

> if the problem is reproducible directly from the postgres prompt
>
I don't have try.

> how you are fetching results after the update
>
 By refreshing the table, in PgAdmin.

> anything else to make the situation more clear (like errors in the
> logfile, or anything else you think is relevant)
>
No errors in the GUI, no errors in PgAdmin.log



-- 
Marco Fochesato

Re: Not able to update some rows in a table

[Marco Fochesato]

>
> I would suggest writing a self-contained script that creates the table,
> inserts a single record, and updates that record.  Present that for
> consideration along with a description or capture of the results of running
> the script on your machine.
>
>
But not all the records fail the update...

Re: Not able to update some rows in a table

[Marco Fochesato]

>
>
> That seems like a remarkably strange definition of a primary key.
> Are all of those columns really identification of an object, and
> not properties of the object?
>

> In any case, I'll bet a lot of money that your can't-update problem
> is related to the fuzzy behavior of floating-point columns.  Just
> because a float8 value prints out as "1.2345" doesn't mean that it'll
> be considered equal to a value you write as "1.2345".  If this fact
> astonishes you, you should likely switch to type numeric.
>
> regards, tom lane
>

Dear Tom, you can bet all your money because you are right.
I drop the table and re-created using numeric instead double precision.
Now everything is ok, I can update every row of the table.
Thank you.

-- 
Marco Fochesato