postgresql 父表数据的问题

宏哥 发布于 2012/07/10 15:46
阅读 1K+
收藏 1

@mark35 

1: 有没有办法从父表当中知道查询出的数据所在的子表?  目前只知道父表查询是对字表查询的并操作.

2: 是否有必要通过字典来直接在父表数据直接能够锁定字表?

加载中
0
mark35
mark35

 

有时候你可能想知道某个行版本来自哪个表。在每个表里我们都有一个 tableoid 系统属性可以告诉你源表是谁:

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

结果如下(你可能会得到不同的 OID):

 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

通过和 pg_class 做一个连接,就可以看到实际的表名字

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 and c.tableoid = p.oid;

它返回:

 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 capitals | Madison   |      845

 

宏哥
宏哥
现在回想起来,很多高级的软件当中,大量运用了这种东西. 对软件开发太重要了.
宏哥
宏哥
King OF PG!!!
1
宏哥
宏哥

引用来自“mark35”的答案

引用来自“宏哥”的答案

回复 @mark35 :  我现在对父表的理解是:

它是一个逻辑视图,并拥有一个物化表(自身),非物理表(除自身),影响query planer的执行,但是由于本身(除了自身)没有物化,所以它无法实现对子表的智能约束,也没有物理存储来支持这个.

如果要做这种约束,只能通过物化整个逻辑视图实现. 要么就在trigger中做.

这块的确还不够智能,也行有其他的考量.

另一个可能的思路是 view上的trigger. 我真不理解view上的trigger,一直认为,视图是动态产生的.

以我们对分区表使用的需求来说,父表不会存储数据,所以我同意你这样的理解。就应用上看父表是一个视图,无法对子表有任何约束,包括查询计划都无法影响(约束排除是建立在子表上的)。但可以影响对自身数据的插入(使用触发器把对父表自身插入转向到特定子表上),当然你可以在应用层实现插入数据时绕过父表(的触发器)而是直接对目的子表进行插入(及UPDATE)。

但 就技术上父表的确是物理表,即使是有子表继承它,它自身也可以保存数据。好比前面那个城市、州府城市父子表的例子。

我的理解,视图不是动态产生的。它逻辑上就是个数据表。我对orale的所谓物化视图不了解,不知道有何区别。好像pg视图上面无法加约束和触发器,后者可通过rule来实现。但rule的缺陷是当SQL包含 RETURNING col时,查询无法获得返回值。

补充:视图上可以有触发器,不过使用有局限

另一方面,一个用于 INSERT 视图的触发器可以做到与规则一样,把数据放到另外的地方去而取代对视图的插入。但它不能在 UPDATE 或 DELETE 时做同样的事情,因为在视图关系里没有可供扫描的真实数据,因而触发器将永远不被调用。这时只有规则可用。

对于这个视图, 我是这样理解的:

query planer 会在 select from parent的时候, 默认行为对子表进行遍历, 这个行为对 select, delete都生效.

但是, 在物理上, 并不存在这个结果集,而是在查询时动态建立的. 

我的感受是, only 的情况,是一个实际存在的物理表, 非only的情况,实际上是一个结果集(逻辑上的表).

这也是它无法支持 constraint的原因, 实际上,这个"表"里面的数据,数据库根本不知道, 而是在查询动态建立.

这个过程类似 cursor移动的时候, 只有到达最后一刻,你才知道匹配数据有多少.

Oracle的物化视图,可以将任意查询, 变成实际的存储表, 而以视图形式展现. 这个内部存储很神奇,并不是简单的数据复制. 可以极高提高查询效率.

在PG达到类似的效果,需要编写 trigger, 但是oracle是透明的.

只是一点点理解,仅供参考.

宏哥
宏哥
回复 @mark35 : 你之前提到oracle的区别.Oracle就是在这种"高级"特性上无出其右.
mark35
mark35
明白了。这也是视图无法被其他表外键引用的原因——视图本身不存在数据,数据库无法据此来实现某些功能,比如约束、引用。
0
mark35
mark35
第二个没看懂~
0
宏哥
宏哥

引用来自“mark35”的答案

第二个没看懂~

你的回答已经告诉我不需要第二个了. 因为表源知道了.

Thanks!

0
宏哥
宏哥

引用来自“mark35”的答案

第二个没看懂~
我又想到一个问题, 父子表之间的关系在那个系统表或者视图查询?
宏哥
宏哥
回复 @mark35 : select * from pg_catalog.pg_inherits;
宏哥
宏哥
@mark35 Ok,我立刻查
mark35
mark35
所有关系都在pg_class中
0
宏哥
宏哥

@mark35   , 我做了一个小的视图,这样在application dictionary 里面就很容易查找表了.

在ad 里面记录一下表的oid即可

CREATE OR REPLACE VIEW master.v_table AS 

 SELECT c.oid, n.nspname AS se_name, c.relname AS tb_name

   FROM pg_class c

   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

  WHERE c.relkind = 'r'::"char";

0
宏哥
宏哥

@mark35    

如果其他表当中字段引用外键为 某个父表, 那么这个父表所属子表数据是否对这个外键有效, 还是可以指定 Only?

0
宏哥
宏哥

引用来自“宏哥”的答案

@mark35    

如果其他表当中字段引用外键为 某个父表, 那么这个父表所属子表数据是否对这个外键有效, 还是可以指定 Only?

@mark35  

找到答案了:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and for-eign key constraints only apply to single tables, not to their inheritance children. This is true on both

the referencing and referenced sides of a foreign key constraint. 

0
宏哥
宏哥

@mark35   

1: 对rules 有没有研究?

2: 在view上的trigger有用过吗?

返回顶部
顶部