PostgreSQL中的GENERATED COLUMN

什么是 GENERATED COLUMN      GENERATE COLUMN 是一个在 CREATE TABLE 时指定的标识列(特征列)。该列将会附着一个隐藏的序列,并且在插入数据时以默认的隐藏序列为数据行进行插入。该列默认约束为 NOT NULL 约束。该列后有可选 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] 关键字。如果使用 ALWAYS 关键字,当在使用 INSERT 语句时接受 OVERRIDING SYSTEM VALUES 语句。如果指定 BY DEFAULT ,则用户指定的值优先。 该特性是在 PostgreSQL V10 版本中推出,属于约束的一种,实际上是自动为列分配一个唯一的值。类似整形序列加非空约束,但是又可以以用户指定的方式进行数据插入。 语法     列名 数据类型 GENERATED {ALWAYS | BY DEFAULT } AS IDENTIFY [ (sequence_option) ] 语法解释: 数据类型: 通常是整型的一种[ int2 | int4 | int8 | smallint | int | bigint ] GENERATED … [Read more...]

PostgreSQL 对象管理

概述 PostgreSQL 中的所有数据都存储在对应的文件中,即我们常见到的文件。这些用来存储数据的文件共同构成了 PostgreSQL 整个数据库集簇,而数据库集簇是对 PostgreSQL 中多个数据库组成的集合的称呼。而在逻辑上,PostgreSQL 所有的数据库都是隶属于某个表空间,并且单个数据库不能跨表空间,而一个表空间中可以存放多个数据库。表空间和数据库的关系属于多对多的关系。那么数据库中的数据是如何存放在数据文件中的呢?接下来一起探究。 理解 oid 和 relfilenode 的关系 在 PostgreSQL 中,oid 全称为 Object identifier,称为对象标识符,在 PostgreSQL 中,用于为每个对象分配的一个内部主键数据类型,其别名为 regclass,并且 oid 可以转换为整数。而 relfilenode 则为 PostgreSQL 数据库中对对象的物理访问信息。relfilenode … [Read more...]

PostgreSQL 密码验证功能增强

密码验证介绍 passwordcheck 模块是在 CREATE ROLE 或者 CREATE USER 期间检查用户密码是否符合指定的规则模块如果密码比较弱,那么在此期间将会拒绝执行密码并返回一个错误。 该模块位于 srcpkg/contrib 目录下,安装后位于 $libdir 目录下,使用 shared_preload_libraries加载并重新启动服务器后生效。在该模块中,主要有两个规则判断,一个是用户名自身的判断,一个是密码长度少于8位的判断,一个是对是否包含用户名本身的判断。 密码验证增强功能 密码验证增强功能主要是在原有密码检查模块的基础上,增加了对密码中是否包含至少一个大小写字母,一个数字和一个特殊字符的判断。 实现 <code class="hljs">/*------------------------------------------------------------------------- * * passwordcheck_enchance.c * * Author: Sungsasong * * IDENTIFICATION * … [Read more...]

Greenplum 数据同步方案—PostgreSQL技术文章晟数

更多精彩内容,请登录:ke.sandata.com.cn 一、目的 当前现有服务器状态为同机房两套 Greenplum 集群,节点数为 2 + 4。为满足未来灾备中心建设,需要构架高可用架构方案,以满足未来在生产系统集群出现不可恢复状态下,备用集群可以完全接管生产系统集群,以实现高可用架构。 二、方案 目前方案分为以下两种,一种为数据实时同步高可用方案,一种为允许数据同步有一定的延迟状态。 2.1 数据实时同步高可用架构(本文省略) 架构图如下(不做说明) 当主机中心由于不可抗拒因素或者硬件及认为因素不可用,备机完全接管主机。 2.2 … [Read more...]

PostgreSQL HLL插件介绍

前言 HLL是 HyperLogLog数据结构的简称。PostgresSQL通过插件的方式引入了这种新的数据类型hll。HyperLogLog是一个具有固定大小,类似于集合结构,用于可调精度的不同值计数。例如,在1280字节的hll数据结构中,它可以在很小的误差范围内估算出数百亿的不同值计数。 算法 hll可以被视为层次结构的不同集合/不同值计数算法的组合,并向上移动该层次结构的规则。为了区分上述描述算法,将其命名为以下: ♠ EMPTY 表示空集的常量值 ♠ EXPLICIT 集合中确定的,唯一的,排序完整的整数列表,该列表保持一个固定的基数 ♠ SPARSE HyperLogLog是基于映射的“惰性”实现,是一种基于概率集合的数据结构。仅将非零寄存器的索引和值存储在 map中,直到非零寄存器的数量超过固定的基数。 ♠ … [Read more...]

PostgreSQL备份工具 pgBackRest使用

前言 pgBackRest是一款开源的备份还原工具,目标旨在为备份和还原提供可靠易用的备份。 特性 并行备份和还原 备份操作期间压缩通常是其瓶颈所在。pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题。 本地远程操作 自定义协议允许 … [Read more...]

PostgreSQL 查找需要收集的vacuum 表信息

前言 通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间。在 PostgreSQL中,使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作系统,因此需要使用 VACUUM FULL,才可以释放空间,并立即将空间返还给操作系统。 实现脚本 记录收集表创建 [cce] CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext text); [/cce] 收集需要VACUUM 表函数 [cce] CREATE OR REPLACE FUNCTION f_vacuum_tables() RETURNS void AS $FUNCTION$ DECLARE v_tablename text; v_dead_cond bigint; v_sql text; cur_tablename REFCURSOR; v_vacuum_record text; BEGIN v_vacuum_record := 'tab_vacuum_record'; OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql'; LOOP FETCH cur_tablename INTO v_tablename; SELECT n_dead_tup INTO v_dead_cond FROM … [Read more...]

PostgreSQL libpq学习指南二

连接状态函数 PQdb 返回连接的数据库名称 PQuser 返回连接的用户名称 PQpass 返回连接的用户密码 如果连接密码没有被提供,那么将会尝试从 password_file 文件中获取。 PQhost 返回连接的服务器主机名称 PQhostaddr 返回连接的服务器IP地址 PQport 返回连接服务器的端口 PQtty 返回连接的DEBUG 终端信息 PQoptions 返回命令行传递的参数信息 PQstatus 返回连接的状态信息 示例: [cce] #include <stdio.h> #include <stdlib.h> #include "libpq-fe.h" int main(int argc, char **argv) { const char *conninfo; //定义连接信息 PGconn *conn; //连接数据库传递参数 if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* 连接数据库字符串 */ conn = PQconnectdb(conninfo); /*检查数据库是否连接成功 */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "连接数据库失败: … [Read more...]

PostgreSQL libpq 客户端接口(一)

前言 Libpq 是为 C 语言开发这提供的关于 PostgreSQL 的接口。Libpq 由相关库函数组成,可以允许客户端程序通过将查询传递给 PostgreSQL 后台服务器并接收这些查询返回的结果。同时,libpq 也是其它几个 PostgreSQL 应用程序接口的基础引擎,包括 C++,Perl,Python,Tcl和ECPGC编程的嵌入式 SQL。 连接字符串 以下函数处理 PostgreSQL 后台服务器的连接。一个应用程序同时可以有多个打开的连接,这也是 PostgreSQL 服务器能够同时访问多个库的原因。每一个连接都通过 PGconn 对象表示,该对象可以从函数 PQconnectdb,PQconnectdbParams 中或者 PQsetdbLogin 获取信息。需要注意的是,这些对象通常返回一个非空的对象指针。除非由于内存太小导致无法分配 PGconn 对象。PQstatus … [Read more...]

PostgreSQL中的ACID特性介绍

任何关系型数据库中,ACID 是组成数据库的重要部分,是数据库事务的一组属性,该特性目的主要确保数据库在异常情况下保证数据的有效性。 数据库ACID特性 A(Atomicity) 原子性: 事务通常由多个语句组成。原子性保证将每个事务视为单个单元,该事务要么完全成功,要么完全失败。换句话说,如果在一个事务中,任何语句都未能完成,整个事务都会失败,未完成事务中的数据条目在数据库中保持不变。一个原子系统必须保证在任何情况下都具有原子性,包括电源故障,数据库错误和实例奔溃。同时,原子性可以防止在数据库中发生部分数据更新的情况。举一个例子,假设用户USER1 和 USER2 之间进行转账。这之间会进行两个动作,第一个动作,从USER1 账户中划钱;第二个动作,将从USER1账户中划拨的钱保存到 USER2 … [Read more...]