search

streamingfast / substreams-sink-sql

Apache License 2.0
12 stars 15 forks source link

Composite primary key support #9

Closed let4be closed 1 year ago

let4be commented 1 year ago

Could be useful to support composite primary keys https://kb.objectrocket.com/postgresql/postgresql-composite-primary-keys-629

maoueh commented 1 year ago

What is needed to support it exactly, could you expand a bit?

let4be commented 1 year ago

Right now export from substream to db is -very- limited. As we see in https://github.com/streamingfast/substreams-database-change/blob/7cab5707e68393cbf970c0c75b4d9a0e459693f2/proto/substreams/sink/database/v1/database.proto#L27 every change gets compressed into a map[string]string and primary key is essentially a string as well...

Export structure should be expanded, to support flexible data types(same as done in EntityChange, https://github.com/streamingfast/substreams-entity-change) - or those 2 concepts should be somehow united. Then primary key should be turned into a repeated field, and sink's logic should be upgraded as well

I could see composite keys being useful(especially if sink properly supports bytea postgres data type). Now bytea support can be added by properly handling param bindings when doing a query - see https://github.com/streamingfast/substreams-sink-postgres/issues/8

gusinacio commented 1 year ago

Are there any plans for composite primary keys support?

maoueh commented 1 year ago

No one is actively working on it. It's something we would like to add, but it's not prioritize now.