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(dot)albe(at)cybertec(dot)at
> <mailto:laurenz(dot)albe(at)cybertec(dot)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(at)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(dot)klaver(at)aklaver(dot)com