Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

2024-02-27 Thread Dean Rasheed
quired projection (I think). Attached is a very rough patch. It seemed better to build the projection in the executor rather than the planner, since then the extra work can be avoided, if EPQ is not invoked. It seems to work (it passes the isolation tests, and I couldn't break it in ad hoc t

Re: Functions to return random numbers in a given range

2024-02-27 Thread Dean Rasheed
On Sat, 24 Feb 2024 at 17:10, Tomas Vondra wrote: > > Hi Dean, > > I did a quick review and a little bit of testing on the patch today. I > think it's a good/useful idea, and I think the code is ready to go (the > code is certainly much cleaner than anything I'd

Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

2024-02-28 Thread Dean Rasheed
haven't been able to break it, but I'm still nervous that I might be overlooking something, particularly in relation to what might get added to the targetlist at various stages during planning for different types of query. Regards, Dean

Re: Supporting MERGE on updatable views

2024-02-29 Thread Dean Rasheed
On Thu, 29 Feb 2024 at 09:50, Alvaro Herrera wrote: > > By all means let's get the feature out there. It's not a frequently > requested thing but it does seem to come up. > Pushed. Thanks for reviewing. Regards, Dean

Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).

2024-03-04 Thread Dean Rasheed
rable PKs, so we still want the throwaway NOT NULL constraints in that case, otherwise we'd be hurting performance of restore. Regards, Dean

Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

2024-03-05 Thread Dean Rasheed
propagate changes to other rows."))); return false; My initial reaction is that neither of those blocks of code is entirely correct, and that they should both be doing both of those checks. I.e., something like the attached (which probably needs some additional test cases). Reg

Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).

2024-03-05 Thread Dean Rasheed
dexList() by b0e96f311985 need reverting. Regards, Dean

Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases

2024-03-05 Thread Dean Rasheed
sure how useful these changes are, but I don't really object. You need to update the synopsis section of the docs though. Regards, Dean

Re: Proposal to include --exclude-extension Flag in pg_dump

2024-03-06 Thread Dean Rasheed
d be extended to support "exclude extension pattern" lines in the filter file. That syntax is already accepted, but it throws a not-supported error, but it's hopefully not too hard to make that work now. 2). It ought to have some tests in the test script. Regards, Dean

Re: MERGE ... RETURNING

2024-03-06 Thread Dean Rasheed
some special-case code during parse analysis to turn them into MergeFunc nodes (not quite a complete revert back to an earlier version of the patch, but not far off). Regards, Dean [1] https://www.postgresql.org/message-id/flat/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50agoe+pv...@mail.gmail.com

Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

2024-03-07 Thread Dean Rasheed
On Tue, 5 Mar 2024 at 13:55, Dean Rasheed wrote: > > > If I only execute merge , I will get the following error: > > merge into tgt a using src1 c on a.a = c.a when matched then update > > set b = c.b when not matched then insert (a,b) values(c.a,c.b); -- excute &

Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).

2024-03-07 Thread Dean Rasheed
's the same thing. So can we get away with making sure that RelationGetIndexList() doesn't set relation->rd_replidindex to a deferrable PK, while still allowing relation->rd_pkindex to be one? Regards, Dean

Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases

2024-03-08 Thread Dean Rasheed
mean it should reindex each listed object as well as the system > catalogs. OK, that looks useful, especially given that most people will still probably use this against a single database, and it's making that more flexible. I think this is good to go. Regards, Dean

Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).

2024-03-08 Thread Dean Rasheed
hould probably be reset in load_relcache_init_file(), alongside rd_pkindex, though I don't think it can matter in practice. Regards, Dean

Re: Improving EXPLAIN's display of SubPlan nodes

2024-03-09 Thread Dean Rasheed
uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) but I think that's useful extra clarification. Regards, Dean diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out new file mode 100644 index c355e8f..f0ff936 --- a

Broken EXPLAIN output for SubPlan in MERGE

2024-03-12 Thread Dean Rasheed
ource relation will be INNER_VAR references to the targetlist of the join node immediately under the ModifyTable node. So I think we want to do something like the attached. Regards, Dean diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c new file mode 100644 index 2

Re: MERGE ... RETURNING

2024-03-13 Thread Dean Rasheed
te.sgml) I think I'll go make those doc changes, and back-patch them separately, since they're not related to this patch. Regards, Dean

Re: MERGE ... RETURNING

2024-03-13 Thread Dean Rasheed
On Wed, 13 Mar 2024 at 08:58, Dean Rasheed wrote: > > I think I'll go make those doc changes, and back-patch them > separately, since they're not related to this patch. > OK, I've done that. Here is a rebased patch on top of that, with the other changes you suggested.

Re: MERGE ... WHEN NOT MATCHED BY SOURCE

2024-03-13 Thread Dean Rasheed
Rebased version attached. Regards, Dean diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index f8f83d4..380d0c9 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -394,10 +394,14 @@ conditions for each action are re-evaluated on the updated version

MERGE ... WHEN NOT MATCHED BY SOURCE

2022-12-30 Thread Dean Rasheed
ached is a WIP patch. I haven't updated the docs yet, and there are probably a few other things to tidy up and test, but the basic functionality is there. Regards, Dean diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c new file mode 100644 ind

Bug in check for unreachable MERGE WHEN clauses

2023-01-02 Thread Dean Rasheed
Re-reading my latest MERGE patch, I realised there is a trivial, pre-existing bug in the check for unreachable WHEN clauses, which means it won't spot an unreachable WHEN clause if it doesn't have an AND condition. So the checks need to be re-ordered, as in the attached. Regards, Dean

Re: MERGE ... WHEN NOT MATCHED BY SOURCE

2023-01-02 Thread Dean Rasheed
On Fri, 30 Dec 2022 at 16:56, Dean Rasheed wrote: > > Attached is a WIP patch. > Updated patch attached, now with updated docs and some other minor tidying up. Regards, Dean diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index b87ad5c..1482ede ---

Re: Underscores in numeric literals

2023-01-04 Thread Dean Rasheed
all responsibility for parsing underscores and non-decimal integers to the datatype input functions, or their support routines, rather than having it distributed. Regards, Dean diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml new file mode 100644 index 956182e..27e53b4 --- a/doc/src/

Re: Underscores in numeric literals

2023-01-04 Thread Dean Rasheed
Oh, one other minor nit -- in parser/scan.l: -real ({decinteger}|{numeric})[Ee][-+]?{decdigit}+ +real ({decinteger}|{numeric})[Ee][-+]?{decinteger}+ the final "+" isn't necessary now. Regards, Dean

Re: Improve tab completion for ALTER FUNCTION/PROCEDURE/ROUTINE

2023-01-05 Thread Dean Rasheed
case offers "FROM CURRENT" and "TO" as completions, which is incorrect. It should really offer to complete with a list of schemas. However, since that's a pre-existing bug in a different region of the code, I think it's best addressed in a separate patch, which proba

Re: MERGE ... WHEN NOT MATCHED BY SOURCE

2023-01-05 Thread Dean Rasheed
d > mergeUseOuterJoin instead. This new code is certainly not the same, but > it makes me a bit unconfortable. Maybe it's OK, though. > Yeah I agree, it's a bit ugly. Perhaps a better solution would be to do away with that field entirely and just make the decision in transform_MERGE_to_join() by examining the action list again. That would require making MergeAction's "matched" field a MergeMatchKind rather than a bool, but maybe that's not so bad, since retaining that information might prove useful one day. Regards, Dean

Re: Improve tab completion for ALTER FUNCTION/PROCEDURE/ROUTINE

2023-01-06 Thread Dean Rasheed
On Fri, 6 Jan 2023 at 02:38, vignesh C wrote: > > On Thu, 5 Jan 2023 at 18:22, Dean Rasheed wrote: > > > > That leads to the attached, which barring objections, I'll push shortly. > > The changes look good to me. > Pushed. Regards, Dean

Re: Improve tab completion for ALTER FUNCTION/PROCEDURE/ROUTINE

2023-01-06 Thread Dean Rasheed
On Thu, 5 Jan 2023 at 12:52, Dean Rasheed wrote: > > While playing around with this, I noticed that the "... SET SCHEMA" > case offers "FROM CURRENT" and "TO" as completions, which is > incorrect. It should really offer to complete with a list of schem

Re: [PATCH] psql: Add tab-complete for optional view parameters

2023-01-06 Thread Dean Rasheed
th create view and > alter view. > Hmm, I don't think we should be offering "check_option" as a tab completion for CREATE VIEW at all, since that would encourage users to use non-SQL-standard syntax, rather than CREATE VIEW ... WITH [CASCADED|LOCAL] CHECK OPTION. Regards, Dean

Re: add \dpS to psql

2023-01-06 Thread Dean Rasheed
o be on the user's search_path. So I think we should use the same SQL clauses as every other psql command that supports "S", namely: if (!showSystem && !pattern) appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"

Re: add \dpS to psql

2023-01-07 Thread Dean Rasheed
On Sat, 7 Jan 2023 at 00:36, Nathan Bossart wrote: > > On Fri, Jan 06, 2023 at 06:52:33PM +, Dean Rasheed wrote: > > > > So I think we should use the same SQL clauses as every other psql > > command that supports "S", namely: >

Re: MERGE ... WHEN NOT MATCHED BY SOURCE

2023-01-07 Thread Dean Rasheed
On Thu, 5 Jan 2023 at 13:21, Dean Rasheed wrote: > > On Thu, 5 Jan 2023 at 11:03, Alvaro Herrera wrote: > > > > > + /* Join type required */ > > > + if (left_join && right_join) > > > + qry->mergeJoinType = JOIN_FULL; >

MERGE ... RETURNING

2023-01-08 Thread Dean Rasheed
xisting terminology used in the docs. Anyway, this feels like a good point to stop playing around and get feedback on whether this seems useful, or if anyone has other ideas. Regards, Dean diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml new file mode 100644 index 0995fe0..4fc0a6

Re: [PATCH] random_normal function

2023-01-09 Thread Dean Rasheed
that file. I think it would also make sense to add a new "Random Functions" section to the docs, and move the descriptions of random(), random_normal() and setseed() there. That way, all the functions affected by setseed() can be kept together on one page (future random functions may not a

Re: MERGE ... RETURNING

2023-01-09 Thread Dean Rasheed
d a bare "*" in the >> returning list expands to all the source columns, followed by all the >> target columns. > > Does this lead to a problem in the event there are same-named columns between > source and target? > Not really. It's exactly the same as doing "SELECT * FROM src JOIN tgt ON ...". That may lead to duplicate column names in the result, but that's not necessarily a problem. Regards, Dean

Re: [PATCH] random_normal function

2023-01-09 Thread Dean Rasheed
On Mon, 9 Jan 2023 at 15:26, Tom Lane wrote: > > Dean Rasheed writes: > > So IMO all pseudorandom functions should share the same PRNG state and > > seed-setting functions. That would mean they should all be in the same > > (new) C file, so that the PRNG state can be k

Re: MERGE ... RETURNING

2023-01-09 Thread Dean Rasheed
that's worth it, just to have a more standard-looking RETURNING list, without a WITH clause. Regards, Dean

Re: [PATCH] random_normal function

2023-01-09 Thread Dean Rasheed
g the > random test to be run in parallel with others. I didn't check the one-in-a-billion claim, but +1 for that. Regards, Dean

Re: [PATCH] random_normal function

2023-01-10 Thread Dean Rasheed
-ASCII character instead of a hyphen in "Kolmogorov-Smirnov" (because I copy-pasted the name from some random website). Regards, Dean

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

2023-01-10 Thread Dean Rasheed
I'm somewhat surprised that a case as simple as this wasn't covered by any pre-existing regression tests. Regards, Dean

Re: [PATCH] random_normal function

2023-01-10 Thread Dean Rasheed
On Tue, 10 Jan 2023 at 08:33, Dean Rasheed wrote: > > The rest looks good to me, except there's a random non-ASCII character > instead of a hyphen in "Kolmogorov-Smirnov" (because I copy-pasted the > name from some random website). > Oh, never mind. I see you alrea

Re: [PATCH]Feature improvement for MERGE tab completion

2023-01-10 Thread Dean Rasheed
e.: /* Complete MERGE INTO ... ON with target table attributes */ else if (TailMatches("MERGE", "INTO", MatchAny, "USING", MatchAny, "ON")) COMPLETE_WITH_ATTR(prev4_wd); else if (TailMatches("MERGE", "INTO", MatchAny, "

Re: Bug in check for unreachable MERGE WHEN clauses

2023-01-10 Thread Dean Rasheed
On Mon, 2 Jan 2023 at 12:13, Dean Rasheed wrote: > > Re-reading my latest MERGE patch, I realised there is a trivial, > pre-existing bug in the check for unreachable WHEN clauses, which > means it won't spot an unreachable WHEN clause if it doesn't have an > AND conditio

Re: MERGE ... WHEN NOT MATCHED BY SOURCE

2023-01-10 Thread Dean Rasheed
Rebased version attached. Regards, Dean diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index b87ad5c..1482ede --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -396,8 +396,8 @@ originally matched appears later in the list of actions. On the

Re: Non-decimal integer literals

2023-01-13 Thread Dean Rasheed
ntirely for regular numbers that aren't NaN or Inf. Testing that with COPY with a few million numbers of different sizes, I observed a 10-15% performance increase. So I'm feeling quite good about the end result -- I set out hoping not to make performance noticeab

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

2023-01-16 Thread Dean Rasheed
) FROM generate_series(1,3) s(a); string_agg | string_agg + 2,1,3 | 3,2,1 (1 row) so pre-sorting wouldn't be right (or at least it would change existing behaviour in a surprising way). Regards, Dean

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

2023-01-18 Thread Dean Rasheed
's pretty counterintuitive that adding an ORDER BY clause changes the contents of the rows returned, not just their order. The trouble is, if we tried to fix that, we'd risk changing some other behaviour that users may have come to rely on. Regards, Dean

Re: random() function documentation

2022-04-11 Thread Dean Rasheed
in a whole lot of places. Others might see it differently though. Agreed, though I think when the release notes are written, they ought to warn that the sequence will change with this release. Regards, Dean

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-18 Thread Dean Rasheed
they were distance 0 away from anything else, which was larger than all the other negative distances! But yes, that needed fixing properly. Thanks for taking care of this. Regards, Dean

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-18 Thread Dean Rasheed
e happens before the addition. Of course a side effect of internally casting first is that you can't do things like this: select '5874897-01-01'::date - '5872897 years'::interval; ERROR: date out of range for timestamp which arguably ought to return '2000-01-01 00:00:00'. In practice though, I think it would be far more trouble than it's worth trying to change that. Regards, Dean

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-19 Thread Dean Rasheed
you describe it seems we will store first 32 values as > is, but later as the number of values grow create ranges from those? > Please point me to the relevant source code/documentation. Anyway, if > we can reduce the number of rows we insert, that will be good. > I don't think 60 values is excessive, but instead of listing them out by hand, perhaps use generate_series(). Regards, Dean

Re: Bug: RLS policy FOR SELECT is used to check new rows

2023-10-24 Thread Dean Rasheed
ttempt to return those new values. That would be consistent with what we do for INSERT. Note, that the current behaviour goes back a long way, though it's not quite clear whether this was intentional [1]. [1] https://github.com/postgres/postgres/commit/7d8db3e8f37aec9d252353904e77381a18a2fa9f Regards, Dean

Re: Infinite Interval

2023-10-27 Thread Dean Rasheed
cing a check on every input value, just to cater for this one specific highly unlikely input. In fact, both these alternative approaches (rejecting the value, or emitting a warning), would impose a small performance penalty on every interval input, which I don't think is really worth it. So overall, my preference is to just accept it. Anything else is more work, for no practical benefit. Regards, Dean

btree_gin: Incorrect leftmost interval value

2023-10-27 Thread Dean Rasheed
SELECT * FROM foo WHERE a < '1 year'; a --- (0 rows) Attached is a patch fixing this by setting all the fields to their minimum values, which is guaranteed to be less than any other interval. Note that this doesn't affect the contents of the index itself, so reindexing is not nec

Re: btree_gin: Incorrect leftmost interval value

2023-10-27 Thread Dean Rasheed
ake sense in back-branches that don't have infinite intervals. Regards, Dean

Re: MERGE ... RETURNING

2023-10-27 Thread Dean Rasheed
r RETURNING lists. If we see that, we could automatically apply it to all actions, which I think would be much more convenient in situations where you don't care about the action executed, and just want the results from the table. That would go a long way towards addressing my usability conce

Re: Supporting MERGE on updatable views

2023-10-29 Thread Dean Rasheed
" attribute is, which can vary. "datum" is then set to the value of the "wholerow" attribute, which is the OLD tuple, so using DatumGetHeapTupleHeader() makes sense. This relies on the code in ExecInitModifyTable(), which sets up ri_RowIdAttNo. Regards, Dean

Re: MERGE ... RETURNING

2023-11-01 Thread Dean Rasheed
that might be harder, if we invent our own syntax now. On the implementation question, I'm not completely against the idea of a MergeFunc node, but it does feel a little over-engineered. Regards, Dean

Re: MERGE ... RETURNING

2023-11-05 Thread Dean Rasheed
function (which isn't actually a function anymore). There's one bug that I know about, to do with cross-partition updates, but since that's a pre-existing bug, I'll start a new thread for it. Regards, Dean diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml new file mode 1

MERGE: AFTER ROW trigger failure for cross-partition update

2023-11-07 Thread Dean Rasheed
I didn't really look into. This patch will stop any of that too.) Regards, Dean diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c new file mode 100644 index 299c2c7..b16fbe9 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nod

64-bit integer subtraction bug on some platforms

2023-11-08 Thread Dean Rasheed
/* to avoid spurious warnings */ return true; } *result = a - b; return false; which fails to spot the fact that overflow is also possible when a == 0. So on such platforms, it returns the wrong result. Patch attached. Regards, Dean diff --git a/src/include/common/int.h b/src/incl

Re: Infinite Interval

2023-11-08 Thread Dean Rasheed
On Wed, 8 Nov 2023 at 06:56, jian he wrote: > > > On Tue, 7 Nov 2023 at 14:33, Dean Rasheed wrote: > > > > Ah, Windows Server didn't like that. Trying again with "INT64CONST(0)" > > instead of just "0" in interval_um(). > > > I found

Re: Infinite Interval

2023-11-09 Thread Dean Rasheed
he previous patch set. > Thanks. That's confirmed, it has indeed turned green! Regards, Dean

Re: MERGE ... RETURNING

2023-11-09 Thread Dean Rasheed
On Sun, 5 Nov 2023 at 11:52, Dean Rasheed wrote: > > OK, that's a fair point. Attached is a new version, replacing those > parts of the implementation with a new MergingFunc node. It doesn't > add that much more complexity, and I think the new code is much > neater. &g

Re: Bug: RLS policy FOR SELECT is used to check new rows

2023-11-09 Thread Dean Rasheed
ew row, but then if you tried to SELECT it later, the USING part of the policy might say no. That seems pretty confusing. I would expect a row to either be visible or not, consistently across all commands. Regards, Dean

Re: Bug: RLS policy FOR SELECT is used to check new rows

2023-11-10 Thread Dean Rasheed
new rows for that table, and whose default matched the current behaviour. Before going too far down that route though, it is perhaps worth asking whether this is something users really want. Is there a real use-case for being able to UPDATE rows and have them disappear? Regards, Dean

Re: Infinite Interval

2023-11-14 Thread Dean Rasheed
On Thu, 9 Nov 2023 at 12:49, Dean Rasheed wrote: > > OK, I have pushed 0001 and 0002. Here's the remaining (main) patch. > OK, I have now pushed the main patch. Regards, Dean

Re: MERGE ... RETURNING

2023-11-15 Thread Dean Rasheed
T, INSERT, UPDATE, DELETE, do we need to add a > MERGE entry in glossary.sgml? Yes, that makes sense. Attached is a separate patch with those doc updates, intended to be applied and back-patched independently of the main RETURNING patch. Regards, Dean merge-docs.patch.no-cfbot Description: Binary data

Re: MERGE ... RETURNING

2023-11-18 Thread Dean Rasheed
lind to grammatical errors in my own text, no matter how many times I read it. Thanks for checking! Pushed. The v13 patch still applies on top of this, so I won't re-post it. Regards, Dean

Re: [PATCH] psql: Add tab-complete for optional view parameters

2023-11-23 Thread Dean Rasheed
k_option" (though there's no point in doing likewise for the boolean options, since they default to true, if present, and false otherwise). Attached is an updated patch, incorporating those comments. Barring any further comments, I think this is ready for commit. Regards, Dean From 2f143cbfe185

Re: [PATCH] psql: Add tab-complete for optional view parameters

2023-11-28 Thread Dean Rasheed
On Tue, 28 Nov 2023 at 03:42, Shubham Khanna wrote: > > I reviewed the given Patch and it is working fine. > Thanks for checking. Patch pushed. Regards, Dean

Adding OLD/NEW support to RETURNING

2023-12-04 Thread Dean Rasheed
eir own name, if they need to. In most contexts, OLD and NEW will work, so they won't need to. Thoughts? Regards, Dean [1] https://www.postgresql.org/message-id/flat/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3...@mail.gmail.com [2] https://www.postgresql.org/message-id/flat/51822C0

Functions to return random numbers in a given range

2023-12-21 Thread Dean Rasheed
ly slow, and messy. The implementation moves the existing random functions to a new source file, so the new functions all share a common PRNG state with the existing random functions, and that state is kept private to that file. Regards, Dean From 0b7015668387c337114adb4b3c24fe1d8053bf9c Mon Sep 17

Re: psql JSON output format

2024-01-08 Thread Dean Rasheed
umber types, json, and jsonb must not be quoted and escaped, since that would change them to strings or double-encode them in the result. And then there are domain types built on top of those types, and arrays, etc. See, for example, the logic in json_categorize_type(). I think that trying to duplicate that client-side is doomed to failure. Regards, Dean

Re: Emitting JSON to file using COPY TO

2024-01-08 Thread Dean Rasheed
TO stdout WITH (format json); both of those generate the following: ERROR: record type has not been registered Regards, Dean

Re: psql JSON output format

2024-01-09 Thread Dean Rasheed
ual fields, but it's not round-trip safe. OTOH, this patch outputs the Postgres string representation of the object, which might be round-trip safe, but is not very convenient for any other tool to read. Regards, Dean

Re: psql JSON output format

2024-01-09 Thread Dean Rasheed
d back into PG. > Reassembling all the json parts back into proper values would be a > pretty hard problem. > What is your use case? It seems like what you want is quite different from the COPY patch. Regards, Dean

Re: Infinite Interval

2023-09-12 Thread Dean Rasheed
1 | 2. 3 | Infinity Infinity | Infinity 4 | 5. 6 | 6. (5 rows) Regards, Dean

Re: Infinite Interval

2023-09-18 Thread Dean Rasheed
On Wed, 13 Sept 2023 at 11:13, Ashutosh Bapat wrote: > > On Tue, Sep 12, 2023 at 2:39 PM Dean Rasheed wrote: > > > > and it looks like the infinite interval > > input code is broken. > > The code required to handle 'infinit

Re: CHECK Constraint Deferrable

2023-09-19 Thread Dean Rasheed
e logical to put them immediately before the valid/invalid entries, to match the order of constraint properties in pg_constraint, and functions like CreateConstraintEntry(). Regards, Dean

Re: Infinite Interval

2023-09-19 Thread Dean Rasheed
llel workers. That makes a big difference to queryE, if the size of the test data is scaled up. This comment: + int64 N; /* count of processed numbers */ should be "count of processed intervals". Regards, Dean

Re: Infinite Interval

2023-09-20 Thread Dean Rasheed
touching state1->N. And, as in do_interval_accum(), there is no need to switch memory context. Given that there are multiple places in this file that need to add intervals, I think it makes sense to further refactor, and add a local function to add 2 finite intervals, along the lines of the code above. This can then be called from do_interval_accum(), interval_avg_combine(), and interval_pl(). And similarly for subtracting 2 finite intervals. Regards, Dean

Re: Infinite Interval

2023-09-20 Thread Dean Rasheed
On Wed, 20 Sept 2023 at 13:09, Dean Rasheed wrote: > > So basically, do_interval_accum() could be simplified to: > Oh, and I guess it also needs an INTERVAL_NOT_FINITE() check, to make sure that finite values don't sum to our representation of infinity, as in interval_pl(). Regards, Dean

Re: Infinite Interval

2023-09-20 Thread Dean Rasheed
ble for some other reasons, but I think we may as well do the same refactoring for interval_mi() as we're doing for interval_pl() -- i.e., introduce a finite_interval_mi() function, making the addition and subtraction code match, and removing the need for neg_val in do_interval_discard(). Regards, Dean

Re: Infinite Interval

2023-09-22 Thread Dean Rasheed
text. +1. And consistency with other deserialisation functions is good. > Rest of the patches are > same as previous set. > OK, I'll take a look. Regards, Dean

Re: Making the subquery alias optional in the FROM clause

2023-10-02 Thread Dean Rasheed
On Mon, 2 Oct 2023 at 01:01, Tom Lane wrote: > > Erwin Brandstetter writes: > > On Mon, 2 Oct 2023 at 00:33, Dean Rasheed wrote: > >> The only place that exposes the eref's made-up relation name is the > >> existing query deparsing code in ruleutils.c, which u

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-13 Thread Dean Rasheed
brin_minmax_multi_distance_int8(). IOW, I think brin_minmax_multi_distance_date/timestamp could be made basically identical to brin_minmax_multi_distance_int4/8. Regards, Dean

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-13 Thread Dean Rasheed
On Fri, 13 Oct 2023 at 11:44, Tomas Vondra wrote: > > On 10/13/23 11:21, Dean Rasheed wrote: > > > > Is this only inefficient? Or can it also lead to wrong query results? > > I don't think it can produce incorrect results. It only affects which > values we "

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

2023-10-13 Thread Dean Rasheed
f overflow, and it'll handle "infinite" intervals just fine, when that feature gets added. Regards, Dean [1] https://www.postgresql.org/message-id/CAExHW5u1JE7dxK=wlzqhcszntoxqzjdiermhrepw6r8w6kc...@mail.gmail.com

Re: Additional improvements to extended statistics

2020-03-11 Thread Dean Rasheed
On Mon, 9 Mar 2020 at 18:19, Tomas Vondra wrote:> > On Mon, Mar 09, 2020 at 08:35:48AM +, Dean Rasheed wrote: > > > > P(a,b) = P(a) * [f + (1-f)*P(b)] > > > >because it might return a value that is larger that P(b), which > >obviously should not be po

Re: PATCH: add support for IN and @> in functional-dependency statistics use

2020-03-12 Thread Dean Rasheed
m dependency_is_compatible_clause(), other than the requirement that the clause refer to a single variable. Regards, Dean [1] https://www.postgresql.org/message-id/CAEZATCXaNFZyOhR4XXAfkvj1tibRBEjje6ZbXwqWUB_tqbH%3Drw%40mail.gmail.com [2] https://www.postgresql.org/message-id/20200309181915.5lx

Re: PATCH: add support for IN and @> in functional-dependency statistics use

2020-03-13 Thread Dean Rasheed
uce a poor estimate. Ultimately, it's up to the user to decide whether or not to build functional dependency statistics, and that decision needs to be based not just on the data distribution, but also on the types of queries expected. Given the timing though, perhaps it is best to limit this to IN (..) clauses for PG13, and we can consider other possibilities later. Regards, Dean

Re: Additional improvements to extended statistics

2020-03-13 Thread Dean Rasheed
;s1 = s1 + s2 - s1 * s2" formula. Perhaps they could be combined into a new function (clauselist_selectivity_simple_or(), say). I guess it would need to be passed the initial starting selectivity s1, but it ought to help reduce code duplication. Regards, Dean

Re: Additional improvements to extended statistics

2020-03-15 Thread Dean Rasheed
&estimatedclauses, true); That final part of the comment is no longer relevant (variable s1 no longer exists). Probably it could now just be deleted, since I think there are sufficient comments elsewhere to explain what's going on. Otherwise it looks good, and I think this will lead to some very worthwhile improvements. Regards, Dean

Re: PATCH: add support for IN and @> in functional-dependency statistics use

2020-03-17 Thread Dean Rasheed
ple values from each column, where before we only allowed one. I took another look at the patch I posted on the other thread, and I've convinced myself that it's correct. Attached is an updated version, with some cosmetic tidying up and now with some additional regression tests. Regards, De

Re: PATCH: add support for IN and @> in functional-dependency statistics use

2020-03-17 Thread Dean Rasheed
On Tue, 17 Mar 2020 at 15:37, Tomas Vondra wrote: > > On Tue, Mar 17, 2020 at 12:42:52PM +, Dean Rasheed wrote: > > >The other thing that I'm still concerned about is the possibility of > >returning estimates with P(a,b) > P(a) or P(b). I think that such a >

Re: Additional improvements to extended statistics

2020-03-19 Thread Dean Rasheed
> Opinions? > -1 I think of clause_selectivity() and clauselist_selectivity() as the public API that everyone is using, whilst the functions that support lists of clauses to be combined using OR are internal (to the planner) implementation details. I think callers of public API tend to either have implicitly AND'ed list of clauses, or a single OR clause. Regards, Dean

Re: PATCH: add support for IN and @> in functional-dependency statistics use

2020-03-19 Thread Dean Rasheed
eplacing it with > > P(a,b) = f * Min(P(a), P(b)) + (1-f) * P(a) * P(b) > > but explaining how the old formula may produce nonsensical selectivity, > and how the new formula addresses that issue. > I think this is purely a comment issue? I've added some more extensive commen

Re: Additional improvements to extended statistics

2020-03-23 Thread Dean Rasheed
d recursively later. Or perhaps estimatedclauses can somehow be used to prevent this, though I'm not sure exactly how that would work. Regards, Dean

Re: Additional improvements to extended statistics

2020-03-24 Thread Dean Rasheed
ivity > clauselist_selectivity_simple >already estimated > > I've only quickly hacked clause_selectivity, but it does not seems very > invasive (of course, it means disruption to clause_selectivity callers, > but I suppose most call clauselist_selectivity). > Sounds like a reasonable approach, but I think it would be better to preserve the current public API by having clauselist_selectivity() become a thin wrapper around a new function that optionally applies extended stats. Regards, Dean

<    1   2   3   4   5   6   7   >