MIME-Version: 1.0
From: Andrew Jackson <andrewjackson947@gmail.com>
Date: Wed, 20 Nov 2024 09:51:12 -0600
Message-ID: <CAKK5BkESSc69sp2TiTWHvvOHCUey0rDWXSrR9pinyRqyfamUYg@mail.gmail.com>
Subject: Add Option To Check All Addresses For Matching target_session_attr
To: pgsql-hackers@postgresql.org
Content-Type: multipart/mixed; boundary="000000000000b70ecb06275a2048"
Archived-At: <https://www.postgresql.org/message-id/CAKK5BkESSc69sp2TiTWHvvOHCUey0rDWXSrR9pinyRqyfamUYg%40mail.gmail.com>
Precedence: bulk

--000000000000b70ecb06275a2048
Content-Type: multipart/alternative; boundary="000000000000b70ecb06275a2046"

--000000000000b70ecb06275a2046
Content-Type: text/plain; charset="UTF-8"

Hi,

I was attempting to set up a high availability system using DNS and
target_session_attrs. I was using a DNS setup similar to below and was
trying to use the connection strings `psql postgresql://
user@pg.database.com/db_name?target_session=read-write` to have clients
dynamically connect to the primary or `psql postgresql://
user@pg.database.com/db_name?target_session=read-only` to have clients
connect to a read replica.

The problem that I found with this setup is that if libpq is unable to get
a matching target_session_attr on the first connection attempt it does not
consider any further addresses for the given host. This patch is designed
to provide an option that allows libpq to look at additional addresses for
a given host if the target_session_attr check fails for previous addresses.

Would appreciate any feedback on the applicability/relevancy of the goal
here or the implementation.

Example DNS setup
________________________________
Name                  | Type    | Record
______________|______|___________
pg.database.com | A        | ip_address_1
pg.database.com | A        | ip_address_2
pg.database.com | A        | ip_address_3
pg.database.com | A        | ip_address_4

--000000000000b70ecb06275a2046
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div><br></div><div>I was attempting to set up a high a=
vailability system using DNS and target_session_attrs. I was using a DNS se=
tup similar to below and was trying to use the connection strings `psql pos=
tgresql://<a href=3D"http://user@pg.database.com/db_name?target_session=3Dr=
ead-write`">user@pg.database.com/db_name?target_session=3Dread-write`</a> t=
o have clients dynamically connect to the primary or `psql postgresql://<a =
href=3D"http://user@pg.database.com/db_name?target_session=3Dread-only`">us=
er@pg.database.com/db_name?target_session=3Dread-only`</a> to have=C2=A0cli=
ents connect to a read replica.=C2=A0</div><div><br></div><div>The problem =
that I found with this setup is that if libpq is unable to get a matching t=
arget_session_attr on the first connection attempt it does not consider any=
 further addresses for the given host. This patch is designed to provide an=
 option that allows libpq to look at additional addresses=C2=A0for a given =
host if the target_session_attr check fails for previous addresses.</div><d=
iv><br></div><div>Would appreciate any feedback on the applicability/releva=
ncy of the goal here or the implementation.</div><div>=C2=A0</div><div>Exam=
ple DNS setup</div><div>________________________________</div><div>Name=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Type=C2=A0 =
=C2=A0 | Record</div><div>______________|______|___________</div><div><a hr=
ef=3D"http://pg.database.com">pg.database.com</a> | A=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 | ip_address_1</div><div><a href=3D"http://pg.database.com">pg.datab=
ase.com</a> | A=C2=A0 =C2=A0 =C2=A0 =C2=A0 | ip_address_2<br></div><div><a =
href=3D"http://pg.database.com">pg.database.com</a> | A=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 | ip_address_3<br></div><div><a href=3D"http://pg.database.com">pg.=
database.com</a> | A=C2=A0 =C2=A0 =C2=A0 =C2=A0 | ip_address_4<br></div></d=
iv>

--000000000000b70ecb06275a2046--
--000000000000b70ecb06275a2048
Content-Type: text/x-patch; charset="US-ASCII"; name="postgres.patch"
Content-Disposition: attachment; filename="postgres.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m3q1mxjg0>
X-Attachment-Id: f_m3q1mxjg0

ZGlmZiAtLWdpdCBhL3NyYy9pbnRlcmZhY2VzL2xpYnBxL2ZlLWNvbm5lY3QuYyBiL3NyYy9pbnRl
cmZhY2VzL2xpYnBxL2ZlLWNvbm5lY3QuYwppbmRleCA1MTA4M2RjZmQ4Li44NjVlYjc0ZmQ3IDEw
MDY0NAotLS0gYS9zcmMvaW50ZXJmYWNlcy9saWJwcS9mZS1jb25uZWN0LmMKKysrIGIvc3JjL2lu
dGVyZmFjZXMvbGlicHEvZmUtY29ubmVjdC5jCkBAIC0zNjUsNiArMzY1LDExIEBAIHN0YXRpYyBj
b25zdCBpbnRlcm5hbFBRY29ubmluZm9PcHRpb24gUFFjb25uaW5mb09wdGlvbnNbXSA9IHsKIAkJ
IkxvYWQtQmFsYW5jZS1Ib3N0cyIsICIiLCA4LAkvKiBzaXplb2YoImRpc2FibGUiKSA9IDggKi8K
IAlvZmZzZXRvZihzdHJ1Y3QgcGdfY29ubiwgbG9hZF9iYWxhbmNlX2hvc3RzKX0sCiAKKwl7ImNo
ZWNrX2FsbF9hZGRycyIsICJQR0NIRUNLQUxMQUREUlMiLAorCQlEZWZhdWx0TG9hZEJhbGFuY2VI
b3N0cywgTlVMTCwKKwkJIkNoZWNrLUFsbC1BZGRycyIsICIiLCAxLAorCW9mZnNldG9mKHN0cnVj
dCBwZ19jb25uLCBjaGVja19hbGxfYWRkcnMpfSwKKwogCS8qIFRlcm1pbmF0aW5nIGVudHJ5IC0t
LSBNVVNUIEJFIExBU1QgKi8KIAl7TlVMTCwgTlVMTCwgTlVMTCwgTlVMTCwKIAlOVUxMLCBOVUxM
LCAwfQpAQCAtNDAzMCwxMSArNDAzNSwxMSBAQCBrZWVwX2dvaW5nOgkJCQkJCS8qIFdlIHdpbGwg
Y29tZSBiYWNrIHRvIGhlcmUgdW50aWwgdGhlcmUgaXMKIAkJCQkJCWNvbm4tPnN0YXR1cyA9IENP
Tk5FQ1RJT05fT0s7CiAJCQkJCQlzZW5kVGVybWluYXRlQ29ubihjb25uKTsKIAotCQkJCQkJLyoK
LQkJCQkJCSAqIFRyeSBuZXh0IGhvc3QgaWYgYW55LCBidXQgd2UgZG9uJ3Qgd2FudCB0byBjb25z
aWRlcgotCQkJCQkJICogYWRkaXRpb25hbCBhZGRyZXNzZXMgZm9yIHRoaXMgaG9zdC4KLQkJCQkJ
CSAqLwotCQkJCQkJY29ubi0+dHJ5X25leHRfaG9zdCA9IHRydWU7CisJCQkJCQlpZiAoY29ubi0+
Y2hlY2tfYWxsX2FkZHJzICYmIGNvbm4tPmNoZWNrX2FsbF9hZGRyc1swXSA9PSAnMScpCisJCQkJ
CQkJY29ubi0+dHJ5X25leHRfYWRkciA9IHRydWU7CisJCQkJCQllbHNlCisJCQkJCQkJY29ubi0+
dHJ5X25leHRfaG9zdCA9IHRydWU7CisKIAkJCQkJCWdvdG8ga2VlcF9nb2luZzsKIAkJCQkJfQog
CQkJCX0KQEAgLTQwODUsMTEgKzQwOTAsMTEgQEAga2VlcF9nb2luZzoJCQkJCQkvKiBXZSB3aWxs
IGNvbWUgYmFjayB0byBoZXJlIHVudGlsIHRoZXJlIGlzCiAJCQkJCQljb25uLT5zdGF0dXMgPSBD
T05ORUNUSU9OX09LOwogCQkJCQkJc2VuZFRlcm1pbmF0ZUNvbm4oY29ubik7CiAKLQkJCQkJCS8q
Ci0JCQkJCQkgKiBUcnkgbmV4dCBob3N0IGlmIGFueSwgYnV0IHdlIGRvbid0IHdhbnQgdG8gY29u
c2lkZXIKLQkJCQkJCSAqIGFkZGl0aW9uYWwgYWRkcmVzc2VzIGZvciB0aGlzIGhvc3QuCi0JCQkJ
CQkgKi8KLQkJCQkJCWNvbm4tPnRyeV9uZXh0X2hvc3QgPSB0cnVlOworCQkJCQkJaWYgKGNvbm4t
PmNoZWNrX2FsbF9hZGRycyAmJiBjb25uLT5jaGVja19hbGxfYWRkcnNbMF0gPT0gJzEnKQorCQkJ
CQkJCWNvbm4tPnRyeV9uZXh0X2FkZHIgPSB0cnVlOworCQkJCQkJZWxzZQorCQkJCQkJCWNvbm4t
PnRyeV9uZXh0X2hvc3QgPSB0cnVlOworCiAJCQkJCQlnb3RvIGtlZXBfZ29pbmc7CiAJCQkJCX0K
IAkJCQl9CkBAIC00NzAzLDYgKzQ3MDgsNyBAQCBmcmVlUEdjb25uKFBHY29ubiAqY29ubikKIAlm
cmVlKGNvbm4tPnJvd0J1Zik7CiAJZnJlZShjb25uLT50YXJnZXRfc2Vzc2lvbl9hdHRycyk7CiAJ
ZnJlZShjb25uLT5sb2FkX2JhbGFuY2VfaG9zdHMpOworCWZyZWUoY29ubi0+Y2hlY2tfYWxsX2Fk
ZHJzKTsKIAl0ZXJtUFFFeHBCdWZmZXIoJmNvbm4tPmVycm9yTWVzc2FnZSk7CiAJdGVybVBRRXhw
QnVmZmVyKCZjb25uLT53b3JrQnVmZmVyKTsKIApkaWZmIC0tZ2l0IGEvc3JjL2ludGVyZmFjZXMv
bGlicHEvbGlicHEtaW50LmggYi9zcmMvaW50ZXJmYWNlcy9saWJwcS9saWJwcS1pbnQuaAppbmRl
eCAwOGNjMzkxY2JkLi5jNTFlYzI4MjM0IDEwMDY0NAotLS0gYS9zcmMvaW50ZXJmYWNlcy9saWJw
cS9saWJwcS1pbnQuaAorKysgYi9zcmMvaW50ZXJmYWNlcy9saWJwcS9saWJwcS1pbnQuaApAQCAt
NDI3LDYgKzQyNyw3IEBAIHN0cnVjdCBwZ19jb25uCiAJY2hhcgkgICAqdGFyZ2V0X3Nlc3Npb25f
YXR0cnM7CS8qIGRlc2lyZWQgc2Vzc2lvbiBwcm9wZXJ0aWVzICovCiAJY2hhcgkgICAqcmVxdWly
ZV9hdXRoOwkvKiBuYW1lIG9mIHRoZSBleHBlY3RlZCBhdXRoIG1ldGhvZCAqLwogCWNoYXIJICAg
KmxvYWRfYmFsYW5jZV9ob3N0czsgLyogbG9hZCBiYWxhbmNlIG92ZXIgaG9zdHMgKi8KKwljaGFy
ICAgICAgICpjaGVja19hbGxfYWRkcnM7ICAvKiB3aGV0aGVyIHRvIGNoZWNrIGFsbCBpcHMgd2l0
aGluIGEgaG9zdCBvciB0ZXJtaW5hdGUgb24gZmFpbHVyZSAqLwogCiAJYm9vbAkJY2FuY2VsUmVx
dWVzdDsJLyogdHJ1ZSBpZiB0aGlzIGNvbm5lY3Rpb24gaXMgdXNlZCB0byBzZW5kIGEKIAkJCQkJ
CQkJICogY2FuY2VsIHJlcXVlc3QsIGluc3RlYWQgb2YgYmVpbmcgYSBub3JtYWwK
--000000000000b70ecb06275a2048--