本文汇总了PostgreSQL数据库的常用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
开启日志打印
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;
创建索引示例
--- 引入扩展
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 tbl_unique_index add constraint uk_tbl_unique_index_b unique(b);
----- 删除索引语句：
DROP INDEX unique_all_column
查看某个表的索引使用情况
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 {
}
}
}
}
}
扫一扫
1224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
