search

cybertec-postgresql / pg_squeeze

A PostgreSQL extension for automatic bloat cleanup
Other
470 stars 31 forks source link

Replica identiry 'full' not supported #37

Closed aleduc closed 1 year ago

aleduc commented 4 years ago

Hi, is it a bug or feature? I can't find in the readme or on your website something about that.

postgres=# SELECT squeeze.squeeze_table('inventory', 'products', null, null, null);
ERROR:  Replica identity "full" not supported

I'm trying to use pg_squeeze instead of pg_repack because I suspect that pg_repack breaks replication slots/debezium, therefore replica identity full is necessary.

ahouska commented 4 years ago

This is expected - please see the first paragraph of the "Register table for regular processing" section in README.

aleduc commented 4 years ago

Thanks for your answer. I've read the first paragraph and didn't catch it again how this connects with "Replica Identity Full".

First, make sure that your table has either primary key or unique constraint. This is necessary to process changes other transactions might do while "pg_squeeze" is doing its work.

My tables certainly have PK.

ahouska commented 4 years ago

Sorry, I misunderstood the problem.

I don't think the "full replica identity" is suitable for the things pg_squeeze does. Consider there are multiple identical rows in the table and one of them gets updated while data is being copied to the new table (for example: UPDATE foo SET bar WHERE ctid = ...). In such a case pg_squeeze has no way to determine to which of those initially identical rows it should apply that "concurrent change".

Of course, pg_squeeze can be changed so that it ignores the "full replica identity" setting if the table has PK (or unique constranit) but that seems to me inconsistent. Why do you use this kind if identity when your tables do have PK?

chripede commented 4 years ago

@ahouska not OP, but same problem. We use REPLICA IDENTITY FULL because we use Debezium to send changes to Kafka. That allows us to see what has changed.

ahouska commented 4 years ago

ok, I'll try to fix it in the next version. (Rough estimate is early next year.)

ahouska commented 3 years ago

Fixed now in the master branch, to be included in the (upcoming) branch 1.4. Note that other unrelated changes will be done in the master branch, so it's not recommended to use master in production environments.