Re: [HACKERS] Just-in-time Background Writer Patch+Test Results

[Greg Smith]

On Sat, 8 Sep 2007, Greg Smith wrote:

Here's the results I got when I pushed the time down significantly from the 
defaults

info  | set | tps  | cleaner_pct
---+-+--+-
jit multiplier=1.0 scan_whole=120s delay=20ms |  20 |  956 |   92.34
jit multiplier=2.0 scan_whole=120s delay=20ms |  21 |  967 |   99.94

jit multiplier=1.5 scan_whole=120s delay=10ms |  22 |  944 |   97.91
jit multiplier=2.0 scan_whole=120s delay=10ms |  23 |  981 |99.7
It seems I have to push the multiplier higher to get good results when using 
a much lower interval


Since I'm not exactly overwhelmed processing field reports, I've continued 
this line of investigation myself...increasing the multiplier to 3.0 got 
me another nine on the buffers written by the LRU BGW without a 
significant change in performance:


 info  | set | tps  | cleaner_pct
---+-+--+-
jit multiplier=3.0 scan_whole=120s delay=10ms  |  24 |  967 | 99.95

After thinking for a bit about why the 10ms case wasn't working so well 
without a big multiplier, I considered that the default moving average 
smoothing makes the sample period operating over such a short period of 
time (10ms * 16=160ms) that it's unlikely to cover a typical pause that 
one might want to smooth over.  My initial thinking was to increase the 
period of the smoothing so that it's of similar length to the default case 
even when the interval goes down, but that didn't really improve anything 
(note that the 16 case here is the default setup with just the delay at 
10ms, which was a missing piece of data from the above as well--I only 
tested with larger multipliers above at 10ms):


 info | set | tps  | cleaner_pct
--+-+--+-
 jit multiplier=1.0 delay=10ms smoothing=16   |  27 |  982 |  89.4
 jit multiplier=1.0 delay=10ms smoothing=64   |  26 |  946 |  89.55
 jit multiplier=1.0 delay=10ms smoothing=320  |  25 |  970 |  89.53

What I realized is that after rounding the number of buffers to an 
integer, dividing a very short period of activity by the smoothing 
constant was resulting in the smoothing value usually dropping to 0 and 
not doing much.  This made me wonder how much the weighted average 
smoothing was really doing in the default case.  I put that code in months 
ago and I hadn't looked recently at its effectiveness.  Here's a 
comparison:


 info | set | tps  | cleaner_pct
--+-+--+-
 jit multiplier=1.0 delay=200ms smoothing=16  |  18 |  970 |  99.99
 jit multiplier=1.0 delay=200ms smoothing=off |  28 |  957 |  97.16

All this data support my suggestion that the exact value of the smoothing 
period constant isn't really a critical one.  It appears moderately 
helpful to have that logic on in some cases and the default value doesn't 
seem to hurt the cases where I'd expect it to be the least effective. 
Tuning the multiplier is much more powerful and useful than ever touching 
this constant.  I could probably even pull the smoothing logic out 
altogether, at the cost of increasing the burden of correctly tuning the 
multiplier on the administrator.  So far it looks like it's reasonable 
instead to leave it as an untunable to help the default configuration, and 
I'll just add a documentation note that if you decrease the interval 
you'll probably have to increase the multiplier.


After going through this, the extra data gives more useful baselines to do 
a similar sensitivity analysis of the other item that's untunable in the 
current patch:


float   scan_whole_pool_seconds = 120.0;

But I'll be travelling for the next week and won't have time to look into 
that myself until I get back.


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

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] Open issues for HOT patch

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> The problem is you can't prune anymore once you have existing pin on the
> >> target page.  I'd really like to get around that, but so far it seems
> >> unacceptably fragile --- the executor really doesn't expect tuples to
> >> get moved around underneath it.
> 
> > I thought you could do the pruning before you pin the page only in
> > update/insert cases.
> 
> But then what happens when you want to update a second tuple on the same
> page?  None of our existing plan types release and reacquire pin if they
> don't have to, and I really doubt that we want to give up that
> optimization.

You will prune when you lock the page and at that point unless you got
enough room for both tuples I doubt trying just before the second tuple
is going to help.

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

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

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

Re: [HACKERS] Raw device I/O for large objects

[Luke Lonergan]

Index organized tables would do this and it would be a generic capability.

- Luke

Msg is shrt cuz m on ma treo

 -Original Message-
From:   Georgi Chulkov [mailto:[EMAIL PROTECTED]
Sent:   Monday, September 17, 2007 11:50 PM Eastern Standard Time
To: Tom Lane
Cc: pgsql-hackers@postgresql.org
Subject:Re: [HACKERS] Raw device I/O for large objects

Hi,

> We've heard this idea proposed before, and it's been shot down as a poor
> use of development effort every time.  Check the archives for previous
> threads, but the basic argument goes like this: when Oracle et al did
> that twenty years ago, it was a good idea because (1) operating systems
> tended to have sucky filesystems, (2) performance and reliability
> properties of same were not very consistent across platforms, and (3)
> being large commercial software vendors they could afford to throw lots
> of warm bodies at anything that seemed like a bottleneck.  None of those
> arguments holds up well for us today however.  If you think you want to
> reimplement a filesystem you need to have some pretty concrete reasons
> why you can outsmart all the smart folks who have worked on
> your-favorite-OS's filesystems for lo these many years.  There's also
> the fact that on any reasonably modern disk hardware, "raw I/O" is
> anything but.

Thanks, I agree with all your arguments.

Here's the reason why I'm looking at raw device storage for large objects only 
(as opposed to all tables): with raw device I/O I can control, to an extent, 
spatial locality. So, if I have an application that wants to store N large 
objects (totaling several gigabytes) and read them back in some order that is 
well-known in advance, I could store my large objects in that order on the 
raw device.* Sequentially reading them back would then be very efficient. 
With a file system underneath, I don't have that freedom. (Such a scenario 
occurs with raster databases, for example.)

* assuming I have a way to communicate these requirements; that's a whole new 
problem

Please allow me to ask then:
1. In your opinion, would the above scenario indeed benefit from a raw-device 
interface for large objects?
2. How feasible it is to decouple general table storage from large object 
storage?

Thank you for your time,

Georgi

---(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] Raw device I/O for large objects

[Georgi Chulkov]

Hi,

> We've heard this idea proposed before, and it's been shot down as a poor
> use of development effort every time.  Check the archives for previous
> threads, but the basic argument goes like this: when Oracle et al did
> that twenty years ago, it was a good idea because (1) operating systems
> tended to have sucky filesystems, (2) performance and reliability
> properties of same were not very consistent across platforms, and (3)
> being large commercial software vendors they could afford to throw lots
> of warm bodies at anything that seemed like a bottleneck.  None of those
> arguments holds up well for us today however.  If you think you want to
> reimplement a filesystem you need to have some pretty concrete reasons
> why you can outsmart all the smart folks who have worked on
> your-favorite-OS's filesystems for lo these many years.  There's also
> the fact that on any reasonably modern disk hardware, "raw I/O" is
> anything but.

Thanks, I agree with all your arguments.

Here's the reason why I'm looking at raw device storage for large objects only 
(as opposed to all tables): with raw device I/O I can control, to an extent, 
spatial locality. So, if I have an application that wants to store N large 
objects (totaling several gigabytes) and read them back in some order that is 
well-known in advance, I could store my large objects in that order on the 
raw device.* Sequentially reading them back would then be very efficient. 
With a file system underneath, I don't have that freedom. (Such a scenario 
occurs with raster databases, for example.)

* assuming I have a way to communicate these requirements; that's a whole new 
problem

Please allow me to ask then:
1. In your opinion, would the above scenario indeed benefit from a raw-device 
interface for large objects?
2. How feasible it is to decouple general table storage from large object 
storage?

Thank you for your time,

Georgi

---(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] Open issues for HOT patch

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The problem is you can't prune anymore once you have existing pin on the
>> target page.  I'd really like to get around that, but so far it seems
>> unacceptably fragile --- the executor really doesn't expect tuples to
>> get moved around underneath it.

> I thought you could do the pruning before you pin the page only in
> update/insert cases.

But then what happens when you want to update a second tuple on the same
page?  None of our existing plan types release and reacquire pin if they
don't have to, and I really doubt that we want to give up that
optimization.

regards, tom lane

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

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

Re: [HACKERS] Open issues for HOT patch

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > If we only prune on an update (or insert) why not just do prune every
> > time?
> 
> The problem is you can't prune anymore once you have existing pin on the
> target page.  I'd really like to get around that, but so far it seems
> unacceptably fragile --- the executor really doesn't expect tuples to
> get moved around underneath it.

I thought you could do the pruning before you pin the page only in
update/insert cases.

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

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

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

   http://archives.postgresql.org

Re: [HACKERS] SPI access to PostgreSQL query plan

[Tom Lane]

Michael Glaesemann <[EMAIL PROTECTED]> writes:
> AIUI, the stress is on the *can*, with a meaning of "may", right? Not  
> all SQL functions can be inlined.

In particular, I think the OP was thinking of a function returning set,
which we currently don't inline at all.  I believe this is doable, but
perhaps not entirely trivial.  I seem to recall looking into it a year
or two ago, and hitting some roadblocks that I can't remember the
details of ...

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Open issues for HOT patch

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> If we only prune on an update (or insert) why not just do prune every
> time?

The problem is you can't prune anymore once you have existing pin on the
target page.  I'd really like to get around that, but so far it seems
unacceptably fragile --- the executor really doesn't expect tuples to
get moved around underneath it.

regards, tom lane

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

Re: [HACKERS] SPI access to PostgreSQL query plan

[Michael Glaesemann]

On Sep 17, 2007, at 19:46 , Florian G. Pflug wrote:

Thats only holds true for functions in languages other than pl/sql  
(Which is
*not* the same as pl/pgsql) - SQL functions can be inlined by the  
executor, and
then are subject to the usual optimizations. (So they essentially  
behave like

views).


AIUI, the stress is on the *can*, with a meaning of "may", right? Not  
all SQL functions can be inlined.


Michael Glaesemann
grzm seespotcode net



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

Re: [HACKERS] Raw device I/O for large objects

[Tom Lane]

Georgi Chulkov <[EMAIL PROTECTED]> writes:
> I am looking into implementing raw device I/O for large objects into 
> PostgreSQL (maybe for all storage, I'm not sure which would be 
> easier/better).

We've heard this idea proposed before, and it's been shot down as a poor
use of development effort every time.  Check the archives for previous
threads, but the basic argument goes like this: when Oracle et al did
that twenty years ago, it was a good idea because (1) operating systems
tended to have sucky filesystems, (2) performance and reliability
properties of same were not very consistent across platforms, and (3)
being large commercial software vendors they could afford to throw lots
of warm bodies at anything that seemed like a bottleneck.  None of those
arguments holds up well for us today however.  If you think you want to
reimplement a filesystem you need to have some pretty concrete reasons
why you can outsmart all the smart folks who have worked on
your-favorite-OS's filesystems for lo these many years.  There's also
the fact that on any reasonably modern disk hardware, "raw I/O" is
anything but.

My opinion is that there is lots of lower-hanging fruit elsewhere.
You can find some ideas on our TODO list, or troll the pghackers
list archives for other discussions.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] Open issues for HOT patch

[Bruce Momjian]

Tom Lane wrote:
> * We also need to think harder about when to invoke the page pruning
> code.  As the patch stands, if you set a breakpoint at
> heap_page_prune_opt it'll seem to be hit constantly (eg, once for every
> system catalog probe), which seems uselessly often.  And yet it also
> seems not often enough, because one thing I found out real fast is that
> the "prune if free space < 1.2 average tuple size" heuristic fails badly
> when you look at queries that execute multiple updates within the same
> heap page.  We only prune when we first pin a particular target page,
> and so the additional updates don't afford another chance to see if it's
> time to prune.
> 
> I'd like to see if we can arrange to only do pruning when reading a page
> that is known to be an update target (ie, never during plain SELECTs);
> I suspect this would be relatively easy with some executor and perhaps
> planner changes.  But that only fixes the first half of the gripe above;
> I'm not at all sure what to do about the multiple-updates-per-page
> issue.

If we only prune on an update (or insert) why not just do prune every
time?  I figure the prune/defrag has to be lighter than the
update/insert itself.

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

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

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

[HACKERS] Open issues for HOT patch

[Tom Lane]

I have finished a first review pass over all of the HOT patch
(updated code is posted on -patches).  I haven't found any showstoppers,
but there seem still several areas that need discussion:

* The patch makes undocumented changes that cause autovacuum's decisions
to be driven by total estimated dead space rather than total number of
dead tuples.  Do we like this?  What should happen to the default
threshold parameters (they are not even in the same units as before...)?
Is there any value in even continuing to track dead tuple counts, per
se, in the pgstats machinery?  It seems redundant/expensive to track
both tuple counts and byte counts, and it's not like the size of the
stats file is not already known to be a performance issue ...

* I'm still pretty unhappy about the patch's use of a relcache copy of
GetAvgFSMRequestSize()'s result.  The fact that there's no provision for
ever updating the value while the relcache entry lives is part of it,
but the bigger part is that I'd rather not have anything at all
depending on that number.  FSM in its current form desperately needs to
die; and once it's replaced by some form of distributed on-disk storage,
it's unlikely that we will have any simple means of getting an
equivalent number.  The average request size was never meant for
external use anyway, but only as a filter to help reject useless entries
from getting into the limited shared-memory FSM space.  Perhaps we could
replace that heuristic with something that is page-local; seems like
dividing the total used space by the number of item pointers would give
at least a rough approximation of the page's average tuple size.

* We also need to think harder about when to invoke the page pruning
code.  As the patch stands, if you set a breakpoint at
heap_page_prune_opt it'll seem to be hit constantly (eg, once for every
system catalog probe), which seems uselessly often.  And yet it also
seems not often enough, because one thing I found out real fast is that
the "prune if free space < 1.2 average tuple size" heuristic fails badly
when you look at queries that execute multiple updates within the same
heap page.  We only prune when we first pin a particular target page,
and so the additional updates don't afford another chance to see if it's
time to prune.

I'd like to see if we can arrange to only do pruning when reading a page
that is known to be an update target (ie, never during plain SELECTs);
I suspect this would be relatively easy with some executor and perhaps
planner changes.  But that only fixes the first half of the gripe above;
I'm not at all sure what to do about the multiple-updates-per-page
issue.

Comments?

regards, tom lane

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

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

Re: [HACKERS] Raw device I/O for large objects

[Sibte Abbas]

On 9/17/07, Georgi Chulkov <[EMAIL PROTECTED]> wrote:
>
> Could someone please point me to the right places to look at, and how/where to
> get started? Would such a development be useful at all? Is anyone working on
> anything related?
>
> Any feedback / information would be highly appreciated!
>

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

The postgresql documentation:
http://www.postgresql.org/docs/8.2/interactive/index.html

Also, If you have the source, the src/tools/backend directory has some
useful material for starters.

regards,
--
Sibte Abbas

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

[HACKERS] Raw device I/O for large objects

[Georgi Chulkov]

Hello,

I am a graduate student of computer science and I have been looking at 
PostgreSQL for my master's thesis work.

I am looking into implementing raw device I/O for large objects into 
PostgreSQL (maybe for all storage, I'm not sure which would be 
easier/better). I am extremely new to the codebase, however.

Could someone please point me to the right places to look at, and how/where to 
get started? Would such a development be useful at all? Is anyone working on 
anything related?

Any feedback / information would be highly appreciated!

Thanks,
Georgi

---(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] SPI access to PostgreSQL query plan

[Florian G. Pflug]

Cristiano Duarte wrote:

2007/9/17, Tom Lane <[EMAIL PROTECTED]>:

"Cristiano Duarte" <[EMAIL PROTECTED]> writes:
Is there a way to have access to PostgreSQL query plan and/or predicates 
inside a function using spi (or any other way)?

No.

Hi Tom,


"No" means: there is no way since the query plan is stored in a 
secret/safe/protected/non-visible/fort-knox like place :) or it means, there

is no friendly way to do it with spi or casual c language programming?


"No" as in: You function is not told by the executor which filters are applied
to it's results, and since it might be called multiple times within a query you
cannot figure that out yourself, even if you somehow got hold of the currently
executed plan.

So unless you start to hack the executor in serious ways, you'll either have to
pass the filter condition manually to your function, or live with it producing
unnecessary output rows.

Thats only holds true for functions in languages other than pl/sql (Which is
*not* the same as pl/pgsql) - SQL functions can be inlined by the executor, and
then are subject to the usual optimizations. (So they essentially behave like
views).

greetings, Florian Pflug


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

Re: [HACKERS] errcontext function

[Guillaume Lelarge]

Tom Lane a écrit :

Guillaume Lelarge <[EMAIL PROTECTED]> writes:

I wonder why all messages going through errcontext function are not
translatable.


I don't think it's errcontext's fault.  The PLs in general don't have
any translation coverage.  This seems a bit difficult to fix: I don't
think we'd wish to put their messages into the main backend's message
domain, but AFAICT gettext can only support one active domain at
a time.



OK, I understand quite well. Thanks for your answer.

Regards.


--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com

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

Re: [HACKERS] database diagram

[Andrew Hammond]

Wrong list. Try asking on the pgAdmin3 list, or maybe pgsql-general.

Andrew


On 9/16/07, Eretna Evrengizid <[EMAIL PROTECTED]> wrote:
>
>
> hi,
>
> I use postgresql 8.2 and I cant see database diagram on pgAdmin III,  I
> think it can be . it is there , isnt it  ?
>
>
>
> --
> Be a better Heartthrob. Get better relationship answers
> from
> someone who knows.
> Yahoo! Answers - Check it out.
>

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Markus Schiltknecht]

Hi,

Tom Lane wrote:

Well, you're probably fine with integers and text, but beyond that it
gets a bit more dicey.  I wouldn't want to assume that floats are
compatible across any random pair of architectures, and in the more
complex datatypes (such as arrays or geometric types) there might be
some impact from alignment rules.  (Or not, I'm too lazy to go look at
the moment.)


Okay, thank you for the hints, I'll go figure out.

Regards

Markus

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

[HACKERS] Timezones change - never ending story

[Zdenek Kotala]

Just for information. Venezuela is going to have new timezone change 
(30minutes shift) on this weekend. This change is not yet integrated in 
the last version in Olson database. (Original announcement said it 
happens on 1.1.2008)


More info:

http://www.reuters.com/article/oddlyEnoughNews/idUSN2328980320070824


Zdenek

PS: Can we add information about Olson database version somewhere (e.g 
Readme in src/timezone directory, or in Release notes?




---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] SPI access to PostgreSQL query plan

[Cristiano Duarte]

2007/9/17, Tom Lane <[EMAIL PROTECTED]>:
>
> "Cristiano Duarte" <[EMAIL PROTECTED]> writes:
> > Is there a way to have access to PostgreSQL query plan and/or predicates
> > inside a function using spi (or any other way)?
>
> No.
>
> Hi Tom,

"No" means: there is no way since the query plan is stored in a
secret/safe/protected/non-visible/fort-knox like place :)
or it means, there is no friendly way to do it with spi or casual c language
programming?

Regards,

Cristiano Duarte.

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Tom Lane]

Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I think you'd be nuts to bet your data on the binary representations
>> really being cross-platform compatible.

> Can you elaborate on this? AFAICT the send/recv functions use network 
> byte ordering. What are the other problems between different architectures?

Well, you're probably fine with integers and text, but beyond that it
gets a bit more dicey.  I wouldn't want to assume that floats are
compatible across any random pair of architectures, and in the more
complex datatypes (such as arrays or geometric types) there might be
some impact from alignment rules.  (Or not, I'm too lazy to go look at
the moment.)

In any one situation you can doubtless find out by testing whether
you've got a problem.  I'm just nervous about a blithe suggestion that
someone could build a cross-platform backup solution this way.  That
sounds like a recipe for getting burnt and not finding out till it's
too late to fix it.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Markus Schiltknecht]

Hello Tom,

Tom Lane wrote:

I think you'd be nuts to bet your data on the binary representations
really being cross-platform compatible.


Can you elaborate on this? AFAICT the send/recv functions use network 
byte ordering. What are the other problems between different architectures?



There might be some excuse for
doing this within a single architecture, but I can't get very excited
about it ...


Is the textual representation (i.e. OidOutputFunctionCall) more cross 
platform compatible?


Gregory Stark wrote:
> Well they're not very useful for their intended purpose of
> client-server communication if they're not.

Agreed. Up until now, I'd have considered it a bug, if a send/recv on 
different platforms would not lead to the very same result.


Regards

Markus


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

[HACKERS] database diagram

[Eretna Evrengizid]

hi,

I use postgresql 8.2 and I cant see database diagram on pgAdmin III,  I think 
it can be . it is there , isnt it  ?


   

Got a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
 

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Gregory Stark]

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

> I think you'd be nuts to bet your data on the binary representations
> really being cross-platform compatible.  There might be some excuse for
> doing this within a single architecture, but I can't get very excited
> about it ...

Well they're not very useful for their intended purpose of client-server
communication if they're not.

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

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

Re: [HACKERS] [COMMITTERS] pgsql: Silence Solaris compiler warnings, per buildfarm.

[Gregory Stark]

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

> Log Message:
> ---
> Silence Solaris compiler warnings, per buildfarm.

This one was also lost in the tsearch merge. 

The second half from query_support.c seems to be in tsquery_op.c now but isn't
relevant because it relates to PG_FUNCTION_INFO_V1().


Index: src/backend/utils/adt/tsquery_cleanup.c
===
RCS file: 
/home/stark/src/REPOSITORY/pgsql/src/backend/utils/adt/tsquery_cleanup.c,v
retrieving revision 1.4
diff -c -r1.4 tsquery_cleanup.c
*** src/backend/utils/adt/tsquery_cleanup.c 7 Sep 2007 16:03:40 -   
1.4
--- src/backend/utils/adt/tsquery_cleanup.c 17 Sep 2007 13:42:39 -
***
*** 194,199 
--- 194,202 
  #ifdef V_UNKNOWN  /* exists in Windows headers */
  #undef V_UNKNOWN
  #endif
+ #ifdef V_FALSE/* exists in Solaris 
headers */
+ #undef V_FALSE
+ #endif
  
  /*
   * output values for result output parameter of clean_fakeval_intree

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

---(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] SPI access to PostgreSQL query plan

[Tom Lane]

"Cristiano Duarte" <[EMAIL PROTECTED]> writes:
> Is there a way to have access to PostgreSQL query plan and/or predicates
> inside a function using spi (or any other way)?

No.

regards, tom lane

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

[HACKERS] SPI access to PostgreSQL query plan

[Cristiano Duarte]

Hi,

Is there a way to have access to PostgreSQL query plan and/or predicates
inside a function using spi (or any other way)?
For example:

explain select * from my_func() as (code int, name varchar) where name like
'a%';
  QUERY PLAN
--
 Function Scan on my_func  (cost=0.00..15.00 rows=5 width=36)
   Filter: ((name)::text ~~ 'a%'::text)

I would like to know, inside the my_func function that PostgreSQL will
filter the function's returned result on the name column
with a like operator matching 'a%'. Since inside the my_func function I do
another database query, I could "pre-filter" this
query and reduce the amount of records returned.

If the whole plan cannot be exposed to spi, can only the part involved on
filtering/manipulating the records returned from
 the function call?

Is the query plan saved in memory at a place where a spi function can have
access?

Regards,

Cristiano Duarte.

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Tom Lane]

Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>> Perhaps if you're doing some form of replication between different
>> architectures you might want to use binary representation for your transfers.
>> Or if you're doing something in a PL language like compressing or bundling up
>> multiple data in a container format or something.

> I'm not quite sure I understand where you're coming from, but isn't that 
> what the send and recv functions for most data types are for? Don't 
> those provide a cross-platform compatible binary representation?

I think you'd be nuts to bet your data on the binary representations
really being cross-platform compatible.  There might be some excuse for
doing this within a single architecture, but I can't get very excited
about it ...

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] [COMMITTERS] pgsql: Fix a portability bug (ye olde not casting a argument

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> Fix a portability bug (ye olde not casting a  argument to
>> unsigned char).  Fortunately we still have buildfarm machines that
>> will flag this.  Seems to be new in CVS HEAD, so no backpatch.

> Should we add buildfarm animals which compile with -fsigned-chars and
> -funsigned-chars in addition to the per-architecture defaults? Does that
> guarantee that we'll trigger this warning or does it not help?

Wouldn't help AFAICS.

regards, tom lane

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

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

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Markus Schiltknecht]

Hi,

Gregory Stark wrote:

Perhaps if you're doing some form of replication between different
architectures you might want to use binary representation for your transfers.
Or if you're doing something in a PL language like compressing or bundling up
multiple data in a container format or something.


I'm not quite sure I understand where you're coming from, but isn't that 
what the send and recv functions for most data types are for? Don't 
those provide a cross-platform compatible binary representation?


Regards

Markus

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

Re: [HACKERS] [COMMITTERS] pgsql: Fix up text concatenation so that it accepts all the reasonable

[Gregory Stark]

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

> Log Message:
> ---
> Fix up text concatenation so that it accepts all the reasonable cases that
> were accepted by prior Postgres releases.  This takes care of the loose end
> left by the preceding patch to downgrade implicit casts-to-text.  To avoid
> breaking desirable behavior for array concatenation, introduce a new
> polymorphic pseudo-type "anynonarray" --- the added concatenation operators
> are actually text || anynonarray and anynonarray || text.

Just a crazy thought -- probably not worth doing now or perhaps ever -- but I
thought I would get it down in bits:

The above change eliminated cast functions to and from text by using the text
i/o functions. We could do the same for casts to and from bytea using binary
i/o functions.

I'm not sure how important it is for users to be able to reliably store any
data type in a bytea -- certainly not as important as being able to reliably
cast to and from text. 

Perhaps if you're doing some form of replication between different
architectures you might want to use binary representation for your transfers.
Or if you're doing something in a PL language like compressing or bundling up
multiple data in a container format or something.

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

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

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

[HACKERS] Small Release Notes Comment

[Gregory Stark]

I think the change to the hash functions needs to be called out in the release
notes.

If anyone stored any results of hashintN, hashfloat8, etc in their database or
outside the database those results will have changed. It's fairly unlikely but
there could be someone out there doing that.

No intervention is required for normal expression indexes using those
functions or hash indexes which will be rebuilt during a database upgrade
anyways. I don't think hash_any itself changed so this wouldn't affect
hashtext or any hash function which was already using hash_any.

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

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

   http://archives.postgresql.org