Here's an attempt to make the bug reproducible. Unfortunately I'm not able
to reproduce the issue with generated data.
dropdb testdb || true
createdb -E UTF8 testdb
cat <<EOF > stress.sql
CREATE TABLE "public".downloaded_images (
itemid text NOT NULL,
property text NOT NULL,
image_number integer DEFAULT 0 NOT NULL,
filename text,
download_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
failures_count integer DEFAULT 0
);
INSERT INTO "public".downloaded_images(itemid, property, filename,
download_time)
SELECT md5(RANDOM()::TEXT), 'categoryPagePhotoUrl', md5(RANDOM()::TEXT),
NOW()
FROM generate_series(1, 100000);
EOF
cat <<EOF > evil.sql
BEGIN;
CREATE TABLE IF NOT EXISTS "vgg16_fc1"
(itemId TEXT,
embedding_number INT DEFAULT 0,
embedding JSONB,
weight NUMERIC DEFAULT 1,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
additional_data JSON,
PRIMARY KEY(itemId, embedding_number)
);
CREATE INDEX IF NOT EXISTS "last_update_vgg16_fc1" ON "vgg16_fc1"
USING btree ("last_update");
DECLARE "test-cursor-vgg16_fc1" CURSOR WITH HOLD FOR
SELECT di.itemId, image_number, filename FROM (SELECT *
FROM "public".downloaded_images
WHERE property='categoryPagePhotoUrl' AND filename IS NOT NULL)
di
LEFT JOIN (SELECT itemId, MIN(last_update) as last_update FROM
"vgg16_fc1" GROUP BY itemId) computed ON di.itemId=computed.itemId
WHERE COALESCE(last_update, '1970-01-01') < download_time;
FETCH 10000 IN "test-cursor-vgg16_fc1";
COMMIT;
EOF
psql -d testdb -f stress.sql
psql -d testdb -f evil.sql
Anytime I run the evil.sql, it crashes the server.
BEGIN
CREATE TABLE
CREATE INDEX
DECLARE CURSOR
psql:evil.sql:28: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:evil.sql:28: fatal: connection to server was lost
On Tue, 7 Sept 2021 at 14:09, Christoph Berg <m...@debian.org> wrote:
> Re: Tomas Barton
> > a slightly sophisticated SELECT query with a CURSOR can lead to
> > postgresql server segmentation fault.
> >
> > LOG: server process (PID 10722) was terminated by signal 11:
> Segmentation
> > fault
> > DETAIL: Failed process was running: COMMIT
>
> > I'll try to make an reproducable code, let me known if you need more
> > information.
> >
> > The query might be a bit nasty, but it shouldn't crash whole server.
>
> Can you share the query and the schema?
>
> Christoph
>
