Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Dave Page
On Fri, Mar 5, 2010 at 4:02 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:
 Kevin Flanagan wrote:

 the compiler
 complained about various missing include files, starting with
 ‘libintl.h’. Having read the post at
 http://archives.postgresql.org/pgsql-general/2009-03/msg00332.php I
 created an empty libint.h in an include dir

 NFI why Pg for win32 doesn't bundle a copy of the libintl it was built
 against. I should poke the EDB guys about it, actually.

We do include the library. We don't include the headers or source for
third party code though - that would be considered part of the build
environment, just the same as the Windows SDK.



-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Craig Ringer
Dave Page wrote:
 On Fri, Mar 5, 2010 at 4:02 AM, Craig Ringer
 cr...@postnewspapers.com.au wrote:
 Kevin Flanagan wrote:

 the compiler
 complained about various missing include files, starting with
 ‘libintl.h’. Having read the post at
 http://archives.postgresql.org/pgsql-general/2009-03/msg00332.php I
 created an empty libint.h in an include dir
 NFI why Pg for win32 doesn't bundle a copy of the libintl it was built
 against. I should poke the EDB guys about it, actually.
 
 We do include the library. We don't include the headers or source for
 third party code though - that would be considered part of the build
 environment, just the same as the Windows SDK.

The installer includes the headers for PostgreSQL its self (ie a
postgresql sdk). For them to be useful for much you need libintl too.
As on windows there's no system libintl, the user has to try to figure
out what libintl Pg was built against and somehow obtain appropriate
headers, including any config headers. There are many different win32
builds of libintl out there and many (most) of them won't match what Pg
was built against.

That seems unnecessarily painful. It seems pretty harmless to ship the
gettext headers, as a separate download if not in the main installer
package.

How do _you_ go about building server extensions for Pg? Where do you
get the headers for gettext etc?


IMO if the gettext headers aren't in the main installer pkg then the Pg
headers shouldn't be either, and a separate sdk installer should be
provided with them both. Ditto any other headers required.

I'm increasingly thinking the win32 package _should_ be split into
server binary and separate headers+pdb+sources packages, with the sdk
package including gettext headers and sources too. It'd be a LOT easier
to develop with Pg on win32 this way.

--
Craig Ringer

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Dave Page
On Fri, Mar 5, 2010 at 9:05 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:

 How do _you_ go about building server extensions for Pg? Where do you
 get the headers for gettext etc?

Same place I get the binaries - gnuwin32 mostly.

 I'm increasingly thinking the win32 package _should_ be split into
 server binary and separate headers+pdb+sources packages, with the sdk
 package including gettext headers and sources too. It'd be a LOT easier
 to develop with Pg on win32 this way.

How does breaking it up into multiple packages make it easier?


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Craig Ringer
Dave Page wrote:
 On Fri, Mar 5, 2010 at 9:05 AM, Craig Ringer
 cr...@postnewspapers.com.au wrote:
 
 How do _you_ go about building server extensions for Pg? Where do you
 get the headers for gettext etc?
 
 Same place I get the binaries - gnuwin32 mostly.
 
 I'm increasingly thinking the win32 package _should_ be split into
 server binary and separate headers+pdb+sources packages, with the sdk
 package including gettext headers and sources too. It'd be a LOT easier
 to develop with Pg on win32 this way.
 
 How does breaking it up into multiple packages make it easier?

What I was trying to say was if you don't want to include gettext in
the main download, perhaps splitting all the dev files into a separate
package would permit you to add gettext and the rest.

I don't much like the fact that presently users have to go hunting for
the libraries, with not even a pointer included in the sources about
where they should look to find headers matching the shipped libraries,
and what version they need.

Why _not_ distribute gettext headers, though? Sources I can understand
for size reasons, but the headers are small and fuss free, and you need
the _right_ _versions_ to build against the Pg backend.

--
Craig Ringer

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Dave Page
On Fri, Mar 5, 2010 at 9:31 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:

 Why _not_ distribute gettext headers, though? Sources I can understand
 for size reasons, but the headers are small and fuss free, and you need
 the _right_ _versions_ to build against the Pg backend.

No reason, other than I didn't realise they were needed to build extension.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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] Explicit psqlrc

2010-03-05 Thread Magnus Hagander
2010/3/5 David Christensen da...@endpoint.com:

 On Mar 4, 2010, at 4:00 PM, Magnus Hagander wrote:

 I've now for the second time found myself wanting to specify an
 explicit psqlrc file instead of just parsing ~/.psqlrc, so attached is
 a patch that accepts the PSQLRC environment variable to control which
 psqlrc file is used.

 Any objections to this (obviously including documentation - this is
 just the trivial code)


 My bikeshed has a --psqlrc path/to/file, but +1 on the idea.

The main reason I went with environment variable is that it's the
path-of-least-code :-) And it easily fullfills my use-cases for it,
which has me launching interactive psql with completely different
settings from a script.

Do you have a use-case where --psqlrc would be more useful than an
environment variable, or is it *only* bike-shedding? ;)

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

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


[HACKERS] Using GIN/Gist to search the union of two indexes?

2010-03-05 Thread Jesper Krogh
Hi.

How complicated would it be to make postgresql-fts search the union of
several GIN/Gist indexes.

The use-case is that you have two tables:

tablea(id serial, tableb_id int, text tsvector);
and
tableb(id serial, text tsvector);
and indices on both tsvectors.

The typical query would join the two tables on the key:

select id from tablea,tableb where tablea.tableb_id = tableb.id;

And then filter the results on the fts-indexes:

select id from tablea,tableb where tablea.tableb_id = tableb.id and
tablea.text @@ to_tsquery('ftsquery') or tableb.text @@
to_tsquery('ftsquery');

This one is doable .. using some mocking of queries in the
application. But if ftsquery is:

terma  termb where terma only exists in tablea and termb only exists
in tableb, then it doesn't work. The path would seem to be to not use
the indexes.

I guess it would be something like a new ts_match_vq() that can take
more than one vector and get the underlying logik to do the union at
search time?

Can someone with more insigth into the code tell me if it is persieved a
hard task to do?

Thanks.
Jesper
-- 
Jesper

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Craig Ringer
Dave Page wrote:
 On Fri, Mar 5, 2010 at 9:31 AM, Craig Ringer
 cr...@postnewspapers.com.au wrote:
 
 Why _not_ distribute gettext headers, though? Sources I can understand
 for size reasons, but the headers are small and fuss free, and you need
 the _right_ _versions_ to build against the Pg backend.
 
 No reason, other than I didn't realise they were needed to build extension.
 

Ah, fair enough. I read:

 We do include the library. We don't include the headers or source for
 third party code though - that would be considered part of the build
 environment, just the same as the Windows SDK.

as we don't want to distribute third-party headers even if required by
Pg's own headers and thus thought you *did* know but by policy didn't
want to distribute them.

--
Craig Ringer

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Kevin Flanagan
Ok, re building with the win32 configuration ... that sounds like just the 
thing I should know about. All I've done is downloaded and installed the 
1-click installer for Windows from 
http://www.enterprisedb.com/products/pgdownload.do#windows ... so while I'm 
sure it knows it's running on Win32, is there some other configuration change I 
should make for dev purposes to indicate that it's the win32 configuration? 
Or does building with the win32 configuration refer to those who are building 
the server from source, or something?

Thanks

Kevin

-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au] 
Sent: 05 March 2010 04:02
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

Kevin Flanagan wrote:

 the compiler
 complained about various missing include files, starting with
 ‘libintl.h’. Having read the post at
 http://archives.postgresql.org/pgsql-general/2009-03/msg00332.php I
 created an empty libint.h in an include dir

NFI why Pg for win32 doesn't bundle a copy of the libintl it was built
against. I should poke the EDB guys about it, actually.

 along with a bunch of other
 empty dummy files that were expected: netdb.h, pwd.h, netinet/in.h and
 arpa/inet.h.

Those I wouldn't expect to be included if you're building for win32.

Are you sure you're building with the win32 configuration?

 The code then compiles ok, but gives ‘inconsistent dll linkage’ on the
 line with PG_FUNCTION_INFO_V1 and the one with PG_MODULE_MAGIC.

This would suggest that the macros that insert appropriate
__declspec(dllimport) and __declspec(dllexport) attributes aren't being
triggered - so again, it makes me wonder if Pg knows it's building for
win32.

--
Craig Ringer


-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Craig Ringer
Kevin Flanagan wrote:
 Ok, re building with the win32 configuration ... that sounds like just the 
 thing I should know about. All I've done is downloaded and installed the 
 1-click installer for Windows from 
 http://www.enterprisedb.com/products/pgdownload.do#windows ... so while I'm 
 sure it knows it's running on Win32, is there some other configuration change 
 I should make for dev purposes to indicate that it's the win32 
 configuration? Or does building with the win32 configuration refer to 
 those who are building the server from source, or something?

I wasn't too specific because it's been a while since I did any coding
against Pg on win32, and I couldn't remember exactly how it selected the
right code to use for a given platform - whether it was a macro that
must be defined, or what.

Having had a look at the sources: It's done by header search path. You
need to make sure that include/port/win32_msvc is on the header search
path as well as the main include/ directory.

I *think* port/win32 is for the MinGW win32 port and thus shouldn't be
included in the search path for msvc builds, but I'm not 100% sure of
that and a quick look doesn't reveal any documentation on the matter.

--
Craig Ringer

-- 
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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Dave Page
On Fri, Mar 5, 2010 at 9:50 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:
 Dave Page wrote:
 On Fri, Mar 5, 2010 at 9:31 AM, Craig Ringer
 cr...@postnewspapers.com.au wrote:

 Why _not_ distribute gettext headers, though? Sources I can understand
 for size reasons, but the headers are small and fuss free, and you need
 the _right_ _versions_ to build against the Pg backend.

 No reason, other than I didn't realise they were needed to build extension.


 Ah, fair enough. I read:

 We do include the library. We don't include the headers or source for
 third party code though - that would be considered part of the build
 environment, just the same as the Windows SDK.

 as we don't want to distribute third-party headers even if required by
 Pg's own headers and thus thought you *did* know but by policy didn't
 want to distribute them.

I didn't know in this case, but was making a general statement about
how I felt the policy should be.

Plus I was feeling a little grumpy in my pre-coffee state. Sorry :-p

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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] Explicit psqlrc

2010-03-05 Thread Magnus Hagander
2010/3/5 Magnus Hagander mag...@hagander.net:
 2010/3/5 David Christensen da...@endpoint.com:

 On Mar 4, 2010, at 4:00 PM, Magnus Hagander wrote:

 I've now for the second time found myself wanting to specify an
 explicit psqlrc file instead of just parsing ~/.psqlrc, so attached is
 a patch that accepts the PSQLRC environment variable to control which
 psqlrc file is used.

 Any objections to this (obviously including documentation - this is
 just the trivial code)


 My bikeshed has a --psqlrc path/to/file, but +1 on the idea.

 The main reason I went with environment variable is that it's the
 path-of-least-code :-) And it easily fullfills my use-cases for it,
 which has me launching interactive psql with completely different
 settings from a script.

 Do you have a use-case where --psqlrc would be more useful than an
 environment variable, or is it *only* bike-shedding? ;)

Just to be clear, the code difference isn't very large. Attached is a
patch that does both PSQLRC and --psqlrc.

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


psqlrc.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] Repetition of warning message while REVOKE

2010-03-05 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
 One thought is that the column cases should be phrased more like
   no privileges could be revoked for column foo of table bar
 Check the messages associated with DROP cascading for the canonical
 phrasing here, but I think that's what it is.

Looks like 'for column foo of relation bar' is more typical, so
that's what I did in the attached patch.  I also cleaned up a few other
things I noticed in looking through the various messages/comments.

Thanks!

Stephen
? src/backend/regex/.regcomp.c.swp
? src/pl/plpgsql/src/pl_scan.c
Index: src/backend/catalog/aclchk.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.163
diff -c -r1.163 aclchk.c
*** src/backend/catalog/aclchk.c	26 Feb 2010 02:00:35 -	1.163
--- src/backend/catalog/aclchk.c	5 Mar 2010 13:16:48 -
***
*** 304,327 
  	if (is_grant)
  	{
  		if (this_privileges == 0)
! 			ereport(WARNING,
! 	(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
!   errmsg(no privileges were granted for \%s\, objname)));
! 		else if (!all_privs  this_privileges != privileges)
! 			ereport(WARNING,
! 	(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
! 			 errmsg(not all privileges were granted for \%s\, objname)));
  	}
  	else
  	{
  		if (this_privileges == 0)
! 			ereport(WARNING,
! 	(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
! 			  errmsg(no privileges could be revoked for \%s\, objname)));
! 		else if (!all_privs  this_privileges != privileges)
! 			ereport(WARNING,
! 	(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
! 	 errmsg(not all privileges could be revoked for \%s\, objname)));
  	}
  
  	return this_privileges;
--- 304,365 
  	if (is_grant)
  	{
  		if (this_privileges == 0)
! 	   	{
! 			if (objkind == ACL_KIND_COLUMN  colname)
! ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
! 	  errmsg(no privileges were granted for column \%s\ of relation \%s\,
! 		  colname, objname)));
! 			else
! ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
! 	  errmsg(no privileges were granted for \%s\, objname)));
! 		}
! 		else
! 		{	
! 			if (!all_privs  this_privileges != privileges)
! 			{
! if (objkind == ACL_KIND_COLUMN  colname)
! 	ereport(WARNING,
! 			(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
! 	 errmsg(not all privileges were granted for column \%s\ of relation \%s\,
! 		 colname, objname)));
! else
! 	ereport(WARNING,
! 			(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
! 	 errmsg(not all privileges were granted for \%s\, objname)));
! 			}
! 		}
  	}
  	else
  	{
  		if (this_privileges == 0)
! 		{
! 			if (objkind == ACL_KIND_COLUMN  colname)
! ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
!   errmsg(no privileges could be revoked for column \%s\ of relation \%s\,
! 	  colname, objname)));
! 			else
! ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
!   errmsg(no privileges could be revoked for \%s\, objname)));
! 		}
! 		else
! 		{
! 			if (!all_privs  this_privileges != privileges)
! 			{
! if (objkind == ACL_KIND_COLUMN  colname)
! 	ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
! 		 errmsg(not all privileges could be revoked for column \%s\ of relation \%s\,
! 			 colname, objname)));
! else
! 	ereport(WARNING,
! 		(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
! 		 errmsg(not all privileges could be revoked for \%s\, objname)));
! 			}
! 		}
  	}
  
  	return this_privileges;
***
*** 1657,1664 
  		/*
  		 * The GRANT TABLE syntax can be used for sequences and non-sequences,
  		 * so we have to look at the relkind to determine the supported
! 		 * permissions.  The OR of table and sequence permissions were already
! 		 * checked.
  		 */
  		if (istmt-objtype == ACL_OBJECT_RELATION)
  		{
--- 1695,1702 
  		/*
  		 * The GRANT TABLE syntax can be used for sequences and non-sequences,
  		 * so we have to look at the relkind to determine the supported
! 		 * permissions.  The OR of relation and sequence permissions were
! 		 * already checked.
  		 */
  		if (istmt-objtype == ACL_OBJECT_RELATION)
  		{
***
*** 3046,3052 
  		case ACLCHECK_NO_PRIV:
  			ereport(ERROR,
  	(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! 	 errmsg(permission denied for column %s of relation %s,
  			colname, objectname)));
  			break;
  		case ACLCHECK_NOT_OWNER:
--- 3084,3090 
  		case ACLCHECK_NO_PRIV:
  			ereport(ERROR,
  	(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! 	 errmsg(permission denied for column \%s\ of relation \%s\,
  			colname, objectname)));
  			break;
  		case ACLCHECK_NOT_OWNER:


signature.asc
Description: Digital signature


Re: [HACKERS] machine-readable pg_controldata?

2010-03-05 Thread Greg Smith

Magnus Hagander wrote:

Huh? It's fixed with, you don't need regexps for that. Just split the
string apart.

Taking options for single fields might have a better usecase, of course :-)
  


I do find it a bit hard to imagine that any program capable of shelling 
out to call pg_controldata and doing something with the output would hit 
a major hurdle parsing the format that's already there.  Moving toward 
single fields I could see as being better for some cases, but going all 
the way to XML/JSON is a step backwards from the plain text format as 
far as I'm concerned.  Anything that can parse one of those complicated 
formats should be able to trivially chew the existing one already.  
Seriously:  I have bash scripts that parse that thing.



Even better would be the ability to get everything which is in
pg_controldata currently as part of a system view in a running
PostgreSQL; I can get most of them, but certainly not all.



+1 for having all the information available from inside the backend,
if that's technically possible (which I assume it should be)
  


I revisit this every time I write yet another user-space parser and ask 
myself why I haven't exposed it in the server yet.  The primary answer 
so far has always been because you can't execute a query on the standby 
while it's in recovery, making half the stuff I wanted the data far 
(e.g. standby lag monitoring like 
http://www.kennygorman.com/wordpress/?p=249 ) unable to use that 
interface anyway.  Now that Hot Standby cracks that objection, it's 
worth talking about for a minute.


pg_controldata itself just reads the file in directly and dumps the 
data.  There is a copy of it kept around all the time in shared memory 
though (ControlFile in xlog.c), protected by a LWLock.  At a high level 
you can imagine a new function in xlog.c that acquires that lock, copies 
the block into a space the backend allocated for saving it, releases the 
lock, and then returns the whole structure.  Then just wrap some number 
of superuser-only UDFs around it (I'd guess nobody wants regular ones 
able to hold a lock on ControlFile) and you've exposed the results to 
user-space.


Two questions before I'd volunteer to write that:

1) How do you handle the situation where the pg_controldata is invalid?  
Not read in yet and CRC is bad are the two most obvious ones that 
can happen.  Return a null for every field, try and guess (the way 
pg_resetxlog does), don't return a row of output at all, or throw an 
error?  Each of these has slightly different implications for how admin 
code that will do something with these values will have to be structured.


2) While it's easy to say I only want one or two of these values and 
expose a whole set of UDFs to grab them individually (perhaps wrapping 
into a system view via that popular approach), I am concerned that 
people are going to call any single-value versions provided one at a 
time and get an inconsistent set.  I think the only reasonable interface 
to this would not return a single field, it would pop out all of them so 
you got a matching set from the point in time the lock was held.  And if 
that's the case, I'm not sure of the most reasonable UI is.  Just return 
a whole row with a column for each field in the file, and then people 
can select out just the ones they want?  (That's probably the right 
one)  Produce the mess as a series of rows with (name,value) pairs?  Put 
them into an array?


Have re-raised these concerns to myself, this is usually the point in 
this exercise where I go screw it, I'll just parse pg_controldata again 
instead and do that instead.  This is happening so much lately that I 
think Josh's suggestion it's just unworkable to keep going via that 
model forever has merit though.  I find it hard to mark this 9.0 
territory though, given the data is not actually difficult to grab--and 
that trail is already well blazed, nothing new in this version.


In short:  I'd vote for TODO item and would happily write myself for 9.1 
given reasonable agreement on the questions raised above, -1 for doing 
anything about it right now though.  Given both the existence of 
completely reasonable workarounds and the existence of much more serious 
blocker problems sitting on the roadmap to release, can't get real 
excited about this as the thing to worry about right now.  Same reason I 
ignored the idea when Joshua Tolley brought it up last month:  
http://archives.postgresql.org/message-id/4b69caeb.9513f30a.731a.3...@mx.google.com


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us




Re: [HACKERS] Explicit psqlrc

2010-03-05 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 2010/3/5 David Christensen da...@endpoint.com:
 My bikeshed has a --psqlrc path/to/file, but +1 on the idea.

 Do you have a use-case where --psqlrc would be more useful than an
 environment variable, or is it *only* bike-shedding? ;)

The env variable solution seems a bit surprising to me.  If we were
dealing with a libpq option it would make sense (to avoid having to pass
the info through other layers) but for a psql option it doesn't AFAICS.

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


[HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread François Pérou
Dear friends,

As a reminder, I took part in the development of pgAdmin and I am not
looking for a flame war.

I would like to point out Drupal community efforts (including myself) to
write down the most frequent problems when porting MySQL from/to
PostgreSQL:

The main MySQL/PostgreSQL issues can be found here:
http://drupal.org/node/14

An important pending issue, which goes on and on for years:

= All non-aggregate fields must be present in the GROUP BY clause
http://drupal.org/node/30

These ones are less urgent, but still needed to ease migration:

= Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM
(SELECT DISTINCT ... ) ... for multiple
http://drupal.org/node/706264

= DELETE SYNTAX on several tables requires the USING syntax:
http://drupal.org/node/62

IMHO, it is no use working on complex issues like replication if the SQL
code of major softwares cannot run on PostgreSQL. 

IMHO, 99% Drupal developers do not have a deep knowledge in SQL:

* For example, part of Drupal developers are trying to find an automated
way to insert DISTINCT on queries automatically using PHP preg. Of
course, this creates bugs, which go on and on for years. The attempt can
be seen here: http://drupal.org/node/284392 (400 replies). It could
well be 10 years more bugs in this thread.

* Another very funny thing from Drupal community is that MySQL trims
data without complaining, which is not the case for PostgreSQL:
http://drupal.org/node/279219

But there is no way to change people. It looks like PostgreSQL SQL
syntax and parser should evolve to become more tolerant.

If PostgreSQL syntax was more tolerant, Drupal developers would be
interested in leaving MySQL for PostgreSQL. SO PLEASE take a deep look
at my request.

So what are your plans for PostgreSQL 9? Do you finally plan to beat
MySQL?

Kind regards,
Jean-Michel Pouré



-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 I do find it a bit hard to imagine that any program capable of shelling 
 out to call pg_controldata and doing something with the output would hit 
 a major hurdle parsing the format that's already there.

+1

 1) How do you handle the situation where the pg_controldata is invalid?  

Throw an error

 2) While it's easy to say I only want one or two of these values and 
 expose a whole set of UDFs to grab them individually (perhaps wrapping 
 into a system view via that popular approach), I am concerned that 
 people are going to call any single-value versions provided one at a 
 time and get an inconsistent set.

I'm not too concerned about this. This will be a fairly advanced interface, 
and a warning in the docs should suffice. I think a good interface will 
help however. I'd lean towards something like pg_settings.

What I *would* like to see is two tweaks to the output of pg_controldata. 
First, having the time of latest checkpoint appear as an epoch (rather than 
or in addition to a localized time string) would help quite a bit. Second, it 
can be hard to build regex solutions when you don't know whan language your 
end user will be using. Not sure of the best solution for that one off the top 
of 
my head, but there are some workarounds. For example, check_postgres.pl stores 
all the languages translations of Time of latest checkoint to help it find 
that 
information, but I'd sure like a more elegant solution. (One could count lines, 
but that's presumes the order and number of items will never change).

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201003050945
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkuRGYMACgkQvJuQZxSWSsiDvgCgxgFtcy99ehUGt7i7gCp8zRTY
044An1JEEwki9KLZu5BhKXCUNGqfyXDf
=ruYL
-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] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Dave Page
2010/3/5 François Pérou francois.pe...@free.fr:
 Dear friends,

 As a reminder, I took part in the development of pgAdmin and I am not
 looking for a flame war.

What did you work on François? I can't find your name in my email
archives or on archives.postgresql.org.


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Heikki Linnakangas
Greg Smith wrote:
 pg_controldata itself just reads the file in directly and dumps the
 data.  There is a copy of it kept around all the time in shared memory
 though (ControlFile in xlog.c), protected by a LWLock.  At a high level
 you can imagine a new function in xlog.c that acquires that lock, copies
 the block into a space the backend allocated for saving it, releases the
 lock, and then returns the whole structure.  Then just wrap some number
 of superuser-only UDFs around it (I'd guess nobody wants regular ones
 able to hold a lock on ControlFile) and you've exposed the results to
 user-space.
 
 Two questions before I'd volunteer to write that:
 
 1) How do you handle the situation where the pg_controldata is invalid? 
 Not read in yet and CRC is bad are the two most obvious ones that
 can happen.

If the data in pg_control are invalid, the database won't start up, so
by the time you're running the user-defined-functions the data really
ought be valid.

 2) While it's easy to say I only want one or two of these values and
 expose a whole set of UDFs to grab them individually (perhaps wrapping
 into a system view via that popular approach), I am concerned that
 people are going to call any single-value versions provided one at a
 time and get an inconsistent set.  I think the only reasonable interface
 to this would not return a single field, it would pop out all of them so
 you got a matching set from the point in time the lock was held.  And if
 that's the case, I'm not sure of the most reasonable UI is.  Just return
 a whole row with a column for each field in the file, and then people
 can select out just the ones they want?  (That's probably the right
 one)

Yeah, one column for field seems ok to me.

Which fields do you want to expose? Perhaps it would make sense to split
the functionality in a few user-defined functions: one to return static
information about the architecture and compilation options (alignment,
32-bin vs 64 bit, block sizes, etc.) one to return all the fields
regarding latest checkpoint, plus other functions for the rest that are
needed.

The REDO location of last checkpoint might deserve a function of its
own, like we have pg_last_xlog_replay_location() and
pg_last_xlog_receive_location().

 Have re-raised these concerns to myself, this is usually the point in
 this exercise where I go screw it, I'll just parse pg_controldata again
 instead and do that instead.  This is happening so much lately that I
 think Josh's suggestion it's just unworkable to keep going via that
 model forever has merit though.  I find it hard to mark this 9.0
 territory though, given the data is not actually difficult to grab--and
 that trail is already well blazed, nothing new in this version.

I have no problem adding this to 9.0 if we have a solid proposal for the
UI. It's low risk and makes the life easier for people. People are
clearly missing this.

Then again, if you don't use the copy in shared memory but just open the
pg_control file and read it in the UDF, you could implement this as a
pgfoundry module that works with older versions too.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Andrew Dunstan



François Pérou wrote:


An important pending issue, which goes on and on for years:

= All non-aggregate fields must be present in the GROUP BY clause
http://drupal.org/node/30


  


The trouble is that the bottom of this page looks like nonsense to me.

The reason that

   |SELECT COUNT(nid) FROM node
   WHERE nid  0 AND type IN ('page')
   ORDER BY nid
   |

fails really has nothing to do with GROUP BY. It has to do with a 
meaningless and silly ORDER BY clause:


   andrew=# SELECT COUNT(nid) FROM node
   andrew-# WHERE nid  0 AND type IN ('page')
   andrew-# ORDER BY nid;
   ERROR:  column node.nid must appear in the GROUP BY clause or be
   used in an aggregate function

And it could be cured by using an alias:

   SELECT COUNT(nid) as nid FROM node
   WHERE nid  0 AND type IN ('page')
   ORDER BY nid;

or by omitting the ORDER BY altogether, or by using ORDER BY 1.

I think this query is NOT, as the page states, equivalant to:

   |SELECT COUNT(nid) FROM node
   WHERE nid  0 AND type IN ('page')
   GROUP BY nid
   ORDER BY nid
   |

If it is equivalent in MySQL then MySQL is broken, IMNSHO, and there 
would be no reason for us to mimic that brokenness. The first query 
(with the order by removed) should produce a single row. The second 
should produce one row per nid.


Now, there is an issue with GROUP BY that has the following TODO item, 
which has not been done (and thus will not be in 9.0):


   Add support for functional dependencies
   This would allow omitting GROUP BY columns when grouping by the
   primary key. 



But AIUI that won't be the same as the MySQL behaviour, as documented at 
http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html:


   When using this feature, all rows in each group should have the same
   values for the columns that are ommitted from the |GROUP BY| part.
   The server is free to return any value from the group, so the
   results are indeterminate unless all values are the same.

It will only be usable when PostgreSQL can know that the omitted columns 
have a single value for the group, i.e. you won't ever get a different 
result by omitting a redundant GROUP BY column.


In general, our aim is not to mimic MySQL. Asking us to do so simply for 
the sake of compatibility is just about a sure way to get people's backs 
up around here. Try going to the MySQL folks and asking them to be more 
compatible with Postgres, and see how far you get. It is quite possible 
to write code that runs on multiple databases. Bugzilla (to mention one 
I have had a personal hand in enabling) has been doing it for years.


cheers

andrew



||
||

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


Re: [HACKERS] Explicit psqlrc

2010-03-05 Thread Magnus Hagander
2010/3/5 Tom Lane t...@sss.pgh.pa.us:
 Magnus Hagander mag...@hagander.net writes:
 2010/3/5 David Christensen da...@endpoint.com:
 My bikeshed has a --psqlrc path/to/file, but +1 on the idea.

 Do you have a use-case where --psqlrc would be more useful than an
 environment variable, or is it *only* bike-shedding? ;)

 The env variable solution seems a bit surprising to me.  If we were
 dealing with a libpq option it would make sense (to avoid having to pass
 the info through other layers) but for a psql option it doesn't AFAICS.

Fair enough. It worked in my environment, but I can see your point. So
I'll just strip that part out then :)


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

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


[HACKERS] Core dump running PL/Perl installcheck with bleadperl [PATCH]

2010-03-05 Thread Tim Bunce
I encountered a core dump running PL/Perl installcheck with a very
recent git HEAD of PostgreSQL and a not quite so recent git HEAD of perl.

The cause is a subtle difference between SvTYPE(sv) == SVt_RV and
SvROK(sv). The former is checking a low-level implementation detail
while the later is directly checking does this sv contains a reference.

The attached patch fixes the problem by changing the SvTYPE check to use
SvROK instead. Although I only tripped over one case, the patch changes
all four uses of SvTYPE(sv) == SVt_RV. The remaining uses of SvTYPE are ok.

Tim.

diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 956eddb..c1cc8ff 100644
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
*** plperl_modify_tuple(HV *hvTD, TriggerDat
*** 976,982 
  		ereport(ERROR,
  (errcode(ERRCODE_UNDEFINED_COLUMN),
   errmsg($_TD-{new} does not exist)));
! 	if (!SvOK(*svp) || SvTYPE(*svp) != SVt_RV || SvTYPE(SvRV(*svp)) != SVt_PVHV)
  		ereport(ERROR,
  (errcode(ERRCODE_DATATYPE_MISMATCH),
   errmsg($_TD-{new} is not a hash reference)));
--- 976,982 
  		ereport(ERROR,
  (errcode(ERRCODE_UNDEFINED_COLUMN),
   errmsg($_TD-{new} does not exist)));
! 	if (!SvOK(*svp) || !SvROK(*svp) || SvTYPE(SvRV(*svp)) != SVt_PVHV)
  		ereport(ERROR,
  (errcode(ERRCODE_DATATYPE_MISMATCH),
   errmsg($_TD-{new} is not a hash reference)));
*** plperl_func_handler(PG_FUNCTION_ARGS)
*** 1549,1555 
  		 * value is an error, except undef which means return an empty set.
  		 */
  		if (SvOK(perlret) 
! 			SvTYPE(perlret) == SVt_RV 
  			SvTYPE(SvRV(perlret)) == SVt_PVAV)
  		{
  			int			i = 0;
--- 1549,1555 
  		 * value is an error, except undef which means return an empty set.
  		 */
  		if (SvOK(perlret) 
! 			SvROK(perlret) 
  			SvTYPE(SvRV(perlret)) == SVt_PVAV)
  		{
  			int			i = 0;
*** plperl_func_handler(PG_FUNCTION_ARGS)
*** 1594,1600 
  		AttInMetadata *attinmeta;
  		HeapTuple	tup;
  
! 		if (!SvOK(perlret) || SvTYPE(perlret) != SVt_RV ||
  			SvTYPE(SvRV(perlret)) != SVt_PVHV)
  		{
  			ereport(ERROR,
--- 1594,1600 
  		AttInMetadata *attinmeta;
  		HeapTuple	tup;
  
! 		if (!SvOK(perlret) || !SvROK(perlret) ||
  			SvTYPE(SvRV(perlret)) != SVt_PVHV)
  		{
  			ereport(ERROR,
*** plperl_return_next(SV *sv)
*** 2218,2224 
   errmsg(cannot use return_next in a non-SETOF function)));
  
  	if (prodesc-fn_retistuple 
! 		!(SvOK(sv)  SvTYPE(sv) == SVt_RV  SvTYPE(SvRV(sv)) == SVt_PVHV))
  		ereport(ERROR,
  (errcode(ERRCODE_DATATYPE_MISMATCH),
   errmsg(SETOF-composite-returning PL/Perl function 
--- 2218,2224 
   errmsg(cannot use return_next in a non-SETOF function)));
  
  	if (prodesc-fn_retistuple 
! 		!(SvOK(sv)  SvROK(sv)  SvTYPE(SvRV(sv)) == SVt_PVHV))
  		ereport(ERROR,
  (errcode(ERRCODE_DATATYPE_MISMATCH),
   errmsg(SETOF-composite-returning PL/Perl function 

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Merlin Moncure
2010/3/5 François Pérou francois.pe...@free.fr:
 = All non-aggregate fields must be present in the GROUP BY clause
 http://drupal.org/node/30

My take is that this is never going to happen unless we are strictly
talking about cases where the non-aggregate fields can be
unambiguously determined.  If we aren't, mysql is wrong to allow this,
and developers that depend on it are wrong, and that is pretty much
all you are ever going to get from this list. :-)

The other stuff is mainly tangential fluff issues (takes 1% extra
effort to write portable sql for) except for the flexible multi table
delete, which would be nice although I wouldn't expect a strict copy
of mysql syntax.  I am personally looking at writeable CTE (which
didn't make 9.0) to do most of the things I would need to do with a
multi table delete feature, plus a quite a few other things.

merlin

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


Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Robert Haas
2010/3/5 François Pérou francois.pe...@free.fr:
 Dear friends,

 As a reminder, I took part in the development of pgAdmin and I am not
 looking for a flame war.

 I would like to point out Drupal community efforts (including myself) to
 write down the most frequent problems when porting MySQL from/to
 PostgreSQL:

 The main MySQL/PostgreSQL issues can be found here:
 http://drupal.org/node/14

 An important pending issue, which goes on and on for years:

 = All non-aggregate fields must be present in the GROUP BY clause
 http://drupal.org/node/30

 These ones are less urgent, but still needed to ease migration:

 = Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM
 (SELECT DISTINCT ... ) ... for multiple
 http://drupal.org/node/706264

 = DELETE SYNTAX on several tables requires the USING syntax:
 http://drupal.org/node/62

Interestingly, all there of these are cases where a portable syntax is
available, but at least some Drupal developers have chosen not to use
it.  All three web pages include a description of the portable syntax,
and a suggestion that it be used.  So the case that we should modify
PostgreSQL to support MySQL-specific syntax seems pretty weak.  Nor is
it the case that every other database in the world handles these like
MySQL and only PostgreSQL does the opposite.  In fact it's closer to
the other way around.  For example, Microsoft SQL Server generates
this error on your first query:

Column 'u.uid' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

PostgreSQL says:

ERROR:  column u.uid must appear in the GROUP BY clause or be used
in an aggregate function

And it sounds like Oracle may do something similar:

http://searchoracle.techtarget.com/answer/Invalid-GROUP-BY-SQL-query

Your complaint about SELECT COUNT(DISTINCT...) is similar.  There is a
perfectly portable way to write this that works in all database
engines, but some Drupal developers have chosen to write it in a way
that only works in some database engines.  Why not use the portable
method?  In fact, the docs already recommend using the portable
method; this seems like a non-issue.  The docs also say that
PostgreSQL will support the other syntax beginning in version 9.0, but
I'm not certain that's correct.

 IMHO, it is no use working on complex issues like replication if the SQL
 code of major softwares cannot run on PostgreSQL.

I think it would be great if Drupal ran on PostgreSQL, but I don't
believe that the solution is for PostgreSQL to support whatever syntax
Drupal happens to use.  I think the solution is for Drupal to use
syntax that works on more than one database, as is already suggested
by the web pages listed above.  Sounds like for about the same amount
of work they could pick up Oracle and Microsoft SQL server support as
well.

 IMHO, 99% Drupal developers do not have a deep knowledge in SQL:

 * For example, part of Drupal developers are trying to find an automated
 way to insert DISTINCT on queries automatically using PHP preg. Of
 course, this creates bugs, which go on and on for years. The attempt can
 be seen here: http://drupal.org/node/284392 (400 replies). It could
 well be 10 years more bugs in this thread.

Interestingly the very first reply here includes this phrase: Wow,
and here I thought Drupal 6 would finally have fixed various
db_rewrite_sql bugs.  And reply #145 includes: This is always what
happens when using MySQL. Franckly, you should always use PostgreSQL
and read detailed logs to understand how the parser works.

 * Another very funny thing from Drupal community is that MySQL trims
 data without complaining, which is not the case for PostgreSQL:
 http://drupal.org/node/279219

That's a feature, and the MySQL behavior is a bug.  From reply #7 on
that thread: When inserting TEXT into a VARCHAR(255), MySQL trims the
value to the first 255 characters. PostgreSQL complains and returns an
error, which the correct behavior.  I hope that Drupal can get fixed
on this issue ... As MySQL does not complain, this bug is unseen. It
is maybe Drupal most annoying bug, as it trims and destroys data, and
noone complains.

 But there is no way to change people. It looks like PostgreSQL SQL
 syntax and parser should evolve to become more tolerant.

 If PostgreSQL syntax was more tolerant, Drupal developers would be
 interested in leaving MySQL for PostgreSQL. SO PLEASE take a deep look
 at my request.

 So what are your plans for PostgreSQL 9? Do you finally plan to beat
 MySQL?

I finally abandoned MySQL completely seven years ago because the query
planner was so poor that no matter what I did I couldn't get even
moderately complex queries to perform decently.  So for my use case
PostgreSQL had MySQL beat even back then.  The main reason I stuck
with MySQL as long as I did is that the first versions of PostgreSQL
that I used didn't support things like dropping columns (that feature
was added in 2002) which was 

Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread François Pérou
Thanks for your answers.

To speak frankly:

* I wrote the Drupal guide for porting from MySQL to PostgreSQL. 

* I am also the author of remarks about people should use PostgreSQL to
write portable SQL. 

* I am very surprised by the SQL level of Php developers. The example
Drupal developers trying to rewrite SQL queries dynamically adding
DISTINCT clause is just an example. So don't expect them to understand
the difference between MySQL and PostgreSQL. It is out of reach. They
focuse on Php code.

* I got banned from Drupal website during 2 days because I opened a bug
complaining about a long running SQL query that moved the whole content
of a 20.000 rows forum into PHP variables just to display 'Previous' and
'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev
and Next features got removed from Drupal. They did not even try to use
SELECT FROM ... LIMIT ... OFFSET to find prev and next records.

* Php developers analyze database performance using PHP cache. They
never read MySQL logging information. I guess they don't have such
information, as on some providers, MySQL is configured without logging
(for ... speed as MySQL configuration states). So they use Php code to
display performance information.

All this is true.

Nevertheless, I feel my explanations are useless. This is like fighting
against the wind.

I believe that PostgreSQL should support more MySQLisms in order to BEAT
MySQL.

Feel free to use my guide on Drupal website. We have to adapt tools to
people, not the converse.

Kind regards,
Jean-Michel Pouré


-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Robert Haas
2010/3/5 François Pérou francois.pe...@free.fr:
 * I am very surprised by the SQL level of Php developers. The example
 Drupal developers trying to rewrite SQL queries dynamically adding
 DISTINCT clause is just an example. So don't expect them to understand
 the difference between MySQL and PostgreSQL. It is out of reach. They
 focuse on Php code.

I think you're still missing the point.  I am sorry that the Drupal
developers (at least in your opinion) do not understand the difference
between MySQL and PostgreSQL, but I don't feel like it's our problem
to fix that.  As far as I can tell, no promiment member of this
community agrees with any of the changes you are proposing.

I have a certain feeling of deja vu about this whole conversation:

http://archives.postgresql.org/pgsql-hackers/2009-08/msg01491.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01500.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01494.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01495.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01492.php

...Robert

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Andrew Dunstan



François Pérou wrote:


I believe that PostgreSQL should support more MySQLisms in order to BEAT
MySQL.


  


Our aim is not to beat MySQL. At least mine is not, and I don't think 
I'm alone. Many of the MySQLisms you want supported are just broken 
behaviour, in the view of many people. So you are not going to win 
arguments using this line of reasoning, IMNSHO.


cheers

andrew



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


Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Alastair Turner
2010/3/5 François Pérou francois.pe...@free.fr:
 Thanks for your answers.

 To speak frankly:

 * I wrote the Drupal guide for porting from MySQL to PostgreSQL.

 * I am also the author of remarks about people should use PostgreSQL to
 write portable SQL.

 * I am very surprised by the SQL level of Php developers. The example
 Drupal developers trying to rewrite SQL queries dynamically adding
 DISTINCT clause is just an example. So don't expect them to understand
 the difference between MySQL and PostgreSQL. It is out of reach. They
 focuse on Php code.

 * I got banned from Drupal website during 2 days because I opened a bug
 complaining about a long running SQL query that moved the whole content
 of a 20.000 rows forum into PHP variables just to display 'Previous' and
 'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev
 and Next features got removed from Drupal. They did not even try to use
 SELECT FROM ... LIMIT ... OFFSET to find prev and next records.

 * Php developers analyze database performance using PHP cache. They
 never read MySQL logging information. I guess they don't have such
 information, as on some providers, MySQL is configured without logging
 (for ... speed as MySQL configuration states). So they use Php code to
 display performance information.

 All this is true.

 Nevertheless, I feel my explanations are useless. This is like fighting
 against the wind.

 I believe that PostgreSQL should support more MySQLisms in order to BEAT
 MySQL.

 Feel free to use my guide on Drupal website. We have to adapt tools to
 people, not the converse.

 Kind regards,
 Jean-Michel Pouré


This is confusing advocacy and technical considerations.

Yes PHP coders are often deeply ignorant of database issues, many of
them deliberately and militantly so. I have had my altercations with
the Drupal community over dumb SQL and uncritical praise of document
databases as a pancea. MySQL has a set of characteristics (maybe even
deliberately) which work well for uptake in that market.

One of the reasons I like Postgres is the feeling I get that the
product and the community around it would like to make better
databases. They would like to use databases better and make it
possible for others to use databases better. On that front MySQL is
already beaten.

Just as the abuse of spreadsheets for data management will probably
never be properly vanquished, the permissiveness of MySQL will always
present a lower hurdle to some coders.

The perception of MySQL as enemy number one does also concern me a
bit. Postgres is competing for installed base on a far wider front
than that. If installed base is really that meaningful a competition
in the first place.

Bell.

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Jaime Casanova
2010/3/5 François Pérou francois.pe...@free.fr:

 I believe that PostgreSQL should support more MySQLisms in order to BEAT
 MySQL.


we BEAT mysql long ago... to make postgres as broken as mysql is not
an improve...

 Feel free to use my guide on Drupal website. We have to adapt tools to
 people, not the converse.


with that reasoning, then Galileo and Copernico should had faken their
tests to adjust the results to most people expections about the earth
being the center of the universe and our sun orbiting us


-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Joshua D. Drake
On Fri, 2010-03-05 at 12:42 -0500, Robert Haas wrote:
 2010/3/5 François Pérou francois.pe...@free.fr:
  * I am very surprised by the SQL level of Php developers. The example
  Drupal developers trying to rewrite SQL queries dynamically adding
  DISTINCT clause is just an example. So don't expect them to understand
  the difference between MySQL and PostgreSQL. It is out of reach. They
  focuse on Php code.
 
 I think you're still missing the point.  I am sorry that the Drupal
 developers (at least in your opinion) do not understand the difference
 between MySQL and PostgreSQL, but I don't feel like it's our problem
 to fix that.  As far as I can tell, no promiment member of this
 community agrees with any of the changes you are proposing.

Correct. 

Not to mention as far as I understand it, 99% of this is resolved in
Drupal 7.

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or 
Sir.


-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Robert Haas
On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote:
 2010/3/5 François Pérou francois.pe...@free.fr:
 Dear friends,

 As a reminder, I took part in the development of pgAdmin and I am not
 looking for a flame war.

 What did you work on François? I can't find your name in my email
 archives or on archives.postgresql.org.

I believe the OP is the same person as Jean-Michel Poure
j...@poure.com.  And I believe we discussed many of these same things
back in August.  And now he is posting them over again just to see if
he gets an answer he likes better the second time.  Perhaps Greg Stark
summed it up best:

http://archives.postgresql.org/pgsql-hackers/2009-08/msg01818.php

...Robert

-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Greg Smith

Heikki Linnakangas wrote:

Then again, if you don't use the copy in shared memory but just open the
pg_control file and read it in the UDF, you could implement this as a
pgfoundry module that works with older versions too.
  


This is the direction I'd prefer to see this go in a 9.0 context.  It's 
easy enough to build a fully functional version that lives works via the 
same proposed final UDF interface, just with the extra step of reading 
the file.  Get that working, and you just added a useful module 
supporting all the way back to 8.2 (I think--not sure if there's been 
any other changes that would break this) that people would love to have. 

Once it's done, the UI is solid, all the data is known to be exposed in 
the right way it turns out people wanted it to be, then do the simple 
conversion it to grab from shared memory instead and add it as an 
official 9.1 feature.  I'm not feeling any pressure that this is a 
must-fix item for the 9.0 release freeze--as warts here go, this is a 
both a small one and one that doesn't have to be fixed in core, so two 
strikes against it being critical.


I would rather have the ability to tweak on this for a few months to get 
everything right, while being able to expose regular updates outside of 
core, than to commit this is the best we've got so far just under the 
wire for 9.0 without necessarily enough time to do it well.  The few 
messages that have shown up here already have made left me with the 
optinion that just getting the requirements and preferred implementation 
nailed down here is going to take a few rounds of development to work 
out to everyone's satisfaction.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] machine-readable pg_controldata?

2010-03-05 Thread Josh Berkus
On 3/5/10 10:28 AM, Greg Smith wrote:
 
 I would rather have the ability to tweak on this for a few months to get
 everything right, while being able to expose regular updates outside of
 core, than to commit this is the best we've got so far just under the
 wire for 9.0 without necessarily enough time to do it well.

Oh, I wasn't proposing doing *anything* for 9.0.  I wanted to get
something on the TODO list for 9.1.  As far as I'm concerned, 9.0 is
closed to new ideas.  We have enough bugs to fix as it is.

--Josh Berkus

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Josh Berkus
All,

Given that Francois seems to return to this list every 3 months with the
exact same set of requests, I think we need to make a habit of ignoring
him the way we used to ignore Al Dev (although I'll comment that Al Dev
was *much* more entertaining).

Several members of our community are working with Drupal project leaders
to help them make Drupal more database-independant, and version 7 is
making great strides in this direction.  If there's an next step, it's
*our* community providing a test instance of PostgreSQL which Drupal
developers can test their modules against, and a pgsql-drupal list or
similar for them to work out the problems quickly and easily.

Neither of the Drupal project leaders I've talked with wanted PostgreSQL
to support more whacky MySQL syntax.  Francois is out on his own here.

And, Francois, it's not our goal to beat MySQL. Nobody can be a better
MySQL than MySQL, ever.

Our goal is to make the best possible SQL-Relational database.

--Josh Berkus

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


[HACKERS] arithmetic about inet

2010-03-05 Thread fanng yuan
Hi Guys:
I just do some research on ip address storage and comparing. I found pgsql
already fixed that issue. I want to get some point from your guys about how
this work. Could you give me some data about that . Also I'm interesting in
pgsql . Could you give me some suggestion about how to hack pgsql.
Thank.


Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Robert Haas
On Fri, Mar 5, 2010 at 1:48 PM, Josh Berkus j...@agliodbs.com wrote:
 Given that Francois seems to return to this list every 3 months with the
 exact same set of requests, I think we need to make a habit of ignoring
 him the way we used to ignore Al Dev (although I'll comment that Al Dev
 was *much* more entertaining).

Perhaps we should refer Francois/Jean-Michele to Al Dev's comments on
MySQL.  :-)

http://www.yolinux.com/HOWTO/PostgreSQL-HOWTO.html#ss4.2

...Robert

-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Greg Smith

Josh Berkus wrote:

Oh, I wasn't proposing doing *anything* for 9.0.  I wanted to get
something on the TODO list for 9.1.  As far as I'm concerned, 9.0 is
closed to new ideas.  We have enough bugs to fix as it is.
  


I didn't get that initially from how you characterized this as past 
time to add.  It's at 
http://wiki.postgresql.org/wiki/Todo#Point-In-Time_Recovery_.28PITR.29 now.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] machine-readable pg_controldata?

2010-03-05 Thread Josh Berkus

 I didn't get that initially from how you characterized this as past
 time to add.  It's at
 http://wiki.postgresql.org/wiki/Todo#Point-In-Time_Recovery_.28PITR.29 now.

Sorry for not being clear.  I took it for granted that since it's past
2/15, no non-critical patches would be even considered.  And
pg_controldata certainly isn't critical.

--Josh Berkus


-- 
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] arithmetic about inet

2010-03-05 Thread Robert Haas
On Thu, Mar 4, 2010 at 4:36 AM, fanng yuan fanngy...@gmail.com wrote:
 Hi Guys:
 I just do some research on ip address storage and comparing. I found pgsql
 already fixed that issue. I want to get some point from your guys about how
 this work. Could you give me some data about that . Also I'm interesting in
 pgsql . Could you give me some suggestion about how to hack pgsql.
 Thank.

You asked almost this exact some question 2 days ago and got a response, so...?

...Robert

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread David Fetter
On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote:
 Dear friends,
 
 As a reminder, I took part in the development of pgAdmin and I am
 not looking for a flame war.

You're doing a poor job on that latter.  You asked before for the
PostgreSQL project to address the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.

That is never going to happen, and you need to stop asking.

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] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Andrew Dunstan



David Fetter wrote:

On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote:
  

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am
not looking for a flame war.



You're doing a poor job on that latter.  You asked before for the
PostgreSQL project to address the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.
  


Drupal is not a little project. Let's keep our own facts straight.

cheers

andrew


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


Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Joshua D. Drake
On Fri, 2010-03-05 at 15:10 -0500, Andrew Dunstan wrote:
 
 David Fetter wrote:
  On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote:

  Dear friends,
 
  As a reminder, I took part in the development of pgAdmin and I am
  not looking for a flame war.
  
 
  You're doing a poor job on that latter.  You asked before for the
  PostgreSQL project to address the concerns of some, but by no means
  all, developers of some little project by introducing massive bugs.

 
 Drupal is not a little project. Let's keep our own facts straight.

Not it isn't, it dwarfs us I am sure. It is within our interest to work
with them but in a proper way. As I have said, Drupal 7 addresses most
of our concerns, so anything we want to complain about should be in the
interest of making sure Drupal 7 works properly, not previous versions.

Sincerely,

Joshua D. Drake


 
 cheers
 
 andrew
 
 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or 
Sir.


-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread David Fetter
On Fri, Mar 05, 2010 at 03:10:59PM -0500, Andrew Dunstan wrote:
 
 
 David Fetter wrote:
 On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote:
 Dear friends,
 
 As a reminder, I took part in the development of pgAdmin and I am
 not looking for a flame war.
 
 You're doing a poor job on that latter.  You asked before for the
 PostgreSQL project to address the concerns of some, but by no means
 all, developers of some little project by introducing massive bugs.
 
 Drupal is not a little project. Let's keep our own facts straight.

Drupal 6 support is, given its finite future.  He's basically asking
us to do something that Drupal 7 and later won't need or want, so I
stick by little.

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] SQL compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Dave Page
On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote:
 2010/3/5 François Pérou francois.pe...@free.fr:
 Dear friends,

 As a reminder, I took part in the development of pgAdmin and I am not
 looking for a flame war.

 What did you work on François? I can't find your name in my email
 archives or on archives.postgresql.org.

 I believe the OP is the same person as Jean-Michel Poure

Yeah, I had email from him offlist. FYI, whilst I disagree that we
should break Postgres for the sake of some lazy developers on another
project, Jean-Michel was a long-term major contributor to pgAdmin I
and II and I have a lot of respect for him and am extremely grateful
for his past work on the project.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Robert Haas
On Fri, Mar 5, 2010 at 3:30 PM, Dave Page dp...@pgadmin.org wrote:
 On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote:
 2010/3/5 François Pérou francois.pe...@free.fr:
 Dear friends,

 As a reminder, I took part in the development of pgAdmin and I am not
 looking for a flame war.

 What did you work on François? I can't find your name in my email
 archives or on archives.postgresql.org.

 I believe the OP is the same person as Jean-Michel Poure

 Yeah, I had email from him offlist. FYI, whilst I disagree that we
 should break Postgres for the sake of some lazy developers on another
 project, Jean-Michel was a long-term major contributor to pgAdmin I
 and II and I have a lot of respect for him and am extremely grateful
 for his past work on the project.

That is good to hear.  I do not think (nor do I think anyone else here
thinks) that making Drupal work with PostgreSQL is a bad idea, and I
hope that he and others will continue to pursue that goal - having
said that, asking us to make changes that are not based on solid
technical arguments, don't conform to the SQL standard, and most
important that we already clearly said we were not going to make is
not the way to get there.

...Robert

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread Chris Browne
francois.pe...@free.fr (François Pérou) writes:
 * I am very surprised by the SQL level of Php developers. The example
 Drupal developers trying to rewrite SQL queries dynamically adding
 DISTINCT clause is just an example. So don't expect them to understand
 the difference between MySQL and PostgreSQL. It is out of reach. They
 focuse on Php code.

If they refuse to contemplate suggestions as to how to write more
portable SQL queries that would work with databases other than MySQL,
then I don't know what constructive discussion there can be about this.

 I believe that PostgreSQL should support more MySQLisms in order to BEAT
 MySQL.

Why, when the MySQLisms in questions are divergences from the SQL
standards, and the issue seems to bite all the other databases in more
or less the same way?

It doesn't seem to me that the answer to Drupal developers refuse to
consider writing portable SQL is to try to impose MySQL's divergences
from SQL onto all the *other* DBMSes.
-- 
Have  you noticed   that,  when we were   young,  we were   told that
`everybody else   is doing  it' was   a  really  stupid reason  to  do
something,  but now it's the standard  reason for picking a particular
software package? -- Barry Gehm 

-- 
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 compatibility reminder: MySQL vs PostgreSQL

2010-03-05 Thread François Pérou
Le vendredi 05 mars 2010 à 15:40 -0500, Robert Haas a écrit :
 having
 said that, asking us to make changes that are not based on solid
 technical arguments, don't conform to the SQL standard, and most
 important that we already clearly said we were not going to make is
 not the way to get there. 

Okay, thank you all for these explanations. 

I was talking about flexibility in the SQL syntax, which is linked to
the need to be flexible in front of developers communities which are not
interested in SQL and therefore use broken tools like MySQL. Drupal 6 is
just an example. D7 may be better, okay. A lot of applications are built
with a bad SQL syntax. You cannot change the world.

I was talking about flexibility to allow people to migrate and test
other databases than MySQL and you stick to the idea of a pure SQL
syntax, like ayatollahs preaching in the desert. 

If an SQL syntax is not pure, why not rewrite it and issue a warning.
This would allow to run MySQL code nearly unchanged. All you say is NO,
NOT PURE code.

I will not ever post again on PostgreSQL mailing list about these
issues. Everyone expressed their ideas. I admit my ideas are not
supported by anyone. 

This is bad enough because I debugged hundreds of Drupal modules and
wrote a short technical guide on D website. Too bad you are not
listening for more flexibility. 

Bye.
Jean-Michel


-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 Greg Smith wrote:
 1) How do you handle the situation where the pg_controldata is invalid? 

 If the data in pg_control are invalid, the database won't start up, so
 by the time you're running the user-defined-functions the data really
 ought be valid.

Yeah.  If you are pulling from the shared-memory copy this is an
entirely idle concern.  If that data is not correct we have *way* worse
concerns than whether some UDF or other is going to go crazy.

 Which fields do you want to expose?

That's actually the part of this that concerns me most.  The data that
is in pg_control is really somewhat ad-hoc, particularly the items that
have to do with checking database compatibility.  I'm not comfortable
with the notion that we should expose all and only these fields, and
even less so with the idea that they should be tied together at the SQL
level.

What I'd prefer to see is a use-case presented and an API defined to
solve that case (or those cases, as the case may be).  If pulling data
from pg_control is the best solution, great.  But let's expose
pg_control seems like a backwards design approach.

(FWIW, my recollection of the original design intention for
pg_controldata was that it was meant as a troubleshooting tool if the
database wouldn't start up.  I'm somewhat bemused to hear that people
are trying to use it as part of production scripts.  It wasn't meant to
produce machine-readable output, much less to give values that you could
rely on with respect to a running server.)

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] Visual Studio 2005, C-language function - avoiding hacks?

2010-03-05 Thread Kevin Flanagan
Ok, that got me on the right track, thanks. I think the key points for this 
build scenario are these:

1. you have to define the symbol BUILDING_DLL in your code before including 
postgres.h (as that then means PGDLLIMPORT gets defined right in 
pg_config_os.h). That makes the 'inconsistent dll linkage' warnings go away.
2. you have to have include\server\port\win32 in the include dirs list as well 
as include\server (as that provides a bunch of otherwise-missing headers such 
as netdb.h)

However, that still leaves one missing include file - libintl.h, which c.h 
tries to include because ENABLE_NLS is defined (and that seems to get defined 
as 1 in pg_config.h whether you like it or not). And in fact, it seems Rostic 
Sheykhet posted at http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html 
with the same problem (now that I know how to do it, I know what to Google for 
to, er, find out how to do it :) ). You can get round it by commenting out the 
include or creating a dummy libintl.h.

Just posting the results here in case they're relevant for anything.

Kevin.


-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au] 
Sent: 05 March 2010 10:05
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Visual Studio 2005, C-language function - avoiding hacks?

Kevin Flanagan wrote:
 Ok, re building with the win32 configuration ... that sounds like just the 
 thing I should know about. All I've done is downloaded and installed the 
 1-click installer for Windows from 
 http://www.enterprisedb.com/products/pgdownload.do#windows ... so while I'm 
 sure it knows it's running on Win32, is there some other configuration change 
 I should make for dev purposes to indicate that it's the win32 
 configuration? Or does building with the win32 configuration refer to 
 those who are building the server from source, or something?

I wasn't too specific because it's been a while since I did any coding
against Pg on win32, and I couldn't remember exactly how it selected the
right code to use for a given platform - whether it was a macro that
must be defined, or what.

Having had a look at the sources: It's done by header search path. You
need to make sure that include/port/win32_msvc is on the header search
path as well as the main include/ directory.

I *think* port/win32 is for the MinGW win32 port and thus shouldn't be
included in the search path for msvc builds, but I'm not 100% sure of
that and a quick look doesn't reveal any documentation on the matter.

--
Craig Ringer


-- 
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] machine-readable pg_controldata?

2010-03-05 Thread Josh Berkus

 (FWIW, my recollection of the original design intention for
 pg_controldata was that it was meant as a troubleshooting tool if the
 database wouldn't start up.  I'm somewhat bemused to hear that people
 are trying to use it as part of production scripts.  It wasn't meant to
 produce machine-readable output, much less to give values that you could
 rely on with respect to a running server.)

I'll have a more detailed list when I've gotten further with testing HS/SR.

--Josh Berkus

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