Re: [HACKERS] DROP DATABASE vs patch to not remove files right away

[Heikki Linnakangas]

Tom Lane wrote:

Heikki Linnakangas <[EMAIL PROTECTED]> writes:

Patch attached that does the three changes we've talked about:'
- make ForgetDatabaseFsyncRequests forget unlink requests as well
- make rmtree() not fail on ENOENT
- force checkpoint on in dropdb on all platforms


This looks fine as far as it goes


Ok, committed.


but I'm still thinking it's a bad
idea for rmtree() to fall over on the first failure.  I propose that it
ought to keep trying to delete the rest of the target directory tree.
Otherwise, one permissions problem (for example) could lead to most
of a dropped database not getting freed up,

>

If there aren't objections, I'll make that happen after Heikki applies
his patch.


Agreed. Be my guest.

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

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

Re: [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Martijn van Oosterhout]

On Thu, Apr 17, 2008 at 03:18:54PM +0200, Peter Eisentraut wrote:
> I think the bottom line is just that having statement_timeout a global 
> setting 
> is stupid for a variety of reasons (dump, restore, vacuum, locks, incidental 
> delays) that we should discourage it (or prevent it, as proposed elsewhere) 
> rather than working around it in countless individual places.

maintainence_statement_timeout? :)

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [HACKERS] get rid of psql welcome message

[Joshua D. Drake]

On Fri, 18 Apr 2008 00:42:06 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
 
> I'm +1 for cutting that down to a single line.  I don't care one way
> or the other about providing a .psqlrc option to suppress it
> altogether.

Peter do you want to run with this, or would you mind if I worked up a
patch?

Joshua D. Drake


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit



signature.asc
Description: PGP signature

Re: [HACKERS] get rid of psql welcome message

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> I think it's getting overlooked because most people don't deal with it, but I
> really think we need to keep the SSL info as is.

Well, in general the *variable* parts of the banner were all put there
because of fairly urgent need, and I'd resist removing them.  It's the
unchanging boilerplate that seems open to debate.

I'm +1 for cutting that down to a single line.  I don't care one way or
the other about providing a .psqlrc option to suppress it altogether.

regards, tom lane

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

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Greg Smith wrote:
> >> Scraping that HTML seems like it would be pretty straightforward.
> 
> > It's awfully incomplete.  Bruce said to me the other day on IM that the
> > list he was getting with the Linux version of find_typedef was something
> > like 2800 symbols.  I checked the doxygen list and I only see about a
> > dozen for each letter, so there's a whole lot missing here.
> 
> [ click click... ]  A quick grep counts 2154 occurrences of the word
> 'typedef' in our tree.  Some of them are no doubt false hits
> (documentation etc), but on the other hand you need to add typedefs
> coming from system headers.
> 
> doxygen's 200-some is clearly an order of magnitude too low, but I
> wonder whether Bruce's list hasn't got some false hits ...

My list is at:

http://momjian.us/tmp/pgtypedefs

pgindent is probably 97% optimal.  Getting a better typedef list will
change that to perhaps 97.2% optimal.  There is a lot of discussion
happening to try to get that 0.2%.  :-O

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] get rid of psql welcome message

[Joshua D. Drake]

On Fri, 18 Apr 2008 00:21:58 -0400
Robert Treat <[EMAIL PROTECTED]> wrote:

> > We could just do:
> >
> > psql 8.1.10 - postgresql server version 8.1.10
> >
> > Type: \h for SQL help, \? for psql help, \q to quit
> >
> > postgres=#
> 
> I think it's getting overlooked because most people don't deal with
> it, but I really think we need to keep the SSL info as is.  Actually
> I think we ought to keep the whole thing and just add the no-splash
> option for advanced users, but barring that, the SSL info is very
> handy when you're working on SSL enabled servers.  
> 

Is it enough to say "SSL: On"? Or do you want all the cert stuff?

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit



signature.asc
Description: PGP signature

Re: [HACKERS] get rid of psql welcome message

[Robert Treat]

On Thursday 17 April 2008 12:04, Joshua D. Drake wrote:
> On Thu, 17 Apr 2008 11:11:58 -0400
>
> Andrew Dunstan <[EMAIL PROTECTED]> wrote:
> > Brendan Jurd wrote:
> > > -BEGIN PGP SIGNED MESSAGE-
> > > Hash: SHA1
> > >
> > > On Fri, Apr 18, 2008 at 12:36 AM, Tom Lane  wrote:
> > >> Peter Eisentraut  writes:
> > >>  > Around  it
> > >>  > was proposed to truncate the psql welcome screen.  What do you
> > >>  > think about that?
> > >>
> > >>  Personally. I'm very seriously against losing the version number
> > >> banner. I could do without any of the rest of it.
>
> Currently our prompt is fairly verbose:
>
> Welcome to psql 8.1.10, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>\h for help with SQL commands
>\? for help with psql commands
>\g or terminate with semicolon to execute query
>\q to quit
>
>
> We could just do:
>
> psql 8.1.10 - postgresql server version 8.1.10
>
> Type: \h for SQL help, \? for psql help, \q to quit
>
> postgres=#

I think it's getting overlooked because most people don't deal with it, but I 
really think we need to keep the SSL info as is.  Actually I think we ought 
to keep the whole thing and just add the no-splash option for advanced users, 
but barring that, the SSL info is very handy when you're working on SSL 
enabled servers.  

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [HACKERS] Lessons from commit fest

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Greg Smith wrote:
>> Scraping that HTML seems like it would be pretty straightforward.

> It's awfully incomplete.  Bruce said to me the other day on IM that the
> list he was getting with the Linux version of find_typedef was something
> like 2800 symbols.  I checked the doxygen list and I only see about a
> dozen for each letter, so there's a whole lot missing here.

[ click click... ]  A quick grep counts 2154 occurrences of the word
'typedef' in our tree.  Some of them are no doubt false hits
(documentation etc), but on the other hand you need to add typedefs
coming from system headers.

doxygen's 200-some is clearly an order of magnitude too low, but I
wonder whether Bruce's list hasn't got some false hits ...

regards, tom lane

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Greg Smith wrote:
> On Fri, 18 Apr 2008, Gregory Stark wrote:
>
>> The reason I was asking these questions was because I was thinking 
>> about how hard it would be to generate the list from a textual analysis 
>> instead of using object files.
>
> Is there some reason I don't understand why the listing doyxgen creates  
> isn't good enough here?  http://doxygen.postgresql.org/globals_type.html
>
> Scraping that HTML seems like it would be pretty straightforward.

It's awfully incomplete.  Bruce said to me the other day on IM that the
list he was getting with the Linux version of find_typedef was something
like 2800 symbols.  I checked the doxygen list and I only see about a
dozen for each letter, so there's a whole lot missing here.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)

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

Re: [HACKERS] Lessons from commit fest

[Greg Smith]

On Fri, 18 Apr 2008, Gregory Stark wrote:

The reason I was asking these questions was because I was thinking about 
how hard it would be to generate the list from a textual analysis 
instead of using object files.


Is there some reason I don't understand why the listing doyxgen creates 
isn't good enough here?  http://doxygen.postgresql.org/globals_type.html


Scraping that HTML seems like it would be pretty straightforward.

--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] Lessons from commit fest

[Andrew Dunstan]

Gregory Stark wrote:

But if we're still doing object file analysis on the build farm and it's easy
to add typedefs manually then perhaps there's not much effort saved by having
such a tool. I think it would be possible to write but it wouldn't really be
easy. You have to parse just enough C to find the typedef but not so much you
get confused by invalid C syntax caused by looking at both sides of #ifdef
branches.

  


I am pretty dead sure that a textual analysis tool is going to be far 
too much work to write and maintain, for the benefit we might get from it.


cheers

andrew



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

Re: [HACKERS] Lessons from commit fest

[Gregory Stark]

"Tom Lane" <[EMAIL PROTECTED]> writes:

"Tom Lane" <[EMAIL PROTECTED]> writes:

> Gregory Stark <[EMAIL PROTECTED]> writes:
>> "Tom Lane" <[EMAIL PROTECTED]> writes:
>
>> 1) I take it we feel safe guaranteeing that we won't use any fancy macros
>>inside typedefs. So no '#define pgtype(x) _pg_##x' or anythin like that.
>
> Hmm ... we are fairly crawling with struct tags built that way:
>
> /* Introduces a catalog's structure definition */
> #define CATALOG(name,oid) typedef struct CppConcat(FormData_,name)
>
> but offhand I can't think of any actual typedef names built with ##.
> Does indent need a preset list of struct tags?  Seems that would be
> stupid ...

It's not just ## that's a problem. Any macro used to build the typedef would
be a problem. There's not a whole lot of other reasons you would want to use
macros in a typedef but...

>> 3) How would this work with typedefs which come from system or library
>>includes?
>
> Ouch, that's a real good point.  Maybe a certain amount of platform
> dependence is inevitable.

The reason I was asking these questions was because I was thinking about how
hard it would be to generate the list from a textual analysis instead of using
object files.

Such a tool *cannot* use cpp to preprocess the file because it would defeat
much of the purpose. The point is that we want to find all the typedefs in all
the #ifdef branches.

But if we don't preprocess the files with CPP then macros like the one I
included before wouldn't be interpreted. Nor would we be pulling in system or
library headers, so no typedefs from them.

But if we're just interested in the names I suppose a hybrid approach would
work. 1) The build farm builds a list of typedefs found in all the various
builds and we check that into CVS. 2) a textual tool run as part of your
normal build builds a list of typedefs found in your tree.

But if we're still doing object file analysis on the build farm and it's easy
to add typedefs manually then perhaps there's not much effort saved by having
such a tool. I think it would be possible to write but it wouldn't really be
easy. You have to parse just enough C to find the typedef but not so much you
get confused by invalid C syntax caused by looking at both sides of #ifdef
branches.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Alvaro Herrera]

PFC wrote:

>   Plan = Tree
>   Tree = XML

If you want to propose a DTD I'm sure there would be many people
interested.

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

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

Re: [HACKERS] pgsql: Fix a couple of oversights associated with the "physical tlist"

[Gregory Stark]

[resending because the first went to -committers which is kind of bogus]

"Tom Lane" <[EMAIL PROTECTED]> writes:

> Log Message:
> ---
> Fix a couple of oversights associated with the "physical tlist" optimization:
> we had several code paths where a physical tlist could be used for the input
> to a Sort node, which is a dumb idea because any unneeded table columns will
> increase the volume of data the sort has to push around.

+   /* Detect if we'll need an explicit sort for grouping */
+   if (parse->groupClause && !use_hashed_grouping &&
+   !pathkeys_contained_in(group_pathkeys, 
current_pathkeys))
+   {
+   need_sort_for_grouping = true;
+   /*
+* Always override query_planner's tlist, so 
that we don't
+* sort useless data from a "physical" tlist.
+*/
+   need_tlist_eval = true;
+   }

Does this do the right thing if all the columns in the physical target list
are in fact present in the target list? Is it going to force us to do extra
work to reconstruct the same data?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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

Re: [HACKERS] Lessons from commit fest

[Aidan Van Dyk]

* Tom Lane <[EMAIL PROTECTED]> [080417 20:47]:
 
> Right, but if the only use is inside #ifdef __BRAND_X_PLATFORM__,
> the only way for the proposed toolchain to extract that usage is
> if someone runs it on BRAND_X_PLATFORM.
> 
> Of course, for seldom-used platforms maybe we don't care that much.

But if we're talking about putting a "definitive list" of them in the
source, we can build that list in a few iterations, with any method we
want (including using Bruce's list as a starting point, some inspection,
some grep/awk/perl, etc).  It's not something that will need to be
re-run on a continual basis and produce a definitive list each and every
run.

a.

-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Lessons from commit fest

[Tom Lane]

Aidan Van Dyk <[EMAIL PROTECTED]> writes:
> * Tom Lane <[EMAIL PROTECTED]> [080417 20:11]:
>> Ouch, that's a real good point.  Maybe a certain amount of platform
>> dependence is inevitable.

> I don't get it.  If they are used as typedefs *anywhere* in the PG
> source, they're needed in the typedef list.

Right, but if the only use is inside #ifdef __BRAND_X_PLATFORM__,
the only way for the proposed toolchain to extract that usage is
if someone runs it on BRAND_X_PLATFORM.

Of course, for seldom-used platforms maybe we don't care that much.

regards, tom lane

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

Re: [HACKERS] Cross-field statistics

[Decibel!]

On Apr 17, 2008, at 12:22 PM, Gregory Stark wrote:

"Decibel!" <[EMAIL PROTECTED]> writes:


For each field that isn't already in a set of field groupings
 * Sort sample rows on that field
 * Calculate correlation for all other fields
 * If there are other fields that have a correlation to this sort   
order over
some threshold, save them along with the field we  originally  
sorted on as a

new 'field grouping'
 * Else, there are no other fields that group with this field;  
it's  a "loner"


I think this is going somewhere. But "correlation" isn't quite  
right. It has
the same problem our use of correlation for clusteredness has.  
Consider the
case of Zip code and City. They're nearly very non-independent  
variables but

there's basically no correlation.


If we have a limited number of values in one of the fields, it would  
be possible to build a histogram of values for other fields based on  
the values in the first field. But I can't see how we could possibly  
represent something like city, zip in a compact form. You would have  
to keep a range of zips that cover a city.


Hmm... but we only care about cities that have a substantial number  
of zip codes. This is what the equivalent of the most-common-values  
list would be for cross-platform stats: for the most_common_vals in  
column a, you store a range or histogram of the corresponding values  
in b, assuming that there is a good correspondence.


For each field grouping, at a minimum we'd need to store a  
histogram  for that

grouping.


This is a problem. What does a histogram on a grouping mean? It's  
not clear

how to come up with a histogram which can help answer questions like
  A between ? and ? and B between ? and ?

You can do a histogram on  or  but neither are going to be
especially useful. Heikki and I came up with a weird hybrid thing  
which might

be useful for avoiding overestimating selectivity like
  WHERE city='BOS' AND areacode = '617'

But it didn't help at all with the converse, ie:
 WHERE city='BOS' AND areacode = '212'

It's hard to see how we could possibly catch cases like that though.


If the two fields share the same correlation, then the histogram is  
just what we use right now. We could actually do this today, but only  
for fields with a high physical correlation. What I was describing  
allowed extending this to fields that have a high correlation to each  
other, even if they didn't have a high physical correlation. I know  
that this doesn't help us for things like city/area code or city/zip,  
but other than my idea above I'm rather at a loss on how to represent  
that in a compact fashion.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Lessons from commit fest

[Aidan Van Dyk]

* Tom Lane <[EMAIL PROTECTED]> [080417 20:11]:
 
> > 3) How would this work with typedefs which come from system or library
> >includes?
> 
> Ouch, that's a real good point.  Maybe a certain amount of platform
> dependence is inevitable.

I don't get it.  If they are used as typedefs *anywhere* in the PG
source, they're needed in the typedef list.  If they are not used in
the PG source, they aren't needed in the typedef list.


-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] RFD: hexstring(n) data type

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> However bytea has an inconvenient string representation. Really I would prefer
> if bytea were *always* read and written in hex format.

Yeah, the escaping rule is a mess: hard to work with and even less
storage-efficient than hex would be.

> We could have a guc to
> enable this but it would make it hard to write code which would function
> consistently as part of a larger application.

Well, the datetime types have had DateStyle for years and people have
managed to deal with it, so maybe you're overestimating the problem.

regards, tom lane

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

Re: [HACKERS] Lessons from commit fest

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> That would certainly be better than the current approach, since
>> presumably it would cover not only Windows but the other
>> conditionally-compiled stuff that Bruce chooses not to compile on
>> his own machine.

> It would, as someone said, rock. But it wouldn't really address the ability of
> a developer to run pgindent on code he's about to send in, since it wouldn't
> have any typedefs that developer just created.

Well, that list is just a simple text file listing typedef names,
so it'd hardly be difficult to add your own to the list.

>> I still wish we could build the list directly from the source code,
>> but I have no suggestions for tools that would do it.

> If we wanted to do that I have a few questions:

> 1) I take it we feel safe guaranteeing that we won't use any fancy macros
>inside typedefs. So no '#define pgtype(x) _pg_##x' or anythin like that.

Hmm ... we are fairly crawling with struct tags built that way:

/* Introduces a catalog's structure definition */
#define CATALOG(name,oid)   typedef struct CppConcat(FormData_,name)

but offhand I can't think of any actual typedef names built with ##.
Does indent need a preset list of struct tags?  Seems that would be
stupid ...

> 2) How much information do we need about the typedefs? Just their name?

Right.

> 3) How would this work with typedefs which come from system or library
>includes?

Ouch, that's a real good point.  Maybe a certain amount of platform
dependence is inevitable.

regards, tom lane

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

Re: [HACKERS] DROP DATABASE vs patch to not remove files right away

[Tom Lane]

Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Patch attached that does the three changes we've talked about:'
> - make ForgetDatabaseFsyncRequests forget unlink requests as well
> - make rmtree() not fail on ENOENT
> - force checkpoint on in dropdb on all platforms

This looks fine as far as it goes, but I'm still thinking it's a bad
idea for rmtree() to fall over on the first failure.  I propose that it
ought to keep trying to delete the rest of the target directory tree.
Otherwise, one permissions problem (for example) could lead to most
of a dropped database not getting freed up,

If there aren't objections, I'll make that happen after Heikki applies
his patch.

regards, tom lane

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

Re: [HACKERS] RFD: hexstring(n) data type

[Gregory Stark]

I don't think we should define types based on how they print. We should define
types based on what they contain. bytea is a perfectly good datatype for
storing binary data, though perhaps we could use a bytea(n) for fixed size
binary data.

However bytea has an inconvenient string representation. Really I would prefer
if bytea were *always* read and written in hex format. We could have a guc to
enable this but it would make it hard to write code which would function
consistently as part of a larger application.

I think this is more akin to the MONEY data type. Really it would be better if
we could declare columns as NUMERIC but attach a "default format" to them for
use when string representation is desired. Similarly with bytea we could
choose a default string representation different from the default in/out
functions.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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

Re: [HACKERS] Lessons from commit fest

[Gregory Stark]

"Tom Lane" <[EMAIL PROTECTED]> writes:

> Andrew Dunstan <[EMAIL PROTECTED]> writes:
>> I have been thinking of pursuing your suggestion of having it as a 
>> buildfarm option. We could provide a SOAP interface to collect the 
>> typedefs and then consolidate them and put them in CVS. We could even do 
>> it per release. That would include Windows, although only MinGW, not 
>> MSVC, which doesn't have objdump.
>
> That would certainly be better than the current approach, since
> presumably it would cover not only Windows but the other
> conditionally-compiled stuff that Bruce chooses not to compile on
> his own machine.

It would, as someone said, rock. But it wouldn't really address the ability of
a developer to run pgindent on code he's about to send in, since it wouldn't
have any typedefs that developer just created.

> I still wish we could build the list directly from the source code,
> but I have no suggestions for tools that would do it.

If we wanted to do that I have a few questions:

1) I take it we feel safe guaranteeing that we won't use any fancy macros
   inside typedefs. So no '#define pgtype(x) _pg_##x' or anythin like that.

2) How much information do we need about the typedefs? Just their name?

3) How would this work with typedefs which come from system or library
   includes?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[PFC]

On Thu, 17 Apr 2008 20:42:49 +0200, Simon Riggs <[EMAIL PROTECTED]>  
wrote:



On Thu, 2008-04-17 at 12:34 -0400, Tom Lane wrote:


I'm tempted to propose redefining the currently-nearly-useless
EXPLAIN VERBOSE option as doing this.


Yes please.

Sounds like a good home for other useful things also.

I'd like to have an EXPLAIN mode that displayed the plan without *any*
changeable info (i.e. no costs, row counts etc). This would then allow
more easy determination of whether plans had changed over time. (But
EXPLAIN TERSE sounds silly).



Plan = Tree
Tree = XML

EXPLAIN ANALYZE SELECT * FROM test NATURAL JOIN test2 WHERE id  
=ANY('{3,666,975,521'});

QUERY PLAN
---
 Nested Loop  (cost=17.04..65.13 rows=1 width=8) (actual  
time=51.835..51.835 rows=0 loops=1)

   Join Filter: (test.value = test2.value)
   ->  Bitmap Heap Scan on test  (cost=17.04..31.96 rows=4 width=8)  
(actual time=16.622..16.631 rows=4 loops=1)

 Recheck Cond: (id = ANY ('{3,666,975,521}'::integer[]))
 ->  Bitmap Index Scan on test_pkey  (cost=0.00..17.04 rows=4  
width=0) (actual time=16.613..16.613 rows=4 loops=1)

   Index Cond: (id = ANY ('{3,666,975,521}'::integer[]))
   ->  Index Scan using test2_pkey on test2  (cost=0.00..8.28 rows=1  
width=8) (actual time=8.794..8.795 rows=1 loops=4)

 Index Cond: (test2.id = test.id)

EXPLAIN XML ...









Nicely parsable and displayable in all its glory in pgadmin ;)

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

Re: [HACKERS] count(*) performance improvement ideas

[PFC]

My wife has a snake phobia, besides, I've just started learning Scala.


	Just had a look at Scala, it looks nice. Slightly Lispish (like all good  
languages)...



txid_current()
No... hold on, it is per session, and a session can't have two or more  
transactions active at once can it?


It could be used to detect rollback.

So the problem is that other functions may be using GD themselves, and  
your own code is at the mercy of the other functions. Conversely you  
shouldn't clear GD, as some other function may be using it.


Exactly.

So you're better off using a single function for everything, and using  
SD within it?


	Since the purpose is to store counts for rows matching a certain criteria  
in a set of tables, you could build a hashtable of hashtables, like :


GD[table name][criteria name][criteria value] = count

	This would add complexity, about half a line of code. But you'd have to  
create lots of plpgsql trigger functions to wrap it.


There isn't any way of telling whether the function is being called for  
the first time in a transaction. You don't know when to clear it.


The first time in a session, GD will be empty.
	Clearing it at the start of a transaction would not be useful (clearing  
it at ROLLBACK would).
	It is updating the "real" summary table with the contents of this hash  
that is the problem, also.


	So, basically, if you connect, do one insert, and disconnect, this would  
be useless.
	But, if you do a zillion inserts, caching the counts deltas in RAM would  
be faster.
	And if you use persistent connections, you could update the counts in the  
real table only every N minutes, for instance, but this would need some  
complicity from the backend.



Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement,  
customer focus, and courage. This email with any attachments is  
confidential and may be subject to legal privilege.  If it is not  
intended for you please advise by reply immediately, destroy it and do  
not copy, disclose or use it in any way.

__
  This email has been scanned by the DMZGlobal Business Quality
  Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__






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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Greg Smith]

On Thu, 17 Apr 2008, Tom Lane wrote:


For debugging the planner work I'm about to do, I'm expecting it will be
useful to be able to get EXPLAIN to print the targetlist of each plan
node, not just the quals (conditions) as it's historically done.


I've heard that some of the academic users of PostgreSQL were hoping to 
add features in this area in order to allow better using planner internals 
for educational purposes.  It would be nice if that were available for 
such purposes without having to recompile.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] get rid of psql welcome message

[Chris Browne]

[EMAIL PROTECTED] (Stephen Frost) writes:
> * Peter Eisentraut ([EMAIL PROTECTED]) wrote:
>> Around  
>> it 
>> was proposed to truncate the psql welcome screen.  What do you think about 
>> that?
>
> I'd recommend an option in .psqlrc to disable it, if possible.  That
> would be in line with what alot of other "splash-screen" type things do.

Shorten:

Welcome to psql 8.1.9 (server 8.1.8), the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

To:

psql 8.1.9 (server 8.1.8) - PostgreSQL interactive terminal
Type: \h for SQL help, \? for psql help, \q to quit

which removes 3/4 of the "bloat," whilst only losing info about
\copyright and \g.

That's close enough to an 80% improvement for me.  

That *would* be a big win in doing cut'n'paste of psql sessions, and
while the experienced user may not care about \h, \?, and \q, I'd miss
getting the version information.

There's enough room still there, by the way, that one might cleverly
add in the port number without forcing the addition of an extra line,
which could be useful material, even in a cut'n'paste...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "linuxfinances.info")
http://www3.sympatico.ca/cbbrowne/spiritual.html
Editing is a rewording activity.
-- Alan J. Perlis
[And EMACS a rewording editor.  Ed.]

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

Re: [HACKERS] pg_terminate_backend() issues

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> The closest thing I can think of to an automated test is to run repeated
> >> sets of the parallel regression tests, and each time SIGTERM a randomly
> >> chosen backend at a randomly chosen time.  Then see if anything "funny"
> 
> > Yep, that was my plan, plus running the parallel regression tests you
> > get the possibility of >2 backends.
> 
> I was intentionally suggesting only one kill per test cycle.  Multiple
> kills will probably create an O(N^2) explosion in the set of possible
> downstream-failure deltas.  I doubt you'd really get any improvement
> in testing coverage to justify the much larger amount of hand validation
> needed.
> 
> It also strikes me that you could make some simple alterations to the
> regression tests to reduce the set of observable downstream deltas.
> For example, anyplace where a test loads a table with successive INSERTs
> and that table is used by later tests, wrap the INSERT sequence with
> BEGIN/END.  Then there is only one possible downstream delta (empty
> table) and not N different possibilities for an N-row table.

I have added pg_terminate_backend() to use SIGTERM and will start
running tests as discussed with Tom.  I will post my scripts too.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] count(*) performance improvement ideas

[Stephen Denne]

> > The big gotcha is that these are all non-transactional 
> > : if you rollback,  
> > GD and SD stay the same, and when you issue a query, you can 
> > assume the  
> > state of SD and GD is random (due to previous queries) unless you  
> > initialize them to a known value.
> 
> Using txid_current() as a key should alleviate that.

No... hold on, it is per session, and a session can't have two or more 
transactions active at once can it?

(Though I hear of things called sub-transactions)

So the problem is that other functions may be using GD themselves, and your own 
code is at the mercy of the other functions. Conversely you shouldn't clear GD, 
as some other function may be using it.

So you're better off using a single function for everything, and using SD 
within it?

There isn't any way of telling whether the function is being called for the 
first time in a transaction. You don't know when to clear it.

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer 
focus, and courage. This email with any attachments is confidential and may be 
subject to legal privilege.  If it is not intended for you please advise by 
reply immediately, destroy it and do not copy, disclose or use it in any way.
__
  This email has been scanned by the DMZGlobal Business Quality
  Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__


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

Re: [HACKERS] get rid of psql welcome message

[Andreas 'ads' Scherbaum]

On Thu, 17 Apr 2008 09:30:04 -0400 Stephen Frost wrote:

> * Peter Eisentraut ([EMAIL PROTECTED]) wrote:
> > Around  
> > it 
> > was proposed to truncate the psql welcome screen.  What do you think about 
> > that?
> 
> I'd recommend an option in .psqlrc to disable it, if possible.  That
> would be in line with what alot of other "splash-screen" type things do.

As long as the default is to display the welcome message, that's ok.
Like Simon explained it would be no good if we change the default
behavior.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

Re: [HACKERS] get rid of psql welcome message

[Andreas 'ads' Scherbaum]

On Thu, 17 Apr 2008 15:58:10 +0200 Peter Eisentraut wrote:

> Mike Aubury wrote:
> > Am I missing something..
> >
> > $ psql -q testdb
> > testdb=#
> 
> This also quiets out a few other unrelated things.

Like all \timing messages *grumble*

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

Re: [HACKERS] count(*) performance improvement ideas

[Stephen Denne]

PFC wrote
> <[EMAIL PROTECTED]> wrote:
> > I don't know plpythonu (nor python), just read a few docs now:
> 
>   Learn Python, it is a really useful language ;)

My wife has a snake phobia, besides, I've just started learning Scala.


>   There is no sharing between processes, so
>   - both SD and GD are limited to the current session 
> (connection, postgres  
> process), no shared memory is involved
>   - GD is global between all python functions (global)
>   - SD is specific to each python function (static)

Thanks.

>   The big gotcha is that these are all non-transactional 
> : if you rollback,  
> GD and SD stay the same, and when you issue a query, you can 
> assume the  
> state of SD and GD is random (due to previous queries) unless you  
> initialize them to a known value.

Using txid_current() as a key should alleviate that.

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer 
focus, and courage. This email with any attachments is confidential and may be 
subject to legal privilege.  If it is not intended for you please advise by 
reply immediately, destroy it and do not copy, disclose or use it in any way.
__
  This email has been scanned by the DMZGlobal Business Quality
  Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__


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

Re: [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Heikki Linnakangas]

Greg Sabino Mullane wrote:

A use case would be dumping a large table and wanting to
load it into the database, but wanting to stop the job if it
is still running an hour from now, when a maintenance window
is scheduled to start.


statement_timeout is pretty useless for that purpose, because it limits 
the time on a per-statement basis. It would cancel the COPY of any 
tables larger than X, but if you have multiple tables (a partitioned 
table, perhaps) just below the threshold, they would all be dumped even 
though the cumulative time is well beyond statement_timeout.


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

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

Re: [HACKERS] DROP DATABASE vs patch to not remove files right away

[Heikki Linnakangas]

Tom Lane wrote:

Heikki Linnakangas <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

ISTM that we must fix the bgwriter so that ForgetDatabaseFsyncRequests
causes PendingUnlinkEntrys for the doomed DB to be thrown away too.


Because of the asynchronous nature of ForgetDatabaseFsyncRequests, this 
still isn't enough, I'm afraid.


Hm.  I notice that there is no bug on Windows because dropdb forces a
checkpoint before it starts to remove files.  Maybe the sanest solution
is to just do that on all platforms.


Yeah, I don't see any other simple solution.

Patch attached that does the three changes we've talked about:'
- make ForgetDatabaseFsyncRequests forget unlink requests as well
- make rmtree() not fail on ENOENT
- force checkpoint on in dropdb on all platforms

plus some comment changes reflecting what we now know. I will apply this 
tomorrow if there's no objections.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
Index: src/backend/commands/dbcommands.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.205
diff -c -r1.205 dbcommands.c
*** src/backend/commands/dbcommands.c	26 Mar 2008 21:10:37 -	1.205
--- src/backend/commands/dbcommands.c	17 Apr 2008 18:53:17 -
***
*** 696,713 
  	pgstat_drop_database(db_id);
  
  	/*
! 	 * Tell bgwriter to forget any pending fsync requests for files in the
! 	 * database; else it'll fail at next checkpoint.
  	 */
  	ForgetDatabaseFsyncRequests(db_id);
  
  	/*
! 	 * On Windows, force a checkpoint so that the bgwriter doesn't hold any
! 	 * open files, which would cause rmdir() to fail.
  	 */
- #ifdef WIN32
  	RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_FORCE | CHECKPOINT_WAIT);
- #endif
  
  	/*
  	 * Remove all tablespace subdirs belonging to the database.
--- 696,714 
  	pgstat_drop_database(db_id);
  
  	/*
! 	 * Tell bgwriter to forget any pending fsync and unlink requests for files
! 	 * in the database; else it'll fail at next checkpoint, or worse it will
! 	 * delete files that belong to a newly created database with the same OID.
  	 */
  	ForgetDatabaseFsyncRequests(db_id);
  
  	/*
! 	 * Force a checkpoint to make sure the bgwriter has received the message
! 	 * sent by ForgetDatabaseFsyncRequests. On Windows, this also ensures that
! 	 * the bgwriter doesn't hold any open files, which would cause rmdir() to
! 	 * fail.
  	 */
  	RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_FORCE | CHECKPOINT_WAIT);
  
  	/*
  	 * Remove all tablespace subdirs belonging to the database.
Index: src/backend/storage/smgr/md.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/storage/smgr/md.c,v
retrieving revision 1.136
diff -c -r1.136 md.c
*** src/backend/storage/smgr/md.c	10 Mar 2008 20:06:27 -	1.136
--- src/backend/storage/smgr/md.c	17 Apr 2008 19:26:16 -
***
*** 1196,1203 
  		if (unlink(path) < 0)
  		{
  			/*
! 			 * ENOENT shouldn't happen either, but it doesn't really matter
! 			 * because we would've deleted it now anyway.
  			 */
  			if (errno != ENOENT)
  ereport(WARNING,
--- 1196,1206 
  		if (unlink(path) < 0)
  		{
  			/*
! 			 * There's a race condition, when the database is dropped at the
! 			 * same time that we process the pending unlink requests. If the
! 			 * DROP DATABASE deletes the file before we do, we will get ENOENT
! 			 * here. rmtree() also has to ignore ENOENT errors, to deal with
! 			 * the possibility that we delete the file first.
  			 */
  			if (errno != ENOENT)
  ereport(WARNING,
***
*** 1321,1327 
--- 1324,1334 
  		/* Remove any pending requests for the entire database */
  		HASH_SEQ_STATUS hstat;
  		PendingOperationEntry *entry;
+ 		ListCell   *cell, 
+    *prev,
+    *next;
  
+ 		/* Remove fsync requests */
  		hash_seq_init(&hstat, pendingOpsTable);
  		while ((entry = (PendingOperationEntry *) hash_seq_search(&hstat)) != NULL)
  		{
***
*** 1331,1336 
--- 1338,1359 
  entry->canceled = true;
  			}
  		}
+ 	
+ 		/* Remove unlink requests */
+ 		prev = NULL;
+ 		for (cell = list_head(pendingUnlinks); cell; cell = next)
+ 		{
+ 			PendingUnlinkEntry *entry = (PendingUnlinkEntry *) lfirst(cell);
+ 
+ 			next = lnext(cell);
+ 			if (entry->rnode.dbNode == rnode.dbNode) 
+ 			{
+ pendingUnlinks = list_delete_cell(pendingUnlinks, cell, prev);
+ pfree(entry);
+ 			}
+ 			else
+ prev = cell;
+ 		}
  	}
  	else if (segno == UNLINK_RELATION_REQUEST)
  	{
***
*** 1386,1392 
  }
  
  /*
!  * ForgetRelationFsyncRequests -- ensure any fsyncs for a rel are forgotten
   */
  void
  ForgetRelationFsyncRequests(RelFileNode rnode)
--- 1409,1415 
  }
  
  /*
!  * ForgetRelationFsyncRequests -- forget any fsyncs for a rel
   */
  void
  ForgetRelationFsyncRequests(Re

Re: [HACKERS] RFD: hexstring(n) data type

[Bruce Momjian]

I am confused how a hex type is any better than using the 'hex' decode()
format we already support:

test=> select decode('5476', 'hex');
 decode

 Tv
(1 row)

---

Dawid Kuroczko wrote:
> Following the discussion on making UUID data type to be much more liberal
> ( http://archives.postgresql.org/pgsql-hackers/2008-02/msg01214.php )
> I have decided to try to approach it from more general perspective.
> 
> The current state of code is available at:
> http://qnex.net/hexstring-0.1-2008-03-03.tgz
> 
> 
> And now for more details:
> 
> The idea is to have a data type HEXSTRING(n) which can have an optional
> typemod specifying the size of data (in bytes).
> 
> Internally the datatype is binary compatible with bytea, but I/O is done as
> hex-encoded strings.  The format is liberal, isspace() and ispunct() 
> characters
> are skipped while the digits are read.
> 
> I have played with two versions of hexstringin() function, one which uses
> strtoul() function and the other which uses "home brew" code.  The latter
> appears to be faster, so I stayed with that.  But I would appreciate
> comments on this from more experienced.
> 
> So, what are the use cases?
> 
> CREATE DOMAIN liberal_uuid AS hexstring(16);
> CREATE DOMAIN liberal_macaddr AS hexstring(6);
> 
> ...it allows for creating other standard hex-types, as for example:
> CREATE DOMAIN wwn AS hexstring(8); --
> http://en.wikipedia.org/wiki/World_Wide_Name
> 
> Also it can be a convenient alternative to bytea format (I know, the
> encode()/decode() pair),
> especially when you have to format output data as some fancy hex-string.
> 
> The code is currently just a bunch of input/output/typemod functions
> which appear
> to work.  I will add casts, operators, etc -- they most likely will be
> nicked from bytea.
> 
> What I would like to also add is ubiquitous to_char(hex, format) function.
> For an UUID-compatilbe format it would be called as:
> SELECT to_char(hex, '----') or
> SELECT to_char(hex, '8x-4x-4x-4x-12x') where x is expanded as
> [0-9a-f] digit and X is expanded as [0-9A-F].
> I am not sure what to do about variable length hexstrings, I am
> considering something
> like to_char(hex, '8X-') which would produce something like
> '--'
> for a 12-byte hexstring (what to do about dangling '-' ?).
> 
> ...but the original case against liberal UUID was that it would make
> the I/O slower.
> My simple test:
> 
> postgres=# CREATE TABLE uuids AS SELECT uuid_generate_v4()::text AS u
> FROM generate_series(1,1000);
> CREATE
> 
> postgres=# CREATE TEMP TABLE text ON COMMIT DROP AS SELECT
> u::hexstring(16) FROM uuids;
> SELECT
> Time: 13058.486 ms
> postgres=# CREATE TEMP TABLE test ON COMMIT DROP AS SELECT u::uuid FROM uuids;
> SELECT
> Time: 13536.816 ms
> 
> ...now hexstring is varlena type but does not use strtoul.  Perhaps
> uuid might be more liberal too.
> 
> What do you think about it?
> 
>Regards,
>  Dawid
> 
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your Subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-hackers

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] get rid of psql welcome message

[Shane Ambler]

Simon Riggs wrote:

On Thu, 2008-04-17 at 09:30 -0400, Stephen Frost wrote:

* Peter Eisentraut ([EMAIL PROTECTED]) wrote:
Around  it 
was proposed to truncate the psql welcome screen.  What do you think about 
that?

I'd recommend an option in .psqlrc to disable it, if possible.  That
would be in line with what alot of other "splash-screen" type things do.


+1



+1

I honestly don't care that I get a few lines of garbage as I start psql 
- I never really look at it myself (the first dozen times I used pg it 
was probably helpful to have the help commands there).
So what if you get a few lines of text as you start a program, it 
scrolls off the screen with everything else, it doesn't fill up your 
drive in log files and I doubt the 250 bytes being sent across the 
network for those running remotely is going to chew up anyone's 
bandwidth allocation.


I do think that an rc file option (or even a ./configure option if you 
want to go that far) is fine for those in the know to adjust to their 
tastes - a better option than not show it once a .psql_history exists.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Simon Riggs]

On Thu, 2008-04-17 at 12:34 -0400, Tom Lane wrote:

> I'm tempted to propose redefining the currently-nearly-useless
> EXPLAIN VERBOSE option as doing this.

Yes please.

Sounds like a good home for other useful things also.

I'd like to have an EXPLAIN mode that displayed the plan without *any*
changeable info (i.e. no costs, row counts etc). This would then allow
more easy determination of whether plans had changed over time. (But
EXPLAIN TERSE sounds silly).

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

Re: [HACKERS] Lessons from commit fest

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Chris Browne <[EMAIL PROTECTED]> writes:
>> Would it be a terrible idea to...
>> 
>> - Draw the indent code from NetBSD into src/tools/pgindent
>
> I am not real eager to become maintainers of our own indent fork, which
> is what you propose.  (Just for starters, what will we have to do to
> make it run on non-BSD systems?)
>
>> We are presently at the extreme position where pgindent is run once in
>> a very long time (~ once a year), at pretty considerable cost, and
>> with the associated cost that a whole lot of indentation problems are
>> managed by hand.
>
> Yeah.  One reason for that is that the typedef problem makes it a pretty
> manual process.

As I hear more about the "typedef problem," a part of me gets more and
more appalled...  It seems like we're creating some problem for
ourselves in that the typedefs don't seem to be able to be consistent.

I don't have an answer, but it's looking like a sore tooth that
clearly needs attention.

> The main problem I see with "pgindent early and often" is that it only
> works well if everyone is using exactly the same pgindent code (and
> exactly the same typedef list).  Otherwise you just get buried in
> useless whitespace diffs.
>
> It's bad enough that Bruce whacks around his copy from time to time :-(.
> I would say that the single greatest annoyance for maintaining our back
> branches is that patches tend to not back-patch cleanly, and well over
> half the time it's because of random reformattings done by pgindent
> to code that hadn't changed at all, but it had formatted differently
> the prior year. 
>
> For the same reason, my take on your "random whitespace changes are
> acceptable" theory is not no but hell no.  It's gonna cost us,
> permanently, in manual patch adjustments if we allow the repository to
> get cluttered with content-free diffs.

I don't want to be cavalier about it; I'm hoping that in the
discussion, some more stable answer may fall out.  Though with the
typedef issues that have emerged, I'm not entirely sanguine...
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxfinances.info/info/internet.html
HEADLINE: Suicidal twin kills sister by mistake! 

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

Re: [HACKERS] MERGE SQL Statement

[Chris Browne]

[EMAIL PROTECTED] (Simon Riggs) writes:
>> Should there be a new rule option?  ie. ON MERGE rules ? 
>
> Maybe, but not as part of this project.

That seems to warrant a bit of elaboration...

If we're running a MERGE, and it performs an INSERT or UPDATE of a
particular tuple in(to) a particular table, will it fire the ON
INSERT/ON UPDATE trigger?  I'd hope so...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/multiplexor.html
Canada,  Mexico,  and Australia  form  the  Axis  of Nations  That Are
Actually Quite Nice But Secretly Have Nasty Thoughts About America

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> Or is it estimating the width based on the belief that only the thousand
> column is actually going to be emitted?

Right.  The width is used to estimate how much space would be needed
for, eg, sorting or hashing the plan node's output.  In any case where
something like that is actually happening, we *should* be emitting only
the required columns, so I didn't see any particular need to make
use_physical_tlist change the reported width.  OTOH this bug shows that
maybe that was hiding useful information ...

regards, tom lane

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Gregory Stark]

"Tom Lane" <[EMAIL PROTECTED]> writes:

> I'm not convinced.  The width is often useful to understand why the
> planner did something (eg, chose a hash plan or not).  The exact
> contents of the targetlist are usually not nearly as interesting.

I've never seen a single post on any of the lists where anyone went through
that exercise though.

>  ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=1 width=4)
>Output: unique1, unique2, two, four, ten, twenty, hundred, 
> thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, 
> string4

I wonder if I even understand what width means. Or does the planner think most
of these columns are mostly null?

Or is it estimating the width based on the belief that only the thousand
column is actually going to be emitted?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

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

Re: [HACKERS] Lessons from commit fest

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I have been thinking of pursuing your suggestion of having it as a 
> buildfarm option. We could provide a SOAP interface to collect the 
> typedefs and then consolidate them and put them in CVS. We could even do 
> it per release. That would include Windows, although only MinGW, not 
> MSVC, which doesn't have objdump.

That would certainly be better than the current approach, since
presumably it would cover not only Windows but the other
conditionally-compiled stuff that Bruce chooses not to compile on
his own machine.

Note though that the existing find_typedef script only works on (some
variants of) Linux and BSD.  Porting it to a wider set of platforms
might be pretty painful.

I still wish we could build the list directly from the source code,
but I have no suggestions for tools that would do it.

regards, tom lane

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Gregory Stark wrote:

> The only thing is that if the whole point is to have patch submitters run
> pgindent on their own added code it won't work since their own code will be
> precisely the code with the missing typedefs. How easy is it to manually add a
> handful of typedefs to the list?

The list is just a plain text file with one typedef per line, so it
should be trivial.

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

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Andrew Dunstan wrote:

> I have been thinking of pursuing your suggestion of having it as a  
> buildfarm option. We could provide a SOAP interface to collect the  
> typedefs and then consolidate them and put them in CVS. We could even do  
> it per release. That would include Windows, although only MinGW, not  
> MSVC, which doesn't have objdump.

That would rock.

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

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> EXPLAIN VERBOSE is indeed ridiculous.

There are other ways to get that printout, too, if you really do need
it.

> IMHO You could also move "width" to VERBOSE while you're at it. In fact you'll
> probably want width in precisely the same cases where you want the target
> list.

I'm not convinced.  The width is often useful to understand why the
planner did something (eg, chose a hash plan or not).  The exact
contents of the targetlist are usually not nearly as interesting.

> So +1 for just redefining VERBOSE.

Barring other objections I'll go do that.


BTW, while testing the code I already found a bug:

regression=# set enable_hashagg to 0;
SET
regression=# explain select thousand from tenk1 group by 1;
 QUERY 
PLAN  
-
 Group  (cost=1122.39..1172.39 rows=998 width=4)
   Output: thousand
   ->  Sort  (cost=1122.39..1147.39 rows=1 width=4)
 Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, 
twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
 Sort Key: thousand
 ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=1 width=4)
   Output: unique1, unique2, two, four, ten, twenty, hundred, 
thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, 
string4
(7 rows)

Only the "thousand" column is needed, so why is it emitting all columns?
It's evidently allowing the "use physical tlist" optimization to fire,
which saves cycles inside the SeqScan node --- but in this context
that's penny-wise and pound-foolish, because we're pumping useless data
through the Sort.  There is code in the planner that's supposed to
notice the needs of the next level up, but it's not getting this case
right for some reason...

regards, tom lane

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

Re: [HACKERS] Lessons from commit fest

[Gregory Stark]

"Alvaro Herrera" <[EMAIL PROTECTED]> writes:

> Bruce Momjian wrote:
>
>> Based on that reaction I am not going to bother uploading my copy of the
>> typedefs.
>
> Please reconsider.  Not having pgindent work at all is not better than
> it working "only" 98%.

I think I'm rescinding my objection to checking a canonical list of typedefs
into CVS. I didn't realize how hard it was to generate these typedefs or how
important it was to have everyone using the same version. 

Since we really *don't* want individual developers rebuilding the list of
typedefs we don't have to worry about conflicts when the upstream list
changes.

Bruce's list of typedefs seems like a good start point for a "canonical" list
of typedefs. The idea of gathering the lists from the build farm and
consolidating them sounds like a good plan going forward.

The only thing is that if the whole point is to have patch submitters run
pgindent on their own added code it won't work since their own code will be
precisely the code with the missing typedefs. How easy is it to manually add a
handful of typedefs to the list?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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

Re: [HACKERS] Lessons from commit fest

[Andrew Dunstan]

Alvaro Herrera wrote:

Bruce Momjian wrote:

  

Based on that reaction I am not going to bother uploading my copy of the
typedefs.



Please reconsider.  Not having pgindent work at all is not better than
it working "only" 98%.

  


I have been thinking of pursuing your suggestion of having it as a 
buildfarm option. We could provide a SOAP interface to collect the 
typedefs and then consolidate them and put them in CVS. We could even do 
it per release. That would include Windows, although only MinGW, not 
MSVC, which doesn't have objdump.


I could probably get most of that done in a day or so.

Thoughts?

cheers

andrew

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

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Based on that reaction I am not going to bother uploading my copy of the
> > typedefs.
> 
> Please reconsider.  Not having pgindent work at all is not better than
> it working "only" 98%.

That's what I thought, but Tom thinks my list is unacceptable.  What do
others think?

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Plan targetlists in EXPLAIN output

[Gregory Stark]

"Tom Lane" <[EMAIL PROTECTED]> writes:

> For debugging the planner work I'm about to do, I'm expecting it will be
> useful to be able to get EXPLAIN to print the targetlist of each plan
> node, not just the quals (conditions) as it's historically done.  My
> first instinct is just to stick in the code under a debugging #ifdef,
> but I wonder if anyone wants to argue for making it more easily
> available?

Yes please.

> I think it'd be a mistake to turn it on by default, because it'd add a
> line for every plan node, which'd be an awful lot of bloat in output
> that's hard enough to read already.  And experience has shown that
> 99.99% of the time people don't need the info.  Still, there's that
> other 0.01%.
>
> I'm tempted to propose redefining the currently-nearly-useless
> EXPLAIN VERBOSE option as doing this.

EXPLAIN VERBOSE is indeed ridiculous. The only downside is that people
following modern instructions on old installs will be sad. But I'm fine with
that.

IMHO You could also move "width" to VERBOSE while you're at it. In fact you'll
probably want width in precisely the same cases where you want the target
list.

I think down the road we'll have a few different independent data sets you can
get out of explain or at least explain analyze. I want to get i/o stats in
there which I think you'll want to turn on and off as a group, for example.
But perhaps by the time we do that someone will have done XML explain and
it'll be irrelevant. I can't think of any nice syntax to do that offhand
anyways. So +1 for just redefining VERBOSE.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Bruce Momjian wrote:

> Based on that reaction I am not going to bother uploading my copy of the
> typedefs.

Please reconsider.  Not having pgindent work at all is not better than
it working "only" 98%.

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

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

Re: [HACKERS] Cross-field statistics

[Gregory Stark]

"Decibel!" <[EMAIL PROTECTED]> writes:

> For each field that isn't already in a set of field groupings
>  * Sort sample rows on that field
>  * Calculate correlation for all other fields
>  * If there are other fields that have a correlation to this sort  order over
> some threshold, save them along with the field we  originally sorted on as a
> new 'field grouping'
>  * Else, there are no other fields that group with this field; it's  a "loner"

I think this is going somewhere. But "correlation" isn't quite right. It has
the same problem our use of correlation for clusteredness has. Consider the
case of Zip code and City. They're nearly very non-independent variables but
there's basically no correlation.

If we found the right metric for clusteredness we could probably use it here
though too though.

> For each field grouping, at a minimum we'd need to store a histogram  for that
> grouping. 

This is a problem. What does a histogram on a grouping mean? It's not clear
how to come up with a histogram which can help answer questions like 
  A between ? and ? and B between ? and ?

You can do a histogram on  or  but neither are going to be
especially useful. Heikki and I came up with a weird hybrid thing which might
be useful for avoiding overestimating selectivity like 
  WHERE city='BOS' AND areacode = '617'

But it didn't help at all with the converse, ie:
 WHERE city='BOS' AND areacode = '212'

It's hard to see how we could possibly catch cases like that though.

> The important thing is that this scheme adds less than O(n) (n being  the
> number of fields), and not O(n^2), both in terms of ANALYZE

It looks like a good method for *finding* column sets which will be
interesting to keep more stats on. That's definitely one of the challenges.

I'm still not sure what stats to actually gather on the resulting column sets.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > What are we going to do about the duality of Windows vs. non-Windows?
> > Perhaps we could collect typedefs generated on the buildfarm.
> 
> I think it's really not acceptable that pgindent misformats Windows-only
> code (or any other part of the code that Bruce doesn't care enough about
> to build on his system).
> 
> This whole business of extracting the typedef names from object files
> sucks to begin with, and it will never not suck.  If we have to have
> such a list, we need to find a more platform-independent procedure
> for getting it.

Based on that reaction I am not going to bother uploading my copy of the
typedefs.

"If it can't be perfect, don't bother doing it" seems to be the
approach, and because pgindent will never be perfect, I don't understand
why we bother even running it.  Not finding all typedefs is only part of
the imperfect-ness of pgindent.

I think we use pgindent and an imperfect typedef list because it has
proven to be "good enough" to be useful.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> Sorry if I missed it in the original thread, but what is the
> use case you have in mind?

A use case would be dumping a large table and wanting to
load it into the database, but wanting to stop the job if it
is still running an hour from now, when a maintenance window
is scheduled to start.

However, I think my objection is more philosophical, as we've
changed from having pg_dump make a SQL representation of part
or all of your database, into also having it force what it
thinks should be the right environment as well. Yes, timeout
can be a foot gun, but it's a foot gun that off by default,
and must be explicitly turned on. The fact that a setting that
kills long-running queries ends up killing long-running queries
via psql -f seems worth a documentation warning, not a change
in the textual representation of a database. I checked the
archives and have yet to find a single instance in -bugs of
anyone complaining about this. The closest I found was someone
having problems with psql and -c, but they were specifically
aware of the timeout and were trying (unseccessfully) to
disable it first. For the record, I have no problem with
disabling the timeout in both pg_dump and pg_restore.


- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200804171250
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkgHf/sACgkQvJuQZxSWSsiXOwCggD1P/SgPwOO3gJdlXKP5bU3l
dWgAnRK5FNixLy8ajgkfI3Y/UpDyoeZB
=qaA5
-END PGP SIGNATURE-



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

Re: [HACKERS] get rid of psql welcome message

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> We could just do:
>
> psql 8.1.10 - postgresql server version 8.1.10
>
> Type: \h for SQL help, \? for psql help, \q to quit

Best idea yet. I also still like the .psqlrc no-splash
option, no reason we can't do both.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200804171238
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkgHfOwACgkQvJuQZxSWSsiqVgCgmICB56XnU0+fuPiAblPmYJmU
CcsAoL4T+3hh3wA04nzrrt3R2ioQeJ69
=YTw/
-END PGP SIGNATURE-



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

[HACKERS] Plan targetlists in EXPLAIN output

[Tom Lane]

For debugging the planner work I'm about to do, I'm expecting it will be
useful to be able to get EXPLAIN to print the targetlist of each plan
node, not just the quals (conditions) as it's historically done.  My
first instinct is just to stick in the code under a debugging #ifdef,
but I wonder if anyone wants to argue for making it more easily
available?

I think it'd be a mistake to turn it on by default, because it'd add a
line for every plan node, which'd be an awful lot of bloat in output
that's hard enough to read already.  And experience has shown that
99.99% of the time people don't need the info.  Still, there's that
other 0.01%.

I'm tempted to propose redefining the currently-nearly-useless
EXPLAIN VERBOSE option as doing this.

regards, tom lane

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

[HACKERS] Multiline privileges in \z

[Brendan Jurd]

Hi hackers,

It occurred to me that psql's \z command could benefit from the
addition of some newlines.  With any more than one grantee per object,
the output of \z rapidly becomes extremely wide, and very hard to
read.

I'd like to split the output onto one line per grantee.  So, instead of this:

 Schema | Name | Type  |Access privileges
+--+---+-
 public | a| table |
{brendanjurd=arwdxt/brendanjurd,foo=arwd/brendanjurd,bar=r/brendanjurd}
 public | b| table | {brendanjurd=arwdxt/brendanjurd,foo=arwd/brendanjurd}
(2 rows)

You would get this:

 Schema | Name | Type  |   Access privileges
+--+---+
 public | a| table | brendanjurd=arwdxt/brendanjurd
   : foo=arwd/brendanjurd
   : bar=r/brendanjurd
 public | b| table | brendanjurd=arwdxt/brendanjurd
   : foo=arwd/brendanjurd
(2 rows)

Because the -BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

ACLs
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIB3kL5YBsbHkuyV0RAgWQAJ9bcl3bOFozvi9LxRAQN1OwT3t+QgCcCGVq
dcMw3wIBQVPv1nYDBCSRpDA=
=s1eD
-END PGP SIGNATURE-
 are stored as an array, the patch to achieve this is trivial (see attached).

Looking forward to your comments.

Added to wiki.

Cheers,
BJ
*** src/bin/psql/describe.c
--- src/bin/psql/describe.c
***
*** 482,488  permissionsList(const char *pattern)
  "SELECT n.nspname as \"%s\",\n"
  "  c.relname as \"%s\",\n"
  "  CASE c.relkind WHEN 'r' THEN '%s' 
WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
! "  c.relacl as \"%s\"\n"
  "FROM pg_catalog.pg_class c\n"
   " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = 
c.relnamespace\n"
  "WHERE c.relkind IN ('r', 'v', 
'S')\n",
--- 482,488 
  "SELECT n.nspname as \"%s\",\n"
  "  c.relname as \"%s\",\n"
  "  CASE c.relkind WHEN 'r' THEN '%s' 
WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
! "  array_to_string(c.relacl, chr(10)) 
as \"%s\"\n"
  "FROM pg_catalog.pg_class c\n"
   " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = 
c.relnamespace\n"
  "WHERE c.relkind IN ('r', 'v', 
'S')\n",
*** src/test/regress/expected/dependency.out
--- src/test/regress/expected/dependency.out
***
*** 68,86  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"deptest_pkey" fo
  GRANT ALL ON deptest1 TO regression_user2;
  RESET SESSION AUTHORIZATION;
  \z deptest1
!   Access privileges 
for database "regression"
!  Schema |   Name   | Type  |  
   Access privileges
  
! 
+--+---+
!  public | deptest1 | table | 
{regression_user0=arwdxt/regression_user0,regression_user1=a*r*w*d*x*t*/regression_user0,regression_user2=arwdxt/regression_user1}
  (1 row)
  
  DROP OWNED BY regression_user1;
  -- all grants revoked
  \z deptest1
!   Access privileges for database "regression"
!  Schema |   Name   | Type  | Access privileges  
! +--+---+
!  public | deptest1 | table | {regression_user0=arwdxt/regression_user0}
  (1 row)
  
  -- table was dropped
--- 68,88 
  GRANT ALL ON deptest1 TO regression_user2;
  RESET SESSION AUTHORIZATION;
  \z deptest1
! Access privileges for database "regression"
!  Schema |   Name   | Type  |   Access privileges
! +--+---+
!  public | deptest1 | table | regression_user0=arwdxt/regression_user0
!: regression_user1=a*r*w*d*x*t*/regression_user0
!: regression_user2=arwdxt/regression_user1
  (1 row)
  
  DROP OWNED BY regression_user1;
  -- all grants revoked
  \z deptest1
!  Access privileges for database "regression"
!  Schema |   Name   | Type  |Access privileges 
! +--+---+--
!  public | deptest1 | table | regression_user0=arwdxt/regressio

Re: [HACKERS] new field content lost

[Tom Lane]

Gaetano Mendola <[EMAIL PROTECTED]> writes:
> since long time I have implemented a materialized view, today I had to add a
> new field and I faced the following (I believe) bug.
> The bug can be replicated on a 8.2.7

Cached plan for the function's UPDATE.  Should work okay in 8.3 ...

regards, tom lane

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

[HACKERS] new field content lost

[Gaetano Mendola]

Hi all,
since long time I have implemented a materialized view, today I had to add a
new field and I faced the following (I believe) bug.

The bug can be replicated on a 8.2.7


-- SETUP
create table test (a integer, b integer);
create table test_trigger (a integer);

CREATE OR REPLACE FUNCTION trigger_test()
RETURNS TRIGGER AS'
DECLARE

BEGIN
   update test set b = b*10 where a = NEW.a;

   RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER trigger_test
AFTER INSERT OR UPDATE ON test_trigger DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trigger_test();

insert into test values (1,1),(2,2),(3,3);
insert into test_trigger values (1),(2),(3);


-- FROM CONNECTION A
update test_trigger set a=1 where a=1;

-- FROM CONNECTION B
alter table test add column c integer;
update test set c = 15;
select * from test;

-- FROM CONNECTION A
update test_trigger set a=2 where a=2;

--FROM CONNECTION B
select * from test;

you can see that the value c=15 for a=2 has been nullified



Regards
Gaetano


























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

Re: [HACKERS] get rid of psql welcome message

[Joshua D. Drake]

On Thu, 17 Apr 2008 11:11:58 -0400
Andrew Dunstan <[EMAIL PROTECTED]> wrote:

> 
> 
> Brendan Jurd wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On Fri, Apr 18, 2008 at 12:36 AM, Tom Lane  wrote:
> >   
> >> Peter Eisentraut  writes:
> >>  > Around  it
> >>  > was proposed to truncate the psql welcome screen.  What do you
> >>  > think about that?
> >>
> >>  Personally. I'm very seriously against losing the version number
> >> banner. I could do without any of the rest of it.

Currently our prompt is fairly verbose:

Welcome to psql 8.1.10, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit


We could just do:

psql 8.1.10 - postgresql server version 8.1.10

Type: \h for SQL help, \? for psql help, \q to quit

postgres=#



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit



signature.asc
Description: PGP signature

Re: [HACKERS] count(*) performance improvement ideas

[Tom Lane]

Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I thought about this in the shower just now, and ISTM that if we want to
>> turn this into an actual feature rather than a kluge, there needs to be
>> some sort of "define variable" command that sets up a custom variable
>> and specifies its type (and whatever other properties seem worth
>> setting).  IOW expose the DefineCustomFooVariable functions to SQL users.
>> 
>> I'd be a bit inclined to restrict the namespace that can be set up that
>> way, eg allow only "local." or "session." as the prefix.  Maybe
>> that's just being too anal, but we could guarantee not to introduce
>> colliding built-in GUCs in future releases, whereas people trying to
>> define variables with any random name would definitely be at risk.

> Would it make sense to have built-in GUCs belong to "pg_catalog." and 
> user defined GUCs default to "public."?

[ after a bit of reflection... ]  I don't think that we want to tie
GUC names to schemas, especially not schemas that might not be there
(remember public is droppable).  The existing scheme for qualified
GUC names considers that the prefix is the name of a loadable module,
which isn't typically tied to any particular schema.

I kinda like "session" as the prefix since it helps remind people that
these things will have session lifespan.

OTOH, there's a possibility for confusion with the SET SESSION syntax
("hm, did you mean SET SESSION foo = ... or SET session.foo = ...?").
"local" has got the same issue.  Maybe "temp"?

regards, tom lane

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

Re: [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Heikki Linnakangas wrote:
>> Sorry if I missed it in the original thread, but what is the use case
>> you have in mind?

> I think the bottom line is just that having statement_timeout a global 
> setting 
> is stupid for a variety of reasons (dump, restore, vacuum, locks, incidental 
> delays) that we should discourage it (or prevent it, as proposed elsewhere) 
> rather than working around it in countless individual places.

I'm not convinced that there's no use-case for global statement_timeout,
and even less convinced that there won't be anyone setting one anyway.
Unless we are prepared to somehow *prevent* such a setting from being
put in place, the proposed patch seems reasonable to me.

Unless you have a use-case in which it's actually desirable for the dump
or restore to fail.  I'm having a tough time imagining one though.

regards, tom lane

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places where SIGTERM exit couldleave shared memory

[Tom Lane]

Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> Is this so? This happened to me the other day (hence the question about
> having COPY note failure earlier) because the disk filled up. I was
> confused because du showed nothing. Eventually I did an lsof and found
> the postgres backend had a large number of open file handles to deleted
> files (each one gigabyte).

The backend, or the bgwriter?  Please be specific.

The bgwriter should drop open file references after the next checkpoint,
but I don't recall any forcing function for regular backends to close
open files.

8.3 and HEAD should ftruncate() the first segment of a relation but I
think they just unlink the rest.  Is it sane to think of ftruncate then
unlink on the non-first segments, to alleviate the disk-space issue when
someone else is holding the file open?

regards, tom lane

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places where SIGTERM exit couldleave shared memory

[Martijn van Oosterhout]

On Thu, Apr 17, 2008 at 04:03:18PM +0300, Heikki Linnakangas wrote:
> They do clean up on abort or SIGTERM. If you experience a sudden power 
> loss, or kill -9 while CLUSTER or REINDEX is running, they will leave 
> behind dangling files, but that's a different problem. It's not limited 
> to utility commands like that either: if you create a table and copy a 
> few gigabytes of data into it in a transaction, and crash before 
> committing, you're left with a dangling file as well.

Is this so? This happened to me the other day (hence the question about
having COPY note failure earlier) because the disk filled up. I was
confused because du showed nothing. Eventually I did an lsof and found
the postgres backend had a large number of open file handles to deleted
files (each one gigabyte).

So something certainly deletes them (though maybe not on windows?)
before the transaction ends.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [HACKERS] get rid of psql welcome message

[Andrew Dunstan]

Brendan Jurd wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, Apr 18, 2008 at 12:36 AM, Tom Lane  wrote:
  

Peter Eisentraut  writes:
 > Around  it
 > was proposed to truncate the psql welcome screen.  What do you think about
 > that?

 Personally. I'm very seriously against losing the version number banner.
 I could do without any of the rest of it.




+1 for keeping the version number up there.


  


A prompt escape for the version would actually be nice.

cheers

andrew

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

Re: [HACKERS] get rid of psql welcome message

[Brendan Jurd]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, Apr 18, 2008 at 12:36 AM, Tom Lane  wrote:
> Peter Eisentraut  writes:
>  > Around  it
>  > was proposed to truncate the psql welcome screen.  What do you think about
>  > that?
>
>  Personally. I'm very seriously against losing the version number banner.
>  I could do without any of the rest of it.
>

+1 for keeping the version number up there.

Cheers,
BJ
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIB2Mx5YBsbHkuyV0RAjhZAJ9wCS4EjQOGb5sJPJLC0yd/CtSWRgCeJ/pi
cQ1qMGtQjsDo7IOiKvPfUNU=
=W8uL
-END PGP SIGNATURE-

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

Re: [HACKERS] Lessons from commit fest

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> What are we going to do about the duality of Windows vs. non-Windows?
> Perhaps we could collect typedefs generated on the buildfarm.

I think it's really not acceptable that pgindent misformats Windows-only
code (or any other part of the code that Bruce doesn't care enough about
to build on his system).

This whole business of extracting the typedef names from object files
sucks to begin with, and it will never not suck.  If we have to have
such a list, we need to find a more platform-independent procedure
for getting it.

regards, tom lane

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

[HACKERS] Cross-field statistics

[Decibel!]

I just had an idea about how to create cross-field statistics, which  
could greatly improve the quality of estimates involving multiple  
conditions on one table. This is rather arm-wavy, but I wanted to at  
least get the idea out...


If we built a table via

CREATE TABLE moo AS SELECT i, i*2 AS j FROM generate_series(1,) i;

Then it would be nice if the planner produced the same estimate for  
all of these:


SELECT * FROM moo WHERE i> AND j>*2;
SELECT * FROM moo WHERE i> OR j>*2;
SELECT * FROM moo WHERE i>;
SELECT * FROM moo WHERE j>*2;

It only actually gets the last 2 correct (1117 rows, close enough to  
the actual  rows). On my laptop, it guesses 125 for the AND case  
and 2109 for the OR case. The problem is that it doesn't know how  
closely i and j are related. But in this (contrived) example, it  
actually *could* make an inference between these two columns, because  
each field has a correlation of 1. That means that you can actually  
compute how much those two conditions will overlap by comparing how  
much they overlap in the histogram that's stored in pg_stats. As a  
first pass, it might be worth having the planner actually take this  
simple case into account.


For all the other fields, what if ANALYZE constructed artificial  
correlation orderings? We don't actually care about how well these  
artificial correlations correspond to physical table ordering, we  
only care about how many fields line up with a particular artificial  
ordering. What I'm proposing is that once we have our set of sample  
records in ANALYZE:


For each field that isn't already in a set of field groupings
 * Sort sample rows on that field
 * Calculate correlation for all other fields
 * If there are other fields that have a correlation to this sort  
order over some threshold, save them along with the field we  
originally sorted on as a new 'field grouping'
 * Else, there are no other fields that group with this field; it's  
a "loner"


For each field grouping, at a minimum we'd need to store a histogram  
for that grouping. It might be worth looking at how things change  
when we sort on different fields in the grouping... the lower the  
correlation threshold used to identify groupings, the more  
variability there will be. I think we'd also want to consider how  
well each field in the grouping correlated to that grouping. It might  
also be worth iteratively dropping the correlation threshold and  
searching again for groupings. At some point we lose the ability to  
draw meaningful conclusion from the information, but I'd expect  
there's some way we can calculate epsilon for different groupings and  
take that into account with query plans.


The important thing is that this scheme adds less than O(n) (n being  
the number of fields), and not O(n^2), both in terms of ANALYZE (ok,  
maybe not entirely true since presumably we don't do any sorting  
there right now) and in terms of storing statistics. I'm not sure  
what it would do to the planner; the entire key there would be  
identifying field groupings that covered sets of fields in the WHERE  
clause.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] get rid of psql welcome message

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Around  it 
> was proposed to truncate the psql welcome screen.  What do you think about 
> that?

Personally. I'm very seriously against losing the version number banner.
I could do without any of the rest of it.

regards, tom lane

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

Re: [HACKERS] runtime error on SPGIST, needed help

[Simone Campora]

Hello and really thanks for the answer, I found the solution for the problem
also thanks to the gdb debugger. My problem was a SegmentationFault runtime
error due to a little tricky:

the function SPGIST_FUNCTION(pquad_equals_op) is defined as follows

#define SPGIST_FUNCTION(name) extern "C" Datum name(PG_FUNCTION_ARGS)


extern "C" {
PG_FUNCTION_INFO_V1(pquad_consistent);
PG_FUNCTION_INFO_V1(pquad_nn_consistent);
PG_FUNCTION_INFO_V1(pquad_penalty);
PG_FUNCTION_INFO_V1(pquad_checkinternalsplit);
PG_FUNCTION_INFO_V1(pquad_picksplit);
PG_FUNCTION_INFO_V1(pquad_childbp);
PG_FUNCTION_INFO_V1(pquad_keylen);
PG_FUNCTION_INFO_V1(pquad_check);
PG_FUNCTION_INFO_V1(pquad_printpred);
PG_FUNCTION_INFO_V1(pquad_getpred);
PG_FUNCTION_INFO_V1(pquad_getparam);
PG_FUNCTION_INFO_V1(pquad_inside_op);
PG_FUNCTION_INFO_V1(pquad_check_chaining);
}

but as you can see, this "C" lacks the row

PG_FUNCTION_INFO_V1(pquad_equals_op);

that's why it was not working... I didn't noticed it because as far as I
don't access any pointer's argument, I don't get any error back! (I really
don't know why, is it maybe because of some default setting?)

Anyway thanks again to all!

Simone



On 12/04/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Simone Campora" <[EMAIL PROTECTED]> writes:
> > I first want to implement it and to make it works, like this:
>
> > SPGIST_FUNCTION(pquad_equals_op)
> > {
> > elog (NOTICE, "1");
>
> > Point *p1 = (Point *)PG_GETARG_POINTER(0);
> > Point *p2 = (Point *)PG_GETARG_POINTER(1);
>
> > elog (NOTICE, "2");
>
>
> (1) are you sure this function is marked as being V1 calling convention?
> Maybe "SPGIST_FUNCTION" takes care of that for you but I don't know.
>
> (2) since you neither marked it STRICT nor defended against nulls in the
> function body, I'd fully expect a crash on a null input ... does that
> table contain any null points?
>
>
> > Could anyone suggest me a good runtime debugger for that purpose?
>
>
> Any C debugger should work fine.  gdb and ddd are the most common
> tools around this project, I think.
>
> regards, tom lane
>



-- 
Campora Simone,

School of computer science
Swiss Federal Institute of Technology, Lausanne
EPFL

E-MAIL: [EMAIL PROTECTED]
 [EMAIL PROTECTED]

WWW: www.simonecampora.com

MOBILE:  +41 762 563466
   +39 347 8036605

SKYPE: sim0ne.

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Bruce Momjian wrote:

> The source code is the same for both Unix and Windows but you are right
> some typedefs are only visible on windows.  I think most are from
> EXEC_BACKEND so compiling with/without that should help but then you
> have to merge the typedef lists, of course.

The source code is the same, of course, but typedef generation uses
object files, not source code.

> I count 2481 typedefs found on my build.  I don't think we have to find
> every single typedef in the system for pgindent to be useful, but if we
> want people to be able to use this we should choose a single typedef
> file and all use that.   I am willing to create a standard one for
> everyone and upload it daily to the community ftp server.  It will not
> be perfect but I can improve it as people make suggestions.

Please do.

What are we going to do about the duality of Windows vs. non-Windows?
Perhaps we could collect typedefs generated on the buildfarm.

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

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

Re: [HACKERS] get rid of psql welcome message

[Simon Riggs]

On Thu, 2008-04-17 at 09:30 -0400, Stephen Frost wrote:
> * Peter Eisentraut ([EMAIL PROTECTED]) wrote:
> > Around  
> > it 
> > was proposed to truncate the psql welcome screen.  What do you think about 
> > that?
> 
> I'd recommend an option in .psqlrc to disable it, if possible.  That
> would be in line with what alot of other "splash-screen" type things do.

+1

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

Re: [HACKERS] MERGE SQL Statement

[Simon Riggs]

On Thu, 2008-04-17 at 07:31 +0100, Simon Riggs wrote:

> > > * MERGE will perform a left outer join between source on left and target
> > > on right. There must be no more than 1 row from table-ref for each row
> > > in the table. Each row in the table can only be updated once during each
> > > MERGE statement. Each non-matching row in the table-ref will result in
> > > one INSERT into table.
> > >
> > > * WHEN clauses are tested in the order specified. If the AND condition
> > > returns false then we skip onto the next WHEN clause. We stop once a
> > > WHEN clause activates, so only *one* action is ever activated for each
> > > row.
> > >
> > > * AND clauses need not form a complete set, i.e. it is possible that no
> > > action will result. It is also possible that some WHEN clauses will
> > > never activate because of the execution order; we would not try to
> > > prevent this, just document it as a possible user error.
> > >
> > 
> > Just curious if any of these behaviors come from the spec?  or maybe from 
> > other databases?  they don't seem unreasonable in general though.  
> 
> First two come from spec following clarifications from other
> implementations. The last point about the AND clauses not necessarily
> covering 100% of cases follows from the other points.

No, it looks like I missed one line in the standard. 

I've spent all day analysing this and writing up test cases, so this
next bit isn't really a reply to you Robert, just lots of additional
detail on this particular area.


In summary, the standard requires us to

1. If MATCHED to more than one source row we are supposed to throw an
error "cardinality violation". However, if there is no target row, yet
would have generated multiple rows had there been one, we do *not* throw
an error.

2. If the source row doesn't match any target row AND there is no WHEN
NOT MATCHING clause that applies, we are then supposed to implicitly
perform an INSERT with DEFAULT VALUES.

Neither of these seem particularly useful behaviours in *all* cases.

For 1, it implies we would need to sort and de-duplicate the output so
that we can throw an error in *all* cases. We would need to do this
because its fairly hard to determine that the set formed from the union
of all WHEN clauses does not cover the universal set. (i.e. its possible
to write a statement that doesn't have a WHEN clause that applies). 

If we try to update a row that just got updated we need to throw an
error, otherwise we may allow the Update Halloween problem. So we will
effectively detect this error in most cases anyway.

So for 1, I suggest we don't throw the explicit error as mentioned in
the standard and allow the secondary update error to provide 99% of
required errors. In the case where there was a matching row but no WHEN
clause tat applies, we simply ignore that matching row.

For 2, DB2 allows an additional statement ELSE IGNORE. I suggest we
support the standard for (2), yet also provide an additional option
WHEN [NOT] MATCHING THEN 
DO NOTHING

which would simply drop the matching or non-matching row and continue
with the next tuple.

My first attempt at a MERGE test case, with simulated output (no, I
haven't written it yet) is attached.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com
--
-- MERGE
--
CREATE TABLE target (id integer, balance integer);
CREATE TABLE source (id integer, balance integer);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
SELECT * FROM target;
 id | balance 
+-
  1 |  10
  2 |  20
  3 |  30
(3 rows)

--
-- initial tests
--
-- empty source means 0 rows touched
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
;
-- insert some source rows to work from
INSERT INTO source VALUES (2, 5);
INSERT INTO source VALUES (3, 20);
INSERT INTO source VALUES (4, 40);
SELECT * FROM source;
 id | balance 
+-
  2 |   5
  3 |  20
  4 |  40
(3 rows)

-- do a simple equivalent of an UPDATE join
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
;
SELECT * FROM target;
 id | balance 
+-
  1 |  10
  2 |  25
  3 |  50
(3 rows)

ROLLBACK;
-- do a simple equivalent of an INSERT SELECT
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
+-
  1 |  10
  2 |  20
  3 |  30
  4 |  40
(4 rows)

ROLLBACK;
-- now the classic UPSERT
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
+-
  1 |  10
  2 |  25
  3 |  50
  4 |  

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Magnus Hagander wrote:
> 
> > > IIRC, last time I tried it, the failure was because I couldn't get it
> > > to build the proper typedefs. Any chance you could just put a regularly
> > > updated typedefs file somewhere that I could wget down?
> > 
> > Have you tried the CVS version?  It should support typedefs on Linux?  I
> > can put a continually-updated version on my ftp site if people want it.
> 
> A typedef file generated from a single build is no good.  We need at
> least one for a regular Unix and another one from Windows.  The set of
> typedefs is different.
> 
> If everyone is expected to generate the typedef on their local builds,
> then the pgindent output will be different for every developer, thus
> generating lots of spurious changes.  This is not acceptable nor useful.

The source code is the same for both Unix and Windows but you are right
some typedefs are only visible on windows.  I think most are from
EXEC_BACKEND so compiling with/without that should help but then you
have to merge the typedef lists, of course.

I count 2481 typedefs found on my build.  I don't think we have to find
every single typedef in the system for pgindent to be useful, but if we
want people to be able to use this we should choose a single typedef
file and all use that.   I am willing to create a standard one for
everyone and upload it daily to the community ftp server.  It will not
be perfect but I can improve it as people make suggestions.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Magnus Hagander wrote:
> > > > Also I can put up a web page where you can upload or email your C
> > > > file and get a formatted version back.
> > > 
> > > IIRC, last time I tried it, the failure was because I couldn't get
> > > it to build the proper typedefs. Any chance you could just put a
> > > regularly updated typedefs file somewhere that I could wget down?
> > 
> > Have you tried the CVS version?  It should support typedefs on
> > Linux?  I can put a continually-updated version on my ftp site if
> > people want it.
> 
> Right, but it requires me to run configure with all modules enabled,
> right? And I don't have all modules installed on all machines, etc etc

I don't enable all modules actually because I don't have everything
installed either, but I do my best.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Bruce Momjian wrote:
> Magnus Hagander wrote:

> > IIRC, last time I tried it, the failure was because I couldn't get it
> > to build the proper typedefs. Any chance you could just put a regularly
> > updated typedefs file somewhere that I could wget down?
> 
> Have you tried the CVS version?  It should support typedefs on Linux?  I
> can put a continually-updated version on my ftp site if people want it.

A typedef file generated from a single build is no good.  We need at
least one for a regular Unix and another one from Windows.  The set of
typedefs is different.

If everyone is expected to generate the typedef on their local builds,
then the pgindent output will be different for every developer, thus
generating lots of spurious changes.  This is not acceptable nor useful.

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

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

Re: [HACKERS] get rid of psql welcome message

[Peter Eisentraut]

A. Kretschmer wrote:
> I'd vote No, because i see very often on #irc people asking something
> like 'how can i see the table definition' or other, and in this cases
> it's easy to say: hey dude, read the fine welcome message *g*

I take this as evidence that the welcome message has limited use in practice.  
The recently added "help" command will probably be (minimally) more suited to 
these kinds of people.

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

Re: [HACKERS] get rid of psql welcome message

[Peter Eisentraut]

Mike Aubury wrote:
> Am I missing something..
>
> $ psql -q testdb
> testdb=#

This also quiets out a few other unrelated things.

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

Re: [HACKERS] Lessons from commit fest

[Magnus Hagander]

Bruce Momjian wrote:
> Magnus Hagander wrote:
> > Bruce Momjian wrote:
> > > Bruce Momjian wrote:
> > > > Tom Lane wrote:
> > > > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > > > I am reviewing the psql wrap patch and just used pgindent
> > > > > > today to clean it up.  (pgindent did not add any extra
> > > > > > spacing changes.)  Patch reviewers should probably be able
> > > > > > to run pgindent.
> > > > > 
> > > > > Well, that means nobody in the world can review except you,
> > > > > because nobody else has ever reported success in duplicating
> > > > > your pgindent results.  I know I haven't been able to.
> > > > > 
> > > > > If you really believe the above then you need to try a bit
> > > > > harder to find a portable version of indent that we all can
> > > > > use.
> > > > 
> > > > The source code of pgindent I use is on our ftp site.
> > > > find_typedefs should now work on Linux as well as BSD.  Not
> > > > sure what else would be a problem.
> > > 
> > > Also I can put up a web page where you can upload or email your C
> > > file and get a formatted version back.
> > 
> > IIRC, last time I tried it, the failure was because I couldn't get
> > it to build the proper typedefs. Any chance you could just put a
> > regularly updated typedefs file somewhere that I could wget down?
> 
> Have you tried the CVS version?  It should support typedefs on
> Linux?  I can put a continually-updated version on my ftp site if
> people want it.

Right, but it requires me to run configure with all modules enabled,
right? And I don't have all modules installed on all machines, etc etc

(And I didn't get it working properly either way, but that could be
because I simply didn't try hard enough)

//Magnus

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

Re: [HACKERS] Lessons from commit fest

[Bruce Momjian]

Magnus Hagander wrote:
> Bruce Momjian wrote:
> > Bruce Momjian wrote:
> > > Tom Lane wrote:
> > > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > > I am reviewing the psql wrap patch and just used pgindent today
> > > > > to clean it up.  (pgindent did not add any extra spacing
> > > > > changes.)  Patch reviewers should probably be able to run
> > > > > pgindent.
> > > > 
> > > > Well, that means nobody in the world can review except you,
> > > > because nobody else has ever reported success in duplicating your
> > > > pgindent results.  I know I haven't been able to.
> > > > 
> > > > If you really believe the above then you need to try a bit harder
> > > > to find a portable version of indent that we all can use.
> > > 
> > > The source code of pgindent I use is on our ftp site.  find_typedefs
> > > should now work on Linux as well as BSD.  Not sure what else would
> > > be a problem.
> > 
> > Also I can put up a web page where you can upload or email your C file
> > and get a formatted version back.
> 
> IIRC, last time I tried it, the failure was because I couldn't get it
> to build the proper typedefs. Any chance you could just put a regularly
> updated typedefs file somewhere that I could wget down?

Have you tried the CVS version?  It should support typedefs on Linux?  I
can put a continually-updated version on my ftp site if people want it.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] get rid of psql welcome message

[A. Kretschmer]

am  Thu, dem 17.04.2008, um 14:39:43 +0200 mailte Peter Eisentraut folgendes:
> Around  it 
> was proposed to truncate the psql welcome screen.  What do you think about 
> that?
> 
> Personally, I'd get rid of it all, because it gets boring after about three 
> uses, so that we would be at
> 
> [EMAIL PROTECTED]:~$ psql testdb
> testdb=#

I'd vote No, because i see very often on #irc people asking something
like 'how can i see the table definition' or other, and in this cases
it's easy to say: hey dude, read the fine welcome message *g*


Okay, maybe a new switch on the command-line or a .psqlrc-parameter to
suppress this message...



Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [HACKERS] get rid of psql welcome message

[Mike Aubury]

Am I missing something..


$ psql -q testdb
testdb=#

And - if you're using bash - you could just 
 


$ alias "psql=psql -q"
$ psql testdb
testdb=#


On Thursday 17 April 2008 13:39:43 Peter Eisentraut wrote:
> Around 
> it was proposed to truncate the psql welcome screen.  What do you think
> about that?
>
> Personally, I'd get rid of it all, because it gets boring after about three
> uses, so that we would be at
>
> [EMAIL PROTECTED]:~$ psql testdb
> testdb=#
>
> The version mismatch warning would remain, of course.
>
> I'd also like to get rid of the SSL notice but I'm not sure what to replace
> it by.  Something in the prompt perhaps?
>
> Btw., any user could put the welcome message in his own psqlrc file via
> \echo commands in case they are really attached to it.



-- 
Mike Aubury

Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ



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

Re: [HACKERS] get rid of psql welcome message

[paul rivers]

Peter Eisentraut wrote:
Around  it 
was proposed to truncate the psql welcome screen.  What do you think about 
that?


Personally, I'd get rid of it all, because it gets boring after about three 
uses, so that we would be at


[EMAIL PROTECTED]:~$ psql testdb
testdb=#

The version mismatch warning would remain, of course.

I'd also like to get rid of the SSL notice but I'm not sure what to replace it 
by.  Something in the prompt perhaps?


Btw., any user could put the welcome message in his own psqlrc file via \echo 
commands in case they are really attached to it.


  


If you do this, adding psql internal variables so a prompt could be 
built would be a nice way to go. For a default, perhaps nothing special 
at all to flag the connection as ssl? If it matters to someone, they can 
always set their prompt default to include this, set pgsslmode 
appropriately, or call ensure sslinfo is installed and use it.


Is it worth promoting sslinfo into the core and adding a few new 
functions to expose at least the same information (cypher, etc)? While 
this isn't strictly related to what you're up to, sslinfo is a very nice 
complement for arbitrary programs to confirm they are talking ssl.




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

Re: [HACKERS] get rid of psql welcome message

[Stephen Frost]

* Peter Eisentraut ([EMAIL PROTECTED]) wrote:
> Around  it 
> was proposed to truncate the psql welcome screen.  What do you think about 
> that?

I'd recommend an option in .psqlrc to disable it, if possible.  That
would be in line with what alot of other "splash-screen" type things do.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Timely reporting of COPY errors

[Stephen Frost]

* Martijn van Oosterhout ([EMAIL PROTECTED]) wrote:
> On Wed, Apr 16, 2008 at 05:22:17PM -0400, Tom Lane wrote:
> > Even if we had some way of letting the application notice that the copy
> > had already failed, I don't see that psql could do very much with it,
> > at least not for COPY FROM STDIN.  It's got to read through the source
> > data anyway or it'll be out of sync with the script file.
> 
> psql could ignore the result of PQputData if it wanted, no big deal
> there.

erm, maybe I'm missing something here, but psql could certainly stop
reading the file it was given on a \copy line when an error on the
backend happens.  I agree that a user doing a copy-from-stdin wouldn't
be able to have it stop, though it'd go alot faster if it's just psql
throwing away data rather than it being sent across the network.  Also,
imv, we should consider adding a 'stdin' option to \copy to let psql
know that it's ok to error-out if it starts to get errors from the
backend.

Admittedly, I do use "zcat | psql -c "copy ... from stdin" quite a bit,
but I'd be extremely happy to change that, in pretty much any way
necessary, to make it so that psql just exit's when the backend starts
reporting errors.  Actually, ideally psql would just say "oh, this is a
-c command, not a script anyway, so I can error out if that command
starts to fail for whatever reason".

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] get rid of psql welcome message

[Simon Riggs]

On Thu, 2008-04-17 at 14:39 +0200, Peter Eisentraut wrote:

> Personally, I'd get rid of it all, because it gets boring after about three 
> uses, so that we would be at

Many people I speak to use Postgres every 6 months or so, so changes
like this make them think its broke when its not.

I'd vote No, because personally I find software that changes for no good
reason to be boring, potentially bug causing and requires many
screenshots and HOWTOs of the software to become outdated.

I do strongly support your efforts to improve usability though but let's
keep stuff that works the same.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

Re: [HACKERS] get rid of psql welcome message

[Magnus Hagander]

Peter Eisentraut wrote:
> Around
> 
> it was proposed to truncate the psql welcome screen.  What do you
> think about that?
> 
> Personally, I'd get rid of it all, because it gets boring after about
> three uses, so that we would be at

If we have readline installed, we could perhaps have it show the first
time you launch psql, but if there is a .psql_history file around,
don't show it?


> [EMAIL PROTECTED]:~$ psql testdb
> testdb=#
> 
> The version mismatch warning would remain, of course.
> 
> I'd also like to get rid of the SSL notice but I'm not sure what to
> replace it by.  Something in the prompt perhaps?

That would work. It just has to keep being possible to quickly see if
a connection is secured.

//Magnus

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

Re: [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Peter Eisentraut]

Heikki Linnakangas wrote:
> Sorry if I missed it in the original thread, but what is the use case
> you have in mind?

I think the bottom line is just that having statement_timeout a global setting 
is stupid for a variety of reasons (dump, restore, vacuum, locks, incidental 
delays) that we should discourage it (or prevent it, as proposed elsewhere) 
rather than working around it in countless individual places.

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places whereSIGTERM exit couldleave shared memory

[Heikki Linnakangas]

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Tom Lane wrote:


Also use this method
for createdb cleanup --- that wasn't a shared-memory-corruption 
problem,

but SIGTERM abort of createdb could leave orphaned files lying around.

I wonder if we could use this mechanism for cleaning up in case of
failed CLUSTER, REINDEX or the like.  I think these can leave dangling
files around.

They do clean up on abort or SIGTERM.


Ah, we're OK then.


Wait, my memory failed me! No, we don't clean up dangling files on 
SIGTERM. We should...


No, wait, we do after all. I was fooled by the new 8.3 behavior to leave 
the files dangling until next checkpoint. The files are not cleaned up 
immediately on SIGTERM, but they are at the next checkpoint.


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

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places whereSIGTERM exit couldleave shared memory

[Heikki Linnakangas]

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Tom Lane wrote:


Also use this method
for createdb cleanup --- that wasn't a shared-memory-corruption problem,
but SIGTERM abort of createdb could leave orphaned files lying around.

I wonder if we could use this mechanism for cleaning up in case of
failed CLUSTER, REINDEX or the like.  I think these can leave dangling
files around.

They do clean up on abort or SIGTERM.


Ah, we're OK then.


Wait, my memory failed me! No, we don't clean up dangling files on 
SIGTERM. We should...


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

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places where SIGTERM exit couldleave shared memory

[Alvaro Herrera]

Heikki Linnakangas wrote:
> Alvaro Herrera wrote:
>> Tom Lane wrote:
>>
>>> Also use this method
>>> for createdb cleanup --- that wasn't a shared-memory-corruption problem,
>>> but SIGTERM abort of createdb could leave orphaned files lying around.
>>
>> I wonder if we could use this mechanism for cleaning up in case of
>> failed CLUSTER, REINDEX or the like.  I think these can leave dangling
>> files around.
>
> They do clean up on abort or SIGTERM.

Ah, we're OK then.

> If you experience a sudden power  loss, or kill -9 while CLUSTER or
> REINDEX is running, they will leave  behind dangling files, but that's
> a different problem.

Sure, no surprises there.

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

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Repair two places where SIGTERM exit couldleave shared memory

[Heikki Linnakangas]

Alvaro Herrera wrote:

Tom Lane wrote:


Also use this method
for createdb cleanup --- that wasn't a shared-memory-corruption problem,
but SIGTERM abort of createdb could leave orphaned files lying around.


I wonder if we could use this mechanism for cleaning up in case of
failed CLUSTER, REINDEX or the like.  I think these can leave dangling
files around.


They do clean up on abort or SIGTERM. If you experience a sudden power 
loss, or kill -9 while CLUSTER or REINDEX is running, they will leave 
behind dangling files, but that's a different problem. It's not limited 
to utility commands like that either: if you create a table and copy a 
few gigabytes of data into it in a transaction, and crash before 
committing, you're left with a dangling file as well.


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

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

Re: [HACKERS] Lessons from commit fest

[Alvaro Herrera]

Peter Eisentraut wrote:
> Martijn van Oosterhout wrote:
> > I doubt it, indent doesn't know nearly enough C to be able to anything
> > other than adjust whitespace. It surely won't remove braces...
> 
> I faintly recall that it does or at least did at some point.

It used to remove braces around single-statement blocks, but that
feature was removed because it broke indentation of PG_TRY blocks.

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

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

Re: [HACKERS] Lessons from commit fest

[Peter Eisentraut]

Martijn van Oosterhout wrote:
> I doubt it, indent doesn't know nearly enough C to be able to anything
> other than adjust whitespace. It surely won't remove braces...

I faintly recall that it does or at least did at some point.

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

Re: [HACKERS] Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P

[Raphaël Jacquot]

On Mon, 2008-03-31 at 18:21 +, sanjay sharma wrote:
> overkill. Compliance requirement for storing private data arises from
> each organizations own declared privacy policies and statutory bodies
> like privacy commissioners and other privacy watchdogs. These
> standards are not as strict as PCI, HIPPA or Sarnabes-Oxley
>  
> Compliance with HIPPA regulation requires not only maintaining all
> records of who created and updated the record but also who accessed
> and viewed records, when and in what context.

you'd be much better served in this case by implementing se-postgresql
with an appropriate policy...
it would allow you to do all that you need and more :-)



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

[HACKERS] get rid of psql welcome message

[Peter Eisentraut]

Around  it 
was proposed to truncate the psql welcome screen.  What do you think about 
that?

Personally, I'd get rid of it all, because it gets boring after about three 
uses, so that we would be at

[EMAIL PROTECTED]:~$ psql testdb
testdb=#

The version mismatch warning would remain, of course.

I'd also like to get rid of the SSL notice but I'm not sure what to replace it 
by.  Something in the prompt perhaps?

Btw., any user could put the welcome message in his own psqlrc file via \echo 
commands in case they are really attached to it.

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

[HACKERS] Re: [COMMITTERS] pgsql: Repair two places where SIGTERM exit could leave shared memory

[Alvaro Herrera]

Tom Lane wrote:

> Also use this method
> for createdb cleanup --- that wasn't a shared-memory-corruption problem,
> but SIGTERM abort of createdb could leave orphaned files lying around.

I wonder if we could use this mechanism for cleaning up in case of
failed CLUSTER, REINDEX or the like.  I think these can leave dangling
files around.

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

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

Re: [HACKERS] Lessons from commit fest

[Magnus Hagander]

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > I am reviewing the psql wrap patch and just used pgindent today
> > > > to clean it up.  (pgindent did not add any extra spacing
> > > > changes.)  Patch reviewers should probably be able to run
> > > > pgindent.
> > > 
> > > Well, that means nobody in the world can review except you,
> > > because nobody else has ever reported success in duplicating your
> > > pgindent results.  I know I haven't been able to.
> > > 
> > > If you really believe the above then you need to try a bit harder
> > > to find a portable version of indent that we all can use.
> > 
> > The source code of pgindent I use is on our ftp site.  find_typedefs
> > should now work on Linux as well as BSD.  Not sure what else would
> > be a problem.
> 
> Also I can put up a web page where you can upload or email your C file
> and get a formatted version back.

IIRC, last time I tried it, the failure was because I couldn't get it
to build the proper typedefs. Any chance you could just put a regularly
updated typedefs file somewhere that I could wget down?

//Magnus

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

Re: [HACKERS] count(*) performance improvement ideas

[PFC]

On Thu, 17 Apr 2008 02:48:37 +0200, Stephen Denne  
<[EMAIL PROTECTED]> wrote:



PFC wrote:

Let's try this quick & dirty implementation of a local
count-delta cache
using a local in-memory hashtable (ie. {}).



CREATE OR REPLACE FUNCTION update_count( key TEXT, delta INTEGER )
   RETURNS INTEGER
AS $$
 if key in GD:
 GD[key] += delta
 else:
 GD[key] = delta
 return GD[key]
$$ LANGUAGE plpythonu;


Thanks for the code, this seems to be very much what I was looking for.

I don't know plpythonu (nor python), just read a few docs now:


Learn Python, it is a really useful language ;)

"The global dictionary SD is available to store data between function  
calls. This variable is private static data. The global dictionary GD is  
public data, available to all Python functions within a session. Use  
with care."


Does session == transaction or connection?
I don't understand the difference between SD and GD, private and public.  
Where are the context boundaries?


There is no sharing between processes, so
	- both SD and GD are limited to the current session (connection, postgres  
process), no shared memory is involved

- GD is global between all python functions (global)
- SD is specific to each python function (static)

	The big gotcha is that these are all non-transactional : if you rollback,  
GD and SD stay the same, and when you issue a query, you can assume the  
state of SD and GD is random (due to previous queries) unless you  
initialize them to a known value.


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

Re: [HACKERS] Lessons from commit fest

[Martijn van Oosterhout]

On Thu, Apr 17, 2008 at 09:11:12AM +0300, Heikki Linnakangas wrote:
> Something like this:
> 
> if (foo)
> {
>   do something;
>   do something else;
> }
> ...
> 
> ->
> 
> if (foo)
>   do something;
> do something else;
> ...

I doubt it, indent doesn't know nearly enough C to be able to anything
other than adjust whitespace. It surely won't remove braces...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature