agora inbox for [email protected]
help / color / mirror / Atom feedRe: .pgpass's behavior has changed
32+ messages / 7 participants
[nested] [flat]
* Re: .pgpass's behavior has changed
@ 2017-05-01 15:34 Robert Haas <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Robert Haas @ 2017-05-01 15:34 UTC (permalink / raw)
To: Kyotaro HORIGUCHI <[email protected]>; +Cc: pgsql-hackers
On Fri, Apr 28, 2017 at 3:54 AM, Kyotaro HORIGUCHI
<[email protected]> wrote:
> But the above also leaves a bug so I sent another patch to fix
> it. The attched patch restores the 9.6's beavior of looking up
> .pgpass file in the same manner to the aother patch.
Thanks for catching this. Committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: .pgpass's behavior has changed
@ 2017-05-10 04:54 Kyotaro HORIGUCHI <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Kyotaro HORIGUCHI @ 2017-05-10 04:54 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-hackers
At Mon, 1 May 2017 11:34:41 -0400, Robert Haas <[email protected]> wrote in <CA+TgmoZngqbwA_kwgt6yE9ufW53QvbGDQH=ETwxgiCt-sZZpSg@mail.gmail.com>
> On Fri, Apr 28, 2017 at 3:54 AM, Kyotaro HORIGUCHI
> <[email protected]> wrote:
> > But the above also leaves a bug so I sent another patch to fix
> > it. The attched patch restores the 9.6's beavior of looking up
> > .pgpass file in the same manner to the aother patch.
>
> Thanks for catching this. Committed.
Thanks!
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour
@ 2021-01-13 11:05 Bharath Rupireddy <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Bharath Rupireddy @ 2021-01-13 11:05 UTC (permalink / raw)
Currently, in logical replication, publisher/walsender publishes
the tables even though they aren't part of the publication i.e
they are dropped from the publication. Because of this ALTER
PUBLICATION...DROP TABLE doesn't work as expected.
---
src/backend/replication/pgoutput/pgoutput.c | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 2f01137b42..118faf6ce3 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1179,5 +1179,18 @@ rel_sync_cache_publication_cb(Datum arg, int cacheid, uint32 hashvalue)
*/
hash_seq_init(&status, RelationSyncCache);
while ((entry = (RelationSyncEntry *) hash_seq_search(&status)) != NULL)
+ {
entry->replicate_valid = false;
+
+ /*
+ * There might some relations dropped from the publication, we do
+ * not need to publish the changes for them. However, we cannot get
+ * the dropped relations (see above), so we reset pubactions for all
+ * entries.
+ */
+ entry->pubactions.pubinsert = false;
+ entry->pubactions.pubupdate = false;
+ entry->pubactions.pubdelete = false;
+ entry->pubactions.pubtruncate = false;
+ }
}
--
2.30.0
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v3-0002-Invalidate-relation-map-cache-in-subscriber-sysca.patch
^ permalink raw reply [nested|flat] 32+ messages in thread
* [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-11-18 14:25 Yan Chengpeng <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Yan Chengpeng @ 2024-11-18 14:25 UTC (permalink / raw)
To: pgsql-hackers
Dear PostgreSQL Hackers,
Problem Description
I encountered an issue with the B-Tree ordering of `jsonb` values. According to the PostgreSQL documentation[1], the ordering should follow this precedence:
`Object > Array > Boolean > Number > String > Null`
However, empty arrays (`[]`) are currently considered smaller than `null`, which violates the documented rules. This occurs due to improper handling of the `rawScalar` flag when comparing arrays in the `compareJsonbContainers()` function in `src/backend/utils/adt/jsonb_util.c`.
Example to Reproduce the Issue
```sql
postgres=# -- Create a test table with a jsonb column
CREATE TABLE jsonb_test (j jsonb PRIMARY KEY);
-- Insert various jsonb values
INSERT INTO jsonb_test VALUES ('null');
INSERT INTO jsonb_test VALUES ('true');
INSERT INTO jsonb_test VALUES ('false');
INSERT INTO jsonb_test VALUES ('0');
INSERT INTO jsonb_test VALUES ('1');
INSERT INTO jsonb_test VALUES ('"string"');
INSERT INTO jsonb_test VALUES ('[]');
INSERT INTO jsonb_test VALUES ('[1, 2, 3]');
INSERT INTO jsonb_test VALUES ('{}');
INSERT INTO jsonb_test VALUES ('{"a": 1}');
-- Query the table to check ordering
SELECT * FROM jsonb_test ORDER BY j;
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
j
-----------
[]
null
"string"
0
1
false
true
[1, 2, 3]
{}
{"a": 1}
(10 rows)
```
The empty array ([]) is incorrectly placed before null.
Analysis
The issue stems from how the rawScalar flag is evaluated in the compareJsonbContainers() function. When comparing arrays, the function does not prioritize the rawScalar flag before comparing the number of elements (nElems), leading to incorrect ordering for arrays treated as “raw scalars.”
Proposed Fix
The proposed fix ensures the rawScalar flag is checked first, and only when both values have the same flag, the number of elements is compared. This guarantees correct ordering of arrays and scalar values. The details are in the attached patch.
Testing
I have added new test cases to validate the B-Tree ordering of various `jsonb` values, including:
1. Scalars (null, true, false, numbers, strings).
2. Arrays (empty, single-element, nested).
3. Objects (empty, single-key, nested).
4. Mixed types (arrays containing objects, scalars, etc.).
The test cases are included in jsonb.sql and the corresponding expected output file.
I have run make check, and all tests pass successfully.
I would appreciate feedback on the proposed solution or suggestions for improvement.
References
[1] JSON Types - https://www.postgresql.org/docs/current/datatype-json.html
Best regards,
Chengpeng(Jack) Yan
Attachments:
[application/octet-stream] v1-0001-fix-jsonb-compare.patch (5.5K, ../../OSBPR01MB45199DD8DA2D1CECD50518188E272@OSBPR01MB4519.jpnprd01.prod.outlook.com/3-v1-0001-fix-jsonb-compare.patch)
download | inline diff:
From 54ab7c97cf6ccea090b5b69dfc5c00467fcf1b10 Mon Sep 17 00:00:00 2001
From: yanchengpeng <[email protected]>
Date: Mon, 18 Nov 2024 13:48:32 +0000
Subject: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
---
src/backend/utils/adt/jsonb_util.c | 10 ++--
src/test/regress/expected/jsonb.out | 88 +++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 56 ++++++++++++++++++
3 files changed, 150 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9941daad2b..8b29b312d2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -239,14 +239,16 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
case jbvArray:
/*
- * This could be a "raw scalar" pseudo array. That's
+ * This could be a "raw scalar" pseudo array. That's
* a special case here though, since we still want the
- * general type-based comparisons to apply, and as far
- * as we're concerned a pseudo array is just a scalar.
+ * general type-based comparisons to apply. As far
+ * as we're concerned, a pseudo array is just a scalar.
+ * If both are either raw scalars or both are arrays,
+ * compare the number of elements.
*/
if (va.val.array.rawScalar != vb.val.array.rawScalar)
res = (va.val.array.rawScalar) ? -1 : 1;
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems != vb.val.array.nElems)
res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
break;
case jbvObject:
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..1043b8eee1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+ j
+----------------------------------------
+ null
+ ""
+ "A"
+ "a"
+ -3.14159
+ 0
+ 0.0000000001
+ 3.14159
+ 10000000000
+ false
+ true
+ []
+ [null]
+ [0]
+ [false]
+ [true]
+ [[]]
+ [[1, 2], [3, 4]]
+ [{"array": [1, 2]}, {"array": [3, 4]}]
+ ["a", "b", "c"]
+ ["c", "b", "a"]
+ [1, 2, 3]
+ [3, 2, 1]
+ [{"a": 1}, [2, 3], false]
+ [{}, [], null, ""]
+ {}
+ {"a": null}
+ {"a": "a"}
+ {"a": 1}
+ {"a": false}
+ {"a": true}
+ {"a": [1, 2, 3]}
+ {"a": {"nested": true}}
+ {"b": 1}
+ {"nested": {"key": "value"}}
+(35 rows)
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..e2ff06c4a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,59 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
--
2.39.3 (Apple Git-145)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-03 14:11 Andrew Dunstan <[email protected]>
parent: Yan Chengpeng <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Andrew Dunstan @ 2024-12-03 14:11 UTC (permalink / raw)
To: Yan Chengpeng <[email protected]>; pgsql-hackers
On 2024-11-18 Mo 9:25 AM, Yan Chengpeng wrote:
>
> Dear PostgreSQL Hackers,
>
> *Problem Description*
>
> I encountered an issue with the B-Tree ordering of `jsonb` values.
> According to the PostgreSQL documentation[1], the ordering should
> follow this precedence:
>
> `Object > Array > Boolean > Number > String > Null`
>
> However, empty arrays (`[]`) are currently considered smaller than
> `null`, which violates the documented rules. This occurs due to
> improper handling of the `rawScalar` flag when comparing arrays in the
> `compareJsonbContainers()` function in
> `src/backend/utils/adt/jsonb_util.c`.
>
I agree that this is a (10 year old) bug:
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems !=
vb.val.array.nElems)
But I don't think we can fix it, because there could well be indexes
that would no longer be valid if we change the sort order. Given that, I
think the best we can do is adjust the documentation to mention the anomaly.
So the actual sort order as implemented is, AIUI,
Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
which is ugly, but fortunately not many apps rely on jsonb sort order.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-08 14:57 Andrew Dunstan <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Andrew Dunstan @ 2024-12-08 14:57 UTC (permalink / raw)
To: Yan Chengpeng <[email protected]>; pgsql-hackers
On 2024-12-03 Tu 9:11 AM, Andrew Dunstan wrote:
>
>
> On 2024-11-18 Mo 9:25 AM, Yan Chengpeng wrote:
>>
>> Dear PostgreSQL Hackers,
>>
>> *Problem Description*
>>
>> I encountered an issue with the B-Tree ordering of `jsonb` values.
>> According to the PostgreSQL documentation[1], the ordering should
>> follow this precedence:
>>
>> `Object > Array > Boolean > Number > String > Null`
>>
>> However, empty arrays (`[]`) are currently considered smaller than
>> `null`, which violates the documented rules. This occurs due to
>> improper handling of the `rawScalar` flag when comparing arrays in
>> the `compareJsonbContainers()` function in
>> `src/backend/utils/adt/jsonb_util.c`.
>>
>
> I agree that this is a (10 year old) bug:
>
>
> - if (va.val.array.nElems != vb.val.array.nElems)
> + else if (va.val.array.nElems !=
> vb.val.array.nElems)
>
>
> But I don't think we can fix it, because there could well be indexes
> that would no longer be valid if we change the sort order. Given that,
> I think the best we can do is adjust the documentation to mention the
> anomaly.
>
> So the actual sort order as implemented is, AIUI,
>
>
> Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
>
>
> which is ugly, but fortunately not many apps rely on jsonb sort order.
>
Nobody else has commented, so I propose to apply this patch documenting
the anomaly.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
Attachments:
[text/x-patch] jsonb-sort-doc.patch (684B, ../../[email protected]/3-jsonb-sort-doc.patch)
download | inline diff:
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..f314d55422 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -590,6 +590,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
<replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable>
</synopsis>
+ with the exception that (for historical reasons) an empty array sorts less than <replaceable>NULL</replaceable>.
Objects with equal numbers of pairs are compared in the order:
<synopsis>
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ...
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-09 08:56 jian he <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: jian he @ 2024-12-09 08:56 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Yan Chengpeng <[email protected]>; pgsql-hackers
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <[email protected]> wrote:
>
> So the actual sort order as implemented is, AIUI,
>
> Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
>
> which is ugly, but fortunately not many apps rely on jsonb sort order.
>
> Nobody else has commented, so I propose to apply this patch documenting the anomaly.
>
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-09 13:22 Yan Chengpeng <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Yan Chengpeng @ 2024-12-09 13:22 UTC (permalink / raw)
To: jian he <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
If many people are already using this ‘wrong’ behavior. I agree to change the doc. I also think using ‘null’ may be a better choice. Thanks for your comments.
From: jian he <[email protected]>
Date: Monday, December 9, 2024 at 16:56
To: Andrew Dunstan <[email protected]>
Cc: Yan Chengpeng <[email protected]>, [email protected] <[email protected]>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <[email protected]> wrote:
>
> So the actual sort order as implemented is, AIUI,
>
> Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
>
> which is ugly, but fortunately not many apps rely on jsonb sort order.
>
> Nobody else has commented, so I propose to apply this patch documenting the anomaly.
>
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
Attachments:
[application/octet-stream] v1-0001-fix-jsonb-compare.patch (5.5K, ../../OSBPR01MB45195E808FEA422E895C47558E3C2@OSBPR01MB4519.jpnprd01.prod.outlook.com/3-v1-0001-fix-jsonb-compare.patch)
download | inline diff:
From 54ab7c97cf6ccea090b5b69dfc5c00467fcf1b10 Mon Sep 17 00:00:00 2001
From: yanchengpeng <[email protected]>
Date: Mon, 18 Nov 2024 13:48:32 +0000
Subject: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
---
src/backend/utils/adt/jsonb_util.c | 10 ++--
src/test/regress/expected/jsonb.out | 88 +++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 56 ++++++++++++++++++
3 files changed, 150 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9941daad2b..8b29b312d2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -239,14 +239,16 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
case jbvArray:
/*
- * This could be a "raw scalar" pseudo array. That's
+ * This could be a "raw scalar" pseudo array. That's
* a special case here though, since we still want the
- * general type-based comparisons to apply, and as far
- * as we're concerned a pseudo array is just a scalar.
+ * general type-based comparisons to apply. As far
+ * as we're concerned, a pseudo array is just a scalar.
+ * If both are either raw scalars or both are arrays,
+ * compare the number of elements.
*/
if (va.val.array.rawScalar != vb.val.array.rawScalar)
res = (va.val.array.rawScalar) ? -1 : 1;
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems != vb.val.array.nElems)
res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
break;
case jbvObject:
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..1043b8eee1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+ j
+----------------------------------------
+ null
+ ""
+ "A"
+ "a"
+ -3.14159
+ 0
+ 0.0000000001
+ 3.14159
+ 10000000000
+ false
+ true
+ []
+ [null]
+ [0]
+ [false]
+ [true]
+ [[]]
+ [[1, 2], [3, 4]]
+ [{"array": [1, 2]}, {"array": [3, 4]}]
+ ["a", "b", "c"]
+ ["c", "b", "a"]
+ [1, 2, 3]
+ [3, 2, 1]
+ [{"a": 1}, [2, 3], false]
+ [{}, [], null, ""]
+ {}
+ {"a": null}
+ {"a": "a"}
+ {"a": 1}
+ {"a": false}
+ {"a": true}
+ {"a": [1, 2, 3]}
+ {"a": {"nested": true}}
+ {"b": 1}
+ {"nested": {"key": "value"}}
+(35 rows)
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..e2ff06c4a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,59 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
--
2.39.3 (Apple Git-145)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-09 13:27 Yan Chengpeng <[email protected]>
parent: Yan Chengpeng <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Yan Chengpeng @ 2024-12-09 13:27 UTC (permalink / raw)
To: jian he <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
From: Yan Chengpeng <[email protected]>
Date: Monday, December 9, 2024 at 21:22
To: jian he <[email protected]>, Andrew Dunstan <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
If many people are already using this ‘wrong’ behavior. I agree to change the doc. I also think using ‘null’ may be a better choice. Thanks for your comments.
From: jian he <[email protected]>
Date: Monday, December 9, 2024 at 16:56
To: Andrew Dunstan <[email protected]>
Cc: Yan Chengpeng <[email protected]>, [email protected] <[email protected]>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <[email protected]> wrote:
>
> So the actual sort order as implemented is, AIUI,
>
> Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
>
> which is ugly, but fortunately not many apps rely on jsonb sort order.
>
> Nobody else has commented, so I propose to apply this patch documenting the anomaly.
>
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
Attachments:
[application/octet-stream] json-doc-update.patch (1.0K, ../../OSBPR01MB451905E6F431F7D68515A0C48E3C2@OSBPR01MB4519.jpnprd01.prod.outlook.com/3-json-doc-update.patch)
download | inline diff:
commit 6f4c911aa98b7ced0b8e93d36e222531cd4f1c87
Author: yanchengpeng <[email protected]>
Date: Mon Dec 9 12:59:28 2024 +0000
docs: Clarify JSONB sorting for empty arrays
Added a note to the JSONB documentation explaining that empty arrays sort less than null, due to historical reasons. This provides better clarity on JSONB sorting rules.
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..2870df34d8 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -590,6 +590,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
<replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable>
</synopsis>
+ with the exception that (for historical reasons) an empty array sorts less than <replaceable>null</replaceable>.
Objects with equal numbers of pairs are compared in the order:
<synopsis>
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ...
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2024-12-09 16:16 jian he <[email protected]>
parent: Yan Chengpeng <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: jian he @ 2024-12-09 16:16 UTC (permalink / raw)
To: Yan Chengpeng <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; pgsql-hackers
On Mon, Dec 9, 2024 at 9:27 PM Yan Chengpeng <[email protected]> wrote:
>
> Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
>
>
sorry. maybe i didn't mention it explicitly.
i mean something like:
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..d9b24e413e 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE
jdoc @@ '$.tags[*] == "qui"';
The <literal>btree</literal> ordering for <type>jsonb</type>
datums is seldom
of great interest, but for completeness it is:
<synopsis>
-<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
> <replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
> <replaceable>Null</replaceable>
+<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
> <replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
> <replaceable>null</replaceable>
<replaceable>Object with n pairs</replaceable> > <replaceable>object
with n - 1 pairs</replaceable>
<replaceable>Array with n elements</replaceable> > <replaceable>array
with n - 1 elements</replaceable>
</synopsis>
+ with the exception that (for historical reasons) an empty array
sorts less than <replaceable>null</replaceable>.
Objects with equal numbers of pairs are compared in the order:
<synopsis>
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>,
<replaceable>key-2</replaceable> ...
Attachments:
[text/x-patch] v2_json_doc_update.diff (1.3K, ../../CACJufxEeBcyvzVE=O6q1eRZ4fE6RUFAQOXnH=KgsfnTKPUS5mQ@mail.gmail.com/2-v2_json_doc_update.diff)
download | inline diff:
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..d9b24e413e 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
The <literal>btree</literal> ordering for <type>jsonb</type> datums is seldom
of great interest, but for completeness it is:
<synopsis>
-<replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>Null</replaceable>
+<replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>null</replaceable>
<replaceable>Object with n pairs</replaceable> > <replaceable>object with n - 1 pairs</replaceable>
<replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable>
</synopsis>
+ with the exception that (for historical reasons) an empty array sorts less than <replaceable>null</replaceable>.
Objects with equal numbers of pairs are compared in the order:
<synopsis>
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ...
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2025-01-03 15:44 Andrew Dunstan <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Andrew Dunstan @ 2025-01-03 15:44 UTC (permalink / raw)
To: jian he <[email protected]>; Yan Chengpeng <[email protected]>; +Cc: pgsql-hackers
On 2024-12-09 Mo 11:16 AM, jian he wrote:
> On Mon, Dec 9, 2024 at 9:27 PM Yan Chengpeng <[email protected]> wrote:
>> Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
>>
>>
> sorry. maybe i didn't mention it explicitly.
> i mean something like:
>
> diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
> index 54648c459c..d9b24e413e 100644
> --- a/doc/src/sgml/json.sgml
> +++ b/doc/src/sgml/json.sgml
> @@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE
> jdoc @@ '$.tags[*] == "qui"';
> The <literal>btree</literal> ordering for <type>jsonb</type>
> datums is seldom
> of great interest, but for completeness it is:
> <synopsis>
> -<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
>> <replaceable>Boolean</replaceable> >
> <replaceable>Number</replaceable> > <replaceable>String</replaceable>
>> <replaceable>Null</replaceable>
> +<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
>> <replaceable>Boolean</replaceable> >
> <replaceable>Number</replaceable> > <replaceable>String</replaceable>
>> <replaceable>null</replaceable>
> <replaceable>Object with n pairs</replaceable> > <replaceable>object
> with n - 1 pairs</replaceable>
>
> <replaceable>Array with n elements</replaceable> > <replaceable>array
> with n - 1 elements</replaceable>
> </synopsis>
> + with the exception that (for historical reasons) an empty array
> sorts less than <replaceable>null</replaceable>.
> Objects with equal numbers of pairs are compared in the order:
> <synopsis>
> <replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>,
> <replaceable>key-2</replaceable> ...
Pushed something along these lines. In master I also added a code
comment so nobody might be tempted to "fix" the anomaly.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
@ 2025-01-18 18:17 Tom Lane <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Tom Lane @ 2025-01-18 18:17 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: jian he <[email protected]>; Yan Chengpeng <[email protected]>; pgsql-hackers
Andrew Dunstan <[email protected]> writes:
> Pushed something along these lines. In master I also added a code
> comment so nobody might be tempted to "fix" the anomaly.
There is still a commitfest entry [1] pointing at this thread.
Should it be closed as committed, or is there more to do?
regards, tom lane
[1] https://commitfest.postgresql.org/51/5394/
^ permalink raw reply [nested|flat] 32+ messages in thread
end of thread, other threads:[~2025-01-18 18:17 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-01 15:34 Re: .pgpass's behavior has changed Robert Haas <[email protected]>
2017-05-10 04:54 ` Kyotaro HORIGUCHI <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2021-01-13 11:05 [PATCH v3 1/2] Fix ALTER PUBLICATION...DROP TABLE behaviour Bharath Rupireddy <[email protected]>
2024-11-18 14:25 [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Yan Chengpeng <[email protected]>
2024-12-03 14:11 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Andrew Dunstan <[email protected]>
2024-12-08 14:57 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Andrew Dunstan <[email protected]>
2024-12-09 08:56 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays jian he <[email protected]>
2024-12-09 13:22 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Yan Chengpeng <[email protected]>
2024-12-09 13:27 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Yan Chengpeng <[email protected]>
2024-12-09 16:16 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays jian he <[email protected]>
2025-01-03 15:44 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Andrew Dunstan <[email protected]>
2025-01-18 18:17 ` Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox