pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18142: strange behaviour of "UPDATE" with id_encode()
4+ messages / 4 participants
[nested] [flat]
* BUG #18142: strange behaviour of "UPDATE" with id_encode()
@ 2023-10-02 08:01 PG Bug reporting form <noreply@postgresql.org>
2023-10-02 14:00 ` Re: BUG #18142: strange behaviour of "UPDATE" with id_encode() Tom Lane <tgl@sss.pgh.pa.us>
2023-10-02 15:02 ` BUG #18142: strange behaviour of "UPDATE" with id_encode() David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2023-10-02 08:01 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: info@dataselfservice.com
The following bug has been logged on the website:
Bug reference: 18142
Logged by: DataSelfService Srl
Email address: info@dataselfservice.com
PostgreSQL version: 15.4
Operating system: docker
Description:
Hi,
In short: why is `UPDATE...id_encode()` missbehaving and behaving
differently in case a single `SET` or multiple `SET` on the same line are
done?
Details
I'm running a kubernetes instance of `15.4-alpine3.18`, with modified image
`IMG_POSTGRES_TAG` added with `pg_hashids` via following `Dockerfile`:
```
FROM postgres:15.4-alpine3.18
RUN apk add --no-cache --virtual .build-deps build-base postgresql-dev
clang15 llvm15 ; \
wget -qO-
https://github.com/iCyberon/pg_hashids/archive/refs/tags/v1.2.1.tar.gz | tar
xzf - -C /tmp && \
make -C /tmp/pg_hashids-1.2.1 && \
make -C /tmp/pg_hashids-1.2.1 install && \
rm -rf /tmp/pg_hashids-1.2.1 && \
apk del .build-deps
```
Deployed to the cluster with:
```
helm install postgres --set
image.repository=postgres,image.tag=${IMG_POSTGRES_TAG},primary.service.clusterIP=${POSTGRES_CLUSTER_IP},primary.persistence.existingClaim=postgres
oci://registry-1.docker.io/bitnamicharts/postgresql
```
The issue is that a trigger function which generates hashes during insert,
miss-behaves and generates a *wrong hash ending with a space*.
Reproduce it by running the following SQL:
```
BEGIN;
DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
CREATE TABLE test.test (
id bigint NOT NULL,
data text,
ids text,
ids1 text,
ids2 text
);
CREATE SEQUENCE test.test_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE test.test_id_seq OWNED BY test.test.id;
ALTER TABLE ONLY test.test
ALTER COLUMN id SET DEFAULT nextval('test.test_id_seq'::regclass);
CREATE FUNCTION test.myfunc ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
test.test
SET
ids = id_encode (NEW.id, 'hash1', 5,
'abcdefghijklmnopqrstuvwxyz'),
ids1 = id_encode (NEW.id, 'hash2', 12,
'abcdefghijklmnopqrstuvwxyz'),
ids2 = id_encode (NEW.id, 'hash3', 12,
'abcdefghijklmnopqrstuvwxyz')
WHERE
id = NEW.id;
RETURN new;
END;
$$;
CREATE FUNCTION test.myfunc_fix ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
test.test
SET
ids = id_encode (NEW.id, 'hash1', 5, 'abcdefghijklmnopqrstuvwxyz')
WHERE
id = NEW.id;
UPDATE
test.test
SET
ids1 = id_encode (NEW.id, 'hash2', 12,
'abcdefghijklmnopqrstuvwxyz'),
ids2 = id_encode (NEW.id, 'hash3', 12,
'abcdefghijklmnopqrstuvwxyz')
WHERE
id = NEW.id;
RETURN new;
END;
$$;
CREATE TRIGGER test_myfunc
AFTER INSERT ON test.test
FOR EACH ROW
EXECUTE FUNCTION test.myfunc ();
\copy test.test to stdout CSV FORCE QUOTE *;
INSERT INTO test.test (data)
VALUES ('a'),
('b');
\copy test.test to stdout CSV FORCE QUOTE *;
DROP TRIGGER test_myfunc ON test.test;
CREATE TRIGGER test_myfunc_fix
AFTER INSERT ON test.test
FOR EACH ROW
EXECUTE FUNCTION test.myfunc_fix ();
\copy test.test to stdout CSV FORCE QUOTE *;
INSERT INTO test.test (data)
VALUES ('a1'),
('b1');
\copy test.test to stdout CSV FORCE QUOTE *;
COMMIT
```
Run it with: `cat test.sql | psq -U testdb`.
Output looks like:
```
WARNING: database "testdb" has no actual collation version, but a version
was recorded
BEGIN
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table test.test
drop cascades to function test.myfunc()
drop cascades to function test.myfunc_fix()
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 2
"1","a","zmnb ","wzejgmprmnpa","yzvmbrydnogd"
"2","b","pylw ","xabdomywmenl","zmbexnawnpaq"
DROP TRIGGER
CREATE TRIGGER
"1","a","zmnb ","wzejgmprmnpa","yzvmbrydnogd"
"2","b","pylw ","xabdomywmenl","zmbexnawnpaq"
INSERT 0 2
"1","a","zmnb ","wzejgmprmnpa","yzvmbrydnogd"
"2","b","pylw ","xabdomywmenl","zmbexnawnpaq"
"3","a1","zmpdm","jlyezmrwvakd","ybjwprgzndxl"
"4","b1","dmepy","ydqlpvjrvxnz","jwmvprxmnkea"
COMMIT
```
The issue is the space in the end of "zmnb ". Utilizing `myfunc_fix()` which
basically runs separated `UPDATE`, works i.e. generates hashes without the
trailing space. But I am not at all satisfied and I would like to
understand what is the root cause?!?!
I noticed the `WARNING: database "testdb" has no actual collation version,
but a version was recorded` and I am not sure how that is realted (I do not
really know what is the impact of that.
Any help or ideas?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18142: strange behaviour of "UPDATE" with id_encode()
2023-10-02 08:01 BUG #18142: strange behaviour of "UPDATE" with id_encode() PG Bug reporting form <noreply@postgresql.org>
@ 2023-10-02 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-10-02 14:00 UTC (permalink / raw)
To: info@dataselfservice.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> In short: why is `UPDATE...id_encode()` missbehaving and behaving
> differently in case a single `SET` or multiple `SET` on the same line are
> done?
[ shrug... ] This is evidently a matter of the behavior of your
id_encode function, which you've not shown us. (Or if it's not yours,
you need to be complaining to its author. But there's no such
function in standard Postgres.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* BUG #18142: strange behaviour of "UPDATE" with id_encode()
2023-10-02 08:01 BUG #18142: strange behaviour of "UPDATE" with id_encode() PG Bug reporting form <noreply@postgresql.org>
@ 2023-10-02 15:02 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-02 18:38 ` Re: BUG #18142: strange behaviour of "UPDATE" with id_encode() DataSelfService Srl <info@dataselfservice.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-10-02 15:02 UTC (permalink / raw)
To: info@dataselfservice.com <info@dataselfservice.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, October 2, 2023, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18142
> Logged by: DataSelfService Srl
> Email address: info@dataselfservice.com
> PostgreSQL version: 15.4
> Operating system: docker
> Description:
>
> RUN apk add --no-cache --virtual .build-deps build-base postgresql-dev
> clang15 llvm15 ; \
> wget -qO-
> https://github.com/iCyberon/pg_hashids/archive/refs/tags/v1.2.1.tar.gz |
> tar
> xzf - -C /tmp && \
> make -C /tmp/pg_hashids-1.2.1 && \
> make -C /tmp/pg_hashids-1.2.1 install && \
> rm -rf /tmp/pg_hashids-1.2.1 && \
> apk del .build-deps
>
>
So you aren’t even using version 3 of that library and it’s read me doesn’t
mention testing on anything earlier that 9.6.x. You are asking for help in
the wrong place unless you can somehow demonstrate a problem that doesn’t
depend on the output of this third-party library function.
I’d at least see whether the most recent release of that library has the
same problem.
I see you did open an issue with them…I do get that the difference in
behavior emanating from how the update is written is quite odd and
seemingly not plausibly the fault of an extension. I assume the trigger is
required to produce the error (if not, an example without it is much
better). Or one with the trigger but a core function instead.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18142: strange behaviour of "UPDATE" with id_encode()
2023-10-02 08:01 BUG #18142: strange behaviour of "UPDATE" with id_encode() PG Bug reporting form <noreply@postgresql.org>
2023-10-02 15:02 ` BUG #18142: strange behaviour of "UPDATE" with id_encode() David G. Johnston <david.g.johnston@gmail.com>
@ 2023-10-02 18:38 ` DataSelfService Srl <info@dataselfservice.com>
0 siblings, 0 replies; 4+ messages in thread
From: DataSelfService Srl @ 2023-10-02 18:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi David,
that was so easy pick. Sorry, my mistake: I was relying on the fact that
the latest tag is <
https://github.com/iCyberon/pg_hashids/releases/tag/v1.2.1;, while in the
log there is a 1.3.
This solved the problem:
```
RUN apk add --no-cache --virtual .build-deps build-base postgresql-dev
clang15 llvm15 ; \
wget -qO-
https://github.com/iCyberon/pg_hashids/archive/cd0e1b31d52b394a0df64079406a14a4f7387cd6.tar.gz
| tar xzf - -C /tmp && \
make -C /tmp/pg_hashids-cd0e1b31d52b394a0df64079406a14a4f7387cd6 && \
make -C /tmp/pg_hashids-cd0e1b31d52b394a0df64079406a14a4f7387cd6
install && \
rm -rf /tmp/pg_hashids-cd0e1b31d52b394a0df64079406a14a4f7387cd6 && \
apk del .build-deps
```
Thank you for the eye opening. Problem solved.
Cheers,
DataSelfService team
On Mon, Oct 2, 2023 at 5:02 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Monday, October 2, 2023, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18142
>> Logged by: DataSelfService Srl
>> Email address: info@dataselfservice.com
>> PostgreSQL version: 15.4
>> Operating system: docker
>> Description:
>>
>> RUN apk add --no-cache --virtual .build-deps build-base postgresql-dev
>> clang15 llvm15 ; \
>> wget -qO-
>> https://github.com/iCyberon/pg_hashids/archive/refs/tags/v1.2.1.tar.gz |
>> tar
>> xzf - -C /tmp && \
>> make -C /tmp/pg_hashids-1.2.1 && \
>> make -C /tmp/pg_hashids-1.2.1 install && \
>> rm -rf /tmp/pg_hashids-1.2.1 && \
>> apk del .build-deps
>>
>>
> So you aren’t even using version 3 of that library and it’s read me
> doesn’t mention testing on anything earlier that 9.6.x. You are asking for
> help in the wrong place unless you can somehow demonstrate a problem that
> doesn’t depend on the output of this third-party library function.
>
> I’d at least see whether the most recent release of that library has the
> same problem.
>
> I see you did open an issue with them…I do get that the difference in
> behavior emanating from how the update is written is quite odd and
> seemingly not plausibly the fault of an extension. I assume the trigger is
> required to produce the error (if not, an example without it is much
> better). Or one with the trigger but a core function instead.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-10-02 18:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-02 08:01 BUG #18142: strange behaviour of "UPDATE" with id_encode() PG Bug reporting form <noreply@postgresql.org>
2023-10-02 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-02 15:02 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-02 18:38 ` DataSelfService Srl <info@dataselfservice.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox