Re: [HACKERS] Review for Add permission check on SELECT INTO

2011-11-21 Thread Albe Laurenz
Kohei KaiGai wrote: The attached patch is a revised version. It fixed up this bug, and revised test cases to ensure permission check error shall be raised due to the new table. Thanks. The second patch seems fine to me, I'll mark it ready for committer. Yours, Laurenz Albe -- Sent via

Re: [HACKERS] Singleton range constructors versus functional coercion notation

2011-11-21 Thread Robert Haas
On Nov 20, 2011, at 10:24 PM, Jeff Davis pg...@j-davis.com wrote: On Sat, 2011-11-19 at 15:57 -0500, Tom Lane wrote: I'm hesitant to remove them because the alternative is significantly more verbose: numrange(1.0, 1.0, '[]'); Right. The question is, does the case occur in practice often

Re: [HACKERS] [Review] Include detailed information about a row failing a CHECK constraint into the error message

2011-11-21 Thread Jan Kundrát
On 11/16/11 23:13, Royce Ausburn wrote: The patch fails the regression tests because it is outputting new DETAIL line which four of tests aren't expecting. The tests will need to be updated. Hi Royce, thanks for your time which you've put into this review. What is the suggested way to go

Re: [HACKERS] ToDo: pg_backup - using a conditional DROP

2011-11-21 Thread Pavel Stehule
Hello There is a use case from GoodData's engineer --- We have many user projects, each project has his own database with granted permissions. We use pg_dump with option --clean which extends SQL dump with syntax like: DROP INDEX tab1_idx1; DROP TABLE tab1; When we load such dump into database

Re: [HACKERS] [Review] Include detailed information about a row failing a CHECK constraint into the error message

2011-11-21 Thread Tom Lane
=?UTF-8?B?SmFuIEt1bmRyw6F0?= j...@flaska.net writes: On 11/16/11 23:13, Royce Ausburn wrote: Another super minor thing, postgres doesn't seem to put periods at the end of log messages, yet this new detail line does. Again, I'm not familiar with the correct procedure. Shall I send a revised

Re: [HACKERS] Singleton range constructors versus functional coercion notation

2011-11-21 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Nov 20, 2011, at 10:24 PM, Jeff Davis pg...@j-davis.com wrote: Well, if there were a good shorter notation, then probably so. But it doesn't look like we have a good idea, so I'm fine with dropping it. We should also keep in mind that people who

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-21 Thread Merlin Moncure
On Fri, Nov 4, 2011 at 7:48 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: This patch adds basic support of arrays in foreign keys, by allowing to define a referencing column as an array of elements having the same type as the referenced column in the referenced table. Every

Re: [HACKERS] Core Extensions relocation

2011-11-21 Thread Bruce Momjian
Greg Smith wrote: I've submitted two changes to this CommitFest that are enhancing features in this core extensions set. Right now I have multiple customers who are desperate for both of those features. With extensions, I can give them changes that solve their immediate crisis right

[HACKERS] Inverse convertion for pg_mb2wchar

2011-11-21 Thread Alexander Korotkov
Hackers, I've a question about pg_mb2wchar function. Is there any way for inverse convertion pg_wchar* to char*? I've looked to pg_wchar_tbl table definition, and I didn't find anything about inverse transformation. So, any change to get inverse convertion? I'm experimenting with index support

Re: [HACKERS] FlexLocks

2011-11-21 Thread Bruce Momjian
Robert Haas wrote: On Wed, Nov 16, 2011 at 12:25 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: We could alternatively change one or the other of them to be a struct with one member, but I think the cure might be worse than the disease. ?By my count, we are talking about saving

Re: [HACKERS] foreign key locks, 2nd attempt

2011-11-21 Thread Robert Haas
On Sat, Nov 19, 2011 at 10:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, Nov 3, 2011 at 6:12 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: So Noah Misch proposed using the FOR KEY SHARE syntax, and that's what I have implemented here.  (There

Re: [HACKERS] testing ProcArrayLock patches

2011-11-21 Thread Kevin Grittner
Kevin Grittner kevin.gritt...@wicourts.gov wrote: I can run one more set of tests tonight before I have to give it back to the guy who's putting it into production. It sounds like a set like the above except with synchronous_commit = off might be desirable? OK, that's what I did. This

Re: [HACKERS] ISN was: Core Extensions relocation

2011-11-21 Thread Bruce Momjian
Peter Geoghegan wrote: On 17 November 2011 03:54, Tom Lane t...@sss.pgh.pa.us wrote: ?It's not reasonable to suppose that nobody is using it today. I didn't suppose that no one is using it, but that those that are using it are unaware of the risks with prefix validation, and that there

Re: [HACKERS] testing ProcArrayLock patches

2011-11-21 Thread Pavan Deolasee
On Mon, Nov 21, 2011 at 10:44 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: I can run one more set of tests tonight before I have to give it back to the guy who's putting it into production.  It sounds like a set like the above except

Re: [HACKERS] testing ProcArrayLock patches

2011-11-21 Thread Kevin Grittner
Pavan Deolasee pavan.deola...@gmail.com wrote: It will be a great help if you could spare few minutes to also test the patch to take out the frequently accessed PGPROC members to a different array. We are seeing good improvements on HPUX IA platform and the AMD Opteron and it will be

Re: [HACKERS] testing ProcArrayLock patches

2011-11-21 Thread Pavan Deolasee
On Mon, Nov 21, 2011 at 11:01 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Pavan Deolasee pavan.deola...@gmail.com wrote: It will be a great help if you could spare few minutes to also test the patch to take out the frequently accessed PGPROC members to a different array. We are

Re: [HACKERS] ISN was: Core Extensions relocation

2011-11-21 Thread Joshua Berkus
Bruce, I don't see any of this reaching the level that it needs to be backpatched, so I think we have to accept that this will be 9.2-only change. Agreed. If users encounter issues with the prefix in the field, it will be easy enough for them to back-patch. But we don't want to be

Re: [HACKERS] psql \ir filename normalization

2011-11-21 Thread Bruce Momjian
Robert Haas wrote: Argh. The root of the problem here seems to be that join_path_components() feels entitled to arbitrarily insert a pathname separator at the front of the output string even if its first input didn't begin with one originally. Lame! The attached patch fixes this report, I

Re: [HACKERS] Client library cross-compiling: Win32, Win64, MacOSX. Possible?

2011-11-21 Thread Bruce Momjian
Pavel Golub wrote: Hello. Are there any howto's or articles about building client access library (libpq) for several target OSes, e.g. Win32, Win64, MacOS in the same MinGW environment? And is it possible at all? I know that there is MinGW-w64 to produce Win64 binaries, but I want to

Re: [HACKERS] Removing postgres -f command line option

2011-11-21 Thread Bruce Momjian
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: While looking at Shigeru Hanada's foreign join pushdown patch, I noticed a command line option that I didn't know to exist: $ postgres --help ... Developer options: -f s|i|n|m|hforbid use of some

[HACKERS] Notes on implementing URI syntax for libpq

2011-11-21 Thread Alexander Shulgin
Hello, It was proposed a while ago for libpq to support URI syntax for specifying the connection information: http://archives.postgresql.org/message-id/1302114698.23164.17.camel@jd-desktop http://archives.postgresql.org/pgsql-hackers/2011-07/msg01144.php It appears to me that the

Re: [HACKERS] COUNT(*) and index-only scans

2011-11-21 Thread Robert Haas
On Sat, Nov 19, 2011 at 11:22 AM, Thom Brown t...@linux.com wrote: While I accept that maybe adapting the existing bitmap index scan functionality isn't necessarily desirable, would it be feasible to create a corresponding bitmap index-only scan method. I've been thinking about this a bit

Re: [HACKERS] psql \ir filename normalization

2011-11-21 Thread Robert Haas
On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Argh.  The root of the problem here seems to be that join_path_components() feels entitled to arbitrarily insert a pathname separator at the front of the output string even if its first input didn't

Re: [HACKERS] proposal: better support for debugging of overloaded functions

2011-11-21 Thread Robert Haas
On Sun, Nov 20, 2011 at 6:16 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Is possible to add GUC variable plpgsql.log_function_signature (maybe just log_function_signature (for all PL))? I am not sure about GUC name. When this variable is true, then CONTEXT line will contain a qualified

Re: [HACKERS] EXPLAIN (plan off, rewrite off) for benchmarking

2011-11-21 Thread Robert Haas
On Sat, Nov 19, 2011 at 11:47 AM, Andres Freund and...@anarazel.de wrote: I absolutely cannot agree on the fact that the speed parse-analyze is irrelevant though. Tom may be right that the speed of the parser *in isolation* is irrelevant, in the narrow sense that if we made the parser twice as

Re: [HACKERS] RFC: list API / memory allocations

2011-11-21 Thread Robert Haas
On Sat, Nov 19, 2011 at 12:33 PM, Stephen Frost sfr...@snowman.net wrote: You've mentioned that before and, to be honest, I could have sworn that we're doing that already.. I tried to write a patch for that at one point, but it crashed and burned over the exact same set of issues discussed

Re: [HACKERS] psql \ir filename normalization

2011-11-21 Thread Bruce Momjian
Robert Haas wrote: On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Argh. ?The root of the problem here seems to be that join_path_components() feels entitled to arbitrarily insert a pathname separator at the front of the output string even if its

Re: [HACKERS] psql \ir filename normalization

2011-11-21 Thread Robert Haas
On Mon, Nov 21, 2011 at 2:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Argh. ?The root of the problem here seems to be that join_path_components() feels entitled to arbitrarily

Re: [HACKERS] psql \ir filename normalization

2011-11-21 Thread Bruce Momjian
Robert Haas wrote: On Mon, Nov 21, 2011 at 2:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Argh. ?The root of the problem here seems to be that join_path_components() feels

Re: [HACKERS] Core Extensions relocation

2011-11-21 Thread Greg Smith
On 11/21/2011 11:40 AM, Bruce Momjian wrote: I think a question is how often people are waiting for features that actually can be addressed in a contrib/plugin way. My gut feeling is that most missing features have to be added to the server core (e.g. index-only scans) and are not possible to

Re: [HACKERS] Core Extensions relocation

2011-11-21 Thread Bruce Momjian
Greg Smith wrote: On 11/21/2011 11:40 AM, Bruce Momjian wrote: I think a question is how often people are waiting for features that actually can be addressed in a contrib/plugin way. My gut feeling is that most missing features have to be added to the server core (e.g. index-only scans)

Re: [HACKERS] Core Extensions relocation

2011-11-21 Thread Robert Haas
On Fri, Nov 18, 2011 at 9:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Why do you figure that, exactly?  The path of least resistance will be precisely to leave everything packaged as it is, in a single postgresql-contrib module.  I'm pretty likely to do that myself for Fedora and RHEL.  

Re: [HACKERS] foreign key locks, 2nd attempt

2011-11-21 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: On Sat, Nov 19, 2011 at 10:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: It's already the case that RI triggers require access to special executor features that are not accessible at the SQL level.  I don't think the above argument is a compelling reason

Re: [HACKERS] Refactoring on DROP/ALTER SET SCHEMA/ALTER RENAME TO statement

2011-11-21 Thread Robert Haas
On Sat, Nov 19, 2011 at 1:49 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: But I'm not sure why we do.  My thought here was that we should extended the ObjectProperty array in objectaddress.c so that AlterObjectNamespace can get by with fewer arguments - specifically, it seems like the following

[HACKERS] Writeable FDWs?

2011-11-21 Thread Josh Berkus
All, When I present to people about the features of 9.1, one of the most frequent questions is when we will get writeable FDWs for data sources where it is appropriate. Is anyone working on this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing

[HACKERS] btvacuumpage useless orig_blkno

2011-11-21 Thread Alvaro Herrera
I just noticed that btvacuumpage has two BlockNumber parameters -- blkno and orig_blkno. The only caller passes them as the same value; the header comments state that blkno would be different when recursing, but actually the function implements recursion internally by way of a cute goto trick.

Re: [HACKERS] btvacuumpage useless orig_blkno

2011-11-21 Thread Simon Riggs
On Mon, Nov 21, 2011 at 10:03 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I just noticed that btvacuumpage has two BlockNumber parameters -- blkno and orig_blkno.  The only caller passes them as the same value; the header comments state that blkno would be different when recursing, but

Re: [HACKERS] btvacuumpage useless orig_blkno

2011-11-21 Thread Alvaro Herrera
Excerpts from Simon Riggs's message of lun nov 21 19:11:21 -0300 2011: On Mon, Nov 21, 2011 at 10:03 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I just noticed that btvacuumpage has two BlockNumber parameters -- blkno and orig_blkno.  The only caller passes them as the same value; the

Re: [HACKERS] Inlining comparators as a performance optimisation

2011-11-21 Thread Robert Haas
On Tue, Sep 20, 2011 at 7:53 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: I don't think that the fact that that happens is at all significant at this early stage, and it never even occurred to me that you'd think that it might be. I was simply disclosing a quirk of this POC patch. The

[HACKERS] strange nbtree corruption report

2011-11-21 Thread Alvaro Herrera
Hi, We got a very strange nbtree corruption report some time ago. This was a btree index on a vey high churn table -- entries are updated and deleted very quickly, so the index grows very large and also shrinks quickly (AFAICT this is a work queue of sorts). The most strange thing of all is

Re: [HACKERS] testing ProcArrayLock patches

2011-11-21 Thread Kevin Grittner
Pavan Deolasee pavan.deola...@gmail.com wrote: It will be a great help if you could spare few minutes to also test the patch to take out the frequently accessed PGPROC members to a different array. We are seeing good improvements on HPUX IA platform and the AMD Opteron and it will be

[HACKERS] explain analyze query execution time

2011-11-21 Thread Rudyar
Hello, I try to get the execution time of a query workload. I try using explain analyze but this time is allways higher than the execution time of a query across a client like pgadmin3 what is the reason about that difference? -- Sent via pgsql-hackers mailing list

Re: [HACKERS] EXPLAIN (plan off, rewrite off) for benchmarking

2011-11-21 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: ... Maybe we could find a way to reduce the size of the parse tree (i.e. fewer nodes), or the number of times that it has to be walked/copied. We could eliminate some annoying tree-copy steps if we could institute the policy that parse analysis doesn't

Re: [HACKERS] btvacuumpage useless orig_blkno

2011-11-21 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Simon Riggs's message of lun nov 21 19:11:21 -0300 2011: tail recursion - read comments at bottom of the function Right, but we don't need to pass the value as a parameter, we can just save it at the start of the function, as my

Re: [HACKERS] Removing postgres -f command line option

2011-11-21 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: I think it is hard to argue that such a user-visible flag is reasonable, even if it helps backend developers avoid some keystrokes. I think flags used only by backend developers should be things that can _only_ be done with flags. Huh? By that

Re: [HACKERS] [COMMITTERS] pgsql: Avoid marking buffer dirty when VACUUM has no work to do.

2011-11-21 Thread Tom Lane
I wrote: Simon Riggs si...@2ndquadrant.com writes: Avoid marking buffer dirty when VACUUM has no work to do. When wal_level = 'hot_standby' we touched the last page of the relation during a VACUUM, even if nothing else had happened. That would alter the LSN of the last block and set the mtime

Re: [HACKERS] Removing postgres -f command line option

2011-11-21 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I think it is hard to argue that such a user-visible flag is reasonable, even if it helps backend developers avoid some keystrokes. I think flags used only by backend developers should be things that can _only_ be done with flags.

[HACKERS] Rename a database that has connections

2011-11-21 Thread Mark Kirkwood
I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to restore a dump into new

Re: [HACKERS] strange nbtree corruption report

2011-11-21 Thread Noah Misch
On Mon, Nov 21, 2011 at 08:00:21PM -0300, Alvaro Herrera wrote: We got a very strange nbtree corruption report some time ago. This was a btree index on a vey high churn table -- entries are updated and deleted very quickly, so the index grows very large and also shrinks quickly (AFAICT this

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Bruce Momjian
Mark Kirkwood wrote: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Tom Lane
Mark Kirkwood mark.kirkw...@catalyst.net.nz writes: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts

Re: [HACKERS] strange nbtree corruption report

2011-11-21 Thread Tom Lane
Noah Misch n...@leadboat.com writes: Just a suspicion ... when looking at the B-tree page reclamation algorithm, I had a thought that the logic in _bt_page_recyclable() was obsolete as of the introduction (in 8.3) of xid-free read-only transactions. A transaction without a persistent xid does

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Mark Kirkwood
On 22/11/11 16:38, Bruce Momjian wrote: Mark Kirkwood wrote: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex)

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Bruce Momjian
Mark Kirkwood wrote: On 22/11/11 16:38, Bruce Momjian wrote: Mark Kirkwood wrote: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically

Re: [HACKERS] strange nbtree corruption report

2011-11-21 Thread Tom Lane
Alvaro Herrera alvhe...@alvh.no-ip.org writes: We got a very strange nbtree corruption report some time ago. This was a btree index on a vey high churn table -- entries are updated and deleted very quickly, so the index grows very large and also shrinks quickly (AFAICT this is a work queue of

Re: [HACKERS] strange nbtree corruption report

2011-11-21 Thread Tom Lane
I wrote: Noah Misch n...@leadboat.com writes: Just a suspicion ... when looking at the B-tree page reclamation algorithm, I had a thought that the logic in _bt_page_recyclable() was obsolete as of the introduction (in 8.3) of xid-free read-only transactions. A transaction without a

Re: [HACKERS] explain analyze query execution time

2011-11-21 Thread Jeff Janes
On 11/21/11, Rudyar rudyar.cor...@gmail.com wrote: Hello, I try to get the execution time of a query workload. I try using explain analyze but this time is allways higher than the execution time of a query across a client like pgadmin3 what is the reason about that difference? Analyze has

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Mark Kirkwood
On 22/11/11 16:41, Tom Lane wrote: Mark Kirkwoodmark.kirkw...@catalyst.net.nz writes: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Mark Kirkwood
On 22/11/11 17:24, Mark Kirkwood wrote: I have not been able to find any other problems caused by this... renaming a db (many times) with hundreds of pgbench connections does not give rise to any issues. One point I did miss - the ps listing still uses the old dbname. pg_stat_activity

[HACKERS] dblink: enable parameters

2011-11-21 Thread Pavel Stehule
Hello I know so dblink is deprecated interface - but it has necessary functionality still - it support a writable statements. Very simple enhancing should be enable params to query - it's analogy to USING clause in EXECUTE statement. Regards Pavel Stehule -- Sent via pgsql-hackers mailing

Re: [HACKERS] Rename a database that has connections

2011-11-21 Thread Mark Kirkwood
On 22/11/11 17:24, Mark Kirkwood wrote: On 22/11/11 16:41, Tom Lane wrote: Mark Kirkwoodmark.kirkw...@catalyst.net.nz writes: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest

Re: [HACKERS] explain analyze query execution time

2011-11-21 Thread Kevin Grittner
Rudyar wrote: I try to get the execution time of a query workload. I try using explain analyze but this time is allways higher than the execution time of a query across a client like pgadmin3 what is the reason about that difference? It's the observer effect -- there is a cost to the

Re: [HACKERS] Singleton range constructors versus functional coercion notation

2011-11-21 Thread Dean Rasheed
On 21 November 2011 14:55, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Nov 20, 2011, at 10:24 PM, Jeff Davis pg...@j-davis.com wrote: Well, if there were a good shorter notation, then probably so. But it doesn't look like we have a good idea, so I'm fine