Re: [HACKERS]

[Harald Armin Massa]

Yuan,

Welcome!

To get you going till someone of quite higher blessing then me can
answer, some helpfull links:

http://www.postgresql.org/developer/

on this page specially the following sections:

http://wiki.postgresql.org/wiki/Developer_FAQ

if you are looking for a concrete job to do

http://www.postgresql.org/docs/faqs.TODO.html

Subscribing to -hackers and saying hello was also a wise thing to do!

Best wishes,

Harald

On Thu, Jun 26, 2008 at 01:42, yuan fang [EMAIL PROTECTED] wrote:
 i am studying the source code of postgresql and want to become a developer
 of it.What should i do?
 thanks

 
 Explore the seven wonders of the world Learn more!



-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pidgeon
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Simon Riggs]

On Wed, 2008-06-25 at 23:34 -0400, Robert Haas wrote:
 I can predict that Tom will say that the planning time it would take
 to avoid this problem isn't justified by the number of queries that it
 would improve.  

 That's possible, but it's unfortunate that there's no
 way to fiddle with the knobs and get the planner to do this kind of
 thing when you want it to.

I don't think we should invent a new parameter for each new
optimisation. We would soon get swamped.

IMHO we should have a single parameter which indicates how much planning
time we consider acceptable for this query. e.g.

 optimization_level = 2 (default), varies 1-3

Most automatic optimisation systems allow this kind of setting, whether
it be a DBMS, or compilers (e.g. gcc). 

We should agree a simple framework so that each new category of
optimization can be described as being a level X optimisation, or
discarded as being never worth the time. We do this with error messages,
so why not do this with something to control planning time?

-- 
 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] Latest on CITEXT 2.0

[Martijn van Oosterhout]

On Wed, Jun 25, 2008 at 11:47:39AM -0700, David E. Wheeler wrote:
 * There seem to still be some implicit CASTS to text that I'd like to  
 duplicate. For example,  select '192.168.1.2'::cidr::text;` works, but  
 `select '192.168.1.2'::cidr::citext;` does not. Where can I find the C  
 functions that do these casts for TEXT so that I can put them to work  
 for citext, too? The internal cast functions used in the old citext  
 distribution don't exist at all on 8.3.

Hmm, casts to/from text are somewhat magic in postgres. They are
implemented by calling the usual type input/output function. I have no
idea how to extend that to other types.

 * There are casts from text that I'd also like to harness for use by  
 citext, like `cidr(text)`. Where can I find these C functions as well?  
 (The upshot of this and the previous points is that I'd like citext to  
 be as compatible with TEXT as possible, and I just need to figure out  
 how to fill in the gaps in that compatibility.)

As above, they're probably not as seperate functions but a special hack
inthe casting code.

 * Regular expression and LIKE comparisons using the the operators  
 properly work case-insensitively, but functions like replace() and  
 regexp_replace() do not. Should they? and if so, how can I make them  
 do so?

Regexes have case-insensetive modifiers, don't they? In which case I
don't think it'd be becessary.

 * As for my C programming, well, what's broken? I'm especially  
 concerned that I pfree variables appropriately, but I'm not at all  
 clear on what needs to be freed. Martijn mentioned before that btree  
 comparison functions free memory, but I'm such a C n00b that I don't  
 know what that actually means for my implementation. I'd actually  
 appreciate a bit of pedantry here. :-)

When creating an index, your comparison functions are going ot be
called O(N log N) times. If they leak into a context that isn't
regularly freed you may have a problem. I'd suggest loking at how the
text comparisons do it. PG_FREE_IF_COPY() is probably a good idea
because the incoming tuples may be detoasted.

 * Am I in fact getting an appropriate nul-terminated string in my  
 cilower() function using this code?
 
 char * str  = DatumGetCString(
 DirectFunctionCall1( textout, PointerGetDatum( arg ) )
 );

Yes.

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


signature.asc
Description: Digital signature

[HACKERS] Join Removal/ Vertical Partitioning

[Simon Riggs]

There are common cases where we want to remove unwanted joins from
queries, especially with view and Object Relational Mapping systems such
as Hibernate etc.. (I've mentioned this before on -hackers or -perform,
but I can't find the links)

Typical case is where we have a class that has a subclass present fairly
infrequently, so we want to physically separate the subclass for
performance. It's easy to separate the data, but then much harder to get
the SQL to recognise that is what we have done. The same problem also
occurs in Data Warehousing and is typically known as Vertical
Partitioning in that context.

The attached example shows this for one and two subclasses. 
[joinrem.sql]

Why do we care? Because the extra join hurts performance considerably.
pgbench test files attached, executed using dual CPU system with
pgbench -n -f filename -c 2 - t 3

Access to 1 table   14,500 tps
Access to 2 tables   9,000 tps
Access to 3 tables   7,000 tps

We might typically expect there to be many subclasses that follow this
pattern (as well as some subclasses that are so frequent we would want
to merge them with the main class table, but that is not relevant to
discussion here), so the number of subclasses could be quite large.

explain select c_c1 from wholeclass2tables where pk = 6;

  QUERY
PLAN   
---
Nested Loop Left Join  (cost=0.00..16.55 rows=1 width=4)
   Join Filter: (c.pk = sc.pk)
   -  Index Scan using class_pkey on class c  (cost=0.00..8.27 rows=1
width=8)
 Index Cond: (pk = 6)
   -  Index Scan using subclass_pkey on subclass sc  (cost=0.00..8.27
rows=1 width=4)
 Index Cond: (sc.pk = 6)
(6 rows)

The EXPLAIN shows that we access both tables, even though the access to
subclass is not required to correctly resolve the query in *all*
cases. Similar plan for 3+ table access.

The join is removable because

* rows are returned by the query whether or not rows exist in subclass
* every row in class matches at *most* one row in subclass because
the join columns are unique on both sides of the join.
* the join operator function is IMMUTABLE (and is therefore able to be
optimised away when circumstances allow)

(Note that the FK link between the tables is not required to prove this,
it is just shown because that is the way we would typically do this).

Why can't we do this manually? We can do the join removal manually by
creating different sets of views that mention or don't mention the
tables. If we have say 5 sub-classes, then to optimise queries we would
need to produce 5! = 120 views, all defined with the different
combinations of tables that we might want to access. This is a
management nightmare, but so is the idea that we might need to run 5
table joins when only direct access to a single table is required.


We can check for removal of a rel by

1. inspecting the target list for the query to see if there are rels
that do not provide any attributes. (We might also use equivalence
classes to recode the targetlist to minimise the numbers of tables
touched, but I think that might be overkill). 

2. checking the rel is on the excluding side of an outer join (i.e. the
right hand table in a left outer join)

3. checking that the join columns on the rel are all the columns of any
unique index on the rel. (If we join on *more* or less columns than are
in the index then we must still do the join.)

Once we have marked all rels that are removable we then need to check
that we can still make one rel using the remaining tables. In some cases
that may not be possible just yet, because of the limited inference
possibilities across outer join boundaries, a problem discussed
elsewhere on -hackers. But it seems possible to allow removal of rels
mentioned in exactly one join.
i.e. given A - B - C then B is not removable, A and C are. 

We do join removal as part of the prep before join planning takes place.
First we would annotate which rels have attributes that form part of the
targetlist during build_base_rel_tlists(). During or immediately after
deconstruct_jointree() we can attempt to remove joins by testing the
other conditions in order.

It looks to me that there would be little additional planning time for
cases where this optimisation would not apply.

I'm not sure whether it would be best to attempt to remove joins before
we have established equivalence classes or afterwards. In the longer
term, afterwards would be best. ISTM it will be easier to remove joins
before we attempt to establish a join order, yet many of the tests run
during join order selection would need to be run to test join removal.
So some thoughts on where to attempt this would be very useful.

Are there specific problems foreseen with this? Would working on this be
sensible before the outer join equivalence problem has been solved?

-- 
 Simon Riggs   

[HACKERS] plpgsql: Is ELSE IF supported or not?

[Marko Kreen]

Docs seems to say it is, but following function fails to compile:

create function err_else() returns void as $$
begin
if 1 = 1 then
else if 1 = 2 then
end if;
end;
$$ language plpgsql;

ERROR:  syntax error at or near ;
LINE 6: end;

Version 8.3.3.

-- 
marko

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

Re: [HACKERS] plpgsql: Is ELSE IF supported or not?

[Pavel Stehule]

hello

2008/6/26 Marko Kreen [EMAIL PROTECTED]:
 Docs seems to say it is, but following function fails to compile:

 create function err_else() returns void as $$
 begin
if 1 = 1 then
else if 1 = 2 then
end if;
 end;
 $$ language plpgsql;

 ERROR:  syntax error at or near ;
 LINE 6: end;

use elseif or elsif :)

Pavel


 Version 8.3.3.

 --
 marko

 --
 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] plpgsql: Is ELSE IF supported or not?

[Marko Kreen]

On 6/26/08, Pavel Stehule [EMAIL PROTECTED] wrote:
  2008/6/26 Marko Kreen [EMAIL PROTECTED]:
  Docs seems to say it is, but following function fails to compile:
  
   create function err_else() returns void as $$
   begin
  if 1 = 1 then
  else if 1 = 2 then
  end if;
   end;
   $$ language plpgsql;
  
   ERROR:  syntax error at or near ;
   LINE 6: end;

 use elseif or elsif :)

Yeah, I know.  Just the docs say this is one form of the IF statement:

  IF ... THEN ... ELSE IF

Although now that i read it more, the actual form is:

  ELSE
 IF THEN
 END IF
  END IF;

That is - the ELSE starts new block unconditionally and ignores any IF
that follows.  Later the IF can be part of new block as usual.  Huh.

This is confusing.  I suggest removing the ELSE IF as one of the forms
because it is not.

-- 
marko

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

Re: [HACKERS] plpgsql: Is ELSE IF supported or not?

[Pavel Stehule]

2008/6/26 Marko Kreen [EMAIL PROTECTED]:
 On 6/26/08, Pavel Stehule [EMAIL PROTECTED] wrote:
  2008/6/26 Marko Kreen [EMAIL PROTECTED]:
  Docs seems to say it is, but following function fails to compile:
  
   create function err_else() returns void as $$
   begin
  if 1 = 1 then
  else if 1 = 2 then
  end if;
   end;
   $$ language plpgsql;
  
   ERROR:  syntax error at or near ;
   LINE 6: end;

 use elseif or elsif :)

 Yeah, I know.  Just the docs say this is one form of the IF statement:

  IF ... THEN ... ELSE IF

 Although now that i read it more, the actual form is:

  ELSE
 IF THEN
 END IF
  END IF;

 That is - the ELSE starts new block unconditionally and ignores any IF
 that follows.  Later the IF can be part of new block as usual.  Huh.

 This is confusing.  I suggest removing the ELSE IF as one of the forms
 because it is not.


this is same in all procedural languages

Pavel

 --
 marko


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

Re: [HACKERS] Creating a VIEW with a POINT column

[Dimitri Fontaine]

Le jeudi 26 juin 2008, Tom Lane a écrit :
 Yeah.  The GROUP BY case is even more annoying, because we *have* the
 planner/executor infrastructure to do it via hashing; but the parser
 barfs immediately if there is not btree opclass support for the type.
 I'm not sure how to fix the parser and the parsetree representation
 to be agnostic about hash versus sort implementations --- any thoughts?

Would it be possible to add some semantics to the operator itself?
I'm thinking about indicating that an operator is the equality one without 
resorting to OPCLASS and while at it adding the notion of transitivity to 
operators (which you'd like to abuse for some joins conditions iirc).

The CREATE OPERATOR =(type, type) (... EQUALITY ...) would give the 
information to PostgreSQL and its planner. I'm not sure it current operator 
catalog allows us to have a unique constraint for an equality operator for a 
given couple of (LEFTARG, RIGHARG), though.

Would this help?
-- 
dim


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

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Jeff McKenna]

Thanks Hiroshi.  Unfortunately libpq.dll does not compile with MSVC  
2003.  Has anyone successfully compiled libpq for 8.3.3 (or CVS head)  
with MSVC 2003??



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:


Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. ...
Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
  cd ..\..
  echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src

It may be necessary to investigate the reference relation of VC2003.
Does someone notice some?
Regards,
Hiroshi Saito
- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 





Hello,
I am trying to compile libpq.dll with MSVC 2003 on windows, using   
postgresql-8.3.3, but I get the following compile error:
  Creating library .\Release\libpqdll.lib and object .\Release  
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external symbol   
__dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external symbol   
__dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual   
Studio .NET 2003

VC7\BIN\nmake.exe' : return code '0x2'
Stop.
Does anyone have any ideas how to solve this??
thanks.
-jeff
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.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



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

[HACKERS] get_relation_stats_hook()

[Simon Riggs]

Currently we have a plugin capability for get_relation_info_hook(), but
no corresponding capability for statistics info.

So, all calls to SearchSysCache would be replaced with a call to
get_relation_info_hook(), if present.

Any objections, thoughts?

-- 
 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] plpgsql: Is ELSE IF supported or not?

[Mark Mielke]

Pavel Stehule wrote:

2008/6/26 Marko Kreen [EMAIL PROTECTED]:
  

Although now that i read it more, the actual form is:

 ELSE
IF THEN
END IF
 END IF;

That is - the ELSE starts new block unconditionally and ignores any IF
that follows.  Later the IF can be part of new block as usual.  Huh.

This is confusing.  I suggest removing the ELSE IF as one of the forms
because it is not.



this is same in all procedural languages
  


I don't agree with this statement. In all procedural languages, or 
probably most, they usually make ELSE IF special, in that you don't 
need to close the block twice as per above. The ELSE IF is not actually 
special in PL/SQL, so it is not a special form. The ELSE can contain a 
block, which contain any statement, including a nested IF statement. Why 
not describe ELSE WHILE as well based upon the logic that ELSE IF is 
valid? :-)


Now, if it were to say an alternative form of ELSEIF is to nest IF 
statement like so: ...


Cheers,
mark

--
Mark Mielke [EMAIL PROTECTED]

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

[Josh Berkus]

Bruce, KaiGai,

Is PGACE ready for CommitFest, now?

--Josh

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

Re: [HACKERS] get_relation_stats_hook()

[Alvaro Herrera]

Simon Riggs wrote:
 Currently we have a plugin capability for get_relation_info_hook(), but
 no corresponding capability for statistics info.
 
 So, all calls to SearchSysCache would be replaced with a call to
 get_relation_info_hook(), if present.

I assume you meant get_relation_stats_hook in the last paragraph, but I
can't understand what you mean with SearchSysCache (surely that's not
it.)

-- 
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] get_relation_stats_hook()

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 Currently we have a plugin capability for get_relation_info_hook(), but
 no corresponding capability for statistics info.

 So, all calls to SearchSysCache would be replaced with a call to
 get_relation_info_hook(), if present.

Surely you didn't mean ALL calls.  Please be more specific about what
you're proposing.

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] Proposal of SE-PostgreSQL patches [try#2]

[KaiGai Kohei]

Hi,

The following patch set (r926) are updated one toward the latest CVS head,
and contains some fixes in security policy and documentation.

I want to push them for the reviewing queue of CommitFest:Jul.

[1/4] Core facilities of PGACE/SE-PostgreSQL

http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r926.patch

[2/4] --enable-selinux option of pg_dump/pg_dumpall

http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r926.patch

[3/4] Default security policy for SE-PostgreSQL

http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r926.patch

[4/4] Documentation updates
http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r926.patch

Thanks,

KaiGai Kohei wrote:
 Hi,
 
 The following patch set is our second proposals of SE-PostgreSQL.
 
 It contains many of fixes and improvements from the previous version.
 Please add them a reviwing queue of the next commit fest.
 
 Thanks,
 
 List of Patches
 ===
 
 [1/4] Core facilities of PGACE/SE-PostgreSQL
 
 http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r914.patch
 
 [2/4] --enable-selinux option of pg_dump/pg_dumpall
 
 http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r914.patch
 
 [3/4] Default security policy for SE-PostgreSQL
 
 http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r914.patch
 
 [4/4] Documentation updates
 
 http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r914.patch
 
 We can provide a quick overview for SE-PostgreSQL at:
 http://code.google.com/p/sepgsql/wiki/WhatIsSEPostgreSQL
 http://sepgsql.googlecode.com/files/PGCON20080523.pdf
 
 Compile and Installation
 
 
 The following items are requirements of SE-PostgreSQL.
   - Fedora 8 or later system
   - SELinux is enabled, and working
   - kernel-2.6.23 or later
   - selinux-policy and selinux-policy-devel v3.0.8 or later
   - libselinux, policycoreutils, checkpolicy
 
 The followings are step by step installation.
 
   $ cvs -z3 -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot \
 export -r HEAD -d pgsql
   $ cd pgsql
   $ patch -p1  sepostgresql-sepgsql-8.4devel-3-r914.patch
   $ patch -p1  sepostgresql-pg_dump-8.4devel-3-r914.patch
   $ patch -p1  sepostgresql-policy-8.4devel-3-r914.patch
   $ patch -p1  sepostgresql-docs-8.4devel-3-r914.patch
   $ ./configure --enable-selinux
   $ make
   $ make -C ./contrib/sepgsql_policy
 
   $ su
   # /usr/sbin/semodule -i ./contrib/sepgsql_policy/sepostgresql.pp  ... [1]
   # make install
   # /sbin/restorecon -R /usr/local/pgsql
 
   $ mkdir -p $PGDATA
   $ chcon -t postgresql_db_t -R $PGDATA
   $ initdb
   $ pg_ctl start
 
 [1] If selinux-policy-3.4.2 or later is installed on your system,
 install sepostgresql-devel.pp instead.
 In this version, most of SE-PostgreSQL's policy are got mainlined.
 
 
 Updates from the previous version
 =
 
 o A new type of security_label has gone
 
 In the previous one, security_context system column is declared as
 security_label type. This type had its input handler, and it translated
 a given text representation into an internal Oid value with looking up
 pg_security system catalog. If it's not found, the input handler inserts
 a new entry automatically.
 
 The following query can show the reason why this design is problematic.
 
   SELECT 'system_u:object_r:sepgsql_db_t'::security_label;
 
 This query seems to us read-only, but it has a possibility to insert
 a new tuple into pg_security implicitly.
 
 In this version, security_context system column is re-defined as a TEXT
 type attribute, and a given text representation is translated into internal
 identifier (Oid) just before insert or update a tuple. This design change
 enables to make sure pg_security is not modified in read-only queries.
 
 
 o Query modification has gone.
 
 In the previous one, SE-PostgreSQL modified WHERE/JOIN ON clause to apply
 tuple-level access controls, but its implementation is a bit complicated.
 In addition, this design had a possibility to conflict with a new MS patent.
 
 Now we put a hook on ExecScan to apply tuple-level access controls.
 It enables to reduce code complexity and avoid patent conflicts.
 
 
 o Scanning with SnapshotSelf has gone.
 
 In the previous one, we had to scan some system catalogs with SnapshotSelf
 mode at three points (pg_class, pg_largeobject and pg_security).
 
   * When we defines a relation on heap_create_with_catalog(), a tuple of
 pg_class and several tuples of pg_attribute are inserted within same
 command id.
 A tuple of pg_class has to be refered just before inserting tuples of
 pg_attribute, because a new column inherits the security context of its
 parent relation in the default. But we cannot find it because these are
 inserted within same command id and SnapshotNow scanning mode ignores
 these tuples. We cannot invoke 

Re: [HACKERS] Latest on CITEXT 2.0

[David E. Wheeler]

On Jun 26, 2008, at 03:28, Martijn van Oosterhout wrote:


Hmm, casts to/from text are somewhat magic in postgres. They are
implemented by calling the usual type input/output function. I have no
idea how to extend that to other types.


Oh. Okay. Perhaps I won't worry about it just now, then.

As above, they're probably not as seperate functions but a special  
hack

inthe casting code.


Okay.


Regexes have case-insensetive modifiers, don't they? In which case I
don't think it'd be becessary.


They do, but replace(), split_part(), strpos(), and translate() do not.


When creating an index, your comparison functions are going ot be
called O(N log N) times. If they leak into a context that isn't
regularly freed you may have a problem. I'd suggest loking at how the
text comparisons do it. PG_FREE_IF_COPY() is probably a good idea
because the incoming tuples may be detoasted.


Okay. I'll have a look at varlena.c, then.


* Am I in fact getting an appropriate nul-terminated string in my
cilower() function using this code?

   char * str  = DatumGetCString(
   DirectFunctionCall1( textout, PointerGetDatum( arg ) )
   );


Yes.


Great, I thought so (since it made the failures go away). Many thanks.

David


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

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

[KaiGai Kohei]

Josh Berkus wrote:

Bruce, KaiGai,

Is PGACE ready for CommitFest, now?

--Josh


Yes, it's ready now.

See the following message:
  http://archives.postgresql.org/pgsql-hackers/2008-06/msg00980.php
  http://archives.postgresql.org/pgsql-hackers/2008-06/msg00840.php

Thanks,
--
KaiGai Kohei [EMAIL PROTECTED]

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

Re: [HACKERS] Regd: TODO Item

[Ramya Chandrasekar]

Regd: TODO Item: Optimizer:: Have EXPLAIN ANALYZE issue NOTICE messages when
the estimated and actual row counts differ by a specified percentage.



Hi,



 We are a team of two graduate students (volunteers) who plan to work on the
above problem from the TODO list of the Optimizer. Being new to Postgres, we
plan on following the instructions from the developers FAQ to perform the
necessary changes.



Kindly let us know if you have any other suggestions that we need to keep in
mind?

Also, can anyone give a suggested value of the Specified Percentage in the
problem statement of the TO DO list?



We look forward on gathering any essential information form this group.



Thank you,



Ramya

[HACKERS] proposal: to_ascii(bytea)

[Pavel Stehule]

Hello,

Changes related to convert* functions in postgresql 8.3 has impact on
to_ascii function.

Before 8.3 I could do:
postgres=#  select to_ascii(convert('Příliš žlutý kůň' using
utf8_to_iso_8859_2),'latin2');
 to_ascii
--
 Prilis zluty kun
(1 row)

but convert_to function return bytea now, and I can't to call it
without unwanted changes in content of translated text.

postgres=# SELECT to_ascii(convert_to('Příliš žlutý kůň',
'latin2')::text,'latin2');
   to_ascii
---
 P\370\355li\271 \276lut\375 k\371\362
(1 row)

ugly hack for it:
postgres=# create function to_ascii(bytea, name) returns text as
'to_ascii_encname' language internal;
CREATE FUNCTION
postgres=#
postgres=# SELECT to_ascii(convert_to('Příliš žlutý kůň', 'latin2'),'latin2');
 to_ascii
--
 Prilis zluty kun
(1 row)

Regards
Pavel Stehule

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

Re: [HACKERS] get_relation_stats_hook()

[Simon Riggs]

On Thu, 2008-06-26 at 11:18 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Currently we have a plugin capability for get_relation_info_hook(), but
  no corresponding capability for statistics info.
 
  So, all calls to SearchSysCache would be replaced with a call to
  get_relation_info_hook(), if present.
 
 Surely you didn't mean ALL calls.  Please be more specific about what
 you're proposing.

The statistics relation STATRELATT is accessed in a few places in the
planner. Since it is in the syscache it is accessed directly from there.
I would like to add hooks so that stats data can come from somewhere
else other than the syscache for tables, just as we can already do with
get_relation_stats_hook(). These new changes would complete the existing
feature to ensure it is fully usabled in the way originally intended.

In selfunc.c: There are 3 calls to SearchSysCache(STATRELATT,...).

In lsyscache.c: There is 1 call to SearchSysCache(STATRELATT...) in
get_attavgwidth() and 2 calls to SysCacheGetAttr(STATRELATT...) in
get_attstatsslot().

Calls to SearchSysCache(STATRELATT...) would be replaced by a call to an
external module, if present, with a function pointer to
get_relation_stats_hook(). This returns a tuple with stats info about
that relation.

Calls to SysCacheGetAttr(STATRELATT...) would be replaced by a call to
an external module, if present with a function pointer to
get_attribute_stats_hook(). This returns a stats slot.

The call in ANALYZE would not be touched.

-- 
 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] Latest on CITEXT 2.0

[David E. Wheeler]

Thanks a million for your answers, Martijn. I just have some more  
stupid questions, if you could bear with me.


On Jun 26, 2008, at 03:28, Martijn van Oosterhout wrote:


When creating an index, your comparison functions are going ot be
called O(N log N) times. If they leak into a context that isn't
regularly freed you may have a problem. I'd suggest loking at how the
text comparisons do it. PG_FREE_IF_COPY() is probably a good idea
because the incoming tuples may be detoasted.


Okay, I see that text_cmp in varlena doesn't use PG_FREE_IF_COPY(),  
and neither do text_smaller nor text_larger (which just dispatch to  
text_cmp anyway).


The operator functions *do* use PG_FREE_IF_COPY(). So I'm guessing  
it's these functions you're talking about. However, my implementation  
just looks like this:


Datum citext_ne (PG_FUNCTION_ARGS) {
// Fast path for different-length inputs. Okay for canonical  
equivalence?

if (VARSIZE(PG_GETARG_TEXT_P(0)) != VARSIZE(PG_GETARG_TEXT_P(1)))
PG_RETURN_BOOL( 1 );
PG_RETURN_BOOL( citextcmp( PG_ARGS ) != 0 );
}

I don't *thinkI any variables are copied there. citextcmp() is just  
this:


int citextcmp (PG_FUNCTION_ARGS) {
// XXX These are all just references to existing structures, right?
text * left  = PG_GETARG_TEXT_P(0);
text * right = PG_GETARG_TEXT_P(1);
return varstr_cmp(
cilower( left ),
VARSIZE_ANY_EXHDR(left),
cilower( right ),
VARSIZE_ANY_EXHDR(right)
);
}

Again, no copying. cilower() does copy:

intindex, len;
char * result;

index  = 0;
len= VARSIZE(arg) - VARHDRSZ;
result = (char *) palloc( strlen( str ) + 1 );

for (index = 0; index = len; index++) {
result[index] = tolower((unsigned char) str[index] );
}
// XXX I don't need to pfree result if I'm returning it, right?
return result;

But the copied value is returned. Hrm…it should probably be pfreed  
somewhere, yes?


So I'm wondering if I should change citextcmp to pfree values?  
Something like this:


text * left  = PG_GETARG_TEXT_P(0);
text * right = PG_GETARG_TEXT_P(1);
char * lcstr = cilower( left  );
char * rcstr = cilower( right );

int result = varstr_cmp(
cilower( left ),
VARSIZE_ANY_EXHDR(left),
cilower( right ),
VARSIZE_ANY_EXHDR(right)
);

pfree( lcstr );
pfree( rcstr );
return result;

This is the only function that calls cilower(). And I *think* it's the  
only place where values are copied or memory is allocated needing to  
be freed. Does that sound right to you?


On a side note, I've implemented this pretty differently from how the  
text functions are implemented in varlena.c, just to try to keep  
things succinct. But I'm wondering now if I shouldn't switch back to  
the style used by varlena.c, if only to keep the style the same, and  
thus perhaps to increase the chances that citext would be a welcome  
contrib addition. Thoughts?


Many thanks again. You're a great help to this C n00b.

Best,

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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Chris Browne]

[EMAIL PROTECTED] (Simon Riggs) writes:
 On Wed, 2008-06-25 at 23:34 -0400, Robert Haas wrote:
 I can predict that Tom will say that the planning time it would take
 to avoid this problem isn't justified by the number of queries that it
 would improve.  

 That's possible, but it's unfortunate that there's no
 way to fiddle with the knobs and get the planner to do this kind of
 thing when you want it to.

 I don't think we should invent a new parameter for each new
 optimisation. We would soon get swamped.

 IMHO we should have a single parameter which indicates how much planning
 time we consider acceptable for this query. e.g.

  optimization_level = 2 (default), varies 1-3

 Most automatic optimisation systems allow this kind of setting, whether
 it be a DBMS, or compilers (e.g. gcc). 

 We should agree a simple framework so that each new category of
 optimization can be described as being a level X optimisation, or
 discarded as being never worth the time. We do this with error messages,
 so why not do this with something to control planning time?

Is there something more parametric that we could use to characterize
this?

That is, to attach some value that *does* have some numeric
interpretation?

I don't quite have a for instance, but here's some thoughts on
modelling this...

 - If there is some query optimization option/node that clearly adds
   to planning cost in a linear (or less) fashion, then it would be
   meaningful to mark it as linear, and we'd be fairly certain to
   validate any linear options.

 - There would also be options/nodes that have a multiplicative effect
   on planning time.

 - Thirdly, there are options/nodes (particularly when considering
   cases of multiple joins) where there is a polynomial/exponential
   effect on query planning.

I could see:

  a) Evaluating which roads to consider from a
 linear/multiplicative/exponential perspective, which would look
 a lot like level 1, level 2, level 3.

  b) Estimating  values, and, in effect, trying to model
 the amount of planning effort, and dropping out sets of routes
 that are expected to make the effort exceed [some value].

Sane?  Silly?
-- 
cbbrowne,@,linuxfinances.info
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
STATED REASON DOES NOT COMPUTE WITH PROGRAMMED FACTS...

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

Re: [HACKERS] Latest on CITEXT 2.0

[Alvaro Herrera]

David E. Wheeler wrote:

 The operator functions *do* use PG_FREE_IF_COPY(). So I'm guessing it's 
 these functions you're talking about. However, my implementation just 
 looks like this:

 Datum citext_ne (PG_FUNCTION_ARGS) {
 // Fast path for different-length inputs. Okay for canonical  
 equivalence?
 if (VARSIZE(PG_GETARG_TEXT_P(0)) != VARSIZE(PG_GETARG_TEXT_P(1)))
 PG_RETURN_BOOL( 1 );
 PG_RETURN_BOOL( citextcmp( PG_ARGS ) != 0 );
 }

PG_GETARG_TEXT_P can detoast the datum, which creates a copy.

-- 
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] [0/4] Proposal of SE-PostgreSQL patches

[Josh Berkus]

KaiGai Kohei wrote:

Josh Berkus wrote:

Bruce, KaiGai,

Is PGACE ready for CommitFest, now?

--Josh


Yes, it's ready now.


OK, added to CommitFest.

--Josh

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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Robert Haas]

 IMHO we should have a single parameter which indicates how much planning
 time we consider acceptable for this query. e.g.

  optimization_level = 2 (default), varies 1-3

 Most automatic optimisation systems allow this kind of setting, whether
 it be a DBMS, or compilers (e.g. gcc).

It's my understanding that the philosophy of the PGDG in the past has
been to avoid putting any kind of hints into the system, focusing
rather an improving the planning of queries.  A quick Google search
turns up, for example:

http://archives.postgresql.org/pgsql-performance/2003-12/msg00181.php

Now, perhaps the thinking on this has changed, but a global knob like
this strikes me as a bad idea.  If Tom is right that improving the
plan on queries like this would result in an exponential increase in
planning time, then it's certainly important not to paint with too
broad a brush.  It would really be best to be able to tell the planner
which specific part of the query may be susceptible to this type of
optimization, because you could easily have many places in a
complicated query that would need to be analyzed, and if the planning
time is going to be a problem then we don't want to overplan the
entire query just to fix the problem in one particular spot.  And we
certainly don't want to do a whole bunch of other, unrelated,
expensive optimizations at the same time.

If one were to add a hint, I think the hint should tell the planner:
Hey, see this left join?  Well, computing the right-hand side of this
thing is going to take forever unless we get some information to help
us out.  So please do all of your limit and filter operations on the
left-hand side first, and then if you have any rows left, then
evaluate the right-hand side for just the values that matter.  i.e. in
the example query:

SELECT * FROM ab LEFT OUTER JOIN (bc JOIN cd ON bc.c = cd.d) ON ab.b =
bc.b WHERE ab.a = 2

...please look up the rows in ab where ab.a = 2.  If you find any,
then make a hash table of all the values you find for b among those
rows.  Then when you evaluate (bc JOIN cd ON bc.c = cd.d) you can
filter bc for rows where bc.b is in the hash table.

This might not be a good query plan in the average case, but there are
definitely instances where you might want to force this behavior.  In
fact, even if you had to do it as a nested loop (re-evaluating the bc
JOIN cd clause for each possible value of b) there are still cases
where it would be a big win.  Of course the nicest thing would be for
the planner to realize on its own that the right-hand side of the join
is going to generate a gazillion rows and the left-hand side is going
to generate one, but maybe (as Tom and the OP suggested) that is
expecting too much (though I confess I don't quite see why).

...Robert

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

Re: [HACKERS] Latest on CITEXT 2.0

[David E. Wheeler]

On Jun 26, 2008, at 09:19, Alvaro Herrera wrote:


PG_GETARG_TEXT_P can detoast the datum, which creates a copy.


Thanks. I've just completely refactored things to look more like the  
approach taken by varlena.c, both in terms of when stuff gets freed  
and in terms of coding style. It's more verbose, but I feel much more  
comfortable with memory management now that I'm following a known  
implementation more closely. :-)


So now I've changed citextcmp to this:

static int
citextcmp (text * left, text * right)
{
char * lcstr, * rcstr;
intresult;

lcstr = cilower( left  );
rcstr = cilower( right );

result = varstr_cmp(
cilower( left ),
VARSIZE_ANY_EXHDR(left),
cilower( right ),
VARSIZE_ANY_EXHDR(right)
);

pfree( lcstr );
pfree( rcstr );
return result;
}

And now all of the operator functions are freeing memory using  
PG_FREE_IF_COPY() like this:


Datum
citext_cmp(PG_FUNCTION_ARGS)
{
text * left  = PG_GETARG_TEXT_PP(0);
text * right = PG_GETARG_TEXT_PP(1);
int32  result;

result = citextcmp(left, right);

PG_FREE_IF_COPY(left, 0);
PG_FREE_IF_COPY(right, 1);

PG_RETURN_INT32( result );
}


The only functions that don't do that are citext_smaller() and  
citext_larger():


Datum
citext_smaller(PG_FUNCTION_ARGS)
{
text * left  = PG_GETARG_TEXT_PP(0);
text * right = PG_GETARG_TEXT_PP(1);
text * result;

result = citextcmp(left, right)  0 ? left : right;
PG_RETURN_TEXT_P(result);
}

This is just how varlena.c does it, but I am wondering if something  
*should* be freed there.


Thanks a bunch!

Best,

David


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

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Hiroshi Saito]

Hi.

Um, Please try this.

#includewindows.h
extern void __cdecl _dosmaperr( unsigned long oserrno );
int main(int argc, char *argv[])
{
   _dosmaperr(GetLastError());
   return(0);
}

Can errorless compile be performed? 


Regards,
Hiroshi Saito

- Original Message - 
From: Jeff McKenna [EMAIL PROTECTED]

To: pgsql-hackers pgsql-hackers@postgresql.org
Sent: Thursday, June 26, 2008 10:04 PM
Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3


Thanks Hiroshi.  Unfortunately libpq.dll does not compile with MSVC  
2003.  Has anyone successfully compiled libpq for 8.3.3 (or CVS head)  
with MSVC 2003??



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:


Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. ...
Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
  cd ..\..
  echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src

It may be necessary to investigate the reference relation of VC2003.
Does someone notice some?
Regards,
Hiroshi Saito
- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 





Hello,
I am trying to compile libpq.dll with MSVC 2003 on windows, using   
postgresql-8.3.3, but I get the following compile error:
  Creating library .\Release\libpqdll.lib and object .\Release  
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external symbol   
__dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external symbol   
__dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual   
Studio .NET 2003

VC7\BIN\nmake.exe' : return code '0x2'
Stop.
Does anyone have any ideas how to solve this??
thanks.
-jeff
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.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



--
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] Planner creating ineffective plans on LEFT OUTER joins

[Simon Riggs]

On Thu, 2008-06-26 at 12:36 -0400, Robert Haas wrote:
  IMHO we should have a single parameter which indicates how much planning
  time we consider acceptable for this query. e.g.
 
   optimization_level = 2 (default), varies 1-3
 
  Most automatic optimisation systems allow this kind of setting, whether
  it be a DBMS, or compilers (e.g. gcc).
 
 It's my understanding that the philosophy of the PGDG in the past has
 been to avoid putting any kind of hints into the system, focusing
 rather an improving the planning of queries.

It's not a specific hint, its a general goal setting. Providing
information to the optimizer about our goals is not a universally bad
thing; telling it to force a particular plan against its better
judgement probably is.

For example, gcc has exactly this kind of optimization mode. -O2 should
be acceptable to us, but an option like -fsplit-ivs-in-unroller probably
isn't.

 If one were to add a hint, I think the hint should tell the planner:
 Hey, see this left join? 

Now that *is* a hint.

-- 
 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] get_relation_stats_hook()

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 Surely you didn't mean ALL calls.  Please be more specific about what
 you're proposing.

 The statistics relation STATRELATT is accessed in a few places in the
 planner. Since it is in the syscache it is accessed directly from there.
 I would like to add hooks so that stats data can come from somewhere
 else other than the syscache for tables, just as we can already do with
 get_relation_stats_hook().

Well, defining the hooks as replacing STATRELATT lookups seems the wrong
level of abstraction.  What if you want to insert stats that can't be
represented by the current pg_statistic definition?  Even if there's no
functional limitation, cons'ing up a dummy pg_statistic tuple seems the
hard way to do it --- we didn't define get_relation_info_hook as working
by supplying made-up catalog rows.  Furthermore, many of the interesting
cases that someone might want to hook into are code paths that don't
even try to look up a pg_statistic row because they know there won't be
one, such as two out of the three cases in examine_variable().

I think you need to move up a level, and perhaps refactor some of the
existing code to make it easier to inject made-up stats.

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] Planner creating ineffective plans on LEFT OUTER joins

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 On Thu, 2008-06-26 at 12:36 -0400, Robert Haas wrote:
 It's my understanding that the philosophy of the PGDG in the past has
 been to avoid putting any kind of hints into the system, focusing
 rather an improving the planning of queries.

 It's not a specific hint, its a general goal setting.

Right.  There are definitely places where we've made engineering
judgements to not attempt a particular type of optimization because it'd
be too expensive compared to the typical payoff.  Simon's idea has some
merit for providing a framework to deal with that type of situation.
However, just adding a GUC variable isn't going to make anything happen
--- we'd need some concrete plans about what we'd do with it.

regards, tom lane

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

Re: [HACKERS] Latest on CITEXT 2.0

[Tom Lane]

David E. Wheeler [EMAIL PROTECTED] writes:
 Datum citext_ne (PG_FUNCTION_ARGS) {
  // Fast path for different-length inputs. Okay for canonical  
 equivalence?
  if (VARSIZE(PG_GETARG_TEXT_P(0)) != VARSIZE(PG_GETARG_TEXT_P(1)))
  PG_RETURN_BOOL( 1 );
  PG_RETURN_BOOL( citextcmp( PG_ARGS ) != 0 );
 }

BTW, I don't think you can use that same-length optimization for
citext.  There's no reason to think that upper/lowercase pairs will
have the same length all the time in multibyte encodings.

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] Latest on CITEXT 2.0

[David E. Wheeler]

On Jun 26, 2008, at 10:02, Tom Lane wrote:


BTW, I don't think you can use that same-length optimization for
citext.  There's no reason to think that upper/lowercase pairs will
have the same length all the time in multibyte encodings.


I was wondering about that. I had been thinking of canonically- 
equivalent stings and combining marks. Doing a quick test it looks  
like combining marks are not equivalent. For example, this returns  
false:


  SELECT 'Ä'::text = 'Ä'::text;

At least with en_US.UTF-8. Hrm. It looks like my client makes them  
both canonical, so I've attached a script demonstrating this issue.


Anyway, I was aware of different byte counts for canonical  
equivalence, but not for differences between upper- and lowercase  
characters. I'd certainly defer to your knowledge of how these things  
truly work in PostgreSQL, Tom, and can of course easily remove that  
optimization. So, are your certain about this?


Many thanks,

David



try.sql
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] Regd: TODO Item

[Tom Lane]

Ramya Chandrasekar [EMAIL PROTECTED] writes:
 Regd: TODO Item: Optimizer:: Have EXPLAIN ANALYZE issue NOTICE messages when
 the estimated and actual row counts differ by a specified percentage.

I think that TODO item hasn't been thought through very carefully.
NOTICEs that are dissociated from the actual explain output seem pretty
useless, or at least painful to use.  You might want to go back in the
archives and read the whole thread that led up to the TODO item to see
what ideas were kicked around.

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] proposal: to_ascii(bytea)

[Tom Lane]

Pavel Stehule [EMAIL PROTECTED] writes:
 Changes related to convert* functions in postgresql 8.3 has impact on
 to_ascii function.

ISTM to_ascii has a completely bogus API anyway.  What is it doing
taking an encoding name as an argument?  It should just assume the
input text is in the database encoding.

The real fix that's needed there is to improve the set of encodings
it can deal with ...

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] plpgsql: Is ELSE IF supported or not?

[Tom Lane]

Mark Mielke [EMAIL PROTECTED] writes:
 I don't agree with this statement. In all procedural languages, or 
 probably most, they usually make ELSE IF special, in that you don't 
 need to close the block twice as per above. The ELSE IF is not actually 
 special in PL/SQL, so it is not a special form. The ELSE can contain a 
 block, which contain any statement, including a nested IF statement. Why 
 not describe ELSE WHILE as well based upon the logic that ELSE IF is 
 valid? :-)

 Now, if it were to say an alternative form of ELSEIF is to nest IF 
 statement like so: ...

Yeah, that might be better.  I think the reason the text looks the way
it does is that we didn't have ELSEIF/ELSIF to start out with, and what
is now section 38.6.2.3 was originally an example of what you had to do
to work around that lack.  I agree that the current presentation is more
confusing than anything else.  ISTM documenting ELSEIF and ELSIF as
separate forms of IF is a bit over-the-top too.

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] Join Removal/ Vertical Partitioning

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 We can check for removal of a rel by

 1. inspecting the target list for the query to see if there are rels
 that do not provide any attributes. (We might also use equivalence
 classes to recode the targetlist to minimise the numbers of tables
 touched, but I think that might be overkill). 

More to the point, it would be wrong.  Equivalence classes do not imply
that two values considered equivalent are equal for all purposes, and
since we don't know what the client is going to do with the returned
data, we can't substitute some other value for the one requested.

 So some thoughts on where to attempt this would be very useful.

The hard part of this is figuring out where to do the work.  As you say,
doing it during prepjointree seems the nicest from an abstract code
structure point of view, but it requires a lot of information that is
not derived until later.

It might be possible to treat ignore the RHS as a join strategy and
try to apply it while forming join relations, which would be late enough
to have all the needed info available.

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] Planner creating ineffective plans on LEFT OUTER joins

[Simon Riggs]

On Thu, 2008-06-26 at 12:57 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Thu, 2008-06-26 at 12:36 -0400, Robert Haas wrote:
  It's my understanding that the philosophy of the PGDG in the past has
  been to avoid putting any kind of hints into the system, focusing
  rather an improving the planning of queries.
 
  It's not a specific hint, its a general goal setting.
 
 Right.  There are definitely places where we've made engineering
 judgements to not attempt a particular type of optimization because it'd
 be too expensive compared to the typical payoff.  Simon's idea has some
 merit for providing a framework to deal with that type of situation.
 However, just adding a GUC variable isn't going to make anything happen
 --- we'd need some concrete plans about what we'd do with it.

Well, I'm convinced the egg came first. 

So I figure to put the framework in place and then start reviewing
things to see if they can be categorised. Plus I want new optimizer
features to be considered in the light of the new framework. This also
allows us a way of handling optimizer performance bugs. We just
reclassify certain cases as being costs-more solutions, rather than
stripping the code out entirely.

-- 
 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] get_relation_stats_hook()

[Simon Riggs]

On Thu, 2008-06-26 at 12:50 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Surely you didn't mean ALL calls.  Please be more specific about what
  you're proposing.
 
  The statistics relation STATRELATT is accessed in a few places in the
  planner. Since it is in the syscache it is accessed directly from there.
  I would like to add hooks so that stats data can come from somewhere
  else other than the syscache for tables, just as we can already do with
  get_relation_stats_hook().
 
 Well, defining the hooks as replacing STATRELATT lookups seems the wrong
 level of abstraction.  What if you want to insert stats that can't be
 represented by the current pg_statistic definition?  Even if there's no
 functional limitation, cons'ing up a dummy pg_statistic tuple seems the
 hard way to do it --- we didn't define get_relation_info_hook as working
 by supplying made-up catalog rows.  Furthermore, many of the interesting
 cases that someone might want to hook into are code paths that don't
 even try to look up a pg_statistic row because they know there won't be
 one, such as two out of the three cases in examine_variable().

The reason for doing it this way is I'm interested in using stats
literally copied from other servers. So the pg_statistic tuples will be
available for us directly. I'm building a tool to allow people to export
their production environment to a test system, so that SQL developers
can experiment with query tuning and optimizer developers can recreate
problems.

 I think you need to move up a level, and perhaps refactor some of the
 existing code to make it easier to inject made-up stats.

Both sound like good ideas. I wasn't really after ultimate flexibility,
but perhaps I should be.

-- 
 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] Latest on CITEXT 2.0

[Josh Berkus]

David,

 Thanks. I've just completely refactored things to look more like the
 approach taken by varlena.c, both in terms of when stuff gets freed
 and in terms of coding style. It's more verbose, but I feel much more
 comfortable with memory management now that I'm following a known
 implementation more closely. :-)

Will this be ready for the July CommitFest?

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Jeff McKenna]

Hi Hiroshi.  What exact file am I to place your test in?  win32.h on  
line#290 ?



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 26-Jun-08, at 12:42 PM, Hiroshi Saito wrote:


Hi.

Um, Please try this.

#includewindows.h
extern void __cdecl _dosmaperr( unsigned long oserrno );
int main(int argc, char *argv[])
{
  _dosmaperr(GetLastError());
  return(0);
}

Can errorless compile be performed?
Regards,
Hiroshi Saito

- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 


To: pgsql-hackers pgsql-hackers@postgresql.org
Sent: Thursday, June 26, 2008 10:04 PM
Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3


Thanks Hiroshi.  Unfortunately libpq.dll does not compile with  
MSVC  2003.  Has anyone successfully compiled libpq for 8.3.3 (or  
CVS head)  with MSVC 2003??

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/
On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:

Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. ...
Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
 cd ..\..
 echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src

It may be necessary to investigate the reference relation of VC2003.
Does someone notice some?
Regards,
Hiroshi Saito
- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 
 




Hello,
I am trying to compile libpq.dll with MSVC 2003 on windows,  
using   postgresql-8.3.3, but I get the following compile error:
 Creating library .\Release\libpqdll.lib and object .\Release   
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external  
symbol   __dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external  
symbol   __dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual
Studio .NET 2003

VC7\BIN\nmake.exe' : return code '0x2'
Stop.
Does anyone have any ideas how to solve this??
thanks.
-jeff
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.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

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



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

Re: [HACKERS] Latest on CITEXT 2.0

[David E. Wheeler]

On Jun 26, 2008, at 12:03, Josh Berkus wrote:


Will this be ready for the July CommitFest?


When is it due, July 1? If so, yes, it should be. I could use a close  
review by someone who knows this shit a whole lot better than I do.


Thanks,

David


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

Re: [HACKERS] Latest on CITEXT 2.0

[Josh Berkus]

David,

 When is it due, July 1? If so, yes, it should be. I could use a close
 review by someone who knows this shit a whole lot better than I do.

Well, that's what the commitfest is for.  Go ahead and add yourself once you 
post the new patch.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Jeff McKenna]

For the record, I just compiled libpq.dll successfully with pg8.2.3  
and MSVC 2003...so something is causing troubles in pg8.3.3 with this  
compiler version.   Hiroshi let me know where to place that test code,  
thanks.



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 26-Jun-08, at 3:12 PM, Jeff McKenna wrote:

Hi Hiroshi.  What exact file am I to place your test in?  win32.h on  
line#290 ?



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 26-Jun-08, at 12:42 PM, Hiroshi Saito wrote:


Hi.

Um, Please try this.

#includewindows.h
extern void __cdecl _dosmaperr( unsigned long oserrno );
int main(int argc, char *argv[])
{
 _dosmaperr(GetLastError());
 return(0);
}

Can errorless compile be performed?
Regards,
Hiroshi Saito

- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 


To: pgsql-hackers pgsql-hackers@postgresql.org
Sent: Thursday, June 26, 2008 10:04 PM
Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3


Thanks Hiroshi.  Unfortunately libpq.dll does not compile with  
MSVC  2003.  Has anyone successfully compiled libpq for 8.3.3 (or  
CVS head)  with MSVC 2003??

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/
On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:

Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. ...
Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
cd ..\..
echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src

It may be necessary to investigate the reference relation of  
VC2003.

Does someone notice some?
Regards,
Hiroshi Saito
- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 
 




Hello,
I am trying to compile libpq.dll with MSVC 2003 on windows,  
using   postgresql-8.3.3, but I get the following compile error:
Creating library .\Release\libpqdll.lib and object .\Release   
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external  
symbol   __dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external  
symbol   __dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual
Studio .NET 2003

VC7\BIN\nmake.exe' : return code '0x2'
Stop.
Does anyone have any ideas how to solve this??
thanks.
-jeff
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.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

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



--
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] Latest on CITEXT 2.0

[Tom Lane]

David E. Wheeler [EMAIL PROTECTED] writes:
 So, are your certain about this?

See Turkish --- in that locale i and I are not an upper/lower pair,
instead they pair with some non-ASCII letters.  There are likely
other cases but that's the counterexample I remember.

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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses

[Josh Berkus]

On Wednesday 25 June 2008 17:52, Dickson S. Guedes wrote:
 Thanks for all yours suggestions, use cases and opinion about this
 thread, I saw that there are more things to consider than I was
 thinking and this make me consider that it is a hard work to do for
 now.

Huh?  You should get started on it ... it will just take longer than you 
thought.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses

[Dickson S. Guedes]

On Thu, Jun 26, 2008 at 6:04 PM, Josh Berkus [EMAIL PROTECTED] wrote:
 On Wednesday 25 June 2008 17:52, Dickson S. Guedes wrote:
 Thanks for all yours suggestions, use cases and opinion about this
 thread, I saw that there are more things to consider than I was
 thinking and this make me consider that it is a hard work to do for
 now.

 Huh?  You should get started on it ... it will just take longer than you
 thought.

Hi Josh,

Yes it will take longer, in realy now I'm studyng how other softwares
(like SSH, Apache ...) do that to plan a way to implement it in the
backend, less impactant as possible. I'll send to list the
implementation plan for suggestions.

Thanks.
-- 
[]s
Dickson S. Guedes
-
Projeto Colmeia - Curitiba - PR
+55 (41) 3254-7130 ramal: 27
http://makeall.wordpress.com/
http://pgcon.postgresql.org.br/
http://planeta.postgresql.org.br/

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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Ron Mayer]

Simon Riggs wrote:

IMHO we should have a single parameter which indicates how much planning
time we consider acceptable for this query. e.g.
 optimization_level = 2 (default), varies 1-3



Couldn't the planner itself make a good guess if it should
keep trying based on the estimated cost?

if (the_best_plan_I_found_so_far_looks_like_itll_take_an_hour)
  keep_optimizing_for_a_few_minutes
if (the_best_plan_I_found_so_far_looks_like_itll_take_0.01ms)
  stop_planning_and_run_with_it

Or maybe as simple as something like

if (time_spent_planning = cost_of_the_best_plan_found / 10)
  stop_optimizing.

If we wanted a GUC, perhaps make it that 10 in the expression above?


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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Tom Lane]

Ron Mayer [EMAIL PROTECTED] writes:
 Couldn't the planner itself make a good guess if it should
 keep trying based on the estimated cost?

 if (the_best_plan_I_found_so_far_looks_like_itll_take_an_hour)
keep_optimizing_for_a_few_minutes
 if (the_best_plan_I_found_so_far_looks_like_itll_take_0.01ms)
stop_planning_and_run_with_it

You're operating on a mistaken assumption, which is that we generate a
complete plan and then generate another.  The places where we'd actually
be doing something with an effort variable are usually dealing with
small parts of plans, or even with preparatory calculations before we've
got anything plan-like at all.  They haven't got a sufficiently holistic
view of what's happening to apply a rule like the above.

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] Planner creating ineffective plans on LEFT OUTER joins

[Ron Mayer]

Tom Lane wrote:

Ron Mayer [EMAIL PROTECTED] writes:

Couldn't the planner itself make a good guess if it should
keep trying based on the estimated cost?



if (the_best_plan_I_found_so_far_looks_like_itll_take_an_hour)
   keep_optimizing_for_a_few_minutes
if (the_best_plan_I_found_so_far_looks_like_itll_take_0.01ms)
   stop_planning_and_run_with_it


You're operating on a mistaken assumption, which is that we generate a
complete plan and then generate another.  The places where we'd actually
be doing something with an effort variable are usually dealing with
small parts of plans, or even with preparatory calculations before we've
got anything plan-like at all.  They haven't got a sufficiently holistic
view of what's happening to apply a rule like the above.


Then could the logic wait until the final plan is computed;
and if that final plan looks very expensive (compared with
the plan time so far), try again with the effort variable
automatically increased?

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

Re: [HACKERS] Table inheritance surprise

[Bruce Momjian]

David Fetter wrote:
 Folks,
 
 When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
 include foreign key constraints (8.3.1).  I believe this is surprising
 behavior, but maybe not a bug, so I'd like to propose another bit of
 syntactic sugar, namely
 
 LIKE [INCLUDING FOREIGN KEYS]
 
 which would do what it looks like it does.
 
 What say?

TODO has:

o Allow inherited tables to inherit indexes, UNIQUE constraints,
  and primary/foreign keys

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

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

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

Re: [HACKERS] CVS Head psql bug?

[Tom Lane]

I wrote:
 I studied the ld man page for awhile but couldn't find any fix other
 than the one Tatsuo suggests of trying to run the linked test program.
 That means we have to guess at what to do in a cross-compilation.
 I suppose the safest choice is to not try to use --as-needed when
 cross-compiling, but does anyone want to argue for the other choice?

I've applied a patch along these lines.  I checked it on RHEL-4 and it
seems to work as expected.

regards, tom lane

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

[HACKERS] Removal of the patches email list

[Bruce Momjian]

We have come to agreement that there is no longer a need for a separate
'patches' email list --- the size of patches isn't a significant issue
anymore, and tracking threads between the patches and hackers lists is
confusing.

I propose we close the patches list and tell everyone to start using
only the hackers list.  This will require email server changes and web
site updates, and some people who are only subscribed to patches have to
figure out if they want to subscribe to hackers.

I have CC'ed hackers, patches, and www because this does affect all
those lists.

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

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

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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Andres Freund]

Hi,

On Thursday 26 June 2008 04:36:09 Tom Lane wrote:
 Andres Freund [EMAIL PROTECTED] writes:
  SELECT *
  FROM
  ab LEFT OUTER JOIN (
  bc JOIN cd
  ON bc.c = cd.d
  )
  ON ab.b = bc.b
 
  WHERE
  ab.a = 2
 
  As ab.a = 2 occurs only once in ab one would expect that it just does
  an index scan on bc for ab.b = bc.b.
There was a typo in here (ON bc.c = cd.d should be ON bc.c = cd.c):
http://anarazel.de/postgres/testtable_query4.plan
Better query plan, but it still not optimal - interestingly the query plan 
works out perfecty for ab.a = 10:
http://anarazel.de/postgres/testtable_query3.plan


 The only way it could do that would be by interchanging the order of the
 left and inner joins, ie (ab left join bc) join cd; which would change
 the results.
My knowledge about the implementation side of relational databases is quite 
limited, so my ideas may be quite flawed:
The planner already recognizes that the left side of the join is quite small 
and the right side will be very big.
Why cant it optimize the query the same way it does for a inner join, namely 
doing an index lookup on bc?
I dont see the fundamental problem? 

 I believe it could interchange the joins if they were both LEFT or
 both INNER.  Do you really need exactly these semantics?
I don't see an easy/effective way to express it:
I need all data belonging left side of the join (proband) through a series 
(participation - answer_group - answer - data) of 
inner joins and NULL if there is no data.
If there would be only one such join it wouldn't be a problem - but a normal 
query has around 20 such LEFT JOINS.
Currently I solve this through separately inserting the data for each join 
into a temporary table which is still way much faster. But not having the 
statistics the planner has selecting a good order isn't that easy. Besides its 
not very elegant.
So, if somebody has a better idea...

If I can use my time to improve pg instead of working around the problem on 
clientside both me and my employer will be happy...



Thanks,

Andres


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

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

[Tom Lane]

Andres Freund [EMAIL PROTECTED] writes:
 The only way it could do that would be by interchanging the order of the
 left and inner joins, ie (ab left join bc) join cd; which would change
 the results.

 My knowledge about the implementation side of relational databases is quite 
 limited, so my ideas may be quite flawed:
 The planner already recognizes that the left side of the join is quite small 
 and the right side will be very big.
 Why cant it optimize the query the same way it does for a inner join, namely 
 doing an index lookup on bc?
 I dont see the fundamental problem? 

The only correct join order for this query is to join bc to cd, then
left-join ab to that result.

Now, if we make ab the outer side of a nestloop over the lower join's
result, it would indeed be theoretically possible to pass down the
value of ab.b through the lower join to the scan on bc and use it to
constrain the scan.  The problem is that finding plans that work like
this would increase the planner's runtime exponentially, compared to
the current situation where we only check for indexscan constraints
coming from the immediate join partner.

(There might be some executor issues too, but I think those would be
relatively easily solved, compared to the plan search time problem.)

regards, tom lane

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

Re: [HACKERS]

[Greg Smith]

On Wed, 25 Jun 2008, yuan fang wrote:

i am studying the source code of postgresql and want to become a 
developer of it.What should i do?


1) If you send e-mail to pgsql-hackers, include a useful subject

2) Read the intros at http://www.postgresql.org/developer/

3) For browsing the code itself, I like
http://doxygen.postgresql.org/

4) Notes on how to deal with version control issues, patch submission, and 
to find out what development is going on currently are all at 
http://wiki.postgresql.org/wiki/Development_information


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

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

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Hiroshi Saito]

Hi Jeff-san.

Would you replace this with src/interfaces/libpq/win32.mak and try it? 
I checked that there was no problem in construction of VC2005 by this 
change. Then, I think if you solve a problem and it should apply. 


Regards,
Hiroshi Saito

- Original Message - 
From: Jeff McKenna [EMAIL PROTECTED]



For the record, I just compiled libpq.dll successfully with pg8.2.3  
and MSVC 2003...so something is causing troubles in pg8.3.3 with this  
compiler version.   Hiroshi let me know where to place that test code,  
thanks.



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 26-Jun-08, at 3:12 PM, Jeff McKenna wrote:

Hi Hiroshi.  What exact file am I to place your test in?  win32.h on  
line#290 ?



---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/





On 26-Jun-08, at 12:42 PM, Hiroshi Saito wrote:


Hi.

Um, Please try this.

#includewindows.h
extern void __cdecl _dosmaperr( unsigned long oserrno );
int main(int argc, char *argv[])
{
 _dosmaperr(GetLastError());
 return(0);
}

Can errorless compile be performed?
Regards,
Hiroshi Saito

- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 


To: pgsql-hackers pgsql-hackers@postgresql.org
Sent: Thursday, June 26, 2008 10:04 PM
Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3


Thanks Hiroshi.  Unfortunately libpq.dll does not compile with  
MSVC  2003.  Has anyone successfully compiled libpq for 8.3.3 (or  
CVS head)  with MSVC 2003??

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/
On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:

Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. ...
Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
cd ..\..
echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src

It may be necessary to investigate the reference relation of  
VC2003.

Does someone notice some?
Regards,
Hiroshi Saito
- Original Message - From: Jeff McKenna [EMAIL PROTECTED] 
 




Hello,
I am trying to compile libpq.dll with MSVC 2003 on windows,  
using   postgresql-8.3.3, but I get the following compile error:
Creating library .\Release\libpqdll.lib and object .\Release   
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external  
symbol   __dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external  
symbol   __dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual
Studio .NET 2003

VC7\BIN\nmake.exe' : return code '0x2'
Stop.
Does anyone have any ideas how to solve this??
thanks.
-jeff
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.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

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



--
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
# Makefile for Microsoft Visual C++ 7.1-8.0

# Will build a static library libpq(d).lib
#and a dynamic library libpq(d).dll with import library libpq(d)dll.lib
# USE_SSL=1 will compile with OpenSSL
# USE_KFW=1 will compile with kfw(kerberos for Windows)
# DEBUG=1 compiles with debugging symbols
# ENABLE_THREAD_SAFETY=1 compiles with threading enabled

ENABLE_THREAD_SAFETY=1

# CPU=i386 or CPU environment of nmake.exe (AMD64 or IA64)

!IF ($(CPU) == )||($(CPU) == i386)
CPU=i386
!MESSAGE Building the Win32 static library...
!MESSAGE
!ELSEIF ($(CPU) == IA64)||($(CPU) == AMD64)
ADD_DEFINES=/D WIN64 /Wp64 /GS
ADD_SECLIB=bufferoverflowU.lib
!MESSAGE Building the Win64 static library...
!MESSAGE
!ELSE
!MESSAGE Please check a CPU=$(CPU) ?
!MESSAGE CPU=i386 or AMD64 or IA64
!ERROR Make aborted.
!ENDIF

!IFDEF DEBUG
OPT=/Od /Zi /MDd
LOPT=/DEBUG
DEBUGDEF=/D _DEBUG
OUTFILENAME=libpqd
!ELSE
OPT=/O2 /MD
LOPT=
DEBUGDEF=/D NDEBUG
OUTFILENAME=libpq
!ENDIF

!IF 

Re: [HACKERS] Removal of the patches email list

[Abhijit Menon-Sen]

At 2008-06-26 18:51:46 -0400, [EMAIL PROTECTED] wrote:

 I propose we close the patches list and tell everyone to start using
 only the hackers list.

That's an excellent idea.

-- ams

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

[HACKERS] Explain XML patch submitted

[raneyt]

I just posted a patch addressing the TODO item:

Allow EXPLAIN output to be more easily processed by scripts, perhaps XML

This is a modified patch originally submitted by Germán Poó Caamaño  
last year.  I added the DTD and some other tweaks.


I did *not* delve much into the ecpg code, other than mildly modifying  
prepoc.y by adding the XML and DTD defines.  I'm sure more work is  
required there.


And, I did not include Init Plan and Sub Plan in the XML output,  
which did not fit into the XML in a graceful way.  But, that can also  
be revisited.


Regards,

Tom Raney

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