Re: [HACKERS] double writes using double-write buffer approach [WIP]

2012-02-13 Thread Amit Kapila
Dan, I believe your approach of double buffer write is right as it has potential that it can avoid the latency backends incur during full page writes after checkpoint. Although there are chances that overall I/O will be more in this case but if we can make sure that in most scenarios backend

Re: [HACKERS] Checkpoint sync pause

2012-02-13 Thread Amit Kapila
Without sorted checkpoints (or some other fancier method) you have to write out the entire pool before you can do any fsyncs. Or you have to do multiple fsyncs of the same file, with at least one occurring after the entire pool was written. With a sorted checkpoint, you can start issuing

Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Chetan Suttraway
On Sun, Feb 12, 2012 at 7:36 AM, Vik Reykja vikrey...@gmail.com wrote: I decided to take a crack at the todo item created from the following post: http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php The attached patch makes the desired changes in both code and function

Re: [HACKERS] bitfield and gcc

2012-02-13 Thread Marti Raudsepp
On Sat, Feb 11, 2012 at 01:54, Gaetano Mendola mend...@gmail.com wrote: I wonder if somewhere in Postgres source we are relying on the GCC correct behaviour regarding the read-modify-write of bitfield in structures. Probably not. I'm pretty sure that we don't have any bitfields, since not all

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Kohei KaiGai
2012/2/13 Greg Smith g...@2ndquadrant.com: On 02/11/2012 08:14 PM, Gaetano Mendola wrote: The trend is to have server capable of running CUDA providing GPU via external hardware (PCI Express interface with PCI Express switches), look for example at PowerEdge C410x PCIe Expansion Chassis from

Re: [HACKERS] Finer Extension dependencies

2012-02-13 Thread Dimitri Fontaine
Hi, Sorry for the delays, I'm back on PostgreSQL related work again. Hitoshi Harada umi.tan...@gmail.com writes: I just tried DROP EXTENSION now, and found it broken :( Please find v2 of the patch. I did change the dependency management in between the simple cases and the more challenging

Re: [HACKERS] ecpglib use PQconnectdbParams

2012-02-13 Thread Michael Meskes
Because connect_timeout is a separate libpq connection parameter, but now it's stuck into options. It might have worked more or less by accident before. So it is not an option, right? But the old function accepted it as an option it seems. It's not clear to me why this only appears on

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2012-02-13 Thread Etsuro Fujita
(2012/02/10 20:39), Shigeru Hanada wrote: (2012/02/08 20:51), Shigeru Hanada wrote: Attached revised patches. Changes from last version are below. snip I've found and fixed a bug which generates wrong remote query when any column of a foreign table has been dropped. Also regression test

Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

2012-02-13 Thread Christoph Berg
Re: Alex Hunsaker 2012-02-10 cafapbrr9y1fu6gpvu+8ta8vty6qvcm3dfarkt8jg_ehgetx...@mail.gmail.com Does the attached fix the issue for you? Yes. :) Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature

Re: [HACKERS] Bugs/slowness inserting and indexing cubes

2012-02-13 Thread Robert Haas
On Thu, Feb 9, 2012 at 3:37 PM, Jay Levitt jay.lev...@gmail.com wrote: So my pre-built 9.1.2 takes 434s, my source-built 9.2 takes 509s, and (probably both of our) 9.1-HEAD takes 1918s... is that something to worry about, and if so, are there any tests I can run to assist? That bug doesn't

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Robert Haas
On Sat, Feb 11, 2012 at 4:23 AM, Simon Riggs si...@2ndquadrant.com wrote: Yeh, I was thinking we would do well to implement cached sequences for say first 1000 sequences. Another option might be to store all the sequences for a particular database in a single underlying data file. The current

Re: [HACKERS] initdb and fsync

2012-02-13 Thread Robert Haas
On Fri, Feb 10, 2012 at 3:57 PM, Peter Eisentraut pete...@gmx.net wrote: On sön, 2012-02-05 at 10:53 -0800, Jeff Davis wrote: initdb should do these syncs by default and offer an option to disable them. For test frameworks that run initdb often, that makes sense. But for developers, it

Re: [HACKERS] Bugs/slowness inserting and indexing cubes

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 7:45 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 9, 2012 at 3:37 PM, Jay Levitt jay.lev...@gmail.com wrote: So my pre-built 9.1.2 takes 434s, my source-built 9.2 takes 509s, and (probably both of our) 9.1-HEAD takes 1918s... is that something to worry

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Marti Raudsepp
On Mon, Feb 13, 2012 at 15:08, Robert Haas robertmh...@gmail.com wrote: Honestly, I think the biggest hassle of the OID code is not so much the way they're generated as the way they're stored within heap tuples.  I've wondered whether we should go through the system catalogs and replace all of

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 8:51 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Feb 13, 2012 at 15:08, Robert Haas robertmh...@gmail.com wrote: Honestly, I think the biggest hassle of the OID code is not so much the way they're generated as the way they're stored within heap tuples.  I've

Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Robert Haas
On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@gmail.com wrote: I decided to take a crack at the todo item created from the following post: http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php The attached patch makes the desired changes in both code and function

Re: [HACKERS] index-only quals vs. security_barrier views

2012-02-13 Thread Robert Haas
On Sat, Feb 11, 2012 at 7:16 AM, Jesper Krogh jes...@krogh.cc wrote: Ok, but there are still cases where we don't even need to construct a data tuple at all: 2012-02-11 13:14:01.579 jk=# explain select count(*) from testtable where fts @@ to_tsquery('english','test1');                        

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Robert Haas
On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: On Fri, Feb 10, 2012 at 01:59:18PM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 6:42 AM, Noah Misch n...@leadboat.com wrote: I like the design you have chosen. ?It would find applications beyond TRUNCATE, so your use

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Andres Freund
On Monday, February 13, 2012 02:08:08 PM Robert Haas wrote: On Sat, Feb 11, 2012 at 4:23 AM, Simon Riggs si...@2ndquadrant.com wrote: Yeh, I was thinking we would do well to implement cached sequences for say first 1000 sequences. Another option might be to store all the sequences for a

Re: [HACKERS] When do we lose column names?

2012-02-13 Thread Robert Haas
On Sat, Feb 11, 2012 at 11:11 AM, Andrew Dunstan aduns...@postgresql.org wrote: Other candidates I have found that don't set colnames and should probably use dummy names are:   * src/backend/parser/gram.y (row: production)   *

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 9:41 AM, Andres Freund and...@anarazel.de wrote: On Monday, February 13, 2012 02:08:08 PM Robert Haas wrote: On Sat, Feb 11, 2012 at 4:23 AM, Simon Riggs si...@2ndquadrant.com wrote: Yeh, I was thinking we would do well to implement cached sequences for say first 1000

Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Vik Reykja
On Mon, Feb 13, 2012 at 15:25, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@gmail.com wrote: I decided to take a crack at the todo item created from the following post: http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php

Re: [HACKERS] Removing special case OID generation

2012-02-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Another option might be to store all the sequences for a particular database in a single underlying data file. We've looked into that before, and found some roadblocks IIRC, though it probably isn't completely infeasible. See the archives.

Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Vik Reykja
On Mon, Feb 13, 2012 at 11:02, Chetan Suttraway chetan.suttra...@enterprisedb.com wrote: The patch was not getting applied. Was seeing below message: postgresql$ git apply /Downloads/unchanged.patch error: src/backend/utils/adt/ri_triggers.c: already exists in working directory Have come

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: The example that I remember was related to SELECT FOR UPDATE/SELECT FOR SHARE. The idea of those statements is that you want to prevent the row from being updated or deleted until some other concurrent action is complete; for example, in the case of

Re: [HACKERS] When do we lose column names?

2012-02-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 11, 2012 at 11:11 AM, Andrew Dunstan aduns...@postgresql.org wrote: Do we actually need to bother with these cases? In flatten_join_alias_vars_mutator(), we've got a RangeTblEntry to work with. I think the column names are to be found in

Re: [HACKERS] auto_explain produces invalid JSON

2012-02-13 Thread Tom Lane
[ sorry for ignoring this over the weekend --- I wasn't feeling very well ] Andrew Dunstan and...@dunslane.net writes: On 02/11/2012 03:22 PM, Tom Lane wrote: I'm inclined to think that this is auto_explain's error, not that of the core code, ie we should be changing the output. Well, maybe

Re: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)

2012-02-13 Thread Fujii Masao
On Mon, Feb 13, 2012 at 8:37 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 13.02.2012 01:04, Jeff Janes wrote: Attached is my quick and dirty attempt to set XLP_FIRST_IS_CONTRECORD.  I have no idea if I did it correctly, in particular if calling GetXLogBuffer(CurrPos)

[HACKERS] Access Error Details from PL/pgSQL

2012-02-13 Thread David E. Wheeler
Hackers, In PL/pgSQL exception handling, I'm able to access the error code (SQLSTATE) and error message (SQLERRM). Is there any way to get at error details (yet)? If not, could SQLDETAIL or some such be added? Thanks, David -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Access Error Details from PL/pgSQL

2012-02-13 Thread Pavel Stehule
Hello 2012/2/13 David E. Wheeler da...@justatheory.com: Hackers, In PL/pgSQL exception handling, I'm able to access the error code (SQLSTATE) and error message (SQLERRM). Is there any way to get at error details (yet)? If not, could SQLDETAIL or some such be added? no in stable

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Gaetano Mendola
On Feb 13, 2012 11:39 a.m., Kohei KaiGai kai...@kaigai.gr.jp wrote: 2012/2/13 Greg Smith g...@2ndquadrant.com: On 02/11/2012 08:14 PM, Gaetano Mendola wrote: The trend is to have server capable of running CUDA providing GPU via external hardware (PCI Express interface with PCI Express

Re: [HACKERS] Access Error Details from PL/pgSQL

2012-02-13 Thread David E. Wheeler
On Feb 13, 2012, at 9:30 AM, Pavel Stehule wrote: no in stable http://www.depesz.com/2011/07/20/waiting-for-9-2-stacked-diagnostics-in-plpgsql/ Ah, great, I had forgotten about that. Thank you, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] auto_explain produces invalid JSON

2012-02-13 Thread Andrew Dunstan
On 02/13/2012 11:15 AM, Tom Lane wrote: [ sorry for ignoring this over the weekend --- I wasn't feeling very well ] Andrew Dunstanand...@dunslane.net writes: On 02/11/2012 03:22 PM, Tom Lane wrote: I'm inclined to think that this is auto_explain's error, not that of the core code, ie we

Re: [HACKERS] auto_explain produces invalid JSON

2012-02-13 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: On 02/13/2012 11:15 AM, Tom Lane wrote: After looking a bit more at the existing explain code, it seems like the critical issue is that explain.c has ExplainOpenGroup/ExplainCloseGroup calls around the ExplainPrintPlan call (see ExplainOnePlan), while

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 10:48 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Well, personally I have a hard time calling READ COMMITTED behavior sensible.  Consider this: [ gigantic pile of fail ] Yeah, that's bad all right. I think it's hard to argue that the current behavior is

Re: [HACKERS] auto_explain produces invalid JSON

2012-02-13 Thread Andrew Dunstan
On 02/13/2012 12:48 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 02/13/2012 11:15 AM, Tom Lane wrote: After looking a bit more at the existing explain code, it seems like the critical issue is that explain.c has ExplainOpenGroup/ExplainCloseGroup calls around the

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Greg Stark
I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be overtaken by changes in the hardware and innovations in parallel

Re: [HACKERS] auto_explain produces invalid JSON

2012-02-13 Thread Andrew Dunstan
On 02/13/2012 01:33 PM, Andrew Dunstan wrote: On 02/13/2012 12:48 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 02/13/2012 11:15 AM, Tom Lane wrote: After looking a bit more at the existing explain code, it seems like the critical issue is that explain.c has

Re: [HACKERS] When do we lose column names?

2012-02-13 Thread Andrew Dunstan
On 02/13/2012 11:00 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Sat, Feb 11, 2012 at 11:11 AM, Andrew Dunstan aduns...@postgresql.org wrote: Do we actually need to bother with these cases? In flatten_join_alias_vars_mutator(), we've got a RangeTblEntry to work with. I

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Well, personally I have a hard time calling READ COMMITTED behavior sensible. Consider this: [ gigantic pile of fail ] Yeah, that's bad all right. I think it's hard to argue that the current

Re: [HACKERS] Bugs/slowness inserting and indexing cubes

2012-02-13 Thread Jay Levitt
Robert Haas wrote: On Mon, Feb 13, 2012 at 7:45 AM, Robert Haasrobertmh...@gmail.com wrote: On Thu, Feb 9, 2012 at 3:37 PM, Jay Levittjay.lev...@gmail.com wrote: So my pre-built 9.1.2 takes 434s, my source-built 9.2 takes 509s, and (probably both of our) 9.1-HEAD takes 1918s... is that

Re: [HACKERS] psql tab completion for SELECT

2012-02-13 Thread Peter Eisentraut
On tor, 2012-02-09 at 23:02 +0100, Dimitri Fontaine wrote: Peter Eisentraut pete...@gmx.net writes: Make tab-completion complete also function names – like: SELECT pg_gettabtab to see all functions that start with pg_get. Make tab-completion work for columns in SELECT. I know that when

Re: [HACKERS] psql tab completion for SELECT

2012-02-13 Thread Peter Eisentraut
On fre, 2012-02-10 at 01:24 -0500, Tom Lane wrote: That seems pretty nearly entirely bogus. What is the argument for supposing that the word right after SELECT is a function name? I would think it would be a column name (from who-knows-what table) much more often. That's what the patch

Re: [HACKERS] pl/python long-lived allocations in datum-dict transformation

2012-02-13 Thread Jan Urbański
On 12/02/12 00:48, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: This is annoying for functions that plough through large tables, doing some calculation. Attached is a patch that does the conversion of PostgreSQL Datums into Python dict objects in a scratch

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Gaetano Mendola
On Feb 13, 2012 7:49 p.m., Greg Stark st...@mit.edu wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be

Re: [HACKERS] Speed dblink using alternate libpq tuple storage

2012-02-13 Thread Marko Kreen
On Tue, Feb 07, 2012 at 04:44:09PM +0200, Marko Kreen wrote: Although it seems we could allow exceptions, at least when we are speaking of Postgres backend, as the connection and result are internally consistent state when the handler is called, and the partial PGresult is stored under

Re: [HACKERS] pl/python long-lived allocations in datum-dict transformation

2012-02-13 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: On 12/02/12 00:48, Tom Lane wrote: What's more, it's unclear that it won't malfunction altogether if the function is used recursively (ie, what if PLyDict_FromTuple ends up calling the same function again?) I was a bit worried

[HACKERS] pg_test_fsync performance

2012-02-13 Thread Bruce Momjian
I have heard complaints that /contrib/pg_test_fsync is too slow. I thought it was impossible to speed up pg_test_fsync without reducing its accuracy. However, now that I some consumer-grade SATA 2 drives, I noticed that the slowness is really in the open_sync test: Compare open_sync

Re: [HACKERS] pg_test_fsync performance

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 7:42 PM, Bruce Momjian br...@momjian.us wrote: I have heard complaints that /contrib/pg_test_fsync is too slow.  I thought it was impossible to speed up pg_test_fsync without reducing its accuracy. However, now that I some consumer-grade SATA 2 drives, I noticed that

Re: [HACKERS] pg_test_fsync performance

2012-02-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Instead of or in addition to a fixed number operations per test, maybe we should cut off each test after a certain amount of wall-clock time, like 15 seconds. +1, I was about to suggest the same thing. Running any of these tests for a fixed number of

Re: [HACKERS] pg_test_fsync performance

2012-02-13 Thread Bruce Momjian
On Mon, Feb 13, 2012 at 08:28:03PM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Instead of or in addition to a fixed number operations per test, maybe we should cut off each test after a certain amount of wall-clock time, like 15 seconds. +1, I was about to suggest

[HACKERS] SSI rw-conflicts and 2PC

2012-02-13 Thread Dan Ports
Looking over the SSI 2PC code recently, I noticed that I overlooked a case that could lead to non-serializable behavior after a crash. When we PREPARE a serializable transaction, we store part of the SERIALIZABLEXACT in the statefile (in addition to the list of SIREAD locks). One of the pieces of

Re: [v9.2] LEAKPROOF attribute of FUNCTION (Re: [HACKERS] [v9.2] Fix Leaky View Problem)

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 7:51 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I rebased the patch due to the updates of pg_proc.h. Please see the newer one. Thanks, Thanks, committed. I think, though, that some further adjustment is needed here, because you currently can't do ALTER FUNCTION ... NO

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2012-02-13 Thread Shigeru Hanada
(2012/02/13 20:50), Etsuro Fujita wrote: The patches have been applied, but role-related regression tests failed in my environment. I fixed it in a similar fashion of /src/test/regress/sql/foreign_data.sql. Please find attached a updated patch for the regression tests. Good catch, thanks.

Re: [HACKERS] Measuring relation free space

2012-02-13 Thread Jaime Casanova
On Wed, Jan 25, 2012 at 9:47 PM, Noah Misch n...@leadboat.com wrote: With all that done, run some quick benchmarks: see how SELECT free_percent FROM pgstattuple(rel) fares compared to SELECT relation_free_space(rel) for a large heap and for a large B-tree index.  If the timing difference is

Re: [HACKERS] pg_stats_recovery view

2012-02-13 Thread Jaime Casanova
On Thu, Feb 2, 2012 at 2:32 AM, Magnus Hagander mag...@hagander.net wrote: I haven't looked through the code in detail, but one direct comment: do we really need/want to send this through the stats collector? It will only ever have one sender - perhaps we should just either store it in shared