PostgreSQL quote ident and literal
背景 在PostgreSQL中，支持特殊字符的表名，列名等，但是他们在使用时需要使用双引号，例如包含大小写的表名。 在字符串输入时，可以使用的方式引用字符串，也可以使用单引号，但是如果在字符串中出现了应用字符，需要对引用字符进行转义。 这是数据库操作的常见问题。 PostgreSQL 提供了几个函数用来输出...
Recent Posts
PostgreSQL pg_upgrade 9.3 improve and example
背景 E.1.3.10.1. pg_upgrade Allow pg_upgrade --jobs to do parallelism (Bruce Momjian) This allows parallel schema dump/restore of databases, as well as pa...
inode and hard link
背景 In computing, an inode (index node) is a data structure found in many Unix file systems. Each inode stores all the information about a file system object...
PostgreSQL 9.3 improve pgstattuple
背景 pgstattuple可以用来查询tuple级别的统计信息. PostgreSQL 9.3 加入了gin索引的pending信息输出. 统计时对被统计的数据加read lock. 所以如果数据正在变更或插入, 只要HeapTupleSatisfiesNow返回false, 就归入dead tuple....
PostgreSQL 9.3 pg_trgm imporve support multi-bytes char and gist,gin index for reg-exp search
背景 PostgreSQL 9.3的pg_trgm模块主要有2方面的增强, 支持多字节字符, 同时支持gist, gin索引的规则表达式查询. Note: pg_trgm ignores non-word characters (non-alphanumerics) when extracting trigra...
PostgreSQL 9.3 Allow PL/pgSQL to access the number of rows processed by COPY
背景 Allow PL/pgSQL to access the number of rows processed by COPY (Pavel Stehule) The command is GET DIAGNOSTICS x = ROW_COUNT. 加入plpgsql函数对获取copy影响行数的支...
PostgreSQL 9.3 Add API for 64-bit large object access
背景 Increase the maximum length of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata) This change includes new libpq and server-side 64-bit-capable l...
PostgreSQL SQL_ASCII encoding introduce
背景 一位网友想将PostgreSQL数据库从sql_ascii编码迁移到utf8编码. 这里涉及到编码的转换, 首先要了解一下sql_ascii的编码含义 : The SQL_ASCII setting behaves considerably differently from the other sett...
PostgreSQL 审计 - PostgreSQL 9.3 Allow OLD and NEW in multi-row VALUES within rules
背景 Allow OLD and NEW in multi-row VALUES within rules. Now that we have LATERAL, it's fairly painless to allow this case, which was left as a TODO in th...
PostgreSQL 9.3 add lock_timeout parameter
背景 Add configuration variable lock_timeout to limit lock wait duration 测试 1. 隐锁 SESSION A : digoal=# begin; BEGIN digoal=# insert into test values (1,...