From: "R, Siva" <sivasubr@amazon.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Bug in gin insert redo code path during re-compression of empty gin
 data leaf pages
Thread-Topic: Bug in gin insert redo code path during re-compression of empty
 gin data leaf pages
Thread-Index: AQHUHhriTtCi9KoAzkiIHCifB95wmw==
Date: Tue, 17 Jul 2018 22:40:12 +0000
Message-ID: <1531867212836.63354@amazon.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: multipart/mixed; boundary="_002_153186721283663354amazoncom_"
MIME-Version: 1.0
Precedence: bulk

--_002_153186721283663354amazoncom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,=0A=
We came across an issue during replay of a gin insert record on a pre-9.4 u=
ncompressed data leaf page that does not have any items in it. The engine v=
ersion where the replay is done is 9.6.3. The redo logic attempts to compre=
ss the existing page items before trying to insert  new items from the WAL =
record[1]. In this particular case, we noticed that if the data leaf page d=
oes not have any items in it (see below on how such a page can be present),=
 the compression should not be attempted as the algorithm expects the page =
to have at least one item [2]. This will lead to incorrect data size set on=
 the page and also makes the assertion that expects npacked and nuncompress=
ed to be equal, false [3].=0A=
=0A=
In Postgres 9.3, when the gin posting tree is vacuumed, the pages that are =
in the leftmost and rightmost branches are not deleted [4]. These empty pag=
es can be part of the database after upgrading to 9.6.3. We verified this b=
y doing the following test:=0A=
=0A=
Step 1: On Postgresql 9.3, create a table with gin index, insert and delete=
 some data followed by vacuum.=0A=
=0A=
* CREATE EXTENSION IF NOT EXISTS pg_trgm;=0A=
* CREATE TABLE gin_test (first_name text, last_name text);=0A=
* INSERT INTO gin_test SELECT md5(random()::text), md5(random()::text) FROM=
 (SELECT * FROM generate_series(1,100000) AS id) AS x;=0A=
* CREATE INDEX gin_t_idx ON gin_test USING gin (first_name gin_trgm_ops, la=
st_name gin_trgm_ops) WITH (fastupdate =3D OFF);=0A=
* DELETE FROM gin_test;=0A=
* VACUUM gin_test;=0A=
=0A=
=0A=
Step 2: Upgrade the database to 9.6.3 using pg_upgrade=0A=
=0A=
* pg_upgrade -b $93_POSTGRES_DIR/bin -B $96_POSTGRES_DIR/bin -d $93_DATADIR=
 -D $96_DATADIR=0A=
=0A=
=0A=
Step 3: Check the gin page opaque information of a uncompressed data leaf p=
age to see if it has no items=0A=
=0A=
* CREATE EXTENSION IF NOT EXISTS pageinspect;=0A=
* SELECT * FROM gin_metapage_info(get_raw_page('gin_t_idx', 0));  >>> Get n=
umber of data pages=0A=
* SELECT * FROM gin_page_opaque_info(get_raw_page('gin_t_idx', 8000)); >>> =
some block number close to total number of blocks=0A=
    * This will return a page that has rightlink 2^32-1, maxoff 0 and flags=
 {data,leaf}.=0A=
=0A=
Attached is a patch that will skip compression of the posting list in the c=
ase that the data leaf page is empty.=0A=
Please let us know any comments or feedback. Thanks!=0A=
=0A=
Best=0A=
Siva=0A=
=0A=
=0A=
*References:*=0A=
[1] - https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsr=
c/backend/access/gin/ginxlog.c;h=3Da40f1683dd80e4620ba6ccd93ae0e178e9616cf7=
;hb=3Drefs/heads/REL9_6_STABLE#l147=0A=
[2] - https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsr=
c/backend/access/gin/ginpostinglist.c;h=3D54d5f6f630c4638474d3208489c5fb6eb=
1ca1eeb;hb=3Drefs/heads/REL9_6_STABLE#l201=0A=
[3] - https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsr=
c/backend/access/gin/ginxlog.c;h=3Da40f1683dd80e4620ba6ccd93ae0e178e9616cf7=
;hb=3Drefs/heads/REL9_6_STABLE#l163=0A=
[4] - https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsr=
c/backend/access/gin/ginvacuum.c;h=3D31b765287f6ac9da798d2637046e706f67bdac=
7a;hb=3Drefs/heads/REL9_3_STABLE#l430=

--_002_153186721283663354amazoncom_
Content-Type: application/octet-stream;
	name="skip-compression-of-pre-9.4-empty-gin-data-leaf-page-replay_v1.patch"
Content-Description: 
 skip-compression-of-pre-9.4-empty-gin-data-leaf-page-replay_v1.patch
Content-Disposition: attachment;
	filename="skip-compression-of-pre-9.4-empty-gin-data-leaf-page-replay_v1.patch";
	size=1766; creation-date="Tue, 17 Jul 2018 22:37:40 GMT";
	modification-date="Tue, 17 Jul 2018 22:37:40 GMT"
Content-Transfer-Encoding: base64

RnJvbSA5NTZhNTMzYzVlYzMxNzE0ODU2MjQ0NjFjYzEzZjc4NmRiZmMxNWU1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTaXZhIFIgPHNpdmFzdWJyQGFtYXpvbi5jb20+CkRhdGU6IFR1
ZSwgMTcgSnVsIDIwMTggMjI6MDE6NDIgKzAwMDAKU3ViamVjdDogW1BBVENIXSBTa2lwIGNvbXBy
ZXNzaW9uIG9mIHByZS05LjQgZW1wdHkgZ2luIGRhdGEgbGVhZiBwYWdlcyBkdXJpbmcKIHJlcGxh
eQoKLS0tCiBzcmMvYmFja2VuZC9hY2Nlc3MvZ2luL2dpbnhsb2cuYyB8IDI1ICsrKysrKysrKysr
KysrKysrKystLS0tLS0KIDEgZmlsZSBjaGFuZ2VkLCAxOSBpbnNlcnRpb25zKCspLCA2IGRlbGV0
aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9naW4vZ2lueGxvZy5jIGIv
c3JjL2JhY2tlbmQvYWNjZXNzL2dpbi9naW54bG9nLmMKaW5kZXggYTQwZjE2OC4uMDc5N2I5MiAx
MDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvYWNjZXNzL2dpbi9naW54bG9nLmMKKysrIGIvc3JjL2Jh
Y2tlbmQvYWNjZXNzL2dpbi9naW54bG9nLmMKQEAgLTE1MSwxNSArMTUxLDI4IEBAIGdpblJlZG9S
ZWNvbXByZXNzKFBhZ2UgcGFnZSwgZ2lueGxvZ1JlY29tcHJlc3NEYXRhTGVhZiAqZGF0YSkKIAkJ
SXRlbVBvaW50ZXIgdW5jb21wcmVzc2VkID0gKEl0ZW1Qb2ludGVyKSBHaW5EYXRhUGFnZUdldERh
dGEocGFnZSk7CiAJCWludAkJCW51bmNvbXByZXNzZWQgPSBHaW5QYWdlR2V0T3BhcXVlKHBhZ2Up
LT5tYXhvZmY7CiAJCWludAkJCW5wYWNrZWQ7Ci0JCUdpblBvc3RpbmdMaXN0ICpwbGlzdDsKIAot
CQlwbGlzdCA9IGdpbkNvbXByZXNzUG9zdGluZ0xpc3QodW5jb21wcmVzc2VkLCBudW5jb21wcmVz
c2VkLAotCQkJCQkJCQkJICAgQkxDS1NaLCAmbnBhY2tlZCk7Ci0JCUFzc2VydChucGFja2VkID09
IG51bmNvbXByZXNzZWQpOworCQl0b3RhbHNpemUgPSAwOwogCi0JCXRvdGFsc2l6ZSA9IFNpemVP
ZkdpblBvc3RpbmdMaXN0KHBsaXN0KTsKKwkJLyoKKwkJICogSW4gOS4zLCB3aGVuIHdlIGRlbGV0
ZSBkYXRhIGxlYWYgcGFnZXMgYXMgcGFydCBvZiB2YWN1dW0sIHdlIHNraXAKKwkJICogZGVsZXRp
b24gb2YgdGhlIGxlZnRtb3N0IGFuZCByaWdodG1vc3QgcGFnZXMgZXZlbiB3aGVuIHRoZXkgYXJl
CisJCSAqIGVtcHR5LiBTbyB0cnkgdG8gY29tcHJlc3MgdGhlIHBvc3RpbmcgbGlzdCBvbmx5IGlm
IHRoZXJlIGFyZSBpdGVtcworCQkgKiBpbiBpdC4KKwkJICovCisJCWlmIChudW5jb21wcmVzc2Vk
ID4gMCkKKwkJeworCQkJR2luUG9zdGluZ0xpc3QgKnBsaXN0OworCisJCQlwbGlzdCA9IGdpbkNv
bXByZXNzUG9zdGluZ0xpc3QodW5jb21wcmVzc2VkLCBudW5jb21wcmVzc2VkLAorCQkJCQkJCQkJ
CSAgIEJMQ0tTWiwgJm5wYWNrZWQpOworCQkJdG90YWxzaXplID0gU2l6ZU9mR2luUG9zdGluZ0xp
c3QocGxpc3QpOworCisJCQlBc3NlcnQobnBhY2tlZCA9PSBudW5jb21wcmVzc2VkKTsKKworCQkJ
bWVtY3B5KEdpbkRhdGFMZWFmUGFnZUdldFBvc3RpbmdMaXN0KHBhZ2UpLCBwbGlzdCwgdG90YWxz
aXplKTsKKwkJfQogCi0JCW1lbWNweShHaW5EYXRhTGVhZlBhZ2VHZXRQb3N0aW5nTGlzdChwYWdl
KSwgcGxpc3QsIHRvdGFsc2l6ZSk7CiAJCUdpbkRhdGFQYWdlU2V0RGF0YVNpemUocGFnZSwgdG90
YWxzaXplKTsKIAkJR2luUGFnZVNldENvbXByZXNzZWQocGFnZSk7CiAJCUdpblBhZ2VHZXRPcGFx
dWUocGFnZSktPm1heG9mZiA9IEludmFsaWRPZmZzZXROdW1iZXI7Ci0tIAoyLjcuMy5BTVpOCgo=

--_002_153186721283663354amazoncom_--