PostgreSQL Casting Error – Troubleshooting Query Failures

hivepostgresqlpostgresql-9.3

I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds

The query is generated by hive metadata service, and I can't modify it.

This is a simplified version of the query:

select
    "TBLS"."TBL_ID",
    "FILTER0"."PART_ID",
    "TBLS"."TBL_NAME",
    "FILTER0"."PART_KEY_VAL"
from
    "PARTITIONS"
inner join "TBLS" on
    "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
    and "TBLS"."TBL_NAME" = 'test_table_int'
inner join "PARTITION_KEY_VALS" "FILTER0" on
    "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
where
    cast("FILTER0"."PART_KEY_VAL" as decimal(21, 0)) = 1

When I spin up a new database, and populate the relevant tables, this is how the whole data looks like (querying without any filters):

and running the query above will return a single row (the one with PART_KEY_VAL = 1)

the problem starts after I run some automated tests that write to those tables. I couldn't find any pattern, I just run a few complicate tests that write to those tables

Now if I populate those tables again, the data looks similar:

but running the query above will result in:

SQL Error [22P02]: ERROR: invalid input syntax for type numeric: "c"

for some reason, the value "c" is being cast to decimal and it fails, even though the same query on the same data was working earlier

what could be the reason for this behavior?

for reference, here is where the query is generated, but I simplified it a bit above: https://github.com/apache/hive/blob/rel/release-3.1.2/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1289-L1339

Best Answer

the problem is that for some reason postgres stops using the index on the column "TBL_NAME".

when the index is used, the table is filtered first, and the remaining values are all ints.
but when the index is not used, the cast will happen on all the rows, and will fail on the strings.

as a workaround, I set enable_seqscan = false, and that forces postgres to use the index, but that is not a long term solution.

I opened a bug on hive with a more detailed explanation of the problem: https://issues.apache.org/jira/browse/HIVE-23576

Related Solutions

Postgresql – Improve SQL query

It looks like raising the work_mem by some MBs (say 30) would speed things up significantly. You could also remove the ORDER BY clause from the aggregation CTE. Similarly, the GROUP BY in the subquery of the first CTE looks useless. Also, sum(1) is basically a count on a non-null column, for example the column used in the inner join. Furthermore, it looks like you carry a bunch of nonaggregated and nongrouping columns through the CTEs - it is usually a good idea to join these in the latest possible step. This way you can decrease the memory consumption in certain steps. I mean, for example, the vd.* in vintage_csums.

Postgresql – Determine percentage from count() without cast issues

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.
Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).
It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).
NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:

SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;

Best Answer

Related Solutions

Postgresql – Improve SQL query

Postgresql – Determine percentage from count() without cast issues

Related Question