Re: Vacuum and state_change

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 06/09/2017 02:26 PM, armand pirvu wrote:
>>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>> On 06/09/2017 02:01 PM, armand pirvu wrote:
>>>>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>>>
>>>>> On 06/09/2017 01:31 PM, armand pirvu wrote:
>>>>>
>>>>>>>
>>>
>>>
>>>> By temporary tables I mean just regular table not tables created by "create temporary table" . I should have been more precise. We call them temporary since we do drop them after all is said and done. Maybe we should change the way we call them
>>>
>>> You will want to look at this before making that decision:
>>>
>>> https://www.postgresql.org/docs/9.6/static/sql-createtable.html
>>>
>>> Temporary Tables
>>>
>>>
>>> Basically, temporary tables are session specific.
>>>
>> I noticed that, but since we use multiple schemas can not have a session temp table in non temp schema
>
> A true temporary table is going to be in its own temporary schema:
>
> https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-SEARCH-PATH <https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-SEARCH-PATH>
>
> "Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first (even before pg_catalog). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for function or operator names.”

The search_path I am aware of it. But it is more than that in our case hence we decided to have several schemas with quote/unquote or what we call temp tables or we can recall them as staging tables

Their purpose is to help with the transition from old system to new (in some way anyways)

>
>> We have those in place for a specific reason in case we mess some processing in between and we want to be able to have the data which we started with.
>>>
>
>> As of now I don’t think we have a draw back per se. We are poised to go live on Postgres soon though so I was thinking maybe have this upgrade done before going live ? Just a thought
>
> Yeah well if you are in pre-production why not, if no other reason then you get another year of community support on the back end.
>

My sentiments exactly

>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>