Possible precision loss for numeric columns

[lospejos]

I'm not sure if is it a pgcopydb bug or PostgreSQL versions incompatibility, or logging issue.

When migrating data with column of type numeric from PostgreSQL 10 to PostgreSQL 14 using pgcopydb version 0.14.1.3 there is a possible precision loss.

Given: column amount in table my_table has type numeric (without specifying exact precision before and after decimal dot) has check constraint check (amount > 0).
When migration data for row containing value amount = 0.00000000000000006938893903907228, there is an error:

2023-11-24 02:51:32 141806 ERROR  pgsql.c:1944 [TARGET 13672] ERROR:  new row for relation "my_table" violates check constraint "my_table_amount_check"
2023-11-24 02:51:32 141806 ERROR  pgsql.c:1944 [TARGET 13672] DETAIL:  Failing row contains (<other columns values>, 0.000000).

It seems that pgcopydb makes rounding and tries to insert a rounded 0.000000 value instead of actual value 0.00000000000000006938893903907228.

Or may be it comes from different default precision settings for numeric type in PostgreSQL 10 and PostgreSQL 14?
Update: see comment below.

[lospejos]

I additionally checked the PostgreSQL 14:

create table if not exists tmp_test_numeric (
    amount numeric not null check (amount > 0)
);

insert into tmp_test_numeric (amount) values (0.00000000000000006938893903907228);

select * from tmp_test_numeric;

Data was inserted without errors.

So this is most likely not a PostgreSQL 14 issue, but pgcopydb issue.

[dimitri]

Hi,

Thanks for opening this issue @lospejos. Could you give more information as to where this happens? Initial COPY of the data, or applying CDC? If the latter, using test_decoding or using wal2json? If the latter, which version of it?

[lospejos]

Hi,

Thanks for opening this issue @lospejos. Could you give more information as to where this happens? Initial COPY of the data, or applying CDC? If the latter, using test_decoding or using wal2json? If the latter, which version of it?

This error happened during CDC applying phase using wal2json plugin, pgcopydb version 0.14.1.5.g8ce63dd built from sources of main branch.

[hanefi]

I believe the fix is merged in wal2json in eulerto/wal2json#255 but they haven't made a new release with this fix.

I hope that this issue will be solved on the next release of wal2json. Maybe we should ask the maintainers to create a new release for the project.

@lospejos Is it possible for you to install wal2json from main branch, and verify that the issue is now resolved?

[lospejos]

I believe the fix is merged in wal2json in eulerto/wal2json#255 but they haven't made a new release with this fix.

I hope that this issue will be solved on the next release of wal2json. Maybe we should ask the maintainers to create a new release for the project.

@lospejos Is it possible for you to install wal2json from main branch, and verify that the issue is now resolved?

Sorry for my possible dumb question, but I have no idea how to do this.
AFAIK the pgcopydb is compiling statically, including the wal2json plugin. At least, searching the whole filesystem for wal2json*, I found no such file.

Could you provide a more detailed instruction if it is possible? Thanks!

[hanefi]

I did some digging and I no longer believe that building wal2json is enough to fix your issues. We are missing the necessary changes to use the unreleased fix on wal2json project.

I am sure that we do not statically link wal2json. I suggest you try using test_decoding instead. When wal2json transforms the numeric into a json number, it may loose some precision and you may end up with a different number. The fix was to add a new option that changes the output format for numeric data type to json string literals to prevent this from happening.

If you are still curious about installing wal2json from source, the project readme at this section lists steps required to install wal2json from source.

You should install the plugin on your source database server. If this is your production environment and there are other users of wal2json, it may not be ideal to do this, and there may be some potential risks unfortunately.

[hanefi]

A clarification on the architecture:

If you'd like to use wal2json, it should be installed on your source database. You can run pgcopydb in any machine that may or may not have wal2json. In that sense pgcopydb is just a user of wal2json.

You can consider waiting for an answer on eulerto/wal2json#274 regarding some instructions on how to use the latest fix on wal2json on your system.

[shubhamdhama]

Linking this here: #576