Solved

UPDATE in postgis with binary data fails, INSERT works

  • 31 January 2020
  • 5 replies
  • 41 views

Badge

I have a workspace that is trying to upload a bytearray into an existing PostGIS table, into a ByteA column. The data is generated in a python caller.

Using INSERT, this works fine. There is no problem, and the resulting data can be read from a different client as expected.

However, using UPDATE it fails, stating:

Error executing SQL command ('update "public"."mesh_test" set "ogc_fid" = 694,"mesh" = E'\003\000\000\000\212\032\005H\004\362\336H33C@g\032\005H&\362\336H\205\353\021@\354\031\005H\004\362\336H\205\353\021@\001\000\000\000\000\000\000\000\002\000\000\000\001\000\000\000' WHERE "ogc_fid" = E'694''): 'ERROR:  invalid byte sequence for encoding "UTF8": 0x00

I can circumvent the error by encoding it into base64, but that yields data I can't interpret from another client.

It seems that the update statement is done using a different method than the insert statement, which is odd. Has anyone seen this before?

EDIT: Added example workspace, plus sqlfile to create and populate table. You need to fill in the correct credentials as parameter.

EDIT2: The example workspace creates a new table, but I just tested: insert into the original table works as well perfectly.

EDIT3: INSERTING with bulk update set to NO gives the same error as updating.

icon

Best answer by tomvanderputte 6 February 2020, 14:02

View original

5 replies

Badge +2

@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw

Badge

@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw

HI @markatsafe, just added an example workspace and sql script to create and populate the source table.

Badge

@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw

Hi @markatsafe, I made some progress: the INSERT only works when setting Bulk Insert = YES. setting it to NO will yield the same error. I saw in the documentation that bulk insert mode uses the COPY command. So somehow the data is correctly transfered and inserted using that way. Does that help in identifying a solution somehow?

Badge

OK I managed to do this, with a work around.

So the problem is that I have bytearray created in Python, that is respected when doing an insert with the SQL COPY command (bulk insert = yes), but not when doing a regular SQL INSERT.

The solution is to encode it first, and in the insert statement, tell Postgres to decode it when writing the data. So we cannot use a Postgres writer, but need an SQLExecutor:

1) although it's already binary data, we need to encode the "mesh" attribute with a BinaryEncoder (HEX).

2a ) then use an SQLExecutor to insert it with this command:

insert into public.mesh_table (ogc_fid, mesh) values 
(nextval('mesh_table_ogc_fid_seq'), decode('@Value(mesh)','HEX'))

OR

2b) UPSERT the data with this command:

INSERT INTO 
    public.mesh_table 
    (ogc_fid
    , mesh) 
VALUES (
    @Value(ogc_fid) 
    , decode('@Value(mesh)','HEX') ) 
ON CONFLICT 
    (ogc_fid) 
DO 
    UPDATE SET mesh = EXCLUDED.mesh

It's a hassle, but it works.

Badge +2

OK I managed to do this, with a work around.

So the problem is that I have bytearray created in Python, that is respected when doing an insert with the SQL COPY command (bulk insert = yes), but not when doing a regular SQL INSERT.

The solution is to encode it first, and in the insert statement, tell Postgres to decode it when writing the data. So we cannot use a Postgres writer, but need an SQLExecutor:

1) although it's already binary data, we need to encode the "mesh" attribute with a BinaryEncoder (HEX).

2a ) then use an SQLExecutor to insert it with this command:

insert into public.mesh_table (ogc_fid, mesh) values 
(nextval('mesh_table_ogc_fid_seq'), decode('@Value(mesh)','HEX'))

OR

2b) UPSERT the data with this command:

INSERT INTO 
    public.mesh_table 
    (ogc_fid
    , mesh) 
VALUES (
    @Value(ogc_fid) 
    , decode('@Value(mesh)','HEX') ) 
ON CONFLICT 
    (ogc_fid) 
DO 
    UPDATE SET mesh = EXCLUDED.mesh

It's a hassle, but it works.

@tomvanderputte Thanks for including the reproduction package. We were able to reproduce the error. We'll try and get this addressed.

Reply