Re: Problems at columns

On Fri, 19 Jun 2020 at 09:42, Mattia Romagnoli
<24mattiaromagnoli96(at)gmail(dot)com> wrote:
>
> Hello! From 3 days a column of our database reset all the values to null.
> In the logs for queries the last query is from 6 hours ago of a name changed (of an update), but it happens almost 1 hour ago.
>
> Then no loggin in are made on the vps and also by SSHD.
>
> SO what is the problem? Is already the third time it happens and i don't know how to solve it.

There's not very much to go on here. I imagine the most likely cause
is that you have some UPDATE statement that does not do quite what you
think it does.

[1] is an example of how badly written SQL can UPDATE more rows that
you might expect it to.
You might also want to look for hazards like [2] which we can craft
into an UPDATE statement to have it, perhaps surprisingly update all
rows in t1 if there is at least 1 row in t2.

create table t1(a int primary key, b int);
create table t2 (c int primary key);

insert into t1 select x,x from generate_Series(1,10) x;
insert into t2 values(1);

-- the following will update all rows in t1 and set b to null.
update t1 set b = null where a in(select a from t2); --- column a
does not exist in t2!
UPDATE 10

This could happen quite innocently if "a" once existed in t2 but the
column was removed. You might expect an error, but it's perfectly
valid SQL.

I'd start by checking for things like that. You should always prefix
column names with the table name, or a short alias to stop these
things from ever happening. Not doing so in your production code is
asking for trouble.

However, perhaps you're not being hit bit that. There's very little to
go on here. If you have more evidence of an actual bug here, and
perhaps a way to recreate it, then please send along that information
to this thread.

David

[1] https://www.postgresql.org/message-id/16462-7160938c9c2989d9@postgresql.org
[2] https://www.postgresql.org/message-id/16400-84f68bb46ba963e5@postgresql.org