agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
6+ messages / 3 participants
[nested] [flat]
* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)
---
src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
1 file changed, 86 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
esac
fix_sql="$fix_sql
DROP FUNCTION IF EXISTS
- public.oldstyle_length(integer, text); -- last in 9.6
+ public.oldstyle_length(integer, text);" # last in 9.6 -- commit 5ded4bd21
+ fix_sql="$fix_sql
DROP FUNCTION IF EXISTS
- public.putenv(text); -- last in v13
- DROP OPERATOR IF EXISTS -- last in v13
- public.#@# (pg_catalog.int8, NONE),
- public.#%# (pg_catalog.int8, NONE),
- public.!=- (pg_catalog.int8, NONE),
+ public.putenv(text);" # last in v13
+ # last in v13 commit 76f412ab3
+ # public.!=- This one is only needed for v11+ ??
+ # Note, until v10, operators could only be dropped one at a time
+ fix_sql="$fix_sql
+ DROP OPERATOR IF EXISTS
+ public.#@# (pg_catalog.int8, NONE);"
+ fix_sql="$fix_sql
+ DROP OPERATOR IF EXISTS
+ public.#%# (pg_catalog.int8, NONE);"
+ fix_sql="$fix_sql
+ DROP OPERATOR IF EXISTS
+ public.!=- (pg_catalog.int8, NONE);"
+ fix_sql="$fix_sql
+ DROP OPERATOR IF EXISTS
public.#@%# (pg_catalog.int8, NONE);"
+
+ # commit 068503c76511cdb0080bab689662a20e86b9c845
+ case $oldpgversion in
+ 10????)
+ fix_sql="$fix_sql
+ DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+ ;;
+ esac
+
+ # commit db3af9feb19f39827e916145f88fa5eca3130cb2
+ case $oldpgversion in
+ 10????)
+ fix_sql="$fix_sql
+ DROP FUNCTION boxarea(box);"
+ fix_sql="$fix_sql
+ DROP FUNCTION funny_dup17();"
+ ;;
+ esac
+
+ # commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+ case $oldpgversion in
+ 10????)
+ fix_sql="$fix_sql
+ DROP TABLE abstime_tbl;"
+ fix_sql="$fix_sql
+ DROP TABLE reltime_tbl;"
+ fix_sql="$fix_sql
+ DROP TABLE tinterval_tbl;"
+ ;;
+ esac
+
+ # Various things removed for v14
+ case $oldpgversion in
+ 906??|10????|11????|12????|13????)
+ fix_sql="$fix_sql
+ DROP AGGREGATE first_el_agg_any(anyelement);"
+ ;;
+ esac
+ case $oldpgversion in
+ 90[56]??|10????|11????|12????|13????)
+ # commit 9e38c2bb5 and 97f73a978
+ # fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+ fix_sql="$fix_sql
+ DROP AGGREGATE array_cat_accum(anyarray);"
+
+ # commit 76f412ab3
+ #fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+ fix_sql="$fix_sql
+ DROP OPERATOR @#@(NONE,bigint);"
+ ;;
+ esac
+
+ # commit 578b22971: OIDS removed in v12
+ case $oldpgversion in
+ 804??|9????|10????|11????)
+ fix_sql="$fix_sql
+ ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+ fix_sql="$fix_sql
+ ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+ #fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+ fix_sql="$fix_sql
+ ALTER TABLE public.emp SET WITHOUT OIDS;"
+ fix_sql="$fix_sql
+ ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+ ;;
+ esac
+
psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
fi
- pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+ echo "fix_sql: $oldpgversion: $fix_sql" >&2
+ pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
if [ "$newsrc" != "$oldsrc" ]; then
# update references to old source tree's regress.so etc
--
2.17.0
--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
* jsonpath: Missing regex_like && starts with Errors?
@ 2024-06-14 16:21 David E. Wheeler <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David E. Wheeler @ 2024-06-14 16:21 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
Hackers,
I noticed that neither `regex_like` nor `starts with`, the jsonpath operators, raise an error when the operand is not a string (or array of strings):
david=# select jsonb_path_query('true', '$ like_regex "^hi"');
jsonb_path_query
------------------
null
(1 row)
david=# select jsonb_path_query('{"x": "hi"}', '$ starts with "^hi"');
jsonb_path_query
------------------
null
(1 row)
This is true in strict and lax mode, and with verbosity enabled (as in these examples). Most other operators raise an error when they can’t operate on the operand:
david=# select jsonb_path_query('{"x": "hi"}', '$.integer()');
ERROR: jsonpath item method .integer() can only be applied to a string or numeric value
david=# select jsonb_path_query('{"x": "hi"}', '$+$');
ERROR: left operand of jsonpath operator + is not a single numeric value
Should `like_regex` and `starts with` adopt this behavior, too?
I note that filter expressions seem to suppress these sorts of errors, but I assume that’s by design:
david=# select jsonb_path_query('{"x": "hi"}', 'strict $ ?(@ starts with "^hi")');
jsonb_path_query
------------------
(0 rows)
david=# select jsonb_path_query('{"x": "hi"}', 'strict $ ?(@ like_regex "^hi")');
jsonb_path_query
------------------
(0 rows)
david=# select jsonb_path_query('{"x": "hi"}', 'strict $ ?(@.integer() == 1)');
jsonb_path_query
------------------
(0 rows)
D
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: jsonpath: Missing regex_like && starts with Errors?
@ 2024-06-15 02:29 Chapman Flack <[email protected]>
parent: David E. Wheeler <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Chapman Flack @ 2024-06-15 02:29 UTC (permalink / raw)
To: David E. Wheeler <[email protected]>; PostgreSQL Hackers <[email protected]>
On 06/14/24 12:21, David E. Wheeler wrote:
> I noticed that neither `regex_like` nor `starts with`, the jsonpath operators, raise an error when the operand is not a string (or array of strings):
>
> david=# select jsonb_path_query('true', '$ like_regex "^hi"');
> jsonb_path_query
> ------------------
> null
> (1 row)
>
> david=# select jsonb_path_query('{"x": "hi"}', '$ starts with "^hi"');
> jsonb_path_query
> ------------------
> null
> (1 row)
To begin with, both of those path queries should have been rejected at
the parsing stage, just like the one David Johnson pointed out:
On 06/13/24 22:14, David G. Johnston wrote:
> On Thursday, June 13, 2024, Chapman Flack <[email protected]> wrote:
>> On 06/13/24 21:46, David G. Johnston wrote:
>>>>> david=# select jsonb_path_query('1', '$ >= 1');
>>>>
>>>> Good point. I can't either. No way I can see to parse that as
>>>> a <JSON path wff>.
>>>
>>> Whether we note it as non-standard or not is an open question then, but
>> it
>>> does work and opens up a documentation question.
All of these are <JSON path predicate> appearing where a <JSON path wff>
is needed, and that's not allowed in the standard. Strictly speaking, the
only place <JSON path predicate> can appear is within <JSON filter expression>.
So I should go look at our code to see what grammar we've implemented,
exactly. It is beginning to seem as if we have simply added
<JSON path predicate> as another choice for an expression, not restricted
to only appearing in a filter. If so, and we add documentation about how
we diverge from the standard, that's probably the way to say it.
On 06/13/24 22:14, David G. Johnston wrote:
> I don’t get why the outcome of a boolean producing operation isn’t just
> generally allowed to be produced
I understand; after all, what is a 'predicate' but another 'boolean
producing operation'? But the committee (at least in this edition) has
stuck us with this clear division in the grammar: there is no
<JSON path wff>, boolean as it may be, that can appear as a
<JSON path predicate>, and there is no <JSON path predicate> that
can appear outside of a filter and be treated as a boolean-valued
expression.
As for the error behavior of a <JSON path predicate> (which strictly,
again, can only appear inside a <JSON filter expression>), the standard
says what seems to be the same thing, in a couple different ways.
In 4.48.5 Overview of SQL/JSON path language, this is said: "The SQL/JSON
path language traps any errors that occur during the evaluation of a
<JSON filter expression>. Depending on the precise <JSON path predicate> ...
the result may be Unknown, True, or False, ...".
Later in 9.46's General Rules where the specific semantics of the
various predicates are laid out, each predicate has rules spelling out
which of Unknown, True, or False results when an error condition is
encountered (usually Unknown, except where something already seen allows
returning True or False). Finally, the <JSON filter expression> itself
collapses the three-valued logic to two; it includes the items for which
the predicate returns True, and excludes them for False or Unknown.
So that's where the errors went.
The question of what should happen to the errors when a
<JSON path predicate> appears outside of a <JSON filter expression>
of course isn't answered in the standard, because that's not supposed
to be possible. So if we're allowing predicates to appear on their own
as expressions, it's also up to us to say what should happen with errors
when they do.
Regards,
-Chap
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: jsonpath: Missing regex_like && starts with Errors?
@ 2024-06-15 03:21 Chapman Flack <[email protected]>
parent: Chapman Flack <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Chapman Flack @ 2024-06-15 03:21 UTC (permalink / raw)
To: David E. Wheeler <[email protected]>; PostgreSQL Hackers <[email protected]>
On 06/14/24 22:29, Chapman Flack wrote:
> So I should go look at our code to see what grammar we've implemented,
> exactly. It is beginning to seem as if we have simply added
> <JSON path predicate> as another choice for an expression, not restricted
> to only appearing in a filter. If so, and we add documentation about how
> we diverge from the standard, that's probably the way to say it.
That's roughly what we've done:
119 result:
120 mode expr_or_predicate {
121 ...
125 }
126 | /* EMPTY */ { *result = NULL; }
127 ;
128
129 expr_or_predicate:
130 expr { $$ = $1; }
131 | predicate { $$ = $1; }
132 ;
Oddly, that's only at the top-level goal production. Your entire JSON
path query we'll allow to be a predicate in lieu of an expr. We still
don't allow a predicate to appear in place of an expr within any other
production.
Regards,
-Chap
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: jsonpath: Missing regex_like && starts with Errors?
@ 2024-06-15 14:47 David E. Wheeler <[email protected]>
parent: Chapman Flack <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: David E. Wheeler @ 2024-06-15 14:47 UTC (permalink / raw)
To: Chapman Flack <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Jun 14, 2024, at 22:29, Chapman Flack <[email protected]> wrote:
> So I should go look at our code to see what grammar we've implemented,
> exactly. It is beginning to seem as if we have simply added
> <JSON path predicate> as another choice for an expression, not restricted
> to only appearing in a filter. If so, and we add documentation about how
> we diverge from the standard, that's probably the way to say it.
Yes, if I understand correctly, these are predicate check expressions, supported and documented as an extension to the standard since Postgres 12[1]. I found their behavior quite confusing for a while, and spent some time figuring it out and submitting a doc patch (committed in 7014c9a[2]) to hopefully clarify things in Postgres 17.
> So that's where the errors went.
Ah, great, that explains the error suppression in filters. Thank you. I still think the supression of `like_regex` and `starts with` errors in predicate path queries is odd, though.
> The question of what should happen to the errors when a
> <JSON path predicate> appears outside of a <JSON filter expression>
> of course isn't answered in the standard, because that's not supposed
> to be possible. So if we're allowing predicates to appear on their own
> as expressions, it's also up to us to say what should happen with errors
> when they do.
Right, and I think there’s an inconsistency right now.
Best,
David
[1]: https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS
[2]: https://github.com/postgres/postgres/commit/7014c9a
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: jsonpath: Missing regex_like && starts with Errors?
@ 2024-06-15 16:23 Chapman Flack <[email protected]>
parent: David E. Wheeler <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Chapman Flack @ 2024-06-15 16:23 UTC (permalink / raw)
To: David E. Wheeler <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 06/15/24 10:47, David E. Wheeler wrote:
> these are predicate check expressions, supported and documented
> as an extension to the standard since Postgres 12[1].
> ...
> [1]: https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS
I see. Yes, that documentation now says "predicate check expressions return
the single three-valued result of the predicate: true, false, or unknown".
(Aside: are all readers of the docs assumed to have learned the habit
of calling SQL null "unknown" when speaking of a boolean? They can flip
back to 8.6 Boolean Type and see 'a third state, “unknown”, which is
represented by the SQL null value'. But would it save them some page
flipping to add " (represented by SQL null)" to the sentence here?)
As Unknown is typically what the predicates return within a filter (where
errors get trapped) when an error has occurred, the existing docs seem to
suggest they behave the same way in a "predicate check expression", so a
change to that behavior now would be a change to what we've documented.
OTOH, getting Unknown because some error occurred is strictly less
information than seeing the error, so perhaps you would want a way
to request non-error-trapping behavior for a "predicate check expression".
Can't really overload jsonb_path_query's 'silent' parameter for that,
because it is already false by default. If predicate check expressions
were nonsilent by default, the existing 'silent' parameter would be a
perfect way to silence them.
No appetite to add yet another optional boolean parameter to
jsonb_path_query for the sole purpose of controlling the silence of
our nonstandard syntax extension ....
Maybe just see the nonstandard syntax extension and raise it another one:
expr_or_predicate
: expr
| predicate
| "nonsilent" '(' predicate ')'
;
or something like that.
Regards,
-Chap
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-15 16:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2024-06-14 16:21 jsonpath: Missing regex_like && starts with Errors? David E. Wheeler <[email protected]>
2024-06-15 02:29 ` Re: jsonpath: Missing regex_like && starts with Errors? Chapman Flack <[email protected]>
2024-06-15 03:21 ` Re: jsonpath: Missing regex_like && starts with Errors? Chapman Flack <[email protected]>
2024-06-15 14:47 ` Re: jsonpath: Missing regex_like && starts with Errors? David E. Wheeler <[email protected]>
2024-06-15 16:23 ` Re: jsonpath: Missing regex_like && starts with Errors? Chapman Flack <[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