Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.10\))
Subject: Re: Problem with "on conflict"
From: Rui DeSousa <rui.desousa@icloud.com>
In-Reply-To: <939e57d2-9294-4557-b436-eb5e9b268f6f@gmail.com>
Date: Wed, 26 Mar 2025 12:31:47 -0400
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <5835F92A-3382-4B66-84EA-3DFB02D0D142@icloud.com>
References: <939e57d2-9294-4557-b436-eb5e9b268f6f@gmail.com>
To: Udo Polder <udo.polder@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5835F92A-3382-4B66-84EA-3DFB02D0D142%40icloud.com>
Precedence: bulk


> On Mar 26, 2025, at 12:15 PM, Udo Polder <udo.polder@gmail.com> wrote:
>=20
> Hi all. I have some really strange behavior with postgres and 'on =
conflict'.
> I had some error today at a customer and i figured out, the problem is =
triggered by postgres doing an insert on a table with wrong data (not =
even provided), when it should insert the data.
>=20
> After some playing around with the query, the error just went away, =
magically =E2=80=A6.=20
> Here you can see, that the insert is updating stuff:
>=20
> There also is a trigger on the db:
>=20
> CREATE OR REPLACE FUNCTION fn_hut_bundle_create_id() returns TRIGGER =
AS $$
> begin
>     if new.bundle_id is null or new.bundle_id=3D'' then
>         NEW.bundle_id =3D concat('HB-', nextval('seq_hut_bundle_id'));
>     end if;
>=20
>     return NEW;
> END;
> $$ LANGUAGE plpgsql;
> create trigger tr_hut_bundle_id before insert on hut_bundle for each =
row EXECUTE FUNCTION fn_hut_bundle_create_id();
>=20
> <ti600gsoOaUj04Ng.png>
>=20
>=20
>=20
> now working:
> <U4iCI1t7CX28e3oM.png>
>=20
> Can someone give me some hint, what the problem could be? After my =
=E2=80=9Eplaying=E2=80=9C the error can not be reproduced any longer, =
and the statement is inserting stuff like it should.
> i played with null and '' as a primary key(bundle_id)
>=20
> Postgres is:
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, =
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
>=20
> Any help would be very welcome ....
>=20
> Thanks
>=20

To me it looks like the record already existed and the on conflict =
update occurred.  Given that hut_id is not updated on conflict; the old =
value remained.  Was the sequence recycled or was 'HB-32=E2=80=99 =
created manually beforehand?