Re: Abscence of synonym

On 11/7/2016 1:39 PM, Rakesh Kumar wrote:
>
> I need some ideas here.
>
> Let us say we have a bunch of tables, called a,b,c,d. We will get a batch of data in files which need to be processed. At the end of processing, the tables will get a fresh set of data. The problem we would like to solve is to allow access to the tables while they are getting processed by the new batch. Obviously, during the processing the data will be in an inconsistent state, which the app should not be exposed to. At the end of processing, the app can see the new data.
>
> In RDBMS where synonyms are allowed we would do this:
>
> keep two set of tables a_1 and a_2
> A synonym a will either point to a_1 or a_2.
> applications will refer synonym a only, never a_1 or a_2.
> While synonym is referring to a_1, we can process a_2.
> At the end of processing change the synonym to point to a_2.
> next time, repeat it, just flip the tables from a_2 to a_1.
>
> How this can be done in PG 9.5.3?
>
> Thanks.
>

I do it with schemas. A live and a work schema. When batch processing
is finished live tables get removed and work gets renamed, in a single
transaction:

begin trans;

drop table live.junk1;
alter table work.junk1 set schema live;

drop table live.junk2;
alter table work.junk2 set schema live;

.. about 200 more tables ...

commit;

works like a champ.

-Andy