Re: [HACKERS] INSERT...ON DUPLICATE KEY IGNORE

2013-09-01 Thread Peter Geoghegan
On Sat, Aug 31, 2013 at 7:53 PM, Peter Geoghegan p...@heroku.com wrote: With a table with many unique indexes and many reasons to decide to reject a tuple proposed for insertion by INSERT...ON DUPLICATE KEY IGNORE, it isn't hard to imagine them all becoming heavily bloated very quickly. There

Re: [HACKERS] Redesigning checkpoint_segments

2013-09-01 Thread Amit Kapila
On Sat, Aug 24, 2013 at 2:38 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 03.07.2013 21:28, Peter Eisentraut wrote: On 6/6/13 4:09 PM, Heikki Linnakangas wrote: Here's a patch implementing that. Docs not updated yet. I did not change the way checkpoint_segments triggers

Re: [HACKERS] INSERT...ON DUPLICATE KEY IGNORE

2013-09-01 Thread Andres Freund
On 2013-08-31 19:38:49 -0700, Peter Geoghegan wrote: On Sat, Aug 31, 2013 at 11:34 AM, Andres Freund and...@2ndquadrant.com wrote: Won't S2 in this case exclusively lock a page in foo_a_key (the one where 2 will reside on) for 3600s, while it's waiting for S1 to finish while getting

Re: [HACKERS] INSERT...ON DUPLICATE KEY IGNORE

2013-09-01 Thread Andres Freund
On 2013-08-31 23:02:46 -0700, Peter Geoghegan wrote: On Sat, Aug 31, 2013 at 7:53 PM, Peter Geoghegan p...@heroku.com wrote: With a table with many unique indexes and many reasons to decide to reject a tuple proposed for insertion by INSERT...ON DUPLICATE KEY IGNORE, it isn't hard to

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Craig Ringer
I think there is another covert channel much more serious than constrains. You can gather information about hidden data by reading query plans. I think a lot of people would be quite happy to simply disallow EXPLAIN. Define a permission for it, grant it to public and newly created

Re: [HACKERS] dynamic shared memory

2013-09-01 Thread Noah Misch
On Sat, Aug 31, 2013 at 08:27:14AM -0400, Robert Haas wrote: On Fri, Aug 30, 2013 at 11:45 AM, Andres Freund and...@2ndquadrant.com wrote: I shared your opinion that preferred_address is never going to be reliable, although FWIW Noah thinks it can be made reliable with a large-enough

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Heikki Linnakangas
On 30.08.2013 22:57, Josh Berkus wrote: Right now, the primary tool for doing row filtering for MTA is Veil, which has numerous and well-known limitations. If RLS has fewer limitations, or is easier to deploy, maintain, and/or understand, then it's a valuable feature for that user base, even if

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Andres Freund
On 2013-09-01 16:38:51 +0300, Heikki Linnakangas wrote: On 30.08.2013 22:57, Josh Berkus wrote: Right now, the primary tool for doing row filtering for MTA is Veil, which has numerous and well-known limitations. If RLS has fewer limitations, or is easier to deploy, maintain, and/or

Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME

2013-09-01 Thread wangshuo
于 2013-08-30 21:27, Tom Lane 回复: wangs...@highgo.com.cn writes: In order to achieve enable/disable constraint name,I made ​​a few modifications to the code. First, someone used to build the constraints while building table. Then inserting data must follow a certain order.

Re: [HACKERS] CREATE FUNCTION .. SET vs. pg_dump

2013-09-01 Thread Stefan Kaltenbrunner
On 09/01/2013 12:53 AM, Stephen Frost wrote: * Stefan Kaltenbrunner (ste...@kaltenbrunner.cc) wrote: On 08/18/2013 05:40 PM, Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: While working on upgrading the database of the search system on postgresql.org to 9.2 I noticed

Re: [HACKERS] dynamic shared memory

2013-09-01 Thread Andres Freund
Hi Noah, On 2013-09-01 09:24:00 -0400, Noah Misch wrote: But even if it isn't reliable, there doesn't seem to be all that much value in forbidding access to that part of the OS-provided API. In That's also valid, though. Even if no core code exploits the flexibility, 3rd-party code

Re: [HACKERS] dynamic shared memory

2013-09-01 Thread Noah Misch
On Sun, Sep 01, 2013 at 05:08:38PM +0200, Andres Freund wrote: On 2013-09-01 09:24:00 -0400, Noah Misch wrote: The difficulty depends on whether processes other than the segment's creator will attach anytime or only as they start. Attachment at startup is enough for parallel query, but

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Greg Smith
On 9/1/13 9:38 AM, Heikki Linnakangas wrote: To phrase it differently: We already have RLS. It's shipped as an extension called Veil. Now please explain what's wrong with that statement, if anything. Veil was last updated for 9.1 to work against that version, so the first thing is that it's

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Josh Berkus
Kaigai, However, we have yet to talk about taking any such provisions with Postgres. If we commit this patch, arguably we'll have a row-level security feature which only protects data from well-behaved users, which seems counterproductive. The point we shouldn't forget is information

Re: [HACKERS] [RFC] Minmax indexes

2013-09-01 Thread Noah Misch
On Fri, Jun 14, 2013 at 06:28:06PM -0400, Alvaro Herrera wrote: Partial indexes are not supported; since an index is concerned with minimum and maximum values of the involved columns across all the pages in the table, it doesn't make sense to exclude values. It can make sense if the

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Kohei KaiGai
2013/8/31 Stephen Frost sfr...@snowman.net: KaiGai, * Kohei KaiGai (kai...@kaigai.gr.jp) wrote: The point we shouldn't forget is information leakage via covert-channel has less grade of threat than leakage via main-channel, because of much less bandwidth. While true, this argument can't be

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Stephen Frost
Josh, * Josh Berkus (j...@agliodbs.com) wrote: That's an astonishingly weak argument, because the bandwidth you're talking about is still very high, as in *hundreds* or *thousands* of values per minute. I agree that, in this specific case, we should do something about it. It's one thing

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Kohei KaiGai
2013/9/1 Josh Berkus j...@agliodbs.com: Kaigai, However, we have yet to talk about taking any such provisions with Postgres. If we commit this patch, arguably we'll have a row-level security feature which only protects data from well-behaved users, which seems counterproductive. The point

[HACKERS] Effectiveness of enable_material = off

2013-09-01 Thread Jeff Janes
I recently could not shift a plan off of using a materialize, to see what other options were out there, by setting enable_material to off. From src/backend/optimizer/path/costsize.c: * We don't test the value of enable_material here, because * materialization is required for

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Greg Stark
On Sun, Sep 1, 2013 at 8:31 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Or, any other criteria even though? My (current) preference is plan (c: we will be able to fix up *this* cover-channel with reasonable efforts on explain code. probably, we can close it if we don't print filtered rows

[HACKERS] max freeze age query in docs

2013-09-01 Thread Andrew Dunstan
The other day I followed the docs and ran this query: SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'; Then after identifying some tables that were close to triggering an automatic vacuum, we vacuumed them only to find that the age of the database's datfrozenxid

Re: [HACKERS] max freeze age query in docs

2013-09-01 Thread Josh Berkus
Maybe for bonus points we'd print out the schema (e.g. by selectting c.oid::regclass instead of c.relname), and also include materialized views which are omitted from the query altogether. Given the importance of this, maybe we need to have it as part of pg_stat_user_tables? -- Josh Berkus

Re: [HACKERS] [v9.4] row level security

2013-09-01 Thread Greg Smith
On 9/1/13 5:54 PM, Greg Stark wrote: So I think up above Tom is wrong about why it's ok that INSERT leaks keys when it reports a unique key violation. The reason why it's ok that there's a covert channel there is that the DBA can avoid that covert channel by being careful when creating unique