健哥的数据花园

摘要： [pgsql@localhost bin]$ ./psql -d postgrespsql (9.1.2)Type "help" for help.postgres=# \dNo relations found.postgres=# create table tab001(id integer);CREATE TABLEpostgres=# select pg_relation_filepath('tab001'); pg_relation_filepath ---------------------- base/12699/139312(1 row)pos 阅读全文

摘要： base目录，这是所有数据库目录的父目录。在base目录下第一层，每个目录就是一个数据库所对应的文件。那么如何知道哪个目录对应哪个数据呢？查询如下：先看数据库列表[pgsql@localhost bin]$ ./psqlpsql (9.1.2)Type "help" for help.pgsql=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+--------... 阅读全文

摘要： 读取普通的table或者系统表，都会调用heap_open函数：/* ---------------- * heap_open - open a heap relation by relation OID * * This is essentially relation_open plus check that the relation * is not an index nor a composite type. (The caller should also * check that it's not a view or forei... 阅读全文

摘要： 看了PostgreSQL的代码后，我觉得有不理解的地方，比如：例如这样的：/* Options that may appear after CATALOG (on the same line) */#define BKI_BOOTSTRAP#define BKI_SHARED_RELATION#define BKI_WITHOUT_OIDS#define BKI_ROWTYPE_OID(oid)#define BKI_SCHEMA_MACROCATALOG(pg_tablespace,1213) BKI_SHARED_RELATION{ NameData spcname; ... 阅读全文

摘要： 先说 heap_insert 函数：/* * heap_insert - insert tuple into a heap * * The new tuple is stamped with current transaction ID and the specified * command ID. * * If the HEAP_INSERT_SKIP_WAL option is specified, the new tuple is not * logged in WAL, even for a non-temp relation. Safe usage of thi... 阅读全文

摘要： 看例子：开两个终端来对比：在终端A:[pgsql@localhost bin]$ ./psqlpsql (9.1.2)Type "help" for help.pgsql=# begin;BEGINpgsql=# select xmin,xmax,cmin,cmax,* from tab01; xmin | xmax | cmin | cmax | id | cd ------+------+------+------+-----------+---- 1878 | 0 | 0 | 0 | 1 | 1 1884 | 0 | 0 | ... 阅读全文

摘要： xmaxThe identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.htt 阅读全文

摘要： 当PostgreSQL需要insert 一条记录的时候，它会把记录头放入xmin,xmax等字段。xmin的值，就是当前的Transaction的TransactionId。这是为了满足MVCC的需要。跟踪程序进行了解：/* * Allocate the next XID for a new transaction or subtransaction. * * The new XID is also stored into MyProc before returning. * * Note: when this is called, we are actually already inside 阅读全文

摘要： 对于PostgreSQL的 "create index concurrently". 我个人认为其中存在一个bug。我的验证过程如下：我有两个表，tab01和 tab02，这两个表之间没有任何关联。我认为 对 tab02执行 "create index concurrently" 不会对 访问tab01的事务有任何影响，然而事实并非尽然。我第一程序的表现: 通过ecpg执行事务，再通过 "create index concurrently" 给tab02建立索引，成功。我第二程序的表现：通过ecpg执行事务，再通过 "cre 阅读全文

摘要： 我给PostgreSQL的源代码加入了调试信息以后，会有如下表现：我执行Prepare：postgres=# prepare s(int) as select * from tst01 t where id < $1;PREPAREpostgres=# 背后的反应：** In PostgresMain In exec_simple_query loop for parsetree_list++++++++++++++++++++++++++Before pg_plan_queries***************In pg_plan_queries -------start....... 阅读全文