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 anything uses 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 extension that relies on another extension.
Take for example, I have tiger geocoder which relies on fuzzystrmatch. I have no idea where someone installs fuzzystrmatch so 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 general and the crazy things people do to get around the issue like doing plain text dumps and parsing the dump.
Thanks,
Regina
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 fuzzystrmatch so 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 fuzzystrmatch so 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
I like that idea a lot though that sounds like something that requires a lot more work. In the long run it would be good though 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 the PostGIS 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 pgRouting doesn't build functions that wrap PostGIS for indexing etc. it is however more of a future concern and is a concern for 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 one is not as much of a concern since they would generally know where their dependent functions are installed and can schema qualify.
Thanks,
Regina
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers