date:20141007

Hi,

The code in there doesn't look very interesting - and very unlikely to
run these days. Notably it relies on a binary called 'postmaster' et
al...

The last realy changes are from a long time ago:

commit 142d42f9386ed81a4f0779ec8a0cad1254173b5e
Author: Vadim B. Mikheev vadi...@yahoo.com
Date:   Fri Sep 26 14:57:36 1997 +

Scripts to run queries and data.

commit dbde5caeed4c9bdaf1292e52eafed80bbf01e9e9
Author: Vadim B. Mikheev vadi...@yahoo.com
Date:   Fri Sep 26 14:55:44 1997 +

Some results.

commit cf76759f34a172d424301cfa3723baee37f4a7ce
Author: Vadim B. Mikheev vadi...@yahoo.com
Date:   Fri Sep 26 14:55:21 1997 +

Start with performance suite.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

2014-10-07 Thread Bruce Momjian

On Fri, Oct  3, 2014 at 06:06:24PM -0400, Bruce Momjian wrote:
  I actually don't think that's true. Every lock acquiration implies a
  number of atomic locks. Those are expensive. And if you see individual
  locks acquired a high number of times in multiple proceses that's
  something important. It causes significant bus traffic between sockets,
  while not necessarily visible in the lock held times.
 
 True, but I don't think users are going to get much value from those
 numbers, and they are hard to get.  Server developers might want to know
 lock counts, but in those cases performance might not be as important.

In summary, I think there are three measurements we can take on locks:

1.  lock wait, from request to acquisition
2.  lock duration, from acquisition to release
3.  lock count

I think #1 is the most useful, and can be tracked by scanning a single
PGPROC lock entry per session (as already outlined), because you can't
wait on more than one lock at a time.

#2 would probably require multiple PGPROC lock entries, though I am
unclear how often a session holds multiple light-weight locks
concurrently.  #3 might require global counters in memory.

#1 seems the most useful from a user perspective, and we can perhaps
experiment with #2 and #3 once that is done.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RLS - permissive vs restrictive

On Tue, Oct 7, 2014 at 6:44 AM, Thom Brown t...@linux.com wrote:
It appears that I'm not the only person who finds it somewhat
unintuitive for overlapping RLS policies to be permissive rather than
restrictive (OR vs AND) (at least 3 others seem to expect AND
behaviour), although I understand the reasoning behind
it. And I've since discovered that the same feature in another
database system uses the latter rather than the former.

I posted a brain coredump of my thoughts on the matter on Depesz's
blog
(http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800)
and I was wondering if there's a future in allowing both systems. The
syntax is less important than the functionality, where restrictive
policies would be AND'd, permissive policies would (like they
currently do) be OR'd, and a combination would involve all restrictive
plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR
perm4)).

I'm just interested to know what others' thoughts on the matter are.

I think that could make sense. I think the main thing to consider is
the case where different policies apply to different users: what will
be the combined effect for users who are subjected to any subset of
those policies? If the same policies applies to everyone, then you
can just do it all as a single policy and put whatever Boolean logic
you like inside of it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Simon Riggs

On 7 October 2014 03:31, Peter Geoghegan p...@heroku.com wrote:

 It may be that people on reading this now believe Peter's HW locking
 approach is the best. I'm happy to go with consensus.

 I bet you didn't think that you'd say that a week ago.  :-)

You're right, because last week I thought heavyweight locking sucks
and I still think that; I haven't said it is the best.

What we've just discovered is that we're locking 100% of the time, but
its not needed in 99.9% of cases and is arguable in the 0.1% case -
not required at all.

The price of avoiding that rare and possibly erroneous condition seems
high to me.

Is there a way of detecting that we are updating a unique constraint
column and then applying the HW locking only in that case? Or can we
only apply locking when we have multiple unique constraints on a
table?
If so, I would withdraw any objection to the HW locking technique.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_upgrade, locale and encoding

While looking at bug #11431, I noticed that pg_upgrade still seems to 
think that encoding and locale are cluster-wide properties. We got 
per-database locale support in 8.4, and encoding has been per-database 
much longer than that.


pg_upgrade checks the encoding and locale of template0 in both clusters, 
and throws an error if they don't match. But it doesn't check the locale 
or encoding of postgres or template1 databases. That leads to problems 
if e.g. the postgres database was dropped and recreated with a different 
encoding or locale in the old cluster. We will merrily upgrade it, but 
strings in the database will be incorrectly encoded.


I propose the attached patch, for git master. It's more complicated in 
back-branches, as they still support upgrading from pre-8.4 clusters. We 
haven't heard any complaints from the field on this, so I don't think 
it's worth trying to back-patch this.


This slightly changes the way the locale comparison works. First, it 
ignores the encoding suffix of the locale name. It's of course important 
that the databases have a compatible encoding, but pg_database has a 
separate field for encoding, and that's now compared directly. Secondly, 
it tries to canonicalize the names, by calling setlocale(). That seems 
like a good idea, in response to bug #11431 
(http://www.postgresql.org/message-id/5424090e.9060...@vmware.com).


- Heikki
From ff44c80710ce16a8268ecfe63b6306026d4db87f Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas heikki.linnakan...@iki.fi
Date: Tue, 7 Oct 2014 15:38:53 +0300
Subject: [PATCH 1/1] In pg_upgrade, check the encoding and locale of template1
 and postgres dbs.

Lc_collate and lc_ctype have been per-database settings since server version
8.4, but we were still treating them as cluster-wide options, fetching the
values for template0 database, and comparing them. That's backwards; we
don't care about the encoding and locale of the template0 database, as
template0 is guaranteed to contain only ASCII characters. But if there are
any other databases that exist on both clusters (in particular template1 and
postgres databases), their encodings and locales must be compatible.

No backpatching, as earlier versions of pg_upgrade still support upgrading
from 8.3 servers. That would be more complicated.
---
 contrib/pg_upgrade/check.c   | 204 ++-
 contrib/pg_upgrade/controldata.c |  34 ---
 contrib/pg_upgrade/info.c|  14 ++-
 contrib/pg_upgrade/pg_upgrade.h  |   6 +-
 4 files changed, 85 insertions(+), 173 deletions(-)

diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
index bbfcab7..3df4b95 100644
--- a/contrib/pg_upgrade/check.c
+++ b/contrib/pg_upgrade/check.c
@@ -14,12 +14,10 @@
 #include pg_upgrade.h
 
 
-static void set_locale_and_encoding(ClusterInfo *cluster);
 static void check_new_cluster_is_empty(void);
-static void check_locale_and_encoding(ControlData *oldctrl,
-		  ControlData *newctrl);
-static bool equivalent_locale(const char *loca, const char *locb);
-static bool equivalent_encoding(const char *chara, const char *charb);
+static void check_databases_are_compatible(void);
+static void check_locale_and_encoding(DbInfo *olddb, DbInfo *newdb);
+static bool equivalent_locale(int category, const char *loca, const char *locb);
 static void check_is_install_user(ClusterInfo *cluster);
 static void check_for_prepared_transactions(ClusterInfo *cluster);
 static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
@@ -81,8 +79,6 @@ check_and_dump_old_cluster(bool live_check)
 	if (!live_check)
 		start_postmaster(old_cluster, true);
 
-	set_locale_and_encoding(old_cluster);
-
 	get_pg_database_relfilenode(old_cluster);
 
 	/* Extract a list of databases and tables from the old cluster */
@@ -127,13 +123,10 @@ check_and_dump_old_cluster(bool live_check)
 void
 check_new_cluster(void)
 {
-	set_locale_and_encoding(new_cluster);
-
-	check_locale_and_encoding(old_cluster.controldata, new_cluster.controldata);
-
 	get_db_and_rel_infos(new_cluster);
 
 	check_new_cluster_is_empty();
+	check_databases_are_compatible();
 
 	check_loadable_libraries();
 
@@ -279,93 +272,24 @@ check_cluster_compatibility(bool live_check)
 
 
 /*
- * set_locale_and_encoding()
- *
- * query the database to get the template0 locale
- */
-static void
-set_locale_and_encoding(ClusterInfo *cluster)
-{
-	ControlData *ctrl = cluster-controldata;
-	PGconn	   *conn;
-	PGresult   *res;
-	int			i_encoding;
-	int			cluster_version = cluster-major_version;
-
-	conn = connectToServer(cluster, template1);
-
-	/* for pg  80400, we got the values from pg_controldata */
-	if (cluster_version = 80400)
-	{
-		int			i_datcollate;
-		int			i_datctype;
-
-		res = executeQueryOrDie(conn,
-SELECT datcollate, datctype 
-FROM	pg_catalog.pg_database 
-WHERE	datname = 'template0' );
-		assert(PQntuples(res) == 1);
-
-		i_datcollate = PQfnumber(res, datcollate);
-		i_datctype =

Re: [HACKERS] Promise index tuples for UPSERT

On Tue, Oct 7, 2014 at 8:33 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On 7 October 2014 03:31, Peter Geoghegan p...@heroku.com wrote:
 It may be that people on reading this now believe Peter's HW locking
 approach is the best. I'm happy to go with consensus.

 I bet you didn't think that you'd say that a week ago.  :-)

 You're right, because last week I thought heavyweight locking sucks
 and I still think that; I haven't said it is the best.

 What we've just discovered is that we're locking 100% of the time, but
 its not needed in 99.9% of cases and is arguable in the 0.1% case -
 not required at all.

 The price of avoiding that rare and possibly erroneous condition seems
 high to me.

 Is there a way of detecting that we are updating a unique constraint
 column and then applying the HW locking only in that case? Or can we
 only apply locking when we have multiple unique constraints on a
 table?
 If so, I would withdraw any objection to the HW locking technique.

I'm not up on the details of what Peter's patch does with heavyweight
locking, but I'd say it this way: if the patch uses heavyweight
locking routinely, that's probably not going to scale well[1].   If
the patch detects possible conflicts and uses heavyweight locking only
in those cases and for the specific purpose of untangling those
conflicts, then that might well be OK.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] As evidence, I offer the fact that removing 1 of 2 places where
heavyweight locking is used in hash indexes resulted in a large
performance gain at high client counts.  See commit
76837c1507cb5a5a0048046233568447729e66dd.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add regression tests for autocommit-off mode for psql and fix some omissions

2014-10-07 Thread Feike Steenbergen

On 7 October 2014 09:55, Marko Tiikkaja ma...@joh.to wrote:
 It's not clear to me that this is fixing a problem, to be honest.  If you're
 running autocommit=off, you have an expectation that you can roll back
 commands at will.  It's fine if I can't roll back a VACUUM, for example,
 since I would practically never want to do that.  But  ALTER TYPE .. ADD
 VALUE ..;  is an entirely different beast.  That one's permanent; there's no
 DROP equivalent.  If the command is just executed, and I can't roll it back,
 wouldn't that be a serious violation of the principle of least astonishment?

I think you have a valid and good point; however the autocommit-off mode can
currently already execute statements which cannnot be rolled back.
Perhaps it is a good idea to not allow any of these statements in autocommit-off
mode to prevent astonishement from users, but that would be a discussion of
itself.

My reason for proposing this is to have all these commands treated
consistently.
The expectation of being able to roll back commands at will cannot be fulfilled
currently, many statemens that are allowed with autocommit-off fall into the
category different beast.

Currently the following statemens call PreventTransactionChain and do not
generate errors in autocommit-off mode:
- REINDEX DATABASE
- CREATE INDEX CONCURRENTLY
- ALTER SYSTEM
- CREATE DATABASE
- DROP DATABASE
- CREATE TABLESPACE
- DROP TABLESPACE
- CLUSTER
- VACUUM

The following statements call PreventTransactionChain and do generate errors
in autocommit-off mode:
- DROP INDEX CONCURRENTLY
- ALTER DATABASE ... SET TABLESPACE
- ALTER TYPE ... ADD

I don't see why these last three should be treated seperately from the
first list; we should
either allow all, or none of these statements IMHO.

kind regards,

Feike Steenbergen

On 7 October 2014 09:55, Marko Tiikkaja ma...@joh.to wrote:
 On 10/7/14, 9:11 AM, Feike Steenbergen wrote:

 Perhaps I am the only one using autocommit-off mode


 You most definitely aren't.

 and we shouldn't put effort
 into fixing this?


 It's not clear to me that this is fixing a problem, to be honest.  If you're
 running autocommit=off, you have an expectation that you can roll back
 commands at will.  It's fine if I can't roll back a VACUUM, for example,
 since I would practically never want to do that.  But  ALTER TYPE .. ADD
 VALUE ..;  is an entirely different beast.  That one's permanent; there's no
 DROP equivalent.  If the command is just executed, and I can't roll it back,
 wouldn't that be a serious violation of the principle of least astonishment?
 DROP INDEX CONCURRENTLY has a bit of the same problem.  You can CREATE INDEX
 CONCURRENTLY, but it might take days in some cases.

 I think that just running the command is a bad idea, and if we want to fix
 something here we should focus on just providing a better error message.


 .marko


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TAP test breakage on MacOS X

2014-10-07 Thread Andrew Dunstan



On 10/07/2014 12:15 AM, Tom Lane wrote:

Robert Haas robertmh...@gmail.com writes:

On Mon, Oct 6, 2014 at 8:15 PM, Peter Eisentraut pete...@gmx.net wrote:

The TAP tests
are arguably already much easier to debug than pg_regress ever was.

Well, maybe.  I wasn't able, after about 5 minutes of searching, to
locate either a log file with details of the failure or the code that
revealed what the test, the expected result, and the actual result
were.  It's possible that all that information is there and I just
don't know where to look; it took me a while to learn where the
various logs (postmaster.log, initdb.log, results) left behind by
pg_regress were, too.  If that information is not there, then I'd say
it's not easier to debug.  If it is and I don't know where to look ...
well then I just need to get educated.

The given case seemed pretty opaque to me too.  Could we maybe
have some documentation about how to debug TAP failures?  Or in
other words, if they're arguably easier to debug, how about
presenting that argument?

Also to the point: does the buildfarm script know how to collect
the information needed to debug a TAP failure?





No. In fact, it doesn't yet know how to run those tests. That's on my 
TODO list.



cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RLS - permissive vs restrictive

* Robert Haas (robertmh...@gmail.com) wrote:
 On Tue, Oct 7, 2014 at 6:44 AM, Thom Brown t...@linux.com wrote:
  It appears that I'm not the only person who finds it somewhat
  unintuitive for overlapping RLS policies to be permissive rather than
  restrictive (OR vs AND) (at least 3 others seem to expect AND
  behaviour), although I understand the reasoning behind
  it.  And I've since discovered that the same feature in another
  database system uses the latter rather than the former.
 
  I posted a brain coredump of my thoughts on the matter on Depesz's
  blog 
  (http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800)
  and I was wondering if there's a future in allowing both systems.  The
  syntax is less important than the functionality, where restrictive
  policies would be AND'd, permissive policies would (like they
  currently do) be OR'd, and a combination would involve all restrictive
  plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR
  perm4)).
 
  I'm just interested to know what others' thoughts on the matter are.
 
 I think that could make sense.  I think the main thing to consider is
 the case where different policies apply to different users: what will
 be the combined effect for users who are subjected to any subset of
 those policies?   If the same policies applies to everyone, then you
 can just do it all as a single policy and put whatever Boolean logic
 you like inside of it.

Agreed- this, as I recall, was the crux of the concern when we were
discussing it back in March(?).  One option might be to have a flag on
the table which basically says and or or, but that feels pretty
limiting to me.  Another option would be to allow the user to define the
specific and-vs-or for each policy like so:

ALTER TABLE t SET ROW SECURITY POLICY p1 OR (p2 AND p3);

... or you could just only have one policy on the table and do whatever
you'd like with it (which was the original idea, actually, though I've
found myself very much liking the ability to have multiple policies, and
to have them set for specific roles and commands, rather than having to
have one very complicated policy or having to use a function..).

As mentioned previously, I'm certainly not against any of these options,
but we need to figure out what we actually want first.  I do think 'OR'
is still the right answer if we're only going to do one thing as this
wouldn't make any sense to me:

CREATE ROLE r1;
CREATE ROLE r2;
CREATE ROLE r3;
GRANT r2, r3 TO r1;

CREATE TABLE t (a int);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON t TO r2 USING (a = 1);
CREATE POLICY p2 ON t TO r3 USING (a = 2);
GRANT SELECT ON t TO r1, r2, r3;

INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);

SET ROLE r1;
TABLE t;
 a 
---
(0 rows)


SET ROLE r2;
TABLE t;
 a 
---
 1
(1 row)

Given that r2 and r3 are granted to r1, using AND would mean that r1
can't actually see any rows (and this could end up happening at a later
point too- perhaps r1 only had r2 granted originally and was then
granted r3..).  We could try to come up with convoluted rules for
policies which have roles associated vs. policies which are for all
users, or try to define the policy overlap handling in some other way,
but I don't think we'd be doing our users a favor by doing so.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] BRIN indexes - TRAP: BadArgument


On 10/07/2014 01:33 AM, Alvaro Herrera wrote:

Heikki Linnakangas wrote:

On 09/23/2014 10:04 PM, Alvaro Herrera wrote:

+ Open questions
+ --
+
+ * Same-size page ranges?
+   Current related literature seems to consider that each index entry in a
+   BRIN index must cover the same number of pages.  There doesn't seem to be a


What is the related literature? Is there an academic paper or
something that should be cited as a reference for BRIN?


I the original minmax-proposal file, I had these four URLs:

: Other database systems already have similar features. Some examples:
:
: * Oracle Exadata calls this storage indexes
:   http://richardfoote.wordpress.com/category/storage-indexes/
:
: * Netezza has zone maps
:   http://nztips.com/2010/11/netezza-integer-join-keys/
:
: * Infobright has this automatically within their data packs according to a
:   May 3rd, 2009 blog post
:   
http://www.infobright.org/index.php/organizing_data_and_more_about_rough_data_contest/
:
: * MonetDB also uses this technique, according to a published paper
:   http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.108.2662
:   Cooperative Scans: Dynamic Bandwidth Sharing in a DBMS

I gave them all a quick look and none of them touches the approach in
detail; in fact other than the Oracle Exadata one, they are all talking
about something else and mention the minmax stuff only in passing.  I
don't think any of them is worth citing.


I think the current related literature phrase should be removed, if 
there isn't in fact any literature on this. If there's any literature 
worth referencing, should add a proper citation.



I added an USE_ASSERTION-only block in brininsert that runs the union
support proc and compares the output with the one from regular addValue.
I haven't tested this too much yet.


Ok, that's better than nothing. I wonder if it's too strict, though. It 
uses brin_tuple_equal(), which does a memcmp() on the tuples. That will 
trip for any non-meaningful differences, like the scale in a numeric.



* clarify the memory context stuff of support functions that we also
discussed earlier


I re-checked this stuff.  Turns out that the support functions don't
palloc/pfree memory too much, except to update the stuff stored in
BrinValues, by using datumCopy().  This memory is only freed when we
need to update a previous Datum.  There's no way for the brin.c code to
know when the Datum is going to be released by the support proc, and
thus no way for a temp context to be used.

The memory context experiments I alluded to earlier are related to
pallocs done in brininsert / bringetbitmap themselves, not in the
opclass-provided support procs.


At the very least, it needs to be documented.


All in all, I don't think there's much
room for improvement, other than perhaps doing so in brininsert/
bringetbitmap.  Don't really care too much about this either way.


Doing it in brininsert/bringetbitmap seems like the right approach. 
GiST, GIN, and SP-GiST all use a temporary memory context like that.



It would be wise to reserve some more support procedure numbers, for 
future expansion. Currently, support procs 1-4 are used by BRIN itself, 
and higher numbers can be used by the opclass. minmax opclasses uses 5-8 
for the , =, = and  operators. If we ever want to add a new, 
optional, support function to BRIN, we're out of luck. Let's document 
that e.g. support procs  10 are reserved for BRIN.


The redo routines should be updated to follow the new 
XLogReadBufferForRedo idiom (commit 
f8f4227976a2cdb8ac7c611e49da03aa9e65e0d2).


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On Tue, Oct 7, 2014 at 8:03 AM, Bruce Momjian br...@momjian.us wrote:
 On Fri, Oct  3, 2014 at 06:06:24PM -0400, Bruce Momjian wrote:
  I actually don't think that's true. Every lock acquiration implies a
  number of atomic locks. Those are expensive. And if you see individual
  locks acquired a high number of times in multiple proceses that's
  something important. It causes significant bus traffic between sockets,
  while not necessarily visible in the lock held times.

 True, but I don't think users are going to get much value from those
 numbers, and they are hard to get.  Server developers might want to know
 lock counts, but in those cases performance might not be as important.

 In summary, I think there are three measurements we can take on locks:

 1.  lock wait, from request to acquisition
 2.  lock duration, from acquisition to release
 3.  lock count

 I think #1 is the most useful, and can be tracked by scanning a single
 PGPROC lock entry per session (as already outlined), because you can't
 wait on more than one lock at a time.

 #2 would probably require multiple PGPROC lock entries, though I am
 unclear how often a session holds multiple light-weight locks
 concurrently.  #3 might require global counters in memory.

 #1 seems the most useful from a user perspective, and we can perhaps
 experiment with #2 and #3 once that is done.

I agree with some of your thoughts on this, Bruce, but there are some
points I'm not so sure about.

I have a feeling that any system that involves repeatedly scanning the
procarray will either have painful performance impact (if it's
frequent) or catch only a statistically insignificant fraction of lock
acquisitions (if it's infrequent).  The reason I think there may be a
performance impact is because quite a number of heavily-trafficked
shared memory structures are bottlenecked on memory latency, so it's
easy to imagine that having an additional process periodically reading
them would increase cache-line bouncing and hurt performance.  We will
probably need some experimentation to find the best idea.

I think the easiest way to measure lwlock contention would be to put
some counters in the lwlock itself.  My guess, based on a lot of
fiddling with LWLOCK_STATS over the years, is that there's no way to
count lock acquisitions and releases without harming performance
significantly - no matter where we put the counters, it's just going
to be too expensive.  However, I believe that incrementing a counter -
even in the lwlock itself - might not be too expensive if we only do
it when (1) a process goes to sleep or (2) spindelays occur.  Those
operations are expensive enough that I think the cost of an extra
shared memory access won't be too significant.

As a further point, when I study the LWLOCK_STATS output, that stuff
is typically what I'm looking for anyway.  The first few times I ran
with that enabled, I was kind of interested by the total lock counts
... but that quickly got uninteresting.  The blocking and spindelays
show you where the problems are, so that's the interesting part.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

2014-10-07 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 I think the easiest way to measure lwlock contention would be to put
 some counters in the lwlock itself.  My guess, based on a lot of
 fiddling with LWLOCK_STATS over the years, is that there's no way to
 count lock acquisitions and releases without harming performance
 significantly - no matter where we put the counters, it's just going
 to be too expensive.  However, I believe that incrementing a counter -
 even in the lwlock itself - might not be too expensive if we only do
 it when (1) a process goes to sleep or (2) spindelays occur.  Those
 operations are expensive enough that I think the cost of an extra
 shared memory access won't be too significant.

FWIW, that approach sounds sane to me as well.  I concur with Robert's
fear that adding cycles to the no-contention case will cost so much
as to make the feature unusable in production, or even for realistic
testing; which would mean it's pretty much useless.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On 2014-10-07 10:04:38 -0400, Robert Haas wrote:
 On Tue, Oct 7, 2014 at 8:03 AM, Bruce Momjian br...@momjian.us wrote:
  On Fri, Oct  3, 2014 at 06:06:24PM -0400, Bruce Momjian wrote:
   I actually don't think that's true. Every lock acquiration implies a
   number of atomic locks. Those are expensive. And if you see individual
   locks acquired a high number of times in multiple proceses that's
   something important. It causes significant bus traffic between sockets,
   while not necessarily visible in the lock held times.
 
  True, but I don't think users are going to get much value from those
  numbers, and they are hard to get.  Server developers might want to know
  lock counts, but in those cases performance might not be as important.
 
  In summary, I think there are three measurements we can take on locks:
 
  1.  lock wait, from request to acquisition
  2.  lock duration, from acquisition to release
  3.  lock count
 
  I think #1 is the most useful, and can be tracked by scanning a single
  PGPROC lock entry per session (as already outlined), because you can't
  wait on more than one lock at a time.
 
  #2 would probably require multiple PGPROC lock entries, though I am
  unclear how often a session holds multiple light-weight locks
  concurrently.  #3 might require global counters in memory.
 
  #1 seems the most useful from a user perspective, and we can perhaps
  experiment with #2 and #3 once that is done.
 
 I agree with some of your thoughts on this, Bruce, but there are some
 points I'm not so sure about.
 
 I have a feeling that any system that involves repeatedly scanning the
 procarray will either have painful performance impact (if it's
 frequent) or catch only a statistically insignificant fraction of lock
 acquisitions (if it's infrequent).

Agreed.

 I think the easiest way to measure lwlock contention would be to put
 some counters in the lwlock itself.  My guess, based on a lot of
 fiddling with LWLOCK_STATS over the years, is that there's no way to
 count lock acquisitions and releases without harming performance
 significantly - no matter where we put the counters, it's just going
 to be too expensive.  However, I believe that incrementing a counter -
 even in the lwlock itself - might not be too expensive if we only do
 it when (1) a process goes to sleep or (2) spindelays occur.

Increasing the size will be painful on its own :(.

Have you tried/considered putting the counters into a per-backend array
somewhere in shared memory? That way they don't blow up the size of
frequently ping-ponged cachelines. Then you can summarize those values
whenever querying the results.

 As a further point, when I study the LWLOCK_STATS output, that stuff
 is typically what I'm looking for anyway.  The first few times I ran
 with that enabled, I was kind of interested by the total lock counts
 ... but that quickly got uninteresting.  The blocking and spindelays
 show you where the problems are, so that's the interesting part.

I don't really agree with this. Especially with shared locks (even more
so if/hwen the LW_SHARED stuff gets in), there's simply no relevant
blocking and spindelay.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Simon Riggs

On 7 October 2014 14:06, Robert Haas robertmh...@gmail.com wrote:

 Is there a way of detecting that we are updating a unique constraint
 column and then applying the HW locking only in that case? Or can we
 only apply locking when we have multiple unique constraints on a
 table?
 If so, I would withdraw any objection to the HW locking technique.

 I'm not up on the details of what Peter's patch does with heavyweight
 locking, but I'd say it this way: if the patch uses heavyweight
 locking routinely, that's probably not going to scale well[1].   If
 the patch detects possible conflicts and uses heavyweight locking only
 in those cases and for the specific purpose of untangling those
 conflicts, then that might well be OK.

Yes, what I meant, just more clearly phrased. Thanks for the link.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RLS - permissive vs restrictive

On Tue, Oct 7, 2014 at 9:55 AM, Stephen Frost sfr...@snowman.net wrote:
 ... or you could just only have one policy on the table and do whatever
 you'd like with it (which was the original idea, actually, though I've
 found myself very much liking the ability to have multiple policies, and
 to have them set for specific roles and commands, rather than having to
 have one very complicated policy or having to use a function..).

The key point from my angle is that if you grant user alice the right
to see records where a = 1 and user bob the right to see records where
a = 2, the multiple-policy approach allows those quals to be
implemented as index-scans.  If you had a single policy granting all
users the right to see records where policyfunc() returns true, it
would never be indexable.

I think that Thom's idea of having some policies that are additional
filter conditions on top of everything else is a pretty good one.
It's probably possible to construct a case where you need multiple
levels of AND and OR logic, which Thom's proposal does not provide
for.  But are there really cases like that which anyone cares about?
I think we're going to be tempted to think about that question for
about 60 seconds and say nope, and that's probably not enough
thought.  It deserves serious reflection, because I think Thom's
proposal is terminal: if we do what he's proposing, it'll be hard to
extend the idea any further if we later discover that it isn't general
enough.  That having been said, what he's proposing is simple and
covers a fair amount of ground, and is thus worthy of serious
consideration, at least IMHO.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)


On 10/07/2014 05:04 PM, Robert Haas wrote:

On Tue, Oct 7, 2014 at 8:03 AM, Bruce Momjian br...@momjian.us wrote:

On Fri, Oct  3, 2014 at 06:06:24PM -0400, Bruce Momjian wrote:

I actually don't think that's true. Every lock acquiration implies a
number of atomic locks. Those are expensive. And if you see individual
locks acquired a high number of times in multiple proceses that's
something important. It causes significant bus traffic between sockets,
while not necessarily visible in the lock held times.


True, but I don't think users are going to get much value from those
numbers, and they are hard to get.  Server developers might want to know
lock counts, but in those cases performance might not be as important.


In summary, I think there are three measurements we can take on locks:

1.  lock wait, from request to acquisition
2.  lock duration, from acquisition to release
3.  lock count

I think #1 is the most useful, and can be tracked by scanning a single
PGPROC lock entry per session (as already outlined), because you can't
wait on more than one lock at a time.

#2 would probably require multiple PGPROC lock entries, though I am
unclear how often a session holds multiple light-weight locks
concurrently.  #3 might require global counters in memory.

#1 seems the most useful from a user perspective, and we can perhaps
experiment with #2 and #3 once that is done.


I agree with some of your thoughts on this, Bruce, but there are some
points I'm not so sure about.

I have a feeling that any system that involves repeatedly scanning the
procarray will either have painful performance impact (if it's
frequent) or catch only a statistically insignificant fraction of lock
acquisitions (if it's infrequent).  The reason I think there may be a
performance impact is because quite a number of heavily-trafficked
shared memory structures are bottlenecked on memory latency, so it's
easy to imagine that having an additional process periodically reading
them would increase cache-line bouncing and hurt performance.  We will
probably need some experimentation to find the best idea.

I think the easiest way to measure lwlock contention would be to put
some counters in the lwlock itself.  My guess, based on a lot of
fiddling with LWLOCK_STATS over the years, is that there's no way to
count lock acquisitions and releases without harming performance
significantly - no matter where we put the counters, it's just going
to be too expensive.  However, I believe that incrementing a counter -
even in the lwlock itself - might not be too expensive if we only do
it when (1) a process goes to sleep or (2) spindelays occur.  Those
operations are expensive enough that I think the cost of an extra
shared memory access won't be too significant.


FWIW, I liked Ilya's design. Before going to sleep, store the lock ID in 
shared memory. When you wake up, clear it. That should be cheap enough 
to have it always enabled. And it can easily be extended to other 
waits, e.g. when you're waiting for input from client.


I don't think counting the number of lock acquisition is that 
interesting. It doesn't give you any information on how long the waits 
were, for example. I think the question the user or DBA is trying to 
answer is Why is this query taking so long, even though the CPU is 
sitting idle?. A sampling approach works well for that.


For comparison, the perf tool works great for figuring out where the 
CPU time is spent in a process. It works by sampling. This is similar, 
but for wallclock time, and that we can hopefully produce more 
user-friendly output.


- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On 2014-10-07 17:22:18 +0300, Heikki Linnakangas wrote:
 FWIW, I liked Ilya's design. Before going to sleep, store the lock ID in
 shared memory. When you wake up, clear it. That should be cheap enough to
 have it always enabled. And it can easily be extended to other waits, e.g.
 when you're waiting for input from client.

I think there's a few locks where that's interesting. But in my
experience many slowdowns aren't caused by actual waits, but because of
cacheline contention. And for that the number of acquisitions is much
more relevant than the waiting. The primary example for this is probably
the procarray lock.

 I don't think counting the number of lock acquisition is that interesting.
 It doesn't give you any information on how long the waits were, for
 example.

Sure, that's a separate thing that we should be able to answer.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On Tue, Oct 7, 2014 at 10:12 AM, Andres Freund and...@2ndquadrant.com wrote:
 Have you tried/considered putting the counters into a per-backend array
 somewhere in shared memory? That way they don't blow up the size of
 frequently ping-ponged cachelines. Then you can summarize those values
 whenever querying the results.

The problem with that is that you need O(N*M) memory instead of O(N),
where N is the number of lwlocks and M is the number of backends.
That gets painful in a hurry.  We just got rid of something like that
with your patch to get rid of all the backend-local buffer pin arrays;
I'm not keen to add another such thing right back.  It might be viable
if we excluded the buffer locks, but that also detracts from the
value.  Plus, no matter how you slice it, you're now touching cache
lines completely unrelated to the ones you need for the foreground
work.  That's got a distributed overhead that's hard to measure, but
it is certainly going to knock other stuff out of the CPU caches to
some degree.

 As a further point, when I study the LWLOCK_STATS output, that stuff
 is typically what I'm looking for anyway.  The first few times I ran
 with that enabled, I was kind of interested by the total lock counts
 ... but that quickly got uninteresting.  The blocking and spindelays
 show you where the problems are, so that's the interesting part.

 I don't really agree with this. Especially with shared locks (even more
 so if/hwen the LW_SHARED stuff gets in), there's simply no relevant
 blocking and spindelay.

If your patch to implement lwlocks using atomics goes in, then we may
have to reassess what instrumentation is actually useful here.  I can
only comment on the usefulness of various bits of instrumentation I
have used in the past on the code bases we had that time, or my
patches thereupon.  Nobody here can reasonably be expected to know
whether the same stuff will still be useful after possible future
patches that are not even in a reviewable state at present have been
committed.

Having said that, if there's no blocking or spindelay any more, to me
that doesn't mean we should look for some other measure of contention
instead.  It just means that the whole area is a solved problem, we
don't need to measure contention any more because there isn't any, and
we can move on to other issues once we finish partying.  But mildly
skeptical that the outcome will be as good as all that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On 2014-10-07 10:30:54 -0400, Robert Haas wrote:
 On Tue, Oct 7, 2014 at 10:12 AM, Andres Freund and...@2ndquadrant.com wrote:
  Have you tried/considered putting the counters into a per-backend array
  somewhere in shared memory? That way they don't blow up the size of
  frequently ping-ponged cachelines. Then you can summarize those values
  whenever querying the results.
 
 The problem with that is that you need O(N*M) memory instead of O(N),
 where N is the number of lwlocks and M is the number of backends.

Right.

 That gets painful in a hurry.  We just got rid of something like that
 with your patch to get rid of all the backend-local buffer pin arrays;
 I'm not keen to add another such thing right back.

I think it might be ok if we'd exclude buffer locks and made it depend
on a GUC.

 It might be viable
 if we excluded the buffer locks, but that also detracts from the
 value.  Plus, no matter how you slice it, you're now touching cache
 lines completely unrelated to the ones you need for the foreground
 work.  That's got a distributed overhead that's hard to measure, but
 it is certainly going to knock other stuff out of the CPU caches to
 some degree.

Yea, it's hard to guess ;(

  As a further point, when I study the LWLOCK_STATS output, that stuff
  is typically what I'm looking for anyway.  The first few times I ran
  with that enabled, I was kind of interested by the total lock counts
  ... but that quickly got uninteresting.  The blocking and spindelays
  show you where the problems are, so that's the interesting part.
 
  I don't really agree with this. Especially with shared locks (even more
  so if/hwen the LW_SHARED stuff gets in), there's simply no relevant
  blocking and spindelay.
 
 If your patch to implement lwlocks using atomics goes in, then we may
 have to reassess what instrumentation is actually useful here.  I can
 only comment on the usefulness of various bits of instrumentation I
 have used in the past on the code bases we had that time, or my
 patches thereupon.  Nobody here can reasonably be expected to know
 whether the same stuff will still be useful after possible future
 patches that are not even in a reviewable state at present have been
 committed.

It's not like it'd be significantly different today - in a read mostly
workload that's bottlenecked on ProcArrayLock you'll not see many
waits. There you'd have to count the total number of spinlocks cycles to
measure anything interesting.

 Having said that, if there's no blocking or spindelay any more, to me
 that doesn't mean we should look for some other measure of contention
 instead.  It just means that the whole area is a solved problem, we
 don't need to measure contention any more because there isn't any, and
 we can move on to other issues once we finish partying.  But mildly
 skeptical that the outcome will be as good as all that.

It's not. Just because we're not waiting in a spinlock loop doesn't mean
there can't be contention... It's just moved one level down, into the cpu.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

2014-10-07 Thread Ilya Kosmodemiansky

On Tue, Oct 7, 2014 at 4:12 PM, Andres Freund and...@2ndquadrant.com wrote:
 I think the easiest way to measure lwlock contention would be to put
 some counters in the lwlock itself.  My guess, based on a lot of
 fiddling with LWLOCK_STATS over the years, is that there's no way to
 count lock acquisitions and releases without harming performance
 significantly - no matter where we put the counters, it's just going
 to be too expensive.  However, I believe that incrementing a counter -
 even in the lwlock itself - might not be too expensive if we only do
 it when (1) a process goes to sleep or (2) spindelays occur.

 Increasing the size will be painful on its own :(.

I am afraid in this case we should think about minimizing overhead but
not about avoiding it at all: having such DBA-friendly feature it is
worth it.

Let me step down a bit, since the discussion went to details, while
the whole design idea stays unclear.

What actually we need: fact, that lwlock acquired? lock count? time
spent in lock? overall lock duration?

Usual way to explain how any of such performance tools work, is
Traffic example (and any oracle/db2 wait-interface aware DBA knows
it):

You have some from home to office way and spend an hour to make it.
You try to optimize it and found, that however you take highway with
no speed limit, you usually stack in traffic turning from highway to
your office and spend there about 10-30 min. Alternative is to take
another way with 2 speed limit zones and one traffic light, totally
you will loose 2 and 5 minutes on speed limit parts and 2 min on red
light - overall better than 30 minutes in a jam and even better than
10 min in a jam. That is all about: to found bottleneck we need
information that process hold certain lock, that it was held certain
time or there are a lot of shorter time locks.

I think, sampling even 1-2 times pro second and building sort of
histogram is well enough at the moment, because it shows (not very in
a very precise manner however) that process hold certain lock, that it
was held certain time or there are a lot of shorter time locks.
After that it is possible to implement something more precise. (As far
as I know, Greg Smith works on some sort of wait events, but it seems
to me there are a lot of work to do to implement exact analog of OWI)

-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On Tue, Oct 7, 2014 at 10:36 AM, Andres Freund and...@2ndquadrant.com wrote:
 That gets painful in a hurry.  We just got rid of something like that
 with your patch to get rid of all the backend-local buffer pin arrays;
 I'm not keen to add another such thing right back.

 I think it might be ok if we'd exclude buffer locks and made it depend
 on a GUC.

Maybe.  I'm skeptical about whether what you'll get at that point is
really useful.

 It's not like it'd be significantly different today - in a read mostly
 workload that's bottlenecked on ProcArrayLock you'll not see many
 waits. There you'd have to count the total number of spinlocks cycles to
 measure anything interesting.

Hmm, really?  I've never had to do that to find bottlenecks.

 Having said that, if there's no blocking or spindelay any more, to me
 that doesn't mean we should look for some other measure of contention
 instead.  It just means that the whole area is a solved problem, we
 don't need to measure contention any more because there isn't any, and
 we can move on to other issues once we finish partying.  But mildly
 skeptical that the outcome will be as good as all that.

 It's not. Just because we're not waiting in a spinlock loop doesn't mean
 there can't be contention... It's just moved one level down, into the cpu.

I guess that's true, but how much of the contention at that level is
really important to expose to DBAs?  We can put anything that is of
developer interest only int LWLOCK_STATS.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

2014-10-07 Thread Ilya Kosmodemiansky

On Tue, Oct 7, 2014 at 4:30 PM, Andres Freund and...@2ndquadrant.com wrote:
 On 2014-10-07 17:22:18 +0300, Heikki Linnakangas wrote:
 FWIW, I liked Ilya's design. Before going to sleep, store the lock ID in
 shared memory. When you wake up, clear it. That should be cheap enough to
 have it always enabled. And it can easily be extended to other waits, e.g.
 when you're waiting for input from client.

 I think there's a few locks where that's interesting. But in my
 experience many slowdowns aren't caused by actual waits, but because of
 cacheline contention. And for that the number of acquisitions is much
 more relevant than the waiting. The primary example for this is probably
 the procarray lock.

I would say, that to see particular lwlockid 50 times in 100 samples
or to see it 50 times one after another or see it only 2 times,
provides good and representative information for DBA. At least better
than nothing.


 I don't think counting the number of lock acquisition is that interesting.
 It doesn't give you any information on how long the waits were, for
 example.

 Sure, that's a separate thing that we should be able to answer.

The point is that a lot of short waits sometimes could be as worse as
one long wait. That is why it is important, but I thing propper
sampling provides good estimation for this.


 Greetings,

 Andres Freund

 --
  Andres Freund http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training  Services



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On 2014-10-07 10:45:24 -0400, Robert Haas wrote:
  It's not like it'd be significantly different today - in a read mostly
  workload that's bottlenecked on ProcArrayLock you'll not see many
  waits. There you'd have to count the total number of spinlocks cycles to
  measure anything interesting.
 
 Hmm, really?  I've never had to do that to find bottlenecks.

How did you diagnose procarray contention in a readonly workload
otherwise, without using perf?

  Having said that, if there's no blocking or spindelay any more, to me
  that doesn't mean we should look for some other measure of contention
  instead.  It just means that the whole area is a solved problem, we
  don't need to measure contention any more because there isn't any, and
  we can move on to other issues once we finish partying.  But mildly
  skeptical that the outcome will be as good as all that.
 
  It's not. Just because we're not waiting in a spinlock loop doesn't mean
  there can't be contention... It's just moved one level down, into the cpu.
 
 I guess that's true, but how much of the contention at that level is
 really important to expose to DBAs?

I think so. Right now it's hard to see for them whether the rate of
transactions/the isolation mode is a significant problem or not.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

2014-10-07 Thread Simon Riggs

On 31 July 2014 22:34, Stephen Frost sfr...@snowman.net wrote:
 * Tom Lane (t...@sss.pgh.pa.us) wrote:
 Stephen Frost sfr...@snowman.net writes:
  * Bruce Momjian (br...@momjian.us) wrote:
  Actually, thinking more, Stephen Frost mentioned that the auditing
  system has to modify database _state_, and dumping/restoring the state
  of an extension might be tricky.

  This is really true of any extension which wants to attach information
  or track things associated with roles or other database objects.  What
  I'd like to avoid is having an extension which does so through an extra
  table or through reloptions or one of the other approaches which exists
  in contrib and which implements a capability we're looking at adding to
  core

 We have core code that uses reloptions --- autovacuum for instance ---
 so I'm not exactly clear on why that's so unacceptable for this.

 There was a pretty good thread regarding reloptions and making it so
 extensions could use them which seemed to end up with a proposal to turn
 'security labels' into a more generic metadata capability.  Using that
 kind of a mechanism would at least address one of my concerns about
 using reloptions (specifically that they're specific to relations and
 don't account for the other objects in the system).  Unfortunately, the
 flexibility desired for auditing is more than just all actions of this
 role or all actions on this table but also actions of this role on
 this table, which doesn't fit as well.

Yes, there is a requirement, in some cases, for per role/relation
metadata. Grant and ACLs are a good example.

I spoke with Robert about a year ago that the patch he was most proud
of was the reloptions abstraction. Whatever we do in the future,
keeping metadata in a slightly more abstract form is very useful.

I hope we can get pgAudit in as a module for 9.5. I also hope that it
will stimulate the requirements/funding of further work in this area,
rather than squash it. My feeling is we have more examples of feature
sets that grow over time (replication, view handling, hstore/JSONB
etc) than we have examples of things languishing in need of attention
(partitioning).

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RLS - permissive vs restrictive

* Robert Haas (robertmh...@gmail.com) wrote:
 The key point from my angle is that if you grant user alice the right
 to see records where a = 1 and user bob the right to see records where
 a = 2, the multiple-policy approach allows those quals to be
 implemented as index-scans.  If you had a single policy granting all
 users the right to see records where policyfunc() returns true, it
 would never be indexable.

Right, that is certainly an important aspect also.

 I think that Thom's idea of having some policies that are additional
 filter conditions on top of everything else is a pretty good one.
 It's probably possible to construct a case where you need multiple
 levels of AND and OR logic, which Thom's proposal does not provide
 for.  But are there really cases like that which anyone cares about?

I keep coming back to the feeling that we'd need some kind of exception
capability (more than just excluding the owner), without which this
feature wouldn't end up being practical.

 I think we're going to be tempted to think about that question for
 about 60 seconds and say nope, and that's probably not enough
 thought.  It deserves serious reflection, because I think Thom's
 proposal is terminal: if we do what he's proposing, it'll be hard to
 extend the idea any further if we later discover that it isn't general
 enough.  That having been said, what he's proposing is simple and
 covers a fair amount of ground, and is thus worthy of serious
 consideration, at least IMHO.

Even given the above, I do like the idea in general and have been
thinking we need to provide something along these lines.  I've been
trying to work out if we could provide a way to get to a generalized
CNF capability for policies, but I agree that it's unclear if there's
a real-world need for such.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

Simon,

* Simon Riggs (si...@2ndquadrant.com) wrote:
 On 31 July 2014 22:34, Stephen Frost sfr...@snowman.net wrote:
  There was a pretty good thread regarding reloptions and making it so
  extensions could use them which seemed to end up with a proposal to turn
  'security labels' into a more generic metadata capability.  Using that
  kind of a mechanism would at least address one of my concerns about
  using reloptions (specifically that they're specific to relations and
  don't account for the other objects in the system).  Unfortunately, the
  flexibility desired for auditing is more than just all actions of this
  role or all actions on this table but also actions of this role on
  this table, which doesn't fit as well.
 
 Yes, there is a requirement, in some cases, for per role/relation
 metadata. Grant and ACLs are a good example.
 
 I spoke with Robert about a year ago that the patch he was most proud
 of was the reloptions abstraction. Whatever we do in the future,
 keeping metadata in a slightly more abstract form is very useful.

Agreed.

 I hope we can get pgAudit in as a module for 9.5. I also hope that it
 will stimulate the requirements/funding of further work in this area,
 rather than squash it. My feeling is we have more examples of feature
 sets that grow over time (replication, view handling, hstore/JSONB
 etc) than we have examples of things languishing in need of attention
 (partitioning).

I've come around to this also (which I think I commented on
previously..), as it sounds like the upgrade concerns I was worried
about can be addressed, and having pgAudit would certainly be better
than not having any kind of auditing support.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

On Tue, Oct 7, 2014 at 12:24 PM, Simon Riggs si...@2ndquadrant.com wrote:
 I spoke with Robert about a year ago that the patch he was most proud
 of was the reloptions abstraction. Whatever we do in the future,
 keeping metadata in a slightly more abstract form is very useful.

To slightly correct the record, I believe I was referring to the
generic EXPLAIN options syntax, since copied into a lot of other
places and used to unblock various patches that would have otherwise
died on the vine.

The reloptions stuff was Alvaro's work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal for better support of time-varying timezone abbreviations

2014-10-07 Thread Jim Nasby


On 10/6/14, 6:19 PM, Jim Nasby wrote:

FWIW, I agree for timestamptz, but I do wish we had a timestamp datatype that stored the 
exact timezone in effect when the data was entered. That can really, REALLY save your 
rear if you screw up either timezone in postgresql.conf, or the server's timezone. The 
part that seems hard (at least to me) is the question of how to actually store the 
timezone, because I don't think storing the text string America/Central is 
going to cut it. :/


For the archives... there's an extension that does what I'd been talking about: 
http://pgxn.org/dist/timestampandtz/.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

2014-10-07 Thread Fabrízio de Royes Mello

On Tue, Oct 7, 2014 at 1:24 PM, Simon Riggs si...@2ndquadrant.com wrote:

On 31 July 2014 22:34, Stephen Frost sfr...@snowman.net wrote:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Stephen Frost sfr...@snowman.net writes:
* Bruce Momjian (br...@momjian.us) wrote:
Actually, thinking more, Stephen Frost mentioned that the auditing
system has to modify database _state_, and dumping/restoring the
state
of an extension might be tricky.

This is really true of any extension which wants to attach
information
or track things associated with roles or other database objects.
What
I'd like to avoid is having an extension which does so through an
extra
table or through reloptions or one of the other approaches which
exists
in contrib and which implements a capability we're looking at adding
to
core

We have core code that uses reloptions --- autovacuum for instance ---
so I'm not exactly clear on why that's so unacceptable for this.

There was a pretty good thread regarding reloptions and making it so
extensions could use them which seemed to end up with a proposal to turn
'security labels' into a more generic metadata capability. Using that
kind of a mechanism would at least address one of my concerns about
using reloptions (specifically that they're specific to relations and
don't account for the other objects in the system). Unfortunately, the
flexibility desired for auditing is more than just all actions of this
role or all actions on this table but also actions of this role on
this table, which doesn't fit as well.

Yes, there is a requirement, in some cases, for per role/relation
metadata. Grant and ACLs are a good example.

I spoke with Robert about a year ago that the patch he was most proud
of was the reloptions abstraction. Whatever we do in the future,
keeping metadata in a slightly more abstract form is very useful.

When we discussed about the rejected patch store-custom-reloptions I
pointed my thoughts about it in
http://www.postgresql.org/message-id/cafcns+p+2oa2fg7o-8kwmckazjaywue6mvnnudpurpt0pz8...@mail.gmail.com

We can think in a mechanism to create properties / options and assign it
to objects (table, index, column, schema, ...) like COMMENT does.

A quickly thought:

CREATE OPTION [ IF NOT EXISTS ] name
VALIDATOR valfunction
[ DEFAULT value ];

ALTER TABLE name
SET OPTION optname { TO | = } { value | 'value' | DEFAULT };

It's just a simple thought of course. We must think better about the syntax
and purposes.

I hope we can get pgAudit in as a module for 9.5. I also hope that it
will stimulate the requirements/funding of further work in this area,
rather than squash it. My feeling is we have more examples of feature
sets that grow over time (replication, view handling, hstore/JSONB
etc) than we have examples of things languishing in need of attention
(partitioning).

Regards.

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello

Re: [HACKERS] OCLASS_ROWSECURITY oversights, and other kvetching

* Robert Haas (robertmh...@gmail.com) wrote:
 The RLS patch added OCLASS_ROWSECURITY but it seems that not enough
 effort was made to grep for places that might require adjustment as a
 result.
 
 In objectaddress.c, getObjectDescription() was updated, but
 getObjectTypeDescription() and getObjectIdentity() were not.
 
 In dependency.c, object_classes didn't get updated.

I'm trying to recall why I didn't think it was necessary to add it into
more places..  I did do the 'grep' as described.  I'll go back and
review these.

 I also really question why we've got OCLASS_ROWSECURITY but
 OBJECT_POLICY.  In most cases, we name the OBJECT_* construct and the
 OCLASS_* construct similarly.  This is actually just the tip of the
 iceberg: we've got OBJECT_POLICY but OCLASS_ROWSECURITY (no underscore
 between row and security) and then we've got DO_ROW_SECURITY (with an
 underscore) and pg_row_security.

I'm guessing you mean pg_rowsecurity..  DO_ROW_SECURITY is in pg_dump
only and the ROW_SECURITY cases in the backend are representing the
'row_security' GUC.  That said, I'm not against changing things to be
more consistent, of course.  In this case, pg_rowsecurity should really
be pg_policies as that's what's actually in that catalog.  The original
naming was from the notion that the table-level attribute is
'ROW LEVEL SECURITY', but on reflection it's clearer to have it as
pg_policies.

 But then on the other hand the
 source code is in policy.c.  

Right, the functions for dealing with policies are in policy.c, while
the actual implementation of the table-level 'ROW LEVEL SECURITY'
attribute is in rowsecurity.c.

 pg_dump tries to sit on the fence by
 alternating between all the different names and sometimes combining
 them (row-security policy).  Some places refer to row-LEVEL security
 rather than row security or policies.

There's three different things happening in pg_dump, which I suspect is
why it's gotten inconsistent.  There's setting the ROW_SECURITY GUC,
dumping the fact that the table has been set to ENABLE ROW LEVEL
SECURITY, and dumping out the actual policies which are defined on the
table.

 I think this kind of messiness makes code really hard to maintain and
 should be cleaned up now while we have a chance.  For the most part,
 we have chosen to name our catalogs, SQL commands, and internal
 constants by *what kind of object it is* (in this case, a policy)
 rather than by *the feature it provides* (in this case, row security).
 So I think that everything relates to a policy specifically
 (OCLASS_ROWSECURITY, pg_row_security, etc.) should be renamed to refer
 to policies instead.  The references to row security should be
 preserved only when we are talking about the table-level property,
 which is actually called ROW SECURITY, or the feature in general.

I certainly agree that it can and should be improved.  Given that the
table property is ROW SECURITY, I'd think we would keep the GUC as
'ROW_SECURITY', but change all of the places which are currently working
with policies to use POLICY, such as OCLASS_ROWSECURITY -
OCLASS_POLICY.  I'll go back through and review it with these three
distinctions top-of-mind and work out what other changes make sense.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] TAP test breakage on MacOS X

2014-10-07 Thread Andrew Dunstan

On 10/07/2014 09:53 AM, Andrew Dunstan wrote:

On 10/07/2014 12:15 AM, Tom Lane wrote:

Robert Haas robertmh...@gmail.com writes:
On Mon, Oct 6, 2014 at 8:15 PM, Peter Eisentraut pete...@gmx.net
wrote:

The TAP tests
are arguably already much easier to debug than pg_regress ever was.

Well, maybe. I wasn't able, after about 5 minutes of searching, to
locate either a log file with details of the failure or the code that
revealed what the test, the expected result, and the actual result
were. It's possible that all that information is there and I just
don't know where to look; it took me a while to learn where the
various logs (postmaster.log, initdb.log, results) left behind by
pg_regress were, too. If that information is not there, then I'd say
it's not easier to debug. If it is and I don't know where to look ...
well then I just need to get educated.

The given case seemed pretty opaque to me too. Could we maybe
have some documentation about how to debug TAP failures? Or in
other words, if they're arguably easier to debug, how about
presenting that argument?

Also to the point: does the buildfarm script know how to collect
the information needed to debug a TAP failure?

No. In fact, it doesn't yet know how to run those tests. That's on my
TODO list.

OK, I have a preliminary cut at adding these tests to the client. See
http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=crakedt=2014-10-07%2015%3A38%3A04stg=bin-check
for an example run. The patch is at
https://github.com/PGBuildFarm/client-code/commit/6f644b779c90b16f96e4454b807e804bde48b563

I don't much like the idea of doing an install/initdb/start for every
directory in src/bin, though. Can't we at least manage a single
installation directory for all these?

Also I notice that the tests remove their data directories. That could
make collecting any diagnosis data more difficult. Right now, I have no
idea what I'm looking for anyway.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] GIN pageinspect functions

Some time ago, when debugging a GIN bug, I wrote these pageinspect 
functions to inspect GIN indexes. They were very useful; we should add them.


- Heikki
From 91ef58aab11e9077ab6a38268a1120806e42f2dd Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas heikki.linnakan...@iki.fi
Date: Fri, 12 Sep 2014 13:36:30 +0300
Subject: [PATCH 1/1] Add pageinspect functions for inspecting GIN indexes.

---
 contrib/pageinspect/Makefile  |   7 +-
 contrib/pageinspect/ginfuncs.c| 264 ++
 contrib/pageinspect/pageinspect--1.2--1.3.sql |  42 
 contrib/pageinspect/pageinspect--1.3.sql  | 149 +++
 contrib/pageinspect/pageinspect.control   |   2 +-
 5 files changed, 460 insertions(+), 4 deletions(-)
 create mode 100644 contrib/pageinspect/ginfuncs.c
 create mode 100644 contrib/pageinspect/pageinspect--1.2--1.3.sql
 create mode 100644 contrib/pageinspect/pageinspect--1.3.sql

diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index f10229d..1a8b060 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -1,11 +1,12 @@
 # contrib/pageinspect/Makefile
 
 MODULE_big	= pageinspect
-OBJS		= rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o $(WIN32RES)
+OBJS		= rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o ginfuncs.o $(WIN32RES)
 
 EXTENSION = pageinspect
-DATA = pageinspect--1.2.sql pageinspect--1.0--1.1.sql \
-	pageinspect--1.1--1.2.sql pageinspect--unpackaged--1.0.sql
+DATA = pageinspect--1.3.sql pageinspect--1.0--1.1.sql \
+	pageinspect--1.1--1.2.sql pageinspect--1.2--1.3.sql \
+pageinspect--unpackaged--1.0.sql
 PGFILEDESC = pageinspect - functions to inspect contents of database pages
 
 ifdef USE_PGXS
diff --git a/contrib/pageinspect/ginfuncs.c b/contrib/pageinspect/ginfuncs.c
new file mode 100644
index 000..949d19d
--- /dev/null
+++ b/contrib/pageinspect/ginfuncs.c
@@ -0,0 +1,264 @@
+/*
+ * contrib/pageinspect/ginfuncs.c
+ */
+
+#include postgres.h
+
+#include access/gin.h
+#include access/gin_private.h
+#include access/htup_details.h
+#include catalog/namespace.h
+#include catalog/pg_type.h
+#include funcapi.h
+#include miscadmin.h
+#include utils/builtins.h
+#include utils/rel.h
+
+#define DatumGetItemPointer(X)	 ((ItemPointer) DatumGetPointer(X))
+#define ItemPointerGetDatum(X)	 PointerGetDatum(X)
+
+PG_FUNCTION_INFO_V1(gin_metapage);
+PG_FUNCTION_INFO_V1(gin_pageopaq);
+PG_FUNCTION_INFO_V1(gin_dataleafpage);
+
+Datum
+gin_metapage(PG_FUNCTION_ARGS)
+{
+	bytea	   *raw_page = PG_GETARG_BYTEA_P(0);
+	int			raw_page_size;
+	TupleDesc	tupdesc;
+	Page		page;
+	GinPageOpaque opaq;
+	GinMetaPageData *metadata;
+	HeapTuple	resultTuple;
+	Datum		values[10];
+	bool		nulls[10];
+
+	if (!superuser())
+		ereport(ERROR,
+(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg(must be superuser to use raw page functions;
+
+	raw_page_size = VARSIZE(raw_page) - VARHDRSZ;
+	if (raw_page_size  BLCKSZ)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(input page too small (%d bytes), raw_page_size)));
+	page = VARDATA(raw_page);
+
+	opaq = (GinPageOpaque) PageGetSpecialPointer(page);
+	if (opaq-flags != GIN_META)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(input page is not a GIN metapage),
+ errdetail(Flags %04X, expected %04X,
+		   opaq-flags, GIN_META)));
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, return type must be a row type);
+
+	metadata = GinPageGetMeta(page);
+
+	memset(nulls, 0, sizeof(nulls));
+
+	values[0] = Int64GetDatum(metadata-head);
+	values[1] = Int64GetDatum(metadata-tail);
+	values[2] = Int32GetDatum(metadata-tailFreeSize);
+	values[3] = Int64GetDatum(metadata-nPendingPages);
+	values[4] = Int64GetDatum(metadata-nPendingHeapTuples);
+
+	/* statistics, updated by VACUUM */
+	values[5] = Int64GetDatum(metadata-nTotalPages);
+	values[6] = Int64GetDatum(metadata-nEntryPages);
+	values[7] = Int64GetDatum(metadata-nDataPages);
+	values[8] = Int64GetDatum(metadata-nEntries);
+
+	values[9] = Int32GetDatum(metadata-ginVersion);
+
+	/* Build and return the result tuple. */
+	resultTuple = heap_form_tuple(tupdesc, values, nulls);
+
+	return HeapTupleGetDatum(resultTuple);
+}
+
+
+Datum
+gin_pageopaq(PG_FUNCTION_ARGS)
+{
+	bytea	   *raw_page = PG_GETARG_BYTEA_P(0);
+	int			raw_page_size;
+	TupleDesc	tupdesc;
+	Page		page;
+	GinPageOpaque opaq;
+	HeapTuple	resultTuple;
+	Datum		values[3];
+	bool		nulls[10];
+	Datum		flags[16];
+	int			nflags = 0;
+	uint16		flagbits;
+
+	if (!superuser())
+		ereport(ERROR,
+(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg(must be superuser to use raw page functions;
+
+	raw_page_size = VARSIZE(raw_page) - VARHDRSZ;
+	if (raw_page_size  BLCKSZ)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(input page too small (%d bytes), raw_page_size)));
+	page

Re: [HACKERS] GIN pageinspect functions

2014-10-07 Thread Oleg Bartunov

On Tue, Oct 7, 2014 at 9:03 PM, Heikki Linnakangas hlinnakan...@vmware.com
wrote:

 Some time ago, when debugging a GIN bug, I wrote these pageinspect
 functions to inspect GIN indexes. They were very useful; we should add them.


May be we can merge it with contrib/gevel, which we use many years for
development and debug purposes ?  Have you seen it ?


 - Heikki


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 2

2014-10-07 Thread Steven Lembark

On Mon, 1 Sep 2014 15:19:41 +0200
Joel Jacobson j...@trustly.com wrote:

 The fatal problems with Python3 and Perl6 was the inability to mix
 code between Python2/3 and Perl5/6.
 We don't have that problem with pl-languages in postgres, so please
 don't make that comparison, as it's incorrect.

Actually Perl6 can include Perl5 code allows you to use v5.6 or use 
v6.0 to regulate how the code in any one block is compiled w/in the 
program. Even Perl 5 allows mixing blocks/modules with different version
syntax w/in the same compiler.

The mistake Python made was not allowing the Python 3 compiler to 
gracefully handle Pythin 2 input.

-- 
Steven Lembark 3646 Flora Pl
Workhorse Computing   St Louis, MO 63110
lemb...@wrkhors.com  +1 888 359 3508


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GIN pageinspect functions


On 10/07/2014 08:36 PM, Oleg Bartunov wrote:

On Tue, Oct 7, 2014 at 9:03 PM, Heikki Linnakangas hlinnakan...@vmware.com
wrote:


Some time ago, when debugging a GIN bug, I wrote these pageinspect
functions to inspect GIN indexes. They were very useful; we should add them.



May be we can merge it with contrib/gevel, which we use many years for
development and debug purposes ?  Have you seen it ?


I remember downloading it many years ago, but that's all I remember. 
Where's the latest version?


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TAP test breakage on MacOS X

2014-10-07 Thread Tom Lane

Andrew Dunstan and...@dunslane.net writes:
 I don't much like the idea of doing an install/initdb/start for every 
 directory in src/bin, though. Can't we at least manage a single 
 installation directory for all these?

Peter had a patch to eliminate the overhead of multiple subinstalls;
not sure where that stands, but presumably it would address your issue.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/pgSQL 2

2014-10-07 Thread Rodolfo Campero

2014-09-04 18:29 GMT-03:00 Robert Haas robertmh...@gmail.com:

 On Thu, Sep 4, 2014 at 2:31 PM, Josh Berkus j...@agliodbs.com wrote:
  Sadly, what's prevented us from having packages already has been the
  insistence of potential feature sponsors that they work *exactly* like
  PL/SQL's packages, which is incompatible with Postgres namespacing.
  Also, we'd want any package concept to be usable with external PLs as
  well as PL/pgSQL, which necessitates other Oracle-incompatible changes.

 This is not a fun area in which to try to be exactly like Oracle.
 Just to take one example, the whole package is created and dumped as a
 single object, with all of its contained functions *and their
 comments*, including the exact position of those comments, such as
 inside the argument list to document what particular arguments are
 supposed to do.  We've worked out a (partial) solution to that problem
 in Advanced Server, but it's not perfect, and it limits the ability to
 implement other features that PostgreSQL users would probably expect,
 like being able to add a function to a package after-the-fact.
 PostgreSQL has a certain cleanliness of design that comes from doing
 things in a way that makes sense from first principles, rather than
 the way that other people may have done it.  I'm not prepared to say
 that a $184B company made a bad design decision here - it certainly
 seems to have worked out for them - but it's not what I would have
 picked, and it's not a very good fit for other design decisions we've
 made in PostgreSQL already.

 All-in-all, I'm pretty happy with our EXTENSION system as a way of
 loading code (and SQL function definitions) in a modular way.  It's
 not perfect, but it's definitely made my life as a developer easier.
 There are some things you can do with an Oracle package but not a
 PostgreSQL extension, but there is an awful lot of overlap, too.  I
 doubt we'd want to duplicate all that machinery just for compatibility
 reasons.


If it were possible to mark a function as private for its extension that
would be awesome (the opposite would work too, i.e. a way to specify a
public API, meaning the rest is private). For big extensions it's not clear
which functions can be used directly by users of the extension and which
ones are just implementation details.

Re: [HACKERS] PL/pgSQL 2

2014-10-07 Thread Merlin Moncure

On Tue, Oct 7, 2014 at 12:42 PM, Steven Lembark lemb...@wrkhors.com wrote:
 On Mon, 1 Sep 2014 15:19:41 +0200
 Joel Jacobson j...@trustly.com wrote:

 The fatal problems with Python3 and Perl6 was the inability to mix
 code between Python2/3 and Perl5/6.
 We don't have that problem with pl-languages in postgres, so please
 don't make that comparison, as it's incorrect.

 Actually Perl6 can include Perl5 code allows you to use v5.6 or use
 v6.0 to regulate how the code in any one block is compiled w/in the
 program. Even Perl 5 allows mixing blocks/modules with different version
 syntax w/in the same compiler.

I don't think that really helps very much at the end of the day; Perl
6 was a disaster for Perl.  Breaking compatibility of a language is a
good way to kill it off.  Compiler support is only one example of a
very broad set of problems it causes.  Hiding that compatibility
breaking under language 2.0 doesn't solve anything either.

merlin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Peter Geoghegan

On Tue, Oct 7, 2014 at 6:06 AM, Robert Haas robertmh...@gmail.com wrote:
 I'm not up on the details of what Peter's patch does with heavyweight
 locking, but I'd say it this way: if the patch uses heavyweight
 locking routinely, that's probably not going to scale well[1].   If
 the patch detects possible conflicts and uses heavyweight locking only
 in those cases and for the specific purpose of untangling those
 conflicts, then that might well be OK.

The patch opportunistically tries to use shared buffer locks when a
conflict is expected, when we restart (but only on the unique index
where a conflict was detected). So in the event of a lot of
near-conflicts, the hwlock traffic is quite modest. That, combined
with the fact that it uses what I've called an index scan with an
identity crisis (could be a near-insertion + hwlock in advance of
insertion proper, or just something akin to a regular index scan)
makes it perform best (at least with one or two unique indexes, which
is what I tested a few months back). It does not have a pre-check that
is always wasted with insertion-heavy workloads.

Now, we're not talking about a huge advantage here (I should re-test
that). And, in case I wasn't clear: I have misgivings about all 3
designs. Like Simon, I think it is appropriate that we figure out our
exact requirements using the two working prototype patches. Although,
right now #1 and #2 (the prototypes) seem quite comparable, that might
just be down to a failure of imagination. It's hard to be completely
confident about something like that.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] lwlock contention with SSI