date:20060303

> Are you running 8.1?  If so, you can use autovacuum and set per table 
> thresholds (read vacuum aggressivly) and per table cost delay settings 
> so that the performance impact is minimal.  If you have tried 8.1 
> autovacuum and found it unhelpful, I would be curious to find out why.

Yes, I'm running 8.1, and I've set up per table auto-vacuum settings :-)
And I lowered the general thresholds too. Generally autovacuum is very
useful from my POV, and in particular the per table settings are so.

But the problem I have is not the performance impact of the vacuum
itself, but the impact of the long running transaction of vacuuming big
tables. I do have big tables which are frequently updated and small
tables which are basically queue tables, so each inserted row will be
updated a few times and then deleted. Those queue tables tend to get
huge unvacuumable dead space during any long running transaction, and
vacuum on the big tables is such a long running transaction. And I have
a few of them, and one is in particular very busy (a task table, all
activities go through that one).

Now when the queue tables get 1000 times dead space compared to their
normal size, I get performance problems. So tweaking vacuum cost delay
doesn't buy me anything, as not vacuum per se is the performance
problem, it's long run time for big tables is.

Cheers,
Csaba.



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

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

Re: [HACKERS] [NOVICE] pg_config --pgxs

2006-03-03 Thread George Weaver



On March 2 Tom Lane wrote:


If you're on Windows this probably means that GetShortPathName() is
failing.  I'm not sure what conditions cause that, exactly.  It might be
a good idea if we fixed pg_config to print out the error code rather
than just silently failing.


Hi Tom,

I am using Windows (XP).

Regards,
George


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

  http://archives.postgresql.org

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

Christopher Browne wrote:

> Here should be the authoritative information:
> 
> http://www.cic.gc.ca/english/visit/visas.html
>   Countries/Territories Requiring Visas
> 
> http://www.cic.gc.ca/english/visit/letter.html 
>   Letter of Invitation for Countries Whose Citizens Require a
>   Temporary Resident Visa to Enter Canada

Wow, this is a great deal of burden that for sure I didn't have to do
last time :-(  Not sure why, maybe the laws changed or something.  It is
crystal clear that I have to do it this time however.

Thanks for the pointers.  I'm looking forward to finding somebody who
wants to "sponsor" me on this issue ... or maybe get me a passport from
the Holy See.

> The other "pointy bit" is that the letter of invitation needs to
> indicate the inviter's relationship to the person being invited.  I
> expect that would need to be a tad more specific than merely "he's
> some guy from Sweden that I heard about on the Internet"...

Rats :-(

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

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

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

Re: [HACKERS] Initdb on Windows 2003

2006-03-03 Thread Magnus Hagander

> All
> 
> I have determined what is causing the failure. It appears 
> that the stdout & stderr redirection to nul produces the 
> "Access is Denied."
> message. This is happening even if I type "dir >nul" at the 
> command prompt! I assume that this re-direction in PostgreSQL 
> is done when starting postgres.exe as to allow the 
> communction between the two processes?
> 
> I know this is not the correct place to ask my next question 
> but maybe there is a Windows expert out there :o) Does anyone 
> have any idea how Windows controls access to devices such as 
> nul,comN, lpt1, etc? Or what security setting governs this?
> 
> Thanks all for your help.

Wow, that's really interesting. Never heard of that one before.

Can you run filemon and/or regmon from sysinternals.com, and see exactly
what object it gets access denied on?

Oh, and did you try this without antivirus/antispyware/personalfirewall
as I beleive someone suggested? Same result, or different but still
broken?

//Magnus

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

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

> Wow, this is a great deal of burden that for sure I didn't have to do
> last time :-(  Not sure why, maybe the laws changed or something.  It is
> crystal clear that I have to do it this time however.

I think you're overreacting guys... I would first try and go to the
nearest Canadian embassy and try to get the visa. I bet in most of the
cases they will just issue it without any invitation letter and the
like... if not, only then worry about it ;-)

I'm also citizen from one of the countries (Romania) which require visas
to most of the world (or it required, the situation's relaxing in this
respect), and I never had any problems getting one. Or maybe it changed
after 9/11 ?

Cheers,
Csaba.




---(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] [NOVICE] pg_config --pgxs

2006-03-03 Thread Thomas Hallgren


George Weaver wrote:


On March 2 Tom Lane wrote:


If you're on Windows this probably means that GetShortPathName() is
failing.  I'm not sure what conditions cause that, exactly.  It might be
a good idea if we fixed pg_config to print out the error code rather
than just silently failing.


Hi Tom,

I am using Windows (XP).


GetShortPathName() seems to work well on my XP machine:

C:\>pg_config --version
PostgreSQL 8.1.1

C:\>pg_config --pgxs
C:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/pgxs.mk

Regards,
Thomas Hallgren


---(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] [NOVICE] pg_config --pgxs

2006-03-03 Thread Dave Page

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Thomas Hallgren
> Sent: 03 March 2006 14:18
> To: George Weaver
> Cc: Michael Fuhr; [EMAIL PROTECTED]; 
> pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] [NOVICE] pg_config --pgxs
> 
> George Weaver wrote:
> > 
> > On March 2 Tom Lane wrote:
> > 
> >> If you're on Windows this probably means that GetShortPathName() is
> >> failing.  I'm not sure what conditions cause that, 
> exactly.  It might be
> >> a good idea if we fixed pg_config to print out the error 
> code rather
> >> than just silently failing.
> > 
> > Hi Tom,
> > 
> > I am using Windows (XP).
> > 
> GetShortPathName() seems to work well on my XP machine:
> 
> C:\>pg_config --version
> PostgreSQL 8.1.1
> 
> C:\>pg_config --pgxs
> C:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/pgxs.mk

It might be that short filenames are disabled - George, what's the value
of the registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisab
le8dot3NameCreation

I did test it with and without short names disabled when I rewrote that
code for Win32, but perhaps there's something odd on your system that's
related.

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] Initdb on Windows 2003

2006-03-03 Thread James_Hughes

I did run filemon but did not get anything from it :( Yes I disabled all
anti-virus software, no difference made.

Have raised a helpdesk ticket with Microsoft, see if they can shed any
light on the problem.

James 

-Original Message-
From: Magnus Hagander [mailto:[EMAIL PROTECTED] 
Sent: 03 March 2006 14:14
To: Hughes, James; [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Initdb on Windows 2003

> All
> 
> I have determined what is causing the failure. It appears that the 
> stdout & stderr redirection to nul produces the "Access is Denied."
> message. This is happening even if I type "dir >nul" at the command 
> prompt! I assume that this re-direction in PostgreSQL is done when 
> starting postgres.exe as to allow the communction between the two 
> processes?
> 
> I know this is not the correct place to ask my next question but maybe

> there is a Windows expert out there :o) Does anyone have any idea how 
> Windows controls access to devices such as nul,comN, lpt1, etc? Or 
> what security setting governs this?
> 
> Thanks all for your help.

Wow, that's really interesting. Never heard of that one before.

Can you run filemon and/or regmon from sysinternals.com, and see exactly
what object it gets access denied on?

Oh, and did you try this without antivirus/antispyware/personalfirewall
as I beleive someone suggested? Same result, or different but still
broken?

//Magnus

---(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] Automatic free space map filling

Csaba Nagy wrote:

> Now when the queue tables get 1000 times dead space compared to their
> normal size, I get performance problems. So tweaking vacuum cost delay
> doesn't buy me anything, as not vacuum per se is the performance
> problem, it's long run time for big tables is.

So for you it would certainly help a lot to be able to vacuum the first
X pages of the big table, stop, release locks, create new transaction,
continue with the next X pages, lather, rinse, repeat.

This is perfectly doable, it only needs enough motivation from a
knowledgeable person.

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

---(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] column order in GROUP BY

Lukas Smith <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I think that's the wrong place.  transformGroupClause is the right
>> place.   It already does some hacking to try to make the GROUP BY
>> semantics match ORDER BY, but it doesn't think to try reordering
>> the GROUP BY items.

> Does it also throw out unnecessary columns in the GROUP BY? Like when 
> the GROUP BY contains multiple columns of which one (or a set) already 
> uniquely identifies every row.

No, and it would be quite inappropriate to do that in the parser, since
the constraints making it a valid transformation might get dropped
before the query is planned/used.  It'd be OK to throw out trivial
duplicates ("GROUP BY x,x") but I doubt that it's worth the cycles even
to try --- if you write a query that stupid you shouldn't complain that
it doesn't run efficiently.

There's a fairly fundamental point here, which is that the parser is
responsible for determining semantics --- in this case, what is the
semantic meaning of GROUP BY, in particular which operators should
implement it --- and then the planner is responsible for optimization
without changing those semantics.  Given the system design assumption
that GROUP BY is associated with a specific sort ordering, changing the
column order is a semantic change and so it's reasonable for the parser
to do it.  If we got rid of that design assumption then it'd become
planner territory, but as Neil observes that's not exactly low-hanging
fruit.

regards, tom lane

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

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

Csaba Nagy wrote:
> > Wow, this is a great deal of burden that for sure I didn't have to do
> > last time :-(  Not sure why, maybe the laws changed or something.  It is
> > crystal clear that I have to do it this time however.
> 
> I think you're overreacting guys... I would first try and go to the
> nearest Canadian embassy and try to get the visa. I bet in most of the
> cases they will just issue it without any invitation letter and the
> like... if not, only then worry about it ;-)

Yeah, you may be right, sorry.  The .gc.ca page says "updated
2004-02-17" so it must be the same page that was in place when I
solicited the visa last year.  However, the invitation letter was very
simple, didn't include any of the confidential information, and actually
it wasn't issued by a Canadian person at all!  It was signed by the
EnterpriseDB guys.

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

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

Re: [HACKERS] [NOVICE] pg_config --pgxs

2006-03-03 Thread George Weaver



From: "Dave Page" 
Sent: Friday, March 03, 2006 8:24 AM



It might be that short filenames are disabled - George, what's the value
of the registry key:



HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisab
le8dot3NameCreation


Hi Dave;

The value is 0.  Does that mean short filenames are disabled?

Regards,
George





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

Re: [HACKERS] Automatic free space map filling

Alvaro Herrera wrote:
> Csaba Nagy wrote:
> 
> > Now when the queue tables get 1000 times dead space compared to their
> > normal size, I get performance problems. So tweaking vacuum cost delay
> > doesn't buy me anything, as not vacuum per se is the performance
> > problem, it's long run time for big tables is.
> 
> So for you it would certainly help a lot to be able to vacuum the first
> X pages of the big table, stop, release locks, create new transaction,
> continue with the next X pages, lather, rinse, repeat.

But what about index clearing?  When do you scan each index?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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] Automatic free space map filling

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Csaba Nagy wrote:
> > 
> > > Now when the queue tables get 1000 times dead space compared to their
> > > normal size, I get performance problems. So tweaking vacuum cost delay
> > > doesn't buy me anything, as not vacuum per se is the performance
> > > problem, it's long run time for big tables is.
> > 
> > So for you it would certainly help a lot to be able to vacuum the first
> > X pages of the big table, stop, release locks, create new transaction,
> > continue with the next X pages, lather, rinse, repeat.
> 
> But what about index clearing?  When do you scan each index?

At the end of each iteration (or earlier, depending on
maintenance_work_mem).  So for each iteration you would need to scan the
indexes.

Maybe we could make maintenance_work_mem be the deciding factor; after
scanning the indexes, do the release/reacquire locks cycle.

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

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

Re: [HACKERS] Automatic free space map filling

2006-03-03 Thread Martijn van Oosterhout

On Fri, Mar 03, 2006 at 11:40:40AM -0300, Alvaro Herrera wrote:
> Csaba Nagy wrote:
> 
> > Now when the queue tables get 1000 times dead space compared to their
> > normal size, I get performance problems. So tweaking vacuum cost delay
> > doesn't buy me anything, as not vacuum per se is the performance
> > problem, it's long run time for big tables is.
> 
> So for you it would certainly help a lot to be able to vacuum the first
> X pages of the big table, stop, release locks, create new transaction,
> continue with the next X pages, lather, rinse, repeat.

I think the issue is that even for that small section, you still need
to scan all the indexes to delete the tuples there. So you actually
cause more work because you have to scan the indexes for each portion
of the table rather than just at the end.

However, if this were combined with some optimistic index deletion
code where the tuple was used to find the entry directly rather than
via bulkdelete, maybe it'd be doable. More overall I/O due to the index
lookups but the transactions become shorter. I say optimistic because
if you don't find the tuple the quick way you can always queue it for a
bulkdelete later. Hopefully it will be the uncommon case.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

signature.asc
Description: Digital signature

Re: [HACKERS] Automatic free space map filling

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > Csaba Nagy wrote:
> > > 
> > > > Now when the queue tables get 1000 times dead space compared to their
> > > > normal size, I get performance problems. So tweaking vacuum cost delay
> > > > doesn't buy me anything, as not vacuum per se is the performance
> > > > problem, it's long run time for big tables is.
> > > 
> > > So for you it would certainly help a lot to be able to vacuum the first
> > > X pages of the big table, stop, release locks, create new transaction,
> > > continue with the next X pages, lather, rinse, repeat.
> > 
> > But what about index clearing?  When do you scan each index?
> 
> At the end of each iteration (or earlier, depending on
> maintenance_work_mem).  So for each iteration you would need to scan the
> indexes.
> 
> Maybe we could make maintenance_work_mem be the deciding factor; after
> scanning the indexes, do the release/reacquire locks cycle.

Ewe.  How expensive is scanning an index compared to the heap?  Does
anyone have figure on that in terms of I/O and time?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

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

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

Re: [HACKERS] Automatic free space map filling

> Ewe.  How expensive is scanning an index compared to the heap?  Does
> anyone have figure on that in terms of I/O and time?

See this post for an example:
http://archives.postgresql.org/pgsql-performance/2006-02/msg00416.php

For my 200 million table, scanning the pk index took ~ 4 hours. And then
there are some more indexes...

So if the index has to be scanned completely, that's still too much.

Cheers,
Csaba.



---(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] Initdb on Windows 2003

2006-03-03 Thread Magnus Hagander

> I did run filemon but did not get anything from it :( Yes I 
> disabled all anti-virus software, no difference made.

Even more interesting. Do you have any other filter drivers that you
know of? SOme kind of quota stuff or so perhaps?

Oh, and if the bug is in the AV filter driver it's often not enough to
disable the AV - it has to be completely uninstalled. I've seen this
with several products wrt LSPs, and at least once with filterdrivers. So
that's also worth a try.


> Have raised a helpdesk ticket with Microsoft, see if they can 
> shed any light on the problem.

Sounds good - since you can reproduce it with a simple commandline, they
should at least accept touching the case ;-)

//Magnus

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

Re: [HACKERS] [NOVICE] pg_config --pgxs

2006-03-03 Thread Dave Page

 

> -Original Message-
> From: George Weaver [mailto:[EMAIL PROTECTED] 
> Sent: 03 March 2006 14:55
> To: Dave Page
> Cc: Thomas Hallgren; pgsql-hackers@postgresql.org; 
> [EMAIL PROTECTED]; Michael Fuhr
> Subject: Re: [HACKERS] [NOVICE] pg_config --pgxs
> 
> 
> From: "Dave Page" 
> Sent: Friday, March 03, 2006 8:24 AM
> 
> >It might be that short filenames are disabled - George, 
> what's the value
> >of the registry key:
> 
> >HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSyste
> m\NtfsDisab
> >le8dot3NameCreation
> 
> Hi Dave;
> 
> The value is 0.  Does that mean short filenames are disabled?

No, 1 means disabled, 0 (or non-existant) means enabled, so unless
there's another way of doing it that I don't know of, that's unlikely to
be the problem.

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] Initdb on Windows 2003


Magnus Hagander wrote:



 

Have raised a helpdesk ticket with Microsoft, see if they can 
shed any light on the problem.
   



Sounds good - since you can reproduce it with a simple commandline, they
should at least accept touching the case ;-)

 



In any case, since dir > nul fails, I think we can probably say it's Not 
Our Problem (tm).


We've used nul on Windows ever since initdb was written in C.

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] PG Extensions: Must be statically linked?

2006-03-03 Thread Craig A. James

Peter Eisentraut wrote:

Unfortunately, we're also using a second library (OpenBabel) that is
written in C++. A good portion of the code I've written is a wrapper
layer that hides the C++ objects and presents a simple C wrapper that
works for Postgres.

I suggest if you want to get any concrete advice out of this, post us
the commands that you execute and the error messages that you get.

Thanks for your answers -- see below.

Based on Peter's and Tom's replies regarding C++, I think you've answered my
question: I should be able to do this without static linking. But the Postgres
linker uses the C (not the C++) linker to resolve references, so it's not
finding the C++ libraries.

My original question was misleading. I said, "I have to link everything statically...",
when in fact what should have said was, "If I link statically, it works." I now realize
that I'm not linking everything statically, just the OpenBable and C++ libraries, and in fact
Postgres is finding the other libraries I need, like libz, libm, and so forth.

Here's what happens when I don't statically link the C++ libraries or the
OpenBabel libraries:

[root]# cp libmyfuncs.so /usr/local/pgsql/lib

$ psql -d myfuncs -U postgres
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
...
myfuncs=# CREATE FUNCTION myfunc(text, text) RETURNS boolean
myfuncs-# AS '/usr/local/pgsql/lib/libmyfuncs.so', 'myfunc'
myfuncs-# LANGUAGE 'C' STRICT;
ERROR: could not load library "/usr/local/pgsql/lib/libmyfuncs.so":
/usr/local/pgsql/lib/libmyfuncs.so: undefined symbol: _ZdlPv

That symbol is obviously a C++ mangled name, so it's not finding the C++
library, and the fact that it prints the mangled name suggests that it's a C
linker, not a C++ linker.

So now my question is: Can I somehow add other directories/libraries to those
that Postgres uses? Or is there an option for Postgres use the C++ dynamic
linker? I don't mind statically linking OpenBabel, but it seems like a bad
idea to put the specific version- and system-dependent location of libstdc++.a
into my makefiles.

(Am I the only guy in the world who has to use a C++ library as part of a
Postgres function?)

Thanks,
Craig

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

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

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for

2006-03-03 Thread Luke Lonergan

Peter,

I'm asking our performance lead, Ayush Parashar, to develop a talk proposal
that will discuss performance of Postgres, including enhancements like the
on-disk bitmap index, sort improvements, etc.  We'd also like to discuss the
business intelligence use-cases and where parallelism is applicable.

Where would such a talk fit in the program?

- Luke

On 3/1/06 2:51 AM, "Peter Eisentraut" <[EMAIL PROTECTED]> wrote:

> PostgreSQL Anniversary Summit
> =
> 
> Call for Contributions
> --

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

Re: [HACKERS] Initdb on Windows 2003

2006-03-03 Thread James_Hughes

I think we can safely say that it is not :-)


-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: 03 March 2006 15:41
To: Magnus Hagander
Cc: Hughes, James; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Initdb on Windows 2003

Magnus Hagander wrote:

>
>  
>
>>Have raised a helpdesk ticket with Microsoft, see if they can shed any

>>light on the problem.
>>
>>
>
>Sounds good - since you can reproduce it with a simple commandline, 
>they should at least accept touching the case ;-)
>
>  
>

In any case, since dir > nul fails, I think we can probably say it's Not
Our Problem (tm).

We've used nul on Windows ever since initdb was written in C.

cheers

andrew

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

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

Re: [HACKERS] PG Extensions: Must be statically linked?

2006-03-03 Thread Martijn van Oosterhout

On Fri, Mar 03, 2006 at 07:46:06AM -0800, Craig A. James wrote:
> Thanks for your answers -- see below.
> 
> Based on Peter's and Tom's replies regarding C++, I think you've answered 
> my question: I should be able to do this without static linking.  But the 
> Postgres linker uses the C (not the C++) linker to resolve references, so 
> it's not finding the C++ libraries.



> So now my question is: Can I somehow add other directories/libraries to 
> those that Postgres uses?  Or is there an option for Postgres use the C++ 
> dynamic linker?  I don't mind statically linking OpenBabel, but it seems 
> like a bad idea to put the specific version- and system-dependent location 
> of libstdc++.a into my makefiles.

There are a number of ways to indicate which extra libraries to load,
but by far the easiest is by specifying them on the link line.

Your problem is that when creating a shared library, you are not
required to make sure all your external symbols are defined somewhere.
You didn't post your compile/link line but if you're using C++ you
probably need to use g++ for the linking to include the special C++
libraries.

One way to find out what's going on is using readelf:

$ readelf -a /bin/bash |grep NEEDED
 0x0001 (NEEDED) Shared library: [libncurses.so.5]
 0x0001 (NEEDED) Shared library: [libdl.so.2]
 0x0001 (NEEDED) Shared library: [libc.so.6]

These are the libraries that will be loaded when someone tries to load
your shared lib (the above works on any ELF object). The easiest is to
specify "--no-undefined" on the link line so the linker checks up front
you won't get undefined symbols at run time. Remember it's
-Wl,--no-undefined if invoking from gcc/g++.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [HACKERS] Automatic free space map filling

2006-03-03 Thread Zeugswetter Andreas DCP SD


> > But what about index clearing?  When do you scan each index?
> 
> At the end of each iteration (or earlier, depending on 
> maintenance_work_mem).  So for each iteration you would need 
> to scan the indexes.
> 
> Maybe we could make maintenance_work_mem be the deciding 
> factor; after scanning the indexes, do the release/reacquire 
> locks cycle.

But you could do the indexes first and remember how far you can 
vacuum the heap later.

So you might as well do each index separately first and remember 
how far you can go with the heap for each one.
Then do the heap with a special restriction that comes from what you 
remembered from the indexes.
You can now separate the heap vacuum in arbitrarily large transactions,
since the indexes are already taken care of.

(You only vacuum to the point of the eldest vacuumed index)

Andreas

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

Re: [HACKERS] Automatic free space map filling

Zeugswetter Andreas DCP SD wrote:
> 
> > > But what about index clearing?  When do you scan each index?
> > 
> > At the end of each iteration (or earlier, depending on 
> > maintenance_work_mem).  So for each iteration you would need 
> > to scan the indexes.
> > 
> > Maybe we could make maintenance_work_mem be the deciding 
> > factor; after scanning the indexes, do the release/reacquire 
> > locks cycle.
> 
> But you could do the indexes first and remember how far you can 
> vacuum the heap later.
> 
> So you might as well do each index separately first and remember 
> how far you can go with the heap for each one.
> Then do the heap with a special restriction that comes from what you 
> remembered from the indexes.
> You can now separate the heap vacuum in arbitrarily large transactions,
> since the indexes are already taken care of.
> 
> (You only vacuum to the point of the eldest vacuumed index)

I thought you need to know the heap tids to remove from the index, so
how do you do the index first?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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

Re: [HACKERS] Automatic free space map filling

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> So for you it would certainly help a lot to be able to vacuum the first
> X pages of the big table, stop, release locks, create new transaction,
> continue with the next X pages, lather, rinse, repeat.

> This is perfectly doable, it only needs enough motivation from a
> knowledgeable person.

Bruce and I were discussing this the other day; it'd be pretty easy to
make plain VACUUM start a fresh transaction immediately after it
finishes a scan heap/clean indexes/clean heap cycle.  The infrastructure
for this (in particular, session-level locks that won't be lost by
closing the xact) is all there.  You'd have to figure out how often to
start a new xact ... every cycle is probably too often, at least for
smaller maintenance_work_mem settings ... but it'd not be hard or
involve any strange changes in system semantics.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Automatic free space map filling

Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > So for you it would certainly help a lot to be able to vacuum the first
> > X pages of the big table, stop, release locks, create new transaction,
> > continue with the next X pages, lather, rinse, repeat.
> 
> > This is perfectly doable, it only needs enough motivation from a
> > knowledgeable person.
> 
> Bruce and I were discussing this the other day; it'd be pretty easy to
> make plain VACUUM start a fresh transaction immediately after it
> finishes a scan heap/clean indexes/clean heap cycle.  The infrastructure
> for this (in particular, session-level locks that won't be lost by
> closing the xact) is all there.  You'd have to figure out how often to
> start a new xact ... every cycle is probably too often, at least for
> smaller maintenance_work_mem settings ... but it'd not be hard or
> involve any strange changes in system semantics.

Oh, reading the original posting, these are cases where
maintenance_work_mem is full and we are going to rescan the indexes
multiple times anyway for this table.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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] Automatic free space map filling

2006-03-03 Thread Matthew T. O'Connor


Alvaro Herrera wrote:

Csaba Nagy wrote:
  

Now when the queue tables get 1000 times dead space compared to their
normal size, I get performance problems. So tweaking vacuum cost delay
doesn't buy me anything, as not vacuum per se is the performance
problem, it's long run time for big tables is.


So for you it would certainly help a lot to be able to vacuum the first
X pages of the big table, stop, release locks, create new transaction,
continue with the next X pages, lather, rinse, repeat.


I got the impression that Csaba is looking more for "multiple 
simultaneous vacuum" more than the partial vacuum.  Not sure the best 
way to set this up, but perhaps a flag in the pg_autovacuum table that 
says "vacuum this table even if there is another vacuum running" that 
way you can control things and not have autovacuum firing off lots of 
vacuums at the same time.  Sounds to me that these frequently updated 
queue tables need to be monitored closely and not ignored for a long 
period of time because we are vacuuming another table.  Has anyone 
looked more closely at the multiple vacuum patch that was submitted to 
the patches list a while ago?


Matt


---(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] ipcclean in 8.1 broken?

Christopher Kings-Lynne wrote:
> >> if [ "$USER" = 'root' -o "$LOGNAME" = 'root' ]
> >>
> >> Always fails because even tho $USER is set to 'pgsql' when su'ed,
> >> $LOGNAME is still root.
> >>
> >> This is on FreeBSD 4.9
> > 
> > It seems to work on Linux; apparently there are different behaviors of su.  
> > Do 
> > you have a suggestion for resolving this?
> 
> Well all I did to fix it on FreeBSD was to remove the '-o "$LOGNAME" = 
> 'root'' bit...

I applied the attached patch to CVS HEAD and 8.1.X.  It looks at LOGNAME
only if USER is not set.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/ipcclean/ipcclean.sh
===
RCS file: /cvsroot/pgsql/src/bin/ipcclean/ipcclean.sh,v
retrieving revision 1.16
diff -c -c -r1.16 ipcclean.sh
*** src/bin/ipcclean/ipcclean.sh5 Jan 2006 01:56:29 -   1.16
--- src/bin/ipcclean/ipcclean.sh3 Mar 2006 16:48:43 -
***
*** 19,25 
  exit 0
  fi
  
! if [ "$USER" = 'root' -o "$LOGNAME" = 'root' ]
  then
(
  echo "$CMDNAME: cannot be run as root" 1>&2
--- 19,26 
  exit 0
  fi
  
! # only check $LOGNAME if $USER is not set
! if [ "$USER" = 'root' -o \( ! "$USER" -a "$LOGNAME" = 'root' \) ]
  then
(
  echo "$CMDNAME: cannot be run as root" 1>&2

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

Re: [HACKERS] Automatic free space map filling

Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Csaba Nagy wrote:
> >  
> >>Now when the queue tables get 1000 times dead space compared to their
> >>normal size, I get performance problems. So tweaking vacuum cost delay
> >>doesn't buy me anything, as not vacuum per se is the performance
> >>problem, it's long run time for big tables is.
> >>
> >So for you it would certainly help a lot to be able to vacuum the first
> >X pages of the big table, stop, release locks, create new transaction,
> >continue with the next X pages, lather, rinse, repeat.
> 
> I got the impression that Csaba is looking more for "multiple 
> simultaneous vacuum" more than the partial vacuum.

So he rather needs Hannu Krosing's patch for simultaneous vacuum ...

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

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

Re: [HACKERS] PG Extensions: Must be statically linked?

"Craig A. James" <[EMAIL PROTECTED]> writes:
> So now my question is: Can I somehow add other directories/libraries
> to those that Postgres uses?

This is not a Postgres problem, it's a dynamic-linker problem, and
I don't believe there is a different dynamic linker for C++ than C.
Your problem is just to get your shared library marked as needing
libstdc++.so.  (ldd on the .so should show what shared libraries it
needs.)  On Linux this should all happen pretty much automatically,
at least for libraries that are in the ldconfig search path.  I'm
not sure what is going wrong, but you could take a look at
contrib/dblink, which dynamically includes libpq.so (and the backend
certainly does not load libpq.so by default).  If that works on your
machine then try to figure out what dblink's Makefile does differently
from yours.

My concern about how nicely libstdc++ will play in the backend
environment still stands though.

regards, tom lane

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

Re: [HACKERS] Foreign keys for non-default datatypes


On Fri, 3 Mar 2006, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Thu, 2 Mar 2006, Tom Lane wrote:
> >> 1. If the index opclass contains an exact operator for the case
> >> "PKtype = FKtype", use that operator.
>
> > Is this rule to read explicitly naming '=' or just the item in that
> > position in the opclass?
>
> The operator occupying the equality position in the opclass.

Okay.

> > I think it's an acceptable idea to fail if we're going to extend the
> > cross-type indexing support, but AFAICS we have to at the very least allow
> > all of the "standard" numeric types in all combinations to work to meet
> > the spec, and I don't think the above rules and current opclasses will
> > give that to us (and I don't honestly understand some of the bits of this
> > to know if there's a problem with extending the opclasses to allow that).
>
> The cases that are likely to be problematic are things like a FLOAT8
> column referencing a NUMERIC primary key.  However, that sort of
> mishmash is fraught with all kinds of risks anyway (think roundoff
> error) so the fact that the spec nominally allows it doesn't tell me
> that we ought to encourage it.

There's a bit of difference between not encouraging it and disallowing it
entirely, but I'm willing to buy that argument.  I do think that numeric
reference int needs to be allowed though, and I thought that's also
currently not there (although int reference numeric should work I think).


---(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] ipcclean in 8.1 broken?

Bruce Momjian  writes:
> I applied the attached patch to CVS HEAD and 8.1.X.  It looks at LOGNAME
> only if USER is not set.
  
> ! if [ "$USER" = 'root' -o "$LOGNAME" = 'root' ]

> ! # only check $LOGNAME if $USER is not set
> ! if [ "$USER" = 'root' -o \( ! "$USER" -a "$LOGNAME" = 'root' \) ]

Bruce, this patch isn't going to fix anything.

regards, tom lane

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

Re: [HACKERS] ipcclean in 8.1 broken?

Tom Lane wrote:
> Bruce Momjian  writes:
> > I applied the attached patch to CVS HEAD and 8.1.X.  It looks at LOGNAME
> > only if USER is not set.
>   
> > ! if [ "$USER" = 'root' -o "$LOGNAME" = 'root' ]
> 
> > ! # only check $LOGNAME if $USER is not set
> > ! if [ "$USER" = 'root' -o \( ! "$USER" -a "$LOGNAME" = 'root' \) ]
> 
> Bruce, this patch isn't going to fix anything.

Chris said he did:

> Well all I did to fix it on FreeBSD was to remove the '-o "$LOGNAME" =
> 'root'' bit...

so I figured the patch would help, no?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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

Re: [HACKERS] Automatic free space map filling

> > I got the impression that Csaba is looking more for "multiple 
> > simultaneous vacuum" more than the partial vacuum.
> 
> So he rather needs Hannu Krosing's patch for simultaneous vacuum ...

Well, I guess that would be a good solution to the "queue table"
problem. The problem is that I can't deploy that patch on our production
systems without being fairly sure it won't corrupt any data... and I
can't rely on non-production testing either. Basically I'm waiting to
see Tom saying it will fly :-)

Cheers,
Csaba.



---(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] PostgreSQL Anniversary Summit, Call for Contributions

2006-03-03 Thread Greg Stark

Alvaro Herrera <[EMAIL PROTECTED]> writes:

> Christopher Browne wrote:
> 
> > http://www.cic.gc.ca/english/visit/letter.html 
> >   Letter of Invitation for Countries Whose Citizens Require a
> >   Temporary Resident Visa to Enter Canada
> 
> Wow, this is a great deal of burden that for sure I didn't have to do
> last time :-(  Not sure why, maybe the laws changed or something.  It is
> crystal clear that I have to do it this time however.

Are you sure that "Temporary Resident Visa" is what you need? 
Isn't the regular visa people get called just a "Guest Visa"?

-- 
greg


---(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] Foreign keys for non-default datatypes

Stephan Szabo <[EMAIL PROTECTED]> writes:
> There's a bit of difference between not encouraging it and disallowing it
> entirely, but I'm willing to buy that argument.  I do think that numeric
> reference int needs to be allowed though, and I thought that's also
> currently not there (although int reference numeric should work I think).

Um, not sure which way you mean there.  The case that would work in the
proposal as I gave it is where the referencing column's type is
implicitly promotable to the referenced column's type.  So for example
an FK int column could reference a PK numeric column but (without more
btre support) not an FK numeric column referencing a PK int.  Is that
what you meant?

If you try numeric-references-int today, you get the "costly sequential
scan" warning, because the selected "=" operator is numeric_eq and
that's not compatible with the PK's index.  Basically, if any implicit
promotion happens on the PK side, you're going to get the warning
because you're no longer using an "=" operator that matches the index.
We have not seen many complaints about getting that warning since 8.0,
so I think that in practice people aren't using these cases and it'd be
OK to make them a hard error instead.  I would also argue that if
implicit promotion does happen on the PK side, it's very questionable
what semantics the FK constraint has anyway --- you can no longer be
sure that the operator you are using has a notion of equality that's
compatible with the PK index's notion.

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] ipcclean in 8.1 broken?

Bruce Momjian  writes:
> Chris said he did:
>> Well all I did to fix it on FreeBSD was to remove the '-o "$LOGNAME" =
>> 'root'' bit...
> so I figured the patch would help, no?

No, because there's no good reason to suppose that $USER wouldn't be set.

I think we should remove that entire code block, and instead check for a
zero value of EffectiveUser after doing the id bit.

(I'm not finding it right now, but I'm pretty sure that the SUS
specifies that numeric userid == 0 for superuser, whereas "root" is not
required to be the name, so this would be more correct anyway.)

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] ipcclean in 8.1 broken?

Tom Lane wrote:
> Bruce Momjian  writes:
> > Chris said he did:
> >> Well all I did to fix it on FreeBSD was to remove the '-o "$LOGNAME" =
> >> 'root'' bit...
> > so I figured the patch would help, no?
> 
> No, because there's no good reason to suppose that $USER wouldn't be set.

But if USER is set, why check LOGNAME?

> I think we should remove that entire code block, and instead check for a
> zero value of EffectiveUser after doing the id bit.
> 
> (I'm not finding it right now, but I'm pretty sure that the SUS
> specifies that numeric userid == 0 for superuser, whereas "root" is not
> required to be the name, so this would be more correct anyway.)

Can we assume 'id' is on all unix systems?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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] Foreign keys for non-default datatypes


On Fri, 3 Mar 2006, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > There's a bit of difference between not encouraging it and disallowing it
> > entirely, but I'm willing to buy that argument.  I do think that numeric
> > reference int needs to be allowed though, and I thought that's also
> > currently not there (although int reference numeric should work I think).
>
> Um, not sure which way you mean there.  The case that would work in the
> proposal as I gave it is where the referencing column's type is
> implicitly promotable to the referenced column's type.  So for example
> an FK int column could reference a PK numeric column but (without more
> btre support) not an FK numeric column referencing a PK int.  Is that
> what you meant?

Yes.

> If you try numeric-references-int today, you get the "costly sequential
> scan" warning, because the selected "=" operator is numeric_eq and
> that's not compatible with the PK's index.  Basically, if any implicit
> promotion happens on the PK side, you're going to get the warning
> because you're no longer using an "=" operator that matches the index.
> We have not seen many complaints about getting that warning since 8.0,
> so I think that in practice people aren't using these cases and it'd be
> OK to make them a hard error instead.  I would also argue that if
> implicit promotion does happen on the PK side, it's very questionable
> what semantics the FK constraint has anyway --- you can no longer be
> sure that the operator you are using has a notion of equality that's
> compatible with the PK index's notion.

I'd argue that this case makes sense in a purely theoretical sense,
numeric(8,0) references int is a sensible operation (when you don't take
into account specific PostgreSQL limitations) and it's required by spec,
but I agree that promotion on the PK side is problematic because of the
issues about equality so we may not be able to do better. I'm just worried
that we're moving further from compliance with the spec.


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

Re: [HACKERS] PG Extensions: Must be statically linked?

2006-03-03 Thread Mark Dilger


Tom Lane wrote:

My concern about how nicely libstdc++ will play in the backend
environment still stands though.


I have had the same concern, though never any hard evidence of a problem.  If 
the C++ functions are wrapped with "extern C", and all exceptions caught 
(perhaps converted into error numbers which are then returned from the wrapper 
functions to the plain-C calling functions), are there any remaining known 
problems?  I have often considered making a C++ allocator which wrapped palloc 
and pfree, so that I could then use the STL within the backend...


Has anyone tried this?

mark

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

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

Re: [HACKERS] ipcclean in 8.1 broken?

Bruce Momjian  writes:
> Tom Lane wrote:
>> (I'm not finding it right now, but I'm pretty sure that the SUS
>> specifies that numeric userid == 0 for superuser, whereas "root" is not
>> required to be the name, so this would be more correct anyway.)

> Can we assume 'id' is on all unix systems?

What's your point?  The script fails anyway if that bit doesn't work.

regards, tom lane

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

Re: [HACKERS] Adding an ignore list to pg_restore, patch take #3

2006-03-03 Thread Martin Pitt

Hi all,

thanks for the feedback. I updated the patch now.

Alvaro Herrera [2006-02-25 13:47 -0300]:
> > I improved the patch now to only ignore TABLE DATA for existing tables
> > if '-X ignore-existing-tables' is specified. I also updated the
> > documentation.
> 
> Is this really an appropiate description for the behavior?  What happens
> if the table is not created for some other reason?  Consider for example
> a table using a datatype that couldn't be created.

Right. However, if the table is not present at all, then it makes even
less sense to attempt to restore its data. Therefore I consider this
mainly a documentation issue. I changed the option to 
-X no-data-for-failed-tables and described it as

By default, table data objects are restored even if the associated
table could not be successfully created (e. g.  because it already
exists). [...]

Tom Lane [2006-02-25 12:18 -0500]:
> Martin Pitt <[EMAIL PROTECTED]> writes:
> > Martin Pitt [2006-02-19 14:39 +0100]:
> >> Since this changes the behaviour of pg_restore, this should probably
> >> become an option, e. g. -D / --ignore-existing-table-data. I'll do
> >> this if you agree to the principle of the current patch.
> 
> > I improved the patch now to only ignore TABLE DATA for existing tables
> > if '-X ignore-existing-tables' is specified. I also updated the
> > documentation.
> 
> This patch is unbelievably ugly and probably vulnerable to coredumps.
> Please use a cleaner way of disabling the subsequent load than tromping
> all over the TOC datastructure, ie, not this:
> 
> > +strcpy (tes->desc, 
> > "IGNOREDATA");

It should not segfault, but I agree that this is a bit hackish. The
updated patch completely removes the TABLE DATA node from the linked
list. It does not free its memory, though; I did not find a
free_tocentry() or similar function. However, pg_restore is no daemon,
and without the new option the memory would be allocated, too, so it
does not make much difference. Can anyone give me a hint how to
properly free the struct?

> BTW, I'm pretty sure it fails for tables with same names in different
> schemas, too.

Right, sorry for that. I fixed that, too.

Bruce Momjian [2006-02-28 19:54 -0500]:
> I will clean it up before applying.

Thank you. I hope the updated patch makes that a little bit easier.

> Your patch has been added to the PostgreSQL unapplied patches list at:
> 
>   http://momjian.postgresql.org/cgi-bin/pgpatches
> 
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.

Great, thanks!

Martin

P.S. I also updated the test script to create two namespaces with
identidal table names.
http://people.debian.org/~mpitt/test-pg_restore-existing.sh
-- 
Martin Pitthttp://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

In a world without walls and fences, who needs Windows and Gates?
diff -ruN postgresql-8.1.3-old/doc/src/sgml/ref/pg_restore.sgml 
postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml
--- postgresql-8.1.3-old/doc/src/sgml/ref/pg_restore.sgml   2005-11-01 
22:09:50.0 +0100
+++ postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml   2006-03-03 
19:13:50.0 +0100
@@ -395,6 +395,20 @@
   
  
 
+ 
+  -X no-data-for-failed-tables
+  
+   
+   By default, table data objects are restored even if the
+   associated table could not be successfully created (e. g.
+   because it already exists). With this option, such table
+   data is silently ignored. This is useful for dumping and
+   restoring databases with tables which contain auxiliary data
+   for PostgreSQL extensions (e. g. PostGIS).
+   
+  
+ 
+
 

 
diff -ruN postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c 
postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c
--- postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c   2006-02-05 
21:58:57.0 +0100
+++ postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c   2006-03-03 
19:14:03.0 +0100
@@ -268,6 +268,23 @@
_printTocEntry(AH, te, ropt, false, false);
defnDumped = true;
 
+   /* If we could not create a table, ignore the 
respective TABLE DATA if 
+* -X no-data-for-failed-tables is given */
+   if (ropt->noDataForFailedTables && AH->lastErrorTE == 
te && strcmp (te->desc, "TABLE") == 0) {
+   TocEntry *tes, *last;
+
+   ahlog (AH, 1, "table %s could not be created, 
will not restore its data\n", te->tag);
+
+   for (last = te, tes = te->next; tes != AH->toc; 
last = tes, tes = tes->next) {
+   if (strcmp (tes->desc, "TABLE DATA") == 
0 && strcmp (tes->tag, te->tag) == 0 &&
+

Re: [HACKERS] ipcclean in 8.1 broken?

Tom Lane wrote:
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> (I'm not finding it right now, but I'm pretty sure that the SUS
> >> specifies that numeric userid == 0 for superuser, whereas "root" is not
> >> required to be the name, so this would be more correct anyway.)
> 
> > Can we assume 'id' is on all unix systems?
> 
> What's your point?  The script fails anyway if that bit doesn't work.

Is 'id' better than what we have now if 'id' isn't widely supported?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

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

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

Re: [HACKERS] Foreign keys for non-default datatypes

Stephan Szabo <[EMAIL PROTECTED]> writes:
> I'd argue that this case makes sense in a purely theoretical sense,
> numeric(8,0) references int is a sensible operation (when you don't take
> into account specific PostgreSQL limitations) and it's required by spec,
> but I agree that promotion on the PK side is problematic because of the
> issues about equality so we may not be able to do better. I'm just worried
> that we're moving further from compliance with the spec.

Well, if anyone complains about it we can add an int-eq-numeric operator
to the btree opclass for int, but the case seems pretty pointless to me.
Why are you using a numeric if the FK constraint is going to keep you
from storing any values that don't fit in int?  (Cases where this
argument doesn't hold will fit under the its-ok-to-promote-the-FK-side
rule.)

The reason I'm hesitant to add a bunch more cross-type operators is
mainly that we have too darn many operators named "=" already.  I've
seen in recent profiling tests that it's taking the parser a noticeable
amount of time to decide which one is meant.  So I don't want to add a
lot more without a fairly credible use-case, and right now this doesn't
look like one to me.

BTW, in researching this I noted that SQL92 requires the referencing
column to be the *same* type as the referenced column (11.8  syntax rule 7).  I see that SQL99 relaxes this to
require only comparable types, but I'm not really going to feel too bad
if we restrict it to being just "sensible" combinations of comparable
types.

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] ipcclean in 8.1 broken?

2006-03-03 Thread Michael Fuhr

On Fri, Mar 03, 2006 at 01:00:59PM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > (I'm not finding it right now, but I'm pretty sure that the SUS
> > specifies that numeric userid == 0 for superuser, whereas "root" is not
> > required to be the name, so this would be more correct anyway.)

The Rationale (XRAT) Definitions section says for "Superuser":

  This concept, with great historical significance to UNIX system
  users, has been replaced with the notion of appropriate privileges.

An excerpt from the definition of "Appropriate Privileges" is

  For many historical implementations of the UNIX system, the
  presence of the term "appropriate privileges" in POSIX.1 may be
  understood as a synonym for "superuser" (UID 0).  However, other
  systems have emerged where this is not the case and each discrete
  controllable action has appropriate privileges associated with
  it.  Because this mechanism is implementation-defined, it must
  be described in the conformance document.

(I'd post links but people elsewhere haved bitched about doing that
because the documents are supposed to require registration to read.
If that's true then it seems silly that they're available to anybody
who knows the URL.)

> Can we assume 'id' is on all unix systems?

It's defined in Shell and Utilities (XCU).  If the system doesn't
have it then one must wonder what else the system is missing.

-- 
Michael Fuhr

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

Re: [HACKERS] ipcclean in 8.1 broken?

2006-03-03 Thread Michael Paesold

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian  writes:
> Tom Lane wrote:
>> (I'm not finding it right now, but I'm pretty sure that the SUS
>> specifies that numeric userid == 0 for superuser, whereas "root" is 
>> not

>> required to be the name, so this would be more correct anyway.)

> Can we assume 'id' is on all unix systems?

What's your point?  The script fails anyway if that bit doesn't work.

Is 'id' better than what we have now if 'id' isn't widely supported?

I don't think this is really a question of portability. The variables $USER 
and $LOGNAME are not always set to the current (effective) user, e.g. on 
linux. That's Chris' current problem, I think. Just compare the difference 
of using "su" with and without the "-l" argument:

$ su
# echo $LOGNAME ; echo $USER
mip
mip
# exit
$ su -l
# echo $LOGNAME ; echo $USER
root
root
#

Of course, if you just want to question the use of "id", that's a different 
story.

Best Regards,
Michael Paesold 

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

  http://archives.postgresql.org

Re: [HACKERS] ipcclean in 8.1 broken?

Bruce Momjian  writes:
> Tom Lane wrote:
>> What's your point?  The script fails anyway if that bit doesn't work.

> Is 'id' better than what we have now if 'id' isn't widely supported?

I'm repeating myself, but: what's your point?  'id' exists on Linux,
and the script fails (in the worst possible way, ie, might remove
inappropriate shmem segments) on all other platforms if it's unable
to detect the correct EffectiveUser.  I would argue that checking for a
numeric, nonzero EffectiveUser is going to make it safer not less so.

regards, tom lane

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

Re: [HACKERS] ipcclean in 8.1 broken?

Tom Lane wrote:
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> What's your point?  The script fails anyway if that bit doesn't work.
> 
> > Is 'id' better than what we have now if 'id' isn't widely supported?
> 
> I'm repeating myself, but: what's your point?  'id' exists on Linux,
> and the script fails (in the worst possible way, ie, might remove
> inappropriate shmem segments) on all other platforms if it's unable
> to detect the correct EffectiveUser.  I would argue that checking for a
> numeric, nonzero EffectiveUser is going to make it safer not less so.

If it can be done more reliably than what we do not.  We support much
more than Linix, and I have not seen anyway say 'id' is available on all
platforms.  We can try 'id' if it exists and fall back if it doesn't.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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

Re: [HACKERS] Foreign keys for non-default datatypes

On Fri, 3 Mar 2006, Tom Lane wrote:

> The reason I'm hesitant to add a bunch more cross-type operators is
> mainly that we have too darn many operators named "=" already.  I've
> seen in recent profiling tests that it's taking the parser a noticeable
> amount of time to decide which one is meant.  So I don't want to add a
> lot more without a fairly credible use-case, and right now this doesn't
> look like one to me.

Honestly, as long as we *could* reasonably add one, I don't have a
complaint, because we aren't taking a step that involves a huge amount of
work if we get a complaint or separately decide to be more compliant on
this.

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

   http://archives.postgresql.org

Re: [HACKERS] ipcclean in 8.1 broken?


Bruce Momjian wrote:


Tom Lane wrote:
 


Bruce Momjian  writes:
   


Tom Lane wrote:
 


What's your point?  The script fails anyway if that bit doesn't work.
   


Is 'id' better than what we have now if 'id' isn't widely supported?
 


I'm repeating myself, but: what's your point?  'id' exists on Linux,
and the script fails (in the worst possible way, ie, might remove
inappropriate shmem segments) on all other platforms if it's unable
to detect the correct EffectiveUser.  I would argue that checking for a
numeric, nonzero EffectiveUser is going to make it safer not less so.
   



If it can be done more reliably than what we do not.  We support much
more than Linix, and I have not seen anyway say 'id' is available on all
platforms.  We can try 'id' if it exists and fall back if it doesn't.

 


perl -e 'exit  $> != 0 ;'

succeeds iff your effective uid is not 0.

or we could revive pg_id.

(for the humor impaired, no, I am not serious.)

cheers

andrew



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

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

Re: [HACKERS] Foreign keys for non-default datatypes


Stephan Szabo wrote:


On Fri, 3 Mar 2006, Tom Lane wrote:

 


The reason I'm hesitant to add a bunch more cross-type operators is
mainly that we have too darn many operators named "=" already.  I've
seen in recent profiling tests that it's taking the parser a noticeable
amount of time to decide which one is meant. 



Speaking of parsers, did anyone else notice that gcc in its latest 
release has ripped out the bison based parser for C and Objective-C in 
favor of a hand cut RD parser?


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] Foreign keys for non-default datatypes

Andrew Dunstan wrote:
> Stephan Szabo wrote:
> 
> >On Fri, 3 Mar 2006, Tom Lane wrote:
> >
> >>The reason I'm hesitant to add a bunch more cross-type operators is
> >>mainly that we have too darn many operators named "=" already.  I've
> >>seen in recent profiling tests that it's taking the parser a noticeable
> >>amount of time to decide which one is meant. 
> 
> Speaking of parsers, did anyone else notice that gcc in its latest 
> release has ripped out the bison based parser for C and Objective-C in 
> favor of a hand cut RD parser?

Yeah, I did.  I wonder what sort of effort they went to write the new
parser.

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

---(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] Foreign keys for non-default datatypes

Alvaro Herrera wrote:
> Andrew Dunstan wrote:

> > Speaking of parsers, did anyone else notice that gcc in its latest 
> > release has ripped out the bison based parser for C and Objective-C in 
> > favor of a hand cut RD parser?
> 
> Yeah, I did.  I wonder what sort of effort they went to write the new
> parser.

This is the new parser
http://gcc.gnu.org/viewcvs/tags/gcc_4_1_0_release/gcc/c-parser.c?view=markup&rev=111560

It's 6000 lines long.

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

---(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] [SQL] Interval subtracting

2006-03-03 Thread Mark Dilger


Attached is the new patch.  To summarize:

  - new function justify_interval(interval)
  - modified function justify_hours(interval)
  - modified function justify_days(interval)

These functions are defined to meet the requirements as discussed in this 
thread.  Specifically:


  - justify_hours makes certain the sign bit on the hours
matches the sign bit on the days.  It only checks the
sign bit on the days, and not the months, when
determining if the hours should be positive or negative.
After the call, -24 < hours < 24.

  - justify_days makes certain the sign bit on the days
matches the sign bit on the months.  It's behavior does
not depend on the hours, nor does it modify the hours.
After the call, -30 < days < 30.

  - justify_interval makes sure the sign bits on all three
fields months, days, and hours are all the same.  After
the call, -24 < hours < 24 AND -30 < days < 30.

'make check' passes all tests.  There are no tests for justify_interval, as it 
is new.  But the existing tests for justify_hours and justify_days appear to 
still work, even though the behavior has changed.  Apparently, their test cases 
are not sensitive to the particular changes that have occurred.


I would include new tests in the patch but do not know on which reference 
machine/platform the patches are supposed to be generated.


mark
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c   22 Nov 2005 22:30:33 -  1.160
--- src/backend/utils/adt/timestamp.c   3 Mar 2006 20:23:26 -
***
*** 1975,1980 
--- 1975,2054 
  }
  
  /*
+  *  interval_justify_interval()
+  *
+  *  Adjust interval so 'month', 'day', and 'time' portions are within
+  *  customary bounds.  Specifically:
+  *
+  *0 <= abs(time) < 24 hours
+  *0 <= abs(day)  < 30 days
+  *
+  *  Also, the sign bit on all three fields is made equal, so either
+  *  all three fields are negative or all are positive.
+  */
+ Datum
+ interval_justify_interval(PG_FUNCTION_ARGS)
+ {
+   Interval   *span = PG_GETARG_INTERVAL_P(0);
+   Interval   *result;
+   
+ #ifdef HAVE_INT64_TIMESTAMP
+   int64   wholeday;
+ #else
+   double  wholeday;
+ #endif
+   int32   wholemonth;
+ 
+   result = (Interval *) palloc(sizeof(Interval));
+   result->month = span->month;
+   result->day = span->day;
+   result->time = span->time;
+ 
+ #ifdef HAVE_INT64_TIMESTAMP
+   TMODULO(result->time, wholeday, USECS_PER_DAY);
+ #else
+   TMODULO(result->time, wholeday, (double) SECS_PER_DAY);
+ #endif
+   result->day += wholeday;/* could overflow... */
+ 
+   wholemonth = result->day / DAYS_PER_MONTH;
+   result->day -= wholemonth * DAYS_PER_MONTH;
+   result->month += wholemonth;
+ 
+   if (result->month < 0 && result->day > 0)
+   {
+   result->day -= DAYS_PER_MONTH;
+   result->month++;
+   }
+   else if (result->month > 0 && result->day < 0)
+   {
+   result->day += DAYS_PER_MONTH;
+   result->month--;
+   }
+ 
+   if (result->time < 0 && result->day > 0)
+   {
+ #ifdef HAVE_INT64_TIMESTAMP
+   result->time += USECS_PER_DAY;
+ #else
+   result->time += (double) SECS_PER_DAY;
+ #endif
+   result->day--;
+   }
+   else if (result->time > 0 && result->day < 0)
+   {
+ #ifdef HAVE_INT64_TIMESTAMP
+   result->time -= USECS_PER_DAY;
+ #else
+   result->time -= (double) SECS_PER_DAY;
+ #endif
+   result->day++;
+   }
+ 
+   PG_RETURN_INTERVAL_P(result);
+ }
+ 
+ /*
   *interval_justify_hours()
   *
   *Adjust interval so 'time' contains less than a whole day, adding
***
*** 2006,2011 
--- 2080,2104 
  #endif
result->day += wholeday;/* could overflow... */
  
+   if (result->time < 0 && result->day > 0)
+   {
+ #ifdef HAVE_INT64_TIMESTAMP
+   result->time += USECS_PER_DAY;
+ #else
+   result->time += (double) SECS_PER_DAY;
+ #endif
+   result->day--;
+   }
+   else if (result->time > 0 && result->day < 0)
+   {
+ #ifdef HAVE_INT64_TIMESTAMP
+   result->time -= USECS_PER_DAY;
+ #else
+   result->time -= (double) SECS_PER_DAY;
+ #endif
+   result->day++;
+   }
+ 
PG_RETURN_INTERVAL_P(result);
  }
  
***
*** 2031,2036 
--- 2124,2140 
result->day -= wholemonth * DAYS_PER_MONTH;
result->month += wholemonth;
  
+   if (result->month < 0 && result->day > 0)
+   {
+   result->day -= DAYS_PER_MONTH;
+   result->month++;
+   }
+   else if (

Re: [HACKERS] Foreign keys for non-default datatypes

2006-03-03 Thread elein

On Thu, Mar 02, 2006 at 08:41:20PM -0500, Tom Lane wrote:
> elein <[EMAIL PROTECTED]> writes:
> > ... What I'm saying is that the opclass needs to be 
> > an option to PRIMARY KEY and FOREIGN KEY--
> 
> PRIMARY KEY and UNIQUE, you mean.
> 
> This was brought up before, but I remain less than excited about it.
> You can get essentially the same functionality by doing a CREATE UNIQUE
> INDEX command, so allowing it as part of the PK/UNIQUE syntax is little
> more than syntactic sugar.  I'm concerned that wedging opclass names
> into that syntax could come back to haunt us some day --- eg, if SQL2009
> decides to put their own kind of option into the same syntactic spot.
> 
> > The case in point is a subtype (domain) with a BTREE operator class.  
> > I can of course create a separate unique index, however, if I use the 
> > PRIMARY KEY syntax the op class of the data type is not recognized.
> 
> Hm, does CREATE INDEX work without explicitly specifying the opclass?
> I suspect your complaint really stems from overeager getBaseType() calls
> in the index definition code, which is maybe fixable without having to
> get into syntactic extensions.

I am also leary of syntactic extensions.  I've found at least one getbasetype
to be misplaced rather than over eager.  But I'm looking at these issues
as I have time available.

> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

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

   http://archives.postgresql.org

Re: [HACKERS] Foreign keys for non-default datatypes

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> Andrew Dunstan wrote:
>>> Speaking of parsers, did anyone else notice that gcc in its latest 
>>> release has ripped out the bison based parser for C and Objective-C in 
>>> favor of a hand cut RD parser?
>> 
>> Yeah, I did.  I wonder what sort of effort they went to write the new
>> parser.

> This is the new parser
> http://gcc.gnu.org/viewcvs/tags/gcc_4_1_0_release/gcc/c-parser.c?view=markup&rev=111560

> It's 6000 lines long.

Given that we whack the grammar around on a pretty regular basis,
I can't imagine that it'd be a smart idea to go to a handmade parser.
gcc is dealing with a very stable language definition so the tradeoffs
for them are a lot different.

Be nice if bison were a tad faster though :-(

regards, tom lane

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

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

[HACKERS] Problemas with gram.y

2006-03-03 Thread tmorelli

Hi,

I'm trying to extend the CREATE INDEX statement with a fillfactor clause. In
Gram.y, I did this:

IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
access_method_clause '(' index_params ')' fillfactor_clause where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
n->idxname = $4;
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
n->fillfactor = $11;
n->whereClause = $12;
$$ = (Node *)n;
}

And the clause:

fillfactor_clause:  
FILLFACTOR IntegerOnly  { $$ = makeInteger($2); }
{ $$ = 0; };

I had to add a new field into IndexStmt (unsigned int fillfactor). Everything
is fine after parsing except that I can't see the integer value. For example,
in transformIndexStmt (analyze.c), I've inspected stmt->fillfactor and I've got
 a strange, obviously wrong, value (137616352) after issuing this statement:

create index ix_desc on products(description) fillfactor 7;

Is thre any statement with numeric clauses? The only one that I found was
Alter/Create Sequence, but there is an ugly list there.

Could anyone help me, please?

Thanks a lot!


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

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

Re: [HACKERS] Problemas with gram.y


[EMAIL PROTECTED] wrote:


Hi,

I'm trying to extend the CREATE INDEX statement with a fillfactor clause. In
Gram.y, I did this:

IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
access_method_clause '(' index_params ')' fillfactor_clause where_clause
{
IndexStmt *n = makeNode(IndexStmt);
   n->unique = $2;
n->idxname = $4;
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
n->fillfactor = $11;
n->whereClause = $12;
$$ = (Node *)n;
}

And the clause:

fillfactor_clause:  
FILLFACTOR IntegerOnly  { $$ = makeInteger($2); }
{ $$ = 0; };

I had to add a new field into IndexStmt (unsigned int fillfactor). Everything
is fine after parsing except that I can't see the integer value. For example,
in transformIndexStmt (analyze.c), I've inspected stmt->fillfactor and I've got
a strange, obviously wrong, value (137616352) after issuing this statement:

create index ix_desc on products(description) fillfactor 7;

Is thre any statement with numeric clauses? The only one that I found was
Alter/Create Sequence, but there is an ugly list there.

Could anyone help me, please?

Thanks a lot!


 



did you change nodes/copyfuncs.c and nodes/equalfuncs.c as you have to 
do when you add new node fields?


cheers

andrew




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

Re: [HACKERS] Problemas with gram.y

2006-03-03 Thread Martijn van Oosterhout

On Fri, Mar 03, 2006 at 07:14:45PM -0200, [EMAIL PROTECTED] wrote:
> Hi,
> 
> I'm trying to extend the CREATE INDEX statement with a fillfactor clause. In
> Gram.y, I did this:



> I had to add a new field into IndexStmt (unsigned int fillfactor). Everything
> is fine after parsing except that I can't see the integer value. For example,
> in transformIndexStmt (analyze.c), I've inspected stmt->fillfactor and I've 
> got
>  a strange, obviously wrong, value (137616352) after issuing this statement:

Did you update infunc/outfuncs/copyfuns in the nodes directory? They're
used to copy the nodes and if you forget to update them you get strange
numbers instead.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [HACKERS] Building Windows Server Extensions Using VC++ 2005

Charles F. I. Savage wrote:
> Hi everyone,
> 
> I've been able to successfully build server extension using Visual 
> Studio 2005 for Windows Postgresql 8.1.  However, it took a few tweaks 
> which I thought I should document (maybe these issues could be fixed in 
> future postgresql versions?):

Sorry for the delay in replying to your issues.

> 1.  There is no lib file for VC++ to link against.  This can be created 
> easily enough by going to src/backend directory and running:
> 
> lib /MACHINE:x86 /NAME:postgres.exe /DEF:postgres.def
> 
> Note the use of /NAME to tell VC++ it is linking against an executable 
> and not a dll.
> 
> It would be nice if this lib file was automatically installed on windows 
> when installing postgresql.

We don't distribute a *.def file in this case because it would be too
hard to keep it up-to-date.  We have *.def files for libpq, but that
changes much less frequently than the backend.  The most reliable
solution is for users to generate the file themselves.

> 2.  Requirement on strings.h
> 
> In c.h:69 there is this code:
> 
> #ifdef HAVE_STRINGS_H
> #include 
> #endif
> 
> 
> In pg_config.h:405 this is defined:
> 
> /* Define to 1 if you have the  header file. */
> #define HAVE_STRINGS_H 1
> 
> However, Visual Studio 2005 does not include this file.  For a 
> workaround I simply added it but that's a bit of hack.

Ah, so even though you are using MSVC, you are using the config file
from MinGW.  Easy fix, included in the patch below, and applied for 8.2.

> 3.  This is a bigger issue, and took a while to figure out. If you try 
> to use the Version 1 calling convention, your function will be called 
> but if you try to do anything with the passed in parameters a 
> segmentation fault will occur.  If you use the Version 0 calling 
> convention things work fine.
> 
> The problem is if you use PG_FUNCTION_INFO_V1 postgres does not see the 
> generated function because it is not exported from the dll and thus 
> assumes the Version 0 calling convention when in fact your function is 
> using Version1.  The problem is in fmgr.h:298
> 
> #define PG_FUNCTION_INFO_V1(funcname) \
> 
> extern  Pg_finfo_record * CppConcat(pg_finfo_,funcname) (void); \
> ...
> 
> For windows to export this function it must be:
> 
> extern __declspec(dllexport) Pg_finfo_record * 
> CppConcat(pg_finfo_,funcname) (void);
> 
> Would it be possible to add a DLLEXPORT macro here to fix this?

Yes, we can add DLLIMPORT (which is our macro for that).

However, looking at why /contrib works for MinGW, I think it is the use
of this from Makefile.shlib:

   $(DLLTOOL) --export-all $(DLLTOOL_DEFFLAGS) --output-def $(NAME).def 
$(OBJS)

I am thinking you should just do the same for MSVC.  Should we add
DLLIMPORT to PG_FUNCTION_INFO_V1()?  It duplicates --export-all but it
seems like the correct solution.

> 4.  Last, and the biggest issue, if my function calls pfree it blows 
> up.  What is happening is that pfree somehow ends up pointing to a 
> random spot in memory - thus when you try to call it you execute invalid 
> code (in fact you never execute pfree at all as far as I can see).  I 
> worked around this by using pgport_pfree which does work.  Haven't a 
> clue why...

The answer is in a comment just above pgport_palloc():

/*
 *  Memory support routines for libpgport on Win32
 *
 *  Win32 can't load a library that DLLIMPORTs a variable
 *  if the link object files also DLLIMPORT the same variable.
 *  For this reason, libpgport can't reference CurrentMemoryContext
 *  in the palloc macro calls.
 *
 *  To fix this, we create several functions here that allow us to
 *  manage memory without doing the inline in libpgport.
 */

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/include/c.h
===
RCS file: /cvsroot/pgsql/src/include/c.h,v
retrieving revision 1.196
diff -c -c -r1.196 c.h
*** src/include/c.h 16 Feb 2006 23:23:50 -  1.196
--- src/include/c.h 3 Mar 2006 17:13:57 -
***
*** 66,72 
  #include 
  #include 
  #include 
! #ifdef HAVE_STRINGS_H
  #include 
  #endif
  #include 
--- 66,73 
  #include 
  #include 
  #include 
! /* Some use MinGW-generated pg_config.h but MSVC for extensions. */
! #if defined(HAVE_STRINGS_H) && !defined(WIN32_CLIENT_ONLY)
  #include 
  #endif
  #include 

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

Re: [HACKERS] PG Extensions: Must be statically linked?

2006-03-03 Thread Andreas Seltenreich

Mark Dilger writes:

> I have had the same concern, though never any hard evidence of a
> problem.  If the C++ functions are wrapped with "extern C", and all
> exceptions caught (perhaps converted into error numbers which are then
> returned from the wrapper functions to the plain-C calling functions),
> are there any remaining known problems?  I have often considered
> making a C++ allocator which wrapped palloc and pfree, so that I could
> then use the STL within the backend...
>
> Has anyone tried this?

I did some experiments on a C++ language handler last year (including
an allocator and a class loader to spare the extern "C"s/name
mangling):




The remaining issue is the impedance mismatch between longjmp()ing and
exceptions.

regards,
Andreas
-- 

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

   http://archives.postgresql.org

Re: [HACKERS] Vertical Partitioning with TOAST

If this would be accepted I might actually be able to accomplish this.
Maybe. :) But having a TODO wouldn't be a bad idea as well...

Would this require 2 new fields in pg_attribute, or is there a better
way to store the thresholds? I'm thinking that each field would need two
special values; 0 for 'no external/compression' and -1 for 'use default'
(hrm, I guess this means we should add at least one GUC to control that
default...)

I suspect there's folks on -general who would express interest if you
want me to ask there...

On Thu, Mar 02, 2006 at 10:15:19PM -0500, Bruce Momjian wrote:
> 
> Is there still interst in this idea for TODO?
> 
> ---
> > As Tom suggested, I think it would be best to be able to change the size
> > at which a field gets stored externally. I think it also makes sense to
> > have this reverse the normal order of compress first, then if it still
> > doesn't fit store it externally. I forsee this typically being useful
> > when you have fields that are between ~100 and 1000 bytes in size, and
> > I'm doubtful that compression would do much good there. But I wouldn't
> > rule out this being useful on fields that can also sometimes contain
> > much larger amounts of data, so I don't think it makes sense to disable
> > compression completely. So, I think this leaves two new options:
> > 
> > SET STORAGE EXTERNAL [THRESHOLD x]
> > If a field is over x in size, it's stored externally.
> > 
> > SET STORAGE EXTENDED [THRESHOLD x]
> > If a field is over x in size, it's stored externally. If it's over
> > BLCKSZ/4 it will also be compressed (I think that's how things work
> > now).
> > 
> > Actually, that's rather ugly. I think it would be better to just break
> > external storage and compression out into their own attributes:
> > 
> > SET STORAGE [ ALLOW EXTERNAL [THRESHOLD x] ] [ ALLOW COMPRESSION [THRESHOLD 
> > x] ]
> > 
> > ALLOW EXTERNAL: if a field is larger than x bytes (BLCKSZ/4 by default)
> > then it will be stored externally. May be specified along with ALLOW
> > COMPRESSION.
> > 
> > ALLOW COMPRESSION: if a field is larger than x bytes (BLCKSZ/4 by default)
> > then it will be COMPRESSED. May be specified along with ALLOW EXTERNAL.
-- 
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 5: don't forget to increase your free space map settings

Re: [HACKERS] [NOVICE] pg_config --pgxs

2006-03-03 Thread George Weaver



- Original Message - 
From: "Tom Lane"



If you're on Windows this probably means that GetShortPathName() is
failing.  I'm not sure what conditions cause that, exactly.  It might be
a good idea if we fixed pg_config to print out the error code rather
than just silently failing.


I think I have figured out what the problem is.

I installed the binary version of 8.1 via the msi-installer which creates 
the PostgreSQL bin directory as "C:\Program Files\PostgreSQL\8.1\bin".


I then downloaded and installed the source code in order to compile and 
install the xml2 contrib module.  When I extracted 
"postgresql-8.1.0.tar.bz2" to the "C:\Program Files\PostgreSQL" directory, 
it creates the directory "C:\Program Files\PostgreSQL\postgresql-8.1.0".  I 
did not need to make and install the entire package from source in order to 
build and install the xml2 module, so the directories remain as above.


When pg_config is run, it's assuming that bindir is "C:/Program 
Files/PostgreSQL/8.1/bin" and starts all its path searches basis this 
directory.


For pgxs, it concatentates the libdir with "/pgxs/src/makefiles/pgxs.mk" so 
its looking for "C:/Program 
Files/PostgreSQL/8.1/lib/pgxs/src/makefiles/pgxs.mk" which doesn't exist. 
In fact under the source tree for 8.1.0 there is no directory "pgxs".  If I 
create a directory "/pgxs/src/makefiles" under "C:/Program 
Files/PostgreSQL/8.1/lib/" and copy pgxs.mk to this directory, pg_config 
finds it correctly:


C:\>pg_config --pgxs
C:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/pgxs.mk

If I made the entire package from source and installed it, would it have 
created the directory "/pgxs/src/makefiles" under "C:/Program 
Files/PostgreSQL/8.1/lib/"?


Regards,
George








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

Re: [HACKERS] Improving free space usage (was: Reducing relation locking overhead)

At the risk of editorializing...

* Allow for tables with more than a certain percent of free space to
  force new tuple allocation in the last few pages to come only from the
  FSM (prefering the earliest pages), with the intention of allowing the
  table to shrink

I also believe there's also a second TODO:

* Allow FSM page hand-out on tables to be influenced by the clustered
  index. This would help maintain cluster order.

On Thu, Mar 02, 2006 at 10:12:59PM -0500, Bruce Momjian wrote:
> 
> Added to TODO:
> 
>   * Allow FSM to return free space toward the beginning of the heap file, 
> in
> hopes that empty pages at the end can be truncated by VACUUM
> 
> 
> ---
> 
> Jim C. Nasby wrote:
> > On Fri, Dec 09, 2005 at 12:00:14AM +0200, Hannu Krosing wrote:
> > > > Along those lines, I've wondered if it makes sense to add more
> > > > flexibility in how free space is reclaimed in a table. One obvious
> > > > possibility is to have a strategy where new tuples will always look to
> > > > the FSM for space (instead of going into the current page if possible),
> > > > and the FSM will always hand out the earliest page in the table it has.
> > > > This mode would have the effect of moving tuples towards the front of
> > > > the table, allowing for space reclamation. A variation might be that
> > > > this mode will not effect tuples that are generated as part of an UPDATE
> > > > and are in the first x% of the table, since it doesn't make sense to
> > > > move a tuple from page 2 to page 1 in a 1000 page table.
> > > 
> > > This % could be depending on some "fill factor" of the table, aiming not
> > > to move tuples, that would end up in the final volume of a balance
> > > table, which, in case of heavily updated table, would probably be 2 to 3
> > > times the volume of densely populated table.
> > > 
> > > > Another possibility is to always go to the FSM and to have the FSM hand
> > > > back the page that is closest to the new tuple according to a certain
> > > > index. This would allow for ALTER TABLE CLUSTER to be much more
> > > > self-maintaining. The downside is that you'd have to do a lookup on that
> > > > index, but presumably if the index is important enough to cluster on
> > > > then it should be well-cached. There's probably some other tweaks that
> > > > could be done as well to make this more performant.
> > > 
> > > Yes, I agree on all your points about better placement of new tuples,
> > > all they would be useful indeed.
> > 
> > Sounds like a TODO, barring objections...
> > -- 
> > 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
> > 
> 
> -- 
>   Bruce Momjian   http://candle.pha.pa.us
>   SRA OSS, Inc.   http://www.sraoss.com
> 
>   + If your life is a hard drive, Christ can be your backup. +
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
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] [NOVICE] pg_config --pgxs

George Weaver <[EMAIL PROTECTED]> writes:
> For pgxs, it concatentates the libdir with "/pgxs/src/makefiles/pgxs.mk" so 
> its looking for "C:/Program 
> Files/PostgreSQL/8.1/lib/pgxs/src/makefiles/pgxs.mk" which doesn't exist. 
> ...
> If I made the entire package from source and installed it, would it have 
> created the directory "/pgxs/src/makefiles" under "C:/Program 
> Files/PostgreSQL/8.1/lib/"?

Yes, it should have.  I think the issue here is that GetShortPathName
insists on the target path actually existing, whereas the Unix
environment imposes no such requirement.

Not sure if we should try to do anything about this --- if the file is
not there, it isn't going to help a lot for pg_config to print out where
it should have been, so really there's not much functionality loss
involved here.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for

On Fri, Mar 03, 2006 at 07:50:51AM -0800, Luke Lonergan wrote:
> Peter,
> 
> I'm asking our performance lead, Ayush Parashar, to develop a talk proposal
> that will discuss performance of Postgres, including enhancements like the
> on-disk bitmap index, sort improvements, etc.  We'd also like to discuss the
> business intelligence use-cases and where parallelism is applicable.
> 
> Where would such a talk fit in the program?

On a related note, I'm wondering if there's any non-technical
business-oriented things folks would be interested in hearing about,
perhaps stuff relating to corporate support and use of PostgreSQL.
-- 
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] Problemas with gram.y

[EMAIL PROTECTED] writes:
> I'm trying to extend the CREATE INDEX statement with a fillfactor
> clause.

Um, are you aware that a patch for that was already submitted?
http://momjian.postgresql.org/cgi-bin/pgpatches

I find the whole idea pretty ugly myself.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Foreign keys for non-default datatypes

BTW, I had another thought about this: if we go this way, then the plans
associated with RI check queries would essentially always be trivial
index lookups (for everything except RI_Initial_Check).  It'd be within
the realm of feasibility to throw away the current cached-plan RI
infrastructure and simply do direct indexscans, in the style that we
currently use for most system-catalog accesses.  I have never done any
performance testing or profiling of routine foreign-key check operations
but I should think that this would be really significantly faster ---
and it'd let us get rid of some ugly warts that we've had to plaster
onto SPI and the executor to support RI semantics, such as the
"crosscheck snapshot" cruft.

If we did this then RI checks would no longer be subvertible by rules or
user triggers.  Although I've been heard to argue that that's a feature,
I think the majority of people feel it's a bug, and wouldn't be sorry to
see it go.

Comments?

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] Foreign keys for non-default datatypes

On Fri, 3 Mar 2006, Tom Lane wrote:

> BTW, I had another thought about this: if we go this way, then the plans
> associated with RI check queries would essentially always be trivial
> index lookups (for everything except RI_Initial_Check).  It'd be within
> the realm of feasibility to throw away the current cached-plan RI
> infrastructure and simply do direct indexscans, in the style that we
> currently use for most system-catalog accesses.

Would we have to do anything odd if we want to be testing only some of the
index columns and possibly not in the index order (like match partial
where some of the fk side is null)?  I don't honestly see us doing match
partial any time soon, but I'd like to have an idea of what'd be involved.

> If we did this then RI checks would no longer be subvertible by rules or
> user triggers.

I don't think that it'd really help because it's the actions that are
generally subvertible not the checks and since those are looking at the
potentially not indexed fk side, I don't think the above would apply.

---(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] Foreign keys for non-default datatypes

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 3 Mar 2006, Tom Lane wrote:
>> BTW, I had another thought about this: if we go this way, then the plans
>> associated with RI check queries would essentially always be trivial
>> index lookups (for everything except RI_Initial_Check).

> Would we have to do anything odd if we want to be testing only some of the
> index columns and possibly not in the index order (like match partial
> where some of the fk side is null)?  I don't honestly see us doing match
> partial any time soon, but I'd like to have an idea of what'd be involved.

Match partial would be an index lookup with a subset of the keys, which
btree at least is fine with.  You could argue that a "sufficiently
partial" match would be better done as a seqscan, but I think we could
just bull ahead and do it as indexscans always ...

>> If we did this then RI checks would no longer be subvertible by rules or
>> user triggers.

> I don't think that it'd really help because it's the actions that are
> generally subvertible not the checks and since those are looking at the
> potentially not indexed fk side, I don't think the above would apply.

Oh, right, we'd probably still need to do planning in that case.  Unless
we wanted to insist on having an FK-side index too for every FK, which
is something I'm not for.

Do you think it's worth redoing the implementation of just the PK checks?

regards, tom lane

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

Re: [HACKERS] Automatic free space map filling

2006-03-03 Thread Matthew T. O'Connor


Csaba Nagy wrote:

So he rather needs Hannu Krosing's patch for simultaneous vacuum ...



Well, I guess that would be a good solution to the "queue table"
problem. The problem is that I can't deploy that patch on our production
systems without being fairly sure it won't corrupt any data... and I
can't rely on non-production testing either. Basically I'm waiting to
see Tom saying it will fly :-)


That patch is a step forward if it's deemed OK by the powers that be.  
However, autovacuum would still need to be taught to handle simultaneous 
vacuums.  I suppose that in the interim, you could disable autovacuum 
for the problematic queue table and have cron issue a manual vacuum 
command for that table at the required frequency.


Anyone up for working on / testing / improving Hannu's patch?  I think 
it's beyond my skill set.


Matt


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

Re: [HACKERS] Foreign keys for non-default datatypes

On Fri, 3 Mar 2006, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Fri, 3 Mar 2006, Tom Lane wrote:
> >> BTW, I had another thought about this: if we go this way, then the plans
> >> associated with RI check queries would essentially always be trivial
> >> index lookups (for everything except RI_Initial_Check).
>
> > Would we have to do anything odd if we want to be testing only some of the
> > index columns and possibly not in the index order (like match partial
> > where some of the fk side is null)?  I don't honestly see us doing match
> > partial any time soon, but I'd like to have an idea of what'd be involved.
>
> Match partial would be an index lookup with a subset of the keys, which
> btree at least is fine with.  You could argue that a "sufficiently
> partial" match would be better done as a seqscan, but I think we could
> just bull ahead and do it as indexscans always ...

So at that level, not having leading columns is okay, so we wouldn't have
an issue with this (as opposed to if we planned such a statement, where it
wouldn't choose to use the index)?

> >> If we did this then RI checks would no longer be subvertible by rules or
> >> user triggers.
>
> > I don't think that it'd really help because it's the actions that are
> > generally subvertible not the checks and since those are looking at the
> > potentially not indexed fk side, I don't think the above would apply.
>
> Oh, right, we'd probably still need to do planning in that case.  Unless
> we wanted to insist on having an FK-side index too for every FK, which
> is something I'm not for.
>
> Do you think it's worth redoing the implementation of just the PK checks?

I'd say it's worth trying.  If it's probably faster, and it actually
sounds like it might be cleaner code than the way the statements are
built, even if it's another, different way of doing it.

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

Re: [HACKERS] Automatic free space map filling

"Matthew T. O'Connor"  writes:
> That patch is a step forward if it's deemed OK by the powers that be.  
> However, autovacuum would still need to be taught to handle simultaneous 
> vacuums.  I suppose that in the interim, you could disable autovacuum 
> for the problematic queue table and have cron issue a manual vacuum 
> command for that table at the required frequency.

I'm not sure you should think of that as an "interim" solution.  I don't
really like the idea of multiple autovacuums running concurrently.  ISTM
autovac is intended to be something that lurks in the background and
doesn't take up an unreasonable percentage of your system bandwidth ...
but if there's more than one of them, it's going to be mighty hard to
control the overall load penalty.  Plus you have to worry about keeping
them off each others' backs, ie, not all trying to vac the same table at
once.  And in a scenario like Csaba's, I think the hotspot tables are
just exactly what they'd all try to vacuum.

For small hotspot tables I think a scheduled vacuum process is just the
thing, whereas autovac is more of a free-lance thing to keep the rest of
your DB in line.

regards, tom lane

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

Re: [HACKERS] Foreign keys for non-default datatypes

2006-03-03 Thread Michael Glaesemann



On Mar 4, 2006, at 9:22 , Tom Lane wrote:

If we did this then RI checks would no longer be subvertible by  
rules or
user triggers.  Although I've been heard to argue that that's a  
feature,
I think the majority of people feel it's a bug, and wouldn't be  
sorry to

see it go.


Just to check, are you referring to constraint triggers here? (I  
don't believe so, as I've been using constraint triggers in cases  
other than referential integrity and as I read it, you're only  
referring to RI.) Currently constraint triggers are the only way to  
defer constraint checking to the end of a transaction. Until we have  
some kind of support for multiple updates in a single statement (or  
perhaps deferred constraints, but I'd prefer the former), I'd like  
constraint triggers to hang around.


Michael Glaesemann
grzm myrealbox com




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

Re: [HACKERS] Automatic free space map filling

2006-03-03 Thread Matthew T. O'Connor


Tom Lane wrote:

"Matthew T. O'Connor"  writes:
  
That patch is a step forward if it's deemed OK by the powers that be.  
However, autovacuum would still need to be taught to handle simultaneous 
vacuums.  I suppose that in the interim, you could disable autovacuum 
for the problematic queue table and have cron issue a manual vacuum 
command for that table at the required frequency.



I'm not sure you should think of that as an "interim" solution.  I don't
really like the idea of multiple autovacuums running concurrently.  ISTM
autovac is intended to be something that lurks in the background and
doesn't take up an unreasonable percentage of your system bandwidth ...
but if there's more than one of them, it's going to be mighty hard to
control the overall load penalty.  Plus you have to worry about keeping
them off each others' backs, ie, not all trying to vac the same table at
once.  And in a scenario like Csaba's, I think the hotspot tables are
just exactly what they'd all try to vacuum.

For small hotspot tables I think a scheduled vacuum process is just the
thing, whereas autovac is more of a free-lance thing to keep the rest of
your DB in line.


While I agree that given the current state of affairs the cron solution 
is elegant, I personally want autovac to solve all of our vacuuming 
needs, I really dislike the idea of requiring a cron based solution to 
solve a fairly typical problem.  Besides the cron solution is sloppy, it 
blindly vacuums whether it's needed or not resulting in a net increase 
of cycles spent vacuuming.


Anyway, I don't know the best way to implement it but I wasn't thinking 
of just firing off multiple autovac processes.  I was envisioning 
something like an autovacuum master process that launches (forks?) 
VACUUM commands and has some smarts about how many processes to fire 
off, or that it would only fire off simultaneous VACUUMS for tables that 
have been flagged as hot spot tables.


I recognize that teaching autovac to handle simultaneous VACUUM's in a 
sane way will require a quantum leap of complexity but it still seems a 
better long term solution.  I would agree that using cron makes sense if 
we were seeing lots of different scenarios that we couldn't possibly 
anticipate, but I don't think that is where we are.


BTW, this discussion is only relevant if we allow simultaneous vacuum.  
Is this something you see as inevitable whether or not you think Hannu's 
implementation is acceptable.


Matt


---(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] Automatic free space map filling

On Thu, Mar 02, 2006 at 10:05:28AM -0500, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > ??hel kenal p??eval, N, 2006-03-02 kell 09:53, kirjutas Zeugswetter
> > Andreas DCP SD:
> >> Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead
> >> tuple by reducing the tuple to it's header info.
> 
> Andreas' idea is possibly doable but I am not sure that I see the point.
> It does not reduce the need for vacuum nor the I/O load imposed by
> vacuum.  What it does do is bias the system in the direction of
> allocating an unreasonably large number of tuple line pointers on a page
> (ie, more than are useful when the page is fully packed with normal
> tuples).  Since we never reclaim such pointers, over time all the pages
> in a table would tend to develop line-pointer-bloat.  I don't know what
> the net overhead would be, but it'd definitely impose some aggregate
> inefficiency.

What would be involved in reclaiming item pointer space? Is there any
reason it's not done today? (I know I've been bit once by this...)
-- 
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] PostgreSQL Anniversary Summit, Call for

2006-03-03 Thread Ned Lilly

I'm interested in such a discussion, at least informally.  I'd probably 
even sit politely and listen to a presentation ;-)



Jim C. Nasby wrote:

On Fri, Mar 03, 2006 at 07:50:51AM -0800, Luke Lonergan wrote:

Peter,

I'm asking our performance lead, Ayush Parashar, to develop a talk proposal
that will discuss performance of Postgres, including enhancements like the
on-disk bitmap index, sort improvements, etc.  We'd also like to discuss the
business intelligence use-cases and where parallelism is applicable.

Where would such a talk fit in the program?


On a related note, I'm wondering if there's any non-technical
business-oriented things folks would be interested in hearing about,
perhaps stuff relating to corporate support and use of PostgreSQL.


---(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] Automatic free space map filling

On Thu, Mar 02, 2006 at 03:19:46PM +0100, Martijn van Oosterhout wrote:
> Note, for this purpose you don't need to keep a bit per page. The
> OS I/O system will load 64k+ (8+ pages) in one go so one bit per 8
> pages would be sufficient.

AFAIK that's entirely dependant on the filesystem and how it's created
(and possibly the OS as well). So arbitrarily deciding each bit is 8
pages is a bad idea. I could see allowing for a setting that determins
how many pages per bit, though, but I think we're also getting ahead of
ourselves.
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] Automatic free space map filling

On Fri, Mar 03, 2006 at 04:14:41PM +0100, Csaba Nagy wrote:
> > Ewe.  How expensive is scanning an index compared to the heap?  Does
> > anyone have figure on that in terms of I/O and time?
> 
> See this post for an example:
> http://archives.postgresql.org/pgsql-performance/2006-02/msg00416.php
> 
> For my 200 million table, scanning the pk index took ~ 4 hours. And then
> there are some more indexes...
> 
> So if the index has to be scanned completely, that's still too much.

But how does a scan of the index compare to a scan of the table? For
example, if indexes are 1/5th the size of the table, you can
(theoretically) scan 5 indexes in the same amount of time it takes to
scan the heap. That indicates to me that even if we did have to scan all
indexes, a dirty page bitmap would still be a win over the current
situation. But it appears that it should be safe to do index lookups on
indexes that aren't expressions. And I believe that we could take steps
down the road to allow for index lookups on indexes that only used
functions that were known to be safe.
-- 
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] Automatic free space map filling

On Fri, Mar 03, 2006 at 11:37:00AM -0500, Tom Lane wrote:
> Bruce and I were discussing this the other day; it'd be pretty easy to
> make plain VACUUM start a fresh transaction immediately after it
> finishes a scan heap/clean indexes/clean heap cycle.  The infrastructure
> for this (in particular, session-level locks that won't be lost by
> closing the xact) is all there.  You'd have to figure out how often to
> start a new xact ... every cycle is probably too often, at least for
> smaller maintenance_work_mem settings ... but it'd not be hard or

If maintenance_work_mem is small you're likely to have poor performance
anyway; I'm suspicious that the overhead of starting a new xact would be
all that important. If you care about performance, you'll probably have
increased maintenance_work_mem anyway.
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] ACCESS EXCLUSIVE LOCK

You have a URL for where in the docs it says that? Sounds like it needs
to be updated to include TRUNCATE.

On Wed, Mar 01, 2006 at 03:55:26PM -0800, [EMAIL PROTECTED] wrote:
> This may be a newbie question, but according to the 7.4 docs, an ACCESS
> EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE,
> REINDEX, CLUSTER, and VACUUM FULL commands.
> 
> However, when viewing pg_locks during the execution of a stored
> procedure that does not perform any of the above commands, I see that
> the table it is working on is locked by ACCESS EXCLUSIVE.
> 
> I have also tested that I can not perform a simple SELECT on the locked
> table while the SP is executing.
> 
> Can anyone provide some insight?
> 
> Thanks
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 

-- 
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] ACCESS EXCLUSIVE LOCK

2006-03-03 Thread Michael Fuhr

On Fri, Mar 03, 2006 at 08:47:41PM -0600, Jim C. Nasby wrote:
> You have a URL for where in the docs it says that? Sounds like it needs
> to be updated to include TRUNCATE.

http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES

It doesn't say that only the listed commands acquire ACCESS EXCLUSIVE,
just that certain commands do.  TRUNCATE isn't shown.

-- 
Michael Fuhr

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

Re: [HACKERS] Automatic free space map filling

2006-03-03 Thread Christopher Browne

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Zeugswetter Andreas 
DCP SD") would write:
>> > But what about index clearing?  When do you scan each index?
>> 
>> At the end of each iteration (or earlier, depending on 
>> maintenance_work_mem).  So for each iteration you would need 
>> to scan the indexes.
>> 
>> Maybe we could make maintenance_work_mem be the deciding 
>> factor; after scanning the indexes, do the release/reacquire 
>> locks cycle.
>
> But you could do the indexes first and remember how far you can 
> vacuum the heap later.

But the indexes _can't_ be done first; you _first_ need to know which
tuples are dead, which requires looking at the table itself.
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/languages.html
Pound for pound, the amoeba is the most vicious animal on earth.

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

   http://archives.postgresql.org

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

2006-03-03 Thread Christopher Browne

A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Greg Stark) 
wrote:
> Are you sure that "Temporary Resident Visa" is what you need? 
> Isn't the regular visa people get called just a "Guest Visa"?

That's possible too...  It is probably a good idea to contact a local
Canadian embassy to see what they think will be required.  This may be
"tilting over windmills" a bit.  

Checking early is still a good idea, after all, if you haven't got a
passport, it may take some time to get that.
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
"Python's minimalism is attractive to people  who like minimalism.  It
is decidedly unattractive to people who  see Python's minimalism as an
exercise in masochism." -- Peter Hickman, comp.lang.ruby

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

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

Re: [HACKERS] Improving free space usage (was: Reducing relation locking

Jim C. Nasby wrote:
> At the risk of editorializing...
> 
> * Allow for tables with more than a certain percent of free space to
>   force new tuple allocation in the last few pages to come only from the
>   FSM (prefering the earliest pages), with the intention of allowing the
>   table to shrink

That seems too confusing.

> I also believe there's also a second TODO:
> 
> * Allow FSM page hand-out on tables to be influenced by the clustered
>   index. This would help maintain cluster order.

How would FSM know how to do that?  I added it with a question mark.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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] Problemas with gram.y

2006-03-03 Thread Jaime Casanova

On 3/3/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] writes:
> > I'm trying to extend the CREATE INDEX statement with a fillfactor
> > clause.
>
> Um, are you aware that a patch for that was already submitted?
> http://momjian.postgresql.org/cgi-bin/pgpatches
>
> I find the whole idea pretty ugly myself.
>
>   regards, tom lane
>

why? if i can ask? you didn't seem upset with that in the thread

--
regards,
Jaime Casanova

"What they (MySQL) lose in usability, they gain back in benchmarks, and that's
all that matters: getting the wrong answer really fast."
   Randal L. Schwartz

---(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] Problemas with gram.y