Обсуждение: Not able to update some rows in a table
--
Hi Marco,
I think more information would give to us a better picture.
can you please post the following?
postgres version
your table structure
how you are updating the records
if the problem is reproducible directly from the postgres prompt
how you are fetching results after the update
anything else to make the situation more clear (like errors in the logfile, or anything else you think is relevant)
regards,
fabio pardi
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 numberof rows. Counted rows are 'not counted'.
--Marco Fochesato
> I have a table with 21 columns. > Primary key is done with 20 of these colums. Oh, okay. What data types do these encompass? > 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. You mean you can't query them with all 20/21 columns present in the where clause?
Il 02/07/2018 16:51, Marco Fochesato ha scritto: > 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. Could you post some of the statement you execute? If you are unable to SELECT some record, it's quite straightforward that you can't UPDATE them. > It happens with Pgadmin, with Python Psycopg2 and also through > Libreoffice Base. OS? PG Version? > > Looking to the properties of the table in Pgadmin, I can see only the > estimated number > of rows. Counted rows are 'not counted'. Seems that autovacuum is not active, or has never run (but i'm quite sure it's not related to your problem, so it can be investigated later). Cheers Moreno.-
On 07/02/2018 07:51 AM, Marco Fochesato wrote: > Dear all, > I have a table with 21 columns. > Primary key is done with 20 of these colums. Do you mean the Primary Key is constructed from 20 columns? > > I have 3.313 records. > I don't know why, but I'm not able to update some of these records. What is the SQL you are using? Are there any error messages and if so what are they? > 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 -- Adrian Klaver adrian.klaver@aklaver.com
On 07/02/2018 08:31 AM, Marco Fochesato wrote: Please reply to list also. Ccing list. > > Do you mean the Primary Key is constructed from 20 columns? > > yes. > > > > > Are there any error messages and if so what are they? > > No error messages To answer your questions will need to see(per previous posts): 1) The schema of the table. 2) The SQL you are running. > > > > -- > Marco Fochesato -- Adrian Klaver adrian.klaver@aklaver.com
postgres version
your table structure
(
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
if the problem is reproducible directly from the postgres prompt
how you are fetching results after the update
anything else to make the situation more clear (like errors in the logfile, or anything else you think is relevant)
--
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
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.
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...
We won't ge anywhere until you show the actual sql and the actual error(s)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...
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...
Marco Fochesato <marcofoc@gmail.com> writes: > 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), 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
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...Do you have a trigger or a rule intercepting the update? You may see this with \d in a console.
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