Question: insert only stmt

[pmpetit]

Hello,
imagine this use case, there is only insert in the table, no updates.
i cluster the table using the pk on 2 fields "store,id". As there is only insert stmt, new tuple will be added in new blocks.
To get new tuple in the same order as the pk,i should re-run 'cluster'.
How pg_squeeze could see that it have to squeeze, without being able to use 'free_space_extra' threshold ? because old block are 100 % full (no fillfactor on the table) ?
thanks

[kmoppel]

Hi @pmpetit,
Have you tried setting "free_space_extra" to zero? Haven't looked into code but sounds like it...

free_space_extra int NOT NULL DEFAULT 50,
CHECK (free_space_extra >= 0 AND free_space_extra < 100),

[pmpetit]

i give it a try, thanks

also :
i can see there that there is fn

Schema | squeeze
Name | squeeze_table
Result data type | void
Argument data types | tabchema name, tabname name, clustering_index name, rel_tablespace name, ind_tablespaces name[]
Type | func

Do you think i can use it ?

[pmpetit]

if it is ok, i could not use it without setting a day in the cronjob, the table has 2To rows, i can not squeeze it every day...

[pmpetit]

Also, as there is an eavy insert load on the server (12), if i understand the process, you will create a logical replication slot. But i often face this msg, if i want to create a replication slot.

postgres@stock(europe)=# select * from pg_create_logical_replication_slot('slot1','pgoutput');
ERROR: cannot create logical replication slot in transaction that has performed writes
postgres@stock(europe)=#

Thanks

[ahouska]

1. Yes, you can call the squeeze.squeeze_table() interactively.
2. Why are you trying to create the replication slot? pg_squeeze creates it automatically for its purposes and it eventually drops it. Anyway, if you are trying to create a replication slot for any reason, the error message indicates that you do so in BEGIN - END block, don't you?

[pmpetit]

i try to create the replication slot manually, because i try to understand what's happening:
i face this message if pg_show_plans is installed:
postgres@pgbench(silver01)=# SELECT * from squeeze.squeeze_table('public','pgbench_accounts','pgbench_accounts_pkey',null,null);
ERROR: cannot create logical replication slot in transaction that has performed writes
postgres@pgbench(silver01)=#
if i remove drop extension "pg_show_plans" AND pg_show_plans from shared_preload_libraries, then it is ok
and i do not execute squeeze.squeeze_table() between BEGIN - END block

[ahouska]

Thanks for the report and for your investigation! Indeed, pg_show_plans seems to be the root cause. I've just created a report for that.