date:20050906

---BeginMessage---
I have been in need of a uuid type and ran across the pguuid download
by Xiongjian (Mike) Wang.  This wasn't really useful to me for two
reasons: first, it is GPLed and I would prefer a more liberal license,
secondly, it didn't compile cleanly on Mac OS 10.3, due to lack of a
SIOCGETIFHWADDR (? i think, i can get the exact name if you want it)
ioctl() under darwin.

While I could dike out the code that calls it, that seems like a suboptimal
solution.  So after a bit of poking around the interweb i ran across
Ralf Engelschall's ossp uuid library.  This compiled with minimal
effort on mac os.  Some reading, and an evening later, i've made
a server plugin with supporting SQL that implements an 'ossp_uuid'
type.

Now i have four questions:

1: Is it feasible for this to be included in the contrib section of
the regular download?  The uuid library is a notice of copyright style
license, and I am willing to put my own code into the public domain.

2: Would just calling the type 'uuid' be better than 'ossp_uuid'?  It's
certainly a nicer name.

3: Would it be possible to include such a type as a postgres extension
to the usual SQL types.  It seems to me that having an officially
supported type would be better than a user contributed type on the grounds
that you could then rely on it being avaiable if postgres was.
In particular, installing it as an extension would require the cooperation
of the DBA, which may be infeasible in some environments.

-- 
Nathan Wagner
---End Message---

Re: [HACKERS] uuid type for postgres

Hey Nathan.

I've started to make heavy use of pguuid. It had several bugs in it
that required fixing before I could use it. I have no preference on
pguuid. It was the only such PostgreSQL project I found that provided
a UUID type.

I'd be willing to work with you on ensuring that such a patch is in an
acceptable form for the core developers to accept, and help maintain
it.

My personal preference is that the type be called 'uuid' and accepted
into the core. Tom? Is their history on this issue?

Should it remain an extension, or can be get it built-in?

I find the UUID concept more useful than the SERIAL concept for data
that must now, or may in the future, be stored on multiple servers.
For example, submitting transactions to two different sites, where the
sites periodically synchronize up with each other, requiring a
relatively safe 'merge'. SERIAL can't do this at all. UUID can do
this as long as the rows are split into domains appropriately,
such as ensuring that unique column constraints do not need to
be enforced across sites.

UUID is also desirable over SERIAL for a unique identifier that will
be accessed from an outside source directly. Exposing the SERIAL
number to the outside isn't appealing for reasons including the
predictability of the sequence, the size of the database is exposed,
the internel implementation is exposed, and so on.

I suggest that UUID be recommended in place of SERIAL for certain
classes of applications, and that it therefore belongs in the core.
UUID and SERIAL can be used together (although, once you have a UUID,
it may not be useful to also have a SERIAL).

Ok, you can all tell me I'm wrong now... :-)

mark


On Tue, Sep 06, 2005 at 01:50:57PM +, nathan wagner wrote:
 
 I have been in need of a uuid type and ran across the pguuid download
 by Xiongjian (Mike) Wang.  This wasn't really useful to me for two
 reasons: first, it is GPLed and I would prefer a more liberal license,
 secondly, it didn't compile cleanly on Mac OS 10.3, due to lack of a
 SIOCGETIFHWADDR (? i think, i can get the exact name if you want it)
 ioctl() under darwin.
 
 While I could dike out the code that calls it, that seems like a suboptimal
 solution.  So after a bit of poking around the interweb i ran across
 Ralf Engelschall's ossp uuid library.  This compiled with minimal
 effort on mac os.  Some reading, and an evening later, i've made
 a server plugin with supporting SQL that implements an 'ossp_uuid'
 type.
 
 Now i have four questions:
 
 1: Is it feasible for this to be included in the contrib section of
 the regular download?  The uuid library is a notice of copyright style
 license, and I am willing to put my own code into the public domain.
 
 2: Would just calling the type 'uuid' be better than 'ossp_uuid'?  It's
 certainly a nicer name.
 
 3: Would it be possible to include such a type as a postgres extension
 to the usual SQL types.  It seems to me that having an officially
 supported type would be better than a user contributed type on the grounds
 that you could then rely on it being avaiable if postgres was.
 In particular, installing it as an extension would require the cooperation
 of the DBA, which may be infeasible in some environments.
 
 -- 
 Nathan Wagner
 

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


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

Re: [HACKERS] How to add column in pg_class

Rafaqat Ali [EMAIL PROTECTED] writes:
   Is there any other file in which I have to make change ?

Fooling with any of the bootstrapped catalogs is pretty messy.
You might grab this patch from the CVS server for comparison:

2005-03-29 14:44  tgl

* doc/src/sgml/bki.sgml, doc/src/sgml/catalogs.sgml,
src/backend/bootstrap/bootstrap.c, src/backend/catalog/pg_proc.c,
src/include/catalog/catversion.h,
src/include/catalog/pg_attribute.h, src/include/catalog/pg_class.h,
src/include/catalog/pg_proc.h: Add proallargtypes and proargmodes
columns to pg_proc, as per my earlier proposal for OUT parameter
support.  The columns don't actually *do* anything yet, they are
just left NULLs.  But I thought I'd commit this part separately as
a fairly pure example of the tasks needed when adding a column to
pg_proc or one of the other core system tables.

Whatever you're doing in heapam.c is probably wrong, too.  There is no
reason for that file to be involved in a system catalog extension ---
it operates at too low a level.

regards, tom lane

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

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

Re: [HACKERS] uuid type for postgres

[EMAIL PROTECTED] writes:
 My personal preference is that the type be called 'uuid' and accepted
 into the core. Tom? Is their history on this issue?
 Should it remain an extension, or can be get it built-in?

There is pretty much zero chance of being accepted into contrib, much
less core, if the code isn't pure BSD license.  (We used to be a bit
lax about that, but are trying to clean things up.)  Nathan's comment
about starting with code that was sorta-BSD-with-advertising alarmed me.

However, you can certainly set up a pgfoundry project with code of any
license you like.  That would be a good starting point anyway --- at
some point you'd have to convince people that there's enough demand
for the feature to justify putting it in core, and being able to point
to X number of downloads from pgfoundry would go a long way towards
making that case.

regards, tom lane

---(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] dbt-4 (tpc-app) kit

2005-09-06 Thread Mark Wong

On Fri, 2 Sep 2005 15:50:25 -0400
Dave Cramer [EMAIL PROTECTED] wrote:

 
 On 2-Sep-05, at 3:38 PM, Mark Wong wrote:
 
  Hi Dave,
 
  Oops, EJB's are distasteful?  My experience in this area is quite
  lacking.
 Well, I said personally distasteful.
  Not that I necessarily want to be 100% strict but the spec
  says this needs to conform to WS-I BP 1.0 specification, which I
  understand is basically using the SOAP transport?  I just thought it
  would be nice to have something that could be plugged into any
  application server (Geronimo, JBoss, etc.) with minimal configuration
  changes and that was where I started.  I don't mind doing away with  
  the
  EJB altogether.  What do you suggest?
 
 I use hibernate, which would allow this to be used without an  
 application server.
 However strictly speaking, many people are going to want to see EJB's  
 as this is
 what the appserver types like to use.
 
  To give you a little better idea of where I'm at, I have most of 7  
  of 9
  interactions implemented, the remaining two I haven't started.  The
  driver needs to be expanded to simulate multiple users.  I haven't
  started any of the post processing or data collection scripts yet and
  some of that can be used from our other kits.  My Java programming  
  style
  should probably be reviewed too. ;)
 
 I had a very (very) quick look at your code, one suggestion would be  
 log4j, (or other configurable logging API ) for your debugging  
 statements.

Ok, I'll have to learn how to do that.  :)
 
 If I have some time, I'll look at it some more, I've come to like  
 using the spring framework. This allows something referred to as IOC  
 (Inversion of Control) Basically, the problem is how to stub test  
 code in when we have static factory objects, the solution is to build  
 the application using a container  which specifies the concrete  
 classes which are instantiated. This would in theory allow us to have  
 tow versions. One which uses hibernate, and the other which uses  
 EJB's. So one thing to think about is to code in interfaces more, and  
 instantiate concrete classes which implement the interface.

That sounds like a very good idea.  I'll probably need some coaching
though...

Mark

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

   http://archives.postgresql.org

Re: [HACKERS] Simple tester for MVCC in PostgreSQL

2005-09-06 Thread Matt Miller

On Tue, 2005-08-30 at 00:56 +0200, Martijn van Oosterhout wrote:
 I saw the discussion about an tester for MVCC. Since I'd never done
 anything with asyncronous queries before, I figured I'd try to write
 something useful with it. The result is at:
 
 http://svana.org/kleptog/pgsql/mvcctest.tar.gz

I've started using it in some simple cases and it seems to be a good
tool.  The feature set looks to me to be a pretty solid core on which to
build.

 It uses Perl and the Pg module from CPAN

This dependency seems easy enough to live with.

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

[HACKERS] Mysteriously lost values in nodes

2005-09-06 Thread Martijn van Oosterhout

[Please CC any replies, thanks]

I added a field to each of Var, Const, FuncExpr and OpExpr which is set
during parse_expr. But somewhere between the parsing and execution the
values of these fields get reset back to NULL. But only for FuncExpr
and OpExpr, for Var and Const it all works as expected.

I've traced with the debugger and confirmed that the field is set but
that it's copied somewhere before execution and that copy didn't copy
this field. The copyFuncExpr worked, it's just that another place did a
copy some other way.

grep reveals several places where new nodes are created but rather than
just changing them all, is there a particular phase where these changes
are made that I should be looking at?

Thanks in advance,
-- 
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.


pgpSmnU6pclcN.pgp
Description: PGP signature

Re: [HACKERS] uuid type for postgres

On Tue, Sep 06, 2005 at 11:38:57AM -0400, [EMAIL PROTECTED] wrote:
 
 There is pretty much zero chance of being accepted into contrib, much
 less core, if the code isn't pure BSD license.

Hmm.  Here is the copyright and license portion of the readme...

  COPYRIGHT AND LICENSE

  Copyright (c) 2004-2005 Ralf S. Engelschall [EMAIL PROTECTED]
  Copyright (c) 2004-2005 The OSSP Project http://www.ossp.org/

  This file is part of OSSP uuid, a library for the generation
  of UUIDs which can found at http://www.ossp.org/pkg/lib/uuid/

  Permission to use, copy, modify, and distribute this software for
  any purpose with or without fee is hereby granted, provided that
  the above copyright notice and this permission notice appear in all
  copies.

  THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
  MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
  IN NO EVENT SHALL THE AUTHORS AND COPYRIGHT HOLDERS AND THEIR
  CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
  USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
  OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
  OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
  SUCH DAMAGE.

Reading the postgres license from the faq...

PostgreSQL Data Base Management System

Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group Portions
Copyright (c) 1994-1996 Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement is
hereby granted, provided that the above copyright notice and this paragraph and
the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST
PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN AS IS BASIS, AND
THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

These two licenses appear to be functionally identical.  Both require
notice of the copyright to be included, and both allow use for any purpose.
The wording of the requirement is even nearly identical.

By way of disclaimer, I am not (yet) an attorney.  

 (We used to be a bit
 lax about that, but are trying to clean things up.)  Nathan's comment
 about starting with code that was sorta-BSD-with-advertising alarmed me.

Perhaps i was a bit lax in my wording.  I don't read the license
as requiring any advertising at run time, just as a requirement that
the copyright notice be kept with the source code.  That is, an
identical requirement to the one that postgres itself uses.

-- 
Nathan Wagner

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

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

Re: [HACKERS] Simple tester for MVCC in PostgreSQL

2005-09-06 Thread Martijn van Oosterhout

On Tue, Sep 06, 2005 at 03:51:41PM +, Matt Miller wrote:
 On Tue, 2005-08-30 at 00:56 +0200, Martijn van Oosterhout wrote:
  http://svana.org/kleptog/pgsql/mvcctest.tar.gz
 
 I've started using it in some simple cases and it seems to be a good
 tool.  The feature set looks to me to be a pretty solid core on which to
 build.

Very nice. I too think the base is sufficient for quite complicated
tests. I actually wrote a script which tested all pairs of locks to
ensure they blocked exactly as the documentation said they should. And
it passed.

Thanks for the feedback,
-- 
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.


pgpLnhVggo4be.pgp
Description: PGP signature

Re: [HACKERS] dbt-4 (tpc-app) kit

2005-09-06 Thread Dave Cramer



On 6-Sep-05, at 11:37 AM, Mark Wong wrote:


On Fri, 2 Sep 2005 15:50:25 -0400
Dave Cramer [EMAIL PROTECTED] wrote:




On 2-Sep-05, at 3:38 PM, Mark Wong wrote:



Hi Dave,

Oops, EJB's are distasteful?  My experience in this area is quite
lacking.


Well, I said personally distasteful.


Not that I necessarily want to be 100% strict but the spec
says this needs to conform to WS-I BP 1.0 specification, which I
understand is basically using the SOAP transport?  I just thought it
would be nice to have something that could be plugged into any
application server (Geronimo, JBoss, etc.) with minimal  
configuration

changes and that was where I started.  I don't mind doing away with
the
EJB altogether.  What do you suggest?



I use hibernate, which would allow this to be used without an
application server.
However strictly speaking, many people are going to want to see EJB's
as this is
what the appserver types like to use.



To give you a little better idea of where I'm at, I have most of 7
of 9
interactions implemented, the remaining two I haven't started.  The
driver needs to be expanded to simulate multiple users.  I haven't
started any of the post processing or data collection scripts yet  
and

some of that can be used from our other kits.  My Java programming
style
should probably be reviewed too. ;)



I had a very (very) quick look at your code, one suggestion would be
log4j, (or other configurable logging API ) for your debugging
statements.



Ok, I'll have to learn how to do that.  :)

Ok, I'll make some changes to your code and send you a diff




If I have some time, I'll look at it some more, I've come to like
using the spring framework. This allows something referred to as IOC
(Inversion of Control) Basically, the problem is how to stub test
code in when we have static factory objects, the solution is to build
the application using a container  which specifies the concrete
classes which are instantiated. This would in theory allow us to have
tow versions. One which uses hibernate, and the other which uses
EJB's. So one thing to think about is to code in interfaces more, and
instantiate concrete classes which implement the interface.



That sounds like a very good idea.  I'll probably need some coaching
though...


Yeah, I'll try to find some time to do this as well. At least part of  
it.I've given some thought

as to how to code this in hibernate, it shouldn't be too hard.



Mark

---(end of  
broadcast)---

TIP 4: Have you searched our list archives?

   http://archives.postgresql.org





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

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Tom Lane wrote:
 Barring further changes, we'll have a hard-wired template list for
 8.1 and a real system catalog in 8.2.  So there's a choice now for
 PLs that are not part of the core distribution: do you want to be
 listed in the hard-wired template?

Is there any way to create the language and not use the template (an 
override option of some kind)?

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

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Josh Berkus

Mark,

 I suggest that UUID be recommended in place of SERIAL for certain
 classes of applications, and that it therefore belongs in the core.
 UUID and SERIAL can be used together (although, once you have a UUID,
 it may not be useful to also have a SERIAL).

I think that, if you want to push a refactored UUID type for PostgreSQL 
8.2, that you'd better separate your database design arguments from your 
inclusion arguments.   

For example, you might get my agreement that it would be useful to have a 
UUID as a core type; you would *never* get my agreement to recommend using 
UUID to newbies.   I have seen *far* too many abuses of UUIDs in really 
bad database design.   People who use them should be experienced enough to 
know what they're doing.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(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] 4D Geometry

Chris Traylor wrote:
  Configure options are generally a pain in the neck,

 Granted. Especially, if all the ifdefs start making the source hard
 to read, but they are a viable compile-time way to allow the user to
 make the decision for themselves.

This missing piece of information here is that 98.6% of our users never 
compile the source code, so that decision will have to be made by the 
packager who will always use the option that is acceptable to the 
plurality of the users.  That is why we have removed most 
feature-related compile-time choices and are very hesitant to add new 
ones.

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

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

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Peter Eisentraut [EMAIL PROTECTED] writes:
 Is there any way to create the language and not use the template (an 
 override option of some kind)?

There is deliberately not a way to override (other than using a
different name for the PL).  One of the points of this facility is to
fix up broken PL definitions being imported from old dump files, so 
believing what the CREATE LANGUAGE command says is exactly what we
don't want.

This will definitely be more palatable with a system catalog that you
could alter, of course.  Still, I don't see that there's any big
problem.  For instance, if you think you might want a validator later,
you can set up a no-op validator procedure today, and then the template
doesn't need to change when you make the validator do something.
Similarly, you could future-proof yourself against adding a trusted (or
untrusted) variant by putting in some stubs now.

regards, tom lane

---(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] Mysteriously lost values in nodes

2005-09-06 Thread Alvaro Herrera

On Tue, Sep 06, 2005 at 06:06:49PM +0200, Martijn van Oosterhout wrote:
 [Please CC any replies, thanks]
 
 I added a field to each of Var, Const, FuncExpr and OpExpr which is set
 during parse_expr. But somewhere between the parsing and execution the
 values of these fields get reset back to NULL. But only for FuncExpr
 and OpExpr, for Var and Const it all works as expected.

Did you change the functions in src/backend/nodes/*funcs.c ?  Nodes are
copied using those.  Any time you change the definition of the node, you
need to change its equalfunc, copyfunc, outfunc, and readfunc, where
applicable.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura (Perelandra, CSLewis)

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

Re: [HACKERS] Attention PL authors: want to be listed in template

2005-09-06 Thread Andrew Dunstan




Tom Lane wrote:


For instance, if you think you might want a validator later,
you can set up a no-op validator procedure today, and then the template
doesn't need to change when you make the validator do something.
Similarly, you could future-proof yourself against adding a trusted (or
untrusted) variant by putting in some stubs now.


 



Only necessary for one release, right?

Anyway, clearly there are cases where a validator make no sense or very 
little sense (pl/sh and pl/{j,java} spring to mind).


cheers

andrew

---(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] Attention PL authors: want to be listed in template table?

Andrew Dunstan wrote:
 Anyway, clearly there are cases where a validator make no sense or
 very little sense (pl/sh and pl/{j,java} spring to mind).

PL/sh has a validator. :-)

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

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Tom Lane wrote:
 There is deliberately not a way to override (other than using a
 different name for the PL).  One of the points of this facility is to
 fix up broken PL definitions being imported from old dump files, so
 believing what the CREATE LANGUAGE command says is exactly what we
 don't want.

I don't doubt that, but I just have a stomach ache with this entire 
notion of having a hard-coded exception list of the sort if the user 
requires this, do this instead.  Why don't we just put all PLs that we 
know of into pg_language to begin with, revoke the permissions, and 
just let CREATE LANGUAGE fail when the dump is restored?  (The dump 
should restore the permissions, shouldn't it?)  At least that would let 
you alter the template using existing means.

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

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

   http://archives.postgresql.org

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Peter Eisentraut [EMAIL PROTECTED] writes:
 I don't doubt that, but I just have a stomach ache with this entire 
 notion of having a hard-coded exception list of the sort if the user 
 requires this, do this instead.

I don't see it as an exception list.  The direction I see for this is
that the parameters to CREATE LANGUAGE are obsolete and will eventually
be removed altogether, with CREATE LANGUAGE foo using an existing
template as the only recommended way to do it.

 Why don't we just put all PLs that we 
 know of into pg_language to begin with, revoke the permissions, and 
 just let CREATE LANGUAGE fail when the dump is restored?

To do that we'd have to force an initdb, in which case we might as well
add the proposed pltemplate catalog and have done with it.

I'm entirely willing to add the catalog if people are willing to accept
an initdb now.  As I said earlier, I don't want to mess with adding a
usage privilege just yet, but we could add the catalog with a
nonfunctional ACL column and write that code later.

regards, tom lane

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

Re: [HACKERS] Attention PL authors: want to be listed in template

2005-09-06 Thread Andrew Dunstan




Peter Eisentraut wrote:


Andrew Dunstan wrote:
 


Anyway, clearly there are cases where a validator make no sense or
very little sense (pl/sh and pl/{j,java} spring to mind).
   



PL/sh has a validator. :-)

 



Really? Curious. I must look more closely.

cheers

andrew

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

  http://archives.postgresql.org

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Tom Lane wrote:
 I don't see it as an exception list.  The direction I see for this is
 that the parameters to CREATE LANGUAGE are obsolete and will
 eventually be removed altogether, with CREATE LANGUAGE foo using an
 existing template as the only recommended way to do it.

So your proposal is to enable a new language by doing:

1. register a template
2. activate template using CREATE LANGUAGE (which would copy it to 
pg_language)

How is this different from

1. register language in pg_language without privileges
2. activate language by granting privileges

This already works and uses only well-known concepts.

  Why don't we just put all PLs that we
  know of into pg_language to begin with, revoke the permissions, and
  just let CREATE LANGUAGE fail when the dump is restored?

 To do that we'd have to force an initdb, in which case we might as
 well add the proposed pltemplate catalog and have done with it.

I don't have a strong opinion on initdb, but a difference would be that 
this solution would not *require* an initdb but only offer the 
improvement if initdb were done while continuing to work as before 
without initdb.

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

---(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] Mysteriously lost values in nodes

Martijn van Oosterhout kleptog@svana.org writes:
 grep reveals several places where new nodes are created but rather than
 just changing them all, is there a particular phase where these changes
 are made that I should be looking at?

Grepping for makeNode(OpExpr) might help you.  Offhand I'd finger
eval_const_expressions as the likely culprit.  clauses.c has some other
code you'd better look at too.

Personally, when I want to add a field to a node, I grep for every
reference to one or two of the existing fields to make sure I've found
all the places I need to touch.

regards, tom lane

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

Re: [HACKERS] Attention PL authors: want to be listed in template table?

Peter Eisentraut [EMAIL PROTECTED] writes:
 So your proposal is to enable a new language by doing:

 1. register a template
 2. activate template using CREATE LANGUAGE (which would copy it to 
 pg_language)

That's the long-term idea, yes.

 How is this different from

 1. register language in pg_language without privileges
 2. activate language by granting privileges

Because you can't create a language without first creating the support
procedures, which ordinarily requires having the shared library present.
(This is why I proposed text names for the support procedures in
pltemplate, rather than OID references.)  This is perhaps not an issue
for entries wired in by initdb, but it's definitely an issue for manual
addition of template entries.

Also, ISTM your proposal is to cause CREATE LANGUAGE foo on an
already-existing language to execute GRANT USAGE ON LANGUAGE foo TO PUBLIC
instead, rather than erroring out.  That doesn't seem to pass the
least-surprise test at all.

regards, tom lane

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

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

Re: [HACKERS] Mysteriously lost values in nodes

2005-09-06 Thread Martijn van Oosterhout

On Tue, Sep 06, 2005 at 01:51:25PM -0400, Tom Lane wrote:
 Grepping for makeNode(OpExpr) might help you.  Offhand I'd finger
 eval_const_expressions as the likely culprit.  clauses.c has some other
 code you'd better look at too.

Yeah, eval_const_expressions was the culprit in this case, though I
think operators might have some more.

 Personally, when I want to add a field to a node, I grep for every
 reference to one or two of the existing fields to make sure I've found
 all the places I need to touch.

So there's no shortcut, I'll remember that :)

Thanks for the help,
-- 
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.


pgpULJaurnt0f.pgp
Description: PGP signature

[HACKERS] purge hash table, how to?

2005-09-06 Thread huaxin zhang

i am using postgresql 8.0.3 as a single user by running postgres

I want to purge all contents in the bufferpool, and I did this by
calling InitBufTable(256) (buf_table.c)  after each query. However,
this seems not working for each followup query I still get less disk read
(tracked by smgrread()  in smgr.c) and increased bufferhitcount.

Could anyone tell me which module shall i modify instead?

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

Re: [HACKERS] Simple tester for MVCC in PostgreSQL

On Tue, Sep 06, 2005 at 06:21:11PM +0200, Martijn van Oosterhout wrote:
 On Tue, Sep 06, 2005 at 03:51:41PM +, Matt Miller wrote:
  On Tue, 2005-08-30 at 00:56 +0200, Martijn van Oosterhout wrote:
   http://svana.org/kleptog/pgsql/mvcctest.tar.gz
  
  I've started using it in some simple cases and it seems to be a good
  tool.  The feature set looks to me to be a pretty solid core on which to
  build.
 
 Very nice. I too think the base is sufficient for quite complicated
 tests. I actually wrote a script which tested all pairs of locks to
 ensure they blocked exactly as the documentation said they should. And
 it passed.

Should we add this stuff to the regression tests (probably as a seperate
option since not everyone will want to install perl)?
-- 
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 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] Call for 7.5 feature completion

FWIW, I think a lot of people didn't me too on all the features they
want, so I wouldn't put too much weight on the ranking here...

On Mon, Sep 05, 2005 at 05:43:16PM +0200, Peter Eisentraut wrote:
 Am Freitag, 26. August 2005 01:13 schrieb Alvaro Herrera:
  Or, slightly different, what are people's most wanted features?
 
 For entertainment, here is a summary the most requested features:
 
 1. MERGE command
 
 2. Table partitioning
 
 2. Materialized views
 
 2. Updatable views
 
 5. Index-organized tables, index-only access
 
 6. Recursive queries
 
 6. Window functions
 
 8. Debuggable PL/pgSQL
 
 8. Better bulk load
 
 8. Multimaster replication
 
 8. Database assertions
 
 8. Multi-threaded/process query execution
 
 8. CUBE and ROLLUP
 
 8. Concurrent vacuum
 
 So there is plenty of work left...
 
 -- 
 Peter Eisentraut
 http://developer.postgresql.org/~petere/
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

-- 
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 6: explain analyze is your friend

[HACKERS] count(*) optimization

2005-09-06 Thread huaxin zhang

not sure where to put this. 

I run two queries: 

1. select count(*) from table where indexed_column10;
2. select * from table where indexed_column10;

the indexed column is not clustered at all. I saw from the trace that
both query runs
through index scans on that index and takes the same amount of buffer
hits and disk read. However, shouldn't the optimizer notice that the
first query only needs to look at the indexes
and possibly reduce the amount of buffer/disk visits?

thanks

---(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] count(*) optimization

huaxin... I'll save you the time... see the topic MUCH ADO ABOUT COUNT(*) and ADVANCED INDEX USAGE.
On 9/6/05, huaxin zhang [EMAIL PROTECTED] wrote:
not sure where to put this.I run two queries:1. select count(*) from table where indexed_column10;2. select * from table where indexed_column10;the indexed column is not clustered at all. I saw from the trace that
both query runsthrough index scans on that index and takes the same amount of bufferhits and disk read. However, shouldn't the optimizer notice that thefirst query only needs to look at the indexesand possibly reduce the amount of buffer/disk visits?
thanks---(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] count(*) optimization

2005-09-06 Thread Bruno Wolff III

On Tue, Sep 06, 2005 at 15:21:16 -0400,
  huaxin zhang [EMAIL PROTECTED] wrote:
 not sure where to put this. 
 
 I run two queries: 
 
 1. select count(*) from table where indexed_column10;
 2. select * from table where indexed_column10;
 
 the indexed column is not clustered at all. I saw from the trace that
 both query runs
 through index scans on that index and takes the same amount of buffer
 hits and disk read. However, shouldn't the optimizer notice that the
 first query only needs to look at the indexes
 and possibly reduce the amount of buffer/disk visits?

No, because that isn't true. Whether or not a tuple is visible to the current
transaction isn't stored in indexes. If you have more questions on this, you
should look through the archives before asking them, as this topic has been
discussed numerous times.

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

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

[HACKERS] need info about extensibility in other databases

2005-09-06 Thread Oleg Bartunov


Hi there,

after we have GiST with concurrency and recovery support
(thanks to PosGIS community for support) I'd trying to 
find info about level of extensibility support in 
other major RDBMS's like Relational Extenders of DB2, 
Cartridges in Oracle and (?) in MS SQL.


With current GiST one could develope new datatype, index access methods and
queries and everything will have automatically concurency and recovery
support. New datatype could be not just a subset of existing types, but
completely new.

What's about other RDBMS ?

btw, GiST programming tutorial is available in Russian,
http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html

Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

Re: [HACKERS] uuid type for postgres

I agree with Josh on the UUID type, it gets abused far too often and
(IMHO) isn't widely enough used to belong in the core. Couldn't you just fix the problem in
pguuid rather than write a whole new type?On 9/6/05, Josh Berkus josh@agliodbs.com wrote:
Mark, I suggest that UUID be recommended in place of SERIAL for certain classes of applications, and that it therefore belongs in the core. UUID and SERIAL can be used together (although, once you have a UUID,
 it may not be useful to also have a SERIAL).I think that, if you want to push a refactored UUID type for PostgreSQL8.2, that you'd better separate your database design arguments from yourinclusion arguments.
For example, you might get my agreement that it would be useful to have aUUID as a core type; you would *never* get my agreement to recommend usingUUID to newbies. I have seen *far* too many abuses of UUIDs in really
bad database design. People who use them should be experienced enough toknow what they're doing.JoshJosh BerkusAglio Database SolutionsSan Francisco---(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] uuid type for postgres

On Tue, Sep 06, 2005 at 03:57:55PM -0400, [EMAIL PROTECTED] wrote:
 I agree with Josh on the UUID type, it gets abused far too often

Out of curiosity, how does it get abused?  It doesn't seem to me
that it would be any more prone to abuse than any other type.

 and (IMHO) isn't widely enough used to belong in the core.

Seems like a self-fulfilling cycle.  There isn't one in core
or contrib (pguuid is not in contrib, IIRC), and so one doesn't
get used.  Since no one uses one, it's not in core or contrib.

 Couldn't you just fix the 
 problem in pguuid rather than write a whole new type?

Not sure which you you're addressing here, but I can't fix the license
problem.  Nor can I readily fix the lack of a mac ioctl() to get
the hardware mac address.

In any case, from an economy of effort view, I'd much rather maintain
a postgres extension/interface to an existing uuid library, than fret
the details of writing a uuid library myself.  UUIDs themselves don't
really interest me, I just happen to need one for my application.

I'm basically done.  I'll tar it up and post a link to an alpha version in a
couple of hours.  As far as i can tell, it works fine.  I'm calling it alpha
because i'm not doing proper error checking, and i'm not sure how to log or
throw an error even if i detect one.  Also, I have only compiled it on
Mac OS 10.3, I'll want to test it on my linux box.

-- 
Nathan Wagner

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

[HACKERS] PITR on different hardware

2005-09-06 Thread Rod Taylor

I didn't see anything mentioned in the docs about this, so I'm curious
as to how significant of a change you can make to the hardware or
software configuration for a restore before breaking things.

Secondly, is PostgreSQL smart enough to complain in these cases or will
it be silent and cause unexpected data corruption later on?

Can you go from Sparc on Solaris to Linux on AMD?

How about from UltraSparc IV to UltraSparc III?

-- 


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

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

Re: [HACKERS] uuid type for postgres

On Sep 6, 2005, at 12:57 PM, Jonah H. Harris wrote:On 9/6/05, Josh Berkus josh@agliodbs.com wrote: Mark, I suggest that UUID be recommended in place of SERIAL for certain classes of applications, and that it therefore belongs in the core. UUID and SERIAL can be used together (although, once you have a UUID,  it may not be useful to also have a SERIAL).I think that, if you want to push a refactored UUID type for PostgreSQL8.2, that you'd better separate your database design arguments from yourinclusion arguments. For example, you might get my agreement that it would be useful to have aUUID as a core type; you would *never* get my agreement to recommend usingUUID to newbies.   I have seen *far* too many abuses of UUIDs in really bad database design.   People who use them should be experienced enough toknow what they're doing.I agree with Josh on the UUID type, it gets abused far too often and (IMHO) isn't widely enough used to belong in the core.  Couldn't you just fix the problem in pguuid rather than write a whole new type?This sounds like a strawman argument.  People abuse a lot of types to do a lot of things they shouldn't be doing, adding types to the core isn't really going to change that much one way or the other.If the documentation gives the user a good idea of when to use UUID and when not, I think it would be a good addition.  Worst case, it could become part of contrib, so at least it ships with PostgreSQL with the same license.  The GPL license of pguuid is a show-stopper for some (and seems like a silly choice for what it is and who it's for).-bob

Re: [HACKERS] uuid type for postgres

The only time I've seen someone use UUIDs in PostgreSQL is when they
were converting from SQL Server. I've seen many bad data models
using UUID that could've/should've used normal sequences for
portability.

I look forward to seeing you're code.

Thanks!
On 9/6/05, nathan wagner [EMAIL PROTECTED] wrote:
On Tue, Sep 06, 2005 at 03:57:55PM -0400, [EMAIL PROTECTED] wrote: I agree with Josh on the UUID type, it gets abused far too oftenOut of curiosity, how does it get abused?It doesn't seem to me
that it would be any more prone to abuse than any other type. and (IMHO) isn't widely enough used to belong in the core.Seems like a self-fulfilling cycle.There isn't one in coreor contrib (pguuid is not in contrib, IIRC), and so one doesn't
get used.Since no one uses one, it's not in core or contrib. Couldn't you just fix the problem in pguuid rather than write a whole new type?Not sure which you you're addressing here, but I can't fix the license
problem.Nor can I readily fix the lack of a mac ioctl() to getthe hardware mac address.In any case, from an economy of effort view, I'd much rather maintaina postgres extension/interface to an existing uuid library, than fret
the details of writing a uuid library myself.UUIDs themselves don'treally interest me, I just happen to need one for my application.I'm basically done.I'll tar it up and post a link to an alpha version in a
couple of hours.As far as i can tell, it works fine.I'm calling it alphabecause i'm not doing proper error checking, and i'm not sure how to log orthrow an error even if i detect one.Also, I have only compiled it on
Mac OS 10.3, I'll want to test it on my linux box.--Nathan Wagner---(end of broadcast)---TIP 6: explain analyze is your friend

Re: [HACKERS] purge hash table, how to?

huaxin zhang [EMAIL PROTECTED] writes:
 I want to purge all contents in the bufferpool,

Why do you think that's a good idea?  It certainly won't purge the
kernel's disk caches, so if you're hoping to restore the system to
ground zero this won't do it.

 and I did this by
 calling InitBufTable(256) (buf_table.c)  after each query.

Let's see ... I'd expect that to run out of shared memory pretty soon,
but not till after it's thoroughly corrupted your database ;-)

There is no API exported by bufmgr.c that does what you want.  Something
like a combination of FlushRelationBuffers and DropRelFileNodeBuffers,
but processing a whole database (see also DropBuffers) or the whole buffer
cache, might work.  You can't drop a buffer that some other process has
pinned, however.

regards, tom lane

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

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

Re: [HACKERS] uuid type for postgres

On Tue, Sep 06, 2005 at 03:57:55PM -0400, Jonah H. Harris wrote:
 I agree with Josh on the UUID type, it gets abused far too often and (IMHO) 
 isn't widely enough used to belong in the core.

There is much in PostgreSQL from my perspective that falls under the
category of 'most advanced open source SQL server in the world', and
not at all in the category of 'widely used'. Unless the history of
PostgreSQL isn't to be accepted in terms of direction for PostgreSQL,
I don't see why such a useful building block shouldn't be supported
by the core. It may not be widely used, because it doesn't exist.
Not yet, anyways. pguuid is broken, remember?

Which isn't to say that it should be in the core, or it must be in the
core. It is to say, however, that I don't buy your arguments. Abused how?
How can you judge widely used for something that doesn't exist?

I'm not very newbie oriented. I think I tend to assume that anybody
using PostgreSQL must know what they are doing. This may be very
wrong of me to assume. I assume you all know what you are doing,
for example.

I'm curious as to what sort of abuses you and Josh are speaking about.
I'd particularly like to know if you think I am abusing it, as I don't
believe that I am. I would also be interested in knowing what you felt
would be an abuse with UUID, that wouldn't be an abuse with SERIAL.
Should we tell newbies not to use SERIAL? Perhaps we should. Do we?

 Couldn't you just fix the 
 problem in pguuid rather than write a whole new type?

I'll submit my patches to pguuid once I've had some more production
use of it. For the little bit of code that it was, there were some
pretty bad bugs. It was sometimes crashing, due to what appears to be
a palloc() that was one byte short (it reliably crashed on me, the
line is wrong as written, and after adding + 1, it no longer crashes
at all). The operator definitions weren't correct, and the indices on
columns of the type were not being preferred by the query planner (a
good thing too - if they were used for any operator other than '=',
the errors in the operator definitions would have caused very
unexpected results).

So yes, pguuid can be fixed. I'm not sure that pguuid makes a good
model for a PostgreSQL extension, but it could be resurrected and
used. (I think the project showed no real updates since 2003?)

But - it's GPL, limiting its use with regard to PostgreSQL
distribution, and it requires special compilation, which as Nathan
found out, doesn't work on MacOS.

I think the GPL reason alone is a compelling reason to create a new
extension. Using a more portable UUID base library (either written
from scratch, or re-used from some other place providing a
compatible license) is a compelling reason to create a new
extension.

As to whether it should be in core - I think that a fully functional
module could be widely used, especially in larger systems that are
having difficulty spreading transactions across multiple
machines. This is the domain that UUID shines in. I can write a
transaction at my site without being very worried that it will collide
with a transaction at your site. We don't have to be connected in real
time.

In my choice of use, I'm using them instead of SERIAL columns, as I
wish to have more freedom merging production data with test data.  I
wish to continually import production data into my test environment,
in a single direction. UUID will prevent conflicts from occurring.
SERIAL cannot (although in theory, I could set my copy of the serial
value to 1 billion or something hacky - but that doesn't scale in a
simple fashion to having several test environments). My other
preferred use, is to expose a handle on the data to the world in
select circumstances. I don't want to give them a SERIAL column, as
it lets them be able to predict what else they might have access
to. I'm not using it to secure the data (other routines will do this),
but I am using it to hide the data. Why should people accessing my
system know how many records exist in my tables? Why should they be
able to predict the next value? Why should they be able to relate
the data, or mine my data in the case that I allow read to all?

Yes, I can avoid exposing the UUID/SERIAL in most circumstances. They
can get at most of the data through a name based path. For a few of my
pieces of data, though, I want to provide a secondary means of
accessing the data that does not require a path. Names cause problems,
especially if the names contain UNICODE characters, or if the names
are very long. Giving them a reliably unique handle of fixed length
is highly desirable to me in these circumstances.

Anyways, I'm using a fixed up pguuid right now, and getting along fine.
Nathan, under MacOS, isn't - with or without my patch. And I believe
he stated he had issues with the GPL license.

Josh may be correct, that in terms of a position statement, my
arguments are all over the map, and ignorant of newbies. I'm not a
speaker, or a writer. I'm a technical person

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread David Fetter

On Tue, Sep 06, 2005 at 09:16:13PM +, nathan wagner wrote:
 On Tue, Sep 06, 2005 at 03:57:55PM -0400, [EMAIL PROTECTED] wrote:
  I agree with Josh on the UUID type, it gets abused far too often
 
 Out of curiosity, how does it get abused?  It doesn't seem to me
 that it would be any more prone to abuse than any other type.

A typical abuse, as I have seen it, is for OO coders on the front-end
to turn the entire databse into what amounts to an associative array.
It then becomes somewhere between difficult and impossible to get good
performance, as UUID is the only thing useful as a PK/FK.

  and (IMHO) isn't widely enough used to belong in the core.
 
 Seems like a self-fulfilling cycle.  There isn't one in core or
 contrib (pguuid is not in contrib, IIRC), and so one doesn't get
 used.  Since no one uses one, it's not in core or contrib.

In this case, not having it easy to get to is, IMHO, an *excellent*
idea.  The whole thing is a giant foot-gun.

Just my $.02.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

Re: [HACKERS] PITR on different hardware

Rod Taylor [EMAIL PROTECTED] writes:
 Secondly, is PostgreSQL smart enough to complain in these cases or will
 it be silent and cause unexpected data corruption later on?

It will catch anything that affects the contents or layout of
pg_control, which includes a fair amount of stuff (endianness,
most of the popular compile options, probably alignment).  We
don't really guarantee to catch every possible incompatibility,
however.

 Can you go from Sparc on Solaris to Linux on AMD?

Almost certainly not --- aren't they different endianness?

 How about from UltraSparc IV to UltraSparc III?

Damifino.  How much difference is there between those architectures?

regards, tom lane

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

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

Re: [HACKERS] count(*) optimization

2005-09-06 Thread Chris Browne

[EMAIL PROTECTED] (huaxin zhang) writes:
 not sure where to put this. 

 I run two queries: 

 1. select count(*) from table where indexed_column10;
 2. select * from table where indexed_column10;

 the indexed column is not clustered at all. I saw from the trace
 that both query runs through index scans on that index and takes the
 same amount of buffer hits and disk read. 

 However, shouldn't the optimizer notice that the first query only
 needs to look at the indexes and possibly reduce the amount of
 buffer/disk visits?

No, it shouldn't, because that is NOT TRUE.

Indexes do not have MVCC visibility information stored in them, so
that a query cannot depend on the index to imply whether a particular
tuple is visible or not.  It must read the tuple itself as well.
-- 
output = (cbbrowne @ acm.org)
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
I promise you a police car on every sidewalk.
-- M. Barry Mayor of Washington, DC

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

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

Re: [HACKERS] need info about extensibility in other databases

2005-09-06 Thread Michael Fuhr

On Tue, Sep 06, 2005 at 11:52:18PM +0400, Oleg Bartunov wrote:
 btw, GiST programming tutorial is available in Russian,
 http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html

Cool.  Is an English version in the works?

-- 
Michael Fuhr

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

Re: [HACKERS] Remove xmin and cmin from frozen tuples

On Fri, Sep 02, 2005 at 03:51:15PM -0400, Bruce Momjian wrote:
 One possible solution is to create a phantom cid which represents a
 cmin/cmax pair and is stored in local memory.

If we're going to look at doing that I think it would also be good to
consider including xmin and xmax as well. This might require persisting
to disk, but for transactions that touch a number of tuples it could
potentially be a big win (imagine being able to shrink all 4 fields down
to a single int; a 45% space reduction).
-- 
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 6: explain analyze is your friend

Re: [HACKERS] uuid type for postgres



On Sep 6, 2005, at 2:16 PM, nathan wagner wrote:

On Tue, Sep 06, 2005 at 03:57:55PM -0400, [EMAIL PROTECTED]  
wrote:



I agree with Josh on the UUID type, it gets abused far too often



Out of curiosity, how does it get abused?  It doesn't seem to me
that it would be any more prone to abuse than any other type.



and (IMHO) isn't widely enough used to belong in the core.



Seems like a self-fulfilling cycle.  There isn't one in core
or contrib (pguuid is not in contrib, IIRC), and so one doesn't
get used.  Since no one uses one, it's not in core or contrib.



Couldn't you just fix the
problem in pguuid rather than write a whole new type?



Not sure which you you're addressing here, but I can't fix the license
problem.  Nor can I readily fix the lack of a mac ioctl() to get
the hardware mac address.


It's not that hard to get a MAC address or serial number out of a  
Mac, the problem is I'm not going to bother writing that code for a  
GPL extension to PostgreSQL.  There's a better way, anyhow.  Darwin  
ships with UUID parse/generate/etc functions in its Libc (uuid/ 
uuid.h).


FWIW, that code (the Theodore Ts'o UUID implementation) looks to be  
suitably licensed for PostgreSQL, and it actually it appears that  
someone has already written a PostgreSQL UUID type using this it...  
which is unfortunately LGPL'ed (why?!).


-bob


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

Re: [HACKERS] PITR on different hardware

2005-09-06 Thread Rod Taylor

On Tue, 2005-09-06 at 16:53 -0400, Tom Lane wrote:
 Rod Taylor [EMAIL PROTECTED] writes:
  Secondly, is PostgreSQL smart enough to complain in these cases or will
  it be silent and cause unexpected data corruption later on?
 
 It will catch anything that affects the contents or layout of
 pg_control, which includes a fair amount of stuff (endianness,
 most of the popular compile options, probably alignment).  We
 don't really guarantee to catch every possible incompatibility,
 however.

Okay, that helps reduce what I need to look for anyway.

  How about from UltraSparc IV to UltraSparc III?
 
 Damifino.  How much difference is there between those architectures?

Quite similar. My understand is that US IV's are essentially a dual-core
US III.

-- 


---(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] uuid type for postgres

On Tue, Sep 06, 2005 at 01:49:28PM -0700, David Fetter wrote:
 On Tue, Sep 06, 2005 at 09:16:13PM +, nathan wagner wrote:
  On Tue, Sep 06, 2005 at 03:57:55PM -0400, [EMAIL PROTECTED] wrote:
   I agree with Josh on the UUID type, it gets abused far too often
  Out of curiosity, how does it get abused?  It doesn't seem to me
  that it would be any more prone to abuse than any other type.
 A typical abuse, as I have seen it, is for OO coders on the front-end
 to turn the entire databse into what amounts to an associative array.
 It then becomes somewhere between difficult and impossible to get good
 performance, as UUID is the only thing useful as a PK/FK.

Replace UUID with SERIAL in your above paragraph, and I think your
argument is still true. Perhaps UUID makes it easier, as it crosses
the table boundary - but somebody so inclined, can use *val() to
define their own database wide SERIAL identifier.

I don't see anything intrinisic about SERIAL, UUID, or OO abstracting
techniques that requires people to write inefficient code. People
write inefficient code because they don't know any better. They don't
properly look at how their data will be used, and what path the data
will take to get from the tables on disk, to the user they are
presenting the information to. If they had, in the case you describe,
perhaps they'd be horrified. Or perhaps they would STILL not know
any better. :-)

Technically, the UUID is 4x the size of a SERIAL, or 2x the size of a
SERIAL8. For this cost in terms of efficiency (table size, index size),
you are purchasing the scaleability of being able to, at any point in
the future, more easily (than SERIAL) merge your tables with other
tables, and have a relatively opaque handle to give out to allow
people to uniquely identify a collection of rows. For me, both are
appealing. For others, one or the other may be more appealing.

In terms of portability, perhaps (somebody else made this
arguments). If other systems don't have a UUID type, you end screwed
if you ever wished to ditch PostgreSQL. The reason I don't buy this
argument, though, is that in my current application, I dumped being
able to ditch PostgreSQL a long time ago. There are just too many neat
things I can do with PostgreSQL, that I can't do with MySQL, and that
I either can't, or don't want to do with Oracle. You're stuck with me
now... :-)

If people truly felt this way, there are many PostgreSQL extensions
that should be phased out, rather than introduced.

I'd like to point out the obvious - a UUID is just an identifier. As
it isn't linked to the table, it doesn't require generation to be
server-side. Any application can generate a UUID as two BIGINTs, and
use these two BIGINTs as a primary key, and accomplish the same.
Before I stumbled upon pguuid, and became interested in the rather
impressive extension interfaces provided by PostgreSQL, I was
considering doing this myself. The costs would have been more
complicated queries, and limited practical use from the psql command
line.

I've become comfortable with the concept of a UUID over the last two
or three years, as they have been a requirement for us to use for a
product at my primary place of employment. We use a source management
system with replicas at each site, that is synchronized periodically.
We write software on top of this system to perform automated tasks at
different sites (sometimes executing at the other sites), or that
requires configuration information to be associated with the data
stored in this source management system. In a previous product, we
used names to access the data. This failed horribly when the names
changed. We now frequently, and liberally use the UUID for the
objects. This allows us to associate data with, access or manipulate
the objects at any site, without ANY problem. To go back to a name
based access method seems a step BACKWARDS.

UUID definately has a place. It's only a question of the imagination
of the user as to how abused, or how well used, it is.

I think it deserves a spot in PostgreSQL, and that people would
possibly use it more than they would SERIAL. Eventually, SERIAL wraps
around. So you switch to SERIAL8. At the point that you have SERIAL8,
you aren't worried terribly about disk space, and you realize there is
usually no benefit at all to the numbers being ordered so closely. Why
not pick a scheme that is based on time? Perhaps create a revision
code field to deal with objects created simultaneously from the same
source. And why not identify the source to prevent collisions from
multiple sources? If we go from 8 bytes, to 16 bytes, we can encode
all of this information neatly. Welcome, UUID. :-)

mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring

Re: [HACKERS] Remove xmin and cmin from frozen tuples

2005-09-06 Thread Alvaro Herrera

On Tue, Sep 06, 2005 at 03:58:28PM -0500, Jim C. Nasby wrote:
 On Fri, Sep 02, 2005 at 03:51:15PM -0400, Bruce Momjian wrote:
One possible solution is to create a phantom cid which represents a
cmin/cmax pair and is stored in local memory.
 
 If we're going to look at doing that I think it would also be good to
 consider including xmin and xmax as well.

If you do that, you'll never be able to delete or update the tuple.


 This might require persisting to disk, but for transactions that touch
 a number of tuples it could potentially be a big win (imagine being
 able to shrink all 4 fields down to a single int; a 45% space
 reduction).

Yeah, I've heard about compression algorithms that managed to fit
megabytes of data in 8 bytes and even less.  They were very cool.  No
one managed to write decompression algorithms however.  Imagine a whole
data warehouse could be stored in a single disk block!!  I imagine the
development of decompressors was boycotted by SAN vendors and the like.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Si un desconocido se acerca y te regala un CD de Ubuntu ...
 Eso es ...  Eau de Tux

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

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Greg Stark


[EMAIL PROTECTED] writes:

 In my choice of use, I'm using them instead of SERIAL columns, as I
 wish to have more freedom merging production data with test data.  I
 wish to continually import production data into my test environment,
 in a single direction. UUID will prevent conflicts from occurring.
 SERIAL cannot (although in theory, I could set my copy of the serial
 value to 1 billion or something hacky - but that doesn't scale in a
 simple fashion to having several test environments). 

Just do something like this for every sequence:

  ALTER SEQUENCE foo INCREMENT BY 100

And then choose a particular initial value for each server.

I agree with the others that uuid seems to be overused in lots of cases where
a simple serial would serve just as well.

However I don't see why a good uuid type is any less of a valid addition to
the set of data types than any of the others. inet, macaddr, all the geometric
types, for example. 

Given the 3-phase commit work going into 8.2 (8.1?) it seems like a pretty
fundamental component of that whole ball of wax. A note in the documentation
that it's designed for use as part of a multiple-database system like that
might be helpful.

-- 
greg


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

Re: [HACKERS] uuid type for postgres

[EMAIL PROTECTED] writes:
 Personally, I'm not sure what the big opposition to UUID is all about.

I don't see any big opposition.  People are simply questioning the
idea whether it belongs in core PG.  The reason we don't want to accept
everything-and-the-kitchen-sink in core is that we have only limited
manpower to maintain it.  So you've got to justify that we should spend
our effort here and not elsewhere.  There's a fair amount of nearly
unmaintained cruft in the core distro already (eg, the never-finished
line datatype ... or the entire rtree index module ...) and a datatype
that might be used by only a few people is a likely candidate to become
an unmaintained backwater.  And yet it's hard to get rid of stuff that's
been there awhile.  So one of the questions that's going to be asked is
how useful/popular it's really going to be.

One thing that is raising my own level of concern quite a bit is the
apparent portability issues.  Code that isn't completely portable is a
huge maintainability problem; in particular, stuff that requires
system-dependent behavior used nowhere else in Postgres is a real pain.
It sounds like the UUID code expects to be able to get at the machine's
MAC address, which suggests serious issues in (a) relying on
not-too-standard APIs, (b) possible protection issues (will an
unprivileged process be able to get at the MAC address?), and (c)
ill-defined behavior on machines with more or less than one MAC address.
Not to mention that MAC addresses aren't so unique as all that.

The bottom line is that we're willing to listen, but it's not by any
means a slam dunk to get this into the distribution.

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] Remove xmin and cmin from frozen tuples

On Tue, Sep 06, 2005 at 05:37:06PM -0400, Alvaro Herrera wrote:
 On Tue, Sep 06, 2005 at 03:58:28PM -0500, Jim C. Nasby wrote:
  On Fri, Sep 02, 2005 at 03:51:15PM -0400, Bruce Momjian wrote:
   One possible solution is to create a phantom cid which represents a
   cmin/cmax pair and is stored in local memory.
  
  If we're going to look at doing that I think it would also be good to
  consider including xmin and xmax as well.
 
 If you do that, you'll never be able to delete or update the tuple.

My idea was to use an int to represent combinations of (c|x)(min|max),
probably on a per-table basis. Essentially, it would normalize these
values. I don't see how this would eliminate the ability to update or
delete.

Of course this would actually hurt on tables that had mostly single-row
operations, so it would have to be a table-creation option. I believe it
could substantially reduce the size of tables that don't see a lot of
transactions. It would be good just to have a quick and dirty patch just
to see if this is the case or not.
-- 
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 5: don't forget to increase your free space map settings

Re: [HACKERS] Remove xmin and cmin from frozen tuples

Jim C. Nasby [EMAIL PROTECTED] writes:
 If we're going to look at doing that I think it would also be good to
 consider including xmin and xmax as well.
 
 If you do that, you'll never be able to delete or update the tuple.

 My idea was to use an int to represent combinations of (c|x)(min|max),
 probably on a per-table basis. Essentially, it would normalize these
 values. I don't see how this would eliminate the ability to update or
 delete.

How will other transactions know whether the tuple is good (yet) or not?
How will you recover if the backend that does know this crashes before
transaction end?  How will you lock tuples for update/delete?

regards, tom lane

---(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] uuid type for postgres

On Tue, Sep 06, 2005 at 05:31:43PM -0400, Greg Stark wrote:
 Just do something like this for every sequence:
   ALTER SEQUENCE foo INCREMENT BY 100
 And then choose a particular initial value for each server.

*shudder*

But you are right. That would work. :-)  (I shudder from the maintenance
head-ache - if I re-cloned the test database, and reset the sequence
to the wrong value, I would have overlap)

 I agree with the others that uuid seems to be overused in lots of cases where
 a simple serial would serve just as well.

Yes.

In March I only used serial columns (actually, I originally used the
hacky MySQL auto_increment type before I migrated the database to
PostgreSQL). I found problems under actual usage with regard to
testing, and with exposing the numbers to the users. I started by
introducing the uuid along side the serial column, but shortly after
realized that it was ridiculous. I effectively had two primary keys,
with two unique indexes. Drop the serial column, and I'm left with
one that does everything I want.

I now used mixed serial and uuid. I retained serial columns for space
reasons. Serial makes it more common for some of my table primary key
indexes or even tables themselves (enumerated type tables) to fit in
fewer pages, improving search time. Some tables may cluster rows more
naturally on a serial column (would depend if the uuid type was sorted
by embedded time stamp, or by literal byte array value - memcmp()).

For the objects represented by a collection of rows, that I expose
to external interfaces, however, I only use the uuid.

My point in this long and winded set of paragraphs, is that although I
agree that uuid might be overused in some situations, I think people
may be currently underusing it in others. They're both ways of
generating unique identifiers. Depending on the context, one will be
better than the other. They may both work for most circumstances.

 However I don't see why a good uuid type is any less of a valid
 addition to the set of data types than any of the others. inet,
 macaddr, all the geometric types, for example.

I've never used geometric types, and don't see myself doing so any
time soon. :-)

 Given the 3-phase commit work going into 8.2 (8.1?) it seems like a pretty
 fundamental component of that whole ball of wax. A note in the documentation
 that it's designed for use as part of a multiple-database system like that
 might be helpful.

Yep.

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


---(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] uuid type for postgres

On Tue, Sep 06, 2005 at 05:54:34PM -0400, Tom Lane wrote:
 I don't see any big opposition.  People are simply questioning the
 idea whether it belongs in core PG.  The reason we don't want to accept
 everything-and-the-kitchen-sink in core is that we have only limited
 manpower to maintain it.  So you've got to justify that we should spend
 our effort here and not elsewhere.  There's a fair amount of nearly
 ...
 been there awhile.  So one of the questions that's going to be asked is
 how useful/popular it's really going to be.

Sounds reasonable, and certainly no more than I expected. If Nathan
hadn't raised the issue, it probably would have been a few months
before I raised it myself.

 One thing that is raising my own level of concern quite a bit is the
 apparent portability issues.  Code that isn't completely portable is a
 huge maintainability problem; in particular, stuff that requires
 system-dependent behavior used nowhere else in Postgres is a real pain.
 It sounds like the UUID code expects to be able to get at the machine's
 MAC address, which suggests serious issues in (a) relying on
 not-too-standard APIs, (b) possible protection issues (will an
 unprivileged process be able to get at the MAC address?), and (c)
 ill-defined behavior on machines with more or less than one MAC address.
 Not to mention that MAC addresses aren't so unique as all that.

I'll try to prepare an answer for this. (I started to write a lot of
information - but is it unverified from memory, and perhaps should be
more authoritative before presented as truth)

 The bottom line is that we're willing to listen, but it's not by any
 means a slam dunk to get this into the distribution.

Sounds good.

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


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

Re: [HACKERS] uuid type for postgres

As promised a link to the code.

http://granicus.if.org/~nw/ossp_pg_uuid-0.1.tar.gz

You'll also need Ralf Engelschall's uuid library, which mine is a postgres
interface to.  It's available at

ftp://ftp.ossp.org/pkg/lib/uuid/uuid-1.3.0.tar.gz

It probably has a few warts.  I'm mainly posting it for suggestions,
comments, and so we have something that isn't vaporware to argue about.

:)

-- 
Nathan Wagner

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

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

Re: [HACKERS] uuid type for postgres

On Tue, Sep 06, 2005 at 05:54:34PM -0400, [EMAIL PROTECTED] wrote:
 
 One thing that is raising my own level of concern quite a bit is the
 apparent portability issues.

I can't speak to the portability in general, but there is a PORTING file
in the ossp uuid library that states

OSSP uuid was already written with maximum portability in mind, so
  there should be no great effort required to get it running on any Unix
  platform with a reasonable POSIX API. Additionally, the portability
  was tested by successfully building and running it on the following
  particular Unix platforms (syntax is cpu-os (compiler)):

  alpha-tru644.0 (cc) alpha-tru645.1 (gcc, cc) hppa-hpux11.11 (cc)
ia64-hpux11.23 (cc) ix86-debian2.2 (gcc, icc) ix86-debian3.0 (gcc)
ix86-debian3.1 (gcc) ix86-freebsd4.9 (gcc) ix86-freebsd5.2 (gcc, icc)
ix86-netbsd1.6 (gcc) ix86-qnx6.2 (gcc) ix86-solaris10 (gcc) ix86-unixware7.1.3
(cc) mips64-irix6.5 (gcc) sparc64-solaris8 (gcc, forte) sparc64-solaris9 (gcc)

On my end I managed to compile it with nothing more than a configure,
make, followed by a make install.

 Code that isn't completely portable is a huge maintainability problem; in
 particular, stuff that requires system-dependent behavior used nowhere
 else in Postgres is a real pain. It sounds like the UUID code expects to
 be able to get at the machine's MAC address,

If the mac address isn't available, I believe it falls back on using
a random 47 bit number with the 48th bit set to make the mac address
fall within the multicast mac numberspace.  You could also use a version
4 uuid, or derive a version 3 or 5 uuid from some other source.

The better answer though, is these sort of questions are exactly why
I would prefer to rely on someone else's library.  Just as I basically
trust that the folks maintaining postgres aren't going to munge my tables
and destroy my data if i mess up a transaction and roll it back,
because they've spent time thinking about just that sort of problem, I
also (having worked with the code a bit now) trust the UUID folks
to have thought about just how do we make a unique number without
centralized coordination?  base on the mac address?  what if we
don't have one?  or don't know it for some reason?  I assume here that
the answer they came up with wasn't oh, hell, just return a 42 then.

The point being, that other people have already written a better uuid
library than i am likely to, so, license permitting, let's use it.

 The bottom line is that we're willing to listen, but it's not by any
 means a slam dunk to get this into the distribution.

Fair enough.

Personally, I think it should be a core type, but would be quite happy
if it were in contrib.  At least that way it would save the next guy
from having to hunt around the net.

I guess i'm volunteering to maintain it in contrib.  I'm not certain
if i have the requisite knowledge to maintain it in the core.  While
I could acquire the familiarity if need be, for the next year and nine
months law school is going to take up the bulk of my free time.  And
of course I'll still need time to play around with my ticketing and gis
databases i'm developing.

-- 
Nathan Wagner

---(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] Remove xmin and cmin from frozen tuples

On Tue, Sep 06, 2005 at 06:02:27PM -0400, Tom Lane wrote:
 Jim C. Nasby [EMAIL PROTECTED] writes:
  If we're going to look at doing that I think it would also be good to
  consider including xmin and xmax as well.
  
  If you do that, you'll never be able to delete or update the tuple.
 
  My idea was to use an int to represent combinations of (c|x)(min|max),
  probably on a per-table basis. Essentially, it would normalize these
  values. I don't see how this would eliminate the ability to update or
  delete.
 
 How will other transactions know whether the tuple is good (yet) or not?
 How will you recover if the backend that does know this crashes before
 transaction end?  How will you lock tuples for update/delete?

If the 4 header fields in question were just normalized out, wouldn't
all the semantics continue to work the same? All I'm envisioning is
replacing them in each tuple with a pointer (vis_id) to another
datastore that would be roughly equivalent to:

CREATE TABLE visibility (
vis_id  SERIAL,
xminint,
xmaxint,
cminint,
cmax_xmax   int
)

Of course you wouldn't use an actual table to do this, but hopefully
this clarifies my idea. Any time the backend would update any of those
fields it would now insert a new row into visibility containing the
proper values and use vis_id in the tuples.
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] Remove xmin and cmin from frozen tuples

Jim C. Nasby [EMAIL PROTECTED] writes:
 If the 4 header fields in question were just normalized out, wouldn't
 all the semantics continue to work the same? All I'm envisioning is
 replacing them in each tuple with a pointer (vis_id) to another
 datastore that would be roughly equivalent to:

 CREATE TABLE visibility (
 vis_id  SERIAL,
 xminint,
 xmaxint,
 cminint,
 cmax_xmax   int
 )

 Of course you wouldn't use an actual table to do this, but hopefully
 this clarifies my idea.

Let's see ... replace every tuple access with TWO tuple accesses
... yes, that should improve performance nicely.  And no, I'm not
sure the semantics are the same, particularly w.r.t. atomicity of
state changes.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] uuid type for postgres



On Sep 6, 2005, at 3:06 PM, [EMAIL PROTECTED] wrote:


On Tue, Sep 06, 2005 at 05:54:34PM -0400, Tom Lane wrote:


I don't see any big opposition.  People are simply questioning the
idea whether it belongs in core PG.  The reason we don't want to  
accept

everything-and-the-kitchen-sink in core is that we have only limited
manpower to maintain it.  So you've got to justify that we should  
spend

our effort here and not elsewhere.  There's a fair amount of nearly
...
been there awhile.  So one of the questions that's going to be  
asked is

how useful/popular it's really going to be.



Sounds reasonable, and certainly no more than I expected. If Nathan
hadn't raised the issue, it probably would have been a few months
before I raised it myself.



One thing that is raising my own level of concern quite a bit is the
apparent portability issues.  Code that isn't completely portable  
is a

huge maintainability problem; in particular, stuff that requires
system-dependent behavior used nowhere else in Postgres is a real  
pain.
It sounds like the UUID code expects to be able to get at the  
machine's

MAC address, which suggests serious issues in (a) relying on
not-too-standard APIs, (b) possible protection issues (will an
unprivileged process be able to get at the MAC address?), and (c)
ill-defined behavior on machines with more or less than one MAC  
address.

Not to mention that MAC addresses aren't so unique as all that.



I'll try to prepare an answer for this. (I started to write a lot of
information - but is it unverified from memory, and perhaps should be
more authoritative before presented as truth)


Some modern UUID implementations prefer /dev/urandom or similar to  
the time or MAC address unless you really beg them to give you a  
weaker UUID.


You can take a look at the man page for the Theodore Y. Ts'o  
implementation that is in Darwin's Libc here:
http://developer.apple.com/documentation/Darwin/Reference/ManPages/ 
man3/uuid_generate.3.html


Specifically:

   The uuid_generate function creates a new universally unique   
identifier
   (UUID).   The  uuid  will be generated based on high-quality  
randomness
   from  /dev/urandom,  if  available.   If  it  is  not   
available,  then
   uuid_generate  will use an alternative algorithm which uses  
the current
   time, the local ethernet MAC address (if available),  and   
random  data

   generated using a pseudo-random generator.

The Apache Portable Runtime has a apr_os_uuid_get() that supports two  
flavors of UUID for unix (Linux/Mac OS X uuid_generate and FreeBSD's  
uuid_create, may be available elsewhere), and the UuidCreate API on  
Win32.  apr-util's apr_uuid_get() will use apr_os_uuid_get() if  
available, and otherwise will default to a relatively weak mostly- 
timestamp-based UUID.


It would probably be reasonable and easy to do what Apache does  
here.  A platform UUID implementation, if present, is generally going  
to be better than anything included into PostgreSQL itself.


-bob


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

Re: [HACKERS] uuid type for postgres

Tom, you worded my thoughts much better than I did.

Bob, I too had heard that host-based UUIDs/GUIDs had issues with
uniqueness. I think Microsoft's implementation was hosed and they
ended up eliminating using the MAC completely.

I'll check out the code get back.
On 9/6/05, Bob Ippolito [EMAIL PROTECTED] wrote:
On Sep 6, 2005, at 3:06 PM, [EMAIL PROTECTED] wrote: On Tue, Sep 06, 2005 at 05:54:34PM -0400, Tom Lane wrote: I don't see any big opposition.People are simply questioning the
idea whether it belongs in core PG.The reason we don't want to accept everything-and-the-kitchen-sink in core is that we have only limited manpower to maintain it.So you've got to justify that we should
spend our effort here and not elsewhere.There's a fair amount of nearly ... been there awhile.So one of the questions that's going to be asked is how useful/popular it's really going to be.
Sounds reasonable, and certainly no more than I expected. If Nathan hadn't raised the issue, it probably would have been a few months before I raised it myself.
One thing that is raising my own level of concern quite a bit is the apparent portability issues.Code that isn't completely portable is a huge maintainability problem; in particular, stuff that requires
system-dependent behavior used nowhere else in Postgres is a real pain. It sounds like the UUID code expects to be able to get at the machine's MAC address, which suggests serious issues in (a) relying on
not-too-standard APIs, (b) possible protection issues (will an unprivileged process be able to get at the MAC address?), and (c) ill-defined behavior on machines with more or less than one MAC
address. Not to mention that MAC addresses aren't so unique as all that. I'll try to prepare an answer for this. (I started to write a lot of information - but is it unverified from memory, and perhaps should be
more authoritative before presented as truth)Some modern UUID implementations prefer /dev/urandom or similar tothe time or MAC address unless you really beg them to give you aweaker UUID.You can take a look at the man page for the Theodore Y. Ts'o
implementation that is in Darwin's Libc here:http://developer.apple.com/documentation/Darwin/Reference/ManPages/man3/uuid_generate.3.html
Specifically:The uuid_generate function creates a new universally uniqueidentifier(UUID).
Theuuidwill be generated based on high-qualityrandomnessfrom/dev/urandom,ifavailable.
Ifitisnotavailable,thenuuid_generatewill use an alternative algorithm which usesthe currenttime, the local ethernet MAC address (if available),andrandomdatagenerated using a pseudo-random generator.
The Apache Portable Runtime has a apr_os_uuid_get() that supports twoflavors of UUID for unix (Linux/Mac OS X uuid_generate and FreeBSD'suuid_create, may be available elsewhere), and the UuidCreate API on
Win32.apr-util's apr_uuid_get() will use apr_os_uuid_get() ifavailable, and otherwise will default to a relatively weak mostly-timestamp-based UUID.It would probably be reasonable and easy to do what Apache does
here.A platform UUID implementation, if present, is generally goingto be better than anything included into PostgreSQL itself.-bob

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Greg Stark


[EMAIL PROTECTED] writes:

 Eventually, SERIAL wraps around. So you switch to SERIAL8. At the point that
 you have SERIAL8, you aren't worried terribly about disk space, and you
 realize there is usually no benefit at all to the numbers being ordered so
 closely.

a) Except for trivially small applications you are _always_ worried terribly
   about disk space. The more money you spend on high end raid arrays the
   *more* you're worried about an incremental drain on performance.

b) You only have to go to SERIAL8 for the few tables that actually have that
   many records. You may well still have SERIAL data types in 99% of your
   tables.

c) You underestimate the cost of the added space. Don't forget it's not just
   an extra few bytes in the primary key. It's also quadrupling the size of
   your primary key index (doubling over bigint).

   Most importantly it's also adding a few extra bytes to every foreign key
   column in every table that references that primary key. For very relational
   databases with tables doing things like many-to-many joins or having 4+
   foreign key referencing columns increasing all those integers to be 16
   bytes increases the size of your database *immensely*.

 Why not pick a scheme that is based on time? Perhaps create a revision code
 field to deal with objects created simultaneously from the same source. And
 why not identify the source to prevent collisions from multiple sources? If
 we go from 8 bytes, to 16 bytes, we can encode all of this information
 neatly. Welcome, UUID. :-)

Welcome to exactly the abuse that people are fearing if it were included as a
built-in type.

Personally I don't think the argument that some people might abuse it is a
good reason not to provide it. There are uses for which it's very effective --
even necessary. And I think it's important enough for the people that need it
that it should be considered a fundamental database feature these days. 

The people who will abuse it (like yourself, imho) will always exist and the
more powerful the tool the bigger the holes in their feet. Shouldn't stop us
from having powerful tools when we need them.

-- 
greg


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

   http://archives.postgresql.org

Re: [HACKERS] [ADMIN] How to determine date / time of last postmaster restart

adey [EMAIL PROTECTED] writes:
 Please let me know if there is a way to determine when the Postmaster was
 last restarted?

The last postmaster start time, or the last database reset?  These are
not the same if any backends have crashed since the postmaster started.
For determining stats lifespan I think you need the latter.

Offhand I think the file timestamp of $PGDATA/postmaster.opts would do
for the postmaster start time, and postmaster.pid for the other (I think
postmaster.pid is updated during a reset).

PG 8.1 will have a function to return postmaster start time, but not
database reset time.  I wonder if this is misdefined --- if you are
trying to measure database uptime, the last reset would be more
appropriate to track.

regards, tom lane

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

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Josh Berkus

Bob, People,

Let me clarify my stance here, because it seems to be getting 
misrepresented.

Mark (and Nathan) pushed at repaired UUID type for possible inclusion in 
the core PostgreSQL distribution.  I'm not opposed to that, provided that 
the portability, licensing, and bugs are worked out.  Why not?  We have 
ipv6 data types, after all.

However, Mark went on to suggest that we should recommend UUID over SERIAL 
in the docs, and that we could consider dropping SERIAL entirely in favor 
of UUID:

---quoth Mark--
I suggest that UUID be recommended in place of SERIAL for certain
classes of applications, and that it therefore belongs in the core.
UUID and SERIAL can be used together (although, once you have a  
UUID, it may not be useful to also have a SERIAL).
-

This was what I objected to; I believe that the use-case for UUIDs is 
actually quite narrow and assert that it's a very bad idea to promote them 
to most users.   

I have a problem with SERIAL abuse, too.   In general, new DB designers 
have come to increasingly believe that surrogate keys (SERIALs, UUIDs, 
hash ids etc.) are an intrinsic part of the relational model and a 
requirement for all tables.   Terrible database designs have resulted, 
chock full of tables which lack real keys and cannot be normalized.

UUIDs tend to encourage this sort of behavior even more than SERIALs, not 
because of any intrinsic quality in the data type, but because much of the 
literature on the subject treats them like some kind of universal object 
identifier and not distinguishing servers, relations, or real keys.  

To repeat, though, this isn't a reason to keep them out of core, but it 
*is* a reason not to throw them at newbies as the holy grail of row 
identifiers.

For my part, I generally push implementing the UUID concept in a better way 
that keeps server, table, and surrogate keys atomic (and thus more useful 
and easier to debug).

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Re: [HACKERS] uuid type for postgres



On Sep 6, 2005, at 6:02 PM, Josh Berkus wrote:


Bob, People,

Let me clarify my stance here, because it seems to be getting
misrepresented.

Mark (and Nathan) pushed at repaired UUID type for possible  
inclusion in
the core PostgreSQL distribution.  I'm not opposed to that,  
provided that
the portability, licensing, and bugs are worked out.  Why not?  We  
have

ipv6 data types, after all.

However, Mark went on to suggest that we should recommend UUID over  
SERIAL
in the docs, and that we could consider dropping SERIAL entirely in  
favor

of UUID:

---quoth Mark--
I suggest that UUID be recommended in place of SERIAL for certain
classes of applications, and that it therefore belongs in the core.
UUID and SERIAL can be used together (although, once you have a
UUID, it may not be useful to also have a SERIAL).
-

This was what I objected to; I believe that the use-case for UUIDs is
actually quite narrow and assert that it's a very bad idea to  
promote them

to most users.


I agree with you (Josh) completely, which is why I said:
If the documentation gives the user a good idea of when to use UUID  
and when not, I think it would be a good addition.


.. the fact that the use-cases are narrow was implicit :)

Everything else I talked about was just implementation details.   
Summary: there are (several) UUID implementations out there that are  
appropriately licensed and easy enough to use, and a lot of OSes ship  
with pretty good implementations already.  Creating a decent UUID  
type should be relatively trivial, as far as those things go.


-bob


---(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] uuid type for postgres

On Tue, Sep 06, 2005 at 06:02:50PM -0700, Josh Berkus wrote:
 However, Mark went on to suggest that we should recommend UUID over SERIAL 
 in the docs, and that we could consider dropping SERIAL entirely in favor 
 of UUID:
 ---quoth Mark--
 I suggest that UUID be recommended in place of SERIAL for certain
 classes of applications, and that it therefore belongs in the core.
 UUID and SERIAL can be used together (although, once you have a  
 UUID, it may not be useful to also have a SERIAL).
 -
 This was what I objected to; I believe that the use-case for UUIDs is 
 actually quite narrow and assert that it's a very bad idea to promote them 
 to most users.   

Ahhh... :-)

I intended the word 'certain' to be emphasized in some way, rather
than dropped. There are genuine uses for UUIDs. I didn't intend for
everybody to pull out their database definitions and change them all to
use UUID instead of SERIAL.

Although - I don't think really bad things would happen if people did.
They would simply be making a non-optimal choice (abusing the type?).
Certainly nothing they weren't capable of before this particular
capability were to arrive. :-)

 I have a problem with SERIAL abuse, too.   In general, new DB designers 
 have come to increasingly believe that surrogate keys (SERIALs, UUIDs, 
 hash ids etc.) are an intrinsic part of the relational model and a 
 requirement for all tables.   Terrible database designs have resulted, 
 chock full of tables which lack real keys and cannot be normalized.

 UUIDs tend to encourage this sort of behavior even more than SERIALs, not 
 because of any intrinsic quality in the data type, but because much of the 
 literature on the subject treats them like some kind of universal object 
 identifier and not distinguishing servers, relations, or real keys.  

 To repeat, though, this isn't a reason to keep them out of core, but it 
 *is* a reason not to throw them at newbies as the holy grail of row 
 identifiers.

I agree. Although I lost it on the cannot be normalized. I'm assuming
there are designs you have seen much worse than the ones I have seen. :-)

 For my part, I generally push implementing the UUID concept in a better way 
 that keeps server, table, and surrogate keys atomic (and thus more useful 
 and easier to debug).

My eyes are glazing over a bit on this last one. Atomic?

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


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

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Josh Berkus

Mark,

 I agree. Although I lost it on the cannot be normalized. I'm assuming
 there are designs you have seen much worse than the ones I have seen.
 :-)

Mostly it's the problem of tables that don't have a real key, only a 
surrogate key.  How do you know what's a duplicate?

  For my part, I generally push implementing the UUID concept in a
  better way that keeps server, table, and surrogate keys atomic (and
  thus more useful and easier to debug).

 My eyes are glazing over a bit on this last one. Atomic?

Sure.  What's a UUID, after all?   It's three pieces of information:
1) A server or database instance identifier
2) A table identifier
3) A row identifier

e.g.:
chayote.sf.agliodbs.com | public.customers | 4271

Generally, it's pretty easy to just grab these 3 pieces of information 
separately and pass them with the data when you want to transmit between 
servers.  If there are space/overhead considerations, you can put them 
into a reversable hash.  I've never understood the complex measures which 
application developers take to create universal IDs.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Dann Corbit

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
 [EMAIL PROTECTED] On Behalf Of Josh Berkus
 Sent: Tuesday, September 06, 2005 6:40 PM
 To: [EMAIL PROTECTED]
 Cc: Bob Ippolito; [EMAIL PROTECTED];
pgsql-hackers@postgresql.org;
 nathan wagner
 Subject: Re: [HACKERS] uuid type for postgres

 Mark,

  I agree. Although I lost it on the cannot be normalized. I'm
assuming
  there are designs you have seen much worse than the ones I have
seen.
  :-)

 Mostly it's the problem of tables that don't have a real key, only a
 surrogate key.  How do you know what's a duplicate?

   For my part, I generally push implementing the UUID concept in a
   better way that keeps server, table, and surrogate keys atomic
(and
   thus more useful and easier to debug).

  My eyes are glazing over a bit on this last one. Atomic?

 Sure.  What's a UUID, after all?   It's three pieces of information:
 1) A server or database instance identifier
 2) A table identifier
 3) A row identifier

 e.g.:
 chayote.sf.agliodbs.com | public.customers | 4271

 Generally, it's pretty easy to just grab these 3 pieces of information
 separately and pass them with the data when you want to transmit
between
 servers.  If there are space/overhead considerations, you can put them
 into a reversable hash.  I've never understood the complex measures
which
 application developers take to create universal IDs.

What if you don't want to move anything from one system to another, but
you would like to do joins across many servers on a network?

It happens all the time. If you have a built in primary key for related
tables and they are guaranteed unique, then you have a leg up.

It is not unusual for the same schema to be used in many different
regional offices, but to have site specific data in each area.

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

   http://archives.postgresql.org

Re: [HACKERS] uuid type for postgres

On Tue, Sep 06, 2005 at 06:40:27PM -0700, josh@agliodbs.com wrote:
 
 Sure.  What's a UUID, after all?   It's three pieces of information:
 1) A server or database instance identifier
 2) A table identifier
 3) A row identifier
 
 e.g.:
 chayote.sf.agliodbs.com | public.customers | 4271
 
 Generally, it's pretty easy to just grab these 3 pieces of information 
 separately and pass them with the data when you want to transmit between 
 servers.  If there are space/overhead considerations, you can put them 
 into a reversable hash.

Take a look at the version 3 or version 5 UUIDs.  They essentially do this.
The hash isn't reversable, but rather recreatable.  Seems that if it
were reversable, it would be compression, not a hash.  Anyway.

You're assuming though that you want to leak this information.  If you do,
or you don't care, go ahead with the v3 or v5 uuid creator.

Or use v1 if you want mac/time based uuids, or v4 for random uuids.

The convenient thing about using the uuid library is that all of these
mechanisms result in the same type, and are thus compatible.

 I've never understood the complex measures which 
 application developers take to create universal IDs.

Different applications have different goals.  Unguessability might
be important in some contexts.

-- 
Nathan Wagner

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

   http://archives.postgresql.org

Re: [HACKERS] inet increment with int

2005-09-06 Thread Andrew - Supernews

On 2005-09-06, Patrick Welche [EMAIL PROTECTED] wrote:
 Now with:

 test=# select '192.168.0.0/24'::inet + 1;
 ERROR:  Trying to increment a network (192.168.0.0/24) rather than a host

What possible justification is there for this behaviour?

 test=# select '192.168.0.1/24'::inet + -1;
 ERROR:  Increment returns a network (192.168.0.0/24) rather than a host

While I suspect I know where this idea came from, it is equally boneheaded
since it is making completely unwarranted assumptions about how inet
values are being used.

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

---(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] 4D Geometry

2005-09-06 Thread Tim Allen


Peter Eisentraut wrote:

Chris Traylor wrote:


Configure options are generally a pain in the neck,


Granted. Especially, if all the ifdefs start making the source hard
to read, but they are a viable compile-time way to allow the user to
make the decision for themselves.



This missing piece of information here is that 98.6% of our users never 
compile the source code, so that decision will have to be made by the 
packager who will always use the option that is acceptable to the 
plurality of the users.  That is why we have removed most 
feature-related compile-time choices and are very hesitant to add new 
ones.


The other point to be made is that every such compile-time option 
bifurcates the postgres universe into two mutually-incompatible 
sections. The postgres community has enough of a challenge supporting 
the one version of the database - there's no point in making things harder.


Tim

--
---
Tim Allen  [EMAIL PROTECTED]
Proximity Pty Ltd  http://www.proximity.com.au/

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

Re: [HACKERS] uuid type for postgres

2005-09-06 Thread Paul Ramsey


Just an FYI:

We also ended up rolling our own uuid type, against libuuid.  It  
seems that uuid is a widespread enough concept that implementors  
*will* be asked to support it, moderately often.  We *could* roll our  
own (were capable), others are not so lucky, and would have to point  
out the lack of a uuid type as a limitation of pgsql.  Which is too  
bad, given how relatively simple they are.


That said:

- linking against libuuid is fine for a contrib/ extension, but no  
good for a built-in type.  A real uuid would have to do a proper  
independent implementation of uuid creation within pgsql.
- we cannot snarf libuuid code, it is LGPL (though perhaps the author  
would re-license. if that is the *only* objection, it is well worth  
asking)


I think having a built-in uuid type is something that a large number  
of people will use.  Whether they use it will or badly is not our  
problem.  It is possible to build crappy databases with all the types  
that already exist, adding uuid is hardly going to bring the walls  
down.  Having uuid removes another excuse for people not doing a  
pgsql implementation.


I am not sure if I heard clearly from the core team that a self- 
contained, BSD-licensed uuid would be accepted(able)?  If so, I'll  
contact the libuuid author about a re-license (shortest path from A  
to B).


P.

On 6-Sep-05, at 6:50 AM, nathan wagner wrote:




I have been in need of a uuid type and ran across the pguuid download
by Xiongjian (Mike) Wang.  This wasn't really useful to me for two
reasons: first, it is GPLed and I would prefer a more liberal license,
secondly, it didn't compile cleanly on Mac OS 10.3, due to lack of a
SIOCGETIFHWADDR (? i think, i can get the exact name if you want it)
ioctl() under darwin.

While I could dike out the code that calls it, that seems like a  
suboptimal

solution.  So after a bit of poking around the interweb i ran across
Ralf Engelschall's ossp uuid library.  This compiled with minimal
effort on mac os.  Some reading, and an evening later, i've made
a server plugin with supporting SQL that implements an 'ossp_uuid'
type.

Now i have four questions:

1: Is it feasible for this to be included in the contrib section of
the regular download?  The uuid library is a notice of copyright  
style

license, and I am willing to put my own code into the public domain.

2: Would just calling the type 'uuid' be better than 'ossp_uuid'?   
It's

certainly a nicer name.

3: Would it be possible to include such a type as a postgres extension
to the usual SQL types.  It seems to me that having an officially
supported type would be better than a user contributed type on the  
grounds

that you could then rely on it being avaiable if postgres was.
In particular, installing it as an extension would require the  
cooperation

of the DBA, which may be infeasible in some environments.

--
Nathan Wagner






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

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

Re: [HACKERS] uuid type for postgres

Paul Ramsey [EMAIL PROTECTED] writes:
 I am not sure if I heard clearly from the core team that a self- 
 contained, BSD-licensed uuid would be accepted(able)?

I don't think any of the other core members weighed in on this thread,
so speaking strictly for myself: BSD license is an issue, and
portability is an issue.  I've heard enough to convince me that the
demand exists, but the legal and technical issues remain.

regards, tom lane

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

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

Re: [HACKERS] need info about extensibility in other databases

2005-09-06 Thread Oleg Bartunov


On Tue, 6 Sep 2005, Michael Fuhr wrote:


On Tue, Sep 06, 2005 at 11:52:18PM +0400, Oleg Bartunov wrote:

btw, GiST programming tutorial is available in Russian,
http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html


Cool.  Is an English version in the works?



I was optimistic when I thought it should be easy, but now I see
how it's  difficult for the author :) Seems, better just write
english version, but I'm not sure when I will have spare time.
If somebody wants to translate we're open for questions.





Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

Re: [HACKERS] uuid type for postgres

On Tue, Sep 06, 2005 at 08:40:08PM -0700, [EMAIL PROTECTED] wrote:
 
 - linking against libuuid is fine for a contrib/ extension, but no  
 good for a built-in type.  A real uuid would have to do a proper  
 independent implementation of uuid creation within pgsql.

Why?

I'm not sure what the default build instructions are, but i've got
the following on my linux box...

granicus:~% ldd /usr/bin/postgres
linux-gate.so.1 =  (0x00368000)
libpam.so.0 = /lib/libpam.so.0 (0x00311000)
libssl.so.5 = /lib/libssl.so.5 (0x009b3000)
libcrypto.so.5 = /lib/libcrypto.so.5 (0x00507000)
libkrb5.so.3 = /usr/lib/libkrb5.so.3 (0x003ab000)
libz.so.1 = /usr/lib/libz.so.1 (0x00c55000)
libreadline.so.5 = /usr/lib/libreadline.so.5 (0x00c6a000)
libtermcap.so.2 = /lib/libtermcap.so.2 (0x00dfa000)
libcrypt.so.1 = /lib/libcrypt.so.1 (0x009f7000)
libresolv.so.2 = /lib/libresolv.so.2 (0x0014e000)
libnsl.so.1 = /lib/libnsl.so.1 (0x00a62000)
libdl.so.2 = /lib/libdl.so.2 (0x00c4f000)
libm.so.6 = /lib/libm.so.6 (0x00c29000)
libc.so.6 = /lib/libc.so.6 (0x00afd000)
libcom_err.so.2 = /lib/libcom_err.so.2 (0x0038e000)
libgssapi_krb5.so.2 = /usr/lib/libgssapi_krb5.so.2 (0x0099a000)
libk5crypto.so.3 = /usr/lib/libk5crypto.so.3 (0x00483000)
/lib/ld-linux.so.2 (0x00adf000)
libkrb5support.so.0 = /usr/lib/libkrb5support.so.0 (0x0041f000)

Quite a list.  I wonder what readline is doing there.

It would appear that linking against libraries is just fine, I don't
see that uuid is any different.

 - we cannot snarf libuuid code, it is LGPL (though perhaps the author  
 would re-license. if that is the *only* objection, it is well worth  
 asking)

Not sure what uuid library you were using, but the one i used
is not LGPL.  I posted earlier the copyright and license portion of
the readme.  No relicensing would be necessary.

-- 
Nathan Wagner

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

Re: [HACKERS] Remove xmin and cmin from frozen tuples

On Tue, Sep 06, 2005 at 07:02:20PM -0400, Tom Lane wrote:
 Jim C. Nasby [EMAIL PROTECTED] writes:
  If the 4 header fields in question were just normalized out, wouldn't
  all the semantics continue to work the same? All I'm envisioning is
  replacing them in each tuple with a pointer (vis_id) to another
  datastore that would be roughly equivalent to:
 
  CREATE TABLE visibility (
  vis_id  SERIAL,
  xminint,
  xmaxint,
  cminint,
  cmax_xmax   int
  )
 
  Of course you wouldn't use an actual table to do this, but hopefully
  this clarifies my idea.
 
 Let's see ... replace every tuple access with TWO tuple accesses
 ... yes, that should improve performance nicely.  And no, I'm not
 sure the semantics are the same, particularly w.r.t. atomicity of
 state changes.

Well, like I said, I'm not envisioning using a table to store that info.
Since we'd be storing 4 fixed-length fields, you wouldn't need to
actually store vis_id per entry, just use the offset into the page.
Assuming you use one 'slot' to store the id of the first set, you could
store 511 tuples per page, which doesn't sound very bad.

But this is why I'm hoping a quick patch could be done so that this
could be tested.
-- 
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 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Attention PL authors: want to be listed in template table?

2005-09-06 Thread Thomas Hallgren


Tom,
I assume that the path of the shared library will be somehow relative to 
the GUC dynamic_library_path? If not, the lanlibrary should be changed 
to $libdir/libpljava. That requires that PL/Java is installed within 
the PostgreSQL distribution.


I also assume that the handler name can be prefixed with a schema name? 
All PL/Java support functions reside in the sqlj schema.


If my assumptions are correct, then please add:

{ java, true, sqlj.java_call_handler, NULL,
libpljava },
{ javaU, false, sqlj.javau_call_handler, NULL,
libpljava },

The validator for PL/Java will have to wait until 8.2.

Regards,
Thomas Hallgren

Tom Lane wrote:

I've committed changes to implement the cut-down form of this proposal:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg01185.php
discussed here:
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00138.php

Barring further changes, we'll have a hard-wired template list for 8.1
and a real system catalog in 8.2.  So there's a choice now for PLs that
are not part of the core distribution: do you want to be listed in the
hard-wired template?

The advantages of being listed are:

1. Reloading old dumps that involve your language should be easier,
since problems like version-specific paths to shared libraries will
go away.

2. Your PL support functions will end up in pg_catalog instead of
the public schema, which will please people who'd like to remove public
from their installations.

The main disadvantage I can see is that you won't easily be able to
change your PL creation parameters (eg, add a validator function)
over the lifespan of the 8.1 release.  So depending on your development
roadmap you might think this a bad tradeoff.

If you want to be listed, let me know.  What I need to know to list you
is values for this table:

typedef struct
{
char   *lanname; /* PL name */
boollantrusted;  /* trusted? */
char   *lanhandler;  /* name of handler function */
char   *lanvalidator;/* name of validator function, or NULL */
char   *lanlibrary;  /* path of shared library */
} PLTemplate;

As examples, the entries for the core PLs are

{ plpgsql, true, plpgsql_call_handler, plpgsql_validator,
  $libdir/plpgsql },
{ pltcl, true, pltcl_call_handler, NULL,
  $libdir/pltcl },
{ pltclu, false, pltclu_call_handler, NULL,
  $libdir/pltcl },
{ plperl, true, plperl_call_handler, plperl_validator,
  $libdir/plperl },
{ plperlu, false, plperl_call_handler, plperl_validator,
  $libdir/plperl },
{ plpythonu, false, plpython_call_handler, NULL,
  $libdir/plpython },


regards, tom lane

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

   http://archives.postgresql.org





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

Re: [HACKERS] [ADMIN] How to determine date / time of last postmaster restart