[HACKERS] proposal: rounding up time value less than its unit.

2014-07-09 Thread Tomonari Katsumata
Hi, Several couple weeks ago, I posted a mail to pgsql-doc. http://www.postgresql.org/message-id/53992ff8.2060...@po.ntts.co.jp In this thread, I concluded that it's better to round up the value which is less than its unit. Current behavior (rounding down) has undesirable setting risk, because so

Re: [HACKERS] psql: show only failed queries

2014-07-09 Thread Fujii Masao
On Wed, Jul 9, 2014 at 9:44 PM, Pavel Stehule wrote: >> Barring any objection, I will commit this patch except tab-completion >> part. Committed. > It can be a second discussion, but I am thinking so anywhere when we can use > autocomplete, then we should it - Although it should not to substitut

Re: [HACKERS] things I learned from working on memory allocation

2014-07-09 Thread Amit Kapila
On Tue, Jul 8, 2014 at 1:27 AM, Robert Haas wrote: > > 6. In general, I'm worried that it's going to be hard to keep the > overhead of parallel sort from leaking into the non-parallel case. > With the no-allocator approach, every place that uses > GetMemoryChunkSpace() or repalloc() or pfree() wil

Re: [HACKERS] 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

2014-07-09 Thread Amit Kapila
On Wed, Jul 9, 2014 at 8:42 PM, Marko Tiikkaja wrote: > > Hi Amit, > > > On 5/14/14 6:41 AM, Amit Kapila wrote: >> >> IIUC, the way new design will work is that for new tuple we will now >> get tableoid+TID, modified column values as an input (for inheritance >> tables we will get this for all chi

Re: [HACKERS] better atomics - v0.5

2014-07-09 Thread Amit Kapila
On Wed, Jul 9, 2014 at 8:58 PM, Robert Haas wrote: > On Tue, Jul 8, 2014 at 2:21 AM, Amit Kapila wrote: > > On Thu, Jul 3, 2014 at 10:56 AM, Amit Kapila > > wrote: > > > > Further review of patch: > > 1. > > /* > > * pg_atomic_test_and_set_flag - TAS() > > * > > * Acquire/read barrier semanti

Re: [HACKERS] postgresql.auto.conf and reload

2014-07-09 Thread Tom Lane
Josh Berkus writes: > On 07/08/2014 10:07 AM, Robert Haas wrote: >> I haven't looked at the code in this area too carefully, but it seems >> to me like the flow ought to be: >> >> 1. Read all of the config files and determine what the final value >> present in each config file is. AFAICS we only

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
Michael Paquier writes: > I guess that this implementation is enough as a first shot, particularly > regarding the complexity that default expression import would bring up for > postgres_fdw (point raised during the review, but not really discussed > afterwards). Yeah. I'm actually more concerne

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Michael Paquier
On Thu, Jul 10, 2014 at 9:52 AM, Tom Lane wrote: > I wrote: > > I've started looking at this patch. I wonder whether it's really such > > a great idea to expect the FDW to return a list of parsetrees for > > CREATE FOREIGN TABLE commands; that seems like a recipe for breakage > > anytime we chan

Re: [HACKERS] things I learned from working on memory allocation

2014-07-09 Thread Peter Geoghegan
On Mon, Jul 7, 2014 at 7:29 PM, Peter Geoghegan wrote: >> I do think that's a problem with our sort implementation, but it's not >> clear to me whether it's *more* of a problem for parallel sort than it >> is for single-backend sort. > > If you'll forgive me for going on about my patch on this thr

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
Another thing ... The SQL-MED standard says that "IMPORT FOREIGN SCHEMA ... LIMIT TO (a,b,c)" should throw an error if not all of a,b,c exist as tables in the remote server. It's rather sloppily worded, though, such that it's not clear whether an error is also expected for "EXCEPT (a,b,c)" when n

Re: [HACKERS] Minmax indexes

2014-07-09 Thread Greg Stark
On Wed, Jul 9, 2014 at 10:16 PM, Alvaro Herrera wrote: > there is no hardcoded assumption on the number of index > values stored per heap column, so it is possible to build an opclass > that stores a bounding box column for a geometry heap column, for > instance. I think the more Postgresy thing

Re: [HACKERS] Minmax indexes

2014-07-09 Thread Alvaro Herrera
Josh Berkus wrote: > On 07/09/2014 02:16 PM, Alvaro Herrera wrote: > > The way it works now, each opclass needs to have three support > > procedures; I've called them getOpers, maybeUpdateValues, and compare. > > (I realize these names are pretty bad, and will be changing them.) > > I kind of like

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Michael Paquier
On Thu, Jul 10, 2014 at 2:30 AM, Tom Lane wrote: > Michael Paquier writes: > > With that, I am marking this patch as ready for committer. > > I've started looking at this patch. I wonder whether it's really such > a great idea to expect the FDW to return a list of parsetrees for > CREATE FOREIG

Re: [HACKERS] Minmax indexes

2014-07-09 Thread Peter Geoghegan
On Wed, Jul 9, 2014 at 2:16 PM, Alvaro Herrera wrote: > All this being said, I'm sticking to the name "Minmax indexes". There > was a poll in pgsql-advocacy > http://www.postgresql.org/message-id/53a0b4f8.8080...@agliodbs.com > about a new name, but there were no suggestions supported by more tha

Re: [HACKERS] Minmax indexes

2014-07-09 Thread Josh Berkus
On 07/09/2014 02:16 PM, Alvaro Herrera wrote: > The way it works now, each opclass needs to have three support > procedures; I've called them getOpers, maybeUpdateValues, and compare. > (I realize these names are pretty bad, and will be changing them.) I kind of like "maybeUpdateValues". Very ...

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Peter Geoghegan
On Tue, Jul 8, 2014 at 6:58 AM, Michael Paquier wrote: > 6) Sometimes no hints are returned... Even in simple cases like this one: > =# create table foo (aa int, bb int); > CREATE TABLE > =# select ab from foo; > ERROR: 42703: column "ab" does not exist > LINE 1: select ab from foo; >

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Peter Geoghegan
On Wed, Jul 9, 2014 at 2:19 PM, Alvaro Herrera wrote: >> That's harder than it sounds. You need even more translatable strings >> for variant ereports(). > > Maybe it is possible to rephrase the message so that the translatable > part doesn't need to concern with how many suggestions there are. F

Re: [HACKERS] Minmax indexes

2014-07-09 Thread Alvaro Herrera
Heikki Linnakangas wrote: > On 06/23/2014 08:07 PM, Alvaro Herrera wrote: > I feel that the below would nevertheless be simpler: > > >>I wonder if it would be simpler to just always store the revmap > >>pages in the beginning of the index, before any other pages. Finding > >>the revmap page would

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Alvaro Herrera
Peter Geoghegan wrote: > On Wed, Jul 9, 2014 at 8:08 AM, Greg Stark wrote: > > A simple rule is easier for users to understand as well as to code. I > > would humbly suggest the following: take all the unqualified column > > names, downcase them, check which ones match most closely the > > unmatch

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Peter Geoghegan
On Wed, Jul 9, 2014 at 8:06 AM, Robert Haas wrote: >> Showing up to 2 hints is fine as it does not pollute the error output with >> perhaps unnecessary messages. That's even more protective than for example >> git that prints all the equidistant candidates. However I can't understand >> why it doe

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Peter Geoghegan
On Wed, Jul 9, 2014 at 8:08 AM, Greg Stark wrote: > A simple rule is easier for users to understand as well as to code. I > would humbly suggest the following: take all the unqualified column > names, downcase them, check which ones match most closely the > unmatched column. Show the top 3 matches

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

2014-07-09 Thread Rahila Syed
>But though the code looks better locally than before, the larger problem >is that this is still unsafe. As Pavan pointed out, XLogInsert is called >from inside critical sections, so we can't allocate memory here. >Could you look into his suggestions of other places to do the >allocation, please?

Re: [HACKERS] 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

2014-07-09 Thread Rukh Meski
On Tue, Jun 24, 2014 at 04:08 AM, Heikki Linnakangas wrote: > IMHO this needs to work with inheritance if we are to accept it. It would be > a rather strange limitation for no apparent reason, other than that we > didn't bother to implement it. It doesn't seem very difficult in theory to > add the

[HACKERS] Re: how to find the order of joins from Explain command XML plan output in PostgreSQL

2014-07-09 Thread David G Johnston
csrajmohan wrote > "EXPLAIN (format XML) " command in PostgreSQL9.3.4 gives the plan chosen > by > the optimizer in XML format. In my program, I have to extract certain data > about optimizer plan from this XML output. I am using *LibXML2* library > for > parsing the XML. I had successfully extract

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Alvaro Herrera
Tom Lane wrote: > Michael Paquier writes: > > With that, I am marking this patch as ready for committer. > > I've started looking at this patch. I wonder whether it's really such > a great idea to expect the FDW to return a list of parsetrees for > CREATE FOREIGN TABLE commands; that seems like

Re: [HACKERS] tweaking NTUP_PER_BUCKET

2014-07-09 Thread Tomas Vondra
On 9.7.2014 16:07, Robert Haas wrote: > On Tue, Jul 8, 2014 at 5:16 PM, Tomas Vondra wrote: >> Thinking about this a bit more, do we really need to build the >> hash table on the first pass? Why not to do this: >> >> (1) batching - read the tuples, stuff them into a simple list - >> don't build t

[HACKERS] how to find the order of joins from Explain command XML plan output in PostgreSQL

2014-07-09 Thread Rajmohan C
"EXPLAIN (format XML) " command in PostgreSQL9.3.4 gives the plan chosen by the optimizer in XML format. In my program, I have to extract certain data about optimizer plan from this XML output. I am using *LibXML2* library for parsing the XML. I had successfully extracted information about which re

Re: [HACKERS] pg_resetxlog to clear backup start/end locations.

2014-07-09 Thread Bruce Momjian
On Wed, Jun 25, 2014 at 02:08:26PM +0900, Kyotaro HORIGUCHI wrote: > Hello, thank you for the suggestion. > > I dont' touch what '-n' option shows and rewrite documents for > the option a bit. And '-n' won't show the changes of backup > location. > > === > > > There are some changes which hav

Re: [HACKERS] add line number as prompt option to psql

2014-07-09 Thread Sawada Masahiko
On Mon, Jul 7, 2014 at 8:33 PM, Jeevan Chalke wrote: > Hi, > > Found two (A and B) issues with latest patch: > > A: > -- Set prompts > postgres=# \set PROMPT1 '%/[%l]%R%# ' > postgres[1]=# \set PROMPT2 '%/[%l]%R%# ' > > postgres[1]=# > postgres[1]=# select > postgres[2]-# * > postgres[3]-# from >

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
Michael Paquier writes: > With that, I am marking this patch as ready for committer. I've started looking at this patch. I wonder whether it's really such a great idea to expect the FDW to return a list of parsetrees for CREATE FOREIGN TABLE commands; that seems like a recipe for breakage anytim

Re: [HACKERS] postgresql.auto.conf and reload

2014-07-09 Thread Josh Berkus
On 07/08/2014 06:10 PM, Mark Kirkwood wrote: > On 09/07/14 05:13, Josh Berkus wrote: >> On 07/06/2014 01:27 AM, Christoph Berg wrote: >>> Another could be that during initdb all the uncommented settings be written to postgresql.auto.conf file rather than to postgresql.conf. I think we can

Re: [HACKERS] postgresql.auto.conf and reload

2014-07-09 Thread Josh Berkus
On 07/08/2014 08:18 PM, Amit Kapila wrote: >> Yes, but even well behaved users will see this type of error, because > initdb uncomments certain values (ones that are dead certs for being > changed via ALTER SYSTEM subsequently like shared_buffers), and then - > bang! your next reload gets that "you

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-09 Thread Bruce Momjian
On Wed, Jul 9, 2014 at 10:13:17AM -0400, Robert Haas wrote: > >> Uh, I guess we could write some code that iterates over all tables and > >> finds the tables that should have TOAST tables, but don't (because > >> binary-upgrade backend mode suppressed their creation), and adds them. > >> > >> Howe

Re: [HACKERS] RLS Design

2014-07-09 Thread Stephen Frost
KaiGai, * Kohei KaiGai (kai...@kaigai.gr.jp) wrote: > 2014-07-09 15:07 GMT+09:00 Stephen Frost : > > * Kohei KaiGai (kai...@kaigai.gr.jp) wrote: > >> What I'd like to implement is adjustment of query like: > >> SELECT * FROM t1 WHERE (x like '%abc%') AND (quals by built-in RLS) > >> AN

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2014-07-09 Thread Robert Haas
On Tue, Jul 8, 2014 at 3:07 AM, Etsuro Fujita wrote: > Attached is a WIP patch for the following: > > /* > * postgresPlanForeignModify > * Plan an insert/update/delete operation on a foreign table > * > * Note: currently, the plan tree generated for UPDATE/DELETE will always > * include

Re: [HACKERS] better atomics - v0.5

2014-07-09 Thread Robert Haas
On Tue, Jul 8, 2014 at 2:21 AM, Amit Kapila wrote: > On Thu, Jul 3, 2014 at 10:56 AM, Amit Kapila > wrote: >> On Tue, Jul 1, 2014 at 4:10 PM, Andres Freund >> wrote: > > Further review of patch: > 1. > /* > * pg_atomic_test_and_set_flag - TAS() > * > * Acquire/read barrier semantics. > */ >

Re: [HACKERS] 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

2014-07-09 Thread Marko Tiikkaja
On 5/11/14 6:47 PM, Tom Lane wrote: The $64 question is whether we'd accept an implementation that fails if the target table has children (ie, is partitioned). That seems to me to not be up to the project's usual quality expectations, but maybe if there's enough demand for a partial solution we

Re: [HACKERS] 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

2014-07-09 Thread Marko Tiikkaja
Hi Amit, On 5/14/14 6:41 AM, Amit Kapila wrote: IIUC, the way new design will work is that for new tuple we will now get tableoid+TID, modified column values as an input (for inheritance tables we will get this for all child tables as well) for ModifyTable and get old tuple (which in current cas

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Greg Stark
On Wed, Jul 9, 2014 at 7:29 AM, Peter Geoghegan wrote: > Everyone is going to have an opinion on something like that. I was > showing deference to the general concern about the absolute (as > opposed to relative) quality of the HINTs in the event of equidistant > matches by having no two suggestio

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Robert Haas
On Wed, Jul 9, 2014 at 2:10 AM, Michael Paquier wrote: > On Wed, Jul 9, 2014 at 5:56 AM, Peter Geoghegan wrote: >> On Tue, Jul 8, 2014 at 1:55 PM, Peter Geoghegan wrote: >> > I was worried about the common case where a >> > column name is misspelled that would otherwise be ambiguous, which is >>

Re: [HACKERS] [PATCH] Improve bytea error messages

2014-07-09 Thread Robert Haas
On Tue, Jul 8, 2014 at 12:49 AM, Craig Ringer wrote: > On 07/08/2014 07:44 AM, Robert Haas wrote: >> On Sun, Jul 6, 2014 at 4:17 AM, Craig Ringer wrote: >>> After someone reported somewhat less than informative errors in bytea >>> decoding (http://stackoverflow.com/q/24588866/398670) I thought I'

Re: [HACKERS] postgresql.auto.conf and reload

2014-07-09 Thread Christoph Berg
Re: Tom Lane 2014-07-08 <28635.1404844...@sss.pgh.pa.us> > > Sounds like a 9.5 feature, though. > > No, ALTER SYSTEM is there now and it needs to work right in its first > release. I will go fix this if nobody else does. I'd like to throw in again that imho this should include ALTER SYSTEM RESET

Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()

2014-07-09 Thread Alvaro Herrera
Peter Geoghegan wrote: > On Tue, Jul 8, 2014 at 11:25 PM, Michael Paquier > wrote: > >> > 5) Do we want hints on system columns as well? > >> I think it's obvious that the answer must be no. That's going to > >> frequently result in suggestions of columns that users will complain > >> aren't even

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-09 Thread Robert Haas
>> Uh, I guess we could write some code that iterates over all tables and >> finds the tables that should have TOAST tables, but don't (because >> binary-upgrade backend mode suppressed their creation), and adds them. >> >> However, that would be a lot of code and might be risky to backpatch. >> Th

Re: [HACKERS] tweaking NTUP_PER_BUCKET

2014-07-09 Thread Robert Haas
On Tue, Jul 8, 2014 at 5:16 PM, Tomas Vondra wrote: > Thinking about this a bit more, do we really need to build the hash > table on the first pass? Why not to do this: > > (1) batching > - read the tuples, stuff them into a simple list > - don't build the hash table yet > > (2) building t

Re: [HACKERS] postgresql.auto.conf and reload

2014-07-09 Thread Amit Kapila
On Wed, Jul 9, 2014 at 10:14 AM, Tom Lane wrote: > > Amit Kapila writes: > > On Wed, Jul 9, 2014 at 6:40 AM, Mark Kirkwood < mark.kirkw...@catalyst.net.nz> > > wrote: > >> Yes, but even well behaved users will see this type of error, because > >> initdb uncomments certain values (ones that are de

[HACKERS] FW: [postgres-discuss] Insert query hangs

2014-07-09 Thread M Tarkeshwar Rao
I have the hang issue in Postgres, When I am going to insert into record in a table. Table structure is enclosed in the discussion. Also we found the issue with unique indexes. When I removed the unique index insert operation is working fine. I need help from this core group that weather this is

Re: [HACKERS] [BUGS] BUG #9652: inet types don't support min/max

2014-07-09 Thread Haribabu Kommi
On Mon, Jul 7, 2014 at 6:59 PM, Asif Naeem wrote: > Hi Haribabu, > > Thank you for sharing the patch. I have spent some time to review the > changes. Overall patch looks good to me, make check and manual testing seems > run fine with it. There seems no related doc/sgml changes ?. Patch added > net

Re: [HACKERS] [bug fix or improvement?] Correctly place DLLs for ECPG apps in bin folder

2014-07-09 Thread MauMau
From: "Alvaro Herrera" I think the suggestion by Peter Eisentraut upthread was pretty reasonable -- the Makefiles are already aware that they are building a shared library, so scrape the info off them. The less hardcoded stuff in the msvc framework, the better. I overlooked his mail. Do you

Re: [HACKERS] psql: show only failed queries

2014-07-09 Thread Pavel Stehule
2014-07-09 14:39 GMT+02:00 Fujii Masao : > On Wed, Jul 9, 2014 at 9:06 PM, Pavel Stehule > wrote: > > Hi > > > > > > 2014-07-09 7:07 GMT+02:00 Fujii Masao : > > > >> On Mon, Jun 30, 2014 at 8:33 PM, Pavel Stehule > > >> wrote: > >> > > >> > > >> > > >> > 2014-06-30 13:01 GMT+02:00 Abhijit Menon-

Re: [HACKERS] psql: show only failed queries

2014-07-09 Thread Fujii Masao
On Wed, Jul 9, 2014 at 9:06 PM, Pavel Stehule wrote: > Hi > > > 2014-07-09 7:07 GMT+02:00 Fujii Masao : > >> On Mon, Jun 30, 2014 at 8:33 PM, Pavel Stehule >> wrote: >> > >> > >> > >> > 2014-06-30 13:01 GMT+02:00 Abhijit Menon-Sen : >> > >> >> At 2014-06-30 12:48:30 +0200, pavel.steh...@gmail.com

Re: [HACKERS] psql: show only failed queries

2014-07-09 Thread Pavel Stehule
Hi 2014-07-09 7:07 GMT+02:00 Fujii Masao : > On Mon, Jun 30, 2014 at 8:33 PM, Pavel Stehule > wrote: > > > > > > > > 2014-06-30 13:01 GMT+02:00 Abhijit Menon-Sen : > > > >> At 2014-06-30 12:48:30 +0200, pavel.steh...@gmail.com wrote: > >> > > >> > + > >> > + Print a failed SQL comman

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-09 Thread Jeevan Chalke
Hi, > With further testing I noticed that the patch was not allowing ANTI joins > in cases like this: > > explain select * from a where id not in(select x from b natural join c); > > > I too found this with natural joins and was about to report that. But its good that you found that and fixed it

Re: [HACKERS] change alter user to be a true alias for alter role

2014-07-09 Thread Jov
Sorry for the late resp,I will try to update the patch. Jov blog: http:amutu.com/blog 2014-07-02 4:17 GMT+08:00 Abhijit Menon-Sen : > At 2014-06-27 16:11:21 +0200, vik.fear...@dalibo.com wrote: > > > > After a week of silence from Jov, I decided to do this myself since i

[HACKERS] Insert query hangs

2014-07-09 Thread M Tarkeshwar Rao
Hi all, We have a table in a database DB1 with name Test. We imported this database from another machine. When I fire insert statement it is going in the hang state. Then I created another table with same structure and with same data within it as in table Test. Then I fired the insert statemen