[HACKERS] Online base backup from the hot-standby

2011-06-14 Thread Jun Ishiduka
( Quotation from
  http://archives.postgresql.org/pgsql-hackers/2011-05/msg01396.php )

  STEP1: Make startup process to acquire backup-end-position from 
 not only backup-end record but also backup-history-file .
   * startup process allows to acquire backup-end-position 
 from backup-history-file .


I have created a patch to the above-mentioned content. 

Please check it. 



Jun Ishizuka
NTT Software Corporation
TEL:045-317-7018
E-Mail: ishizuka@po.ntts.co.jp



standby_online_backup_01.patch
Description: Binary data

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


Re: [HACKERS] Online base backup from the hot-standby

2011-06-14 Thread Heikki Linnakangas
On 14.06.2011 09:03, Jun Ishiduka wrote:
 ( Quotation from
http://archives.postgresql.org/pgsql-hackers/2011-05/msg01396.php )
 
   STEP1: Make startup process to acquire backup-end-position from
  not only backup-end record but also backup-history-file .
* startup process allows to acquire backup-end-position
  from backup-history-file .
 
 
 I have created a patch to the above-mentioned content.

I still think that's headed in the wrong direction.
(http://archives.postgresql.org/pgsql-hackers/2011-05/msg01405.php)

-- 
  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] Online base backup from the hot-standby

2011-06-14 Thread Jun Ishiduka

 I still think that's headed in the wrong direction.
 (http://archives.postgresql.org/pgsql-hackers/2011-05/msg01405.php)

Please check these mails, and teach the reason for content of the wrong 
direction.
(http://archives.postgresql.org/pgsql-hackers/2011-06/msg00209.php)
(http://archives.postgresql.org/pgsql-hackers/2011-05/msg01566.php)



Jun Ishizuka
NTT Software Corporation
TEL:045-317-7018
E-Mail: ishizuka@po.ntts.co.jp




-- 
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] FOREIGN TABLE doc fix

2011-06-14 Thread Dave Page
On Mon, Jun 13, 2011 at 8:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dave Page dp...@pgadmin.org writes:
 I've already implemented some simple qual pushdown in the redis FDW,
 and am planning to do something similar for MySQL - however I won't be
 surprised if I have to rewrite redisGetQual in
 https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for
 example.

 OK, *now* we're on the same page.  This sort of experimentation is
 exactly what I'm talking about: we'll certainly want to rewrite the code
 once we have better infrastructure, but it's necessary to write some
 throwaway code while we're learning what infrastructure FDWs want.

 (I find it a bit weird btw that you seem to be doing that at execution
 time not plan time...)

That's largely because as we discussed a while back
(http://archives.postgresql.org/pgsql-hackers/2011-04/msg00084.php),
persisting data from plan time to scan time isn't exactly clean.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] 9.1 beta1 error

2011-06-14 Thread Thom Brown
On 14 June 2011 05:17, Franklin Haut franklin.h...@gmail.com wrote:
 hi people, postgresql goes down on a  WindowsXP SP3 machine.
 there a script to reproduce. (on XP with 8.4 works fine)



 CREATE DATABASE teste
   WITH OWNER = postgres
        ENCODING = 'UTF8'
        TABLESPACE = pg_default
        LC_COLLATE = 'Portuguese_Brazil.1252'
        LC_CTYPE = 'Portuguese_Brazil.1252'
        CONNECTION LIMIT = -1;

 CREATE TABLE public.s230chr ( codemp INTEGER, codfil INTEGER,
 codass INTEGER,  codseq INTEGER, PRIMARY KEY(codemp, codfil,
 codass, codseq) ) WITH OIDS
 ALTER TABLE public.s230chr ADD COLUMN ho1ser TIME(0) WITHOUT TIME
 ZONE;
 ALTER TABLE public.s230chr ADD COLUMN ho2ser TIME(0) WITHOUT TIME
 ZONE;
 ALTER TABLE public.s230chr ADD COLUMN ho3ser TIME(0) WITHOUT TIME
 ZONE;
 ALTER TABLE public.s230chr ADD COLUMN ho4ser TIME(0) WITHOUT TIME
 ZONE;
 ALTER TABLE public.s230chr ADD COLUMN cobhte BOOLEAN;
 INSERT into s230chr (codemp, codfil, codass, codseq, ho1ser, ho2ser) values
 (1,1,1,1,'08:00', '11:00');
 -- ERROR on next query
 select
   sum( extract ( EPOCH FROM coalesce( (ho2ser - ho1ser), '00:00:00') +
                             coalesce( (ho4ser - ho3ser), '00:00:00') ) /
 3600 ) as tthora
 from s230chr where codemp = 1 and codfil = 1 and codass = 1 and cobhte IS
 NULL

...and what's the error?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] ECPG parse.pl and parse2.pl

2011-06-14 Thread Michael Meskes
On Mon, Jun 13, 2011 at 10:59:38PM +0300, Peter Eisentraut wrote:
 I propose that we move parse2.pl to parse.pl and only keep the one.

I agree, thanks for taking care.

Originally I wanted to keep parse.pl around for a bit, so I could run both and
compare the output over the development cycle. But with 9.1 being so close,
there's no need for the old script anymore.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at googlemail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

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


[HACKERS] per-column generic option

2011-06-14 Thread Shigeru Hanada
Hi,

I would like to propose support for per-column generic option, which is
defined in the SQL/MED standard.  In 9.0 release, support for foreign
tables and per-table generic option have been added, but support for
per-column generic option hasn't.

Please examine the description below and attached patch
per_column_option_v1.patch.  Any comments or questions are welcome.

Possible use cases
~~
Purpose of per-column generic option is passing column-specific settings
to the foreign-data wrapper.

1) For file_fdw, per-column generic option can be used to represent
per-column COPY option FORCE_NOT_NULL with boolean value (currently
file_fdw doesn't support FORCE_NOT_NULL option).

2) For postgresql_fdw (even though it has not been implemented yet),
per-column generic option could be used to represent the name of the
column on the foreign side.  It is similar to per-table generic option
such as nspname and relname for namespace name/relation name,
proposed in the last development cycles. Such option would be named
attname after pg_attribute.attname.

Catalog design
~~
This proposal requires changing some catalogs.

1) To store per-column generic options, new attribute attfdwoptions
(text[]) was added at tail of pg_attribute.  This is similar to the
generic option of other FDW objects such as FDW, server, user mapping
and foreign table.  Existing attribute attoptions is not used for
generic options.

2) To conform the SQL/MED standard, an information_schema view
COLUMN_OPTIONS was added.  Also underlying view
_pg_foreign_table_columns was added to show only columns which current
user has any access privilege.  This fashion is same as other FDW views.

Syntax design
~
Per-column generic options can be operated via CREATE FOREIGN TABLE
statement and ALTER FOREIGN TABLE statement.  Similar to other generic
options, ADD/SET/DROP can be specified for ALTER FOREIGN TABLE.

1) In CREATE FOREIGN TABLE statement, per-column generic options can be
specified in a column definition without operation qualifier such as
SET, ADD and DROP; all options are treated as ADD.  Similar to other FDW
objects, multiple options can be specified for one column by separating
option-value pairs with comma.

-- multiple options can be specified for one column at once
CREATE FOREIGN TABLE foo (
   c1 int  OPTIONS (opt1 'value1'),
   c2 text OPTIONS (opt2 'values2', opt3 'value3'),
   c3 date OPTIONS (opt4 'value4) NOT NULL
) SERVER server;

To avoid syntax conflict between OPTIONS (...) and DEFAULT b_expr
(b_expr can end with a token OPTION), I placed OPTIONS (...) between
data type and any other column qualifier such as default values and
constraints.

The SQL/MED standard doesn't consider any column qualifier other than
data type, so it defines the syntax simply as below.  I think new syntax
conforms the standard...

CREATE FOREIGN TABLE foo (
  { column_name data_type [ OPTIONS ( option 'value' [, ...] ) ] }
[, ... ]
) SERVER server [ OPTIONS (...) ]

Please note that CREATE FOREIGN TABLE shares the columnDef, a syntax
element for a column definition, with CREATE TABLE.  I thought that they
should so, and I didn't introduce separated syntax for foreign tables.

2) Similar to other FDW objects' ALTER statement, ALTER FOREIGN TABLE
ALTER COLUMN accepts ADD/SET/DROP operation for each option.  DROP
requires only option name.

ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (SET opt1 'VALUE1');-- should be set in advance
ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (ADD opt2 'VALUE1', DROP opt1);

Similar to other ALTER FOREIGN TABLE commands, ALTER COLUMN ... OPTIONS
(...) can be contained in the list of ALTER commands.

ALTER FOREIGN TABLE foo
ALTER COLUMN col1 OPTIONS (opt1 'val1'),
ALTER COLUMN col2 SET NOT NULL;

psql support

1) psql should support describing per-column generic options, so \dec
command was added.  If the form \dec+ is used, generic options are also
displayed.  Output sample is:

postgres=# \dec csv_branches
  List of foreign table columns
 Schema |Table |  Column
+--+--
 public | csv_branches | bid
 public | csv_branches | bbalance
 public | csv_branches | filler
(3 rows)

postgres=# \dec+ csv_branches
   List of foreign table columns
 Schema |Table |  Column  |Options
+--+--+
 public | csv_branches | bid  | {force_not_null=false}
 public | csv_branches | bbalance | {force_not_null=true}
 public | csv_branches | filler   |
(3 rows)

Here I found an inconsistency about privilege to see generic options
(not only column but also FDW and server et al).  The
information_schema.*_options only shows options which are associated to
objects that current user can access, but \de*+ doesn't have such
restriction.  \de* commands should be fixed to hide forbidden 

Re: [HACKERS] pg_trgm: unicode string not working

2011-06-14 Thread Florian Pflug
On Jun14, 2011, at 07:15 , Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Mon, Jun 13, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote:
 No, it does not.  Under what circumstances should I issue a suggestion
 to reindex, and what should the text be?
 
 It sounds like GIN indexes need to be reindexed after upgrading from 
 9.1 to = 9.1.
 
 Only if you care whether they work for corner cases such as empty
 arrays ... corner cases which didn't work before 9.1, so very likely
 you don't care.

We also already say To fix this, do REINDEX INDEX ...  in the errhint
of old GIN indexes do not support whole-index scans nor searches for nulls.

best regards,
Florian Pflug


-- 
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] SSI patch renumbered existing 2PC resource managers??

2011-06-14 Thread Heikki Linnakangas

On 13.06.2011 22:33, Tom Lane wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com  writes:

On 13.06.2011 21:31, Tom Lane wrote:

So far as I can tell, that breaks pg_upgrade (if there are any open
prepared transactions) for no redeeming social benefit.



Surely pg_upgrade can't work anyway if there's any open prepared
transactions in the database. We're not going to guarantee to keep all
the data structures we write in two-phase state files unchanged over
major releases. If pg_upgrade is not checking for prepared transcations
at the moment, such a check should probably should be added.


No, pg_upgrade should not be unilaterally refusing that.  The correct
way to deal with this consideration is to change the TWOPHASE_MAGIC
number when we make a change in on-disk 2PC state.  Which wasn't done
in the SSI patch.  We can either change that now, or undo the
unnecessary change in existing RM IDs.  I vote for the latter.


Ok, I've renumbered the existing RMs back the way they were.

I nevertheless don't think it's worthwhile to try to migrate 2pc state 
files in pg_upgrade. More than likely, it's a mistake on part of the 
admin anyway if there is a prepared transaction open at that point.


--
  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] Bug in XPATH() if expression returns a scalar value

2011-06-14 Thread Florian Pflug
On Jun13, 2011, at 21:24 , Peter Eisentraut wrote:
 On ons, 2011-06-08 at 10:14 +0200, Florian Pflug wrote:
 But then you lose the ability to evaluate user-supplied
 XPath expressions, because there's no way of telling which of these
 function to use.
 
 Perhaps having both variants, one type-safe and one not, would work.  I
 don't agree with doing away with type-safety completely for the sake of
 convenience.

In theory, I agree.

In practice, however, XPath 1.0 isn't very strongly typed itself. The
built-in types are all auto-converted into one another (As if
string(), number(), boolean() had been called). Also, only three of
the functions defined by XPath 1.0 seem interesting. Here's the break-down

The functions returning string are
  string(): Converts arbitrary values to strings
  local-name(): Name of node-set's first top-level w/o namespace prefix
  namespace-uri(): Namespace of node-set's first top-level
  name(): Namespace prefix and name of node-set's first top-level node
  concat()
  starts-with()
  contains()
  substring-before()
  substring-after()
  substring()
  string-length()
  translate()
  
For all of these function postgres provides corresponding SQL functions,
which the exception of
  local-name()
  namespace-uri()
  name()

In fact, these three functions are the raison d'être for my patch and this 
thread.
I needed to find the name of a tag returned by an XPath expression, and to my
dismay discovered that XPATH('local-name(...)', ...) returns an empty array. The
only reason I added support for boolean and numeric XPath expressions at all was
for the sake of completeness.

Here's the rest of the functions defined by XPath 1.0. I'm convinces that none
of them are particularly useful as top-level functions, and therefore believe
that adding XPATH_BOOLEAN() and XPATH_NUMBER() is largely overkill.

The functions returning number are
  number(): Converts arbitrary values to numbers
  last()
  position()
  count()
  sum(): Sum over a node-set after implicit conversion of nodes to numbers
  floor()
  ceiling()
  round()
  operators +, -, *, div, mod

The functions returning boolean are
  boolean(): Converts arbitrary to boolean
  not()
  true()
  false()
  operators or, and, =, !=, =, , =, 

best regards,
Florian Pflug



-- 
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] Boolean operators without commutators vs. ALL/ANY

2011-06-14 Thread Florian Pflug
On Jun13, 2011, at 16:19 , Andrew Dunstan wrote:
 On 06/13/2011 10:07 AM, Robert Haas wrote:
 Some languages use =~ and some use just ~... I was just
 wondering if anyone thought the commutator of =~ was ~=...
 
 My feeling is it's a bit dangerous. It's too easy to fat-finger the reverse 
 op, and get something quite unintended.

Well, but with ~ you need to *remember* that the regexp
goes on the right side and the text on the left. That seems
worse than the risk of fat-fingering =~ and getting ~=.

At, at least, have looked up the argument order of ~
countless in the past...

best regards,
Florian Pflug


-- 
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] Boolean operators without commutators vs. ALL/ANY

2011-06-14 Thread Florian Pflug
On Jun13, 2011, at 05:44 , Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote:
 (B) There should be a way to use ANY()/ALL() with the
 array elements becoming the left arguments of the operator.
 
 It seems to me that if we provided some way of handling this, your
 first proposal would be moot; and I have to say I like the idea of
 allowing this a lot more than tinkering with the operator names.
 
 There are syntactic reasons not to do that.  It'd be a lot easier just
 to provide a commutator operator for ~.

My suggestion would be the add a commutator for ~ as a short-term
solution (preferably in 9.1).

Since ~ doesn't inspire any obvious names for a possible commutator,
I suggest adding =~ and ~=.

Is there any support for that proposal?

In the long term, I'd like to add support for (ANY() op expr)
(Note the enclosing parens). I've checked that this works grammar-wise,
but haven't no idea how much tweaking the executor needs to support
that...

best regards,
Florian Pflug


-- 
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] SSI work for 9.1

2011-06-14 Thread Heikki Linnakangas

On 10.06.2011 19:05, Kevin Grittner wrote:

I found that pgindent would like to tweak whitespace in three places
in that patch, and I found an unnecessary include that I would like
to remove.  Normally, I would post a new version of the patch with
those adjustments, but there's been a disquieting tendency for
people to not look at what's actually happening with revisions of
this patch and act like the sky is falling with each refinement.

Let me be clear: each posted version of this patch has been safe and
has been an improvement on what came before.  Dan and I didn't
disagree about what to do at any point; Dan figured out what to do
with two calls I left alone because I faded before I could work out
how to deal with them.  Essentially we collaborated on-list rather
than discussing things off-list and posting the end result.  Perhaps
that was a bad choice, but time was short and I had hopes that a
change people had requested could be included in beta2.


I did some further changes, refactoring SkipSerialization so that it's 
hopefully more readable, and added a comment about the side-effects. See 
attached. Let me know if I'm missing something.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 01a492e..b947c11 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -274,7 +274,8 @@ heapgetpage(HeapScanDesc scan, BlockNumber page)
 			else
 valid = HeapTupleSatisfiesVisibility(loctup, snapshot, buffer);
 
-			CheckForSerializableConflictOut(valid, scan-rs_rd, loctup, buffer);
+			CheckForSerializableConflictOut(valid, scan-rs_rd, loctup,
+			buffer, snapshot);
 
 			if (valid)
 scan-rs_vistuples[ntup++] = lineoff;
@@ -469,7 +470,8 @@ heapgettup(HeapScanDesc scan,
 	 snapshot,
 	 scan-rs_cbuf);
 
-CheckForSerializableConflictOut(valid, scan-rs_rd, tuple, scan-rs_cbuf);
+CheckForSerializableConflictOut(valid, scan-rs_rd, tuple,
+scan-rs_cbuf, snapshot);
 
 if (valid  key != NULL)
 	HeapKeyTest(tuple, RelationGetDescr(scan-rs_rd),
@@ -478,7 +480,7 @@ heapgettup(HeapScanDesc scan,
 if (valid)
 {
 	if (!scan-rs_relpredicatelocked)
-		PredicateLockTuple(scan-rs_rd, tuple);
+		PredicateLockTuple(scan-rs_rd, tuple, snapshot);
 	LockBuffer(scan-rs_cbuf, BUFFER_LOCK_UNLOCK);
 	return;
 }
@@ -747,7 +749,7 @@ heapgettup_pagemode(HeapScanDesc scan,
 if (valid)
 {
 	if (!scan-rs_relpredicatelocked)
-		PredicateLockTuple(scan-rs_rd, tuple);
+		PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
 	scan-rs_cindex = lineindex;
 	return;
 }
@@ -755,7 +757,7 @@ heapgettup_pagemode(HeapScanDesc scan,
 			else
 			{
 if (!scan-rs_relpredicatelocked)
-	PredicateLockTuple(scan-rs_rd, tuple);
+	PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
 scan-rs_cindex = lineindex;
 return;
 			}
@@ -1470,9 +1472,9 @@ heap_fetch(Relation relation,
 	valid = HeapTupleSatisfiesVisibility(tuple, snapshot, buffer);
 
 	if (valid)
-		PredicateLockTuple(relation, tuple);
+		PredicateLockTuple(relation, tuple, snapshot);
 
-	CheckForSerializableConflictOut(valid, relation, tuple, buffer);
+	CheckForSerializableConflictOut(valid, relation, tuple, buffer, snapshot);
 
 	LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
 
@@ -1588,11 +1590,12 @@ heap_hot_search_buffer(ItemPointer tid, Relation relation, Buffer buffer,
 
 		/* If it's visible per the snapshot, we must return it */
 		valid = HeapTupleSatisfiesVisibility(heapTuple, snapshot, buffer);
-		CheckForSerializableConflictOut(valid, relation, heapTuple, buffer);
+		CheckForSerializableConflictOut(valid, relation, heapTuple, buffer,
+		snapshot);
 		if (valid)
 		{
 			ItemPointerSetOffsetNumber(tid, offnum);
-			PredicateLockTuple(relation, heapTuple);
+			PredicateLockTuple(relation, heapTuple, snapshot);
 			if (all_dead)
 *all_dead = false;
 			return true;
@@ -1750,7 +1753,7 @@ heap_get_latest_tid(Relation relation,
 		 * result candidate.
 		 */
 		valid = HeapTupleSatisfiesVisibility(tp, snapshot, buffer);
-		CheckForSerializableConflictOut(valid, relation, tp, buffer);
+		CheckForSerializableConflictOut(valid, relation, tp, buffer, snapshot);
 		if (valid)
 			*tid = ctid;
 
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 0208765..31d705c 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -126,7 +126,7 @@ do { \
 } while(0)
 
 static IndexScanDesc index_beginscan_internal(Relation indexRelation,
-		 int nkeys, int norderbys);
+		 int nkeys, int norderbys, Snapshot snapshot);
 
 
 /* 
@@ -234,7 +234,7 @@ index_beginscan(Relation heapRelation,
 {
 	IndexScanDesc scan;
 
-	scan = index_beginscan_internal(indexRelation, nkeys, 

Re: [HACKERS] lazy vxid locks, v1

2011-06-14 Thread Robert Haas
On Mon, Jun 13, 2011 at 8:10 PM, Jeff Janes jeff.ja...@gmail.com wrote:
 On Sun, Jun 12, 2011 at 2:39 PM, Robert Haas robertmh...@gmail.com wrote:
 ...

 Profiling reveals that the system spends enormous amounts of CPU time
 in s_lock.  LWLOCK_STATS reveals that the only lwlock with significant
 amounts of blocking is the BufFreelistLock;

 This is curious.  Clearly the entire working set fits in RAM, or you
 wouldn't be getting number like this.  But does the entire working set
 fit in shared_buffers?  If so, you shouldn't see any traffic on
 BufFreelistLock once all the data is read in.  I've only seen
 contention here when all data fits in OS cache memory but not in
 shared_buffers.

Yeah, that does seem odd:

rhaas=# select pg_size_pretty(pg_database_size(current_database()));
 pg_size_pretty

 1501 MB
(1 row)

rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts'));
 pg_size_pretty

 1281 MB
(1 row)

rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts_pkey'));
 pg_size_pretty

 214 MB
(1 row)

rhaas=# show shared_buffers;
 shared_buffers

 8GB
(1 row)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] 9.1 beta1 error

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 1:17 AM, Franklin Haut franklin.h...@gmail.com wrote:
 hi people, postgresql goes down on a  WindowsXP SP3 machine.
 there a script to reproduce. (on XP with 8.4 works fine)

There is a crash related to time zone handling that was fixed in 9.1beta2.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Make relation_openrv atomic wrt DDL

2011-06-14 Thread Robert Haas
On Mon, Jun 13, 2011 at 4:04 PM, Noah Misch n...@leadboat.com wrote:
 On Mon, Jun 13, 2011 at 08:21:05AM -0400, Robert Haas wrote:
 On Mon, Jun 13, 2011 at 1:12 AM, Noah Misch n...@leadboat.com wrote:
  This probably would not replace a backend-local counter of processed 
  messages
  for RangeVarLockRelid()'s purposes. ?It's quite possibly a good way to 
  reduce
  SInvalReadLock traffic, though.

 I was imagining one shared global counter, not one per backend, and
 thinking that each backend could do something like:

 volatile uint32 *the_global_counter = global_counter;
 uint32 latest_counter;
 mfence();
 latest_counter = *the_global_counter;
 if (latest_counter != previous_value_of_global_counter || 
 myprocstate-isReset)
    really_do_it();
 previous_value_of_global_counter = latest_counter;

 I'm not immediately seeing why that wouldn't work for your purposes as well.

 That takes us back to the problem of answering the (somewhat rephrased) 
 question
 Did any call to AcceptInvalidationMessages() between code point A and code
 point B call really_do_it()? in a way not prone to breaking when new calls to
 AcceptInvalidationMessages(), perhaps indirectly, get added.  That's what the
 local counter achieved.  To achieve that, previous_value_of_global_counter 
 would
 need to be exposed outside sinval.c.  That leaves us with a backend-local
 counter updated in a different fashion.  I might be missing something...

I see your point.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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


[HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-14 Thread Kohei KaiGai
The attached patch is a preparation to rework implementation of DROP statement
using a common code. That intends to apply get_object_address() to resolve name
of objects to be removed, and eventually minimizes the number of places to put
permission checks.

Its first step is an enhancement of get_object_address; to accept 'missing_ok'
argument to handle cases when IF EXISTS clause is supplied.
If 'missing_ok' was true and the supplied name was not found, the patched
get_object_address() returns an ObjectAddress with InvalidOid as objectId.
If 'missing_ok' was false, its behavior is not changed.

Thanks,
-- 
KaiGai Kohei kai...@kaigai.gr.jp


pgsql-v9.2-drop-reworks-part-0.1.patch
Description: Binary data

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


Re: [HACKERS] pg_trgm: unicode string not working

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I'm not sure that pg_upgrade is a good vehicle for dispensing such
 advice, anyway.  At least in the Red Hat packaging, end users will never
 read what it prints, unless maybe it fails outright and they're trying
 to debug why.

In my experience to date, that happens 100% of the time.  :-(

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Creating new remote branch in git?

2011-06-14 Thread Robert Haas
On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote:
 Alvaro Herrera wrote:
 Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011:
  Andrew Dunstan wrote:

   Is putting remotes in your ~/.gitconfig  good practice? I certainly
   don't have any in mine.
 
  Putting 'github' in there allows me to push/pull from github branches
  without having to specify the github URL.

 I think his point is that they are more properly specified in each
 repo's .git/config file, not the global $HOME/.gitconfig.  If you were
 to check out some other, unrelated project, you could end up pushing
 unrelated branches to PG's repo ...  Not sure if this is really
 possible, but it certainly seems scary to do things that way.

 I understand now --- that it is risky to create an origin branch in
 ~/.gitconfig.  I am now using an alias:

        [alias]
                pgclone = clone 
 ssh://g...@gitmaster.postgresql.org/postgresql.git

 I assume the 'github' branch in ~/.gitconfig is fine.

That, too, would better off inside $REPO/.git/config, although it's
certainly less risky than the other one.  It doesn't make much sense
to have an upstream that applies across every repository you have
checked out.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] SSI patch renumbered existing 2PC resource managers??

2011-06-14 Thread Kevin Grittner
Heikki Linnakangas  wrote:
 
 Ok, I've renumbered the existing RMs back the way they were.
 
Don't we also need something like the attached?
 
-Kevin




ssi-twophase-c.patch
Description: Binary data

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


Re: [HACKERS] per-column generic option

2011-06-14 Thread Robert Haas
I haven't looked at the patch yet, but here are a few comments on the
design, which overall looks good.

2011/6/14 Shigeru Hanada shigeru.han...@gmail.com:
 1) psql should support describing per-column generic options, so \dec
 command was added.  If the form \dec+ is used, generic options are also
 displayed.  Output sample is:

I would not add a new backslash command for this - it's unlikely to be
useful to see this information across all tables.  It would be more
helpful to somehow (not sure of the details) incorporate this into the
output of running \d on a foreign table.

 Here I found an inconsistency about privilege to see generic options
 (not only column but also FDW and server et al).  The
 information_schema.*_options only shows options which are associated to
 objects that current user can access, but \de*+ doesn't have such
 restriction.  \de* commands should be fixed to hide forbidden objects?

It's less important whether \de* is consistent with information_schema
in this regard than it is whether it is consistent with other psql
backslash commands, e.g. \dv or \db or \dC.  AFAIK those commands do
not filter by privilege.

 1) Is generic options proper term to mean FDW-specific option
 associated to a FDW object?  It's used in the SQL/MED standard, but
 seems not popular...  FDW option would be better than generic option?

I think FDW option is much clearer.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] SSI patch renumbered existing 2PC resource managers??

2011-06-14 Thread Heikki Linnakangas

On 14.06.2011 15:14, Kevin Grittner wrote:

Heikki Linnakangas  wrote:


Ok, I've renumbered the existing RMs back the way they were.


Don't we also need something like the attached?


Yes. I just committed a fix for that after noticing that the buildfarm 
didn't like it. Sorry..


--
  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: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread Robert Haas
On Mon, Jun 13, 2011 at 4:10 PM,  richhguard-monot...@yahoo.co.uk wrote:
 Apologies - I meant to CC in the list but forgot.

 I have gone through and changed all the related functions except 
 ``update_attstats''.

 Do you have any advice of how to handle the inner loops, such as those 
 initializing ``stakindN''. The entries before can be handled just like in 
 this patch, by using the symbolic constants.

 Again, this is based on master and all existing tests pass.

Anything you're not sure about, just leave alone.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Boolean operators without commutators vs. ALL/ANY

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug f...@phlo.org wrote:
 On Jun13, 2011, at 05:44 , Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote:
 (B) There should be a way to use ANY()/ALL() with the
 array elements becoming the left arguments of the operator.

 It seems to me that if we provided some way of handling this, your
 first proposal would be moot; and I have to say I like the idea of
 allowing this a lot more than tinkering with the operator names.

 There are syntactic reasons not to do that.  It'd be a lot easier just
 to provide a commutator operator for ~.

 My suggestion would be the add a commutator for ~ as a short-term
 solution (preferably in 9.1).

I don't think we want to bump catversion again before release if we
can avoid it.  And I don't see this as being a terribly urgent problem
- it's not like this is a new regression, and I can't remember hearing
any complaints about it prior to two days ago.

 Since ~ doesn't inspire any obvious names for a possible commutator,
 I suggest adding =~ and ~=.

 Is there any support for that proposal?

I'm OK with adding a commutator but I guess I don't see the point of
adding a synonym for ~ along the way.  The existing use of ~ is
consistent with, for example, awk, so it's not like we've dreamed up
something utterly crazy that we now need to fix.  I'd suggest we just
come up with some arbitrary variant, like ~~ or ~ or #~ or
!#!%@~bikeshed++!.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] 9.1 beta1 error

2011-06-14 Thread Franklin Haut
Hass,

there problem are fixed in 9.1beta2, and now works.


thanks


2011/6/14 Robert Haas robertmh...@gmail.com

 On Tue, Jun 14, 2011 at 1:17 AM, Franklin Haut franklin.h...@gmail.com
 wrote:
  hi people, postgresql goes down on a  WindowsXP SP3 machine.
  there a script to reproduce. (on XP with 8.4 works fine)

 There is a crash related to time zone handling that was fixed in 9.1beta2.

 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company



Re: [HACKERS] Creating new remote branch in git?

2011-06-14 Thread Bruce Momjian
Robert Haas wrote:
 On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote:
  Alvaro Herrera wrote:
  Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011:
   Andrew Dunstan wrote:
 
Is putting remotes in your ~/.gitconfig ?good practice? I certainly
don't have any in mine.
  
   Putting 'github' in there allows me to push/pull from github branches
   without having to specify the github URL.
 
  I think his point is that they are more properly specified in each
  repo's .git/config file, not the global $HOME/.gitconfig. ?If you were
  to check out some other, unrelated project, you could end up pushing
  unrelated branches to PG's repo ... ?Not sure if this is really
  possible, but it certainly seems scary to do things that way.
 
  I understand now --- that it is risky to create an origin branch in
  ~/.gitconfig. ?I am now using an alias:
 
  ? ? ? ?[alias]
  ? ? ? ? ? ? ? ?pgclone = clone 
  ssh://g...@gitmaster.postgresql.org/postgresql.git
 
  I assume the 'github' branch in ~/.gitconfig is fine.
 
 That, too, would better off inside $REPO/.git/config, although it's
 certainly less risky than the other one.  It doesn't make much sense
 to have an upstream that applies across every repository you have
 checked out.

Wouldn't I conceivably use github with a variety of projects?  I try to
use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall
my PG git tree.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Creating new remote branch in git?

2011-06-14 Thread Bruce Momjian
Magnus Hagander wrote:
   I understand now --- that it is risky to create an origin branch in
   ~/.gitconfig. ?I am now using an alias:
  
   ? ? ? ?[alias]
   ? ? ? ? ? ? ? ?pgclone = clone 
   ssh://g...@gitmaster.postgresql.org/postgresql.git
  
   I assume the 'github' branch in ~/.gitconfig is fine.
 
  That, too, would better off inside $REPO/.git/config, although it's
  certainly less risky than the other one. ?It doesn't make much sense
  to have an upstream that applies across every repository you have
  checked out.
 
  Wouldn't I conceivably use github with a variety of projects? ?I try to
  use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall
  my PG git tree.
 
 Yes, but your reference goes to a specific repository at github
 (bmomjian/postgres). Which wouldn't be correct for any other
 project...

Ah, I see your point.  Thanks.  I renamed it 'pggithub'.  I think I need
to read Pro Git (http://progit.org/book/), though I am not sure that
would have helped me in this exact case.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Creating new remote branch in git?

2011-06-14 Thread Magnus Hagander
On Tue, Jun 14, 2011 at 14:40, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
 On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote:
  Alvaro Herrera wrote:
  Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011:
   Andrew Dunstan wrote:
 
Is putting remotes in your ~/.gitconfig ?good practice? I certainly
don't have any in mine.
  
   Putting 'github' in there allows me to push/pull from github branches
   without having to specify the github URL.
 
  I think his point is that they are more properly specified in each
  repo's .git/config file, not the global $HOME/.gitconfig. ?If you were
  to check out some other, unrelated project, you could end up pushing
  unrelated branches to PG's repo ... ?Not sure if this is really
  possible, but it certainly seems scary to do things that way.
 
  I understand now --- that it is risky to create an origin branch in
  ~/.gitconfig. ?I am now using an alias:
 
  ? ? ? ?[alias]
  ? ? ? ? ? ? ? ?pgclone = clone 
  ssh://g...@gitmaster.postgresql.org/postgresql.git
 
  I assume the 'github' branch in ~/.gitconfig is fine.

 That, too, would better off inside $REPO/.git/config, although it's
 certainly less risky than the other one.  It doesn't make much sense
 to have an upstream that applies across every repository you have
 checked out.

 Wouldn't I conceivably use github with a variety of projects?  I try to
 use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall
 my PG git tree.

Yes, but your reference goes to a specific repository at github
(bmomjian/postgres). Which wouldn't be correct for any other
project...

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

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


Re: [HACKERS] ITYM DROP TABLE

2011-06-14 Thread Robert Haas
On Mon, Jun 13, 2011 at 5:44 PM, David E. Wheeler da...@kineticode.com wrote:
 I was reading the partitioning docs when I spotted this. I think it means to 
 highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.

I guess they might mean ALTER TABLE .. NO INHERIT.  But I think I
agree that DROP TABLE would be better.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] SSI patch renumbered existing 2PC resource managers??

2011-06-14 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian br...@momjian.us writes:
  Tom Lane wrote:
  No, pg_upgrade should not be unilaterally refusing that.
 
  Uh, isn't there some physical files in pg_twophase/ that stick around to
  keep prepared transactions --- if so, pg_upgrade does not copy them from
  the old cluster to the new one.  I am also hesistant to do so because
  there might be data in there that isn't portable.
 
 This argument seems a tad peculiar, since the *entire* *point* of
 pg_upgrade is to push physical files from one installation into another
 even though compatibility isn't guaranteed.  It is the program's duty to
 understand enough to know whether it can transport the cluster's state
 safely.  Not to arbitrarily discard state because it might possibly not
 be transportable.

Well, pg_upgrade succeeds because it does as little as necessary to do
the migration, relying on pg_dump to do much of the migration work at
the catalog level.  pg_upgrade tries to be involved as little as
possible with the Postgres code so it doesn't have to be changed
regularly between major versions.

The prepared transaction case seems ugly enough that we don't want
pg_upgrade to have to check every major release if anything changed
about the data stored in prepared transactions.  This is the same reason
pg_upgrade doesn't transfer WAL files from the old cluster, just pg_clog
files (which rarely changes its format).

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Boolean operators without commutators vs. ALL/ANY

2011-06-14 Thread Florian Pflug
On Jun14, 2011, at 14:29 , Robert Haas wrote:
 On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug f...@phlo.org wrote:
 On Jun13, 2011, at 05:44 , Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote:
 (B) There should be a way to use ANY()/ALL() with the
 array elements becoming the left arguments of the operator.
 
 It seems to me that if we provided some way of handling this, your
 first proposal would be moot; and I have to say I like the idea of
 allowing this a lot more than tinkering with the operator names.
 
 There are syntactic reasons not to do that.  It'd be a lot easier just
 to provide a commutator operator for ~.
 
 My suggestion would be the add a commutator for ~ as a short-term
 solution (preferably in 9.1).
 
 I don't think we want to bump catversion again before release if we
 can avoid it.  And I don't see this as being a terribly urgent problem
 - it's not like this is a new regression, and I can't remember hearing
 any complaints about it prior to two days ago.

Hm, OK, that makes sense...

 Since ~ doesn't inspire any obvious names for a possible commutator,
 I suggest adding =~ and ~=.
 
 Is there any support for that proposal?
 
 I'm OK with adding a commutator but I guess I don't see the point of
 adding a synonym for ~ along the way.  The existing use of ~ is
 consistent with, for example, awk, so it's not like we've dreamed up
 something utterly crazy that we now need to fix.  I'd suggest we just
 come up with some arbitrary variant, like ~~ or ~ or #~ or
 !#!%@~bikeshed++!.


That, however, I'm not at all happy with. Quite frankly, operator
naming is already a bit of a mess, and readability of queries
suffers as a result. The geometric types are especially vile
offenders in this regard, but the various array-related operators
aren't poster children either.

I think we should try to work towards more mnemonic operator
naming, not add to the mess by defining commutator pairs whose
names bear no visual resemblance whatsoever to one each other.

I'm not wedded to =~, it's just the only name I could come
up which
  (a) has a natural commutator
  (b) gives visual indication of which argument constitutes the
  text and which the pattern
  (c) there is precedent for.

BTW, there's actually precedent for a commutator of ~, namely
@. Some of the geometric types (polygon, box, circle, point,
path) use ~ as a commutator for @ (which stands for contains).
But IMHO that mainly proves that the geometric types are vile
offenders when it comes to readability...

The pair (@, ~ ) is also the only pair of commutators whose
names are totally unrelated to each other. Given a suitable
definition of a reverse() function for text [1], the following query

select
o1.oprleft::regtype || ' ' || o1.oprname || ' ' || o1.oprright::regtype 
as opr,
o2.oprleft::regtype || ' ' || o2.oprname || ' ' || o2.oprright::regtype 
as com,
o1.oprcode as opr_code,
o2.oprcode as com_code
from pg_operator o1
join pg_operator o2 on o1.oprcom = o2.oid or o2.oprcom = o1.oid
where
o1.oid  o2.oid and
o1.oprname  reverse(translate(o2.oprname, '', '')) and
o1.oprname  translate(o2.oprname, '', '');

produces

opr|com|  opr_code   | com_code 
 
---+---+-+---
 polygon @ polygon | polygon ~ polygon | poly_contained  | poly_contain
 box @ box | box ~ box | box_contained   | box_contain
 circle @ circle   | circle ~ circle   | circle_contained| circle_contain
 point @ path  | path ~ point  | on_ppath| path_contain_pt
 point @ polygon   | polygon ~ point   | pt_contained_poly   | poly_contain_pt
 point @ circle| circle ~ point| pt_contained_circle | circle_contain_pt
(6 rows)

best regards,
Florian Pflug

[1] I used
create or replace function reverse(text) returns text as $$
select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, 
-1) i
$$ language sql immutable;


-- 
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: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread Alvaro Herrera
Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 2011:
 Apologies - I meant to CC in the list but forgot.
 
 I have gone through and changed all the related functions except 
 ``update_attstats''.
 
 Do you have any advice of how to handle the inner loops, such as those 
 initializing ``stakindN''. The entries before can be handled just like in 
 this patch, by using the symbolic constants.

Based on Tom's comments, I'd submit the patch without that bit, at least
as a first step.

 Again, this is based on master and all existing tests pass.

Please post the patch and add it here:
https://commitfest.postgresql.org/action/commitfest_view?id=10

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] WIP: collect frequency statistics for arrays

2011-06-14 Thread Alexander Korotkov
Version of patch with few more comments and some fixes.

--
With best regards,
Alexander Korotkov.


arrayanalyze-0.4.patch.gz
Description: GNU Zip compressed data

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


[HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Cédric Villemain
2011/5/16 Greg Smith g...@2ndquadrant.com:
 Cédric Villemain wrote:


 http://git.postgresql.org/gitweb?p=users/c2main/postgres.git;a=shortlog;h=refs/heads/analyze_cache


 This rebases easily to make Cedric's changes move to the end; I just pushed
 a version with that change to
 https://github.com/greg2ndQuadrant/postgres/tree/analyze_cache if anyone
 wants a cleaner one to browse.  I've attached a patch too if that's more
 your thing.

 I'd recommend not getting too stuck on the particular hook Cédric has added
 here to compute the cache estimate, which uses mmap and mincore to figure it
 out.  It's possible to compute similar numbers, albeit less accurate, using
 an approach similar to how pg_buffercache inspects things.  And I even once
 wrote a background writer extension that collected this sort of data as it
 was running the LRU scan anyway.  Discussions of this idea seem to focus on
 how the what's in the cache? data is collected, which as far as I'm
 concerned is the least important part.  There are multiple options, some
 work better than others, and there's no reason that can't be swapped out
 later.  The more important question is how to store the data collected and
 then use it for optimizing queries.

Attached are updated patches without the plugin itself. I've also
added the cache_page_cost GUC, this one is not per tablespace, like
others page_cost.

There are 6 patches:

0001-Add-reloscache-column-to-pg_class.patch
0002-Add-a-function-to-update-the-new-pg_class-cols.patch
0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch
0004-Add-a-Hook-to-handle-OSCache-stats.patch
0005-Add-reloscache-to-Index-Rel-OptInfo.patch
0006-Add-cache_page_cost-GUC.patch

I have some comments on my own code:

* I am not sure of the best datatype to use for 'reloscache'
* I didn't include the catalog number change in the patch itself.
* oscache_update_relstats() is very similar to vac_update_relstats(),
maybe better to merge them but reloscache should not be updated at the
same time than other stats.
* There is probably too much work done in do_oscache_analyze_rel()
because I kept vac_open_indexes() (not a big drama atm)
* I don't know so much how gram.y works, so I am not sure my changes
cover all cases.
* No tests; similar columns and GUC does not have test either, but it
lacks a test for ANALYZE OSCACHE

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support
From d2fe7e85aea31cfe8cd6559a060f71c424fe03af Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9dric=20Villemain?= ced...@2ndquadrant.fr
Date: Wed, 25 May 2011 23:17:36 +0200
Subject: [PATCH 1/7] Add reloscache column to pg_class

1 column reloscache to contain the percentage of pages in cache per relation.
May be used by the planner and updated with ANALYZE OSCACHE; (not done yet,
see next commits)
---
 doc/src/sgml/catalogs.sgml |   11 +
 src/backend/catalog/heap.c |1 +
 src/backend/utils/cache/relcache.c |2 +
 src/include/catalog/pg_class.h |   44 ++-
 4 files changed, 37 insertions(+), 21 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
new file mode 100644
index 8504555..4cfad39
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***
*** 1634,1639 
--- 1634,1650 
   /row
  
   row
+   entrystructfieldreloscache/structfield/entry
+   entrytypefloat4/type/entry
+   entry/entry
+   entry
+Percentage of the files in OS cache.  This is only an estimate used by
+the planner.  It is updated by commandANALYZE OSCACHE/command.
+By default, the value is not updated and an extension is required.
+   /entry
+  /row
+ 
+  row
entrystructfieldreltoastrelid/structfield/entry
entrytypeoid/type/entry
entryliterallink linkend=catalog-pg-classstructnamepg_class/structname/link.oid/literal/entry
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
new file mode 100644
index a6e541d..2043c40
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
*** InsertPgClassTuple(Relation pg_class_des
*** 764,769 
--- 764,770 
  	values[Anum_pg_class_reltablespace - 1] = ObjectIdGetDatum(rd_rel-reltablespace);
  	values[Anum_pg_class_relpages - 1] = Int32GetDatum(rd_rel-relpages);
  	values[Anum_pg_class_reltuples - 1] = Float4GetDatum(rd_rel-reltuples);
+ 	values[Anum_pg_class_reloscache - 1] = Float4GetDatum(rd_rel-reloscache);
  	values[Anum_pg_class_reltoastrelid - 1] = ObjectIdGetDatum(rd_rel-reltoastrelid);
  	values[Anum_pg_class_reltoastidxid - 1] = ObjectIdGetDatum(rd_rel-reltoastidxid);
  	values[Anum_pg_class_relhasindex - 1] = BoolGetDatum(rd_rel-relhasindex);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
new file mode 100644
index d7e94ff..ca09e3b
*** a/src/backend/utils/cache/relcache.c
--- 

Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 2011:
 Do you have any advice of how to handle the inner loops, such as those 
 initializing ``stakindN''. The entries before can be handled just like in 
 this patch, by using the symbolic constants.

 Based on Tom's comments, I'd submit the patch without that bit, at least
 as a first step.

He already did no?

I did think of a possible way to rewrite update_attstats: instead of

for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
{
values[i++] = ObjectIdGetDatum(stats-staop[k]);/* staopN */
}

do

for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
{
values[Anum_pg_statistic_staop1 - 1 + k] = 
ObjectIdGetDatum(stats-staop[k]);
}

etc.  However, it's not clear to me whether this is really an
improvement.  Opinions?

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] ITYM DROP TABLE

2011-06-14 Thread Alvaro Herrera
Excerpts from David E. Wheeler's message of lun jun 13 17:44:05 -0400 2011:
 I was reading the partitioning docs when I spotted this. I think it means to 
 highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.

I think the point of the existing wording is to point out
ALTER TABLE / NO INHERIT.  I wonder if it's worth expanding the text to
mention both, such as

--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2320,7 +2320,9 @@ VALUES ('New York', NULL, NULL, 'NY');
  para
   Bulk loads and deletes can be accomplished by adding or removing
   partitions, if that requirement is planned into the partitioning design.
-  commandALTER TABLE/ is far faster than a bulk operation.
+  commandALTER TABLE/ (to split out a sub-table from the partitioned
+  table) and commandDROP TABLE/ (to remove a partition altogether) are
+  both far faster than a bulk operation.
   It also entirely avoids the commandVACUUM/command
   overhead caused by a bulk commandDELETE/.
  /para


However, this introductory text is supposed to be very brief; maybe we
should remove mention of specific commands here.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] PATCH: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 
 2011:
 Do you have any advice of how to handle the inner loops, such as those 
 initializing ``stakindN''. The entries before can be handled just like in 
 this patch, by using the symbolic constants.

 Based on Tom's comments, I'd submit the patch without that bit, at least
 as a first step.

 He already did no?

 I did think of a possible way to rewrite update_attstats: instead of

        for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
        {
            values[i++] = ObjectIdGetDatum(stats-staop[k]);    /* staopN */
        }

 do

        for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
        {
            values[Anum_pg_statistic_staop1 - 1 + k] = 
 ObjectIdGetDatum(stats-staop[k]);
        }

 etc.  However, it's not clear to me whether this is really an
 improvement.  Opinions?

I don't care that much, but IMV that's just gilding the lily.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar jun 14 10:30:28 -0400 2011:
 Alvaro Herrera alvhe...@commandprompt.com writes:
  Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 
  2011:
  Do you have any advice of how to handle the inner loops, such as those 
  initializing ``stakindN''. The entries before can be handled just like in 
  this patch, by using the symbolic constants.
 
  Based on Tom's comments, I'd submit the patch without that bit, at least
  as a first step.
 
 He already did no?

I don't see the patch attached anywhere ...

 I did think of a possible way to rewrite update_attstats: instead of
 
 for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
 {
 values[i++] = ObjectIdGetDatum(stats-staop[k]);/* staopN */
 }
 
 do
 
 for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
 {
 values[Anum_pg_statistic_staop1 - 1 + k] = 
 ObjectIdGetDatum(stats-staop[k]);
 }
 
 etc.  However, it's not clear to me whether this is really an
 improvement.  Opinions?

I guess the other option is

 i = Anum_pg_statistic_staop1 - 1;
 for (k = 0; k  STATISTIC_NUM_SLOTS; k++)
 {
 values[i++] = ObjectIdGetDatum(stats-staop[k]);
 }

(I also tried moving the i initialization to the for first arg, but it
seems better this way)

Not sure what's better.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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: patch review : Add ability to constrain backend temporary file space

2011-06-14 Thread Cédric Villemain
2011/6/3 Mark Kirkwood mark.kirkw...@catalyst.net.nz:
 On 02/06/11 18:34, Jaime Casanova wrote:


 - the patch adds this to serial_schedule but no test has been added...

 diff --git a/src/test/regress/serial_schedule
 b/src/test/regress/serial_schedule
 index bb654f9..325cb3d 100644
 --- a/src/test/regress/serial_schedule
 +++ b/src/test/regress/serial_schedule
 @@ -127,3 +127,4 @@ test: largeobject
  test: with
  test: xml
  test: stats
 +test: resource


 Corrected v4 patch with the test files, for completeness. Note that
 discussion has moved on and there will be a v5 :-)


Mark, can you submit your updated patch ?

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et 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] SSI work for 9.1

2011-06-14 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
 
 I did some further changes, refactoring SkipSerialization so that
 it's hopefully more readable, and added a comment about the
 side-effects. See attached. Let me know if I'm missing something.
 
I do think the changes improve readability.  I don't see anything
missing, but there's something we can drop.  Now that you've split
the read and write tests, this part can be dropped from the
SerializationNeededForWrite function:
 
+
+   /* Check if we have just become RO-safe. */
+   if (SxactIsROSafe(MySerializableXact))
+   {
+   ReleasePredicateLocks(false);
+   return false;
+   }
 
If it's doing a write, it can't be a read-only transaction
 
-Kevin

-- 
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] ITYM DROP TABLE

2011-06-14 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from David E. Wheeler's message of lun jun 13 17:44:05 -0400 2011:
 I was reading the partitioning docs when I spotted this. I think it means to 
 highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.

 I think the point of the existing wording is to point out
 ALTER TABLE / NO INHERIT.  I wonder if it's worth expanding the text to
 mention both, such as

 -  commandALTER TABLE/ is far faster than a bulk operation.
 +  commandALTER TABLE/ (to split out a sub-table from the partitioned
 +  table) and commandDROP TABLE/ (to remove a partition altogether) 
 are
 +  both far faster than a bulk operation.

I think you need to spell out ALTER TABLE NO INHERIT if you are going
to do that.  This formulation seems to imply that *any* form of ALTER
TABLE is fast, which surely ain't the truth.

 However, this introductory text is supposed to be very brief; maybe we
 should remove mention of specific commands here.

No, I don't think it needs to be that brief.  But if you think your
version is too long, remove the parenthetical remarks.

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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 10:29 AM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
 0001-Add-reloscache-column-to-pg_class.patch
 0002-Add-a-function-to-update-the-new-pg_class-cols.patch
 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch
 0004-Add-a-Hook-to-handle-OSCache-stats.patch
 0005-Add-reloscache-to-Index-Rel-OptInfo.patch
 0006-Add-cache_page_cost-GUC.patch

It seems to me that posting updated versions of this patch gets us no
closer to addressing the concerns I (and Tom, on other threads)
expressed about this idea previously.  Specifically:

1. ANALYZE happens far too infrequently to believe that any data taken
at ANALYZE time will still be relevant at execution time.
2. Using data gathered by ANALYZE will make plans less stable, and our
users complain not infrequently about the plan instability we already
have, therefore we should not add more.
3. Even if the data were accurate and did not cause plan stability, we
have no evidence that using it will improve real-world performance.

Now, it's possible that you or someone else could provide some
experimental evidence refuting these points.  But right now there
isn't any, and until there is, -1 from me on applying any of this.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] use less space in xl_xact_commit patch

2011-06-14 Thread Robert Haas
On Wed, May 25, 2011 at 3:05 PM, Simon Riggs si...@2ndquadrant.com wrote:
 Yes, that's correct. We can remove them from the normal commit record
 when nmsgs == 0.

Leonardo, can you submit an updated version of this patch today that
incorporates Simon's suggestion?  The CommitFest starts tomorrow.  If
not, please feel free to resubmit to the next CommitFest.  Simon or I
may find the time to review it sooner rather than later even if it
misses the deadline for this CommitFest, because I think it's an
important optimization and I know you have other work that depends on
it.  But for now we should mark it Returned with Feedback if you don't
have an updated version ready to go.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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: add GiST support for BOX @ POINT queries

2011-06-14 Thread Robert Haas
On Fri, Jun 10, 2011 at 6:16 AM, Hitoshi Harada umi.tan...@gmail.com wrote:
 2011/2/24 Andrew Tipton andrew.t.tip...@gmail.com:
 While playing around with the BOX and POINT datatypes, I was surprised to
 note that BOX @ POINT (and likewise POINT @ BOX) queries were not using
 the GiST index I had created on the BOX column.  The attached patch adds a
 new strategy @(BOX,POINT) to the box_ops opclass.  Internally,
 gist_box_consistent simply transforms the POINT into its corresponding BOX.
 This is my first Postgres patch, and I wasn't able to figure out how to go
 about creating a regression test for this change.  (All existing tests do
 pass, but none of them seem to specifically test index behaviour.)

 I reviewed the patch and worried about hard-wired magic number as
 StrategyNumber. At least you should use #define to indicate the
 number's meaning.

 In addition, the modified gist_box_consistent() is too dangerous;
 q_box is declared in the if block locally and is referenced, which
 pointer is passed to the outer process of the block. AFAIK if the
 local memory of each block is alive outside if block is
 platform-dependent.

 Isn't it worth adding new consistent function for those purposes? The
 approach in the patch as stands looks kludge to me.

Andrew - in case it's not clear, we're waiting on you to respond to
Hitoshi's comments or provide an updated patch.

Thanks,

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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: CreateComments: use explicit indexing for ``values''

2011-06-14 Thread richhguard-monotone
I have left update_attstat which I'm unsure about, and have attached the 
updated patch handling the other cases. This will be linked in via the 
commitfest page.

I picked commands/comment.c randomly and found the i = 0, i++ method of 
initializing the array made it harder for me to visualize it's structure, or 
verify each value was in the correct place in the array.

Obviously we can assume each value is in the correct place because it's been 
like that in working code.

This patch makes the intent of each initialization clear by using the constants 
directly instead of in a comment, and has the effect of being able to verify 
each line on it's own. The original requires verification of the preceding 
i++'s.

I expanded upon my original patch of only handling CreateComments to include 
the other cases for consistency - but only so far as update_attstats as I found 
it too complex for me and left it. I don't like to break anything.

I'm not going to push for it if most people prefer the original code for 
readability, or maintainability across versions; I just thought I'd post my 
thoughts with a patch.

An easier route, might be for me to submit a patch which just changes comment.c 
by adding in the constants via a comment like the other places. What do you 
think ?

Richard

--- On Tue, 14/6/11, Alvaro Herrera alvhe...@commandprompt.com wrote:

 From: Alvaro Herrera alvhe...@commandprompt.com
 Subject: Re: [HACKERS] PATCH: CreateComments: use explicit indexing for 
 ``values''
 To: richhguard-monotone richhguard-monot...@yahoo.co.uk
 Cc: pgsql-hackers pgsql-hackers@postgresql.org
 Date: Tuesday, 14 June, 2011, 15:20
 Excerpts from richhguard-monotone's
 message of lun jun 13 16:10:17 -0400 2011:
  Apologies - I meant to CC in the list but forgot.
  
  I have gone through and changed all the related
 functions except ``update_attstats''.
  
  Do you have any advice of how to handle the inner
 loops, such as those initializing ``stakindN''. The entries
 before can be handled just like in this patch, by using the
 symbolic constants.
 
 Based on Tom's comments, I'd submit the patch without that
 bit, at least
 as a first step.
 
  Again, this is based on master and all existing tests
 pass.
 
 Please post the patch and add it here:
 https://commitfest.postgresql.org/action/commitfest_view?id=10
 
 -- 
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development,
 24x7 support
commit 7a689fc96b624de5de589cd9e2ef1c42ae542a16
Author: Richard Hopkins richhguard-monot...@yahoo.co.uk
Date:   Mon Jun 13 20:44:15 2011 +0100

Initialize arrays using Anum_pg symbolic constants

This clarifies the intent of the code by using the already defined symbolic
constants; the constants were referenced in the related comments for each
column anyway.

However, ``update_attstats'' still uses the old style of i=0, and then i++
with each element initialization. I have left this for now as it seems too
tricky to include with these changes.

``update_attstats'' has a few loops using ``STATISTIC_NUM_SLOTS'' and I'm
unsure whether to remove them, and use the symbolic constants such as
``Anum_pg_statistic_stakind1'' at the expense of adding extra lines.

diff --git a/src/backend/catalog/pg_operator.c b/src/backend/catalog/pg_operator.c
index ccd0fe1..4d2d7b7 100644
--- a/src/backend/catalog/pg_operator.c
+++ b/src/backend/catalog/pg_operator.c
@@ -234,22 +234,21 @@ OperatorShellMake(const char *operatorName,
 	 * initialize values[] with the operator name and input data types. Note
 	 * that oprcode is set to InvalidOid, indicating it's a shell.
 	 */
-	i = 0;
 	namestrcpy(oname, operatorName);
-	values[i++] = NameGetDatum(oname); /* oprname */
-	values[i++] = ObjectIdGetDatum(operatorNamespace);	/* oprnamespace */
-	values[i++] = ObjectIdGetDatum(GetUserId());		/* oprowner */
-	values[i++] = CharGetDatum(leftTypeId ? (rightTypeId ? 'b' : 'r') : 'l');	/* oprkind */
-	values[i++] = BoolGetDatum(false);	/* oprcanmerge */
-	values[i++] = BoolGetDatum(false);	/* oprcanhash */
-	values[i++] = ObjectIdGetDatum(leftTypeId); /* oprleft */
-	values[i++] = ObjectIdGetDatum(rightTypeId);		/* oprright */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprresult */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprcom */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprnegate */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprcode */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprrest */
-	values[i++] = ObjectIdGetDatum(InvalidOid); /* oprjoin */
+	values[Anum_pg_operator_oprname - 1] = NameGetDatum(oname);
+	values[Anum_pg_operator_oprnamespace - 1] = ObjectIdGetDatum(operatorNamespace);
+	values[Anum_pg_operator_oprowner - 1] = ObjectIdGetDatum(GetUserId());
+	values[Anum_pg_operator_oprkind - 1] = CharGetDatum(leftTypeId ? (rightTypeId ? 'b' : 'r') : 'l');
+	values[Anum_pg_operator_oprcanmerge 

Re: [HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Cédric Villemain
2011/6/14 Robert Haas robertmh...@gmail.com:
 On Tue, Jun 14, 2011 at 10:29 AM, Cédric Villemain
 cedric.villemain.deb...@gmail.com wrote:
 0001-Add-reloscache-column-to-pg_class.patch
 0002-Add-a-function-to-update-the-new-pg_class-cols.patch
 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch
 0004-Add-a-Hook-to-handle-OSCache-stats.patch
 0005-Add-reloscache-to-Index-Rel-OptInfo.patch
 0006-Add-cache_page_cost-GUC.patch

 It seems to me that posting updated versions of this patch gets us no
 closer to addressing the concerns I (and Tom, on other threads)
 expressed about this idea previously.  Specifically:

 1. ANALYZE happens far too infrequently to believe that any data taken
 at ANALYZE time will still be relevant at execution time.

ANALYZE happens when people execute it, else it is auto-analyze and I
am not providing auto-analyze-oscache.
ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The
frequency is not important here, I believe.

 2. Using data gathered by ANALYZE will make plans less stable, and our
 users complain not infrequently about the plan instability we already
 have, therefore we should not add more.

Again, it is hard to do a UPDATE pg_class SET reloscache, so I used
ANALYZE logic.
Also I have taken into account the fact that someone may want to SET
the values like it was also suggested, so my patches allow to do :
'this table is 95% in cache, the DBA said' (it is stable, not based on
OS stats).

This case has been suggested several times and is covered by my patch.

 3. Even if the data were accurate and did not cause plan stability, we
 have no evidence that using it will improve real-world performance.

I have not finish my work on cost estimation and I believe this work
will take some time and can be done in another commitfest. At the
moment my patches do not change anything on the dcision of the
planner, just offers the tools I need to hack cost estimates.


 Now, it's possible that you or someone else could provide some
 experimental evidence refuting these points.  But right now there
 isn't any, and until there is, -1 from me on applying any of this.

I was trying to split the patch size by group of features to reduce
its size. The work is in progress.


 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et 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] psql describe.c cleanup

2011-06-14 Thread Merlin Moncure
On Sat, May 7, 2011 at 2:40 PM, Josh Kupershmidt schmi...@gmail.com wrote:
 Hi all,

 I use psql's -E mode every now and then, copy-and-pasting and further
 tweaking the SQL displayed. Most queries are displayed terminated by a
 semicolon, but quite a few aren't, making copy-and-paste just a bit
 more tedious.

 Attached is a patch to fix every SQL query I saw in describe.c. There
 were also a few queries with trailing newlines, and I fixed those too.

I did a quick review and test of your patch.  It didn't quite apply
cleanly due to recent non-related describe.c changes -- updated patch
attached.

First, I'll give you a thumbs up on the original inspiration for the
patch.  The output should be standardized, and I see no reason not to
append a semicolon on usability basis.  Beyond that, the changes are
mostly cosmetic and I can't see how it will break things outside of
terminating a query early by accident (I didn't see any).

What I do wonder though is if the ; appending should really be
happening in printQuery() instead of in each query -- the idea being
that formatting for external consumption should be happening in one
place.  Maybe that's over-thinking it though.

merlin


psql_describe.v3.patch
Description: Binary data

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


Re: [HACKERS] ITYM DROP TABLE

2011-06-14 Thread David E. Wheeler
On Jun 14, 2011, at 8:03 AM, Tom Lane wrote:

 -  commandALTER TABLE/ is far faster than a bulk operation.
 +  commandALTER TABLE/ (to split out a sub-table from the partitioned
 +  table) and commandDROP TABLE/ (to remove a partition altogether) 
 are
 +  both far faster than a bulk operation.
 
 I think you need to spell out ALTER TABLE NO INHERIT if you are going
 to do that.  This formulation seems to imply that *any* form of ALTER
 TABLE is fast, which surely ain't the truth.
 
 However, this introductory text is supposed to be very brief; maybe we
 should remove mention of specific commands here.
 
 No, I don't think it needs to be that brief.  But if you think your
 version is too long, remove the parenthetical remarks.

+1 I think that would be perfect.

Best,

David


-- 
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] ITYM DROP TABLE

2011-06-14 Thread Alvaro Herrera
Excerpts from David E. Wheeler's message of mar jun 14 12:33:27 -0400 2011:
 On Jun 14, 2011, at 8:03 AM, Tom Lane wrote:
 
  -  commandALTER TABLE/ is far faster than a bulk operation.
  +  commandALTER TABLE/ (to split out a sub-table from the 
  partitioned
  +  table) and commandDROP TABLE/ (to remove a partition 
  altogether) are
  +  both far faster than a bulk operation.
  
  I think you need to spell out ALTER TABLE NO INHERIT if you are going
  to do that.  This formulation seems to imply that *any* form of ALTER
  TABLE is fast, which surely ain't the truth.
  
  However, this introductory text is supposed to be very brief; maybe we
  should remove mention of specific commands here.
  
  No, I don't think it needs to be that brief.  But if you think your
  version is too long, remove the parenthetical remarks.
 
 +1 I think that would be perfect.

Done that way (9.0 and beyond).

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] procpid?

2011-06-14 Thread Jim Nasby
On Jun 13, 2011, at 10:56 AM, Simon Riggs wrote:
 If we were going to make changes like this, I'd suggest we save them
 up in a big bag for when we change major version number. Everybody in
 the world thinks that PostgreSQL v8 is compatible across all versions
 (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we
 would still have forward progress, but in more sensible sized steps.
 Otherwise we just break the code annually for all the people that
 support us. If we had a more stable environment for tools vendors,
 maybe people wouldn't need to be manually typing procpid anyway...

Wouldn't it be better still to have both the new and old columns available for 
a while? That would produce the minimum amount of disruption to tools, etc. The 
only downside is some potential confusion, but that would just serve to drive 
people to the documentation to see why there were two fields, where they would 
find out one was deprecated.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.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] procpid?

2011-06-14 Thread Bruce Momjian
Jim Nasby wrote:
 On Jun 13, 2011, at 10:56 AM, Simon Riggs wrote:
  If we were going to make changes like this, I'd suggest we save them
  up in a big bag for when we change major version number. Everybody in
  the world thinks that PostgreSQL v8 is compatible across all versions
  (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we
  would still have forward progress, but in more sensible sized steps.
  Otherwise we just break the code annually for all the people that
  support us. If we had a more stable environment for tools vendors,
  maybe people wouldn't need to be manually typing procpid anyway...
 
 Wouldn't it be better still to have both the new and old columns
 available for a while? That would produce the minimum amount of
 disruption to tools, etc. The only downside is some potential confusion,
 but that would just serve to drive people to the documentation to see
 why there were two fields, where they would find out one was deprecated.

Well, someone doing SELECT *, which is probably 90% of the users, are
going to be pretty confused by duplicate columns, asking, What is the
difference?  For those people this would make things worse than they
are now.

I would say 90% of users are doing SELECT *, and 10% are joining to
other tables or displaying specific columns.  We want to help that 10%
without making that 90% confused.

--
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Why polecat and colugos are failing to build back branches

2011-06-14 Thread Robert Creager

On Jun 13, 2011, at 6:05 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 I looked into $SUBJECT.  There appear to be two distinct issues:
 
 1. On colugos (OS X with LLVM), the 
...
 However, because when using gcc that only results in a warning,
 we didn't back-patch it.  Now it appears that it's an error when using
 LLVM, so maybe we oughta back-patch it, at least to whichever releases
 we think will build with LLVM.

FYI, in the latest beta developer XCode release, gcc is llvm. That's not my 
test machine though.

 
 2. Pre-9.0, the installation step is failing like this:
...
  alternatives seem to be
 to ask Robert to rename the volume, or stop testing pre-9.0 branches on
 that machine.  

Any change is no problem, just let me know.

Later,
Rob
-- 
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] ITYM DROP TABLE

2011-06-14 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Done that way (9.0 and beyond).

Re-reading the actual commit, I notice that there's now a grammatical
problem: the following sentence says

   It also entirely avoids the commandVACUUM/command
   overhead caused by a bulk commandDELETE/.

which was okay when it referred to ALTER TABLE, but now that there
are two commands mentioned in the previous sentence, it doesn't match.
Perhaps These commands also avoid the 

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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Greg Smith

On 06/14/2011 11:04 AM, Robert Haas wrote:

Even if the data were accurate and did not cause plan stability, we
have no evidence that using it will improve real-world performance.
   


That's the dependency Cédric has provided us a way to finally make 
progress on.  Everyone says there's no evidence that this whole approach 
will improve performance.  But we can't collect such data, to prove or 
disprove it helps, without a proof of concept patch that implements 
*something*.  You may not like the particular way the data is collected 
here, but it's a working implementation that may be useful for some 
people.  I'll take data collected at ANALYZE time as a completely 
reasonable way to populate the new structures with realistic enough test 
data to use initially.


Surely at least one other way to populate the statistics, and possibly 
multiple other ways that the user selects, will be needed eventually.  I 
commented a while ago on this thread:  every one of these discussions 
always gets dragged into the details of how the cache statistics data 
will be collected and rejects whatever is suggested as not good enough.  
Until that stops, no progress will ever get made on the higher level 
details.  By its nature, developing toward integrating cached 
percentages is going to lurch forward on both collecting the cache 
data and using the cache knowledge in queries fronts almost 
independently.  This is not a commit candidate; it's the first useful 
proof of concept step for something we keep talking about but never 
really doing.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us



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


Re: [HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 1:10 PM, Greg Smith g...@2ndquadrant.com wrote:
 On 06/14/2011 11:04 AM, Robert Haas wrote:
 Even if the data were accurate and did not cause plan stability, we
 have no evidence that using it will improve real-world performance.

 That's the dependency Cédric has provided us a way to finally make progress
 on.  Everyone says there's no evidence that this whole approach will improve
 performance.  But we can't collect such data, to prove or disprove it helps,
 without a proof of concept patch that implements *something*.  You may not
 like the particular way the data is collected here, but it's a working
 implementation that may be useful for some people.  I'll take data
 collected at ANALYZE time as a completely reasonable way to populate the
 new structures with realistic enough test data to use initially.

But there's no reason that code (which may or may not eventually prove
useful) has to be incorporated into the main tree.  We don't commit
code so people can go benchmark it; we ask for the benchmarking to be
done first, and then if the results are favorable, we commit the code.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] procpid?

2011-06-14 Thread Greg Smith

On 06/14/2011 11:44 AM, Jim Nasby wrote:
Wouldn't it be better still to have both the new and old columns 
available for a while? That would produce the minimum amount of 
disruption to tools, etc.


Doing this presumes the existence of a large number of tools where the 
author is unlikely to be keeping up with PostgreSQL development.  I 
don't believe that theorized set of users actually exists.  There are 
people who use pg_stat_activity simply, and there are tool authors who 
are heavily involved enough that they will see a change here coming far 
enough in advance to adopt it without disruption.  If there's a large 
base of casual tool authors, who wrote something using 
pg_stat_activity once and will never update it again, I don't know where 
they are.


Anyway, I want a larger change to pg_stat_activity than this one, and I 
would just roll fixing this column name into that more disruptive and 
positive change.  Right now the biggest problem with this view is that 
you have to parse the text of the query to figure out what state the 
connection is in.  This is silly; there should be boolean values exposed 
for idle and in transaction.  I want to be able to write things like 
this:


SELECT idle,in_trans,count(*) FROM pg_stat_activity GROUP BY idle,in_trans;
SELECT min(backend_start) FROM pg_stat_activity WHERE idle;

Right now the standard approach to this is to turn current_query into a 
derived state value using CASE statements.  It's quite unfriendly, and a 
bigger problem than this procpid mismatch.  Fix that whole mess at once, 
and now you've got something useful enough to justify breaking tools.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us



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


Re: [HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 12:06 PM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
 1. ANALYZE happens far too infrequently to believe that any data taken
 at ANALYZE time will still be relevant at execution time.

 ANALYZE happens when people execute it, else it is auto-analyze and I
 am not providing auto-analyze-oscache.
 ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The
 frequency is not important here, I believe.

Well, I'm not saying you have to have all the answers to post a WIP
patch, certainly.  But in terms of getting something committable, it
seems like we need to have at least an outline of what the long-term
plan is.  If ANALYZE OSCACHE is an infrequent operation, then the data
isn't going to be a reliable guide to what will happen at execution
time...

 2. Using data gathered by ANALYZE will make plans less stable, and our
 users complain not infrequently about the plan instability we already
 have, therefore we should not add more.

...and if it is a frequent operation then it's going to result in
unstable plans (and maybe pg_class bloat).  There's a fundamental
tension here that I don't think you can just wave your hands at.

 I was trying to split the patch size by group of features to reduce
 its size. The work is in progress.

Totally reasonable, but I can't see committing any of it without some
evidence that there's light at the end of the tunnel.  No performance
tests *whatsoever* have been done.  We can debate the exact amount of
evidence that should be required to prove that something is useful
from a performance perspective, but we at least need some.  I'm
beating on this point because I believe that the whole idea of trying
to feed this information back into the planner is going to turn out to
be something that we don't want to do.  I think it's going to turn out
to have downsides that are far larger than the upsides.  I am
completely willing to be be proven wrong, but right now I think this
will make things worse and you think it will make things better and I
don't see any way to bridge that gap without doing some measurements.

For example, if you run this patch on a system and subject that system
to a relatively even workload, how much do the numbers bounce around
between runs?  What if you vary the workload, so that you blast it
with OLTP traffic at some times and then run reporting queries at
other times?  Or different tables become hot at different times?

Once you've written code to make the planner do something with the
caching % values, then you can start to explore other questions.  Can
you generate plan instability, especially on complex queries, which
are more prone to change quickly based on small changes in the cost
estimates?  Can you demonstrate a workload where bad performance is
inevitable with the current code, but with your code, the system
becomes self-tuning and ends up with good performance?  What happens
if you have a large cold table with a small hot end where all activity
is concentrated?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] procpid?

2011-06-14 Thread Jaime Casanova
On Tue, Jun 14, 2011 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote:

 Anyway, I want a larger change to pg_stat_activity than this one

Well, Simon recomended to have a big bag of changes that justify break
tools... and you have presented a good one item for that bag...
Maybe we should start a wiki page for this and put there all the
changes we want to see before break anything?

for example, a change i want to see is in csvlog: i want a duration
field there because tools like pgfouine, pgsi and others parse the
message field for a duration string which is only usefull if the
message is in english which non-english dba's won't have

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

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


[HACKERS] startupBufferPinWaitBufId vs. ProcGlobalShmemSize

2011-06-14 Thread Robert Haas
ProcGlobalShmemSize() currently includes code to allow space for
startupBufferPinWaitBufId.  But I think that's redundant, because that
variable is stored in PROC_HDR *ProcGlobal, for which this function is
separately allocating space.

So I propose to apply the attached patch, barring objections.

I see no reason to back-patch this, since allocating an extra 4 bytes
of shared memory is hardly anything to get excited about.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


procglobalshmemsize-cleanup.patch
Description: Binary data

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


Re: [HACKERS] procpid?

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 1:43 PM, Jaime Casanova ja...@2ndquadrant.com wrote:
 On Tue, Jun 14, 2011 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote:

 Anyway, I want a larger change to pg_stat_activity than this one

 Well, Simon recomended to have a big bag of changes that justify break
 tools... and you have presented a good one item for that bag...
 Maybe we should start a wiki page for this and put there all the
 changes we want to see before break anything?

 for example, a change i want to see is in csvlog: i want a duration
 field there because tools like pgfouine, pgsi and others parse the
 message field for a duration string which is only usefull if the
 message is in english which non-english dba's won't have

There are real problems with the idea of having one release where we
break everything that we want to break - mostly from a process
standpoint.  We aren't always good at being organized and disciplined,
and coming up with a multi-year plan to break everything all at once
in 2014 for release in 2015 may be difficult, because it requires a
consensus on release management to hold together for years, and
sometimes we can't even manage days.

But I don't think it's a bad idea to try.  So +1 for creating a list
of things that we think we might like to break at some point.  It
might be worth trying to do this in the context of the Todo list -
come up with some special badge or flag that we can put on items that
require a compatibility break, so that we can scan for them there
easily.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] procpid?

2011-06-14 Thread Kevin Grittner
Greg Smith g...@2ndquadrant.com wrote:
 
 Doing this presumes the existence of a large number of tools where
 the author is unlikely to be keeping up with PostgreSQL
 development.  I don't believe that theorized set of users actually
 exists.
 
There could be a number of queries used for monitoring or
administration which will be affected.  Just on our Wiki pages we
have some queries available for copy/paste which would need multiple
versions while both column names were in supported versions of the
software:
 
http://wiki.postgresql.org/wiki/Lock_dependency_information
http://wiki.postgresql.org/wiki/Lock_Monitoring
http://wiki.postgresql.org/wiki/Backend_killer_function
 
I agree that these are manageable, but not necessarily trivial. 
(You should see how long it can take to get them to install new
monitoring software to our centralized system here.)  I think that's
consistent with the save up our breaking changes to do them all at
once approach.
 
-Kevin

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


[HACKERS] One-Shot Plans

2011-06-14 Thread Simon Riggs
Currently, the planner and executor are mostly independent of each
other: the planner doesn't really know when the plan will be executed,
and the executor doesn't know how recently the plan was made.

We can work out the various paths through the traffic cop to see when
a plan will be a one-shot - planned and then executed immediately,
then discarded.

In those cases we can take advantage of better optimisations. Most
interestingly, we can evaluate stable functions at plan time, to allow
us to handle partitioning and partial indexes better.

Patch attached. Works...

SET constraint_exclusion = on;
ALTER TABLE table ADD CHECK (dt  current_date - 5);
SELECT * FROM table WHERE datecolumn = current_date - 1;
QUERY PLAN
--
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false
(2 rows)

WIP in the sense that we might want to change the special case
parameter handling as well.

Comments?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


oneshot_plans.v2.patch
Description: Binary data

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


Re: [HACKERS] One-Shot Plans

2011-06-14 Thread Bruce Momjian
Simon Riggs wrote:
 Currently, the planner and executor are mostly independent of each
 other: the planner doesn't really know when the plan will be executed,
 and the executor doesn't know how recently the plan was made.
 
 We can work out the various paths through the traffic cop to see when
 a plan will be a one-shot - planned and then executed immediately,
 then discarded.

I was also hoping someday allow plans that are to be immediately
executed to probe the buffer cache to determine how expensive index
scans would be.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] use less space in xl_xact_commit patch

2011-06-14 Thread Simon Riggs
On Tue, Jun 14, 2011 at 4:21 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, May 25, 2011 at 3:05 PM, Simon Riggs si...@2ndquadrant.com wrote:
 Yes, that's correct. We can remove them from the normal commit record
 when nmsgs == 0.

 Leonardo, can you submit an updated version of this patch today that
 incorporates Simon's suggestion?  The CommitFest starts tomorrow.  If
 not, please feel free to resubmit to the next CommitFest.  Simon or I
 may find the time to review it sooner rather than later even if it
 misses the deadline for this CommitFest, because I think it's an
 important optimization and I know you have other work that depends on
 it.  But for now we should mark it Returned with Feedback if you don't
 have an updated version ready to go.

We don't need to be in a hurry here. As the reviewer I'm happy to give
Leonardo some time, obviously no more than the end of the commit fest.

If he doesn't respond at all, I'll do it, but I'd like to give him the
chance and the experience if possible.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  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] One-Shot Plans

2011-06-14 Thread Simon Riggs
On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.us wrote:
 Simon Riggs wrote:
 Currently, the planner and executor are mostly independent of each
 other: the planner doesn't really know when the plan will be executed,
 and the executor doesn't know how recently the plan was made.

 We can work out the various paths through the traffic cop to see when
 a plan will be a one-shot - planned and then executed immediately,
 then discarded.

 I was also hoping someday allow plans that are to be immediately
 executed to probe the buffer cache to determine how expensive index
 scans would be.

Yes, it opens up many optimizations, both for cache sensitivity and
dynamic data access.

But those are later ideas based on the existence of this first step.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


[HACKERS] Crash dumps

2011-06-14 Thread Radosław Smogura

Hello,

Because, I work a little bit on streaming protocol and from time to 
time I have crashes. I want ask if you wont crash reporting (this is one 
of minors products from mmap playing) those what I have there is mmaped 
areas, and call stacks, and some other stuff. This based reports works 
for Linux with gdb, but there is some pluggable architecture, which 
connects with segfault - one thing that should be considered is to kill 
other processes immediately when reporting started (as taking report 
takes some time) so some IPC will be required.


I may polish this a little bit, and send patch for this (currently 
without IPC killing of others).


Regards,
Radosław Smogura

--
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] One-Shot Plans

2011-06-14 Thread Bruce Momjian
Simon Riggs wrote:
 On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.us wrote:
  Simon Riggs wrote:
  Currently, the planner and executor are mostly independent of each
  other: the planner doesn't really know when the plan will be executed,
  and the executor doesn't know how recently the plan was made.
 
  We can work out the various paths through the traffic cop to see when
  a plan will be a one-shot - planned and then executed immediately,
  then discarded.
 
  I was also hoping someday allow plans that are to be immediately
  executed to probe the buffer cache to determine how expensive index
  scans would be.
 
 Yes, it opens up many optimizations, both for cache sensitivity and
 dynamic data access.
 
 But those are later ideas based on the existence of this first step.

Agreed.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] ITYM DROP TABLE

2011-06-14 Thread Simon Riggs
On Mon, Jun 13, 2011 at 10:44 PM, David E. Wheeler da...@kineticode.com wrote:
 I was reading the partitioning docs when I spotted this. I think it means to 
 highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.

 Best,

 David

 diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
 index 4c9fc5d..0cdb800 100644
 *** a/doc/src/sgml/ddl.sgml
 --- b/doc/src/sgml/ddl.sgml
 *** VALUES ('New York', NULL, NULL, 'NY');
 *** 2332,2338 
       para
        Bulk loads and deletes can be accomplished by adding or removing
        partitions, if that requirement is planned into the partitioning 
 design.
 !       commandALTER TABLE/ is far faster than a bulk operation.
        It also entirely avoids the commandVACUUM/command
        overhead caused by a bulk commandDELETE/.
       /para
 --- 2332,2338 
       para
        Bulk loads and deletes can be accomplished by adding or removing
        partitions, if that requirement is planned into the partitioning 
 design.
 !       commandDROP TABLE/ is far faster than a bulk operation.
        It also entirely avoids the commandVACUUM/command
        overhead caused by a bulk commandDELETE/.
       /para


That looks weird. I'm sure that *used* to say DROP TABLE.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  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] SSI patch renumbered existing 2PC resource managers??

2011-06-14 Thread Bruce Momjian
Bruce Momjian wrote:
  This argument seems a tad peculiar, since the *entire* *point* of
  pg_upgrade is to push physical files from one installation into another
  even though compatibility isn't guaranteed.  It is the program's duty to
  understand enough to know whether it can transport the cluster's state
  safely.  Not to arbitrarily discard state because it might possibly not
  be transportable.
 
 Well, pg_upgrade succeeds because it does as little as necessary to do
 the migration, relying on pg_dump to do much of the migration work at
 the catalog level.  pg_upgrade tries to be involved as little as
 possible with the Postgres code so it doesn't have to be changed
 regularly between major versions.
 
 The prepared transaction case seems ugly enough that we don't want
 pg_upgrade to have to check every major release if anything changed
 about the data stored in prepared transactions.  This is the same reason
 pg_upgrade doesn't transfer WAL files from the old cluster, just pg_clog
 files (which rarely changes its format).

I have applied the attached pg_upgrade patch to head and 9.1 to fail if
prepared transactions are in the old or new cluster.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index fdec6e3..376d25a
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** static void check_old_cluster_has_new_cl
*** 16,21 
--- 16,22 
  static void check_locale_and_encoding(ControlData *oldctrl,
  		  ControlData *newctrl);
  static void check_is_super_user(ClusterInfo *cluster);
+ static void check_for_prepared_transactions(ClusterInfo *cluster);
  static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
  static void check_for_reg_data_type_usage(ClusterInfo *cluster);
  
*** check_old_cluster(bool live_check,
*** 65,70 
--- 66,72 
  	 * Check for various failure cases
  	 */
  	check_is_super_user(old_cluster);
+ 	check_for_prepared_transactions(old_cluster);
  	check_for_reg_data_type_usage(old_cluster);
  	check_for_isn_and_int8_passing_mismatch(old_cluster);
  
*** check_new_cluster(void)
*** 117,122 
--- 119,125 
  	get_db_and_rel_infos(new_cluster);
  
  	check_new_cluster_is_empty();
+ 	check_for_prepared_transactions(new_cluster);
  	check_old_cluster_has_new_cluster_dbs();
  
  	check_loadable_libraries();
*** check_is_super_user(ClusterInfo *cluster
*** 507,512 
--- 510,545 
  
  
  /*
+  *	check_for_prepared_transactions()
+  *
+  *	Make sure there are no prepared transactions because the storage format
+  *	might have changed.
+  */
+ static void
+ check_for_prepared_transactions(ClusterInfo *cluster)
+ {
+ 	PGresult   *res;
+ 	PGconn	   *conn = connectToServer(cluster, template1);
+ 
+ 	prep_status(Checking for prepared transactions);
+ 
+ 	res = executeQueryOrDie(conn,
+ 			SELECT * 
+ 			FROM pg_catalog.pg_prepared_xact());
+ 
+ 	if (PQntuples(res) != 0)
+ 		pg_log(PG_FATAL, The %s cluster contains prepared transactions\n,
+ 			   CLUSTER_NAME(cluster));
+ 
+ 	PQclear(res);
+ 
+ 	PQfinish(conn);
+ 
+ 	check_ok();
+ }
+ 
+ 
+ /*
   *	check_for_isn_and_int8_passing_mismatch()
   *
   *	contrib/isn relies on data type int8, and in 8.4 int8 can now be passed

-- 
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] use less space in xl_xact_commit patch

2011-06-14 Thread Robert Haas
On Tue, Jun 14, 2011 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote:
 We don't need to be in a hurry here. As the reviewer I'm happy to give
 Leonardo some time, obviously no more than the end of the commit fest.

Well, we certainly have the option to review and commit the patch any
time up until feature freeze.  However, I don't want the CommitFest
application to be full of entries for patches that are not actually
being worked on, because it makes it hard for reviewers to figure out
which patches in a state where they can be usefully looked at.  AIUI,
this one is currently not, because it was reviewed three weeks ago and
hasn't been updated.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Cédric Villemain
2011/6/14 Robert Haas robertmh...@gmail.com:
 On Tue, Jun 14, 2011 at 12:06 PM, Cédric Villemain
 cedric.villemain.deb...@gmail.com wrote:
 1. ANALYZE happens far too infrequently to believe that any data taken
 at ANALYZE time will still be relevant at execution time.

 ANALYZE happens when people execute it, else it is auto-analyze and I
 am not providing auto-analyze-oscache.
 ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The
 frequency is not important here, I believe.

 Well, I'm not saying you have to have all the answers to post a WIP
 patch, certainly.  But in terms of getting something committable, it
 seems like we need to have at least an outline of what the long-term
 plan is.  If ANALYZE OSCACHE is an infrequent operation, then the data
 isn't going to be a reliable guide to what will happen at execution
 time...

Ok.


 2. Using data gathered by ANALYZE will make plans less stable, and our
 users complain not infrequently about the plan instability we already
 have, therefore we should not add more.

 ...and if it is a frequent operation then it's going to result in
 unstable plans (and maybe pg_class bloat).  There's a fundamental
 tension here that I don't think you can just wave your hands at.

I don't want to hide that point, which is just correct.
The idea is not to have something (which need to be) updated too much
but it needs to be taken into account.


 I was trying to split the patch size by group of features to reduce
 its size. The work is in progress.

 Totally reasonable, but I can't see committing any of it without some
 evidence that there's light at the end of the tunnel.  No performance
 tests *whatsoever* have been done.  We can debate the exact amount of
 evidence that should be required to prove that something is useful
 from a performance perspective, but we at least need some.  I'm
 beating on this point because I believe that the whole idea of trying
 to feed this information back into the planner is going to turn out to
 be something that we don't want to do.  I think it's going to turn out
 to have downsides that are far larger than the upsides.

it is possible, yes.
I try to do changes in a way that if the reloscache values is the one
by default then the planner keep the same behavior than in the past.

 I am
 completely willing to be be proven wrong, but right now I think this
 will make things worse and you think it will make things better and I
 don't see any way to bridge that gap without doing some measurements.

correct.


 For example, if you run this patch on a system and subject that system
 to a relatively even workload, how much do the numbers bounce around
 between runs?  What if you vary the workload, so that you blast it
 with OLTP traffic at some times and then run reporting queries at
 other times?  Or different tables become hot at different times?

This is all true, this is *already* true.
Like the thread about random_page_cost vs index_page_cost where the
good option is to change the parameters at certain moment in the day
(IIRC the use case).

I mean that I agree that those benchs need to be done, hopefully I can
fix some usecases, while not breaking others too much or not at all,
or ...


 Once you've written code to make the planner do something with the
 caching % values, then you can start to explore other questions.  Can
 you generate plan instability, especially on complex queries, which
 are more prone to change quickly based on small changes in the cost
 estimates?  Can you demonstrate a workload where bad performance is
 inevitable with the current code, but with your code, the system

My next step is cost estimation changes. I have already some very
small usecases where the minimum changes I did so far are interesting
but it is not enought to come with that as evidences.

 becomes self-tuning and ends up with good performance?  What happens
 if you have a large cold table with a small hot end where all activity
 is concentrated?

We are at step 3 here :-) I have already some ideas to handle those
situations but not yet polished.

The current idea is to be conservative, like PostgreSQL used to be, for example:

/*
 * disk and cache costs
 * this assumes an agnostic knowledge of the data repartition and query
 * usage despite large tables may have a hot part of 10% which is the 
only
 * requested part or that we select only (c)old data so the cache 
useless.
 * We keep the original strategy to not guess too much and just 
ponderate
 * the cost globaly.
 */
run_cost += baserel-pages * ( spc_seq_page_cost * (1 - 
baserel-oscache)
 + cache_page_cost   * 
baserel-oscache);



 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

-- 
Sent 

Re: [HACKERS] ITYM DROP TABLE

2011-06-14 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar jun 14 13:04:30 -0400 2011:
 Alvaro Herrera alvhe...@commandprompt.com writes:
  Done that way (9.0 and beyond).
 
 Re-reading the actual commit, I notice that there's now a grammatical
 problem: the following sentence says
 
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
 
 which was okay when it referred to ALTER TABLE, but now that there
 are two commands mentioned in the previous sentence, it doesn't match.
 Perhaps These commands also avoid the 

Yeah, fixed.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] psql describe.c cleanup

2011-06-14 Thread Alvaro Herrera
Excerpts from Josh Kupershmidt's message of sáb may 07 16:40:35 -0300 2011:

 And while I'm griping about describe.c, is it just me or is the source
 code indentation in that file totally screwy? I'm using emacs and I've
 loaded the snippet for pgsql-c-mode from
 ./src/tools/editors/emacs.samples into my ~/.emacs, but the
 indentation of multiline strings still looks inconsistent. See
 attached screenshot, e.g. the start of line 80 has four tabs and one
 space, while line 79 has six tabs and two spaces?

pgindent moves strings back to the left when it thinks they fit within
80 columns.  Yes, that seems pretty screwy.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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


[HACKERS] perltidy

2011-06-14 Thread Peter Eisentraut
How much do we care about applying perltidy, as described in
src/tools/msvc/README, everywhere?  I just ran it across the entire
tree, using

perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm

and it generated 6531 lines of (unified) diff, of which 357 are in
src/tools/msvc/ itself.  So clearly it's not being applied very
consistently.

Given how easily this appears to work and how we're sneakily expanding
the use of Perl, I think we ought to add this to the standard pgindent
routine.



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

2011-06-14 Thread Bruce Momjian
Peter Eisentraut wrote:
 How much do we care about applying perltidy, as described in
 src/tools/msvc/README, everywhere?  I just ran it across the entire
 tree, using
 
 perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm
 
 and it generated 6531 lines of (unified) diff, of which 357 are in
 src/tools/msvc/ itself.  So clearly it's not being applied very
 consistently.
 
 Given how easily this appears to work and how we're sneakily expanding
 the use of Perl, I think we ought to add this to the standard pgindent
 routine.

Yes, I would support that.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

2011-06-14 Thread Magnus Hagander
On Tue, Jun 14, 2011 at 21:39, Bruce Momjian br...@momjian.us wrote:
 Peter Eisentraut wrote:
 How much do we care about applying perltidy, as described in
 src/tools/msvc/README, everywhere?  I just ran it across the entire
 tree, using

 perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm

 and it generated 6531 lines of (unified) diff, of which 357 are in
 src/tools/msvc/ itself.  So clearly it's not being applied very
 consistently.

 Given how easily this appears to work and how we're sneakily expanding
 the use of Perl, I think we ought to add this to the standard pgindent
 routine.

 Yes, I would support that.

I think I suggested that before at some point, but can't find the
reference. But that means, +1.


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

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


Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)

2011-06-14 Thread Jeff Janes
On Mon, Jun 13, 2011 at 9:09 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 I noticed that pgbench's doCustom (the function highest in the profile
 posted) returns doing nothing if the connection is supposed to be
 sleeping; seems an open door for busy waiting.  I didn't check the
 rest of the code to see if there's something avoiding that condition.

Yes, there is a select in threadRun that avoids that.  Also, I don't
think anyone would but in a sleep in this particular type of pgbench
run.

 I
 also noticed that it seems to be very liberal about calling
 INSTR_TIME_SET_CURRENT in the same function which perhaps could be
 optimizing by calling it a single time at entry and reusing the value,
 but I guess that would show up in the profile as a kernel call so it's
 maybe not a problem.

I think that only gets called when you specifically asked for
latencies or for logging, or when making new connection (which should
be rare)

Cheers,

Jeff

-- 
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] Detailed documentation for external calls (threading, shared resources etc)

2011-06-14 Thread Dimitri Fontaine
Seref Arikan serefari...@kurumsalteknoloji.com writes:
 This is actually a request for documentation guidance. I intend to
 develop an extension to postgresql. Basically I'd like to place calls
 to network using ZeroMQ, and I need to have detailed information about

You didn't tell us about what you want to achieve, only how.  It could
be that your problem could be solved with a custom PGQ consumer, that
leaves well outside the database server.  PGQ is the generic queue layer
on which the replication system Londiste is built.

  http://wiki.postgresql.org/wiki/Skytools
  http://wiki.postgresql.org/wiki/PGQ_Tutorial
  http://wiki.postgresql.org/wiki/Londiste_Tutorial

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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


[HACKERS] Polecat quit unexpectdly

2011-06-14 Thread Robert Creager
Stack trace, nothing else. Now that the horse has left the barn, I changed to keep error builds...Process: postgres [17457]Path:  /Volumes/High Usage/usr/local/src/build-farm-4.4/builds/HEAD/pgsql.6849/src/test/regress/tmp_check/install/usr/local/src/build-farm-4.4/builds/HEAD/inst/bin/postgresIdentifier:   postgresVersion: ??? (???)Code Type:X86-64 (Native)Parent Process: postgres [17136]Date/Time:2011-06-14 05:00:27.466 -0600OS Version:   Mac OS X 10.6.7 (10J869)Report Version: 6Interval Since Last Report: 658000 secCrashes Since Last Report:  1Per-App Crashes Since Last Report:  1Anonymous UUID:   77053C10-A2B5-4078-A796-5862E233A1ACException Type: EXC_CRASH (SIGABRT)Exception Codes: 0x, 0xCrashed Thread: 0 Dispatch queue: com.apple.main-threadApplication Specific Information:abort() calledThread 0 Crashed: Dispatch queue: com.apple.main-thread0  libSystem.B.dylib   	0x7fff821815d6 __kill + 101  libSystem.B.dylib   	0x7fff82221cd6 abort + 832  postgres   	0x0001002fcd52 0x1 + 31327543  postgres   	0x00010005cafa multixact_twophase_postcommit + 74 (multixact.c:1367)4  postgres   	0x00010005deab ProcessRecords + 91 (twophase.c:1407)5  postgres   	0x00010005f78a FinishPreparedTransaction + 1610 (twophase.c:1368)6  postgres   	0x000100236a5a PortalRunUtility + 298 (palloc.h:88)7  postgres   	0x0001002381c5 PortalRunMulti + 597 (pquery.c:1315)8  postgres   	0x000100238b78 PortalRun + 984 (pquery.c:817)9  postgres   	0x000100234b2d exec_simple_query + 589 (postgres.c:1025)10 postgres   	0x0001002356f1 PostgresMain + 1937 (postgres.c:3926)11 postgres   	0x0001001e82ec ServerLoop + 2780 (postmaster.c:3600)12 postgres   	0x0001001e9251 PostmasterMain + 2753 (postmaster.c:1120)13 postgres   	0x00010017c305 main + 965 (main.c:199)14 postgres   	0x00010b14 start + 52Thread 0 crashed with X86 Thread State (64-bit):rax: 0x rbx: 0x0018 rcx: 0x7fff5fbfd3b8 rdx: 0xrdi: 0x4431 rsi: 0x0006 rbp: 0x7fff5fbfd3d0 rsp: 0x7fff5fbfd3b8 r8: 0x0004  r9: 0x r10: 0x7fff8217d616 r11: 0xff80002e28b0r12: 0x006a r13: 0x000100508980 r14: 0x0aba r15: 0x0001004ffb20rip: 0x7fff821815d6 rfl: 0x0202 cr2: 0x7fff70419230Binary Images:   0x1 -0x1004fbfef +postgres ??? (???) E4B56AD5-5ABD-37F5-8D5A-ADE9F24372F2 /Volumes/High Usage/usr/local/src/build-farm-4.4/builds/HEAD/pgsql.6849/src/test/regress/tmp_check/install/usr/local/src/build-farm-4.4/builds/HEAD/inst/bin/postgres 0x7fff5fc0 -   0x7fff5fc3bdef dyld 132.1 (???) 486E6C61-1197-CC7C-2197-82CE505102D7 /usr/lib/dyld 0x7fff80b92000 -   0x7fff80cb3fe7 libcrypto.0.9.8.dylib 0.9.8 (compatibility 0.9.8) 48AEAFE1-21F4-B3C8-4199-35AD5E8D0613 /usr/lib/libcrypto.0.9.8.dylib 0x7fff80cb4000 -   0x7fff80cc5ff7 libz.1.dylib 1.2.3 (compatibility 1.0.0) 97019C74-161A-3488-41EC-A6CA8738418C /usr/lib/libz.1.dylib 0x7fff80df1000 -   0x7fff80f08fef libxml2.2.dylib 10.3.0 (compatibility 10.0.0) 1B27AFDD-DF87-2009-170E-C129E1572E8B /usr/lib/libxml2.2.dylib 0x7fff82132000 -   0x7fff822f3fff libSystem.B.dylib 125.2.10 (compatibility 1.0.0) 9BAEB2F2-B485-6349-E1AB-637FE12EE770 /usr/lib/libSystem.B.dylib 0x7fff8330 -   0x7fff834befff libicucore.A.dylib 40.0.0 (compatibility 1.0.0) 2C6ECACF-CD56-1714-6F63-CB6F5EE7A1E2 /usr/lib/libicucore.A.dylib 0x7fff835a7000 -   0x7fff835b9fe7 libsasl2.2.dylib 3.15.0 (compatibility 3.0.0) 76B83C8D-8EFE-4467-0F75-275648AFED97 /usr/lib/libsasl2.2.dylib 0x7fff83c6c000 -   0x7fff83c70ff7 libmathCommon.A.dylib 315.0.0 (compatibility 1.0.0) 95718673-FEEE-B6ED-B127-BCDBDB60D4E5 /usr/lib/system/libmathCommon.A.dylib 0x7fff843c3000 -   0x7fff843c4ff7 com.apple.TrustEvaluationAgent 1.1 (1) 5952A9FA-BC2B-16EF-91A7-43902A5C07B6 /System/Library/PrivateFrameworks/TrustEvaluationAgent.framework/Versions/A/TrustEvaluationAgent 0x7fff85c2e000 -   0x7fff85cabfef libstdc++.6.dylib 7.9.0 (compatibility 7.0.0) 35ECA411-2C08-FD7D-11B1-1B7A04921A5C /usr/lib/libstdc++.6.dylib 0x7fff85d27000 -   0x7fff85d48fff libresolv.9.dylib 41.0.0 (compatibility 1.0.0) 9F322F47-0584-CB7D-5B73-9EBD670851CD /usr/lib/libresolv.9.dylib 0x7fff85e5c000 -   0x7fff85e99fff com.apple.LDAPFramework 2.0 (120.1) 54A6769E-D7E2-DBE2-EA61-87B9EA355DA4 /System/Library/Frameworks/LDAP.framework/Versions/A/LDAP 0x7fff87cdd000 -   0x7fff87d17fff libssl.0.9.8.dylib 0.9.8 (compatibility 0.9.8) C7153747-50E3-32DA-426F-CC4C505D1D6C /usr/lib/libssl.0.9.8.dylib 0x7fe0 -   0x7fe01fff libSystem.B.dylib ??? (???) 9BAEB2F2-B485-6349-E1AB-637FE12EE770 /usr/lib/libSystem.B.dylibModel: MacBookPro5,1, BootROM MBP51.007E.B05, 2 processors, Intel Core 2 Duo, 2.53 GHz, 6 GB, SMC 1.33f8Graphics: NVIDIA GeForce 

Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)

2011-06-14 Thread Stefan Kaltenbrunner
On 06/14/2011 02:27 AM, Jeff Janes wrote:
 On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner
 ste...@kaltenbrunner.cc wrote:
 ...


 so it seems that sysbench is actually significantly less overhead than
 pgbench and the lower throughput at the higher conncurency seems to be
 cause by sysbench being able to stress the backend even more than
 pgbench can.
 
 Hi Stefan,
 
 pgbench sends each query (per connection) and waits for the reply
 before sending another.
 
 Do we know whether sysbench does that, or if it just stuffs the
 kernel's IPC buffer full of queries without synchronously waiting for
 individual replies?
 
 I can't get sysbench to make for me, or I'd strace in single client
 mode and see what kind of messages are going back and forth.

yeah sysbench compiled from a release tarball needs some
autoconf/makefile hackery to get running on a modern system - but I can
provide you with the data you are interested in if you tell me exactly
what you are looking for...


Stefan

-- 
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] Polecat quit unexpectdly

2011-06-14 Thread Kevin Grittner
Robert Creager robert.crea...@oracle.com wrote:
 
 Stack trace, nothing else.
 
 3   postgres  0x00010005cafa 
 multixact_twophase_postcommit + 74 (multixact.c:1367)
 4   postgres  0x00010005deab
 ProcessRecords + 91 (twophase.c:1407)
 5   postgres  0x00010005f78a 
 FinishPreparedTransaction + 1610 (twophase.c:1368)
 
If this was a checkout from more than about 7 hours ago and less
than about 10 hours ago, please get a fresh copy of the source and
try again.
 
-Kevin

-- 
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] procpid?

2011-06-14 Thread Greg Smith

On 06/14/2011 02:20 PM, Kevin Grittner wrote:
Just on our Wiki pages we have some queries available for copy/paste 
which would need multiple

versions while both column names were in supported versions of the
software:

http://wiki.postgresql.org/wiki/Lock_dependency_information
http://wiki.postgresql.org/wiki/Lock_Monitoring
http://wiki.postgresql.org/wiki/Backend_killer_function
   


...and most of these would actually be simplified if they could just 
JOIN on pid instead of needing this common idiom:


   join pg_catalog.pg_stat_activity ka
   on kl.pid = ka.procpid

Yes, there are a lot of these floating around.  I'd bet that in an hour 
of research I could find 95% of them though, and make sure they were all 
updated in advance of the release.  (I already did most of this search 
as part of stealing every good idea I could find in this area for my book)



I think that's consistent with the save up our breaking changes to do them all 
at
once approach.
   


I don't actually buy into this whole idea at all.  We already have this 
big wall at 8.3 because changes made in that release are too big for 
people on the earlier side to upgrade past.  I'd rather see a series of 
smaller changes in each release, even if they are disruptive, so that no 
one version turns into a frustrating hurdle seen as impossible to 
clear.  This adjustment is a perfect candidate for putting into 9.2 to 
me, because I'd rather reduce max(breakage) across releases than 
intentionally aim at increasing it but bundling them into larger clumps.


For me, the litmus test is whether the change provides enough 
improvement that it outweighs the disruption when the user runs into 
it.  This is why I suggested a specific, useful, and commonly requested 
(to me at least) change to pg_stat_activity go along with this.  If 
people discover their existing pg_stat_activity tools break, presumably 
they're going to look at the view again to see what changed.  When they 
do that, I don't want the reaction to be why was this random change 
made?  I want it to be look, there are useful new fields in here; let 
me see if I can use them too here.  That's how you make people tolerate 
disruption in upgrades.  If they see a clear improvement in the same 
spot when forced to fix around it, the experience is much more pleasant 
if they get something new out of it too.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us



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


Re: [HACKERS] procpid?

2011-06-14 Thread Alvaro Herrera
Excerpts from Bruce Momjian's message of mar jun 14 12:59:15 -0400 2011:

 Well, someone doing SELECT *, which is probably 90% of the users, are
 going to be pretty confused by duplicate columns, asking, What is the
 difference?  For those people this would make things worse than they
 are now.
 
 I would say 90% of users are doing SELECT *, and 10% are joining to
 other tables or displaying specific columns.  We want to help that 10%
 without making that 90% confused.

I think if you had column synonyms, you would get only a single one when
doing select *.  The other name would still be accepted in a query
that explicitely asked for it.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Alvaro Herrera
Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011:

 Attached are updated patches without the plugin itself. I've also
 added the cache_page_cost GUC, this one is not per tablespace, like
 others page_cost.
 
 There are 6 patches:
 
 0001-Add-reloscache-column-to-pg_class.patch

Hmm, do you really need this to be a new column?  Would it work to have
it be a reloption?

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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


[HACKERS] possible connection leak in dblink?

2011-06-14 Thread Peter Eisentraut
With gcc 4.6, I get this warning:

dblink.c: In function ‘dblink_send_query’:
dblink.c:620:7: warning: variable ‘freeconn’ set but not used 
[-Wunused-but-set-variable]

I don't know much about the internals of dblink, but judging from the
surrounding code, I guess that this fix is necessary:

diff --git i/contrib/dblink/dblink.c w/contrib/dblink/dblink.c
index 19b98fb..e014c1a 100644
--- i/contrib/dblink/dblink.c
+++ w/contrib/dblink/dblink.c
@@ -634,6 +634,10 @@ dblink_send_query(PG_FUNCTION_ARGS)
if (retval != 1)
elog(NOTICE, %s, PQerrorMessage(conn));
 
+   /* if needed, close the connection to the database and cleanup */
+   if (freeconn)
+   PQfinish(conn);
+
PG_RETURN_INT32(retval);
 }
 
Otherwise the connection might not get freed.  Could someone verify
that?


-- 
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] One-Shot Plans

2011-06-14 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes:
 Currently, the planner and executor are mostly independent of each
 other: the planner doesn't really know when the plan will be executed,
 and the executor doesn't know how recently the plan was made.

 We can work out the various paths through the traffic cop to see when
 a plan will be a one-shot - planned and then executed immediately,
 then discarded.

I have already got plans for a significantly more sophisticated approach
to this.

 In those cases we can take advantage of better optimisations. Most
 interestingly, we can evaluate stable functions at plan time, to allow
 us to handle partitioning and partial indexes better.

I don't believe that's correct in detail.

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] Polecat quit unexpectdly

2011-06-14 Thread Kevin Grittner
Robert Creager robert.crea...@oracle.com wrote:
 
 You believe it was related to the flurry of errors that popped up
 then.
 
I haven't looked at all the error in the flurry.  I think your
particular report is consistent with being caused by this commit:
 
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=b81831acbc671445061ed41a55fb1cc21d8e2979
 
and solved by this one:
 
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=85ea93384ae21ff59f5e5b292884a86f9c10b852
 
I suspect that other builds based on checkouts in this window of
time would probably show similar errors.
 
-Kevin

-- 
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] procpid?

2011-06-14 Thread Peter Eisentraut
On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote:
 There are real problems with the idea of having one release where we
 break everything that we want to break - mostly from a process
 standpoint.  We aren't always good at being organized and disciplined,
 and coming up with a multi-year plan to break everything all at once
 in 2014 for release in 2015 may be difficult, because it requires a
 consensus on release management to hold together for years, and
 sometimes we can't even manage days.

I have had this fantasy of a break-everything release for a long time as
well, but frankly, experience from other projects such as Python 3, Perl
6, KDE 4, Samba 4, add-yours-here, indicates that such things might not
work out so well.

OK, some of those were rewrites as well as interface changes, but the
effect visible to the end user is mostly the same.



-- 
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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Cédric Villemain
2011/6/14 Alvaro Herrera alvhe...@commandprompt.com:
 Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011:

 Attached are updated patches without the plugin itself. I've also
 added the cache_page_cost GUC, this one is not per tablespace, like
 others page_cost.

 There are 6 patches:

 0001-Add-reloscache-column-to-pg_class.patch

 Hmm, do you really need this to be a new column?  Would it work to have
 it be a reloption?

If we can have ALTER TABLE running on heavy workload, why not.
I am bit scared by the effect of such reloption, it focus on HINT
oriented strategy when I would like to allow a dynamic strategy from
the server. This work is not done and may not work, so a reloption is
good at least as a backup  (and is more in the idea suggested by Tom
and others)


 --
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et 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] creating CHECK constraints as NOT VALID

2011-06-14 Thread Alvaro Herrera
Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.
 
 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

Here's the complete patch.


*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***
*** 1898,1904 
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
!   entryHas the constraint been validated? Can only be false for foreign 
keys/entry
   /row
  
   row
--- 1898,1904 
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
!   entryHas the constraint been validated? Can only be false for foreign 
keys and CHECK constraints/entry
   /row
  
   row
*** a/doc/src/sgml/ref/alter_domain.sgml
--- b/doc/src/sgml/ref/alter_domain.sgml
***
*** 28,37  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  { SET | DROP } NOT NULL
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  DROP CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  OWNER TO replaceable class=PARAMETERnew_owner/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
--- 28,39 
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  { SET | DROP } NOT NULL
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable [ NOT 
VALID ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  DROP CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
+ VALIDATE CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable
+ ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  OWNER TO replaceable class=PARAMETERnew_owner/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
***
*** 70,82  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
 /varlistentry
  
 varlistentry
! termADD replaceable 
class=PARAMETERdomain_constraint/replaceable/term
  listitem
   para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
!   This will only succeed if all columns using the domain satisfy the
!   new constraint.
   /para
  /listitem
 /varlistentry
--- 72,88 
 /varlistentry
  
 varlistentry
! termADD replaceable class=PARAMETERdomain_constraint/replaceable 
[ NOT VALID ]/term
  listitem
   para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
!   If NOT VALID is not specified,
! the command will only succeed if all columns using the
! domain satisfy the new constraint.
! The constraint is going to be enforced on new data inserted into 
columns
! using the domain in all cases, regardless of literalNOT VALID/.
! literalNOT VALID/ is only accepted for literalCHECK/ 
constraints.
   /para
  /listitem
 /varlistentry
***
*** 91,96  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 97,113 
 /varlistentry
  
 varlistentry
+ termVALIDATE CONSTRAINT/term
+ listitem
+  para
+   This form validates a constraint previously added, that is, verify that
+   all data in columns using the domain satisfy the specified constraint.
+  /para
+ /listitem
+/varlistentry
+ 
+ 
+varlistentry
  termOWNER/term
  listitem
   para
***
*** 156,161  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 173,188 
   /varlistentry
  
   varlistentry
+   termreplaceable class=PARAMETERNOT VALID/replaceable/term
+   listitem
+para
+ Do not verify existing column data for constraint validity.
+/para
+   /listitem
+  /varlistentry
+ 
+ 
+  varlistentry
termliteralCASCADE/literal/term
listitem
 para
***
*** 251,257  ALTER DOMAIN zipcode SET SCHEMA customers;
para
 

Re: [HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Alvaro Herrera
Excerpts from Cédric Villemain's message of mar jun 14 17:10:20 -0400 2011:

 If we can have ALTER TABLE running on heavy workload, why not.
 I am bit scared by the effect of such reloption, it focus on HINT
 oriented strategy when I would like to allow a dynamic strategy from
 the server. This work is not done and may not work, so a reloption is
 good at least as a backup  (and is more in the idea suggested by Tom
 and others)

Hmm, sounds like yet another use case for pg_class_nt.  Why do these
keep popping up?

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] creating CHECK constraints as NOT VALID

2011-06-14 Thread Jaime Casanova
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.

 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

 Here's the complete patch.


this doesn't apply

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

-- 
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] Why polecat and colugos are failing to build back branches

2011-06-14 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 06/13/2011 08:05 PM, Tom Lane wrote:
 I looked into $SUBJECT.  There appear to be two distinct issues:
 ...

 I think we can be a bit more liberal about build patches than things 
 that can affect the runtime behaviour.

 So +1 for fixing both of these.

I've committed patches that fix these issues on my own OS X machine,
though it remains to be seen whether polecat and colugos will like
them.  It turns out that whatever setup Robert has got with
'/Volumes/High Usage/' is really *not* fully exercising the system
as far as space-containing paths go, because I found bugs in all
active branches when I tried to do builds and installs underneath
'/Users/tgl/foo bar/'.  Is it worth setting up a buildfarm critter
to exercise the case on a long-term basis?  If we don't, I think
we can expect that it'll break regularly.

(I wouldn't care to bet that the MSVC case works yet, either.)

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] procpid?

2011-06-14 Thread Bruce Momjian
Peter Eisentraut wrote:
 On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote:
  There are real problems with the idea of having one release where we
  break everything that we want to break - mostly from a process
  standpoint.  We aren't always good at being organized and disciplined,
  and coming up with a multi-year plan to break everything all at once
  in 2014 for release in 2015 may be difficult, because it requires a
  consensus on release management to hold together for years, and
  sometimes we can't even manage days.
 
 I have had this fantasy of a break-everything release for a long time as
 well, but frankly, experience from other projects such as Python 3, Perl
 6, KDE 4, Samba 4, add-yours-here, indicates that such things might not
 work out so well.
 
 OK, some of those were rewrites as well as interface changes, but the
 effect visible to the end user is mostly the same.

Funny you mentioned Perl 6 because I just blogged about that:

http://momjian.us/main/blogs/pgblog/2011.html#June_14_2011

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] procpid?

2011-06-14 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote:
 There are real problems with the idea of having one release where we
 break everything that we want to break - mostly from a process
 standpoint.  We aren't always good at being organized and disciplined,
 and coming up with a multi-year plan to break everything all at once
 in 2014 for release in 2015 may be difficult, because it requires a
 consensus on release management to hold together for years, and
 sometimes we can't even manage days.

 I have had this fantasy of a break-everything release for a long time as
 well, but frankly, experience from other projects such as Python 3, Perl
 6, KDE 4, Samba 4, add-yours-here, indicates that such things might not
 work out so well.

 OK, some of those were rewrites as well as interface changes, but the
 effect visible to the end user is mostly the same.

Good point.  I think the case that has actually been discussed is the
idea of saving up binary-compatibility breaks (on-disk format changes).
That seems sensible.  It doesn't create a bigger problem for users,
since a dump/reload is a dump/reload no matter how many individual
format changes happened underneath.  But we should be wary of applying
that approach to application-visible incompatibilities.

As far as Greg's proposal is concerned, I don't see how a proposed
addition of two columns would justify renaming an existing column.
Additions should not break any sanely-implemented application, but
renamings certainly will.

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] Why polecat and colugos are failing to build back branches

2011-06-14 Thread Andrew Dunstan



On 06/14/2011 05:45 PM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net  writes:

On 06/13/2011 08:05 PM, Tom Lane wrote:

I looked into $SUBJECT.  There appear to be two distinct issues:
...

I think we can be a bit more liberal about build patches than things
that can affect the runtime behaviour.
So +1 for fixing both of these.

I've committed patches that fix these issues on my own OS X machine,
though it remains to be seen whether polecat and colugos will like
them.  It turns out that whatever setup Robert has got with
'/Volumes/High Usage/' is really *not* fully exercising the system
as far as space-containing paths go, because I found bugs in all
active branches when I tried to do builds and installs underneath
'/Users/tgl/foo bar/'.  Is it worth setting up a buildfarm critter
to exercise the case on a long-term basis?  If we don't, I think
we can expect that it'll break regularly.

(I wouldn't care to bet that the MSVC case works yet, either.)




Well, OSX is just using our usual *nix paraphernalia, so if it's broken 
won't all such platforms probably be broken too? I'd actually bet a 
modest amount MSVC is less broken because it uses perl modules like 
File::Copy to do most of its work and so will be less prone to shell 
parsing breakage.


But yes, we should check regularly.

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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011:
 0001-Add-reloscache-column-to-pg_class.patch

 Hmm, do you really need this to be a new column?  Would it work to have
 it be a reloption?

If it's to be updated in the same way as ANALYZE updates reltuples and
relpages (ie, an in-place non-transactional update), I think it'll have
to be a real column.

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] Why polecat and colugos are failing to build back branches

2011-06-14 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 06/14/2011 05:45 PM, Tom Lane wrote:
 I've committed patches that fix these issues on my own OS X machine,

 Well, OSX is just using our usual *nix paraphernalia, so if it's broken 
 won't all such platforms probably be broken too?

Yes, certainly.  The reason I specified OS X in particular is that I
only tested the darwin branch of Makefile.shlib.  The -install_name
switch that was the problem there is specific to OS X, but I wouldn't
be surprised if some of the other branches have their own platform-
specific issues.

 I'd actually bet a modest amount MSVC is less broken

Very possibly, but unless it's being tested it's no sure bet.

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] [WIP] cache estimates, cache access cost

2011-06-14 Thread Greg Smith

On 06/14/2011 01:16 PM, Robert Haas wrote:

But there's no reason that code (which may or may not eventually prove
useful) has to be incorporated into the main tree.  We don't commit
code so people can go benchmark it; we ask for the benchmarking to be
done first, and then if the results are favorable, we commit the code.
   


Who said anything about this being a commit candidate?  The WIP in the 
subject says it's not intended to be.  The community asks people to 
submit design ideas early so that ideas around them can be explored 
publicly.  One of the things that needs to be explored, and that could 
use some community feedback, is exactly how this should be benchmarked 
in the first place.  This topic--planning based on cached 
percentage--keeps coming up, but hasn't gone very far as an abstract 
discussion.  Having a patch to test lets it turn to a concrete one.


Note that I already listed myself as the reviewer  here, so it's not 
even like this is asking explicitly for a community volunteer to help.  
Would you like us to research this privately and then dump a giant patch 
that is commit candidate quality on everyone six months from now, 
without anyone else getting input to the process, or would you like the 
work to happen here?  I recommended Cédric not ever bother soliciting 
ideas early, because I didn't want to get into this sort of debate.  I 
avoid sending anything here unless I already have a strong idea about 
the solution, because it's hard to keep criticism at bay even with 
that.  He was more optimistic about working within the community 
contribution guidelines and decided to send this over early instead.  If 
you feel this is too rough to even discuss, I'll mark it returned with 
feedback and we'll go develop this ourselves.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us



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


Re: [HACKERS] [WIP] cache estimates, cache access cost

2011-06-14 Thread Bruce Momjian
Greg Smith wrote:
 On 06/14/2011 01:16 PM, Robert Haas wrote:
  But there's no reason that code (which may or may not eventually prove
  useful) has to be incorporated into the main tree.  We don't commit
  code so people can go benchmark it; we ask for the benchmarking to be
  done first, and then if the results are favorable, we commit the code.
 
 
 Who said anything about this being a commit candidate?  The WIP in the 
 subject says it's not intended to be.  The community asks people to 
 submit design ideas early so that ideas around them can be explored 
 publicly.  One of the things that needs to be explored, and that could 
 use some community feedback, is exactly how this should be benchmarked 
 in the first place.  This topic--planning based on cached 
 percentage--keeps coming up, but hasn't gone very far as an abstract 
 discussion.  Having a patch to test lets it turn to a concrete one.
 
 Note that I already listed myself as the reviewer  here, so it's not 
 even like this is asking explicitly for a community volunteer to help.  
 Would you like us to research this privately and then dump a giant patch 
 that is commit candidate quality on everyone six months from now, 
 without anyone else getting input to the process, or would you like the 
 work to happen here?  I recommended C?dric not ever bother soliciting 
 ideas early, because I didn't want to get into this sort of debate.  I 
 avoid sending anything here unless I already have a strong idea about 
 the solution, because it's hard to keep criticism at bay even with 
 that.  He was more optimistic about working within the community 
 contribution guidelines and decided to send this over early instead.  If 
 you feel this is too rough to even discuss, I'll mark it returned with 
 feedback and we'll go develop this ourselves.

I would like to see us continue researching in this direction.  I think
perhaps the background writer would be ideal for collecting this
information because it scans the buffer cache already, and frequently.
(Yes, I know it can't access databases.)

I think random_page_cost is a dead-end --- it will never be possible for
it to produce the right value for us.  Its value is tied up in caching,
e.g. the default 4 is not the right value for a physical drive (it
should be much higher), but kernel and shared buffer caching require it
to be a hybrid number that isn't really realistic.  And once we have
caching in that number, it is not going to be even caching for all
tables, obviously.  Hence, there is no way for random_page_cost to be
improved and we have to start thinking about alternatives.

Basically, random_page_cost is a terrible setting and we have to admit
that and move forward.  I realize the concerns about unstable plans, and
we might need to give users the option of stable plans with a fixed
random_page_cost, but at this point we don't even have enough data to
know we need that.  What we do know is that random_page_cost is
inadequate.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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


  1   2   >