[HACKERS] close_ps, NULLs, and DirectFunctionCall

(From IRC discussion with Andreas about some sqlsmith errors) Commit 278148907a9 changed close_ps in geo_ops.c to return SQL NULL in the event that a valid result point was not found (rather than crashing or failing an assert). But close_ps is called with DirectFunctionCall from other close_*

Re: [HACKERS] Inadequate infrastructure for NextValueExpr

> "Thomas" == Thomas Munro writes: >> [...] >> T_NamedTuplestoreScan can be produced by outfuncs.c with tagname >> NAMEDTUPLESTORESCAN but that tagname is not recognized by readfuncs.c >> [...] >> >> That revealed a defect in commit >>

Re: [HACKERS] COPY vs. transition tables

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: >>>>> "Thomas" == Thomas Munro <thomas.mu...@enterprisedb.com> writes: Thomas> Here it is. Added to open items. Andrew> On it. Committed. -- Andre

Re: [HACKERS] COPY vs. transition tables

> "Thomas" == Thomas Munro writes: Thomas> Here it is. Added to open items. On it. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] transition table behavior with inheritance appears broken

Commits pushed. Unless I broke the buildfarm again (which I'll check up on later), or some new issue arises with the fixes, this should close all 3 related items for transition tables. -- Andrew. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] transition table behavior with inheritance appears broken

> "Noah" == Noah Misch writes: Noah> IMMEDIATE ATTENTION REQUIRED. This PostgreSQL 10 open item is Noah> long past due for your status update. Please reacquaint yourself Noah> with the policy on open item ownership[1] and then reply Noah> immediately. If I do not

Re: [HACKERS] transition table behavior with inheritance appears broken

> "Noah" == Noah Misch writes: Noah> This PostgreSQL 10 open item is past due for your status update. Noah> Kindly send a status update within 24 hours, oops, sorry! I forgot to include a date in the last one, and in fact a personal matter delayed things anyway. I

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

> "Thomas" == Thomas Munro writes: Thomas> Thanks both for the review. New version of patch #2 attached. I'm looking to commit this soon; if anyone has any further comment now would be a good time to speak up. -- Andrew (irc:RhodiumToad) -- Sent via

Re: [HACKERS] Transition tables vs ON CONFLICT

> "Thomas" == Thomas Munro writes: Thomas> That accidentally removed a comment that I wanted to keep. Thomas> Here is a better version. I plan to commit this soon; if anyone has any comment to make, now would be a good time. -- Andrew (irc:RhodiumToad)

Re: [HACKERS] transition table behavior with inheritance appears broken

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: Andrew> Unfortunately I've been delayed over the past couple of days, Andrew> but I have Thomas' latest patchset in hand and will be working Andrew> on it over the rest of the week. St

Re: [HACKERS] transition table behavior with inheritance appears broken

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: Andrew> I will post a further status update before 23:59 BST on 14th Andrew> Jun. Unfortunately I've been delayed over the past couple of days, but I have Thomas' latest patchset i

Re: [HACKERS] transition table behavior with inheritance appears broken

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: Andrew> I have it; I will post a status update before 23:59 BST on 11 Andrew> Jun. This is that status update. I am still studying Thomas' latest patch set; as I mentioned in another

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

> "Robert" == Robert Haas writes: Robert> I don't see a reason why MakeTransitionCaptureState needs to Robert> force the tuplestores into TopTransactionContext or make them Robert> owned by TopTransactionResourceOwner. Nor do I, and I'm pretty sure it's leaking

Re: [HACKERS] transition table behavior with inheritance appears broken

> "Robert" == Robert Haas writes: Robert> So, Andrew, are you running with this, or should I keep looking Robert> into it? I have it; I will post a status update before 23:59 BST on 11 Jun. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

> "Robert" == Robert Haas writes: Robert> unless some other committer volunteers. (Of course, anyone Robert> could step in to do the work, as Thomas already has to a Robert> considerable degree, but without a committer involved it Robert> doesn't fix the problem.)

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

> "Thomas" == Thomas Munro writes: Thomas> So, afterTriggers.query_stack is used to handle the reentrancy Thomas> that results from triggers running further statements that Thomas> might fire triggers. It isn't used for dealing with extra Thomas>

Re: [HACKERS] Assertion failure in REL9_5_STABLE

> "Pavan" == Pavan Deolasee writes: Pavan> I am attaching a patch that throws a similar ERROR during Pavan> planning even for 9.5. AFAICS in presence of grouping sets, we Pavan> always decide to use sort-based implementation for grouping, but Pavan> do not check

Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans

> "Alexander" == Alexander Kuzmenkov writes: Alexander> Structurally, the patch consists of two major parts: a Alexander> specialized executor node Why? It strikes me that the significant fact here is not that we're doing count(*), but that we don't need any

Re: [HACKERS] Ongoing issues with representation of empty arrays

> "Tom" == Tom Lane writes: >> First is contrib/intarray, _AGAIN_ (see past bugs such as #7730): >> ... >> I plan to fix this one properly, unless anyone has any objections. Tom> Just to clarify, what do you think is "properly"? I would say, that any time an

[HACKERS] Ongoing issues with representation of empty arrays

The distinction between the standard representation of '{}' as an array with zero dimensions and nonstandard representations as a 1-dimensional array with zero elements has come up in a couple of contexts on the IRC channel recently. First is contrib/intarray, _AGAIN_ (see past bugs such as

Re: [HACKERS] [sqlsmith] Planner crash on foreign table join

> "Thomas" == Thomas Munro writes: >> SomeType *x = (SomeType *) lfirst(l); >> >> (in my code I tend to omit the (SomeType *), which I dislike because >> it adds no real protection) Thomas> Just BTW, without that cast it's not compilable as C++, so I'm

Re: [HACKERS] Malformed Array Literal in PL/pgSQL Exception Block

> "David" == David E Wheeler writes: >> If you change this to EXCEPTION WHEN division_by_zero THEN, the >> reported error becomes: >> >> ERROR: malformed array literal: "foo" >> LINE 1: SELECT things || 'foo' David> So the issue stands, yes? Tom's response

Re: [HACKERS] Malformed Array Literal in PL/pgSQL Exception Block

> "David" == David E Wheeler writes: David> And it works great, including in PL/pgSQL functions, except in David> an exception block. When I run this: David> BEGIN; David> CREATE OR REPLACE FUNCTION foo( David> ) RETURNS BOOLEAN IMMUTABLE LANGUAGE

Re: [HACKERS] [sqlsmith] Planner crash on foreign table join

> "Tom" == Tom Lane writes: Tom> Experimentation shows that actually, the standard regression tests Tom> provide dozens of opportunities for find_relation_from_clauses to Tom> fail on non-RestrictInfo input. However, it lacks any IsA check, In a discussion with

Re: [HACKERS] [sqlsmith] Planner crash on foreign table join

> "Andreas" == Andreas Seltenreich writes: Andreas> Hi, Andreas> testing master at f0e44021df with a loopback postgres_fdw Andreas> installed, I see lots of crashes on queries joining foreign Andreas> tables with various expressions. Below is a reduced recipe

Re: [HACKERS] Instead of DROP function use UPDATE pg_proc in an upgrade extension script

> "Vicky" == Vicky Vergara writes: Vicky> UPDATE pg_proc SET [...] Vicky> So, I want to know how "safe" can you consider the second Vicky> method, and what kind of other objects do I need to test besides Vicky> views. Speaking from personal experience (I did

Re: [HACKERS] Unable to build doc on latest head

> "Peter" == Peter Eisentraut writes: > On 4/3/17 02:44, Ashutosh Bapat wrote: >> [1] says that id.attribute is supported in stylesheets version >> 1.77.1. Do I need to update stylesheets version? How do I do it? >> Any help will be appreciated.

Re: [HACKERS] Hash support for grouping sets

> "Andres" == Andres Freund writes: Andres> a) cast result of lfirst/lnext/whatnot. Again, what we need here is something like #define lfirst_node(_type_, l) (castNode(_type_, lfirst(l))) etc. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Hash support for grouping sets

> "Andres" == Andres Freund writes: Andres> We usually cast the result of palloc. >> Rough count in the backend has ~400 without casts to ~1350 with, so >> this doesn't seem to have been consistently enforced. Andres> Yea, but we're still trying. Well, a lot of the

Re: [HACKERS] Hash support for grouping sets

> "Mark" == Mark Dilger writes: Mark> Is there a performance test case where this patch should shine Mark> brightest? I'd like to load a schema with lots of data, and run Mark> a grouping sets query, both before and after applying the patch, Mark> to see what

Re: [HACKERS] Hash support for grouping sets

> "Mark" == Mark Dilger writes: Mark> You define DiscreteKnapsack to take integer weights and double Mark> values, and perform the usual Dynamic Programming algorithm to Mark> solve. But the only place you call this, you pass in NULL for Mark> the values,

Re: [HACKERS] Hash support for grouping sets

> "Andres" == Andres Freund writes: >> - Assert(newphase == 0 || newphase == aggstate->current_phase + 1); >> + Assert(newphase <= 1 || newphase == aggstate->current_phase + 1); Andres> I think this somewhere in the file header needs an expanded Andres>

Re: [HACKERS] Hash support for grouping sets

> "Andres" == Andres Freund writes: Andres> Changes to advance_aggregates() are, in my experience, quite Andres> likely to have performance effects. This needs some Andres> performance tests. [...] Andres> Looks like it could all be noise, but it seems worthwhile to

Re: [HACKERS] Hash support for grouping sets

[snip] This thread seems to have gone quiet - is it time for me to just go ahead and commit the thing anyway? Anyone else want to weigh in? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] Composite IS NULL again, this time with plpgsql

This came up recently on irc: create type t1 as (a integer, b integer); create type t2 as (p t1, q t1); create function null_t2() returns t2 language sql as $f$ select null::t2; $f$; Now consider the following plpgsql: declare v t2; begin v := null_t2(); raise info 'v is null = %', v is

Re: [HACKERS] Hash support for grouping sets

> "Mark" == Mark Dilger writes: Mark> Hi Andrew, Mark> Reviewing the patch a bit more, I find it hard to understand the Mark> comment about passing -1 as a flag for finalize_aggregates. Any Mark> chance you can spend a bit more time word-smithing that code

Re: [HACKERS] Hash support for grouping sets

> "Mark" == Mark Dilger writes: Mark> Hi Andrew, Mark> Reviewing the patch a bit more, I find it hard to understand the Mark> comment about passing -1 as a flag for finalize_aggregates. Any Mark> chance you can spend a bit more time word-smithing that code

Re: [HACKERS] Hash support for grouping sets

> "Mark" == Mark Dilger writes: Mark> On linux/gcc the patch generates a warning in nodeAgg.c that is Mark> fairly easy to fix. Using -Werror to make catching the error Mark> easier, I get: what gcc version is this exactly? -- Andrew (irc:RhodiumToad) --

Re: [HACKERS] Hash support for grouping sets

> "Thom" == Thom Brown writes: Thom> This doesn't apply cleanly to latest master. Could you please Thom> post a rebased patch? Sure. -- Andrew (irc:RhodiumToad) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index c9e0a3e..480a07e 100644

Re: [HACKERS] smallint out of range EXECUTEing prepared statement

> "Justin" == Justin Pryzby writes: Justin> Is this expected behavior ? Justin> ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1; Justin> (0 rows) Justin> ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1; Justin> PREPARE Justin> ts=# EXECUTE x(32768);

Re: [HACKERS] Couple of issues with prepared FETCH commands

> "Robert" == Robert Haas writes: >> But the problem that actually came up is this: if you do the >> PQprepare before the named cursor has actually been opened, then >> everything works _up until_ the first event, such as a change to >> search_path, that forces a

Re: [HACKERS] sequence data type

> "Daniel" == Daniel Verite writes: Daniel> Consider the case of a table with a SERIAL column which later Daniel> has to become a BIGINT due to growth. Currently a user would Daniel> just alter the column's type and does need to do anything with Daniel> the

[HACKERS] Couple of issues with prepared FETCH commands

(This came up on IRC, but I'm not sure to what extent it should be considered a "bug") If you do PQprepare(conn, "myfetch", "FETCH ALL FROM mycursor", ...); then the results are unpredictable in two ways: Firstly, nothing causes the plancache entry to be revalidated just because "mycursor" got

Re: [HACKERS] Teaching query_planner to handle multiple sort orders?

> "Tom" == Tom Lane writes: >> Of course there is one good solution, which is to have query_planner >> take a set of acceptable output sort orders rather than just a >> single one. >> How wild an idea is this? Tom> It's been on my to-do list for years, see e.g.

[HACKERS] Teaching query_planner to handle multiple sort orders?

So in the grouping sets patch post, I said: >> There is one current weakness which I don't see a good solution for: >> the planner code still has to pick a single value for group_pathkeys >> before planning the input path. This means that we sometimes can't >> choose a minimal set of sorts,

[HACKERS] Hash support for grouping sets

Herewith a patch for doing grouping sets via hashing or mixed hashing and sorting. The principal objective is to pick whatever combination of grouping sets has an estimated size that fits in work_mem, and minimizes the number of sorting passes we need to do over the data, and hash those. (Yes,

Re: [HACKERS] Strange result with LATERAL query

> "Tom" == Tom Lane writes: Tom> Hm, I was just working on inserting something of the sort into Tom> ExecInitAgg. But I guess we could do it in the planner too. Will Tom> run with your approach. Tom> I think it's a bit too stupid as-is, though. We don't need to

Re: [HACKERS] Strange result with LATERAL query

> "Pavel" == Pavel Stehule writes: Pavel> The result should not depend on GUC - hashagg on/off changing Pavel> output - it is error. I don't think anyone's suggesting leaving it unfixed, just whether the fix should introduce unnecessary rescans of the aggregate

Re: [HACKERS] Strange result with LATERAL query

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: >>>>> "Tom" == Tom Lane <t...@sss.pgh.pa.us> writes: Tom> I'm not sure if it's worth trying to distinguish whether the Param Tom> is inside any aggregate

Re: [HACKERS] Strange result with LATERAL query

> "Tom" == Tom Lane writes: Tom> I'm not sure if it's worth trying to distinguish whether the Param Tom> is inside any aggregate calls or not. The existing code gets the Tom> right answer for Tom> select array(select x+sum(y) from generate_series(1,3) y group by y)

Re: [HACKERS] Strange result with LATERAL query

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: >>>>> "Jeevan" == Jeevan Chalke <jeevan.cha...@enterprisedb.com> writes: Jeevan> Hi, Jeevan> While playing with LATERAL along with some aggregates in Je

Re: [HACKERS] Strange result with LATERAL query

> "Jeevan" == Jeevan Chalke writes: Jeevan> Hi, Jeevan> While playing with LATERAL along with some aggregates in Jeevan> sub-query, I have observed somewhat unusual behavior. Simpler example not needing LATERAL: select array(select sum(x+y) from

Re: [HACKERS] SP-GiST support for inet datatypes

> "Tom" == Tom Lane writes: > Emre Hasegeli writes: >> Attached patches add SP-GiST support to the inet datatypes. Tom> I started to look at this patch. The reported speedup is pretty Tom> nice, but ... The builtin gist support for inet seems

Re: [HACKERS] [GENERAL] C++ port of Postgres

> "Robert" == Robert Haas writes: Robert> Hmm, so sizeof() has different semantics in C vs. C++? No. '1' has different semantics in C vs C++. (In C, '1' is an int, whereas in C++ it's a char. It so happens that (sizeof '1') is the only case which is valid in both C

Re: [HACKERS] EXLCUDE constraints and Hash indexes

> "Jeff" == Jeff Janes writes: Jeff> From: https://www.postgresql.org/docs/9.4/static/sql-createtable.html Jeff> "The access method must support amgettuple (see Chapter 55); at Jeff> present this means GIN cannot be used. Although it's allowed, there is Jeff>

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Latest patch. Names and scopes are as per discussion. New files for code and regression test. Docs included. -- Andrew (irc:RhodiumToad) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7830334..4552a74 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

> "Greg" == Greg Stark writes: >> No, because as the pages split, they fill more slowly (because there >> are now more pages). So on average in a large randomly filled index, >> pages spend more time nearer 50% full than 100% full. This is easy >> to demonstrate by

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: Tom> But we need to be clear in the documentation about what this Tom> property actually means. My objection to having it answer at the Tom> index or column level is basically that that encourages confusion Tom> as to what it means. OK.

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> This table shows what properties are exposed at the AM-wide level, >> the per-index level and the per-column level. Tom> +1 mostly, but I'm a bit bemused by can_order and can_backward Tom> having different scopes --- how come? That's

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> distance_orderable now returns true/false depending on the opclass, >> not just on the amcanorderbyop field. In order to do this, I've >> added an optional amproperty function to the AM api, which if it >> exists, gets first dibs on all

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

> "Jeff" == Jeff Janes writes: Jeff> But shouldn't that still leave us with a 75% full index, rather Jeff> than slightly over 50% full? Average is usually about 67%-70%. (For capacity estimation I always assume 66% for a non-sequentially-filled btree.) Jeff> The

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

So I'm tidying up and doing docs for the next version of this patch, but here for comment is the current functionality: select cap, pg_indexam_has_property(a.oid, cap) as "AM", pg_index_has_property('onek_hundred'::regclass, cap) as "Index",

[HACKERS] Why is box <-> point missing, and box <-> box not indexable?

point <-> point, circle <-> point and polygon <-> point all exist as orderable-by-operator operators (in fact they are the only ones by default). But there's no box <-> point operator at all, and no index support for box <-> box. Was this intentional, or just a strange oversight? -- Andrew

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> - this still has everything in amapi.c rather than creating any new >> files. Also, the regression tests are in create_index.sql for lack >> of any obviously better place. Tom> This more than doubles the size of amapi.c, so it has a

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Updated patch. Changes: - returns NULL rather than "cache lookup failed" - added pg_index_column_has_property (incl. docs) - added regression tests Not changed / need consideration: - this still has everything in amapi.c rather than creating any new files. Also, the regression

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Alvaro" == Alvaro Herrera writes: >> One idea is utils/adt/misc.c. Or we could make a new file under >> utils/adt/ though I'm not very sure what to name it. amaccess.c? >> catutils.c? If there's only ever likely to be one or two functions >> of this ilk,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Kevin" == Kevin Grittner writes: >>> Building on the has-property approach Andrew suggested, I wonder if >>> we need something like pg_index_column_has_property(indexoid, colno, >>> propertyname) with properties like "sortable", "desc", "nulls first". >> >> This

Re: [HACKERS] Oddity with NOT IN

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: Andrew> The easy to catch case, I think, is when the targetlist of the Andrew> IN or NOT IN subquery contains vars of the outer query level Andrew> but no vars of the inner one and

Re: [HACKERS] Oddity with NOT IN

> "Pavel" == Pavel Stehule writes: >> Well now I feel dumb... >> >> It would be very useful if we had some way to warn users about stuff >> like this. Emitting a NOTICE comes to mind. Pavel> This can be valid query It can be, but it essentially never is. The

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Bruce" == Bruce Momjian writes: Bruce> Would it be helpful to output an array of strings representing Bruce> the index definition? >> Why would that help, if the point is to enable programmatic access >> to information? Bruce> I was thinking an array of strings

Re: [HACKERS] [sqlsmith] Crash in GetOldestSnapshot()

> "Amit" == Amit Kapila writes: Amit> Sure, that is the reason of crash, but even if we do that it will Amit> lead to an error "no known snapshots". Here, what is going on is Amit> that we initialized toast snapshot when there is no active Amit> snapshot in the

Re: [HACKERS] [sqlsmith] Crash in GetOldestSnapshot()

> "Andreas" == Andreas Seltenreich writes: 418 if (OldestActiveSnapshot != NULL) 419 ActiveLSN = OldestActiveSnapshot->as_snap->lsn; 420 421 if (XLogRecPtrIsInvalid(RegisteredLSN) || RegisteredLSN > ActiveLSN) 422 return OldestActiveSnapshot->as_snap;

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Bruce" == Bruce Momjian writes: >> As far as I understood Andrew's use case, he was specifically *not* >> interested in a complete representation of an index definition, but >> rather about whether it had certain properties that would be of >> interest to

Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

> "Tom" == Tom Lane writes: Tom> Also, the way that the value is calculated in the Tom> samples-not-all-distinct case corresponds to the way I have it in Tom> the patch. Ahh, gotcha. You're referring to this: /* * If we estimated the number of

Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: >>>>> "Tom" == Tom Lane <t...@sss.pgh.pa.us> writes: Tom> What I did in the patch is to scale the formerly fixed "-1.0" Tom> stadistinct estimate

Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

> "Tom" == Tom Lane writes: Tom> What I did in the patch is to scale the formerly fixed "-1.0" Tom> stadistinct estimate to discount the fraction of nulls we found. This seems quite dubious to me. stadistinct representing only the non-null values seems to me to be

Re: [HACKERS] Wanting to learn about pgsql design decision

> "Tom" == Tom Lane writes: >> - Why to read from a table, both a usage permission on the schema >> and a read access permission on the table is needed? Tom> Because the SQL standard says so. You'd think, but in fact it doesn't; the spec (at least 2008 and the 2011

Re: [HACKERS] copyParamList

> "Robert" == Robert Haas writes: Robert> So I think we instead ought to fix it as in the attached. Robert>if (retval->paramMask != NULL && Robert> - !bms_is_member(i, retval->paramMask)) Robert> +

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

And a doc patch to go with it: -- Andrew (irc:RhodiumToad) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0689cc9..3e13e38 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -577,6 +577,89 @@ + +Capability information

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: Tom> Andrew still hasn't shown a concrete example of what he needs to Tom> do and why. The issue I ran into was the exact same one as in the JDBC thread I linked to earlier: correctly interpreting pg_index.indoption (to get the ASC / DESC

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Here is my proposed code (first cut; obviously it needs docs too). Opinions? -- Andrew (irc:RhodiumToad) diff --git a/src/backend/access/index/amapi.c b/src/backend/access/index/amapi.c index d347ebc..3e7e084 100644 --- a/src/backend/access/index/amapi.c +++ b/src/backend/access/index/amapi.c

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> It could easily be exposed as a function interface of the form >> index_has_capability(oid,name) or indexam_has_capability(oid,name) >> without any initdb worries. Tom> You missed the "compelling argument why it's needed" part. What

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> With the gutting of pg_am in 9.6, there seems to be no longer any >> way for a query of the system catalogs to discover any of the index >> capabilities that were formerly columns in pg_am (notably >> amcanorder, amcanorderbyop,

[HACKERS] No longer possible to query catalogs for index capabilities?

With the gutting of pg_am in 9.6, there seems to be no longer any way for a query of the system catalogs to discover any of the index capabilities that were formerly columns in pg_am (notably amcanorder, amcanorderbyop, amclusterable, amsearcharray, amsearchnulls). Am I missing something or is

Re: [HACKERS] Proposal: revert behavior of IS NULL on row types

>>>>> "Andrew" == Andrew Gierth <and...@tao11.riddles.org.uk> writes: >>> Whole-row vars when constructed never contain the null value. David> ...but what does this mean in end-user terms?​ Andrew> It means for example that this query: Andre

Re: [HACKERS] Proposal: revert behavior of IS NULL on row types

> "David" == David G Johnston writes: >> Prohibiting IS NOT NULL is not on the cards; it's very widely used. David> ​Yet changing how it behaves, invisibly, is? Did you mean prohibiting it only for composite-type args? It's obviously widely used for

Re: [HACKERS] Proposal: revert behavior of IS NULL on row types

> "David" == David G Johnston writes: >> 1. x IS NULL is true if and only if x has the null value (isnull set). David> ​I don't have a problem conforming to "ROW(NULL, NULL) IS NULL" David> being true...​if you somehow get a hold of something in that David>

Re: [HACKERS] Proposal: revert behavior of IS NULL on row types

> "David" == David G Johnston writes: >> 2. x IS NOT NULL if and only if NOT (x IS NULL) David> ​I would rather prohibit "IS NOT NULL" altogether.​ If one needs David> to test "NOT (x IS NULL)" they can write it that way. Prohibiting IS NOT NULL is not on

[HACKERS] Proposal: revert behavior of IS NULL on row types

In light of the fact that it is an endless cause of bugs both in pg and potentially to applications, I propose that we cease attempting to conform to the spec's definition of IS NULL in favour of the following rules: 1. x IS NULL is true if and only if x has the null value (isnull set). 2. x IS

Re: [HACKERS] \timing interval

> "Gavin" == Gavin Flower writes: >> How about >> >> Time: 1234567.666 ms (20m 34.6s) Gavin> I like that, but I think the human form should retain the 3 Gavin> decimal places. Scale it. Time: 12.345 ms (0.012345s) Time: 1234.567 ms (1.235s) Time:

Re: [HACKERS] \timing interval

> "Tom" == Tom Lane writes: > Peter Eisentraut writes: >> I'm not quite sure what you mean by wanting to do arithmetic on the >> numbers. My phrasing of the problem is that after a long query, you >> might get output like this:

Re: [HACKERS] reserved role names

> "Joshua" == Joshua D Drake writes: Joshua> Is it intentional that insert and delete are allowed and select Joshua> is not or is it an oversight? Just an artifact of SELECT being fully reserved (needed for (SELECT ...) syntax to work right) while INSERT and DELETE

Re: [HACKERS] New design for FK-based join selectivity estimation

> "Tom" == Tom Lane writes: > Tomas Vondra writes: >> Attached is a reworked patch, mostly following the new design proposal >> from this thread. Tom> Comments and testing appreciated. This blows up (see bug 14219 for testcase) in

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

> "Robert" == Robert Haas writes: >> Why is the correct rule not "check for and ignore pre-upgrade mxids >> before even trying to fetch members"? Robert> I entirely believe that's the correct rule, but doesn't Robert> implementing it require a crystal balll? Why

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

> "Alvaro" == Alvaro Herrera writes: >> (It can, AFAICT, be inside the currently valid range due to >> wraparound, i.e. without there being a valid pg_multixact entry for >> it, because AFAICT in 9.2, once the mxid is hinted dead it is never >> again either

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

> "Alvaro" == Alvaro Herrera writes: Alvaro> I think that was a good choice in general so that Alvaro> possibly-data-eating bugs could be reported, but there's a Alvaro> problem in the specific case of tuples carried over by Alvaro> pg_upgrade whose Multixact is

Re: [HACKERS] regexp_match() returning text

> "Emre" == Emre Hasegeli writes: Emre> Attached patch adds regexp_match() function which is a simple Emre> variant of regexp_matches() that doesn't return a set. We already have a function that takes a string and a regexp and returns a single text result: substring().

Re: [HACKERS] Re: Request for Patch Feedback: Lag & Lead Window Functions Can Ignore Nulls

> "Dean" == Dean Rasheed writes: Dean> That may be so, but we already support FILTER for all windows Dean> functions as well as aggregates: Not so: "If FILTER is specified, then only the input rows for which the filter_clause evaluates to true are fed to the

[HACKERS] copyParamList

copyParamList does not respect from->paramMask, in what looks to me like an obvious oversight: retval->paramMask = NULL; [...] /* Ignore parameters we don't need, to save cycles and space. */ if (retval->paramMask != NULL && !bms_is_member(i, retval->paramMask))

Re: [HACKERS] Allow COPY to use parameters

> "Merlin" == Merlin Moncure writes: Merlin> Note, the biggest pain point I have with COPY is not being able Merlin> to parameterize the filename argument. Second proof of concept attached. This goes so far as to allow statements like: do $$ declare t text := 'bar';

  1   2   3   4   5   >