[HACKERS] Surfacing qualifiers

[David Fetter]

Folks,

Neil Conway sent me a patch that sketched out a plan to make quals
visible to functions, and Korry Douglas filled in much of the rest of
what you see attached here.  Mistakes are all mine. :)

Random observations:

* It appears I've botched the call to deparse_context_for_plan in
  src/backend/executor/execQual.c and/or made some more fundamental
  error because when testing with PL/Perl, I only see quals inside the
  function when there is just one.  Where are the APIs for things like
  deparse_context_for_plan() documented?

* This stuff should be available to all the PLs.  How might it work in
  PL/PgSQL, etc.?

* The patch just appends a WHERE clause on when it finds quals in
  contrib/dblink/dblink.c.  While this is probably a good place to
  start, it might be good to explore some kind of approach that allows
  more subtlety.

* In PL/Perl, $_TD->{_quals} gets the qualifiers, but they really
  should go in their own variable.  I'm thinking that one should be
  called $_QUAL.

* More generally, it would be nice to have the quals in some kind of
  data structure so the calling code could do smarter things with them
  than the current string-mashing the example code does.

Help, comments, brickbats, etc. appreciated :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: contrib/dblink/dblink.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.70
diff -c -c -r1.70 dblink.c
*** contrib/dblink/dblink.c 25 Mar 2008 22:42:41 -  1.70
--- contrib/dblink/dblink.c 25 Mar 2008 23:24:42 -
***
*** 752,757 
--- 752,758 
char   *conname = NULL;
remoteConn *rconn = NULL;
boolfail = true;/* default to backward 
compatible */
+   ReturnSetInfo   *rsi;
  
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
***
*** 826,831 
--- 827,843 
elog(ERROR, "wrong number of arguments");
}
  
+   if (sql && rsi->qual)
+   {
+   char *quals = rsinfo_get_qual_str(rsi);
+   char *qualifiedQuery = palloc(strlen(sql) + strlen(" 
WHERE ") +
+   
  strlen(quals) + 1);
+ 
+   sprintf(qualifiedQuery, "%s WHERE %s", sql, quals);
+ 
+   sql = qualifiedQuery;
+   }
+ 
if (!conn)
DBLINK_CONN_NOT_AVAIL;
  
Index: src/backend/executor/execQual.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/executor/execQual.c,v
retrieving revision 1.228
diff -c -c -r1.228 execQual.c
*** src/backend/executor/execQual.c 25 Mar 2008 22:42:43 -  1.228
--- src/backend/executor/execQual.c 25 Mar 2008 23:24:43 -
***
*** 45,50 
--- 45,51 
  #include "funcapi.h"
  #include "miscadmin.h"
  #include "nodes/makefuncs.h"
+ #include "optimizer/clauses.h"
  #include "optimizer/planmain.h"
  #include "parser/parse_expr.h"
  #include "utils/acl.h"
***
*** 1415,1420 
--- 1416,1440 
return result;
  }
  
+ /*
+  *
+  * Get either an empty string or a batch of qualifiers.
+  *
+  */
+ char *
+ rsinfo_get_qual_str(ReturnSetInfo *rsinfo)
+ {
+   Node*qual;
+   List*context;
+ 
+   if (rsinfo->qual == NIL)
+   return pstrdup("");
+ 
+   qual = (Node *) make_ands_explicit(rsinfo->qual);
+   context = deparse_context_for_plan(NULL, NULL, rsinfo->rtable);
+ 
+   return deparse_expression(qual, context, false, false);
+ }
  
  /*
   *ExecMakeTableFunctionResult
***
*** 1426,1431 
--- 1446,1452 
  Tuplestorestate *
  ExecMakeTableFunctionResult(ExprState *funcexpr,
ExprContext *econtext,
+   List *qual, List 
*rtable,
TupleDesc expectedDesc,
TupleDesc *returnDesc)
  {
***
*** 1458,1463 
--- 1479,1486 
InitFunctionCallInfoData(fcinfo, NULL, 0, NULL, (Node *) &rsinfo);
rsinfo.type = T_ReturnSetInfo;
rsinfo.econtext = econtext;
+   rsinfo.qual = qual;
+   rsinfo.rtable = rtable;
rsinfo.expectedDesc = expectedDesc;
rsinfo.allowedModes = (int) (SFRM_ValuePerCall | SFRM_Materialize);
rsinfo.re

Re: [HACKERS] advancing snapshot's xmin

[Dimitri Fontaine]

Le mercredi 26 mars 2008, Tom Lane a écrit :
> whenever the number of active snapshots goes to zero

Does this ever happen?
I mean, if the way to avoid locking contention is to rely on a production 
system which let the service "breathe" from time to time, maybe there's 
something wrong in the reasoning.

Of course I'm much more ready to accept I don't understand the first bit of it 
all than to consider you're off-tracks here, but...
-- 
dim

If you ask a stupid question, you may feel stupid. If you don’t ask a stupid 
question, you remain stupid.
-- Tony Rothman, Ph.D.U. Chicago, Physics


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

Re: [HACKERS] advancing snapshot's xmin

[Gregory Stark]

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

> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
>> Neil Conway wrote:
>>> If we're just updating MyProc->xmin, we only need to acquire
>>> ProcArrayLock in shared mode, right?
>
>> In fact, do you need a lock at all?
>
> I think you probably do.  GetSnapshotData needs to be confident that the
> global xmin it computes is <= the xmin that any other backend might be
> about to store into its MyProc->xmin; how can you ensure that if there's
> no locking happening?

Surely xmin would only ever advance? How can removing snapshots cause xmin to
retreat at all, let alone behind the gloal xmin GetSnapshotData calculated?

> Now the way I'd been envisioning this would work is that whenever the
> number of active snapshots goes to zero, we clear MyProc->xmin, and
> that probably could be done without a lock.  Then the next time we do 
> GetSnapshotData, it would compute and store a new MyProc->xmin
> (this would be the same activity that we currently think of as "setting
> the serializable snapshot").  So you don't need any more locking than
> already exists.

It's the same locking in theory from the point of view of where in the code
the locking happens. But I don't think it's the same locking in practice from
the point of view of how much wall-clock time passes between locks.

Consider a data loading job which has millions of INSERT statements in a file.
Currently if you put them all in a transaction it takes a single snapshot and
runs them all with the same snapshot.

If you reset xmin whenever you have no live snapshots then that job would be
doing that between every INSERT statement.

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

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

Re: [HACKERS] Problem with site doc search

[Gurjeet Singh]

On Tue, Mar 25, 2008 at 3:01 AM, Magnus Hagander <[EMAIL PROTECTED]>
wrote:

> Gurjeet Singh wrote:
> > On Thu, Mar 6, 2008 at 5:01 PM, Gevik Babakhani <[EMAIL PROTECTED]
> > > wrote:
> >
> >  > No, it's still on the TODO. Gevik has also been looking a bit
> >  > at it (I think - at least he's indicated that he is), and he
> >  > recently got some new parser code to look at to see if we can
> >  > use to fix it.
> >  >
> >
> > I have the new parser code. Next week I have some time reserved to
> > look at
> > it.
> >
> >
> > For the last few days I have been noticing that the _ (underscore) is no
> > longer being treated as a word boundary. Can you confirm this has been
> > fixed?
>
> AFAIK, nothing has been done yet :-)
>

You are right I was looking for pg_standby and another pg_
and since I found those results on top, I thought there was some action
taken.

The problem persists

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Script binaries renaming

[k Kotala <[EMAIL PROTECTED]>]

Bruce Momjian napsal(a):

Where are we on this?  Tom thinks we don't want this.  TODO has:


I plan to send survey on general list about it today.

Zdenek

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

Re: [HACKERS] Script binaries renaming

[Zdeněk Kotala]

Marc G. Fournier napsal(a):

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1



- --On Tuesday, March 25, 2008 22:51:53 -0400 Bruce Momjian <[EMAIL PROTECTED]> 
wrote:



Uh, I think it is hard to make a case that 'createuser' is an
appropriate name for a Postgres utility.  On the other hand, we haven't
had many complaints about it, which is kind of odd.


If nobody has ever complained, what is the reason for the change?  How many ppl 
are going to complain because the commands they are used to "suddenly stop 
existing"?


Minimal me :-) and Solaris Architect committee have complain. Question is also 
how many users really use these commands. For example vacuumdb is not too 
important now when we have autovacuum. You can specify tablespace in createdb 
command but you don't have createtablespace command and so on.


I will send survey to general list today and I hope we get some useful 
information.

Zdenek

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

Re: [HACKERS] Script binaries renaming

[Magnus Hagander]

On Tue, 2008-03-25 at 21:59 -0400, Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Where are we on this?  Tom thinks we don't want this.  TODO has:
> >   * Prefix command-line utilities like createuser with 'pg_'
> > http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php
> 
> It wasn't just me; quite a few people were dubious about it when the
> patch was submitted.  See the thread here:
> http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php
> 
> > One idea is to keep the existing commands and just add pg_* (or pg*) to
> > additional versions, with the idea that the original versions will be
> > removed some day.
> 
> AFAICS the only argument for doing this is to eliminate confusion and
> potential conflicts, which means that we get no benefit at all until we
> actually do remove the old names.  So if we're going to do this, we have
> to make a commitment that we're going to remove the old names within the
> reasonably foreseeable future (say, about two releases out).
> 
> Are we really prepared to break everyone's scripts for this?

I wonder how many people actually use those commands :-) I know I always
use psql with a commandline parameter, and the majority of other peoples
scripts that I've come across also do that. So I'm not sure exactly how
important it is.

Another option then might be to simply deprecate their use, and
eventually get rid of them, instead of renaming them?

//Magnus

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

Re: [HACKERS] Script binaries renaming

[Zdeněk Kotala]

Magnus Hagander napsal(a):

On Tue, 2008-03-25 at 21:59 -0400, Tom Lane wrote:





Are we really prepared to break everyone's scripts for this?


I wonder how many people actually use those commands :-) I know I always
use psql with a commandline parameter, and the majority of other peoples
scripts that I've come across also do that. So I'm not sure exactly how
important it is.

Another option then might be to simply deprecate their use, and
eventually get rid of them, instead of renaming them?


In one of my mail I also mentioned to replace all of these commands by one (e.g. 
pg_cmd) which will integrate all of them.  Removing is not good solution for 
people who writes scripts, because process psql output is complicated and there 
is not easy way how to run vacuum on all databases for example.



Zdenek

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

Re: [HACKERS] Script binaries renaming

[Magnus Hagander]

On Wed, 2008-03-26 at 13:21 +0100, Zdeněk Kotala wrote:
> Magnus Hagander napsal(a):
> > On Tue, 2008-03-25 at 21:59 -0400, Tom Lane wrote:
> 
> 
> 
> >> Are we really prepared to break everyone's scripts for this?
> > 
> > I wonder how many people actually use those commands :-) I know I always
> > use psql with a commandline parameter, and the majority of other peoples
> > scripts that I've come across also do that. So I'm not sure exactly how
> > important it is.
> > 
> > Another option then might be to simply deprecate their use, and
> > eventually get rid of them, instead of renaming them?
> 
> In one of my mail I also mentioned to replace all of these commands by one 
> (e.g. 
> pg_cmd) which will integrate all of them.  Removing is not good solution for 
> people who writes scripts, because process psql output is complicated and 
> there 
> is not easy way how to run vacuum on all databases for example.

You can add lots of nice parameters to psql to make it quite easy to
process the output. Running vacuum on all databases isn't particularly
hard - but it does require a small bit of shell-fu.

But I'll grant you that one for vacuumdb. I was specifically thinking
about the create/drop user/db/lang scripts, which are the ones likely to
"conflict" with other parts of the system. Didn't think of vacuumdb.

//Magnus


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

Re: [HACKERS] Surfacing qualifiers

[Andrew Dunstan]

David Fetter wrote:

Folks,

Neil Conway sent me a patch that sketched out a plan to make quals
visible to functions
  


er, what? Please explain what this means, why it might be useful. 
Example(s) would help.



* In PL/Perl, $_TD->{_quals} gets the qualifiers, but they really
  should go in their own variable.  I'm thinking that one should be
  called $_QUAL.

  


$_TD is only there for triggers. Is this a trigger feature?

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] Script binaries renaming

[Zdeněk Kotala]

Magnus Hagander napsal(a):

On Wed, 2008-03-26 at 13:21 +0100, Zdeněk Kotala wrote:

Magnus Hagander napsal(a):

On Tue, 2008-03-25 at 21:59 -0400, Tom Lane wrote:




Are we really prepared to break everyone's scripts for this?

I wonder how many people actually use those commands :-) I know I always
use psql with a commandline parameter, and the majority of other peoples
scripts that I've come across also do that. So I'm not sure exactly how
important it is.

Another option then might be to simply deprecate their use, and
eventually get rid of them, instead of renaming them?
In one of my mail I also mentioned to replace all of these commands by one (e.g. 
pg_cmd) which will integrate all of them.  Removing is not good solution for 
people who writes scripts, because process psql output is complicated and there 
is not easy way how to run vacuum on all databases for example.


You can add lots of nice parameters to psql to make it quite easy to
process the output. Running vacuum on all databases isn't particularly
hard - but it does require a small bit of shell-fu.


Yes, it needs extra lines in shell script and probably most of use cases are 
possible do by psql command. Maybe removing will be better solution.


 > But I'll grant you that one for vacuumdb. I was specifically thinking

about the create/drop user/db/lang scripts, which are the ones likely to
"conflict" with other parts of the system. Didn't think of vacuumdb.


I see. I think that autovacuum stops usage of vacuumdb command anyway.

Zdenek

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

Re: [HACKERS] Script binaries renaming

[Andrew Dunstan]

Zdeněk Kotala wrote:
Question is also how many users really use these commands. For example 
vacuumdb is not too important now when we have autovacuum.


This is not true. Plenty of apps will quite reasonably choose to follow 
large batch updates by a single vacuumdb rather than using autovacuum.


Incidentally, I am less opposed than some to some sensible renaming 
here, eventually. Perhaps we could take the opportunity to fix the 
naming of initdb, which confuses the heck out of many people.


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] Script binaries renaming

[Zdeněk Kotala]

Andrew Dunstan napsal(a):



Zdeněk Kotala wrote:
Question is also how many users really use these commands. For example 
vacuumdb is not too important now when we have autovacuum.


This is not true. Plenty of apps will quite reasonably choose to follow 
large batch updates by a single vacuumdb rather than using autovacuum.


Yes, up to 8.2, but I think situation for 8.3 could be different. We have more 
works, autovacuum is better and so on.


Incidentally, I am less opposed than some to some sensible renaming 
here, eventually. Perhaps we could take the opportunity to fix the 
naming of initdb, which confuses the heck out of many people.


Instead of renaming initdb extend pg_ctl (pg_ctl init) seems to me as a better 
idea.

Zdenek


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

Re: [HACKERS] Script binaries renaming

[Andrew Dunstan]

Zdeněk Kotala wrote:

Andrew Dunstan napsal(a):



Zdeněk Kotala wrote:
Question is also how many users really use these commands. For 
example vacuumdb is not too important now when we have autovacuum.


This is not true. Plenty of apps will quite reasonably choose to 
follow large batch updates by a single vacuumdb rather than using 
autovacuum.


Yes, up to 8.2, but I think situation for 8.3 could be different. We 
have more works, autovacuum is better and so on.





Again, this is just not true. It might not be a situation you run  
across, but autovacuum does not suit all needs. This includes 8.3.


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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Alvaro Herrera]

Tom Lane wrote:

> The real question here is whether Windows' stat() is telling the truth
> about how much filesystem space has actually been allocated to a file.
> It seems entirely possible that it's not; but if it is, then I think we
> have a problem.

Has this been examined by a Windows hacker?

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

[HACKERS] Proposal: improve shutdown during online backup

[Albe Laurenz]

I'm referring to the discussion in this thread:
http://archives.postgresql.org/pgsql-hackers/2007-11/msg00946.php

As expressed in the thread, I think that there should not be
a backup_label file in the data directory after a clean shutdown,
because
a) it's probably an oversight anyway (someone forgot to
   call pg_stop_backup) and
b) it will force an unnecessary recovery at server restart,
   which will sometimes fail (if the WAL file is no longer there).

This is my proposal:

1) On "pg_ctl stop|restart -m smart", check if online backup is
   in progress and do not shutdown in this case (treat the online
   backup like an open connection).
2) On "pg_ctl stop|restart -m fast", remove backup_label after
   the server has been brought down successfully.

If that's acceptable, I'd be willing to work on it.

Yours,
Laurenz Albe

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

[HACKERS] having problem in rsync'ing cvs

[Gurjeet Singh]

Hi all,

I was rsyncing a fresh copy of CVS repository, and suddenly, midway
(around heap.c) rsync complained. When after many retries I couldn't start
it, I restarted the OS.

Since it is a VM running inside Vista (from where I could ping and
browse anoncvs.postgresql.org), I restarted the Host OS too.

Here's the configuration:
Host OS: Windows Vista
VM Software: Innotek VirtualBox
Guest OS: Ubuntu 7.10

And here's the error I am getting:

[EMAIL PROTECTED]:~/dev/repositories$ rsync -avzCH --delete
anoncvs.postgresql.org::pgsql-cvs pg_cvsroot/
rsync: getaddrinfo: anoncvs.postgresql.org 873: Name or service not known
rsync error: error in socket IO (code 10) at clientserver.c(104) [receiver=
2.6.9]
[EMAIL PROTECTED]:~/dev/repositories$

Is the rsync daemon on anoncvs down? Is everyone else able to do rsync?

Thanks in advance,

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT

[Andrew Dunstan]

Alvaro Herrera wrote:

Tom Lane wrote:

  

The real question here is whether Windows' stat() is telling the truth
about how much filesystem space has actually been allocated to a file.
It seems entirely possible that it's not; but if it is, then I think we
have a problem.



Has this been examined by a Windows hacker?

  


If someone can suggest a test program I'll be happy to run it.

cheers

andrew

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

Re: [HACKERS] advancing snapshot's xmin

[Tom Lane]

Dimitri Fontaine <[EMAIL PROTECTED]> writes:
> Le mercredi 26 mars 2008, Tom Lane a écrit :
>> whenever the number of active snapshots goes to zero

> Does this ever happen?

Certainly: between any two commands of a non-serializable transaction.

In a serializable transaction the whole thing is a dead issue
anyway, since the original snapshot has to be kept.

There are corner cases involving open cursors where a snapshot
might persist longer, and then the optimization wouldn't apply.

The formulation that Alvaro gave would sometimes be able to
move xmin forward when the simple no-snaps-left rule wouldn't,
such as create cursor A, create cursor B (with a newer snap),
close cursor A.  However I really doubt that scenarios like
this occur often enough to be worth having a much more expensive
snapshot-management mechanism.

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] having problem in rsync'ing cvs

[Brendan Jurd]

On 27/03/2008, Gurjeet Singh <[EMAIL PROTECTED]> wrote:
> Is the rsync daemon on anoncvs down? Is everyone else able to do rsync?
>

Possibly related; the Postgres git repository at
http://repo.or.cz/w/PostgreSQL.git is showing the last commit at 25
hours ago.  It's usually a bit more spry than that.

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

Re: [HACKERS] having problem in rsync'ing cvs

[Alvaro Herrera]

Gurjeet Singh escribió:

> Is the rsync daemon on anoncvs down? Is everyone else able to do rsync?

I can rsync with no issue.

-- 
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] advancing snapshot's xmin

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> I think you probably do.  GetSnapshotData needs to be confident that the
>> global xmin it computes is <= the xmin that any other backend might be
>> about to store into its MyProc->xmin; how can you ensure that if there's
>> no locking happening?

> Surely xmin would only ever advance?

You couldn't guarantee that without any lock.  The risk case is where
someone else is in progress of setting his own xmin, but is running so
slowly that he's included an XID that isn't there anymore.  So someone
else coming in and doing a computation of global xmin will compute a
higher value than what the slow guy is about to publish.

I agree that it would be safe for a backend to increase its
already-published xmin to some higher value without a lock.  But I don't
see the point.  The place where you'd actually be computing the new
value is in GetSnapshotData, and that can't run without a lock for the
above-mentioned reason.

> It's the same locking in theory from the point of view of where in the code
> the locking happens. But I don't think it's the same locking in practice from
> the point of view of how much wall-clock time passes between locks.

> Consider a data loading job which has millions of INSERT statements in a file.
> Currently if you put them all in a transaction it takes a single snapshot and
> runs them all with the same snapshot.

> If you reset xmin whenever you have no live snapshots then that job would be
> doing that between every INSERT statement.

These statements are 100% nonsense.

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] Surfacing qualifiers

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> David Fetter wrote:
>> Neil Conway sent me a patch that sketched out a plan to make quals
>> visible to functions

> er, what? Please explain what this means, why it might be useful. 

It's utterly useless, because it only exposes a small fraction of the
information that would be needed to do anything.

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] Array behavior oddities

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >   If any dimension is written as a slice, i.e. contains a colon, then all
> >   dimensions are treated as slices.
> 
> > Is the the behavior of assuming an entry with no colon is a slice what
> > we want, or are we just stuck with it?
> 
> Why do you find that surprising?  It's either a slice or it isn't,
> there's no halfway point.  Are you proposing to throw an error if only
> some of the subscripts have colons?  What would be the point?
> 
> > Is there a reason out-of-bounds array accesses behave differently for
> > slices and non-slices?
> 
> History (although "sloppy original implementation" would do too).  I'm
> not sure if we should try to clean it up --- there've not been that many
> complaints, but I'm sure we'd get complaints from people whose code
> stopped working, if we change it.

I have updated the documention (patch attached) to be clearer on the odd
array slice behavior.

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

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/array.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/array.sgml,v
retrieving revision 1.62
diff -c -c -r1.62 array.sgml
*** doc/src/sgml/array.sgml	7 Jun 2007 14:49:56 -	1.62
--- doc/src/sgml/array.sgml	26 Mar 2008 14:42:14 -
***
*** 258,263 
--- 258,266 
   {{meeting,lunch},{training,presentation}}
  (1 row)
  
+ 
+   To avoid confusion with slices, use slice syntax for all dimmension
+   references, e.g.  [1:2][1:1], not [2][1:1].
   
  
   
***
*** 275,281 
any of the subscript expressions are null.  However, in other corner
cases such as selecting an array slice that
is completely outside the current array bounds, a slice expression
!   yields an empty (zero-dimensional) array instead of null.
If the requested slice partially overlaps the array bounds, then it
is silently reduced to just the overlapping region.
   
--- 278,285 
any of the subscript expressions are null.  However, in other corner
cases such as selecting an array slice that
is completely outside the current array bounds, a slice expression
!   yields an empty (zero-dimensional) array instead of null.  (This
!   does not match non-slice behavior and is done for historical reasons.)
If the requested slice partially overlaps the array bounds, then it
is silently reduced to just the overlapping region.
   

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

Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT

[Zubkovsky, Sergey]

Can anybody tell me how filesystem space is allocated and point me to
the sources if it's possible?
I have some experience with programming for Windows and I'll try to
investigate this problem.


-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 26, 2008 4:56 PM
To: Alvaro Herrera
Cc: Tom Lane; Gregory Stark; Zubkovsky, Sergey;
pgsql-hackers@postgresql.org; Magnus Hagander
Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT



Alvaro Herrera wrote:
> Tom Lane wrote:
>
>   
>> The real question here is whether Windows' stat() is telling the
truth
>> about how much filesystem space has actually been allocated to a
file.
>> It seems entirely possible that it's not; but if it is, then I think
we
>> have a problem.
>> 
>
> Has this been examined by a Windows hacker?
>
>   

If someone can suggest a test program I'll be happy to run it.

cheers

andrew

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

[HACKERS] [GSoC] Need for advice on improving hash index performance

[Xiao Meng]

Hello, Hackers:

I've post a question about GSoC before.
[GSoC] (Is it OK to choose items without % mark in theToDoList) && (is
it an acceptable idea to build index on Flash Disk)
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00859.php

Now, I start a new thread since the topic had been transfered to
improving hash index.
Kenneth had started a thread about this topic before.
http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php

I've browsed the long discussion follow Kenneth's advice.
Now, I've have some rough idea. And there's still something confused
me.I'm glad to hear your advice.

1. The Benchmark
In what condition, hash index "should" be used in general , i.e. when
it should work better that b-tree? I think we should focus on these
condition. IMHO, hash should be efficient when the cost of comparison
is expensive and most of the query is equality query.
In addition, maybe we can use TPC benchmark's data.

2.The management of the bucket
some idea in the todo list is about this

> o Pack hash index buckets onto disk pages more efficiently
>
>  Currently only one hash bucket can be stored on a page. Ideally
>  several hash buckets could be stored on a single page and greater
>  granularity used for the hash algorithm.
>
>  http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php
>
AIUI, one approach is to divide  one  big  hash  bucket into
sub-bucket and re-hash in the big  bucket. And we should use closed
hash IMHO.

2.what about other Hash Implementation
What about static hash ? We need to re-organize the whole file when
the file grows big. But what about in such a condition  that we can
estimate the size of table and it's not changed frequently. Besides
the high cost of re-organization ,it's an attractive technique because
we only  need one I/O  to fetch  a  item.
Extendable hash is also effective when the memory is big enough .It's
also need one I/O to fetch a item. Of course the growth of memory
usage is a big problem.

3. the page layout of the bucket
> o In hash indexes, consider storing the hash value with or instead
> of the key itself
>
It means that we need at least two I/O's to fetch a tuple.
For a three level B-Tree(if we place the upper two level into the
memory), it works not worse than hash index. IMHO, Hash is effective
when the index is big enough and the B-Tree grows to 4 level.
Neil Conway had posted a patch doing this with an old version of
PostgreSQL. I think It's a good start point for me.


4.what about user defined hash function?
Sometimes, DBA may know the  data distribution well, so DBA can give a
better hash function in some cases.
As far as I know, Oracle support user defined hash function using a
"Hash is exper" clause
e.g.
create cluster acctclust (acctid int) size 80 single table hashkeys 10
 hash is mod(acctid,13)


Any comment or advices? Hope to hear from you,thanks!
And I think I should finish my proposal as soon as possible now, since
the deadline of application is coming.

Have a good day;-)
-- 
Best Regards,
Xiao Meng

━
Data and Knowledge Engineering Research Center,CS&T
Harbin Institute of Technology, Harbin, Heilongjiang, China
Gtalk: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
Blog: http://xiaomeng.yo2.cn

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

Re: [HACKERS] Script binaries renaming

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> Another option then might be to simply deprecate their use, and
> eventually get rid of them, instead of renaming them?

I'd like to get rid of ipcclean immediately; it hasn't had any usefulness
in years.

The issue is larger than the proposed patch addresses, though.
I see the following stuff installed in .../bin by CVS HEAD:

clusterdbinitdb   pg_resetxlog postmaster
createdb ipcclean pg_restore   psql 
createlang   oid2name pg_standby   reindexdb 
createuser   pg_configpgbench  vacuumdb 
dropdb   pg_controldata   pltcl_delmod vacuumlo 
droplang pg_ctl   pltcl_listmod 
dropuser pg_dump  pltcl_loadmod 
ecpg pg_dumpall   postgres 

There's an awful lot of names here that don't have any obvious
connection to Postgres ...

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] advancing snapshot's xmin

[Gregory Stark]

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

>> Consider a data loading job which has millions of INSERT statements in a 
>> file.
>> Currently if you put them all in a transaction it takes a single snapshot and
>> runs them all with the same snapshot.
>
>> If you reset xmin whenever you have no live snapshots then that job would be
>> doing that between every INSERT statement.
>
> These statements are 100% nonsense.

Uhm, yeah, I somehow didn't write was I was thinking. I didn't mean to say we
would be taking a new snapshot for each INSERT but that we would be resetting
xmin for each INSERT. Whereas currently we only set xmin once when we set the
serializable snapshot.

-- 
  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] Script binaries renaming

[Zdenek Kotala]

Tom Lane napsal(a):

Magnus Hagander <[EMAIL PROTECTED]> writes:

Another option then might be to simply deprecate their use, and
eventually get rid of them, instead of renaming them?


I'd like to get rid of ipcclean immediately; it hasn't had any usefulness
in years.


+1


The issue is larger than the proposed patch addresses, though.
I see the following stuff installed in .../bin by CVS HEAD:

clusterdbinitdb   pg_resetxlog postmaster
createdb ipcclean pg_restore   psql 
createlang   oid2name pg_standby   reindexdb 
createuser   pg_configpgbench  vacuumdb 
dropdb   pg_controldata   pltcl_delmod vacuumlo 
droplang pg_ctl   pltcl_listmod 
dropuser pg_dump  pltcl_loadmod 
ecpg pg_dumpall   postgres 


There's an awful lot of names here that don't have any obvious
connection to Postgres ...


Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same output like 
pg_controldata. I think we can merge these commands.


Zdenek

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

Re: [HACKERS] Script binaries renaming

[Bruce Momjian]

Zdenek Kotala wrote:
> Tom Lane napsal(a):
> > Magnus Hagander <[EMAIL PROTECTED]> writes:
> >> Another option then might be to simply deprecate their use, and
> >> eventually get rid of them, instead of renaming them?
> > 
> > I'd like to get rid of ipcclean immediately; it hasn't had any usefulness
> > in years.
> 
> +1

I have just posted a patch for this.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.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] Script binaries renaming

[Tom Lane]

Zdenek Kotala <[EMAIL PROTECTED]> writes:
>> There's an awful lot of names here that don't have any obvious
>> connection to Postgres ...

> Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same
> output like pg_controldata. I think we can merge these commands.

Now we're into change for the sake of change?  Those programs don't
have any naming 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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> Tom Lane wrote:
>>> The real question here is whether Windows' stat() is telling the truth
>>> about how much filesystem space has actually been allocated to a file.
>>> It seems entirely possible that it's not; but if it is, then I think we
>>> have a problem.

>> Has this been examined by a Windows hacker?

> If someone can suggest a test program I'll be happy to run it.

One thing that would be good is just to see who else can reproduce
the original observation:
http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php

It might occur only on some versions of Windows, for instance.

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] Script binaries renaming

[Zdenek Kotala]

Tom Lane napsal(a):

Zdenek Kotala <[EMAIL PROTECTED]> writes:

There's an awful lot of names here that don't have any obvious
connection to Postgres ...



Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same
output like pg_controldata. I think we can merge these commands.


Now we're into change for the sake of change?  Those programs don't
have any naming problem.


yes, but they are redundant and I think when we start a cleaning and polishing 
it should be done completely. But names are correct :-)


Zdenek

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

Re: [HACKERS] advancing snapshot's xmin

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> Uhm, yeah, I somehow didn't write was I was thinking. I didn't mean to say we
> would be taking a new snapshot for each INSERT but that we would be resetting
> xmin for each INSERT. Whereas currently we only set xmin once when we set the
> serializable snapshot.

Right, but setting xmin within GetSnapshotData is essentially free.
What I'm envisioning is that we lose the notion of "this is a
serializable snapshot" that that function currently has, and just
give it the rule "if MyProc->xmin is currently zero, then set it".
Then the only additional mechanism needed is for the snapshot
manager to detect when all snapshots are gone and zero out
MyProc->xmin --- that would happen sometime during command shutdown,
and per current discussion it shouldn't need a lock.

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] having problem in rsync'ing cvs

[Gurjeet Singh]

On Wed, Mar 26, 2008 at 7:56 PM, Alvaro Herrera <[EMAIL PROTECTED]>
wrote:

> Gurjeet Singh escribió:
>
> > Is the rsync daemon on anoncvs down? Is everyone else able to do rsync?
>
> I can rsync with no issue.
>

I attempted again, and it seems to have started responding  Seems like
an intermittent issue.

Thanks much,

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] advancing snapshot's xmin

[Dimitri Fontaine]

Le mercredi 26 mars 2008, Tom Lane a écrit :
> Dimitri Fontaine <[EMAIL PROTECTED]> writes:
> > Le mercredi 26 mars 2008, Tom Lane a écrit :
> >> whenever the number of active snapshots goes to zero
> > Does this ever happen?
> Certainly: between any two commands of a non-serializable transaction.

Oh, it's a transaction scope snapshot when I though about cluster global 
snapshots. Thanks a lot for explaining, and sorry for disturbing! :)

-- 
dim


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

Re: [HACKERS] Script binaries renaming

[Andrew Dunstan]

Zdenek Kotala wrote:

Tom Lane napsal(a):

Zdenek Kotala <[EMAIL PROTECTED]> writes:

Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same
output like pg_controldata. I think we can merge these commands.


Now we're into change for the sake of change?  Those programs don't
have any naming problem.


yes, but they are redundant

  


Really? How so? They have overlapping functionality, but neither has a 
subset of the other's functionality.


Possibly we should merge them, but that's a different issue, and in 
particular has nothing to do with renaming, so it doesn't belong in this 
thread.



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] Script binaries renaming

[Zdenek Kotala]

Andrew Dunstan napsal(a):



Zdenek Kotala wrote:

Tom Lane napsal(a):

Zdenek Kotala <[EMAIL PROTECTED]> writes:

Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same
output like pg_controldata. I think we can merge these commands.


Now we're into change for the sake of change?  Those programs don't
have any naming problem.


yes, but they are redundant

  


Really? How so? They have overlapping functionality, but neither has a 
subset of the other's functionality.


Sorry, overlapping is better word.

Possibly we should merge them, but that's a different issue, and in 
particular has nothing to do with renaming, so it doesn't belong in this 
thread.


Ok. Agree.

Zdenek

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

Re: [HACKERS] advancing snapshot's xmin

[Gregory Stark]

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

> Gregory Stark <[EMAIL PROTECTED]> writes:
>> Uhm, yeah, I somehow didn't write was I was thinking. I didn't mean to say we
>> would be taking a new snapshot for each INSERT but that we would be resetting
>> xmin for each INSERT. Whereas currently we only set xmin once when we set the
>> serializable snapshot.
>
> Right, but setting xmin within GetSnapshotData is essentially free.
> What I'm envisioning is that we lose the notion of "this is a
> serializable snapshot" that that function currently has, and just
> give it the rule "if MyProc->xmin is currently zero, then set it".
> Then the only additional mechanism needed is for the snapshot
> manager to detect when all snapshots are gone and zero out
> MyProc->xmin --- that would happen sometime during command shutdown,
> and per current discussion it shouldn't need a lock.

It would be nice if there was some way to notice that no other transactions
have committed since last we calculated a snapshot and just reuse that
snapshot.

I would say ideally before we throw out our xmin but I suspect the point of
synchronization needed to notice this condition would be tantamount to that
same lock anyways.

-- 
  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] [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Tom Lane]

[EMAIL PROTECTED] (Bruce Momjian) writes:
> Log Message:
> ---
> Strengthen warnings about using pg_dump's -i option.

The proposed TODO item was not about doing this, it was about removing
the option altogether.  AFAICS it's a foot-gun and nothing else --- why
do we have it?

BTW, a point I had forgotten is that pg_restore doesn't enforce that it
not be used with a newer server:

/* XXX Should get this from the archive */
AHX->minRemoteVersion = 070100;
AHX->maxRemoteVersion = 99;

I think this is probably sane, since after all we couldn't enforce that
the plain script output not be loaded into a newer server.  But it means
that -i is effectively a no-op for pg_restore, which again begs the
question of why we have it.

regards, tom lane

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

[HACKERS] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Bruce Momjian]

Tom Lane wrote:
> [EMAIL PROTECTED] (Bruce Momjian) writes:
> > Log Message:
> > ---
> > Strengthen warnings about using pg_dump's -i option.
> 
> The proposed TODO item was not about doing this, it was about removing
> the option altogether.  AFAICS it's a foot-gun and nothing else --- why
> do we have it?

I thought the simple fix was to just have a better warning and see how
that works in practice.  There was some concern from people about
removing it without more feedback/warning.  I am happy to remove it.

> BTW, a point I had forgotten is that pg_restore doesn't enforce that it
> not be used with a newer server:
> 
>   /* XXX Should get this from the archive */
>   AHX->minRemoteVersion = 070100;
>   AHX->maxRemoteVersion = 99;
> 
> I think this is probably sane, since after all we couldn't enforce that
> the plain script output not be loaded into a newer server.  But it means
> that -i is effectively a no-op for pg_restore, which again begs the
> question of why we have it.

So pg_restore -i does nothing?  Seems it should be removed.

The plain text file will be a foot-gun too, of course.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.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] Surfacing qualifiers

[David Fetter]

On Wed, Mar 26, 2008 at 08:31:04AM -0400, Andrew Dunstan wrote:
> David Fetter wrote:
>> Folks,
>>
>> Neil Conway sent me a patch that sketched out a plan to make quals
>> visible to functions
>>   
> er, what? Please explain what this means, why it might be useful.
> Example(s) would help.

Right now, user-callable code has no way to access the predicates of
the query which calls it.  Neil's patch was intended to expose some of
them.  The biggest use cases I see are for inter-database
communication such as dblink and friends.  The dblink part of the
patch shows what's supposed to be happening.

What happens now with dblink is that the remote table (more generally,
the output of a fixed query) gets materialized into memory in its
entirety, and if it's bigger than what's available, it will crash the
backend or worse.  That happens because functions do not have any
access to the predicates with which they were called, so the current
workaround is to pass the predicates manually and then cast.

Speaking of casting, how hard would it be to make something like this
work?

SELECT foo, bar, baz
FROM function_returning_setof_record('parameters','here') AS f(compound_type);

>> * In PL/Perl, $_TD->{_quals} gets the qualifiers, but they really
>>   should go in their own variable.  I'm thinking that one should be
>>   called $_QUAL.
>
> $_TD is only there for triggers. Is this a trigger feature?

Clearly it is not, but the procedure for adding a new variable which
needs to be available to functions is far from clear, so Korry decided
to wedge it into that existing variable to get a proof of concept out
the door.

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

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

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> [EMAIL PROTECTED] (Bruce Momjian) writes:
>>> Strengthen warnings about using pg_dump's -i option.
>> 
>> The proposed TODO item was not about doing this, it was about removing
>> the option altogether.  AFAICS it's a foot-gun and nothing else --- why
>> do we have it?

> I thought the simple fix was to just have a better warning and see how
> that works in practice.  There was some concern from people about
> removing it without more feedback/warning.  I am happy to remove it.

My proposal would be to continue to accept the option but just ignore it
(ie, error out on version mismatch whether or not -i is given).  This
way we wouldn't break any scripts that use the option, but things would
still be safe.

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] [GSoC] Need for advice on improving hash index performance

[Kenneth Marshall]

On Wed, Mar 26, 2008 at 10:55:44PM +0800, Xiao Meng wrote:
> Hello, Hackers:
> 
> I've post a question about GSoC before.
> [GSoC] (Is it OK to choose items without % mark in theToDoList) && (is
> it an acceptable idea to build index on Flash Disk)
> http://archives.postgresql.org/pgsql-hackers/2008-03/msg00859.php
> 
> Now, I start a new thread since the topic had been transfered to
> improving hash index.
> Kenneth had started a thread about this topic before.
> http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php
> 
> I've browsed the long discussion follow Kenneth's advice.
> Now, I've have some rough idea. And there's still something confused
> me.I'm glad to hear your advice.
> 
> 1. The Benchmark
> In what condition, hash index "should" be used in general , i.e. when
> it should work better that b-tree? I think we should focus on these
> condition. IMHO, hash should be efficient when the cost of comparison
> is expensive and most of the query is equality query.
> In addition, maybe we can use TPC benchmark's data.
> 
Yes, the cost of comparison for large values can be much better
with a hash index. Hash indexes really only work with an equality
query. The hash should outperform the normal Btree index when the
index probe takes O(1). This can be accomplished by making the
bucket size smaller, packing fewer items per bucket, or both.

> 2.The management of the bucket
> some idea in the todo list is about this
> 
> > o Pack hash index buckets onto disk pages more efficiently
> >
> >  Currently only one hash bucket can be stored on a page. Ideally
> >  several hash buckets could be stored on a single page and greater
> >  granularity used for the hash algorithm.
> >
> >  http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php
> >
> AIUI, one approach is to divide  one  big  hash  bucket into
> sub-bucket and re-hash in the big  bucket. And we should use closed
> hash IMHO.
> 
One approach that I had been considering, is to subdivide the page
into buckets that are some fraction of the page. We still need to
handle the fact that hash functions are not perfect, in general, and
that for non-unique indexes there could be many, many items in the
bucket with the same value (and hash value). The current overflow
page idea will handle that case if we extend the idea slightly. First,
the fractional page-size buckets should be a 1/2^n. Then do not use
all of the index tuple slots in each bucket. Reserve the space that
is unused on the page through this to create the initial overflow
page on the spare space on the same index page. Then any overflow
pages after the first will be a full page in size. Obviously, testing
can be used to determine the best bucket-size vs. initial overflow
page space size. If the buckets are relatively empty, then simply using
a sub-set of the hash value to index the sub-page bucket will require
no additional computation.

What do you see as the advantage in using a closed hash for re-hashing
the big bucket into sub-buckets?

> 2.what about other Hash Implementation
> What about static hash ? We need to re-organize the whole file when
> the file grows big. But what about in such a condition  that we can
> estimate the size of table and it's not changed frequently. Besides
> the high cost of re-organization ,it's an attractive technique because
> we only  need one I/O  to fetch  a  item.
> Extendable hash is also effective when the memory is big enough .It's
> also need one I/O to fetch a item. Of course the growth of memory
> usage is a big problem.
> 

The file re-organization can be minimized by allowing for the setting
of some intial size and multiplicity values during the initial index
creation.

> 3. the page layout of the bucket
> > o In hash indexes, consider storing the hash value with or instead
> > of the key itself
> >
> It means that we need at least two I/O's to fetch a tuple.
> For a three level B-Tree(if we place the upper two level into the
> memory), it works not worse than hash index. IMHO, Hash is effective
> when the index is big enough and the B-Tree grows to 4 level.
> Neil Conway had posted a patch doing this with an old version of
> PostgreSQL. I think It's a good start point for me.
> 

Right, we need to get hash indexes to the point at which their
performance surpasses Btree indexes for some problem domains. If
we could include unique index support and WAL, that would be great.

> 
> 4.what about user defined hash function?
> Sometimes, DBA may know the  data distribution well, so DBA can give a
> better hash function in some cases.
> As far as I know, Oracle support user defined hash function using a
> "Hash is exper" clause
> e.g.
> create cluster acctclust (acctid int) size 80 single table hashkeys 10
>  hash is mod(acctid,13)
> 

I think that having a user defined hash function is not nearly as
valuable as fixing the other warts and performance issues. One item
that could be easily change is the some of the h

Re: [HACKERS] Surfacing qualifiers

[David Fetter]

On Wed, Mar 26, 2008 at 01:21:14PM -0400, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > What happens now with dblink is that the remote table (more generally,
> > the output of a fixed query) gets materialized into memory in its
> > entirety, and if it's bigger than what's available, it will crash the
> > backend or worse.
> 
> This is utter nonsense.  It gets put into a tuplestore which is entirely
> capable of spilling to disk.  Slow, yes, but crashing is a lie.
> 
> > That happens because functions do not have any
> > access to the predicates with which they were called, so the current
> > workaround is to pass the predicates manually and then cast.
> 
> dblink is not a suitable framework for improving that situation.
> Maybe someday we'll have a proper implementation of SQL/MED ...

This is intended to be a step or two along the way :)

You mentioned in an earlier mail that the information exposed was
inadequate.  Could you sketch out what information would really be
needed and where to find it?

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

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

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

Re: [HACKERS] Surfacing qualifiers

[Tom Lane]

David Fetter <[EMAIL PROTECTED]> writes:
> What happens now with dblink is that the remote table (more generally,
> the output of a fixed query) gets materialized into memory in its
> entirety, and if it's bigger than what's available, it will crash the
> backend or worse.

This is utter nonsense.  It gets put into a tuplestore which is entirely
capable of spilling to disk.  Slow, yes, but crashing is a lie.

> That happens because functions do not have any
> access to the predicates with which they were called, so the current
> workaround is to pass the predicates manually and then cast.

dblink is not a suitable framework for improving that situation.
Maybe someday we'll have a proper implementation of SQL/MED ...

regards, tom lane

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> [EMAIL PROTECTED] (Bruce Momjian) writes:
> >>> Strengthen warnings about using pg_dump's -i option.
> >> 
> >> The proposed TODO item was not about doing this, it was about removing
> >> the option altogether.  AFAICS it's a foot-gun and nothing else --- why
> >> do we have it?
> 
> > I thought the simple fix was to just have a better warning and see how
> > that works in practice.  There was some concern from people about
> > removing it without more feedback/warning.  I am happy to remove it.
> 
> My proposal would be to continue to accept the option but just ignore it
> (ie, error out on version mismatch whether or not -i is given).  This
> way we wouldn't break any scripts that use the option, but things would
> still be safe.

A larger question is why the option was added in the first place.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.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

[HACKERS] Sending queries directly

[Pedro Belmino]

Hello,
I am creating an agent that runs alongside the postgres written in c++, I
have a question: How send sql queries directly for the database without
going need to make any connection?
What I call function, which I use file?

Thanks,
-- 
Pedro Belmino.

# Ciência da Computação - UNIFOR
# [EMAIL PROTECTED]

[HACKERS] HELP

[Pedro Belmino]

Hello,
I am creating an agent that runs alongside the postgres written in c++, I
have a question: How send sql queries directly for the database without
going need to make any connection?
What I call function, which I use file?

Thanks,

-- 
Pedro Belmino.

# Ciência da Computação - UNIFOR
# [EMAIL PROTECTED]

Re: [HACKERS] Surfacing qualifiers

[Tom Lane]

David Fetter <[EMAIL PROTECTED]> writes:
> You mentioned in an earlier mail that the information exposed was
> inadequate.  Could you sketch out what information would really be
> needed and where to find it?

The main problem with what you suggest is that it'll fail utterly
on join queries.

AFAICS any real improvement in the situation will require exposing
remote tables as a concept understood by the planner, complete
with ways to obtain index and statistical information at plan time.
After suitable decisions about join strategy and so forth, we'd
wind up with a plan containing a "RemoteTableScan" node which
would have some restriction conditions attached.  Then forwarding
those to the remote database would be sensible.  But expecting a
dblink function to figure out which clauses are restrictions for
its table, when it doesn't even know what other tables were in
the query, is not sensible.

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] Sending queries directly

[Heikki Linnakangas]

Pedro Belmino wrote:

I am creating an agent that runs alongside the postgres written in c++, I
have a question: How send sql queries directly for the database without
going need to make any connection?


You can't. Why would you not want to open a connection?

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

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

Re: [HACKERS] Surfacing qualifiers

[Andrew Dunstan]

David Fetter wrote:


dblink is not a suitable framework for improving that situation.
Maybe someday we'll have a proper implementation of SQL/MED ...



This is intended to be a step or two along the way :)


  


I'm still waiting to see an example of where you say this patch is even 
marginally useful.



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] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> My proposal would be to continue to accept the option but just ignore it
>> (ie, error out on version mismatch whether or not -i is given).  This
>> way we wouldn't break any scripts that use the option, but things would
>> still be safe.

> A larger question is why the option was added in the first place.

It probably seemed like the conservative choice at the time: allow the
user to be smarter than pg_dump when necessary.  What we couldn't have
foreseen was the way the option has been abused by tools that are not as
bright as they think they are.  With the current situation where -i is
used by default, without the user's knowledge (and without showing him
the warning messages, which is why your patch isn't going to improve
matters), it just seems too dangerous to continue to accept the switch.

(I wonder whether some of the complaints we've seen about broken
dump/restore are courtesy of pgAdmin forcing the dump to be taken with
a too-old copy of pg_dump.)

One point after looking back at the previous discussion is that the
current version test is too strict: it will complain if your server is
8.2.7 and pg_dump is 8.2.6.  We probably should not make a newer minor
number a hard error, since 99.99% of the time it would be fine.  So
while I think newer major should be a hard error regardless of -i,
we could consider several responses to newer minor:
* silently allow it always
* print warning and proceed always
* allow -i to control error vs warning for this case only.

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] Sending queries directly

[Andrew Dunstan]

Pedro Belmino wrote:

Hello,
I am creating an agent that runs alongside the postgres written in 
c++, I have a question: How send sql queries directly for the database 
without going need to make any connection?

What I call function, which I use file?




You can't. Why can't you use a connection?

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] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> My proposal would be to continue to accept the option but just ignore it
> >> (ie, error out on version mismatch whether or not -i is given).  This
> >> way we wouldn't break any scripts that use the option, but things would
> >> still be safe.
> 
> > A larger question is why the option was added in the first place.
> 
> It probably seemed like the conservative choice at the time: allow the
> user to be smarter than pg_dump when necessary.  What we couldn't have
> foreseen was the way the option has been abused by tools that are not as
> bright as they think they are.  With the current situation where -i is
> used by default, without the user's knowledge (and without showing him
> the warning messages, which is why your patch isn't going to improve
> matters), it just seems too dangerous to continue to accept the switch.
> 
> (I wonder whether some of the complaints we've seen about broken
> dump/restore are courtesy of pgAdmin forcing the dump to be taken with
> a too-old copy of pg_dump.)

Agreed, but I thought the tools have been fixed so is this still a
problem?

> One point after looking back at the previous discussion is that the
> current version test is too strict: it will complain if your server is
> 8.2.7 and pg_dump is 8.2.6.  We probably should not make a newer minor
> number a hard error, since 99.99% of the time it would be fine.  So
> while I think newer major should be a hard error regardless of -i,
> we could consider several responses to newer minor:
>   * silently allow it always
>   * print warning and proceed always
>   * allow -i to control error vs warning for this case only.

I think it should be silent.  Do we ever change the server behavior that
is visible to pg_dump in a minor release?

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.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] gcc 4.3 breaks ContribCheck in 8.2 and older.

[Kurt Roeckx]

On Tue, Mar 25, 2008 at 06:03:39PM -0400, Andrew Dunstan wrote:
>>
>> Patches committed, please re-enable the back branches so we can
>> see what happens.
>
> I have tested this back as far as 8.0, and all seems OK.

7.4 passed too.


Kurt


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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Strengthen warnings about using pg_dump's -i option.

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> One point after looking back at the previous discussion is that the
>> current version test is too strict: it will complain if your server is
>> 8.2.7 and pg_dump is 8.2.6.  We probably should not make a newer minor
>> number a hard error, since 99.99% of the time it would be fine.  So
>> while I think newer major should be a hard error regardless of -i,
>> we could consider several responses to newer minor:
>> * silently allow it always
>> * print warning and proceed always
>> * allow -i to control error vs warning for this case only.

> I think it should be silent.  Do we ever change the server behavior that
> is visible to pg_dump in a minor release?

It's hardly out of the question --- consider the backslash-escaping
security fixes we applied in 8.1.4, 8.0.8, etc.  Parts of the server
changes were intended to intentionally break unpatched clients, and
I think that'd apply to unpatched pg_dump as well.

Of course, that precedent suggests that any such change would be made in
such a way as to be enforced on the server side, so it wouldn't matter
if pg_dump didn't know it wouldn't work.

Silent allow is fine with me, I was just wondering if anyone liked
the other options better.

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

[Gurjeet Singh]

On Wed, Mar 26, 2008 at 11:10 PM, Pedro Belmino <[EMAIL PROTECTED]>
wrote:

> Hello,
> I am creating an agent that runs alongside the postgres written in c++, I
> have a question: How send sql queries directly for the database without
> going need to make any connection?
> What I call function, which I use file?
>
>
Try looking for the documentation of libpq or libpqxx.
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Proposal: improve shutdown during online backup

[Simon Riggs]

On Wed, 2008-03-26 at 14:36 +0100, Albe Laurenz wrote:
> I'm referring to the discussion in this thread:
> http://archives.postgresql.org/pgsql-hackers/2007-11/msg00946.php
> 
> As expressed in the thread, I think that there should not be
> a backup_label file in the data directory after a clean shutdown,
> because
> a) it's probably an oversight anyway (someone forgot to
>call pg_stop_backup) and
> b) it will force an unnecessary recovery at server restart,
>which will sometimes fail (if the WAL file is no longer there).
> 
> This is my proposal:
> 
> 1) On "pg_ctl stop|restart -m smart", check if online backup is
>in progress and do not shutdown in this case (treat the online
>backup like an open connection).
> 2) On "pg_ctl stop|restart -m fast", remove backup_label after
>the server has been brought down successfully.
> 
> If that's acceptable, I'd be willing to work on it.

Seems reasonable. Go for it.

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

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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

Re: [HACKERS] Sending queries directly

[Chris Browne]

[EMAIL PROTECTED] ("Pedro Belmino") writes:
>  I am creating an agent that runs alongside the postgres written in
>  c++, I have a question: How send sql queries directly for the
>  database without going need to make any > connection?  > What I
>  call function, which I use file?

You don't do that; your agent should use libpq to establish a
connection just like everyone else.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/linux.html
I just removed the instructions in MC:COMMON;LINS > which specify that
it should be installed on AI.  We'll certainly  miss that machine, and
probably spend the rest of our lives fixing programs that mention 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] Surfacing qualifiers

[Neil Conway]

On Wed, 2008-03-26 at 14:38 -0400, Andrew Dunstan wrote:
> I'm still waiting to see an example of where you say this patch is even 
> marginally useful.

It's not hard to think of one:

SELECT * FROM remote_table() WHERE x = 5;

Applying the predicate on the remote database (pushing the predicate
below the function scan) is an elementary optimization, and in many
cases would be enormously more efficient than materializing the entire
remote table at the local site and then applying the qual there.

Certainly I agree with Tom that proper SQL/MED support requires
significant support from both the executor and the optimizer. This is
just a quick hack to take advantage of the existing predicate pushdown
logic -- I just thought it was a cute trick, not something I'd suggest
we include in mainline sources.

-Neil



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

[HACKERS] Timing of parameter/variable name lookup vs legacy behaviors

[Tom Lane]

I spent part of today looking at Gevik Babakhani's patch to let
SQL-language functions refer to their parameters by name instead
of just as $n.  It's not ready to go yet but there are interesting
definitional issues here, especially when you look ahead to using
the same mechanism for resolving references to plpgsql parameters
and local variables, as we've discussed previously:
http://archives.postgresql.org/pgsql-hackers/2007-11/msg00169.php

The first question is: what precedence should parameter lookup have
relative to other possible ways to resolve an ambiguous name, ie, at
what point do we give the callback a chance?  In particular, should this
occur before or after trying to resolve the name using an implicit RTE?
Given that we've deprecated implicit RTEs, I think there's a good
argument to be made for trying that last.

The other thing that I'm thinking about is that if we change plpgsql to
use this method then it will start resolving ambiguous names as query
columns rather than local variables, when it has always done the
opposite in the past.  Based on all the complaints we've heard, this is
probably the better definition, but surely it's going to break a few
peoples' code in subtle ways.  Is it important to provide a
compatibility mode?  If so, it seems the way to make that happen would
be to give the callback hook two chances, once before we've tried for
query-local names and once after.  We'd need an extra argument in its
signature so it'd know which call this is.

Comments?

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] Surfacing qualifiers

[David Fetter]

On Wed, Mar 26, 2008 at 02:26:41PM -0700, Neil Conway wrote:
> On Wed, 2008-03-26 at 14:38 -0400, Andrew Dunstan wrote:
> > I'm still waiting to see an example of where you say this patch is
> > even marginally useful.
> 
> It's not hard to think of one:
> 
> SELECT * FROM remote_table() WHERE x = 5;

In DBI-Link's case, remote_table() may not have all the nice features
a Postgres data store would.  It might or might not have indexes, for
example, and the best hints it could get might well be those
predicates supplied by DBI-Link.

> Applying the predicate on the remote database (pushing the predicate
> below the function scan) is an elementary optimization, and in many
> cases would be enormously more efficient than materializing the
> entire remote table at the local site and then applying the qual
> there.
> 
> Certainly I agree with Tom that proper SQL/MED support requires
> significant support from both the executor and the optimizer.
> This is just a quick hack to take advantage of the existing
> predicate pushdown logic -- I just thought it was a cute trick, not
> something I'd suggest we include in mainline sources.

I disagree that it's "just" a cute trick.  I've managed to get dblink
to use it transparently with dblink :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

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

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

[HACKERS] Windows shared_buffers limitations

[Greg Smith]

Was working on some documentation today and I realized that I've taken for 
granted the lore about not using large values for shared_buffers in 
Windows without ever understanding why.  Can someone explain what the 
underlying mechanism that causes that limitation is?  From poking the 
archives I got the impression it was some page mapping issue but details 
are elusive.


--
* 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] Script binaries renaming

[Marc G. Fournier]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1



- --On Wednesday, March 26, 2008 12:58:41 +0100 Zdeněk Kotala 
<[EMAIL PROTECTED]> wrote:

> Minimal me :-) and Solaris Architect committee have complain. Question is
> also how many users really use these commands. For example vacuumdb is not
> too important now when we have autovacuum.

Huh?  I run a vacuumdb once a week on all my databases, even with autovacuum 
turned on

- -- 
Marc G. FournierHub.Org Hosting Solutions S.A. (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.4 (FreeBSD)

iD8DBQFH6s4h4QvfyHIvDvMRAnUAAKCByD6R2Kvbf1zBaBQNOAsa2GHwhgCfRs99
s2xER8beIYpPCRsdsDJmLmA=
=6oB1
-END PGP SIGNATURE-


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

Re: [HACKERS] Surfacing qualifiers

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> Certainly I agree with Tom that proper SQL/MED support requires
> significant support from both the executor and the optimizer. This is
> just a quick hack to take advantage of the existing predicate pushdown
> logic -- I just thought it was a cute trick, not something I'd suggest
> we include in mainline sources.

My bad, I had misread where you were pulling the quals from.  There are
still too many cases where it wouldn't work (anyplace EXPLAIN shades the
truth, including Params and subplans) but it's not quite as useless as
I first thought.

Possibly the Param issue could be fixed by inserting the current actual
value of the Param instead of listing it out as $n.

The real problem is still going to be joins, though.  You'd really
really want the thing to be capable of generating the equivalent of
a nestloop-with-inner-indexscan plan, because that is the only join type
that has any hope of not sucking down the entire content of the remote
table.  And that's not gonna happen without some amount of planner
support.

It might be interesting to think about using the planner's
get_relation_info_hook to attach pseudo indexes to RTE_FUNCTION
relations ... though where it gets the data from is not clear.

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] Script binaries renaming

[Joshua D. Drake]

On Wed, 26 Mar 2008 19:28:49 -0300
"Marc G. Fournier" <[EMAIL PROTECTED]> wrote:

> Huh?  I run a vacuumdb once a week on all my databases, even with
> autovacuum turned on

Yeah I have to agree. Autovacuum only solves the common data issues.
There are still many, many issues that it can't solve. Although 8.3 is
a huge step forward.

Joshua D. Drake

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



signature.asc
Description: PGP signature

Re: [HACKERS] Proposal: improve shutdown during online backup

[Greg Smith]

On Wed, 26 Mar 2008, Albe Laurenz wrote:


1) On "pg_ctl stop|restart -m smart", check if online backup is
  in progress and do not shutdown in this case (treat the online
  backup like an open connection).


As long as you give a warning as to the cause.  While you're in there, I 
think more output in general about the reason why a smart shutdown failed 
would be nice as well.  I haven't looked at the code to see if it's 
practical but I'd love "shutdown blocked by pid 53213,53216" rather than 
having to go search for them myself after it quietly fails.



2) On "pg_ctl stop|restart -m fast", remove backup_label after
  the server has been brought down successfully.


And you need a warning here as well about this fact.  I think the actual 
details associated with that label should be both printed and put into the 
logs at this time, so you know which backup you just hosed.  Maybe the 
label file could get renamed instead?  Just deleting the file without 
saving it somewhere doesn't seem right, that's the sort of thing MySQL 
would do. If there's [one|some] of those failed backup logs inside $PGDATA 
that gives an additional clue to an admin who doesn't watch that logs that 
something is wrong with the backups.


There are three options here for how "-m fast" could handle things:

1) Warning, remove backup label.

2) Warning and server is not stopped.  This is unacceptable because too 
many scripts expect fast shutdown will usually take the server down ( 
/etc/init.d/postgresql being the most popular)


3) Server stops but you do get a stern warning that it will not start 
again until you remove the backup label yourself--the current behavior 
with a warning.  The problem with this one is that some shutdowns don't 
have any human involvement (again, consider server reboot) and therefore 
you can't assume anyone will ever see this message.


If you want to remove the root problem here, you have to follow (1) and 
remove the label.  Otherwise it's still the case that the person who 
starts the database will be surprised if the person stopping it isn't 
paying attention (or isn't a person).


--
* 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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan <[EMAIL PROTECTED]> writes:
  

Alvaro Herrera wrote:


Tom Lane wrote:
  

The real question here is whether Windows' stat() is telling the truth
about how much filesystem space has actually been allocated to a file.
It seems entirely possible that it's not; but if it is, then I think we
have a problem.



  

Has this been examined by a Windows hacker?
  


  

If someone can suggest a test program I'll be happy to run it.



One thing that would be good is just to see who else can reproduce
the original observation:
http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php

It might occur only on some versions of Windows, for instance.


  


I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 
host.


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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>>> The real question here is whether Windows' stat() is telling the truth
>>> about how much filesystem space has actually been allocated to a file.
>>
>> One thing that would be good is just to see who else can reproduce
>> the original observation:
>> http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php

> I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 
> host.

OK, so the next question is do we really have an issue, or is this just
an observational artifact?  What I'd try is deliberately running the
machine out of disk space with a long series of inserts, and then see
whether subsequent checkpoint attempts fail due to ENOSPC errors while
trying to write out dirty buffers.

To avoid conflating this effect with anything else, it'd be best if you
could put the DB on its own small partition, and *not* put pg_xlog
there.

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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan <[EMAIL PROTECTED]> writes:
  

Tom Lane wrote:


The real question here is whether Windows' stat() is telling the truth
about how much filesystem space has actually been allocated to a file.


One thing that would be good is just to see who else can reproduce
the original observation:
http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php
  


  
I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 
host.



OK, so the next question is do we really have an issue, or is this just
an observational artifact?  What I'd try is deliberately running the
machine out of disk space with a long series of inserts, and then see
whether subsequent checkpoint attempts fail due to ENOSPC errors while
trying to write out dirty buffers.

To avoid conflating this effect with anything else, it'd be best if you
could put the DB on its own small partition, and *not* put pg_xlog
there.


  


I'm working on this (thank goodness for junctions). Maybe we shopuld 
look at providing a config setting for pg_xlog.


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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan <[EMAIL PROTECTED]> writes:
  

Tom Lane wrote:


The real question here is whether Windows' stat() is telling the truth
about how much filesystem space has actually been allocated to a file.


One thing that would be good is just to see who else can reproduce
the original observation:
http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php
  


  
I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 
host.



OK, so the next question is do we really have an issue, or is this just
an observational artifact?  What I'd try is deliberately running the
machine out of disk space with a long series of inserts, and then see
whether subsequent checkpoint attempts fail due to ENOSPC errors while
trying to write out dirty buffers.

To avoid conflating this effect with anything else, it'd be best if you
could put the DB on its own small partition, and *not* put pg_xlog
there.


  


OK, a very large insert failed as expected. Checkpoint succeeded. Then 
vacuum recovered the space.


I suspect that the size reported by stat() is a little delayed here, but 
the file system is keeping proper track of it, so the lseek that tries 
to extend the file fails at the right spot.


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] [DOCS] pg_total_relation_size() and CHECKPOINT

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I suspect that the size reported by stat() is a little delayed here, but 
> the file system is keeping proper track of it, so the lseek that tries 
> to extend the file fails at the right spot.

Hmm.  If it really works that way, one would hope Microsoft would've
documented that someplace.  Can anyone find a statement that Windows'
stat() is not current?

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] Planner/optimizer tool

[Tom Raney]

I'm scoping a project to display the choices and plans the 
planner/optimizer considers while PostgreSQL develops a plan.  It would 
be primarily a teaching tool but may be of use to users curious about 
the inner workings of the planner. 

This is in contrast with EXPLAIN, which provides information about the 
*final* plan selected by the planner.   This tool would show details 
about the access methods available at each step and the associated 
access costs, for example.


There is a TODO item: "Allow EXPLAIN output to be more easily processed 
by scripts, perhaps XML" which could be a good first step for this 
project. 


Is anyone working on a related project?

-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