Обсуждение: Attaching database

Hi,
IIUC, PostgreSQL does not support attaching a database to an existing
connection.
However I was told that I can use this:
https://www.postgresql.org/docs/9.3/postgres-fdw.html.

Is it the same thing? Why there is no simple

ATTACH <db> AS <dbname>

?

Thank you.

Hi, Pavel,

On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> po 19. 10. 2020 v 19:40 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
>>
>> Hi,
>> IIUC, PostgreSQL does not support attaching a database to an existing
>> connection.
>> However I was told that I can use this:
>> https://www.postgresql.org/docs/9.3/postgres-fdw.html.
>>
>> Is it the same thing? Why there is no simple
>>
>> ATTACH <db> AS <dbname>
>>
>
> It is a different thing - postgresql_fdw does nested connect - it uses client-server protocol.
>
> For postgres connect and sql engine process is one entity - and engine is written without a possibility to reconnect
toanother database. 

So if I understand correctly the postgresql_fdw is creating a second
connection and uses it as kind of "virtualizing mechanism"
in order to give access to the second database.

Am I right?

Thank you.

>
> Regards
>
> Pavel
>
>
>
>>
>> ?
>>
>> Thank you.
>>
>>

On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> po 19. 10. 2020 v 20:18 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
>     On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com>
>     wrote:
>     > It is a different thing - postgresql_fdw does nested connect - it uses
>     > client-server protocol.
>     >
>     > For postgres connect and sql engine process is one entity - and engine is
>     > written without a possibility to reconnect to another database.
>
>     So if I understand correctly the postgresql_fdw is creating a second
>     connection and uses it as kind of "virtualizing mechanism"
>     in order to give access to the second database.
>
>
> yes - it does new connect to somewhere (local or remote server, but mechanism
> is absolutely same)

In case this isn't clear:

It is the server (or more specifically, the foreign data wrapper) which
opens that connection. To the client it looks like it's just accessing a
normal table within the same database.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Hi, guys,

On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> > po 19. 10. 2020 v 20:18 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
> >     On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com>
> >     wrote:
> >     > It is a different thing - postgresql_fdw does nested connect - it uses
> >     > client-server protocol.
> >     >
> >     > For postgres connect and sql engine process is one entity - and engine is
> >     > written without a possibility to reconnect to another database.
> >
> >     So if I understand correctly the postgresql_fdw is creating a second
> >     connection and uses it as kind of "virtualizing mechanism"
> >     in order to give access to the second database.
> >
> >
> > yes - it does new connect to somewhere (local or remote server, but mechanism
> > is absolutely same)
>
> In case this isn't clear:
>
> It is the server (or more specifically, the foreign data wrapper) which
> opens that connection. To the client it looks like it's just accessing a
> normal table within the same database.

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?

Thank you.

>
>         hp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"

Hi, David et al,

On Fri, Oct 14, 2022 at 11:39 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Fri, Oct 14, 2022 at 9:17 PM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, guys,
>>
>> On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>> >
>> > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
>>
>> > In case this isn't clear:
>> >
>> > It is the server (or more specifically, the foreign data wrapper) which
>> > opens that connection. To the client it looks like it's just accessing a
>> > normal table within the same database.
>>
>> Sorry for resurrecting this old thread...
>
>
> Then why did you do it?  You couldn't send a new email without copying possibly no longer interested people and with
betterthought out self-contained content that simply notes you are somehow using an FDW.
 
>
>>
>> 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
>
>
> Call this how exactly? There are three information_schema instances that you can be talking about, though only the
onein the local database is going to be called that.  If you are dealing with FDWs you would have to have different
namesinvolved.
 
>
>>
>> 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?
>
>
> Do it and find out?  Then if still confused, show what you attempted.  But I don't know what this concept of "make
thecatalog current" you speak of comes from.  That isn't a thing that I am aware of.  Where do you see this
documented?

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).

I hope now its clearer.

Thank you.

>
> David J.

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.

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.klaver@aklaver.com

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.klaver@aklaver.com

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.klaver@aklaver.com

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
alex@dossi.info
347-514-5420

Hi, guys,
After reading the documentation on
https://www.postgresql.org/docs/current/postgres-fdw.html
and checking the example I have a different question.

The presentation in the link referenced doesn't explain how to get the
table list on the
remote server and the information on the specific table.

The example tals about connection and creating a linked table, but it
doesn't explain
how to know the information about the tables on the remote

Is it possible or I will have to know this beforehand?

(Sorry for the top-posting).

Thank you.


On Sat, Oct 15, 2022 at 5:57 PM Alex Theodossis <alex@dossi.info> wrote:
>
> 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
> alex@dossi.info
> 347-514-5420
>
>
>

Hi,

On Tue, Oct 18, 2022 at 10:06:40PM -0500, Igor Korot wrote:
> Hi, guys,
> After reading the documentation on
> https://www.postgresql.org/docs/current/postgres-fdw.html
> and checking the example I have a different question.
>
> The presentation in the link referenced doesn't explain how to get the
> table list on the
> remote server and the information on the specific table.
>
> The example tals about connection and creating a linked table, but it
> doesn't explain
> how to know the information about the tables on the remote
>
> Is it possible or I will have to know this beforehand?

In general it's up to you to make sure that the remote table definition matches
the local one.  You can use IMPORT FOREIGN SCHEMA (1) to automate this process,
but you will still have to make sure that any subsequent modification on the
remote table (added/removed column, changed datatype...) is also done on the
local foreign table.

> (Sorry for the top-posting).

This isn't really related to the previous discussion so it's quite ok, although
it's usually a good practice to trim the unwanted parts of the previous
message (in this case the whole previous message).

[1] https://www.postgresql.org/docs/current/sql-importforeignschema.html