Обсуждение: Re: Is there a way around function search_path killing SQL function inlining? - and backup / restore issue
> Hmm. The meaning of funcs.inline depends on the search_path, not just during dump restoration but all the time. So anythinguses it under a different search_path setting than the normal one will have this kind of problem; not just > dump/restore. > I don't have a very good idea what to do about that. > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company I wasn't suggesting it was a restore only issue, but it's most felt when your data doesn't come back. It affects any extensionthat relies on another extension. Take for example, I have tiger geocoder which relies on fuzzystrmatch. I have no idea where someone installs fuzzystrmatchso I can't schema qualify those calls. I use that dependent function to use to build an index on tables. The indexes don't come back. What I was trying to suggest (side topic, forget about inline issue), Is the pg_dump should have a switch to allow users to tack on extra schemas So that the dump restore set search_path thing looks like: Set search_path=my_data_schema, pg_catalog, whatever_otehr_schemas_I_have_for_db People can choose to use that switch or not. So that way if people do have database search_paths, they normally run with,their data will come back. Am I missing something here in this suggestion? It's one of the most common complaints I hear about PostgreSQL in generaland the crazy things people do to get around the issue like doing plain text dumps and parsing the dump. Thanks, Regina
On 3/10/16 3:29 PM, Regina Obe wrote: > Take for example, I have tiger geocoder which relies on fuzzystrmatch. I have no idea where someone installs fuzzystrmatchso I can't schema qualify those calls. I use that dependent function to use to build an index on tables. This is something I've thought about as well, and I think the real problem is search_path just isn't the right way to handle this. I think there needs to be some way to definitively reference something that's part of an extension; a method that doesn't depend on whatever schema the extension happens to be installed in. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
>> On 3/10/16 3:29 PM, Regina Obe wrote: >> Take for example, I have tiger geocoder which relies on fuzzystrmatch. I have no idea where someone installs fuzzystrmatchso I can't schema qualify those calls. I use that dependent function to use to build an index on tables. > This is something I've thought about as well, and I think the real problem is search_path just isn't the right way to handlethis. I think there needs to be some way to definitively reference something that's part of an extension; a method > that doesn't depend on whatever schema the extension happens to be installed in. -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Datain Trouble? Get it in Treble! http://BlueTreble.com I like that idea a lot though that sounds like something that requires a lot more work. In the long run it would be goodthough especially since I expect more and more extensions will rely on each other. I have similar concerns with pgRouting which I am a member of dev team too, and pgRouting can't schema qualify any of thePostGIS calls because they have no idea where PostGIS is installed and the extension model as it stands doesn't have provisions for referencing dependent extension locations. That hasn't been a major issue yet since pgRoutingdoesn't build functions that wrap PostGIS for indexing etc. it is however more of a future concern and is a concernfor people who build materialized views using pgRouting functions since all of those use PostGIS heavily. There is even if we do that the case of people just building their own functions untop of other things. I guess that oneis not as much of a concern since they would generally know where their dependent functions are installed and can schemaqualify. Thanks, Regina