Thread: BUG #14434: Drop a table with a serial in an extension
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQzNApMb2dnZWQgYnk6ICAg ICAgICAgIFBoaWxpcHBlIEJFQVVET0lOCkVtYWlsIGFkZHJlc3M6ICAgICAg cGhiMDdAYXByYS5hc3NvLmZyClBvc3RncmVTUUwgdmVyc2lvbjogOS42LjEK T3BlcmF0aW5nIHN5c3RlbTogICBMaW51eCBVYnVudHUKRGVzY3JpcHRpb246 ICAgICAgICAKCkkgcmVhY2ggc29tZXRoaW5nIHN0cmFuZ2UgcmVjZW50bHkg d2hlbiBkZXZlbG9waW5nIGFuIHVwZ3JhZGUgc2NyaXB0IGZvciBhbgpleHRl bnNpb24uDQoNCkxldCdzIGNyZWF0ZSBhIHRhYmxlIHdpdGggYSBzZXJpYWwg Y29sdW1uIChvdXRzaWRlIGFueSBleHRlbnNpb24pLg0KVGhlbiBsZXQncyBs aW5rIHRoZSB0YWJsZSB3aXRoIGl0cyBzZXF1ZW5jZSB0byBhIG5ldyBleHRl bnNpb24gdXNpbmcgQUxURVIKRVhURU5TSU9OIEFERCAuLi4gaW4gdGhlIGV4 dGVuc2lvbiBzY3JpcHQuDQpFdmVuIHRob3VnaCB0aGUgZG9jdW1lbnRhdGlv biBpcyBub3QgY2xlYXIgb24gdGhhdCBwb2ludCwgaXQgaXMgbmVjZXNzYXJ5 IHRvCmV4cGxpY2l0bHkgbGluayB0aGUgc2VxdWVuY2UgdG8gdGhlIGV4dGVu c2lvbiwgbm90IHRvIGJlIGluIHRyb3VibGUgd2l0aApwZ19kdW1wIGZvciBp bnN0YW5jZS4NClNvIGZhciBzbyBnb29kLiBFdmVyeXRoaW5nIHJlYWN0cyBh cyBpZiB0aGUgdGFibGUgaGFkIGJlZW4gY3JlYXRlZCBpbnNpZGUKdGhlIGV4 dGVuc2lvbi4NCg0KQnV0IGFuIGVycm9yIG9jY3VycyB3aGVuIEkgdHJ5IHRv IGRyb3AgdGhpcyB0YWJsZSBpbiB0aGUgbmV4dCBleHRlbnNpb24KdXBncmFk ZSBzY3JpcHQ6DQpFUlJPUjogIGNhbm5vdCBkcm9wIHRhYmxlIG9sZF90Ymwx IGJlY2F1c2Ugb3RoZXIgb2JqZWN0cyBkZXBlbmQgb24gaXQNCkRFVEFJTDog IGV4dGVuc2lvbiBleHQxIGRlcGVuZHMgb24gdGFibGUgb2xkX3RibDENCkhJ TlQ6ICBVc2UgRFJPUCAuLi4gQ0FTQ0FERSB0byBkcm9wIHRoZSBkZXBlbmRl bnQgb2JqZWN0cyB0b28uDQoNCkFzIGEgd29ya2Fyb3VuZCwgdGhlIHNlcXVl bmNlIGNhbiBiZSB1bmxpbmtlZCBmcm9tIHRoZSBleHRlbnNpb24ganVzdCBi ZWZvcmUKZHJvcHBpbmcgdGhlIHRhYmxlLg0KDQpIZXJlIGlzIGEgdGVzdCBj YXNlIHRoYXQgc2hvd3MgdGhlIGlzc3VlIChqdXN0IGFkanVzdCB0aGUgMyB2 YXJpYWJsZXMgc2V0IGF0CnRoZSBiZWdpbm5pbmcgb2YgdGhlIHNjcmlwdCk6 DQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KDQpleHBv cnQgUEdIT1NUPWxvY2FsaG9zdA0KZXhwb3J0IFBHUE9SVD01NDk2DQpQR0VY VEVOU0lPTj0iL2hvbWUvcG9zdGdyZXMvcGcvcGc5Ni9zaGFyZS9wb3N0Z3Jl c3FsL2V4dGVuc2lvbiINCg0KIyBTdGVwIDE6IHByZXBhcmUgZXh0ZW5zaW9u IGVudmlyb25tZW50DQplY2hvICIqKipQcmVwYXJlIGV4dGVuc2lvbiBlbnZp cm9ubWVudCINCg0KY2F0ID4vdG1wL2V4dDEuY29udHJvbCA8PEVPRjANCmRl ZmF1bHRfdmVyc2lvbiA9ICcxJw0KZGlyZWN0b3J5ID0gJy90bXAnDQpzdXBl cnVzZXIgPSB0cnVlDQpFT0YwDQpzdWRvIGxuIC1zIC90bXAvZXh0MS5jb250 cm9sICRQR0VYVEVOU0lPTi9leHQxLmNvbnRyb2wNCg0KIyBTdGVwIDI6IGNy ZWF0ZSBhIHRhYmxlDQplY2hvICIqKiogQ3JlYXRlIDIgaW5pdGlhbCB0YWJs ZXMiDQpwc3FsIDw8RU9GMQ0Kc2VsZWN0IHZlcnNpb24oKTsNCmRyb3AgdGFi bGUgaWYgZXhpc3RzIG9sZF90YmwxOw0KY3JlYXRlIHRhYmxlIG9sZF90Ymwx IChjb2wxIHNlcmlhbCk7DQpkcm9wIHRhYmxlIGlmIGV4aXN0cyBvbGRfdGJs MjsNCmNyZWF0ZSB0YWJsZSBvbGRfdGJsMiAoY29sMiBzZXJpYWwpOw0KRU9G MQ0KDQojIFN0ZXAgMzogY3JlYXRlIHRoZSBleHRlbnNpb24gZnJvbSB1bnBh Y2thZ2VkDQplY2hvICIqKiogQ3JlYXRlIHRoZSBleHRlbnNpb24iDQpjYXQg Pi90bXAvZXh0MS0tdW5wYWNrYWdlZC0tMS5zcWwgPDxFT0YyDQotLSBsaW5r IGJvdGggb2xkIHRhYmxlcyBidXQgb25seSBmaXJzdCBzZXF1ZW5jZSB0byB0 aGUgZXh0ZW5zaW9uDQphbHRlciBleHRlbnNpb24gZXh0MSBhZGQgdGFibGUg b2xkX3RibDE7DQphbHRlciBleHRlbnNpb24gZXh0MSBhZGQgc2VxdWVuY2Ug b2xkX3RibDFfY29sMV9zZXE7DQphbHRlciBleHRlbnNpb24gZXh0MSBhZGQg dGFibGUgb2xkX3RibDI7DQotLSBjcmVhdGUgYW5vdGhlciByZWd1bGFyIHRh YmxlIHdpdGggYSBzZXJpYWwgY29sdW1uDQpkcm9wIHRhYmxlIGlmIGV4aXN0 cyBuZXdfdGJsMzsNCmNyZWF0ZSB0YWJsZSBuZXdfdGJsMyAoY29sMyBzZXJp YWwpOw0KRU9GMg0KDQpwc3FsIDw8RU9GMw0KY3JlYXRlIGV4dGVuc2lvbiBl eHQxIGZyb20gdW5wYWNrYWdlZDsNClxkeCBleHQxDQpcZWNobyBsaXN0IGRl cGVuZGFuY2llcyB3aXRoIHRoZSBvbGRfdGJsMSB0YWJsZSBhbmQgaXRzIHNl cXVlbmNlDQpzZWxlY3QKcGdfZGVzY3JpYmVfb2JqZWN0KGNsYXNzaWQsb2Jq aWQsb2Jqc3ViaWQpLHBnX2Rlc2NyaWJlX29iamVjdChyZWZjbGFzc2lkLHJl Zm9iamlkLHJlZm9ianN1YmlkKSwqCmZyb20gcGdfZGVwZW5kDQp3aGVyZSBk ZXB0eXBlIDw+ICduJyBhbmQNCiAgICAgKHBnX2Rlc2NyaWJlX29iamVjdChj bGFzc2lkLG9iamlkLG9ianN1YmlkKSA9ICd0YWJsZSBvbGRfdGJsMScNCiAg IG9yIHBnX2Rlc2NyaWJlX29iamVjdChjbGFzc2lkLG9iamlkLG9ianN1Ymlk KSA9ICdzZXF1ZW5jZQpvbGRfdGJsMV9jb2wxX3NlcScpDQpvcmRlciBieSAx LDI7DQoNClxlY2hvIGxpc3QgZGVwZW5kYW5jaWVzIHdpdGggdGhlIG9sZF90 YmwyIHRhYmxlIGFuZCBpdHMgc2VxdWVuY2UNCnNlbGVjdApwZ19kZXNjcmli ZV9vYmplY3QoY2xhc3NpZCxvYmppZCxvYmpzdWJpZCkscGdfZGVzY3JpYmVf b2JqZWN0KHJlZmNsYXNzaWQscmVmb2JqaWQscmVmb2Jqc3ViaWQpLCoKZnJv bSBwZ19kZXBlbmQNCndoZXJlIGRlcHR5cGUgPD4gJ24nIGFuZA0KICAgICAo cGdfZGVzY3JpYmVfb2JqZWN0KGNsYXNzaWQsb2JqaWQsb2Jqc3ViaWQpID0g J3RhYmxlIG9sZF90YmwyJw0KICAgb3IgcGdfZGVzY3JpYmVfb2JqZWN0KGNs YXNzaWQsb2JqaWQsb2Jqc3ViaWQpID0gJ3NlcXVlbmNlCm9sZF90YmwyX2Nv bDJfc2VxJykNCm9yZGVyIGJ5IDEsMjsNCg0KXGVjaG8gbGlzdCBkZXBlbmRh bmNpZXMgd2l0aCB0aGUgbmV3X3RibDMgdGFibGUgYW5kIGl0cyBzZXF1ZW5j ZQ0Kc2VsZWN0CnBnX2Rlc2NyaWJlX29iamVjdChjbGFzc2lkLG9iamlkLG9i anN1YmlkKSxwZ19kZXNjcmliZV9vYmplY3QocmVmY2xhc3NpZCxyZWZvYmpp ZCxyZWZvYmpzdWJpZCksKgpmcm9tIHBnX2RlcGVuZA0Kd2hlcmUgZGVwdHlw ZSA8PiAnbicgYW5kDQogICAgIChwZ19kZXNjcmliZV9vYmplY3QoY2xhc3Np ZCxvYmppZCxvYmpzdWJpZCkgPSAndGFibGUgbmV3X3RibDMnDQogICBvciBw Z19kZXNjcmliZV9vYmplY3QoY2xhc3NpZCxvYmppZCxvYmpzdWJpZCkgPSAn c2VxdWVuY2UKbmV3X3RibDNfY29sM19zZXEnKQ0Kb3JkZXIgYnkgMSwyOw0K XGVjaG8gVGhpcyByZWZsZWN0cyBjb3JyZWN0bHkgdGhlIGV4cGVjdGVkIGRl cGVuZGFuY2llcyAoYXQgbGVhc3QgdG8gbWUpDQpFT0YzDQoNCmVjaG8gIioq KiBwZ19kdW1wIGVmZmVjdGl2ZWx5IGRvZXNuJ3QgY29uc2lkZXIgdGhlIDJu ZCBzZXF1ZW5jZSBhcyBtZW1iZXIgb2YKdGhlIGV4dGVuc2lvbiINCnBnX2R1 bXAgfGdyZXAgLVAgJ1NFUVVFTkNFIC4qdGJsXGRfY29sXGRfc2VxJw0KDQoj IFN0ZXAgNDogdXBkYXRlIHRoZSBleHRlbnNpb24NCmVjaG8gIioqKiBUcnkg dG8gdXBkYXRlIHRoZSBleHRlbnNpb24iDQpjYXQgPi90bXAvZXh0MS0tMS0t Mi5zcWwgPDxFT0Y0DQpkcm9wIHRhYmxlIG9sZF90YmwyOw0KLS0gYWx0ZXIg ZXh0ZW5zaW9uIGV4dDEgZHJvcCBzZXF1ZW5jZSBvbGRfdGJsMV9jb2wxX3Nl cTsNCmRyb3AgdGFibGUgb2xkX3RibDE7DQpFT0Y0DQoNCnBzcWwgPDxFT0Y1 DQphbHRlciBleHRlbnNpb24gZXh0MSB1cGRhdGUgdG8gJzInOw0KXGR4IGV4 dDENCkVPRjUNCg0KZWNobyAiKioqIEVuZCBvZiB0ZXN0Ig0KcHNxbCAtYyAi ZHJvcCBleHRlbnNpb24gZXh0MTsiDQpzdWRvIHJtICRQR0VYVEVOU0lPTi9l eHQxLmNvbnRyb2wNCnJtIC90bXAvZXh0MSoNCg0KLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KQW5kIGF0IGV4ZWN1dGlvbiB0aW1l Og0KDQoqKipQcmVwYXJlIGV4dGVuc2lvbiBlbnZpcm9ubWVudA0KKioqIENy ZWF0ZSAyIGluaXRpYWwgdGFibGVzDQogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHZlcnNpb24gICAgICAg ICAgICAgICAgCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg DQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIFBvc3RncmVTUUwgOS42LjEg b24geDg2XzY0LXBjLWxpbnV4LWdudSwgY29tcGlsZWQgYnkgZ2NjIChVYnVu dHUKNS40LjAtNnVidW50dTF+MTYuMDQuNCkgNS40LjAgMjAxNjA2MDksIDY0 LWJpdA0KKDEgcm93KQ0KDQpOT1RJQ0U6ICB0YWJsZSAib2xkX3RibDEiIGRv ZXMgbm90IGV4aXN0LCBza2lwcGluZw0KRFJPUCBUQUJMRQ0KQ1JFQVRFIFRB QkxFDQpOT1RJQ0U6ICB0YWJsZSAib2xkX3RibDIiIGRvZXMgbm90IGV4aXN0 LCBza2lwcGluZw0KRFJPUCBUQUJMRQ0KQ1JFQVRFIFRBQkxFDQoqKiogQ3Jl YXRlIHRoZSBleHRlbnNpb24NCkNSRUFURSBFWFRFTlNJT04NCiAgICAgTGlz dCBvZiBpbnN0YWxsZWQgZXh0ZW5zaW9ucw0KIE5hbWUgfCBWZXJzaW9uIHwg U2NoZW1hIHwgRGVzY3JpcHRpb24NCi0tLS0tLSstLS0tLS0tLS0rLS0tLS0t LS0rLS0tLS0tLS0tLS0tLQ0KIGV4dDEgfCAxICAgICAgIHwgcHVibGljIHwN CigxIHJvdykNCg0KbGlzdCBkZXBlbmRhbmNpZXMgd2l0aCB0aGUgb2xkX3Ri bDEgdGFibGUgYW5kIGl0cyBzZXF1ZW5jZQ0KICAgICBwZ19kZXNjcmliZV9v YmplY3QgICAgIHwgICAgIHBnX2Rlc2NyaWJlX29iamVjdCAgICAgfCBjbGFz c2lkIHwgb2JqaWQgCnwgb2Jqc3ViaWQgfCByZWZjbGFzc2lkIHwgcmVmb2Jq aWQgfCByZWZvYmpzdWJpZCB8IGRlcHR5cGUNCi0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0t LS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0rLS0tLS0t LS0tLSstLS0tLS0tLS0tLS0tKy0tLS0tLS0tLQ0KIHNlcXVlbmNlIG9sZF90 YmwxX2NvbDFfc2VxIHwgZXh0ZW5zaW9uIGV4dDEgICAgICAgICAgICAgfCAg ICAxMjU5IHwgMTYxNTY1CnwgICAgICAgIDAgfCAgICAgICAzMDc5IHwgICAx NjE1NzcgfCAgICAgICAgICAgMCB8IGUNCiBzZXF1ZW5jZSBvbGRfdGJsMV9j b2wxX3NlcSB8IHRhYmxlIG9sZF90YmwxIGNvbHVtbiBjb2wxIHwgICAgMTI1 OSB8IDE2MTU2NQp8ICAgICAgICAwIHwgICAgICAgMTI1OSB8ICAgMTYxNTY3 IHwgICAgICAgICAgIDEgfCBhDQogdGFibGUgb2xkX3RibDEgICAgICAgICAg ICAgfCBleHRlbnNpb24gZXh0MSAgICAgICAgICAgICB8ICAgIDEyNTkgfCAx NjE1NjcKfCAgICAgICAgMCB8ICAgICAgIDMwNzkgfCAgIDE2MTU3NyB8ICAg ICAgICAgICAwIHwgZQ0KKDMgcm93cykNCg0KbGlzdCBkZXBlbmRhbmNpZXMg d2l0aCB0aGUgb2xkX3RibDIgdGFibGUgYW5kIGl0cyBzZXF1ZW5jZQ0KICAg ICBwZ19kZXNjcmliZV9vYmplY3QgICAgIHwgICAgIHBnX2Rlc2NyaWJlX29i amVjdCAgICAgfCBjbGFzc2lkIHwgb2JqaWQgCnwgb2Jqc3ViaWQgfCByZWZj bGFzc2lkIHwgcmVmb2JqaWQgfCByZWZvYmpzdWJpZCB8IGRlcHR5cGUNCi0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0t LS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tKy0tLS0tLS0tLQ0K IHNlcXVlbmNlIG9sZF90YmwyX2NvbDJfc2VxIHwgdGFibGUgb2xkX3RibDIg Y29sdW1uIGNvbDIgfCAgICAxMjU5IHwgMTYxNTcxCnwgICAgICAgIDAgfCAg ICAgICAxMjU5IHwgICAxNjE1NzMgfCAgICAgICAgICAgMSB8IGENCiB0YWJs ZSBvbGRfdGJsMiAgICAgICAgICAgICB8IGV4dGVuc2lvbiBleHQxICAgICAg ICAgICAgIHwgICAgMTI1OSB8IDE2MTU3Mwp8ICAgICAgICAwIHwgICAgICAg MzA3OSB8ICAgMTYxNTc3IHwgICAgICAgICAgIDAgfCBlDQooMiByb3dzKQ0K DQpsaXN0IGRlcGVuZGFuY2llcyB3aXRoIHRoZSBuZXdfdGJsMyB0YWJsZSBh bmQgaXRzIHNlcXVlbmNlDQogICAgIHBnX2Rlc2NyaWJlX29iamVjdCAgICAg fCAgICAgcGdfZGVzY3JpYmVfb2JqZWN0ICAgICB8IGNsYXNzaWQgfCBvYmpp ZCAKfCBvYmpzdWJpZCB8IHJlZmNsYXNzaWQgfCByZWZvYmppZCB8IHJlZm9i anN1YmlkIHwgZGVwdHlwZQ0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0t LS0tLSstLS0tLS0tLS0tKy0tLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0t LS0tLS0tLS0rLS0tLS0tLS0tDQogc2VxdWVuY2UgbmV3X3RibDNfY29sM19z ZXEgfCBleHRlbnNpb24gZXh0MSAgICAgICAgICAgICB8ICAgIDEyNTkgfCAx NjE1NzgKfCAgICAgICAgMCB8ICAgICAgIDMwNzkgfCAgIDE2MTU3NyB8ICAg ICAgICAgICAwIHwgZQ0KIHNlcXVlbmNlIG5ld190YmwzX2NvbDNfc2VxIHwg dGFibGUgbmV3X3RibDMgY29sdW1uIGNvbDMgfCAgICAxMjU5IHwgMTYxNTc4 CnwgICAgICAgIDAgfCAgICAgICAxMjU5IHwgICAxNjE1ODAgfCAgICAgICAg ICAgMSB8IGENCiB0YWJsZSBuZXdfdGJsMyAgICAgICAgICAgICB8IGV4dGVu c2lvbiBleHQxICAgICAgICAgICAgIHwgICAgMTI1OSB8IDE2MTU4MAp8ICAg ICAgICAwIHwgICAgICAgMzA3OSB8ICAgMTYxNTc3IHwgICAgICAgICAgIDAg fCBlDQooMyByb3dzKQ0KDQpUaGlzIHJlZmxlY3RzIGNvcnJlY3RseSB0aGUg ZXhwZWN0ZWQgZGVwZW5kYW5jaWVzIChhdCBsZWFzdCB0byBtZSkNCioqKiBw Z19kdW1wIGVmZmVjdGl2ZWx5IGRvZXNuJ3QgY29uc2lkZXIgdGhlIDJuZCBz ZXF1ZW5jZSBhcyBtZW1iZXIgb2YgdGhlCmV4dGVuc2lvbg0KQ1JFQVRFIFNF UVVFTkNFIG9sZF90YmwyX2NvbDJfc2VxDQoqKiogVHJ5IHRvIHVwZGF0ZSB0 aGUgZXh0ZW5zaW9uDQpFUlJPUjogIGNhbm5vdCBkcm9wIHRhYmxlIG9sZF90 YmwxIGJlY2F1c2Ugb3RoZXIgb2JqZWN0cyBkZXBlbmQgb24gaXQNCkRFVEFJ TDogIGV4dGVuc2lvbiBleHQxIGRlcGVuZHMgb24gdGFibGUgb2xkX3RibDEN CkhJTlQ6ICBVc2UgRFJPUCAuLi4gQ0FTQ0FERSB0byBkcm9wIHRoZSBkZXBl bmRlbnQgb2JqZWN0cyB0b28uDQogICAgIExpc3Qgb2YgaW5zdGFsbGVkIGV4 dGVuc2lvbnMNCiBOYW1lIHwgVmVyc2lvbiB8IFNjaGVtYSB8IERlc2NyaXB0 aW9uDQotLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0N CiBleHQxIHwgMSAgICAgICB8IHB1YmxpYyB8DQooMSByb3cpDQoNCioqKiBF bmQgb2YgdGVzdA0KRFJPUCBFWFRFTlNJT04KCg==
phb07@apra.asso.fr writes:
> Let's create a table with a serial column (outside any extension).
> Then let's link the table with its sequence to a new extension using ALTER
> EXTENSION ADD ... in the extension script.
> Even though the documentation is not clear on that point, it is necessary to
> explicitly link the sequence to the extension, not to be in trouble with
> pg_dump for instance.
> So far so good. Everything reacts as if the table had been created inside
> the extension.
> But an error occurs when I try to drop this table in the next extension
> upgrade script:
> ERROR: cannot drop table old_tbl1 because other objects depend on it
> DETAIL: extension ext1 depends on table old_tbl1
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
> As a workaround, the sequence can be unlinked from the extension just before
> dropping the table.
Don't see why that's a "workaround". You added the extension membership
for the sequence explicitly, why wouldn't you expect to need to drop it
explicitly? Seems to me the system is behaving properly.
If you want to drop the table but keep the sequence, you need to unlink
the sequence from the table, a la
alter sequence old_tbl1_col1_seq owned by none;
before dropping the table.
regards, tom lane
Thanks Tom, Le 26/11/2016 à 01:59, Tom Lane a écrit : > phb07@apra.asso.fr writes: >> Let's create a table with a serial column (outside any extension). >> Then let's link the table with its sequence to a new extension using ALTER >> EXTENSION ADD ... in the extension script. >> Even though the documentation is not clear on that point, it is necessary to >> explicitly link the sequence to the extension, not to be in trouble with >> pg_dump for instance. >> So far so good. Everything reacts as if the table had been created inside >> the extension. >> But an error occurs when I try to drop this table in the next extension >> upgrade script: >> ERROR: cannot drop table old_tbl1 because other objects depend on it >> DETAIL: extension ext1 depends on table old_tbl1 >> HINT: Use DROP ... CASCADE to drop the dependent objects too. >> As a workaround, the sequence can be unlinked from the extension just before >> dropping the table. > Don't see why that's a "workaround". You added the extension membership > for the sequence explicitly, why wouldn't you expect to need to drop it > explicitly? Seems to me the system is behaving properly. Because depending on the way a table has been included in the extension (either directly created inside the extension or first created outside and then linked to the extension) the procedure to drop it some versions later would be different. Of course it would be normal to get this error if the sequence would have been linked to another extension. But my feeling is that dropping a table that generates a sequence drop, both belonging to the same extension, shouldn't be refused. If the current behaviour is considered as "normal", then the error message looks strange anyway. In particular the DETAIL output doesn't reflect the real issue as it doesn't mention the faulting sequence. And the DROP TABLE ... CASCADE suggested in the HINT doesn't help too. And a short sentence in the documentation (in the ALTER EXTENSION page for instance) would also probably help. > If you want to drop the table but keep the sequence, you need to unlink > the sequence from the table, a la > > alter sequence old_tbl1_col1_seq owned by none; > > before dropping the table. OK, but in this case, I didn't want to keep the sequence. > regards, tom lane > Regards. Philippe Beaudoin
phb07 <phb07@apra.asso.fr> writes:
> Le 26/11/2016 à 01:59, Tom Lane a écrit :
>> Don't see why that's a "workaround". You added the extension membership
>> for the sequence explicitly, why wouldn't you expect to need to drop it
>> explicitly? Seems to me the system is behaving properly.
> Because depending on the way a table has been included in the extension
> (either directly created inside the extension or first created outside
> and then linked to the extension) the procedure to drop it some versions
> later would be different.
Well, no it wouldn't be, but nonetheless on closer study I think you're
right that this is a bug. There is code in there that intends to make it
unnecessary to issue "ALTER EXTENSION DROP member" when an extension
update script drops a member object; it should be sufficient to just
drop the object. It was failing to fire in this case because the drop
was indirect, but it should work anyway.
I've applied a patch for that. Thanks for the report!
regards, tom lane
Le 26/11/2016 à 19:36, Tom Lane a écrit : > phb07 <phb07@apra.asso.fr> writes: >> Le 26/11/2016 à 01:59, Tom Lane a écrit : >>> Don't see why that's a "workaround". You added the extension membership >>> for the sequence explicitly, why wouldn't you expect to need to drop it >>> explicitly? Seems to me the system is behaving properly. >> Because depending on the way a table has been included in the extension >> (either directly created inside the extension or first created outside >> and then linked to the extension) the procedure to drop it some versions >> later would be different. > Well, no it wouldn't be, but nonetheless on closer study I think you're > right that this is a bug. There is code in there that intends to make it > unnecessary to issue "ALTER EXTENSION DROP member" when an extension > update script drops a member object; it should be sufficient to just > drop the object. It was failing to fire in this case because the drop > was indirect, but it should work anyway. > > I've applied a patch for that. Thanks for the report! Thank You very much, Tom. > > regards, tom lane >