Обсуждение: [GENERAL] Change location of function/type installed from C-extension
Hello. I have database with installed pg_trgm extension with module path '/usr/lib/pg_trgm' (yes, this is mistake without $libdir (: ). Now I want upgrade postgresql to new major version. I keep old version 9.3 in /opt/postgresql/9.3 and new version 9.6 in /usr. Old version $libdir - /opt/postgresql/9.3/lib, new version $libdir - /usr/lib/postgresql. So now I am in trap because old version try to load shared libarry from '/usr/lib/pg_trgm'. So I want change '/usr/lib/pg_trgm' to '$libdir/pg_trgm' for old database. And I have only one ugly solution: replace '/usr/lib/pg_trgm' with '$libdir//pg_trgm' in table files (/usr/lib - 8 chars, $libdir/ - 8 chars). May be someone has better solution? Thanks.
I try investigate where PotsgreSQL keeps path of load libraries in catalog. select version(); version ---------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit select oid, datname from pg_database where datname = 'user'; oid | datname -------+--------- 16384 | user $ grep --text --null-data '$libdir/pg_trgm' data/test/base/16384/* data/test/base/16384/11829:set_limit!$libdir/pg_trgm ... select relname, relfilenode from pg_class where relfilenode = 11829; relname | relfilenode ---------+------------- (0 rows) Hmmm. Where is table with filenode 11829? Thanks.
On 26.08.2017 15:10, Dmitry Lazurkin wrote: > I try investigate where PotsgreSQL keeps path of load libraries in catalog. > > select version(); > version > ---------------------------------------------------------------------------------------------------------------------- > PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu > 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit > > > select oid, datname from pg_database where datname = 'user'; > > oid | datname > -------+--------- > 16384 | user > > > $ grep --text --null-data '$libdir/pg_trgm' data/test/base/16384/* > > data/test/base/16384/11829:set_limit!$libdir/pg_trgm ... > > > select relname, relfilenode from pg_class where relfilenode = 11829; > relname | relfilenode > ---------+------------- > (0 rows) > > > Hmmm. Where is table with filenode 11829? > > Thanks. > This is pg_proc. inst/test/bin/oid2name -d user -f 11829 From database "user": Filenode Table Name ---------------------- 11829 pg_proc
Dmitry Lazurkin <dilaz03@gmail.com> writes: > select relname, relfilenode from pg_class where relfilenode = 11829; > relname | relfilenode > ---------+------------- > (0 rows) > Hmmm. Where is table with filenode 11829? pg_class.relfilenode doesn't contain useful data for pg_proc and some other critical system catalogs: regression=# select relname, relfilenode from pg_class where relname like 'pg_proc%'; relname | relfilenode --------------------------------+------------- pg_proc_oid_index | 0 pg_proc | 0 pg_proc_proname_args_nsp_index | 0 (3 rows) You need to use pg_relation_filenode(): regression=# select relname, pg_relation_filenode(oid) from pg_class where relname like 'pg_proc%'; relname | pg_relation_filenode --------------------------------+---------------------- pg_proc_oid_index | 12662 pg_proc | 12657 pg_proc_proname_args_nsp_index | 12663 (3 rows) regards, tom lane
On 26.08.2017 18:24, Tom Lane wrote: > You need to use pg_relation_filenode(): > > regression=# select relname, pg_relation_filenode(oid) from pg_class where relname like 'pg_proc%'; > relname | pg_relation_filenode > --------------------------------+---------------------- > pg_proc_oid_index | 12662 > pg_proc | 12657 > pg_proc_proname_args_nsp_index | 12663 > (3 rows) > > regards, tom lane Thanks. Can I update "pg_proc.probin" without any problems?
Dmitry Lazurkin <dilaz03@gmail.com> writes: > Thanks. Can I update "pg_proc.probin" without any problems? Should work. I'd experiment in a scratch database before doing it in production, but I can't think of a problem offhand. regards, tom lane
On 26.08.2017 22:05, Tom Lane wrote: > Dmitry Lazurkin <dilaz03@gmail.com> writes: >> Thanks. Can I update "pg_proc.probin" without any problems? > Should work. I'd experiment in a scratch database before doing > it in production, but I can't think of a problem offhand. > > regards, tom lane Thank you. That's working. I will try to implement in production. Update with sql is better than replace in binary file (: .