Re: [HACKERS] Help with User-defined function in PostgreSQL with Visual C++

[Tom Lane]

Itagaki Takahiro  writes:
> On Tue, Sep 28, 2010 at 12:12 PM, Robert Haas  wrote:
>> Oh - I didn't realize this meant marking lots of things in contrib
>> that didn't otherwise need to be marked.  Why do other people need
>> this if we don't?

> As I mentioned, we don't need the marks in our build environment at all.

In that case, anybody who does need it should fix their build
environment.

I grow really weary of the idea that we should submit to arbitrary
amounts of uglification of our source code so that it will deal with
this week's Windows oddness.  The Windows folk need to be willing to
do a bit of work on their end.

regards, tom lane

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

[HACKERS] Proposal: plpgsql - "for in array" statement

[Pavel Stehule]

Hello

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]
LOOP
  ..
END LOOP

var - declared variable - theoretically we can a detect var type from
array type, but it needs a early expression an analyze (not used in
PL/pgSQL), so var should be declared before. This construct ensure
iteration over all items of array. When somebody needs a subscripts
from some dimension, then he can use a proposed function "subscripts".

so iteration over two dimensional array can be written:

DECLARE
  i integer;
  j integer;
BEGIN
  FOR i IN subscripts(myarray, 1) LOOP
FOR j IN subscripts(myarray, 2) LOOP
  RAISE NOTICE 'myarray[%,%] = %', i, j, myarray[i,j];
END LOOP;
  END LOOP;

When input array is multidimensional, then this array is flattened -
order of iteration is specified by physical store of items inside the
array. This construct iterate over all items of input array - it has a
same behave as "unnest" function.

some examples:

DECLARE
  v integer;
  a int[] := ARRAY[2,3.4,5];
BEGIN
  FOR val IN a
  LOOP
RAISE NOTICE '%', val; -- produce 2,3,4,5
  END LOOP;

  FOR val IN subscripts(a, 1)
  LOOP
RAISE NOTICE '%', val; -- produce 1,2,3,4
  END LOOP;

  FOR val IN subscripts(a,1)
  LOOP
RAISE NOTICE '%', a[val]; -- produce 2,3,4,5
  END LOOP;
END;

Comments, ideas?

Regards

Pavel Stehule

-- 
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] recovery.conf location

[Fujii Masao]

On Mon, Sep 27, 2010 at 5:02 PM, Magnus Hagander  wrote:
> On Mon, Sep 27, 2010 at 08:34, Fujii Masao  wrote:
>> On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova  
>> wrote:
>>> Maybe i'm missing something but this would be a problem if we put a
>>> trigger file and the recovery.conf gets renamed to recovery.done, no?
>>> at least that would be a problem for the standbys that still need to
>>> be standbys
>>
>> That's not problem unless more than one standbys become master at the
>> same time. Since recovery.conf is read by standby only at the start unless
>> it's triggered, already-started standbys can work even if recovery.conf is
>> renamed to recovery.done. Though it's somewhat tricky..
>
> Uh, what if the slave is restarted for one reason or another? That
> seems like it would be really fragile..

Agreed ;)

So, even if we move primary_conninfo and trigger_file to postgresql.conf,
we would need to still leave standby_mode in recovery.conf.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Using streaming replication as log archiving

[Fujii Masao]

On Mon, Sep 27, 2010 at 9:07 PM, Magnus Hagander  wrote:
> As has been previously mentioned a couple of times, it should be
> perfectly possible to use streaming replication to get around the
> limitations of archive_command/archive_timeout to do log archiving for
> PITR (being that you either keep archive_timeout high and risk data
> loss or you set it very low and generate a huge log archive without
> need).
>
> I've put together a tool to do this. The basic idea is to just stream
> down replication and write it to regular WAL files, which can then be
> used for recovery. You'll still need to use archive_command together
> with it to ensure that the backups are complete. Streaming replication
> doesn't guarantee that - in fact, regular replication will fallback to
> using whatever archive_command created when wal_keep_segments isn't
> enough.
>
> I've put up an early version of the tool at
> http://github.com/mhagander/pg_streamrecv

Great! This also might be useful for users who want something like
Oracle redo log mirroring.

> Comments and contributions are most welcome. And frankly, a good
> review is very much required before I'd trust it ;) Hopefully, I
> didn't overlook something critical :D

When I ran that, the size of the WAL file in inprogress directory
became more than 16MB. Obviously something isn't right.

When I requested immediate shutdown to the master, segmentation
fault occurred in pg_streamrecv. I guess that the return value 0
of PQgetCopyData would not be handled correctly.

After I repeated Ctrl+C and start of pg_streamrecv some times,
I encountered the following error and pg_streamrecv was never up.
Is this intentional?

In progress directory contains more than one file!

$ ls foo/inprogress/
0001000D  0001000D.save

When there is inprogress or archived WAL file, pg_streamrecv should
not execute pg_current_xlog_location because that result is not used?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Perf regression in 2.6.32 (Ubuntu 10.04 LTS)

[Mark Kirkwood]

On 28/09/10 16:59, Robert Haas wrote:

On Mon, Sep 27, 2010 at 11:37 PM, Mark Kirkwood
  wrote:
   

Greg, have you run into any other evidence suggesting a problem with 2.6.32?

Not Greg (sorry), but this might be worth a look:

http://www.spinics.net/lists/linux-ext4/msg20299.html
 

Oh, interesting.  But why wouldn't that also affect MySQL?

   


Yeah, wondered that myself - perhaps if sysbench is using myisam tables 
then there is probably no fsync activity at all for a read only 
workload. Be interesting to see if Mysql suffers a hit for sysbench 
configured to use innodb storage...

Re: [HACKERS] Perf regression in 2.6.32 (Ubuntu 10.04 LTS)

[Robert Haas]

On Mon, Sep 27, 2010 at 11:37 PM, Mark Kirkwood
 wrote:
> Greg, have you run into any other evidence suggesting a problem with 2.6.32?
>
> Not Greg (sorry), but this might be worth a look:
>
> http://www.spinics.net/lists/linux-ext4/msg20299.html

Oh, interesting.  But why wouldn't that also affect MySQL?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] security hook on table creation

[Robert Haas]

2010/9/1 KaiGai Kohei :
> This patch allows external security providers to check privileges
> to create a new relation and to inform the security labels to be
> assigned on the new one.

Review:

I took a brief look at this patch tonight and I think it's on the
wrong track.  There's no reason for the hook function to return the
list of security labels and then have the core code turn around and
apply them to the object.  If the hook function wants to label the
object, it can just as easily call SetSecurityLabel() itself.

It seems to me that there is a general pattern to the hooks that are
needed here.  For each object type for which we wish to have MAC
integration, you need the ability to get control when the object is
created and again when the object is dropped.  You might want to deny
the operation, apply labels to the newly created object, do some
logging, or whatever.  So it strikes me that you could have a hook
function with a signature like this:

typedef void (*object_access_hook_type)(ObjectType objtype, Oid oid,
int subid, ObjectAccessType op);

...where ObjectAccessType is an enum.

Then you could do something like this:

#define InvokeObjectAccessHook(objtype, oid, subid, op) \
if (object_access_hook != NULL) \
object_access_hook(objtype, oid, subid, op);

Then you can sprinkle calls to that macro in strategically chosen
places to trap create, drop, comment, security label, ... whatever the
object gets manipulated in a way that something like SE-Linux is apt
to care about.  So ObjectAccessType can have values like OAT_CREATE,
OAT_DROP, OAT_COMMENT, OAT_SECURITY_LABEL, ...

I would like to mark this patch Returned with Feedback, because I
think the above suggestions are going to amount to a complete rewrite.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Perf regression in 2.6.32 (Ubuntu 10.04 LTS)

[Mark Kirkwood]

On 28/09/10 04:28, Robert Haas wrote:

On Mon, Sep 13, 2010 at 12:05 PM, Greg Smith  wrote:
   

Domas Mituzas wrote:
 

I've been playing around today a lot with sysbench, and observed that
2.6.32 kernel supplied by Ubuntu is having perf regression with PG (which
does not affect MySQL), compared to 2.6.28 builds I have.
What I observed can be seen in a paste at
http://p.defau.lt/?8_GQV82Pz3_SDZbNOdP93Q (db12 is 2.6.28, db20 is 2.6.32 -
2.6.32-24-server).
Machines are two socket quad-opterons 2356s.
oprofile output can be seen at http://p.defau.lt/?OIR1vDFK4cze_fmBTQbV9w -
system has>20% of idle cpu, which is somewhere in the top symbol :)

   

Are you using the same filesystem setup on both setups?  And regardless,
what is that filesystem?  We know that between 2.6.28 and 2.6.32 the kernel
improved how it handles fsync requests in a good way from a reliability
perspective (to fix bugs that could cause data loss before), particularly on
ext4, so it's possible the regression you're seeing is just the expense of
handling things properly.

If you already have sysbench on there, I'd suggest comparing the two systems
by seeing how fast each can execute fsync requests:

sysbench --test=fileio --file-fsync-freq=1 --file-num=1
--file-total-size=16384 --file-test-mode=rndwr run | grep "Requests/sec"

To help distinguish whether this regression might be coming from the already
known changes in that area, or if it's instead from something that's
impacting CPU efficiency.

Also, it's easy to see a performance change of this size just from the
database files being on a different part of the disk if you didn't control
for that.  Disks are almost twice as fast at their beginning than their end
nowadays.
 

Greg, have you run into any other evidence suggesting a problem with 2.6.32?

   


Not Greg (sorry), but this might be worth a look:

http://www.spinics.net/lists/linux-ext4/msg20299.html

regards

Mark

Re: [HACKERS] Help with User-defined function in PostgreSQL with Visual C++

[Robert Haas]

On Mon, Sep 27, 2010 at 11:26 PM, Itagaki Takahiro
 wrote:
> On Tue, Sep 28, 2010 at 12:12 PM, Robert Haas  wrote:
>>> If we do so, many PGDLLEXPORT will be added:
>>>  *  17 in src/tutorial
>>>  * 507 in contrib
>>> for each exported PGFunction, _PG_init, and _PG_fini.
>>
>> Oh - I didn't realize this meant marking lots of things in contrib
>> that didn't otherwise need to be marked.  Why do other people need
>> this if we don't?
>
> As I mentioned, we don't need the marks in our build environment at all.

Why not?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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: tsearch - some memory diet

[Robert Haas]

On Tue, Sep 7, 2010 at 1:30 PM, Tom Lane  wrote:
> In the particular case here, the dictionary structures could probably
> safely use such a context type, but I'm not sure it's worth bothering
> if the long-term plan is to implement a precompiler.  There would be
> no need for this after the precompiled representation is installed,
> because that'd just be one big hunk of memory anyway.

Rather than inventing something more complex, I'm inclined to say we
should just go ahead and apply this more or less as Pavel wrote it.  I
haven't tried to reproduce Pavel's results, but I assume that they are
accurate and that's a pretty big savings for a pretty trivial amount
of code.  If it gets thrown away later when/if someone codes up a
precompiler, no harm done.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Itagaki Takahiro]

On Tue, Sep 28, 2010 at 12:12 PM, Robert Haas  wrote:
>> If we do so, many PGDLLEXPORT will be added:
>>  *  17 in src/tutorial
>>  * 507 in contrib
>> for each exported PGFunction, _PG_init, and _PG_fini.
>
> Oh - I didn't realize this meant marking lots of things in contrib
> that didn't otherwise need to be marked.  Why do other people need
> this if we don't?

As I mentioned, we don't need the marks in our build environment at all.
So, the PGDLLEXPORT marks are for users who refers our tutorials and
contrib modules as sample codes.

Personally, I learned many idioms from contrib modules, but didn't
notice the tutorial directory. I think codes in contribs are often
copied-and-pasted. So, if we add PGDLLEXPORTs to some places,
I'd like to add them to contribs, too.

-- 
Itagaki Takahiro

-- 
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] proposal: tsearch dictionary initialization hook

[Robert Haas]

On Tue, Sep 7, 2010 at 12:55 PM, Pavel Stehule  wrote:
> 2010/9/7 Teodor Sigaev :
>> Hm, what is aim of this hook? It looks like a wrapper of dictionary init
>> method.
>
> If I use a mmap for shared dictionary, then I have to prealloc and
> maybe preread dictionary - it can be done in external module. But I
> have to join preloaded dictionary to requested dictionary. This hook
> allows this relation - and it's general - I don't need any special
> support in ispell dictionary.

Review:

1. Is this really necessary?  It seems that you're inserting a hook
here when you could just as well change tmplinit to point to whatever
function you want to call, which wouldn't require a code change.

2. Our standard criteria for the inclusion of a hook is some sample
code that demonstrates how it can be usefully used.  I think you need
to provide that before we can consider this further.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Robert Haas]

On Mon, Sep 27, 2010 at 11:09 PM, Itagaki Takahiro
 wrote:
> On Tue, Sep 28, 2010 at 9:51 AM, Robert Haas  wrote:
>>> Since we have PGDLLEXPORT in 9.0, we can mark some of exported
>>> functions with it in tutorial codes and maybe contrib modules.
>>
>> If that (a) works and (b) reduces user confusion, +1 from me.  We've
>> gotten this question a few times lately.
>
> If we do so, many PGDLLEXPORT will be added:
>  *  17 in src/tutorial
>  * 507 in contrib
> for each exported PGFunction, _PG_init, and _PG_fini.
>
> Any objections? Am I missing something?

Oh - I didn't realize this meant marking lots of things in contrib
that didn't otherwise need to be marked.  Why do other people need
this if we don't?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Itagaki Takahiro]

On Tue, Sep 28, 2010 at 9:51 AM, Robert Haas  wrote:
>> Since we have PGDLLEXPORT in 9.0, we can mark some of exported
>> functions with it in tutorial codes and maybe contrib modules.
>
> If that (a) works and (b) reduces user confusion, +1 from me.  We've
> gotten this question a few times lately.

If we do so, many PGDLLEXPORT will be added:
  *  17 in src/tutorial
  * 507 in contrib
for each exported PGFunction, _PG_init, and _PG_fini.

Any objections? Am I missing something?

-- 
Itagaki Takahiro

-- 
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] Hot Standby tuning for btree_xlog_vacuum()

[Robert Haas]

On Thu, Apr 29, 2010 at 4:12 PM, Simon Riggs  wrote:
> Simple tuning of btree_xlog_vacuum() using an idea I had a while back,
> just never implemented. XXX comments removed.
>
> Allows us to avoid reading in blocks during VACUUM replay that are only
> required for correctness of index scans.

Review:

1. The block comment in XLogConfirmBufferIsUnpinned appears to be
copied from somewhere else, and doesn't really seem appropriate for a
new function since it refers to "the original coding of this routine".
 I think you could just delete the parenthesized portion of the
comment.

2. This bit from ConfirmBufferIsUnpinned looks odd to me.

+   /*
+* Found it.  Now, pin/unpin the buffer to prove it's unpinned.
+*/
+   if (PinBuffer(buf, NULL))
+   UnpinBuffer(buf, false);

I don't think pinning and unpinning the buffer is sufficient to
provide that it isn't otherwise pinned.  If the buffer isn't in shared
buffers at all, it seems clear that no one can have it pinned.  But if
it's present in shared buffers, it seems like you still need
LockBufferForCleanup().

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

[Robert Haas]

On Mon, Sep 27, 2010 at 8:42 AM, Alexander Korotkov
 wrote:
> The second idea is to make values in matrix possible greater. I analyze what
> exactly is matrix in this case. It is sum of original matrix, which
> represent distances between prefixes of strings, and matrix, which represent
> cost of insertions or deletions for moving to diagonal, which containing
> bottom right cell. There is an example of second matrix summand:
>
>   k  i  t  t  e  n
>    1  2  3  4  5  6  7
> s  0  1  2  3  4  5  6
> i  1  0  1  2  3  4  5
> t  2  1  0  1  2  3  4
> t  3  2  1  0  1  2  3
> i  4  3  2  1  0  1  2
> n  5  4  3  2  1  0  1
> g  6  5  4  3  2  1  0
>
> And an example of resulting matrix:
>
>   k  i  t  t  e  n
>    1  3  5  7  9  11 13
> s  1  2  4  6  8  10 12
> i  3  2  2  4  6  8  10
> t  5  4  2  2  4  6  8
> t  7  6  4  2  2  4  6
> i  9  8  6  4  2  3  5
> n  11 10 8  6  4  3  3
> g  13 12 10 8  6  5  3
>
> The resulting matrix saves important property of original matrix, that cell
> value always greater or equal than values, which are used for it's
> calculation.

Hmm.  So if I understand correctly (and it's possible that I don't),
the idea here is that for each cell in the matrix, we store the sum of
the following two quantities:

1. The cost of transforming an initial substring of s into an initial
substring of t (as before), and
2. The smallest imaginable cost of transforming the remaining portion
of s into the remaining portion of t, based on the difference in the
string lengths.

Thus, any cell whose value is already > max_d can't be relevant to the
final result.

The code seems a bit complex, though.  In particular, I'm wondering if
we couldn't simplify things by leaving the meaning of the cells in the
matrix unchanged and just using this calculation to adjust the lower
and upper bounds for each scan.  Perhaps instead of
curr/prev_left/right we could call them min_i and max_i, and after
each inner loop from min_i to max_i we could try to increment min_i
and decrement max_i based on whether cur[min/max_i] + the smallest
possible residual cost > max_d.  Then you've got to also increment
max_i once for each value of j.  Does that make any sense or am I all
wet?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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: I: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Itagaki Takahiro]

On Tue, Aug 31, 2010 at 8:04 PM, Itagaki Takahiro
 wrote:
> I think the patch is almost ready to commit, but still
> have some comments for the usability and documentations.
> I hope native English speakers would help improving docs.

I'm checking the latest patch for applying.
I found we actually use maintenance_work_mem for the sort in seqscan+sort
case, but the cost was estimated based on work_mem in the patch. I added
internal cost_sort_with_mem() into costsize.c.

> * Documentation could be a bit more simplified like as
>  "CLUSTER requires twice disk spaces of your original table".
>  The added description seems too difficult for standard users.

I re-ordered some description in the doc. Does it look better?
Comments and suggestions welcome.

-- 
Itagaki Takahiro


sorted_cluster-20100928.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] PlaceHolderVars versus join ordering

[Tom Lane]

Robert Haas  writes:
> On Mon, Sep 27, 2010 at 1:15 PM, Tom Lane  wrote:
>> This is a larger change than I would prefer to back-patch, but the only
>> less-invasive alternative I can see is to lobotomize the PlaceHolderVar
>> mechanism entirely by reverting to 8.3-style logic wherein we prevented
>> pullup of sub-selects that would require introduction of placeholders.
>> That would undo a significant optimization feature of 8.4, one that
>> I believe we're now relying on for reasonable performance of some system
>> views.
>> 
>> Thoughts, better ideas?

> Personally, I would rather back-patch a more invasive bug fix than a
> performance regression.

Yeah, me too.  Attached is a draft patch against HEAD --- comments?

regards, tom lane

diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index deaeb761d4a90192489c7b8398000cc45f45c07c..2b226bcf2f25586c3b96df146a900f72f0d55843 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*** _copyPlaceHolderInfo(PlaceHolderInfo *fr
*** 1806,1811 
--- 1806,1812 
  	COPY_NODE_FIELD(ph_var);
  	COPY_BITMAPSET_FIELD(ph_eval_at);
  	COPY_BITMAPSET_FIELD(ph_needed);
+ 	COPY_BITMAPSET_FIELD(ph_may_need);
  	COPY_SCALAR_FIELD(ph_width);
  
  	return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 6b6cd9966ce29518d9f3ca3f008768a5816021c5..c7f379c58a523ef13db49480643f87cfe3f67896 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*** _equalPlaceHolderInfo(PlaceHolderInfo *a
*** 838,843 
--- 838,844 
  	COMPARE_NODE_FIELD(ph_var);
  	COMPARE_BITMAPSET_FIELD(ph_eval_at);
  	COMPARE_BITMAPSET_FIELD(ph_needed);
+ 	COMPARE_BITMAPSET_FIELD(ph_may_need);
  	COMPARE_SCALAR_FIELD(ph_width);
  
  	return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 3b30d4f81c02b5c882ffd6facb5b693b50a95364..55665ca20e5f3e8bfb678a4b09acb7bae5ddd2d9 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*** _outPlaceHolderInfo(StringInfo str, Plac
*** 1768,1773 
--- 1768,1774 
  	WRITE_NODE_FIELD(ph_var);
  	WRITE_BITMAPSET_FIELD(ph_eval_at);
  	WRITE_BITMAPSET_FIELD(ph_needed);
+ 	WRITE_BITMAPSET_FIELD(ph_may_need);
  	WRITE_INT_FIELD(ph_width);
  }
  
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 2a913578eba442e95952b79c0a29276012da34f2..89805c36b0664a3c44ab66e71177084f8aad5220 100644
*** a/src/backend/optimizer/plan/analyzejoins.c
--- b/src/backend/optimizer/plan/analyzejoins.c
*** remove_rel_from_query(PlannerInfo *root,
*** 396,401 
--- 396,403 
  			phinfo->ph_eval_at = bms_add_member(phinfo->ph_eval_at, relid);
  
  		phinfo->ph_needed = bms_del_member(phinfo->ph_needed, relid);
+ 		/* ph_may_need probably isn't used after this, but fix it anyway */
+ 		phinfo->ph_may_need = bms_del_member(phinfo->ph_may_need, relid);
  	}
  
  	/*
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 2c61795ff271899892db8d94ff052eb02f30be4f..d95bee7107771c324be884a779e70e3900f8c056 100644
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
*** add_vars_to_targetlist(PlannerInfo *root
*** 187,192 
--- 187,199 
  
  			phinfo->ph_needed = bms_add_members(phinfo->ph_needed,
  where_needed);
+ 			/*
+ 			 * Update ph_may_need too.  This is currently only necessary
+ 			 * when being called from build_base_rel_tlists, but we may as
+ 			 * well do it always.
+ 			 */
+ 			phinfo->ph_may_need = bms_add_members(phinfo->ph_may_need,
+   where_needed);
  		}
  		else
  			elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
*** deconstruct_recurse(PlannerInfo *root, N
*** 465,471 
--- 472,482 
  
  		/* Now we can add the SpecialJoinInfo to join_info_list */
  		if (sjinfo)
+ 		{
  			root->join_info_list = lappend(root->join_info_list, sjinfo);
+ 			/* Each time we do that, recheck placeholder eval levels */
+ 			update_placeholder_eval_levels(root, sjinfo);
+ 		}
  
  		/*
  		 * Finally, compute the output joinlist.  We fold subproblems together
*** make_outerjoininfo(PlannerInfo *root,
*** 688,693 
--- 699,729 
  	}
  
  	/*
+ 	 * Examine PlaceHolderVars.  If a PHV is supposed to be evaluated within
+ 	 * this join's nullable side, and it may get used above this join, then
+ 	 * ensure that min_righthand contains the full eval_at set of the PHV.
+ 	 * This ensures that the PHV actually can be evaluated within the RHS.
+ 	 * Note that this works only because we should already have determined
+ 	 * the final eval_at level for any PHV syntactically within this join.
+ 	 */
+ 	foreach(l, root->placeholder_list)
+ 	{
+ 		PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l);
+ 		Relids		ph_syn_level = phinf

Re: [HACKERS] security label support, revised

[Robert Haas]

On Mon, Sep 27, 2010 at 4:05 AM, KaiGai Kohei  wrote:
>> Thanks, this looks like mostly good stuff.  Here's a new version of
>> the patch with some bug fixes, additional regression tests, and other
>> cleanup.  I think this is about ready to commit.
>
> Thanks for your reviewing and cleaning-up.

I found and fixed a few more issues and committed this.  The pg_dump
support had a few escaping bugs, and I added tab completion support
for psql.  Considering the size of the patch, it seems likely that
there are some issues we both overlooked, but this is as solid as I
can make it for right now.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Robert Haas]

On Mon, Sep 27, 2010 at 8:45 PM, Itagaki Takahiro
 wrote:
> On Tue, Sep 28, 2010 at 5:13 AM, Euler Taveira de Oliveira
>  wrote:
>> Itagaki Takahiro escreveu:
>>> I had the same problems before, and I wrote some hacks for VC++.
>>>
>> Isn't there such a code in core or am i missing something? Is it worth
>> supporting the VC++ standalone projects?
>
> Since we have PGDLLEXPORT in 9.0, we can mark some of exported
> functions with it in tutorial codes and maybe contrib modules.
>
> Is it worth doing?  We don't need the marks in our build environment,
> but they might help users referencing our codes in standalone VC++ projects.

If that (a) works and (b) reduces user confusion, +1 from me.  We've
gotten this question a few times lately.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Itagaki Takahiro]

On Tue, Sep 28, 2010 at 5:13 AM, Euler Taveira de Oliveira
 wrote:
> Itagaki Takahiro escreveu:
>> I had the same problems before, and I wrote some hacks for VC++.
>>
> Isn't there such a code in core or am i missing something? Is it worth
> supporting the VC++ standalone projects?

Since we have PGDLLEXPORT in 9.0, we can mark some of exported
functions with it in tutorial codes and maybe contrib modules.

Is it worth doing?  We don't need the marks in our build environment,
but they might help users referencing our codes in standalone VC++ projects.

-- 
Itagaki Takahiro

-- 
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] Large objects.

[Robert Haas]

On Mon, Sep 27, 2010 at 2:25 PM, Dmitriy Igrishin  wrote:
> Hey Robert, Tom
>
> Tom, thank you for explanation!
>
>> Ouch.  Letting people write data to where they can't get it back from
>> seems double-plus ungood.
>>
> Robert, yes, I agree with you. This is exactly what I wanted to say.
> I've implemented a stream class in C++ and this circumstance makes
> the code not so clean because I need to take into account the behavior
> of lo_write() and 2GB limit.

On further examination, it appears we're not doing this.  The reason
lo_read wasn't returning any data in your earlier example is because
you called it after seeking to the end of the object.  If you seek to
the position where the data was written, it works fine.

A fairly plausible argument could be made that we shouldn't allow
reading or writing past 2^31-1, but it now appears to me that the
behavior is at least self-consistent.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] trailing whitespace in psql table output

[Robert Haas]

On Mon, Sep 27, 2010 at 4:12 PM, David Fetter  wrote:
> On Mon, Sep 27, 2010 at 03:11:07PM -0400, Robert Haas wrote:
>> On Mon, Sep 27, 2010 at 2:09 PM, David Fetter  wrote:
>> > On Mon, Sep 27, 2010 at 01:53:45PM -0400, Robert Haas wrote:
>> >> On Mon, Sep 27, 2010 at 1:34 PM, Alvaro Herrera
>> >>  wrote:
>> >> > Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 
>> >> > 2010:
>> >> >> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
>> >> >>
>> >> >> > Um, no.
>> >> >> >
>> >> >> > In the meantime, I have arrived at the conclusion that doing this 
>> >> >> > isn't
>> >> >> > worth it because it will break all regression test output.  We can 
>> >> >> > fix
>> >> >> > the stuff in our tree, but pg_regress is also used externally, and 
>> >> >> > those
>> >> >> > guys would have a nightmare with this change.  Perhaps if there is
>> >> >> > another more significant revision of the table style in the future, 
>> >> >> > we
>> >> >> > should keep this issue in mind.
>> >> >>
>> >> >> Or change the way pg_regress works.
>> >> >
>> >> > Perhaps using unaligned mode?  The problem with that is that it becomes
>> >> > very difficult to review changes to expected output.
>> >>
>> >> Uh, yuck!  If we don't care about changing the expected output, we can
>> >> just trim the whitespace as Peter suggested originally.
>> >
>> > I must be missing something pretty crucial here as far as the
>> > complexity of changing all the regression tests.  Wouldn't trimming
>> > all trailing whitespace do the trick?
>>
>> Sure.  But everyone using pg_regress will have to update their
>> regression test expected outputs.
>
> Again, I must be missing something super important.  What is it that
> prevents people from doing
>
> find . -type f |xargs perl -pi.bak -e 's/\s+$//g'
>
> or moral equivalent on their pg_regression tree?

I assume it's not that simple, but I haven't tried it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Help with User-defined function in PostgreSQL with Visual C++

[Euler Taveira de Oliveira]

Itagaki Takahiro escreveu:
> I had the same problems before, and I wrote some hacks for VC++.
> 
Isn't there such a code in core or am i missing something? Is it worth
supporting the VC++ standalone projects?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] trailing whitespace in psql table output

[David Fetter]

On Mon, Sep 27, 2010 at 03:11:07PM -0400, Robert Haas wrote:
> On Mon, Sep 27, 2010 at 2:09 PM, David Fetter  wrote:
> > On Mon, Sep 27, 2010 at 01:53:45PM -0400, Robert Haas wrote:
> >> On Mon, Sep 27, 2010 at 1:34 PM, Alvaro Herrera
> >>  wrote:
> >> > Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 
> >> > 2010:
> >> >> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
> >> >>
> >> >> > Um, no.
> >> >> >
> >> >> > In the meantime, I have arrived at the conclusion that doing this 
> >> >> > isn't
> >> >> > worth it because it will break all regression test output.  We can fix
> >> >> > the stuff in our tree, but pg_regress is also used externally, and 
> >> >> > those
> >> >> > guys would have a nightmare with this change.  Perhaps if there is
> >> >> > another more significant revision of the table style in the future, we
> >> >> > should keep this issue in mind.
> >> >>
> >> >> Or change the way pg_regress works.
> >> >
> >> > Perhaps using unaligned mode?  The problem with that is that it becomes
> >> > very difficult to review changes to expected output.
> >>
> >> Uh, yuck!  If we don't care about changing the expected output, we can
> >> just trim the whitespace as Peter suggested originally.
> >
> > I must be missing something pretty crucial here as far as the
> > complexity of changing all the regression tests.  Wouldn't trimming
> > all trailing whitespace do the trick?
> 
> Sure.  But everyone using pg_regress will have to update their
> regression test expected outputs.

Again, I must be missing something super important.  What is it that
prevents people from doing

find . -type f |xargs perl -pi.bak -e 's/\s+$//g'

or moral equivalent on their pg_regression tree?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] trailing whitespace in psql table output

[Robert Haas]

On Mon, Sep 27, 2010 at 2:09 PM, David Fetter  wrote:
> On Mon, Sep 27, 2010 at 01:53:45PM -0400, Robert Haas wrote:
>> On Mon, Sep 27, 2010 at 1:34 PM, Alvaro Herrera
>>  wrote:
>> > Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 2010:
>> >> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
>> >>
>> >> > Um, no.
>> >> >
>> >> > In the meantime, I have arrived at the conclusion that doing this isn't
>> >> > worth it because it will break all regression test output.  We can fix
>> >> > the stuff in our tree, but pg_regress is also used externally, and those
>> >> > guys would have a nightmare with this change.  Perhaps if there is
>> >> > another more significant revision of the table style in the future, we
>> >> > should keep this issue in mind.
>> >>
>> >> Or change the way pg_regress works.
>> >
>> > Perhaps using unaligned mode?  The problem with that is that it becomes
>> > very difficult to review changes to expected output.
>>
>> Uh, yuck!  If we don't care about changing the expected output, we can
>> just trim the whitespace as Peter suggested originally.
>
> I must be missing something pretty crucial here as far as the
> complexity of changing all the regression tests.  Wouldn't trimming
> all trailing whitespace do the trick?

Sure.  But everyone using pg_regress will have to update their
regression test expected outputs.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Large objects.

[Dmitriy Igrishin]

Hey Robert, Tom

Tom, thank you for explanation!

Ouch.  Letting people write data to where they can't get it back from
> seems double-plus ungood.
>
> Robert, yes, I agree with you. This is exactly what I wanted to say.
I've implemented a stream class in C++ and this circumstance makes
the code not so clean because I need to take into account the behavior
of lo_write() and 2GB limit.

-- 
// Dmitriy.

Re: [HACKERS] Improving prep_buildtree used in VPATH builds

[Gurjeet Singh]

On Mon, Sep 27, 2010 at 7:21 PM, Tom Lane  wrote:

> Alvaro Herrera  writes:
> > Excerpts from Gurjeet Singh's message of dom sep 26 22:15:59 -0400 2010:
> >> Currently I am seeing a performance improvement of this script by only
> about
> >> 500 ms; say 11.8 seconds vs. 11.3 secs. But I remember distinctly that
> >> yesterday I was able to see an improvement of 11% on the same virtual
> >> machine, averaged on multiple runs; 42 sec vs 37 sec. It might be the
> case
> >> that the host OS or my Linux virtual machine were loaded at that time
> and
> >> the filesystem could not cache enough inodes.
>
> > Hmm.  On my otherwise idle desktop machine, I can't measure a difference.
>
> Yeah, this seems like something that would have at best an
> environment-specific effect.  I'm not convinced that it couldn't make
> things worse in some cases ...
>

I can't think of any obvious cases where this might hurt. I am unable to
reproduce the 11% improvement, but I did see that dramatic change which
prompted me for the patch. On the contrary, nothing so far suggests that it
could hurt configure times.

Regards,
-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [HACKERS] trailing whitespace in psql table output

[David Fetter]

On Mon, Sep 27, 2010 at 01:53:45PM -0400, Robert Haas wrote:
> On Mon, Sep 27, 2010 at 1:34 PM, Alvaro Herrera
>  wrote:
> > Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 2010:
> >> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
> >>
> >> > Um, no.
> >> >
> >> > In the meantime, I have arrived at the conclusion that doing this isn't
> >> > worth it because it will break all regression test output.  We can fix
> >> > the stuff in our tree, but pg_regress is also used externally, and those
> >> > guys would have a nightmare with this change.  Perhaps if there is
> >> > another more significant revision of the table style in the future, we
> >> > should keep this issue in mind.
> >>
> >> Or change the way pg_regress works.
> >
> > Perhaps using unaligned mode?  The problem with that is that it becomes
> > very difficult to review changes to expected output.
> 
> Uh, yuck!  If we don't care about changing the expected output, we can
> just trim the whitespace as Peter suggested originally.

I must be missing something pretty crucial here as far as the
complexity of changing all the regression tests.  Wouldn't trimming
all trailing whitespace do the trick?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] trailing whitespace in psql table output

[Robert Haas]

On Mon, Sep 27, 2010 at 1:34 PM, Alvaro Herrera
 wrote:
> Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 2010:
>> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
>>
>> > Um, no.
>> >
>> > In the meantime, I have arrived at the conclusion that doing this isn't
>> > worth it because it will break all regression test output.  We can fix
>> > the stuff in our tree, but pg_regress is also used externally, and those
>> > guys would have a nightmare with this change.  Perhaps if there is
>> > another more significant revision of the table style in the future, we
>> > should keep this issue in mind.
>>
>> Or change the way pg_regress works.
>
> Perhaps using unaligned mode?  The problem with that is that it becomes
> very difficult to review changes to expected output.

Uh, yuck!  If we don't care about changing the expected output, we can
just trim the whitespace as Peter suggested originally.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] client socket TIME_WAIT state after PQfinish

[Nicolas Barbier]

2010/9/27 Robert Haas :

> On Mon, Sep 27, 2010 at 12:56 PM, Guillaume Du Pasquier
>  wrote:
>
>> Our client runs on the same machine as the postgresql server.
>> Would it be possible to use PF_UNIX sockets ?
>
> Yeah, actually that's the default, if you just run "psql" with no
> parameters.  It looks for a socket in /tmp/.s.PGSQL.5432.

It depends on the interface; e.g., the JDBC driver doesn't support
unix sockets, AFAIR.

Nicolas

-- 
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] trailing whitespace in psql table output

[Alvaro Herrera]

Excerpts from David E. Wheeler's message of lun sep 27 12:25:31 -0400 2010:
> On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:
> 
> > Um, no.
> > 
> > In the meantime, I have arrived at the conclusion that doing this isn't
> > worth it because it will break all regression test output.  We can fix
> > the stuff in our tree, but pg_regress is also used externally, and those
> > guys would have a nightmare with this change.  Perhaps if there is
> > another more significant revision of the table style in the future, we
> > should keep this issue in mind.
> 
> Or change the way pg_regress works.

Perhaps using unaligned mode?  The problem with that is that it becomes
very difficult to review changes to expected output.

-- 
Álvaro Herrera 
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] client socket TIME_WAIT state after PQfinish

[Robert Haas]

On Mon, Sep 27, 2010 at 12:56 PM, Guillaume Du Pasquier
 wrote:
> Dear Nicolas, Dear Robert,
>
> Thank you for your quick answers.
> We do not have such behavior using SSL, how do you explain it ?
> I suppose that openssl is using the setsockopt SO_LINGER that
> removes this behavior. Therefore, there is a RST sent to close
> the socket.
>
> We work with an environment that uses a lot of socket connections.
> Therefore, many file descriptors are opened. If after each
> Sql requests a TIME_WAIT arises we will end up with many
> file descriptors opened. By default the maximum number of file descriptors
> is set to 1024 and we reach that number quite fast.

Hrm.  Does a socket in the TIME_WAIT state count against the number of
open file descriptors?  Certainly, it shouldn't count against the
per-process limit, as the process has already closed it.

> Do you have any advices to get rid of this TIME_WAIT problem ?
> Our client runs on the same machine as the postgresql server.
> Would it be possible to use PF_UNIX sockets ?

Yeah, actually that's the default, if you just run "psql" with no
parameters.  It looks for a socket in /tmp/.s.PGSQL.5432.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] PlaceHolderVars versus join ordering

[Robert Haas]

On Mon, Sep 27, 2010 at 1:15 PM, Tom Lane  wrote:
> I've been looking at the bug reported by Kirill Simonov here:
> http://archives.postgresql.org/pgsql-bugs/2010-09/msg00265.php
> This is pretty ugly :-(.  What we have is
>        select * from A
>        left join (select some-expressions from B left join C ...)
> The expressions are not guaranteed nullable; that is, they won't
> automatically produce nulls from all-null rows for B and C.
> This means that we have to evaluate the expressions at the join of
> B and C, producing PlaceHolderVars, which then bubble up through
> the left join with A, and will get replaced with nulls in any
> null-extended row of that join.  Then references to them in the final
> select list will correctly show as nulls.
>
> The problem is that there isn't anything guaranteeing that the B/C join
> will be formed before joining to A.  In Kirill's example the planner
> thinks it should commute the two joins, and then it evaluates the
> PlaceHolderVars at the now-upper B/C join, meaning they fail to go to
> nulls in rows where they should.
>
> This is a fundamental oversight in the original design of the
> PlaceHolderVar mechanism.  I'm not sure how come I didn't see that some
> sort of join-order interlock was necessary, but I didn't.
>
> The most trustworthy fix that I can think of goes like this:
>
> 1. Add an extra pass over the parsetree early in query_planner() to
> locate PlaceHolderVars, set up the PlaceHolderInfo list, and remember the
> syntactic level of each actually-used PlaceHolderVar.  It's slightly
> annoying to have to make an extra tree traversal for this, but at least
> we can skip it in the common case where no PlaceHolderVars were created.
> (Currently, this processing happens as a side-effect during
> distribute_qual_to_rels, but that's too late for what we need to do in
> point 2.  Also, making the PlaceHolderInfos immediately when the
> PlaceHolderVars are created would be unpalatable since we don't know at
> that stage whether the PlaceHolderVars are actually used above the
> relevant outer join.)
>
> 2. In make_outerjoininfo, scan the PlaceHolderInfo list to see if there
> are any placeholders that should be evaluated below the current outer join
> and are needed above it.  If so, enlarge the outer join's min_righthand
> set to ensure that it won't get moved below where the placeholder has to
> be evaluated.  We can fold the existing fix_placeholder_eval_levels()
> logic into this operation, which will make that code less fragile than it
> is now.
>
> This is a larger change than I would prefer to back-patch, but the only
> less-invasive alternative I can see is to lobotomize the PlaceHolderVar
> mechanism entirely by reverting to 8.3-style logic wherein we prevented
> pullup of sub-selects that would require introduction of placeholders.
> That would undo a significant optimization feature of 8.4, one that
> I believe we're now relying on for reasonable performance of some system
> views.
>
> Thoughts, better ideas?

Personally, I would rather back-patch a more invasive bug fix than a
performance regression.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Gurjeet Singh's message of dom sep 26 22:15:59 -0400 2010:
>> Currently I am seeing a performance improvement of this script by only about
>> 500 ms; say 11.8 seconds vs. 11.3 secs. But I remember distinctly that
>> yesterday I was able to see an improvement of 11% on the same virtual
>> machine, averaged on multiple runs; 42 sec vs 37 sec. It might be the case
>> that the host OS or my Linux virtual machine were loaded at that time and
>> the filesystem could not cache enough inodes.

> Hmm.  On my otherwise idle desktop machine, I can't measure a difference.

Yeah, this seems like something that would have at best an
environment-specific effect.  I'm not convinced that it couldn't make
things worse in some cases ...

regards, tom lane

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

[HACKERS] PlaceHolderVars versus join ordering

[Tom Lane]

I've been looking at the bug reported by Kirill Simonov here:
http://archives.postgresql.org/pgsql-bugs/2010-09/msg00265.php
This is pretty ugly :-(.  What we have is
select * from A
left join (select some-expressions from B left join C ...)
The expressions are not guaranteed nullable; that is, they won't
automatically produce nulls from all-null rows for B and C.
This means that we have to evaluate the expressions at the join of
B and C, producing PlaceHolderVars, which then bubble up through
the left join with A, and will get replaced with nulls in any
null-extended row of that join.  Then references to them in the final
select list will correctly show as nulls.

The problem is that there isn't anything guaranteeing that the B/C join
will be formed before joining to A.  In Kirill's example the planner
thinks it should commute the two joins, and then it evaluates the
PlaceHolderVars at the now-upper B/C join, meaning they fail to go to
nulls in rows where they should.

This is a fundamental oversight in the original design of the
PlaceHolderVar mechanism.  I'm not sure how come I didn't see that some
sort of join-order interlock was necessary, but I didn't.

The most trustworthy fix that I can think of goes like this:

1. Add an extra pass over the parsetree early in query_planner() to
locate PlaceHolderVars, set up the PlaceHolderInfo list, and remember the
syntactic level of each actually-used PlaceHolderVar.  It's slightly
annoying to have to make an extra tree traversal for this, but at least
we can skip it in the common case where no PlaceHolderVars were created.
(Currently, this processing happens as a side-effect during
distribute_qual_to_rels, but that's too late for what we need to do in
point 2.  Also, making the PlaceHolderInfos immediately when the
PlaceHolderVars are created would be unpalatable since we don't know at
that stage whether the PlaceHolderVars are actually used above the
relevant outer join.)

2. In make_outerjoininfo, scan the PlaceHolderInfo list to see if there
are any placeholders that should be evaluated below the current outer join
and are needed above it.  If so, enlarge the outer join's min_righthand
set to ensure that it won't get moved below where the placeholder has to
be evaluated.  We can fold the existing fix_placeholder_eval_levels()
logic into this operation, which will make that code less fragile than it
is now.

This is a larger change than I would prefer to back-patch, but the only
less-invasive alternative I can see is to lobotomize the PlaceHolderVar
mechanism entirely by reverting to 8.3-style logic wherein we prevented
pullup of sub-selects that would require introduction of placeholders.
That would undo a significant optimization feature of 8.4, one that
I believe we're now relying on for reasonable performance of some system
views.

Thoughts, better ideas?

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] client socket TIME_WAIT state after PQfinish

[Guillaume Du Pasquier]

Dear Nicolas, Dear Robert,

Thank you for your quick answers.
We do not have such behavior using SSL, how do you explain it ?
I suppose that openssl is using the setsockopt SO_LINGER that
removes this behavior. Therefore, there is a RST sent to close
the socket.

We work with an environment that uses a lot of socket connections.
Therefore, many file descriptors are opened. If after each
Sql requests a TIME_WAIT arises we will end up with many
file descriptors opened. By default the maximum number of file descriptors
is set to 1024 and we reach that number quite fast.

Do you have any advices to get rid of this TIME_WAIT problem ?
Our client runs on the same machine as the postgresql server.
Would it be possible to use PF_UNIX sockets ?

Thank you,

Franck


-Original Message-
From: Nicolas Barbier [mailto:nicolas.barb...@gmail.com] 
Sent: lundi 27 septembre 2010 18:23
To: Guillaume Du Pasquier
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] client socket TIME_WAIT state after PQfinish

2010/9/27 Guillaume Du Pasquier :

> In both cases, the client socket (pgadmin or my program) remains in
> TIME_WAIT state.
>
> I have used wireshark to sniff the TCP protocol.
>
> We have at the end of a connection:
>
> Client      Server
>
>    --->    FIN,ACK  --->
>    <---    FIN,ACK  <---
>    --->      ACK    --->
>
> This ends up in a TIME_WAIT state. The TCP protocol should be

According to the Two Generals' Problem [1], one of the sides
necessarily doesn't know whether the other side has received its last
packet. Therefore, TCP lets one of the sides sit in TIME_WAIT status
for as long as any packets could in principle survive on the network
(typically defined as 2 minutes on IP networks IIRC), and potentially
disturb a new connection between the same (dst IP, dst port, src IP,
src port) combination.

[1] http://en.wikipedia.org/wiki/Two_Generals'_Problem>

> Client      Server
>
>    --->    FIN,ACK  --->
>    <---      ACK    <---
>    <---    FIN,ACK  <---

AFAIK, this last ACK (in above packet) is not needed: the server can
ACK the client's FIN _while_ it sends its own FIN (by using an
appropriate sequence number, as FIN "uses" one byte in the sequence).

>    --->      ACK    --->
>
> I suppose there is a bug in the postgresql server that do not send an ack to
> the client.

I don't think so.

Nicolas

-- 
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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 11:15 AM, Robert Haas  wrote:
> On Mon, Sep 27, 2010 at 11:08 AM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> Well, the historical set of topics varies from CommitFest to
>>> CommitFest, by design.  There are some that recur pretty regularly, of
>>> course, like Security, Performance, and Miscellaneous.  But not every
>>> CF will have a section for ECPG or Refactoring, for example.  In one
>>> CF, we may have six ECPG patches, so ECPG gets its own topic; in
>>> another CF, 1 ECPG patch + 2 libpq patches + 1 psql patch get merged
>>> together under a section called Interfaces.  This generally makes it
>>> easier to group things in ways that are useful in practice than a
>>> fixed list of topics, so I'm in favor of keeping it that way.
>>
>> If it's intentional that the topic for the same patch might vary
>> depending on what else is submitted in the same CF, then I think that
>> asking submitters to select topics is the wrong thing from the get-go.
>> The patches should be uncategorized initially, and then someone like the
>> CF manager should group them into topics after-the-fact.
>
> That's actually not a bad idea, although it would require a bit of
> hacking given the way the schema is currently set up.  The current
> system has been working well enough that I'm inclined to do something
> simpler for the present, like maybe just auto-create MIscellaneous for
> each new CF.  That would have more or less the same effect for about
> one-tenth the work.

Eh, on further review, I decided to do something even simpler still,
which is to say unbreak the warning message that's supposed to appear
in this case.  Doing one of the things listed above is probably
better, but this took approximately 60 seconds, so let's wait and see
whether it helps.  If not, I'll whack it around some more.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 11:28 AM, Andrew Dunstan  wrote:
>>> Could this perhaps be made clearer on the page, perhaps with an example?
>>> It confused me recently too.
>>>
>>> Can you suggest something more specific?
>>>
>>> Well, it could say something like:
>>>
>>> The Message-ID can be found in the headers of the relevant email to the
>>> pgsql-hackers mailing list, and also in the mailing list archives at
>>> http://archives.postgresql.org. It looks something like this (the format
>>> varies somewhat depending on the sender's Mail User Agent):
>>> aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>>>
>>> That would certainly have given me, and I suspect Gurjeet, enough clue.
>>
>> Where on the page would you suggest that we put that text?
>
> Following "Enter your comments below. If you wish your comment to reference
> a message from the mailing list archives, enter the message ID into the
> space provided."

Done.

> Another way to handle this might be to extract it from an http URL if given
> one.

I'm going to leave this idea for another day, thought it's not a bad
one if someone feels motivated to write the code.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] client socket TIME_WAIT state after PQfinish

[Robert Haas]

On Mon, Sep 27, 2010 at 12:22 PM, Nicolas Barbier
 wrote:
> According to the Two Generals' Problem [1], one of the sides
> necessarily doesn't know whether the other side has received its last
> packet. Therefore, TCP lets one of the sides sit in TIME_WAIT status
> for as long as any packets could in principle survive on the network
> (typically defined as 2 minutes on IP networks IIRC), and potentially
> disturb a new connection between the same (dst IP, dst port, src IP,
> src port) combination.

In other words, this is the way TCP is designed to work, not something
specific to PostgreSQL.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] trailing whitespace in psql table output

[David E. Wheeler]

On Sep 27, 2010, at 5:05 AM, Peter Eisentraut wrote:

> Um, no.
> 
> In the meantime, I have arrived at the conclusion that doing this isn't
> worth it because it will break all regression test output.  We can fix
> the stuff in our tree, but pg_regress is also used externally, and those
> guys would have a nightmare with this change.  Perhaps if there is
> another more significant revision of the table style in the future, we
> should keep this issue in mind.

Or change the way pg_regress works.

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] client socket TIME_WAIT state after PQfinish

[Nicolas Barbier]

2010/9/27 Guillaume Du Pasquier :

> In both cases, the client socket (pgadmin or my program) remains in
> TIME_WAIT state.
>
> I have used wireshark to sniff the TCP protocol.
>
> We have at the end of a connection:
>
> Client      Server
>
>    --->    FIN,ACK  --->
>    <---    FIN,ACK  <---
>    --->      ACK    --->
>
> This ends up in a TIME_WAIT state. The TCP protocol should be

According to the Two Generals' Problem [1], one of the sides
necessarily doesn't know whether the other side has received its last
packet. Therefore, TCP lets one of the sides sit in TIME_WAIT status
for as long as any packets could in principle survive on the network
(typically defined as 2 minutes on IP networks IIRC), and potentially
disturb a new connection between the same (dst IP, dst port, src IP,
src port) combination.

[1] http://en.wikipedia.org/wiki/Two_Generals'_Problem>

> Client      Server
>
>    --->    FIN,ACK  --->
>    <---      ACK    <---
>    <---    FIN,ACK  <---

AFAIK, this last ACK (in above packet) is not needed: the server can
ACK the client's FIN _while_ it sends its own FIN (by using an
appropriate sequence number, as FIN "uses" one byte in the sequence).

>    --->      ACK    --->
>
> I suppose there is a bug in the postgresql server that do not send an ack to
> the client.

I don't think so.

Nicolas

-- 
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] A small update for postgresql.conf.sample

[Robert Haas]

2010/9/27 Alvaro Herrera :
> Excerpts from Robert Haas's message of lun sep 27 09:45:57 -0400 2010:
>
>>
>> (Dang this is a lot easier than the old way.)
>>
>
> Did you use git cherry-pick?

Yes!

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] client socket TIME_WAIT state after PQfinish

[Guillaume Du Pasquier]

Dear all,

Environement:
- OS : Ubuntu 10.04 LTS.
- DB:  postgresql 8.4.
- Connection to postgresql using sslmode = disable

Scenario :

1.   I use pgadmin to connect/disconnect to the postgresql server on port 
5432 or

2.   I use a progam using libpq  and make PQconnectdb and PQfinish.

Bug:

In both cases, the client socket (pgadmin or my program) remains in TIME_WAIT 
state.
I have used wireshark to sniff the TCP protocol.

We have at the end of a connection:

Client  Server
   --->FIN,ACK  --->
   <---FIN,ACK  <---
   --->  ACK--->

This ends up in a TIME_WAIT state. The TCP protocol should be

Client  Server
   --->FIN,ACK  --->
   <---  ACK<---
   <---FIN,ACK  <---
   --->  ACK--->

I suppose there is a bug in the postgresql server that do not send an ack to 
the client.

Could you please clarify this situation ? I am a bit lost.

Thank you,

Franck Lefort

Re: [HACKERS] gist access methods parameter types

[Marios Vodas]

Please can you answer the question of whether entry->key in compress should
be of delta3d sql type (composite type) and if not of what it should be
since the type I index is different from the type stored in tree?
Taking into consideration the types I described before this is my code for
compress.

Datum delta3d_compress(PG_FUNCTION_ARGS) {
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
GISTENTRY *retval;

HeapTupleHeader in;
HeapTupleHeader i;
HeapTupleHeader e;
bool isnull;

if (entry->leafkey) {
in = DatumGetHeapTupleHeader(entry->key);

if (in != NULL) {
i = DatumGetHeapTupleHeader(GetAttributeByName(in, "i",
&isnull));
e = DatumGetHeapTupleHeader(GetAttributeByName(in, "e",
&isnull));

delta3d *compressed_data = (delta3d *) palloc(sizeof (delta3d));
compressed_data->xi = DatumGetFloat8(GetAttributeByName(i, "x",
&isnull));
compressed_data->yi = DatumGetFloat8(GetAttributeByName(i, "y",
&isnull));
compressed_data->ti = DatumGetTimestamp(GetAttributeByName(i,
"t", &isnull));
compressed_data->xe = DatumGetFloat8(GetAttributeByName(e, "x",
&isnull));
compressed_data->ye = DatumGetFloat8(GetAttributeByName(e, "y",
&isnull));
compressed_data->te = DatumGetTimestamp(GetAttributeByName(e,
"t", &isnull));
compressed_data->trajectory =
DatumGetInt32(GetAttributeByName(in, "trajectory", &isnull));

retval = palloc(sizeof (GISTENTRY));
gistentryinit(*retval, PointerGetDatum(compressed_data),
entry->rel, entry->page, entry->offset, FALSE);
} else {
retval = palloc(sizeof (GISTENTRY));
gistentryinit(*retval, (Datum) 0, entry->rel, entry->page,
entry->offset, FALSE);
}
} else {
retval = entry; *//does this have to change? I thing it is going to
be of C type delta3d_mbb, am I right?*
}

PG_RETURN_POINTER(retval);
}

Re: [HACKERS] A small update for postgresql.conf.sample

[Alvaro Herrera]

Excerpts from Robert Haas's message of lun sep 27 09:45:57 -0400 2010:

> 
> (Dang this is a lot easier than the old way.)
> 

Did you use git cherry-pick?

-- 
Álvaro Herrera 
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] Improving prep_buildtree used in VPATH builds

[Alvaro Herrera]

Excerpts from Andrew Dunstan's message of lun sep 27 11:28:33 -0400 2010:

> Another way to handle this might be to extract it from an http URL if 
> given one.

+1 for this approach

-- 
Álvaro Herrera 
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] Improving prep_buildtree used in VPATH builds

[Alvaro Herrera]

Excerpts from Gurjeet Singh's message of dom sep 26 22:15:59 -0400 2010:

> Currently I am seeing a performance improvement of this script by only about
> 500 ms; say 11.8 seconds vs. 11.3 secs. But I remember distinctly that
> yesterday I was able to see an improvement of 11% on the same virtual
> machine, averaged on multiple runs; 42 sec vs 37 sec. It might be the case
> that the host OS or my Linux virtual machine were loaded at that time and
> the filesystem could not cache enough inodes.

Hmm.  On my otherwise idle desktop machine, I can't measure a difference.
But this machine has enough RAM for inode cache.

With patch:

real0m3.092s
user0m0.900s
sys 0m2.220s

real0m3.116s
user0m0.928s
sys 0m2.176s

real0m3.128s
user0m1.040s
sys 0m2.108s

Without patch:

real0m3.109s
user0m0.852s
sys 0m2.180s

real0m3.101s
user0m0.884s
sys 0m2.264s

real0m3.121s
user0m0.968s
sys 0m2.140s

> Seems like it would improve performance in general, but more so under load
> conditions when you actually need it. I am not sure if -depth option is
> supported by all incarnations of 'find'.

-- 
Álvaro Herrera 
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] Improving prep_buildtree used in VPATH builds

[Andrew Dunstan]

On 09/27/2010 11:16 AM, Robert Haas wrote:

On Mon, Sep 27, 2010 at 11:15 AM, Andrew Dunstan  wrote:


On 09/27/2010 10:39 AM, Robert Haas wrote:

On Mon, Sep 27, 2010 at 10:15 AM, Andrew Dunstan
wrote:

On 09/27/2010 10:11 AM, Robert Haas wrote:

What should be the value of 'Message-ID for original patch' ?
the URL:
http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

The latter.

Could this perhaps be made clearer on the page, perhaps with an example? It
confused me recently too.

Can you suggest something more specific?


Well, it could say something like:

The Message-ID can be found in the headers of the relevant email to the
pgsql-hackers mailing list, and also in the mailing list archives at
http://archives.postgresql.org. It looks something like this (the format
varies somewhat depending on the sender's Mail User Agent):
aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

That would certainly have given me, and I suspect Gurjeet, enough clue.

Where on the page would you suggest that we put that text?



Following "Enter your comments below. If you wish your comment to 
reference a message from the mailing list archives, enter the message ID 
into the space provided." The point is that because the app nicely turns 
the Message-id into a URL that links to the archives, it's not entirely 
clear whether the user needs to enter the whole URL or not.


Another way to handle this might be to extract it from an http URL if 
given one.


It's a minor nit - it didn't seem worth raising at the time, and I only 
commented when I saw that someone else had the same small confusion I 
had had.



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] Perf regression in 2.6.32 (Ubuntu 10.04 LTS)

[Robert Haas]

On Mon, Sep 13, 2010 at 12:05 PM, Greg Smith  wrote:
> Domas Mituzas wrote:
>>
>> I've been playing around today a lot with sysbench, and observed that
>> 2.6.32 kernel supplied by Ubuntu is having perf regression with PG (which
>> does not affect MySQL), compared to 2.6.28 builds I have.
>> What I observed can be seen in a paste at
>> http://p.defau.lt/?8_GQV82Pz3_SDZbNOdP93Q (db12 is 2.6.28, db20 is 2.6.32 -
>> 2.6.32-24-server).
>> Machines are two socket quad-opterons 2356s.
>> oprofile output can be seen at http://p.defau.lt/?OIR1vDFK4cze_fmBTQbV9w -
>> system has >20% of idle cpu, which is somewhere in the top symbol :)
>>
>
> Are you using the same filesystem setup on both setups?  And regardless,
> what is that filesystem?  We know that between 2.6.28 and 2.6.32 the kernel
> improved how it handles fsync requests in a good way from a reliability
> perspective (to fix bugs that could cause data loss before), particularly on
> ext4, so it's possible the regression you're seeing is just the expense of
> handling things properly.
>
> If you already have sysbench on there, I'd suggest comparing the two systems
> by seeing how fast each can execute fsync requests:
>
> sysbench --test=fileio --file-fsync-freq=1 --file-num=1
> --file-total-size=16384 --file-test-mode=rndwr run | grep "Requests/sec"
>
> To help distinguish whether this regression might be coming from the already
> known changes in that area, or if it's instead from something that's
> impacting CPU efficiency.
>
> Also, it's easy to see a performance change of this size just from the
> database files being on a different part of the disk if you didn't control
> for that.  Disks are almost twice as fast at their beginning than their end
> nowadays.

Greg, have you run into any other evidence suggesting a problem with 2.6.32?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 11:15 AM, Andrew Dunstan  wrote:
>
>
> On 09/27/2010 10:39 AM, Robert Haas wrote:
>
> On Mon, Sep 27, 2010 at 10:15 AM, Andrew Dunstan 
> wrote:
>
> On 09/27/2010 10:11 AM, Robert Haas wrote:
>
> What should be the value of 'Message-ID for original patch' ?
> the URL:
> http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
> or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>
> The latter.
>
> Could this perhaps be made clearer on the page, perhaps with an example? It
> confused me recently too.
>
> Can you suggest something more specific?
>
>
> Well, it could say something like:
>
> The Message-ID can be found in the headers of the relevant email to the
> pgsql-hackers mailing list, and also in the mailing list archives at
> http://archives.postgresql.org. It looks something like this (the format
> varies somewhat depending on the sender's Mail User Agent):
> aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>
> That would certainly have given me, and I suspect Gurjeet, enough clue.

Where on the page would you suggest that we put that text?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 11:08 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> Well, the historical set of topics varies from CommitFest to
>> CommitFest, by design.  There are some that recur pretty regularly, of
>> course, like Security, Performance, and Miscellaneous.  But not every
>> CF will have a section for ECPG or Refactoring, for example.  In one
>> CF, we may have six ECPG patches, so ECPG gets its own topic; in
>> another CF, 1 ECPG patch + 2 libpq patches + 1 psql patch get merged
>> together under a section called Interfaces.  This generally makes it
>> easier to group things in ways that are useful in practice than a
>> fixed list of topics, so I'm in favor of keeping it that way.
>
> If it's intentional that the topic for the same patch might vary
> depending on what else is submitted in the same CF, then I think that
> asking submitters to select topics is the wrong thing from the get-go.
> The patches should be uncategorized initially, and then someone like the
> CF manager should group them into topics after-the-fact.

That's actually not a bad idea, although it would require a bit of
hacking given the way the schema is currently set up.  The current
system has been working well enough that I'm inclined to do something
simpler for the present, like maybe just auto-create MIscellaneous for
each new CF.  That would have more or less the same effect for about
one-tenth the work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Andrew Dunstan]

On 09/27/2010 10:39 AM, Robert Haas wrote:

On Mon, Sep 27, 2010 at 10:15 AM, Andrew Dunstan  wrote:


On 09/27/2010 10:11 AM, Robert Haas wrote:

What should be the value of 'Message-ID for original patch' ?
the URL:
http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

The latter.


Could this perhaps be made clearer on the page, perhaps with an example? It
confused me recently too.

Can you suggest something more specific?



Well, it could say something like:

   The Message-ID can be found in the headers of the relevant email to
   the pgsql-hackers mailing list, and also in the mailing list
   archives at http://archives.postgresql.org. It looks something like
   this (the format varies somewhat depending on the sender's Mail User
   Agent): aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

That would certainly have given me, and I suspect Gurjeet, enough clue.

cheers

andrew

Re: [HACKERS] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

[Alexander Korotkov]

I'll try to illustrate different approaches in examples.

  k  i  t  t  e  n
   0  1  2  3  4  5  6
s  1  1  2  3  4  5  6
i  2  2  1  2  3  4  5
t  3  3  2  1  2  3  4
t  4  4  3  2  1  2  3
i  5  5  4  3  2  2  3
n  6  6  5  4  3  3  2
g  7  7  6  5  4  4  3

The approach mentioned in Wikipedia limits filling of the matrix by
diagonals, which are in k-distance from main diagonal (diagonal which
contain left top cell). All other cell is guaranteed to have value greater
than k. This approach can be extended to the case of costs different from 1,
in this case limit diagonals will be closer to main diagonal proportional to
the costs. Here is example of such limited matrix with k = 3.

  k  i  t  t  e  n
   0  1  2  3
s  1  1  2  3  4
i  2  2  1  2  3  4
t  3  3  2  1  2  3  4
t 4  3  2  1  2  3
i4  3  2  2  3
n   4  3  3  2
g  4  4  3

The first idea of my approach is to use actual cell values to limit matrix
filling. For each row the range of columns where cell values are not greater
than k is stored. And in the next row only cells are caclucated, which use
values of cells from previous row in stored range. Here is example of this
approach with k = 3. There are slightly less filled cells but calculation
are more complicated than in previoud approach.

  k  i  t  t  e  n
   0  1  2  3
s  1  1  2  3
i  2  2  1  2  3
t  3  3  2  1  2  3
t3  2  1  2  3
i   3  2  2  3
n  3  3  2
g3

The second idea is to make values in matrix possible greater. I analyze what
exactly is matrix in this case. It is sum of original matrix, which
represent distances between prefixes of strings, and matrix, which represent
cost of insertions or deletions for moving to diagonal, which containing
bottom right cell. There is an example of second matrix summand:

  k  i  t  t  e  n
   1  2  3  4  5  6  7
s  0  1  2  3  4  5  6
i  1  0  1  2  3  4  5
t  2  1  0  1  2  3  4
t  3  2  1  0  1  2  3
i  4  3  2  1  0  1  2
n  5  4  3  2  1  0  1
g  6  5  4  3  2  1  0

And an example of resulting matrix:

  k  i  t  t  e  n
   1  3  5  7  9  11 13
s  1  2  4  6  8  10 12
i  3  2  2  4  6  8  10
t  5  4  2  2  4  6  8
t  7  6  4  2  2  4  6
i  9  8  6  4  2  3  5
n  11 10 8  6  4  3  3
g  13 12 10 8  6  5  3

The resulting matrix saves important property of original matrix, that cell
value always greater or equal than values, which are used for it's
calculation. That's why we can use idea about matrix filling limiting for
this matrix, but values in this matrix are greater and it allow to avoid
filling bigger part of matrix. There is an example of matrix filling
limiting for this matrix:

  k  i  t  t  e  n
   1  3
s  1  2
i 2  2
t2  2
t   2  2
i  2  3
n 3  3
g3


With best regards,
Alexander Korotkov.

Re: [HACKERS] gist access methods parameter types

[Marios Vodas]

>
> Have you looked at PostGIS?
>

Yes ofcourse, I also read everything in postgresql official documentation
plus http://gist.cs.berkeley.edu/pggist/opcltour.html.


> Yeah, I still don't think that's the right way to do it.  Storing the
> bounding box seems right, but just do that for all the nodes.  It's
> probably worth looking at the implementation of these functions for,
> say, the existing point, box, and polygon datatypes, which have
> similar issues.
>

I intend to change the structure I describe in the future. So if it is not
wrong and harmful to the implementation I want to keep that extra info in
leaf node entries.
I have read src/backend/access/gist/gistproc.c I am aware of what it does.

According to the documentation, no.  It takes a GISTENTRY and returns
> a GISTENTRY.  But you can extract the relevant key out of there.  The
> best way to do this, again, is to look at existing examples, like
> gist_poly_compress.
>

 Exactly what I am saying. I extract the key out of GISTENTRY of course but
that key has to contain a value of some type. And my question is of what
type if it is a leaf and of what if it is not a leaf entry?
 Thank you for helping.

Re: [HACKERS] Improving prep_buildtree used in VPATH builds

[Tom Lane]

Robert Haas  writes:
> Well, the historical set of topics varies from CommitFest to
> CommitFest, by design.  There are some that recur pretty regularly, of
> course, like Security, Performance, and Miscellaneous.  But not every
> CF will have a section for ECPG or Refactoring, for example.  In one
> CF, we may have six ECPG patches, so ECPG gets its own topic; in
> another CF, 1 ECPG patch + 2 libpq patches + 1 psql patch get merged
> together under a section called Interfaces.  This generally makes it
> easier to group things in ways that are useful in practice than a
> fixed list of topics, so I'm in favor of keeping it that way.

If it's intentional that the topic for the same patch might vary
depending on what else is submitted in the same CF, then I think that
asking submitters to select topics is the wrong thing from the get-go.
The patches should be uncategorized initially, and then someone like the
CF manager should group them into topics after-the-fact.

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] Improving prep_buildtree used in VPATH builds

[Kevin Grittner]

Tom Lane  wrote: 
 
> I liked the idea of pre-populating with the historical set of
> topics.
 
+1
 
-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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 10:54 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> Andrew's question seemed to be about the message-ID.  I agree the
>> topic thing is confusing, though.  I'm wondering if it would be
>> sufficient to do the following - if no topic are available, instead of
>> showing the form, it says something like:
>
>> No topics have been created for this CommitFest yet.  Before adding
>> your patch, you must add one or more items to the topic
>> list.
>
> I liked the idea of pre-populating with the historical set of topics.
> If you encourage the first few submitters to a new CF to invent their
> own topic categories without any guidance, you're going to get some
> crazy topics.

Well, the historical set of topics varies from CommitFest to
CommitFest, by design.  There are some that recur pretty regularly, of
course, like Security, Performance, and Miscellaneous.  But not every
CF will have a section for ECPG or Refactoring, for example.  In one
CF, we may have six ECPG patches, so ECPG gets its own topic; in
another CF, 1 ECPG patch + 2 libpq patches + 1 psql patch get merged
together under a section called Interfaces.  This generally makes it
easier to group things in ways that are useful in practice than a
fixed list of topics, so I'm in favor of keeping it that way.

This is surely a surmountable issue but the exact right thing to do is
not altogether obvious to me.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Large objects.

[Robert Haas]

On Mon, Sep 27, 2010 at 10:50 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> According to the documentation, the maximum size of a large object is
>> 2 GB, which may be the reason for this behavior.
>
> In principle, since pg_largeobject stores an integer pageno, we could
> support large objects of up to LOBLKSIZE * 2^31 bytes = 4TB without any
> incompatible change in on-disk format.  This'd require converting a lot
> of the internal LO access logic to track positions as int64 not int32,
> but now that we require platforms to have working int64 that's no big
> drawback.  The main practical problem is that the existing lo_seek and
> lo_tell APIs use int32 positions.  I'm not sure if there's any cleaner
> way to deal with that than to add "lo_seek64" and "lo_tell64" functions,
> and have the existing ones throw error if asked to deal with positions
> past 2^31.
>
> In the particular case here, I think that lo_write may actually be
> writing past the 2GB boundary, while the coding in lo_read is a bit
> different and stops at the 2GB "limit".

Ouch.  Letting people write data to where they can't get it back from
seems double-plus ungood.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] gist access methods parameter types

[Robert Haas]

On Mon, Sep 27, 2010 at 10:37 AM, Marios Vodas  wrote:
> Let me explain better what I want to do.
> I want to have the types in sql level (composite types) like this:
>
> --Spatio-Temporal Position in 3 Dimensions(cartessian x, cartessian y, time)

Have you looked at PostGIS?

> As you see the first is a replica of my sql type in C and the second misses
> some information (it is just the minimum bounding box which means that we
> don't know initial/ending positions neither the trajectory id). The second
> type is intended for non-leaf nodes while the first for leaf nodes.

Yeah, I still don't think that's the right way to do it.  Storing the
bounding box seems right, but just do that for all the nodes.  It's
probably worth looking at the implementation of these functions for,
say, the existing point, box, and polygon datatypes, which have
similar issues.

> My implementation of the tree is a kind of 3D-Rtree.
> Now I am a little confused about the type of the arguments that each access
> method (consistent, union, compress, decompress, penalty, picksplit, same)
> requires in order to accomplish my goal.
> Another black spot I have regarding compress.
> If the entry passed to compress is a leaf entry then it is going to be of
> sql type (composite type) delta3d (meaning I will get the values using
> tuples etc). Correct? If not of what type is it going to be?

According to the documentation, no.  It takes a GISTENTRY and returns
a GISTENTRY.  But you can extract the relevant key out of there.  The
best way to do this, again, is to look at existing examples, like
gist_poly_compress.

> Assume it is a non-leaf entry. In that case of what type will it be? If it
> is delta3d_mbb (C type) then I don't have to change it. But is it going to
> be?
> I am sorry if I seem importunate, but it has only been 2 weeks since I
> started messing with postgresql C extensions, and I need help...

If you need a moderate amount of help, you can probably get it the way
that you have been: asking questions.  If you need more help than
that, see http://www.postgresql.org/support/professional_support

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Tom Lane]

Robert Haas  writes:
> Andrew's question seemed to be about the message-ID.  I agree the
> topic thing is confusing, though.  I'm wondering if it would be
> sufficient to do the following - if no topic are available, instead of
> showing the form, it says something like:

> No topics have been created for this CommitFest yet.  Before adding
> your patch, you must add one or more items to the topic
> list.

I liked the idea of pre-populating with the historical set of topics.
If you encourage the first few submitters to a new CF to invent their
own topic categories without any guidance, you're going to get some
crazy topics.

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] Large objects.

[Tom Lane]

Robert Haas  writes:
> According to the documentation, the maximum size of a large object is
> 2 GB, which may be the reason for this behavior.

In principle, since pg_largeobject stores an integer pageno, we could
support large objects of up to LOBLKSIZE * 2^31 bytes = 4TB without any
incompatible change in on-disk format.  This'd require converting a lot
of the internal LO access logic to track positions as int64 not int32,
but now that we require platforms to have working int64 that's no big
drawback.  The main practical problem is that the existing lo_seek and
lo_tell APIs use int32 positions.  I'm not sure if there's any cleaner
way to deal with that than to add "lo_seek64" and "lo_tell64" functions,
and have the existing ones throw error if asked to deal with positions
past 2^31.

In the particular case here, I think that lo_write may actually be
writing past the 2GB boundary, while the coding in lo_read is a bit
different and stops at the 2GB "limit".

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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 10:18 AM, Gurjeet Singh  wrote:
> On Mon, Sep 27, 2010 at 4:15 PM, Andrew Dunstan  wrote:
>>
>>
>> On 09/27/2010 10:11 AM, Robert Haas wrote:
>>>
 What should be the value of 'Message-ID for original patch' ?
 the URL:
 http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
 or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>>>
>>> The latter.
>>>
>>
>> Could this perhaps be made clearer on the page, perhaps with an example?
>> It confused me recently too.
>>
>
> Or maybe populate the drop-down with every available topic from previous
> CFs, and add an additional '(Add new topic)' to the drop-down which would
> take you to topic creation page.

Andrew's question seemed to be about the message-ID.  I agree the
topic thing is confusing, though.  I'm wondering if it would be
sufficient to do the following - if no topic are available, instead of
showing the form, it says something like:

No topics have been created for this CommitFest yet.  Before adding
your patch, you must add one or more items to the topic
list.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Heads up: upcoming back-branch releases

[Tom Lane]

After some back-and-forth among core and -packagers, we've agreed that
we're overdue for update releases for the back branches.  Accordingly,
we'll be wrapping update tarballs of all active branches on Thursday
for public announcement Monday Oct 4.

We'll include a 9.0.1 update in this, so as to catch up on the early
bug reports for 9.0.

Please note that this will be the last community-supported update for
the 7.4.x and 8.0.x release branches, and probably the next-to-last
update for 8.1.x.  It's more than time to be upgrading away from those
branches if you're still using them.

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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 10:15 AM, Andrew Dunstan  wrote:
>
>
> On 09/27/2010 10:11 AM, Robert Haas wrote:
>>
>>> What should be the value of 'Message-ID for original patch' ?
>>> the URL:
>>> http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
>>> or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>>
>> The latter.
>>
>
> Could this perhaps be made clearer on the page, perhaps with an example? It
> confused me recently too.

Can you suggest something more specific?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] gist access methods parameter types

[Marios Vodas]

Let me explain better what I want to do.
I want to have the types in sql level (composite types) like this:

--Spatio-Temporal Position in 3 Dimensions(cartessian x, cartessian y, time)
CREATE TYPE pos3d AS
(
  x double precision,
  y double precision,
  t timestamp
);

--Spatio-Temporal Delta (comes from Δ, meaning alteration/change)
  --i stands for (i)nitial position
  --e stands for (e)nding position
  --trajectory holds the id of the trajectory in which the current delta
belongs to
CREATE TYPE delta3d AS
(
  i pos3d,
  e pos3d,
  trajectory integer
);

Having them in sql level eases my work much more than having them as base
types in C. But I need an index to speed up some operations. So I build two
types in C just for indexing purposes:

typedef struct {
float8 xi, yi;
Timestamp ti;
float8 xe, ye;
Timestamp te;
int32 trajectory;
} delta3d;

typedef struct {
float8 xl, yl;
Timestamp tl;
float8 xh, yh;
Timestamp th;
} delta3d_mbb;


As you see the first is a replica of my sql type in C and the second misses
some information (it is just the minimum bounding box which means that we
don't know initial/ending positions neither the trajectory id). The second
type is intended for non-leaf nodes while the first for leaf nodes.
My implementation of the tree is a kind of 3D-Rtree.
Now I am a little confused about the type of the arguments that each access
method (consistent, union, compress, decompress, penalty, picksplit, same)
requires in order to accomplish my goal.
Another black spot I have regarding compress.
If the entry passed to compress is a leaf entry then it is going to be of
sql type (composite type) delta3d (meaning I will get the values using
tuples etc). Correct? If not of what type is it going to be?
Assume it is a non-leaf entry. In that case of what type will it be? If it
is delta3d_mbb (C type) then I don't have to change it. But is it going to
be?
I am sorry if I seem importunate, but it has only been 2 weeks since I
started messing with postgresql C extensions, and I need help...

Re: [HACKERS] Large objects.

[Robert Haas]

On Sun, Sep 26, 2010 at 12:21 PM, Dmitriy Igrishin  wrote:
> Yes, I am sure. I've tested it by test case in my original post.
> Do you can compile and reproduce it please?

I think the reason lo_read is returning 0 is because it's not reading
anything.  See attached test case, cleaned up a bit from yours and
with some error checks added.

According to the documentation, the maximum size of a large object is
2 GB, which may be the reason for this behavior.

http://www.postgresql.org/docs/9/static/lo-intro.html

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


d.c
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] patch: SQL/MED(FDW) DDL

[Shigeru HANADA]

Hi hackers,

On Mon, 27 Sep 2010 15:50:34 +0900
SAKAMOTO Masahiko  wrote:
> Right. In any case, I should clearify what this API could cover
> by this patch and what could not.
> # And also how far I and my collaborator can implement..
> 
> As Itagaki points out, we have two types of FDW implementations in
> progress: flat file wrapper and postgresql wrapper. these items are
> described in wiki:
>   http://wiki.postgresql.org/wiki/SQL/MED
> 
> But it may be hard to get what this fdw routines 'could' provide,
> so I and my collaborator is now summarizing these items to discuss and
> ask for help. Anyway I think these items are needed to discuss further.
>   - API design that is used in executor for a single external table.
>   - simple FDW implements to clearfy what this API could provide.

To discuss how the SQL/MED patches should be, we made summary of
current SQL/MED proposal.

* What the current SQL/MED patches provide

Recent patch fdw_select20100917 consists of some parts: I/F of
FDW, new executor node ForeignScan, connection caching, and FDW
for PostgreSQL.  FDW for flat file is not included.  All of them
are based on DDLs and catalogs which are provided by
fdw_table20100917 patch.

Itagaki has summarized the details of fdw_table20100917 patch
well.  Thanks for review.
http://archives.postgresql.org/pgsql-hackers/2010-09/msg01653.php

The I/F of FDW is defined as struct FdwRoutine, set of API
functions which are called from backend modules.  The APIs are
designed to support only scanning a foreign at once.  Path/Plan
optimizations like JOIN/UNION push-down are out of scope.  Such
optimizations require planner hook as mentioned by Itagaki before. 
In short, our current goal is to implement SeqScan for foreign
tables.

ForeignScan node is an executor node which is like SeqScan node
for local table.  Optimizer generates T_ForeignScan path instead
of T_SeqScan path for a foreign table in set_plain_rel_pathlist()
if the RangeTblEntry was a foreign table.  Index paths and
tidscan paths are never generated for foreign tables.

ForeignScanState and FdwReply are introduced to represent the
status of a foreign scan.  ForeignScanState is a subclass of
ScanState, and FdwReply is a abstract type which is used to pass
FDW-specific data between API calls.

* Details of FDW API

FDWs should implement HANDLER function which returns a pointer to
a FdwRoutine instance which has pointers to actual functions.

struct FdRoutine {

/*
 * ConnectServer() will be called from ExecInitForeignScan()
 * if the backend has no connection which can be used to
 * execute the foreign query for the foreign table.
 * FDW should establish a connection between foreign server
 * and return it with casting to pointer to FSConnection
 * (abstract connection type).  If the FDW doesn't need any
 * connection, returning NULL is OK.
 * The arguments, server and user, can be used to extract
 * connection information.
 */
FSConnection* (*ConnectServer)(ForeignServer *server,
   UserMapping *user);

/*
 * FreeFSConnection() will be called when backend dies or
 * DISCARD ALL command was executed.
 * FDW should close connection gracefully and free resources
 * if any.
 */
void (*FreeFSConnection)(FSConnection *conn);

/*
 * Open() will be called from ExecInitForeignScan().
 * FDW should initialize ForeignScanState, internal state of
 * a foreign scan, and ready to return tuple in next Iterate()
 * call.
 * For instance, FDW for PostgreSQL only generate SQL from
 * ScanState.  And we implemented WHERE clause push-down here.
 */
void (*Open)(ForeignScanState *scanstate);

/*
 * Iterate() will be called from ExecForeignScan() when the
 * executor requests next tuple.  
 * For instance, FDW for PostgreSQL executes foreign query at
 * first call and stores all results into TupleStore, and
 * returns each tuple for each Iterate() call.
 */
void (*Iterate)(ForeignScanState *scanstate);

/*
 * Close() will be called from ExecEndForeignScan().
 * FDW should free resources for FdwReply if any.
 */
void (*Close)(ForeignScanState *scanstate);

/*
 * ReOpen() will be called from ExecForeignReScan() when the
 * foreign scan should be reseted to scan whole data from the
 * head again.
 * For instance, FDW for PostgreSQL frees current result set
 * to make next Iterate() call to execute foreign query again.
 */
void (*ReOpen)(ForeignScanState *scanstate);

};

Maybe FdwRoutine should have more APIs to support essential
features.

The startup/total cost of scanning a foreign table are fixed to
1.0 in current patch.  They are groundless values, they just
say that scanning foreign table costs more than scanning local
table.  The cost should be estimated from statistics in
pg_statistic and pg_class.  This function has not been
implemented yet

Re: [HACKERS] Improving prep_buildtree used in VPATH builds

[Gurjeet Singh]

On Mon, Sep 27, 2010 at 4:15 PM, Andrew Dunstan  wrote:

>
>
> On 09/27/2010 10:11 AM, Robert Haas wrote:
>
>>
>>  What should be the value of 'Message-ID for original patch' ?
>>> the URL:
>>> http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
>>> or the ID: 
>>> aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com
>>>
>> The latter.
>>
>>
> Could this perhaps be made clearer on the page, perhaps with an example? It
> confused me recently too.
>
>
Or maybe populate the drop-down with every available topic from previous
CFs, and add an additional '(Add new topic)' to the drop-down which would
take you to topic creation page.

Regards,
-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [HACKERS] Improving prep_buildtree used in VPATH builds

[Andrew Dunstan]

On 09/27/2010 10:11 AM, Robert Haas wrote:



What should be the value of 'Message-ID for original patch' ?
the URL: http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

The latter.



Could this perhaps be made clearer on the page, perhaps with an example? 
It confused me recently too.


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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Mon, Sep 27, 2010 at 10:09 AM, Gurjeet Singh  wrote:
> On Mon, Sep 27, 2010 at 3:02 PM, Robert Haas  wrote:
>>
>> On Sun, Sep 26, 2010 at 10:15 PM, Gurjeet Singh 
>> wrote:
>> > I have been away from Postgres development for quite a while, so would
>> > appreciate if someone could tell me if such a patch should be submitted
>> > for
>> > commitfest (since this is not actually a source patch).
>>
>> By all means add it to the open CF.
>>
>> https://commitfest.postgresql.org/action/commitfest_view/open
>>
>
> When trying to submit new patch, the 'CommitFest' drop-down has just one
> entry '(None Selected)', and 'Submit' would refuse to go through without a
> topic.

Oh, blah.  I just added a Miscellaneous topic.  You can add others
yourself, just look on the CF page for "CommitFest topics".

> What should be the value of 'Message-ID for original patch' ?
> the URL: http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
> or the ID: aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

The latter.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] forming tuple as an attribute inside another tuple in c function

[Robert Haas]

On Mon, Sep 27, 2010 at 2:52 AM, Marios Vodas  wrote:
> OK but what is the recommended way to get TupleDesc for p_type?

It's mentioned in the documentation...

http://www.postgresql.org/docs/current/static/xfunc-c.html#AEN47214

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Gurjeet Singh]

On Mon, Sep 27, 2010 at 3:02 PM, Robert Haas  wrote:

> On Sun, Sep 26, 2010 at 10:15 PM, Gurjeet Singh 
> wrote:
> > I have been away from Postgres development for quite a while, so would
> > appreciate if someone could tell me if such a patch should be submitted
> for
> > commitfest (since this is not actually a source patch).
>
> By all means add it to the open CF.
>
> https://commitfest.postgresql.org/action/commitfest_view/open
>
>
When trying to submit new patch, the 'CommitFest' drop-down has just one
entry '(None Selected)', and 'Submit' would refuse to go through without a
topic.

What should be the value of 'Message-ID for original patch' ?
the URL: http://archives.postgresql.org/pgsql-hackers/2010-09/msg01837.php
or the ID: 
aanlktinw0hl+jqmrtwxc9y2tqhcfhfgfekxyyfygv...@mail.gmail.com

Regards,
-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [HACKERS] gist access methods parameter types

[Dimitri Fontaine]

Marios Vodas  writes:
> I am implementing consistent, union, compress, decompress, penalty,
> picksplit and same.
[…]
> The problem is that some of these methods take as input parameters
> the d_type and some the struct type that I internally implemented in
> c (which will be saved to the tree).

That shouldn't be a problem as you're going to implement the functions
in C too, I guess. After all an index is all about performances.

  http://www.postgresql.org/docs/current/static/gist-implementation.html
  http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf

Please not that the documentation here has been true in 8.3 too but
didn't get backpatched there. Maybe we should consider?

> If I understand correctly consistent and compress are the only
> functions that will have input parameter of d_type. The others will
> have my c internal type.
> Is this correct?

Well how is your C coded type different from d_type and why?

> Something else, will a non-leaf node have one entry that will be
> produced by union? I am asking because I want the leaf node entries
> to be of different type from non-leaf node entries (the difference
> between them is that non-leaf entry will not keep the id attribute).

Non-leaf nodes will contain a page full of entries all consistent with
each-other and sharing a common union. You can have leaf nodes entries
of a different type with the STORAGE option of the CREATE OPERATOR CLASS
command here, and the compress() and decompress() methods:

  http://www.postgresql.org/docs/current/static/sql-createopclass.html

Then you have to take care about that in several of the functions in the
GiST API, in particular in consistent, see the GIST_LEAF(entry) macro.

Your main source of documentation at this point lies in the source of
the different GiST implementations, see ip4r, period, prefix and some
more.

Regards,
-- 
dim

-- 
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] A small update for postgresql.conf.sample

[Robert Haas]

2010/9/27 Devrim GÜNDÜZ :
> On Mon, 2010-09-27 at 09:40 -0400, Robert Haas wrote:
>> > Actually, I don't see any reason why not to backpatch it.
>>
>> I was wondering if it would cause package management headaches for
>> people who had already modified their postgresql.conf.
>
> We don't overwrite .conf files during upgrades.

All right, have it your way.  Done.  :-)

(Dang this is a lot easier than the old way.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] A small update for postgresql.conf.sample

[Devrim GÜNDÜZ]

On Mon, 2010-09-27 at 09:40 -0400, Robert Haas wrote:
> > Actually, I don't see any reason why not to backpatch it.
> 
> I was wondering if it would cause package management headaches for
> people who had already modified their postgresql.conf.

We don't overwrite .conf files during upgrades.
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Patch: Extend NOT NULL representation to pg_constraint

[Tom Lane]

Bernd Helmle  writes:
> What i can try is to record the inheritance information only in case of 
> attinhcount > 0. This would make maintenance of the pg_constraint records 
> for NOT NULL columns a little complicater though. Another thing we should 
> consider is that Peter's functional dependency patch was supposed to rely 
> on this feature (1), once it gets done. Not sure this still holds true

Oh, right, that's a killer argument.  Finishing that patch still
requires that NOT NULL constraints have pg_constraint OIDs assigned,
which means they *have to* have pg_constraint rows to carry the OIDs.
So forget the whole thing; we'll just eat the space penalty.

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] A small update for postgresql.conf.sample

[Robert Haas]

2010/9/27 Guillaume Lelarge :
> Le 27/09/2010 15:18, Robert Haas a écrit :
>> 2010/9/27 Devrim GÜNDÜZ :
>>> Attached is a small patch that adds a few comments for the settings that
>>> require restart. Applicable for 9.0+.
>>
>> I'm not sure this is worth back-patching, but I've committed it to the
>> master branch.
>>
>
> +1 for backpatching.
>
> Otherwise, the fact that "requires restart" is not here doesn't mean
> anything (ie, doesn't mean if restart is required or not).
>
> Actually, I don't see any reason why not to backpatch it.

I was wondering if it would cause package management headaches for
people who had already modified their postgresql.conf.

No?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] gist access methods parameter types

[Robert Haas]

On Mon, Sep 27, 2010 at 4:26 AM, Marios Vodas  wrote:
> The problem is that some of these methods take as input parameters the
> d_type and some the struct type that I internally implemented in c (which
> will be saved to the tree).
> If I understand correctly consistent and compress are the only functions
> that will have input parameter of d_type. The others will have my c internal
> type.
> Is this correct?

It looks to me like you need to read the documentation on this topic.

http://www.postgresql.org/docs/current/static/gist-implementation.html

From what I can gather from said documentation, consistent will indeed
get the data type as an argument, but compress does not.

You might also want to look at contrib/btree_gist.

> Something else, will a non-leaf node have one entry that will be produced by
> union?

I believe that's correct.

> I am asking because I want the leaf node entries to be of different
> type from non-leaf node entries (the difference between them is that
> non-leaf entry will not keep the id attribute).
> Thank you in advance.

I don't think this is a good idea.  I suspect you want to keep the id
attribute never, and use the recheck stuff.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] A small update for postgresql.conf.sample

[Guillaume Lelarge]

Le 27/09/2010 15:18, Robert Haas a écrit :
> 2010/9/27 Devrim GÜNDÜZ :
>> Attached is a small patch that adds a few comments for the settings that
>> require restart. Applicable for 9.0+.
> 
> I'm not sure this is worth back-patching, but I've committed it to the
> master branch.
> 

+1 for backpatching.

Otherwise, the fact that "requires restart" is not here doesn't mean
anything (ie, doesn't mean if restart is required or not).

Actually, I don't see any reason why not to backpatch it.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.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] A small update for postgresql.conf.sample

[Robert Haas]

2010/9/27 Devrim GÜNDÜZ :
> Attached is a small patch that adds a few comments for the settings that
> require restart. Applicable for 9.0+.

I'm not sure this is worth back-patching, but I've committed it to the
master branch.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Improving prep_buildtree used in VPATH builds

[Robert Haas]

On Sun, Sep 26, 2010 at 10:15 PM, Gurjeet Singh  wrote:
> I have been away from Postgres development for quite a while, so would
> appreciate if someone could tell me if such a patch should be submitted for
> commitfest (since this is not actually a source patch).

By all means add it to the open CF.

https://commitfest.postgresql.org/action/commitfest_view/open

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

[Dave Page]

On Thu, Sep 9, 2010 at 9:09 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> It's hard to say what the safest option is, I think.  There seem to be
>> basically three proposals on the table:
>
>> 1. Back-port the dead-man switch, and ignore exit 128.
>> 2. Don't back-port the dead-man switch, but ignore exit 128 anyway.
>> 3. Revert to Magnus's original solution.
>
>> Each of these has advantages and disadvantages.  The advantage of #1
>> is that it is safer than #2, and that is usually something we prize
>> fairly highly.  The disadvantage of #1 is that it involves
>> back-porting the dead-man switch, but on the flip side that code has
>> been out in the field for over a year now in 8.4, and AFAIK we haven't
>> any trouble with it.  Solution #3 should be approximately as safe as
>> solution #1, and has the advantage of touching less code in the back
>> branches, but on the other hand it is also NEW code.  So I think it's
>> arguable which is the best solution.  I think I like option #2 least
>> as among those choices, but it's a tough call.
>
> Well, I don't want to use Magnus' original solution in 8.4 or up,
> so I don't like #3 much: it's not only new code but code which would
> get very limited testing.  And I don't believe that the risk of
> unexpected use of exit(128) is large enough to make #1 preferable to #2.
> YMMV.

So, can we go with #2 for the next point releases of <= 8.3? I
understand that our customer who has been testing that approach hasn't
seen any unexpected side-effects.

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres 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] Git migration deadline for Buildfarm

[Andrew Dunstan]

In about a week I propose to have the buildfarm server start to reject 
results based on snapshots where the newest file is older than the time 
of the git migration a week ago. That means buildfarm owners need to 
upgrade to using git. Many have already: see 



The move to git has been a long advertised change, and the software 
updates have been available for months. Just to be clear, here are the 
steps involved in upgrading a buildfarm client:


   * install git on your machine if it's not there already - latest
 version is best, but most moderately modern versions should do.
   * upgrade to the latest release of the buildfarm code (release 4.1)
 See 
   * remove the repo copies in your buildroot ("rm -rf
 /path/to/buildroot/*/pgsql")
   * update the buildfarm.conf. At a minimum, remove the settings for
 cvsmethod and cvsrepo and add setting "scm => 'git'". But you
 probably want a local git mirror rather than a separate clone for
 each branch. See
  for
 more details

That's not exactly hard, on most platforms.

If you think you deserve a dispensation, please ask me, but I really 
don't want the buildfarm server polluted with new builds of frozen code. 
That doesn't help anyone - it's just useless noise.



cheers

andrew

Re: [HACKERS] psql's \dn versus temp schemas

[Peter Eisentraut]

On fre, 2010-09-24 at 14:42 -0400, Tom Lane wrote:
> How do we want to define "system" exactly?  My original proposal was
> for bare \dn to hide the temp and toast schemas.  If we consider that
> what it's hiding is "system" schemas then there's some merit to the
> idea that it should hide pg_catalog and information_schema too.
> In that case, in a fresh database you would *only* see "public".
> I'm not sure that I like this though.  Comments?

I think that is sensible.


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

[HACKERS] Using streaming replication as log archiving

[Magnus Hagander]

As has been previously mentioned a couple of times, it should be
perfectly possible to use streaming replication to get around the
limitations of archive_command/archive_timeout to do log archiving for
PITR (being that you either keep archive_timeout high and risk data
loss or you set it very low and generate a huge log archive without
need).

I've put together a tool to do this. The basic idea is to just stream
down replication and write it to regular WAL files, which can then be
used for recovery. You'll still need to use archive_command together
with it to ensure that the backups are complete. Streaming replication
doesn't guarantee that - in fact, regular replication will fallback to
using whatever archive_command created when wal_keep_segments isn't
enough.

I've put up an early version of the tool at
http://github.com/mhagander/pg_streamrecv

Comments and contributions are most welcome. And frankly, a good
review is very much required before I'd trust it ;) Hopefully, I
didn't overlook something critical :D

-- 
 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] trailing whitespace in psql table output

[Peter Eisentraut]

On fre, 2010-09-24 at 22:38 +0100, Roger Leigh wrote:
> On Tue, Sep 21, 2010 at 09:28:07PM +0300, Peter Eisentraut wrote:
> > Everyone using git diff in color mode will already or soon be aware that
> > psql, for what I can only think is an implementation oversight, produces
> > trailing whitespace in the table headers, like this:
> > 
> >  two | f1 $
> > -+$
> >  | asdfghjkl;$
> >  | d34aaasdf$
> > (2 rows)$
> 
> Does this break the output with "\pset border 2"?

Um, no.

In the meantime, I have arrived at the conclusion that doing this isn't
worth it because it will break all regression test output.  We can fix
the stuff in our tree, but pg_regress is also used externally, and those
guys would have a nightmare with this change.  Perhaps if there is
another more significant revision of the table style in the future, we
should keep this issue in mind.


-- 
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] TODO: You can alter it, but you can't view it

[Bernd Helmle]

--On 27. September 2010 16:54:32 +0900 Itagaki Takahiro 
 wrote:



I found it in 8.4 and newer versions. It might be an internal API
(for pg_dump?), but it'd be better to add documentation for it.


Additionally we could extend pg_tables with an additional column? This 
would make the query more user-friendly, too.


--
Thanks

Bernd

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

[HACKERS] A small update for postgresql.conf.sample

[Devrim GÜNDÜZ]

Attached is a small patch that adds a few comments for the settings that
require restart. Applicable for 9.0+.

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index d31f1a1..b6a8f91 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -151,6 +151,7 @@
 # - Settings -
 
 #wal_level = minimal			# minimal, archive, or hot_standby
+	# (change requires restart)
 #fsync = on# turns forced synchronization on or off
 #synchronous_commit = on		# immediate fsync at commit
 #wal_sync_method = fsync		# the default is the first option 
@@ -186,6 +187,7 @@
 # - Streaming Replication -
 
 #max_wal_senders = 0		# max number of walsender processes
+# (change requires restart)
 #wal_sender_delay = 200ms	# walsender cycle time, 1-1 milliseconds
 #wal_keep_segments = 0		# in logfile segments, 16MB each; 0 disables
 #vacuum_defer_cleanup_age = 0	# number of xacts by which cleanup is delayed
@@ -193,6 +195,7 @@
 # - Standby Servers -
 
 #hot_standby = off			# "on" allows queries during recovery
+	# (change requires restart)
 #max_standby_archive_delay = 30s	# max delay before canceling queries
 	# when reading WAL from archive;
 	# -1 allows indefinite delay
@@ -392,7 +395,7 @@
 #track_activities = on
 #track_counts = on
 #track_functions = none			# none, pl, all
-#track_activity_query_size = 1024
+#track_activity_query_size = 1024 	# (change requires restart)
 #update_process_title = on
 #stats_temp_directory = 'pg_stat_tmp'
 
@@ -416,6 +419,7 @@
 	# actions running at least this number
 	# of milliseconds.
 #autovacuum_max_workers = 3		# max number of autovacuum subprocesses
+	# (change requires restart)
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 	# vacuum


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Patch: Extend NOT NULL representation to pg_constraint

[Bernd Helmle]

--On 26. September 2010 15:50:06 -0400 Tom Lane  wrote:


I think his question was - how do we feel about the massive catalog
bloat this patch will create?


It's a fair question.

I can imagine designing things so that we don't create an explicit
pg_constraint row for the simplest case of an unnamed, non-inherited
NOT NULL constraint.  Seems like it would complicate matters quite
a lot though, in exchange for saving what in the end isn't an enormous
amount of space.


What i can try is to record the inheritance information only in case of 
attinhcount > 0. This would make maintenance of the pg_constraint records 
for NOT NULL columns a little complicater though. Another thing we should 
consider is that Peter's functional dependency patch was supposed to rely 
on this feature (1), once it gets done. Not sure this still holds true


1) 



--
Thanks

Bernd

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

[HACKERS] gist access methods parameter types

[Marios Vodas]

If I have this sql composite type:

CREATE TYPE d_type AS
(

i integer,

e integer,

id integer

);

and this table:

CREATE TABLE my_tab
(
   d_col d_type NOT NULL
)


CREATE INDEX my_tab_d_col_gist ON my_tab USING gist (d_col);


I am implementing consistent, union, compress, decompress, penalty,
picksplit and same.
CREATE OPERATOR CLASS d_type_ops
DEFAULT FOR TYPE d_type USING gist AS
FUNCTION 1 d_type_consistent(internal, d_type, smallint, oid, internal),
 FUNCTION 2 d_type_union(internal, internal),
FUNCTION 3 d_type_compress(internal),
 FUNCTION 4 d_type_decompress(internal),
FUNCTION 5 d_type_penalty(internal, internal, internal),
 FUNCTION 6 d_type_picksplit(internal, internal),
FUNCTION 7 d_type_same(internal, internal, internal);

The problem is that some of these methods take as input parameters the
d_type and some the struct type that I internally implemented in c (which
will be saved to the tree).
If I understand correctly consistent and compress are the only functions
that will have input parameter of d_type. The others will have my c internal
type.
*Is this correct?*
Something else, will a non-leaf node have one entry that will be produced by
union? I am asking because I want the leaf node entries to be of different
type from non-leaf node entries (the difference between them is that
non-leaf entry will not keep the id attribute).
Thank you in advance.

Re: [HACKERS] do we want to gitignore regression-test-failure files?

[Dimitri Fontaine]

Tom Lane  writes:
> I don't find it indecipherable.  We're ignoring stuff that can be
> expected to be present after a normal build and successful "make
> check" or "make installcheck".  As soon as we ignore more than that,
> I'm going to insist on ignoring *~ ... do you want to open that can
> of worms?

Couldn't resist to note that $EDITOR might make it easy to divert the
backup files elsewhere (out-of-tree). For example:

  (setq backup-directory-alist '((".*" . "~/.emacs.d/backups/")))

Regards,
-- 
dim

-- 
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] [COMMITTERS] pgsql: Still more tweaking of git_changelog.

[Dimitri Fontaine]

Tom Lane  writes:
> Author: Tom Lane 
> Branch: master Release: REL8_1 [872c1497f] 2005-05-24 18:02:31 +
> Branch: REL8_0_STABLE Release: REL8_0_4 [a94ace079] 2005-05-24 18:02:55 +
> Branch: REL7_4_STABLE Release: REL7_4_9 [0a7b3a364] 2005-05-24 18:03:24 +
>
> Previous fix for "x FULL JOIN y ON true" failed to handle the case
> where there was also a WHERE-clause restriction that applied to the
> join.  The check on restrictlist == NIL is really unnecessary anyway,
> because select_mergejoin_clauses already checked for and complained
> about any unmergejoinable join clauses.  So just take it out.

It's easy enough to find it on the website too, or to search for some
other patch as I did this morning:

  http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=872c1497f
  
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=40608e7f949fb7e4025c0ddd5be01939adc79eec

Having your proposed output in there would be awesome, because the use
case of determining which releases contain the patch is a *huge* one of
course --- and gitweb searches are easy, fast and convenient.

Now I can't comment on the git_changelog tool chapter, so that's a
little Off Topic, but still. Can we do something about the gitweb
interface to include such a feature?

Regards,
-- 
dim

-- 
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] recovery.conf location

[Magnus Hagander]

On Mon, Sep 27, 2010 at 08:34, Fujii Masao  wrote:
> On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova  wrote:
>> Maybe i'm missing something but this would be a problem if we put a
>> trigger file and the recovery.conf gets renamed to recovery.done, no?
>> at least that would be a problem for the standbys that still need to
>> be standbys
>
> That's not problem unless more than one standbys become master at the
> same time. Since recovery.conf is read by standby only at the start unless
> it's triggered, already-started standbys can work even if recovery.conf is
> renamed to recovery.done. Though it's somewhat tricky..

Uh, what if the slave is restarted for one reason or another? That
seems like it would be really fragile..

-- 
 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] recovery.conf location

[Magnus Hagander]

On Mon, Sep 27, 2010 at 08:52, Fujii Masao  wrote:
> On Mon, Sep 27, 2010 at 10:55 AM, Robert Haas  wrote:
>> Again, I think the real question is how to handle values that need to
>> be maintained PER SLAVE from values of which there is only one copy.
>
> Yep. One idea is to support something like "pg_ctl standby" and "pg_ctl pitr".
> If we do so, we would be able to get rid of standby_mode from recovery.conf,
> and move the others to postgresql.conf or elsewhere.

That wouldn't really help, would it? You'd just push the problem out
to the initscript that would have to keep track of which mode to go up
in, etc. And then have to put the logic right back in pg_ctl for
Windows where there is no initscript...


-- 
 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] security label support, revised

[KaiGai Kohei]

(2010/09/27 11:49), Robert Haas wrote:

On Sat, Sep 25, 2010 at 7:04 AM, KaiGai Kohei  wrote:

* The "dummy_esp" module and regression test for SECURITY LABEL statement.
  This module allows only four labels: "unclassified", "classified",
  "secret" and "top secret". The later two labels can be set by only
  superusers. The new regression test uses this "dummy_esp" module to
  find out future regression in SECURITY LABEL statement.
* A minimum description about external security provider at the tail
  of Database Roles and Privileges  chapter.
* Add pg_seclabels system view
* Revising pg_dump/pg_dumpall
  - '--security-label' was replaced by '--no-security-label'
  - implemented according to the manner in comments.
findSecLabels() and collectSecLabels() are added to reduce number of
SQL queries, in addition to dumpSecLabel().


Thanks, this looks like mostly good stuff.  Here's a new version of
the patch with some bug fixes, additional regression tests, and other
cleanup.  I think this is about ready to commit.


Thanks for your reviewing and cleaning-up.


I didn't adopt your
documentation and I renamed your contrib module from dummy_esp to
dummy_seclabel, but the rest I took more or less as you had it.


Fair enough. I intended the name of "dummy_esp" to host any other
upcoming regression tests corresponding to security hooks, but
right now it just provides dummy labels.


For
now, I don't want to use the term "external security provider" because
that's not really what this provides - it just provides labels.  I
initially thought that an external security provider would really turn
out to be a concept that was somewhat embedded in the system, but on
further reflection I don't think that's the case.  I think what we're
going to end up with is a collection of hooks that might happen to be
useful for security-related things, but not necessarily just those.


Right, the "security provider" plugin which uses the collection of
security hooks will provides access control decision, but we don't
force anything in the way to make decisions.
Someone may provide label based security, but other one may provide
non-label based security.
All we can say is that guest of the hook on SECURITY LABEL provides
security label, but it is unclear whether it also provides any access
control, or not.
I also think the "label provider" is a fair enough naming.


Anyway, I feel that it's a bit premature to document too much about
what this might do someday; the documentation already in the patch is
adequate to explain what it does now.


I agreed. It is quite internal stuff how security hooks should perform
on interactions with plugin modules, so it might be premature.

Thanks,
--
KaiGai Kohei 

--
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] bg worker: general purpose requirements

[Markus Wanner]

Greg,

On 09/25/2010 08:03 PM, Greg Stark wrote:
> The dynamic ramp-up is a feature to deal for the default install and
> for use case where the system has lots of different users with
> different needs.

Thanks for sharing this. From that perspective, neither the current
min/max nor the timeout configuration approach would be satisfying, as
it's not really possible to configure it to always have a certain amount
of bgworkers (exactly adjusted to the requirements at hand, with
possibly differing requirements per database).

I'm unsure about how to continue here. It seems we need the ability to
switch between databases not (only) for performance, but for ease of
configuration.

Regards

Markus Wanner

-- 
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] recovery.conf location

[Josh Berkus]

On 09/27/2010 10:08 AM, Robert Haas wrote:

The thing about the parameters for synchronous replication that is a
little different is that you need a whole set of parameters *for each
standby*.  There's not a terribly clean way to handle that in
postgresql.conf as it exists today, but getting any agreement on
non-trivial changes to postgresql.conf has proven to be next to
impossible, despite the fact that AFAICT approximately no one is happy
with the status quo.


Huh?  Since when?  I thought the whole "standby registration on the 
master" discussion was still ongoing.  And if we don't have a clean 
format to do that in postgresql.conf, we don't have one anywhere else, 
either.


As far as each standby is concerned, there's absolutely no reason not to 
have all the standby parameters in postgresql.conf.  The one and only 
thing we'd need to deal with is how the standby writes a bit to indicate 
that it has failed over and is no longer a standby ... probably the 
simplest idea is to simply leave the trigger file in place.


I am opposed to any solution to configuring sync rep which involves 
PostgreSQL having multiple configuration files in multiple different 
formats.  All of our configuration files should be in the exact same 
format, and ideally there should only be one configuration file.  There 
is no DBA or sysadmin on Earth who would appreciate having to edit one 
file in param=val format, and a second file in JSON or XML.


A good compromise with historical formats is to use Apache HTTPD's 
approach, which supports both simple param=val declarations, and also 
simple blocks.  Assuming we need to configure the standbys on the master 
at all, which has not yet been decided on this list AFAIK.


--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] TODO: You can alter it, but you can't view it

[Itagaki Takahiro]

On Mon, Sep 27, 2010 at 4:39 PM, Josh Berkus  wrote:
>> Can you use pg_options_to_table() for your purpose?
>
> Yes, thanks.  What version did that get added in?  Even for 9.0, that
> function doesn't seem to appear in the docs.

I found it in 8.4 and newer versions. It might be an internal API
(for pg_dump?), but it'd be better to add documentation for it.

-- 
Itagaki Takahiro

-- 
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] TODO: You can alter it, but you can't view it

[Josh Berkus]

Can you use pg_options_to_table() for your purpose?


Yes, thanks.  What version did that get added in?  Even for 9.0, that 
function doesn't seem to appear in the docs.


--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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