Saturday, February 10, 2024

"ERROR: duplicate key value violates unique constraint" in PostgreSQL but not in Oracle/SQLServer

In both Oracle and SQL Server, these non-deferrable constraints are evaluated at the statement completion, and it does allow intermediate state which could break constraint rules as long as final state at the statement completion is compliant.

PostgreSQL implements this differently, and the output can depends on the physical data orders.

PostgreSQL Example

mytest=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
(1 row)
mytest=# drop table p;
DROP TABLE
mytest=# create table p (id int primary key);
CREATE TABLE
mytest=# insert into p values(1),(2),(3);
INSERT 0 3
mytest=# select * from p;
 id
----
  1
  2
  3
(3 rows)
mytest=# update p set id=id+1;
ERROR:  duplicate key value violates unique constraint "p_pkey"
DETAIL:  Key (id)=(2) already exists.

If we update the table backwards, it would succeed. As showed in followed code:

mytest=# do
$$
declare
  rec_p record;
  cur_p cursor for
       select id from p order by id desc;
begin
  open cur_p;
  loop
    fetch cur_p into rec_p;
    exit when not found;
      update p set id=id+1 where id=rec_p.id;
  end loop;
end;
$$ language plpgsql;
DO
mytest=# select * from p;
 id
----
  4
  3
  2
(3 rows)

If the data in different physical order, it would succeed too.

mytest=# truncate table p;
TRUNCATE TABLE
mytest=# insert into p values(3),(2),(1);
INSERT 0 3
mytest=# select * from p;
 id
----
  3
  2
  1
(3 rows)
mytest=# update p set id=id+1;
UPDATE 3
mytest=# select * from p;
 id
----
  4
  3
  2
(3 rows)

MS SQL Server Example

[oracle@ol ~]$ sqlcmd  -S localhost -U sa -C -P p_ssw0rd -d TestDB
1> select @@version;
2> go

-------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64)
  Nov 14 2023 18:33:19
  Copyright (C) 2022 Microsoft Corporation
  Developer Edition (64-bit) on Linux (Oracle Linux Server 9.3) <X64>

(1 rows affected)
1> create table p (id int primary key);
2> go

1> select * from t;
2> go

1> select * from p;
2> go
id
-----------

(0 rows affected)

1> insert into p values(1),(2),(3);
2> go

(3 rows affected)
1> update p set id=id+1;
2> go

(3 rows affected)
1> select * from p;
2> go

id
-----------
          2
          3
          4

(3 rows affected)

Oracle Example

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0

SQL> create table p (id int primary key);

Table created.

SQL> insert into p values(1),(2),(3);

3 rows created.

SQL> commit;

Commit complete.

SQL> update p set id=id+1;

3 rows updated.

SQL> commit;

Commit complete.
SQL> select * from p;

  ID
----------
   2
   3
   4

Tuesday, February 6, 2024

How to keep PostgreSQL sequences' value in sync via script/automation

 Sample scripts to demonstrate how to generate DDLs to keep sequences in sync between 2 PostgreSQL databases.

One assumption is made that both database contains same sequence definitions, and the only differences are these last_value for these sequences.

Method 1 - Generate DDL on source

  1. In order to retrieve last_value for different sequence, create following helper function.
create or replace function get_sequence_last_value 
(schema_name name, sequence_name name)
returns table (sequence_value bigint)
language 'plpgsql'
AS
$$
begin
   return query execute format ('select last_value from %I.%I',schema_name,sequence_name);
end;
$$;

Test the function, make sure it works.

mytest=# select get_sequence_last_value('public','s1');
 get_sequence_last_value
-------------------------
                      35
(1 row)
  1. Generate DDL scripts.
select 'alter sequence '||seq.schema_name||'.'||seq.sequence_name||' restart with '||v.sequence_value||';'
from
(select s.nspname schema_name, c.relname sequence_name
       from pg_class c, pg_namespace s
       where c.relnamespace=s.oid 
         and c.relkind='S'
         and s.nspname in ('public')) seq 
join lateral get_sequence_last_value(seq.schema_name,seq.sequence_name) v
on true
order by seq.schema_name,seq.sequence_name;

Sample output:

mytest=# select 'alter sequence '||seq.schema_name||'.'||seq.sequence_name||' restart with '||v.sequence_value||';'
mytest-# from
mytest-# (select s.nspname schema_name, c.relname sequence_name
mytest(#        from pg_class c, pg_namespace s
mytest(#        where c.relnamespace=s.oid
mytest(#          and c.relkind='S'
mytest(#          and s.nspname in ('public')) seq
mytest-# join lateral get_sequence_last_value(seq.schema_name,seq.sequence_name) v
mytest-# on true
mytest-# order by seq.schema_name,seq.sequence_name;
                          ?column?
-------------------------------------------------------------
 alter sequence public.s1 restart with 35;
 alter sequence public.s2 restart with 28;
 alter sequence public.sporting_event_id_seq restart with 1;
(3 rows)

  1. Apply these DDLs to the target database

In below script, it create dblink, retrieve latest value from over the dblink and set the restart value for these sequences in the current database.

do
$$
declare
  seq_value bigint;
  rec_seq record;
  cur_seq cursor for 
       select s.nspname schema_name, c.relname sequence_name
       from pg_class c, pg_namespace s
       where c.relnamespace=s.oid 
         and c.relkind='S'
         and s.nspname in ('public');
begin
       begin
         execute 'SELECT dblink_connect($1,$2)' using 'myconn','dbname=mytest';
       exception when others then 
         raise notice 'Connection creation exception, safely ignore if subsequent code works';
       end;
  
       open cur_seq;
       loop
         fetch cur_seq into rec_seq;
         exit when not found;
         raise notice 'Processing: %', rec_seq.schema_name||'.'||rec_seq.sequence_name;
         -- process for each sequence found on the target
         begin
           select id into seq_value 
           from dblink('myconn', 'select last_value from '||rec_seq.schema_name||'.'||rec_seq.sequence_name) 
           as p(id int);
           
           begin
                      raise notice 'alter sequence %.% restart with %', rec_seq.schema_name,rec_seq.sequence_name,seq_value;
             execute format('alter sequence %I.%I restart with %s',rec_seq.schema_name,rec_seq.sequence_name,seq_value);
           exception when others then 
             raise notice 'Exception occurred to set sequence %', rec_seq.schema_name||'.'||rec_seq.sequence_name; 
           end;
           
         exception when others then 
           raise notice 'Exception occurred to fetch sequence %', rec_seq.schema_name||'.'||rec_seq.sequence_name;
         end;
     
       end loop;
       begin
         execute 'SELECT dblink_disconnect($1)' using 'myconn';
       exception when others then 
         raise notice 'Connection disconnect exception, safely ignore.';
       end;
       
end;
$$ language plpgsql;

Sample output:


NOTICE:  Processing: public.sporting_event_id_seq
NOTICE:  alter sequence public.sporting_event_id_seq restart with 1
NOTICE:  Processing: public.s3
NOTICE:  Exception occurred to fetch sequence public.s3
NOTICE:  Processing: public.s1
NOTICE:  alter sequence public.s1 restart with 35
NOTICE:  Processing: public.s2
NOTICE:  alter sequence public.s2 restart with 28
DO

Query returned successfully in 87 msec.

Sunday, February 4, 2024

Behavior in PostgreSQL after "kill -9" on backend server process

 In PostgreSQL, server (backend) process exits abnormally could cause PostgreSQL restart, and impacts all existing connections, not just the database client served by the server process.

Check the existing 2 sessions with corresponding server process.

-- Session 1
mytest=# select pg_backend_pid();
 pg_backend_pid
----------------
          89176
(1 row)
-- Session 2
mytest=# select pg_backend_pid();
 pg_backend_pid
----------------
          89182
(1 row)

Terminate the process 89176 by kill -9.

With kill -9, the process ended abruptly.

myhost$ kill -9 89176

PostgreSQL error log contents

2024-02-04 19:28:45.573 +08 [59773] DEBUG:  server process (PID 89176) was terminated by signal 9: Killed: 9
2024-02-04 19:28:45.573 +08 [59773] DETAIL:  Failed process was running: select * from pg_stat_activity where pid=pg_backend_pid();
2024-02-04 19:28:45.573 +08 [59773] LOG:  server process (PID 89176) was terminated by signal 9: Killed: 9
2024-02-04 19:28:45.573 +08 [59773] DETAIL:  Failed process was running: select * from pg_stat_activity where pid=pg_backend_pid();
2024-02-04 19:28:45.573 +08 [59773] LOG:  terminating any other active server processes
2024-02-04 19:28:45.573 +08 [59773] DEBUG:  sending SIGQUIT to process 59780
2024-02-04 19:28:45.573 +08 [59773] DEBUG:  sending SIGQUIT to process 89182
2024-02-04 19:28:45.573 +08 [59773] DEBUG:  sending SIGQUIT to process 84277
2024-02-04 19:28:45.574 +08 [59773] DEBUG:  sending SIGQUIT to process 59776
2024-02-04 19:28:45.574 +08 [59773] DEBUG:  sending SIGQUIT to process 59775
2024-02-04 19:28:45.575 +08 [59773] DEBUG:  sending SIGQUIT to process 59778
2024-02-04 19:28:45.575 +08 [59773] DEBUG:  sending SIGQUIT to process 59779
2024-02-04 19:28:45.575 +08 [59773] DEBUG:  server process (PID 89182) exited with exit code 2
2024-02-04 19:28:45.575 +08 [59773] DETAIL:  Failed process was running: select * from pg_stat_activity where pid=pg_backend_pid();
2024-02-04 19:28:45.578 +08 [59773] DEBUG:  server process (PID 84277) exited with exit code 2
2024-02-04 19:28:45.578 +08 [59773] DETAIL:  Failed process was running: elect * from pg_database;

2024-02-04 19:28:45.578 +08 [59773] LOG:  all server processes terminated; reinitializing
2024-02-04 19:28:45.595 +08 [59773] DEBUG:  cleaning up orphaned dynamic shared memory with ID 1898246468
2024-02-04 19:28:45.595 +08 [59773] DEBUG:  cleaning up dynamic shared memory control segment with ID 3888812834
2024-02-04 19:28:45.608 +08 [59773] DEBUG:  dynamic shared memory system will support 674 segments
2024-02-04 19:28:45.608 +08 [59773] DEBUG:  created dynamic shared memory control segment 2163855276 (26976 bytes)
2024-02-04 19:28:45.612 +08 [89227] LOG:  database system was interrupted; last known up at 2024-02-04 12:00:25 +08
2024-02-04 19:28:45.612 +08 [89227] DEBUG:  removing all temporary WAL segments
2024-02-04 19:28:45.612 +08 [89228] DEBUG:  checkpointer updated shared memory configuration values
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  checkpoint record is at 1/102B4200
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  redo record is at 1/102B41C8; shutdown false
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  next transaction ID: 1232; next OID: 25618
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  oldest unfrozen transaction ID: 722, in database 1
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  oldest MultiXactId: 1, in database 5
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2024-02-04 19:28:46.094 +08 [89227] LOG:  database system was not properly shut down; automatic recovery in progress
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  transaction ID wrap limit is 2147484369, limited by database with OID 1
2024-02-04 19:28:46.094 +08 [89227] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 5
2024-02-04 19:28:46.097 +08 [89227] DEBUG:  starting up replication slots
2024-02-04 19:28:46.097 +08 [89227] DEBUG:  restoring replication slot from "pg_replslot/my_logical_slot/state"
2024-02-04 19:28:46.098 +08 [89227] DEBUG:  xmin required by slots: data 0, catalog 1232
2024-02-04 19:28:46.098 +08 [89227] DEBUG:  starting up replication origin progress state
2024-02-04 19:28:46.098 +08 [89227] DEBUG:  didn''t need to unlink permanent stats file "pg_stat/pgstat.stat" - didn''t exist
2024-02-04 19:28:46.099 +08 [89227] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2024-02-04 19:28:46.102 +08 [59773] DEBUG:  postmaster received pmsignal signal
2024-02-04 19:28:46.103 +08 [89227] LOG:  redo starts at 1/102B41C8
2024-02-04 19:28:46.105 +08 [89227] LOG:  invalid record length at 1/102B6128: expected at least 24, got 0
2024-02-04 19:28:46.106 +08 [89227] LOG:  redo done at 1/102B60F0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-02-04 19:28:46.106 +08 [89227] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2024-02-04 19:28:46.112 +08 [89227] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 5
2024-02-04 19:28:46.112 +08 [89227] DEBUG:  MultiXact member stop limit is now 4294914944 based on MultiXact 1
2024-02-04 19:28:46.113 +08 [89228] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-02-04 19:28:46.113 +08 [89228] DEBUG:  performing replication slot checkpoint
2024-02-04 19:28:46.113 +08 [89228] DEBUG:  removing snapbuild snapshot pg_logical/snapshots/1-102B4190.snap
2024-02-04 19:28:46.113 +08 [89228] DEBUG:  removing snapbuild snapshot pg_logical/snapshots/1-102B41C8.snap
2024-02-04 19:28:46.117 +08 [89228] DEBUG:  checkpoint sync: number=1 file=base/16384/2619 time=0.218 ms
2024-02-04 19:28:46.117 +08 [89228] DEBUG:  checkpoint sync: number=2 file=pg_multixact/offsets/0000 time=0.070 ms
2024-02-04 19:28:46.117 +08 [89228] DEBUG:  checkpoint sync: number=3 file=base/16384/2619_fsm time=0.055 ms
2024-02-04 19:28:46.117 +08 [89228] DEBUG:  checkpoint sync: number=4 file=pg_xact/0000 time=0.054 ms
2024-02-04 19:28:46.118 +08 [89228] DEBUG:  attempting to remove WAL segments older than log file 00000000000000010000000F
2024-02-04 19:28:46.118 +08 [89228] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.006 s; sync files=4, longest=0.001 s, average=0.001 s; distance=7 kB, estimate=7 kB; lsn=1/102B6128, redo lsn=1/102B6128
2024-02-04 19:28:46.123 +08 [59773] DEBUG:  starting background worker process "logical replication launcher"
2024-02-04 19:28:46.124 +08 [89231] DEBUG:  autovacuum launcher started
2024-02-04 19:28:46.124 +08 [59773] LOG:  database system is ready to accept connections

Status/error message for these 2 PostgreSQL sessions

-- Session 1, process been killed by kill -9
mytest=# select * from pg_stat_activity where pid=pg_backend_pid();

server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
mytest=#
-- Session 2
mytest=# select * from pg_stat_activity where pid=pg_backend_pid();
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
mytest=?#
mytest=?# \q


myhost$ psql
psql (16.1)
Type "help" for help.
mytest=#