Re: [HACKERS] Escaping from blocked send() reprised.

2014-10-10 Thread Kyotaro HORIGUCHI
Hmm.. Sorry for my stupidity. Why is that necessary? It seems really rather wrong to make BIO_set_retry_write() dependant on ProcDiePending? Especially as, at least in my testing, it's not even required because the be_tls_write() can just check the error properly? I mistook the previous

Re: [HACKERS] Scaling shared buffer eviction

2014-10-10 Thread Amit Kapila
On Fri, Oct 10, 2014 at 1:08 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-09 16:01:55 +0200, Andres Freund wrote: I don't think OLTP really is the best test case for this. Especially not pgbench with relatilvely small rows *and* a uniform distribution of access. Try

Re: [HACKERS] Obsolete reference to _bt_tuplecompare() within tuplesort.c

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 02:04 AM, Peter Geoghegan wrote: I found a reference made obsolete by commit 9e85183b, which is from way back in 2000. comparetup_index_btree() says: /* * This is similar to _bt_tuplecompare(), but we have already done the * index_getattr calls for the first column, and we need

Re: [HACKERS] Obsolete reference to _bt_tuplecompare() within tuplesort.c

2014-10-10 Thread Peter Geoghegan
On Thu, Oct 9, 2014 at 11:58 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Yeah. Want to write that into a patch? Attached. -- Peter Geoghegan diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c new file mode 100644 index 8e57505..2d15da0 ***

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
Hi, On 2014-10-10 10:13:03 +0530, Amit Kapila wrote: I have done few performance tests for above patches and results of same is as below: Cool, thanks. Performance Data -- IBM POWER-7 16 cores, 64 hardware threads RAM = 64GB max_connections =210 Database

[HACKERS] alter user/role CURRENT_USER

2014-10-10 Thread Kyotaro HORIGUCHI
Hello, on the way considering alter role set .., I found that ALTER ROLE/USER cannot take CURRENT_USER as the role name. In addition to that, documents of ALTER ROLE / USER are inconsistent with each other in spite of ALTER USER is said to be an alias for ALTER ROLE. Plus, ALTER USER cannot take

Re: [HACKERS] pg_receivexlog --status-interval add fsync feedback

2014-10-10 Thread Fujii Masao
On Thu, Oct 9, 2014 at 6:42 PM, furu...@pm.nttdata.co.jp wrote: If we remove --fsync-interval, resoponse time to user will not be delay. Although, fsync will be executed multiple times in a short period. And there is no way to solve the problem without --fsync-interval, what should we

Re: [HACKERS] alter user set local_preload_libraries.

2014-10-10 Thread Kyotaro HORIGUCHI
Hello, I overlooked this thread. On Mon, Sep 15, 2014 at 1:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On 9/1/14 7:51 AM, Kyotaro HORIGUCHI wrote: The attached patch simply changes the context for local_... to PGC_USERSET and edits the doc. I

[HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number = '1234 5678 5344 7733' 2. If we want to look for card fraud, we need to be able to use the full card number to join to

Re: [HACKERS] Column Redaction

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 11:57 AM, Simon Riggs wrote: Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number = '1234 5678 5344 7733' 2. If we want to look for card fraud, we need to be able to

Re: [HACKERS] Column Redaction

2014-10-10 Thread Damian Wolgast
This would have other uses as well, such as default report formats, so we can store financial amounts as NUMERIC, but format them on retrieval as $12,345.78 etc.. Nice idea, but what if you need to do further calculations? If you output the value of credit card transactions it works fine, but

Re: [HACKERS] alter user set local_preload_libraries.

2014-10-10 Thread Fujii Masao
On Fri, Oct 10, 2014 at 5:36 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, I overlooked this thread. On Mon, Sep 15, 2014 at 1:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On 9/1/14 7:51 AM, Kyotaro HORIGUCHI wrote: The attached

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 10:29, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 10/10/2014 11:57 AM, Simon Riggs wrote: Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number =

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 10:15, Thom Brown t...@linux.com wrote: On 10 October 2014 09:57, Simon Riggs si...@2ndquadrant.com wrote: Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number

Re: [HACKERS] Column Redaction

2014-10-10 Thread Damian Wolgast
The problem there is that the SQL for (2) changes frequently, so we want to give people SQL access. So you want to give people access to your SQL database and worry that they could see specific information (credit card numbers) in plain and therefore you want to format it, so that people

Re: [HACKERS] Scaling shared buffer eviction

2014-10-10 Thread Andres Freund
On 2014-10-10 12:28:13 +0530, Amit Kapila wrote: On Fri, Oct 10, 2014 at 1:08 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-09 16:01:55 +0200, Andres Freund wrote: I don't think OLTP really is the best test case for this. Especially not pgbench with relatilvely small rows

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 11:08, Damian Wolgast damian.wolg...@si-co.net wrote: The problem there is that the SQL for (2) changes frequently, so we want to give people SQL access. So you want to give people access to your SQL database and worry that they could see specific information (credit card

Re: [HACKERS] Column Redaction

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 01:21 PM, Simon Riggs wrote: Redaction is now a feature available in other databases. I guess its possible its all smoke and mirrors, but thats why we discuss stuff before we build it. I googled for Oracle Data redaction, and found General Usage guidelines: General Usage

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
Simon, * Simon Riggs (si...@2ndquadrant.com) wrote: The requirement for redaction cannot be provided by a view. A view provides a single value for each column, no matter whether it is used in SELECT or WHERE clause. Redaction requires output formatting only, but unchanged for other

Re: [HACKERS] Column Redaction

2014-10-10 Thread Pavel Stehule
Hi 2014-10-10 10:57 GMT+02:00 Simon Riggs si...@2ndquadrant.com: Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number = '1234 5678 5344 7733' 2. If we want to look for card

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.2

2014-10-10 Thread Andres Freund
On 2014-10-08 20:07:35 -0400, Robert Haas wrote: On Wed, Oct 8, 2014 at 2:04 PM, Andres Freund and...@2ndquadrant.com wrote: So, what makes it work for me (among other unrelated stuff) seems to be the following in .gdbinit, defineing away some things that gdb doesn't handle: macro define

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 01:21 PM, Simon Riggs wrote: Redaction is now a feature available in other databases. I guess its possible its all smoke and mirrors, but thats why we discuss stuff before we build it. I googled for Oracle Data redaction,

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Thom Brown (t...@linux.com) wrote: To be honest, this all sounds rather flaky. Even if you do rate-limit their queries, they can use methods that avoid rate-limiting, such as recursive queries. And if you're only after one credit card number (to use the original example), you'd get it in a

Re: [HACKERS] Column Redaction

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 01:35 PM, Stephen Frost wrote: Regarding functions, 'leakproof' functions should be alright to allow, though Heikki brings up a good point regarding binary search being possible in a plpgsql function (or even directly by a client). Of course, that approach also requires that you

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 01:35 PM, Stephen Frost wrote: Regarding functions, 'leakproof' functions should be alright to allow, though Heikki brings up a good point regarding binary search being possible in a plpgsql function (or even directly by a

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
Hi Robert, On 2014-10-08 16:01:53 -0400, Robert Haas wrote: [ comment fixes ] Thanks, I've incorporated these + a bit more. Could you otherwise make sense of the explanation and the algorithm? +/* yipeyyahee */ Although this will be clear to individuals with a good command of

Re: [HACKERS] Column Redaction

2014-10-10 Thread Hannu Krosing
On 10/10/2014 11:38 AM, Simon Riggs wrote: On 10 October 2014 10:29, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 10/10/2014 11:57 AM, Simon Riggs wrote: Postgres currently supports column level SELECT privileges. 1. If we want to confirm a credit card number, we can issue SELECT 1

Re: [HACKERS] Column Redaction

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 02:05 PM, Stephen Frost wrote: * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 01:35 PM, Stephen Frost wrote: Regarding functions, 'leakproof' functions should be alright to allow, though Heikki brings up a good point regarding binary search being possible in

Re: [HACKERS] Column Redaction

2014-10-10 Thread Thom Brown
On 10 October 2014 12:00, Stephen Frost sfr...@snowman.net wrote: * Thom Brown (t...@linux.com) wrote: To be honest, this all sounds rather flaky. Even if you do rate-limit their queries, they can use methods that avoid rate-limiting, such as recursive queries. And if you're only after one

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 02:05 PM, Stephen Frost wrote: * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 01:35 PM, Stephen Frost wrote: Regarding functions, 'leakproof' functions should be alright to allow, though Heikki brings up

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Thom Brown (t...@linux.com) wrote: On 10 October 2014 12:00, Stephen Frost sfr...@snowman.net wrote: The discussion about looking up specific card numbers in the original email from Simon was actually an allowed use-case, as I understood it, not a risk concern. Indeed, if you know a

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Amit Kapila
On Fri, Oct 10, 2014 at 1:27 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-10 10:13:03 +0530, Amit Kapila wrote: I have done few performance tests for above patches and results of same is as below: Cool, thanks. Performance Data -- IBM POWER-7

Re: [HACKERS] Column Redaction

2014-10-10 Thread Heikki Linnakangas
On 10/10/2014 02:27 PM, Stephen Frost wrote: * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 02:05 PM, Stephen Frost wrote: * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 10/10/2014 01:35 PM, Stephen Frost wrote: Regarding functions, 'leakproof' functions should

Re: [HACKERS] Column Redaction

2014-10-10 Thread Thom Brown
On 10 October 2014 12:45, Stephen Frost sfr...@snowman.net wrote: This gives the vague impression of security, but it really seems just the placing of a few obstacles in the way. One might consider that all security is just placing obstacles in the way. There's a difference between

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 11:45, Thom Brown t...@linux.com wrote: To be honest, this all sounds rather flaky. To be honest, suggesting anything at all is rather difficult and I recommend people try it. Everything sounds crap when you didn't think of it and you've given it an hour's thought. I'm not

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 12:01, Heikki Linnakangas hlinnakan...@vmware.com wrote: Really, I don't see how this can possible be made to work. You can't allow ad hoc processing of data, and still avoid revealing it to the user. Anyone with unmonitored access and sufficient time can break through

Re: [HACKERS] Column Redaction

2014-10-10 Thread Robert Haas
On Fri, Oct 10, 2014 at 7:00 AM, Stephen Frost sfr...@snowman.net wrote: * Thom Brown (t...@linux.com) wrote: To be honest, this all sounds rather flaky. Even if you do rate-limit their queries, they can use methods that avoid rate-limiting, such as recursive queries. And if you're only

Re: [HACKERS] Column Redaction

2014-10-10 Thread Thom Brown
On 10 October 2014 13:43, Simon Riggs si...@2ndquadrant.com wrote: On 10 October 2014 11:45, Thom Brown t...@linux.com wrote: To be honest, this all sounds rather flaky. To be honest, suggesting anything at all is rather difficult and I recommend people try it. I have, and most ideas I've

Re: [HACKERS] Column Redaction

2014-10-10 Thread Claudio Freire
On Fri, Oct 10, 2014 at 5:57 AM, Simon Riggs si...@2ndquadrant.com wrote: 1. If we want to confirm a credit card number, we can issue SELECT 1 FROM customer WHERE stored_card_number = '1234 5678 5344 7733' ... 3. We want to block the direct retrieval of card numbers for additional security.

[HACKERS] Inconsistencies in documentation of row-level locking

2014-10-10 Thread Michael Paquier
Hi all, Currently all the row-level lock modes are described in the page for SELECT query: http://www.postgresql.org/docs/devel/static/explicit-locking.html#LOCKING-ROWS However, after browsing the documentation, I noticed in the page describing all the explicit locks of the system that there is

Re: [HACKERS] [9.4 bug] The database server hangs with write-heavy workload on Windows

2014-10-10 Thread MauMau
From: Craig Ringer cr...@2ndquadrant.com It sounds like they've produced a test case, so they should be able to with a bit of luck. Or even better, send you the test case. I asked the user about this. It sounds like the relevant test case consists of many scripts. He explained to me that

Re: [HACKERS] [9.4 bug] The database server hangs with write-heavy workload on Windows

2014-10-10 Thread Andres Freund
On 2014-10-10 23:08:34 +0900, MauMau wrote: From: Craig Ringer cr...@2ndquadrant.com It sounds like they've produced a test case, so they should be able to with a bit of luck. Or even better, send you the test case. I asked the user about this. It sounds like the relevant test case

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
On 2014-10-10 17:18:46 +0530, Amit Kapila wrote: On Fri, Oct 10, 2014 at 1:27 PM, Andres Freund and...@2ndquadrant.com wrote: Observations -- a. The patch performs really well (increase upto ~40%) incase all the data fits in shared buffers (scale factor -100).

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Heikki Linnakangas (hlinnakan...@vmware.com) wrote: You said above that it's OK to pass the card numbers to leakproof functions. But if you allow that, you can write a function that takes as argument a redacted card number, and unredacts it (using the and = operators in a binary search).

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
* Thom Brown (t...@linux.com) wrote: On 10 October 2014 12:45, Stephen Frost sfr...@snowman.net wrote: There's a difference between intending that there shouldn't be a way past security and just making access a matter of walking a longer route. Throwing random 16-digit numbers and

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
Robert, * Robert Haas (robertmh...@gmail.com) wrote: On Fri, Oct 10, 2014 at 7:00 AM, Stephen Frost sfr...@snowman.net wrote: The discussion about looking up specific card numbers in the original email from Simon was actually an allowed use-case, as I understood it, not a risk concern.

Re: [HACKERS] Column Redaction

2014-10-10 Thread Thom Brown
On 10 October 2014 15:56, Stephen Frost sfr...@snowman.net wrote: * Thom Brown (t...@linux.com) wrote: Data such as plain credit card numbers stored in a column, even with all its data masked, would be easy to determine. I'm not as convinced of that as you are.. Though I'll point out that in

Re: [HACKERS] Column Redaction

2014-10-10 Thread Claudio Freire
On Fri, Oct 10, 2014 at 11:58 AM, Stephen Frost sfr...@snowman.net wrote: You are obviously wearing your rose-colored glasses this morning. I predict a competent SQL programmer could write an SQL function, or client-side code, to pump the data out of the database using binary search in

Re: [HACKERS] Column Redaction

2014-10-10 Thread Rod Taylor
On Fri, Oct 10, 2014 at 10:56 AM, Stephen Frost sfr...@snowman.net wrote: * Thom Brown (t...@linux.com) wrote: On 10 October 2014 12:45, Stephen Frost sfr...@snowman.net wrote: There's a difference between intending that there shouldn't be a way past security and just making access a

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
On 2014-10-10 16:41:39 +0200, Andres Freund wrote: FWIW, the profile always looks like - 48.61% postgres postgres [.] s_lock - s_lock + 96.67% StrategyGetBuffer + 1.19% UnpinBuffer + 0.90% PinBuffer + 0.70% hash_search_with_hash_value +

Re: [HACKERS] schema-only -n option in pg_restore fails

2014-10-10 Thread Fabrízio de Royes Mello
On Thu, Oct 9, 2014 at 6:19 PM, Josh Berkus j...@agliodbs.com wrote: On 10/09/2014 12:36 PM, Josh Berkus wrote: Summary: pg_restore -n attempts to restore objects to pg_catalog schema Versions Tested: 9.3.5, 9.3.0, 9.2.4 Explored this some with Andrew offlist. Turns out this is going to

Re: [HACKERS] [GENERAL] Understanding and implementing a GiST Index

2014-10-10 Thread Connor Wolf
I'd be ok with either SP-GiST or GiST, though there are tree structures (BK tree) that are particularly suited to this application that I /think/ map to GiST better then SP-GiST. Re: hamming in the RD-tree implementation: Where? I cloned the smlar git repo, and poked around, and it looks like

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Robert Haas
On Wed, Oct 8, 2014 at 5:01 PM, Kevin Grittner kgri...@ymail.com wrote: Peter Geoghegan p...@heroku.com wrote: On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas robertmh...@gmail.com wrote: I think the problem is that it's not possible to respect the usual guarantees even at READ COMMITTED level

Re: [HACKERS] Column Redaction

2014-10-10 Thread Kevin Grittner
Thom Brown t...@linux.com wrote: On 10 October 2014 15:56, Stephen Frost sfr...@snowman.net wrote: Thom Brown (t...@linux.com) wrote: Data such as plain credit card numbers stored in a column, even with all its data masked, would be easy to determine. I'm not as convinced of that as you

Re: [HACKERS] bad estimation together with large work_mem generates terrible slow hash joins

2014-10-10 Thread Tomas Vondra
Hi! On 9.10.2014 22:28, Kevin Grittner wrote: Tomas Vondra t...@fuzzy.cz wrote: The only case I've been able to come up with is when the hash table fits into work_mem only thanks to not counting the buckets. The new code will start batching in this case. Hmm. If you look at the timings in

Re: [HACKERS] Column Redaction

2014-10-10 Thread Bruce Momjian
On Fri, Oct 10, 2014 at 02:05:05PM +0100, Thom Brown wrote: On 10 October 2014 13:43, Simon Riggs si...@2ndquadrant.com wrote: On 10 October 2014 11:45, Thom Brown t...@linux.com wrote: To be honest, this all sounds rather flaky. To be honest, suggesting anything at all is rather

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas robertmh...@gmail.com wrote: Anything we do about triggers will by definition be novel. Right now, we have INSERT, UPDATE, and DELETE. If we add a new operation, whether it's called UPSERT or MERGE or FROB, or if we add a flag to insert that

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Anything we do about triggers will by definition be novel. Right now, we have INSERT, UPDATE, and DELETE. If we add a new operation, whether it's called UPSERT or MERGE or FROB, [ ... ] If we call it MERGE, then we had better follow the rules the

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 11:30 AM, Kevin Grittner kgri...@ymail.com wrote: That seems a lot cleaner than the proposal on the Wiki page. If we go that route, it makes sense to fire the BEFORE INSERT triggers before attempting the insert and then fire BEFORE UPDATE triggers before attempting the

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Peter Geoghegan p...@heroku.com wrote: On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas robertmh...@gmail.com wrote: I actually like this syntax reasonably well in some ways, but I don't like that we're mentioning the index name, and the CONFLICTING() notation is decidedly odd. People keep

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 11:38 AM, Peter Geoghegan p...@heroku.com wrote: That seems a lot cleaner than the proposal on the Wiki page. If we go that route, it makes sense to fire the BEFORE INSERT triggers before attempting the insert and then fire BEFORE UPDATE triggers before attempting the

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 11:44 AM, Kevin Grittner kgri...@ymail.com wrote: We've all read that, and your repeated arguments for that point of view. We disagree and have said why. What in that is not a realistic conversation? Because, as I've said many times, there are problems with naming

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Peter Geoghegan p...@heroku.com wrote: On Fri, Oct 10, 2014 at 11:30 AM, Kevin Grittner kgri...@ymail.com wrote: That seems a lot cleaner than the proposal on the Wiki page. If we go that route, it makes sense to fire the BEFORE INSERT triggers before attempting the insert and then fire

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Peter Geoghegan p...@heroku.com wrote: There is no danger of UPDATE before row-level triggers firing without then updating (unless the xact aborts, but you know what I mean). Well, let's make sure I do know what you mean. If a BEFORE UPDATE ... FOR EACH ROW trigger returns NULL, the UPDATE

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Robert Haas
On Fri, Oct 10, 2014 at 2:29 PM, Peter Geoghegan p...@heroku.com wrote: People keep remarking that they don't like that you can (optionally) name a unique index explicitly, and I keep telling them why I've done it that way [1]. There is a trade-off here. I am willing to go another way in that

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Peter Geoghegan p...@heroku.com wrote: On Fri, Oct 10, 2014 at 11:44 AM, Kevin Grittner kgri...@ymail.com wrote: [discussion on committers rejecting the notion of a syntax involving specification of an index name] as I've said many times, there are problems with naming columns directly

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 11:55 AM, Kevin Grittner kgri...@ymail.com wrote: I haven't read the patch, but the discussion has made that clear, yes. Try to take agreement on a point gracefully, will ya? ;-) Heh, sorry. I did literally mean what I said - it wasn't 100% clear to me that you got

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 12:04 PM, Kevin Grittner kgri...@ymail.com wrote: Peter Geoghegan p...@heroku.com wrote: There is no danger of UPDATE before row-level triggers firing without then updating (unless the xact aborts, but you know what I mean). Well, let's make sure I do know what you

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 12:09 PM, Robert Haas robertmh...@gmail.com wrote: I think what's realistic here is that the patch isn't going to get committed the way you have it today, because nobody else likes that design. That may be harsh, but I think it's accurate. I do think that's harsh,

Re: [HACKERS] Column Redaction

2014-10-10 Thread Stephen Frost
Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: For fun I gave the search a try. Neat! On my laptop I can pull all 10,000 card numbers in less than 1 second. For a text based item I don't imagine it would be much different. Numbers are pretty easy to work with though. I had been planning

Re: [HACKERS] Column Redaction

2014-10-10 Thread Simon Riggs
On 10 October 2014 19:26, Bruce Momjian br...@momjian.us wrote: On Fri, Oct 10, 2014 at 02:05:05PM +0100, Thom Brown wrote: On 10 October 2014 13:43, Simon Riggs si...@2ndquadrant.com wrote: On 10 October 2014 11:45, Thom Brown t...@linux.com wrote: To be honest, this all sounds rather

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Jim Nasby
On 10/9/14, 6:59 PM, Gavin Flower wrote: On 10/10/14 12:38, Jim Nasby wrote: On 10/8/14, 5:51 PM, Peter Geoghegan wrote: On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittnerkgri...@ymail.com wrote: Although the last go-around does suggest that there is at least one point of difference on the

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Kevin Grittner
Peter Geoghegan p...@heroku.com wrote: On Fri, Oct 10, 2014 at 12:09 PM, Robert Haas robertmh...@gmail.com wrote: I think what's realistic here is that the patch isn't going to get committed the way you have it today, because nobody else likes that design. That may be harsh, but I think it's

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 2:17 PM, Jim Nasby jim.na...@bluetreble.com wrote: That said, since the use case for UPSERT is different than both INSERT and UPDATE maybe it would be a good idea to have a separate trigger for them anyway. -1. I think that having a separate UPSERT trigger is a bad

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-10 Thread Peter Geoghegan
On Fri, Oct 10, 2014 at 2:16 PM, Kevin Grittner kgri...@ymail.com wrote: Would you be okay with this never working with partial unique indexes? That gives me something to work with. That seems like the only sensible course, to me. Okay, great. If that's the consensus, then that's all I need

[HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Sehrope Sarkuni
Hi, I've been testing out some of the new materialized view functionality in 9.4 and noticed that they don't show up in the information_schema data dictionary views, specifically information_schema.tables or information_schema.views (sample output is below and it's the same for 9.3 as well). Is

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Tom Lane
Sehrope Sarkuni sehr...@jackdb.com writes: I've been testing out some of the new materialized view functionality in 9.4 and noticed that they don't show up in the information_schema data dictionary views, specifically information_schema.tables or information_schema.views (sample output is

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Sehrope Sarkuni sehr...@jackdb.com writes: I've been testing out some of the new materialized view functionality in 9.4 and noticed that they don't show up in the information_schema data dictionary views, specifically information_schema.tables or

Re: [HACKERS] orangutan seizes up during isolation-check

2014-10-10 Thread Noah Misch
On Mon, Sep 15, 2014 at 12:51:14AM -0400, Noah Misch wrote: 1. Fork, call setlocale(LC_x, ) in the child, pass back the effective locale name through a pipe, and pass that name to setlocale() in the original process. The short-lived child will get the extra threads, and the

[HACKERS] B-Tree index builds, CLUSTER, and sortsupport

2014-10-10 Thread Peter Geoghegan
Both Robert and Heikki expressed dissatisfaction with the fact that B-Tree index builds don't use sortsupport. Because B-Tree index builds cannot really use the onlyKey optimization, the historic oversight of not supporting B-Tree builds (and CLUSTER) might have been at least a little

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Peter Eisentraut
On 10/10/14 6:53 PM, Stephen Frost wrote: I'm not particularly thrilled with this answer. I'd aruge that the 'materialized' part of mat views isn't relevant to the standard, which does not concern itself with such performance-oriented considerations, and therefore, to the standard's view (pun

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Stephen Frost
* Peter Eisentraut (pete...@gmx.net) wrote: On 10/10/14 6:53 PM, Stephen Frost wrote: I'm not particularly thrilled with this answer. I'd aruge that the 'materialized' part of mat views isn't relevant to the standard, which does not concern itself with such performance-oriented

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Amit Kapila
On Fri, Oct 10, 2014 at 8:11 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-10 17:18:46 +0530, Amit Kapila wrote: On Fri, Oct 10, 2014 at 1:27 PM, Andres Freund and...@2ndquadrant.com wrote: Observations -- a. The patch performs really well

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
On 2014-10-11 06:18:11 +0530, Amit Kapila wrote: On Fri, Oct 10, 2014 at 8:11 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-10 17:18:46 +0530, Amit Kapila wrote: On Fri, Oct 10, 2014 at 1:27 PM, Andres Freund and...@2ndquadrant.com wrote: Observations

Re: [HACKERS] orangutan seizes up during isolation-check

2014-10-10 Thread Peter Eisentraut
On 10/10/14 8:24 PM, Noah Misch wrote: Here's an implementation thereof covering both backend and frontend use of setlocale(). A setlocale() wrapper, pg_setlocale(), injects use of the child process where necessary. Would such a change not be better in gnulib itself? -- Sent via

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Sehrope Sarkuni
Another example is an ETL tool or ORM that queries the data dictionary to generate SQL or object models. If mviews show up in the regular information_schema views (specifically the .columns view) then it'll just work as if it's a regular table or view (ex: like foreign tables). If not, it's

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Amit Kapila
On Sat, Oct 11, 2014 at 6:29 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-11 06:18:11 +0530, Amit Kapila wrote: I've run some short tests on hydra: scale 1000: base: 4GB: tps = 296273.004800 (including connections establishing) tps = 296373.978100 (excluding connections

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-10 Thread Peter Eisentraut
On 10/10/14 8:44 PM, Stephen Frost wrote: As a comparison, what about unlogged tables? They're not normal tables and they aren't defined by the SQL standard either. They are normal tables when considered within the scope of the SQL standard. The only difference to normal tables is their crash

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Andres Freund
On 2014-10-11 06:49:54 +0530, Amit Kapila wrote: On Sat, Oct 11, 2014 at 6:29 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-11 06:18:11 +0530, Amit Kapila wrote: I've run some short tests on hydra: scale 1000: base: 4GB: tps = 296273.004800 (including connections

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.9

2014-10-10 Thread Amit Kapila
On Sat, Oct 11, 2014 at 7:00 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-11 06:49:54 +0530, Amit Kapila wrote: On Sat, Oct 11, 2014 at 6:29 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-11 06:18:11 +0530, Amit Kapila wrote: I've run some short tests on

Re: [HACKERS] orangutan seizes up during isolation-check

2014-10-10 Thread Noah Misch
On Fri, Oct 10, 2014 at 09:14:38PM -0400, Peter Eisentraut wrote: On 10/10/14 8:24 PM, Noah Misch wrote: Here's an implementation thereof covering both backend and frontend use of setlocale(). A setlocale() wrapper, pg_setlocale(), injects use of the child process where necessary.