dbdpg
dbdpg copied to clipboard
Published
20 hours ago •
bucardo
bucardo
Value trimmed upon insert
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.
country => PG_CHAR,
This will cause the bind parameter to have the type character, which truncates the input to just one character.
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.
Okay, added a little blurb in commit b074817ab3d6a74f2e21c5c3cba11eb356793da8