date:20060719

Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I think the place we'd ultimately like to get to involves changing the
>> executor's Result node type to have a list of targetlists and sequence
>> through those lists to produce its results

> I was actually just looking at that and ended up thinking that it might 
> be better to deal with it one level down in ExecProject (because it is 
> already passing targetlists directly to ExecTargetList).

I'd vote against that, because (a) ExecProject is used by all executor
node types, and we shouldn't add overhead to all of them for the benefit
of one; (b) ExecProject doesn't actually have any internal state at the
moment.  To keep track of which targetlist to evaluate next, it would
not only need some internal state, it would have to be told the current
"es_direction".  This stuff fits much better at the exec node level ---
again, I'd suggest looking at Append for a comparison.

But really the executor part of this is not the hard part; what we need
to think about first is what's the impact on the Query datastructure
that the parser/rewriter/planner use.

I'm still liking the idea of pushing multi-values into a jointree node
type.  Basically this would suggest representing "VALUES ..." as if it
were "SELECT * FROM VALUES ..." (which I believe is actually legal
syntax per spec) --- in the general case you'd need to have a Query node
that has a trivial "col1, col2, col3, ..." targetlist and then the
multiple values lists are in some kind of jointree entry.  But possibly
this could be short-circuited somehow, at least for INSERT.

BTW, I noticed an interesting property of historical Postgres behavior:
you can put a table reference into a VALUES targetlist.

regression=# create table foo (like tenk1);
CREATE TABLE
regression=# insert into foo values (tenk1.*);
ERROR:  missing FROM-clause entry for table "tenk1"
LINE 1: insert into foo values (tenk1.*);
^
regression=# set add_missing_from to 1;
SET
regression=# insert into foo values (tenk1.*);
NOTICE:  adding missing FROM-clause entry for table "tenk1"
LINE 1: insert into foo values (tenk1.*);
^
INSERT 0 1
regression=# 

So that last is really exactly equivalent to

insert into foo select * from tenk1;

I do not feel a need to support this sort of thing when there are
multiple VALUES targetlists, but it'd be nice not to break it for the
single-targetlist case.  At least not till we're ready to disable
add_missing_from entirely.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_regress breaks on msys

I wrote:
> What I think happened here is that diff reported a difference and
> pg_regress misinterpreted the exit status as being a hard failure.
> Can someone check on whether it's possible to tell the difference
> between these cases with Windows diff ?

So the latest result shows that the return value from system() is
in fact "1":

 type_sanity  ... ok
 opr_sanity   ... ok
test geometry ... diff command failed with status 1: "diff -w 
"./expected/geometry.out" "./results/geometry.out" >"./results/geometry.diff""
server stopped

What I am now wondering is why win32.h defines WIFEXITED and WEXITSTATUS
the way it does.  We have not previously been using those macros to test
the result of system() --- at least not in any exercised code path ---
and what I'm thinking is that they are flat out wrong.  At least for
testing system().  Are the results of GetExitCodeProcess() and pclose()
really defined differently?

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PATCHES] [HACKERS] 8.2 features?


Tom Lane wrote:

Joe Conway <[EMAIL PROTECTED]> writes:

The difficulty is finding a way to avoid all that extra work without a 
very ugly special case kludge just for inserts.


[ thinks a bit ... ]

It seems to me that the reason it's painful is exactly that INSERT
... VALUES is a kluge already.  We've special-cased the situation where
the INSERT's  is a  with
exactly one row --- but actually a  with
multiple rows ought to be allowed anywhere you can currently write
"SELECT ...".  So ideally fixing this would include eliminating the
current artificial distinction between two types of INSERT command.

I think the place we'd ultimately like to get to involves changing the
executor's Result node type to have a list of targetlists and sequence
through those lists to produce its results


I was actually just looking at that and ended up thinking that it might 
be better to deal with it one level down in ExecProject (because it is 
already passing targetlists directly to ExecTargetList).



That part seems clear, what's a bit less clear is what the
ripple effect on the upstream parser/planner data structures should be.
Should *all* occurrences of Query be changed to have a
list-of-targetlists?  Sounds ugly, and I don't understand what it would
mean for any Query other than one representing a VALUES construct.


There are certainly many places to be looked at if Query.targetList 
becomes a list-of-targetlists (about 153 if I grep'd correctly).




[ thinks some more ... ]

Maybe the right place to put the list-of-targetlists functionality is
not in Query per se, but in a new type of jointree node.  This would
localize the impact as far as changing the datastructures go, but I've
not thought hard enough about what the impact would actually be.


OK. You've given me a good bit to think about -- thanks!

Joe


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Statement Queuing

2006-07-19 Thread Mark Kirkwood


Tom Lane wrote:

Mark Kirkwood <[EMAIL PROTECTED]> writes:
Right - in principle it is not that difficult to add (once I have the 
machinery for the cost limiter going properly that is). I thinking we 
could either:



1. Add hooks to count work_mem allocations where they happen, or
2. Scan the plan tree and deduce how many work_mem allocations there 
will be.


The problem with this is that many of the cost models depend on
work_mem, so you can't simply arbitrarily alter the setting
after-the-fact.  At least not if you don't want to kill performance.



Right - the intention would be to merely count the number of work_mem 
allocations against some type of total, rather than modifying work_mem 
itself.


Cheers

Mark

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PATCHES] [HACKERS] 8.2 features?

Joe Conway <[EMAIL PROTECTED]> writes:
> The difficulty is finding a way to avoid all that extra work without a 
> very ugly special case kludge just for inserts.

[ thinks a bit ... ]

It seems to me that the reason it's painful is exactly that INSERT
... VALUES is a kluge already.  We've special-cased the situation where
the INSERT's  is a  with
exactly one row --- but actually a  with
multiple rows ought to be allowed anywhere you can currently write
"SELECT ...".  So ideally fixing this would include eliminating the
current artificial distinction between two types of INSERT command.

I think the place we'd ultimately like to get to involves changing the
executor's Result node type to have a list of targetlists and sequence
through those lists to produce its results (cf Append --- perhaps while
at it, divorce the "gating node" functionality into a different node
type).  That part seems clear, what's a bit less clear is what the
ripple effect on the upstream parser/planner data structures should be.
Should *all* occurrences of Query be changed to have a
list-of-targetlists?  Sounds ugly, and I don't understand what it would
mean for any Query other than one representing a VALUES construct.

[ thinks some more ... ]

Maybe the right place to put the list-of-targetlists functionality is
not in Query per se, but in a new type of jointree node.  This would
localize the impact as far as changing the datastructures go, but I've
not thought hard enough about what the impact would actually be.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PATCHES] [HACKERS] 8.2 features?


Tom Lane wrote:

Joe Conway <[EMAIL PROTECTED]> writes:

I did some testing today against mysql and found that it will easily 
absorb insert statements with 1 million targetlists provided you set 
max_allowed_packet high enough for the server. It peaked out at about 
600MB, compared to my test similar last night where it was using about 
3.8 GB when I killed it.



So the question is, do we care?


What's the performance like relative to mysql?  It seems hard to believe
that we can afford the overhead of a separate INSERT statement per row
(duplicating all the work of parse analysis, rewrite, planning, executor
start/stop) ... at least not without looking mighty bad.


I don't have the exact numbers handy, but not too great.

As I recall, with last night's patch we did 100K inserts in about 4 
seconds, and today mysql did 100K in about 1 second. We never finished 
the 1 million insert test due to swapping (I killed it after quite a 
while), and mysql did 1 million in about 18 seconds (we did 300K in 13 
seconds). The hardware was not identical between last night's test and 
today's on mysql, but very similar (similar CPUs and memory, although 
the machine I did the mysql tests on had scsi drives, while the pg test 
was done on sata).


The difficulty is finding a way to avoid all that extra work without a 
very ugly special case kludge just for inserts. I've been banging my 
head on that on-and-off for a few days now, and every idea looks uglier 
than the last. One suggestion I got off list was to figure out a way to 
build a tuplestore and use it to feed the executor. That's starting to 
sound better and better to me.


Any ideas or guidance would be greatly appreciated.

Joe

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PATCHES] [HACKERS] 8.2 features?

Joe Conway <[EMAIL PROTECTED]> writes:
> I did some testing today against mysql and found that it will easily 
> absorb insert statements with 1 million targetlists provided you set 
> max_allowed_packet high enough for the server. It peaked out at about 
> 600MB, compared to my test similar last night where it was using about 
> 3.8 GB when I killed it.

> So the question is, do we care?

What's the performance like relative to mysql?  It seems hard to believe
that we can afford the overhead of a separate INSERT statement per row
(duplicating all the work of parse analysis, rewrite, planning, executor
start/stop) ... at least not without looking mighty bad.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Statement Queuing

Mark Kirkwood <[EMAIL PROTECTED]> writes:
> Right - in principle it is not that difficult to add (once I have the 
> machinery for the cost limiter going properly that is). I thinking we 
> could either:

> 1. Add hooks to count work_mem allocations where they happen, or
> 2. Scan the plan tree and deduce how many work_mem allocations there 
> will be.

The problem with this is that many of the cost models depend on
work_mem, so you can't simply arbitrarily alter the setting
after-the-fact.  At least not if you don't want to kill performance.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Statement Queuing

2006-07-19 Thread Mark Kirkwood


Jim C. Nasby wrote:

Something that would be extremely useful to add to the first pass of
this would be to have a work_mem limiter. This would allow users to set
work_mem much more aggressively without worrying about pushing the
machine to swapping. That capability alone would make this valuable to a
very large number of our users.




Right - in principle it is not that difficult to add (once I have the 
machinery for the cost limiter going properly that is). I thinking we 
could either:


1. Add hooks to count work_mem allocations where they happen, or
2. Scan the plan tree and deduce how many work_mem allocations there 
will be.


1. might be tricky, because I'm taking the resource lock before the 
executor is actually run (beginning of PortalRun), so 2. might be the 
most workable approach.


However as I understand it, this sounds very like Simon's shared 
work_mem proposal, and the major issue there (as I understood it) was 
that for many/most(?) OSes free(3) doesn't synchronously release memory 
back to OSes free list - it may only be immediately reusable for the 
process that actually freed it (in some cases it may only *ever* be 
reusable for the process that freed it - until that process terminates 
of course).


Now it may be for DSS workloads that the freed memory gets back to the 
free list "quickly enough", or that this type of work_mem limiting - 
even though not entirely accurate in its memory arithmetic, is "good 
enough" to prevent OOM situations - clearly some time will need to be 
spent checking this for the various platforms.


These factors may make it better to aim for the simple count + cost 
limiters first, and *then* look at the memory one.



Cheers

Mark

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_regress breaks on msys


Tom Lane wrote:
We used to pass these values almost same way when we first did initdb in 
C, and I don't recall any such problems. We had:



  

override CPPFLAGS := -DPGBINDIR=\"$(*bindir*)\" -DPGDATADIR=\"$(*datadir*)\" 
-DFRONTEND -I$(*libpq_srcdir*) $(*CPPFLAGS*)



That seems a bit interesting.  What are the stars for?  I don't see
anything about a syntax like that in my gmake documentation.

  


The stars are from my MUA not handling C&P from formatted text as well 
as it should in text mode. It should have read:


override CPPFLAGS := -DPGBINDIR=\"$(bindir)\" -DPGDATADIR=\"$(datadir)\" 
-DFRONTEND -I$(libpq_srcdir) $(CPPFLAGS)


cheers

andrew






---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Progress bar updates

2006-07-19 Thread Agent M


Why make it so complicated?

There could be a guc to indicate that the client is interested in 
progress updates. For the execution phase, elog(INFO,...) could be 
emitted for each major plan node. (The client would probably run the 
explain plan beforehand or it would be embedded in the elog).


During the downloading of the rows, the client would display the bar 
relative to the number of estimated rows returned.


-M

On Jul 18, 2006, at 2:35 PM, Gregory Stark wrote:



Has anyone looked thought about what it would take to get progress 
bars from

clients like pgadmin? (Or dare I even suggest psql:)

My first thought would be a message like CancelQuery which would cause 
the
backend to peek into a static data structure and return a message that 
the
client could parse and display something intelligent. Various commands 
would

then stuff information into this data structure as they worked.


¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
[EMAIL PROTECTED]
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] pg_regress breaks on msys

I wrote:
> I just committed a change to extract the paths via pg_config_paths.h.
> If that doesn't fix it then I guess the next thing is to put in some
> debug printout to show what values are really getting compiled in :-(

Seems that *did* fix it, which opens a whole new set of questions about
how much you can trust msys' make.  However, the latest seahorse results
show we still have a bug or two:

 oidjoins ... ok
 type_sanity  ... ok
 opr_sanity   ... ok
test geometry ... server stopped
diff command failed: "diff -w "./expected/geometry.out" 
"./results/geometry.out" >"./results/geometry.diff""
make: *** [check] Error 2

What I think happened here is that diff reported a difference and
pg_regress misinterpreted the exit status as being a hard failure.
Can someone check on whether it's possible to tell the difference
between these cases with Windows diff ?

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_regress breaks on msys

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Unless it also lies on the echoed command line this seems an 
> unconvincing explanation. The seahorse log says:

> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline 
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing  
> -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND  
> -I/c/tcl/include "-I../../../src/include/port/win32" 
> '-DPGBINDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/bin"' 
> '-DLIBDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/lib"' 
> '-DPGSHAREDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql"' 
> '-DHOST_TUPLE="i686-pc-mingw32"' '-DMAKEPROG="make"' 
> '-DSHELLPROG="/bin/sh.exe"' -c -o pg_regress.o pg_regress.c

> If those -D values are not what it gets then that would be quite evil.

Indeed ... but if those *are* what it gets then how can you explain the
constructed paths?

I just committed a change to extract the paths via pg_config_paths.h.
If that doesn't fix it then I guess the next thing is to put in some
debug printout to show what values are really getting compiled in :-(

> We used to pass these values almost same way when we first did initdb in 
> C, and I don't recall any such problems. We had:

> override CPPFLAGS := -DPGBINDIR=\"$(*bindir*)\" -DPGDATADIR=\"$(*datadir*)\" 
> -DFRONTEND -I$(*libpq_srcdir*) $(*CPPFLAGS*)

That seems a bit interesting.  What are the stars for?  I don't see
anything about a syntax like that in my gmake documentation.

> There is also this warning, by the way:
> pg_regress.c:63: warning: 'shellprog' defined but not used

Good catch, fix committed.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Progress bar updates

2006-07-19 Thread Christopher Kings-Lynne


It would be the most practical way for a DBA to monitor an application. But
it's not going to be convenient for clients like pgadmin or psql. Even a web
server may want to, for example, stream ajax code updating a progress bar
until it has results and then stream the ajax to display the results. Having
to get the backend pid before your query and then open a second database
connection to monitor your first connection would be extra footwork for
nothing.


But that said, it CAN be coded and work just fine no?


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] pg_regress breaks on msys


Tom Lane wrote:

I wrote:
  

I don't see where cwd enters into it.  The thing I don't understand is
that the value of the make variable $(bindir) is apparently changing.
How can it, when it's been hard-wired into Makefile.global by configure?



After some googling I gather that msys' make has been hacked to
transform paths between actual Windows paths and virtual paths
at what-they-think-are-strategic spots.  If this is correct, then
I think our problem is that the method I used to inject the values
of $(bindir) and friends into pg_regress.c ends up supplying actual
Windows paths, where we would much rather it supplied virtual paths.



  


Unless it also lies on the echoed command line this seems an 
unconvincing explanation. The seahorse log says:


gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing  -I../../../src/include 
-I./src/include/port/win32 -DEXEC_BACKEND  -I/c/tcl/include "-I../../../src/include/port/win32" 
'-DPGBINDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/bin"' '-DLIBDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/lib"' 
'-DPGSHAREDIR="/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql"' '-DHOST_TUPLE="i686-pc-mingw32"' 
'-DMAKEPROG="make"' '-DSHELLPROG="/bin/sh.exe"' -c -o pg_regress.o pg_regress.c



If those -D values are not what it gets then that would be quite evil.

We used to pass these values almost same way when we first did initdb in 
C, and I don't recall any such problems. We had:


override CPPFLAGS := -DPGBINDIR=\"$(*bindir*)\" -DPGDATADIR=\"$(*datadir*)\" 
-DFRONTEND -I$(*libpq_srcdir*) $(*CPPFLAGS*)


There is also this warning, by the way:

pg_regress.c:63: warning: 'shellprog' defined but not used


cheers


andrew



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[HACKERS] always denying corruption

2006-07-19 Thread mdean


Marc Munro wrote:


For the record, here are the results of our (ongoing) inevstigation into
the index/heap corruption problems I reported a couple of weeks ago.

We were able to trigger the problem with kernels 2.6.16, 2.6.17 and
2.6.18.rc1, with 2.6.16 seeming to be the most flaky.

By replacing the NFS-mounted netapp with a fibre-channel SAN, we have
eliminated the problem on all kernels.

From this, it would seem to be an NFS bug introduced post 2.6.14, though
we cannot rule out a postgres bug exposed by unusual timing issues.

Our starting systems are: 


Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875
Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux (and others)
kernel boot option: elevator=deadline
16 Gigs of RAM
postgresql-8.0.8-1PGDG
Bonded e1000/tg3 NICs with 8192 MTU.
Slony 1.1.5

NetApp FAS270 OnTap 7.0.3
Mounted with the NFS options
rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac
Jumbo frames 8192 MTU.

All postgres data and logs are stored on the netapp.

All tests results were reproduced with postgres 8.0.8

__
Marc

On Fri, 2006-06-30 at 23:20 -0400, Tom Lane wrote:
 


Marc Munro <[EMAIL PROTECTED]> writes:
   


We tried all of these suggestions and still get the problem.  Nothing
interesting in the log file so I guess the Asserts did not fire.
 


Not surprising, it was a long shot that any of those things were really
broken.  But worth testing.

   


We are going to try experimenting with different kernels now.  Unless
anyone has any other suggestions.
 


Right at the moment I have no better ideas :-(

regards, tom lane
   



 

On a good stock day, some levity is justified.  How are hackers like 
politicians?



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.1/390 - Release Date: 7/17/2006


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Online index builds

On Sat, Jul 15, 2006 at 09:10:46PM -0400, Greg Stark wrote:
> 
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> 
> > Maybe we can show progress indicators in status line (either
> > pg_stat_activity.current_query or commandline shown in ps), like 
> > 
> > WAITING TO START PHASE 1 - WAITING FOR TRANSACTION XXX TO COMPLETE 
> > 
> > or 
> > 
> > INSERTING INDEX ENTRY N OF M
> > 
> > changing every few seconds.
> 
> Hm. That would be very interesting. I'll say that one of the things that
> impressed me very much with Postgres moving from Oracle was the focus on
> usability. Progress indicators would be excellent for a lot of operations.
> 
> That said I'm not sure how much I can do here. For a substantial index we
> should expect most of the time will be spent in the tuplesort. It's hard to
> see how to get any sort of progress indicator out of there and as long as we
> can't it's hard to see the point of getting one during the heap scan or any of
> the other i/o operations.

I'd love to have any kind of progress indication for any sorts that
spill to disk, and there's any number of other long-running operations
where progress info would be very welcome. I certainly wouldn't let lack
of a progress indicator for sorts prevent you from adding one. I like
the idea of periodically updating both current_query and the commandline
that ps shows.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Resurrecting per-page cleaner for btree

On Wed, Jul 12, 2006 at 03:59:01PM +0900, ITAGAKI Takahiro wrote:
> Hi Hackers,
> 
> Can we resurrect the patch proposed by Junji TERAMOTO?
> It removes unnecessary items before btree pages split.
>   http://archives.postgresql.org/pgsql-patches/2006-01/msg00301.php
> 
> There was a problem in the patch when we restarted scans from deleted tuples.
> But now we scan pages at-a-time, so the problem is resolved, isn't it?
>   http://archives.postgresql.org/pgsql-patches/2006-05/msg8.php
> 
> I think this feature is independent from the SITC project and useful for
> heavily-updated indexes. If it is worthwhile, I'll revise the patch to
> catch up on HEAD.

Tom's comment about the patch needing better comments still holds. If
nothing else, do the best you can with the comments in English and
someone else can clean the grammar up.

It's also not clear to me if Tom's comment about not deleting LP_DELETE
tuples at-will is still valid or not.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] How does the planner deal with multiple possible indexes?

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Indeed, if I find a case where there's a large enough number of rows it
> will choose the smaller index. But I'm wondering if it would be better
> to always favor the smaller index, since it would (presumably) be easier
> to keep it in cache?

AFAICS, in existing releases that should happen, because the cost
estimate varies with the size of the index.  And it does happen for me
in simple tests.  You did not provide the requested information to help
us find out why it's not happening for you.

(I'm a bit worried about whether CVS HEAD may have broken this behavior
with the recent changes in the indexscan cost equations ... but unless
you are working with HEAD that's not relevant.)

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Freezing tuples on pages dirtied by vacuum

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> ISTM that as soon as vacuum dirties a page, it might as well update all
> tuples it can (any where Xmin < GetOldestXmin()), since that won't take
> much time compared to the cost of writing the page out.

Perhaps not, but what it will do is destroy data that you might wish you
had later.  Check the archives and note how often we ask people for xmin
values when trying to debug a problem.  I don't think it's a good idea
for aggressive freezing of tuples to be the default behavior.  Moreover,
I can't see that there'd be any real gain from having done it --- it
doesn't look to me like it would save any vacuum-to-prevent-wraparound
operations, since nothing would happen at non-dirty pages.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Max size of a btree index entry

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Could you not just scan right and see what the first key was? Thought
> granted, that means there's a chance of a wasted page scan, but I think
> that'd be somewhat of a corner case, so it might not be bad.

No, because (a) that confuses the first key that happens to be on a page
with its keyspace boundary --- what happens when you need to delete that
data key? and (b) because of locking considerations, you don't want to
move right and then have to back up.  You'd have to hold lock on the
first page while reading in the second, which makes for a nontrivial
performance hit.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] How does the planner deal with multiple possible indexes?

On Wed, Jul 19, 2006 at 07:00:40PM -0400, Tom Lane wrote:
> "Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> > On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> > [snip]
> >> \d does list bdata__ident_filed_departure before bdata_ident; I'm
> >> wondering if the planner is finding the first index with ident_id in it
> >> and stopping there?
> 
> >> From my own experience it was grabbing the first that has the
> > requested field as its first member.. I haven't looked at the code to
> > see if that is the intended behavior.
> 
> Ordering would only matter if the estimated costs were exactly the same,
> which they probably shouldn't be for indexes with such different sizes.
> However, if the estimated number of matching rows were real small, you
> might be winding up with a "one page to fetch" estimate in either case.
> Jim didn't provide enough details to guess what the cost estimates
> actually are...

Indeed, if I find a case where there's a large enough number of rows it
will choose the smaller index. But I'm wondering if it would be better
to always favor the smaller index, since it would (presumably) be easier
to keep it in cache?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Max size of a btree index entry

On Wed, Jul 19, 2006 at 06:23:44PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote:
> >> 1. In a non-rightmost page, we need to include a "high key", or page
> >> boundary key, that isn't one of the useful data keys.
>  
> > Why does a leaf page need a boundary key?
> 
> So you can tell whether a proposed insertion ought to go into this page,
> or the one to its right.  The tree descent logic doesn't guarantee that
> you descend to exactly the correct page --- if concurrent page splits
> are going on, you might have to "move right" one or more times after
> reaching the leaf level.  You need the boundary key to make this test
> correctly.
> 
> And of course, the reason there's no high key on the rightmost page is
> exactly that it has no right-hand neighbor, hence no upper limit on its
> delegated key space.

Could you not just scan right and see what the first key was? Thought
granted, that means there's a chance of a wasted page scan, but I think
that'd be somewhat of a corner case, so it might not be bad.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] Freezing tuples on pages dirtied by vacuum

Currently, the loop in vacuumlazy.c that scans through the tuples on a
page checks each tuple to see if it needs to be frozen (is it's Xmin
older than half-way to wrap-around).

ISTM that as soon as vacuum dirties a page, it might as well update all
tuples it can (any where Xmin < GetOldestXmin()), since that won't take
much time compared to the cost of writing the page out. This would help
prevent the need to dirty the page in the distant future for no reason
other than to freeze tuples. Granted, the old code/checks would still
have to stay in place to ensure that tuples were vacuumed before they
got too old, but that's not much overhead compared to writing the page
to disk.

Comments? If people think this is a good idea I should be able to come
up with a patch.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] How does the planner deal with multiple possible indexes?

"Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> [snip]
>> \d does list bdata__ident_filed_departure before bdata_ident; I'm
>> wondering if the planner is finding the first index with ident_id in it
>> and stopping there?

>> From my own experience it was grabbing the first that has the
> requested field as its first member.. I haven't looked at the code to
> see if that is the intended behavior.

Ordering would only matter if the estimated costs were exactly the same,
which they probably shouldn't be for indexes with such different sizes.
However, if the estimated number of matching rows were real small, you
might be winding up with a "one page to fetch" estimate in either case.
Jim didn't provide enough details to guess what the cost estimates
actually are...

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] pgxs problem

Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Yes, that's pretty small-minded.  It should be something like
> > PG_CONFIG = pg_config
> > bindir := $(shell $(PG_CONFIG) --bindir)
> > That way you can override it.
>
> No objection here, although I'm not entirely convinced why anyone
> would prefer doing that to setting their PATH.  If the pg_config you
> want isn't (first in) your PATH, none of the other PG programs will
> be either, which seems like an awkward situation for getting any
> PG-related work done.

Well, with the above change, both camps would be happy.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] plPHP and plRuby


Ron Mayer wrote:

Tom Lane wrote:

The difference is that I will have reasonable confidence that
the README.TXT under "src/pl" will give instructions that match
the version of PostgreSQL that I have.   I assume that README
will call out the version of PL/R or PL/Ruby that I want that
was tested with the release of PostgreSQL I have.


On what do you base that assumption?  A README file laying about in an
otherwise unused part of the source tree is the very definition of "out
of sight, out of mind".  


I was hoping it would say something like

  PostgreSQL 8.2.0 has been tested with version XX.YY.01 of PL/Whatever
  You can install it by getting that release and doing the following.

with specific version numbers rather than links to URLS that would 
change.


It that wasn't the intent of the README.TXT, I'm not sure what is.



This is way too DIY.

The only thing I think might be worthwhile (and it would help from a 
buildfarm POV) would be something to assist an integrated build from 
disparate sources.


Just a Readme doesn't come close to what I think we need in the general 
case.



cheers

andrew

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] plPHP and plRuby

2006-07-19 Thread Ron Mayer


Tom Lane wrote:

The difference is that I will have reasonable confidence that
the README.TXT under "src/pl" will give instructions that match
the version of PostgreSQL that I have.   I assume that README
will call out the version of PL/R or PL/Ruby that I want that
was tested with the release of PostgreSQL I have.


On what do you base that assumption?  A README file laying about in an
otherwise unused part of the source tree is the very definition of "out
of sight, out of mind".  


I was hoping it would say something like

  PostgreSQL 8.2.0 has been tested with version XX.YY.01 of PL/Whatever
  You can install it by getting that release and doing the following.

with specific version numbers rather than links to URLS that would change.

It that wasn't the intent of the README.TXT, I'm not sure what is.


I can pretty much guarantee you that it will
NOT get updated, especially not during minor releases.  Even if it is up
to date at the instant we put out a release, it'll be obsolete as soon
as the external project makes an update release.  ISTM links like this
are far better kept on project websites ...


I was hoping that this README.TXT point to the specific old version
that was tested in much the same way that the old 8.0.0 source tree
continues to have the same PL/pgsql that has always been there.

If the external project updates their release and breaks compatability
I think they should be encouraged to update the README.TXT to say
something like
  PostgreSQL 8.2.1 has been tested with PL/Whatever version XX.YY.99
If they don't make that update, the README would
  PostgreSQL 8.2.0 has been tested with PL/Whatever version XX.YY.00



I would imagine with this README.TXT proposal, it's the responsibility
of the PL/XXX developer to port their PL to PostgreSQL during the Beta,
and if the did and tested it, the release will point to the version
of the PL supported by the PL maintainer for that version.


And if they didn't?  I was just noticing that the current release of
plruby contains installation instructions that appear to date to 7.3.
If he can't be bothered to update his own docs, what are the odds that
he'll submit timely updates for a README in the main source tree?


Yeah.  Good point.   I guess the alternatives are that the README
still say
  PostgreSQL 7.3.0 has been tested with PL/Ruby version X.Y.Z
or
  We are unaware of up-to-date instructions for PL/Ruby.  Good Luck.
Though if you'd welcome people in the community to submit patches
to that README I suspect they'll be updated even more regularly
than 2002 or whenever 7.3 come out.

If I spent time figuring it out, I wouldn't mind submitting a patch
for such a README; and I suspect the other guys who blog about
PL/Ruby installation instructions in late 2005 would be happy to
submit such patches as well.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] How does the planner deal with multiple possible indexes?

2006-07-19 Thread Gregory Maxwell


On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
[snip]

\d does list bdata__ident_filed_departure before bdata_ident; I'm
wondering if the planner is finding the first index with ident_id in it
and stopping there?



From my own experience it was grabbing the first that has the

requested field as its first member.. I haven't looked at the code to
see if that is the intended behavior.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Max size of a btree index entry

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote:
>> 1. In a non-rightmost page, we need to include a "high key", or page
>> boundary key, that isn't one of the useful data keys.

> Why does a leaf page need a boundary key?

So you can tell whether a proposed insertion ought to go into this page,
or the one to its right.  The tree descent logic doesn't guarantee that
you descend to exactly the correct page --- if concurrent page splits
are going on, you might have to "move right" one or more times after
reaching the leaf level.  You need the boundary key to make this test
correctly.

And of course, the reason there's no high key on the rightmost page is
exactly that it has no right-hand neighbor, hence no upper limit on its
delegated key space.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Max size of a btree index entry

On Tue, Jul 11, 2006 at 10:02:49AM -0400, Tom Lane wrote:
> Currently, we restrict btree index tuples to a size that ensures three of
> them will fit on a page.  The motivation for this is the following two
> considerations:
> 
> 1. In a non-rightmost page, we need to include a "high key", or page
> boundary key, that isn't one of the useful data keys.
 
Why does a leaf page need a boundary key? ISTM if that wasn't the case,
we could actually allow keys to be nearly 8K, constrained by a non-leaf
page needing to include two pointers.

I guess I must be missing something here (and nbtree/README isn't
helping).

> 2. In a non-leaf page, there had better be at least two child pages
> (downlink entries), else we have failed to subdivide the page's key
> range at all, and thus there would be a nonterminating recursion.
> 
> However: a non-leaf page actually has one more pointer than key,
> eg a page with three children needs only two data keys:
> 
>  entire key range assigned to page --
> 
> -- range 1 --  boundary key -- range 2 --  boundary key -- range 3 --
>  |   |   |
>  v   v   v
> child page 1   child page 2 child page 3
> 
> We implement this by having the first data "tuple" on a non-leaf page
> contain only a downlink TID and no key data, ie it's just the header.
> 
> So it appears to me that we could allow the maximum size of a btree
> entry to be just less than half a page, rather than just less than
> a third of a page --- the worst-case requirement for a non-leaf page
> is not three real tuples, but one tuple header and two real tuples.
> On a leaf page we might manage to fit only one real data item, but
> AFAICS that doesn't pose any correctness problems.
> 
> Obviously a tree containing many such pages would be awfully inefficient
> to search, but I think a more common case is that there are a few wide
> entries in an index of mostly short entries, and so pushing the hard
> limit up a little would add some flexibility with little performance
> cost in real-world cases.
> 
> Have I missed something?  Is this worth changing?
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pgxs problem

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Yes, that's pretty small-minded.  It should be something like
> PG_CONFIG = pg_config
> bindir := $(shell $(PG_CONFIG) --bindir)
> That way you can override it.

No objection here, although I'm not entirely convinced why anyone would
prefer doing that to setting their PATH.  If the pg_config you want
isn't (first in) your PATH, none of the other PG programs will be
either, which seems like an awkward situation for getting any PG-related
work done.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] pgxs problem

Michael Fuhr <[EMAIL PROTECTED]> writes:
> How do people with multiple PostgreSQL installations keep track of
> which installation they're using?  I use shell scripts that set
> PATH and a few other environment variables and then exec the command
> I want to run (shell aliases would also work).

Yeah, I do something similar.  In my case I generally want to switch my
attention to different installations at different times, so what I do
is make shellscripts that adjust PATH and other variables.  Then I type
eg ". setv81" to switch into the environment for my REL8_1_STABLE tree.
(Need the "." because just executing the script normally would fail to
affect the parent shell's variables.)  The script itself looks like

# Source this, eg with ". bin/setvariables", to prepare for Postgres work.

STDPATH=${STDPATH:-$PATH}
STDMANPATH=${STDMANPATH:-$MANPATH}

PGSRCROOT=$HOME/REL8_1/pgsql
PGBLDROOT=$PGSRCROOT
PGINSTROOT=$HOME/version81
PATH=$PGINSTROOT/bin:$STDPATH
DEFPORT=5481
MANPATH=$PGINSTROOT/man:$STDMANPATH
PGDATA=$PGINSTROOT/data
PMOPTIONS="-p 5481 -i -F"
PMLOGFILE=server81.log

export PGSRCROOT PGBLDROOT PGINSTROOT PATH MANPATH STDPATH STDMANPATH
export DEFPORT PGDATA PMOPTIONS PMLOGFILE

The reason for the passel of variables is that I have some other scripts
that use the variables to "do the right thing" in all installations.
For instance the script that invokes configure includes

--with-pgport="$DEFPORT" --prefix="$PGINSTROOT"

In particular the point of STDPATH/STDMANPATH is to capture the shell's
original path settings so that switching between installations
repeatedly doesn't cause PATH and MANPATH to grow indefinitely.
You can probably guess what all the other vars are for.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pgxs problem


Michael Fuhr wrote:

On Wed, Jul 19, 2006 at 10:29:14AM -0400, Tom Lane wrote:


The documented behavior is that pgxs invokes whatever pg_config is in
your PATH.


How do people with multiple PostgreSQL installations keep track of
which installation they're using?  I use shell scripts that set
PATH and a few other environment variables and then exec the command
I want to run (shell aliases would also work).  For example, I'd
type "pg73 psql" to run the 7.3 version of psql (which would connect
to a 7.3 server) and I'd type "pg82 gmake" to build an extension
for 8.2devel.  Prefixing each command with pgXX is a minor nuisance
but by being explicit I always know what version I'm using.

What are others doing?



I use something very similar that Tom Lane sent me a while back. The 
only difference is I type, say, "pg81" just once, and it sets up my 
environment for 8.1 (PATH, LD_LIBRARY_PATH, PGDATA, etc). From that 
point on I just use normal commands. Works great for me.


Joe

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[HACKERS] How does the planner deal with multiple possible indexes?

Client has a table with 9 indexes; one is on just ident_id and takes up
75181 pages, the other is on ident_id and another field and uses 117461
pages.  

"bdata__ident_filed_departure" btree (ident_id, filed_departuretime), 
tablespace "array4"
"bdata_ident" btree (ident_id), tablespace "array4"


Whats interesting is that even a simple

SELECT * FROM table WHERE ident_id=1234

uses bdata__ident_filled_departure, even though it would require less IO
to use bdata_ident.

\d does list bdata__ident_filed_departure before bdata_ident; I'm
wondering if the planner is finding the first index with ident_id in it
and stopping there?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] pgxs problem

2006-07-19 Thread Michael Fuhr

On Wed, Jul 19, 2006 at 10:29:14AM -0400, Tom Lane wrote:
> The documented behavior is that pgxs invokes whatever pg_config is in
> your PATH.

How do people with multiple PostgreSQL installations keep track of
which installation they're using?  I use shell scripts that set
PATH and a few other environment variables and then exec the command
I want to run (shell aliases would also work).  For example, I'd
type "pg73 psql" to run the 7.3 version of psql (which would connect
to a 7.3 server) and I'd type "pg82 gmake" to build an extension
for 8.2devel.  Prefixing each command with pgXX is a minor nuisance
but by being explicit I always know what version I'm using.

What are others doing?

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] pgxs problem

Gregory Stark wrote:
> I've tracked down my problem with pgxs to Makefile.global in
> lib/pgxs/src. These lines seem to be the culprits:
>
> bindir := $(shell pg_config --bindir)

Yes, that's pretty small-minded.  It should be something like

PG_CONFIG = pg_config

bindir := $(shell $(PG_CONFIG) --bindir)

That way you can override it.

> I think it should be running $(pkglibdir)/bin/pg_config

Actually pg_config is defined to live in $(bindir), so that would be 
wrong.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] pg_regress breaks on msys

I wrote:
> I don't see where cwd enters into it.  The thing I don't understand is
> that the value of the make variable $(bindir) is apparently changing.
> How can it, when it's been hard-wired into Makefile.global by configure?

After some googling I gather that msys' make has been hacked to
transform paths between actual Windows paths and virtual paths
at what-they-think-are-strategic spots.  If this is correct, then
I think our problem is that the method I used to inject the values
of $(bindir) and friends into pg_regress.c ends up supplying actual
Windows paths, where we would much rather it supplied virtual paths.

An alternative method I had considered using was to have pg_regress.c
get the paths by #including pg_config_paths.h.  Can anyone say whether
pg_config_paths.h receives real or virtual paths when building under
mingw?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] [HACKERS] 8.2 features?


Joe Conway wrote:

Tom Lane wrote:


Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:

Strange.  Last time I checked I thought MySQL dump used 'multivalue 
lists in inserts' for dumps, for the same reason that we use COPY


I think Andrew identified the critical point upthread: they don't try
to put an unlimited number of rows into one INSERT, only a megabyte
or so's worth.  Typical klugy-but-effective mysql design approach ...


OK, so given that we don't need to be able to do 1 million 
multi-targetlist insert statements, here is rev 2 of the patch.


I did some testing today against mysql and found that it will easily 
absorb insert statements with 1 million targetlists provided you set 
max_allowed_packet high enough for the server. It peaked out at about 
600MB, compared to my test similar last night where it was using about 
3.8 GB when I killed it.


So the question is, do we care?

If we do, I'll start looking for a new rev 3 strategy (ideas/pointers 
etc very welcome). If not, I'll start working on docs and regression test.


Thanks,

Joe



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] contrib/hstore - missing, deleted or not material for

2006-07-19 Thread Oleg Bartunov


On Tue, 18 Jul 2006, Nicolai Petri wrote:


If looking at http://www.sai.msu.su/~megera/oddmuse/index.cgi?Hstore the
following is displayed :

-
License

Stable version, included into PostgreSQL distribution, released under BSD
license. Development version, available from this site, released under the
GNU General Public License, version 2 (June 1991).

-

But I never found it in my contrib dir - is it just me that overlooked it and
have it been removed or has it never been included ? I would really like it
to be included for ease of installation on linux systems - if pgxs will fix
this so it can be compiled as a "standalone" extension then it is a solution
I can live with.


USE_PGXS=1 make should works with Makefile below
-
override CPPFLAGS := -I. $(CPPFLAGS)

MODULE_big = hstore
OBJS = hstore_io.o hstore_op.o hstore_gist.o crc32.o

DATA_built = hstore.sql
DOCS = README.hstore
REGRESS = hstore

ifdef USE_PGXS
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/hstore
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif




Best regards,
Nicolai Petri



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] pg_regress breaks on msys

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I think we'll need to have the makefile tell us what it thinks the cwd 
> is, so if it's a virtual path we'll be able to use that.

I don't see where cwd enters into it.  The thing I don't understand is
that the value of the make variable $(bindir) is apparently changing.
How can it, when it's been hard-wired into Makefile.global by configure?

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] constraints, inheritance and "_RETURN" ON SELECT rules

2006-07-19 Thread Andrew Hammond

I posted about this a couple of days ago, but the post was not
complete. Trying again:

-- suppose the following table exists
CREATE TABLE many_tables (
table_id text,  -- defines which virtual table encoded
att0 text,
att1 text,
att2 text,
att3 text
);
-- with some example data
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '1', 'fudge', '0.08', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '2', 'pepper', '0.44', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('dog', '1', 'morgan', 'Golden Retriever', '7');
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('bird', '1', 'boo', 'a grumpy parrot', 'Grey');

-- Goal:
-- 1) Allow incremental application migration to a relational schema
design.
-- 2) Improve performance, even for existing applications.
--
-- Method:
-- 1) Migrate the data out of many_tables into relational tables which
have
--appropriate data-types, constraints etc.
-- 2) Place rules on many_tables to support DML.
-- 3) Use inheritance + constraint to create separate child tables.
-- 4) Define "_RETURN" ON SELECT rule to viewify each child table with
data
--from the appropriate relational table.

CREATE TABLE cat (
cat_id INTEGER PRIMARY KEY -- was att0
  , cat_name TEXT NOT NULL -- was att1
  , aloofness NUMERIC(4,3) -- was att2
 DEFAULT 1.0
 CHECK (0.0 <= aloofness AND aloofness <= 1.0)
);

BEGIN;
INSERT INTO cat (cat_id, cat_name, aloofness)
SELECT CAST(att0 AS integer), att1, CAST(att2 AS numeric(4,3))
FROM many_tables WHERE table_id = 'cat';
DELETE FROM many_tables WHERE table_id = 'cat';

CREATE OR REPLACE RULE many_tables_cat_insert AS
ON INSERT TO many_tables
WHERE NEW.table_id = 'cat'
DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness)
VALUES ( CAST(NEW.att0 AS integer)
   , NEW.att1
   , CAST(NEW.att2 AS numeric(1,3))
 -- gleefully ignore the other attributes
);

CREATE OR REPLACE RULE many_tables_cat_update AS
ON UPDATE TO many_tables
WHERE OLD.table_id = 'cat' AND NEW.table_id = 'cat'
DO INSTEAD
UPDATE cat
SET cat_id = CAST(NEW.att0 AS integer)
  , cat_name = NEW.att1
  , aloofness = CAST(NEW.att2 AS numeric(1,3))
WHERE cat_id = CAST(OLD.att0 AS integer);

CREATE OR REPLACE RULE many_tables_cat_delete AS
ON DELETE TO many_tables
WHERE OLD.table_id = 'cat'
DO INSTEAD
DELETE FROM cat
WHERE cat_id = CAST(OLD.att0 AS integer);

CREATE TABLE many_tables_cat (CHECK (table_id = 'cat')) INHERITS
(many_tables);

CREATE OR REPLACE RULE "_RETURN" AS
ON SELECT TO many_tables_cat DO INSTEAD
SELECT CAST('cat' AS text) AS table_id
 , CAST(cat_id AS text)AS att0
 , cat_nameAS att1
 , CAST(aloofness AS text) AS att2
 , CAST(null AS text)  AS att3
FROM cat;

COMMIT;

-- test
SELECT * FROM cat; --ok
SELECT * FROM many_tables; -- oops!


ahammond=# CREATE TABLE many_tables_cat (CHECK (table_id = 'cat'))
INHERITS (many_tables);
CREATE TABLE
ahammond=# \d many_tables_cat
Table "public.many_tables_cat"
  Column  | Type | Modifiers
--+--+---
 table_id | text |
 att0 | text |
 att1 | text |
 att2 | text |
 att3 | text |
Check constraints:
"many_tables_cat_table_id_check" CHECK (table_id = 'cat'::text)
Inherits: many_tables

ahammond=# CREATE OR REPLACE RULE "_RETURN" AS
ahammond-# ON SELECT TO many_tables_cat DO INSTEAD
ahammond-# SELECT CAST('cat' AS text) AS table_id
ahammond-#  , CAST(cat_id AS text)AS att0
ahammond-#  , cat_nameAS att1
ahammond-#  , CAST(aloofness AS text) AS att2
ahammond-#  , CAST(null AS text)  AS att3
ahammond-# FROM cat;
CREATE RULE
ahammond=# \d many_tables_cat
View "public.many_tables_cat"
  Column  | Type | Modifiers
--+--+---
 table_id | text |
 att0 | text |
 att1 | text |
 att2 | text |
 att3 | text |
View definition:
 SELECT 'cat'::text AS table_id, cat.cat_id::text AS att0, cat.cat_name
AS att1, cat.aloofness::text AS att2, NULL::text AS att3
   FROM cat;

ahammond=# SELECT * FROM many_tables;
ERROR:  could not open relation 1663/16385/209728: No such file or
directory

Ideally, I think this should work as expected. I don't know for sure
how the constraint should fit into things, but I suspect that it should
remain and become more of an assertion. You're kind of stuck trusting
the DBA if the constraint refers to a column which doesn't even exist
in the source of the view.

Either that, or viewifying inherited tables should fail. Which is
probably the correct behaviour if the above can't reasonably be
supported.

Drew


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] lastval exposes information that currval does not

2006-07-19 Thread Phil Frost

On Wed, Jul 19, 2006 at 02:42:49PM -0400, Bruce Momjian wrote:
> Updated text:
> 
>For schemas, allows access to objects contained in the specified
>schema (assuming that the objects' own privilege requirements are
>also met).  Essentially this allows the grantee to look up
>objects within the schema.  Without this permission, it is still
>possible to see the object names, e.g. by querying the system tables,
>so this is not a completely secure way to prevent object access.

I think you are not understanding the nature of the problem I have
described. It is just not the names that can be accessed in the absence
of usage on a schema, it is *the content of the relations*. It is
obvious to anyone who has ever looked in pg_* that metadata is not
hidden by any amount of permission twiddling with grant and revoke. This
isn't great from a security standpoint, but at least it's documented and
expected, so one can design around it.

However, the usage on schema privilege has undocumented, unexpected
behavior. One would think from the documentation and from
experimentation that one can not exercise any privileges on an object
(excepting what can be done through the system catalogs) without having
usage on the schema that contains it. However, this is not always the
case!

If you look at my previous posts, I have repeatedly demonstrated ways to
access objects (not just the names or metadata, but the _full_
_contents_) contained in a schema to which one does not have the 'usage'
privilege. The developers must consider this a "feature", because none
have acknowledged it as a security bug. This being the case, it is
important that people be advised that the schema usage privilege does
not always control access to contained objects, and that the ways which
it can be bypassed are perhaps not numerous, but definitely subtle, and
thus likely to escape security audits and later be discovered by an
attacker. It should be known that the PostgreSQL developers have
recently added a function lastval() which newly exposes such a way to
bypass the check, and that this has not been officially acknowledged as
a security flaw.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] pg_regress breaks on msys

Tom Lane wrote:

I wrote:

Ah-hah, so apparently "make install DESTDIR=foo" somehow inserts DESTDIR
after that instead of before it? What we need is a way to determine the
paths that make install used ...

AFAICS, the makefiles are just blindly concatenating DESTDIR with bindir
etc, for instance this is how initdb/Makefile installs initdb:

$(INSTALL_PROGRAM) initdb$(X) '$(DESTDIR)$(bindir)/initdb$(X)'

The evidence at hand says that this should produce exactly the same path
string as pg_regress is then using to call initdb. So the question in
my mind now is how come the "make install" step isn't failing. For that
matter, this same path-construction technique was used by the
shellscript... so how come it worked before?

It would be simple enough to make pg_regress strip off a drive letter
from the path strings it receives from the Makefile, but I'm not seeing
a principled way to discover that the "/msys/1.0/" part has to go. How
are the makefiles managing to generate a different value of $(bindir) at
install time than what was passed into pg_regress at build time?

regards, tom lane

I think we'll need to have the makefile tell us what it thinks the cwd
is, so if it's a virtual path we'll be able to use that.

Compare the install log on the 8.1 branch (from our new buildfarm member
bandicoot) here
http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=bandicoot&dt=2006-07-19%2009%3A52%3A28&stg=check

with what seahorse is showing. Note that the install does not involve
windows paths at all - just Msys virtual paths. But we do need to use
Windows paths for the data files.

cheers

andrew

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] plPHP and plRuby

Ron Mayer <[EMAIL PROTECTED]> writes:
> Peter Eisentraut wrote:
>> Right.  When was the last time any user looked under src/pl in the first 
>> place?  Or even under src?  If you're looking for pljava, it's the 
>> first hit in Google.

> The difference is that I will have reasonable confidence that
> the README.TXT under "src/pl" will give instructions that match
> the version of PostgreSQL that I have.   I assume that README
> will call out the version of PL/R or PL/Ruby that I want that
> was tested with the release of PostgreSQL I have.

On what do you base that assumption?  A README file laying about in an
otherwise unused part of the source tree is the very definition of "out
of sight, out of mind".  I can pretty much guarantee you that it will
NOT get updated, especially not during minor releases.  Even if it is up
to date at the instant we put out a release, it'll be obsolete as soon
as the external project makes an update release.  ISTM links like this
are far better kept on project websites ...

> I would imagine with this README.TXT proposal, it's the responsibility
> of the PL/XXX developer to port their PL to PostgreSQL during the Beta,
> and if the did and tested it, the release will point to the version
> of the PL supported by the PL maintainer for that version.

And if they didn't?  I was just noticing that the current release of
plruby contains installation instructions that appear to date to 7.3.
If he can't be bothered to update his own docs, what are the odds that
he'll submit timely updates for a README in the main source tree?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Index corruption

2006-07-19 Thread Marc Munro

For the record, here are the results of our (ongoing) inevstigation into
the index/heap corruption problems I reported a couple of weeks ago.

We were able to trigger the problem with kernels 2.6.16, 2.6.17 and
2.6.18.rc1, with 2.6.16 seeming to be the most flaky.

By replacing the NFS-mounted netapp with a fibre-channel SAN, we have
eliminated the problem on all kernels.

From this, it would seem to be an NFS bug introduced post 2.6.14, though
we cannot rule out a postgres bug exposed by unusual timing issues.

Our starting systems are: 

Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875
Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux (and others)
kernel boot option: elevator=deadline
16 Gigs of RAM
postgresql-8.0.8-1PGDG
Bonded e1000/tg3 NICs with 8192 MTU.
Slony 1.1.5

NetApp FAS270 OnTap 7.0.3
Mounted with the NFS options
rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac
Jumbo frames 8192 MTU.

All postgres data and logs are stored on the netapp.

All tests results were reproduced with postgres 8.0.8

__
Marc

On Fri, 2006-06-30 at 23:20 -0400, Tom Lane wrote:
> Marc Munro <[EMAIL PROTECTED]> writes:
> > We tried all of these suggestions and still get the problem.  Nothing
> > interesting in the log file so I guess the Asserts did not fire.
> 
> Not surprising, it was a long shot that any of those things were really
> broken.  But worth testing.
> 
> > We are going to try experimenting with different kernels now.  Unless
> > anyone has any other suggestions.
> 
> Right at the moment I have no better ideas :-(
> 
>   regards, tom lane
> 

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

Re: [HACKERS] pg_regress breaks on msys

I wrote:
> Ah-hah, so apparently "make install DESTDIR=foo" somehow inserts DESTDIR
> after that instead of before it?  What we need is a way to determine the
> paths that make install used ...

AFAICS, the makefiles are just blindly concatenating DESTDIR with bindir
etc, for instance this is how initdb/Makefile installs initdb:

$(INSTALL_PROGRAM) initdb$(X) '$(DESTDIR)$(bindir)/initdb$(X)'

The evidence at hand says that this should produce exactly the same path
string as pg_regress is then using to call initdb.  So the question in
my mind now is how come the "make install" step isn't failing.  For that
matter, this same path-construction technique was used by the
shellscript... so how come it worked before?

It would be simple enough to make pg_regress strip off a drive letter
from the path strings it receives from the Makefile, but I'm not seeing
a principled way to discover that the "/msys/1.0/" part has to go.  How
are the makefiles managing to generate a different value of $(bindir) at
install time than what was passed into pg_regress at build time?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] plPHP and plRuby

2006-07-19 Thread Ron Mayer


Peter Eisentraut wrote:

Hannu Krosing wrote:

So we would have
src/pl/pljava/README.TXT

and anybody looking for pl-s would find the info in a logical place


Right.  When was the last time any user looked under src/pl in the first 
place?  Or even under src?  If you're looking for pljava, it's the 
first hit in Google.


The difference is that I will have reasonable confidence that
the README.TXT under "src/pl" will give instructions that match
the version of PostgreSQL that I have.   I assume that README
will call out the version of PL/R or PL/Ruby that I want that
was tested with the release of PostgreSQL I have.

The first hit on Google will probably give me the most
recently blogged about version; which does nothing to help
me find what I need.

The organization of the source code is controlled by exactly two 
factors:

2. convenience of development


I thought "convenience of development" included the addressing
the problem that PLs are annoyingly deeply tied to specific
versions of Core.

I would imagine with this README.TXT proposal, it's the responsibility
of the PL/XXX developer to port their PL to PostgreSQL during the Beta,
and if the did and tested it, the release will point to the version
of the PL supported by the PL maintainer for that version.   If they
don't do this testing during the beta, the README.TXT may merely say
the "PL/Haskell team did not complete testing during the 8.2 beta; so
good luck".

This aids to the convenience of development of PostgreSQL and the PLs
because it defines the process and responsibility for integration
testing the PLs with the Core releases; and puts some pressure to
synchronize the releases.


Anything else is between you and your packager.

And if that didn't convince you, I still got PL/sh in the wait ...


With which versions of PostgreSQL is this version of PL/sh supported?
I see that PL/sh on http://pgfoundry.org/projects/plsh/ is version 1.1?
Does that mean it goes with PostgreSQL 1.1?   The Projects page
for PL/SH (http://plsh.projects.postgresql.org/) suggests it was
last modified in May 2005 - does that mean I need the May 2005 backend
of PostgreSQL to compile it?  Oh.  The download page says older
releases are also supported.  Does that include 7.1?

Putting this info in the README.TXT is one way to help users
know what's supported.   If I saw a README.TXT for pl/sh I'd
have some confidence I'd be directly pointed to the version I need.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] pg_regress breaks on msys

2006-07-19 Thread Bort, Paul

> >Andrew Dunstan <[EMAIL PROTECTED]> writes:
> >  
> 
> Well, we have a result, courtesy of a special run from Stefan: 
> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=seahorse&dt=
> 2006-07-19%2017:52:41 
> has:
> 
> Command was: 
> ""C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test
> /regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbu
> ild/HEAD/inst/bin/initdb" -D 
> "C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/
> regress/./tmp_check/data" -L 
> "C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/
> regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbui
> ld/HEAD/inst/share/postgresql" --noclean  --no-locale 
> >"./log/initdb.log" 2>&1"
> 
> 
> The second "C:/msys/1.0/" should not be in the path to initdb.
> 

Andrew's on to something, I think. Colons are verboten anywhere in a
filename except position 2, right after a drive letter. The path to
postgresql later in the command will also have problems.

Regards,
Paul Bort
 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_regress breaks on msys

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Command was: 
> ""C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/bin/initdb"
>  -D 
> "C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/data"
>  -L 
> "C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql"
>  --noclean  --no-locale >"./log/initdb.log" 2>&1"

> The second "C:/msys/1.0/" should not be in the path to initdb.

Ah-hah, so apparently "make install DESTDIR=foo" somehow inserts DESTDIR
after that instead of before it?  What we need is a way to determine the
paths that make install used ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] pg_regress breaks on msys

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> Hmm, that suggests that our code works *only* if there's white space in
> >> all the paths !?  Seems unlikely that this description is fully correct,
> >> or we'd have had problems before.
> 
> > It is saying _all_ these have to be true, and we already quote
> > executables, and the string, so we always have more than two quotes:
> 
> Well, the description is about as clear as mud, because it's not saying
> which two quote characters it's talking about.  I read it as talking
> about the two quote characters around any one word/pathname.  If you
> think it's talking about the two quote characters we put around the
> whole command (the SYSTEMQUOTE dodge), then we're certainly going to
> fail the "no special characters" test, because all these commands use
> I/O redirection symbols.

Right, the top says:

 *  1. If all of the following conditions are met, then quote characters
 *  on the _command_ _line_ are preserved:

It is talking about the entire command string, because this is system()
and there is no distinction between commands and arguments --- it is one
big string.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)

2006-07-19 Thread korry





Thanks for the quick feedback.

  
1) I think the most straightforward way to load an instrumentation 
plugin is to define a new custom GUC variable (using the 
custom_variable_classes mechanism).

  
  
This seems a bit messy and special-purpose.  

Agreed, I'm not crazy about using a custom_variable_class variable
either.

  I see no good reason to tie
it to plpgsql; we'll just need another one for every other language.
  

Hmmm... but the plugins themselves would be language-specific.  I can't
imagine that a plugin (say a profiler) for PL/python would work for
PL/pgSQL.  It seems to me that, even if we come up with a common
mechanism, we'll still need a separate GUC variable *name* for each
PL.  Or am I not understanding something?  Can you post an example of
what you are thinking (what would such a GUC variable look like)?


  IMHO what we want is something with similar properties to preload_libraries,
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application.  A GUC variable can be set for an app
without much cooperation from the app.)
  

Agreed. 

  When the plugin's shared library gets loaded, one way or the other,
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin).
  

But there's a timing issue there.  If you ask the plugin to call a
call-handler function, then you can't load the plugin at backend
startup because the PL/pgSQL call-handler isn't loaded until it's
required.  Since both the plugin and the call-handler are dynamically
loaded, I think one of them has to load the other.  We already have a
mechanism for loading call-handlers on demand - it seems kind of messy
to introduce another mechanism for loading plugins (that in turn load
the call-handlers).

The PL/pgSQL call-handler has a convenient initialization function that
could read the GUC variable and load the referenced plugin (that's what
I'm doing right now).

What I'm thinking is that the plpgsql_init() function would look
something like this (my changes in red);

PLpgSQL_plugin   pluginHooks;
typedef void (*plugin_loader_func)(PLpgSQL_plugin *hooks);

void
plpgsql_init(void)
{
    static char * pluginName;
    plugin_load_func   plugin_loader();

    /* Do initialization only once */
    if (!plpgsql_firstcall)
        return;

    plpgsql_HashTableInit();
    RegisterXactCallback(plpgsql_xact_cb, NULL);
    plpgsql_firstcall = false;

    /* Load any instrumentation plugins */
    DefineCustomStringVariable( "plpgsql.plugin", 
                                "Name of instrumentation plugin to use
when PL/pgSQL function is invoked",
                                NULL,
                                &pluginName,
                                PGC_USERSET,
                                NULL,
                                NULL );

    EmitWarningsOnPlaceholders("plpgsql");

    if (pluginName )
    {
    plugin_loader = (plugin_loader_func
*)load_external_function(pluginName, "plugin_loader", false, NULL );

    if (plugin_loader)
    (*plugin_loader)(&pluginHooks);
    }
}    

(Ignore the custom variable stuff for now)

Each plugin would export a plugin_loader() function - that function,
given a pointer to a PLpgSQL_plugin structure, would fill in that
structure with the required function pointers. 

  
One issue that needs to be thought about with either this proposal or
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.

Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql.
  

You're right, privileges are an issue.  Is it safe enough if we force
all plugins to reside in $libdir?  Each plugin could enforce additional
security as needed that way, but you'd have to hold enough privileges
to get your plugin into $libdir to begin with so you can't write your
own nasty plugin to gain more privileges than you ought to have.

  
We should also think about a deregistration function.  This would allow
you to turn debugging on and off within an interactive session.  The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ...
  

Ok.

  
3) Any comments on the PLpgSQL_plugin structure?  Should

Re: [HACKERS] lastval exposes information that currval does not

Phil Frost wrote:
> On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote:
> > Phil Frost wrote:
> > > On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
> > > > 
> > > > Updated text:
> > > > 
> > > >For schemas, allows access to objects contained in the specified
> > > >schema (assuming that the objects' own privilege requirements are
> > > >also met).  Essentially this allows the grantee to look 
> > > > up
> > > >objects within the schema.  Without this permission, it is still
> > > >possible to see the object names by querying the system tables, 
> > > > but
> > > >they cannot be accessed via SQL.
> > > 
> > > No, this still misses the point entirely. See all my examples in this
> > > thread for ways I have accessed objects without usage to their schema
> > > with SQL.
> > 
> > OK, well we are not putting a huge paragraph in there.  Please suggest
> > updated text.
> 
> Well, if you won't explain the whole situation, nor change it, then all
> you can really say is it doesn't really work always. How about this:
> 
> For schemas, allows access to objects contained in the specified
> schema. Note that the converse is not true in many cases: revoking
> usage on a schema is not sufficient to prevent access in all cases.
> There is precedent for new ways to bypass this check being added in
> future releases. It would be unwise to give this privilege much
> security value.

Updated text:

   For schemas, allows access to objects contained in the specified
   schema (assuming that the objects' own privilege requirements are
   also met).  Essentially this allows the grantee to look up
   objects within the schema.  Without this permission, it is still
   possible to see the object names, e.g. by querying the system tables,
   so this is not a completely secure way to prevent object access.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] pg_regress breaks on msys

After looking at the presumably-working uses of system() in initdb and
pg_ctl, I have a theory about the pg_regress problem --- could it be
that Windows system() requires a space between I/O redirection symbols
and pathnames?  I see that the pre-existing code consistently puts one,
except in cases like "2>&1":

snprintf(cmd, MAXPGPATH, "%s\"%s\" %s%s < \"%s\" >> \"%s\" 2>&1 &%s",
 SYSTEMQUOTE, postgres_path, pgdata_opt, post_opts,
 DEVNULL, log_file, SYSTEMQUOTE);

but there's nothing in our docs saying this is necessary ...

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_regress breaks on msys

Tom Lane wrote:

Andrew Dunstan <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

This error message seems pretty thoroughly unhelpful though. Any ideas
what it's unhappy about?

I think we need to change the pg_regress error messages so that it
includes the command string that failed, at least for now.

Done, but I bet it doesn't tell us anything we don't know already.

Well, we have a result, courtesy of a special run from Stefan:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=seahorse&dt=2006-07-19%2017:52:41
has:

Command was:
""C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/bin/initdb"
-D "C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/data" -L
"C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/inst/share/postgresql"
--noclean --no-locale >"./log/initdb.log" 2>&1"

The second "C:/msys/1.0/" should not be in the path to initdb.

Not sure how to fix.

cheers

andrew

It will be either quoting problem or a vitual path problem, I am pretty
sure. The old shell script ran in a bourne-shell-like manner. But
calling system() from a C program will call the Windows command shell,
where the quoting rules are quite different.

In src/include/port.h we have

/*
* Win32 needs double quotes at the beginning and end of system()
* strings. If not, it gets confused with multiple quoted strings.
* It also requires double-quotes around the executable name and
* any files used for redirection. Other args can use single-quotes.
*
* See the "Notes" section about quotes at:
* http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
*/

The referenced link seems to be dead :-( but AFAICS the pg_regress code
is following the stated rules. Also, how is it getting past the "make
install" step which is quoting things just the same? Puzzling.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_regress breaks on msys

Bruce Momjian <[EMAIL PROTECTED]> writes:
>> Hmm, that suggests that our code works *only* if there's white space in
>> all the paths !?  Seems unlikely that this description is fully correct,
>> or we'd have had problems before.

> It is saying _all_ these have to be true, and we already quote
> executables, and the string, so we always have more than two quotes:

Well, the description is about as clear as mud, because it's not saying
which two quote characters it's talking about.  I read it as talking
about the two quote characters around any one word/pathname.  If you
think it's talking about the two quote characters we put around the
whole command (the SYSTEMQUOTE dodge), then we're certainly going to
fail the "no special characters" test, because all these commands use
I/O redirection symbols.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Possible Typecasting Bug with coalesce()

2006-07-19 Thread MotherMGA

You are correct, Andreas.

=> select now()>'Jul 14 2006 9:16:47AM';
 ?column?
--
 t
(1 row)

=> select now() > CASE WHEN 'Jul 14 2006 9:16:47AM' IS NOT NULL THEN
'Jul 14 2006 9:16:47AM' END;
 ?column?
--
 f
(1 row)

I've also found that there must be more than one typecasting function
being used because the result can be different depending on the format
of the timestamp string:

=> select now()>coalesce('Jul 14 2006 9:16:47AM');
 ?column?
--
 f
(1 row)

=> select now()>coalesce('2006-07-14 9:16:47');
 ?column?
--
 t
(1 row)


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] plPHP and plRuby

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> And if that didn't convince you, I still got PL/sh in the wait ...

It seems like there may be enough interest in PL/Ruby to justify
including it in our distro, but after taking a look at the package
I can see a couple of pretty serious objections:

1. Wrong license.  Unless the author can be persuaded to relicense as
straight BSD, this discussion is a waste of time.

2. Coding style.  The man does not believe in comments; do we really
think anyone else is going to be able to maintain his work?

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_regress breaks on msys

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >  *  From http://www.computerhope.com/cmd.htm:
> >  *
> >  *  1. If all of the following conditions are met, then quote characters
> >  *  on the command line are preserved:
> >  *
> >  *   - no /S switch
> >  *   - exactly two quote characters
> >  *   - no special characters between the two quote characters, where special
> >  * is one of: &<>()@^|
> >  *   - there are one or more whitespace characters between the the two quote
> >  * characters
> >  *   - the string between the two quote characters is the name of an
> >  * executable file.
> 
> Hmm, that suggests that our code works *only* if there's white space in
> all the paths !?  Seems unlikely that this description is fully correct,
> or we'd have had problems before.

It is saying _all_ these have to be true, and we already quote
executables, and the string, so we always have more than two quotes:

 *  Win32 needs double quotes at the beginning and end of system()
 *  strings.  If not, it gets confused with multiple quoted strings.
 *  It also requires double-quotes around the executable name and
 *  any files used for redirection.  Other args can use single-quotes.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] plPHP and plRuby

Hannu Krosing wrote:
> So we would have
>
> src/pl/plphp/README.TXT
> src/pl/pljava/README.TXT
> src/pl/plj/README.TXT
>
> and anybody looking for pl-s would find the info in a logical place

Right.  When was the last time any user looked under src/pl in the first 
place?  Or even under src?  If you're looking for pljava, it's the 
first hit in Google.

I think people need to relax more.  We are not making statements about 
language preferences -- making that claim is just paranoia.  We are not 
missing the enterprise train, and there might be just as many people 
moving from PHP to Java, or we might just be making this up because no 
one can count that anyway.  And we are not going to educate any Rail 
users, because people don't like to be lectured to if they didn't ask 
for it.

The organization of the source code is controlled by exactly two 
factors:

1. history
2. convenience of development

Anything else is between you and your packager.

And if that didn't convince you, I still got PL/sh in the wait ...

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] plPHP and plRuby

Marc G. Fournier wrote:
> > src/pl/plphp/README.TXT
> > src/pl/pljava/README.TXT
> > src/pl/plj/README.TXT
> >
> > and anybody looking for pl-s would find the info in a logical place
>
> *That* idea I like ...

Why don't we just reorganize our tree like that:

everything/databases/postgresql/src/...
everything/databases/mysql/README.txt
everything/kernels/freebsd/README.txt
everything/kernels/linux/README.txt
...

That will make it much easier for people to set up their systems.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Loading the PL/pgSQL debugger (and other plugins)

korry <[EMAIL PROTECTED]> writes:
> I'm working on a patch that implements the PL/pgSQL instrumentation 
> stuff (i.e. the PL/pgSQL debugger)  that I discussed at the Anniversary 
> Summit and I need some opinions (this seems like a good place to look 
> for opinions :-)

Opinions R US ;-)

> 1) I think the most straightforward way to load an instrumentation 
> plugin is to define a new custom GUC variable (using the 
> custom_variable_classes mechanism).

This seems a bit messy and special-purpose.  I see no good reason to tie
it to plpgsql; we'll just need another one for every other language.
IMHO what we want is something with similar properties to preload_libraries,
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application.  A GUC variable can be set for an app
without much cooperation from the app.)

When the plugin's shared library gets loaded, one way or the other,
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin).

One issue that needs to be thought about with either this proposal or
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.

Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql.

We should also think about a deregistration function.  This would allow
you to turn debugging on and off within an interactive session.  The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ...

> 3) Any comments on the PLpgSQL_plugin structure?  Should it include (as 
> it's first member) a structure version number so we can add to/change 
> the structure as needed?

Given our current plans for enforcing recompiles at major version
changes (via magic-block checking), I'm not sure I see a need for this.

> 4) Do we need to support multiple active plugins?

Probably, but let's fix the API to hide this, so we don't have to commit
now.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Progress bar updates

2006-07-19 Thread Andrew Hammond

Neil Conway wrote:
> > I would suggest starting with utility functions like index builds or COPY
> > which would have to be specially handled anyways. Handling all optimizable
> > queries in a single generic implementation seems like something to tackle 
> > only
> > once the basic infrastructure is there and working for simple cases.
> >
> > Of course the estimates would be not much better than guesses.
>
> Estimating query progress for DDL should be reasonably doable, but I
> think it would require some hard thought to get even somewhat accurate
> estimates for SELECT queries -- and I'm not sure there's much point
> doing this if we don't at least have an idea how we might implement
> reasonably accurate progress reporting for every kind of query.

We already have EXPLAIN ANALYZE. Perhaps the right way to do this is
something that provides similar output. I could see something that
looks like EXPLAIN for the parts that have not yet executed, something
reasonable to show progress of the currently active part of the plan
(current time, rows, loops), and EXPLAIN ANALYZE output for the parts
which have been completed.

I can see how this might lead to dynamically re-planning queries. Going
backwards, perhaps there's something related to progress monitoring
that could be taken from the TelegraphCQ work?

Drew

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_regress breaks on msys

Bruce Momjian <[EMAIL PROTECTED]> writes:
>  *  From http://www.computerhope.com/cmd.htm:
>  *
>  *  1. If all of the following conditions are met, then quote characters
>  *  on the command line are preserved:
>  *
>  *   - no /S switch
>  *   - exactly two quote characters
>  *   - no special characters between the two quote characters, where special
>  * is one of: &<>()@^|
>  *   - there are one or more whitespace characters between the the two quote
>  * characters
>  *   - the string between the two quote characters is the name of an
>  * executable file.

Hmm, that suggests that our code works *only* if there's white space in
all the paths !?  Seems unlikely that this description is fully correct,
or we'd have had problems before.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well

Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> s/possible criticism/deal-breaker/ ... you can't possibly think that the
>> above would be acceptable.  It'd be worse than "won't be undoable"; it'd
>> probably corrupt your database.

> I'm not sure I understand why. Or are you just referring to the snapshot bugs
> in cluster?

The ROLLBACK would undo the catalog updates made by the command, but not
its non-transactional changes.  Possibly in some cases there would be
no resulting inconsistency, but in general it would leave inconsistent
state.  In most of our usages of PreventTransactionChain, the point is
that a rollback occuring after the command thinks it's completed would
be unsafe.

> I do have an alternative idea: Instead of having psql parse the SQL commands
> to try to guess which commands are non-transactional, have psql simply try the
> command, and check the error code.

I don't think it is really all that hard to have psql parse the commands
far enough to determine if they're transactional or not.  If you can't
tell by easy visual inspection which kind of command it is, then we've
misdesigned the command language and should change it: it'll be
confusing for people as well as programs.

In the case of the online-index-creation command, this may require
putting the critical keyword up front rather than burying it somewhere
in the command string, but I don't see a problem with that.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] pg_regress breaks on msys

Tom Lane wrote:
> In src/include/port.h we have
> 
> /*
>  *Win32 needs double quotes at the beginning and end of system()
>  *strings.  If not, it gets confused with multiple quoted strings.
>  *It also requires double-quotes around the executable name and
>  *any files used for redirection.  Other args can use single-quotes.
>  *
>  *See the "Notes" section about quotes at:
>  *http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
>  */
> 
> The referenced link seems to be dead :-( but AFAICS the pg_regress code
> is following the stated rules.  Also, how is it getting past the "make
> install" step which is quoting things just the same?  Puzzling.

I found the description somewhere else and copied it into our header
file:

 *  From http://www.computerhope.com/cmd.htm:
 *
 *  1. If all of the following conditions are met, then quote characters
 *  on the command line are preserved:
 *
 *   - no /S switch
 *   - exactly two quote characters
 *   - no special characters between the two quote characters, where special
 * is one of: &<>()@^|
 *   - there are one or more whitespace characters between the the two quote
 * characters
 *   - the string between the two quote characters is the name of an
 * executable file.
 *
 *   2. Otherwise, old behavior is to see if the first character is a quote
 *   character and if so, strip the leading character and remove the last
 *   quote character on the command line, preserving any text after the last
 *   quote character.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well

2006-07-19 Thread Greg Stark

Tom Lane <[EMAIL PROTECTED]> writes:

> Gregory Stark <[EMAIL PROTECTED]> writes:
> > One possible criticism is that a user that manually does BEGIN; CLUSTER
> > DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable.
> 
> s/possible criticism/deal-breaker/ ... you can't possibly think that the
> above would be acceptable.  It'd be worse than "won't be undoable"; it'd
> probably corrupt your database.

I'm not sure I understand why. Or are you just referring to the snapshot bugs
in cluster?

I'm imagining what would happen is that cluster would take the liberty of
committing the transaction begun by the BEGIN since it hadn't been used yet
anyways. Then it would leave you with a fresh transaction when it was done so
the rollback would be a noop as it just rolled back that empty transaction.

I do have an alternative idea: Instead of having psql parse the SQL commands
to try to guess which commands are non-transactional, have psql simply try the
command, and check the error code. If a command fails immediately after the
BEGIN implicitly inserted when autocommit=false and it fails with a specific
error code set aside for this purpose, then abort the transaction and
reattempt it outside a transaction.

If that error comes back during a user-initiated transaction or with
autocommit=true then psql wouldn't do anything special.

I'm still a bit bothered by all this since I think it would still make it hard
to use non-transactional commands from other clients. Clients like DBI and
JDBC generally assume you're *always* in a transaction so one imagines they do
something similar to psql with inserting implicit BEGINs everywhere.

The "real" solution is probably to go back to autocommit=false semantics on
the server and have psql implement autocommit mode simply by inserting
"commit" all the time. But I have a feeling people are so burned by the last
change in this area that bringing it up again isn't going to win me any
friends :)

-- 
greg

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] How to refer to standard functions?

2006-07-19 Thread Greg Stark

Tom Lane <[EMAIL PROTECTED]> writes:

> Gregory Stark <[EMAIL PROTECTED]> writes:
> > Is there any way to refer to standard functions when defining new functions?
> 
> Sure, but they're language INTERNAL, not C.

ah, thanks

-- 
greg


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] pg_regress breaks on msys

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> This error message seems pretty thoroughly unhelpful though.  Any ideas
>> what it's unhappy about?

> I think we need to change the pg_regress error messages so that it 
> includes the command string that failed, at least for now.

Done, but I bet it doesn't tell us anything we don't know already.

> It will be either quoting problem or a vitual path problem, I am pretty 
> sure.  The old shell script ran in a bourne-shell-like manner. But 
> calling system() from a C program will call the Windows command shell, 
> where the quoting rules are quite different.

In src/include/port.h we have

/*
 *  Win32 needs double quotes at the beginning and end of system()
 *  strings.  If not, it gets confused with multiple quoted strings.
 *  It also requires double-quotes around the executable name and
 *  any files used for redirection.  Other args can use single-quotes.
 *
 *  See the "Notes" section about quotes at:
 *  http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
 */

The referenced link seems to be dead :-( but AFAICS the pg_regress code
is following the stated rules.  Also, how is it getting past the "make
install" step which is quoting things just the same?  Puzzling.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[HACKERS] Windows 2000 Support

Hi,

I just finished setting up a new buildfarm member (Bandicoot) running
Windows 2000 Pro. Aside from the fact that it now fails with the same
cyptic pg_regress error as seen on Snake, it also became apparent that
CVS HEAD won't run properly on an unpatched Windows 2000 (initdb - and
probably pg_ctl - fails when trying to dynamically load advapi32.dll
which is used to shed excess privileges). This was solved by the
installation of service pack 4. Unfortunately I couldn't find a way to
catch the error - it seems to kill the app and throw a messagebox with a
cryptic message.

Given that you have to be clinically insane to run Win2K without
patching it to the hilt I'm not overly concerned by this (and will add
appropriate checks to pgInstaller), but it's probably worth mentioning
that our minimum supported platform is Windows 2000 Pro with Service
Pack 4 from 8.2.

Regards, Dave.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Continuous dataflow streaming

On Mon, Jul 17, 2006 at 09:25:49AM -0700, Josh Berkus wrote:
> Dragan,
> 
> >What are the possibilities (if any) for continuous dataflow streaming with
> >PostgreSQL v.8.1 ? Something like TelegraphCQ project,but it was for
> >v.7.3.Is there any alternatives for the latest version of PostgreSQL ?
> 
> The TelegraphCQ team has stopped public development.  So it's pretty 
> much waiting for someone to take on their code.
> 
> FWIW, the existing version of TCQ never solved the "not crashing" 
> problem, let alone integration with transactional tables.

Also, Neil Conway gave a talk about this at the conference, which
unfortunately I couldn't attend. I talked to him afterwards though, and
he's definately interested in getting streaming support for PostgreSQL
in some fashion.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] pg_regress breaks on msys


Tom Lane wrote:


Andrew Dunstan <[EMAIL PROTECTED]> writes:
 


pg_regress now seems to break on Msys virtual locations:
Example from the buildfarm: 
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snake&dt=2006-07-19%2009:00:00
   




 


== pgsql.4660/src/test/regress/log/initdb.log 
===
The filename, directory name, or volume label syntax is incorrect.
   



 


Surely this was tested when the original was prepared?
   



You can probably blame me instead of Magnus, because I did extensive
fooling with the quoting of the commands issued by pg_regress ... and
no, I don't have msys to test with, that's what the buildfarm is for ;-)
 



Neither do I right now.


This error message seems pretty thoroughly unhelpful though.  Any ideas
what it's unhappy about?
 




I think we need to change the pg_regress error messages so that it 
includes the command string that failed, at least for now.


Then we might know instead of speculating.

It will be either quoting problem or a vitual path problem, I am pretty 
sure.  The old shell script ran in a bourne-shell-like manner. But 
calling system() from a C program will call the Windows command shell, 
where the quoting rules are quite different.


cheers

andrew

---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] Loading the PL/pgSQL debugger (and other plugins)

2006-07-19 Thread korry

I'm working on a patch that implements the PL/pgSQL instrumentation 
stuff (i.e. the PL/pgSQL debugger)  that I discussed at the Anniversary 
Summit and I need some opinions (this seems like a good place to look 
for opinions :-)


A quick review:  the PL/pgSQL debugger is designed as an optional 
"plugin" that loads into the PL/pgSQL interpreter on-demand.  You can 
use the plugin idea to implement other kinds of instrumentation (I 
demo'ed a tracer and a profiler at the conference, along with a 
debugger).  A plugin architecture greatly reduces the (source code) 
footprint that would normally be required to implement a full-featured 
debugger.


A plugin is basically a structure that contains a few function 
pointers.  If those function pointers are NULL, the PL/pgSQL interpreter 
works exactly the way it does today.  If any of those function pointers 
are non-NULL, the PL/pgSQL interpreter calls the target function (which 
points to a chunk of code inside of the plugin) and the plugin does 
whatever it needs to do.


Right now, the plugin structure looks like this:

typedef struct
{
   void (*init)( estate,  func, error_callback, assign_expr, expr );
   void (*func_beg)( PLpgSQL_execstate * estate, PLpgSQL_function * func );
   void (*func_end)( PLpgSQL_execstate * estate, PLpgSQL_function * func );
   void (*stmt_beg)( PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt );
   void (*stmt_end)( PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt );
} PLpgSQL_plugin;

I've truncated the argument list (in this e-mail) for the (*init)() 
function since it's rather long (error_callback and assign_expr are both 
function pointers).


When the PL/pgSQL intrepreter loads the plugin, it calls the 
plugin->init() function.
When the PL/pgSQL intrepreter starts running a new function, it calls 
the plugin->func_beg() function.
When the PL/pgSQL intrepreter completes a function, it calls the 
plugin->func_end() function.
When the PL/pgSQL interpreter is about to execute a line of PL/pgSQL 
code, it calls plugin->stmt_beg()
When the PL/pgSQL interpreter has finished executing a line of PL/pgSQL 
code, it calls plugin->stmt_end()


So here is where I need a few opinions:

1) I think the most straightforward way to load an instrumentation 
plugin is to define a new custom GUC variable (using the 
custom_variable_classes mechanism).  When the PL/pgSQL call-handler 
loads, it can check that config. variable (something like plpgsql.plugin 
= '$libdir/plugin_profiler' or plpgsql.plugin = 
'$libdir/plugin_debugger') and load the plugin if non-NULL.  That seems 
a little obtuse to me since custom variables don't appear in the 
prototype postgresql.conf file.  Would it be better to add a real GUC 
variable instead of a custom variable?


2) Given that plpgsql.plugin points to the name of a shared-object file 
(or DLL or whatever you prefer to call it), we need to find *something* 
inside of the file.  The most obvious choice would be to look for a 
variable (a structure or structure pointer) with a fixed name. That 
would mean, for example, that a plugin would define an externally 
visible PLpgSQL_plugin structure named "plugin_hooks" and the PL/pgSQL 
interpreter would look for that symbol inside of the plugin.  
Alternatively, we could look for a function inside of the plugin 
(something like 'plugin_loader') and then call that function with a 
pointer to a PLpgSQL_plugin structure.  I prefer the function-pointer 
approach since we already have a reliable mechanism in place for finding 
a function inside of a shared-object (the same mechanism works for 
finding a variable instead of a function pointer, but I doubt that that 
has been tested in all platforms).


3) Any comments on the PLpgSQL_plugin structure?  Should it include (as 
it's first member) a structure version number so we can add to/change 
the structure as needed?


4) Do we need to support multiple active plugins?  Would you ever need 
to load the debugger at the same time you've loaded the profiler (no)?  
Would you ever need to load the tracer at the same time you need the 
debugger (probably not)?  If we need to support multiple plugins, should 
be just introduce a meta-plugin that knows how to handle a list of other 
plugins? (Messy, but certainly gets the job done without worrying about 
it right now).


5) I'll also be adding a void pointer to the PLpgSQL_execstate structure 
(think of a PLpgSQL_execstate as a stack frame).  The new pointer is 
reserved for use by the plugin.  It may be handy to add a void pointer 
to each PLpgSQL_stmt as well - is that acceptable? (That would mean an 
extra 4-bytes per-line of compiled PL/pgSQL code, even if you don't have 
a plugin loaded).


Any other comments?  Obviously, you'll have a chance to critique the 
patch when I get it sent in.


Thanks for your help.

 -- Korry



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://arch

Re: [HACKERS] Progress bar updates

2006-07-19 Thread Darcy Buskermolen

On Wednesday 19 July 2006 07:33, Tom Lane wrote:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Tom Lane <[EMAIL PROTECTED]> writes:
> >> In practice, if a query is taking long enough for this feature to be
> >> interesting, making another connection and looking to see what's
> >> happening is not a problem, and it's likely to be the most practical way
> >> anyway for many clients.
> >
> > It would be the most practical way for a DBA to monitor an application.
> > But it's not going to be convenient for clients like pgadmin or psql.
>
> [ shrug... ]  Let me explain it to you this way: a progress counter
> visible through pg_stat_activity is something that might possibly get
> done in time for 8.2.  If you insist on having the other stuff right
> off the bat as well, it won't get done this cycle.

Having the progress, or estimated time of completion in pg_stat_activity 
sounds like a good starting point, the rest of the desired features can be 
bolted on top of this down the road

>
>   regards, tom lane
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq

-- 
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Patch process?


Dave Page wrote:





You should post into -patches@ list, then some core member will
review and apply it. 
   



Not core, a committer. Although some people are both, not all are. If
that makes sense!

 



I have raised this issue before: I don't believe committers are 
identified as such on the web site, and they (we) probably should be.


cheers

andrew

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Patch process?

2006-07-19 Thread Joshua Reich




Tom Lane wrote:

Joshua Reich <[EMAIL PROTECTED]> writes:

Do we have an active maintainer of this code?


It sounds like you've just acquired that position ;-)


More than happy to take the role.


How is it reviewed?


Same as everything else, pretty much: patches go to pgsql-patches and
are (supposed to be) reviewed before being committed.  If it's in the
nature of a new feature rather than a simple bug fix or code cleanup,
you might want to first start a discussion on pgsql-hackers --- if
anyone has a better idea about how to do things, it's better to find it
out before you start coding instead of after you finish.


Ok. I'll keep changes to a minimum; before I add features, I'll discuss 
here.



Because we're relying so heavily on the buildfarm these days, failing
regression tests are quite unacceptable.  Adding an ORDER BY might be
the best solution, or maybe you should just change the expected output
--- do you understand exactly why the results changed?  As for adding
more tests, you can within reason --- don't make the running time
enormous.


Ok. I'll fix the test cases so that everything is hunky dory.

Josh


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] RESET CONNECTION?

Tatsuo Ishii wrote:
> I'm disappointed.
> 
> Can you point out past discussion for this?

Yes:

http://archives.postgresql.org/pgsql-patches/2005-01/msg00029.php

---


> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> 
> > Mario Weilguni wrote:
> > > Will this patch make it into 8.2?
> > > http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php
> > > 
> > > It's a really nice feature, would be extremly useful with tools like 
> > > pgpool.
> > 
> > No, it will not because RESET CONNECTION can mess up interface code that
> > doesn't want the connection reset.  We are not sure how to handle that.
> > 
> > ---
> > 
> > 
> > > 
> > > Am Freitag, 7. Juli 2006 19:13 schrieb Bruce Momjian:
> > > > There are roughly three weeks left until the feature freeze on August 1.
> > > > If people are working on items, they should be announced before August
> > > > 1, and the patches submitted by August 1.  If the patch is large, it
> > > > should be discussed now and an intermediate patch posted to the lists
> > > > soon.
> > > >
> > > > FYI, we don't have many major features ready for 8.2.
> > > >
> > > > --
> > > >   Bruce Momjian   [EMAIL PROTECTED]
> > > >   EnterpriseDBhttp://www.enterprisedb.com
> > > >
> > > >   + If your life is a hard drive, Christ can be your backup. +
> > > >
> > > > ---(end of broadcast)---
> > > > TIP 5: don't forget to increase your free space map settings
> > > 
> > > ---(end of broadcast)---
> > > TIP 1: if posting/reading through Usenet, please send an appropriate
> > >subscribe-nomail command to [EMAIL PROTECTED] so that your
> > >message can get through to the mailing list cleanly
> > 
> > -- 
> >   Bruce Momjian   [EMAIL PROTECTED]
> >   EnterpriseDBhttp://www.enterprisedb.com
> > 
> >   + If your life is a hard drive, Christ can be your backup. +
> > 
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match
> > 

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] RESET CONNECTION?

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Mario Weilguni wrote:
> > > Will this patch make it into 8.2?
> > > http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php
> > > 
> > > It's a really nice feature, would be extremly useful with tools like 
> > > pgpool.
> > 
> > No, it will not because RESET CONNECTION can mess up interface code that
> > doesn't want the connection reset.  We are not sure how to handle that.
> 
> Hmm, what interface code are you talking about?

I believe JDBC, for example, sets things inside the interface that would
be broken by RESET CONNECTION.  Here is a thread about it:

http://archives.postgresql.org/pgsql-patches/2005-01/msg00029.php

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] poor performance with Context Switch Storm at TPC-W.

On Fri, Jul 14, 2006 at 02:58:36PM +0900, Katsuhiko Okano wrote:
> NOT occurrence of CSStorm. The value of WIPS was about 400.
> (but the value of WIPS fell about to 320 at intervals of 4 to 6 minutes.)

If you haven't changed checkpoint timeout, this drop-off every 4-6
minutes indicates that you need to make the bgwriter more aggressive.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Patch process?

2006-07-19 Thread Marko Kreen

On 7/19/06, Dave Page  wrote:

> You should post into -patches@ list, then some core member will
> review and apply it.

Not core, a committer. Although some people are both, not all are. If
that makes sense!

Indeed.

Obviously, non-committers can (and do) review patches.  Just you need
to get the attention of at least one committer to get it into CVS.

--
marko

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Patch process?

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Marko Kreen
> Sent: 19 July 2006 16:13
> To: Joshua Reich
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Patch process?
> 
> On 7/19/06, Joshua Reich <[EMAIL PROTECTED]> wrote:
> > Just a general question - I submitted a patch for 
> contrib/cube (adding a
> > new function & converting everything from V0 to V1), what 
> is the process
> > from here onwards? Do we have an active maintainer of this 
> code? How is
> > it reviewed?
> 
> You should post into -patches@ list, then some core member will
> review and apply it. 

Not core, a committer. Although some people are both, not all are. If
that makes sense!

Regards, Dave.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Patch process?

Joshua Reich <[EMAIL PROTECTED]> writes:
> Just a general question - I submitted a patch for contrib/cube (adding a 
> new function & converting everything from V0 to V1), what is the process 
> from here onwards? Do we have an active maintainer of this code?

It sounds like you've just acquired that position ;-)

> How is it reviewed?

Same as everything else, pretty much: patches go to pgsql-patches and
are (supposed to be) reviewed before being committed.  If it's in the
nature of a new feature rather than a simple bug fix or code cleanup,
you might want to first start a discussion on pgsql-hackers --- if
anyone has a better idea about how to do things, it's better to find it
out before you start coding instead of after you finish.

> I noticed that one of the regression tests now fail, as the original 
> regression test expects a certain ordering from a query that doesn't 
> request ordered results. Should I fix the test case so that ordering 
> will be assured? Should I add test cases for most of the functions that 
> currently aren't being tested?

Because we're relying so heavily on the buildfarm these days, failing
regression tests are quite unacceptable.  Adding an ORDER BY might be
the best solution, or maybe you should just change the expected output
--- do you understand exactly why the results changed?  As for adding
more tests, you can within reason --- don't make the running time
enormous.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Patch process?

2006-07-19 Thread Marko Kreen


On 7/19/06, Joshua Reich <[EMAIL PROTECTED]> wrote:

Just a general question - I submitted a patch for contrib/cube (adding a
new function & converting everything from V0 to V1), what is the process
from here onwards? Do we have an active maintainer of this code? How is
it reviewed?


You should post into -patches@ list, then some core member will
review and apply it.  The review may take some time, you should
not worry about that.  I see that you already got general ACK.


I would like to continue working on the cube stuff, as our company uses
it heavily and has developed a core library of functions that we use
regularly. I would love to be able to add these back to the Postgres
project, but I'm not sure about what the guidelines are for contrib. I
have noticed comments elsewhere about contrib packages being removed
because they didn't follow guidelines, so I don't want to fall foul of
that line, but I am not sure where the line is.


The line mostly depends on quetions 'Is it useful?' and 'Is it active?'

As you mentioned, it is useful and if you will fix problems then
its also active.  Seeing cvs log of the module, you could consider
becoming the maintainer.


I noticed that one of the regression tests now fail, as the original
regression test expects a certain ordering from a query that doesn't
request ordered results. Should I fix the test case so that ordering
will be assured? Should I add test cases for most of the functions that
currently aren't being tested?


Sure, as a maintainer, you should fix all problems :)

--
marko

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] How to refer to standard functions?

Gregory Stark <[EMAIL PROTECTED]> writes:
> Is there any way to refer to standard functions when defining new functions?

Sure, but they're language INTERNAL, not C.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] url for TODO item, is it right?

Marc G. Fournier wrote:
> >> Why can't we just write a script that creates new numbers as needed,
> >> such as msg00163.1.php and msg00163.2.php? As far as I can tell, there
> >> is nothing magical about the naming schema itself that would cause
> >> such URLs to break anything.
> >
> > Agreed.  It is nice to have the emails numbered in arrival order, but
> > changes to old URLs are worse.
> 
> 'k, so is the concensus here that I regenerate everything with the 'broken 
> msg seperator', and then revert to the unbroken one for new stuff?  its no 
> sweat, I just fear this is going to re-crop up sometime in the future if 
> we ever have to regenerate from the mbox files, as well have some in 
> 'broken format' and some in the 'unbroken', but renumbering *then* will 
> still affect everything ...
> 
> Basically, we're just differing the headaches to a later date when we have 
> no choice :(

Well, ideally we could have the new items renumbered on to the end,
starting at 10,000 or something.  That way, the numbers aren't changed,
but the missing items are now visible.  Does the search system assume
that numering is always increasing?

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Statement Queuing

Something that would be extremely useful to add to the first pass of
this would be to have a work_mem limiter. This would allow users to set
work_mem much more aggressively without worrying about pushing the
machine to swapping. That capability alone would make this valuable to a
very large number of our users.

On Mon, Jul 10, 2006 at 11:02:58AM +1200, Mark Kirkwood wrote:
> A while ago in connection with the 8.2 planning [1] there was some
> discussion of resource management and statement queuing [2].
> 
> I am currently looking at implementing a resource management/queuing
> module for Greenplum - initially targeting Bizgres, but I'm thinking it
> could be beneficial for non-Bizgres (i.e Postgresql) users too.
> 
> There has been some discussion on the Bizgres list already [3] together
> with some WIP code [4] and a review [5].
> 
> the code is a little rough - representing my getting to grips with the
> standard lock mechanism in order to produce enough of a prototype to
> study the issues. In that light I would very much appreciate comments
> concerning the design itself and also feedback for the questions posted
> in the review [4] - either here, the Bizgres-general list or both.
> 
> Here is a lightning overview of this whole resource queuing/scheduling
> business to hopefully put it in context (very short version of [3]):
> 
> For certain workloads (particularly DSS and reporting), the usual
> controls (max_connections or a connection pool, work_mem etc) are not
> really enough to stop the situation where several simultaneously
> executing expensive queries temporarily cripple a system. This is
> particularly the case where user specified queries are permitted. What
> is needed is some way to throttle or queue these queries in some finer
> manner - such as (simple case) restricting the number of simultaneously
> executing queries, or restricting the total cost of all simultaneously
> executing queries (others are obviously possible, these are just the
> simplest).
> 
> To make this work a new object - a resource queue is proposed, which
> holds limits and current counters for resources, plus a new sort of
> lock, something like a standard one, but instead of deterministic
> conflict rules based on lockmethod, a check on the counter/total for the
> relevant resource is performed instead.
> 
> best wishes
> 
> Mark
> 
> [1] http://archives.postgresql.org/pgsql-hackers/2006-03/msg01122.php
> [2] http://archives.postgresql.org/pgsql-hackers/2006-03/msg00821.php
> [3] http://pgfoundry.org/pipermail/bizgres-general/2006-June/000492.html
> [4]
> http://homepages.paradise.net.nz/markir/download/bizgres/bizgres-resschedular-06-29.patch
> [5]
> http://lists.pgfoundry.org/pipermail/bizgres-general/2006-July/000521.html
> 
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>   choose an index scan if your joining column's datatypes do not
>   match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] password is no required, authentication is overridden

 

> -Original Message-
> From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
> Sent: 19 July 2006 15:20
> To: Dave Page
> Cc: Hiroshi Saito; Thomas Bley; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] password is no required, 
> authentication is overridden
> 
> >From: http://www.pgadmin.org/docs/1.4/connect.html
> >
> >If you select "store password", pgAdmin stores passwords you enter in
> >the ~/.pgpass file under *nix or 
> %APPDATA%\postgresql\pgpass.conf under
> >Win32 for later reuse. For details, see pgpass 
> documentation. It will be
> >used for all libpq based tools. If you want the password removed, you
> >can select the server's properties and uncheck the selection 
> any time.
> >
> >  
> >
> 
> OK, although I am not sure I think that is sensible - it is at least 
> documented. Does the dialog box also carry similar info?

It has a Help button that takes you straight to that doc page.

> >We have no sensible way of determining whether or not the 
> libpq we are
> >running with supports PGPASSFILE.
> >
> >
> >  
> >
> 
> Well, this answer is better. The lack of an API to tell you 
> the library 
> version is possibly worrying, though.

Indeed. The issue has been raised a few times in the past but for
reasons I forget, such an API has never been added :-(

I'd be happy with:

int PQmajorVer()
int PQminorVer()

Or something similar. It would also be handy to have something like
PQhasKerberos()...

Regards, Dave.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_regress breaks on msys

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> pg_regress now seems to break on Msys virtual locations:
> Example from the buildfarm: 
> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snake&dt=2006-07-19%2009:00:00

> == pgsql.4660/src/test/regress/log/initdb.log 
> ===
> The filename, directory name, or volume label syntax is incorrect.

> Surely this was tested when the original was prepared?

You can probably blame me instead of Magnus, because I did extensive
fooling with the quoting of the commands issued by pg_regress ... and
no, I don't have msys to test with, that's what the buildfarm is for ;-)

This error message seems pretty thoroughly unhelpful though.  Any ideas
what it's unhappy about?

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Patch process?

2006-07-19 Thread Joshua Reich

Just a general question - I submitted a patch for contrib/cube (adding a 
new function & converting everything from V0 to V1), what is the process 
from here onwards? Do we have an active maintainer of this code? How is 
it reviewed?


I would like to continue working on the cube stuff, as our company uses 
it heavily and has developed a core library of functions that we use 
regularly. I would love to be able to add these back to the Postgres 
project, but I'm not sure about what the guidelines are for contrib. I 
have noticed comments elsewhere about contrib packages being removed 
because they didn't follow guidelines, so I don't want to fall foul of 
that line, but I am not sure where the line is.


I noticed that one of the regression tests now fail, as the original 
regression test expects a certain ordering from a query that doesn't 
request ordered results. Should I fix the test case so that ordering 
will be assured? Should I add test cases for most of the functions that 
currently aren't being tested?


Thanks,

Josh Reich

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well

Gregory Stark <[EMAIL PROTECTED]> writes:
> One possible criticism is that a user that manually does BEGIN; CLUSTER
> DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable.

s/possible criticism/deal-breaker/ ... you can't possibly think that the
above would be acceptable.  It'd be worse than "won't be undoable"; it'd
probably corrupt your database.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Progress bar updates

Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> In practice, if a query is taking long enough for this feature to be
>> interesting, making another connection and looking to see what's happening
>> is not a problem, and it's likely to be the most practical way anyway for
>> many clients.

> It would be the most practical way for a DBA to monitor an application. But
> it's not going to be convenient for clients like pgadmin or psql.

[ shrug... ]  Let me explain it to you this way: a progress counter
visible through pg_stat_activity is something that might possibly get
done in time for 8.2.  If you insist on having the other stuff right
off the bat as well, it won't get done this cycle.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] pgxs problem

Gregory Stark <[EMAIL PROTECTED]> writes:
> I've tracked down my problem with pgxs to Makefile.global in lib/pgxs/src.
> These lines seem to be the culprits:

> bindir := $(shell pg_config --bindir)
> datadir := $(shell pg_config --sharedir)
> sysconfdir := $(shell pg_config --sysconfdir)
> libdir := $(shell pg_config --libdir)
> pkglibdir := $(shell pg_config --pkglibdir)
> includedir := $(shell pg_config --includedir)
> pkgincludedir := $(shell pg_config --pkgincludedir)
> mandir := $(shell pg_config --mandir)
> docdir := $(shell pg_config --docdir)
> localedir := $(shell pg_config --localedir)

> I think it should be running $(pkglibdir)/bin/pg_config

Your reasoning is circular.  How are we to find out pkglibdir, if not
by asking pg_config?  (It's the wrong path anyway, since pkglibdir
isn't where pg_config lives...)

The documented behavior is that pgxs invokes whatever pg_config is in
your PATH.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[HACKERS] How to refer to standard functions?

2006-07-19 Thread Gregory Stark


Is there any way to refer to standard functions when defining new functions? I
tried " AS '-','int4eq' " but it just said "ERROR: could not access file "-":
No such file or directory".

It seems like a lot of data types would find it convenient if they have a
representation that is similar to one of the standard data types.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] password is no required, authentication is overridden


Dave Page wrote:




 


-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of 
Andrew Dunstan

Sent: 19 July 2006 13:55
To: Hiroshi Saito
Cc: Thomas Bley; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] password is no required, 
authentication is overridden



I don't understand what you are saying here. The problem is 
that it is 
not clear (at least to the original user, and maybe to 
others) that when 
pgadmin3 saves a password it saves it where it will be found by all 
libpq clients, not just by pgadmin3. 
   



From: http://www.pgadmin.org/docs/1.4/connect.html

If you select "store password", pgAdmin stores passwords you enter in
the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under
Win32 for later reuse. For details, see pgpass documentation. It will be
used for all libpq based tools. If you want the password removed, you
can select the server's properties and uncheck the selection any time.

 



OK, although I am not sure I think that is sensible - it is at least 
documented. Does the dialog box also carry similar info?


 

How is that optimal? If pgadmin3 
were to save it in a non-standard location and then set PGPASSFILE to 
point to that location that would solve the problem. Or maybe 
it should 
offer a choice. Either way, how would a malicious user affect that? 
PGPASSFILE only contains a location, not the contents of the file, so 
exposing it is not any great security issue, as long as the 
location is 
itself protected.
   



We have no sensible way of determining whether or not the libpq we are
running with supports PGPASSFILE.


 



Well, this answer is better. The lack of an API to tell you the library 
version is possibly worrying, though.


cheers

andrew


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] [PATCHES] pg_regress in C

Martijn van Oosterhout  writes:
> On Tue, Jul 18, 2006 at 10:46:04PM -0400, Tom Lane wrote:
>> ...  One reason I didn't try to do this is I'm a bit hesitant to
>> write a signal handler that does anything as interesting as a system()
>> call, which would seem to be necessary to duplicate what the shell
>> script did.  Comments?

> It might not actually be unsafe, given system() actually blocks on
> waitpid() which is specifically listed as a "safe" function. I'm a bit
> confused though, because system() generally sets the parent to ignore
> SIGINT which running the child process. That means the postmaster is
> being killed but pg_regress is not? If this is the case, then you won't
> be able to catch SIGINT anyway.

The cases of interest are where the (new) code goes through
spawn_process, which does a fork() and then calls system() in the
child.  So the intermediate child is probably SIGINT-blocked, but
pg_regress itself isn't.

I was planning to rewrite spawn_process anyway, because I noticed that
as it's currently set up, we are actually creating four(!) processes per
parallel test: the pg_regress child, the shell invoked by system, the
psql invoked by the shell, and the connected backend.  That's even worse
than the shell script, which (at least on my system) used three processes.
I believe we can get down to two (psql and backend) if spawn_process
exec's the shell instead of using system, and also puts "exec" in front
of the command string passed to the shell.  So in that scenario there'd
not be any signal-blocking going on anyway.

That still leaves us with the question of whether pg_regress needs to do
anything special when signaled, though.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] lastval exposes information that currval does not

2006-07-19 Thread Phil Frost

On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote:
> Phil Frost wrote:
> > On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
> > > 
> > > Updated text:
> > > 
> > >For schemas, allows access to objects contained in the specified
> > >schema (assuming that the objects' own privilege requirements are
> > >also met).  Essentially this allows the grantee to look 
> > > up
> > >objects within the schema.  Without this permission, it is still
> > >possible to see the object names by querying the system tables, but
> > >they cannot be accessed via SQL.
> > 
> > No, this still misses the point entirely. See all my examples in this
> > thread for ways I have accessed objects without usage to their schema
> > with SQL.
> 
> OK, well we are not putting a huge paragraph in there.  Please suggest
> updated text.

Well, if you won't explain the whole situation, nor change it, then all
you can really say is it doesn't really work always. How about this:

For schemas, allows access to objects contained in the specified
schema. Note that the converse is not true in many cases: revoking
usage on a schema is not sufficient to prevent access in all cases.
There is precedent for new ways to bypass this check being added in
future releases. It would be unwise to give this privilege much
security value.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] feature request: pg_dump --view

2006-07-19 Thread Stephen Frost

* Andrew Dunstan ([EMAIL PROTECTED]) wrote:
> elein wrote:
> >>pg_dump -t  will work.
> >
> >Oh, you got me all worked up.  I was reading this as a way to
> >dump the CONTENTS of a view not the DEFINITION of a view.
> >I thought someone sneaked in pg_dump of a query in there.
> >
> >  
> 
> How would you load such a dump, since views are by default select-only?

Create the appropriate table definition, and then load it up?  Of
course, an option to have the pg_dump include the table definition
derived from the view would be nice also.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] password is no required, authentication is overridden

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Andrew Dunstan
> Sent: 19 July 2006 13:55
> To: Hiroshi Saito
> Cc: Thomas Bley; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] password is no required, 
> authentication is overridden
> 
> 
> I don't understand what you are saying here. The problem is 
> that it is 
> not clear (at least to the original user, and maybe to 
> others) that when 
> pgadmin3 saves a password it saves it where it will be found by all 
> libpq clients, not just by pgadmin3. 

From: http://www.pgadmin.org/docs/1.4/connect.html

If you select "store password", pgAdmin stores passwords you enter in
the ~/.pgpass file under *nix or %APPDATA%\postgresql\pgpass.conf under
Win32 for later reuse. For details, see pgpass documentation. It will be
used for all libpq based tools. If you want the password removed, you
can select the server's properties and uncheck the selection any time.


> How is that optimal? If pgadmin3 
> were to save it in a non-standard location and then set PGPASSFILE to 
> point to that location that would solve the problem. Or maybe 
> it should 
> offer a choice. Either way, how would a malicious user affect that? 
> PGPASSFILE only contains a location, not the contents of the file, so 
> exposing it is not any great security issue, as long as the 
> location is 
> itself protected.

We have no sensible way of determining whether or not the libpq we are
running with supports PGPASSFILE.

Regards, Dave.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] password is no required, authentication is overridden