Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?

[Asko Oja]

Hi

One of reasons to get PL/proxy into core is to make it available to Windows
users also.
The idea is to get to the situation

createlang plproxy mydb

If we can achieve this without putting plproxy into core then i would like
to hear how.

Asko

On Fri, Jul 25, 2008 at 2:19 AM, Tom Lane [EMAIL PROTECTED] wrote:

 Robert Haas [EMAIL PROTECTED] writes:
  ISTM that if that if you're willing to admit, even with caveats, that
  PL/perl, PL/tcl, or PL/python doesn't need to be in core, then
  excluding anything else from core on the basis that it doesn't need to
  be there is silly.

 You are merely setting up a straw man, as no one has suggested such a
 policy.  Any specific decision of this type is going to involve a
 combination of factors, and that's only one.

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] Additional psql requirements

[Simon Riggs]

On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote:
 Simon Riggs [EMAIL PROTECTED] wrote:
 
  * access to version number
  * simple mechanism for conditional execution
  * ability to set substitution variables from command execution
  * conditional execution whether superuser or not
 
 Can we use pgScript for such flow controls?
 http://pgscript.projects.postgresql.org/INDEX.html
 
 I'm not sure pgScript can be used in pgAdmin already, but if we support
 it both psql and pgAdmin, the scripting syntax will be a defact standard
 because they are the most major user interfaces to postgres. I think it is
 not good to add another dialect that can be used only in psql.

I just want good way, not two imperfect ones.

And I'm not going to suggest having pgscript in core.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] pg_dump vs data-only dumps vs --disable-triggers

[Simon Riggs]

On Thu, 2008-07-24 at 19:11 -0400, Tom Lane wrote:
 There's some fairly squirrely logic in pg_dump/pg_restore that tries to
 detect whether it's doing a data-only operation, ie, no schema
 information is to be dumped or restored.  The reason it wants to
 know this is to decide whether to enable the --disable-triggers
 code.  However, since --disable-triggers is off by default and has
 to be manually requested, I'm not sure why we've got all this extra
 complexity in there.  (Actually, I'm sure the reason is that that
 code predates the existence of the --disable-triggers switch, but
 anyway...)
 
 Simon's patch to split up --schema-only into two switches has broken
 this logic, but I'm inclined to just rip it out rather than trying
 to fix it.  If the user says --disable-triggers, he should get
 trigger disable commands around the data part of the dump, no matter
 what he said or didn't say about schema dumping.

Agreed. Thanks for the clear thinking.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Uncopied parameters on CREATE TABLE LIKE

[Simon Riggs]

On Thu, 2008-07-24 at 11:41 -0400, Tom Lane wrote:

 Now, if you're suggesting we need a plugin hook somewhere in or around
 default_reloptions, that's possibly reasonable; but a GUC like you're
 suggesting seems quite pointless.

OK, I'll have a look, or perhaps Itagaki?

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] [PATCHES] GIN improvements

[Teodor Sigaev]

(a) that's not back-patchable and (b) it'll create a merge conflict with
your patch, if you're still going to add a new AM function column.
I think that aminsertcleanup per se isn't needed, but if we want an
amanalyze there'd still be a conflict.  Where are we on that?


I'll revert aminsertcleanup framework but leave gininsertcleanup function as is, 
because I'll not have enough time until end of summer - I'd like to finalize 
patch and fixes first.


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

--
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] Review: DTrace probes (merged version) ver_03

[Zdenek Kotala]

Theo Schlossnagle napsal(a):


On Jul 24, 2008, at 11:11 AM, Zdenek Kotala wrote:

I performed review and I prepared own patch which contains only probes 
without any issue. I suggest commit this patch because the rest of 
patch is independent and it can be committed next commit fest after 
rework.


I found following issues:

1) SLRU probes.

I think it is good to have probes there but they needs polish. See my 
comments

http://reviewdemo.postgresql.org/r/25/


The slru's are quite useful and general enough to use easily.  I used 
them to verify the metered checkpointing stuff:


http://lethargy.org/~jesus/archives/112-Probing-for-Success.html


I agree that SLRU probes are useful but I'm worry about implementation. I think 
that these probes need more work before commit. Currently there are several bugs 
in placement and arguments (from my point of view).



3) Executor probes

I would like to see any use case for them/


I added them with two thoughts (and knowing that they cost nothing).
(1) you can trace them to assist in debugging an explain plan and to 
better understand the flow of the execution engine.  This is not a 
compelling reason, but a reason none-the-less.
(2) you can trace and existing long-running query for which you do not 
have the original plan (may have changed) and make an educated guess at 
the plan chosen at time of execution.


I'm not executor expert and (1) is useful for me :-). What I'm thinking about is 
if we can mine more information from executor like number of tuples processed by 
node number and so on. I think that it needs discussion.



8) mark dirty and BM_HINT... flag

I remove these because I don't see any use case for it. It would be 
nice provide some dtrace script or describe basic ideas.



Perhaps I misunderstood what mark dirty does, but here was my thinking:

Because of the background writer, it is difficult to understand which 
postgres process (and thus query) induced disk writes.  Marking a page 
as dirty is a good indication that a query will be causing I/O and you 
can measure calls to mark dirty per query as a telling metric.


Perhaps I misunderstood, but I have a very serious problem that I can't 
reliably track write I/O to postgresql process ID as the bgwriter and 
the kernel are flushing those dirty blocks to disk while the process 
isn't running.  In my (albeit naive) tests, the mark dirty gave me quite 
expected results for correlating query execution to disk I/O to be induced.




If I understand correctly you need to analyze number of writes per 
query/session. It seems to me, that to use mark dirty is good way, but it 
probably needs more probes. (Robert L. any idea?)


However what I suggested is commit probes without issue now and the rest will be 
processed on the next commit fest after rework/discussion.


Zdenek

--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


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

Re: [HACKERS] Additional psql requirements

[daveg]

On Fri, Jul 25, 2008 at 08:16:59AM +0100, Simon Riggs wrote:
 
 On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote:
  Simon Riggs [EMAIL PROTECTED] wrote:
  
   * access to version number
   * simple mechanism for conditional execution
   * ability to set substitution variables from command execution
   * conditional execution whether superuser or not
  
  Can we use pgScript for such flow controls?
  http://pgscript.projects.postgresql.org/INDEX.html
  
  I'm not sure pgScript can be used in pgAdmin already, but if we support
  it both psql and pgAdmin, the scripting syntax will be a defact standard
  because they are the most major user interfaces to postgres. I think it is
  not good to add another dialect that can be used only in psql.
 
 I just want good way, not two imperfect ones.
 
 And I'm not going to suggest having pgscript in core.

It seems to me that a sql-like client side scripting language should be as
similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is
pretty much incompatible with it for no particularly obvious reason.

-dg

-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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] Additional psql requirements

[Pavel Stehule]

2008/7/25 Simon Riggs [EMAIL PROTECTED]:

 On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote:
 Simon Riggs [EMAIL PROTECTED] wrote:

  * access to version number
  * simple mechanism for conditional execution
  * ability to set substitution variables from command execution
  * conditional execution whether superuser or not

 Can we use pgScript for such flow controls?
 http://pgscript.projects.postgresql.org/INDEX.html

 I'm not sure pgScript can be used in pgAdmin already, but if we support
 it both psql and pgAdmin, the scripting syntax will be a defact standard
 because they are the most major user interfaces to postgres. I think it is
 not good to add another dialect that can be used only in psql.

 I just want good way, not two imperfect ones.

 And I'm not going to suggest having pgscript in core.
+ 1

pgScript is too heavy

for most purposes is enough some like

\for select * from information_schema.tables
grant read on $1 to public;
\endfor

regards
Pavel Stehule


 --
  Simon Riggs   www.2ndQuadrant.com
  PostgreSQL Training, Services and Support


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


-- 
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] pltcl_*mod commands are broken on Solaris 10

[Zdenek Kotala]

Tom Lane napsal(a):

Zdenek Kotala [EMAIL PROTECTED] writes:
I understand. However I have another dumb idea/question - It seems to me that it 
is client code. I think that it should be integrated into psql

command.


That doesn't seem like a particularly appropriate thing to do ... nor
do I see the argument for calling it client-side code.



I think that best thing at this moment is to add item to the TODO list about 
cleanup.


Zdenek


--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


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

Re: [HACKERS] Additional psql requirements

[Dave Page]

On Fri, Jul 25, 2008 at 8:52 AM, daveg [EMAIL PROTECTED] wrote:

 It seems to me that a sql-like client side scripting language should be as
 similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is
 pretty much incompatible with it for no particularly obvious reason.

pgScript originally used a c-like syntax when it was pgUnitTest iirc.
The new version is designed to be familiar to users of T-SQL.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [HACKERS] Additional psql requirements

[Pavel Stehule]

2008/7/25 Dave Page [EMAIL PROTECTED]:
 On Fri, Jul 25, 2008 at 8:52 AM, daveg [EMAIL PROTECTED] wrote:

 It seems to me that a sql-like client side scripting language should be as
 similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is
 pretty much incompatible with it for no particularly obvious reason.

 pgScript originally used a c-like syntax when it was pgUnitTest iirc.
 The new version is designed to be familiar to users of T-SQL.

it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and
it's far to plpgsql

regards
Pavel Stehule


 --
 Dave Page
 EnterpriseDB UK: http://www.enterprisedb.com

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


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

[HACKERS] Whence cometh the data in src/test/regress/data/streets.data ?

[Bjorn Munch]

OK, I may be in a nitpicking mood today. :-)

IANAL, but it's my responsibility to check that Sun won't be violating
any copyright or licencing terms when delivering PostgreSQL with
(Open)Solaris.

I am now working on adding the regression tests (gmake check) to the
8.3 packages integrated into OpenSolaris. While going through the list
of files I come across

  src/test/regress/data/streets.data

This file includes ~5000 test data entries which appear to be
geographical locations for end points of streets etc. in the San
Francisco Bay Area.

I don't think whoever made this has typed it all in, nor does it look
like random data, it almost certainly comes from a real data
source. Which means someone probably owns the copyright.

This file was checked in way back in July 1996, by Marc G. Fournier
but that doesn't mean he was the one who got the data from
somewhere. Does anyone know where it comes from? Or has this
information been lost in the mist of time?

If it's a US Goverment source, then it's in the public domain and
we[1] can freely use it. Otherwise, at least in theory, we may have a
problem.

[1] we can here be read as either PostgreSQL or Sun.

-- 
Bjorn Munch Sun Microsystems
Trondheim, Norway   http://sun.com/postgresql/

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

Re: [HACKERS] Additional psql requirements

[Simon Riggs]

On Fri, 2008-07-25 at 09:40 +0100, Dave Page wrote:
 On Fri, Jul 25, 2008 at 9:36 AM, Pavel Stehule [EMAIL PROTECTED] wrote:
  2008/7/25 Dave Page [EMAIL PROTECTED]:
  On Fri, Jul 25, 2008 at 8:52 AM, daveg [EMAIL PROTECTED] wrote:
 
  It seems to me that a sql-like client side scripting language should be as
  similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is
  pretty much incompatible with it for no particularly obvious reason.
 
  pgScript originally used a c-like syntax when it was pgUnitTest iirc.
  The new version is designed to be familiar to users of T-SQL.
 
  it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and
  it's far to plpgsql
 
 I see no point in replicating pl/pgsql. Better to implement anonymous
 blocks in the server for that.

Agreed. My suggestion was for something much simpler than either.
Complex logic can be done in functions. 

I just wanted an easy way to write install scripts that work on various
releases/schemas/environments, works on core and on any platform.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Uncopied parameters on CREATE TABLE LIKE

[ITAGAKI Takahiro]

Simon Riggs [EMAIL PROTECTED] wrote:

 On Thu, 2008-07-24 at 11:41 -0400, Tom Lane wrote:
 
  Now, if you're suggesting we need a plugin hook somewhere in or around
  default_reloptions, that's possibly reasonable; but a GUC like you're
  suggesting seems quite pointless.
 
 OK, I'll have a look, or perhaps Itagaki?

Yes, I agree, too. But my proposal is independent from such hooks :-)
I just suggested to copy reloptions as-is on CREATE TABLE LIKE.

I guess the first applicaitons using the extended reloptions are
user defined access methods for GiST and GIN. If those access methods
can receive reloptions, they can support their own parameters.

Regards,
---
ITAGAKI Takahiro
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] Additional psql requirements

[Dave Page]

On Fri, Jul 25, 2008 at 9:36 AM, Pavel Stehule [EMAIL PROTECTED] wrote:
 2008/7/25 Dave Page [EMAIL PROTECTED]:
 On Fri, Jul 25, 2008 at 8:52 AM, daveg [EMAIL PROTECTED] wrote:

 It seems to me that a sql-like client side scripting language should be as
 similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is
 pretty much incompatible with it for no particularly obvious reason.

 pgScript originally used a c-like syntax when it was pgUnitTest iirc.
 The new version is designed to be familiar to users of T-SQL.

 it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and
 it's far to plpgsql

I see no point in replicating pl/pgsql. Better to implement anonymous
blocks in the server for that.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: Column level privileges was:(Re: [HACKERS] Extending grant insert on tables to sequences)

[Stephen Frost]

* Jaime Casanova ([EMAIL PROTECTED]) wrote:
 ok, seems this is the last one for column level patch
 http://archives.postgresql.org/pgsql-patches/2008-04/msg00417.php
 
 any one working it...

Yes, I'm working on it, but I'm not against having help, of course.  The
past couple weeks have been given over to commitfest though, so I havn't
made much progress on it yet.  My plan is to focus on it during August
and have a good patch to submit for the September commitfest.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] [RFC] Unsigned integer support.

[Gregory Stark]

Ryan Bradetich [EMAIL PROTECTED] writes:

 My plans for the example above would be:

   1. SELECT 15 + 15  -- Throws overflow error.
   2. SELECT 15::uint4 + 15 -- Returns 30::uint4.

I think that wouldn't actually work. Postgres's parser immediately assigns a
type to the bare unquoted integral constant so it would end up with a int4
type. Then when it has to pick an operator for uint4+int4 it wouldn't be able
to cast the int4 to uint4 because there would be no implicit cast.

You could make it work by having a uint4+int4 operator which returns uint4 but
then you're going to need a *lot* of operators

One other idea that's been mentioned before is treating integral constants
like 15 as type unknown like the quoted '15' constant is. That way
the parser would see uint4+unknown and could pick the uint4 operator. But that
would be a pretty massive semantics change.

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

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

Re: [HACKERS] [RFC] Unsigned integer support.

[Peter Eisentraut]

Am Friday, 25. July 2008 schrieb Ryan Bradetich:
 PgFoundry already has an uint project:
         http://pgfoundry.org/projects/uint/

     Unfortunately this project seems to have not gone anywhere.  Last
 activity was late 2006 and there are not any files checked into the
 SCM repository.
     Is it acceptable to hijack this PgFoundry project?  Or should I
 start a new project (assuming there is any interest in publishing this
 work).

Please hijack the project and develop your code there.  Of course you can 
always ask for advice here.

-- 
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] Do we really want to migrate plproxy and citext into PG core distribution?

[Joshua D. Drake]

On Fri, 2008-07-25 at 09:37 +0300, Asko Oja wrote:
 Hi
 
 One of reasons to get PL/proxy into core is to make it available to
 Windows users also.
 The idea is to get to the situation 
 
 createlang plproxy mydb
 
 If we can achieve this without putting plproxy into core then i would
 like to hear how.

If the installer project wants to use it on Windows they can. Of course
that assumes that it runs on windows (I have no idea if it does).

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: 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] Do we really want to migrate plproxy and citext into PG core distribution?

[Andrew Dunstan]

Asko Oja wrote:

Hi

One of reasons to get PL/proxy into core is to make it available to 
Windows users also.

The idea is to get to the situation

createlang plproxy mydb

If we can achieve this without putting plproxy into core then i would 
like to hear how.


The same way you would for any other module. This is a non-argument.

If you want to be able to do it without building your own, then you 
would need to ask the Windows Installer guys (Dave and Magnus) to 
include it - they already include lots of non-core stuff, including at 
least one PL, IIRC.



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

[HACKERS] [patch] gsoc, improving hash index v2

[Xiao Meng]

Hi, hackers.
I've post a hash patch in a previous thread
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00794.php
I do apologize for the bad readability  of previous patch. Thank you all for
your comments.
Here is a new patch which fixed some bugs in the previous one.
I post it here to get some feedback and further suggestion. Any comment is
welcome.
Changes since v1:
- fix bug that it crashed in _h_spool when test big data set
- adjust the target-fillfactor calculation in _hash_metapinit
- remove the HASHVALUE_ONLY macro
- replace _create_hash_desc with _get_hash_desc  to get a hard-coded hash
index tuple.
- replace index_getattr with _hash_get_datum to get the hash key datum and
avoid too many calls to _get_hash_desc and index_getattr

Here is what I intend to do.
Todo:
- get  the statistics of block access i/o
- write unit tests using pgunitest to test the following:
  (Josh Berkus suggested in this thread
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00535.php )
bulk load, both COPY and INSERT
single-row updates, inserts and deletes
batch update by key
batch update by other index
batch delete by key
batch delete by other index
concurrent index updates (64 connections insert/deleting concurrently)

I makes some simple test mentioned here (
http://archives.postgresql.org/pgsql-hackers/2007-09/msg00208.php)
I'll make some test on bigger data set later.
using a word list of  3628800 unique words
The table size is 139MB.
Index  BuildTimeIndexSize

btree51961.123 ms   93MB
hash411069.264 ms   2048MB
hash-patch   36288.931 ms   128MB

dict=# SELECT * from hash-dict where word = '0234567891' ;
word

 0234567891
(1 row)

Time: 33.960 ms
dict=# SELECT * from btree-dict where word = '0234567891' ;
word

 0234567891
(1 row)

Time: 1.662 ms

dict=# SELECT * from hash2-dict where word = '0234567891' ;
word

 0234567891
(1 row)

Time: 1.457 ms

At last, there is a problem I encounter.
I'm confused by the function _hash_checkqual.
IMHO, the index tuple only store one column here and  key-sk_attno should
always be 1 here.
And scanKeySize should be 1 since we didn't support multi-column hash yet.
Do I make some misunderstanding?
/*
 * _hash_checkqual -- does the index tuple satisfy the scan conditions?
 */
bool
_hash_checkqual(IndexScanDesc scan, IndexTuple itup)
{
TupleDesctupdesc = RelationGetDescr(scan-indexRelation);
ScanKeykey = scan-keyData;
intscanKeySize = scan-numberOfKeys;

IncrIndexProcessed();

while (scanKeySize  0)
{
Datumdatum;
boolisNull;
Datumtest;

datum = index_getattr(itup,
  key-sk_attno,
  tupdesc,
  isNull);

/* assume sk_func is strict */
if (isNull)
return false;
if (key-sk_flags  SK_ISNULL)
return false;

test = FunctionCall2(key-sk_func, datum, key-sk_argument);

if (!DatumGetBool(test))
return false;

key++;
scanKeySize--;
}

return true;
}

Hope to hear from you.
-- 
Best Regards,
Xiao Meng

DKERC, Harbin Institute of Technology, China
Gtalk: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
http://xiaomeng.yo2.cn
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index 6a5c000..140142d 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -129,8 +129,8 @@ hashbuildCallback(Relation index,
 	IndexTuple	itup;
 
 	/* form an index tuple and point it at the heap tuple */
-	itup = index_form_tuple(RelationGetDescr(index), values, isnull);
-	itup-t_tid = htup-t_self;
+itup = _hash_form_tuple(index, values,isnull);
+itup-t_tid = htup-t_self;
 
 	/* Hash indexes don't index nulls, see notes in hashinsert */
 	if (IndexTupleHasNulls(itup))
@@ -153,8 +153,8 @@ hashbuildCallback(Relation index,
 /*
  *	hashinsert() -- insert an index tuple into a hash table.
  *
- *	Hash on the index tuple's key, find the appropriate location
- *	for the new tuple, and put it there.
+ *	Hash on the heap tuple's key, form an index tuple with hash code.
+ *	Find the appropriate location for the new tuple, and put it there.
  */
 Datum
 hashinsert(PG_FUNCTION_ARGS)
@@ -171,8 +171,8 @@ hashinsert(PG_FUNCTION_ARGS)
 	IndexTuple	itup;
 
 	/* generate an index tuple */
-	itup = index_form_tuple(RelationGetDescr(rel), values, isnull);
-	itup-t_tid = *ht_ctid;
+itup = _hash_form_tuple(rel, values, isnull);
+itup-t_tid = *ht_ctid;
 
 	/*
 	 * If the single index key is null, we don't insert it into the index.
@@ -211,8 +211,8 @@ hashgettuple(PG_FUNCTION_ARGS)
 	OffsetNumber offnum;
 	bool		res;
 
-	/* Hash indexes are never lossy (at the moment anyway) */
-	scan-xs_recheck = false;
+	/* Hash indexes maybe lossy since we store hash code only */
+

Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?

[Alvaro Herrera]

 On Fri, 2008-07-25 at 09:37 +0300, Asko Oja wrote:

  One of reasons to get PL/proxy into core is to make it available to
  Windows users also.
  The idea is to get to the situation 
  
  createlang plproxy mydb
  
  If we can achieve this without putting plproxy into core then i would
  like to hear how.

Sounds like you just need to get a new row in the standard pg_pltemplate.

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

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

[HACKERS] Adding WHERE clause to pg_dump

[Simon Riggs]

Attached patch implements WHERE clauses for pg_dump.

This is useful for producing data samples of a database

e.g. pg_dump -w ctid  '(1000,1)' or random()  0.1

and can also be used for taking incremental backups, if data columns
exist to make a partial dump sensible.

e.g. pg_dump -w last_update_timestamp  

Columns such as this are very common because of optimistic locking
techniques in many databases.

This is designed to be used in conjunction with the TOM utility, and the
forthcoming patch to implement stats hooks. Taken together these
features will allow the ability to take a cut-down database environment
for testing, yet with statistics matching the main production database.

It was easier to write it and then discuss, since I needed to check the
feasibility of the idea before presenting it.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support
Index: doc/src/sgml/ref/pg_dump.sgml
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.103
diff -c -r1.103 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml	20 Jul 2008 18:43:30 -	1.103
--- doc/src/sgml/ref/pg_dump.sgml	25 Jul 2008 08:29:25 -
***
*** 674,679 
--- 674,696 
   /varlistentry
  
   varlistentry
+   termoption-w replaceableSQL where clause/replaceable/option/term
+   termoption--where=replaceable class=parameterSQL where clause/replaceable/option/term
+   listitem
+para
+ Dumps data only for those rows specified. When this parameter is not 
+ specified the default is all rows. The optionwhere/ clause
+ is applied to all tables dumped, so any columns named must be present
+ on all tables being dumped or applicationpg_dump/application
+ will return an error. The phrase quotewhere/quote need not be used,
+ since this will be added automatically. This option is ignored if
+ no data is dumped. option-w/ cannot currently be used at the same
+ time as option-o/--oids/.
+/para
+   /listitem
+  /varlistentry
+ 
+  varlistentry
termoption-W/option/term
termoption--password/option/term
listitem
***
*** 875,880 
--- 892,908 
/para
  
para
+You can specify a data sample using option-w/. An example would be to
+dump all tables less than 8MB in full, while only a random 10% of rows for
+any table 8MB or larger. Note that this may not dump all foreign key data
+correctly, so choose your extract carefully for your own database.
+ 
+ screen
+ prompt$/prompt userinputpg_dump -w ctid  '(1000,1)' or random()  0.1 mydb gt; db.sql/userinput
+ /screen
+   /para
+ 
+   para
 To dump all database objects except for tables whose names begin with
 literalts_/literal:
  
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.497
diff -c -r1.497 pg_dump.c
*** src/bin/pg_dump/pg_dump.c	20 Jul 2008 18:43:30 -	1.497
--- src/bin/pg_dump/pg_dump.c	25 Jul 2008 08:34:05 -
***
*** 95,100 
--- 95,102 
  static SimpleStringList table_exclude_patterns = {NULL, NULL};
  static SimpleOidList table_exclude_oids = {NULL, NULL};
  
+ static	const char *where_clause = NULL;
+ 
  /* default, if no inclusion switches appear, is to dump everything */
  static bool include_everything = true;
  
***
*** 188,194 
  static void dumpEncoding(Archive *AH);
  static void dumpStdStrings(Archive *AH);
  static const char *getAttrName(int attrnum, TableInfo *tblInfo);
! static const char *fmtCopyColumnList(const TableInfo *ti);
  static void do_sql_command(PGconn *conn, const char *query);
  static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
   ExecStatusType expected);
--- 190,196 
  static void dumpEncoding(Archive *AH);
  static void dumpStdStrings(Archive *AH);
  static const char *getAttrName(int attrnum, TableInfo *tblInfo);
! static const char *fmtCopyColumnList(const TableInfo *ti, bool with_brackets);
  static void do_sql_command(PGconn *conn, const char *query);
  static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
   ExecStatusType expected);
***
*** 250,255 
--- 252,258 
  		{superuser, required_argument, NULL, 'S'},
  		{table, required_argument, NULL, 't'},
  		{exclude-table, required_argument, NULL, 'T'},
+ 		{where, required_argument, NULL, 'w'},
  		{password, no_argument, NULL, 'W'},
  		{username, required_argument, NULL, 'U'},
  		{verbose, no_argument, NULL, 'v'},
***
*** 303,309 
  		}
  	}
  
! 	while ((c = getopt_long(argc, argv, abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:,
  			long_options, optindex)) != -1)
  	{
  		switch (c)
--- 

Re: [HACKERS] [RFC] Unsigned integer support.

[Ryan Bradetich]

Hello Peter,

On Fri, Jul 25, 2008 at 5:14 AM, Peter Eisentraut [EMAIL PROTECTED] wrote:
 Am Friday, 25. July 2008 schrieb Ryan Bradetich:
 PgFoundry already has an uint project:
 http://pgfoundry.org/projects/uint/

 Unfortunately this project seems to have not gone anywhere.  Last
 activity was late 2006 and there are not any files checked into the
 SCM repository.
 Is it acceptable to hijack this PgFoundry project?  Or should I
 start a new project (assuming there is any interest in publishing this
 work).

 Please hijack the project and develop your code there.  Of course you can
 always ask for advice here.

I will work on getting the PgFoundry project setup.

Thanks!

- Ryan

-- 
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] [RFC] Unsigned integer support.

[Ryan Bradetich]

Hello Greg,

On Fri, Jul 25, 2008 at 3:57 AM, Gregory Stark [EMAIL PROTECTED] wrote:
 Ryan Bradetich [EMAIL PROTECTED] writes:

 My plans for the example above would be:

   1. SELECT 15 + 15  -- Throws overflow error.
   2. SELECT 15::uint4 + 15 -- Returns 30::uint4.

 I think that wouldn't actually work. Postgres's parser immediately assigns a
 type to the bare unquoted integral constant so it would end up with a int4
 type. Then when it has to pick an operator for uint4+int4 it wouldn't be able
 to cast the int4 to uint4 because there would be no implicit cast.

 You could make it work by having a uint4+int4 operator which returns uint4 but
 then you're going to need a *lot* of operators

This was my plan.  I performed some testing last night to verify that
bare literals
are considered plain integers and would not be implicitly casted to a
different type
(i.e. smallint or bigint).  I am seeing three operators for most operations:

1. uint4 - uint4  = uint4
2. int4 - uint4= uint4
3. uint4 - int4= uint4

Is there something I need to watch out for when adding this number of
operators (i.e.
performance impact, etc)?  Some tests I should be running to measure the impact
of adding these operators?


 One other idea that's been mentioned before is treating integral constants
 like 15 as type unknown like the quoted '15' constant is. That way
 the parser would see uint4+unknown and could pick the uint4 operator. But that
 would be a pretty massive semantics change.

This would require changes to the core PostgreSQL code correct?  My
goal for this
type was to have it as an external project on PgFoundry since there
does not appear
to be much demand for it and unsigned types are not specified in the
SQL standard.
If the community decides this support would be better in core
PostgreSQL code, then
I am willing to help with that work, but I will need a significant
amount of guidance :)

With my limited knowledge, the best (and easiest) path seems to take
advantage of
the extensible type system in PostgreSQL and support unsigned integers as a
PgFoundry project.

Thanks for your review and comments!

- Ryan

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

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

Re: [HACKERS] [RFC] Unsigned integer support.

[Alvaro Herrera]

Gregory Stark escribió:

 One other idea that's been mentioned before is treating integral constants
 like 15 as type unknown like the quoted '15' constant is. That way
 the parser would see uint4+unknown and could pick the uint4 operator. But that
 would be a pretty massive semantics change.

Hmm, if we do that, how would the system resolve something like this?

select 1000 + 1000

There would be no clue as to what + operator to pick, since both
operands are unknown.  This is in fact what happens today with

alvherre=# select '100' + '100';
ERROR:  operator is not unique: unknown + unknown at character 14
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.
STATEMENT:  select '100' + '100';

I think this is a nonstarter.

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

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

Re: [HACKERS] Do we really want to migrate plproxy and citext intoPG core distribution?

[Hiroshi Saito]

Hi.

I tackled with hope temporarily. It seems that some adjustment is still 
required.
http://winpg.jp/~saito/pg_work/plproxy/
However, windows user desires to use. Of course, it is also me. 


Regards,
Hiroshi Saito

From: Joshua D. Drake [EMAIL PROTECTED]



On Fri, 2008-07-25 at 09:37 +0300, Asko Oja wrote:

Hi

One of reasons to get PL/proxy into core is to make it available to
Windows users also.
The idea is to get to the situation 


createlang plproxy mydb

If we can achieve this without putting plproxy into core then i would
like to hear how.


If the installer project wants to use it on Windows they can. Of course
that assumes that it runs on windows (I have no idea if it does).



--
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] [RFC] Unsigned integer support.

[Gregory Stark]

Alvaro Herrera [EMAIL PROTECTED] writes:

 Hmm, if we do that, how would the system resolve something like this?

 select 1000 + 1000

Well we have the same problem with 'foo' || 'bar'. The question I think is
whether the solution there scales to having two different fallback types.

 There would be no clue as to what + operator to pick, since both
 operands are unknown.  This is in fact what happens today with

 alvherre=# select '100' + '100';
 ERROR:  operator is not unique: unknown + unknown at character 14
 HINT:  Could not choose a best candidate operator. You might need to add 
 explicit type casts.
 STATEMENT:  select '100' + '100';

Perhaps we could kill two birds with one stone...

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

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

Re: Column level privileges was:(Re: [HACKERS] Extending grant insert on tables to sequences)

[Jaime Casanova]

On Fri, Jul 25, 2008 at 4:51 AM, Stephen Frost [EMAIL PROTECTED] wrote:
 * Jaime Casanova ([EMAIL PROTECTED]) wrote:
 ok, seems this is the last one for column level patch
 http://archives.postgresql.org/pgsql-patches/2008-04/msg00417.php

 any one working it...

 Yes, I'm working on it, but I'm not against having help, of course.  The
 past couple weeks have been given over to commitfest though, so I havn't
 made much progress on it yet.  My plan is to focus on it during August
 and have a good patch to submit for the September commitfest.


seems like a plan to me... do you have a repository for it? or can you
send me the patch in early august?

-- 
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

-- 
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] [RFC] Unsigned integer support.

[Alvaro Herrera]

Gregory Stark escribió:
 Alvaro Herrera [EMAIL PROTECTED] writes:
 
  Hmm, if we do that, how would the system resolve something like this?
 
  select 1000 + 1000
 
 Well we have the same problem with 'foo' || 'bar'. The question I think is
 whether the solution there scales to having two different fallback types.

Hmm, right.  But you need more than two: consider

alvherre=# select 0.42 + 1;
 ?column? 
--
 1.42
(1 ligne)

However, it would be neat if this behaved the same as

alvherre=# select '0.42' + 1;
ERROR:  invalid input syntax for integer: 0.42
STATEMENT:  select '0.42' + 1;

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

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

Re: [HACKERS] Transaction-controlled robustness for replication

[Simon Riggs]

On Wed, 2008-07-23 at 10:49 +1000, Jens-Wolfhard Schicke wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 Simon Riggs wrote:
  Asynchronous commit controls whether we go to disk at time of commit, or
  whether we defer this slightly. We have the same options with
  replication: do we replicate at time of commit, or do we defer this
  slightly for performance reasons. DRBD and other replication systems
  show us that there is actually another difference when talking about
  synchronous replication: do we go to disk on the standby before
  acknowledging the primary?
  
  We can generalise this as three closed questions, answered either Yes
  (Synchronous) or No (Asynchronous)
  
  * Does WAL get forced to disk on primary at commit time?
  * Does WAL get forced across link to standby at commit time?
  * Does WAL get forced to disk on standby at commit time?

 * Does WAL get applied [and synced] to disk on standby at commit time?

 This is important if you want to use the standby as a read-only.

That's an assumption - I'm not sure its a requirement in all cases. 

If a standby query needed to see particular data then the *query* would
wait until correct data has been applied. I certainly wouldn't want to
penalise writing transactions on the primary because there *might* be a
statement on the standby that wishes to see an updated view.

 I am slightly confused about what the fsync setting does to all this, hence
 the brackets.

There is no sync() during WAL apply when each individual transaction
hits commit. This is because there is no WAL i.e. changes comes from
WAL to the database, so we have no need of a second WAL to protect the
changes being made.

 I think that questions 2 and 3 are trivially bundled together. Once the
 user can specify 2, implementing 3 should be trivial and vice versa.
 I am not even convinced that these need to be two different parameters.
 Also please note that an answer of yes to 3 means that 2 must also
 be answered yes.

Yes, they are trivially bundled together, but there is benefit in doing
so. The difference between 2 and 3 is about performance and levels of
robustness.

Waiting for transfer across link to standby (only) is much faster than
waiting for transfer *and* waiting for fsync. Probably twice as fast in
a tightly coupled cluster, i.e. option 3 will make your transactions
somewhat more robust, but twice the response time and half the
throughput.

  We could represent this with 3 parameters:
  synchronous_commit = on | off
  synchronous_standby_transfer = on | off
  synchronous_standby_wal_fsync = on | off
 synchronous_standby_apply = on | off# just to propose a name
 
  Changing the parameter setting at transaction-level would be expensive
  if we had to set three parameters.
 What exactly does expensive mean? All three parameters can probably be set
 in one TCP packet from client to server.

Expensive as in we need to parse and handle each statement separately.
If we have a single parameter then much lower overhead.

  Or we could use just a single parameter
  synchronous_commit = 'AAA', 'SAA', 'SSA', 'SSS' or on |off when no
  log-based replication is defined
  
  Having the ability to set these at the transaction-level would be very
  cool. Having it set via a *single* parameter would make it much more
  viable to switch between AAA for bulk, low importance data and SSS for
  very low volume, critical data, or somewhere in between on the same
  server, at the same time.

 The problem with a single parameter is that everything becomes position
 dependent and if whyever a new parameter is introduced, it's not easy to
 upgrade old application code.

True, but what new parameter do you imagine?

  So proposal in summary is
  * allow various modes of synchronous replication for perf/robustness
  * allow modes to be specified per-transaction
  * allow modes to be specified as a single parameter

 How about creating named modes? 

Good idea

 This would give the user the ability to
 define more fine-grained control especially in larger clusters of 
 fail-over/read-only
 servers without totally clogging the parameter space and application code.
 Whether this should be done SQL-style or in some config file is not so clear 
 to me,
 although I'd prefer SQL-style like
 
 CREATE SYNCHRONIZING MODE immediate_readonly AS
   LOCALSYNCHRONOUS APPLY
   192.168.0.10 SYNCHRONOUS APPLY-- read-only slave
   192.168.0.11 SYNCHRONOUS APPLY-- read-only slave
   192.168.0.20 SYNCHRONOUS SHIP -- backup-server
   192.168.0.21 SYNCHRONOUS SHIP -- backup-server
   192.168.0.30 SYNHCRONOUS FSYNC-- backup-server with fast disks
 ;

Thats not how we define parameter values, so no.

 and then something like
 
 synchronize_mode = immediate_readonly;
 
 Yeah, I know, give patches not pipe-dreams :)

Ah yes. Of course.

The only sensible options are these four:

AAA 
SAA 
SSA 
SSS

plus the existing on  off

So we give them 4 

Re: [HACKERS] [RFC] Unsigned integer support.

[Kevin Grittner]

 Alvaro Herrera [EMAIL PROTECTED] wrote: 
 
 consider
 
 alvherre=# select 0.42 + 1;
  ?column? 
 --
  1.42
 (1 ligne)
 
 However, it would be neat if this behaved the same as
 
 alvherre=# select '0.42' + 1;
 ERROR:  invalid input syntax for integer: 0.42
 STATEMENT:  select '0.42' + 1;
 
I wouldn't want the former to fail.
 
I also wouldn't like these to fail:
 
select 50 + 1;
select 'abc'::text || 'def'::varchar(3);
 
-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] [RFC] Unsigned integer support.

[Alvaro Herrera]

Kevin Grittner escribió:
  Alvaro Herrera [EMAIL PROTECTED] wrote: 
  
  consider
  
  alvherre=# select 0.42 + 1;
   ?column? 
  --
   1.42
  (1 ligne)
  
  However, it would be neat if this behaved the same as
  
  alvherre=# select '0.42' + 1;
  ERROR:  invalid input syntax for integer: 0.42
  STATEMENT:  select '0.42' + 1;
  
 I wouldn't want the former to fail.

Sorry, I was unclear.  What I meant was that both 0.42 + 1 and
'0.42' + 1 should be treated the same, and they should both produce a
numeric output.

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

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

Re: [HACKERS] Do we really want to migrate plproxy and citext intoPG core distribution?

[Andrew Dunstan]

Hiroshi Saito wrote:
 Hi.

 I tackled with hope temporarily. It seems that some adjustment is
 still required.
 http://winpg.jp/~saito/pg_work/plproxy/
 However, windows user desires to use. Of course, it is also me.


What is stopping you? Whether or not it works on Windows has (or should
have) nothing to do with whether or not it is in core.

Regarding your patch, the change w.r.t. the CONST token looks a bit odd
- can you explain what you're doing and why?


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] Do we really want to migrate plproxy and citext into PG core distribution?

[Tom Lane]

Alvaro Herrera [EMAIL PROTECTED] writes:
 On Fri, 2008-07-25 at 09:37 +0300, Asko Oja wrote:
 createlang plproxy mydb
 
 If we can achieve this without putting plproxy into core then i would
 like to hear how.

 Sounds like you just need to get a new row in the standard pg_pltemplate.

When pg_pltemplate was first proposed, we discussed including entries in
its standard contents for all the known non-core PLs.  I forget the
arguments that were made against that, but I still think it'd be a good
idea.  It'd save one step in installing a non-core PL, and the entries
couldn't cause any harm, since they'd be useless unless the admin had
actually installed the corresponding .so into the installation's
$libdir.

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] Adding WHERE clause to pg_dump

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 Attached patch implements WHERE clauses for pg_dump.

I still have serious reservations about adding such an ugly,
non-orthogonal wart to pg_dump.  Why is it not appropriate to just
do a COPY (SELECT ...) TO STDOUT when you need this?

regards, tom lane

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

Re: [HACKERS] Do we really want to migrate plproxy and citext intoPG core distribution?

[Hiroshi Saito]

What is stopping you? Whether or not it works on Windows has (or should
have) nothing to do with whether or not it is in core.


I think that plproxy is great. However, the windows user did not complain. 
Because, build was not easy. Therefore, pginstaller has not chosen. 
Then, I thought that I wanted to solvebut, I do not have a spare time.
Are they unrelated? I'm sorry if it is a noise 



Regarding your patch, the change w.r.t. the CONST token looks a bit odd
- can you explain what you're doing and why?


Ad hoc in order to clarify a problem.

Regards,
Hiroshi Saito

--
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] Adding WHERE clause to pg_dump

[Simon Riggs]

On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Attached patch implements WHERE clauses for pg_dump.
 
 I still have serious reservations about adding such an ugly,
 non-orthogonal wart to pg_dump.  Why is it not appropriate to just
 do a COPY (SELECT ...) TO STDOUT when you need this?

So you can dump a coherent sample database in one command, not 207.

Every user of PostgreSQL wants a dev/test database. If the database is
large it isn't practical to take a complete copy. Nor is it practical to
hand-write a data sampling extraction program and if you do, its usually
imperfect in many ways.

Adding this feature gives a very fast capability to create sample
databases, or incremental backups for many cases.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Adding WHERE clause to pg_dump

[Joshua D. Drake]

On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:
 On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   Attached patch implements WHERE clauses for pg_dump.
  
  I still have serious reservations about adding such an ugly,
  non-orthogonal wart to pg_dump.  Why is it not appropriate to just
  do a COPY (SELECT ...) TO STDOUT when you need this?
 
 So you can dump a coherent sample database in one command, not 207.
 
 Every user of PostgreSQL wants a dev/test database. If the database is
 large it isn't practical to take a complete copy. Nor is it practical to
 hand-write a data sampling extraction program and if you do, its usually
 imperfect in many ways.
 
 Adding this feature gives a very fast capability to create sample
 databases, or incremental backups for many cases.

Not sure I buy this argument. I am all for usability and I would be the
first to shout about the general ridiculousness of pg_dump/all/restore
but in this case I think Tom is right. This feature could easily be done
in a script without harassing pg_dump.

Sincerely,

Joshua D. Drake




-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: 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] Research/Implementation of Nested Loop Join optimization

[Manoel Henrique]

Yes, I'm relying on the assumption that backwards scan has the same cost as
forward scan, why shouldn't it?

Yet, all plan node types we are testing works with backwards scan (looking
on ExecSupportsBackwardScan). But, is there a easy way to make a query
execute only in backwards scan? How we can do that? Our first objective is
to make a backwards scan only and then test a forward-and-backward scan.

-- Manoel


On Thu, Jul 24, 2008 at 2:49 AM, Tom Lane [EMAIL PROTECTED] wrote:

 Manoel Henrique [EMAIL PROTECTED] writes:
  The nodeMergejoin.c is the code for the Merge Join isn`t it? I am trying
 to
  find a way to change the Nested Loop Join, It would be more like on
  nodeNestloop.c when rescanning the inner plan, (second time scanning the
  inner plan and so on) he`d change the scan direction, If the scan
 direction
  was from first tuple to last tuple it would go backwards, if it was from
  last to first it would go forward... The code I`m looking atm is from
 8.3.1
  , seems to have some kind of direction manager but doesn`t seems to be in
  use.

 I find this a bit dubious.  If the inner rel is small enough to fit in
 memory then it buys nothing.  If not, then you win only to the extent
 that a pretty large fraction of the inner rel fits in memory.  In any
 case you are relying on the assumption that backwards scan is just as
 efficient as forward scan, which seems to me to be a pretty large
 assumption --- we expect forward seqscans to get a performance boost
 from kernel readahead, but I'd be surprised if the kernel recognized
 what was happening in a backwards scan.

 Note also that backwards scan doesn't work at all in some plan
 node types (cf ExecSupportsBackwardScan).  You'd need to check
 what the inner input node was before trying this.

regards, tom lane

Re: [HACKERS] Adding WHERE clause to pg_dump

[Simon Riggs]

On Fri, 2008-07-25 at 11:46 -0700, Joshua D. Drake wrote:
 On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:
  On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
   Simon Riggs [EMAIL PROTECTED] writes:
Attached patch implements WHERE clauses for pg_dump.
   
   I still have serious reservations about adding such an ugly,
   non-orthogonal wart to pg_dump.  Why is it not appropriate to just
   do a COPY (SELECT ...) TO STDOUT when you need this?
  
  So you can dump a coherent sample database in one command, not 207.
  
  Every user of PostgreSQL wants a dev/test database. If the database is
  large it isn't practical to take a complete copy. Nor is it practical to
  hand-write a data sampling extraction program and if you do, its usually
  imperfect in many ways.
  
  Adding this feature gives a very fast capability to create sample
  databases, or incremental backups for many cases.
 
 Not sure I buy this argument. I am all for usability and I would be the
 first to shout about the general ridiculousness of pg_dump/all/restore
 but in this case I think Tom is right. This feature could easily be done
 in a script without harassing pg_dump.

You can do it, yes. But it takes a lot longer. If the time to implement
was similar, then I would immediately agree feature available already.

pg_dump is not harassed by this. What is lost by adding this feature?

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] [RFC] Unsigned integer support.

[Tom Lane]

Ryan Bradetich [EMAIL PROTECTED] writes:
 On Fri, Jul 25, 2008 at 3:57 AM, Gregory Stark [EMAIL PROTECTED] wrote:
 Ryan Bradetich [EMAIL PROTECTED] writes:
 My plans for the example above would be:
 
 1. SELECT 15 + 15  -- Throws overflow error.
 2. SELECT 15::uint4 + 15 -- Returns 30::uint4.
 
 You could make it work by having a uint4+int4 operator which returns uint4 
 but
 then you're going to need a *lot* of operators

 This was my plan.

Like he says, it's a *lot* of operators, and the point doesn't seem
entirely clear to me.  You'll still have overflow cases, they'll just be
in different places.

Consider the idea of not having any uint4-specific arithmetic operators,
but instead providing the following:

* assignment casts from int4 and int8 to uint4
  (these throw error if out of range, of course)
* implicit cast from uint4 to int8 (can never fail)

The effect of providing the latter cast would be that any arithmetic
involving a uint4 column would automatically be done in int8.  Which
would make it a shade slower than a native implementation, but probably
not enough slower to be a problem --- and you'd avoid having to write
dozens of operators and underlying support functions.  Storing into the
uint4 column would work fine with no extra notation because of the
assignment casts.

Moreover, you'd avoid cluttering the system with a pile of cross-type
operators, which we have recently realized are not a good thing, because
they increase the likelihood of ambiguous operator problems --- see
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00750.php

For uint8 you'd have to promote to numeric to guarantee no failure
in the implicit cast; which is going to be a rather bigger performance
hit, but I don't really see uint8 as being a type with huge demand.

Now you probably *will* want cross-type comparison operators, if you
are going to support indexing of unsigned columns, so that something
like
uint4col  42
can be indexed without any casting.  But limiting yourself to the six
basic comparison operators certainly makes it a much less bulky project.

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] Adding WHERE clause to pg_dump

[Joshua D. Drake]

On Fri, 2008-07-25 at 20:26 +0100, Simon Riggs wrote:
 On Fri, 2008-07-25 at 11:46 -0700, Joshua D. Drake wrote:
  On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:

   Adding this feature gives a very fast capability to create sample
   databases, or incremental backups for many cases.
  
  Not sure I buy this argument. I am all for usability and I would be the
  first to shout about the general ridiculousness of pg_dump/all/restore
  but in this case I think Tom is right. This feature could easily be done
  in a script without harassing pg_dump.
 
 You can do it, yes. But it takes a lot longer. If the time to implement
 was similar, then I would immediately agree feature available already.
 
 pg_dump is not harassed by this. What is lost by adding this feature?

Gained. Code complexity. Right now pg_dump does, copy. You are
introducing a whole other level of complexity by adding WHERE clause
capability. Secondly I don't think it would actually add anything but
complexity to the user.

How do we deal with this?

pg_dump -w last_update_timestamp  ... -t 'table*'

What I see is a recipe for inconsistent, un-restorable backups without a
user realizing what they have done. The only way to deal with the above
is:

1. Wildcards aren't allowed if you have -w
2. You dump everything, if the WHERE clause isn't relevant you just dump
the whole table

I don't like either.

I do see utility if you know what you are doing but I think it makes
more sense to have it outside of pg_dump.

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: 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][PATCHES] odd output in restore mode

[Simon Riggs]

On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote:

 reviewing your patch 

Current status is this:

* My understanding is that Dave and Andrew (and therefore Simon) think
the approach proposed here is an acceptable one. Heikki disagrees and
wants different approach. Perhaps I misunderstand.

* Patch needs work to complete the proposed approach

* I'm willing to change the patch, but not able to test it on Windows.

Is there someone able to test the patch, if I make the changes? If not,
we should just kick this out of the CommitFest queue now and be done. If
nobody cares enough about this issue to test a fix, we shouldn't bother.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Whence cometh the data in src/test/regress/data/streets.data ?

[Tom Lane]

Bjorn Munch [EMAIL PROTECTED] writes:
 This file was checked in way back in July 1996, by Marc G. Fournier
 but that doesn't mean he was the one who got the data from
 somewhere. Does anyone know where it comes from? Or has this
 information been lost in the mist of time?

It's in the postgres v4r2 tarball with a file date of 1993-01-08,
which means nobody around the current project has a clue.

IANAL either, but I think it'd be fairly hard for anyone to assert a
copyright on it given that it's a compilation of publicly available
facts, and is surely not in the same format the information was
originally published in anyhow.

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] Additional psql requirements

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 I just wanted an easy way to write install scripts that work on various
 releases/schemas/environments, works on core and on any platform.

The word easy is out of place in that sentence.  Such scripts would
likely need information that's entirely outside the province of the
database proper --- directory paths, system names and versions, etc.
I really doubt that anything that could get accepted as a database
scripting feature would solve such problems.

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] Whence cometh the data in src/test/regress/data/streets.data ?

[Dann Corbit]

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
 [EMAIL PROTECTED] On Behalf Of Tom Lane
 Sent: Friday, July 25, 2008 12:52 PM
 To: Bjorn Munch
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Whence cometh the data in
 src/test/regress/data/streets.data ?
 
 Bjorn Munch [EMAIL PROTECTED] writes:
  This file was checked in way back in July 1996, by Marc G. Fournier
  but that doesn't mean he was the one who got the data from
  somewhere. Does anyone know where it comes from? Or has this
  information been lost in the mist of time?
 
 It's in the postgres v4r2 tarball with a file date of 1993-01-08,
 which means nobody around the current project has a clue.
 
 IANAL either, but I think it'd be fairly hard for anyone to assert a
 copyright on it given that it's a compilation of publicly available
 facts, and is surely not in the same format the information was
 originally published in anyhow.

Probably something from here:
http://www.freegis.org/database/?cat=1

-- 
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] [RFC] Unsigned integer support.

[Dann Corbit]

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
 [EMAIL PROTECTED] On Behalf Of Tom Lane
 Sent: Friday, July 25, 2008 12:32 PM
 To: Ryan Bradetich
 Cc: Gregory Stark; pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] [RFC] Unsigned integer support.
 
 Ryan Bradetich [EMAIL PROTECTED] writes:
  On Fri, Jul 25, 2008 at 3:57 AM, Gregory Stark
 [EMAIL PROTECTED] wrote:
  Ryan Bradetich [EMAIL PROTECTED] writes:
  My plans for the example above would be:
 
  1. SELECT 15 + 15  -- Throws overflow error.
  2. SELECT 15::uint4 + 15 -- Returns
 30::uint4.
 
  You could make it work by having a uint4+int4 operator which
returns
 uint4 but
  then you're going to need a *lot* of operators
 
  This was my plan.
 
 Like he says, it's a *lot* of operators, and the point doesn't seem
 entirely clear to me.  You'll still have overflow cases, they'll just
 be
 in different places.
 
 Consider the idea of not having any uint4-specific arithmetic
 operators,
 but instead providing the following:
 
   * assignment casts from int4 and int8 to uint4
 (these throw error if out of range, of course)
   * implicit cast from uint4 to int8 (can never fail)
 
 The effect of providing the latter cast would be that any arithmetic
 involving a uint4 column would automatically be done in int8.  Which
 would make it a shade slower than a native implementation, but
probably
 not enough slower to be a problem --- and you'd avoid having to write
 dozens of operators and underlying support functions.  Storing into
the
 uint4 column would work fine with no extra notation because of the
 assignment casts.
 
 Moreover, you'd avoid cluttering the system with a pile of cross-type
 operators, which we have recently realized are not a good thing,
 because
 they increase the likelihood of ambiguous operator problems --- see
 http://archives.postgresql.org/pgsql-hackers/2008-06/msg00750.php
 
 For uint8 you'd have to promote to numeric to guarantee no failure
 in the implicit cast; which is going to be a rather bigger performance
 hit, but I don't really see uint8 as being a type with huge demand.
 
 Now you probably *will* want cross-type comparison operators, if you
 are going to support indexing of unsigned columns, so that something
 like
   uint4col  42
 can be indexed without any casting.  But limiting yourself to the six
 basic comparison operators certainly makes it a much less bulky
 project.

At the cost of one bit of storage, you have compatible types using
CREATE DOMAIN:

CREATE DOMAIN name [ AS ] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

More specifically:

CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);

Seems like a heck of a lot less work to me.  Not to mention very easy to
use.

C:\Program Files (x86)\PostgreSQL\8.3\binpsql -h localhost -U postgres
domaintest
Password for user postgres:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

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

Warning: Console code page (437) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page Notes for Windows users for details.

domaintest=# CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
CREATE DOMAIN
domaintest=# CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);
CREATE DOMAIN
domaintest=#
domaintest=# create table integer_types (
domaintest(# usCol usmallint,
domaintest(# sCol smallint,
domaintest(# uiCol uinteger,
domaintest(# iCol integer,
domaintest(# ubCol ubigint,
domaintest(# bCol bigint,
domaintest(# unCol unumeric,
domaintest(# nCol numeric
domaintest(# );
CREATE TABLE
domaintest=# create index i1 on integer_types(usCol);
CREATE INDEX
domaintest=# create index i2 on integer_types(sCol);
CREATE INDEX
domaintest=# create index i3 on integer_types(uiCol);
CREATE INDEX
domaintest=# create index i4 on integer_types(iCol);
CREATE INDEX
domaintest=# create index i5 on integer_types(ubCol);
CREATE INDEX
domaintest=# create index i6 on integer_types(bCol);
CREATE INDEX
domaintest=# create index i7 on integer_types(unCol);
CREATE INDEX
domaintest=# create index i8 on integer_types(nCol);
CREATE INDEX
domaintest=# insert into integer_types values(1,1,1,1,1,1,1,1);
INSERT 0 1
domaintest=# select * from integer_types;
 uscol | scol | uicol | icol | ubcol | bcol | uncol | ncol

Re: [HACKERS] [RFC] Unsigned integer support.

[Andrew Dunstan]

Dann Corbit wrote:


CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);


  


s//=/g

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] [RFC] Unsigned integer support.

[Dann Corbit]

 -Original Message-
 From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
 Sent: Friday, July 25, 2008 1:11 PM
 To: Dann Corbit
 Cc: Tom Lane; Ryan Bradetich; Gregory Stark; pgsql-
 [EMAIL PROTECTED]
 Subject: Re: [HACKERS] [RFC] Unsigned integer support.
 
 
 
 Dann Corbit wrote:
 
  CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
  CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
  CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
  CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);
 
 
 
 
 s//=/g

I turned off the default option to remove extra line breaks.  
Future posts should not be quite as even and bletcherous.
God willing, and the crick don't rise.


-- 
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] Do we really want to migrate plproxy and citext into PG core distribution?

[Ron Mayer]

Tom Lane wrote:

Hannu Krosing [EMAIL PROTECTED] writes:

AFAIK, there is nothing that requires pl/perl, pl/tcl or pl/python to be
in core either.


True, but I think it's a good idea to have at least one such in core,
as a prototype to help us track the issues associated with loading a
large third-party library along with a PL.  The fact that we have three
is historical, but on the other hand I believe we've seen distinct
issues crop up from each one, so maybe only one isn't enough either.



Wouldn't it provide even more benefit if these were maintained
as independent modules *outside* of core but still by the core team.

That would not only help track issues of loading the library as Tom
described; but also issues related to maintaining external modules.


--
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] [RFC] Unsigned integer support.

[Andrew Dunstan]

Dann Corbit wrote:

-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
Sent: Friday, July 25, 2008 1:11 PM
To: Dann Corbit
Cc: Tom Lane; Ryan Bradetich; Gregory Stark; pgsql-
[EMAIL PROTECTED]
Subject: Re: [HACKERS] [RFC] Unsigned integer support.



Dann Corbit wrote:


CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);



  

s//=/g



I turned off the default option to remove extra line breaks.  
Future posts should not be quite as even and bletcherous.

God willing, and the crick don't rise.

  


I suspect you're missing my point, namely that 0 should be an allowed 
value for unsigned types.


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] [RFC] Unsigned integer support.

[Dann Corbit]

 -Original Message-
 From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
 Sent: Friday, July 25, 2008 1:28 PM
 To: Dann Corbit
 Cc: Tom Lane; Ryan Bradetich; Gregory Stark; pgsql-
 [EMAIL PROTECTED]
 Subject: Re: [HACKERS] [RFC] Unsigned integer support.
 
 
 
 Dann Corbit wrote:
  -Original Message-
  From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
  Sent: Friday, July 25, 2008 1:11 PM
  To: Dann Corbit
  Cc: Tom Lane; Ryan Bradetich; Gregory Stark; pgsql-
  [EMAIL PROTECTED]
  Subject: Re: [HACKERS] [RFC] Unsigned integer support.
 
 
 
  Dann Corbit wrote:
 
  CREATE DOMAIN usmallint AS SMALLINT CHECK(VALUE  0);
  CREATE DOMAIN uinteger AS INTEGER CHECK(VALUE  0);
  CREATE DOMAIN ubigint AS BIGINT CHECK(VALUE  0);
  CREATE DOMAIN unumeric AS NUMERIC CHECK(VALUE  0);
 
 
 
 
  s//=/g
 
 
  I turned off the default option to remove extra line breaks.
  Future posts should not be quite as even and bletcherous.
  God willing, and the crick don't rise.
 
 
 
 I suspect you're missing my point, namely that 0 should be an allowed
 value for unsigned types.

Quite right.  The domains I created were really the 'natural numbers'
rather than unsigned types.

-- 
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][PATCHES] odd output in restore mode

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote:
 reviewing your patch 

 Current status is this:
 * My understanding is that Dave and Andrew (and therefore Simon) think
 the approach proposed here is an acceptable one. Heikki disagrees and
 wants different approach. Perhaps I misunderstand.
 * Patch needs work to complete the proposed approach
 * I'm willing to change the patch, but not able to test it on Windows.

I thought the latest conclusion was that changing the behavior of
pg_standby itself wouldn't address the problem anyway, and that what we
need is just a docs patch recommending that people use safe copying
methods in their scripts that copy to the archive area?

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] Do we really want to migrate plproxy and citext into PG core distribution?

[Tom Lane]

Ron Mayer [EMAIL PROTECTED] writes:
 Wouldn't it provide even more benefit if these were maintained
 as independent modules *outside* of core but still by the core team.

This part of the core team isn't willing to do that.  I've got enough
work to do without trying to keep multiple repositories in sync.

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][PATCHES] odd output in restore mode

[Simon Riggs]

On Fri, 2008-07-25 at 16:31 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote:
  reviewing your patch 
 
  Current status is this:
  * My understanding is that Dave and Andrew (and therefore Simon) think
  the approach proposed here is an acceptable one. Heikki disagrees and
  wants different approach. Perhaps I misunderstand.
  * Patch needs work to complete the proposed approach
  * I'm willing to change the patch, but not able to test it on Windows.
 
 I thought the latest conclusion was that changing the behavior of
 pg_standby itself wouldn't address the problem anyway, and that what we
 need is just a docs patch recommending that people use safe copying
 methods in their scripts that copy to the archive area?

Plus the rest of this patch, which is really very simple.

pg_standby currently waits (on Windows) for the sleep time. We agreed
that this sleep would be on by default, but optional.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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][PATCHES] odd output in restore mode

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 On Fri, 2008-07-25 at 16:31 -0400, Tom Lane wrote:
 I thought the latest conclusion was that changing the behavior of
 pg_standby itself wouldn't address the problem anyway, and that what we
 need is just a docs patch recommending that people use safe copying
 methods in their scripts that copy to the archive area?

 Plus the rest of this patch, which is really very simple.

Why?  AFAICT the patch is just a kluge that adds user-visible complexity
without providing a solution that's actually sure to work.

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] Adding WHERE clause to pg_dump

[daveg]

On Fri, Jul 25, 2008 at 08:26:35PM +0100, Simon Riggs wrote:
 
 On Fri, 2008-07-25 at 11:46 -0700, Joshua D. Drake wrote:
  On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:
   On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
 Attached patch implements WHERE clauses for pg_dump.

I still have serious reservations about adding such an ugly,
non-orthogonal wart to pg_dump.  Why is it not appropriate to just
do a COPY (SELECT ...) TO STDOUT when you need this?
   
   So you can dump a coherent sample database in one command, not 207.
   
   Every user of PostgreSQL wants a dev/test database. If the database is
   large it isn't practical to take a complete copy. Nor is it practical to
   hand-write a data sampling extraction program and if you do, its usually
   imperfect in many ways.
   
   Adding this feature gives a very fast capability to create sample
   databases, or incremental backups for many cases.
  
  Not sure I buy this argument. I am all for usability and I would be the
  first to shout about the general ridiculousness of pg_dump/all/restore
  but in this case I think Tom is right. This feature could easily be done
  in a script without harassing pg_dump.
 
 You can do it, yes. But it takes a lot longer. If the time to implement
 was similar, then I would immediately agree feature available already.
 
 pg_dump is not harassed by this. What is lost by adding this feature?

This was discussed at the beginning of June on patches, Dave Durham submitted
a patch to add where clauses via a -w option and then in response to feedback
to add it to each each table of -t. See discussion here:

  http://archives.postgresql.org/pgsql-patches/2008-06/msg1.php

and final patch here:

  http://archives.postgresql.org/pgsql-patches/2008-06/msg00026.php.

We now have two patches on this topic from different submitters with
different use cases supplied as justification. I have yet another use case
not mentioned by either of the submitters and will probably hand patch
pg_dump locally to do so.

I don't think at this point we should wave this off under the impression
that no one really wants or needs it as obviously some people want it enough
to code it. The other objections seem to be based on the themes:

 - code complexity.

 Davy's patch is quite simple. I have looked at Simon's yet.

 - we need an ETL tool so this should be preempted by that.
 - pg_dump should be made into a library so this can be done separately.

 We don't generally allow imaginary futures to prevent us from adding
 useful functionality on other topics.

 - This can be done with a script.

 Not really. The script would pretty much have to contain most of
 pg_dump. That's more than a script.

 - users could make partial dumps and be confused and lose data.

  Yes, but they can already do that with -n, -t, and the new pre-data
  and post-data switches. This is one more case where the default is
  a full dump but you one can specificly request less.

I think that once COPY sprouted a WHERE clause it becomes almost inevitable
that pg_dump will take advantage of them. How many patches on this topic do
we want to ignore?

As you may have guessed by this point:
+1

-dg 

-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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][PATCHES] odd output in restore mode

[Simon Riggs]

On Fri, 2008-07-25 at 16:58 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Fri, 2008-07-25 at 16:31 -0400, Tom Lane wrote:
  I thought the latest conclusion was that changing the behavior of
  pg_standby itself wouldn't address the problem anyway, and that what we
  need is just a docs patch recommending that people use safe copying
  methods in their scripts that copy to the archive area?
 
  Plus the rest of this patch, which is really very simple.
 
 Why?  AFAICT the patch is just a kluge that adds user-visible complexity
 without providing a solution that's actually sure to work.

First, I'm not the one objecting to the current behaviour. 

Currently, there is a wait in there that can be removed if you use a
copy utility that sets size after it does a copy. So we agreed to make
it optional (at PGCon).

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Adding WHERE clause to pg_dump

[Joshua D. Drake]

On Fri, 2008-07-25 at 14:11 -0700, daveg wrote:
 On Fri, Jul 25, 2008 at 08:26:35PM +0100, Simon Riggs wrote:

  - This can be done with a script.
 
  Not really. The script would pretty much have to contain most of
  pg_dump. That's more than a script.
 

Yes really. :) The only thing pg_dump is buying you here is easy of
schema pull. In a situation like this you would pull a pg_dump -s then
only restore data that you want based on a single transaction snapshot
of the objects you are going to query.

  - users could make partial dumps and be confused and lose data.
 
   Yes, but they can already do that with -n, -t, and the new pre-data
   and post-data switches. This is one more case where the default is
   a full dump but you one can specificly request less.

No they actually can't. You are guaranteed that regardless of a -n or -t
flag that the data you receive is consistent. You can't guarantee that
with -w because you could pull different data based on an arbitrary
conditional that can not apply to all objects.

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: 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] Adding WHERE clause to pg_dump

[Simon Riggs]

On Fri, 2008-07-25 at 14:11 -0700, daveg wrote:
 On Fri, Jul 25, 2008 at 08:26:35PM +0100, Simon Riggs wrote:
  
  On Fri, 2008-07-25 at 11:46 -0700, Joshua D. Drake wrote:
   On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:
On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Attached patch implements WHERE clauses for pg_dump.
 
 I still have serious reservations about adding such an ugly,
 non-orthogonal wart to pg_dump.  Why is it not appropriate to just
 do a COPY (SELECT ...) TO STDOUT when you need this?

So you can dump a coherent sample database in one command, not 207.

Every user of PostgreSQL wants a dev/test database. If the database is
large it isn't practical to take a complete copy. Nor is it practical to
hand-write a data sampling extraction program and if you do, its usually
imperfect in many ways.

Adding this feature gives a very fast capability to create sample
databases, or incremental backups for many cases.
   
   Not sure I buy this argument. I am all for usability and I would be the
   first to shout about the general ridiculousness of pg_dump/all/restore
   but in this case I think Tom is right. This feature could easily be done
   in a script without harassing pg_dump.
  
  You can do it, yes. But it takes a lot longer. If the time to implement
  was similar, then I would immediately agree feature available already.
  
  pg_dump is not harassed by this. What is lost by adding this feature?
 
 This was discussed at the beginning of June on patches, Dave Durham submitted
 a patch to add where clauses via a -w option and then in response to feedback
 to add it to each each table of -t. See discussion here:
 
   http://archives.postgresql.org/pgsql-patches/2008-06/msg1.php
 
 and final patch here:
 
   http://archives.postgresql.org/pgsql-patches/2008-06/msg00026.php.
 
 We now have two patches on this topic from different submitters with
 different use cases supplied as justification. 

Well, that is truly bizarre.

I had no idea about the existence of the other patch. I guess I must
have been busy that week.

This was designed a while back in conjunction with other related
thoughts. I still want an easy way to create a data sample for creating
dev databases from large production systems.

I defer and apologise to the previous submitter, since he got there
first, and apologise again for the noise.

(Cheeky code review: Davy's patch fails if used with -o option, plus I
think it outputs the wrong text into the dump file, AFAICS).

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Adding WHERE clause to pg_dump

[Simon Riggs]

On Fri, 2008-07-25 at 14:29 -0700, Joshua D. Drake wrote:
 
   - users could make partial dumps and be confused and lose data.
  
Yes, but they can already do that with -n, -t, and the new
 pre-data
and post-data switches. This is one more case where the
 default is
a full dump but you one can specificly request less.
 
 No they actually can't. You are guaranteed that regardless of a -n or
 -t
 flag that the data you receive is consistent. You can't guarantee that
 with -w because you could pull different data based on an arbitrary
 conditional that can not apply to all objects.

But are you guaranteed that you have all tables in FK relationships? No.
(But I like that capability also - its useful).

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Adding WHERE clause to pg_dump

[Davy Durham]

Simon Riggs wrote:



Well, that is truly bizarre.

I had no idea about the existence of the other patch. I guess I must
have been busy that week.

This was designed a while back in conjunction with other related
thoughts. I still want an easy way to create a data sample for creating
dev databases from large production systems.

I defer and apologise to the previous submitter, since he got there
first, and apologise again for the noise.

(Cheeky code review: Davy's patch fails if used with -o option, plus I
think it outputs the wrong text into the dump file, AFAICS).

 

Are you using my patch at 
http://archives.postgresql.org/pgsql-patches/2008-06/msg00026.php ?  
I'll be glad to fix it.


--
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] Research/Implementation of Nested Loop Join optimization

[Gregory Stark]

Manoel Henrique [EMAIL PROTECTED] writes:

 Yes, I'm relying on the assumption that backwards scan has the same cost as
 forward scan, why shouldn't it?

Because hard drives only spin one direction

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

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

Re: [HACKERS] Adding WHERE clause to pg_dump

[Gregory Stark]

Joshua D. Drake [EMAIL PROTECTED] writes:

 How do we deal with this?

 pg_dump -w last_update_timestamp  ... -t 'table*'

 What I see is a recipe for inconsistent, un-restorable backups without a
 user realizing what they have done. The only way to deal with the above
 is:

 1. Wildcards aren't allowed if you have -w
 2. You dump everything, if the WHERE clause isn't relevant you just dump
 the whole table

There's always 

  3. Apply the WHERE clause to all tables and if there's a table missing
 columns referenced in the where clause then fail with the appropriate
 error.

Which seems like the right option to me. The tricky bit would be how to deal
with cases where you want a different where clause for different tables. But
even if it doesn't handle all cases that doesn't mean a partial solution is
unreasonable.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

-- 
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] Research/Implementation of Nested Loop Join optimization

[Jonah H. Harris]

On Fri, Jul 25, 2008 at 6:10 PM, Gregory Stark [EMAIL PROTECTED] wrote:
 Manoel Henrique [EMAIL PROTECTED] writes:

 Yes, I'm relying on the assumption that backwards scan has the same cost as
 forward scan, why shouldn't it?

 Because hard drives only spin one direction

:)

-- 
Jonah H. Harris, Senior DBA
myYearbook.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] Research/Implementation of Nested Loop Join optimization

[Joshua D. Drake]

On Fri, 2008-07-25 at 19:31 -0400, Jonah H. Harris wrote:
 On Fri, Jul 25, 2008 at 6:10 PM, Gregory Stark [EMAIL PROTECTED] wrote:
  Manoel Henrique [EMAIL PROTECTED] writes:
 
  Yes, I'm relying on the assumption that backwards scan has the same cost as
  forward scan, why shouldn't it?
 
  Because hard drives only spin one direction
 
 :)

What if you are below the equator?

 -- 
 Jonah H. Harris, Senior DBA
 myYearbook.com
 
-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: 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] Research/Implementation of Nested Loop Join optimization

[Alvaro Herrera]

Joshua D. Drake escribió:
 On Fri, 2008-07-25 at 19:31 -0400, Jonah H. Harris wrote:
  On Fri, Jul 25, 2008 at 6:10 PM, Gregory Stark [EMAIL PROTECTED] wrote:
   Manoel Henrique [EMAIL PROTECTED] writes:
  
   Yes, I'm relying on the assumption that backwards scan has the same cost 
   as
   forward scan, why shouldn't it?
  
   Because hard drives only spin one direction
  
  :)
 
 What if you are below the equator?

They spin the same direction here too, thanks :-)  (Coriolis does not
affect much in this case)

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

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

Re: [HACKERS] Research/Implementation of Nested Loop Join optimization

[Tom Lane]

Gregory Stark [EMAIL PROTECTED] writes:
 Manoel Henrique [EMAIL PROTECTED] writes:
 Yes, I'm relying on the assumption that backwards scan has the same cost as
 forward scan, why shouldn't it?

 Because hard drives only spin one direction

Good joke, but to be serious: we expect that forward scans will result
in the kernel doing read-ahead, which will allow overlapping of
CPU work to process one page with the I/O to bring in the next page.
A backwards scan will get no such overlapping and thus be up to 2X
slower, unless the kernel is smart enough to do read-ahead for
descending-order read requests.  Which seems not too probable.  A fairly
typical kernel behavior is that read-ahead is triggered by successive
read() requests without any intervening seek(), and this is impossible
for a backward scan.

(Yes, we do optimize out the seek calls in a forward scan.  IIRC it's
done in fd.c.)

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] Adding WHERE clause to pg_dump

[daveg]

On Fri, Jul 25, 2008 at 11:17:20PM +0100, Gregory Stark wrote:
 Joshua D. Drake [EMAIL PROTECTED] writes:
 
  How do we deal with this?
 
  pg_dump -w last_update_timestamp  ... -t 'table*'
 
  What I see is a recipe for inconsistent, un-restorable backups without a
  user realizing what they have done. The only way to deal with the above
  is:
 
  1. Wildcards aren't allowed if you have -w
  2. You dump everything, if the WHERE clause isn't relevant you just dump
  the whole table
 
 There's always 
 
   3. Apply the WHERE clause to all tables and if there's a table missing
  columns referenced in the where clause then fail with the appropriate
  error.
 
 Which seems like the right option to me. The tricky bit would be how to deal
 with cases where you want a different where clause for different tables. But
 even if it doesn't handle all cases that doesn't mean a partial solution is
 unreasonable.

Actually, Davy's patch does deal with the case where you want a different
where clause for different tables.

-dg


-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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] Research/Implementation of Nested Loop Join optimization

[Alvaro Herrera]

Tom Lane escribió:
 Gregory Stark [EMAIL PROTECTED] writes:
  Manoel Henrique [EMAIL PROTECTED] writes:
  Yes, I'm relying on the assumption that backwards scan has the same cost as
  forward scan, why shouldn't it?
 
  Because hard drives only spin one direction
 
 Good joke, but to be serious: we expect that forward scans will result
 in the kernel doing read-ahead, which will allow overlapping of
 CPU work to process one page with the I/O to bring in the next page.

I wonder if this is spoiled (or rather, the backwards case fixed) by the
attempts to call posix_fadvise() on certain types of scan.

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

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

Re: [HACKERS] Research/Implementation of Nested Loop Join optimization

[Tom Lane]

Alvaro Herrera [EMAIL PROTECTED] writes:
 Tom Lane escribió:
 Good joke, but to be serious: we expect that forward scans will result
 in the kernel doing read-ahead, which will allow overlapping of
 CPU work to process one page with the I/O to bring in the next page.

 I wonder if this is spoiled (or rather, the backwards case fixed) by the
 attempts to call posix_fadvise() on certain types of scan.

Yeah, I started wondering about that too after sending off the above.
The fadvise patch might eliminate the distinction ... on platforms where
fadvise exists and actually works well.

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] Review: DTrace probes (merged version) ver_03

[Alvaro Herrera]

Zdenek Kotala wrote:
 I performed review and I prepared own patch which contains only probes 
 without any issue. I suggest commit this patch because the rest of patch 
 is independent and it can be committed next commit fest after rework.

 I found following issues:

I noticed that CLOG, Subtrans and Multixact probes are added during a
regular Checkpoint, but not during a shutdown flush.  I think the probes
should count that too (probably with the same counter).

In the pgstat_report_activity probe, is it good to call the probe before
taking the fast path out?

In the BUFFER_READ_START probe, we do not include the smgrnblocks()
call, which could be significant since it includes a number of system
calls.

I think BUFFER_HIT and BUFFER_MISS should include the isLocalBuf flag.
I also wonder whether BUFFER_HIT should be called in the block above,
lines 220-238, where we check the found flag, i.e.

if (isLocalBuf)
{
ReadLocalBufferCount++;
bufHdr = LocalBufferAlloc(smgr, blockNum, found);
if (found)
{
LocalBufferHitCount++;
TRACE_POSTGRESQL_BUFFER_HIT(true);  /* local buffer */
}
else
{
TRACE_POSTGRESQL_BUFFER_MISS(true); /* ditto */
}
}
else
{
ReadBufferCount++;

/*
 * lookup the buffer.  IO_IN_PROGRESS is set if the requested block is
 * not currently in memory.
 */
bufHdr = BufferAlloc(smgr, blockNum, strategy, found);
if (found)
{
BufferHitCount++;
TRACE_POSTGRESQL_BUFFER_HIT(false); /* not local */
}
else
{
TRACE_POSTGRESQL_BUFFER_MISS(false);/* ditto */
}
}

(note that this changes the semantics w.r.t. the isExtend flag).


I understand the desire to have DEADLOCK_FOUND, but is there really a
point in having a DEADLOCK_NOTFOUND probe?  Since this code runs every
time someone waits for a lock longer than a second, there would be a lot
of useless counts and nothing useful.

I find it bogus that we include query rewriting in QUERY_PARSE_START/DONE.  
I think query rewriting should be a separate probe.

QUERY_PLAN_START is badly placed -- it should be after the check for
utility commands (alternatively there could be a QUERY_PLAN_DONE in the
fast way out for utility commands, but in that case a is utility flag
would be needed.  I don't see that there's any point in tracing planning
of utility commands though).

Why are there no probes for the v3 protocol stuff?  There should
be probes for Parse, Bind, Execute message processing too, for
completeness.  Also, I wonder if these probes should be in the for(;;)
loop in PostgresMain() instead of sprinkled in the other routines.
I note that the probes in PortalRun and PortalRunMulti are schizophrenic
about whether they include utility functions or not.

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

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

Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723

[Tatsuo Ishii]

 Thanks for the patch :)
 
 Now, I get a different problem, this time with the following code
 intended to materialize paths on the fly and summarize down to a
 certain depth in a tree:
 
 CREATE TABLE tree(
 id INTEGER PRIMARY KEY,
 parent_id INTEGER REFERENCES tree(id)
 );
 
 INSERT INTO tree
 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
 
 WITH RECURSIVE t(id, path) AS (
 VALUES(1,ARRAY[NULL::integer])
 UNION ALL
 SELECT tree.id, t.path || tree.id
 FROM tree JOIN t ON (tree.parent_id = t.id)
 )
 SELECT
 t1.id, count(t2.*)
 FROM
 t t1
 JOIN
 t t2
 ON (
 t1.path[1:2] = t2.path[1:2]
 AND
 array_upper(t1.path,1) = 2
 AND
 array_upper(t2.path,1)  2
 )
 GROUP BY t1.id;
 ERROR: unrecognized node type: 203

Thanks for the report. Here is the new patches from Yoshiyuki against
CVS HEAD. Also I have added your test case to the regression test.

 Please apply the attached patch to help out with tab
 completion in psql.

Thanks. Your patches has been included.
--
Tatsuo Ishii
SRA OSS, Inc. Japan


recursive_query.patch.gz
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] [RFC] Unsigned integer support.

[Ryan Bradetich]

Hello Dann,

On Fri, Jul 25, 2008 at 1:06 PM, Dann Corbit [EMAIL PROTECTED] wrote:

 At the cost of one bit of storage, you have compatible types using

Thanks for your review and feedback!  Unfortunately, I do need the full range
of the unsigned types for the project I am looking at.  The reason I started
working on these types is because it seemed wasteful to use the next size
larger signed integer for the storage type of the unsigned integer.

Thanks for the suggestion!

- Ryan

-- 
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] [RFC] Unsigned integer support.

[Tom Lane]

Ryan Bradetich [EMAIL PROTECTED] writes:
 ... I did have the following
 concern looking through src/backend/utils/adt/int8.c:  There is code that is
 optionally compiled based on the INT64_IS_BUSTED pre-processor define.
 Is this pre-processor define something I should worry about for portability
 with this plan?

I wouldn't worry, really ;-).  Five or more years ago, it seemed
important for PG to work on machines without functional int64 support,
but there is little if any evidence that anyone is using current PG
releases on such platforms.  I might well be the last active PG hacker
who gives a damn about that case at all, and even I long ago stopped
expecting anything beyond core functionality to work on such a machine.
Since your proposed unsigned types certainly aren't core functionality,
I see no reason that they should need to work on INT64_IS_BUSTED
platforms.

 After I get uint types implemented, for fun I might try some benchmarks
 to see if I can detect the int8 overhead on a 32-bit system.

Right, you need to check that before drinking the kool-aid ...

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] [RFC] Unsigned integer support.

[Ryan Bradetich]

Tom,

On Fri, Jul 25, 2008 at 12:32 PM, Tom Lane [EMAIL PROTECTED] wrote:

 Consider the idea of not having any uint4-specific arithmetic operators,
 but instead providing the following:

* assignment casts from int4 and int8 to uint4
  (these throw error if out of range, of course)
* implicit cast from uint4 to int8 (can never fail)

 The effect of providing the latter cast would be that any arithmetic
 involving a uint4 column would automatically be done in int8.  Which
 would make it a shade slower than a native implementation, but probably
 not enough slower to be a problem --- and you'd avoid having to write
 dozens of operators and underlying support functions.  Storing into the
 uint4 column would work fine with no extra notation because of the
 assignment casts.

This is an interesting idea that I will test out tonight.  I did have
the following
concern looking through src/backend/utils/adt/int8.c:  There is code that is
optionally compiled based on the INT64_IS_BUSTED pre-processor define.
Is this pre-processor define something I should worry about for portability
with this plan?

After I get uint types implemented, for fun I might try some benchmarks
to see if I can detect the int8 overhead on a 32-bit system.

 Moreover, you'd avoid cluttering the system with a pile of cross-type
 operators, which we have recently realized are not a good thing, because
 they increase the likelihood of ambiguous operator problems --- see
 http://archives.postgresql.org/pgsql-hackers/2008-06/msg00750.php

Good to know.  Thanks for the link.

 For uint8 you'd have to promote to numeric to guarantee no failure
 in the implicit cast; which is going to be a rather bigger performance
 hit, but I don't really see uint8 as being a type with huge demand.

Hopefully I will not need the uint8 type.  Right now for a project I am
looking at I need the uint2 and uint4 types.  uint8 support can come
later if it is needed or requested.

 Now you probably *will* want cross-type comparison operators, if you
 are going to support indexing of unsigned columns, so that something
 like
uint4col  42
 can be indexed without any casting.  But limiting yourself to the six
 basic comparison operators certainly makes it a much less bulky project.

This sounds excellent!  Hopefully by using these operators I will be able to
avoid most of the casting to int8 for my use, while still providing the
complete functionality for this type.

Thanks again for your review and feedback!

- Ryan

-- 
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] Research/Implementation of Nested Loop Join optimization

[Ron Mayer]

Tom Lane wrote:

Gregory Stark [EMAIL PROTECTED] writes:

Manoel Henrique [EMAIL PROTECTED] writes:

Yes, I'm relying on the assumption that backwards scan has the same cost as
forward scan, why shouldn't it?


G...we expect that forward scans will result
in the kernel doing read-ahead, ...
A backwards scan will get no such overlapping and thus be up to 2X
slower, unless the kernel is smart enough to do read-ahead for
descending-order read requests.  Which seems not too probable. 


Linux's old adaptive readahead patches claimed to[1]:
  It also have methods to detect some less common cases:
  - reading backward
Interestingly the author of that patch used postgres as the example
application that benefits from the patch (30%).

I'm not sure if the backward reading feature got kept
in the simplified on-demand readahead that seems to have
superseded the adaptive readahead stuff in 2.6.23[2].

[1] http://lwn.net/Articles/185469/
[2] 
http://kernelnewbies.org/Linux_2_6_23#head-102af265937262a7a21766ae58fddc1a29a5d8d7


--
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] Research/Implementation of Nested Loop Join optimization

[Tom Lane]

Ron Mayer [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 A backwards scan will get no such overlapping and thus be up to 2X
 slower, unless the kernel is smart enough to do read-ahead for
 descending-order read requests.  Which seems not too probable. 

 Linux's old adaptive readahead patches claimed to[1]:

I didn't say that there were *no* platforms that could do it.

regards, tom lane

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