Re: BUG #14599: Segmentation fault and database corruption with hstore

On Mon, Mar 27, 2017 at 7:32 AM, <coladict(at)gmail(dot)com> wrote:

> 3. "CREATE CAST (varchar AS hstore) WITHOUT FUNCTION AS ASSIGNMENT;". If we
> skip step 4, everything works fine, but I was afraid it might fail with
> longer strings, so I did that anyway.
> 4. "CREATE CAST (text AS hstore) WITHOUT FUNCTION AS ASSIGNMENT;". If I
> skipped step 3 and only did 4, the cast was not detected with strings that
> are too short.
> 5. This was done using Java + JDBC, but should work the same via
> PREPARE/EXECUTE or any other driver's API:
>
> PreparedStatement ps = conn.prepareStatement("UPDATE the_table SET
> hstore_field = ? WHERE id = ?");
> ps.setString(1, syntax_valid_data);
> ps.setLong(2, id);
> ps.execute();
>
>
​FWIW the differences between text and varchar observed here are an
implementation detail of the JDBC driver and not inherent to PostgreSQL.
Neither text nor varchar, unadorned, have length limitations and text is
always (I think...) the default target when an explicit type is not
specified.​

As Andrew said, its not a bug when you start doing legal but conceptually
invalid stuff.

Create a "string_to_hstore" function and do something like:

UPDATE the_table SET hstore_field = string_to_hstore(?::text) WHERE id =
?::integer;

Or just rely on the existing text-to-hstore cast...

UPDATE the_table SET hstore_field = string_to_hstore(?::text::hstore) WHERE
id = ?::integer;

David J.