Обсуждение: BUG #14147: Restore of MatView of Foreign Data Table fails
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE0NwpMb2dnZWQgYnk6ICAg ICAgICAgIExsb3lkIEFsYmluCkVtYWlsIGFkZHJlc3M6ICAgICAgbGFsYmlu QHNjaGFycC5vcmcKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuMwpPcGVyYXRp bmcgc3lzdGVtOiAgIFNVU0UgTGludXggKDY0LWJpdCkKRGVzY3JpcHRpb246 ICAgICAgICAKCkkgaGF2ZSBmb3VuZCB0aGF0IHJlc3RvcmF0aW9uIG9mIE1h dGVyaWFsaXplZCBWaWV3cyBvZiBGb3JlaWduIERhdGEgVGFibGVzCndoZXJl IHVzZXIgcG9zdGdyZXMgZG9lcyBub3QgaGF2ZSBhIHVzZXIgbWFwcGluZyB3 aWxsIGZhaWwuIEl0IHNlZW1zIHRoYXQgaXQKaXMgdHJ5aW5nIHRvIGV4ZWN1 dGUgdGhlIEZvcmVpZ24gRGF0YSBUYWJsZSBpbiBzb21lIHdheSBpbnN0ZWFk IG9mIHJlYWRpbmcKdGhlIEZvcmVpZ24gVGFibGUgRGVmaW5pdGlvbi4NCg0K VGhlIG9ubHkgd29ya2Fyb3VuZCB0aGF0IEkgaGF2ZSBmb3VuZCBpcyB0byBj cmVhdGUgYSB1c2VyIG1hcHBpbmcgZm9yIHVzZXIKcG9zdGdyZXMgZm9yIGV2 ZXJ5IGZvcmVpZ24gZGF0YSBjb25uZWN0aW9uLiANCg0KRXhhbXBsZToNCg0K Q1JFQVRFIERBVEFCQVNFIGRiX2ENCiAgV0lUSCBFTkNPRElORyA9ICdVVEY4 Jw0KICAgIFRFTVBMQVRFID0gdGVtcGxhdGUxOw0KDQpDUkVBVEUgUk9MRSBq b2UgTE9HSU4gUEFTU1dPUkQgJ3h4eCc7DQoNClNFVCBST0xFIGpvZTsNCgkN CkNSRUFURSBUQUJMRSBwdWJsaWMudGVzdCAoDQogIGlkIFNFUklBTCwNCiAg diBURVhULA0KICBQUklNQVJZIEtFWShpZCkNCikgDQpXSVRIIChvaWRzID0g ZmFsc2UpOw0KDQpTRVQgUk9MRSBwb3N0Z3JlczsNCgkNCkNSRUFURSBEQVRB QkFTRSBkYl9iDQogIFdJVEggRU5DT0RJTkcgPSAnVVRGOCcNCiAgICBURU1Q TEFURSA9IHRlbXBsYXRlMTsJDQoNCkNSRUFURSBFWFRFTlNJT04gcG9zdGdy ZXNfZmR3Ow0KDQpHUkFOVCBVU0FHRQ0KICBPTiBGT1JFSUdOIERBVEEgV1JB UFBFUiBwb3N0Z3Jlc19mZHcgVE8gam9lOw0KDQpDUkVBVEUgU0VSVkVSIGRi X2ENCiAgRk9SRUlHTiBEQVRBIFdSQVBQRVIgcG9zdGdyZXNfZmR3DQogIE9Q VElPTlMgKA0KICAgIGhvc3QgJ2xvY2FsaG9zdCcsDQogICAgcG9ydCAnNTQz MicsDQogICAgZGJuYW1lICdkYl9hJyk7DQoNCkNSRUFURSBVU0VSIE1BUFBJ TkcgRk9SIGpvZQ0KICBTRVJWRVIgZGJfYQ0KICBPUFRJT05TICgNCiAgICB1 c2VyICdqb2UnLA0KICAgIHBhc3N3b3JkICd4eHgnKTsNCg0KQ1JFQVRFIEZP UkVJR04gVEFCTEUgcHVibGljLiJ0ZXN0IiAoDQogIGlkIElOVEVHRVIgTk9U IE5VTEwsDQogIHYgVEVYVA0KKSANClNFUlZFUiBkYl9hDQpPUFRJT05TICgN CiAgc2NoZW1hX25hbWUgJ3B1YmxpYycsDQogIHRhYmxlX25hbWUgJ3Rlc3Qn KTsNCg0KQ1JFQVRFIE1BVEVSSUFMSVpFRCBWSUVXIHB1YmxpYy5tdl90ZXN0 IEFTIFNFTEVDVCAqIEZST00gcHVibGljLnRlc3Q7DQogIA0KQ1JFQVRFIERB VEFCQVNFIGRiX2MNCiAgV0lUSCBFTkNPRElORyA9ICdVVEY4Jw0KICAgIFRF TVBMQVRFID0gdGVtcGxhdGUxOw0KDQpwZ19kdW1wIC1oIGxvY2FsaG9zdCAt VSBwb3N0Z3JlcyAtRmMgZGJfYiA+IGRiX2IucGdkdW1wDQoNCnBnX3Jlc3Rv cmUgLWggbG9jYWxob3N0IC1VIHBvc3RncmVzIC1kIGRiX2MgZGJfYi5wZ2R1 bXAJDQoNCnBnX3Jlc3RvcmU6IFthcmNoaXZlciAoZGIpXSBFcnJvciB3aGls ZSBQUk9DRVNTSU5HIFRPQzoNCnBnX3Jlc3RvcmU6IFthcmNoaXZlciAoZGIp XSBFcnJvciBmcm9tIFRPQyBlbnRyeSAxODM7IDEyNTkgMTY0MzAKTUFURVJJ QUxJWkVEIFZJDQpFVyBtdl90ZXN0IGpvZQ0KcGdfcmVzdG9yZTogW2FyY2hp dmVyIChkYildIGNvdWxkIG5vdCBleGVjdXRlIHF1ZXJ5OiBFUlJPUjogIHVz ZXIgbWFwcGluZwpub3QgZm8NCnVuZCBmb3IgInBvc3RncmVzIg0KICAgIENv bW1hbmQgd2FzOiBDUkVBVEUgTUFURVJJQUxJWkVEIFZJRVcgbXZfdGVzdCBB Uw0KIFNFTEVDVCB0ZXN0LmlkLA0KICAgIHRlc3Qudg0KICAgRlJPTSB0ZXN0 DQogIFdJVEggTk8gREFUQTsNCg0KDQoNCnBnX3Jlc3RvcmU6IFthcmNoaXZl ciAoZGIpXSBjb3VsZCBub3QgZXhlY3V0ZSBxdWVyeTogRVJST1I6ICByZWxh dGlvbgoibXZfdGVzdCINCmRvZXMgbm90IGV4aXN0DQogICAgQ29tbWFuZCB3 YXM6IEFMVEVSIFRBQkxFIG12X3Rlc3QgT1dORVIgVE8gam9lOw0KDQoNCnBn X3Jlc3RvcmU6IFthcmNoaXZlciAoZGIpXSBFcnJvciBmcm9tIFRPQyBlbnRy eSAyMTA0OyAwIDE2NDMwIE1BVEVSSUFMSVpFRApWSUVXDQogREFUQSBtdl90 ZXN0IGpvZQ0KcGdfcmVzdG9yZTogW2FyY2hpdmVyIChkYildIGNvdWxkIG5v dCBleGVjdXRlIHF1ZXJ5OiBFUlJPUjogIHJlbGF0aW9uCiJtdl90ZXN0Ig0K ZG9lcyBub3QgZXhpc3QNCiAgICBDb21tYW5kIHdhczogUkVGUkVTSCBNQVRF UklBTElaRUQgVklFVyBtdl90ZXN0Ow0KDQoNCg0KV0FSTklORzogZXJyb3Jz IGlnbm9yZWQgb24gcmVzdG9yZTogMw0KCgo=
lalbin@scharp.org writes:
> I have found that restoration of Materialized Views of Foreign Data Tables
> where user postgres does not have a user mapping will fail. It seems that it
> is trying to execute the Foreign Data Table in some way instead of reading
> the Foreign Table Definition.
This is another variant of a problem that's been complained of multiple
times before: CREATE MATERIALIZED VIEW ... WITH NO DATA runs planning and
even does ExecutorStart for the view's query, causing issues such as lack
of permissions to be exposed. This breaks assorted assumptions in pg_dump
and probably other places. We need to refactor things so that that does
not happen. Kevin, are you planning to fix that anytime soon?
regards, tom lane
On Mon, May 23, 2016 at 2:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > lalbin@scharp.org writes: >> I have found that restoration of Materialized Views of Foreign Data Tables >> where user postgres does not have a user mapping will fail. It seems that it >> is trying to execute the Foreign Data Table in some way instead of reading >> the Foreign Table Definition. > > This is another variant of a problem that's been complained of multiple > times before: CREATE MATERIALIZED VIEW ... WITH NO DATA runs planning and > even does ExecutorStart for the view's query, causing issues such as lack > of permissions to be exposed. This breaks assorted assumptions in pg_dump > and probably other places. We need to refactor things so that that does > not happen. Kevin, are you planning to fix that anytime soon? I'll move this to the top of my list after dealing with any 9.6 issues that come up. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company