system variable can be edited by all user?
Hi, Ive created a read only user (SELECT PRIVILEGE) but it turns out that this user can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I limit him? Thanks, Butching
Re: system variable can be edited by all user?
On Tue, 2022-11-22 at 14:25 +0800, chris navarroza wrote: > Ive created a read only user (SELECT PRIVILEGE) but it turns out that this > user > can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I limit him? We call these "configuration parameters", and there is no way you can prevent a database user from changing them for her session. The recommendation is not to give untrustworthy users access to directly run SQL statements on the database. Yours, Laurenz Albe
copying json data and backslashes
Hi all,
I have come across this apparently common issue COPY-ing json and wondering if
there is potentially a better solution.
I am copying data into a jsonb column originating from a 3rd party API. The
data may have literal \r,\t,\n and also double backslashes.
I discovered that I can cast this data to a jsonb value directly but I can't
COPY the data without pre-processing.
The example below illustrates my issue (only with \r, but the problem extends
to other \X combinations).
do $$
lines=[r'{"test" : "\r this data has a carriage return"}']
with open("/tmp/test1.json","w") as f:
for line in lines:
f.write(line.strip() + "\n")
$$ language plpython3u;
create temp table testing (data jsonb);
-- this works
insert into testing (data)
select l::jsonb
from pg_read_file('/tmp/test1.json') f,
lateral regexp_split_to_table(f,'\n') l where l <> '';
-- fails
copy testing (data) from '/tmp/test1.json';
-- works
copy testing (data) from program $c$ sed -e 's/\\r/u000a/g' /tmp/test1.json
$c$;
Is there any other solution with COPY that doesn't require manual
implementation of search/replace to handle these edge cases?
Why does ::jsonb work but COPY doesn't? It seems a bit inconsistent.
Best regards,
Alastair
Re: copying json data and backslashes
> On 22/11/2022 15:23 CET Alastair McKinley
> wrote:
>
> Hi all,
>
> I have come across this apparently common issue COPY-ing json and wondering if
> there is potentially a better solution.
>
> I am copying data into a jsonb column originating from a 3rd party API. The
> data may have literal \r,\t,\n and also double backslashes.
>
> I discovered that I can cast this data to a jsonb value directly but I can't
> COPY the data without pre-processing.
>
> The example below illustrates my issue (only with \r, but the problem extends
> to other \X combinations).
>
> > do $$
> > lines=[r'{"test" : "\r this data has a carriage return"}']
> >
> > with open("/tmp/test1.json","w") as f:
> > for line in lines:
> > f.write(line.strip() + "\n")
> >
> > $$ language plpython3u;
> >
> > create temp table testing (data jsonb);
> >
> > -- this works
> > insert into testing (data)
> > select l::jsonb
> > from pg_read_file('/tmp/test1.json') f,
> > lateral regexp_split_to_table(f,'\n') l where l <> '';
> >
> > -- fails
> > copy testing (data) from '/tmp/test1.json';
> >
> > -- works
> > copy testing (data) from program $c$ sed -e 's/\\r/u000a/g'
> > /tmp/test1.json $c$;
> >
>
> Is there any other solution with COPY that doesn't require manual
> implementation of search/replace to handle these edge cases?
> Why does ::jsonb work but COPY doesn't? It seems a bit inconsistent.
COPY handles special backslash sequences[1]. The \r in your sample JSON,
although properly escaped according to JSON, is replaced with an actual
carriage return by COPY before casting to jsonb. The error results from JSON
prohibiting unescaped control characters in strings[2].
You must double escape to pass those characters through COPY.
See how COPY outputs backslash sequences:
-- Actual carriage return:
copy (select e'\r') to stdout;
\r
-- Backslash sequence for carriage return:
copy (select '\r') to stdout;
\\r
[1] https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.9.2
[2] https://www.json.org/json-en.html
--
Erik
Re: copying json data and backslashes
> > On Tuesday, November 22, 2022 at 10:16:11 AM EST, Erik Wienhold wrote: > > > > On 22/11/2022 15:23 CET Alastair McKinley wrote: > > > > Hi all, > > > > I have come across this apparently common issue COPY-ing json and wondering if > > there is potentially a better solution. > > > > I am copying data into a jsonb column originating from a 3rd party API. The > > data may have literal \r,\t,\n and also double backslashes. > > > > I discovered that I can cast this data to a jsonb value directly but I can't > > COPY the data without pre-processing. > > > > Is there any other solution with COPY that doesn't require manual > > implementation of search/replace to handle these edge cases? > > Why does ::jsonb work but COPY doesn't? It seems a bit inconsistent. > > COPY handles special backslash sequences[1]. The \r in your sample JSON, > although properly escaped according to JSON, is replaced with an actual > carriage return by COPY before casting to jsonb. The error results from JSON > prohibiting unescaped control characters in strings[2]. > > You must double escape to pass those characters through COPY. > > See how COPY outputs backslash sequences: > > -- Actual carriage return: > copy (select e'\r') to stdout; > \r > > -- Backslash sequence for carriage return: > copy (select '\r') to stdout; > > \\r I have been able to get around this problem by using the following method: \copy footable from 'input.json' (format csv, escape '^B', delimieter '^C', quote '^E') where the control characters are the actual control char, not the caret-letter, and it requires no escaping escapes. I realize this won't work for all situations. PJ > > [1] > https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.9.2 > [2] https://www.json.org/json-en.html > > -- > Erik
Re: system variable can be edited by all user?
On 11/21/22 22:25, chris navarroza wrote: Hi, Ive created a read only user (SELECT PRIVILEGE) but it turns out that this user can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I limit him? Short answer is what Laurenz Albe posted. Long answer is: From: https://www.postgresql.org/docs/current/view-pg-settings.html select name, setting, context from pg_settings where name = 'work_mem'; name | setting | context --+-+- work_mem | 4096| user user These settings can be set from postgresql.conf, or within a session via the SET command. Any user is allowed to change their session-local value. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET. See page for the other possible values for context and what they mean with regard to privileges. Thanks, Butching -- Adrian Klaver adrian.kla...@aklaver.com
Re: copying json data and backslashes
> From: p...@cmicdo.com > Sent: 22 November 2022 15:30 > To: Alastair McKinley ; > pgsql-general@lists.postgresql.org ; Erik > Wienhold > Subject: Re: copying json data and backslashes > > > > > On Tuesday, November 22, 2022 at 10:16:11 AM EST, Erik Wienhold > wrote: > > > > > > > On 22/11/2022 15:23 CET Alastair McKinley > wrote: > > > > > > Hi all, > > > > > > I have come across this apparently common issue COPY-ing json and > wondering if > > > there is potentially a better solution. > > > > > > I am copying data into a jsonb column originating from a 3rd party API. > The > > > data may have literal \r,\t,\n and also double backslashes. > > > > > > I discovered that I can cast this data to a jsonb value directly but I > can't > > > COPY the data without pre-processing. > > > > > > > Is there any other solution with COPY that doesn't require manual > > > implementation of search/replace to handle these edge cases? > > > Why does ::jsonb work but COPY doesn't? It seems a bit inconsistent. > > > > COPY handles special backslash sequences[1]. The \r in your sample JSON, > > although properly escaped according to JSON, is replaced with an actual > > carriage return by COPY before casting to jsonb. The error results from > JSON > > prohibiting unescaped control characters in strings[2]. > > > > You must double escape to pass those characters through COPY. > > > > See how COPY outputs backslash sequences: > > > > -- Actual carriage return: > > copy (select e'\r') to stdout; > > \r > > > > -- Backslash sequence for carriage return: > > copy (select '\r') to stdout; > > > > \\r > > I have been able to get around this problem by using the following method: > > \copy footable from 'input.json' (format csv, escape '^B', delimieter '^C', > quote '^E') > > where the control characters are the actual control char, not the > caret-letter, and it requires no escaping escapes. I realize this won't work > for all > situations. > > PJ Hi PJ, Thanks for the suggestion, this is interesting to me to try but I am not quite sure how this works. As far as I understand, escape/quote/delimiter have to be a single character, and CTRL-C etc. are multiple characters. What way do you input each of the escape/quote/delimiter characters? Best regards, Alastair > > > > > [1] > > https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.9.2 > > [2] https://www.json.org/json-en.html > > > > -- > > Erik >
Re: copying json data and backslashes
On 2022-11-22 17:39:04 +, Alastair McKinley wrote:
> > \copy footable from 'input.json' (format csv, escape '^B', delimieter '^C',
> > quote '^E')
> >
> > where the control characters are the actual control char, not the
> > caret-letter, and it requires no escaping escapes. I realize this
> > won't work for all
> > situations.
>
> Thanks for the suggestion, this is interesting to me to try but I am
> not quite sure how this works.
> As far as I understand, escape/quote/delimiter have to be a single
> character, and CTRL-C etc. are multiple characters.
You may have to hit several Keys[1] on your keyboard, but Ctrl-C is a
single character, just like Shift-C is (the former has code 0003, the
latter 0043).
On Unix-like systems you can usually type the control characters by
typing Ctrl-V first:
At the psql prompt, type
select ascii('
then hit V while holding the ctrl key
then hit C while holding the ctrl key
The terminal should display that as ^C
then complete the line with
');
so that it looks like
select ascii('^C');
and hit return:
╔═══╗
║ ascii ║
╟───╢
║ 3 ║
╚═══╝
(1 row)
Same for the other ctrl characters.
hp
[1] There are usually four Ctrl-Characters which need only a single
key: Ctrl-I (TAB), Ctrl-M (CR), Ctrl-[ (ESC) and Ctrl-H (BS) or Ctrl-?
(DEL).
(On Unix systems CR is normally translated to LF, on Windows to CRLF)
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
security label and indexes
I noticed that the 'security label' sql command does not include indexes as objects that can be labeled, why is that? What sepgsql security class are indexes, db_table? Ted
Re: copying json data and backslashes
On Tuesday, November 22, 2022 at 01:16:02 PM EST, Peter J. Holzer
wrote:
> On 2022-11-22 17:39:04 +, Alastair McKinley wrote:
> > > \copy footable from 'input.json' (format csv, escape '^B', delimieter '^C
', quote '^E')
> > >
> > > where the control characters are the actual control char, not the
> > > caret-letter, and it requires no escaping escapes. I realize this
> > > won't work for all
> > > situations.
> >
> > Thanks for the suggestion, this is interesting to me to try but I am
> > not quite sure how this works.
> > As far as I understand, escape/quote/delimiter have to be a single
> > character, and CTRL-C etc. are multiple characters.
>
Yes, Alastair, Peter said what I would have...
> You may have to hit several Keys[1] on your keyboard, but Ctrl-C is a
> single character, just like Shift-C is (the former has code 0003, the
> latter 0043).
>
> On Unix-like systems you can usually type the control characters by
> typing Ctrl-V first:
>
> At the psql prompt, type
> select ascii('
> then hit V while holding the ctrl key
> then hit C while holding the ctrl key
> The terminal should display that as ^C
> then complete the line with
> ');
> so that it looks like
> select ascii('^C');
> and hit return:
>
>
> [1] There are usually four Ctrl-Characters which need only a single
> key: Ctrl-I (TAB), Ctrl-M (CR), Ctrl-[ (ESC) and Ctrl-H (BS) or Ctrl-?
> (DEL).
>
> (On Unix systems CR is normally translated to LF, on Windows to CRLF)
>
Re: copying json data and backslashes
> On 22/11/2022 20:11 CET p...@cmicdo.com wrote:
>
> On Tuesday, November 22, 2022 at 01:16:02 PM EST, Peter J. Holzer
> wrote:
>
> > On 2022-11-22 17:39:04 +, Alastair McKinley wrote:
> > > > \copy footable from 'input.json' (format csv, escape '^B', delimieter
> '^C
> ', quote '^E')
> > > >
> > > > where the control characters are the actual control char, not the
> > > > caret-letter, and it requires no escaping escapes. I realize this
> > > > won't work for all
> > > > situations.
> > >
> > > Thanks for the suggestion, this is interesting to me to try but I am
> > > not quite sure how this works.
> > > As far as I understand, escape/quote/delimiter have to be a single
> > > character, and CTRL-C etc. are multiple characters.
> >
>
> Yes, Alastair, Peter said what I would have...
>
> > You may have to hit several Keys[1] on your keyboard, but Ctrl-C is a
> > single character, just like Shift-C is (the former has code 0003, the
> > latter 0043).
> >
> > On Unix-like systems you can usually type the control characters by
> > typing Ctrl-V first:
> >
> > At the psql prompt, type
> > select ascii('
> > then hit V while holding the ctrl key
> > then hit C while holding the ctrl key
> > The terminal should display that as ^C
> > then complete the line with
> > ');
> > so that it looks like
> > select ascii('^C');
> > and hit return:
> >
> >
> > [1] There are usually four Ctrl-Characters which need only a single
> > key: Ctrl-I (TAB), Ctrl-M (CR), Ctrl-[ (ESC) and Ctrl-H (BS) or Ctrl-?
> > (DEL).
> >
> > (On Unix systems CR is normally translated to LF, on Windows to CRLF)
> >
Or use C-style escapes[1]:
\copy footable from 'input.json' (format csv, escape e'\2', delimiter
e'\3', quote e'\5')
[1]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
--
Erik
Re: security label and indexes
Ted Toth writes: > I noticed that the 'security label' sql command does not include indexes as > objects that can be labeled, why is that? What sepgsql security class are > indexes, db_table? Indexes don't have security labels, just as they don't have SQL permissions. From a security standpoint, they're implementation details of the table they belong to. Whatever usage restrictions you want should be put on the table, instead. regards, tom lane
Re: Upgrading to v12
On 11/18/2022 6:34 PM, Adrian Klaver wrote: On 11/18/22 16:05, Brad White wrote: --> The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time. Code in question: rst!Update <-- success rst!QtyDeliverable = rst!Quantity rst.Update <-- fails here The wisdom of the internet says that this is most likely with a BIT field that has null that Access can't handle. But that isn't the case here. Both are int4 fields and both have values before the update. The new PostgreSQL timestamp data type defaults to microsecond precision. This means that timestamp values are stored like 2002-05-22 09:00:00.123456-05. However, Access does not support the extra precision, so the value that Access uses is 2002-05-22 09:00:00-05. When one tries to update a record, one gets the error message above because the value that Access uses in its UPDATE query does not match the value in the PostgreSQL table, similar to the NULL vs. empty string conflict that is already reported in this FAQ entry. " The above is the problem I usually ran into with Access and Postgres and updating. Is there a timestamp field in the record you are updating? UPDATE: Yes, there are 5 timestamp fields. It seems unlikely to be the culprit for 3 reasons. 1) It worked fine in v9.4 2) It worked the previous 4 times I saved that record in v12. 3) As the data came from Access, there is no data in any of the fields in the last three decimal places. ex. 45.234000 But as it is the best lead I have, and it could still be the culprit until proven otherwise, I'm working to convert those 5 fields from timestamp to timestamp(3). Of course, PG doesn't allow to edit a table with dependent views. Which means that I'm attempting to modify a script that will allow me to save, drop, restore the views. Of course, PG coerces all table and field names to lowercase unless quoted. So I have to figure how to recognize all table names and add quotes. This table is core to the app, so a LOT of the views reference it. I may not be done anytime soon. Have a good vacation!
Puzzled by ROW constructor behaviour?
Hi all, I'm puzzled by some behaviour of the ROW constructor that I noticed when I was playing around. >From the documentation >(https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS), > we have NUMBER 1 > SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same') AS test1; result: > test1 > f This is fine. and then NUMBER 2 > SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a test')) AS > test2; result: > test2 > t OK - notice the equivalence of a ROW constructor and a VALUES clause. So, then I create this table: > CREATE TABLE test > ( > a INT NOT NULL, > b INT NOT NULL, > c TEXT NOT NULL > ); and then tried: NUMBER 3 > INSERT INTO test ((ROW (1, 2.4, 'test...'))); and I get: > ERROR: syntax error at or near "ROW" > LINE 1: INSERT INTO test ((ROW (1, 2.4, 'test...'))); I tried various permutations of brackets and whatnot but nothing doing. My question is that if a ROW constructor works for a VALUES clause in statement NUMBER 2, then why not NUMBER 3? TIA and rgs, E.
Re: Puzzled by ROW constructor behaviour?
Hi Eagna, Did you check the syntax of the INSERT statement? You either need 'VALUES ...' or a query. I don't think your expression on its own is considered a query. Cheers, Steve On Wed, Nov 23, 2022 at 8:11 AM Eagna wrote: > > Hi all, > > I'm puzzled by some behaviour of the ROW constructor that I noticed when I > was playing around. > > From the documentation ( > https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS), > we have > > NUMBER 1 > > > SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same') AS test1; > > result: > > > test1 > > f > > This is fine. > > and then > > NUMBER 2 > > > SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a > test')) AS test2; > > result: > > > test2 > > t > > OK - notice the equivalence of a ROW constructor and a VALUES clause. > > So, then I create this table: > > > CREATE TABLE test > > ( > > a INT NOT NULL, > > b INT NOT NULL, > > c TEXT NOT NULL > > ); > > and then tried: > > NUMBER 3 > > > INSERT INTO test ((ROW (1, 2.4, 'test...'))); > > and I get: > > > ERROR: syntax error at or near "ROW" > > LINE 1: INSERT INTO test ((ROW (1, 2.4, 'test...'))); > > > I tried various permutations of brackets and whatnot but nothing doing. > > > My question is that if a ROW constructor works for a VALUES clause in > statement NUMBER 2, then why not NUMBER 3? > > > TIA and rgs, > > E. > > > > >
Re: Upgrading to v12
On 11/22/22 12:53, Brad White wrote: On 11/18/2022 6:34 PM, Adrian Klaver wrote: On 11/18/22 16:05, Brad White wrote: --> The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time. Code in question: rst!Update <-- success rst!QtyDeliverable = rst!Quantity rst.Update <-- fails here The wisdom of the internet says that this is most likely with a BIT field that has null that Access can't handle. But that isn't the case here. Both are int4 fields and both have values before the update. The new PostgreSQL timestamp data type defaults to microsecond precision. This means that timestamp values are stored like 2002-05-22 09:00:00.123456-05. However, Access does not support the extra precision, so the value that Access uses is 2002-05-22 09:00:00-05. When one tries to update a record, one gets the error message above because the value that Access uses in its UPDATE query does not match the value in the PostgreSQL table, similar to the NULL vs. empty string conflict that is already reported in this FAQ entry. " The above is the problem I usually ran into with Access and Postgres and updating. Is there a timestamp field in the record you are updating? UPDATE: Yes, there are 5 timestamp fields. It seems unlikely to be the culprit for 3 reasons. 1) It worked fine in v9.4 2) It worked the previous 4 times I saved that record in v12. 3) As the data came from Access, there is no data in any of the fields in the last three decimal places. ex. 45.234000 But as it is the best lead I have, and it could still be the culprit until proven otherwise, I'm working to convert those 5 fields from timestamp to timestamp(3). It is worse then that: https://learn.microsoft.com/en-us/office/troubleshoot/access/store-calculate-compare-datetime-data Valid time values range from .0 (00:00:00) to .9 (23:59:59) So no fractional seconds. Before you do any of the below I would set up a test table with timestamps and verify they are the issue. Of course, PG doesn't allow to edit a table with dependent views. Which means that I'm attempting to modify a script that will allow me to save, drop, restore the views. Of course, PG coerces all table and field names to lowercase unless quoted. So I have to figure how to recognize all table names and add quotes. This table is core to the app, so a LOT of the views reference it. I may not be done anytime soon. Have a good vacation! -- Adrian Klaver adrian.kla...@aklaver.com
Re: Puzzled by ROW constructor behaviour?
On Tue, Nov 22, 2022 at 2:11 PM Eagna wrote: > > NUMBER 2 > > > SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a > test')) AS test2; > > My question is that if a ROW constructor works for a VALUES clause in > statement NUMBER 2, then why not NUMBER 3? > You've drawn a false equivalence from the similarity of the syntax. The following also works: SELECT 1 = (SELECT 1*1); But I don't think there is any argument that while this works: INSERT INTO tbl (col) SELECT 1*1; This doesn't: INSERT INTO tbl (col) 1; There is no difference with replacing 1 with a composite type and the SELECT subquery with VALUES instead. Your "VALUES" is just a scalar subquery expression that, if it indeed produces a single row and column, can be compared to any other scalar value (if it produces more than one row you will get an error - try it). David J.
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Tue, Nov 22, 2022 at 4:25 AM Kirk Wolak wrote: > In researching this problem, it appears that the decision was made like > 17yrs ago, when windows did not have a realistic "terminal" type interface. > Assuming we target Windows 8.1 or higher, I believe this goes away. FWIW PostgreSQL 16 will require Windows 10+. Not a Windows user myself, but I read somewhere that Windows 8.1 has already started showing full screen warnings at startup that security patches etc end in January, while PostgreSQL 16 (the soonest release that could include your changes) is expected late in the year. > What we are looking for is a simple bullet point list of what would make > such a contribution acceptable. And how far should we get through that list > on our own, before getting some help, especially from the build teams? [Our > goal would be an NEW Config type flag: READLINE_FOR_WINDOWS (you guys name > it, and control the default setting)] Some thoughts: Re configuration flags: don't waste time with the old perl-based build system. The configuration should be done only with the new meson build system (soon to be the only way to build on Windows). I didn't quite understand if you were saying that readline itself needs patches for this (I gather from earlier threads about this that there were some problems with dll symbol export stuff, so maybe that's it?). In passing, I noticed that there is also a Windows port of editline AKA libedit, the BSD-licensed alternative to readline. It has a compatible API and PostgreSQL can use that too. I have no idea which is easier, more supported, etc on Windows. It's OK to post a work-in-progress patch to pgsql-hackers, even if it doesn't work right yet. With any luck, people will show up to help with problems. I am 100% sure that our Windows user community would love this feature. It would be good if the tests in src/bin/psql/t/010_tab_completion.pl pass on Windows, but if that's hard, don't let that stop you sharing a patch.
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Mon, Nov 21, 2022 at 1:10 PM Dominique Devienne wrote: > On Mon, Nov 21, 2022 at 6:12 PM Kirk Wolak wrote: > > On Mon, Nov 21, 2022 at 11:01 AM Dominique Devienne > wrote: > > > FWIW, I've been using https://github.com/arangodb/linenoise-ng for > Linux and Windows, > > > > I've look at linenoise and without tab/autocomplete it's not worth the > effort, IMO. > > Not sure to understand... Linenoise* has completion hooks. > Completion is something done in the app, not readline/linenoise. > Only the app has the context to know what to complete the text with. > But perhaps I'm missing something? --DD > > https://github.com/arangodb/linenoise-ng/blob/master/include/linenoise.h > I missed that. There is a hook for it. Thank you. Apologies. I just got the first pass of readline working in windows with psql!! it's "rough" right now, I will have to work through a bunch of assumptions... but it's WORKING! (at least in utf8)... Tons of testing, and some of this code has not been compiled for Windows in 17yrs. I might need to find one of the geniuses who understands the inner workings of psqlscanslash... To avoid pasting a screenshot, here is a link to the auto-complete prompt: https://app.screencast.com/RBwKbZeXFdBnO
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
On Tue, Nov 22, 2022 at 5:51 PM Thomas Munro wrote: > On Tue, Nov 22, 2022 at 4:25 AM Kirk Wolak wrote: > > In researching this problem, it appears that the decision was made > like 17yrs ago, when windows did not have a realistic "terminal" type > interface. Assuming we target Windows 8.1 or higher, I believe this goes > away. > > FWIW PostgreSQL 16 will require Windows 10+. Not a Windows user > ... > Some thoughts: > > Re configuration flags: don't waste time with the old perl-based build > system. The configuration should be done only with the new meson > build system (soon to be the only way to build on Windows). > Perl? I am building/running in VS2022 community edition. https://app.screencast.com/RBwKbZeXFdBnO (screenshot) just working today (5 long days!) > I didn't quite understand if you were saying that readline itself > needs patches for this (I gather from earlier threads about this that > there were some problems with dll symbol export stuff, so maybe that's > it?). ... > yeah, readline does not build well in windows without some "tweaks", I am working with someone. They are reaching back to see if we can get the updates pushed back into readline, as Tom requested we not require supporting our own branch. Currently it's a single patch file, plus some "glue"... (putting the terminal in CHAR mode, and binary mode vs line mode, etc) > It's OK to post a work-in-progress patch to pgsql-hackers, even if it > doesn't work right yet. With any luck, people will show up to help > with problems. I am 100% sure that our Windows user community would > love this feature. It would be good if the tests in > src/bin/psql/t/010_tab_completion.pl pass on Windows, but if that's > hard, don't let that stop you sharing a patch. > Thomas, thanks for that! So new to this, I didn't realize... That's a great idea. Honestly not sure how to even run it? I probably should learn how to build with your build system. And how to submit a patch. Let me get it working better (I don't think it does multi-line yet). Thanks for the support, it's encouraging... especially when I know there's an 80% chance that this may fail to get accepted for any number of reasons.
