Re: Attaching database
Hi, you can only run inquires now on information_schema for the database you are connected to. If you are looking/searching in pg_database though, you can information globally. Something changed recently (I am using Dbeaver); I was able to attach to a single database and see all my databases; run queries, etc. With the latest update, now you have to have a separate connection to each database. Navicat now does the same thing, so my thinking is they must have changed the drivers. Regards, On 10/15/22 02:06, Julien Rouhaud wrote: Hi, On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote: Sorry for resurrecting this old thread... If an attaching the DB creates new connection which will be cmpletely independent - how the INFORMATION_SCHEMA.table@table_catalog field is handled. Lets say I open connection to the DB (finance) and then attached another DB (finance_2021). So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table I will get all tables from (finance) DB only. And to get all tables from (finance_2021) I need to make this catalog current and reissue the query. Am I right? No. In postgres, databases are completely disjoint containers and once you have a connection on a given database it will stay on that database, there's no way around that. Using postgres_fdw allows you to create a local table that will point to another table, possibly on another database or even another server, but it will still be a (foreign) table, that has to be created in the current database in the schema of your choice. Depending on your use case, maybe what you could do is create a finance_2021 schema, and create all the foreign tables you need in that schema pointing to the finance_2021 database. Any table existing in both "finance" and "finance_2021" will then be visible in information_schema.tables, with a different table_schema. If you have multiple schemas in each database, then find a way to make it work, maybe adding a _2021 suffix on each schema or something like that. You can then maybe use the search_path (see https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) to use by default one of the set of tables rather than the other. But in any case, it's only a workaround that has to be implemented on your client, as you will always be connected on the same database, and see both set of object in information_schema. -- Alex Theodossis a...@dossi.info 347-514-5420
Re: Attaching database
On 10/15/22 08:56, Igor Korot wrote: Hi, Adrian, So any and all operations/queries performed before, during or after that will be done on (finance) catalog), because this is the "main" connection, right? I think you are getting stuck on SQLite terminology: https://sqlite.org/lang_attach.html "Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. ..." That is different mechanism all together. The closest I can come with alternate example is linked tables in Access. You are working in one client connected to Server A that makes as needed connections to Server B to move data back and forth. You are not actually connected full time to Server B and your point of reference is the connection your client initially made to Server A. So yes because it is the "main" connection. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Attaching database
Hi, Adrian, On Sat, Oct 15, 2022, 10:51 AM Adrian Klaver wrote: > On 10/15/22 08:20, Adrian Klaver wrote: > > On 10/14/22 21:46, Igor Korot wrote: > > > > > >> Making catalog current means switching between DBs. > >> Remember initially I connected to (finance) DB, which made the > >> (finance) catalog > >> current. > >> Then I "opened a second connection" to (finance_2021), which made > >> that current catalog and so that select would give me all tables from > >> (finance_2021). > > > > No you didn't. > > > > Rough outline of what happens: > > > > 1) You connected to the database finance. > > 2) While in the finance database you queried the foreign tables that are > > linked to finance_2021. > > 3) The queries made connections the to finance_2021 for the purposes of > > making the data visible in the foreign tables in finance. > > 4) The client you did this did not 'leave' the finance database, so the > ^ > in > > only information_schema you have access to is in the finance database. > So any and all operations/queries performed before, during or after that will be done on (finance) catalog), because this is the "main" connection, right? Thank you. > > >> > >> I hope now its clearer. > >> > >> Thank you. > >> > >>> > >>> David J. > >> > >> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Attaching database
On 10/15/22 08:20, Adrian Klaver wrote: On 10/14/22 21:46, Igor Korot wrote: Making catalog current means switching between DBs. Remember initially I connected to (finance) DB, which made the (finance) catalog current. Then I "opened a second connection" to (finance_2021), which made that current catalog and so that select would give me all tables from (finance_2021). No you didn't. Rough outline of what happens: 1) You connected to the database finance. 2) While in the finance database you queried the foreign tables that are linked to finance_2021. 3) The queries made connections the to finance_2021 for the purposes of making the data visible in the foreign tables in finance. 4) The client you did this did not 'leave' the finance database, so the ^ in only information_schema you have access to is in the finance database. I hope now its clearer. Thank you. David J. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Attaching database
On 10/14/22 21:46, Igor Korot wrote: Making catalog current means switching between DBs. Remember initially I connected to (finance) DB, which made the (finance) catalog current. Then I "opened a second connection" to (finance_2021), which made that current catalog and so that select would give me all tables from (finance_2021). No you didn't. Rough outline of what happens: 1) You connected to the database finance. 2) While in the finance database you queried the foreign tables that are linked to finance_2021. 3) The queries made connections the to finance_2021 for the purposes of making the data visible in the foreign tables in finance. 4) The client you did this did not 'leave' the finance database, so the only information_schema you have access to is in the finance database. I hope now its clearer. Thank you. David J. -- Adrian Klaver adrian.kla...@aklaver.com
