Value trimmed upon insert

[jackdeguest]

I am performing an upsert using PostgreSQL v12.11 with DBD::Pg version 3.16.0 and DBI version 1.643, i.e. the latest as of today 2022-11-03. I am getting a PostgreSQL error the value violates a contraint because it is J where it should be JP (this is a country code). When I perform the query directly with psql no problem and the row is successfully inserted or updated. However, if I do it using DBI and DBD::Pg, I get this error:

DBD::Pg::st execute failed: ERROR:  insert or update on table "address" violates foreign key constraint "fk_address_country"
DETAIL:  Key (country)=(J ) is not present in table "country". at ./insert_address.pl line 51.

The PostgreSQL server shows the same message, and if I do a trace, such as:

my $file = "/var/tmp/pgbackend-debug.log";
open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
$dbh->pg_server_trace($fh);
# The perl code here
$dbh->pg_server_untrace;
close($fh);

I get:

2022-11-03 10:57:31.649073      F       10      Query    "begin"
2022-11-03 10:57:31.649307      B       10      CommandComplete  "BEGIN"
2022-11-03 10:57:31.649312      B       5       ReadyForQuery    T
2022-11-03 10:57:31.649326      F       496     Parse    "" "INSERT INTO address (pobox, ext_addr, street_addr, city, region, postal_code, country, type, lang, longitude, latitude) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) ON CONFLICT ON CONSTRAINT idx_address DO UPDATE SET pobox = $12, ext_addr = $13, street_addr = $14, city = $15, region = $16, postal_code = $17, country = $18, type = $19, lang = $20, longitude = $21, latitude = $22 RETURNING *" 22 1043 1043 1043 1043 1043 1043 18 0 1043 701 701 1043 1043 1043 1043 1043 1043 18 0 1043 701 701
2022-11-03 10:57:31.649330      F       242     Bind     "" "" 0 22 0 '' 14 'KS Building 5F' 18 '1-2-3 Kudan-minami' 10 'Chiyoda-ku' 5 'Tokyo' 8 '123-4567' 2 'JP' 8 '{"work"}' 5 'en_GB' -1 -1 0 '' 14 'KS Building 5F' 18 '1-2-3 Kudan-minami' 10 'Chiyoda-ku' 5 'Tokyo' 8 '123-4567' 2 'JP' 8 '{"work"}' 5 'en_GB' -1 -1 1 0
2022-11-03 10:57:31.649335      F       6       Describe         P ""
2022-11-03 10:57:31.649336      F       9       Execute  "" 0
2022-11-03 10:57:31.649337      F       4       Sync
2022-11-03 10:57:31.652176      B       4       ParseComplete
2022-11-03 10:57:31.652182      B       4       BindComplete
2022-11-03 10:57:31.652185      B       458     RowDescription   17 "created" 21662 1 1114 8 0 0 "updated" 21662 2 1114 8 0 0 "created_login" 21662 3 1043 65535 21 0 "updated_login" 21662 4 1043 65535 21 0 "id" 21662 5 23 4 -1 0 "pobox" 21662 6 1043 65535 259 0 "ext_addr" 21662 7 1043 65535 259 0 "street_addr" 21662 8 1043 65535 259 0 "city" 21662 9 1043 65535 259 0 "region" 21662 10 1043 65535 259 0 "postal_code" 21662 11 1043 65535 54 0 "country" 21662 12 1042 65535 6 0 "type" 21662 13 1009 65535 -1 0 "lang" 21662 14 1043 65535 9 0 "longitude" 21662 15 701 8 -1 0 "latitude" 21662 16 701 8 -1 0 "address_id" 21662 17 2950 16 -1 0
2022-11-03 10:57:31.652201      B       249     ErrorResponse    S "ERROR" V "ERROR" C "23503" M "insert or update on table "address" violates foreign key constraint "fk_address_country"" D "Key (country)=(J ) is not present in table "country"." s "public" t "address" n "fk_address_country" F "ri_triggers.c" L "2463" R "ri_ReportViolation" \x00
2022-11-03 10:57:31.652223      B       5       ReadyForQuery    E

The perl code used is:

#!/usr/local/bin/perl
use v5.36;
use strict;
use warnings;
use DBI;
use DBD::Pg qw( :pg_types );

my $sql = q{INSERT INTO address (pobox, ext_addr, street_addr, city, region, postal_code, country, type, lang, longitude, latitude) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT ON CONSTRAINT idx_address DO UPDATE SET pobox = ?, ext_addr = ?, street_addr = ?, city = ?, region = ?, postal_code = ?, country = ?, type = ?, lang = ?, longitude = ?, latitude = ? RETURNING *};
my $data =
{
    city        => 'Chiyoda-ku',
    country     => "JP",
    ext_addr    => 'KS Building 5F',
    lang        => 'en_GB',
    pobox       => '',
    postal_code => '123-4567',
    region      => 'Tokyo',
    street_addr => '1-2-3 Kudan-minami',
    type        => ['work'],
};
my @keys = qw( pobox ext_addr street_addr city region postal_code country type lang longitude latitude );
push( @keys, @keys );
my $constant = 
{
    city        => PG_VARCHAR,
    country     => PG_CHAR,
    ext_addr    => PG_VARCHAR,
    lang        => PG_VARCHAR,
    latitude    => PG_FLOAT8,
    longitude   => PG_FLOAT8,
    pobox       => PG_VARCHAR,
    postal_code => PG_VARCHAR,
    region      => PG_VARCHAR,
    street_addr => PG_VARCHAR,
    type        => PG_TEXT,
};
my $dbh = DBI->connect("dbi:Pg:dbname=mydatabase;host=localhost;port=5432",
 'user_name',
 'XXXXXpassword',
 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
);
my $sth = $dbh->prepare( $sql ) || die( $dbh->errstr );
for( my $i = 0; $i < scalar( @keys ); $i++ )
{
    my $key = $keys[$i];
    say "Binding field '${key}' at position ${i} with value '", ( $data->{ $key } // '' ), "' and constant '", $constant->{ $key }, "'";
    $sth->bind_param( $i + 1, ( exists( $data->{ $key } ) ? $data->{ $key } : undef ), { pg_type => $constant->{ $key } } ) ||
        die( $sth->errstr );
}
say "Executing query now.";
# my $file = "/var/tmp/pgbackend-debug.log";
# open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
# $dbh->pg_server_trace($fh);
my $rv = $sth->execute || die( $sth->errstr );
# $dbh->pg_server_untrace;
# close($fh);
say "Execute resulted in: ${rv}";

exit(0);

The SQL table is:

CREATE TABLE address (
     id                      SERIAL NOT NULL
    ,address_id              UUID NOT NULL DEFAULT UUID_GENERATE_V4()
    ,pobox                   VARCHAR(255)
    ,ext_addr                VARCHAR(255)
    ,street_addr             VARCHAR(255)
    ,city                    VARCHAR(255)
    ,region                  VARCHAR(255)
    ,postal_code             VARCHAR(50)
    ,country                 CHAR(2)
    ,type                    TEXT[]
    /* Example fr_FR, ja_JP */
    ,lang                    VARCHAR(5)
    ,longitude               FLOAT8
    ,latitude                FLOAT8
    ,CONSTRAINT pk_address PRIMARY KEY (id)
    ,CONSTRAINT idx_address_address_id_unique UNIQUE (address_id)
    ,CONSTRAINT idx_address UNIQUE (street_addr, city, postal_code, country)
    ,CONSTRAINT fk_address_country FOREIGN KEY (country) REFERENCES country(code) ON DELETE RESTRICT
    ,CONSTRAINT fk_address_lang FOREIGN KEY (lang) REFERENCES language(lang) ON DELETE RESTRICT
) INHERITS (generic);
COMMENT ON TABLE address IS 'rfc2426 3.2.1. The field are provided in the order specified in the rfc';
COMMENT ON COLUMN address.ext_addr IS 'Extended address';
COMMENT ON COLUMN address.city IS 'locality';
COMMENT ON COLUMN address.region IS 'e.g., state or province';
COMMENT ON COLUMN address.country IS 'iso3166';
COMMENT ON COLUMN address.type IS 'rfc2426 3.2.1 : The TYPE parameter values can include "dom" to indicate a domestic delivery address; "intl" to indicate an international delivery address; "postal" to indicate a postal delivery address; "parcel" to indicate a parcel delivery address; "home" to indicate a delivery address for a residence; "work" to indicate delivery address for a place of work; and "pref" to indicate the preferred delivery address when more than one address is specified.';
COMMENT ON COLUMN address.lang IS 'Language in which the address is written';
COMMENT ON COLUMN address.longitude IS 'As decimal degrees : Decimal degrees (DDD)';
COMMENT ON COLUMN address.latitude IS 'As decimal degrees : Decimal degrees (DDD)';

I have run out of ideas on how to solve this riddle, and would very much appreciate a workaround to this apparent bug.

[markottt]

    country     => PG_CHAR,

This will cause the bind parameter to have the type character, which truncates the input to just one character.

[jackdeguest]

This will cause the bind parameter to have the type character, which truncates the input to just one character.

I had tried it with PG_VARCHAR or PG_TEXT, but it still yielded the same result. However, I just did it now, and it worked with PG_VARCHAR and not with PG_CHAR.

Maybe it ought to be documented somewhere that the data type PG_CHAR equates to just one character and if the data is greater it would get truncated.

[turnstep]

Open to adding something, but not sure where to put it; ideas welcome.

[turnstep]

Okay, added a little blurb in commit b074817ab3d6a74f2e21c5c3cba11eb356793da8