[HACKERS] 'configure --disable-shared' and 'make check'

[Albe Laurenz]

I notice that when I run 'make check' on a
statically linked HEAD, it fails during
'createlang' with

== installing plpgsql ==
ERROR:  could not access file "$libdir/plpgsql": No such file or
directory
command failed:
"/postgres/cvs/pgsql/src/test/regress/./tmp_check/install//magwien/postg
res-8.2.a/bin/psql" -X -c "CREATE LANGUAGE \"plpgsql\"" "regression"

This is not a problem of the regression test
since I see the same behaviour when I do it manually.

Yours,
Laurenz Albe

---(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] [COMMITTERS] pgsql: Remove completed TODO items: < * -Make postmater

[Teodor Sigaev]

< * -Add fillfactor to control reserved free space during index creation


GIN doesn't use fillfactor yet...

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[Gregory Stark]

[EMAIL PROTECTED] writes:

> I have the impression I'm not being heard.
> 
> *I* control the MAC address assignment for all of *MY* units.

No, you're missing the point. How does that help *me* avoid collisions with
your UUIDs? UUIDs are supposed to be unique period, not just unique on your
database.

If all you want is unique number generation in your database then you can just
use sequences and they'll take a lot less space and perform much better.
(16-byte foreign keys throughout the whole database, *shudder*)

The reason to use UUIDs is when you want to have unique identifiers that you
can send outside the database and know they won't conflict with other unique
identifiers generated elsewhere.


Really this whole debate only reinforces the point that there isn't a single
way of doing UUID generation. There are multiple libraries out there each with
pros and cons. It makes more sense to have multiple pgfoundry UUID generating
modules.



-- 
greg


---(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] [PATCHES] Patch for UUID datatype (beta)

[Jeremy Drake]

On Wed, 20 Sep 2006, Gregory Stark wrote:

>
> [EMAIL PROTECTED] writes:
>
> > I have the impression I'm not being heard.
> >
> > *I* control the MAC address assignment for all of *MY* units.
>
> No, you're missing the point. How does that help *me* avoid collisions with
> your UUIDs? UUIDs are supposed to be unique period, not just unique on your
> database.


I must jump in with my amusement at this whole conversation.  I just
looked up the standard (http://www.ietf.org/rfc/rfc4122.txt) and it
includes this abstract:

Abstract

   This specification defines a Uniform Resource Name namespace for
   UUIDs (Universally Unique IDentifier), also known as GUIDs (Globally
   Unique IDentifier).  A UUID is 128 bits long, and can guarantee
   uniqueness across space and time.  UUIDs were originally used in the
   Apollo Network Computing System and later in the Open Software
   Foundation's (OSF) Distributed Computing Environment (DCE), and then
   in Microsoft Windows platforms.


It then goes on to detail multiple versions of them which are generated in
various ways.  But they are all called UUID, and thus should all be
UNIVERSALLY unique, and the statement "can guarantee uniqueness across
space and time" should apply equally to all versions, as it is an absolute
statement.  So perhaps the ietf have been drinking the kool-aid (or
whatever), or perhaps you plan to use your databases in multiple
universes.  But the standard seems to make the whole discussion moot by
guaranteeing all UUIDs to be unique across space and time.  Or am I
misreading that?

So I guess I am just ROFL at the fact that people can't seem to get their
definition of "universe" quite straight.  Either the UUID is misnamed, or
some people here are vastly underestimating the scope of the universe, or
perhaps both.  Or perhaps it's just that it's 3am and this thing seems
extraordiarily funny to me right now ;)


-- 
Menu, n.:
A list of dishes which the restaurant has just run out of.

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

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

Re: [HACKERS] minor feature request: Secure defaults during

[Markus Schaber]

Hi, Martijn,

Martijn van Oosterhout wrote:

> Someone writing SECURITY DEFINER in their function definition has to be
> understood to know what they're doing. After all, "chmod +s" doesn't
> reset global execute permissions either, because that would be far too
> confusing. The same applies here IMHO. The whole point is to be
> executed by other users.

But I have the possibility to "chmod a-x" before "chmod +s" the file.

Maybe we should add "[NOT] PUBLICLY EXCUTABLE"[1] keywords to CREATE
FUNCTION, with the default being the current behaviour for now (possibly
configurable). Add an appropriate note in the docs for CREATE FUNCTION,
so users are informed about the security implications.


[1] alternative spelling proposals: "[NOT] PUBLIC" or "PUBLIC | PRIVATE"
Thinking about it, "CREATE [OR REPLACE] [PUBLIC|PRIVATE] FUNCTION ..."
seems the "most sexy" variant in my eyes.


HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Truncation of email subject lines

[Markus Schaber]

Hi, Bruce,

Bruce Momjian wrote:

> Should I try hacking my mail reader to prevent this?  I think I see
> where it is happening in the code.

AFAICT, the wrapping of long header lines by indentation (as your mailer
seems to do) is RFC conformant, so I think it is majordomo who needs the
fix.

The only possible bug I could see is that your mailer implements the
indentation incorrectly (tabs vs. spaces, incorrect level of indentation
etc.).

Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Truncation of email subject lines

[Markus Schaber]

Hi, Bruce,

Markus Schaber wrote:

>> Should I try hacking my mail reader to prevent this?  I think I see
>> where it is happening in the code.
> 
> AFAICT, the wrapping of long header lines by indentation (as your mailer
> seems to do) is RFC conformant, so I think it is majordomo who needs the
> fix.
> 
> The only possible bug I could see is that your mailer implements the
> indentation incorrectly (tabs vs. spaces, incorrect level of indentation
> etc.).

I just re-read http://www.faqs.org/rfcs/rfc2822.html and it seems that
the first character of the continuation has to be a space or tab, so I
assume that your mailer wors correctly.

Btw, header lines have a limit of 998 characters, so, for longer
subjects, wrapping them is a must. :-)

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



signature.asc
Description: OpenPGP digital signature

Re: [pgsql-www] [HACKERS] Developer's Wiki

[Markus Schaber]

Hi, Martijn,

Martijn van Oosterhout wrote:

> 2. I can see the official todo list being in CVS, which gives it all
> the access protection it needs. A wiki todo list can stay where it is,
> just that it's not official.
> 
> [I've just made a reference to the TODO list in CVS from the wiki, that
> should help].

Maybe you should rename the public writable Wiki page list to Wishlist
instead of Todo, to make the difference more explicit.

Thanks,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] [PATCHES] Include file in regress.c

[Magnus Hagander]

> > if I look into my cvs repository directory, it shows only
> gram.y,v,
> > with gram.c,v in Attic - which seems to make sense. Must be my
> client
> > that's gone crazy. In fact, mmy output ends up as:
> >
> > Index: src\backend\parser/gram.c
> >
> ===
> > RCS file:
> > c:/prog/cvsrepo/pgsql/pgsql/src/backend/parser/Attic/gram.c,v
> > retrieving revision 2.90
> > diff -c -r2.90 gram.c
> > *** src\backend\parser/gram.c   7 May 1999 01:22:54 -
> 2.90
> > --- src\backend\parser/gram.c   14 Sep 2006 06:10:08 -
> >
> >
> > So I guess the question is down to why the hell it's looking at
> the
> > attic file at all. When I run cvs update on it, I get things
> like:
> 
> Hmm, maybe you did a "cvs add" for that file at some point? 

I very much doubt that. Especially since it asks me to do that whenever
I 'cvs diff'.

> What does your CVS/Entries file look for that dir?

It does contain both gram.c and gram.y. They look just the same (except
for version and date, of course). I don't know how it got there ;-) Is
it safe to just remove that?


//Magnus


---(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] minor feature request: Secure defaults during

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 11:59:52AM +0200, Markus Schaber wrote:
> But I have the possibility to "chmod a-x" before "chmod +s" the file.
> 
> Maybe we should add "[NOT] PUBLICLY EXCUTABLE"[1] keywords to CREATE
> FUNCTION, with the default being the current behaviour for now (possibly
> configurable). Add an appropriate note in the docs for CREATE FUNCTION,
> so users are informed about the security implications.

If you're that paranoid, start a transaction, create the function,
revoke the permissions and then commit. Then no-one else will see the
function before you've set the permissions the way you want.

I agree that maybe being able to specify it during function creation
would be nice, but it's not like it's impossible now.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[HACKERS] pg_upgrade: downgradebility

[Zdenek Kotala]

I discussed with Gavin about "pg_downgrade" process. I think that it 
should be much dangerous and more complex problem than upgrade. Some 
operation on the new system should makes downgrade impossible ...


My experience with database upgrades is that downgrade is requested only 
if there some show stopper. Upgrade process is performed on test machine 
at first time to avoid any problems.


My first question is how important is downgrade for You and Your customers?


And second is how to verify that downgrade is possible?

Following article describes how to downgrade oracle: 
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10763/downgrade.htm
The main problem is when new functionality is used for example in the 
PL/SQL or new data type is used in the table definition. Oracle has 
compatible parameter which disable new features. I think that there is 
not way how to secure this in postgres now? And it should be big change 
in the code. Any suggestion?




Zdenek

---(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] pg_upgrade: downgradebility

[Andrew Sullivan]

On Wed, Sep 20, 2006 at 12:54:14PM +0200, Zdenek Kotala wrote:
> My first question is how important is downgrade for You and Your customers?
> 
> 
> And second is how to verify that downgrade is possible?

Well, one way to do it is to set up a Slony replica using the older
version of the software.  So, if you've upgraded to 8.1.x, you
replicate to an old 8.0.x back end as well.  If 8.1 doesn't work for
you, you just MOVE everything back to the 8.0.x back end, and you're
golden.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Information security isn't a technological problem.  It's an economics
problem.
--Bruce Schneier

---(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: [pgsql-www] [HACKERS] Developer's Wiki

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 12:28:54PM +0200, Markus Schaber wrote:
> Maybe you should rename the public writable Wiki page list to Wishlist
> instead of Todo, to make the difference more explicit.

Hmm, all the stuff there now does refer to things that are on the TODO
list (I think). So it's not wishlist at all, it's the *detail* that's
unoffical.

But you're right, it'd probably be a good idea to make a section for
absolutly wishlist stuff. I just can't think of any right now, the TODO
list is quite extensive.

In any case I've altered the wording a bit to make the distinction a
bit clearer.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] polite request about syntax

[Andrew Dunstan]

Jeremy Drake wrote:
> On Tue, 19 Sep 2006, Alvaro Herrera wrote:
>
>> Jeremy Drake wrote:
>>
>> > I have found the same thing with the type "timestamp without time
>> zone".
>> > The verbosity of type names seems rather extreme.
>>
>> Then use simply "timestamptz" (with TZ) or "timestamp" (without).
>
> Didn't know about these, learn something new every day I guess.  I know
> that double did not work due to the countless times I forget the
> "precision" :)

then use float8.

for a list of all the builtin aliases see
http://www.postgresql.org/docs/current/static/datatype.html

cheers

andrew



---(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] pg_upgrade: downgradebility

[Gavin Sherry]

On Wed, 20 Sep 2006, Andrew Sullivan wrote:

> On Wed, Sep 20, 2006 at 12:54:14PM +0200, Zdenek Kotala wrote:
> > My first question is how important is downgrade for You and Your customers?
> >
> >
> > And second is how to verify that downgrade is possible?
>
> Well, one way to do it is to set up a Slony replica using the older
> version of the software.  So, if you've upgraded to 8.1.x, you
> replicate to an old 8.0.x back end as well.  If 8.1 doesn't work for
> you, you just MOVE everything back to the 8.0.x back end, and you're
> golden.

Well, I think that people who really want downgrade in such a tool are
those for which slony replication is just not an option. That is, data in
the range of hundreds of gigabytes. Using slony to upgrade is often not
practical either.

I wonder if pg_upgrade could be designed in such a way that upgrade is the
same as downgrade from a development point of view. That is, the tool can
change the system from one binary format to another.

Thanks,

Gavin

---(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] Release notes

[Bruce Momjian]

Thanks, done.

---

Alvaro Herrera wrote:
> Jim C. Nasby wrote:
> > On Tue, Sep 19, 2006 at 09:37:39AM -0400, Bruce Momjian wrote:
> > > Jim C. Nasby wrote:
> > > > On Mon, Sep 18, 2006 at 06:47:02PM -0400, Bruce Momjian wrote:
> > > > > > Both this and pg_prepared_statements are very useful for pooled
> > > > > > connection setups."
> > > > > > 
> > > > > > Should read "Both of these are very useful..."
> > > > > 
> > > > > I don't think I can't change that because they are two separate bullet
> > > > > items.
> > > >  
> > > > Except it's refering to both items. Maybe the two items should just be
> > > > combined into one?
> > > 
> > > Please post some combined wording, but I am afraid it will be too
> > > complicated to merge them.
> > 
> > - Add pg_prepared_statements and pg_cursors system views to show prepared
> > statements and open cursors.
> > 
> > Both of these are very useful for pooled connection setups.
> 
> - Add pg_prepared_statements ...
> 
> - Add pg_cursors ...
>   This, and pg_prepared_statements above, are both useful for ...
> 
> -- 
> Alvaro Herrerahttp://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[Markus Schaber]

Hi, Mark,

[EMAIL PROTECTED] wrote:

> The versions that include a MAC address, time, and serial number for
> the machine come pretty close, presuming that the user has not
> overwritten the MAC address with something else. It's unique at
> manufacturing time.

Not even that is guaranteed. I remember that, about 8 years ago, me and
a co-student bought a cheap "network starting kit" each, containing two
network kards and a crossover cable.

Now, it turned out, that the first cards in both packages had the same
mac address, and the second cards as well, so we could not network
together using proper cabling and a hub.

Luckily, the mac address was flashable in an eeprom, and so my friend
"fixed" his hards with those from two 10 MBit Coax cards we had
abandoned in favour of the new twisted pair network.

AFAIR, in the end it turned out that the whole charge of cards was
manufactured this way. Officially, it was a bug in the eeprom content
generating software, but there were rumours that the manufacturer wanted
to avoid paying the registration fees for the mac address ranges...


Just gettin' off topic,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] pg_upgrade: downgradebility

[Andrew Sullivan]

On Wed, Sep 20, 2006 at 09:28:42PM +1000, Gavin Sherry wrote:
> 
> Well, I think that people who really want downgrade in such a tool are
> those for which slony replication is just not an option. That is, data in
> the range of hundreds of gigabytes. Using slony to upgrade is often not
> practical either.

Yes, I figured as much.  I was just suggesting that people who
_really_ need the fault tolerance of being able to fall back do have
an option, though it's probably somewhat painful to use.  (You can
use Slony for hundreds of gigs, although if the throughput is high
enough, it might be tricky to get started.)

> I wonder if pg_upgrade could be designed in such a way that upgrade is the
> same as downgrade from a development point of view. That is, the tool can
> change the system from one binary format to another.

I guess the question is whether there is any case where you can map
an old version of some feature into some number of more-granular new
versions, but going the other way is impossible.  I know that sounds
all hand-wavy.  The analogy I'm thinking of is cases I've worked on
where protocols change: the old protocol has one flag that can
happily be mapped into, say, three in the new system.  But in the new
system, you can have just one of those flags by itself, and there's
no obvious way to preserve that data when moving back to the old
protocol.  If we never have that sort of case with the binary
formats, then what you propose ought to work.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

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

Re: [HACKERS] pg_upgrade: downgradebility

[Zdenek Kotala]

Gavin Sherry wrote:

On Wed, 20 Sep 2006, Andrew Sullivan wrote:


On Wed, Sep 20, 2006 at 12:54:14PM +0200, Zdenek Kotala wrote:

My first question is how important is downgrade for You and Your customers?


And second is how to verify that downgrade is possible?

Well, one way to do it is to set up a Slony replica using the older
version of the software.  So, if you've upgraded to 8.1.x, you
replicate to an old 8.0.x back end as well.  If 8.1 doesn't work for
you, you just MOVE everything back to the 8.0.x back end, and you're
golden.


Well, I think that people who really want downgrade in such a tool are
those for which slony replication is just not an option. That is, data in
the range of hundreds of gigabytes. Using slony to upgrade is often not
practical either


I agree with Gavin, Slony need a lot of extra resources. It is similarly 
to use pg_dump for upgrade/downgrade.



I wonder if pg_upgrade could be designed in such a way that upgrade is the
same as downgrade from a development point of view. That is, the tool can
change the system from one binary format to another.


The main problem is that newer format has more functionality that older. 
There is not possible perform downgrade if some create store procedure 
with SQL command which is not supported in the older version. Downgrade 
must check it but is it possible to perform 100% check now (without 
postgres code change)?



Zdenek


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

Re: [HACKERS] Opinion wanted on UUID/GUID datatype output formats.

[Tom Dunstan]

devdb=# select * from tbluuid;
pk|
--+
 6b13c5a1afb4dcf5ce8f8b4656b6c93c |
 01e40a79b55b6e226bffb577e960453d |
(2 rows)
The UUID standards define a single perfectly clear format, and the one 
you show is not it.



I was wondering if we want to have a formatting function to be able
to provide other common formats of the uuid/guid?
If you stick to the standard format, I don't think that will be 
necessary.


+1. For people that care about the non-standard MSSQL format, they can
easily create their own function that will wrap it in {}.


Having been reading through this thread, I was about to make the above 
points, but was glad to see that I was beaten to it.


The dashless format is neither standards compliant nor compatible with 
other databases that have uuid functions (notably MS SQL Server and 
MySQL), nor with microsoft tools where they're used frequently. 
(ignoring the {} wrapping stuff which is trivial).


If we add a UUID type to core, I think that a vast majority of the 
people who are going to want to use it out there will be expecting the 
standard format with dashes. And asking them to put a formatting 
function into every query is beyond horrific.


If we want a general raw hex type then let's call it something else, 
because calling it UUID will just confuse people. Everyone else follows 
the standard on this; we should too.


Tom

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

Re: guc comment changes (was Re: [HACKERS] Getting a move on for 8.2

[Zdenek Kotala]

Tom Lane wrote:

Peter Eisentraut <[EMAIL PROTECTED]> writes:
That does not mean that the patch is bad, and I certainly support the 
feature change.  But I can't efficiently review the patch.  If someone 
else wants to do it, go ahead.


I've finally taken a close look at this patch, and I don't like it any
more than Peter does.  The refactoring might or might not be good at its
core, but as presented it is horrid.  As just one example, it replaces one
reasonably well-commented function with three misnamed, poorly commented
functions.  In place of


Thanks Tom for your time to look on the code and for your feedback. It 
is very useful for me.


Thanks Zdenek

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

  http://archives.postgresql.org

Re: [HACKERS] Opinion wanted on UUID/GUID datatype output formats.

[Gevik Babakhani]

> The dashless format is neither standards compliant nor compatible with 
> other databases that have uuid functions (notably MS SQL Server and 
> MySQL), nor with microsoft tools where they're used frequently. 
> (ignoring the {} wrapping stuff which is trivial).
> 
> If we add a UUID type to core, I think that a vast majority of the 
> people who are going to want to use it out there will be expecting the 
> standard format with dashes. And asking them to put a formatting 
> function into every query is beyond horrific.
> 
> If we want a general raw hex type then let's call it something else, 
> because calling it UUID will just confuse people. Everyone else follows 
> the standard on this; we should too.

Agreed to all above. The formatting issues are all handled in the patch.

Regards,
Gevik



---(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] pdfs of the conference

[Matthew T. O'Connor]

Walter Cruz wrote:

The larger version is only hidden from everyone :)

http://people.planetpostgresql.org/mha/uploads/photo/conf/conference_group.jpg 



Very cool, I was hoping someone would post this.  Any chance we can get 
a list of names to go with the faces?




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

[HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Gevik Babakhani]

I would like to work on the domain casting problem. I have spent
sometime in order to understand how this whole domain handling works
when it comes to casting and I think I understand why this cannot be
fixed in isolation as Tom has described in:

http://archives.postgresql.org/pgsql-hackers/2006-05/msg00190.php

Perhaps I am way off but I am starting to think we need to handle
domains in more organized and consistent way in order to avoid bugs like
this.


First I would like to know how PG's code looked like without the
domains. I went searching in the release notes and I found the following
regarding the domains:

7.3 : Add domain support (Rod)
7.3.3   : Fix planner's selectivity estimation functions to handle domains
properly
7.4 : Add check constraints for domains (Rod)
: Improve automatic type casting for domains (Rod, Tom)
7.4.12  : Properly check DOMAIN constraints for UNKNOWN parameters in
prepared statements (Neil)
8.0.1   : Make ALTER TABLE ADD COLUMN enforce domain constraints in all
cases
8.0.7   : Properly check DOMAIN constraints for UNKNOWN parameters in
prepared statements (Neil)
8.1.4   : Properly check DOMAIN constraints for UNKNOWN parameters in
prepared statements (Neil)


I need to go back and see where and how the domains are handled in a
global sense. Then I hope I can gather enough information to be able to
submit a coherent proposal.

If you have any thoughts you would like to share about this, please let
me know.

Regards,
Gevik.




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

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[Harald Armin Massa]

Mark,A model that intended to try and guarantee uniqueness would provide aUUID generation service for the entire host, that was not specific to
any application, or database, possibly accessible via the loopbackaddress. It would ensure that at any given time, either the time isnew, or the sequence is new for the time. If computer time ever wentbackwards, it could keep the last time issued persistent, and
increment from this point forward through the clock sequence valuesuntil real time catches up. An alternative would be along the lines ofa /dev/uuid device, that like /dev/random, would be responsible foroutputting unique uuid values for the system. Who does this? Probably
nobody. I'm tempted to implement it, though, for my uses. :-)That is an excellent summary. There is just one wrong assumption in it:>Probably nobody. Within win32 there is an API call, which provides you with an GUID / UUID with to my knowledge exactly the features you are describing. win32 is installed on some computers. So for PostgreSQL on win32 the new_guid() you describe in detail would be quite simple to implement:
 a call to CoCreateGuid.The challenging part is: I use PostgreSQL in a mixed environment. And Linux i.e. does not provide CoCreateGuid. That's why I am voting to have it in PostgreSQL :)
Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.

Re: [PATCHES] [HACKERS] Incrementally Updated Backup

[Simon Riggs]

> On Tue, 2006-09-19 at 12:13 -0400, Tom Lane wrote:

> Also, I'm not sold that the concept is even useful.  Apparently the idea
> is to offload the expense of taking periodic base backups from a master
> server, by instead backing up a PITR slave's fileset --- which is fine.

Good. That's the key part of the idea and its a useful one, so I was
looking to document it for 8.2

I thought of this idea separately, then, as usual, realised that this
idea has a long heritage: Change Accumulation has been in production use
with IMS for at least 20 years. 

> But why in the world would you want to stop the slave to do it?  ISTM
> we would want to arrange things so that you can copy the slave's files
> while it continues replicating, just as with a standard base backup.

You can do that, of course, but my thinking was that people would regard
the technique as "unsupported", so I added a quick flag as a prototype.

On Tue, 2006-09-19 at 12:13 -0400, Tom Lane wrote:

> This patch has obviously been thrown together with no thought and even
> less testing.  It breaks the normal case (I think the above if-test is
> backwards), and I don't believe that it works for the advertised purpose
> either (because nothing gets done to force a checkpoint before aborting,
> thus the files on disk are not up to date with the end of WAL).

Yes, it was done very quickly and submitted to ensure it could be
considered yesterday for inclusion. It was described by me as rushed,
which it certainly was because of personal time pressure yesterday: I
thought that made it clear that discussion was needed. Heikki mentions
to me it wasn't clear, so those criticisms are accepted.

On Tue, 2006-09-19 at 16:05 +0100, Heikki Linnakangas wrote: 
> Simon Riggs wrote:
> > +
> > + if (startupAfterRecovery)
> > + ereport(ERROR,
> > + (errmsg("recovery ends normally with startup_after_recovery=false")));
> > +
> 
> I find this part of the patch a bit ugly. 

Me too.



Overall, my own thoughts and Tom's and Heikki's comments indicate I
should withdraw the patch rather than fix it. Patch withdrawn.

Enclose a new doc patch to describe the capability, without s/w change.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com
Index: doc/src/sgml/backup.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.86
diff -c -r2.86 backup.sgml
*** doc/src/sgml/backup.sgml	16 Sep 2006 00:30:11 -	2.86
--- doc/src/sgml/backup.sgml	20 Sep 2006 12:43:55 -
***
*** 1137,1142 
--- 1150,1197 
 

  
+   
+Incrementally Updated Backups
+ 
+   
+incrementally updated backups
+   
+ 
+   
+change accumulation
+   
+ 
+
+ Restartable Recovery can also be utilised to offload the expense of
+ taking periodic base backups from a main server, by instead backing
+ up a Standby server's files.  This concept is also generally known as 
+ incrementally updated backups, log change accumulation or more simply,
+ change accumulation.
+
+ 
+
+ If we take a backup of the server files whilst a recovery is in progress,
+ we will be able to restart the recovery from the last restartpoint. 
+ That backup now has many of the changes from previous WAL archive files,
+ so this version is now an updated version of the original base backup.
+ If we need to recover, it will be faster to recover from the 
+ incrementally updated backup than from the base backup.
+
+ 
+
+ To make use of this capability you will need to set up a Standby database
+ on a second system, as described in . By
+ taking a backup of the Standby server while it is running you will
+ have produced an incrementally updated backup. Once this configuration
+ has been implemented you will no longer need to produce regular base 
+ backups of the Primary server: all base backups can be performed on the 
+ Standby server. If you wish to do this, it is not a requirement that you
+ also implement the failover features of a Warm Standby configuration,
+ though you may find it desirable to do both.
+
+ 
+   
+ 

 Caveats
  
***
*** 1287,1292 
--- 1342,1355 
 really offers a solution for Disaster Recovery, not HA.

  
+
+ When running a Standby Server, backups can be performed on the Standby
+ rather than the Primary, thereby offloading the expense of
+ taking periodic base backups. (See 
+ )
+
+ 
+ 

 Other mechanisms for High Availability replication are available, both
 commercially and as open-source software.  

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

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[mark]

On Wed, Sep 20, 2006 at 05:04:00AM -0400, Gregory Stark wrote:
> [EMAIL PROTECTED] writes:
> > I have the impression I'm not being heard.
> > *I* control the MAC address assignment for all of *MY* units.
> No, you're missing the point. How does that help *me* avoid collisions with
> your UUIDs? UUIDs are supposed to be unique period, not just unique on your
> database.

As you already said, they can't be. I don't see how random is better than
unique by intent (MAC address).

> If all you want is unique number generation in your database then
> you can just use sequences and they'll take a lot less space and
> perform much better.  (16-byte foreign keys throughout the whole
> database, *shudder*)

I want unique number generation from several separate databases, and
I don't like the idea of maintaining complicated SERIAL ranges, or using
one of the increment by X, offset Y techniques. Too hard.

> The reason to use UUIDs is when you want to have unique identifiers that you
> can send outside the database and know they won't conflict with other unique
> identifiers generated elsewhere.

If you don't control the factors that influence the UUID generation, this
is a cross your fingers type of merge. Random numbers might collide.
Shared MAC address might collide. Not controlling the time source might
collide. Although it will probably work, if I know my domain, if I know
what will need to be merged, I can ensure that they can be merged.

> Really this whole debate only reinforces the point that there isn't
> a single way of doing UUID generation. There are multiple libraries
> out there each with pros and cons. It makes more sense to have
> multiple pgfoundry UUID generating modules.

Exactly. If I lead you to the impression that I want UUIDv1 in core, this
was not the intent. What I intend to say is that different people want
different implementations, and one of the most useful versions, in my
opinion, is difficult to implement portably.

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] pdfs of the conference

[Stijn Vanroye]

Matthew T. O'Connor schreef:

Walter Cruz wrote:

The larger version is only hidden from everyone :)

http://people.planetpostgresql.org/mha/uploads/photo/conf/conference_group.jpg 
 



Very cool, I was hoping someone would post this.  Any chance we can get 
a list of names to go with the faces?


Yeah, then we can see if the people actually look as intelligent as they 
sound ;)


But seriously, it would be nice to be able to visualise the people who's 
  mail you are reading/responding to. No actual value in this, just fun...


Regards, Stijn.

---(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] system cache and buffer cache

[Simon Riggs]

On Tue, 2006-09-19 at 14:59 +0100, Heikki Linnakangas wrote:
> Praveen Kumar N wrote:

> > how about system cache? Can we control the size of system cache?
> 
> It's in backend-private memory. I don't remember how it's sized.

It's fixed size: syscache caches a predefined set of catalog info.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

Re: [HACKERS] pdfs of the conference

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Stijn Vanroye
> Sent: 20 September 2006 14:48
> To: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] pdfs of the conference
> 
> Matthew T. O'Connor schreef:
> > Walter Cruz wrote:
> >> The larger version is only hidden from everyone :)
> >>
> >> 
> http://people.planetpostgresql.org/mha/uploads/photo/conf/conf
> erence_group.jpg 
> >> 
>  ference_group.jpg> 
> >>
> > 
> > Very cool, I was hoping someone would post this.  Any 
> chance we can get 
> > a list of names to go with the faces?
> 
> Yeah, then we can see if the people actually look as 
> intelligent as they 
> sound ;)
> 
> But seriously, it would be nice to be able to visualise the 
> people who's 
>mail you are reading/responding to. No actual value in 
> this, just fun...

I'm not sure many of us could name everyone.

Here's a starter though:

The guy with the PostgreSQL sign round his neck is Devrim Gunduz. The
guy holding one up at the back is Chris Browne. On the front row theres
Josh Berkus in the middle, Peter Eisentraut to the right, and Gavin
Sherry on the far right. Bruce is behind Gavin in the light blue shirt,
next to Tatsuo Ishii and Alvaro. Tom is one row from the back, in the
middle with the grey-brown shirt on, and to the left of him is D'arcy
(with the hat) and then Magnus Hagander, Thomas Hallgren and Neil
Clifford.

Regards Dave.

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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[Tom Dunstan]

[EMAIL PROTECTED] wrote:

Really this whole debate only reinforces the point that there isn't
a single way of doing UUID generation. There are multiple libraries
out there each with pros and cons. It makes more sense to have
multiple pgfoundry UUID generating modules.


Exactly. If I lead you to the impression that I want UUIDv1 in core, this
was not the intent. What I intend to say is that different people want
different implementations, and one of the most useful versions, in my
opinion, is difficult to implement portably.


Actually, you could do it very portably, at the cost of a minute or so's 
worth of configuration. Simply have a GUC variable called, say, 
uuid_mac_address. Then the person who gets a box of dud NICs or who, 
like me, has a virtual server somewhere without a true ethernet port 
visible to the operating system, can easily set it. No cross-platform 
code, no requirement to build a third party module in contrib (at least 
not for v1 uuids).


I actually DO think that we should have at least one default generation 
routine in core, even if the above idea doesn't float and it's just v4 
random numbers. If we advertise that we have uuids, people will not 
expect to have to install a contrib module just to get some values 
generated. The SQL server function newsequentialid() which gives v1 
uuids, sort of, is ONLY available as a default value for a column, you 
can't use it in normal expressions (figure that out). So people clearly 
will expect to be able to generate these at the database level.


Using either v1s as configured above or v4s, there's no portability 
issue. Indeed MS SQL Server has a both available (newsequentialid() and 
newid()). And sufficient documentation should allow people to make their 
minds up regarding what their needs are. If they really want funky v3 
namespace ones then they can install a contrib, no problem with that.


Cheers

Tom

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

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

[Tom Lane]

Jeremy Drake <[EMAIL PROTECTED]> writes:
> I must jump in with my amusement at this whole conversation.  I just
> looked up the standard (http://www.ietf.org/rfc/rfc4122.txt) and it
> includes this abstract:

>A UUID is 128 bits long, and can guarantee
>uniqueness across space and time.

The only meaningful word in that claim is "can".   Which boils down to
"if everybody always follows best practices and no failures ever occur,
maybe they're really unique".  We already know that two of the more
critical assumptions embedded in those best practices (unique MAC
addresses and always-correct system clocks) are seriously flawed in
the real world.

To see just how much of the kool-aid that RFC's authors have been
drinking, note that their "sample implementation" in Appendix A
implements the unique node identifier as ... a random number.
So much for guaranteed uniqueness.

regards, tom lane

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

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

Re: [HACKERS] pdfs of the conference

[Dave Page]

 

> -Original Message-
> From: Dave Cramer [mailto:[EMAIL PROTECTED] 
> Sent: 20 September 2006 15:40
> To: Dave Page
> Subject: Re: [HACKERS] pdfs of the conference
> 
> 
> > The guy with the PostgreSQL sign round his neck is Devrim 
> Gunduz. The
> > guy holding one up at the back is Chris Browne. On the front row  
> > theres
> > Josh Berkus in the middle, Peter Eisentraut to the right, and Gavin
> > Sherry on the far right. Bruce is behind Gavin in the light blue  
> > shirt,
> > next to Tatsuo Ishii and Alvaro. Tom is one row from the 
> back, in the
> > middle with the grey-brown shirt on, and to the left of him 
> is D'arcy
> > (with the hat) and then Magnus Hagander, Thomas Hallgren and Neil
> > Clifford.
> 
> I believe you mean Neil Conway ?

Err, yes. Sorry Neil. Neil Clifford is someone else entirely.

Regards, Dave.

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

   http://archives.postgresql.org

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Tom Lane]

Gevik Babakhani <[EMAIL PROTECTED]> writes:
> First I would like to know how PG's code looked like without the
> domains.

IIRC, as far as the datatype coercion and operator/function resolution
code were concerned, the domain patch basically consisted of dropping
getBaseType() calls in at a bunch of choke points, so that these
routines all treat a domain the same as its base type.

I was never real happy about this, mainly because it adds extra syscache
lookups that buy you nothing when you're not using domains.

What would be nice is to revert all that, and instead have domain
creation insert explicit cast entries between a domain and its base type
into pg_cast, so that domains don't require any special-case code in
this part of the system.  The problem with that glib answer is that
maintaining anything like the current behavior would seem to require
that we abandon the current principle that we consider only one-step
cast pathways when trying to match arguments to functions.  (For
instance, a varchar value can be passed to a text-taking function
because varchar->text is an implicit cast according to pg_cast.  What
of a domain over varchar?)  And allowing multi-step casts to be chosen
automatically is unbelievably scary --- I think it would basically
destroy the system's ability to resolve overloaded functions at all,
because you can get from any type to any other if you allow enough cast
steps.  (The unreasonably large number of implicit casts to text aren't
helping any here :-()

So the hard part of this doesn't really require any understanding of
code at all.  What we need is a proposal for an algorithm that loosens
the casting rules "just enough" to make explicit pg_cast entries for
domains work the way we would like them to, without wholesale breakage
of situations that have nothing to do with domains.  See
http://developer.postgresql.org/pgdocs/postgres/typeconv.html

Rereading what I just wrote, it might be as simple as allowing a
two-step cast in certain cases, only if the first step is a domain to
base type coercion (which we assume would be specially marked in
pg_cast).  But the devil is in the details ... and anyway there might
be a cleaner approach than that.

regards, tom lane

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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Andrew Dunstan]

Tom Lane wrote:

So the hard part of this doesn't really require any understanding of
code at all.  What we need is a proposal for an algorithm that loosens
the casting rules "just enough" to make explicit pg_cast entries for
domains work the way we would like them to, without wholesale breakage
of situations that have nothing to do with domains.  See
http://developer.postgresql.org/pgdocs/postgres/typeconv.html

Rereading what I just wrote, it might be as simple as allowing a
two-step cast in certain cases, only if the first step is a domain to
base type coercion (which we assume would be specially marked in
pg_cast).  


FWIW, before I got to this paragraph that was the thought that 
immediately occurred to me.


cheers

andrew

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

Re: [HACKERS] pdfs of the conference

[Magnus Hagander]

> > >> The larger version is only hidden from everyone :)
> > >>
> > >>
> > http://people.planetpostgresql.org/mha/uploads/photo/conf/conf
> > erence_group.jpg
> > >>
> >  > ference_group.jpg>
> > >>
> > >
> > > Very cool, I was hoping someone would post this.  Any
> > chance we can get
> > > a list of names to go with the faces?
> >
> > Yeah, then we can see if the people actually look as intelligent
> as
> > they sound ;)
> >
> > But seriously, it would be nice to be able to visualise the
> people
> > who's
> >mail you are reading/responding to. No actual value in this,
> just
> > fun...
> 
> I'm not sure many of us could name everyone.
> 
> Here's a starter though:
> 
> The guy with the PostgreSQL sign round his neck is Devrim Gunduz.
> The guy holding one up at the back is Chris Browne. On the front
> row theres Josh Berkus in the middle, Peter Eisentraut to the
> right, and Gavin Sherry on the far right. Bruce is behind Gavin in
> the light blue shirt, next to Tatsuo Ishii and Alvaro. Tom is one
> row from the back, in the middle with the grey-brown shirt on, and
> to the left of him is D'arcy (with the hat) and then Magnus
> Hagander, Thomas Hallgren and Neil Clifford.

I started with an imagemap version of this one after the conference. Let
me finish off some of that and put it up somewhere so people can
complete it.

//Magnus


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

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

Re: [HACKERS] Truncation of email subject lines

[Alvaro Herrera]

Markus Schaber wrote:
> Hi, Bruce,
> 
> Bruce Momjian wrote:
> 
> > Should I try hacking my mail reader to prevent this?  I think I see
> > where it is happening in the code.
> 
> AFAICT, the wrapping of long header lines by indentation (as your mailer
> seems to do) is RFC conformant, so I think it is majordomo who needs the
> fix.

Yep.  We were actually offered a patch on Majordomo's mailing list, a
couple of days ago.  It made a lot of sense to me -- it was kinda
obvious that without it, multiline stuff would be truncated.  What I'm
not sure about is why it would apply only to Subject: and not, say,
to Received:

Marc would need to install it to see if it fixes things.  (It may be
smarter to install a test list somewhere else, replicate then problem,
then apply to patch and prove that the problem no longer occurs; but I'm
rather short on time to do it myself)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] [PATCHES] setseed() doc

[Neil Conway]

On Mon, 2006-09-04 at 15:19 -0400, Tom Lane wrote:
> AFAICT it's just junk.  It happens to be the input times
> MAX_RANDOM_VALUE, but what use is that?  I wonder if we shouldn't
> change the function to return VOID

I agree. Given how soon we want to get an 8.2 beta out the door, perhaps
this change would be best postponed to 8.3 (unless there's another
outstanding 8.2 patch that requires initdb?).

-Neil



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

[HACKERS] Release Notes: Major Changes in 8.2

[Simon Riggs]

I'd like to include a section on Major changes in this release at the
top of the release notes, as has been done for at least the last 6 major
releases. The notes below are one stab at that, for **discussion**. I've
tried to arrange specific changes into groups... 


Major changes in this release:

Improved scalability and performance on multi-processor systems (Tom,
Alvaro, Itagaki, Qingqing, Heikki)

A variety of changes improves the performance of both sequential scans
and index scans, as well as enhancing multi-processor scalability. The
advanced query optimizer has also been further enhanced, allowing
indexes and partitioning to be useful in more cases. 

Improved utility and large query performance (Tom, Simon, Alon, Andreas)

Large sorts will have typical performance increases of 100-300%,
improving complex queries and creating new indexes. Loading times have
also been reduced. Large queries, data loads, upgrades and restores will
be considerably improved.

Improved monitoring and performance tuning (Tom, Bruce, Greg, Larry)

Overhead of statistics collection has been considerably reduced and new
statistics and system information is available. Better query logging
improves diagnostics and especially performance tuning. Server now
includes DTrace support. Indexes can now also be created CONCURRENTLY,
allowing application tuning without effecting server availability.

Zero administration overhead now possible (Alvaro)

With autovacuum enabled, all required vacuuming will now take place
without administrator intervention enabling wider distribution of
embedded databases.

Improved defaults and configuration (Peter, Andrew)

Installation defaults are now improved for many tunable memory
parameters and these can now be specified in kB, MB and GB. 

Warm Standby Servers for High Availability (Simon, Tom)

Warm Standby servers can now be more easily configured and are
appropriate in a wider range of circumstances than previously.

Improved scalability and performance of text search: GIN and Tsearch2
(Teodor, Oleg)

New GIN indexes allow much larger text search indexes than were
previously possible. TSearch2 has been enhanced and performance has also
been greatly improved.

Enhanced DML Functionality (Jonah, Joe, Tom, Susanne, Atsushi)

INSERT/UPDATE/DELETE RETURNING and INSERT .. VALUES (), VALUES (),
VALUES () allow more efficient application designs. Enhancements to
UPDATE and DELETE allow additional constructs for clarity and ease of
use.

SQL:2003 Analytical functions (Sergey, Tom, Neil)

All statistical aggregate functions defined by SQL:2003 are now
supported and user-defined aggregates now can take multiple columns as
inputs.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] setseed() doc

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> On Mon, 2006-09-04 at 15:19 -0400, Tom Lane wrote:
>> AFAICT it's just junk.  It happens to be the input times
>> MAX_RANDOM_VALUE, but what use is that?  I wonder if we shouldn't
>> change the function to return VOID

> I agree. Given how soon we want to get an 8.2 beta out the door, perhaps
> this change would be best postponed to 8.3 (unless there's another
> outstanding 8.2 patch that requires initdb?).

Nothing outstanding at the moment.

Although this is surely a small change, it's also pretty low-priority,
so I'd counsel leaving it for 8.3 rather than trying to cram it in now.
We have more important things to be worrying about ...

regards, tom lane

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

Re: [HACKERS] Bitmap index status

[Jie Zhang]

On 9/19/06 5:15 AM, "Gavin Sherry" <[EMAIL PROTECTED]> wrote:

> On Tue, 19 Sep 2006, Heikki Linnakangas wrote:
> 
>> Jie Zhang wrote:
>>> Hi Heikki and all,
>>> 
>>> Please find the latest bitmap index patch in the attachment. This patch is
>>> generated against the postgresql cvs head.
>>> 
>> 
>> Thanks.
>> 
>> The handling of stream and hash bitmaps looks pretty complicated to me.
>> All the bitmap-related nodes have logic to handle both types slightly
>> differently. It all seems to come down to that if a subnode (or
>> amgetbitmap in a bitmap index scan node) returns a StreamBitmap, the
>> caller needs to call the subnode many times, until it returns a NULL.
>> With a HashBitmap, the caller only calls the subnode once.
>> 
>> I think amgetbitmap should be called just once per index scan, and it
>> should return either a hash bitmap or a stream bitmap. The same applies
>> to all the executor nodes that return bitmaps, they would only return a
>> single HashBitmap or StreamBitmap, and the upper node would call
>> tbm_iterate repeatedly on that.
>> 
>> StreamBitmap would contain a callback (filled by the indexam) that
>> tbm_iterate would call to fill the next TBMIterateResult.
> 
> Right, this was the approach taken by an earlier version of the patch I
> had worked on. It was significantly uglified by the need to keep the index
> state around and to be careful about what amrescan might do behind out
> back. I will, however, introduce the idea again because it makes the code
> much cleaner and more logical, as you seem to suggest.

I will think about this approach. But I am not quite convinced that this
approach will be simpler and cleaner than the above approach. :-)

Thanks,
Jie



---(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] Release notes

[Simon Riggs]

On Fri, 2006-09-15 at 15:37 -0400, Bruce Momjian wrote:
> I have completed my first pass over the release notes and Tom has made
> some additions:
> 
>   http://momjian.postgresql.org/cgi-bin/pgrelease
> 
> I will probably go over them again in a few hours, update them to
> current CVS, then move them into our SGML documentation by Monday.

We talk about "standby point-in-time-recovery (PITR) servers" in the
release notes, but in the docs PITR has now been replaced by Continuous
Archiving and we talk about Warm Standby servers.

Can we call them Warm Standby servers? That makes more sense for the
general reader and matches the docs.


Also, not sure what the thoughts are regarding surnames. I'm referred to
as both Simon and Simon Riggs in the release notes. Should we have a
policy of first mention uses full name, subsequent mentions just use
first name if there is no confusion by doing so? 

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Andreas Pflug]

Simon Riggs wrote:
> Zero administration overhead now possible (Alvaro)
>
>   With autovacuum enabled, all required vacuuming will now take place
> without administrator intervention enabling wider distribution of
> embedded databases.
>   
This was true for 8.1 already, no?

Regards,
Andreas


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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Mark Dilger]

Tom Lane wrote:

Rereading what I just wrote, it might be as simple as allowing a
two-step cast in certain cases, only if the first step is a domain to
base type coercion (which we assume would be specially marked in
pg_cast).  But the devil is in the details ... and anyway there might
be a cleaner approach than that.


ISTM casts from a domain to their base type are fundamentally different from 
casts between types.  In general, casting TYPE_X to TYPE_Y requires malloc'ing 
memory for TYPE_Y, and converting the data of TYPE_X into TYPE_Y, possibly with 
loss of accuracy or correctness, etc.  (4-byte or less types are handled on the 
stack, not the heap, but that seems irrelevant to me and I'm only mentioning it 
here to head off any replies along those lines.)  Certainly, having the system 
chain together lots of implicit casts of this sort is scary.  But casting a 
domain to its base type never involves loss of accuracy or correctness, right? 
(Casting from the base type to the domain might not work, on account of the 
domain restrictions forbidding the particular value stored in the base.)


Perhaps we need to be able to register casts with more information than just 
IMPLICIT vs. EXPLICIT.  Perhaps we also need something like SAFE or some other 
term, and then have a rule that no chain of casts chosen by the system (as 
opposed to specified by the user) can contain more than one IMPLICIT cast, but 
can contain unlimited many SAFE casts.


When a domain is created, a SAFE cast from the domain to its base type could 
automatically be generated.


Casts between the existing varchar(n) to text could be marked as SAFE, given 
that the underlying storage scheme for varchar(n) is the same as text.  (Casts 
from text to varchar(n) are not SAFE, because the text might be too long to fit.)


Casts from int2 -> int4, int2 -> int8, and int4 -> int8 would all be SAFE, I 
think, because they are not lossy.  But perhaps I have not thought enough about 
this and these should be IMPLICIT rather than SAFE.


Casts from non-text types to text would remain IMPLICIT, I expect.

If a user created their own type, such as the recent discussion of an int3 type, 
 they could also create an int3 -> int4 cast marked as SAFE, and from int2 -> 
int3 marked as SAFE, and from int3 -> int2 marked as EXPLICIT, and from int4 -> 
int3 marked as EXPLICIT, and could avoid writing all the casts to other integral 
types.


(I've pretty much abandoned the idea of an int3 type because my testing 
convinced me there were no performance advantages to it.  But it serves ok as an 
 example.)


mark

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

  http://archives.postgresql.org

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Joshua D. Drake]

Andreas Pflug wrote:

Simon Riggs wrote:

Zero administration overhead now possible (Alvaro)

With autovacuum enabled, all required vacuuming will now take place
without administrator intervention enabling wider distribution of
embedded databases.
  

This was true for 8.1 already, no?


No. 8.1 did not have it turned on by default.

Sincerely,

Joshua D. Drake




Regards,
Andreas


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




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Simon Riggs]

On Wed, 2006-09-20 at 18:22 +0200, Andreas Pflug wrote:
> Simon Riggs wrote:
> > Zero administration overhead now possible (Alvaro)
> >
> > With autovacuum enabled, all required vacuuming will now take place
> > without administrator intervention enabling wider distribution of
> > embedded databases.
> >   
> This was true for 8.1 already, no?

Hmmm. You're correct.

Perhaps that is not a major change after all.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

Re: [HACKERS] pdfs of the conference

[Magnus Hagander]

> > Here's a starter though:
> >
> > The guy with the PostgreSQL sign round his neck is Devrim Gunduz.
> > The guy holding one up at the back is Chris Browne. On the front
> row
> > theres Josh Berkus in the middle, Peter Eisentraut to the right,
> and
> > Gavin Sherry on the far right. Bruce is behind Gavin in the light
> blue
> > shirt, next to Tatsuo Ishii and Alvaro. Tom is one row from the
> back,
> > in the middle with the grey-brown shirt on, and to the left of
> him is
> > D'arcy (with the hat) and then Magnus Hagander, Thomas Hallgren
> and
> > Neil Clifford.
> 
> I started with an imagemap version of this one after the
> conference. Let me finish off some of that and put it up somewhere
> so people can complete it.

Ok. I've got this up at
http://www.postgresql.org/files/community/conference06/conference_group.
html.

Help requested to complete the names not yet added - send private email.

//Magnus


---(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] Lock partitions

[Mark Wong]

Mark Wong wrote:

Tom Lane wrote:

Mark Wong <[EMAIL PROTECTED]> writes:
Curious, I'm still seeing the same behavior.  Maybe I'll take another 
snapshot from CVS.


Hm, maybe I need to try a bit harder here.  Does the "not registered"
error happen immediately/reliably for you, or do you need to run the
test awhile?


I did a gross test and my kit appears broken between the 8.0 and 8.1 
releases.  I'll try to narrow down the exact date.


I've narrowed it down between cvs pulls from Dec 14, 2005 and Dec 15, 
2005.  Does the attached diff appear to be a plausible cause?


Thanks,
Mark
diff -urN pgsql-2005-12-14/src/backend/commands/prepare.c 
pgsql-2005-12-15/src/backend/commands/prepare.c
--- pgsql-2005-12-14/src/backend/commands/prepare.c 2005-11-28 
17:25:49.0 -0800
+++ pgsql-2005-12-15/src/backend/commands/prepare.c 2005-12-14 
09:06:27.0 -0800
@@ -10,7 +10,7 @@
  * Copyright (c) 2002-2005, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *   $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.43 2005/11/29 
01:25:49 tgl Exp $
+ *   $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.44 2005/12/14 
17:06:27 tgl Exp $
  *
  *-
  */
@@ -448,6 +448,30 @@
 }
 
 /*
+ * Given a prepared statement, determine whether it will return tuples.
+ *
+ * Note: this is used rather than just testing the result of
+ * FetchPreparedStatementResultDesc() because that routine can fail if
+ * invoked in an aborted transaction.  This one is safe to use in any
+ * context.  Be sure to keep the two routines in sync!
+ */
+bool
+PreparedStatementReturnsTuples(PreparedStatement *stmt)
+{
+   switch (ChoosePortalStrategy(stmt->query_list))
+   {
+   case PORTAL_ONE_SELECT:
+   case PORTAL_UTIL_SELECT:
+   return true;
+
+   case PORTAL_MULTI_QUERY:
+   /* will not return tuples */
+   break;
+   }
+   return false;
+}
+
+/*
  * Given a prepared statement that returns tuples, extract the query
  * targetlist. Returns NIL if the statement doesn't have a determinable
  * targetlist.
diff -urN pgsql-2005-12-14/src/backend/executor/execQual.c 
pgsql-2005-12-15/src/backend/executor/execQual.c
--- pgsql-2005-12-14/src/backend/executor/execQual.c2005-11-22 
10:17:10.0 -0800
+++ pgsql-2005-12-15/src/backend/executor/execQual.c2005-12-14 
08:28:32.0 -0800
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *   $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.185 2005/11/22 
18:17:10 momjian Exp $
+ *   $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.186 2005/12/14 
16:28:32 tgl Exp $
  *
  *-
  */
@@ -523,7 +523,7 @@
Assert(variable->varno != OUTER);
slot = econtext->ecxt_scantuple;
 
-   tuple = slot->tts_tuple;
+   tuple = ExecFetchSlotTuple(slot);
tupleDesc = slot->tts_tupleDescriptor;
 
/*
diff -urN pgsql-2005-12-14/src/backend/tcop/postgres.c 
pgsql-2005-12-15/src/backend/tcop/postgres.c
--- pgsql-2005-12-14/src/backend/tcop/postgres.c2005-11-22 
10:17:21.0 -0800
+++ pgsql-2005-12-15/src/backend/tcop/postgres.c2005-12-14 
09:06:27.0 -0800
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *   $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.470 2005/11/22 
18:17:21 momjian Exp $
+ *   $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.471 2005/12/14 
17:06:27 tgl Exp $
  *
  * NOTES
  *   this is the "main" module of the postgres backend and
@@ -1849,6 +1849,15 @@
ListCell   *l;
StringInfoData buf;
 
+   /*
+* Start up a transaction command. (Note that this will normally change
+* current memory context.) Nothing happens if we are already in one.
+*/
+   start_xact_command();
+
+   /* Switch back to message context */
+   MemoryContextSwitchTo(MessageContext);
+
/* Find prepared statement */
if (stmt_name[0] != '\0')
pstmt = FetchPreparedStatement(stmt_name, true);
@@ -1862,6 +1871,22 @@
 errmsg("unnamed prepared statement 
does not exist")));
}
 
+   /*
+* If we are in aborted transaction state, we can't safely create a 
result
+* tupledesc, because that needs catalog accesses.  Hence, refuse to
+* Describe statements that return data.  (We shouldn't just refuse all
+* Describes, since that might break the ability of some clients to 
issue
+* COMMIT or ROLLBACK commands, if they use code that blindly Describes
+* whatever it does.)  We can Describe parameters without doing anything
+* dangerous, so we don't restrict that.
+*/
+   if (IsAbortedTransactionBlockState() &&
+   PreparedStatementReturnsTuples(p

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Gregory Stark]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:

> No. 8.1 did not have it turned on by default.

Neither does 8.2 though.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Mark Dilger]

Mark Dilger wrote:
Casts from int2 -> int4, int2 -> int8, and int4 -> int8 would all be 
SAFE, I think, because they are not lossy.  But perhaps I have not 
thought enough about this and these should be IMPLICIT rather than SAFE.


I have thought about this some more.  I think these are indeed SAFE.  The 
distinction between SAFE and IMPLICIT should not, I think, be whether the 
storage type is identical, but rather whether there is any possible loss of 
precision, range, accuracy, etc., or whether there is any change in the 
fundamental interpretation of the data when cast from the source to destination 
type.


The built-in cast from int2 -> int4, which is currently IMPLICIT, cannot lose 
any information, nor can it cause the data to be interpreted differently. 
Therefore it is SAFE.


The built-in cast from int8 -> float8 is currently marked as IMPLICIT, but since 
 a large integer value which is cast in this fashion might be somewhat altered, 
it is not SAFE.  It is also interpreted differently, since floating point 
numbers are typically interpreted as approximations, whereas integers are 
interpreted as exact.  (Hence the tendency to compare integers for equality, but 
not floats.)


Can anyone think of examples where chaining together SAFE casts would cause 
problems, using the guidelines for what is SAFE outlined above?


mark

---(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] TODO: Fix CREATE CAST on DOMAINs

[Tom Lane]

Mark Dilger <[EMAIL PROTECTED]> writes:
> Mark Dilger wrote:
>> Casts from int2 -> int4, int2 -> int8, and int4 -> int8 would all be 
>> SAFE, I think, because they are not lossy.  But perhaps I have not 
>> thought enough about this and these should be IMPLICIT rather than SAFE.

> I have thought about this some more.  I think these are indeed SAFE.  The 
> distinction between SAFE and IMPLICIT should not, I think, be whether the 
> storage type is identical, but rather whether there is any possible loss of 
> precision, range, accuracy, etc., or whether there is any change in the 
> fundamental interpretation of the data when cast from the source to 
> destination 
> type.

You are going in exactly the wrong direction --- this line of thought is
aiming to make *more* casts possible by default, which is not what we
need, at least not among the collection of base types.

regards, tom lane

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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Alvaro Herrera]

Simon Riggs wrote:
> On Wed, 2006-09-20 at 18:22 +0200, Andreas Pflug wrote:
> > Simon Riggs wrote:
> > > Zero administration overhead now possible (Alvaro)
> > >
> > >   With autovacuum enabled, all required vacuuming will now take place
> > > without administrator intervention enabling wider distribution of
> > > embedded databases.
> > >   
> > This was true for 8.1 already, no?
> 
> Hmmm. You're correct.
> 
> Perhaps that is not a major change after all.

What happened in 8.2 is that you no longer need database-wide vacuums,
ever (except for template databases).  Not sure if that qualifies as a
major change or not.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Lock partitions

[Tom Lane]

Mark Wong <[EMAIL PROTECTED]> writes:
>> I did a gross test and my kit appears broken between the 8.0 and 8.1 
>> releases.  I'll try to narrow down the exact date.

> I've narrowed it down between cvs pulls from Dec 14, 2005 and Dec 15, 
> 2005.  Does the attached diff appear to be a plausible cause?

No, not particularly.  You sure about those dates?

regards, tom lane

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

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

Re: [HACKERS] Lock partitions

[Mark Wong]

Tom Lane wrote:

Mark Wong <[EMAIL PROTECTED]> writes:
I did a gross test and my kit appears broken between the 8.0 and 8.1 
releases.  I'll try to narrow down the exact date.


I've narrowed it down between cvs pulls from Dec 14, 2005 and Dec 15, 
2005.  Does the attached diff appear to be a plausible cause?


No, not particularly.  You sure about those dates?


Ugh, double checking tells me I'm wrong.  I'll keep testing.

Thanks,
Mark

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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Joshua D. Drake]

Gregory Stark wrote:

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:


No. 8.1 did not have it turned on by default.


Neither does 8.2 though.


oh... heh.

J







--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Mark Dilger]

Tom Lane wrote:

Mark Dilger <[EMAIL PROTECTED]> writes:

Mark Dilger wrote:
Casts from int2 -> int4, int2 -> int8, and int4 -> int8 would all be 
SAFE, I think, because they are not lossy.  But perhaps I have not 
thought enough about this and these should be IMPLICIT rather than SAFE.


I have thought about this some more.  I think these are indeed SAFE.  The 
distinction between SAFE and IMPLICIT should not, I think, be whether the 
storage type is identical, but rather whether there is any possible loss of 
precision, range, accuracy, etc., or whether there is any change in the 
fundamental interpretation of the data when cast from the source to destination 
type.


You are going in exactly the wrong direction --- this line of thought is
aiming to make *more* casts possible by default, which is not what we
need, at least not among the collection of base types.



If I understand correctly, you are worried about two issues:  ambiguity and 
performance.  You don't want the system to be slower from the extra searching 
needed to find possible multiple step casts, and you don't want any new 
ambiguity where the system can't deterministically decide which choice of 
cast(s) should be used.  Is that right?


If the system chooses cast chains based on a breadth-first search, then the 
existing int2 -> int8 cast would be chosen over an int2 -> int4 -> int8 chain, 
or an int2 -> int3 -> int4 -> int8 chain, or in fact any chain at all, because 
the int2 -> int8 cast is the shortest.


So the code to search chains should only be invoked in what would currently be 
an *error condition*, that being that the SQL includes a request for a cast that 
cannot be resolved without chaining.


Since the chaining code would be new, and the rules for it would be new, we can 
still design them however we like (within reason.)  I would propose:


1) Shorter chains trump longer chains.

2) When comparing two equal length chains, one made entirely of SAFE casts 
trumps one which contains an IMPLICIT cast.


3) When two or more chains remain that cannot be resolved under the above two 
rules, the SQL is considered ambiguous and an error condition is raised.


I don't see how this would break any existing valid SQL.  But it seems like it 
would solve both the DOMAIN problem you mentioned and the oft lamented problem 
that adding a new datatype requires quadratically many casts to the system.


mark

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

[HACKERS] Phantom Command ID

[Heikki Linnakangas]

Per discussion on reducing heap tuple header, I've started to work on 
the phantom cid idea.


I'm thinking of having an array of cmin,cmax pairs, indexed by phantom 
cid number. Looking up cmin,cmax of a phantom id is then a simple array 
lookup. To allow reusing phantom cids, we have a hash table that allows 
looking up a phantomid by cmin,cmax pair.


A big question is, do we need to implement spilling to disk?

With the above data structures, each phantom cid is going to take 28 
bytes of backend-private memory [See math below]. Transactions that 
actually need phantom cids are not that common, but I suppose that 
applications that make heavy use of plpgsql functions or do a lot of 
repeated UPDATES of same rows might need millions.



[quick sizing math]
array element = sizeof(cmin) + sizeof(cmax) = 4 + 4 = 8
hash table key + data + hash element overhead = sizeof(cmin) + 
sizeof(cmax) + sizeof(phantomcid) + sizeof(HASHELEMENT) = 20

Total: 28 bytes (or 32 if MAXALIGN is 8-bytes)

this excludes overhead of hash table buckets etc.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---(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] pg_upgrade: downgradebility

[Josh Berkus]

Zdenek, Andrew,

Overall, I'd tend to say that downgradability should be for a v2 version of 
the tool.  It's simply not as important.  For one thing, there's always 
reverting-to-backup.

However, we *do* need to be able to halt the upgrade and reverse it if it 
starts erroring out.  So that might end up being the same code.

Supposedly someone from EnterpriseDB is working on this as well; where are 
you?

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(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] -HEAD planner issue wrt hash_joins on dbt3 ?

[Tom Lane]

Matteo Beccati <[EMAIL PROTECTED]> writes:
> Tom Lane ha scritto:
>> Matteo Beccati <[EMAIL PROTECTED]> writes:
>>> I cannot see anything bad by using something like that:
>>> if (histogram is large/representative enough)
>> 
>> Well, the question is exactly what is "large enough"?  I feel a bit
>> uncomfortable about applying the idea to a histogram with only 10
>> entries (especially if we ignore two of 'em).  With 100 or more,
>> it sounds all right.  What's the breakpoint?

> Yes, I think 100-200 could be a good breakpoint.

I've committed this change with (for now) 100 as the minimum histogram
size to use.  Stefan, are you interested in retrying your benchmark?

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] 'configure --disable-shared' and 'make check'

[Peter Eisentraut]

Albe Laurenz wrote:
> I notice that when I run 'make check' on a
> statically linked HEAD, it fails during
> 'createlang' with

Because createlang relies on *dynamic* loading.

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

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

   http://archives.postgresql.org

Re: [HACKERS] Phantom Command ID

[Tom Lane]

Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> A big question is, do we need to implement spilling to disk?

My thought is no, at least not in the first cut ... this is something
that can be added later if it proves critical, and right at the moment
my guess is that it never will.  The data structure design sounds fine.

regards, tom lane

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

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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Tom Lane]

Mark Dilger <[EMAIL PROTECTED]> writes:
> If the system chooses cast chains based on a breadth-first search,
> then the existing int2 -> int8 cast would be chosen over an int2 ->
> int4 -> int8 chain, or an int2 -> int3 -> int4 -> int8 chain, or in
> fact any chain at all, because the int2 -> int8 cast is the shortest.

Well, this is the sort of thing that has to be thought about pretty
carefully.  Is "length of chain" the most appropriate metric?  What are
you going to do when resolving a multi-input operator or function, and
there are different ways to match different candidates with different
sets of path lengths?

There's been some prior discussion of attaching a "measure of goodness"
to different potential cast pathways.  I'm too lazy to look it up at the
moment but I strongly suggest whoever wants to produce a concrete
proposal in this area should go check the archives.

regards, tom lane

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

Re: [HACKERS] Phantom Command ID

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 04:02:00PM -0400, Tom Lane wrote:
> Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > A big question is, do we need to implement spilling to disk?
> 
> My thought is no, at least not in the first cut ... this is something
> that can be added later if it proves critical, and right at the moment
> my guess is that it never will.  The data structure design sounds fine.

What would the failure mode be? Would we just keep going until the box
ran out of memory? I think it'd be better to have some kind of hard
limit so that a single backend can't grind a production server into a
swap-storm. (Arguably, not having a limit is exposing a DoS
vulnerability).
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(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] TODO: Fix CREATE CAST on DOMAINs

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 09:31:48AM -0700, Mark Dilger wrote:
> Perhaps we need to be able to register casts with more information than 
> just IMPLICIT vs. EXPLICIT.  Perhaps we also need something like SAFE or 
> some other term, and then have a rule that no chain of casts chosen by the 
> system (as opposed to specified by the user) can contain more than one 
> IMPLICIT cast, but can contain unlimited many SAFE casts.

Currently cast have three types, implicit, explicit and assignment.

You don't want to have an unlimited number of anything. Cast lookups
are expensive enough as it is.

> Casts from int2 -> int4, int2 -> int8, and int4 -> int8 would all be SAFE, 
> I think, because they are not lossy.  But perhaps I have not thought enough 
> about this and these should be IMPLICIT rather than SAFE.

Yeah, but the trick is you want, with the above casts to only ever
produce the *shortest* path. That's what makes it expensive.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] Release notes

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 05:10:13PM +0100, Simon Riggs wrote:
> Also, not sure what the thoughts are regarding surnames. I'm referred to
> as both Simon and Simon Riggs in the release notes. Should we have a
> policy of first mention uses full name, subsequent mentions just use
> first name if there is no confusion by doing so? 

Hrm, I'd assumed that "well known" community members didn't get Surname
mentions... maybe we should just stick to "First Last" everywhere..
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

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

[HACKERS] Units in postgresql.conf.sample

[Peter Eisentraut]

Should the values in the default configuration file contain units?  And 
if so, should we phase out the verbal description of the units, e.g.,

#work_mem = 1024# min 64, size in kB

becomes

#work_mem = 1MB # min 64kB

(The "native" units are of course still shown in the documentation for 
reference.)

-- 
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: [PATCHES] [HACKERS] Incrementally Updated Backup

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 02:09:43PM +0100, Simon Riggs wrote:
> > But why in the world would you want to stop the slave to do it?  ISTM
> > we would want to arrange things so that you can copy the slave's files
> > while it continues replicating, just as with a standard base backup.
> 
> You can do that, of course, but my thinking was that people would regard
> the technique as "unsupported", so I added a quick flag as a prototype.

An advantage to being able to stop the server is that you could have one
server processing backups for multiple PostgreSQL clusters by going
through them 1 (or more likely, 2, 4, etc) at a time, essentially
providing N+1 capability.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] Phantom Command ID

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> What would the failure mode be? Would we just keep going until the box
> ran out of memory? I think it'd be better to have some kind of hard
> limit so that a single backend can't grind a production server into a
> swap-storm. (Arguably, not having a limit is exposing a DoS
> vulnerability).

[ shrug... ]  If we tried to guarantee such a thing we'd be putting
arbitrary limits into hundreds if not thousands of different bits of the
backend.  I think the correct answer for an admin who is worried about
such a thing is to make sure that the process ulimit is a sufficiently
small fraction of the machine's available RAM.  Only if we can't
gracefully handle running up against ulimit is it our problem (hence,
we have a stack-size overflow check, but not any such thing for data size).

regards, tom lane

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

Re: [HACKERS] Units in postgresql.conf.sample

[David Fetter]

On Wed, Sep 20, 2006 at 10:20:18PM +0200, Peter Eisentraut wrote:
> Should the values in the default configuration file contain units?
> And if so, should we phase out the verbal description of the units,
> e.g.,
> 
> #work_mem = 1024# min 64, size in kB
> 
> becomes
> 
> #work_mem = 1MB # min 64kB
> 
> (The "native" units are of course still shown in the documentation
> for reference.)

+1 for the change :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(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: [PATCHES] [HACKERS] Incrementally Updated Backup

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> An advantage to being able to stop the server is that you could have one
> server processing backups for multiple PostgreSQL clusters by going
> through them 1 (or more likely, 2, 4, etc) at a time, essentially
> providing N+1 capability.

Why wouldn't you implement that by putting N postmasters onto the backup
server?  It'd be far more efficient than the proposed patch, which by
aborting at random points is essentially guaranteeing a whole lot of
useless re-replay of WAL whenever you restart it.

regards, tom lane

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

Re: [HACKERS] [PATCHES] Include file in regress.c

[Alvaro Herrera]

Magnus Hagander wrote:

> > What does your CVS/Entries file look for that dir?
> 
> It does contain both gram.c and gram.y. They look just the same (except
> for version and date, of course). I don't know how it got there ;-) Is
> it safe to just remove that?

I don't know if it's safe, but my Entries file only contains gram.y, and
I don't see any mention of gram.y anywhere else.

Strangely, if I try to do a "cvs add gram.c", it fails with

cvs add: `gram.c' added independently by second party

I don't know what this means.  (Why "second party" and not "third
party"?).  Even if I delete gram.c.  Even if I remove it from
.cvsignore.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

   http://archives.postgresql.org

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 10:26:55AM -0700, Mark Dilger wrote:
> I have thought about this some more.  I think these are indeed SAFE.  The 
> distinction between SAFE and IMPLICIT should not, I think, be whether the 
> storage type is identical, but rather whether there is any possible loss of 
> precision, range, accuracy, etc., or whether there is any change in the 
> fundamental interpretation of the data when cast from the source to 
> destination type.

My question is whether there should be any implicit casts that are not
safe. Your example int8 -> float8 being implicit is I think an error
and we should wonder why that cast implicit now anyway.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] [PATCHES] Include file in regress.c

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Strangely, if I try to do a "cvs add gram.c", it fails with
> cvs add: `gram.c' added independently by second party
> I don't know what this means.  (Why "second party" and not "third
> party"?).  Even if I delete gram.c.  Even if I remove it from
> .cvsignore.

I think "cvs add" probably contacts the server, because I seem to recall
that it gives different output depending on whether the file already
exists on another branch, and there's no way to tell that from your
local working directory contents.

The CVS history for gram.c looks a bit confused:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/Attic/gram.c
How did revisions 2.89 and 2.90 come into existence when the file was
already cvs-removed?  This may be confusing the server too.

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] TODO: Fix CREATE CAST on DOMAINs

[Tom Lane]

Martijn van Oosterhout  writes:
> My question is whether there should be any implicit casts that are not
> safe. Your example int8 -> float8 being implicit is I think an error
> and we should wonder why that cast implicit now anyway.

Because the SQL spec requires it.  You are not required to write a cast
to add an exact and an approximate quantity, and the spec says the
result is approximate.

Trying to design this stuff purely according to abstract notions of
elegance of the cast rules isn't going to work out well --- we have
both spec requirements and backwards compatibility to worry about.

Now we do have the flexibility to alter the default contents of pg_cast
--- there could be more or fewer entries in there than there are now,
if the type coercion rules are altered to do less or more automatically
than they do now.  But the end-result behavior needs to wind up being
pretty darn near the same thing, at least within the numeric type
category (I'm not as certain that we have the other ones right, but the
numeric category has been *very* heavily scrutinized and beat upon).
The only thing I really want to see changing is the behavior for domain
types --- and even there, the "default" behavior when there are no
user-created domain-specific operators or casts has to stay the same.

regards, tom lane

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

Re: [HACKERS] Units in postgresql.conf.sample

[Josh Berkus]

Peter,

> #work_mem = 1024# min 64, size in kB
>
> becomes
>
> #work_mem = 1MB # min 64kB
>
> (The "native" units are of course still shown in the documentation for
> reference.)

+1

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] Phantom Command ID

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 04:22:47PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > What would the failure mode be? Would we just keep going until the box
> > ran out of memory? I think it'd be better to have some kind of hard
> > limit so that a single backend can't grind a production server into a
> > swap-storm. (Arguably, not having a limit is exposing a DoS
> > vulnerability).
> 
> [ shrug... ]  If we tried to guarantee such a thing we'd be putting
> arbitrary limits into hundreds if not thousands of different bits of the
> backend.  I think the correct answer for an admin who is worried about
> such a thing is to make sure that the process ulimit is a sufficiently
> small fraction of the machine's available RAM.  Only if we can't
> gracefully handle running up against ulimit is it our problem (hence,
> we have a stack-size overflow check, but not any such thing for data size).

I didn't realize we had a lot of ways a backend could run a machine out
of memory, or at least ways that didn't have some kind of limit (ie:
work_mem). Are any of them very easy to run into?
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

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

Re: [HACKERS] Phantom Command ID

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I didn't realize we had a lot of ways a backend could run a machine out
> of memory, or at least ways that didn't have some kind of limit (ie:
> work_mem). Are any of them very easy to run into?

work_mem has nothing to do with trying to guarantee "no swapping DoS".
If it did, it wouldn't be USERSET, and it wouldn't be per query step.
The fact is that ulimit does what you want in that regard already;
why should we try to reinvent that wheel?

regards, tom lane

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

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

Re: [PATCHES] [HACKERS] Incrementally Updated Backup

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 04:26:30PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > An advantage to being able to stop the server is that you could have one
> > server processing backups for multiple PostgreSQL clusters by going
> > through them 1 (or more likely, 2, 4, etc) at a time, essentially
> > providing N+1 capability.
> 
> Why wouldn't you implement that by putting N postmasters onto the backup
> server?  It'd be far more efficient than the proposed patch, which by
> aborting at random points is essentially guaranteeing a whole lot of
> useless re-replay of WAL whenever you restart it.

My thought is that in many envoronments it would take much beefier
hardware to support N postmasters running simultaneously than to cycle
through them periodically bringing the backups up-to-date.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [PATCHES] [HACKERS] Incrementally Updated Backup

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> My thought is that in many envoronments it would take much beefier
> hardware to support N postmasters running simultaneously than to cycle
> through them periodically bringing the backups up-to-date.

How you figure that?  The cycling approach will require more total I/O
due to extra page re-reads ... particularly if it's built on a patch
like this one that abandons work-in-progress at arbitrary points.

A postmaster running WAL replay does not require all that much in the
way of CPU resources.  It is going to need I/O comparable to the gross
I/O load of its master, but cycling isn't going to reduce that at all.

regards, tom lane

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

Re: [PATCHES] [HACKERS] Incrementally Updated Backup

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 05:50:48PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > My thought is that in many envoronments it would take much beefier
> > hardware to support N postmasters running simultaneously than to cycle
> > through them periodically bringing the backups up-to-date.
> 
> How you figure that?  The cycling approach will require more total I/O
> due to extra page re-reads ... particularly if it's built on a patch
> like this one that abandons work-in-progress at arbitrary points.
> 
> A postmaster running WAL replay does not require all that much in the
> way of CPU resources.  It is going to need I/O comparable to the gross
> I/O load of its master, but cycling isn't going to reduce that at all.

True, but running several dozen instances on a single machine will
require a lot more memory (or, conversely, each individual database gets
a lot less memory to use).

Of course, this is all hand-waving right now... it'd be interesting to
see which approach was actually better.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Mark Dilger]

Tom Lane wrote:

Now we do have the flexibility to alter the default contents of pg_cast
--- there could be more or fewer entries in there than there are now,
if the type coercion rules are altered to do less or more automatically
than they do now.  But the end-result behavior needs to wind up being
pretty darn near the same thing, at least within the numeric type
category (I'm not as certain that we have the other ones right, but the
numeric category has been *very* heavily scrutinized and beat upon).
The only thing I really want to see changing is the behavior for domain
types --- and even there, the "default" behavior when there are no
user-created domain-specific operators or casts has to stay the same.


Your suggestion upthread that domains have two-step casts (from domain to base, 
then from base to whatever) is what got my attention.  I don't like the idea of 
having an interim solution to that subset of the problem if it might get in the 
way of solving the general problem later.  But perhaps it can be argued that no 
cruftiness would result from the special case code for casting domains to their 
base types?


mark


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

  http://archives.postgresql.org

Re: [HACKERS] TODO: Fix CREATE CAST on DOMAINs

[Gevik Babakhani]

> Trying to design this stuff purely according to abstract notions of
> elegance of the cast rules isn't going to work out well --- we have
> both spec requirements and backwards compatibility to worry about.
> 
> Now we do have the flexibility to alter the default contents of pg_cast
> --- there could be more or fewer entries in there than there are now,
> if the type coercion rules are altered to do less or more automatically
> than they do now.  But the end-result behavior needs to wind up being
> pretty darn near the same thing, at least within the numeric type
> category (I'm not as certain that we have the other ones right, but the
> numeric category has been *very* heavily scrutinized and beat upon).
> The only thing I really want to see changing is the behavior for domain
> types --- and even there, the "default" behavior when there are no
> user-created domain-specific operators or casts has to stay the same.

Trying to solve this problem requires more investigation having spec
requirements and backwards compatibility etc.. etc.. in mind.

After reading the thread, I think there are some interesting
similarities, ideas (or even techniques) used in OO languages like JAVA
and C# regarding internal handling when type boxing and type casting. (I
would like to think domains as inherited classes of their super or the
base class.)

I will come back with more thoughts after I have investigated a thing or
two.  Say tuned


---(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] -HEAD planner issue wrt hash_joins on dbt3 ?

[Matteo Beccati]

Hi,

Tom Lane wrote:

I've committed this change with (for now) 100 as the minimum histogram
size to use.  Stefan, are you interested in retrying your benchmark?


A first try with ltree gave big improvements on my smaller data set: the 
estimated row count is correct or off by only 1 row. I'm now restoring a 
bigger database to get more reliable results.


I hope Stefan can confirm the improvement on dbt3 too.

Thanks Tom :)


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

---(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] Release notes

[Bruce Momjian]

Jim C. Nasby wrote:
> On Wed, Sep 20, 2006 at 05:10:13PM +0100, Simon Riggs wrote:
> > Also, not sure what the thoughts are regarding surnames. I'm referred to
> > as both Simon and Simon Riggs in the release notes. Should we have a
> > policy of first mention uses full name, subsequent mentions just use
> > first name if there is no confusion by doing so? 
> 
> Hrm, I'd assumed that "well known" community members didn't get Surname
> mentions... maybe we should just stick to "First Last" everywhere..

My system has been to use first names if the person appears at the
bottom of the TODO list, else use full name in all references.  We refer
to too many people to use full names only on the first reference.


-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] [PATCHES] docs for advisory locks

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> ok, here is the promised docs for the advisory locks.

Applied with light editorialization.

> some quick notes here: this is my first non trivial patch (albeit only
> documentation) and i am a complete docbook novice.

Not bad for a first try --- I fixed a few things, but I wish all docs
submissions were this clean ;-)

regards, tom lane

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

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

[HACKERS] advisory locks and permissions

[Tom Lane]

One good thing about advisory locks having been in contrib was that they
didn't affect anyone who didn't actually install the module.  Now that
we've put those functions in core, I wonder whether we don't need to
face up to the possibility of malicious use.  For instance, it's not
very hard to create a DoS situation by running the system out of shared
lock table space:

regression=# select pg_advisory_lock(x) from generate_series(1,100) x;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You may need to increase max_locks_per_transaction.
regression=#

and once you've done that about the only fix is to quit your backend :-(

The brute force answer is to make those functions superuser-only, but I
wonder if there is a better way.  Perhaps we could just deny public
execute access on them by default, and let admins grant the privilege to
whom they trust.

Or we could try to do something about limiting the number of such locks
that can be granted, but that seems nontrivial to tackle at such a late
stage of the devel cycle.

Thoughts?

regards, tom lane

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

Re: [HACKERS] Release notes

[Alvaro Herrera]

Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > On Wed, Sep 20, 2006 at 05:10:13PM +0100, Simon Riggs wrote:
> > > Also, not sure what the thoughts are regarding surnames. I'm referred to
> > > as both Simon and Simon Riggs in the release notes. Should we have a
> > > policy of first mention uses full name, subsequent mentions just use
> > > first name if there is no confusion by doing so? 
> > 
> > Hrm, I'd assumed that "well known" community members didn't get Surname
> > mentions... maybe we should just stick to "First Last" everywhere..
> 
> My system has been to use first names if the person appears at the
> bottom of the TODO list, else use full name in all references.  We refer
> to too many people to use full names only on the first reference.

The problem is that the release notes don't contain the bottom of the
TODO list.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] [PATCHES] WIP: Hierarchical Queries - stage 1

[Tom Lane]

Mark Cave-Ayland <[EMAIL PROTECTED]> writes:
> My main issue at the moment is that the code in transformFromClauseItem
> seems a terrible hack, mainly because the grammar returns each string
> within the FROM clause as a RangeVar, and transformFromClauseItem
> assumes that each RangeVar represents a physical relation. Of course,
> this is not the case when referencing a CTE and so the code first checks
> to see if an entry has already been created when processing the WITH
> clause; if it does then we return NULL to indicate that
> transformFromClause should do nothing. Messy, but I wanted to see what
> other developers thought before jumping in and rewriting this part of
> the code.

You really can't get away with having the identical representation for
CTEs and ordinary sub-selects in the range table.  For instance, it
looks like your patch will think that

select ... from (select ...) as x, x, ...

is legal when it certainly is not.  I think you need either a new
RTEKind or an additional flag in the RTE to show that it's a CTE rather
than a plain subselect.  I'm not entirely sure that you even want the
CTEs in the rangetable at all --- that still needs some thought.

> Another point to think about is what should a query return if the SELECT
> doesn't refer to a CTE?

The spec ought to make this perfectly clear ... or perhaps not so clear,
but I'm sure it's defined.

> - with myrel(p1) as (select oid from pg_class) select myrel.p1 from
> myrel AS foo, pg_class AS bar WHERE foo.p1 = bar.oid; -- FAILS

> So in this case, should foo be accepted as a valid alias for myrel?

This comes back to the question of whether the CTE per se should be an
RTE at all.  Maybe only the reference to it should be an RTE.  The
behavior when seeing a plain RangeVar in FROM would be to first search
the side list of valid CTEs, and only on failure go looking for a real
table.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] advisory locks and permissions

[Jim C. Nasby]

On Wed, Sep 20, 2006 at 07:52:33PM -0400, Tom Lane wrote:
> face up to the possibility of malicious use.  For instance, it's not
> very hard to create a DoS situation by running the system out of shared
> lock table space:

Didn't you just say we don't try and protect against DoS? ;P

> The brute force answer is to make those functions superuser-only, but I
> wonder if there is a better way.  Perhaps we could just deny public
> execute access on them by default, and let admins grant the privilege to
> whom they trust.
> 
> Or we could try to do something about limiting the number of such locks
> that can be granted, but that seems nontrivial to tackle at such a late
> stage of the devel cycle.

ISTM that just restricting default access still leaves a pretty big
foot-gun laying around... perhaps the best compromise would be to do
that for this release and add some kind of a limit in the next release.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] advisory locks and permissions

[Merlin Moncure]

On 9/21/06, Tom Lane <[EMAIL PROTECTED]> wrote:

One good thing about advisory locks having been in contrib was that they
didn't affect anyone who didn't actually install the module.  Now that
we've put those functions in core, I wonder whether we don't need to
face up to the possibility of malicious use.  For instance, it's not
very hard to create a DoS situation by running the system out of shared
lock table space:


It's much more likely to accidentally bork yourself.


regression=# select pg_advisory_lock(x) from generate_series(1,100) x;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You may need to increase max_locks_per_transaction.
regression=#

and once you've done that about the only fix is to quit your backend :-(

The brute force answer is to make those functions superuser-only, but I
wonder if there is a better way.  Perhaps we could just deny public
execute access on them by default, and let admins grant the privilege to
whom they trust.

Or we could try to do something about limiting the number of such locks
that can be granted, but that seems nontrivial to tackle at such a late
stage of the devel cycle.


I vote for locking down to superuser access (lets be frank here: I
would estimate 90%+ database installatons run with the application as
root) so we are not losing much.

I honestly think exhausting the lock space should raise a fatal, not
an error.  This gives a chance for recovery without a hard reset.  Any
other limitation would be fine, now or later, just please don't slow
them down! (advisory locks are extremely fast).

merlin

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

Re: [HACKERS] Release notes

[Bruce Momjian]

Simon Riggs wrote:
> On Fri, 2006-09-15 at 15:37 -0400, Bruce Momjian wrote:
> > I have completed my first pass over the release notes and Tom has made
> > some additions:
> > 
> > http://momjian.postgresql.org/cgi-bin/pgrelease
> > 
> > I will probably go over them again in a few hours, update them to
> > current CVS, then move them into our SGML documentation by Monday.
> 
> We talk about "standby point-in-time-recovery (PITR) servers" in the
> release notes, but in the docs PITR has now been replaced by Continuous
> Archiving and we talk about Warm Standby servers.
> 
> Can we call them Warm Standby servers? That makes more sense for the
> general reader and matches the docs.

Agreed. Updated.

> Also, not sure what the thoughts are regarding surnames. I'm referred to
> as both Simon and Simon Riggs in the release notes. Should we have a
> policy of first mention uses full name, subsequent mentions just use
> first name if there is no confusion by doing so? 

OK, changed to "Simon", but am open to making more wholesale changes.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/release.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/release.sgml,v
retrieving revision 1.446
diff -c -c -r1.446 release.sgml
*** doc/src/sgml/release.sgml	20 Sep 2006 22:48:47 -	1.446
--- doc/src/sgml/release.sgml	21 Sep 2006 03:09:54 -
***
*** 37,52 
   
  
   This release adds many improvements to commands and database
!  facilities that were requested by users.  Rather than adding a
!  few new features, this release makes many features from previous
!  releases easier to use.  For example, it is now much easier to
!  create standby point-in-time-recovery (PITR) servers.  Many
   performance bottlenecks have been eliminated, allowing more
!  functionality to be enabled by default.  Various additions will
!  make porting from other databases easier.  The changes in this
!  release continue the PostgreSQL tradition of
!  being not only the most advanced open source database, but also
!  the easiest to use.
  
  
 
--- 37,52 
   
  
   This release adds many improvements to commands and database
!  facilities that were requested by users.  Rather than adding
!  a few new features, this release makes many features from
!  previous releases easier to use.  For example, there are now
!  additional controls for continuous archiving.  Many
   performance bottlenecks have been eliminated, allowing more
!  functionality to be enabled by default.  Various additions
!  will make porting from other databases easier.  The changes
!  in this release continue the PostgreSQL
!  tradition of being not only the most advanced open source
!  database, but also the easiest to use.
  
  
 
***
*** 489,509 
  

 
! Allow a forced switch to a new xlog file (Simon Riggs, Tom)
 
  
 
! This is valuable for keeping PITR standby
! servers in sync with the master.  xlog file switching also
! happens automatically during pg_stop_backup().
! This ensures that PITR servers have all xlog
  files needed for recovery.
 

  

 
! Add WAL informational functions (Simon Riggs)
 
  
 
--- 489,509 
  

 
! Allow a forced switch to a new xlog file (Simon, Tom)
 
  
 
! This is valuable for keeping continuous archiving servers
! in sync with the master.  xlog file switching also happens
! automatically during pg_stop_backup().  This
! ensures that continuous archiving servers have all xlog
  files needed for recovery.
 

  

 
! Add WAL informational functions (Simon)
 
  
 
***
*** 517,543 

 
  Allow WAL replay to be restored quicker in case
! of a crash (Simon Riggs)
 
  
 
  The server now does periodic checkpoints during WAL
  recovery, so if there is a crash, future WAL
  recovery is shortened.  This also eliminates the need for
! PITR standby servers to replay the entire log
! since the base backup if they crash.
 

  

 
  Add archive_timeout to force xlog file switches
! at a given interval (Simon Riggs)
 
  
 
! This enforces a maximum delay for PITR standby
! servers.
 

  
--- 517,542 

 
  Allow WAL replay to be restored quicker in case
! of a crash (Simon)
 
  
 
  The server now does perio

Re: [HACKERS] advisory locks and permissions

[Josh Berkus]

All,

> I vote for locking down to superuser access (lets be frank here: I
> would estimate 90%+ database installatons run with the application as
> root) so we are not losing much.

Not in my experience.   Note that making them superuser-only pretty much puts 
them out of the hands of hosted applications.

How simple would it be to limit the number of advisory locks available to a 
single request?  That would at least make the DOS non-trivial.  Or to put in 
a handle (GUC?) that allows turning advisory locks off?

Hmmm ... I'll bet I could come up with other ways to use generate_series in a 
DOS, even without advisory locks ...

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] advisory locks and permissions

[Bruce Momjian]

Doesn't creating many temp tables in a transaction do the same thing?

---

Josh Berkus wrote:
> All,
> 
> > I vote for locking down to superuser access (lets be frank here: I
> > would estimate 90%+ database installatons run with the application as
> > root) so we are not losing much.
> 
> Not in my experience.   Note that making them superuser-only pretty much puts 
> them out of the hands of hosted applications.
> 
> How simple would it be to limit the number of advisory locks available to a 
> single request?  That would at least make the DOS non-trivial.  Or to put in 
> a handle (GUC?) that allows turning advisory locks off?
> 
> Hmmm ... I'll bet I could come up with other ways to use generate_series in a 
> DOS, even without advisory locks ...
> 
> -- 
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Bruce Momjian]

Usually the major items just jump out of the release list.  In this
case, nothing really jumped out, and I felt if I listed sereral, it was
going to look weak because they were not big things, so I figured I
would just go with the "broad" list.

The criteria I usually use are things that were not easy to do before.

Does the list below look good for inclusion?

I guess my point is that what we have now overwhelms people with the
number of small things we did.  If you try to put a few at the top, does
it diminish it because the top things are not large?

Or perhaps we can do more broad-stroke list items, like monitoring or
performance, as listed below.

---

Simon Riggs wrote:
> I'd like to include a section on Major changes in this release at the
> top of the release notes, as has been done for at least the last 6 major
> releases. The notes below are one stab at that, for **discussion**. I've
> tried to arrange specific changes into groups... 
> 
> 
> Major changes in this release:
> 
> Improved scalability and performance on multi-processor systems (Tom,
> Alvaro, Itagaki, Qingqing, Heikki)
> 
>   A variety of changes improves the performance of both sequential scans
> and index scans, as well as enhancing multi-processor scalability. The
> advanced query optimizer has also been further enhanced, allowing
> indexes and partitioning to be useful in more cases. 
> 
> Improved utility and large query performance (Tom, Simon, Alon, Andreas)
> 
>   Large sorts will have typical performance increases of 100-300%,
> improving complex queries and creating new indexes. Loading times have
> also been reduced. Large queries, data loads, upgrades and restores will
> be considerably improved.
> 
> Improved monitoring and performance tuning (Tom, Bruce, Greg, Larry)
> 
>   Overhead of statistics collection has been considerably reduced and new
> statistics and system information is available. Better query logging
> improves diagnostics and especially performance tuning. Server now
> includes DTrace support. Indexes can now also be created CONCURRENTLY,
> allowing application tuning without effecting server availability.
> 
> Zero administration overhead now possible (Alvaro)
> 
>   With autovacuum enabled, all required vacuuming will now take place
> without administrator intervention enabling wider distribution of
> embedded databases.
> 
> Improved defaults and configuration (Peter, Andrew)
> 
>   Installation defaults are now improved for many tunable memory
> parameters and these can now be specified in kB, MB and GB. 
> 
> Warm Standby Servers for High Availability (Simon, Tom)
> 
>   Warm Standby servers can now be more easily configured and are
> appropriate in a wider range of circumstances than previously.
> 
> Improved scalability and performance of text search: GIN and Tsearch2
> (Teodor, Oleg)
> 
>   New GIN indexes allow much larger text search indexes than were
> previously possible. TSearch2 has been enhanced and performance has also
> been greatly improved.
> 
> Enhanced DML Functionality (Jonah, Joe, Tom, Susanne, Atsushi)
> 
>   INSERT/UPDATE/DELETE RETURNING and INSERT .. VALUES (), VALUES (),
> VALUES () allow more efficient application designs. Enhancements to
> UPDATE and DELETE allow additional constructs for clarity and ease of
> use.
> 
> SQL:2003 Analytical functions (Sergey, Tom, Neil)
> 
>   All statistical aggregate functions defined by SQL:2003 are now
> supported and user-defined aggregates now can take multiple columns as
> inputs.
> 
> -- 
>   Simon Riggs 
>   EnterpriseDB   http://www.enterprisedb.com
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

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

Re: [HACKERS] advisory locks and permissions

[Jeremy Drake]

On Wed, 20 Sep 2006, Bruce Momjian wrote:

>
> Doesn't creating many temp tables in a transaction do the same thing?
>
> ---

Like this?

jeremyd=# CREATE OR REPLACE FUNCTION testy(n integer) returns integer as $$
BEGIN
 EXECUTE 'CREATE TEMP TABLE testy_' || n::text || ' (a integer, b text);';
 RETURN n;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;
CREATE FUNCTION
jeremyd=# select testy(n) from generate_series(1,100) n;
WARNING:  out of shared memory
CONTEXT:  SQL statement "CREATE TEMP TABLE testy_3323 (a integer, b
text);" PL/pgSQL function "testy" line 2 at execute statement
ERROR:  out of shared memory
HINT:  You may need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "CREATE TEMP TABLE testy_3323 (a integer, b
text);"
PL/pgSQL function "testy" line 2 at execute statement



>
> Josh Berkus wrote:
> > All,
> >
> > > I vote for locking down to superuser access (lets be frank here: I
> > > would estimate 90%+ database installatons run with the application as
> > > root) so we are not losing much.
> >
> > Not in my experience.   Note that making them superuser-only pretty much 
> > puts
> > them out of the hands of hosted applications.
> >
> > How simple would it be to limit the number of advisory locks available to a
> > single request?  That would at least make the DOS non-trivial.  Or to put in
> > a handle (GUC?) that allows turning advisory locks off?
> >
> > Hmmm ... I'll bet I could come up with other ways to use generate_series in 
> > a
> > DOS, even without advisory locks ...
> >
> > --
> > Josh Berkus
> > PostgreSQL @ Sun
> > San Francisco
> >
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
>
>

-- 
Two percent of zero is almost nothing.

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

Re: [HACKERS] Release Notes: Major Changes in 8.2

[Josh Berkus]

Bruce,

What happened to PL/pgSQL debugging?  Did it die?

-- 
Josh Berkus
PostgreSQL @ Sun
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] Release Notes: Major Changes in 8.2

[Zdenek Kotala]

Simon Riggs napsal(a):



Improved monitoring and performance tuning (Tom, Bruce, Greg, Larry)

Overhead of statistics collection has been considerably reduced and new
statistics and system information is available. Better query logging
improves diagnostics and especially performance tuning. Server now
includes DTrace support. Indexes can now also be created CONCURRENTLY,
allowing application tuning without effecting server availability.



You forgot to Robert Lor - author of DTrace support.

Zdenek

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

Re: [HACKERS] 'configure --disable-shared' and 'make check'

[Albe Laurenz]

Peter Eisentraut wrote:
>> I notice that when I run 'make check' on a
>> statically linked HEAD, it fails during
>> 'createlang' with
> 
> Because createlang relies on *dynamic* loading.

So that is working as designed.
I interpret that as 'static builds for the database
server are not supported'. Ok by me.

Yours,
Laurenz Albe

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