New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Floating point comparison issue #289

Closed
jakob opened this Issue Oct 25, 2016 · 4 comments

Comments

Projects
None yet
1 participant
@jakob
Owner

jakob commented Oct 25, 2016

When Postico creates update statements for tables without primary keys or for views, it compares the value of every row. When there are floating point columns, rounding issues can prevent rows from matching.

This causes errors like "the transaction did not affect the expected number of rows"

I have to investigate exactly how PostgreSQL compares floats, I'm not sure where this is s coming from.

@jakob jakob modified the milestone: v1.1.2 Mar 22, 2017

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jul 19, 2017

Owner

After investigating the issue, I've discovered that the problem is caused by the fact that PostgreSQL does not send sufficient digits for floats and doubles to uniquely identify them. I was able to fix this problem by setting the "extra_float_digits" parameter to 3.

Unfortunately, the value 3 is not allowed before PostgreSQL 9.0 (including Amazon Redshift). Postico automatically detects this error and sets the parameter to 2 instead. For doubles this does not cause any issues, but it can cause problems with floats. For this reason, I recommend not to use floats with versions of PostgreSQL before 9.0, especially in tables that lack a primary key.

The fix is in build 1710. I still need to test if this new connection parameter works with Redshift and CockroachDB

Owner

jakob commented Jul 19, 2017

After investigating the issue, I've discovered that the problem is caused by the fact that PostgreSQL does not send sufficient digits for floats and doubles to uniquely identify them. I was able to fix this problem by setting the "extra_float_digits" parameter to 3.

Unfortunately, the value 3 is not allowed before PostgreSQL 9.0 (including Amazon Redshift). Postico automatically detects this error and sets the parameter to 2 instead. For doubles this does not cause any issues, but it can cause problems with floats. For this reason, I recommend not to use floats with versions of PostgreSQL before 9.0, especially in tables that lack a primary key.

The fix is in build 1710. I still need to test if this new connection parameter works with Redshift and CockroachDB

@jakob jakob added the needs-testing label Jul 19, 2017

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jul 24, 2017

Owner

Works fine on Redshift.

I've added a small optimisation: Postico now checks the hostname, and if it ends with "redshift.amazonaws.com", it immediately uses settings compatible with Redshift (avoids a couple of round trips and makes connections faster)

Owner

jakob commented Jul 24, 2017

Works fine on Redshift.

I've added a small optimisation: Postico now checks the hostname, and if it ends with "redshift.amazonaws.com", it immediately uses settings compatible with Redshift (avoids a couple of round trips and makes connections faster)

@jakob jakob closed this Jul 24, 2017

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jul 24, 2017

Owner

A customer complained about spurious log messages on Greenplum. I might need to add an option that disables extra_float_digits=3 param.

Owner

jakob commented Jul 24, 2017

A customer complained about spurious log messages on Greenplum. I might need to add an option that disables extra_float_digits=3 param.

@jakob jakob reopened this Jul 24, 2017

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 1, 2017

Owner

Postico now remembers for each favorite if the server supports extra_float_digits=3, so the error should now only occur the first time you connect.

Owner

jakob commented Aug 1, 2017

Postico now remembers for each favorite if the server supports extra_float_digits=3, so the error should now only occur the first time you connect.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment