Re: [HACKERS] psql \watch always ignores \pset null
On Tue, Nov 18, 2014 at 9:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: Hi, /* * Set up rendering options, in particular, disable the pager, because * nobody wants to be prompted while watching the output of 'watch'. */ myopt.nullPrint = NULL; myopt.topt.pager = 0; I found psql's \watch command always ignores \pset null setting. The above source code comment explains why it does, but I'd like to see the specified string for null value even in \watch's output, in order to distinguish null and an empty value. Thought? Is there any reason why \watch must ignore \pset null setting? Hmmm ... the comment offers a reasonable argument for forcing pager = 0, but I agree the nullPrint change is not adequately explained. Will, do you remember why you did that? regards, tom lane I tracked down the individual commit[1] from my history where I added that. What I added there is very similar to sections in src/bin/psql/describe.c. I can't remember specifically my reasoning then, but it's likely I copied the patterns there while getting things working. I do still think it's important to remove the pager, but the nullPrint is probably a mistake. [1]: https://github.com/will/postgres/commit/c42d29fece16ec9cb13c159b3307ab9fca892eb2 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch to add \watch to psql
On Thu, Apr 4, 2013 at 5:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: I whacked this around some more, added basic docs, and committed it. Thanks! Unfortunately rl_clear_screen() is not included at all in libedit, causing compilation to fail, and I was completely unable to find a way to distinguish libedit from readline on OS X. It tries extraordinarily hard to pretend that it's readline. Instead falling back to simple control characters to clear the screen worked very well on both linux and OS X. I took that out; works on the two cases I tried does not mean portable. Completely understandable. I'm very excited to have helped contribute something, however small this was, to the project. Thanks again, Will
[HACKERS] Clang compiler warning on 9.3 HEAD
On ref 8507907 when compiling with clang on os x, I got this warning which seems like a possible bug. I thought to report this because I imagine clang isn't frequently used day-to-day by most. dependency.c:213:36: warning: implicit conversion from enumeration type 'ObjectClass' (aka 'enum ObjectClass') to different enumeration type 'ObjectType' (aka 'enum ObjectType') [-Wconversion] EventTriggerSupportsObjectType(getObjectClass(thisobj))) ~~ ^~~ 1 warning generated.
Re: [HACKERS] patch to add \watch to psql
Here is an updated patch that addresses several of the points brought up so far, such as the sleep, internationalization banner, and zero wait check, and it removes the premature input check. Unfortunately rl_clear_screen() is not included at all in libedit, causing compilation to fail, and I was completely unable to find a way to distinguish libedit from readline on OS X. It tries extraordinarily hard to pretend that it's readline. Instead falling back to simple control characters to clear the screen worked very well on both linux and OS X. On Tue, Mar 26, 2013 at 2:14 PM, Peter Eisentraut pete...@gmx.net wrote: On 3/24/13 3:10 PM, Tom Lane wrote: I also concur with the complaint here http://www.postgresql.org/message-id/caazkufzxyj-rt1aec6s0g7zm68tdlfbbm1r6hgrbbxnz80k...@mail.gmail.com that allowing a minimum sleep of 0 is rather dangerous The original watch command apparently silently corrects a delay of 0 to 0.1 seconds. Another minor question is whether we really need the time-of-day in the banner, That's also part of the original watch and occasionally useful, I think. psql-watch-v4.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] json api WIP patch
On Mon, Feb 4, 2013 at 11:38 AM, Robert Haas robertmh...@gmail.com wrote: I suspect both of those are pretty safe from an SQL standards point of view. Of course, as Tom is often wont to point out, the SQL standards committee sometimes does bizarre things, so nothing's perfect, but I'd be rather shocked if any of those got tapped to mean something else. That having been said, I still don't see value in adding operators at all. Good old function call notation seems perfectly adequate from where I sit. Sure, it's a little more verbose, but when you try to too hard make things concise then you end up having to explain to your users why \ditS is a sensible thing for them to type into psql, or why s@\W@sprintf%%%02x,ord($)@e in Perl. I recognize that I may lose this argument, but I've worked with a couple of languages where operators can be overloaded (C++) or defined (ML) and it's just never seemed to work out very well. YMMV, of course. For what my opinion is worth I absolute agree with just having function names. The - in hstore is kind of nice, but it lead me to a whole lot of greif when I couldn't figure out how to create an index using it (turns out you have to use _double_ parens, who knew?), but could create an index on fetchval and assumed that postgres would figure it out. Also a for quite a while it felt just like incantation of when I'd need parens around those operatiors or not. Now that I sorta-kinda-not-really understand the operation precedence rules in postgres/sql standard, I've mostly given up on using cute operators because their much more of a pain on a day-to-day basis.
[HACKERS] patch to add \watch to psql
This patch adds \watch to psql. It is much like the unix equivalent, defaulting to every 2 seconds, and allowing you optionally specify a number of seconds. I will add this to the commit fest app. Thanks, Will Leinweber Example: psql (9.3devel, server 9.1.4) Type help for help. will=# \watch select now(); Watch every 2s Fri Oct 19 17:09:23 2012 now --- 2012-10-19 17:09:23.743176-07 (1 row) Watch every 2s Fri Oct 19 17:09:25 2012 now --- 2012-10-19 17:09:25.745125-07 (1 row) Watch every 2s Fri Oct 19 17:09:27 2012 now --- 2012-10-19 17:09:27.746732-07 (1 row) ^Cwill=# \watch 5 select now(); Watch every 5s Fri Oct 19 17:09:33 2012 now --- 2012-10-19 17:09:33.563695-07 (1 row) Watch every 5s Fri Oct 19 17:09:38 2012 now --- 2012-10-19 17:09:38.564802-07 (1 row) ^Cwill=# \watch select pg_sleep(20); ^CCancel request sent ERROR: canceling statement due to user request will=# psql-watch-v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] query planner does not canonicalize infix operators
I created an index on an hstore function, fetchval(hstore, text), however
when I use the - infix operator which resolves to the very same function,
this index is not used. It should be used.
I have included an example:
Table with hstore index:
de10keipt01939= \d log_data
Table public.log_data
Column | Type | Modifiers
+--+---
id | bigint | not null default
nextval('log_data_id_seq'::regclass)
time | timestamp with time zone |
data | hstore |
Indexes:
index_log_data_by_time btree (time)
index_participant_id btree (fetchval(data, 'participant_id'::text))
query with function notation:
de10keipt01939= explain ANALYZE select * from log_data where
(data-'participant_id')='2851' order by id desc;
QUERY PLAN
-
Sort (cost=16432.56..16433.36 rows=1583 width=315) (actual
time=198.643..198.777 rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
- Seq Scan on log_data (cost=0.00..16415.74 rows=1583 width=315)
(actual time=6.926..198.297 rows=183 loops=1)
Filter: ((data - 'participant_id'::text) = '2851'::text)
Total runtime: 198.922 ms
(6 rows)
query with infix notation:
de10keipt01939= explain ANALYZE select * from log_data where
fetchval(data,'participant_id')='2851' order by id desc;
QUERY PLAN
---
Sort (cost=341.14..341.23 rows=179 width=315) (actual time=0.724..0.841
rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
- Bitmap Heap Scan on log_data (cost=2.35..339.80 rows=179 width=315)
(actual time=0.091..0.489 rows=183 loops=1)
Recheck Cond: (fetchval(data, 'participant_id'::text) =
'2851'::text)
- Bitmap Index Scan on index_participant_id (cost=0.00..2.34
rows=179 width=0) (actual time=0.060..0.060 rows=183 loops=1)
Index Cond: (fetchval(data, 'participant_id'::text) =
'2851'::text)
Total runtime: 1.010 ms
(8 rows)
—Will
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
My coworker Dan suggested that some people copy and paste scripts. However I feel that that is an orthogonal problem and if there is a very high rate of input psql should detect that and turn interactive off. And I still strongly feel that on_error_rollback=interactive should be the default. Until then, I've included this as a PSA at the start of any postgres talks I've given, because it's simply not widely known. On Mon, Nov 14, 2011 at 2:19 PM, Ross Reedstrom reeds...@rice.edu wrote: On Wed, Sep 28, 2011 at 11:47:51AM -0700, David Fetter wrote: On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: See ON_ERROR_ROLLBACK http://www.postgresql.org/docs/9.0/static/app-psql.html I had missed that. Dang, this database product is rich with nice features! :-) +1 I would like it to be on/interactive by default, though. You can have it by putting it in your .psqlrc. If we were just starting out, I'd be all for changing the defaults, but we're not. We'd break things unnecessarily if we changed this default. This discussion died out with a plea for better documentation, and perhaps some form of discoverability. I've scanned ahead and see no further discussion. However, I'm wondering, what use-cases would be broken by setting the default to 'interactive'? Running a non-interactive script by piping it to psql? Reading the code, I see that case is covered: the definition of 'interactive' includes both stdin and stdout are a tty, and the source of commands is stdin. Seems this functionality appeared in version 8.1. Was there discussion re: making it the default at that time? I'm all for backward compatibility, but I'm having trouble seeing what would break. I see that Peter blogged about this from a different angle over a year ago ( http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html ) which drew a comment from Tom Lane that perhaps we need a better/different tool for running scripts. That would argue the defaults for psql proper should favor safe interactive use (autocommit off, anyone?) Peter mentioned the traditional method unix shells use to handle this: different config files are read for interactive vs. non-interactive startup. Seems we have that, just for the one setting ON_ERROR_ROLLBACK. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
[HACKERS] feature request: auto savepoint for interactive psql when in transaction.
I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify the update worked. I suppose I have no one else to blame, but it was really frustrating, to say the least. I assume this has happened to others as well. I only later found out about SAVEPOINT, which I immediately ran the next time I attempted the huge update. psql console, while in a transaction, and while in interactive mode, should savepoint for me. —Will bqjezaraxa=# select count(*) from transactions where log=''; ERROR: relation transactions does not exist bqjezaraxa=# select count(*) from transfers where log=''; ERROR: current transaction is aborted, commands ignored until end of transaction block bqjezaraxa=# oh damn it ; ERROR: syntax error at or near oh LINE 1: oh damn it; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja marko.tiikk...@2ndquadrant.com wrote: Simply add this to your .psqlrc: \set ON_ERROR_ROLLBACK on Thank you Marko and Alvaro for pointing me in the right direction. I set it to 'interactive', which I think makes the most sense. I do wish this behavior was a little more discoverable, even though it is in the manual. —Will -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
