CREATE SERVER makes 2 entries in the dictionary

Hi,

Le mar. 1 juin 2021 à 14:47, Godfrin, Philippe E <philippe.godfrin@nov.com>
a écrit :

I’ve connected to a test database, executed the create server syntax
successfully. While connected to the test database, I see results in the
pg_foreign_server table. Changing to the postgres database, I see the same
results in the postgres database, but with a different OID. Furthermore,
alterations can be made independently to each server based on which db one
is connected to.

Seems to me this is a bug:

Pg

postgres@tsthpx01:~/work/tsdb=> psql -dtsdb

Pager usage is off.

psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))

Type "help" for help.

[tsdb@] # select * from pg_foreign_server;

oid | srvname | srvowner | srvfdw | srvtype | srvversion
| srvacl
|
srvoptions

-------+---------+----------+--------+---------+------------+---------------------------------------------------------+--------------------------------------------------------------------------------------------------------------

16586 | shard01 | 10 | 16391 | (null) | (null) |
{postgres=U/postgres,tsdb=U/postgres,tsdbrw=U/postgres} |
{dbname=tsdb,use_remote_estimate=true,fetch_size=100000,"port=5432,6432","host=172.31.68.147,172.31.68.147"}

(1 row)

Time: 0.812 ms

[tsdb@] # \c postgres

You are now connected to database "postgres" as user "postgres".

[postgres@] # select * from pg_foreign_server;

oid | srvname | srvowner | srvfdw | srvtype | srvversion
| srvacl
|
srvoptions

-------+---------+----------+--------+---------+------------+---------------------------------------------------------+----------------------------------------------------------------------------------------------------------------

16521 | shard01 | 10 | 16399 | (null) | (null) |
{postgres=U/postgres,tsdb=U/postgres,tsdbrw=U/postgres} |
{host=172.31.68.147,dbname=tsdb,use_remote_estimate=true,fetch_size=100000,target_session_attrs=any,port=6432}

(1 row)

Time: 0.702 ms

[postgres@] # select version();

version

---------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

(1 row)

Time: 0.400 ms

[postgres@] #

Foreign servers aren't global objects, so it sounds right that they get
assigned different OIDs on different databases, and could be altered
independently. <Philippe.Godfrin@nov.com>

--
Guillaume.

OK, however, I believe I was connected to the test database, with no server defined on the postgres database, but the test copy was progagated from test to postgres.

I’ll double check that again.

pg

From: Guillaume Lelarge <guillaume@lelarge.info>
Sent: Tuesday, June 1, 2021 8:24 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
Cc: pgsql-bugs@lists.postgresql.org; Sangunni, Sudheer T <Sudheer.Sangunni@nov.com>
Subject: [EXTERNAL] Re: CREATE SERVER makes 2 entries in the dictionary

Use caution when interacting with this [EXTERNAL] email!

Hi,

Le mar. 1 juin 2021 à 14:47, Godfrin, Philippe E <philippe.godfrin@nov.com> a écrit :

I’ve connected to a test database, executed the create server syntax successfully. While connected to the test database, I see results in the pg_foreign_server table. Changing to the postgres database, I see the same results in the postgres database, but with a different OID. Furthermore, alterations can be made independently to each server based on which db one is connected to.

Seems to me this is a bug:

Pg

postgres@tsthpx01:~/work/tsdb=> psql -dtsdb

Pager usage is off.

psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))

Type "help" for help.

[tsdb@] # select * from pg_foreign_server;

  oid  | srvname | srvowner | srvfdw | srvtype | srvversion |                         srvacl                          |                                                  srvoptions                                                 

-------+---------+----------+--------+---------+------------+---------------------------------------------------------+--------------------------------------------------------------------------------------------------------------

16586 | shard01 |       10 |  16391 | (null)  | (null)     | {postgres=U/postgres,tsdb=U/postgres,tsdbrw=U/postgres} | {dbname=tsdb,use_remote_estimate=true,fetch_size=100000,"port=5432,6432","host=172.31.68.147,172.31.68.147"}

(1 row)

Time: 0.812 ms

[tsdb@] # \c postgres

You are now connected to database "postgres" as user "postgres".

[postgres@] # select * from pg_foreign_server;

  oid  | srvname | srvowner | srvfdw | srvtype | srvversion |                         srvacl                          |                                                   srvoptions                                                  

-------+---------+----------+--------+---------+------------+---------------------------------------------------------+----------------------------------------------------------------------------------------------------------------

16521 | shard01 |       10 |  16399 | (null)  | (null)     | {postgres=U/postgres,tsdb=U/postgres,tsdbrw=U/postgres} | {host=172.31.68.147,dbname=tsdb,use_remote_estimate=true,fetch_size=100000,target_session_attrs=any,port=6432}

(1 row)

Time: 0.702 ms

[postgres@] # select version();

                                                             version                                                            

---------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

(1 row)

Time: 0.400 ms

[postgres@] #

Foreign servers aren't global objects, so it sounds right that they get assigned different OIDs on different databases, and could be altered independently.

--

Guillaume.