Migration using raw tables doesn't trigger watched queries

[js2702]

Our migration only consist of a new table in the sync rules. We have created the raw table in the local db migration and we have calledSELECT powersync_trigger_resync(TRUE) manually after the app has opened, migrated and connected to PS. What we are seeing is that after using this statement the data is not available in the raw table until restarting the app.

If we do the trigger before connecting it seems to work. Is this expected?

[simolus3]

This sounds like a bug. When you say "after using this statement the data is not available in the raw table until restarting the app", does that just affect existing watched queries that don't update or are manual select statements outside of watched streams also affected by this? Basically I'm wondering if the client doesn't know about the raw table or if this just breaks watched queries (which would still be weird).

And the flow is for is this, correct?

1. Open PowerSync database.
2. Run migrations on opened database instance.
3. Call connect?

[davidmartos96]

@simolus3 After executing the statement we have opened the database with SQLite browser and the raw table was empty.
Our guess is that the data populates after the next connect after the Hot Restart. Maybe stream queries are not an issue, because data should be in the raw table to begin with.

The order of operations is as you say.
First call initialize() on the powersync instance, then call our migration logic (creates the new raw table. This particular raw table doesn't have any triggers as it's read only, in case it matters), and then we call connect

[simolus3]

Thanks! In SQLite browser, do you see any data in a ps_data__ or ps_untyped__ table that might indicate that the raw table was ignored by the sync client? Or is data just missing entirely?

[js2702]

@simolus3 Just checked and yes, the data is in the ps_untyped table. Before and after executing the resync function. What would be the correct process to migrate raw tables where the migration is only adding a new synced table. Do we need to manually insert data from the untyped into the raw table? Or is the resync function supposed to do that automatically?

[simolus3]

That might have been a false-alert, trigger_resync doesn't delete data from previous local tables. But at the next sync iteration, the sync client should respect raw tables and update only those.

Do we need to manually insert data from the untyped into the raw table?

I recommend you run DELETE FROM ps_untyped WHERE type = 'nameOfRawTable' in the migration (before or after the resync shouldn't matter). It doesn't copy that data manually, but there's still the oplog table with source data and the next completed sync (should happen quickly when triggering a resync) would copy from oplog to your raw table.

To actually figure out the problem causing raw tables to get ignored here, could you perhaps set a breakpoint here. The function should get called with op == 'start' initially, and it would be good to know whether the JSON payload object for that call contains the raw tables.

[js2702]

In the op == 'start' the payload contains the new raw table.

Maybe we can clarify this migration:

1. App is in version 1.0 with old schema and server has already applied the new sync rules with the new table. (PowerSync Flutter libs 0.4.14 and Powersync core 1.7.0)
2. App is updated to 2.0 with new PowerSync schema that contains the new raw table. Here the app runs a migration that creates the new table. (PowerSync Flutter libs 0.4.15+1 and Powersync core 1.8.0)
3. (EDIT) The data from this new table comes from a source that also populates another existing table. We have a sync rule that filters based on an archived criteria. And the new table contains the names of all the archived data simplified. Does this matter?

Questions:

1. I assume that in step one it's expected that the new table data appears in the ps_untyped table. Because the client was opened when the new sync rules were published.
2. In which step should we expect to see the data in the raw table? Should it be right after calling powersync_trigger_resync? Should it matter if it's called before or after connecting to the service? Should we expect to see the data in the raw table automatically? We don't see anything related to this in the docs, you have an example with a new column, but no new table.

[simolus3]

I assume that in step one it's expected that the new table data appears in the ps_untyped table. Because the client was opened when the new sync rules were published.

Correct.

In which step should we expect to see the data in the raw table?

Calling powersync_trigger_resync alone is not enough. But after you've called this and your app connects to the PowerSync service, the next completed sync checkpoint should copy all rows into the raw table.

Should it be right after calling powersync_trigger_resync? Should it matter if it's called before or after connecting to the service

You should call powersync_trigger_resync before connecting, the function will report an error if it's called while the database is connected. The resync trigger has an effect when you connect after calling it.

Should we expect to see the data in the raw table automatically?

After the next sync completes (isDownloading is no longer true / lastSyncedAt was updated), then the sync client should have written into raw tables at that point.