So what I'm proposing, is to have a schema belonging to the powa user, and create temp tables with the exact same name as those currently user (data_source_name_src_tmp) in those schemas, so we can use them to overload the table names with the temporary ones transparently.

Those temporary tables would be created using create temp table data_source_name_src_tmp like (app_schema.data_source_name_src_tmp) or something similar

I'm a bit confused by this part. You can't create a temporary table with a schema except pg_temp, but you example also doesn't try to do that and uses the schema for the existing unlogged tables (which should be in the sae schema as powa).

For a later iteration of powa-archivist, we could replace this delete with a truncate: the table will never be shared by several servers anymore

If you want to avoid sharing temp table with multiple remote serers, you will need to create multiple temp tables so you need to have the server id part of its name or something like that, and therefore make the query that reads from it dynamic.

The best solution I could come up with is to do something like

CREATE TEMP TABLE powa_XXX_src_tmp_$srvid (CHECK (srvid = $srvid) INHERITS (powa_XXX_src_tmp);

This way it would not require any change and will be transparent on the powa archivist side. If you have an old collector you just do the same as before, and if you have the new collector you will still get the correct value but hopefully better performance provided that constraint_exclusion is enabled.

We can still do the delete as before, but transform it to a DELETE FROM ONLY ..., which will be a no-op if you have the new collector (and otherwise will cleanup the data with the old collector), and the new collector can be responsible for the TRUNCATE. If you happen to have the old powa-archivist, then the DELETE will not be a no-op and will be a bit slower, but as least it won't bloat as before.

So what I'm proposing, is to have a schema belonging to the powa user, and create temp tables with the exact same name as those currently user (data_source_name_src_tmp) in those schemas, so we can use them to overload the table names with the temporary ones transparently.
Those temporary tables would be created using create temp table data_source_name_src_tmp like (app_schema.data_source_name_src_tmp) or something similar

I'm a bit confused by this part. You can't create a temporary table with a schema except pg_temp, but you example also doesn't try to do that and uses the schema for the existing unlogged tables (which should be in the sae schema as powa).
Yeah so

• 1 error from my part. You can't specify a schema, but you don't need anyway: if you create a temp table with the same name as a regular table, as it's earlier in the search path it will be used instead of the regular table
• yeah we need to find a way around the schema… couldn't we instead set a search_path in the function ?

For a later iteration of powa-archivist, we could replace this delete with a truncate: the table will never be shared by several servers anymore

If you want to avoid sharing temp table with multiple remote serers, you will need to create multiple temp tables so you need to have the server id part of its name or something like that, and therefore make the query that reads from it dynamic.

Aren't all the powa collector workers using their own dedicated session to the central powa database ?

The best solution I could come up with is to do something like

CREATE TEMP TABLE powa_XXX_src_tmp_$srvid (CHECK (srvid = $srvid) INHERITS (powa_XXX_src_tmp);

This way it would not require any change and will be transparent on the powa archivist side. If you have an old collector you just do the same as before, and if you have the new collector you will still get the correct value but hopefully better performance provided that constraint_exclusion is enabled.

We can still do the delete as before, but transform it to a DELETE FROM ONLY ..., which will be a no-op if you have the new collector (and otherwise will cleanup the data with the old collector), and the new collector can be responsible for the TRUNCATE. If you happen to have the old powa-archivist, then the DELETE will not be a no-op and will be a bit slower, but as least it won't bloat as before.

Yeah that may even be easier, you're right

Aren't all the powa collector workers using their own dedicated session to the central powa database ?

They do, but I think that @frost242 had to use some connection pooler to avoid having too many concurrent snapshots. That's an easy solution if you have more than a few dozen of remote servers and we should avoid breaking that compatibility.

Ok, then it means they can't be temp tables I suppose… or we need to recreate them on each collect

why not? if we use the srvid in the temp table name there shouldn't be any problem.

Because we're not sure that the next collection for the same srvid will come from the same session?

we can just check if the table exists before each snapshot rather than relying on some thread state. That will be even easier to do and not that expensive compared to the snapshot itself. That will increase a bit the number of pgss entries but they should stay somewhat stable, as long as you don't keep restarting postgres, the pooler or the collector