PostGres常用sql锦集（持续更新中，内容会越来越多）

POSTGRES常用sql示例(持续更新中，搞个示例，忘了语法可以查一下)

psql常用命令

1、使用postgres超级用户登录： ./psql -U postgres
2、创建imsdb这个数据库： create database imsdb [owner rcb]
3、赋予rcb用户所有权限： grant all privileges on database imsdb to rcb
4、如果创建时未设置owner，可以更改： alter database imsdb owner to rcb
5、查看数据库： \l命令查看所有数据库
6、连接数据库： \c imsdb 连接imsdb这个数据库
7、显示所有的schema： \dn
8、显示所有的用户： \du
9、显示表的权限分配情况： \dp
10、显示当前的模式： show search_path
11、更改模式： set search_path to myschema
12、已列的形式展示： \x （当不想列展示时，再次\x即可）
13、查看数据存储目录： show data_directory;
14、 查看函数定义：select prosrc from pg_proc where proname =‘函数名称’;
15、 查看函数定义更简便的方法： \sf 函数名 ## sf 是 show function’s definition的简称
16、 查看表结构： \d 表名 或 \d+ 表名
17、 修改数据库密码：alter user postgres with password ‘123456’; 修改密码为123456

pg数据dump示例

#! /bin/bash
export PGPASSWORD='123456';pg_dump -U cbdca -h 10.33.65.9 -p 5432 -c -C -d cbdca -f /app/dbbak/cbdca/cbdca-20220718/cbdca-all.sql
export PGPASSWORD='123456';pg_dump -U cbdca -h 10.33.65.9 -p 5432 -d cbdca -t acs_config -f /app/dbbak/cbdca/cbdca-20220718/acs_config.sql
export PGPASSWORD='123456';pg_dump -U cbdca -h 10.33.65.9 -p 5432 -d cbdca -t acs_pic_server_bind_info -f /app/dbbak/cbdca/cbdca-20220718/acs_pic_server_bind_info.sql

开启日志打印

PostgreSQL 日志配置在 $PGDATA/postgresql.conf ；修改并保存配置后需要重载配置 select pg_reload_conf(); 或重启服务后生效

一般错误跟踪
logging_collector = on     # 开启日志采集
log_destination = 'stderr' # 日志输出格式，配置为 csvlog - 输出到csv文件：
log_directory = 'pg_log'   # 可配置为：$PGDATA的相对路径或绝对路径
log_filename = 'postgresql-%Y-%m-%d.log' # 输出日志文件名格式
log_filename = 'postgresql-%a.log' # 这是默认格式，%a代表星期
log_rotation_age = 1440             # minute,多长时间创建新的文件记录日志。0 表示禁扩展。
log_rotation_size = 10240           # kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
log_truncate_on_rotation = on       # 可重用同名日志文件
log_min_messages = info   



# 监控慢sql日志
log_statement = all  # 需设置跟踪所有语句，否则只能跟踪出错信息，设置跟踪的语句类型，有4种类型：none(默认), ddl, mod, all。跟踪所有语句时可设置为 "all"。 
log_min_duration_statement = 5000   # milliseconds,记录执行5秒及以上的语句，跟踪慢查询语句，单位为毫秒。如设置 5000，表示日志将记录执行5秒以上的SQL语句。-1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = verbose   
log_line_prefix = '%m'
# 监控数据库中长时间的锁
log_lock_waits = on
# 记录DDL操作
log_statement = 'ddl'   #可以是all
deadlock_timeout = 5s  ##模拟死锁期间，可以将值调大，方便观察，生产环境不要设置太大的值



# 例如查询执行时间超过1秒的SQL  
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ; 


##查看日志文件和目录
show log_directory;
show log_filename;

删除数据库

1 删除数据库：
DROP DATABASE [ IF EXISTS ] name

2 创建数据库和用户
drop database if exists artemis;
create user artemis with password '123qaz!@#';
create database artemis with template = template0 encoding = 'UTF8' lc_collate = 'en_US.UTF8' lc_ctype = 'en_US.UTF8';
alter database artemis owner to artemis;
grant all on database artemis to artemis;

导出查询数据方式

1 使用psql -U xxx -d xxx命名进入交互界面，输入下属命令并回车，可以将输出重定向到person.txt文件
\o /home/hik/lyc/person.txt
2 输入查询语句，这样，满足查询条件的数据会被定向输出到文件：/home/hik/lyc/person.txt:
  select * from person where is_delete = 0;

查看所有表，序列

查看所有表：
select  * from   pg_tables  where schemaname = 'public';

查看所有序列：
select * from pg_class where relkind='S'

--查看数据库:
select * from pg_database;

--查看表空间
select * from pg_tablespace;

--查看语言
select * from pg_language;

--查看角色用户
select * from pg_user;

select * from pg_shadow;

select * from pg_roles;

--查看会话进程
select * from pg_stat_activity;

--查看表
SELECT * FROM pg_tables where schemaname = 'public';

--查看表字段
select * from information_schema.columns where table_schema = 'public' and table_name = 'noip_user';

--查看视图
select * from pg_views where schemaname = 'public';

select * from information_schema.views where table_schema = 'public';

--查看触发器
select * from information_schema.triggers;

--查看序列
select * from information_schema.sequences where sequence_schema = 'public';

--查看约束
select * from pg_constraint where contype = 'p';

注意： --u unique,p primary,f foreign,c check,t trigger,x exclusion

select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';

--查看索引
select * from pg_index ;

--查看表大小(不含索引等信息)
select pg_relation_size('noip_user'); 
select pg_size_pretty(pg_relation_size('noip_user'));

--查看DB大小
select pg_size_pretty(pg_database_size('noip')); 

查看表信息

--- 查看表中各个字段类型

SELECT
    format_type (A .atttypid, A .atttypmod) AS TYPE,
    A .attname AS NAME
FROM
    pg_class AS C,
    pg_attribute AS A
WHERE
    C .relname = '你的表名'
AND A .attrelid = C .oid
AND A .attnum > 0;

删除数据库中所有的表,进入欲删除表的数据库，执行下列存储过程

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not"current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

修改表，序列的owner:

ALTER TABLE public.temperature_warn_event OWNER TO postgres;
GRANT ALL ON TABLE public.temperature_warn_event TO postgres;
alter  sequence  public.seq_device_type_id owner to postgres;

更换字段引用的序列示例：

alter table tpe_event alter column id drop default; 
create sequence seq_tpe_event_id  minvalue 1 maxvalue 9223372036854775807 start with 1 cycle; 
select nextval('seq_tpe_event_id');
select setval('seq_tpe_event_id', '291305');
alter table tpe_event alter column id set default nextval('seq_tpe_event_id');

创建表，使用自增主键示例

DROP SEQUENCE  IF EXISTS  seq_device_type_id;
create sequence  public.seq_device_type_id  minvalue 1 maxvalue 9223372036854775807 start with 1 cycle;
alter  sequence  public.seq_device_type_id owner to postgres;
DROP TABLE IF EXISTS device_type;
CREATE TABLE "public"."device_type" (
  id int4 NOT NULL default nextval('seq_device_type_id'::regclass),
  device_type int4 not null,
  device_type_name varchar(64),
  device_sub_type int4 not null,
  device_sub_type_name varchar(64),
  device_model varchar not null,
  device_model_type int4 not null,
  create_time timestamp not null default now(),
  update_time timestamp(0) not null default now(), --精度保存到秒，默认是毫秒 
  CONSTRAINT device_type_pkey PRIMARY KEY (id)
) WITH (
    OIDS = FALSE
);

alter table public.device_type owner to postgres;

COMMENT ON TABLE device_type IS '设备类型表';
------------------------------
COMMENT ON COLUMN device_type.id IS '主键';
------------------------------
COMMENT ON COLUMN device_type.device_type IS '设备大类, 0: 代表门禁设备';
COMMENT ON COLUMN device_type.device_type_name IS '设备大类名称, 比如: 门禁设备';
------------------------------
COMMENT ON COLUMN device_type.device_sub_type IS '设备子类, 比如门口机, 门口机也属于门禁设备';
COMMENT ON COLUMN device_type.device_sub_type_name IS '设备子类名称, 比如门口机';
------------------------------
COMMENT ON COLUMN device_type.device_model IS '设备出场型号, 比如: DS-KH9300-A';
COMMENT ON COLUMN device_type.device_model_type IS '设备型号类型，0: 完整型号， 1: 设备型号前缀';

insert into device_type(id, device_type, device_type_name, device_sub_type, device_sub_type_name, device_model, device_model_type, create_time) values (nextval('seq_device_type_id'), 0, '门禁设备',  10, '门口机', 'DS-K1T671MI', 0,  now());

字段相关操作

---修改字段类型，从int4改为int8
ALTER TABLE event ALTER COLUMN event_id TYPE int8 USING event_id::int4;


------修改字段长度
alter table nb_device alter column  cmd_json  type varchar(1024);

---新增字段
alter table event add column house_id varchar(36);

---修改字段名称
ALTER TABLE event rename event_id to id;

----移除非空限制
alter table person_house alter COLUMN person_house_type drop not null;

------- 删除字段
ALTER TABLE users DROP COLUMN id_card CASCADE; 

alter table tb_gas_cmd_record add column if not exists batch_id varchar(32) default '';

创建索引示例

--- 引入扩展
CREATE EXTENSION pg_trgm;

----采用gin_trgm_ops建立索引完成，但对于联合索引，gin_trgm_ops将合并成一个字符串查询， 例如
CREATE INDEX INDEX_BILL_RECORDING_GIN ON bill_recording using gin ((bill_name || ' ' || payment_account || ' ' || account_id) gin_trgm_ops);

---采用pg_trgm 建立gin索引
CREATE INDEX tpe_event_gin_index_parking_name     ON tpe_event USING gin ((parking_name) gin_trgm_ops);

---创建btree索引
CREATE INDEX nb_device_btree_index_ys_device_id             ON nb_device USING btree (ys_device_id);

-- gin联合索引占用空间比btree大，索引数量与列数有关，执行过程中会锁表，为不影响插入，修改等操作，可使用CONCURRENTLY不锁表建立索引
CREATE INDEX CONCURRENTLY index_name ON table_name USING gin ( UPPER ( first_name ), UPPER ( last_name ), UPPER ( email ), UPPER(username));

---有时候需要建立联合索引，但同时不同的列不愿意合成一个字段，这个时候可以gin建立联合索引， 先修改默认pg_opclass
UPDATE pg_opclass SET opcdefault = TRUE WHERE opcname = 'gin_trgm_ops';

----补充主键
alter table person add constraint person_pkey primary key(id);
ALTER TABLE "person " ADD CONSTRAINT "person_pkey" IF NOT EXISTS  PRIMARY KEY ("id");

----- 添加唯一性索引
alter table tbl_unique_index add constraint uk_tbl_unique_index_b unique(b);

  CREATE INDEX if not exists idx_pu_tian_shp_mzguid ON pu_tian_shp (mzguid);

----- 删除索引语句：
DROP INDEX unique_all_column

Geometry类型操作

--- 创建视图pt_yw，并授权给test_user
 CREATE SCHEMA IF NOT EXISTS pt_yw AUTHORIZATION  test_user;
--- 插入多边形数据
 CREATE TABLE IF NOT EXISTS  "pt_yw"."my_geo" (
          "id" int8 ,
          "geom",
          constraint "my_geo_pkey" primary key ("id")
        ) ;
insert into "pt_yw".my_geo(id, geom) values(1, 'MULTIPOINT((0 0), (1 1 ), (2 2 ))');
insert into "pt_yw".my_geo(id, geom) values(2,   'MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))');
select  ST_AsText(geom)  from pt_yw.my_geo;
        

查看某个表的索引使用情况

select 
    relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch 
from 
    pg_stat_user_indexes 
where
    relname = table_name 
order by 
    idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;

插入数据

将表dev_user_copy1 中的数据插入到dev_user中
insert into dev_user(id, name, pwd, company, job ,email) select id, name, pwd, company, job ,email from dev_user_copy1 where id not in (select id from dev_user)

--插入数据，id冲突会报错
INSERT INTO t_system_config (id, name, value) VALUES ('mapType', '地图类型', '10');
--插入数据，id冲突不会报错
insert into t_system_config(id,name,value) ( select 'mapType', '地图类型', '10' from t_system_config
   where not exists(select * from t_system_config where id='mapType'));
或者：
insert into t_system_config(id,name,value) ( select 'mapType', '地图类型', '10' from t_system_config
   where not exists(select * from t_system_config where id='mapType') limit 1);


情空表中数据
truncate  table;比如表名是account, 则 truncate  account;

修改名称

--- 修改表名
alter table t_announcement_info rename to announcement_info;
alter table t_advise rename to advise_info;

常用语句举例

## 给district表的create_time增加一个随机值，消除重复,语句中20000是district表的记录总数
select count(*) from district;
update district  dis set create_time = create_time + (1/20000 ::float) * a.num *(interval '5' second) from (select  id, row_number() over() as num from district) a   where dis.id = a.id;


----- update from语句
update nb_event set handle_result = h.handle_result, handle_suggestion = h.handle_suggestion,   handle_person = h.handle_person, handle_time = h.handle_time, extend = h.extend from nb_event_handle h where nb_event .id = h.event_id;

数据库磁盘占用统计

统计数据库中各表占用大小

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc;

统计各数据库占用磁盘大小：

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20;

数据库状态

查看数据库连接进程 每个服务器进程一行，显示数据库OID、数据库名、进程ID、客户端地址、客户端端口、最近查询时间、进程开始时间

select datid, datname, pid, client_addr, client_port, query_start, backend_start from pg_stat_activity;

select * from pg_stat_activity;

查看数据库使用情况 每个数据库一行，显示数据库OID、数据库名、数据库连接进程数、提交的事务总数、回滚总数、读取磁盘块的总数，缓冲区命中的总数

select * from pg_stat_database;

列出数据库中的自定义函数

//列出函数名称，返回类型，入参数量
SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
    JOIN pg_type
   ON (pg_proc.prorettype = pg_type.oid)
WHERE
  pg_type.typname != 'void'
  AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');
 
 //列出所有字段 
SELECT *
FROM
  pg_proc
    JOIN pg_type
   ON (pg_proc.prorettype = pg_type.oid)
WHERE
  pg_type.typname != 'void'
  AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');

RANGE 分区函数使用示例

DROP TABLE IF EXISTS  measurement;
CREATE TABLE measurement (
    id int8 null,
    logdate   timestamp not null
    
) PARTITION BY RANGE (logdate);

--创建普通分区

create table m_20230519 partition of measurement for values from ('2023-05-19 00:00:00') to ('2023-05-19 23:59:59');
alter table m_20230519  add constraint  m_20230519_pkey primary key (id);


create table m_20230520 partition of measurement for values from ('2023-05-20 00:00:00') to ('2023-05-20 23:59:59');
alter table m_20230520  add constraint  m_20230520_pkey primary key (id);

-- 创建默认分区
create table m_default  partition of  measurement default;

-- 删除默认分区
ALTER TABLE measurement DETACH PARTITION m_default;


-- 绑定默认分区
alter table measurement attach partition m_default default;


-- 插入数据源
insert into measurement (id, logdate) values (1, now());

insert into measurement (id, logdate) values (2, '2023-05-25');


insert into measurement (id, logdate) values (2, '2023-05-20');




在PostgreSQL中，可以通过查询系统表pg_partitions来判断表是否分区。以下是一个示例查询：
SELECT * FROM pg_partitions WHERE parent_table = 'your_table_name';
这将返回指定表的所有分区信息。如果返回结果为空，则说明该表没有分区。您也可以通过查看表的定义来确定是否为分区表，例如：

SELECT relkind FROM pg_class WHERE relname = 'your_table_name';
如果返回的relkind为 ‘p’，则表示该表是一个分区表。




select * from pg_inherits where cast(cast(inhparent as regclass) as text) = 'measurement' and  cast(cast(inhrelid as regclass) as text) = 'm_default';

select * from pg_inherits where cast(cast(inhparent as regclass) as text) = 'measurement';


select c.relname from pg_class c join pg_inherits i on i.inhrelid = c.oid join pg_class d on d.oid = i.inhparent where d.relname = 'measurement';

关于锁

PostgreSQL中主要有两种锁，一个表锁一个行锁
PostgreSQL中也提供了页锁，咨询锁，But，这个不需要关注，他是为了锁的完整性

表锁显而易见，就是锁住整张表。表锁也分为很多中模式。表锁的模式很多，其中最核心的两个：ACCESS SHARE：共享锁（读锁），读读操作不阻塞，但是不允许出现写操作并行。ACCESS EXCLUSIVE：互斥锁（写锁），无论什么操作进来，都阻塞。

表锁使用示例：
begin;
-- 基于默认的ACCESS EXCLUSIVE锁住test表
lock test in ACCESS SHARE mode;
-- 操作
select * from test;
-- 提交事务，表锁释放
commit;

注意，表锁语法为 lock table [only] [表名] in [xxx]  mode;  [xxx]的可选值为： ROW SHARE ， ROW EXCLUSIVE 
SHARE ， exclusive ，ACCESS SHARE  ， ACCESS EXCLUSIVE ，SHARE ROW EXCLUSIVE ， SHARE update exclusive
比如: lock table only "event" in SHARE update EXCLUSIVE  mode;  ## event是表名


PostgreSQL的行锁和MySQL的基本是一模一样的，基于select for update就可以指定行锁。MySQL中有一个概念，for update时，如果select的查询没有命中索引，可能会锁表。PostgerSQL有个特点，一般情况，在select的查询没有命中索引时，他不一定会锁表，依然会实现行锁。PostgreSQL的行锁，就玩俩，一个for update，一个for share。在开启事务之后，直接执行select * from table where 条件 for update;

行锁使用示例：
-- 先开启事务
begin;
-- 基于for update 锁住id为3的数据
select * from test where id = 3 for update;
update test set name = 'v1' where id = 3;
-- 提交事务，锁释放
commit;

jdbc连接工具类

package com.hikvision.data.modules.backup;

import com.hikvision.data.core.support.logger.LogFactory;
import org.slf4j.Logger;

import java.sql.*;

public class PgTool {

    private static Logger logger = LogFactory.getLogger(PgTool.class);

    public Connection getConnection(String ip, Integer port, String dbName, String username, String password) {
        String url = "jdbc:postgresql://" + ip + ":" + port + "/" + dbName +
                "?useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC";

        Connection connection = null;
        try {
            Class.forName("org.postgresql.Driver").newInstance();
            connection = DriverManager.getConnection(url, username, password);
        } catch (InstantiationException e) {
           logger.error("", e);
        } catch (IllegalAccessException e) {
            logger.error("", e);
        } catch (ClassNotFoundException e) {
            logger.error("", e);
        } catch (SQLException e) {
            logger.error("", e);
        }
        return connection;
    }

    public ResultSet query(Connection conn, String sql) {
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        try {
            pStatement = conn.prepareStatement(sql);
            rs = pStatement.executeQuery();
        } catch (SQLException e) {
            logger.error("", e);
        }
        return rs;
    }

    public boolean queryUpdate(Connection conn, String sql) {
        PreparedStatement pStatement = null;
        int rs = 0;
        try {
            pStatement = conn.prepareStatement(sql);
            rs = pStatement.executeUpdate();
        } catch (SQLException e) {
            logger.error("", e);
        }
        if (rs > 0) {
            return true;
        }
        return false;
    }


}

java代码模拟行锁、表锁发生死锁（运行单元测试即可复现）

package com.hikvision.data.test;

import com.hikvision.data.modules.backup.PgTool;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.junit.Before;

import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.TimeUnit;

public class DeadLockTest {

    //需要事先执行如下SQL
    @Before
    public void init() {
/**
         drop table if exists tb_user;
         create table tb_user
         (
         id    integer not null,
         name varchar(64),
         age int4,
         constraint pk_id primary key (id)
         );
         insert into tb_user(id, name, age) values (1, '张三', 100);
         insert into tb_user(id, name, age) values (2, '李四', 200);
         select * from tb_user;

         update tb_user set name = '张三' where id = 1;
         update tb_user set name = '李四' where id = 2;


         drop table if exists tb_fruit;
         create table tb_fruit
         (
         id    integer not null,
         name varchar(64),
         age int4,
         constraint pk_fruit_id primary key (id)
         );
         insert into tb_fruit(id, name, age) values (1, '天麻', 100);
         insert into tb_fruit(id, name, age) values (2, '火龙果', 200);
         select * from tb_fruit;
 */
    }


    /**
     * 演示行锁发生死锁
     */
    @org.junit.Test
    public void testRowDeadLock() {

        int total = 2;
        CountDownLatch latch = new CountDownLatch(1);
        List<Thread> threads = new ArrayList<>();
        for (int i = 0; i < total; i++) {
            RowDeadLockTask runner = new RowDeadLockTask(latch, i);
            Thread t = new Thread(runner, "dead-" + i);
            threads.add(t);
        }
        for (Thread thread : threads) {
            thread.start();
        }

        System.out.println("停顿10秒");
        try {
            TimeUnit.SECONDS.sleep(10L);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        latch.countDown();
        System.out.println("停顿10秒结束，开始运行任务");
        try {
            CountDownLatch wait = new CountDownLatch(1);
            wait.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    /**
     * 演示表锁发生死锁
     */
    @org.junit.Test
    public void testTableDeadLock() {

        int total = 2;
        CountDownLatch latch = new CountDownLatch(1);
        List<Thread> threads = new ArrayList<>();
        for (int i = 0; i < total; i++) {
            TableDeadLockTask runner = new TableDeadLockTask(latch, i);
            Thread t = new Thread(runner, "dead-" + i);
            threads.add(t);
        }
        for (Thread thread : threads) {
            thread.start();
        }

        System.out.println("停顿10秒");
        try {
            TimeUnit.SECONDS.sleep(10L);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        latch.countDown();
        System.out.println("停顿10秒结束，开始运行任务");
        try {
            CountDownLatch wait = new CountDownLatch(1);
            wait.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }



    private static StringReader reader1() {
        String a =
                "begin;\n" +
                "select * from tb_user where id = 1 for update;\n" +
                "update tb_user set name = '张三11111' where id = 1;\n" +
                "\n" +
                "select * from tb_user where id = 2 for update;\n" +
                "update tb_user set name = '李四2222222' where id = 2;\n" +
                "commit;";
        StringReader reader = new StringReader(a);
        return reader;
    }

    private static StringReader reader2() {
        String a =
                "begin;\n" +
                "select * from tb_user where id = 2 for update;\n" +
                "update tb_user set name = '李四2222222' where id = 2;\n" +
                "\n" +
                "select * from tb_user where id = 1 for update;\n" +
                "update tb_user set name = '张三11111' where id = 1;\n" +
                "commit;";
        StringReader reader = new StringReader(a);
        return reader;
    }


    private static StringReader reader3() {
        String a =
                "begin;\n" +
                "lock table tb_user in ACCESS exclusive mode;\n" +
                "update tb_user set name = '张三11111' where id = 1;\n" +
                "lock table tb_fruit in ACCESS exclusive mode;\n" +
                "update tb_fruit set name = '红心天麻' where id = 1;\n" +
                "commit;";
        StringReader reader = new StringReader(a);
        return reader;
    }


    private static StringReader reader4() {
        String a =
                "begin;\n" +
                "lock table tb_fruit in ACCESS exclusive mode;\n" +
                "update tb_fruit set name = '红心天麻' where id = 1;\n" +
                "lock table tb_user in ACCESS exclusive mode;\n" +
                "update tb_user set name = '张三11111' where id = 1;\n" +
                "commit;";
        StringReader reader = new StringReader(a);
        return reader;
    }


    public static final class RowDeadLockTask implements  Runnable {

        private CountDownLatch countDownLatch;
        private int index;

        public RowDeadLockTask(CountDownLatch countDownLatch, int index) {
            this.countDownLatch = countDownLatch;
            this.index = index;
        }

        @Override
        public void run() {
            Connection connection = null;
            try {
                connection = new PgTool().getConnection("10.42.43.9", 5432, "postgres", "postgres", "123qaz!@#");
                ScriptRunner scriptRunner = new ScriptRunner(connection); //该类是由，mybatis框架提供的
                scriptRunner.setAutoCommit(true);
                scriptRunner.setSendFullScript(true);
                Reader reader = null;
                int i = index % 2;
                if (i == 1) {
                    reader = reader1();
                } else {
                   reader =  reader2();
                }
                System.out.println("运行sql开始:  " + index);
                countDownLatch.await();
                scriptRunner.runScript(reader);
                System.out.println("运行sql结束:  " + index);
            } catch (Throwable e) {
                e.printStackTrace();
            }finally {
                try {
                    connection.close();
                } catch (Throwable throwables) {
                    throwables.printStackTrace();
                } finally {
                }

            }

        }
    }


    public static final class TableDeadLockTask implements Runnable {

        private CountDownLatch countDownLatch;
        private int index;

        public TableDeadLockTask(CountDownLatch countDownLatch, int index) {
            this.countDownLatch = countDownLatch;
            this.index = index;
        }

        @Override
        public void run() {
            Connection connection = null;
            try {
                connection = new PgTool().getConnection("10.42.43.9", 5432, "postgres", "postgres", "123qaz!@#");
                ScriptRunner scriptRunner = new ScriptRunner(connection); //该类是由，mybatis框架提供的
                scriptRunner.setAutoCommit(true);
                scriptRunner.setSendFullScript(true);
                Reader reader = null;
                int i = index % 2;
                if (i == 1) {
                    reader = reader3();
                } else {
                    reader = reader4();
                }
                System.out.println("运行sql开始:  " + index);
                countDownLatch.await();
                scriptRunner.runScript(reader);
                System.out.println("运行sql结束:  " + index);
            } catch (Throwable e) {
                e.printStackTrace();
            } finally {
                try {
                    connection.close();
                } catch (Throwable throwables) {
                    throwables.printStackTrace();
                } finally {
                }

            }

        }
    }





}