View without primary id
SkymaxPlay opened this issue · 2 comments
PGSync version: 3.1.0
Postgres version: 12
Elasticsearch/OpenSearch version: 8.13.2
Redis version: 7.2.4
Python version: 3.10
Problem Description:
I created schem with table and view, which sum data group by foreign key (primary key of table mentioned before). The problem is, that when I edit table, everythings updates, but if I edit table that the view is based ofn nothing updates. I found example with views in project. After adding base_tables
I can see that the trigger has sent data but I have an error. I think the problem is I dont have id in view, but in this case I cant add id.
Schem:
[
{
"database": "manager",
"index": "testview",
"nodes": {
"table": "producttest",
"primary_key": ["id"],
"columns": [
"id",
"a"
],
"transform": {
"mapping": {
"id": {
"type": "keyword"
}
}
},
"children": [
{
"table": "reservation_view",
"base_tables": ["reservation"],
"label": "reservation",
"primary_key": ["user_id"],
"columns": [
"user_id",
"amount_reserved"
],
"relationship": {
"type": "one_to_one",
"variant": "object",
"foreign_key": {
"parent": ["id"],
"child": ["user_id"]
}
}
}
]
}
}
]
View:
create view reservation_view(user_id, amount_reserved) as
SELECT reservation.user_id,
sum(reservation.amount) AS amount_reserved
FROM reservation
GROUP BY reservation.user_id;
Table reservation:
id uuid default uuid_generate_v4() not null
primary key,
user_id uuid not null,
amount integer not null,
Any ideas?
Error Message (if any):
2024-05-07 16:37:41.981:ERROR:pgsync.sync: Primary keys ['user_id'] not subset of payload data dict_keys(['id']) for table public.reservation_view
NoneType: None
- I think its desirable to have a primary id field in the view.
- I could not figure out an elegant way to handle views without a primary.
- Perhaps this should be documented somewhere.
- Does it work as expected when you add a primary key to the view?
@toluaina Great work here could you please add documentation for using views