Обсуждение: [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 (: .