ERROR 4568: Relation projection_name does not exist

Eqtconsulting

* First, I create a projection:

create projection scheme.projection_name (
       cp1,
       cp2,
       cp3,
       cp4
       ) as
       select 
         a.c1,
         a.c2,
         b.d1,
         b.d2,
       from scheme.t1 as a join scheme.t2 as b
       on a.c3=b.d1
       and a.c4=b.d2
    ksafe 1;

* The next step, I execute:
select refresh();
                                                                                                                                                                                                                          refresh                                                                                                                                                                                                                          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"scheme"."projection_name_node0002": [t1] [refreshed] [scratch] [0] [1]
"scheme"."projection_name_node0003": [t1] [refreshed] [scratch] [0] [1]
"scheme"."projection_name_node0001": [t1] [refreshed] [scratch] [0] [1]

(1 row)


* And finally, when I try to do a query about this projection, get the error:

ABTLC=> select * from scheme.projection_name;ERROR 4568:  Relation "scheme.projection_name" does not exist
ABTLC=> 

* But, if I put de projection_name with the node, then, I get the result:

ABTLC=> select * from scheme.projection_name_node0001; 
cp1   |  cp2                 | cp3         | cp4 |
-------+---------------+--------------+------------------
    10 | desc dato dos |            1 | uno             
    10 | desc dato uno |            1 | uno             
(2 rows)

* How can I do the query with the projection_name (without the node)?

wwong2

Hi, if you issue command \dj, you will see list of all your projections and see that it does not exist.  Looks like you will see projection_name_node0001, projection_name_node0002, projection_name_node0003 from the output yo show.
Appears you defined projection as unsegmented which will replicate across all nodes. 
 

Eqtconsulting

Thanks Wayne, but I tried doing a segmented PROJECTION and now the error is another:

create projection scheme.projection_name (
       cp1,
       cp2,
       cp3,
       cp4
       ) as
       select 
         a.c1,
         a.c2,
         b.d1,
         b.d2,
       from scheme.t1 as a join scheme.t2 as b
       on a.c3=b.d1
       and a.c4=b.d2
segmented by hash (b.d1,b.d2) all nodes;

Code: 4468 SQL State: 01000 --- Projection <schema.projectio_name> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.

          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh

... 4 statement(s) executed, 0 row(s) affected, exec/fetch time: 1.590/0.000 sec  [0 successful, 4 warnings, 0 errors]

ABTLC=> select refresh();

                                                                                                                                                    refresh                                                                                                                                                    

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]

----------------------------------------------------------------------------------------

"schema"."projection_name": [t1] [failed: projection is unsafe] [] [1] [0]

 

(1 row)

 

ABTLC=> \dj projection_name*

                          List of projections

   Schema   |        Name        |  Owner  |       Node       | Comment

------------+--------------------+---------+------------------+---------

schema | projection_name         | dbadmin |                  |

 ( rows)

 

ABTLC=> select * from schema.projection_name;

ERROR 3586:  Insufficient projections to answer query

DETAIL:  No projections eligible to answer query

HINT:  Projection projection_name not used in the plan because the projection is not safe.

Prasanta_Pal

Do from the scratch as follows:

Drop table t1 cascade;
Create table t1 (....);
Create projection ...

..
segmented by hash (b.d1,b.d2) all nodes ksafe;

it will create two projections <projection_name>_b0 and <projection_name>_b1

Now insert data
Do the the query on the table t1;

Your issue:
1) It created 3 unsegmented projections, so if you query using the projection name then if you have to use the proper projection name which was ..._node001, ..._node002, etc
2) You did not mention the ksafe in the create projection statement. so it creates only one projection, so it would allow to load the data and also would not allow to query on the table if your database was k-safe 1

Hope this will clear your projection creation understanding. Let me know if you need any more help.

Prasanta_Pal

Correction:
so it would allow to load the data and also would not allow to query on the table if your database was k-safe 1

to
so it would NOT allow to load the data and also would NOT allow to query on the table if your database was k-safe 1