Re: pg_temp schema created while using DB Link

On 5/8/20 12:32 AM, Jill Jade wrote:
> Hello
> 
> We do not have any temporary tables in the Oracle database. The 
> temporary schema is created only while using the db_link. These temp 
> schemas (pg_temp, pg_toast) are not disappearing even after restarting 
> the database.
> 
> These are empty schemas that do not contain anything (tables, views 
> etc). And these schemas keep on increasing each time the query using the 
> db_link is run.

Postgres will keep empty pg_temp schemas around rather then having to 
recreate them the next time they are needed. Not sure where the cutoff 
is for number?

> 
> Why the db_link is causing these schemas? Is there any issue with db_link?
> 
> Thanks
> 
> Regards,
> Jill
> 
> 
> On Fri, 8 May 2020 at 10:36, Laurenz Albe <laurenz.albe@cybertec.at 
> <mailto:laurenz.albe@cybertec.at>> wrote:
> 
>     On Fri, 2020-05-08 at 10:11 +0400, Jill Jade wrote:
>      > We have an issue with DB_Link from Oracle to PostgreSQL. When we
>     try to access tables from
>      > Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are
>     automatically created
>      > in the database while using the query below.
>      >
>      > create table as select * from table@oralink
>      >
>      > Even if we reload the database, the temp schemas are still there.
>      >
>      > Do you know the reason why the temp schemas are being created
>     with DB_link?
>      > Does anyone has a solution for this problem?
> 
>     These schemas contain temporary tables and are not connected to your
>     connection
>     from Oracle (unless you create temporary tables via that connection).
> 
>     They are an implementation detail and should not bother you.
> 
>     What is your problem with these schemmas?
> 
>     Yours,
>     Laurenz Albe
>     -- 
>     Cybertec | https://www.cybertec-postgresql.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com