pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16871: Insert with wrong key field, causing later crash of DB.
2+ messages / 2 participants
[nested] [flat]

* BUG #16871: Insert with wrong key field, causing later crash of DB.
@ 2021-02-17 11:24 PG Bug reporting form <noreply@postgresql.org>
  2021-02-17 16:02 ` Re: BUG #16871: Insert with wrong key field, causing later crash of DB. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2021-02-17 11:24 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: david@littleriver.ca

The following bug has been logged on the website:

Bug reference:      16871
Logged by:          David Waddy
Email address:      david@littleriver.ca
PostgreSQL version: 12.6
Operating system:   linux
Description:        

This has happened three times to me over the last few months.  Database
crashes later on insert with faulty char[32] key field, matthew vs proper
key of matt.  

PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

2021-02-16 10:58:13 AST STATEMENT:  INSERT INTO
bovinemanagement.medicine_administered 
(bovine_id,medicine_index,scheduled_event_time,dosage,dosage_unit,location,note,medical_case_id,scheduled_userid,calendar_event_id,protocol_uuid,uuid)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2021-02-16 10:58:15 AST ERROR:  invalid input syntax for type uuid: "2"
2021-02-16 10:58:15 AST STATEMENT:  INSERT INTO
bovinemanagement.medicine_administered 
(bovine_id,medicine_index,scheduled_event_time,dosage,dosage_unit,location,note,medical_case_id,scheduled_userid,calendar_event_id,protocol_uuid,uuid)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2021-02-16 10:58:17 AST ERROR:  invalid input syntax for type uuid: "2"
2021-02-16 10:58:17 AST STATEMENT:  INSERT INTO
bovinemanagement.medicine_administered 
(bovine_id,medicine_index,scheduled_event_time,dosage,dosage_unit,location,note,medical_case_id,scheduled_userid,calendar_event_id,protocol_uuid,uuid)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2021-02-17 00:25:29 AST ERROR:  insert or update on table "employee_shift"
violates foreign key constraint "employee_shift_userid_fkey"
2021-02-17 00:25:29 AST DETAIL:  Key (userid)=(matthew                      
  ) is not present in table "users".
2021-02-17 00:25:29 AST STATEMENT:  INSERT INTO batch.employee_shift
(userid, date, shift,milking_number) VALUES ('matthew', '2019-09-14',
'am',1);
2021-02-17 06:23:09 AST LOG:  received fast shutdown request
2021-02-17 06:23:09 AST LOG:  aborting any active transactions
2021-02-17 06:23:09 AST FATAL:  terminating connection due to administrator
command
2021-02-17 06:23:09 AST FATAL:  terminating connection due to administrator
command
2021-02-17 06:23:09 AST LOG:  background worker "logical replication
launcher" (PID 4534) exited with exit code 1
2021-02-17 06:23:09 AST FATAL:  terminating connection due to administrator
command
2021-02-17 06:23:09 AST LOG:  shutting down
2021-02-17 06:23:10 AST LOG:  database system is shut down
2021-02-17 07:09:29 AST LOG:  starting PostgreSQL 12.6 (Ubuntu
12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-02-17 07:09:29 AST LOG:  listening on IPv4 address "0.0.0.0", port
5432
2021-02-17 07:09:29 AST LOG:  listening on IPv6 address "::", port 5432
2021-02-17 07:09:29 AST LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2021-02-17 07:09:29 AST LOG:  database system was shut down at 2021-02-17
06:23:10 AST
2021-02-17 07:09:29 AST LOG:  database system is ready to accept connections



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16871: Insert with wrong key field, causing later crash of DB.
  2021-02-17 11:24 BUG #16871: Insert with wrong key field, causing later crash of DB. PG Bug reporting form <noreply@postgresql.org>
@ 2021-02-17 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2021-02-17 16:02 UTC (permalink / raw)
  To: david@littleriver.ca; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> This has happened three times to me over the last few months.  Database
> crashes later on insert with faulty char[32] key field, matthew vs proper
> key of matt.  

> 2021-02-17 00:25:29 AST ERROR:  insert or update on table "employee_shift"
> violates foreign key constraint "employee_shift_userid_fkey"
> 2021-02-17 00:25:29 AST DETAIL:  Key (userid)=(matthew                      
>   ) is not present in table "users".
> 2021-02-17 00:25:29 AST STATEMENT:  INSERT INTO batch.employee_shift
> (userid, date, shift,milking_number) VALUES ('matthew', '2019-09-14',
> 'am',1);
> 2021-02-17 06:23:09 AST LOG:  received fast shutdown request
> 2021-02-17 06:23:09 AST LOG:  aborting any active transactions

Well, that's not a crash; that's a commanded shutdown.  (The *only*
way to get that message is for something to send the postmaster a
SIGINT signal.)  It's fairly hard to credit that there's any direct
connection to a simple SQL error six hours earlier, either.

I'm going to venture out on a limb here, but I'd suggest reviewing
exactly how you're starting Postgres.  If you're launching it from
a manual shell command, and you're not being careful to fully dissociate
it from the shell's terminal, then you can get results like this when
you later type control-C, log out from that session, etc.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2021-02-17 16:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-17 11:24 BUG #16871: Insert with wrong key field, causing later crash of DB. PG Bug reporting form <noreply@postgresql.org>
2021-02-17 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox