[HACKERS] Add column-name hint to log messages generated by inserts when varchars don't fit
Hi everybody again, on our production servers I have quite some errors due to excessively long varchar-values which application-code tries to insert into tables and which dont fit. The tables have many columns, the statements are not readable and many columns happen to have the same length. Powers of 2 most often for some odd reason ... I fired up gdb and saw that the error message is generated during the preprocessing of the query where some kind of the constant-folding/constant-elimination happens on the parse-tree. I went ahead and added a try/catch at some point upwards in the call-stack where at least i have the contact of the T_TargetEntry. That has a field resname which gives me exactly the information i need... The column which was overflown. With that info i can fix the application code much more easily. Relation name was out of reach for me, there is a void* passed transparently to the constant-mutator but that is not checkable at the point. That context contains the original top-level statement node however. The patch just adds a bit of hinting to the error message and goes on.. That is all but really helpful to me and potentially also others. Attached Patch has more Infos and comments. Regards from Germany, Stepan Stepan Rutz Phone: +49 (0) 178 654 9284 Email: stepan.r...@gmx.de Earth: Brunnenallee 25a, 50226 Frechen, Germany columname_hint.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] Add column-name hint to log messages generated by inserts when varchars don't fit
Hi everybody again, (Resending this EMail again because apparently I have just send in HTML format, which wasn't my intention) on our production servers I have quite some errors due to excessively long varchar-values which application-code tries to insert into tables and which don't fit. The errors look like ERROR: value too long for type character varying(4) This is not helping me much. The patch will turn this too ERROR: value too long for type character varying(4) (hint: column-name is mycolumn) if the column that was overflown was mycolumn. The tables have many columns, the statements are not readable and many columns happen to have the same length. Powers of 2 most often for some odd reason ... I fired up gdb and saw that the error message is generated during the preprocessing of the query where some kind of the constant-folding/constant-elimination happens on the parse-tree. I went ahead and added a try/catch at some point upwards in the call-stack where at least i have the contact of the T_TargetEntry. That has a field resname which gives me exactly the information i need... The column which was overflown. With that info i can fix the application code much more easily. Relation name was out of reach for me, there is a void* passed transparently to the constant-mutator but that is not checkable at the point. That context contains the original top-level statement node however. The patch just adds a bit of hinting to the error message and goes on.. That is all but really helpful to me and potentially also others. Attached Patch has more Infos and comments. Regards from Germany, Stepan Stepan Rutz Phone: +49 (0) 178 654 9284 Email: stepan.r...@gmx.de Earth: Brunnenallee 25a, 50226 Frechen, Germany columname_hint.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] Patch for psql History Display on MacOSX
Hello again, just my thoughts… in psql \s without a file is nice for me iff going through less (e.g. pager), but for the most part it doesn't work at all on mac-osx. so nothing to lose for the mac psql users. regards, stepan Am 03.09.2014 um 07:45 schrieb Noah Misch n...@leadboat.com: On Tue, Sep 02, 2014 at 09:49:56AM -0400, Tom Lane wrote: Noah Misch n...@leadboat.com writes: I'm with you that far. Given a patch that does not change \s /tmp/foo and that makes \s equivalent to \s /tmp/foo + \! cat /tmp/foo /dev/tty, back-patch by all means. No patch posted on this thread is so surgical, hence my objection. In particular, your latest patch revision changes \s /tmp/foo to match the novel output the patch introduces for plain \s. \s /tmp/foo would no longer write data that libedit can reload as a history file. BTW, I failed last night to produce a coherent argument against that particular point, but consider this. What are the main use-cases for \s to a file? I argue that they are 1. Create a human-readable record of what you did. 2. Create the starting point for a SQL script file. I do not deny it's possible that somebody out there is also using \s for 3. Create a file that I can overwrite ~/.psql_history with later. But if this is being done in the field at all, surely it is miles behind the applications listed above. I'm unprepared to speculate about the relative prevalence of those use cases. Now, if you are using libreadline, the output of \s has always been perfectly fit for purposes 1 and 2, because it's plain text of the history entries. Moreover, it is *not* particularly fit for purpose 3, because intra-command newlines aren't encoded. Yes, you could get libreadline to read the file, but multiline SQL commands will be seen as multiple history entries which is very far from convenient to use. (This adds to my suspicion that nobody is doing #3 in practice.) On the other hand, if you are using libedit, purpose 3 works great but the output is utterly unfit for either purpose 1 or 2. Here are the first few lines of ~/.psql_history on one of my Macs: _HiStOrY_V2_ explain\040verbose\^A\040\040select\0401\^Aunion\^A\040\040select\0402; \\q select\0404; explain\040verbose\^A\040\040select\0401\^Aunion\^A\040\040select\0402; select\04044; \\q \\s \\s\040foobar \\q What the proposed patch does is ensure that \s produces plain text regardless of which history library you are using. I think arguing that we shouldn't do that is stretching the concept of backwards compatibility well past the breaking point. Given the negligible urgency to improve \s, the slightest compatibility hazard justifies punting this work from back-patch to master-only. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers smime.p7s Description: S/MIME cryptographic signature
[HACKERS] Patch for psql History Display on MacOSX
Hi everbody, My first mail to this one, so please be mild. I fired up the debugger to get this item going, which is also on the Todo List. Attached is a very trivial patch as a basis for discussion that at least makes \s (show history) work in psql on Macs. Macs uses libedit, which has a libreadline interface. A short investigation showed that the way psql iterates over the history does not work with libedit. I changed the iteration scheme to an index based loop (see code and comments), which seemed to be the only working option for both readline and libedit. In any case, i have tested and compiled this on MacOX 10.9.3 and Linux. Windows doesn’t have the pager in the first place. As noted in the todo I have made this code pay attention to the pager configuration from psql. The odd part is when your history opens in less you see the top part rather then the bottom part, but the bottom is just a single keystroke away. If pager is disabled history is just printed fine. Please note that this didn’t work at all on Mac before. Could this go into …./regress/sql/psql.sql at all? I am not sure on that one. Regards, Stepan psql_pager_history_libedit_and_readline.patch Description: Binary data smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Patch for psql History Display on MacOSX
Thanks Tom. This would help the poor mac-osx guys like me. I guess this is not that important because no one runs a production server on OS-X. Back patching to 9.3 won’t work as is, some minor conflict was there. Anyway, I am sure the iteration used in encode_history and decode_history in input.c does not work on libedit. Regards from cologne, Stepan Am 01.09.2014 um 20:05 schrieb Tom Lane t...@sss.pgh.pa.us: Stepan Rutz stepan.r...@gmx.de writes: Attached is a very trivial patch as a basis for discussion that at least makes \s (show history) work in psql on Macs. Macs uses libedit, which has a libreadline interface. Hm. The $64 question here is whether we can assume that history_get() exists and works compatibly in every interesting version of libreadline and libedit. I poked into the oldest version of GNU readline I could find, 4.0 (released in 1999), and that has it. The oldest libedit I have around is the one that came with OSX 10.4 (the CVS marker in readline.h from that says 2004/01/17). That has it too. So that looks pretty good. The readline code says that the argument ranges from history_base up, not from 1 up as this patch assumes. And it looks like history_base can change once the max number of stored lines is exceeded, so we can't assume that 1 is good enough. Fortunately, the global variable history_base also exists in both libraries (though it looks like it never changes from 1 in libedit). Functionally this seems like a clear win over what we had, especially since it supports using the pager. I'm inclined to think we should not only apply this change but back-patch it. One thing worth thinking about: should we use a history_get() loop like this for *all* \s commands, even when the target file is a regular file not /dev/tty? libedit's version of write_history does not write the history in the clear exactly, which you would think is the behavior wanted when saving a command history for any purpose other than updating ~/.psql_history. Such a change would break a workflow that involves doing \s to some random file and then copying that file to ~/.psql_history, but I find it hard to fathom why anyone would do that. There are a couple other minor bugs and some cosmetic things I don't like in this patch, but I'm willing to fix it up and commit it if there are not objections. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] pg_filedump for 9.4?
Hi community, while I am currently investigating why a certain table with highly redundant and utterly verbose xml becomes worse storage wise when making the xml more compact. Since i am quite new to this, I believe its the lz compression in the text database. But thats irrelevant now, just mentioning because tools like pg_filedump allow people like me to help themselves and a basic understanding of things. During checking I noticed pg_filedump (current from git.postgresql.org incl. the below mentioned commit) does not compile on Mac-OSX. Afaik it will not compile as soon as post.h comes into play and USE_REPL_SNPRINTF is defined. Then printf and sprintf (ouch particular but code path seems tolerable) in the source of pg_filedump become pg_printf and so on. These replacements are part of postgres and can’t be linked into the standalone pg_filedump. At least that is certainly not the intention. Putting #undef sprintf #undef print after the includes in pg_filedump fixes the mac compile and imho all builds where the USE_REPL_SNPRINTF is defined as a side effect of include postgres.h effectively taking printf from me. Not sure how to deal with this issue correctly so this is just for your consideration since the issue is a bit broader imho. Regards, Stepan Am 31.08.2014 um 17:25 schrieb Fabrízio de Royes Mello fabriziome...@gmail.com: Em domingo, 31 de agosto de 2014, Christoph Berg c...@df7cb.de escreveu: Re: Fabrízio de Royes Mello 2014-06-25 CAFcNs+oAb8h-0w2vLEWj6R-Gv=xizgdBya3K=SCd_9Tjyo=z...@mail.gmail.com On Wed, Jun 25, 2014 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Would like that, but I'm not sure what pgindent will do with the // comments. It's been on my to-do list to switch all the comments to C89 style and then pgindent it, but I don't see myself getting to that in this decade :-( I changed all // comments to /* */ and run pgindent. I've pushed these patches to the git repository, thanks. Thanks! Fabrízio de Royes Mello -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello smime.p7s Description: S/MIME cryptographic signature