4 Replies Latest reply on Jan 3, 2018 1:36 PM by Marcel Prust

    Create reports with DB on a server in stand by mode

    Marcel Prust

      I have a report on Custom SQL that exploits data from a stand by server, which is a replica server of production DB.

      Actually, as far as I understand, with each session Tableau makes a temporary table on a server that I address to, e.g.

      UTC STATEMENT:  CREATE LOCAL TEMPORARY TABLE "#Tableau_182_2_Connect" (

              "COL" INTEGER

              ) ON COMMIT PRESERVE ROWS

       

      However, this replica shouldn't allow to create even temporary tables, how does this report work then?

       

      Besides, it seems to me that these temp tables are created somehow and after that I experience another problem with deleting these temporary tables:

       

      2017-12-20 07:43:50 UTC ERROR:  cannot execute SELECT INTO in a read-only transaction

      2017-12-20 07:43:50 UTC STATEMENT:  -- statement does not return rows

          SELECT *

          INTO TEMPORARY TABLE "#Tableau_182_1_Connect"

          FROM (SELECT 1 AS COL) AS CHECKTEMP

          LIMIT 1

      2017-12-20 07:43:51 UTC ERROR:  cannot execute DROP TABLE in a read-only transaction

      2017-12-20 07:43:51 UTC STATEMENT:  DROP TABLE "#Tableau_182_1_Connect"

      2017-12-20 07:43:51 UTC ERROR:  cannot execute CREATE TABLE in a read-only transaction

      2017-12-20 07:43:51 UTC STATEMENT:  CREATE LOCAL TEMPORARY TABLE "#Tableau_182_2_Connect" (

              "COL" INTEGER

              ) ON COMMIT PRESERVE ROWS

      2017-12-20 07:43:51 UTC ERROR:  cannot execute DROP TABLE in a read-only transaction

      2017-12-20 07:43:51 UTC STATEMENT:  DROP TABLE "#Tableau_182_2_Connect"

      2017-12-20 07:56:46 UTC ERROR:  cannot execute SELECT INTO in a read-only transaction

      2017-12-20 07:56:46 UTC STATEMENT:  -- statement does not return rows

          SELECT *

          INTO TEMPORARY TABLE "#Tableau_185_1_Connect"

          FROM (SELECT 1 AS COL) AS CHECKTEMP

          LIMIT 1

      2017-12-20 07:56:46 UTC ERROR:  cannot execute DROP TABLE in a read-only transaction

      2017-12-20 07:56:46 UTC STATEMENT:  DROP TABLE "#Tableau_185_1_Connect"

       

       

       

       

      So, 2 main questions:

      1) Can I make reports running on replica?

      2) Is it good practice?