Re: BUG #6266: Create temp tables on Slave

The following bug has been logged online:

Bug reference: 6266
Logged by: Sally
Email address: sally(dot)nayer(at)tedata(dot)net
PostgreSQL version: 9.1
Operating system: Centos 5.5
Description: Create temp tables on Slave
Details:

We are Using replica (wal streaming replica)to replicate between Master and
slave.

We need to be able to create temp tables on Slave,

Is there any workaround?
How could we create temp database and tables on slave?

Thanks and Regards,
Sally

On Mon, Oct 24, 2011 at 8:48 AM, Sally <sally(dot)nayer(at)tedata(dot)net> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6266
> Logged by:          Sally
> Email address:      sally(dot)nayer(at)tedata(dot)net
> PostgreSQL version: 9.1
> Operating system:   Centos 5.5
> Description:        Create temp tables on Slave
> Details:
>
> We are Using replica (wal streaming replica)to replicate between Master and
> slave.
>
> We need to be able to create temp tables on Slave,
>

you can't. this isn't a bug but a known limitation and is documented here:
http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-USERS

> Is there any workaround?
> How could we create temp database and tables on slave?
>

not with streaming replication

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

Dear Jaime,

Is there commercial solution that uses the same concept of wal streaming and allows the Creation of temp tables on slave

I really appreciate your help

Thanks and Regards,

Sally Nayer
IT Systems Senior Engineer
TE Data
TE Data, Building A11- B90, Smart Village
Cairo- Alex Desert Road, 28 Km
6th of October 12577, Egypt
T:  2 02 33 32 0733 |
F: 2 -02 33 32 0815| E: 2543
Visit: www.tedata.net
                                               

-----Original Message-----
From: jcasanov(at)systemguards(dot)com(dot)ec [mailto:jcasanov(at)systemguards(dot)com(dot)ec] On Behalf Of Jaime Casanova
Sent: Tuesday, October 25, 2011 5:51 AM
To: Sally Nayer
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6266: Create temp tables on Slave

On Mon, Oct 24, 2011 at 8:48 AM, Sally <sally(dot)nayer(at)tedata(dot)net> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6266
> Logged by:          Sally
> Email address:      sally(dot)nayer(at)tedata(dot)net
> PostgreSQL version: 9.1
> Operating system:   Centos 5.5
> Description:        Create temp tables on Slave
> Details:
>
> We are Using replica (wal streaming replica)to replicate between Master and
> slave.
>
> We need to be able to create temp tables on Slave,
>

you can't. this isn't a bug but a known limitation and is documented here:
http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-USERS

> Is there any workaround?
> How could we create temp database and tables on slave?
>

not with streaming replication

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

On 10/25/11 12:11 AM, Sally Nayer wrote:
> Is there commercial solution that uses the same concept of wal streaming and allows the Creation of temp tables on slave

WAL files represent 'delta changes' between the state of the postgres
file system. *anything* that changes database files on the slave side
would render the WAL files inherently unusable.

to do what you want would require using a fundamentally different
replication method, such as Slony or Bucardo.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Well, it is imaginable for there to be some side mechanism to enable use of
temporary tables on a WAL-based replica, after all, we have leapt through
enough hoops to get read transactions working.

That being said...

A) a solution for how to do this has not yet emerged, so it seems not too
likely that such a feature will enter 9.2, let alone earlier versions. It's
not yet really scheduled for 9.2, so it would be wiser to assume 9.3 or
later, ergo don't make any plans earlier than 2013...

B) yes, indeed, "logical" replication systems like Slony, Londiste, and
Bucardo are the alternative at present.

I have been hearing comments to the effect of these systems being passé -
when WAL replication does not support fairly important cases such as
allowing reports to use temporary tables, that seems rather premature.
On Oct 25, 2011 5:10 AM, "John R Pierce" <pierce(at)hogranch(dot)com> wrote:

> On 10/25/11 12:11 AM, Sally Nayer wrote:
>
>> Is there commercial solution that uses the same concept of wal streaming
>> and allows the Creation of temp tables on slave
>>
>
> WAL files represent 'delta changes' between the state of the postgres file
> system. *anything* that changes database files on the slave side would
> render the WAL files inherently unusable.
>
> to do what you want would require using a fundamentally different
> replication method, such as Slony or Bucardo.
>
>
>
> --
> john r pierce N 37, W 122
> santa cruz ca mid-left coast
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-bugs<http://www.postgresql.org/mailpref/pgsql-bugs>
>

> you can't. this isn't a bug but a known limitation and is documented here:
> http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-USERS
>
>> Is there any workaround?
>> How could we create temp database and tables on slave?
>>
>
> not with streaming replication
>
> --
> Jaime Casanova         www.2ndQuadrant.com
> Professional PostgreSQL: Soporte 24x7 y capacitación
>

I wonder if a workaround is possible using dblink, pl/proxy, or FDW to
write out to a different "temp" db?