Re: [HACKERS] SQL access to database attributes

[Jaime Casanova]

On Fri, May 23, 2014 at 11:06 PM, Vik Fearing  wrote:
> On 05/24/2014 12:03 AM, Jaime Casanova wrote:
>> On Fri, May 23, 2014 at 10:53 PM, Vik Fearing  wrote:
>>> It was suggested to me that these options should either error out if
>>> there are existing connections or terminate said connections.  I don't
>>> agree with that because there is no harm in connecting to a template
>>> database (how else do you modify it?), and adding a reject rule in
>>> pg_hba.conf doesn't disconnect existing users so why should turning off
>>> ALLOW CONNECTIONS do it?
>>>
>> Which lead us to the question: you need to connect to the database to
>> modify it, don't you? then, how do you change ALLOW CONNECTIONS to
>> true?
>
> You can ALTER DATABASE from anywhere.
>

ah! doh! right!
don't know why i was convinced you need to connect to the database to
execute ALTER DATABASE

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157


-- 
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] SQL access to database attributes

[Vik Fearing]

On 05/24/2014 12:03 AM, Jaime Casanova wrote:
> On Fri, May 23, 2014 at 10:53 PM, Vik Fearing  wrote:
>> It was suggested to me that these options should either error out if
>> there are existing connections or terminate said connections.  I don't
>> agree with that because there is no harm in connecting to a template
>> database (how else do you modify it?), and adding a reject rule in
>> pg_hba.conf doesn't disconnect existing users so why should turning off
>> ALLOW CONNECTIONS do it?
>>
> Which lead us to the question: you need to connect to the database to
> modify it, don't you? then, how do you change ALLOW CONNECTIONS to
> true?

You can ALTER DATABASE from anywhere.

-- 
Vik



-- 
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] SQL access to database attributes

[Jaime Casanova]

On Fri, May 23, 2014 at 10:53 PM, Vik Fearing  wrote:
>
> It was suggested to me that these options should either error out if
> there are existing connections or terminate said connections.  I don't
> agree with that because there is no harm in connecting to a template
> database (how else do you modify it?), and adding a reject rule in
> pg_hba.conf doesn't disconnect existing users so why should turning off
> ALLOW CONNECTIONS do it?
>

Which lead us to the question: you need to connect to the database to
modify it, don't you? then, how do you change ALLOW CONNECTIONS to
true?

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157


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

[HACKERS] SQL access to database attributes

[Vik Fearing]

We try to tell our clients not to update the catalogs directly, but
there are at least two instances where it's not possible to do otherwise
(pg_database.datistemplate and .datallowconn).  This patch aims to
remedy that.

For example, it is now possible to say
ALTER DATABASE d ALLOW CONNECTIONS = false;
and
ALTER DATABASE d IS TEMPLATE = true;

This syntax matches that of CONNECTION LIMIT but unfortunately required
me to make ALLOW and CONNECTIONS unreserved keywords.  I know we try not
to do that but I didn't see any other way.  The two new options are of
course also available on CREATE DATABASE.

There is a slight change in behavior with this patch in that previously
one had to be superuser or have rolcatupdate appropriately set, and now
the owner of the database is also allowed to change these settings.  I
believe this is for the better.

It was suggested to me that these options should either error out if
there are existing connections or terminate said connections.  I don't
agree with that because there is no harm in connecting to a template
database (how else do you modify it?), and adding a reject rule in
pg_hba.conf doesn't disconnect existing users so why should turning off
ALLOW CONNECTIONS do it?

As for regression tests, I couldn't figure out how to make CREATE/ALTER
DATABASE play nice with make installcheck and so I haven't provided any.

Other than that, I think this patch is complete and so I'm adding it the
next commitfest.

-- 
Vik

*** a/doc/src/sgml/ref/alter_database.sgml
--- b/doc/src/sgml/ref/alter_database.sgml
***
*** 25,30  ALTER DATABASE name [ [ WITH ] where option can be:
  
+ IS TEMPLATE istemplate
+ ALLOW CONNECTIONS allowconn
  CONNECTION LIMIT connlimit
  
  ALTER DATABASE name RENAME TO new_name
***
*** 107,112  ALTER DATABASE name RESET ALL
--- 109,134 
   
  
   
+   istemplate
+   
+
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false, then only superusers or the owner of the
+ database can clone it.
+
+   
+  
+ 
+  
+   allowconn
+   
+
+ If false then no one can connect to this database.
+
+   
+  
+ 
+  
connlimit

 
*** a/doc/src/sgml/ref/create_database.sgml
--- b/doc/src/sgml/ref/create_database.sgml
***
*** 28,33  CREATE DATABASE name
--- 28,35 
 [ LC_COLLATE [=] lc_collate ]
 [ LC_CTYPE [=] lc_ctype ]
 [ TABLESPACE [=] tablespace_name ]
+[ IS TEMPLATE [=] istemplate]
+[ ALLOW CONNECTIONS [=] allowconn]
 [ CONNECTION LIMIT [=] connlimit ] ]
  
   
***
*** 148,153  CREATE DATABASE name
--- 150,175 
   
  
   
+   istemplate
+   
+
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false, then only superusers or the owner of the
+ database can clone it.
+
+   
+  
+ 
+  
+   allowconn
+   
+
+ If false then no one can connect to this database.
+
+   
+  
+ 
+  
connlimit

 
*** a/src/backend/commands/dbcommands.c
--- b/src/backend/commands/dbcommands.c
***
*** 39,44 
--- 39,45 
  #include "catalog/pg_tablespace.h"
  #include "commands/comment.h"
  #include "commands/dbcommands.h"
+ #include "commands/defrem.h"
  #include "commands/seclabel.h"
  #include "commands/tablespace.h"
  #include "mb/pg_wchar.h"
***
*** 122,127  createdb(const CreatedbStmt *stmt)
--- 123,130 
  	DefElem*dencoding = NULL;
  	DefElem*dcollate = NULL;
  	DefElem*dctype = NULL;
+ 	DefElem*distemplate = NULL;
+ 	DefElem	   *dallowconn = NULL;
  	DefElem*dconnlimit = NULL;
  	char	   *dbname = stmt->dbname;
  	char	   *dbowner = NULL;
***
*** 130,135  createdb(const CreatedbStmt *stmt)
--- 133,140 
  	char	   *dbctype = NULL;
  	char	   *canonname;
  	int			encoding = -1;
+ 	bool	istemplate = false;
+ 	bool	allowconn = true;
  	int			dbconnlimit = -1;
  	int			notherbackends;
  	int			npreparedxacts;
***
*** 188,193  createdb(const CreatedbStmt *stmt)
--- 193,214 
  		 errmsg("conflicting or redundant options")));
  			dctype = defel;
  		}
+ 		else if (strcmp(defel->defname, "istemplate") == 0)
+ 		{
+ 			if (distemplate)
+ ereport(ERROR,
+ 		(errcode(ERRCODE_SYNTAX_ERROR),
+ 		 errmsg("conflicting or redundant options")));
+ 			distemplate = defel;
+ 		}
+ 		else if (strcmp(defel->defname, "allowconnections") == 0)
+ 		{
+ 			if (dallowconn)
+ ereport(ERROR,
+ 		(errcode(ERRCODE_SYNTAX_ERROR),
+ 		 errmsg("conflicting or redundant options")));
+ 			dallowconn = defel;
+ 		}
  		else if (strcmp(defel->defname, "connectionlimit") == 0)
  		{
  			if (dconn

Re: [HACKERS] Allowing join removals for more join types

[David Rowley]

On Sat, May 24, 2014 at 3:13 AM, Tom Lane  wrote:

> David Rowley  writes:
> > I've just had a bit of a look at implementing checks allowing subqueries
> > with unique indexes on the join cols being removed,
>
> I'm a bit confused by this statement of the problem.  I thought the idea
> was to recognize that subqueries with DISTINCT or GROUP BY clauses produce
> known-unique output column(s), which permits join removal in the same way
> that unique indexes on a base table allow us to deduce that certain
> columns are known-unique and hence can offer no more than one match for
> a join.  That makes it primarily a syntactic check, which you can perform
> despite the fact that the subquery hasn't been planned yet (since the
> parser has done sufficient analysis to determine the semantics of
> DISTINCT/GROUP BY).
>
>
Up thread a little Dilip was talking about in addition to checking that if
the sub query could be proved to be unique on the join condition using
DISTINCT/GROUP BY, we might also check unique indexes in the subquery to
see if they could prove the query is unique on the join condition.

For example a query such as:

SELECT a.* FROM a LEFT JOIN (SELECT b.* FROM b LIMIT 1) b ON a.column =
b.colwithuniqueidx

The presence of the LIMIT would be enough to stop the subquery being pulled
up, but there'd be no reason to why the join couldn't be removed.

I think the use case for this is likely a bit more narrow than the GROUP
BY/DISTINCT case, so I'm planning on using the time on looking into more
common cases such as INNER JOINs where we can prove the existence of the
row using a foreign key.


> Drilling down into the subquery is a whole different matter.  For one
> thing, there's no point in targeting cases in which the subquery would be
> eligible to be flattened into the parent query, and your proposed list of
> restrictions seems to eliminate most cases in which it couldn't be
> flattened.  For another, you don't have access to any planning results for
> the subquery yet, which is the immediate problem you're complaining of.
> Duplicating the work of looking up a relation's indexes seems like a
> pretty high price to pay for whatever improvement you might get here.
>
>
I agree that there are not many cases left to remove the join that remain
after is_simple_subquery() has decided not to pullup the subquery. Some of
the perhaps more common cases would be having windowing functions in the
subquery as this is what you need to do if you want to include the results
of a windowing function from within the where clause. Another case, though
I can't imagine it would be common, is ORDER BY in the subquery... But for
that one I can't quite understand why is_simple_subquery() stops that being
flattened in the first place.

Regards

David Rowley

Re: [HACKERS] SKIP LOCKED DATA (work in progress)

[Robert Haas]

On Fri, May 23, 2014 at 3:24 PM, Simon Riggs  wrote:
> PostgreSQL already chose to follow the Oracle syntax when we
> implemented NOWAIT. So my proposal is that we follow the Oracle syntax
> again and use the words SKIP LOCKED.
>
> I don't see any advantage in inventing new syntax that leaves us
> incompatible with Oracle, nor do I see any need to be compatible with
> both Oracle and DB2 since the latter is much less likely to gain us
> anything in practice.

+1.

-- 
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] SKIP LOCKED DATA (work in progress)

[Pavel Stehule]

2014-05-23 21:24 GMT+02:00 Simon Riggs :

> On 23 May 2014 10:40, Tom Lane  wrote:
>
> > If we're pulling syntax out of the air it'd be nice if we could avoid
> > adding new keywords to the grammar.
>
> Oracle, SQLServer and DB2 have this capability. MySQL does not.
>
> SQLServer implements that using the table hint of READPAST. Since that
> whole syntax area is radically different to what we have, it isn't
> easy to maintain code compatibility.
>
> DB2 z/OS 10 provides SKIP LOCKED DATA clause to allow moving past
> already locked rows. That's fairly recent and I don't believe there
> will be many programs using that. DB2 UDB supports some complex
> functionality using DB2_SKIPINSERTED, DB2_EVALUNCOMMITTED and
> DB2_SKIPDELETED, all of which is complex and mostly exists for
> benchmarks, AFAICS.
>
> Oracle uses both SKIP LOCKED and NOWAIT.
>
> PostgreSQL already chose to follow the Oracle syntax when we
> implemented NOWAIT. So my proposal is that we follow the Oracle syntax
> again and use the words SKIP LOCKED.
>
> I don't see any advantage in inventing new syntax that leaves us
> incompatible with Oracle, nor do I see any need to be compatible with
> both Oracle and DB2 since the latter is much less likely to gain us
> anything in practice.
>

+1

Pavel


>
> --
>  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] SKIP LOCKED DATA (work in progress)

[Simon Riggs]

On 23 May 2014 10:40, Tom Lane  wrote:

> If we're pulling syntax out of the air it'd be nice if we could avoid
> adding new keywords to the grammar.

Oracle, SQLServer and DB2 have this capability. MySQL does not.

SQLServer implements that using the table hint of READPAST. Since that
whole syntax area is radically different to what we have, it isn't
easy to maintain code compatibility.

DB2 z/OS 10 provides SKIP LOCKED DATA clause to allow moving past
already locked rows. That's fairly recent and I don't believe there
will be many programs using that. DB2 UDB supports some complex
functionality using DB2_SKIPINSERTED, DB2_EVALUNCOMMITTED and
DB2_SKIPDELETED, all of which is complex and mostly exists for
benchmarks, AFAICS.

Oracle uses both SKIP LOCKED and NOWAIT.

PostgreSQL already chose to follow the Oracle syntax when we
implemented NOWAIT. So my proposal is that we follow the Oracle syntax
again and use the words SKIP LOCKED.

I don't see any advantage in inventing new syntax that leaves us
incompatible with Oracle, nor do I see any need to be compatible with
both Oracle and DB2 since the latter is much less likely to gain us
anything in practice.

-- 
 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] 9.4 release notes

[Bruce Momjian]

On Fri, May 23, 2014 at 10:11:37AM +0900, Tomonari Katsumata wrote:
> Hi,
> 
> I have two comments about 9.4 release notes.
> 
> 1. typo
> >Pg_upgrade now uses -U to specify the user name (Bruce Momjian)
> 
> It should be pg_upgrade.
> 
> 2. undesirable link
> >Allow pg_recvlogical to receive data logical decoding data (Andres Freund)
> 
> The term of "pg_recvlogical" jumps to a page of pg_receivexlog.
> It should jump to pg_recvlogical(app-pgrecvlogical.html).


Fixed.  Thanks.  Uppdated documentation changes can be viewed in five
minutes at:

http://momjian.us/pgsql_docs/release-9-4.html

-- 
  Bruce Momjian  http://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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Bruce Momjian]

On Fri, May 23, 2014 at 03:36:20PM +0200, Andres Freund wrote:
> On 2014-05-22 09:20:38 -0600, Jeff Ross wrote:
> > On 5/21/14, 2:37 PM, Bruce Momjian wrote:
> > >The only item I can think of that would cause this is someone changing
> > >the length of a string.  Did someone modify pg_attribute directly to
> > >increase the length of one of the character columns?
> 
> > I don't know, sorry.
> 
> > >I just tested ALTER TABLE in 8.4 and it does create a toast table for
> > >this case in 9.4:
> > >
> > >   CREATE TABLE test (x CHAR(10));
> > >   ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> > >
> > I just tried this on the problem table and it did indeed create a toast
> > table.
> > 
> > I then retried pg_upgrade and it failed with the same problem on a different
> > table in the same database.  Of the 67 databases in the 8.4 cluster, 5 (so
> > far) have had this problem on at least one table.
> 
> Any chance you could, *before* you create the toast table, do a:
> SELECT attrelid::regclass, attname, attnum, attlen, *
> FROM pg_attribute
> WHERE attrelid = 'a'::regclass
> ORDER BY attnum ASC;
> 
> Where 'a' is replaced by the affected table?

That's an interesting idea.  In binary_upgrade mode, if we create a
TOAST table and were _not_ passed in a toast id, we could detect that,
though it just means we detect the failure earlier.  Seeing as this is
the first such report, I am not included to see that as a win.

-- 
  Bruce Momjian  http://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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Bruce Momjian]

On Fri, May 23, 2014 at 08:32:35AM -0600, Jeff Ross wrote:
> 
> On 5/23/14, 7:21 AM, Bruce Momjian wrote:
> >
> >On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:
> >>>I just tested ALTER TABLE in 8.4 and it does create a toast table for
> >>>this case in 9.4:
> >>>
> >>>   CREATE TABLE test (x CHAR(10));
> >>>   ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> >>>
> >>I just tried this on the problem table and it did indeed create a
> >>toast table.
> >>
> >>I then retried pg_upgrade and it failed with the same problem on a
> >>different table in the same database.  Of the 67 databases in the
> >>8.4 cluster, 5 (so far) have had this problem on at least one table.
> >
> >Yeah, it would be nice to be able to report all the problem tables, but
> >I don't know how to do that except from pg_upgrade failing.  Is there
> >anything similar about these tables?
> >
> 
> 
> Here are the last 2 tables I had a problem with:

Both have character varying fields, which supports the idea that the
field length might have been modified in pg_attribute.

-- 
  Bruce Momjian  http://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] Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Bruce Momjian]

On Fri, May 23, 2014 at 06:28:28AM -0700, David G Johnston wrote:
> Bruce Momjian wrote
> > On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:
> >> >I just tested ALTER TABLE in 8.4 and it does create a toast table for
> >> >this case in 9.4:
> >> >
> >> >  CREATE TABLE test (x CHAR(10));
> >> >  ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> >> >
> >> I just tried this on the problem table and it did indeed create a
> >> toast table.
> >> 
> >> I then retried pg_upgrade and it failed with the same problem on a
> >> different table in the same database.  Of the 67 databases in the
> >> 8.4 cluster, 5 (so far) have had this problem on at least one table.
> > 
> > Yeah, it would be nice to be able to report all the problem tables, but
> > I don't know how to do that except from pg_upgrade failing.  Is there
> > anything similar about these tables?
> 
> Would a toast table in this situation have to be empty on the 8.4 database? 
> Is there some kind of stat table query that would identify all such toast
> tables?  Although it is possible some of those tables do indeed need a toast
> table but never make use of it (especially if one makes judicious use of
> unlimited text columns but never fills them with large amounts of data -
> like for lookup tables).

I don't see that as helping here.

-- 
  Bruce Momjian  http://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] SKIP LOCKED DATA (work in progress)

[Thomas Munro]

On 23 May 2014 15:40, Tom Lane  wrote:
> A different concern is that this patch adds not one but two new unreserved
> keywords, ie SKIP and LOCKED.  That bloats our parser tables, which are
> too darn large already, and it has a nonzero compatibility cost (since
> we only allow AS-less column aliases when they are no keyword at all).
> If we're pulling syntax out of the air it'd be nice if we could avoid
> adding new keywords to the grammar.

How about some of these combinations of existing words:

EXCLUDE LOCK
NOWAIT EXCLUDE
NOWAIT NEXT
NOWAIT FOLLOWING
NOWAIT DISCARD

Of those I think I prefer NOWAIT EXCLUDE (perhaps with NOWAIT ABORT as a
long version of the existing NOWAIT behaviour for contrast).

Or adding just one new keyword:

NOWAIT SKIP
SKIP LOCK

Regards,
Thomas Munro

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

[Amit Kapila]

On Fri, Jan 31, 2014 at 3:24 PM, Andres Freund 
wrote:
> I've pushed a rebased version of the patchset to
> http://git.postgresql.org/gitweb/?p=users/andresfreund/postgres.git
> branch rwlock contention.
> 220b34331f77effdb46798ddd7cca0cffc1b2858 actually was the small problem,
> ea9df812d8502fff74e7bc37d61bdc7d66d77a7f was the major PITA.

As per discussion in developer meeting, I wanted to test shared
buffer scaling patch with this branch.  I am getting merge
conflicts as per HEAD.  Could you please get it resolved, so that
I can get the data.

>From git://git.postgresql.org/git/users/andresfreund/postgres
 * branchrwlock-contention -> FETCH_HEAD
Auto-merging src/test/regress/regress.c
CONFLICT (content): Merge conflict in src/test/regress/regress.c
Auto-merging src/include/storage/proc.h
Auto-merging src/include/storage/lwlock.h
CONFLICT (content): Merge conflict in src/include/storage/lwlock.h
Auto-merging src/include/storage/ipc.h
CONFLICT (content): Merge conflict in src/include/storage/ipc.h
Auto-merging src/include/storage/barrier.h
CONFLICT (content): Merge conflict in src/include/storage/barrier.h
Auto-merging src/include/pg_config_manual.h
Auto-merging src/include/c.h
Auto-merging src/backend/storage/lmgr/spin.c
Auto-merging src/backend/storage/lmgr/proc.c
Auto-merging src/backend/storage/lmgr/lwlock.c
CONFLICT (content): Merge conflict in src/backend/storage/lmgr/lwlock.c
Auto-merging src/backend/storage/ipc/shmem.c
Auto-merging src/backend/storage/ipc/ipci.c
Auto-merging src/backend/access/transam/xlog.c
CONFLICT (content): Merge conflict in src/backend/access/transam/xlog.c
Auto-merging src/backend/access/transam/twophase.c
Auto-merging configure.in
Auto-merging configure
Auto-merging config/c-compiler.m4
Automatic merge failed; fix conflicts and then commit the result.



With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] uuid-ossp (Re: [pgsql-packagers] Postgresapp 9.4 beta build ready)

[Matteo Beccati]

On 23/05/2014 10:05, Matteo Beccati wrote:
> You can find the code here:
> https://github.com/mbeccati/uuid # NetBSD variant
> https://github.com/mbeccati/uuid/tree/linux # Ubuntu variant
> 
> For now, I've forked just RhodiumToad's uuid-freebsd extension, but I've
> made sure make works fine when cloned in the contrib folder.
> 
> * Both the variants use a copy of pgcrypto md5/sha1 implementations to
> generate v3 and v5 UUIDs as porting is much easier than trying to use
> the system provided ones, if any.
> * I've fixed a bug in v3/v5 generation wrt endianness as the results I
> was getting didn't match the RFC.
> * The code is PoC quality and I haven't touched the docs/readme yet.

And here's my last effort w/ autoconf support:

https://github.com/mbeccati/postgres/compare/postgres:master...master

It's surely far from perfect, but maybe closer to something that can be
considered as a replacement for OSSP.

Especially I'm not that happy about the #ifdefs cluttering the code and
AC_SEARCH_LIB putting libuuid in $LIBS. Any suggestion?


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.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] Allowing join removals for more join types

[Tom Lane]

David Rowley  writes:
> I've just had a bit of a look at implementing checks allowing subqueries
> with unique indexes on the join cols being removed,

I'm a bit confused by this statement of the problem.  I thought the idea
was to recognize that subqueries with DISTINCT or GROUP BY clauses produce
known-unique output column(s), which permits join removal in the same way
that unique indexes on a base table allow us to deduce that certain
columns are known-unique and hence can offer no more than one match for
a join.  That makes it primarily a syntactic check, which you can perform
despite the fact that the subquery hasn't been planned yet (since the
parser has done sufficient analysis to determine the semantics of
DISTINCT/GROUP BY).

Drilling down into the subquery is a whole different matter.  For one
thing, there's no point in targeting cases in which the subquery would be
eligible to be flattened into the parent query, and your proposed list of
restrictions seems to eliminate most cases in which it couldn't be
flattened.  For another, you don't have access to any planning results for
the subquery yet, which is the immediate problem you're complaining of.
Duplicating the work of looking up a relation's indexes seems like a
pretty high price to pay for whatever improvement you might get here.

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] postgres_fdw and connection management

[Sandro Santilli]

On Fri, May 23, 2014 at 11:13:50AM -0300, Fabrízio de Royes Mello wrote:
> On Fri, May 23, 2014 at 8:09 AM, Sandro Santilli  wrote:
> >
> > I'm evaluating use of the postgres FDW to keep in sync a central database
> > to changes made in thousand other databases, via triggers.
> >
> > But as long as postgres_fdw keeps connections open for the whole lifetime
> > of a session this conflicts with large use of poolers which make sessions
> > virtually never expire.
> >
> > Is there any way to ask the postgres_fdw to close connections at the
> > end of a transaction ? Or would it be possible at all to do from a
> > FDW handler ? Do you see any drawback in doing that ?
> >
> > I'm willing to work on a patch, maybe accepting an additional OPTION,
> > if you agree on the idea.
> >
> 
> Maybe this is a case for the idea pointed here [1]. One way to register a
> cleanup code to extensions, then we can run a command called 'DISCARD
> EXTENSIONS' and/or 'DISCARD ALL' to do this job.
> 
> Unfortunately nobody comment my suggestion yet.
>
> [1]
> http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=przuhv4-m1+j5eth6...@mail.gmail.com

Indeed I tried "DISCARD ALL" in hope it would have helped, so I find
good your idea of allowing extensions to register an hook there.

Still, I'd like the FDW handler itself to possibly be configured
to disable the pool completely as a server-specific configuration.

--strk;


-- 
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] SKIP LOCKED DATA (work in progress)

[Tom Lane]

Robert Haas  writes:
> On Sat, May 17, 2014 at 1:02 AM, Craig Ringer  wrote:
>> We have a long tradition of trying to allow noise keywords where it's
>> harmless.
>> 
>> So the clause should probably be
>> 
>> SKIP LOCKED [DATA]
>> 
>> in much the same way we have
>> 
>> BEGIN [ WORK | TRANSACTION ] ...
>> 
>> There won't be any ambiguity there.

> We've had some problems in the past where allowing optional noise
> words resulted in grammar conflicts that made future features harder
> to add.

In this particular case, I'd be worried about whether we'd not end up
having to fully reserve DATA in order to allow it to be optional here.
That would be necessary if this clause could be followed immediately
by an identifier, either now or in the future.  That would be a mighty
high price to pay for failing to make up our minds about which syntax
to use.  (How many tables out there do you think have "data" as a column
name?)

A different concern is that this patch adds not one but two new unreserved
keywords, ie SKIP and LOCKED.  That bloats our parser tables, which are
too darn large already, and it has a nonzero compatibility cost (since
we only allow AS-less column aliases when they are no keyword at all).
If we're pulling syntax out of the air it'd be nice if we could avoid
adding new keywords to the grammar.

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] fix worker_spi to run as non-dynamic background worker

[Michael Paquier]

On Fri, May 23, 2014 at 6:29 PM, Shigeru Hanada
 wrote:
> I noticed that contrib/worker_spi can't run as non-dynamic background
> worker (IOW, load via shared_preload_libraries), because of
> uninitialized bgw_notify_pid.
That's actually the case of all the bgworkers that have been developed
with 9.3 and not only worker_spi. If bgw_notify_pid needs to be
specifically initialized with 9.4, this should be mentioned in the
release notes or users will be surprised.
-- 
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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Jeff Ross]

On 5/23/14, 7:21 AM, Bruce Momjian wrote:


On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:

I just tested ALTER TABLE in 8.4 and it does create a toast table for
this case in 9.4:

CREATE TABLE test (x CHAR(10));
ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);


I just tried this on the problem table and it did indeed create a
toast table.

I then retried pg_upgrade and it failed with the same problem on a
different table in the same database.  Of the 67 databases in the
8.4 cluster, 5 (so far) have had this problem on at least one table.


Yeah, it would be nice to be able to report all the problem tables, but
I don't know how to do that except from pg_upgrade failing.  Is there
anything similar about these tables?




Here are the last 2 tables I had a problem with:

UDB=# \d contact_email
   Table "public.contact_email"
 Column |Type | Modifiers
+-+
 id | integer | not null default 
nextval('contact_email_id_seq'::regclass)

 email1 | character varying(255)  | not null
 email2 | character varying(255)  |
 time   | timestamp without time zone | not null default now()
 source | email_source| not null
Indexes:
"contact_email_pkey" PRIMARY KEY, btree (id)
"idx_contact_email_email1" btree (lower(email1::text) 
varchar_pattern_ops)
"idx_contact_email_email2" btree (lower(email2::text) 
varchar_pattern_ops)

Referenced by:
TABLE "abandoned_master_booking" CONSTRAINT 
"abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY 
(contact_email_id) REFERENCES contact_email(id)
TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN 
KEY (contact_email_id) REFERENCES contact_email(id)
TABLE "eticketaccesslog" CONSTRAINT 
"eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id) 
REFERENCES contact_email(id)


UDB=# \d masterairportlist
Table "public.masterairportlist"
 Column  |Type | 
Modifiers

-+-+
 id  | integer | not null default 
nextval('masterairportlist_id_seq'::regclass)

 airport_code| character varying(3)|
 airport_name| character varying(64)   |
 city_name   | character varying(64)   |
 confirm | boolean |
 country_code| character varying(2)|
 country_name| character varying(64)   |
 destrank| integer |
 dstflag | boolean |
 enddst  | timestamp without time zone |
 fr_airport_name | character varying(256)  |
 fr_city_name| character varying(64)   |
 fr_country_code | character varying(2)|
 fr_country_name | character varying(64)   |
 fr_prov_state   | character varying(64)   |
 origrank| integer |
 prov_code   | character varying(100)  |
 prov_name   | character varying(100)  |
 prov_state  | character varying(64)   |
 startdst| timestamp without time zone |
 timeoffset  | character varying(100)  |
 timeref | character varying(100)  |
Indexes:
"masterairportlist_pkey" PRIMARY KEY, btree (id)
"idx_dest_cityname_conf" btree (city_name text_pattern_ops, 
destrank, confirm)
"idx_orig_cityname_conf" btree (city_name text_pattern_ops, 
origrank, confirm)





--
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] Congrats Andres Freund, the newest PostgreSQL Commiter!

[Mike Blackwell]

​Congrats Andres!

Mike​

__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *


On Thu, May 22, 2014 at 9:24 PM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:

> Hi All,
>
> At the Developer Meeting that occurred 21th May 2014 was announced a new
> PostgreSQL commiter [1], Mr. Andres Freund.
>
> I had the opportunity to work and be mentored by him. He deserves very
> much this confidence, for the excellent work that has been doing for the
> community.
>
> Thank you and Congrats Andres!
>
>
> [1]
> https://wiki.postgresql.org/wiki/PgCon_2014_Developer_Meeting#New_Committer
>
> --
> 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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Jeff Ross]

On 5/23/14, 7:36 AM, Andres Freund wrote:



Any chance you could, *before* you create the toast table, do a:
SELECT attrelid::regclass, attname, attnum, attlen, *
FROM pg_attribute
WHERE attrelid = 'a'::regclass
ORDER BY attnum ASC;

Where 'a' is replaced by the affected table?

Greetings,

Andres Freund



Yes, here you go:

postgres@vdev1commandprompt2:~$ psql -p 5433 UDB
psql (9.3.4, server 8.4.21)
Type "help" for help.

UDB=# \x
Expanded display is on.
UDB=# SELECT attrelid::regclass, attname, attnum, attlen, *
FROM pg_attribute
WHERE attrelid = 'masterairportlist'::regclass
ORDER BY attnum ASC;
UDB=#
[ RECORD 1 ]-+--
attrelid  | masterairportlist
attname   | tableoid
attnum| -7
attlen| 4
attrelid  | 18098
attname   | tableoid
atttypid  | 26
attstattarget | 0
attlen| 4
attnum| -7
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 2 ]-+--
attrelid  | masterairportlist
attname   | cmax
attnum| -6
attlen| 4
attrelid  | 18098
attname   | cmax
atttypid  | 29
attstattarget | 0
attlen| 4
attnum| -6
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 3 ]-+--
attrelid  | masterairportlist
attname   | xmax
attnum| -5
attlen| 4
attrelid  | 18098
attname   | xmax
atttypid  | 28
attstattarget | 0
attlen| 4
attnum| -5
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 4 ]-+--
attrelid  | masterairportlist
attname   | cmin
attnum| -4
attlen| 4
attrelid  | 18098
attname   | cmin
atttypid  | 29
attstattarget | 0
attlen| 4
attnum| -4
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 5 ]-+--
attrelid  | masterairportlist
attname   | xmin
attnum| -3
attlen| 4
attrelid  | 18098
attname   | xmin
atttypid  | 28
attstattarget | 0
attlen| 4
attnum| -3
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 6 ]-+--
attrelid  | masterairportlist
attname   | ctid
attnum| -1
attlen| 6
attrelid  | 18098
attname   | ctid
atttypid  | 27
attstattarget | 0
attlen| 6
attnum| -1
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | f
attstorage| p
attalign  | s
attnotnull| t
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 7 ]-+--
attrelid  | masterairportlist
attname   | id
attnum| 1
attlen| 4
attrelid  | 18098
attname   | id
atttypid  | 23
attstattarget | -1
attlen| 4
attnum| 1
attndims  | 0
attcacheoff   | -1
atttypmod | -1
attbyval  | t
attstorage| p
attalign  | i
attnotnull| t
atthasdef | t
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 8 ]-+--
attrelid  | masterairportlist
attname   | airport_code
attnum| 2
attlen| -1
attrelid  | 18098
attname   | airport_code
atttypid  | 1043
attstattarget | -1
attlen| -1
attnum| 2
attndims  | 0
attcacheoff   | -1
atttypmod | 7
attbyval  | f
attstorage| x
attalign  | i
attnotnull| f
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 9 ]-+--
attrelid  | masterairportlist
attname   | airport_name
attnum| 3
attlen| -1
attrelid  | 18098
attname   | airport_name
atttypid  | 1043
attstattarget | -1
attlen| -1
attnum| 3
attndims  | 0
attcacheoff   | -1
atttypmod | 68
attbyval  | f
attstorage| x
attalign  | i
attnotnull| f
atthasdef | f
attisdropped  | f
attislocal| t
attinhcount   | 0
attacl|
-[ RECORD 10 ]+--
attrelid  | masterairportlist
attname   | city_name
attnum| 4
attlen| -1
attrelid  | 18098
attname   | city_name
atttypid  | 1043
attstattarget

Re: [HACKERS] postgres_fdw and connection management

[Fabrízio de Royes Mello]

On Fri, May 23, 2014 at 8:09 AM, Sandro Santilli  wrote:
>
> I'm evaluating use of the postgres FDW to keep in sync a central database
> to changes made in thousand other databases, via triggers.
>
> But as long as postgres_fdw keeps connections open for the whole lifetime
> of a session this conflicts with large use of poolers which make sessions
> virtually never expire.
>
> Is there any way to ask the postgres_fdw to close connections at the
> end of a transaction ? Or would it be possible at all to do from a
> FDW handler ? Do you see any drawback in doing that ?
>
> I'm willing to work on a patch, maybe accepting an additional OPTION,
> if you agree on the idea.
>

Maybe this is a case for the idea pointed here [1]. One way to register a
cleanup code to extensions, then we can run a command called 'DISCARD
EXTENSIONS' and/or 'DISCARD ALL' to do this job.

Unfortunately nobody comment my suggestion yet.

Regards,

[1]
http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=przuhv4-m1+j5eth6...@mail.gmail.com

--
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] wrapping in extended mode doesn't work well with default pager

[Alvaro Herrera]

Sergey Muraviov wrote:
> I found some new bugs and fix them.
> And I had to make many changes.

This version fixes some bugs I had noticed in expanded mode too.  For instance,
the original looked like this (five lines plus header):

-[ RECORD 49 
]-+-
 pg_identify_object | (rule,,,"""_RETURN"" on 
pg_catalog.pg_available_extension_versions") 

 pg_identify_object | 
(view,pg_catalog,pg_available_extension_versions,pg_catalog.pg_availabl
e.
|._extension_versions)  
   


whereas it's correctly only three lines plus header with this patch
applied.  I can't tell whether this patch is minimal enough.

Having a more comprehensive test case is good, of course, though I
didn't check the expected file.  Note that some things cannot be tested
correctly in this way, namely column count that actually match the
terminal -- to wit: I had to add line breaks manually to the above paste
so that it would look like what it does in my terminal.  If I just paste
it, it looks correct, but then my email terminal is wider than the one I
ran psql in.  I would presume that failure to account for this is what
caused (some of?) the bugs being fixed now ...

-- 
Á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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Bruce Momjian]

On Thu, May 22, 2014 at 09:55:10AM -0400, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Moving forward, I think you need to add a dummy column to each problem
> > table and drop the column  that will create a toast table and allow
> > you to do the upgrade.  I could have pg_upgrade detect this problem, but
> > until I know the cause, I don't think that is wise.
> 
> Maybe --check mode could examine both clusters and see whether each
> table having toast table or not matches.  That wouldn't solve the actual
> problem but at least give a clue, instead of these very obscure
> problems.

There is no way to check for an old/new toast mismatch except creating
the tables on the new cluster, and check mode can't do that due to time
and because it would modify the new cluster and make it non-upgradeable.

-- 
  Bruce Momjian  http://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] -DDISABLE_ENABLE_ASSERT

[Tom Lane]

Andres Freund  writes:
> The next question is whether to wait till after the branching with this?

+1 for waiting (it's only a couple weeks anyway).  This isn't a
user-facing feature in any way, so I feel no urgency to ship it in 9.4.

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] -DDISABLE_ENABLE_ASSERT

[Andres Freund]

On 2014-05-23 09:56:03 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > On Fri, May 23, 2014 at 8:15 AM, Andres Freund  
> > wrote:
> >> That means you're for a (differently named) disable macro? Or is it not
> >> recent enough that you don't care?
> 
> > I'm leaning toward thinking we should just rip it out.  The fact that
> > 3 out of the 4 people commenting on this thread have used it at some
> > point provides some evidence that it has more than no value - but on
> > the other hand, there's a cost to keeping it around.
> 
> Yeah.  For the record, I've used it too (don't recall what for exactly).
> But I don't think it's worth adding yet another layer of complication for.

Cool. Seems like we have an agreement then.

The next question is whether to wait till after the branching with 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] -DDISABLE_ENABLE_ASSERT

[Tom Lane]

Robert Haas  writes:
> On Fri, May 23, 2014 at 8:15 AM, Andres Freund  wrote:
>> That means you're for a (differently named) disable macro? Or is it not
>> recent enough that you don't care?

> I'm leaning toward thinking we should just rip it out.  The fact that
> 3 out of the 4 people commenting on this thread have used it at some
> point provides some evidence that it has more than no value - but on
> the other hand, there's a cost to keeping it around.

Yeah.  For the record, I've used it too (don't recall what for exactly).
But I don't think it's worth adding yet another layer of complication for.

The main argument for it given in this thread is recompile cost ...
but TBH, I have one word for anybody who's worried about that, and
that word is "ccache".  If you don't have that tool installed, you're
missing out on a huge timesaver.

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] Re: popen and pclose redefinitions causing many warning in Windows build

[Alvaro Herrera]

Heikki Linnakangas wrote:
> On 05/15/2014 04:15 PM, Michael Paquier wrote:
> >On Thu, May 15, 2014 at 6:20 PM, Heikki Linnakangas
> > wrote:
> >>Ok, I committed #undefs. I don't have a Mingw(-w64) environment to test
> >>with, so let's see if the buildfarm likes it.
> >There does not seem to be a buildfarm machine using MinGW-w64...
> 
> Jacana. It has "gcc 4.8.1" listed as the compiler, but if you look
> at the config in detail, it's mingw-w64. The popen/pclose warnings
> are there. It hasn't performed a build after I committed the fix
> yet.

There are no warnings about popen in Jacana currently.  These are the
warnings that remain:

x86_64-w64-mingw32-gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
-I../../../../src/include 
-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include 
-I../pgsql/src/include/port/win32 -DEXEC_BACKEND -I/c/prog/3p64/include/libxml2 
 -I/c/prog/3p64/include -I/c/prog/3p64/openssl/include 
"-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include/port/win32" 
-DBUILDING_DLL  -c -o mingwcompat.o 
/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/backend/port/win32/mingwcompat.c
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/backend/port/win32/mingwcompat.c:60:1:
 warning: 'RegisterWaitForSingleObject' redeclared without dllimport attribute: 
previous dllimport ignored [-Wattributes]
 RegisterWaitForSingleObject(PHANDLE phNewWaitObject,
 ^

x86_64-w64-mingw32-gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/interfaces/libpq 
-I../../../src/include 
-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include 
-I../pgsql/src/include/port/win32 -DEXEC_BACKEND -I/c/prog/3p64/include/libxml2 
 -I/c/prog/3p64/include -I/c/prog/3p64/openssl/include 
"-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include/port/win32"  -c 
-o parallel.o 
/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/bin/pg_dump/parallel.c
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/bin/pg_dump/parallel.c:
 In function 'pgpipe':
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/bin/pg_dump/parallel.c:1332:2:
 warning: overflow in implicit constant conversion [-Woverflow]
  handles[0] = handles[1] = INVALID_SOCKET;
  ^
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/bin/pg_dump/parallel.c:1386:3:
 warning: overflow in implicit constant conversion [-Woverflow]
   handles[1] = INVALID_SOCKET;
   ^

x86_64-w64-mingw32-gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g  
-I. 
-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/contrib/pg_stat_statements 
-I../../src/include 
-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include 
-I../pgsql/src/include/port/win32 -DEXEC_BACKEND -I/c/prog/3p64/include/libxml2 
 -I/c/prog/3p64/include -I/c/prog/3p64/openssl/include 
"-I/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/src/include/port/win32"  -c 
-o pg_stat_statements.o 
/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/contrib/pg_stat_statements/pg_stat_statements.c
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/contrib/pg_stat_statements/pg_stat_statements.c:
 In function 'pgss_ProcessUtility':
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/contrib/pg_stat_statements/pg_stat_statements.c:998:4:
 warning: unknown conversion type character 'l' in format [-Wformat=]
sscanf(completionTag, "COPY " UINT64_FORMAT, &rows) != 1)
^
c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.5100/../pgsql/contrib/pg_stat_statements/pg_stat_statements.c:998:4:
 warning: too many arguments for format [-Wformat-extra-args]



-- 
Á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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Andres Freund]

On 2014-05-22 09:20:38 -0600, Jeff Ross wrote:
> On 5/21/14, 2:37 PM, Bruce Momjian wrote:
> >The only item I can think of that would cause this is someone changing
> >the length of a string.  Did someone modify pg_attribute directly to
> >increase the length of one of the character columns?

> I don't know, sorry.

> >I just tested ALTER TABLE in 8.4 and it does create a toast table for
> >this case in 9.4:
> >
> > CREATE TABLE test (x CHAR(10));
> > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> >
> I just tried this on the problem table and it did indeed create a toast
> table.
> 
> I then retried pg_upgrade and it failed with the same problem on a different
> table in the same database.  Of the 67 databases in the 8.4 cluster, 5 (so
> far) have had this problem on at least one table.

Any chance you could, *before* you create the toast table, do a:
SELECT attrelid::regclass, attname, attnum, attlen, *
FROM pg_attribute
WHERE attrelid = 'a'::regclass
ORDER BY attnum ASC;

Where 'a' is replaced by the affected table?

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

[HACKERS] Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[David G Johnston]

Bruce Momjian wrote
> On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:
>> >I just tested ALTER TABLE in 8.4 and it does create a toast table for
>> >this case in 9.4:
>> >
>> >CREATE TABLE test (x CHAR(10));
>> >ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
>> >
>> I just tried this on the problem table and it did indeed create a
>> toast table.
>> 
>> I then retried pg_upgrade and it failed with the same problem on a
>> different table in the same database.  Of the 67 databases in the
>> 8.4 cluster, 5 (so far) have had this problem on at least one table.
> 
> Yeah, it would be nice to be able to report all the problem tables, but
> I don't know how to do that except from pg_upgrade failing.  Is there
> anything similar about these tables?

Would a toast table in this situation have to be empty on the 8.4 database? 
Is there some kind of stat table query that would identify all such toast
tables?  Although it is possible some of those tables do indeed need a toast
table but never make use of it (especially if one makes judicious use of
unlimited text columns but never fills them with large amounts of data -
like for lookup tables).

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-upgrade-fails-Mismatch-of-relation-OID-in-database-8-4-9-3-tp5804593p5804793.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

[Bruce Momjian]

On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:
> >I just tested ALTER TABLE in 8.4 and it does create a toast table for
> >this case in 9.4:
> >
> > CREATE TABLE test (x CHAR(10));
> > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> >
> I just tried this on the problem table and it did indeed create a
> toast table.
> 
> I then retried pg_upgrade and it failed with the same problem on a
> different table in the same database.  Of the 67 databases in the
> 8.4 cluster, 5 (so far) have had this problem on at least one table.

Yeah, it would be nice to be able to report all the problem tables, but
I don't know how to do that except from pg_upgrade failing.  Is there
anything similar about these tables?

-- 
  Bruce Momjian  http://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] btree_gist macaddr valgrind woes

[Alvaro Herrera]

Heikki Linnakangas wrote:
> On 05/18/2014 12:23 AM, Tom Lane wrote:
> >A larger issue is that we evidently have no buildfarm animals that are
> >picky about alignment, or at least none that are running a modern-enough
> >buildfarm script to be running the contrib/logical_decoding test.
> >That seems like a significant gap.  I don't want to volunteer to run
> >a critter on my HPPA box: it's old enough, and eats enough electricity,
> >that I no longer want to leave it on 24x7.  Plus a lot of the time its
> >response to a bus error is to lock up in a tight loop rather than report
> >an error, so a failure wouldn't get reported usefully by the buildfarm
> >anyway.  Does anyone have an ARM or PPC box where they can configure
> >the kernel not to mask misaligned fetches?
> 
> I did "echo 4 > /proc/cpu/alignment" on chipmunk - let's see what it
> crops up.
> 
> In quick testing with a little test program, it looks like an
> unaligned access to a 32-bit int still works without error. But an
> unaligned access to a 64-bit "long long" causes a SIGBUS now.

There seems to have been no failure here, FWIW.

-- 
Á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] -DDISABLE_ENABLE_ASSERT

[Robert Haas]

On Fri, May 23, 2014 at 8:15 AM, Andres Freund  wrote:
>> >> I've used it once or twice to avoid having to recompile postgres when I
>> >> wanted things not to be *that* slow (AtEOXactBuffers() I am looking at
>> >> you). But I wouldn't be very sad if it'd go.
>> >>
>> >> Anybody against that?
>> >
>> > I have used it too (for a different reason IIRC), but like you I
>> > wouldn't have a problem if it weren't there.
>>
>> I've used it, too, although not recently.
>
> That means you're for a (differently named) disable macro? Or is it not
> recent enough that you don't care?

I'm leaning toward thinking we should just rip it out.  The fact that
3 out of the 4 people commenting on this thread have used it at some
point provides some evidence that it has more than no value - but on
the other hand, there's a cost to keeping it around.  The need to
guard some sections of code by both #ifdef USE_ASSERT_CHECKING and if
(assert_enabled) has occasionally been a source of confusion over the
years, and once I had a customer who I was afraid had gotten an
assert-enabled build into production and the only way to distinguish
between an assert-enabled build with assertions shut off via the GUC
and a build that didn't support them in the first place was to try
turning the GUC on and see whether it worked, which led to some
confusion.  Now it looks like we need to add another macro to make
this dance even more complicated ... and I'm starting to think it's
just not worth 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] HEAD crashes with assertion and LWLOCK_STATS enabled

[Robert Haas]

On Tue, May 20, 2014 at 4:02 AM, Yuto HAYAMIZU  wrote:
> The failing assertion is for prohibiting memory allocation in a critical 
> section, which is introduced by commit 4a170ee9 on 2014-04-04.
>
> In my understanding, the root cause of the assertion failure is on-demand 
> allocation of lwlock_stats entry.  For each LWLock, a lwlock_stats entry is 
> created at the first invocation of LWLockAcquire using MemoryContextAlloc.  
> If the first invocation is in a critical section, the assertion fails.
>
> For 'initdb' case I mentioned above, WALWriteLock locking in XLogFlush 
> function was the problem.
> I also confirmed the assertion failure on starting postgres on a correctly 
> initialized database. In this case, locking CheckpointerCommLock in 
> AbsorbFsyncRequests function was the problem.
>
> ## A solution
>
> In order to avoid memory allocation during critical sections, lwlock_stats 
> hash table should be populated at the initialization of each process.
> The attached patch populate lwlock_stats entries of MainLWLockArray at the 
> end of CreateLWLocks, InitProcess and InitAuxiliaryProcess.
>
> With this patch, all regression tests can be passed so far, but I think this 
> patch is not perfect because it does not cover LWLocks outside of 
> MainLWLockArray.  I'm not sure where is the right place to initialize 
> lwlock_stats entries for that locks.  So I feel it needs some refinements by 
> you hackers.

Prior to my commit ea9df812d8502fff74e7bc37d61bdc7d66d77a7f, which
introduced LWLockTranche, we used to allocate enough storage for all
of the LWLOCK_STATS entries in the system; that commit changed things
so that we allocate entries for particular LWLocks on an as-needed
basis.  Although that wasn't the main point of that patch, I thought
it was a nice idea, since it might save you quite a bit of memory if
you have a lot of backends that don't touch very many LWLocks.  But
maybe we need to give up on that in view of this report.

I don't think we should adopt the approach proposed in this patch,
though, because if we're going to preallocate all of the entries
anyway there's little reason to use a hash table instead of an array.
If we're going to go with the approach of preallocating all the
entries, maybe we should change the definition of LWLockTranche to
include the number of lwlocks in the tranche.  We could then add
another array parallel to LWLockTrancheArray which would point to an
appropriately-sized array of lwlock_stats objects for each tranche.

-- 
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] -DDISABLE_ENABLE_ASSERT

[Andres Freund]

On 2014-05-23 07:20:12 -0400, Robert Haas wrote:
> On Thu, May 22, 2014 at 5:00 PM, Alvaro Herrera
>  wrote:
> > Andres Freund wrote:
> >> On 2014-05-22 16:37:35 -0400, Tom Lane wrote:
> >> > We could do that ... but I wonder if we shouldn't remove assert_enabled
> >> > altogether.  What's the use case for turning it off?  Not matching the
> >> > speed of a non-cassert build, because for instance 
> >> > MEMORY_CONTEXT_CHECKING
> >> > doesn't get turned off.
> >>
> >> I've used it once or twice to avoid having to recompile postgres when I
> >> wanted things not to be *that* slow (AtEOXactBuffers() I am looking at
> >> you). But I wouldn't be very sad if it'd go.
> >>
> >> Anybody against that?
> >
> > I have used it too (for a different reason IIRC), but like you I
> > wouldn't have a problem if it weren't there.
> 
> I've used it, too, although not recently.

That means you're for a (differently named) disable macro? Or is it not
recent enough that you don't care?

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] Allowing join removals for more join types

[David Rowley]

On Fri, May 23, 2014 at 8:28 PM, Dilip kumar  wrote:

>  On 23 May 2014 12:43 David Rowley Wrote,
>
>
>
> >I'm hitting a bit of a roadblock on point 1. Here's a snipped from my
> latest attempt:
>
>
>
> >  if (bms_membership(innerrel->relids) ==
> BMS_SINGLETON)
>
> >  {
>
> >  int subqueryrelid =
> bms_singleton_member(innerrel->relids);
>
> >  RelOptInfo *subqueryrel =
> find_base_rel(innerrel->subroot, subqueryrelid);
>
> >
>
> >  if (relation_has_unique_index_for(root,
> subqueryrel, clause_list, NIL, NIL))
>
> >  return true;
>
> >  }
>
>
>
> >But it seems that innerrel->subroot is still NULL at this stage of
> planning and from what I can tell does not exist anywhere else yet and is
> not generated until make_one_rel() is called from query_planner()
>
>
>
> >Am I missing something major here,or does this sound about right?
>
>
>
> It’s true that, till this point of time we haven’t prepared the base
> relation list for the subquery, and that will be done from make_one_rel
> while generating the SUBQURY path list.
>
>
>
> I can think of one solution but I think it will be messy…
>
>
>
> We get the base relation info directly from subquery
>
> Like currently in your patch (shown in below snippet) we are getting the
> distinct and groupby clause from sub Query,  similarly we can get base
> relation info from  (Query->jointree)
>
>
>
> if (innerrel->rtekind == RTE_SUBQUERY)
>
> {
>
> Query *query =
> root->simple_rte_array[innerrelid]->subquery;
>
>
>
> if (sortclause_is_unique_on_restrictinfo(query,
> clause_list, query->groupClause) ||
>
>
> sortclause_is_unique_on_restrictinfo(query, clause_list,
> query->distinctClause))
>
> return true;
>
> }
>
>
I'm getting the idea that this is just not the right place in planning to
do this for subqueries.
You seem to be right about the messy part too

Here's a copy and paste of the kludge I've ended up with while testing this
out:

if (list_length(subquery->jointree->fromlist) == 1)
{
RangeTblEntry *base_rte;
RelOptInfo *subqueryrelid;
RangeTblRef *rtr = (RangeTblRef *) linitial(subquery->jointree->fromlist);
if (!IsA(rtr, RangeTblRef))
return false;

base_rte = rt_fetch(rtr->rtindex, subquery->rtable);
if (base_rte->relkind != RTE_RELATION)
return false;

subqueryrelid = build_simple_rel(, rtr->rtindex,
RELOPT_BASEREL);

I don't have a PlannerInfo to pass to build_simple_rel and it just seems
like a horrid hack to create one that we're not going to be keeping.
Plus It would be a real shame to have to call build_simple_rel() for the
same relation again when we plan the subquery later.

I'm getting the idea that looking for unique indexes on the sub query is
not worth the hassle for now. Don't get me wrong, they'd be nice to have,
but I just think that it's a less common use case and these are more likely
to have been pulled up anyway.

Unless there's a better way, I think I'm going to spend the time looking
into inner joins instead.

Regards

David Rowley


>
>
>
> Regards,
>
> Dilip
>

Re: [HACKERS] SKIP LOCKED DATA (work in progress)

[Robert Haas]

On Sat, May 17, 2014 at 1:02 AM, Craig Ringer  wrote:

> We have a long tradition of trying to allow noise keywords where it's
> harmless.
>
> So the clause should probably be
>
>  SKIP LOCKED [DATA]
>
> in much the same way we have
>
> BEGIN [ WORK | TRANSACTION ] ...
>
> There won't be any ambiguity there.

We've had some problems in the past where allowing optional noise
words resulted in grammar conflicts that made future features harder
to add.  See previous discussions about LOCK TABLE, wherein we almost
went to the extreme of adding a completely separate ACQUIRE LOCK
command.  A lot of these things seem harmless when you first do them,
and then later they seem less harmless.

Anyway, +1 for the general idea of this feature.  It's come up a
number of times on this mailing list, and we've had customer requests
for it, too.

-- 
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] -DDISABLE_ENABLE_ASSERT

[Robert Haas]

On Thu, May 22, 2014 at 5:00 PM, Alvaro Herrera
 wrote:
> Andres Freund wrote:
>> On 2014-05-22 16:37:35 -0400, Tom Lane wrote:
>> > We could do that ... but I wonder if we shouldn't remove assert_enabled
>> > altogether.  What's the use case for turning it off?  Not matching the
>> > speed of a non-cassert build, because for instance MEMORY_CONTEXT_CHECKING
>> > doesn't get turned off.
>>
>> I've used it once or twice to avoid having to recompile postgres when I
>> wanted things not to be *that* slow (AtEOXactBuffers() I am looking at
>> you). But I wouldn't be very sad if it'd go.
>>
>> Anybody against that?
>
> I have used it too (for a different reason IIRC), but like you I
> wouldn't have a problem if it weren't there.

I've used it, too, although not recently.

-- 
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

[HACKERS] postgres_fdw and connection management

[Sandro Santilli]

I'm evaluating use of the postgres FDW to keep in sync a central database
to changes made in thousand other databases, via triggers.

But as long as postgres_fdw keeps connections open for the whole lifetime
of a session this conflicts with large use of poolers which make sessions
virtually never expire.

Is there any way to ask the postgres_fdw to close connections at the
end of a transaction ? Or would it be possible at all to do from a 
FDW handler ? Do you see any drawback in doing that ?

I'm willing to work on a patch, maybe accepting an additional OPTION,
if you agree on the idea.

--strk;

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt  


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

[HACKERS] fix worker_spi to run as non-dynamic background worker

[Shigeru Hanada]

Hi all,

I noticed that contrib/worker_spi can't run as non-dynamic background
worker (IOW, load via shared_preload_libraries), because of
uninitialized bgw_notify_pid.

I got log lines below when starting PostgreSQL with
shared_preload_libraries = 'worker_spi'.

$ pg_ctl start -w
waiting for server to startLOG:  registering background worker "worker 1"
LOG:  background worker "worker 1": only dynamic background workers
can request notification
LOG:  registering background worker "worker 2"
LOG:  background worker "worker 2": only dynamic background workers
can request notification
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".

Attached patch fixes this issue.  Please apply onto HEAD and 9.4.

-- 
Shigeru HANADA


fix_worker_spi.patch
Description: Binary data

-- 
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] Allowing join removals for more join types

[Dilip kumar]

On 23 May 2014 12:43 David Rowley Wrote,

>I'm hitting a bit of a roadblock on point 1. Here's a snipped from my latest 
>attempt:

>  if (bms_membership(innerrel->relids) == BMS_SINGLETON)
>  {
>  int subqueryrelid = 
> bms_singleton_member(innerrel->relids);
>  RelOptInfo *subqueryrel = 
> find_base_rel(innerrel->subroot, subqueryrelid);
>
>  if (relation_has_unique_index_for(root, 
> subqueryrel, clause_list, NIL, NIL))
>  return true;
>  }

>But it seems that innerrel->subroot is still NULL at this stage of planning 
>and from what I can tell does not exist anywhere else yet and is not generated 
>until make_one_rel() is called from query_planner()

>Am I missing something major here,or does this sound about right?

It’s true that, till this point of time we haven’t prepared the base relation 
list for the subquery, and that will be done from make_one_rel while generating 
the SUBQURY path list.

I can think of one solution but I think it will be messy…

We get the base relation info directly from subquery
Like currently in your patch (shown in below snippet) we are getting the 
distinct and groupby clause from sub Query,  similarly we can get base relation 
info from  (Query->jointree)

if (innerrel->rtekind == RTE_SUBQUERY)
{
Query *query = 
root->simple_rte_array[innerrelid]->subquery;

if (sortclause_is_unique_on_restrictinfo(query, 
clause_list, query->groupClause) ||
sortclause_is_unique_on_restrictinfo(query, 
clause_list, query->distinctClause))
return true;
}


Regards,
Dilip

Re: [HACKERS] uuid-ossp (Re: [pgsql-packagers] Postgresapp 9.4 beta build ready)

[Matteo Beccati]

On 22/05/2014 21:55, Matteo Beccati wrote:
> On 22/05/2014 17:07, Tom Lane wrote:
>> Well, *I* don't want to do that work.  I was hoping to find a volunteer,
>> but the silence has been notable.  I think deprecation is the next step.
> 
> This sounds an easy enough task to try and submit a patch, if I'm able
> to allocate enough time to work on it.
> 
> I have successfully compiled the extension on a NetBSD box using a
> slightly modified version of Palle's patch. I have a few doubts though:
> 
> - should we keep the extension name? If not, what would be the plan?
> - the patch also uses BSD's own md5 and sha1 implementations: for md5 I
> should be able to use pg's own core version, but I'm not sure about
> sha1, as it lives in pgcrypto. Any suggestion?

Maybe I've put the cart before the horse a little bit ;)

Anyway, BSD and Linux UUID implementations are slightly different, but I
was able to get two variants of the extension to compile on NetBSD and
Ubuntu. I don't have the necessary autoconf-fu to "merge" them together
though, and to make sure that they compile on the various bsd/linux
flavours.

You can find the code here:
https://github.com/mbeccati/uuid # NetBSD variant
https://github.com/mbeccati/uuid/tree/linux # Ubuntu variant

For now, I've forked just RhodiumToad's uuid-freebsd extension, but I've
made sure make works fine when cloned in the contrib folder.

* Both the variants use a copy of pgcrypto md5/sha1 implementations to
generate v3 and v5 UUIDs as porting is much easier than trying to use
the system provided ones, if any.
* I've fixed a bug in v3/v5 generation wrt endianness as the results I
was getting didn't match the RFC.
* The code is PoC quality and I haven't touched the docs/readme yet.


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.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] Allowing join removals for more join types

[David Rowley]

On Mon, May 19, 2014 at 5:47 PM, Dilip kumar  wrote:

>
>
>  So I think now when you are considering this join removal for subqueries
> then this can consider other case also like unique index inside subquery,
>
> because in attached patch  unique index is considered only if its
> RTE_RELATION
>
>
>
> +  if (innerrel->rtekind == RTE_RELATION &&
>
> +  relation_has_unique_index_for(root, innerrel,
> clause_list, NIL, NIL))
>
>return true;
>
>
>
>
>

I've just had a bit of a look at implementing checks allowing subqueries
with unique indexes on the join cols being removed, but I'm hitting a bit
of a problem and I'm not quite sure if this is possible at this stage of
planning.

In the function join_is_removable() the variable innerrel is set to the
RelOptInfo of the relation which we're checking if we can remove. In the
case of removing subqueries the innerrel->rtekind will be RTE_SUBQUERY. I
started going over the pre-conditions that the sub query will need to meet
for this to be possible and the list so far looks something like:

1. Only a single base table referenced in the sub query.
2. No FOR UPDATE clause
3. No GROUP BY or DISTINCT clause
4. No set returning functions
5. no volatile functions.
6. has unique index that covers the join conditions or a subset of.

I'm hitting a bit of a roadblock on point 1. Here's a snipped from my
latest attempt:

if (bms_membership(innerrel->relids) == BMS_SINGLETON)
{
int subqueryrelid = bms_singleton_member(innerrel->relids);
RelOptInfo *subqueryrel = find_base_rel(innerrel->subroot, subqueryrelid);
 if (relation_has_unique_index_for(root, subqueryrel, clause_list, NIL,
NIL))
return true;
}

But it seems that innerrel->subroot is still NULL at this stage of planning
and from what I can tell does not exist anywhere else yet and is not
generated until make_one_rel() is called from query_planner()

Am I missing something major here,or does this sound about right?

Regards

David Rowley