Subject: Re: PATCH: psql tab completion for SELECT
To: Edmund Horner <ejrh00@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
References: 
 <CAMyN-kB_xrU4iYdcF1j=tijgO1DSyjtb3j96O4UEj91XZrZcMg@mail.gmail.com>
 <F9466A55-8F16-4DC2-BAE8-6386870A3C3C@anarazel.de>
 <CAMyN-kC65Xdn3Y5A+muYWMAR50gk-oCABcyZ809wuV0x9_nsJQ@mail.gmail.com>
 <882D6617-FA16-4C56-8FE0-884BCBBE5A4C@anarazel.de>
 <CAMyN-kAJX9fkzm1aFE5sp4se9t1c+p1e1JJGc_MXerxHUK9qoQ@mail.gmail.com>
 <2738.1516234038@sss.pgh.pa.us>
 <3a919958-e508-3b66-686f-44a7937fb6e7@2ndquadrant.com>
 <CAMyN-kDBXUAmoRb1oYaSMQDUA3=xccEzQncRBMywt4KodPdwTw@mail.gmail.com>
 <cbd1e86b-88c9-edc1-8a88-66df58d8cd39@2ndquadrant.com>
 <CAMyN-kBY=cPuD-LXTZndS6biWDYUMScd8N=vGeArPU+bztP5VA@mail.gmail.com>
 <24314.1520190408@sss.pgh.pa.us>
 <CAMyN-kDJk01k=qKRHA-SKBBbdmTcJ4fyehjLc_N0CTCm4NdA2Q@mail.gmail.com>
 <10992.1520260903@sss.pgh.pa.us>
 <CAMyN-kA4FNA=TOw7WBacoJGYh_u8traYXTOffR-WiCyV6YZKsw@mail.gmail.com>
 <CAMyN-kAkd8dOBsi6oYXUttT5S5Y+-J=xr_DoCoxwPKSFO=0WKQ@mail.gmail.com>
 <CAMyN-kA3m2ApH6Q6TmpYMtnq7t7DsQGdGo4JPAE7BSz6GX80xQ@mail.gmail.com>
Cc: Vik Fearing <vik.fearing@2ndquadrant.com>,
 Andres Freund <andres@anarazel.de>, pgsql-hackers@lists.postgresql.org
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <bced97f2-a68a-e5ca-a214-6b99f9d89a6d@2ndquadrant.com>
Date: Thu, 5 Apr 2018 21:29:06 -0400
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAMyN-kA3m2ApH6Q6TmpYMtnq7t7DsQGdGo4JPAE7BSz6GX80xQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 3/21/18 02:51, Edmund Horner wrote:
> I still think the number of completions on an empty string is a bit
> too big, but I don't know what to omit.  There are around 1700
> completions on the empty "postgres" database in my testing, and we
> show the first 1000 (arbitrarily limited, as the generated SQL has
> LIMIT 1000 but no ORDER BY).
> 
> Should we just leave it as is?
> 
> Whether we improve the filtering or not, I'm optimistic the feature
> will be committed in this CF or the next.

I looked at this a bit now.  I think it still needs some work.

Some of the queries for older versions contain syntax errors that causes
them not to work.

For example, in 80100:

"'internal'::regtype != ALL ([.proargtypes) "

The query definition structures are missing a comma between selcondition
and viscondition.  This causes all the following fields to be
misassigned.  I'm not quite sure how it actually works at all some of
the time.  There might be another bug that offsets this one.

I'd like to see a short comment what is different between each of the
version queries.  You had a list earlier in the thread.

The selection of which functions to show can be further refined.

I don't think the contents of pg_amproc and pg_cast should be excluded.
Some of those functions are useful.  Similarly for pg_operator.oprcode.

Things like oprrest and oprjoin will already be excluded because they
have "internal" arguments.  Similarly for some columns in pg_aggregate.

There are also additional pseudo-types that should be excluded.  See
pg_type.typtype = 'p', except some of those should not be excluded.
Needs more thought.

Considering these issues, I think it would be appropriate to move this
patch to the next commitfest.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services