Re: [HACKERS] Offer for PG Developers/Hackers

[Tino Wildenhain]

Tony Caduto schrieb:

Hi,
I want to give something back(I would give a donation but sales are poor 
:-( ,so I am offering to any verified Postgresql developer(by verified I 
mean your name shows up on this list  a LOT ) a free copy of PG 
Lightning Admin.


Does this mean postgres developer who delelops postgres or one who uses
postgres? :-)

Regards
Tino

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] Weird pg_dumpall bug?

[Christopher Kings-Lynne]

I did a dump of a 7.4.11 database using the 8.1.2 pg_dumpall.  I got 
this at the top of the dump:


...
...
CREATE ROLE support;
ALTER ROLE support WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB 
LOGIN PASSWORD 'md5';

...
...
CREATE ROLE support;
ALTER ROLE support WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN;
...
...

It dumped the support role twice!

Any ideas?

H...actually.  It's because I have a user called 'support' and a 
group called 'support'.


Seems like it needs a fix...

Chris


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] [PATCHES] postmaster/postgres merge for testing

[Marko Kreen]

On 1/23/06, Tom Lane [EMAIL PROTECTED] wrote:
 Peter Eisentraut [EMAIL PROTECTED] writes:
  Attached is a patch that merges postmaster and postgres into just a
  postmaster command.

 I had some second thoughts about this, specifically about which
 direction do we really want to go in.  With this patch, it no longer
 really matters what the executable file is named, right?  We were both
 implicitly assuming that the name should end up being postmaster,
 but I think there's a good case to be made that the right thing to do
 is to migrate in the direction of having just one executable named
 postgres.  We've seen complaints before that having a daemon named
 postmaster confuses newbies into thinking it's got something to do
 with mail.  And it's already the case that the child processes all call
 themselves postgres, which will become even more confusing if there is
 no longer any executable named postgres.

+1 for 'postgres'.

--
marko

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] Cache lookup failed error in tsearch2?

[Christopher Kings-Lynne]

What would be the cause of this error after upgrading from pgsql 7.4 to 8.1?

usatest=# SELECT lexize[1] FROM lexize('en_stem', 'bacon');
ERROR:  cache lookup failed for function 861011

Does tsearch2 need to somehow be tweaked after the upgrade?

Chris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

[R, Rajesh (STSD)]

Its not a macro.I meant that the code generated by 
AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for "configure"does not have 
"#include netdb.h". Hence function is not detected(unresolved 
getaddrinfo).Hence I thought AC_TRY_LINK could give test program 
instead of AC_REPLACE_FUNCS taking one.$ diff -r configure.in 
configure.in.new918a919 AC_MSG_CHECKING([for 
getaddrinfo])920c921,926 
AC_REPLACE_FUNCS([getaddrinfo])--- AC_TRY_LINK([#include 
netdb.h #include 
assert.h], 
[char (*f)();f=getaddrinfo;], ac_cv_func_getaddrinfo=yes, 
ac_cv_func_getaddrinfo=no) if test x"$ac_cv_func_getaddrinfo" = xyes; 
then AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the 
getaddrinfo function]) fi923a930 
AC_MSG_RESULT([$ac_cv_func_getaddrinfo])Regards,Rajesh R--This 
space intentionally left non-blank.-Original 
Message-From: Tom Lane [mailto:[EMAIL PROTECTED]]Sent: Tuesday, 
January 17, 2006 8:34 PMTo: R, Rajesh (STSD)Cc: 
pgsql-hackers@postgresql.orgSubject: Re: [HACKERS] [GENERAL] [PATCH] Better 
way to check for getaddrinfo function."R, Rajesh (STSD)" 
[EMAIL PROTECTED] writes: But the bottomline is the default test 
does not include netdb.h in the test code.That's 
odd. Is getaddrinfo a macro on Tru64? If so, the appropriate patch 
would probably make the test look more like the tests for finite() and 
friends:dnl Cannot use AC_CHECK_FUNC because finite may be a macro 
AC_MSG_CHECKING(for finite) AC_TRY_LINK([ #include math.h double 
glob_double; 
], [return 
finite(glob_double) ? 0 : 
1;], 
[AC_DEFINE(HAVE_FINITE, 1, [Define to 1 if you have finite().]) 
AC_MSG_RESULT(yes)], 
[AC_MSG_RESULT(no)]) 
 
 regards, tom 
lane

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

[Martijn van Oosterhout]

On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote:
 Its not a macro.
 I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by
 configure.in for configure
 does not have #include netdb.h. Hence function is not
 detected(unresolved getaddrinfo).
 Hence  I thought AC_TRY_LINK could give test program instead of
 AC_REPLACE_FUNCS taking one.

But if it isn't a macro, why do you need the header file? In C it's
perfectly legal to declare the symbol yourself and try to link and it
should work *unless* it's normally a macro.

We're still missing some necessary understanding here...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [HACKERS] Offer for PG Developers/Hackers

[Aftab Alam]

Hi Tony,

I am using PD Admin as a client, to connect PostgresSQL in windows
environment, It is not much flexibale.

I am highly obliged if i can get free copy of PG Lightning Admin.



Regards,
AFTAB ALAM





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Tony Caduto
Sent: Tuesday, January 24, 2006 1:29 PM
To: pgsql-hackers
Subject: [HACKERS] Offer for PG Developers/Hackers


Hi,
I want to give something back(I would give a donation but sales are poor
:-( ,so I am offering to any verified Postgresql developer(by verified I
mean your name shows up on this list  a LOT ) a free copy of PG
Lightning Admin.

I know most of you guys don't use windows, but if you do your welcome to
a copy.  There are no strings attached and you don't have to do anything
  other than keeping the setup password to yourself.

Just let me know via email.

Thanks,

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Cache lookup failed error in tsearch2?

[Andrew J. Kopciuch]

On Tuesday 24 January 2006 02:03, Christopher Kings-Lynne wrote:
 What would be the cause of this error after upgrading from pgsql 7.4 to
 8.1?

 usatest=# SELECT lexize[1] FROM lexize('en_stem', 'bacon');
 ERROR:  cache lookup failed for function 861011

 Does tsearch2 need to somehow be tweaked after the upgrade?



How did you dump and reload?

In 7.4 OIDs were used as column types in tsearch2.  They were changed to 
regprocedure types.  

There is a patch to apply to tsearch2 for 7.4 :
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_7.4.patch.gz

You can also use the sql script I wrote to make mods to your tsearch2 tables 
real time so a dump / reload will work :
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql


Andy

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

[R, Rajesh (STSD)]

sorry. It is a macro.so, would it be better to 
check for the macroas suggested by Tom or go with this patch$ diff -r configure.in configure.in.new918a919 
AC_MSG_CHECKING([for getaddrinfo])920c921,926 
AC_REPLACE_FUNCS([getaddrinfo])--- AC_TRY_LINK([#include 
netdb.h #include 
assert.h], 
[char (*f)();f=getaddrinfo;], ac_cv_func_getaddrinfo=yes, 
ac_cv_func_getaddrinfo=no) if test x"$ac_cv_func_getaddrinfo" = xyes; 
then AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the 
getaddrinfo function]) fi923a930 
AC_MSG_RESULT([$ac_cv_func_getaddrinfo])
I guess, instead of adding seperate code for macro 
checking as suggested by Tom, this might serve dual purpose.Thanks,Rajesh R--This 
space intentionally left non-blank.-Original 
Message-From: Martijn van Oosterhout [mailto:kleptog@svana.org]Sent: Tuesday, 
January 24, 2006 2:46 PMTo: R, Rajesh (STSD)Cc: Tom Lane; 
pgsql-hackers@postgresql.org; pgsql-general@postgresql.orgSubject: Re: 
[HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo 
function.On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) 
wrote: Its not a macro. I meant that the code generated by 
AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for "configure" 
does not have "#include netdb.h". Hence function is not 
detected(unresolved getaddrinfo). Hence I thought AC_TRY_LINK 
could give test program instead of AC_REPLACE_FUNCS taking 
one.But if it isn't a macro, why do you need the header file? In C it's 
perfectly legal to declare the symbol yourself and try to link and it should 
work *unless* it's normally a macro.We're still missing some necessary 
understanding here...Have a nice day,--Martijn van 
Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. 
n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for 
doing 5% of the work and then sitting around waiting for someone else to 
do the other 95% so you can sue them.

Re: [HACKERS] Surrogate keys (Was: enums)

[Bruno Wolff III]

On Thu, Jan 19, 2006 at 00:06:41 -0500,
  Tom Lane [EMAIL PROTECTED] wrote:
 
 The problem with SSN is that somebody other than you controls it.
 If you are the college registrar, then you control the student's
 registration number, and you don't have to change it.  In fact, guess
 what: you probably generated it in the same way as a surrogate key.

I work for a University and even the numbers assigned by us get changed on a
regular basis as it is very easy for people to get entered into the
system multiple times. (And for a while campus ids were SSNs by default and we
are still in the process of making them different for everyone.) There are
several effectively surrogate keys (campus id and emplid), but they don't map
1 to 1 to real people. I believe we keep a history of campus ids, and delete
emplids for duplicates.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Surrogate keys (Was: enums)

[Bruno Wolff III]

On Thu, Jan 19, 2006 at 09:53:11 -0500,
  [EMAIL PROTECTED] wrote:
 
 Yes. Representation of the DNA is probably best. But - that's a lot of
 data to use as a key in multiple tables. :-)

On a simple level, this would be a problem for twins.
There are other complications as well. People are going to have slightly
different DNA in different cells due to mutations. Though you could probably
do some averaging over a number of cells to get a single value.
For people that have had transplants, you could probably define something
for doing the sample for original material.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [PATCHES] postmaster/postgres merge for testing

[Gustavo Tonini]

pgd?

Gustavo.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Cache lookup failed error in tsearch2?

[Oleg Bartunov]

On Tue, 24 Jan 2006, Christopher Kings-Lynne wrote:


What would be the cause of this error after upgrading from pgsql 7.4 to 8.1?

usatest=# SELECT lexize[1] FROM lexize('en_stem', 'bacon');
ERROR:  cache lookup failed for function 861011

Does tsearch2 need to somehow be tweaked after the upgrade?



you miss regprocedure_7.4.patch.gz, available from 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/



Chris



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] ROLLBACK triggers?

[Jonah H. Harris]

My read is such that he is using Senna as follows (where external means outside of PostgreSQL):1. Create external FTI2. BEGIN TRANS3a. Insert record into PostgreSQL3b. Insert record into Senna4. Commit
His problem is that the PostgreSQL record (3a) *could* be rolled-back after he adds them to the Senna index (it would depend what else he's doing in the transaction). Although, I don't see why he wouldn't just be able to keep a list of UNDO-like information (DELETEs) for the Senna INSERTs and do this check at commit-time rather than needing some kind of success/failure notification; it seems like he would have to do this somehow anyway.
On 1/23/06, Jeroen T. Vermeulen [EMAIL PROTECTED] wrote:
On Mon, January 23, 2006 16:35, Daisuke Maki wrote: I'm currently trying to embed Senna full text search engine (http://qwik.jp/senna/) into postgres. I'm trying to achieve this by
 using triggers (implemented in C) to cause an update to senna's index at various points. This seemed to work fine until I realized that while postgres' SQL commands could be rolled back, Senna's index remained already-changed.
 There are other potential issues with regards to transaction safety, but currently this seems to be a problem that I cannot fix by simply patching Senna. So I thought that if there was a rollback trigger, I
 could call whatever necessary to undo the changes that were made to the index.I may just be being stupid here (haven't had my coffee yet) but are yousure that:I. The triggers really do arrive even when the modifications are aborted?
AFAIK triggers that were, er, triggered during a transaction only reallyget notified once the transaction commits.In psql:= LISTEN x;LISTEN= BEGIN;BEGIN= NOTIFY x;NOTIFY
= ABORT;ROLLBACK= BEGIN;BEGIN= NOTIFY x;NOTIFY= COMMIT;COMMITAsynchronous notification x received from server process with PID 42.As you can see, the ABORT also rolled back the NOTIFY, so it never
arrived.This may be exactly what you want.Well, actually it raises another question: is it alright for the ongoingtransaction not to see any changes it makes reflected in your index?II. Is there any chance of wrapping your work in a function, so you can
then create an index on the result of that function?I've never triedthis but presumably the server would then do all the work to keep yourindex updated, without any need for triggers and such.This is no different from what you'd do if you wanted, say, an index on an
upper-cased version of a text field to speed up case-insensitive searches. You create an index on TOUPPER(name) or whatever it is, and then whenyou select on WHERE TOUPPER(name)=TOUPPER(searchstring) you get full use
of the index, which you wouldn't get from a regular index on name.Jeroen---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Weird pg_dumpall bug?

[Stephen Frost]

* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote:
 H...actually.  It's because I have a user called 'support' and a 
 group called 'support'.
 
 Seems like it needs a fix...

Have you got a suggestion on just how to fix it...?  Debian's
pg_upgradecluster bails out with an error when it discovers this
situation but I don't think it'd be sensible for pg_dump to do that...

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Weird pg_dumpall bug?

[Andrew Dunstan]

On Tue, 2006-01-24 at 09:44 -0500, Stephen Frost wrote:
 * Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote:
  H...actually.  It's because I have a user called 'support' and a 
  group called 'support'.
  
  Seems like it needs a fix...
 
 Have you got a suggestion on just how to fix it...?  Debian's
 pg_upgradecluster bails out with an error when it discovers this
 situation but I don't think it'd be sensible for pg_dump to do that...
 


How about an option to map groups whose names conflict with user names
using a prefix mechanism? 

e.g. --map-conflicting-groups=gr_

Then in Christopher's example his support group would become the role
gr_support.

Just a thought.

cheers

andrew


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Weird pg_dumpall bug?

[Tom Lane]

Christopher Kings-Lynne [EMAIL PROTECTED] writes:
 H...actually.  It's because I have a user called 'support' and a 
 group called 'support'.

This was discussed already.  It's a corner case we didn't really think
about while designing roles.  It's possible to support this: the group
and the user will now really be the same entity, ie a role that has both
its own login privileges and members.  However pg_dump isn't doing the
right things to make the old situation morph into the new one.  Want to
write a patch?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Weird pg_dumpall bug?

[Andrew Dunstan]

On Tue, 2006-01-24 at 10:05 -0500, Tom Lane wrote:
 Christopher Kings-Lynne [EMAIL PROTECTED] writes:
  H...actually.  It's because I have a user called 'support' and a 
  group called 'support'.
 
 It's possible to support this: the group
 and the user will now really be the same entity, ie a role that has both
 its own login privileges and members.  
 


Assuming you actually want to unify the two objects. That might well be
the common case, but will it always be true?

cheers

andrew


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Weird pg_dumpall bug?

[Tom Lane]

Andrew Dunstan [EMAIL PROTECTED] writes:
 On Tue, 2006-01-24 at 10:05 -0500, Tom Lane wrote:
 It's possible to support this: the group
 and the user will now really be the same entity, ie a role that has both
 its own login privileges and members.  

 Assuming you actually want to unify the two objects. That might well be
 the common case, but will it always be true?

As compared to what?  I didn't like the notion of auto-renaming one of
the roles, if that's what you're suggesting.  That seems well outside
pg_dump's charter.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Weird pg_dumpall bug?

[Peter Eisentraut]

Am Dienstag, 24. Januar 2006 15:44 schrieb Stephen Frost:
 Have you got a suggestion on just how to fix it...?  Debian's
 pg_upgradecluster bails out with an error when it discovers this
 situation but I don't think it'd be sensible for pg_dump to do that...

Why not?  If the backup cannot be made in a way such that the semantics of the 
restored database are the same, it shouldn't do it.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] TODO item: locale per database patch (new iteration)

[Alexey Slynko]

Hi,

it's a renewed locale per database patch. Unfortunately, i've not found
clean way to rebuild database indexes automatically, if locale settings
of two databases (created and template) are differs. Now it's only
raises a NOTICE. So, if anyone has a right notion about it - let will
express. Comment and suggestions are highly appreciated

Index: src/backend/access/transam/xlog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.226
diff -u -r1.226 xlog.c
--- src/backend/access/transam/xlog.c   11 Jan 2006 08:43:12 -  1.226
+++ src/backend/access/transam/xlog.c   22 Jan 2006 16:41:02 -
@@ -3394,7 +3394,6 @@
 {
int fd;
charbuffer[BLCKSZ]; /* need not be aligned */
-   char   *localeptr;
 
/*
 * Initialize version and compatibility-check fields
@@ -3418,18 +3417,6 @@
ControlFile-enableIntTimes = FALSE;
 #endif
 
-   ControlFile-localeBuflen = LOCALE_NAME_BUFLEN;
-   localeptr = setlocale(LC_COLLATE, NULL);
-   if (!localeptr)
-   ereport(PANIC,
-   (errmsg(invalid LC_COLLATE setting)));
-   StrNCpy(ControlFile-lc_collate, localeptr, LOCALE_NAME_BUFLEN);
-   localeptr = setlocale(LC_CTYPE, NULL);
-   if (!localeptr)
-   ereport(PANIC,
-   (errmsg(invalid LC_CTYPE setting)));
-   StrNCpy(ControlFile-lc_ctype, localeptr, LOCALE_NAME_BUFLEN);
-
/* Contents are protected with a CRC */
INIT_CRC32(ControlFile-crc);
COMP_CRC32(ControlFile-crc,
@@ -3612,34 +3599,6 @@
but the server was compiled without 
HAVE_INT64_TIMESTAMP.),
 errhint(It looks like you need to recompile 
or initdb.)));
 #endif
-
-   if (ControlFile-localeBuflen != LOCALE_NAME_BUFLEN)
-   ereport(FATAL,
-   (errmsg(database files are incompatible with 
server),
-errdetail(The database cluster was 
initialized with LOCALE_NAME_BUFLEN %d,
-  but the server was compiled with 
LOCALE_NAME_BUFLEN %d.,
-  ControlFile-localeBuflen, 
LOCALE_NAME_BUFLEN),
-errhint(It looks like you need to recompile 
or initdb.)));
-   if (pg_perm_setlocale(LC_COLLATE, ControlFile-lc_collate) == NULL)
-   ereport(FATAL,
-   (errmsg(database files are incompatible with operating 
system),
-errdetail(The database cluster was initialized with 
LC_COLLATE \%s\,
-   which is not recognized by 
setlocale().,
-  ControlFile-lc_collate),
-errhint(It looks like you need to initdb or install 
locale support.)));
-   if (pg_perm_setlocale(LC_CTYPE, ControlFile-lc_ctype) == NULL)
-   ereport(FATAL,
-   (errmsg(database files are incompatible with operating 
system),
-   errdetail(The database cluster was initialized with LC_CTYPE 
\%s\,
-  which is not recognized by setlocale().,
- ControlFile-lc_ctype),
-errhint(It looks like you need to initdb or install 
locale support.)));
-
-   /* Make the fixed locale settings visible as GUC variables, too */
-   SetConfigOption(lc_collate, ControlFile-lc_collate,
-   PGC_INTERNAL, PGC_S_OVERRIDE);
-   SetConfigOption(lc_ctype, ControlFile-lc_ctype,
-   PGC_INTERNAL, PGC_S_OVERRIDE);
 }
 
 void
Index: src/backend/commands/dbcommands.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.175
diff -u -r1.175 dbcommands.c
--- src/backend/commands/dbcommands.c   22 Nov 2005 18:17:08 -  1.175
+++ src/backend/commands/dbcommands.c   22 Jan 2006 16:41:03 -
@@ -25,6 +25,10 @@
 #include unistd.h
 #include sys/stat.h
 
+#ifdef HAVE_LANGINFO_H
+#include langinfo.h
+#endif
+
 #include access/genam.h
 #include access/heapam.h
 #include catalog/catalog.h
@@ -49,6 +53,7 @@
 #include utils/fmgroids.h
 #include utils/guc.h
 #include utils/lsyscache.h
+#include utils/pg_locale.h
 #include utils/syscache.h
 
 
@@ -57,9 +62,11 @@
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId 
*dbFrozenXidP,
-   Oid *dbTablespace);
+   Oid *dbTablespace, char **dbCollate, char **dbCtype);
 static bool have_createdb_privilege(void);
 

Re: [HACKERS] Weird pg_dumpall bug?

[Tom Lane]

Peter Eisentraut [EMAIL PROTECTED] writes:
 Am Dienstag, 24. Januar 2006 15:44 schrieb Stephen Frost:
 Have you got a suggestion on just how to fix it...?  Debian's
 pg_upgradecluster bails out with an error when it discovers this
 situation but I don't think it'd be sensible for pg_dump to do that...

 Why not?  If the backup cannot be made in a way such that the
 semantics of the restored database are the same, it shouldn't do it.

If you take a hard line on that position, then it's not necessary for
pg_dump to support cross-version operation at all, because no major
PG release is ever 100.0% compatible with the previous one.

What is actually required of pg_dump is that it produce the closest
approximation it can get to the old behavior within the context of the
new version's semantics.  I can easily cite half a dozen examples of
cases where we've applied this logic in previous versions.  I do not
see a reason to treat this case differently.  The difference between
a single role acting as both user and group and the prior behavior of
separate objects is certainly well within the fuzz factor that we've
allowed pg_dump to have in the past.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] Cleaning up the INET/CIDR mess

[Tom Lane]

We've had previous discussions about how the distinction between INET
and CIDR isn't very well thought out, for instance
http://archives.postgresql.org/pgsql-hackers/2005-01/msg01021.php
http://archives.postgresql.org/pgsql-hackers/2006-01/msg00233.php

The basic problem is that the code is schizophrenic about whether these
types are the same or not.  The fact that we have implicit binary
(no function) coercions in both directions makes them effectively the
same, so why are there two different type names in the catalogs?
On the other hand, if they should be different (and they definitely
have different I/O behavior), this coercion behavior is wrong.  Also,
if they are different types, it's redundant to have a flag inside the
data structure saying which type a particular value is.

After some consideration I've come to the conclusion that we really do
want them to be separate types: the I/O behavior is settled (after quite
some long discussions) and we don't want to change it, so we can't merge
them into one type.  That leads to the following proposals:

Remove the internal is_cidr flag; it's a waste of space.  (It doesn't
actually cost anything today, because of alignment considerations, but
it would cost 2 bytes if we implement the proposed 2-byte-length-word
variant datum format.)  Even more to the point, the presence of the
flag has encouraged the sort of sloppy thinking and coding that got us
into this mess.  Whether it's an INET or a CIDR should be totally
determined by the SQL type system.

Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no
function) conversion.  However, inet-to-cidr has to either zero out bits
to the right of the netmask, or error out if any are set.  Joachim Wieland
posted a patch that makes the coercion function just silently zero out any
such bits.  That's OK with me, but does anyone want to argue for an error?
(If we do make the coercion function raise error, then we'd probably need
to provide a separate function that supports the bit-zeroing conversion.)

Currently, both directions of cast are implicit, but that is a bad idea.
I propose keeping cidr-to-inet as implicit but making inet-to-cidr an
assignment cast.  This fits with the fact that inet can represent all
values of cidr but not vice versa (compare int4 and int8).

Given the implicit binary-compatible coercion, it's OK to have just a
single function taking inet for any case where the function truly doesn't
care if it's looking at inet or cidr input.  For the cases where the code
currently pays attention to is_cidr, we'd have to make two separate
functions.  AFAICT that's only abbrev(inet) and text(inet) among the
current functions.  Also, set_masklen(inet,integer) would have to come
in two flavors since the output type should be the same as the input.

The relationship of cidr and inet would be a little bit like the relation
between varchar and text.  For instance, varchar doesn't have any
comparison operators of its own, but piggybacks on text's comparison
operators, relying on the implicit cast from varchar to text to make this
transparent to users.

One other point is what to do with the binary I/O functions (send/receive)
for inet and cidr.  I think that we should continue to send the is_cidr
flag byte for backwards-compatibility reasons.  On receive, we could
either ignore that byte entirely, or insist that it match the expected
datatype.  I'm inclined to ignore the byte but am willing to listen to
arguments to raise an error instead.

Comments?

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [PATCHES] postmaster/postgres merge for testing

[Mark Kirkwood]

Tom Lane wrote:

Peter Eisentraut [EMAIL PROTECTED] writes:

Attached is a patch that merges postmaster and postgres into just a 
postmaster command.



I had some second thoughts about this, specifically about which
direction do we really want to go in.  With this patch, it no longer
really matters what the executable file is named, right?  We were both
implicitly assuming that the name should end up being postmaster,
but I think there's a good case to be made that the right thing to do
is to migrate in the direction of having just one executable named
postgres.  We've seen complaints before that having a daemon named
postmaster confuses newbies into thinking it's got something to do
with mail.  And it's already the case that the child processes all call
themselves postgres, which will become even more confusing if there is
no longer any executable named postgres.

If we went in this direction we'd have to keep the installed
postmaster-postgres symlink for awhile to avoid breaking existing
start scripts, but it could be deprecated and then removed in a few
releases.

Thoughts?




+1 postgres (having the executable name matching the default os 
superuser and database accounts seems logical).


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Cleaning up the INET/CIDR mess

[Andrew Sullivan]

On Tue, Jan 24, 2006 at 01:23:17PM -0500, Tom Lane wrote:

 Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no
 function) conversion.  However, inet-to-cidr has to either zero out bits
 to the right of the netmask, or error out if any are set.  Joachim Wieland
 posted a patch that makes the coercion function just silently zero out any
 such bits.  That's OK with me, but does anyone want to argue for an error?
 (If we do make the coercion function raise error, then we'd probably need
 to provide a separate function that supports the bit-zeroing conversion.)

I'd argue for an error, on correctness grounds (someone's bound to
come back having misused these, and complain that it silently changed
data.  They'd have a point).

 One other point is what to do with the binary I/O functions (send/receive)
 for inet and cidr.  I think that we should continue to send the is_cidr
 flag byte for backwards-compatibility reasons.  On receive, we could
 either ignore that byte entirely, or insist that it match the expected
 datatype.  I'm inclined to ignore the byte but am willing to listen to
 arguments to raise an error instead.

If this is exposed to users in some way (I don't think it is, is it?)
then I'd argue for erroring, on the same grounds of what I say above. 
But otherwise, I think you could ignore it.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
--J.D. Baldwin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] Some platform-specific MemSet research

[Seneca Cunningham]

After reading the post on -patches proposing that MemSet be changed to
use long instead of int32 on the grounds that a pair of x86-64 linux
boxes took less time to execute the long code 64*10^6 times[1], I took a
look at how the testcode performed on AIX with gcc.  While the switch to
long did result in a minor performance improvement, dropping the
MemSetLoop in favour of the native memset resulted in the tests taking
~25% the time as the MemSetLoop-like int loop. The 32-bit linux system I
ran the expanded tests on showed that for the buffer size range that
postgres can use the looping MemSet instead of memset (size = 1024
bytes), MemSet generally had better performance.

Test results, reformatted for space:

* AIX5.3 ML3
gcc version 4.0.1
OBJECT_MODE=64 gcc -maix64 -O2
sizeof(int)  = 4
sizeof(long) = 8

intsize=8  1.876096   1.875817   1.875998
long   size=8  0.215347   0.215389   0.215367
memset size=8  0.127711   0.127726   0.127706
intsize=16 0.617316   0.617346   0.617300
long   size=16 0.408607   0.408294   0.408263
memset size=16 0.212843   0.176918   0.212854
intsize=32 2.983032   2.982887   2.982724
long   size=32 2.172499   2.172440   2.172549
memset size=32 0.255465   0.255449   0.255422
intsize=64 3.560825   3.559743   3.559785
long   size=64 2.974126   2.999054   2.942597
memset size=64 1.021843   1.021709   1.021704
intsize=1284.983803   4.983515   4.983236
long   size=1283.515213   3.514761   3.514733
memset size=1281.319846   1.319699   1.319671
intsize=2569.071160   9.070497   9.070350
long   size=2567.428318   7.001997   6.990831
memset size=2561.830684   1.830558   1.830533
intsize=512   17.330519  17.329175  17.328520
long   size=512   14.903931  14.902345  14.902329
memset size=5123.512420   3.512139   3.512111
intsize=1024  34.593734  34.592775  34.591700
long   size=1024  23.804386  23.652192  24.043249
memset size=1024   6.010309   6.049034   6.052664
intsize=2048  66.380036  66.374455  66.375010
long   size=2048  45.094202  45.087909  45.087128
memset size=2048  11.638963  11.662794  11.664649
intsize=4096 131.777427 131.764230 131.764542
long   size=4096  88.906880  88.840758  88.887926
memset size=4096  22.882468  22.921160  22.920992


* Pentium 4 2.80GHz
Ubuntu 5.10 2.6.12-10-686 #1
gcc version 4.0.2 20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9)
gcc -O2
sizeof(int)  = 4
sizeof(long) = 4

intsize=8  0.319620   0.270326   0.288407
long   size=8  0.279157   0.278571   0.339791
memset size=8  0.186439   0.192561   0.194865
intsize=16 0.455448   0.459051   0.519848
long   size=16 0.455193   0.451253   0.565159
memset size=16 0.257428   0.256752   0.356195
intsize=32 0.732009   0.730730   0.750304
long   size=32 0.731353   0.734311   0.743041
memset size=32 1.386004   1.404297   1.378161
intsize=64 1.289708   1.397941   1.288536
long   size=64 1.302256   1.380754   1.294904
memset size=64 2.965440   3.197489   2.958864
intsize=1283.162121   3.548065   3.158412
long   size=1283.150525   3.161121   3.153037
memset size=1283.705133   3.739082   3.704949
intsize=2565.393701   5.415562   5.583510
long   size=2565.420254   5.367381   5.362041
memset size=2569.246601   8.983931   9.040215
intsize=512   10.219667   9.854537   9.851564
long   size=5129.906317   9.878196  10.202070
memset size=512   11.290588  11.050312  11.789231
intsize=1024  19.06  20.752631  19.846717
long   size=1024  18.934663  18.870325  19.854066
memset size=1024  15.349694  15.487714  15.999638
intsize=2048  28.783087  28.214086  26.228851
long   size=2048  26.628890  30.611856  26.245331
memset size=2048  24.434751  24.095879  23.435490
intsize=4096  53.817698  57.266583  51.547177
long   size=4096  55.868670  53.012144  51.564656
memset size=4096  45.772710  40.651142  39.702063


[1] http://archives.postgresql.org/pgsql-patches/2006-01/msg00211.php

-- 
Seneca Cunningham
[EMAIL PROTECTED]
#include stdio.h
#include sys/time.h
#include string.h

#define TYPEALIGN(ALIGNVAL,LEN)  \
	(((long) (LEN) + ((ALIGNVAL) - 1))  ~((long) ((ALIGNVAL) - 1)))

#define MemSetLoop(type, start, val, len) \
	do \
	{ \
		type * _start = (type *) (start); \
		type * _stop = (type *) ((char *) _start + (size_t) (len)); \
	\
		while (_start  _stop) \
			*_start++ = 0; \
	} while (0)

#define MAXALIGN	8
#define MAXSIZE		4096
#define LOOP		(1000*1000*64)

static void print_time(const char* msg, int size, const struct timeval *start, const struct timeval *end)
{
	double t;
	t = (end-tv_sec - start-tv_sec) + (end-tv_usec - start-tv_usec) / 100.0;
	printf(%s (size=%d) : %f\n, msg, size, t);
}

#define TEST(type, size)	\
	do { \
		int i; \
		gettimeofday(start, NULL); \
		for(i = 0; i  LOOP; i++) \
		{ \
			MemSetLoop(type, buffer, 0, size); \
		} \
		gettimeofday(end, 

Re: [HACKERS] Some platform-specific MemSet research

[Martijn van Oosterhout]

On Tue, Jan 24, 2006 at 05:24:28PM -0500, Seneca Cunningham wrote:
 After reading the post on -patches proposing that MemSet be changed to
 use long instead of int32 on the grounds that a pair of x86-64 linux
 boxes took less time to execute the long code 64*10^6 times[1], I took a
 look at how the testcode performed on AIX with gcc.  While the switch to
 long did result in a minor performance improvement, dropping the
 MemSetLoop in favour of the native memset resulted in the tests taking
 ~25% the time as the MemSetLoop-like int loop. The 32-bit linux system I
 ran the expanded tests on showed that for the buffer size range that
 postgres can use the looping MemSet instead of memset (size = 1024
 bytes), MemSet generally had better performance.

Could you please check the asm output to see what's going on. We've had
tests like these produce odd results in the past because the compiler
optimised away stuff that didn't have any effect. Since every memset
after the first is a no-op, you want to make sure it's still actually
doing the work...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [HACKERS] Cleaning up the INET/CIDR mess

[Andrew - Supernews]

On 2006-01-24, Tom Lane [EMAIL PROTECTED] wrote:
 Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no
 function) conversion.  However, inet-to-cidr has to either zero out bits
 to the right of the netmask, or error out if any are set.  Joachim Wieland
 posted a patch that makes the coercion function just silently zero out any
 such bits.  That's OK with me, but does anyone want to argue for an error?
 (If we do make the coercion function raise error, then we'd probably need
 to provide a separate function that supports the bit-zeroing conversion.)

 Currently, both directions of cast are implicit, but that is a bad idea.
 I propose keeping cidr-to-inet as implicit but making inet-to-cidr an
 assignment cast.  This fits with the fact that inet can represent all
 values of cidr but not vice versa (compare int4 and int8).

If inet-to-cidr can zero out bits silently, then wouldn't making it an
assignment cast be rather dangerous and error-prone?

 Given the implicit binary-compatible coercion, it's OK to have just a
 single function taking inet for any case where the function truly doesn't
 care if it's looking at inet or cidr input.  For the cases where the code
 currently pays attention to is_cidr, we'd have to make two separate
 functions.  AFAICT that's only abbrev(inet) and text(inet) among the
 current functions.  Also, set_masklen(inet,integer) would have to come
 in two flavors since the output type should be the same as the input.

You sometimes need set_masklen(cidr,integer) returning inet, and I'd bet
there's existing code that does that.

 The relationship of cidr and inet would be a little bit like the relation
 between varchar and text.  For instance, varchar doesn't have any
 comparison operators of its own, but piggybacks on text's comparison
 operators, relying on the implicit cast from varchar to text to make this
 transparent to users.

Well, inet/cidr have far more justification for being separate types than
text/varchar do - the text/varchar issue causes a great deal of confusion.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Weird pg_dumpall bug?

[Christopher Kings-Lynne]

How about an option to map groups whose names conflict with user names
using a prefix mechanism? 


e.g. --map-conflicting-groups=gr_

Then in Christopher's example his support group would become the role
gr_support.


No bad, have to change some application code then as well...

Chris


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Weird pg_dumpall bug?

[Jim C. Nasby]

On Tue, Jan 24, 2006 at 10:42:17AM -0500, Tom Lane wrote:
 Andrew Dunstan [EMAIL PROTECTED] writes:
  On Tue, 2006-01-24 at 10:05 -0500, Tom Lane wrote:
  It's possible to support this: the group
  and the user will now really be the same entity, ie a role that has both
  its own login privileges and members.  
 
  Assuming you actually want to unify the two objects. That might well be
  the common case, but will it always be true?
 
 As compared to what?  I didn't like the notion of auto-renaming one of
 the roles, if that's what you're suggesting.  That seems well outside
 pg_dump's charter.

If you want something renamed, you can handle that case by just renaming
it before you do the dump, but it would be nice if pg_dump would raise a
nice big warning when this condition exists so you're aware of it. Or
maybe even refuse to run unless you supply some command line option to
over-ride.

I don't think we should morph the two together by default either,
because that's very possibly not what the user originally intended.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Cleaning up the INET/CIDR mess

[Bruce Momjian]

This is exactly what I had in mind:

split the types 
zero out the bits going to cidr
no change going to inet
make functions take inet, which as not cast change

---

Tom Lane wrote:
 We've had previous discussions about how the distinction between INET
 and CIDR isn't very well thought out, for instance
 http://archives.postgresql.org/pgsql-hackers/2005-01/msg01021.php
 http://archives.postgresql.org/pgsql-hackers/2006-01/msg00233.php
 
 The basic problem is that the code is schizophrenic about whether these
 types are the same or not.  The fact that we have implicit binary
 (no function) coercions in both directions makes them effectively the
 same, so why are there two different type names in the catalogs?
 On the other hand, if they should be different (and they definitely
 have different I/O behavior), this coercion behavior is wrong.  Also,
 if they are different types, it's redundant to have a flag inside the
 data structure saying which type a particular value is.
 
 After some consideration I've come to the conclusion that we really do
 want them to be separate types: the I/O behavior is settled (after quite
 some long discussions) and we don't want to change it, so we can't merge
 them into one type.  That leads to the following proposals:
 
 Remove the internal is_cidr flag; it's a waste of space.  (It doesn't
 actually cost anything today, because of alignment considerations, but
 it would cost 2 bytes if we implement the proposed 2-byte-length-word
 variant datum format.)  Even more to the point, the presence of the
 flag has encouraged the sort of sloppy thinking and coding that got us
 into this mess.  Whether it's an INET or a CIDR should be totally
 determined by the SQL type system.
 
 Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no
 function) conversion.  However, inet-to-cidr has to either zero out bits
 to the right of the netmask, or error out if any are set.  Joachim Wieland
 posted a patch that makes the coercion function just silently zero out any
 such bits.  That's OK with me, but does anyone want to argue for an error?
 (If we do make the coercion function raise error, then we'd probably need
 to provide a separate function that supports the bit-zeroing conversion.)
 
 Currently, both directions of cast are implicit, but that is a bad idea.
 I propose keeping cidr-to-inet as implicit but making inet-to-cidr an
 assignment cast.  This fits with the fact that inet can represent all
 values of cidr but not vice versa (compare int4 and int8).
 
 Given the implicit binary-compatible coercion, it's OK to have just a
 single function taking inet for any case where the function truly doesn't
 care if it's looking at inet or cidr input.  For the cases where the code
 currently pays attention to is_cidr, we'd have to make two separate
 functions.  AFAICT that's only abbrev(inet) and text(inet) among the
 current functions.  Also, set_masklen(inet,integer) would have to come
 in two flavors since the output type should be the same as the input.
 
 The relationship of cidr and inet would be a little bit like the relation
 between varchar and text.  For instance, varchar doesn't have any
 comparison operators of its own, but piggybacks on text's comparison
 operators, relying on the implicit cast from varchar to text to make this
 transparent to users.
 
 One other point is what to do with the binary I/O functions (send/receive)
 for inet and cidr.  I think that we should continue to send the is_cidr
 flag byte for backwards-compatibility reasons.  On receive, we could
 either ignore that byte entirely, or insist that it match the expected
 datatype.  I'm inclined to ignore the byte but am willing to listen to
 arguments to raise an error instead.
 
 Comments?
 
   regards, tom lane
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly