[HACKERS] Compilation of pg_recvlogical on Windows

2014-04-24 Thread Michael Paquier
Hi all,

I noticed that pg_recvlogical is not currently compiled on Windows
when using the msvc scripts. The patch attached corrects that.
Regards,
-- 
Michael
commit b552fbe5bc5ef705ee5a320f1afae66b40dcaedd
Author: Michael Paquier mich...@otacoo.com
Date:   Thu Apr 24 15:46:26 2014 +0900

Support compilation of pg_recvlogical in msvc scripts

diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 308a4b4..d06d6ad 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -375,6 +375,11 @@ sub mkvcbuild
$pgreceivexlog-AddFile('src\bin\pg_basebackup\pg_receivexlog.c');
$pgreceivexlog-AddLibrary('ws2_32.lib');
 
+   my $pgrecvlogical= AddSimpleFrontend('pg_basebackup', 1);
+   $pgrecvlogical-{name} = 'pg_recvlogical';
+   $pgrecvlogical-AddFile('src\bin\pg_basebackup\pg_recvlogical.c');
+   $pgrecvlogical-AddLibrary('ws2_32.lib');
+
my $pgconfig = AddSimpleFrontend('pg_config');
 
my $pgcontrol = AddSimpleFrontend('pg_controldata');

-- 
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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Simon Riggs
On 24 April 2014 05:32, David Fetter da...@fetter.org wrote:
 On Wed, Apr 23, 2014 at 08:27:52PM -0400, Tom Lane wrote:
 David Fetter da...@fetter.org writes:
  Is there any good reason not to roll native UUID generation into
  our standard distribution?

 It's already there (as of 9.4) in pg_crypto.

 Sorry I wasn't clear enough.

 Since contrib/pgcrypto is a module that might well not be installed,
 people can't just build software for PostgreSQL and have UUIDs
 available, certainly not in the sense that, for example, BIGSERIAL is.

+1 to include in core - strange to have a UUID datatype in core but no
means to generate

-- 
 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] Compilation of pg_recvlogical on Windows

2014-04-24 Thread Magnus Hagander
On Thu, Apr 24, 2014 at 8:49 AM, Michael Paquier
michael.paqu...@gmail.comwrote:

 Hi all,

 I noticed that pg_recvlogical is not currently compiled on Windows
 when using the msvc scripts. The patch attached corrects that.


Applied, thanks.



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: [HACKERS] logical changeset generation v6

2014-04-24 Thread Magnus Hagander
On Mon, Sep 23, 2013 at 7:03 PM, Peter Geoghegan p...@heroku.com wrote:

 On Mon, Sep 23, 2013 at 9:54 AM, Andres Freund and...@2ndquadrant.com
 wrote:
  I still find it wierd/inconsistent to have:
  * pg_receivexlog
  * pg_recvlogical
  binaries, even from the same source directory. Why once pg_recv and
  once pg_receive?

 +1


Digging up a really old thread since I just got annoyed by the inconsistent
naming the first time myself :)

I can't find that this discussion actually came to a proper consensus, but
I may be missing something. Did we go with pg_recvlogical just because we
couldn't decide on a better name, or did we intentionally decide it was the
best?

I definitely think pg_receivelogical would be a better name, for
consistency (because it's way too late to rename pg_receivexlog of course -
once released that can't really chance. Which is why *if* we want to change
the name of pg_recvxlog we have a few more days to make a decision..)


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: [HACKERS] logical changeset generation v6

2014-04-24 Thread Andres Freund
On 2014-04-24 09:39:21 +0200, Magnus Hagander wrote:
 I can't find that this discussion actually came to a proper consensus, but
 I may be missing something. Did we go with pg_recvlogical just because we
 couldn't decide on a better name, or did we intentionally decide it was the
 best?

I went with pg_recvlogical because that's where the (small) majority
seemed to be. Even if I was unconvinced. There were so many outstanding
big fights at that point that I didn't want to spend my time on this ;)

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] logical changeset generation v6

2014-04-24 Thread Magnus Hagander
On Thu, Apr 24, 2014 at 9:43 AM, Andres Freund and...@2ndquadrant.comwrote:

 On 2014-04-24 09:39:21 +0200, Magnus Hagander wrote:
  I can't find that this discussion actually came to a proper consensus,
 but
  I may be missing something. Did we go with pg_recvlogical just because we
  couldn't decide on a better name, or did we intentionally decide it was
 the
  best?

 I went with pg_recvlogical because that's where the (small) majority
 seemed to be. Even if I was unconvinced. There were so many outstanding
 big fights at that point that I didn't want to spend my time on this ;)


I was guessing something like the second part there, which is why I figured
this would be a good time to bring this fight back up to the surface ;)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: [HACKERS] logical changeset generation v6

2014-04-24 Thread Andres Freund
On 2014-04-24 09:46:07 +0200, Magnus Hagander wrote:
 On Thu, Apr 24, 2014 at 9:43 AM, Andres Freund and...@2ndquadrant.comwrote:
 
  On 2014-04-24 09:39:21 +0200, Magnus Hagander wrote:
   I can't find that this discussion actually came to a proper consensus,
  but
   I may be missing something. Did we go with pg_recvlogical just because we
   couldn't decide on a better name, or did we intentionally decide it was
  the
   best?
 
  I went with pg_recvlogical because that's where the (small) majority
  seemed to be. Even if I was unconvinced. There were so many outstanding
  big fights at that point that I didn't want to spend my time on this ;)
 
 
 I was guessing something like the second part there, which is why I figured
 this would be a good time to bring this fight back up to the surface ;)

I have to admit that I still don't care too much. By now I'd tentatively
want to stay with the current name because that's what I got used to,
but if somebody else has strong opinions and finds concensus...

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] Perfomance degradation 9.3 (vs 9.2) for FreeBSD

2014-04-24 Thread Ian Barwick
On 24/04/14 09:26, Tatsuo Ishii wrote:
 Included is the graph (from PostgreSQL Enterprise Consortium's 2014
 report page 13: https://www.pgecons.org/downloads/43). I see up to 14%
 degration (at 128 concurrent users) comparing with 9.2.

 That URL returns 'Forbidden'...

 Sorry for this. I sent a problem report to the person in charge.  In
 the mean time, please go to:
 https://www.pgecons.org/download/works_2013/ then click the link 2013
 年度WG1活動報告 (sorry for not English). You should be able to
 download a report (PDF).

 Also the report is written in Japanese. I hope you can read at leat
 the graph in page 13 and the table in page 14.

 Is pgecons planning to do a translation of that at some point? It looks
 like good material, and the audience able to understand it is rather
 limited now :)
 
 Yeah, once I proposed a translation of the documents by professional
 translators to the organization. Their decision was no. The main
 reason was cost. The document is huge and the translation work could
 cost tremendously. So unless someone comes up for volunteering the
 translation work, the document would not be translated.

I actually started translating one of those reports on the way home
from last year's PgCon (PgEcons made a presentation there:
http://www.pgcon.org/2013/schedule/events/556.en.html ) - it was a long flight 
- but
didn't have any
particular incentive to finish it.

It might make a nice JPUG project for members who want to practise their
English.


Regards

Ian Barwick

-- 
 Ian Barwick   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] bgworker crashed or not?

2014-04-24 Thread Petr Jelinek

On 24/04/14 07:39, Craig Ringer wrote:

On 04/17/2014 08:35 AM, Craig Ringer wrote:


As far as I can tell we have a couple of options:

- Redefine what the exit codes mean so that exit 0 suppresses
auto-restart and exits silently. Probably simplest.


I'm now strongly in favour of this alternative.

I've just noticed that the bgworker control interfaces do not honour
bgw.bgw_restart_time = BGW_NEVER_RESTART if you exit with status zero.

This means that it's not simply a problem where you can't say restart
me if I crash, but not if I exit normally.

You also can't even say never restart me at all. Because
BGW_NEVER_RESTART seems to really mean BGW_NO_RESTART_ON_CRASH.



Yes this was one of the reasons why I complained also. Maybe it would be 
enough to just honor the bgw_restart_time always.



--
 Petr Jelinek  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] Json(b) extension

2014-04-24 Thread Dmitry Dolgov
Hi all,

As you know, PostgreSQL introduced Json(b) support at the 9.4 version [1],
and hstore v2.0 saved in separate repository [2]. But although PostgreSQL
has this support at the core level, there are many useful functions, which
wasn't ported to Json(b) from hstore v2.0 and json. Here [3], I've made a
review of the missing Json(b) functions, which can be implemented. This
list can be updated in the future, of course. I want to implement the
missing functions in the form of extension (e.g. contrib/jsonx).

What do you think about this?


[1]: http://obartunov.livejournal.com/177247.html
[2]: http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary
[3]:
https://gist.githubusercontent.com/erthalion/10890778/raw/f042872a92a1ab22ed7eb753fee2358b60d99d4a/hstore_to_jsonb.rst


Re: [HACKERS] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Michael Paquier
On Thu, Apr 24, 2014 at 3:59 PM, Simon Riggs si...@2ndquadrant.com wrote:
 On 24 April 2014 05:32, David Fetter da...@fetter.org wrote:
 Since contrib/pgcrypto is a module that might well not be installed,
 people can't just build software for PostgreSQL and have UUIDs
 available, certainly not in the sense that, for example, BIGSERIAL is.

 +1 to include in core - strange to have a UUID datatype in core but no
 means to generate
+1.
-- 
Michael


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


[HACKERS] Minor improvement to fdwhandler.sgml

2014-04-24 Thread Etsuro Fujita
Hi all,

The patch attached improves docs in fdwhandler.sgml a little bit.

Thanks,

Best regards,
Etsuro Fujita
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 9c818cd..ffb38af 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -873,11 +873,11 @@ GetForeignServerByName(const char *name, bool missing_ok);
 
 para
  In functionGetForeignPlan/, generally the passed-in target list can
- be copied into the plan node as-is.  The passed scan_clauses list
+ be copied into the plan node as-is.  The passed 
structfieldscan_clauses/ list
  contains the same clauses as literalbaserel-gt;baserestrictinfo/,
  but may be re-ordered for better execution efficiency.  In simple cases
  the FDW can just strip structnameRestrictInfo/ nodes from the
- scan_clauses list (using functionextract_actual_clauses/) and put
+ structfieldscan_clauses/ list (using 
functionextract_actual_clauses/) and put
  all the clauses into the plan node's qual list, which means that all the
  clauses will be checked by the executor at run time.  More complex FDWs
  may be able to check some of the clauses internally, in which case those
@@ -895,7 +895,7 @@ GetForeignServerByName(const char *name, bool missing_ok);
  affect the cost estimate for the path.  The path's
  structfieldfdw_private/ field would probably include a pointer to
  the identified clause's structnameRestrictInfo/ node.  Then
- functionGetForeignPlan/ would remove that clause from scan_clauses,
+ functionGetForeignPlan/ would remove that clause from 
structfieldscan_clauses/,
  but add the replaceablesub_expression/ to structfieldfdw_exprs/
  to ensure that it gets massaged into executable form.  It would probably
  also put control information into the plan node's

-- 
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] Review: ECPG FETCH readahead

2014-04-24 Thread Michael Meskes
Thanks an awful lot Antonin.

 Committer availability might well be the issue, but missing review
 probably too.

Yes, you're right. If my taks is mostly one last glance and a commit I will 
make time for that.

 Whether this review is enough to move the patch to ready for committer
 - I tend to let the next CFM decide. (I don't find it productive to
 ignite another round of discussion about kinds of reviews - already saw
 some.)

I saw some remarks in your review that Zoltan wants to address. Once I got the
updated version I'll have a look at it.

Zoltan, could you send a new version by end of day tomorrow? I'll be sitting on
a plane for a longer time again on Saturday. :)

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL


-- 
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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Heikki Linnakangas

On 04/17/2014 12:06 PM, Andres Freund wrote:

On 2014-04-16 19:33:52 -0400, Bruce Momjian wrote:

On Tue, Feb  4, 2014 at 12:58:49AM +0100, Andres Freund wrote:

On 2014-02-03 11:22:45 -0500, Tom Lane wrote:

Andres Freund and...@2ndquadrant.com writes:

On larger, multi-socket, machines, startup takes a fair bit of time. As
I was profiling anyway I looked into it and noticed that just about all
of it is spent in LWLockAssign() called by InitBufferPool(). Starting
with shared_buffers=48GB on the server Nate Boley provided, takes about
12 seconds. Nearly all of it spent taking the ShmemLock spinlock.
Simply modifying LWLockAssign() to not take the spinlock when
!IsUnderPostmaster speeds it up to 2 seconds. While certainly not making
LWLockAssign() prettier it seems enough of a speedup to be worthwile
nonetheless.


Hm.  This patch only works if the postmaster itself never assigns any
LWLocks except during startup.  That's *probably* all right, but it
seems a bit scary.  Is there any cheap way to make the logic actually
be what your comment claims, namely Interlocking is not necessary during
postmaster startup?  I guess we could invent a ShmemInitInProgress global
flag ...


So, here's a flag implementing things with that flag. I kept your name,
as it's more in line with ipci.c's naming, but it looks kinda odd
besides proc_exit_inprogress.


Uh, where are we on this?


I guess it's waiting for the next CF :(.


Now that we have LWLock tranches in 9.4, it might be cleanest to have 
the buffer manager allocate a separate tranche for the buffer locks. We 
could also save some memory if we got rid of the LWLock pointers in 
BufferDesc altogether, and just used the buffer id as an index into the 
LWLock array (we could do that without tranches too, but would have to 
assume that the lock ids returned by LWLockAssign() are a contiguous range).


Another idea is to add an LWLockAssignBatch(int) function that assigns a 
range of locks in one call. That would be very simple, and I think it 
would be less likely to break things than a new global flag. I would be 
OK with sneaking that into 9.4 still.


- 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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Andres Freund
On 2014-04-24 15:56:45 +0300, Heikki Linnakangas wrote:
 On 04/17/2014 12:06 PM, Andres Freund wrote:
 On 2014-04-16 19:33:52 -0400, Bruce Momjian wrote:
 On Tue, Feb  4, 2014 at 12:58:49AM +0100, Andres Freund wrote:
 On 2014-02-03 11:22:45 -0500, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com writes:
 On larger, multi-socket, machines, startup takes a fair bit of time. As
 I was profiling anyway I looked into it and noticed that just about all
 of it is spent in LWLockAssign() called by InitBufferPool(). Starting
 with shared_buffers=48GB on the server Nate Boley provided, takes about
 12 seconds. Nearly all of it spent taking the ShmemLock spinlock.
 Simply modifying LWLockAssign() to not take the spinlock when
 !IsUnderPostmaster speeds it up to 2 seconds. While certainly not making
 LWLockAssign() prettier it seems enough of a speedup to be worthwile
 nonetheless.
 
 Hm.  This patch only works if the postmaster itself never assigns any
 LWLocks except during startup.  That's *probably* all right, but it
 seems a bit scary.  Is there any cheap way to make the logic actually
 be what your comment claims, namely Interlocking is not necessary during
 postmaster startup?  I guess we could invent a ShmemInitInProgress global
 flag ...
 
 So, here's a flag implementing things with that flag. I kept your name,
 as it's more in line with ipci.c's naming, but it looks kinda odd
 besides proc_exit_inprogress.
 
 Uh, where are we on this?
 
 I guess it's waiting for the next CF :(.
 
 Now that we have LWLock tranches in 9.4, it might be cleanest to have the
 buffer manager allocate a separate tranche for the buffer locks. We could
 also save some memory if we got rid of the LWLock pointers in BufferDesc
 altogether, and just used the buffer id as an index into the LWLock array
 (we could do that without tranches too, but would have to assume that the
 lock ids returned by LWLockAssign() are a contiguous range).

I tried that, and it's nontrivial from a performance POV because it
influences how a buffer descriptor fits into cacheline(s). I think this
needs significant experimentation.
My experimentation hinted that it'd be a good idea to put the content
lwlock inline, but the io one not since it's accessed much less
frequently. IIRC I could fit the remainder of the buffer descriptor into
one cacheline after putting the io locks into a separate array. I wonder
if we can't somehow get rid of the io locks entirely...

 Another idea is to add an LWLockAssignBatch(int) function that assigns a
 range of locks in one call. That would be very simple, and I think it would
 be less likely to break things than a new global flag. I would be OK with
 sneaking that into 9.4 still.

I don't really see the advantage tbh. Assuming we always can avoid the
spinlock initially seems simple enough - and I have significant doubts
that anything but buffer locks will need enough locks that it matters
for other users.

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] Review: ECPG FETCH readahead

2014-04-24 Thread Boszormenyi Zoltan

2014-04-24 14:50 keltezéssel, Michael Meskes írta:

Thanks an awful lot Antonin.


Committer availability might well be the issue, but missing review
probably too.

Yes, you're right. If my taks is mostly one last glance and a commit I will 
make time for that.


Whether this review is enough to move the patch to ready for committer
- I tend to let the next CFM decide. (I don't find it productive to
ignite another round of discussion about kinds of reviews - already saw
some.)

I saw some remarks in your review that Zoltan wants to address. Once I got the
updated version I'll have a look at it.

Zoltan, could you send a new version by end of day tomorrow? I'll be sitting on
a plane for a longer time again on Saturday. :)


I will try to.

Thanks in advance,
Zoltán



Michael




--
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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com wrote:
 but for a long time I've thought that it would be nice if
 PostgreSQL came with an example database that had a number of tables,
 perhaps that mock up some easy to relate to real-world application. These
 would be very useful to use as examples in the documents instead of
 inventing them in the ad-hoc way that we currently do. Like here:
 http://www.postgresql.org/docs/9.3/static/tutorial-window.html

I think that's a great idea. I'm not convinced it should be created by
default in initdb, but a CREATE EXTENSION sample_data seems easy
enough for newbies to use and has a good chance of getting merged into
contrib.

Regards,
Marti


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


[HACKERS] Finding relfilenode

2014-04-24 Thread Soroosh Sardari
Hi

In cost functions such as cost_seqscan, a RelOptinfo indicate a base
relation.
But there  is no relfilenode in the RelOptinfo, So how could i find
relfilenode or reloid of the relation?

Thanks,
Soroosh Sardari


Re: [HACKERS] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Pavel Stehule
2014-04-24 15:40 GMT+02:00 Marti Raudsepp ma...@juffo.org:

 On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com
 wrote:
  but for a long time I've thought that it would be nice if
  PostgreSQL came with an example database that had a number of tables,
  perhaps that mock up some easy to relate to real-world application. These
  would be very useful to use as examples in the documents instead of
  inventing them in the ad-hoc way that we currently do. Like here:
  http://www.postgresql.org/docs/9.3/static/tutorial-window.html

 I think that's a great idea. I'm not convinced it should be created by
 default in initdb, but a CREATE EXTENSION sample_data seems easy
 enough for newbies to use and has a good chance of getting merged into
 contrib.


Good idea

Pavel




 Regards,
 Marti


 --
 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] Runing DBT2 on Postgresql

2014-04-24 Thread Rohit Goyal
Hi Peter/All,

Can you please elaborate a bit in details the steps? where are how to run
these steps?

Regards,
Rohit Goyal


On Thu, Apr 24, 2014 at 6:57 AM, Peter Geoghegan p...@heroku.com wrote:

 On Wed, Apr 23, 2014 at 2:33 AM, Rohit Goyal rhtgyl...@gmail.com wrote:
  I am trying to install dbt2 on postgresql database.
 
  cmake(configure) command work fine and but make command(build) give an
 error
  given below. I have no idea about how to solve it

 ld has become less tolerant of certain flag orderings over time in
 certain distros. The following tweak may be used as a quick-and-dirty
 work around:

 diff --git a/CMakeLists.txt b/CMakeLists.txt
 index 6a128e3..f6a796b 100644
 --- a/CMakeLists.txt
 +++ b/CMakeLists.txt
 @@ -11,6 +11,7 @@ SET(DBT2_CLIENT bin/dbt2-client)
  SET(DBT2_DATAGEN bin/dbt2-datagen)
  SET(DBT2_DRIVER bin/dbt2-driver)
  SET(DBT2_TXN_TEST bin/dbt2-transaction-test)
 +set(CMAKE_EXE_LINKER_FLAGS -Wl,--no-as-needed)

  #
  # Check for large file support by using 'getconf'.


 --
 Peter Geoghegan




-- 
Regards,
Rohit Goyal


Re: [HACKERS] Review: ECPG FETCH readahead

2014-04-24 Thread Alvaro Herrera
Just a quickie: I remember noticing earlier that a few comments on
functions would probably get mangled badly by pgindent.  You probably
want to wrap them in /*-   */ to avoid this.  In a very quick glance
now I saw them in ecpg_get_data, ecpg_cursor_next_pos, ECPGfetch.
Perhaps you want to run pgindent on the files you modify, review the
changes, and apply tweaks to avoid unwanted ones.  (I don't mean to
submit pgindented files, because they will be fixed later on anyway; I
only suggest tweaking things that would be damaged.)

-- 
Álvaro Herrerahttp://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] Finding relfilenode

2014-04-24 Thread Tom Lane
Soroosh Sardari soroosh.sard...@gmail.com writes:
 In cost functions such as cost_seqscan, a RelOptinfo indicate a base
 relation.
 But there  is no relfilenode in the RelOptinfo, So how could i find
 relfilenode or reloid of the relation?

You could get the table OID out of the associated RTE.  But I fail to
see what value the relfilenode would have at plan time.

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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes:
 On 24 April 2014 05:32, David Fetter da...@fetter.org wrote:
 Since contrib/pgcrypto is a module that might well not be installed,
 people can't just build software for PostgreSQL and have UUIDs
 available, certainly not in the sense that, for example, BIGSERIAL is.

 +1 to include in core - strange to have a UUID datatype in core but no
 means to generate

The reason why there's no generation function in core is that there is no
standardized, guaranteed-to-produce-a-universally-unique-value generation
algorithm.  That was the reason for not putting something in core when the
type was first created, and I do not see that the technology has advanced.

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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Fabrízio de Royes Mello
On Thu, Apr 24, 2014 at 11:21 AM, Pavel Stehule pavel.steh...@gmail.comwrote:


 2014-04-24 15:40 GMT+02:00 Marti Raudsepp ma...@juffo.org:

 On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com
 wrote:
  but for a long time I've thought that it would be nice if
  PostgreSQL came with an example database that had a number of tables,
  perhaps that mock up some easy to relate to real-world application.
 These
  would be very useful to use as examples in the documents instead of
  inventing them in the ad-hoc way that we currently do. Like here:
  http://www.postgresql.org/docs/9.3/static/tutorial-window.html

 I think that's a great idea. I'm not convinced it should be created by
 default in initdb, but a CREATE EXTENSION sample_data seems easy
 enough for newbies to use and has a good chance of getting merged into
 contrib.


 Good idea


+1

This sample data can be very useful to many things:
* trainning
* automated tests
* benchmark ???

-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello


Re: [HACKERS] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes:
 On 2014-04-24 15:56:45 +0300, Heikki Linnakangas wrote:
 Another idea is to add an LWLockAssignBatch(int) function that assigns a
 range of locks in one call. That would be very simple, and I think it would
 be less likely to break things than a new global flag. I would be OK with
 sneaking that into 9.4 still.

 I don't really see the advantage tbh. Assuming we always can avoid the
 spinlock initially seems simple enough - and I have significant doubts
 that anything but buffer locks will need enough locks that it matters
 for other users.

FWIW, I like the LWLockAssignBatch idea a lot better than the currently
proposed patch.  LWLockAssign is a low-level function that has no business
making risky assumptions about the context it's invoked in.

The other ideas are 9.5 material at this point, since they involve
research --- but I agree with Heikki that LWLockAssignBatch could be
snuck into 9.4 still.

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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Hannu Krosing
On 04/24/2014 04:57 PM, Tom Lane wrote:
 Simon Riggs si...@2ndquadrant.com writes:
 On 24 April 2014 05:32, David Fetter da...@fetter.org wrote:
 Since contrib/pgcrypto is a module that might well not be installed,
 people can't just build software for PostgreSQL and have UUIDs
 available, certainly not in the sense that, for example, BIGSERIAL is.
 +1 to include in core - strange to have a UUID datatype in core but no
 means to generate
 The reason why there's no generation function in core is that there is no
 standardized, guaranteed-to-produce-a-universally-unique-value generation
 algorithm.  That was the reason for not putting something in core when the
 type was first created, and I do not see that the technology has advanced.
Why can't we implement all 5 variants from 
http://en.wikipedia.org/wiki/Universally_unique_identifier
and just warn about the dangers in documentation ?

we could expose it something like next_uuid(version nr);

As the article points out  Since the identifiers have a finite size, it is
possible for two differing items to share the same identifier. so it is a
known limitation of UUID and not something PostgreSQL specific.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Runing DBT2 on Postgresql

2014-04-24 Thread Rohit Goyal
Hi Everyone,

Sorry, i got this now. :)

Regards,
Rohit Goyal


On Thu, Apr 24, 2014 at 4:24 PM, Rohit Goyal rhtgyl...@gmail.com wrote:

 Hi Peter/All,

 Can you please elaborate a bit in details the steps? where are how to run
 these steps?

 Regards,
 Rohit Goyal


 On Thu, Apr 24, 2014 at 6:57 AM, Peter Geoghegan p...@heroku.com wrote:

 On Wed, Apr 23, 2014 at 2:33 AM, Rohit Goyal rhtgyl...@gmail.com wrote:
  I am trying to install dbt2 on postgresql database.
 
  cmake(configure) command work fine and but make command(build) give an
 error
  given below. I have no idea about how to solve it

 ld has become less tolerant of certain flag orderings over time in
 certain distros. The following tweak may be used as a quick-and-dirty
 work around:

 diff --git a/CMakeLists.txt b/CMakeLists.txt
 index 6a128e3..f6a796b 100644
 --- a/CMakeLists.txt
 +++ b/CMakeLists.txt
 @@ -11,6 +11,7 @@ SET(DBT2_CLIENT bin/dbt2-client)
  SET(DBT2_DATAGEN bin/dbt2-datagen)
  SET(DBT2_DRIVER bin/dbt2-driver)
  SET(DBT2_TXN_TEST bin/dbt2-transaction-test)
 +set(CMAKE_EXE_LINKER_FLAGS -Wl,--no-as-needed)

  #
  # Check for large file support by using 'getconf'.


 --
 Peter Geoghegan




 --
 Regards,
 Rohit Goyal




-- 
Regards,
Rohit Goyal


Re: [HACKERS] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Tom Lane
Hannu Krosing ha...@2ndquadrant.com writes:
 On 04/24/2014 04:57 PM, Tom Lane wrote:
 The reason why there's no generation function in core is that there is no
 standardized, guaranteed-to-produce-a-universally-unique-value generation
 algorithm.  That was the reason for not putting something in core when the
 type was first created, and I do not see that the technology has advanced.

 Why can't we implement all 5 variants from 
 http://en.wikipedia.org/wiki/Universally_unique_identifier
 and just warn about the dangers in documentation ?

Essentially, that would mean carrying around our own implementation
of libuuid --- which includes a bunch of not-terribly-portable stuff,
such as discovering the machine's MAC address(es).  That's not really
something I want to see us putting project manpower into.

I wonder what it would take to adapt contrib/uuid-ossp to work on
top of some other popular implementation of that code.  We pretty
much bet on the wrong horse when we picked the OSSP library to
depend on, but otherwise I think the principle of using an external
library was good.

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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Andres Freund
On 2014-04-24 11:02:44 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com writes:
  On 2014-04-24 15:56:45 +0300, Heikki Linnakangas wrote:
  Another idea is to add an LWLockAssignBatch(int) function that assigns a
  range of locks in one call. That would be very simple, and I think it would
  be less likely to break things than a new global flag. I would be OK with
  sneaking that into 9.4 still.
 
  I don't really see the advantage tbh. Assuming we always can avoid the
  spinlock initially seems simple enough - and I have significant doubts
  that anything but buffer locks will need enough locks that it matters
  for other users.
 
 FWIW, I like the LWLockAssignBatch idea a lot better than the currently
 proposed patch.  LWLockAssign is a low-level function that has no business
 making risky assumptions about the context it's invoked in.

I don't think LWLockAssignBatch() is that easy without introducing
layering violations. It can't just return a pointer out of the main
lwlock array that then can be ++ed clientside because MainLWLockArray's
stride isn't sizeof(LWLock).
We could just add a LWLockAssignStartup(), that'd be pretty
trivial. Whoever uses it later gets to keep the pieces...

I guess if it's not that, the whole thing is 9.5 material. Once those
locks are in a separate tranche the whole thing is moot anyway.

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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Heikki Linnakangas

On 04/24/2014 07:24 PM, Andres Freund wrote:

On 2014-04-24 11:02:44 -0400, Tom Lane wrote:

Andres Freund and...@2ndquadrant.com writes:

On 2014-04-24 15:56:45 +0300, Heikki Linnakangas wrote:

Another idea is to add an LWLockAssignBatch(int) function that assigns a
range of locks in one call. That would be very simple, and I think it would
be less likely to break things than a new global flag. I would be OK with
sneaking that into 9.4 still.



I don't really see the advantage tbh. Assuming we always can avoid the
spinlock initially seems simple enough - and I have significant doubts
that anything but buffer locks will need enough locks that it matters
for other users.


FWIW, I like the LWLockAssignBatch idea a lot better than the currently
proposed patch.  LWLockAssign is a low-level function that has no business
making risky assumptions about the context it's invoked in.


I don't think LWLockAssignBatch() is that easy without introducing
layering violations. It can't just return a pointer out of the main
lwlock array that then can be ++ed clientside because MainLWLockArray's
stride isn't sizeof(LWLock).


Well, it could copy the pointers to an array of pointers that the caller 
provides. Or palloc an array and return that. Allocating a large enough 
array to hold NBuffers locks might not be nice, but if you do it in 
batches of, say, 1k locks, that would make it fast enough. Makes the 
caller a bit more complicated, but still might be worth 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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread David Fetter
On Thu, Apr 24, 2014 at 11:30:15AM -0400, Tom Lane wrote:
 Hannu Krosing ha...@2ndquadrant.com writes:
  On 04/24/2014 04:57 PM, Tom Lane wrote:
  The reason why there's no generation function in core is that
  there is no standardized,
  guaranteed-to-produce-a-universally-unique-value generation
  algorithm.  That was the reason for not putting something in core
  when the type was first created, and I do not see that the
  technology has advanced.
 
  Why can't we implement all 5 variants from
  http://en.wikipedia.org/wiki/Universally_unique_identifier and
  just warn about the dangers in documentation ?
 
 Essentially, that would mean carrying around our own implementation
 of libuuid --- which includes a bunch of not-terribly-portable
 stuff, such as discovering the machine's MAC address(es).  That's
 not really something I want to see us putting project manpower into.

We don't need to do the not-terribly-portable stuff in the first
round.  For that, there could still be a bundled extension.

The point is that UUIDs are nowhere near as usable as users have the
right to expect, and we should fix that.

 I wonder what it would take to adapt contrib/uuid-ossp to work on
 top of some other popular implementation of that code.  We pretty
 much bet on the wrong horse when we picked the OSSP library to
 depend on, but otherwise I think the principle of using an external
 library was good.

So long as we can pick another horse later, sure.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes:
 On 2014-04-24 11:02:44 -0400, Tom Lane wrote:
 FWIW, I like the LWLockAssignBatch idea a lot better than the currently
 proposed patch.  LWLockAssign is a low-level function that has no business
 making risky assumptions about the context it's invoked in.

 I don't think LWLockAssignBatch() is that easy without introducing
 layering violations. It can't just return a pointer out of the main
 lwlock array that then can be ++ed clientside because MainLWLockArray's
 stride isn't sizeof(LWLock).

Meh.  I knew this business of using pointers instead of indexes would
have some downsides.

We could return the array stride ... kinda ugly, but since there's
probably only one consumer for this API, it's not *that* bad.  Could
wrap the stride-increment in a macro, perhaps.

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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Tom Lane
David Fetter da...@fetter.org writes:
 On Thu, Apr 24, 2014 at 11:30:15AM -0400, Tom Lane wrote:
 Essentially, that would mean carrying around our own implementation
 of libuuid --- which includes a bunch of not-terribly-portable
 stuff, such as discovering the machine's MAC address(es).  That's
 not really something I want to see us putting project manpower into.

 We don't need to do the not-terribly-portable stuff in the first
 round.  For that, there could still be a bundled extension.

 The point is that UUIDs are nowhere near as usable as users have the
 right to expect, and we should fix that.

The reason that UUIDs aren't as usable as users have a right to expect
is that the underlying technology doesn't meet their (your) expectations.
Just because it's easy to imagine that there are universally unique
identifiers doesn't mean that there actually *are* universally unique
identifiers.  There are only approximations with varying failure modes.

This is not our fault, and I don't want us to get caught up in trying
to fix a fundamentally broken concept --- which is what a generic
uuidserial API would be.  If you try to paper over the difficulties
here, they'll just bite you on the rear someday.

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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Alvaro Herrera
Tom Lane wrote:

 This is not our fault, and I don't want us to get caught up in trying
 to fix a fundamentally broken concept --- which is what a generic
 uuidserial API would be.  If you try to paper over the difficulties
 here, they'll just bite you on the rear someday.

But we have non-colliding generation technology for OIDs in system
catalogs.  We could try to reuse the idea in a UUID generator: grab one
value, try to insert; if it fails generate a new one, lather, rinse,
repeat.

This would make uuidserial not quite as simple as SERIAL pseudotype, of
course.

-- 
Álvaro Herrerahttp://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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Heikki Linnakangas

On 04/24/2014 08:00 PM, Alvaro Herrera wrote:

Tom Lane wrote:


This is not our fault, and I don't want us to get caught up in trying
to fix a fundamentally broken concept --- which is what a generic
uuidserial API would be.  If you try to paper over the difficulties
here, they'll just bite you on the rear someday.


But we have non-colliding generation technology for OIDs in system
catalogs.  We could try to reuse the idea in a UUID generator: grab one
value, try to insert; if it fails generate a new one, lather, rinse,
repeat.


Umm, UUID stands for Universally Unique IDentifier. That would hardly be 
*universally* unique.


- 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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Josh Berkus
On 04/24/2014 10:00 AM, Alvaro Herrera wrote:
 Tom Lane wrote:
 
 This is not our fault, and I don't want us to get caught up in trying
 to fix a fundamentally broken concept --- which is what a generic
 uuidserial API would be.  If you try to paper over the difficulties
 here, they'll just bite you on the rear someday.
 
 But we have non-colliding generation technology for OIDs in system
 catalogs.  We could try to reuse the idea in a UUID generator: grab one
 value, try to insert; if it fails generate a new one, lather, rinse,
 repeat.
 
 This would make uuidserial not quite as simple as SERIAL pseudotype, of
 course.

Tangentally related to this, I believe that PostgreSQL may be the only
current user of the uuid-ossp code at this point.  So we're going to end
up maintaining it for the UUID contrib module, or dropping that module.

Regardless, this all sounds like speculative 9.5ish conversation, and
only vaguely related to Simon's original proposal.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Json(b) extension

2014-04-24 Thread Josh Berkus
On 04/24/2014 03:46 AM, Dmitry Dolgov wrote:
 Hi all,
 
 As you know, PostgreSQL introduced Json(b) support at the 9.4 version [1],
 and hstore v2.0 saved in separate repository [2]. But although PostgreSQL
 has this support at the core level, there are many useful functions, which
 wasn't ported to Json(b) from hstore v2.0 and json. Here [3], I've made a
 review of the missing Json(b) functions, which can be implemented. This
 list can be updated in the future, of course. I want to implement the
 missing functions in the form of extension (e.g. contrib/jsonx).

Thanks for making this list!

 
 What do you think about this?

I think you should do it as a pgxn extension, for now.  For it to be in
contrib/ or core, we'd need to argue extensively about the names of
operators and functions, which will take a while.If your goal is to
make this functionality available as soon as possible to users, an
external extension is the way to go.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Alvaro Herrera
Heikki Linnakangas wrote:
 On 04/24/2014 08:00 PM, Alvaro Herrera wrote:
 Tom Lane wrote:
 
 This is not our fault, and I don't want us to get caught up in trying
 to fix a fundamentally broken concept --- which is what a generic
 uuidserial API would be.  If you try to paper over the difficulties
 here, they'll just bite you on the rear someday.
 
 But we have non-colliding generation technology for OIDs in system
 catalogs.  We could try to reuse the idea in a UUID generator: grab one
 value, try to insert; if it fails generate a new one, lather, rinse,
 repeat.
 
 Umm, UUID stands for Universally Unique IDentifier. That would
 hardly be *universally* unique.

I don't understand your point.  I'm only replying to Tom's assertion
that UUID generation might not be all that unique after all (or, in
other words, AIUI, that the universally unique part of the name is
wishful thinking and not an actual property of the real thing.)

-- 
Álvaro Herrerahttp://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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Alvaro Herrera
Alvaro Herrera wrote:
 Heikki Linnakangas wrote:
  On 04/24/2014 08:00 PM, Alvaro Herrera wrote:
  Tom Lane wrote:
  
  This is not our fault, and I don't want us to get caught up in trying
  to fix a fundamentally broken concept --- which is what a generic
  uuidserial API would be.  If you try to paper over the difficulties
  here, they'll just bite you on the rear someday.
  
  But we have non-colliding generation technology for OIDs in system
  catalogs.  We could try to reuse the idea in a UUID generator: grab one
  value, try to insert; if it fails generate a new one, lather, rinse,
  repeat.
  
  Umm, UUID stands for Universally Unique IDentifier. That would
  hardly be *universally* unique.
 
 I don't understand your point.  I'm only replying to Tom's assertion
 that UUID generation might not be all that unique after all (or, in
 other words, AIUI, that the universally unique part of the name is
 wishful thinking and not an actual property of the real thing.)

Oh, I think I see your point: it's that no matter what we do here, there
would be no way to guarantee that a value we generate does not collide
with any other value elsewhere (either on other uuidserial columns, or
on other servers).

Is that it?

Because if it is, then I think the problem is that the UUID concept
might be flawed yet users still want to use it, and we do no service by
refusing to provide it on those grounds.


Now I certainly don't want my rear bitten more than it already is,
anyway.

-- 
Álvaro Herrerahttp://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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Heikki Linnakangas

On 04/24/2014 08:23 PM, Alvaro Herrera wrote:

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

On 04/24/2014 08:00 PM, Alvaro Herrera wrote:

Tom Lane wrote:


This is not our fault, and I don't want us to get caught up in trying
to fix a fundamentally broken concept --- which is what a generic
uuidserial API would be.  If you try to paper over the difficulties
here, they'll just bite you on the rear someday.


But we have non-colliding generation technology for OIDs in system
catalogs.  We could try to reuse the idea in a UUID generator: grab one
value, try to insert; if it fails generate a new one, lather, rinse,
repeat.


Umm, UUID stands for Universally Unique IDentifier. That would
hardly be *universally* unique.


I don't understand your point.  I'm only replying to Tom's assertion
that UUID generation might not be all that unique after all (or, in
other words, AIUI, that the universally unique part of the name is
wishful thinking and not an actual property of the real thing.)


Oh, I think I see your point: it's that no matter what we do here, there
would be no way to guarantee that a value we generate does not collide
with any other value elsewhere (either on other uuidserial columns, or
on other servers).

Is that it?


Yep.


Because if it is, then I think the problem is that the UUID concept
might be flawed yet users still want to use it, and we do no service by
refusing to provide it on those grounds.


Well, we should make a reasonable effort to make them unique. If there 
is a reliable-enough way to generate UUIDs that doesn't depend on 
external libraries, by all means lets have it in core. I believe the 
reason we put gen_random_uuid() in pgcrypto is that it needs a good 
random number generator, and we don't trust plain old random() to be 
good enough for that.


- 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] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Josh Berkus
Alvaro,

 I don't understand your point.  I'm only replying to Tom's assertion
 that UUID generation might not be all that unique after all (or, in
 other words, AIUI, that the universally unique part of the name is
 wishful thinking and not an actual property of the real thing.)
 
 Oh, I think I see your point: it's that no matter what we do here, there
 would be no way to guarantee that a value we generate does not collide
 with any other value elsewhere (either on other uuidserial columns, or
 on other servers).
 
 Is that it?
 
 Because if it is, then I think the problem is that the UUID concept
 might be flawed yet users still want to use it, and we do no service by
 refusing to provide it on those grounds.

It's more than that:

1) the concept of UUIDs is fundamentally flawed, to the extent that if
we have a UUID type in core its flaws become our flaws, to be handled in
bug reports forever.

2) Because the concept of UUIDs is flawed, there are multiple competing
implementations, none of which is clearly dominant and durable.  As
such, any UUID algorithm we adopt for core stands a significant risk of
being later abandoned by everyone else and becoming a PostgreSQL wart.

3) In general, users who want UUIDs don't want a generic concept of
UUIDs; they want the specific UUIDs which work with their individual
programming languages, web frameworks, or queueing platforms.  So, see
competing implementations above.

As case in point for (2), as I said upthread: uuid-ossp, which has been
our option for UUID in contrib since originally it was the only OSS
implementation, is now abandoned by everyone but us.

Additionally, were I to adopt a UUID scheme for PostgreSQL, I would want
to to be *for postgresql*, with components indicating server, table and
schema of origin for each ID.  A pseudo-random UUID is frankly pretty
useless to me because (a) it's not really unique, and (b) it doesn't
help me route data at all.

Alternatively, what would be *really* useful is to have a way for an
extension to plug into the serial concept, so that it gets all of the
benefits of serial (permissions, dependancies, etc.) while being able to
call a custom generator function.

Oh, and:

4) IIRC, Andres has already worked out a scheme for distributed serials
to support BDR.  So this is a solved problem for the only really
interesting use case ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes:
 Oh, I think I see your point: it's that no matter what we do here, there
 would be no way to guarantee that a value we generate does not collide
 with any other value elsewhere (either on other uuidserial columns, or
 on other servers).

Not that way, anyway.

 Because if it is, then I think the problem is that the UUID concept
 might be flawed yet users still want to use it, and we do no service by
 refusing to provide it on those grounds.

My point is a bit more subtle than that.  It's perfectly reasonable to
want a universally-unique identifier in some applications.  The problem
is that there is no perfect implementation, and so people really need
to stop and think about which generator algorithm they're going to use
and whether its particular failure modes are acceptable in their
context.  So I'm not eager to provide some easy to use API that
encourages users to believe that no thought is required on their part.

Having said that, though, the argument around whether such facilities
belong in core seems to devolve to whether you want to buy into
maintaining libuuid for ourselves (in fact worse than that: it's not clear
that libuuid ever has worked on all our platforms, so we might be buying
into some up-front porting work too).  As Josh notes, we may be forced
into that anyway if we don't want to walk away from contrib/uuid-ossp.
But first I'd like to see a bit more research into what the alternatives
might be...

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] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Christopher Browne
Last year, I built a pl/pgsql generator of version 1-ish UUIDs, which
would combine timestamps with local information to construct data that kind
of emulated the timestamp+MAC address that is version #1 of UUID.

Note that there are several versions of UUIDs:

1.  Combines MAC address, timestamp, random #
2.  DCE Security (replaces some bits with user's UID/GID and others with
POSIX Domain); I don't think this one is much used...
3.  MD5 Hash
4.  Purely Random
5.  SHA-1 Hash

There are merits to each.  The tough one is #1, as that requires pulling
data that can't generally be accessed portably.

I figured out (and could probably donate some code) how to construct the
bits of #1 using the inputs of *my* choice (e.g. - I set up to make up my
own MAC address surrogate, and transformed PostgreSQL timestamp values into
the timestamp, and threw in my own bit of randomness), which provided
well-formed UUIDs with nice enough characteristics.

It wouldn't be out there to do a somewhat PostgreSQL-flavoured version of
this that wouldn't actually use MAC addresses, but rather, would use data
we have:

a) Having a sequence feeding some local uniqueness would fit with the
clock seq bits (e.g. - the octets in RFC 4122 entitled
clock-seq-and-reserved and clock-seq-low)
b) NOW() provides data for time-low, time-mid, time-high-and-version
c) We'd need 6 hex octets for node; I seem to recall there being
something established by initdb that might be usable.

The only piece that's directly troublesome, for UUID Type 1, is the node
value.  I'll observe that it isn't unusual for UUID implementations to
generate random values for that.

Note that for the other UUID versions, there's NO non-portable data needed.

It seems to me that a UUIDserial type, which combined:
  a) A sequence, to be the 'clock';
  b) Possibly another sequence to store local node ID, which might get
seeded from DB internals
would provide a PostgreSQL-flavoured version of UUID Type 1.


Re: [HACKERS] assertion failure 9.3.4

2014-04-24 Thread Alvaro Herrera
Alvaro Herrera wrote:
 Alvaro Herrera wrote:
 
  I'm thinking about the comparison of full infomask as you propose
  instead of just the bits that we actually care about.   I think the only
  thing that could cause a spurious failure (causing an extra execution of
  the HeapTupleSatisfiesUpdate call and the stuff below) is somebody
  setting HEAP_XMIN_COMMITTED concurrently; but that seems infrequent
  enough that it should pretty harmless.  However, should we worry about
  possible future infomask bit changes that could negatively affect this
  behavior?
 
 Here's a complete patch illustrating what I mean.  This is slightly more
 expensive than straight infomask comparison in terms of machine
 instructions, but that seems okay to me.

I have pushed a slightly tweaked version of this, after verifying that
it solves the problem in Andrew's test environment.

Josh, if you could verify that it solves the problem for you too, it'd
be great.

Thanks for the report and test case.

-- 
Álvaro Herrerahttp://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] slow startup due to LWLockAssign() spinlock

2014-04-24 Thread Andres Freund
On 2014-04-24 12:43:13 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com writes:
  On 2014-04-24 11:02:44 -0400, Tom Lane wrote:
  FWIW, I like the LWLockAssignBatch idea a lot better than the currently
  proposed patch.  LWLockAssign is a low-level function that has no business
  making risky assumptions about the context it's invoked in.
 
  I don't think LWLockAssignBatch() is that easy without introducing
  layering violations. It can't just return a pointer out of the main
  lwlock array that then can be ++ed clientside because MainLWLockArray's
  stride isn't sizeof(LWLock).
 
 Meh.  I knew this business of using pointers instead of indexes would
 have some downsides.
 
 We could return the array stride ... kinda ugly, but since there's
 probably only one consumer for this API, it's not *that* bad.  Could
 wrap the stride-increment in a macro, perhaps.

I think I am just going to wait for 9.5 where I sure hope we can
allocate the buffer lwlocks outside the main array...

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] Clock sweep not caching enough B-Tree leaf pages?

2014-04-24 Thread Peter Geoghegan
On Mon, Apr 21, 2014 at 11:57 PM, Peter Geoghegan p...@heroku.com wrote:
 Here is a benchmark that is similar to my earlier one, but with a rate
 limit of 125 tps, to help us better characterize how the prototype
 patch helps performance:

 http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/3-sec-delay-limit/

I've added some more test sets to this result report, again with a 125
TPS limit, but on this occasion with a pgbench Gaussian distribution.
I used V13 of the recently proposed Guassian distribution pgbench
patch [1] to accomplish this, including the Gaussian variant of tpc-b
that the pgbench patch has baked in. The distribution threshold used
was consistently 5, causing the patched pgbench to report for each
test:

transaction type: Custom query
scaling factor: 5000
standard deviation threshold: 5.0
access probability of top 20%, 10% and 5% records: 0.68269 0.38293 0.19741

It looks like the patch continues to have much lower latency than
master for this somewhat distinct workload. Actually, even though the
background writer is somewhat working harder than in the uniform
distribution case, the average latency with patched is appreciably
lower. Total buffers allocated are just as consistent as before for
patched, but the number is markedly lower than for the prior uniform
distribution case. Dirty memory graphs start off similar to the
uniform case with patched, but get a bit spikier towards the end of
each test run there. It's still *markedly* better than master for
either distribution type, which is still really aggressive at times
for master, and other times by far isn't aggressive enough, in much
the same way as before.

In general, with the Gaussian distribution, average latency is lower,
but worst case is higher. The patch maintains its clear lead for
average case, albeit a smaller lead than with uniform, and with worst
case things are much better relatively speaking. Absolute worst case
(and not worst case averaged across client counts) is 1.4 seconds with
patched, to 8.3 with master...and that terrible worst case happens
*twice* with master. For uniform distribution, the same figure was 5.4
- 5.8 seconds for master, and 0.6 seconds for patched.

What is curious is that with master and with the Gaussian
distribution, I see distinct latency no man's land in multiple test
runs, like this one here for example:
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/3-sec-delay-limit/49/index.html
. It looks like there is a clear differentiation between going to disk
and not going to disk, or something like that. I don't see this for
any other case, and it's quite obviously a consistent and distinct
feature of master + Gaussian when the OS isn't aggressively writing
out a mountain of dirty memory. This is something that I personally
have never seen before.

I also note that master had 3 huge background writer spikes with a
Gaussian distribution, rather than 2 and 1 small one, as was
(consistently) demonstrated to happen with a uniform distribution.
What's more, 90th percentile latency is very consistent across client
counts for the new patched test run, as opposed to being very much
higher with higher client counts when master is tested.

[1] http://www.postgresql.org/message-id/alpine.DEB.2.10.1404011107220.2557@sto
-- 
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] Composite Datums containing toasted fields are a bad idea(?)

2014-04-24 Thread Tom Lane
I wrote:
 I'm actually planning to set this patch on the shelf for a bit and go
 investigate the other alternative, ie, not generating composite Datums
 containing toast pointers in the first place.

Here's a draft patch along those lines.  It turned out to be best to
leave heap_form_tuple() alone and instead put the dirty work into
HeapTupleGetDatum().  That has some consequences worth discussing:

* The patch changes HeapTupleGetDatum from a simple inline macro into
a function call.  This means that third-party extensions will not get
protection against creation of toast-pointer-containing composite Datums
until they recompile.  I'm not sure that this is a big deal, though.
After looking through the existing core and contrib code, it seems that
nearly all users of HeapTupleGetDatum are building their tuples from
locally-sourced data that could not possibly be toasted anyway.  (This is
true a-priori for users of BuildTupleFromCStrings, for example, and seems
to be true of all uses of HeapTupleGetDatum in SQL-callable functions.)
So it's entirely possible that there would be no live bug anywhere even
without recompiles.

* If we were sufficiently worried about the previous point, we could
get some protection against unfixed extension code by not removing
toast_flatten_tuple_attribute() in the back branches, only in HEAD.
I'm doubtful that it's worth the cycles though.

* Because HeapTupleGetDatum might allocate a new tuple, the wrong thing
might happen if the caller changes CurrentMemoryContext between
heap_form_tuple and HeapTupleGetDatum.  I could only find two places
that did that, though, both in plpgsql.  I thought about having
HeapTupleGetDatum try to identify the context the passed tuple had been
allocated in, but the problem with that is the passed tuple isn't
necessarily heap-allocated at all --- in fact, one of the two problematic
places in plpgsql passes a pointer to a stack-local variable, so we'd
actually crash if we tried to apply GetMemoryChunkContext() to it.
Of course we can (and I did) change plpgsql, but the question is whether
any third-party code has copied either coding pattern.  On balance it
seems best to just use palloc; that's unlikely to cause anything worse
than a memory leak.

* I was quite pleased with the size of the patch: under 100 net new lines,
half of that being a new regression test case.  And it's worth emphasizing
that this is a *complete* fix, modulo the question of third-party code
recompiles.  The patch I showed a few days ago added several times that
much just to fix arrays of composites, and I wasn't too confident that it
fixed every case even for arrays.

* The cases where an extra detoast would be incurred are pretty narrow:
basically, whole-row-Var references, ROW() constructs, and the outputs of
functions returning tuples.  As discussed earlier, whether this would cost
anything or save anything would depend on the number of subsequent uses of
the composite Datum's previously-toasted fields.  But I'm thinking that
the amount of application code that would actually be impacted in either
direction is probably pretty small.  Moreover, we aren't adding any
noticeable overhead in cases where no detoasting occurs, unlike the
situation with the previous patch.  (In fact, we're saving some overhead
by getting rid of syscache lookups in toast_flatten_tuple_attribute.)

So, despite Noah's misgivings, I'm thinking this is the way to proceed.

Comments?

regards, tom lane

diff --git a/src/backend/access/common/heaptuple.c b/src/backend/access/common/heaptuple.c
index aea9d40..c64ede9 100644
*** a/src/backend/access/common/heaptuple.c
--- b/src/backend/access/common/heaptuple.c
*** heap_copytuple_with_tuple(HeapTuple src,
*** 617,622 
--- 617,657 
  	memcpy((char *) dest-t_data, (char *) src-t_data, src-t_len);
  }
  
+ /* 
+  *		heap_copy_tuple_as_datum
+  *
+  *		copy a tuple as a composite-type Datum
+  * 
+  */
+ Datum
+ heap_copy_tuple_as_datum(HeapTuple tuple, TupleDesc tupleDesc)
+ {
+ 	HeapTupleHeader td;
+ 
+ 	/*
+ 	 * If the tuple contains any external TOAST pointers, we have to inline
+ 	 * those fields to meet the conventions for composite-type Datums.
+ 	 */
+ 	if (HeapTupleHasExternal(tuple))
+ 		return toast_flatten_tuple_to_datum(tuple-t_data,
+ 			tuple-t_len,
+ 			tupleDesc);
+ 
+ 	/*
+ 	 * Fast path for easy case: just make a palloc'd copy and insert the
+ 	 * correct composite-Datum header fields (since those may not be set if
+ 	 * the given tuple came from disk, rather than from heap_form_tuple).
+ 	 */
+ 	td = (HeapTupleHeader) palloc(tuple-t_len);
+ 	memcpy((char *) td, (char *) tuple-t_data, tuple-t_len);
+ 
+ 	HeapTupleHeaderSetDatumLength(td, tuple-t_len);
+ 	HeapTupleHeaderSetTypeId(td, tupleDesc-tdtypeid);
+ 	HeapTupleHeaderSetTypMod(td, tupleDesc-tdtypmod);
+ 
+ 	return PointerGetDatum(td);
+ }
+ 
  /*
   * heap_form_tuple
   *		construct a tuple from 

Re: [HACKERS] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Thu, Apr 24, 2014 at 8:40 PM, Josh Berkus j...@agliodbs.com wrote:
 A pseudo-random UUID is frankly pretty
 useless to me because (a) it's not really unique

This is FUD. A pseudorandom UUID contains 122 bits of randomness. As
long as you can trust the random number generator, the chances of a
value occurring twice can be estimated using the birthday paradox:
there's a 50% chance of having *one* collision in a set of 2^61 items.
Storing this amount of UUIDs alone requires 32 exabytes of storage.
Factor in the tuple and indexing overheads and you'd be needing close
to all the hard disk space ever manufactured in the world.

If you believe there's a chance of ever seeing a pseudorandom UUID
collision in practice, you should be buying lottery tickets.

To the contrary. Combined with the fact that pseudorandom UUID
generation doesn't require any configuration (node ID), doesn't leak
any private data (MAC address) and relies on infrastructure that's
ubiquitous anyway (cryptographic PRNG) it's almost always the right
answer.

 (b) it doesn't help me route data at all.

That's really out of scope for UUIDs. They're about generating
identifiers, not describing what the identifier means. UUIDs also
don't happen to cure cancer.

Regards,
Marti


-- 
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] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Tom Lane
Marti Raudsepp ma...@juffo.org writes:
 On Thu, Apr 24, 2014 at 8:40 PM, Josh Berkus j...@agliodbs.com wrote:
 A pseudo-random UUID is frankly pretty
 useless to me because (a) it's not really unique

 This is FUD. A pseudorandom UUID contains 122 bits of randomness. As
 long as you can trust the random number generator, the chances of a
 value occurring twice can be estimated using the birthday paradox:
 there's a 50% chance of having *one* collision in a set of 2^61 items.

Of course, the weak spot in this analysis is the assumption that there
are actually 122 independent bits in the value.  It's not difficult to
imagine that systems with crummy random() implementations might only have
something like 32 bits worth of real randomness.  Or less.  Seeding your
PRNG from gettimeofday(), for instance, is highly likely to lead to
collisions ... no matter how good the PRNG itself is.

 If you believe there's a chance of ever seeing a pseudorandom UUID
 collision in practice, you should be buying lottery tickets.

Now *that*, I'd call FUD.  The issue here is not whether collisions
are improbable under ideal circumstances.  The issue is how much work
does it take to have some confidence that you're anywhere near the
ideal case.

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] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Fri, Apr 25, 2014 at 3:36 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Of course, the weak spot in this analysis is the assumption that there
 are actually 122 independent bits in the value.  It's not difficult to
 imagine that systems with crummy random() implementations might only have
 something like 32 bits worth of real randomness.

Obviously you can't use random(). That's why I talked about
cryptographic PRNGs, crypto libraries do proper seeding and generate
reliably random numbers all the time.

Regards,
Marti


-- 
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] API change advice: Passing plan invalidation info from the rewriter into the planner?

2014-04-24 Thread Craig Ringer
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/15/2014 10:06 AM, Stephen Frost wrote:
 I've uploaded the latest patch, rebased against master, with my
 changes to here: http://snowman.net/~sfrost/rls_ringerc_sf.patch.gz
 as I don't believe it'd clear the mailing list (it's 29k).

Does this exist in the form of an accessible git branch, too?

I was trying to maintain the patch as a series of distinct changes to
make it easier to see what each part is doing, and it'd be nice to
preserve that if possible. It also makes seeing what's changed a lot
easier.

- -- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTWbGNAAoJELBXNkqjr+S28W4H/R49CJfz4Y3TMbvwxhrwkjL2
WEv80qY4GDCzG5CGKROn3kT9H5xePvL9eadSjr+CPsilerHrPkHmXnU5w+K2LnKV
MCL/A2969b4ng1cUK9eHEFVx9BLLQmiVI6DbJ2OA2oWUs/Y7Zne5h6q0fNnnnTSq
XEU6r3tVkUp5ipbhHi+aJ+mfckirdcMR0U5X+2fgGpLZ3D+8j9azvuXvQjSOekVB
3+EVVI0UXhhvw4It4/1CjieHvScdxnsz9bOpKGiEeePUB3CGC0iPtBgIGtE0n2OK
cqKryuwZ3++LZih74M8z+Rn6yao5f4ElJrO3gz5q8axKzH/bHkEYElwEUhVfbSE=
=AKzL
-END PGP SIGNATURE-


-- 
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] review: Non-recursive processing of AND/OR lists

2014-04-24 Thread Gurjeet Singh
On Thu, Jul 18, 2013 at 1:54 PM, Gurjeet Singh gurj...@singh.im wrote:

 On Thu, Jul 18, 2013 at 10:19 AM, Tom Lane t...@sss.pgh.pa.us wrote:


  Because simpler code is less likely to have bugs and is easier to
  maintain.

 I agree with that point, but one should also remember Polya's Inventor's
 Paradox: the more general problem may be easier to solve.  That is, if
 done right, code that fully flattens an AND tree might actually be
 simpler than code that does just a subset of the transformation.  The
 current patch fails to meet this expectation,


 The current patch does completely flatten any type of tree
 (left/right-deep or bushy) without recursing, and right-deep and bushy tree
 processing is what Robert is recommending to defer to recursive processing.
 Maybe I haven't considered a case where it doesn't flatten the tree; do you
 have an example in mind.


 but maybe you just haven't
 thought about it the right way.


I tried to eliminate the 'pending' list, but I don't see a way around it.
We need temporary storage somewhere to store the branches encountered on
the right; in recursion case the call stack was serving that purpose.



 My concerns about this patch have little to do with that, though, and
 much more to do with the likelihood that it breaks some other piece of
 code that is expecting AND/OR to be strictly binary operators, which
 is what they've always been in parsetrees that haven't reached the
 planner.  It doesn't appear to me that you've done any research on that
 point whatsoever


 No, I haven't, and I might not be able to research it for a few more weeks.


There are about 30 files (including optimizer and executor) that match
case-insensitive search for BoolExpr, and I scanned those for the usage of
the member 'args'. All the instances where BoolExpr.args is being accessed,
it's being treated as a null-terminated list. There's one exception that I
could find, and it was in context of NOT expression: not_clause() in
clauses.c.




 you have not even updated the comment for BoolExpr
 (in primnodes.h) that this patch falsifies.


 I will fix that.


I think this line in that comment already covers the fact that in some
special cases a BoolExpr may have more than 2 arguments.

There are also a few special cases where more arguments can appear before
optimization.

I have updated the comment nevertheless, and removed another comment in
parse_expr.c that claimed to be the only place where a BoolExpr with more
than 2 args is generated.

I have isolated the code for right-deep and bushy tree processing via the
macro PROCESS_BUSHY_TREES. Also, I have shortened some variable names while
retaining their meaning.

Please find the updated patch attached (based on master).

Best regards,
-- 
Gurjeet Singh http://gurjeet.singh.im/

EDB www.EnterpriseDB.com http://www.enterprisedb.com
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 81c9338..eb35d70 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -41,8 +41,7 @@ bool		Transform_null_equals = false;
 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
 static Node *transformParamRef(ParseState *pstate, ParamRef *pref);
 static Node *transformAExprOp(ParseState *pstate, A_Expr *a);
-static Node *transformAExprAnd(ParseState *pstate, A_Expr *a);
-static Node *transformAExprOr(ParseState *pstate, A_Expr *a);
+static Node *transformAExprAndOr(ParseState *pstate, A_Expr *a);
 static Node *transformAExprNot(ParseState *pstate, A_Expr *a);
 static Node *transformAExprOpAny(ParseState *pstate, A_Expr *a);
 static Node *transformAExprOpAll(ParseState *pstate, A_Expr *a);
@@ -224,10 +223,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 		result = transformAExprOp(pstate, a);
 		break;
 	case AEXPR_AND:
-		result = transformAExprAnd(pstate, a);
+		result = transformAExprAndOr(pstate, a);
 		break;
 	case AEXPR_OR:
-		result = transformAExprOr(pstate, a);
+		result = transformAExprAndOr(pstate, a);
 		break;
 	case AEXPR_NOT:
 		result = transformAExprNot(pstate, a);
@@ -918,32 +917,102 @@ transformAExprOp(ParseState *pstate, A_Expr *a)
 	return result;
 }
 
+/*
+ * Transform the AND/OR trees non-recursively.
+ *
+ * The parser turns a list of consecutive AND expressions into a left-deep tree.
+ *
+ * a AND b AND c
+ *
+ *  AND
+ * /  \
+ *   AND   c
+ *  /  \
+ * ab
+ *
+ * For very long lists, it gets deep enough that processing it recursively causes
+ * check_stack_depth() to raise error and abort the query. Hence, it is necessary
+ * that we process these trees iteratively.
+ */
 static Node *
-transformAExprAnd(ParseState *pstate, A_Expr *a)
+transformAExprAndOr(ParseState *pstate, A_Expr *a)
 {
-	Node	   *lexpr = transformExprRecurse(pstate, a-lexpr);
-	Node	   *rexpr = transformExprRecurse(pstate, a-rexpr);
+#define PROCESS_BUSHY_TREES 1
+	List		   *exprs = NIL;
+#if PROCESS_BUSHY_TREES
+	

Re: [HACKERS] API change advice: Passing plan invalidation info from the rewriter into the planner?

2014-04-24 Thread Stephen Frost
* Craig Ringer (cr...@2ndquadrant.com) wrote:
 On 04/15/2014 10:06 AM, Stephen Frost wrote:
  I've uploaded the latest patch, rebased against master, with my
  changes to here: http://snowman.net/~sfrost/rls_ringerc_sf.patch.gz
  as I don't believe it'd clear the mailing list (it's 29k).
 
 Does this exist in the form of an accessible git branch, too?

Eh, no.

 I was trying to maintain the patch as a series of distinct changes to
 make it easier to see what each part is doing, and it'd be nice to
 preserve that if possible. It also makes seeing what's changed a lot
 easier.

Yeah, I almost just posted a patch against your tree.  I'll look at
doing that tomorrow.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Peter Eisentraut
On Thu, 2014-04-24 at 13:45 -0400, Tom Lane wrote:
 Having said that, though, the argument around whether such facilities
 belong in core seems to devolve to whether you want to buy into
 maintaining libuuid for ourselves (in fact worse than that: it's not
 clear
 that libuuid ever has worked on all our platforms, so we might be
 buying
 into some up-front porting work too).  As Josh notes, we may be forced
 into that anyway if we don't want to walk away from contrib/uuid-ossp.

I think we arrived at the conclusion a short while go that we wanted to
stick a deprecation notice on contrib/uuid-ossp for the 9.4 release.

I wrote an extension that wraps libuuid:
https://github.com/petere/pglibuuid

Between pgcrypto and that, is there anything missing that users of
uuid-ossp would have had?




-- 
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] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Christopher Browne
On Thu, Apr 24, 2014 at 8:43 PM, Marti Raudsepp ma...@juffo.org wrote:

 On Fri, Apr 25, 2014 at 3:36 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  Of course, the weak spot in this analysis is the assumption that there
  are actually 122 independent bits in the value.  It's not difficult to
  imagine that systems with crummy random() implementations might only have
  something like 32 bits worth of real randomness.

 Obviously you can't use random(). That's why I talked about
 cryptographic PRNGs, crypto libraries do proper seeding and generate
 reliably random numbers all the time.


... And we can't be certain that there won't be some internal
characteristic weakness.

Cryptography is *hard*; treating it as certainty that things will be gotten
correct
tends to be a foolish assumption.

Which is why UUID type 1 resolves this by combining multiple sorts of
anti-correlations, the combination of:
a) Node-specific information (e.g. - in the standard form, parts of the MAC
address), so no collisions between node A and node B.
b) Timestamp, so that things that happen at different times will be kept
unique.
c) An extra sequence, so that if there are multiple events on the same node
at the same time, they *still* don't collide.

I trust the combination to work pretty well, and that's why it was designed
that way.

A RNG, however good, can't provide the same guarantees of lack of conflicts.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?


Re: [HACKERS] review: Non-recursive processing of AND/OR lists

2014-04-24 Thread Tom Lane
Gurjeet Singh gurj...@singh.im writes:
 I tried to eliminate the 'pending' list, but I don't see a way around it.
 We need temporary storage somewhere to store the branches encountered on
 the right; in recursion case the call stack was serving that purpose.

I still think we should fix this in the grammar, rather than introducing
complicated logic to try to get rid of the recursion later.  For example,
as attached.

The existing A_Expr representation of raw AND/OR nodes isn't conducive to
this, but it's not that hard to change it.  The attached patch chooses to
use BoolExpr as both the raw and transformed representation of AND/OR/NOT;
we could alternatively invent some new raw-parsetree node type, but I
don't see any advantage in that.

I continue to think that more thought is needed about downstream
processing.  For instance, at least the comment at the head of prepqual.c
is wrong now, and it's worth wondering whether the planner still needs to
worry about AND/OR flattening at all.  (It probably does, to deal with
view-flattening cases for example; but it's worth considering whether
anything could be saved if we stopped doing that.)

regards, tom lane

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 1e48a7f..95f5dd2 100644
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
*** raw_expression_tree_walker(Node *node,
*** 3047,3052 
--- 3047,3060 
  /* operator name is deemed uninteresting */
  			}
  			break;
+ 		case T_BoolExpr:
+ 			{
+ BoolExpr   *expr = (BoolExpr *) node;
+ 
+ if (walker(expr-args, context))
+ 	return true;
+ 			}
+ 			break;
  		case T_ColumnRef:
  			/* we assume the fields contain nothing interesting */
  			break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 10e8139..cd4bce1 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*** _outAExpr(StringInfo str, const A_Expr *
*** 2437,2451 
  			appendStringInfoChar(str, ' ');
  			WRITE_NODE_FIELD(name);
  			break;
- 		case AEXPR_AND:
- 			appendStringInfoString(str,  AND);
- 			break;
- 		case AEXPR_OR:
- 			appendStringInfoString(str,  OR);
- 			break;
- 		case AEXPR_NOT:
- 			appendStringInfoString(str,  NOT);
- 			break;
  		case AEXPR_OP_ANY:
  			appendStringInfoChar(str, ' ');
  			WRITE_NODE_FIELD(name);
--- 2437,2442 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7b9895d..dd04b1a 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*** static void insertSelectOptions(SelectSt
*** 151,156 
--- 151,159 
  static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
  static Node *doNegate(Node *n, int location);
  static void doNegateFloat(Value *v);
+ static Node *makeAndExpr(Node *lexpr, Node *rexpr, int location);
+ static Node *makeOrExpr(Node *lexpr, Node *rexpr, int location);
+ static Node *makeNotExpr(Node *expr, int location);
  static Node *makeAArrayExpr(List *elements, int location);
  static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args,
  		 List *args, int location);
*** a_expr:		c_expr	{ $$ = $1; }
*** 10849,10859 
  { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, NULL, @2); }
  
  			| a_expr AND a_expr
! { $$ = (Node *) makeA_Expr(AEXPR_AND, NIL, $1, $3, @2); }
  			| a_expr OR a_expr
! { $$ = (Node *) makeA_Expr(AEXPR_OR, NIL, $1, $3, @2); }
  			| NOT a_expr
! { $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL, $2, @1); }
  
  			| a_expr LIKE a_expr
  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ~~, $1, $3, @2); }
--- 10852,10862 
  { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, NULL, @2); }
  
  			| a_expr AND a_expr
! { $$ = makeAndExpr($1, $3, @2); }
  			| a_expr OR a_expr
! { $$ = makeOrExpr($1, $3, @2); }
  			| NOT a_expr
! { $$ = makeNotExpr($2, @1); }
  
  			| a_expr LIKE a_expr
  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ~~, $1, $3, @2); }
*** a_expr:		c_expr	{ $$ = $1; }
*** 11022,11032 
  }
  			| a_expr IS NOT DISTINCT FROM a_expr		%prec IS
  {
! 	$$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL,
! 	(Node *) makeSimpleA_Expr(AEXPR_DISTINCT,
! 			  =, $1, $6, @2),
! 			 @2);
! 
  }
  			| a_expr IS OF '(' type_list ')'			%prec IS
  {
--- 11025,11033 
  }
  			| a_expr IS NOT DISTINCT FROM a_expr		%prec IS
  {
! 	$$ = makeNotExpr((Node *) makeSimpleA_Expr(AEXPR_DISTINCT,
! 			   =, $1, $6, @2),
! 	 @2);
  }
  			| a_expr IS OF '(' type_list ')'			%prec IS
  {
*** a_expr:		c_expr	{ $$ = $1; }
*** 11044,11086 
  			 */
  			| a_expr BETWEEN opt_asymmetric b_expr AND b_expr		%prec BETWEEN
  {
! 	$$ = (Node *) makeA_Expr(AEXPR_AND, NIL,
  		(Node *) makeSimpleA_Expr(AEXPR_OP, =, $1, $4, @2),
  		(Node *)