* 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)?
ERROR 4568: Relation projection_name does not exist
+1
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.
Appears you defined projection as unsegmented which will replicate across all nodes.
+1Thanks 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.
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.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.
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.
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
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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
