Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mXi8I-0007MK-SM for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Oct 2021 10:57:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mXi8H-0003C5-R0 for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Oct 2021 10:57:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mXhCr-00012M-O7 for pgsql-hackers@lists.postgresql.org; Tue, 05 Oct 2021 09:57:54 +0000 Received: from smtp-fw-33001.amazon.com ([207.171.190.10]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mXhCn-0001AN-9r for pgsql-hackers@postgresql.org; Tue, 05 Oct 2021 09:57:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=amazon.com; i=@amazon.com; q=dns/txt; s=amazon201209; t=1633427870; x=1664963870; h=from:to:cc:date:message-id:references:in-reply-to: mime-version:subject; bh=DUCUpXiIamLYdNikvXF3I9dD4O5j5B/HKKCbXUrNDdw=; b=eUD4XdmbnDaDmhrT2gqKBTgMXhdGqUHN/TEpYdWWDd6D2ksB0zJ5IDzr Jwazp2edxHf0NyfCtf2UqXs0OjymEXFugZbUQ5KxfGlCMXd94Tiq8V+HF VcD9nBAumA3+pI0Kq5TGhkURGTxOI7VECjAV2hipoQpeyjLxK/C4k6859 s=; X-Amazon-filename: v1-earthdistance-cube-fix.patch X-IronPort-AV: E=Sophos;i="5.85,348,1624320000"; d="scan'208";a="151750029" Subject: RE: Extension relocation vs. schema qualification Thread-Topic: Extension relocation vs. schema qualification Received: from iad12-co-svc-p1-lb1-vlan2.amazon.com (HELO email-inbound-relay-iad-1d-54a073b7.us-east-1.amazon.com) ([10.43.8.2]) by smtp-border-fw-33001.sea14.amazon.com with ESMTP; 05 Oct 2021 09:57:41 +0000 Received: from EX13MTAUWB001.ant.amazon.com (iad12-ws-svc-p26-lb9-vlan3.iad.amazon.com [10.40.163.38]) by email-inbound-relay-iad-1d-54a073b7.us-east-1.amazon.com (Postfix) with ESMTPS id 2808281197; Tue, 5 Oct 2021 09:57:38 +0000 (UTC) Received: from EX13D13UWB002.ant.amazon.com (10.43.161.21) by EX13MTAUWB001.ant.amazon.com (10.43.161.249) with Microsoft SMTP Server (TLS) id 15.0.1497.23; Tue, 5 Oct 2021 09:57:38 +0000 Received: from EX13D20EUC002.ant.amazon.com (10.43.164.34) by EX13D13UWB002.ant.amazon.com (10.43.161.21) with Microsoft SMTP Server (TLS) id 15.0.1497.23; Tue, 5 Oct 2021 09:57:37 +0000 Received: from EX13D20EUC002.ant.amazon.com ([10.43.164.34]) by EX13D20EUC002.ant.amazon.com ([10.43.164.34]) with mapi id 15.00.1497.023; Tue, 5 Oct 2021 09:57:37 +0000 From: "Verona, Luiz" To: Noah Misch , Bruce Momjian CC: "keiko713@gmail.com" , "pgsql-bugs@lists.postgresql.org" , PostgreSQL-development , "Imseih (AWS), Sami" Thread-Index: AQHXuSoYMZCOYCXusUKtTS0dIGV+9qvDB0lg Date: Tue, 5 Oct 2021 09:57:36 +0000 Message-ID: <3a89b08b90ac4aef99bce42ba7a23c67@EX13D20EUC002.ant.amazon.com> References: <152106914669.1223.5104148605998271987@wrigleys.postgresql.org> <20180330205229.GS8476@momjian.us> <20180402202402.GA24073@momjian.us> <20180405065957.GA2276534@rfd.leadboat.com> <20180710014308.GA805781@rfd.leadboat.com> In-Reply-To: <20180710014308.GA805781@rfd.leadboat.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.43.165.203] Content-Type: multipart/mixed; boundary="_002_3a89b08b90ac4aef99bce42ba7a23c67EX13D20EUC002antamazonc_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_002_3a89b08b90ac4aef99bce42ba7a23c67EX13D20EUC002antamazonc_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I am writing to resurrect this 3-year-old thread. Attached is a patch to ad= dress earthdistance related failures during pg_restore. The proposed patch will:=20 - Create a new version of earthdistance (1.2) and make this new version de= fault - Change earthdistance relocatable from true to false - SET SEARCH_PATH=3D@extschema@ in earthdistance functions - Create validation to restrict earthdistance to be created only in the sa= me schema as cube - Change cube extension relocatable from true to false to avoid a relocate= of cube which will break earthdistance. - Change documentation [1] FROM "It is strongly recommended that earthdist= ance and cube be installed in the same schema". TO "it's required that ear= thdistance and cube be installed and kept in the same schema" [1] - https://www.postgresql.org/docs/current/earthdistance.html#EARTHDISTA= NCE ---------- ## Proposal - pg_restore test: $ psql < select version(); > drop extension if exists cube cascade; > create extension earthdistance cascade ; > > drop schema if exists test cascade; > create schema test; > > --set search_path=3Dtest,public; > drop table if exists test.addresses cascade; > create table test.addresses ( > latitude float8, > longitude float8 > ); > > CREATE INDEX locations ON test.addresses USING gist(ll_to_earth(latitude,= longitude)); > > \dx > EOF version ---------------------------------------------------------------------------= --------------------------------- PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 201= 80712 (Red Hat 7.3.1-12), 64-bit (1 row) NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to extension earthdistance drop cascades to index test.locations DROP EXTENSION NOTICE: installing required extension "cube" CREATE EXTENSION NOTICE: drop cascades to table test.addresses DROP SCHEMA CREATE SCHEMA NOTICE: table "addresses" does not exist, skipping DROP TABLE CREATE TABLE CREATE INDEX List of installed extensions Name | Version | Schema | Description ---------------+---------+------------+------------------------------------= -------------------------- cube | 1.5 | public | data type for multidimensional cube= s earthdistance | 1.2 | public | calculate great-circle distances on= the surface of the Earth plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows) $ ## dump the schema $ rm /tmp/test.dmp $ pg_dump --schema test --format=3Dcustom --file /tmp/test.dmp $ $ ## drop the schema $ psql < drop schema test cascade ; > EOF NOTICE: drop cascades to table test.addresses DROP SCHEMA $ $ $ ## attempt to restore the schema $ pg_restore -d postgres /tmp/test.dmp $ ## Check restored schema. $ psql < \d+ test.addresses > EOF Table "test.addresses" Column | Type | Collation | Nullable | Default | Storage | = Compression | Stats target | Description -----------+------------------+-----------+----------+---------+---------+-= ------------+--------------+------------- latitude | double precision | | | | plain | = | | longitude | double precision | | | | plain | = | | Indexes: "locations" gist (ll_to_earth(latitude, longitude)) Access method: heap ---------- ## Proposal - schema dependency validation test: $ psql < select version(); > drop schema if exists test cascade; > create schema test; > drop extension if exists cube cascade; > create extension cube; > create extension earthdistance with schema test; > > \dx > EOF version ---------------------------------------------------------------------------= --------------------------------- PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 201= 80712 (Red Hat 7.3.1-12), 64-bit (1 row) DROP SCHEMA CREATE SCHEMA DROP EXTENSION CREATE EXTENSION ERROR: earthdistance extension must be installed in the same schema as the= cube extension CONTEXT: PL/pgSQL function inline_code_block line 11 at RAISE List of installed extensions Name | Version | Schema | Description ---------+---------+------------+-------------------------------------- cube | 1.5 | public | data type for multidimensional cubes plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) Luiz Verona -----Original Message----- From: Noah Misch =20 Sent: Tuesday, July 10, 2018 2:43 AM To: Bruce Momjian Cc: keiko713@gmail.com; pgsql-bugs@lists.postgresql.org; PostgreSQL-develop= ment Subject: [EXTERNAL] Extension relocation vs. schema qualification CAUTION: This email originated from outside of the organization. Do not cli= ck links or open attachments unless you can confirm the sender and know the= content is safe. On Wed, Apr 04, 2018 at 11:59:57PM -0700, Noah Misch wrote: > On Mon, Apr 02, 2018 at 04:24:02PM -0400, Bruce Momjian wrote: > > > I am not sure we can fix this without requiring people to drop and=20 > > > recreate such indexes. However, I am even at a loss in how to fix=20 > > > the CREATE FUNCTION to reference a cast in the same schema as the=20 > > > function, in this case 'public'. We can rewrite the cast to not=20 > > > use :: and use a function call with schema qualification. e.g.=20 > > > public.earth(), but how do we know what schema that is in, i.e.=20 > > > what if the extension is loaded into a schema other than public? > > The task is to convert it to being a non-relocatable extension that=20 > uses @extschema@, like here: > https://www.postgresql.org/docs/devel/static/extend-extensions.html#EX > TEND-EXTENSIONS-EXAMPLE Extension "earthdistance" creates sql-language functions that call function= s of extension "cube", which may appear outside @extschema@ and may relocat= e at any moment. Hence, this case is not as simple as using @extschema@. = While the cube/earthdistance system happens to be revealing this problem, i= t would arise in many cases of a function referring to an object of a reloc= atable extension. I see these options: 1. Stop using relocatable=3Dtrue in core extensions (by adding a new versio= n number and versioned control file). To relocate an extension, drop and recreate it. Deprecate relocatable=3Dtrue. Sub-options: 1a. Require that "earthdistance" and "cube" appear in the same schema by forcing an error[1] when they don't. 1b. Expand @DEPNAME_schema@ in extension SQL files. Use @cube_schema@ t= o refer to the right objects. 1c. Use plpgsql to query pg_extension.extnamespace, then EXECUTE a CREAT= E FUNCTION statement after substituting the right schema names. 2. Like (1), including all sub-options, but warn about the problem without deprecating relocatable=3Dtrue. Drop relocatable=3Dtrue from extensions= that have cause to do so: cube, earthdistance, pageinspect, pg_freespacemap, xml2. Do likewise for others as needed in the future. 3. Make "earthdistance" dynamically discover the location of "cube" during each function call. This entails rewriting earthdistance sql-language functions in C. (One could use plpgsql, but that would add a substantia= l performance loss and a runtime dependency.) 4. Re-implement the earthdistance sql functions in C, not calling "cube" functions at all. 5. Create copies in "earthdistance" of the "cube" functions it uses[2]. Th= is violates modularity. It makes \dx+ uglier. 6. Allow an extension to ship SQL commands for re-binding to schemas when i= t or a dependency relocates. This would allow relocatable=3Dtrue in exten= sions that refer to @extschema@. Include (1b) in this project. 7. Augment function system and LANGUAGE sql to offer the ability to parse a= t CREATE time, storing a Query tree like we do for views/rules. (This wou= ld be a complex feature.) (One can simulate this today with a rule[3], but= it defeats inline_function().) Overall, I lean toward (2b). It's a self-contained project that doesn't ug= lify contrib and that sets a reasonable example for non-core extensions. While (7) would solve this and other problems nicely, it's a poor back-patc= h candidate. I liked (1b) for awhile, but it would be overkill if we ever = get (7). Other ideas or preferences? Thanks, nm [1] SELECT 'cube and earthdistance must appear in the same schema', 1 / (count(DISTINCT extnamespace) =3D 1)::int FROM pg_extension WHERE extname IN ('cube', 'earthdistance'); [2] CREATE FUNCTION _earthdistance_cube(float8) RETURNS cube AS '$libdir/cube', 'cube_f8' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; [3] CREATE TABLE sum_scratch (addend0 int, addend1 int, sum int); CREATE VIEW sum_impl AS SELECT * FROM sum_scratch; CREATE RULE sum_calc AS ON INSERT TO sum_impl DO INSTEAD INSERT INTO sum_scratch VALUES (NEW.addend0, NEW.addend1, NEW.addend0 + NEW.addend1) RETURNING= *; CREATE FUNCTION sum(int, int) RETURNS int LANGUAGE sql AS $$INSERT INTO sum_impl VALUES ($1, $2) RETURNING sum$$; SELECT sum(4, 3); --_002_3a89b08b90ac4aef99bce42ba7a23c67EX13D20EUC002antamazonc_ Content-Type: application/octet-stream; name="v1-earthdistance-cube-fix.patch" Content-Description: v1-earthdistance-cube-fix.patch Content-Disposition: attachment; filename="v1-earthdistance-cube-fix.patch"; size=2810; creation-date="Tue, 05 Oct 2021 09:57:28 GMT"; modification-date="Tue, 05 Oct 2021 09:57:28 GMT" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL2NvbnRyaWIvY3ViZS9jdWJlLmNvbnRyb2wgYi9jb250cmliL2N1YmUvY3Vi ZS5jb250cm9sCmluZGV4IDUwNDI3ZWMxMTcuLjhhZjYzN2M5MDEgMTAwNjQ0Ci0tLSBhL2NvbnRy aWIvY3ViZS9jdWJlLmNvbnRyb2wKKysrIGIvY29udHJpYi9jdWJlL2N1YmUuY29udHJvbApAQCAt Miw1ICsyLDUgQEAKIGNvbW1lbnQgPSAnZGF0YSB0eXBlIGZvciBtdWx0aWRpbWVuc2lvbmFsIGN1 YmVzJwogZGVmYXVsdF92ZXJzaW9uID0gJzEuNScKIG1vZHVsZV9wYXRobmFtZSA9ICckbGliZGly L2N1YmUnCi1yZWxvY2F0YWJsZSA9IHRydWUKK3JlbG9jYXRhYmxlID0gZmFsc2UKIHRydXN0ZWQg PSB0cnVlCmRpZmYgLS1naXQgYS9jb250cmliL2VhcnRoZGlzdGFuY2UvTWFrZWZpbGUgYi9jb250 cmliL2VhcnRoZGlzdGFuY2UvTWFrZWZpbGUKaW5kZXggZjkzYjdhOTI1YS4uZmE2NDhiZTgzMSAx MDA2NDQKLS0tIGEvY29udHJpYi9lYXJ0aGRpc3RhbmNlL01ha2VmaWxlCisrKyBiL2NvbnRyaWIv ZWFydGhkaXN0YW5jZS9NYWtlZmlsZQpAQCAtMyw3ICszLDcgQEAKIE1PRFVMRVMgPSBlYXJ0aGRp c3RhbmNlCiAKIEVYVEVOU0lPTiA9IGVhcnRoZGlzdGFuY2UKLURBVEEgPSBlYXJ0aGRpc3RhbmNl LS0xLjEuc3FsIGVhcnRoZGlzdGFuY2UtLTEuMC0tMS4xLnNxbAorREFUQSA9IGVhcnRoZGlzdGFu Y2UtLTEuMS5zcWwgZWFydGhkaXN0YW5jZS0tMS4wLS0xLjEuc3FsIGVhcnRoZGlzdGFuY2UtLTEu MS0tMS4yLnNxbAogUEdGSUxFREVTQyA9ICJlYXJ0aGRpc3RhbmNlIC0gY2FsY3VsYXRlIGRpc3Rh bmNlcyBvbiB0aGUgc3VyZmFjZSBvZiB0aGUgRWFydGgiCiAKIFJFR1JFU1MgPSBlYXJ0aGRpc3Rh bmNlCmRpZmYgLS1naXQgYS9jb250cmliL2VhcnRoZGlzdGFuY2UvZWFydGhkaXN0YW5jZS0tMS4x LS0xLjIuc3FsIGIvY29udHJpYi9lYXJ0aGRpc3RhbmNlL2VhcnRoZGlzdGFuY2UtLTEuMS0tMS4y LnNxbApuZXcgZmlsZSBtb2RlIDEwMDY0NAppbmRleCAwMDAwMDAwMDAwLi42NGU0MzFiOGQ4Ci0t LSAvZGV2L251bGwKKysrIGIvY29udHJpYi9lYXJ0aGRpc3RhbmNlL2VhcnRoZGlzdGFuY2UtLTEu MS0tMS4yLnNxbApAQCAtMCwwICsxLDMwIEBACisvKiBjb250cmliL2VhcnRoZGlzdGFuY2UvZWFy dGhkaXN0YW5jZS0tMS4xLS0xLjIuc3FsICovCisKKy0tIGNvbXBsYWluIGlmIHNjcmlwdCBpcyBz b3VyY2VkIGluIHBzcWwsIHJhdGhlciB0aGFuIHZpYSBBTFRFUiBFWFRFTlNJT04KK1xlY2hvIFVz ZSAiQUxURVIgRVhURU5TSU9OIGVhcnRoZGlzdGFuY2UgVVBEQVRFIFRPICcxLjInIiB0byBsb2Fk IHRoaXMgZmlsZS4gXHF1aXQKKworRE8gTEFOR1VBR0UgcGxwZ3NxbAorJCQKK0RFQ0xBUkUKKwl2 X2N1YmVuc3BuYW1lIFRFWFQ7CitCRUdJTgorCVNFTEVDVCBuc3BuYW1lIElOVE8gdl9jdWJlbnNw bmFtZQorCUZST00gIHBnX2V4dGVuc2lvbiwgcGdfbmFtZXNwYWNlCisJV0hFUkUgcGdfZXh0ZW5z aW9uLmV4dG5hbWVzcGFjZSA9IHBnX25hbWVzcGFjZS5vaWQKKwlBTkQgZXh0bmFtZT0nY3ViZSc7 CisKKwlJRiB2X2N1YmVuc3BuYW1lICE9ICdAZXh0c2NoZW1hQCcgVEhFTgorCQlSQUlTRSBFWENF UFRJT04gJ2VhcnRoZGlzdGFuY2UgZXh0ZW5zaW9uIG11c3QgYmUgaW5zdGFsbGVkIGluIHRoZSBz YW1lIHNjaGVtYSBhcyB0aGUgY3ViZSBleHRlbnNpb24nOworCUVORCBJRjsKK0VORAorJCQ7CisK K0FMVEVSIEZVTkNUSU9OIGVhcnRoKCkgU0VUIFNFQVJDSF9QQVRIPUBleHRzY2hlbWFAOworQUxU RVIgRlVOQ1RJT04gc2VjX3RvX2djKGZsb2F0OCkgU0VUIFNFQVJDSF9QQVRIPUBleHRzY2hlbWFA OworQUxURVIgRlVOQ1RJT04gZ2NfdG9fc2VjKGZsb2F0OCkgU0VUIFNFQVJDSF9QQVRIPUBleHRz Y2hlbWFAOworQUxURVIgRlVOQ1RJT04gbGxfdG9fZWFydGgoZmxvYXQ4LCBmbG9hdDgpIFNFVCBT RUFSQ0hfUEFUSD1AZXh0c2NoZW1hQDsKK0FMVEVSIEZVTkNUSU9OIGxhdGl0dWRlKGVhcnRoKSBT RVQgU0VBUkNIX1BBVEg9QGV4dHNjaGVtYUA7CitBTFRFUiBGVU5DVElPTiBsb25naXR1ZGUoZWFy dGgpIFNFVCBTRUFSQ0hfUEFUSD1AZXh0c2NoZW1hQDsKK0FMVEVSIEZVTkNUSU9OIGVhcnRoX2Rp c3RhbmNlKGVhcnRoLCBlYXJ0aCkgU0VUIFNFQVJDSF9QQVRIPUBleHRzY2hlbWFAOworQUxURVIg RlVOQ1RJT04gZWFydGhfYm94KGVhcnRoLCBmbG9hdDgpIFNFVCBTRUFSQ0hfUEFUSD1AZXh0c2No ZW1hQDsKK0FMVEVSIEZVTkNUSU9OIGdlb19kaXN0YW5jZShwb2ludCwgcG9pbnQpIFNFVCBTRUFS Q0hfUEFUSD1AZXh0c2NoZW1hQDsKZGlmZiAtLWdpdCBhL2NvbnRyaWIvZWFydGhkaXN0YW5jZS9l YXJ0aGRpc3RhbmNlLmNvbnRyb2wgYi9jb250cmliL2VhcnRoZGlzdGFuY2UvZWFydGhkaXN0YW5j ZS5jb250cm9sCmluZGV4IDU4MTZkMjJjZGQuLjgzNGVjYTI5MDYgMTAwNjQ0Ci0tLSBhL2NvbnRy aWIvZWFydGhkaXN0YW5jZS9lYXJ0aGRpc3RhbmNlLmNvbnRyb2wKKysrIGIvY29udHJpYi9lYXJ0 aGRpc3RhbmNlL2VhcnRoZGlzdGFuY2UuY29udHJvbApAQCAtMSw2ICsxLDYgQEAKICMgZWFydGhk aXN0YW5jZSBleHRlbnNpb24KIGNvbW1lbnQgPSAnY2FsY3VsYXRlIGdyZWF0LWNpcmNsZSBkaXN0 YW5jZXMgb24gdGhlIHN1cmZhY2Ugb2YgdGhlIEVhcnRoJwotZGVmYXVsdF92ZXJzaW9uID0gJzEu MScKK2RlZmF1bHRfdmVyc2lvbiA9ICcxLjInCiBtb2R1bGVfcGF0aG5hbWUgPSAnJGxpYmRpci9l YXJ0aGRpc3RhbmNlJwotcmVsb2NhdGFibGUgPSB0cnVlCityZWxvY2F0YWJsZSA9IGZhbHNlCiBy ZXF1aXJlcyA9ICdjdWJlJwo= --_002_3a89b08b90ac4aef99bce42ba7a23c67EX13D20EUC002antamazonc_--