Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I'd like to see a clear explanation of what assumptions are being made
>> and why they represent a useful case.

> Absolutely agreed there.

Just to be clear: I believe our current assumptions can be stated as
"Postgres will not lose data if the kernel and disk drive do not lose
data that they have acknowledged as being successfully fsync'd."
This is independent of any questions about Postgres bugs or measures
that we take to limit the impact of our bugs --- it's about what our
extent of responsibility is.  I think that Hideyuki-san is proposing
a different contract for data integrity, and I want to understand what
that contract is and why someone would want it.

regards, tom lane

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


Re: [HACKERS] XML export

2007-02-10 Thread David Fetter
On Sat, Feb 10, 2007 at 11:35:08AM -0800, Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> > The issue of XML export has been discussed a few times throughout
> > history.  Right now you've got the HTML output in psql.  A few
> > people have proposed "real" XML output formats in psql or elsewhere.
> > 
> > I dug out some old code today that implements what SQL/XML has to say
> > on the matter and fitted the code to work with the current XML support
> > in the backend.
> > 
> > Below are examples of what it can do.  I'm thinking about hosting this
> > on PgFoundry, but if the crowd thinks this should be somewhere else,
> > short of the moon, let me know.
> > 
> 
> Integrated, native XML support can only help PostgreSQL. IMO, I want
> this in core.

+1 :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake
Tom Lane wrote:
> Gene <[EMAIL PROTECTED]> writes:
>> ... just my two cents. on a side note, would putting the wal on a
>> tmpfs partition give you something similar?
> 
> Indeed, I'm wondering why one needs to hack the Postgres core to throw
> away data integrity guarantees; there are plenty of ways to do that
> already :-(. 

Under a Linux or FreeBSD environment sure... but what about where a good
portion of the memory *is* flash? We have a customer right now where
they have a device that has 64 megs of ram and 512 megs of flash. The
system itself considers it total ram. They run over the 64 megs almost
on boot.

Allowing postgresql to be less aggressive on writes would help them
quite a bit.

> Hideyuki-san has not explained exactly what integrity
> assumptions he wants to make or not make.  I'm surely willing to listen
> to supporting a different set of assumptions than we currently use, but
> I'd like to see a clear explanation of what assumptions are being made
> and why they represent a useful case.

Absolutely agreed there.

Sincerely,

Joshua D. Drake

> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
> 
> http://www.postgresql.org/about/donate
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake
Christopher Browne wrote:
> [EMAIL PROTECTED] (Hideyuki Kawashima) wrote:
>> Joshua,
>>
>> I appreciate your quick & informative reply. And, I also really
>> appreciate your kind comments. Since I have joined this ML 3 hours
>> ago, I tried to be polite and slightly nervous. But I was relieved
>> by your message.
> 
> Your idea sounds interesting; there is likely to be a considerable
> resistance to mechanisms, however, that would be likely to make
> PostgreSQL less robust.
> 
> Be aware, also, that in a public forum like this, people are sometimes
> less gentle than Joshua.
> 
> The fundamental trouble with this mechanism is that a power outage can
> instantly turn a database into crud.

Correct, that is certainly a problem. However, I think the interesting
opportunity here is in devices that don't typically loose power. A PDA
for example.

The PostgreSQL footprint is actually quite small, and PDAs are getting
larger and larger in capacity. Heck, they even have 32GB SD now. In the
near future I believe we can expect to see always on, mini laptops as well.

>From an deployable application perspective, this could be a big deal. We
are already starting to see very large traction in the Win32 desktop app
arena.


> 
> One may try to mitigate that problem by supporting the memory device
> with multiple power supplies *and* multiple UPSes.
> 
> But there is a not-inconsiderable risk that people will fail to read
> warnings, deploy databases in a way that leaves them exposed to total
> loss, and then lay blame at this community's feet.  I'm sure you can
> understand why the community might resist that...

I certainly can, but a feature left off by default and documented
thoroughly can mitigate a lot of that. Heck if we really wanted to we
could even make it a custom build; --with-lazy-wal ;)

Sincerely,

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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


Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy -> strlcpy (not complete)

2007-02-10 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Woh.  Peter, you realize one of the reasons we use StrNCpy as a macro is
> for performance.  I don't see strlcpy as a macro.

Huh?  StrNCpy is a wrapper around strncpy().  Do you have reason to
think that strncpy() is especially tightly implemented on most
platforms?

regards, tom lane

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

   http://archives.postgresql.org


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Tom Lane
Gene <[EMAIL PROTECTED]> writes:
> ... just my two cents. on a side note, would putting the wal on a
> tmpfs partition give you something similar?

Indeed, I'm wondering why one needs to hack the Postgres core to throw
away data integrity guarantees; there are plenty of ways to do that
already :-(.  Hideyuki-san has not explained exactly what integrity
assumptions he wants to make or not make.  I'm surely willing to listen
to supporting a different set of assumptions than we currently use, but
I'd like to see a clear explanation of what assumptions are being made
and why they represent a useful case.

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] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread J. Andrew Rogers


On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote:

The fundamental trouble with this mechanism is that a power outage can
instantly turn a database into crud.

One may try to mitigate that problem by supporting the memory device
with multiple power supplies *and* multiple UPSes.



Ask me about the time a year ago that I had a 24x7 database, with two  
power supplies connected to two UPSes on independent mains circuits,  
dropped dead because one UPS was overloaded (more than one server  
connected to it, apparently too much) and the other UPS was simply  
dead (undetected zombie UPS), when a catastrophic power failure  
killed both of the generator backed mains circuits.


I wasn't pleased, but it happened nonetheless.  A UPS is not a 100%  
guarantee of anything.  They fail more often than they should.  No  
amount of paranoia guarantees uptime.


That said, I see plenty of use for loosening restrictions on  
databases where the contents do not matter and a little loss is  
acceptable.


Cheers,

J. Andrew Rogers
[EMAIL PROTECTED]


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

  http://archives.postgresql.org


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Hideyuki Kawashima

Gene,

Thanks for your comments !

> On our project, when the power is out, we aren't receiving data 
anyways... just my two cents.


I am sorry, but I do not know how I can appropriately answer since I do 
not understand what you would like to ask me in this sentence. I 
understand that you have a project with UPS, and when the power failure 
occurs on UPS, suddenly your system cannot get data. Is my understanding 
right ? If so, then I agree with you and the reliability and durability 
of UPS is mandatory for your system.


> on a side note, would putting the wal on a tmpfs partition give you 
something similar?


There are two differences  between Sigres method and tmpfs with UPS method.
1:  XLogWrite
Even if you use tmpfs, your system executes XLogWrite which includes 
write().
Since write() is heavy system call, Sigres would be slightly faster than 
tmpfs method.

2: XLogWriteLock
Even if you use tmpfs, your system lock/release XLogWriteLock while 
Sigres ignores.
Although the frequency of XLogWriteLock accesses is lower than 
XLogInsertLock,
ignoring XLogWriteLock improves the performance especially in many core 
environment.


Best Regards,

-- Hideyuki

Gene wrote:
I think it would be great to have this kind of functionality built 
into postgres (optional and disabled by default of course) I use 
postgres mainly for its querying and concurrency features (a 10x 
increase in insert/update speed would be phenomenal) I know most 
people need 100% data integrity but as Hideyuki pointed out we all 
don't need 100%. On our project, when the power is out, we aren't 
receiving data anyways... just my two cents. on a side note, would 
putting the wal on a tmpfs partition give you something similar?


On 2/10/07, *Joshua D. Drake* <[EMAIL PROTECTED] 
> wrote:


Hideyuki Kawashima wrote:
> Joshua,

:)

> The reason why I made the Sigres is, the advances of recent non
volatile
> memories. Just now we do not usually use non volatile memories.
But in
> the near future, situation would change. I think if a non volatile
> memories can be considered as a persistence device, PostgreSQL WAL
> mechanism should be modified.
> However, I do not use such devices usually. Thus I made Sigres which
> requires UPS.

This is actually very interesting. We ( www.commandprompt.com
) have had
several customers ask us how we can make PostgreSQL more reasonable
within a flash environment.

I agree with you that in the future you will see many such databases
including PostgreSQL living on these devices.

Tom? What do you think? Is there some room for movement here
within the
postgresql.conf to make something like sigres usable within PostgreSQL
proper?

>
> Currently I have just ignored XLogWrite and WALWriteLock, but a
friend
> of mine (a Japanese great hacker of PostgreSQL) has more idea to
improve
> WAL if a battery supplied memory can be considered as a
persistent device.
>

We are coming up very quickly on a feature freeze for the next version
of PostgreSQL. If... we can has something out quickly enough and in a
thought out fashion, the hackers may be willing to accept a patch for
8.3.. If not there is always 8.4..

Sincerely,

Joshua D. Drake




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project:
http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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




--
Gene Hart
cell: 443-604-2679 



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

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


Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy -> strlcpy (not complete)

2007-02-10 Thread mark
On Sat, Feb 10, 2007 at 09:21:04PM -0500, Bruce Momjian wrote:
> Woh.  Peter, you realize one of the reasons we use StrNCpy as a macro is
> for performance.  I don't see strlcpy as a macro.  Are you going to
> change all call locations to strlcpy?  If so, have you measured the
> performance impact?

I think we had this discussion already. strncpy() copies N bytes,
whereas strlcpy() copies only as many bytes as necessary. For short
strings with larger buffers, strlcpy() wins. It's understood that
in many cases in PostgreSQL, the expectation is for short strings,
and it is not required for the later bytes to be '\0'.

I assume Peter is only changing the provably good uses? :-)

Cheers,
mark


> Peter Eisentraut wrote:
> > Log Message:
> > ---
> > StrNCpy -> strlcpy (not complete)
> > 
> > Modified Files:
> > --
> > pgsql/src/backend/bootstrap:
> > bootstrap.c (r1.229 -> r1.230)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/bootstrap.c.diff?r1=1.229&r2=1.230)
> > pgsql/src/backend/libpq:
> > crypt.c (r1.72 -> r1.73)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/crypt.c.diff?r1=1.72&r2=1.73)
> > hba.c (r1.159 -> r1.160)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/hba.c.diff?r1=1.159&r2=1.160)
> > ip.c (r1.39 -> r1.40)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/ip.c.diff?r1=1.39&r2=1.40)
> > pgsql/src/backend/nodes:
> > print.c (r1.83 -> r1.84)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/print.c.diff?r1=1.83&r2=1.84)
> > pgsql/src/backend/postmaster:
> > pgarch.c (r1.28 -> r1.29)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgarch.c.diff?r1=1.28&r2=1.29)
> > postmaster.c (r1.518 -> r1.519)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c.diff?r1=1.518&r2=1.519)
> > pgsql/src/backend/tcop:
> > postgres.c (r1.521 -> r1.522)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c.diff?r1=1.521&r2=1.522)
> > pgsql/src/backend/utils/misc:
> > guc-file.l (r1.46 -> r1.47)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc-file.l.diff?r1=1.46&r2=1.47)
> > pgsql/src/bin/initdb:
> > initdb.c (r1.131 -> r1.132)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c.diff?r1=1.131&r2=1.132)
> > pgsql/src/bin/pg_ctl:
> > pg_ctl.c (r1.77 -> r1.78)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c.diff?r1=1.77&r2=1.78)
> > pgsql/src/bin/pg_dump:
> > pg_dumpall.c (r1.89 -> r1.90)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dumpall.c.diff?r1=1.89&r2=1.90)
> > pgsql/src/bin/pg_resetxlog:
> > pg_resetxlog.c (r1.56 -> r1.57)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_resetxlog/pg_resetxlog.c.diff?r1=1.56&r2=1.57)
> > pgsql/src/interfaces/libpq:
> > fe-auth.c (r1.122 -> r1.123)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c.diff?r1=1.122&r2=1.123)
> > fe-connect.c (r1.342 -> r1.343)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-connect.c.diff?r1=1.342&r2=1.343)
> > pgsql/src/timezone:
> > pgtz.c (r1.49 -> r1.50)
> > 
> > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c.diff?r1=1.49&r2=1.50)
> > 
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
> 
> -- 
>   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 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


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

Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Christopher Browne
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki 
Kawashima) wrote:
> I appreciate your great suggestion!
> It is great honor for me if Sigres will be merged to PostgreSQL.
> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> incorporating Sigres into PostgreSQL would be easy.

You should consider submitting a patch for this against CVS HEAD.

And actually, I'd think it a better idea to define a GUC variable and
use that to control whether Sigres is active or not.

At the more sophisticated end of the spectrum, you might set things up
so that it could be activated/deactivated at runtime by a superuser.

At the less sophisticated end, it might need to be configured in
postgresql.conf...
-- 
output = ("cbbrowne" "@" "acm.org")
http://linuxfinances.info/info/
If you've done  six impossible things  this morning, why not  round it
off  with breakfast  at  Milliways, the  Restaurant at the  End of the
Universe?

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

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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Christopher Browne
[EMAIL PROTECTED] (Hideyuki Kawashima) wrote:
> Joshua,
>
> I appreciate your quick & informative reply. And, I also really
> appreciate your kind comments. Since I have joined this ML 3 hours
> ago, I tried to be polite and slightly nervous. But I was relieved
> by your message.

Your idea sounds interesting; there is likely to be a considerable
resistance to mechanisms, however, that would be likely to make
PostgreSQL less robust.

Be aware, also, that in a public forum like this, people are sometimes
less gentle than Joshua.

The fundamental trouble with this mechanism is that a power outage can
instantly turn a database into crud.

One may try to mitigate that problem by supporting the memory device
with multiple power supplies *and* multiple UPSes.

But there is a not-inconsiderable risk that people will fail to read
warnings, deploy databases in a way that leaves them exposed to total
loss, and then lay blame at this community's feet.  I'm sure you can
understand why the community might resist that...
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/internet.html
Babbage's  Rule: "No  man's  cipher  is worth  looking  at unless  the
inventor has himself solved a very difficult cipher" (The Codebreakers
by Kahn, 2nd ed, pg 765)

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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake
Hideyuki Kawashima wrote:
> Joshua,
> 
> I appreciate your great suggestion!
> It is great honor for me if Sigres will be merged to PostgreSQL.
> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> incorporating Sigres into PostgreSQL would be easy.

The best way is to create a patch against -head and submit that patch
with a complete description of why, and what. If you have test cases
that show the improvement all the better.

I would suggest though if you are going to submit the patch that you
take a look at how you could disable/enable the feature within the
postgresql.conf via a guc.

Sincerely,

Joshua D. Drake

> 
> However, Sigres modifies WAL which is the most important point of DBMS
> on stability.
> Although I myself could not find any bugs in Sigres, I am really afraid
> of it. It a bug exists on Sigres, it puts everyone to huge
> inconvenience... Therefore, before incorporating Sigres into PostgreSQL,
> the code must be checked, and the behaviors of Sigres must be checked
> carefully. Since PostgreSQL is a famous and wide spread software, I
> strongly want to avoid losing its great reputation. Unfortunately in
> Japan, I do not know any WAL hackers except for a friend of mine, and he
> is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am
> really happy.
> 
> Best Regards,
> 
> -- Hideyuki
> 
> Joshua D. Drake wrote:
>> Hideyuki Kawashima wrote:
>>   
>>> Joshua,
>>> 
>> :)
>>
>>   
>>> The reason why I made the Sigres is, the advances of recent non volatile
>>> memories. Just now we do not usually use non volatile memories. But in
>>> the near future, situation would change. I think if a non volatile
>>> memories can be considered as a persistence device, PostgreSQL WAL
>>> mechanism should be modified.
>>> However, I do not use such devices usually. Thus I made Sigres which
>>> requires UPS.
>>> 
>> This is actually very interesting. We (www.commandprompt.com) have had
>> several customers ask us how we can make PostgreSQL more reasonable
>> within a flash environment.
>>
>> I agree with you that in the future you will see many such databases
>> including PostgreSQL living on these devices.
>>
>> Tom? What do you think? Is there some room for movement here within the
>> postgresql.conf to make something like sigres usable within PostgreSQL
>> proper?
>>
>>   
>>> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
>>> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
>>> WAL if a battery supplied memory can be considered as a persistent device.
>>>
>>> 
>> We are coming up very quickly on a feature freeze for the next version
>> of PostgreSQL. If... we can has something out quickly enough and in a
>> thought out fashion, the hackers may be willing to accept a patch for
>> 8.3.. If not there is always 8.4..
>>
>> Sincerely,
>>
>> Joshua D. Drake
>>
>>
>>
>>
>>   
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Hideyuki Kawashima
Joshua,

I appreciate your great suggestion!
It is great honor for me if Sigres will be merged to PostgreSQL.
Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
incorporating Sigres into PostgreSQL would be easy.

However, Sigres modifies WAL which is the most important point of DBMS
on stability.
Although I myself could not find any bugs in Sigres, I am really afraid
of it. It a bug exists on Sigres, it puts everyone to huge
inconvenience... Therefore, before incorporating Sigres into PostgreSQL,
the code must be checked, and the behaviors of Sigres must be checked
carefully. Since PostgreSQL is a famous and wide spread software, I
strongly want to avoid losing its great reputation. Unfortunately in
Japan, I do not know any WAL hackers except for a friend of mine, and he
is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am
really happy.

Best Regards,

-- Hideyuki

Joshua D. Drake wrote:
> Hideyuki Kawashima wrote:
>   
>> Joshua,
>> 
>
> :)
>
>   
>> The reason why I made the Sigres is, the advances of recent non volatile
>> memories. Just now we do not usually use non volatile memories. But in
>> the near future, situation would change. I think if a non volatile
>> memories can be considered as a persistence device, PostgreSQL WAL
>> mechanism should be modified.
>> However, I do not use such devices usually. Thus I made Sigres which
>> requires UPS.
>> 
>
> This is actually very interesting. We (www.commandprompt.com) have had
> several customers ask us how we can make PostgreSQL more reasonable
> within a flash environment.
>
> I agree with you that in the future you will see many such databases
> including PostgreSQL living on these devices.
>
> Tom? What do you think? Is there some room for movement here within the
> postgresql.conf to make something like sigres usable within PostgreSQL
> proper?
>
>   
>> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
>> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
>> WAL if a battery supplied memory can be considered as a persistent device.
>>
>> 
>
> We are coming up very quickly on a feature freeze for the next version
> of PostgreSQL. If... we can has something out quickly enough and in a
> thought out fashion, the hackers may be willing to accept a patch for
> 8.3.. If not there is always 8.4..
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
>   

-- 
筑波大学大学院システム情報工学研究科 講師 川島英之
〒305-8573 つくば市天王台1-1-1  TEL: 029-853-5322
#2月より所属が変わりました



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

   http://archives.postgresql.org


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Gene

I think it would be great to have this kind of functionality built into
postgres (optional and disabled by default of course) I use postgres mainly
for its querying and concurrency features (a 10x increase in insert/update
speed would be phenomenal) I know most people need 100% data integrity but
as Hideyuki pointed out we all don't need 100%. On our project, when the
power is out, we aren't receiving data anyways... just my two cents. on a
side note, would putting the wal on a tmpfs partition give you something
similar?

On 2/10/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:


Hideyuki Kawashima wrote:
> Joshua,

:)

> The reason why I made the Sigres is, the advances of recent non volatile
> memories. Just now we do not usually use non volatile memories. But in
> the near future, situation would change. I think if a non volatile
> memories can be considered as a persistence device, PostgreSQL WAL
> mechanism should be modified.
> However, I do not use such devices usually. Thus I made Sigres which
> requires UPS.

This is actually very interesting. We (www.commandprompt.com) have had
several customers ask us how we can make PostgreSQL more reasonable
within a flash environment.

I agree with you that in the future you will see many such databases
including PostgreSQL living on these devices.

Tom? What do you think? Is there some room for movement here within the
postgresql.conf to make something like sigres usable within PostgreSQL
proper?

>
> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
> WAL if a battery supplied memory can be considered as a persistent
device.
>

We are coming up very quickly on a feature freeze for the next version
of PostgreSQL. If... we can has something out quickly enough and in a
thought out fashion, the hackers may be willing to accept a patch for
8.3.. If not there is always 8.4..

Sincerely,

Joshua D. Drake




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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





--
Gene Hart
cell: 443-604-2679


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake
Hideyuki Kawashima wrote:
> Joshua,

:)

> The reason why I made the Sigres is, the advances of recent non volatile
> memories. Just now we do not usually use non volatile memories. But in
> the near future, situation would change. I think if a non volatile
> memories can be considered as a persistence device, PostgreSQL WAL
> mechanism should be modified.
> However, I do not use such devices usually. Thus I made Sigres which
> requires UPS.

This is actually very interesting. We (www.commandprompt.com) have had
several customers ask us how we can make PostgreSQL more reasonable
within a flash environment.

I agree with you that in the future you will see many such databases
including PostgreSQL living on these devices.

Tom? What do you think? Is there some room for movement here within the
postgresql.conf to make something like sigres usable within PostgreSQL
proper?

> 
> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
> WAL if a battery supplied memory can be considered as a persistent device.
> 

We are coming up very quickly on a feature freeze for the next version
of PostgreSQL. If... we can has something out quickly enough and in a
thought out fashion, the hackers may be willing to accept a patch for
8.3.. If not there is always 8.4..

Sincerely,

Joshua D. Drake




-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Hideyuki Kawashima
Joshua,

I appreciate your quick & informative reply. And, I also really
appreciate your kind comments. Since I have joined this ML 3 hours ago,
I tried to be polite and slightly nervous. But I was relieved by your
message.

Major topic.
The full_page_writes option is already ignored. In Sigres, bgWriter does
not call CreatCheckPoint periodically. Sigres calls CreateCheckPoint
only when bgWriter calls ShutdownXLOG. Thus also from this point,
durable UPS is required for Sigres.

The reason why I made the Sigres is, the advances of recent non volatile
memories. Just now we do not usually use non volatile memories. But in
the near future, situation would change. I think if a non volatile
memories can be considered as a persistence device, PostgreSQL WAL
mechanism should be modified.
However, I do not use such devices usually. Thus I made Sigres which
requires UPS.

Currently I have just ignored XLogWrite and WALWriteLock, but a friend
of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
WAL if a battery supplied memory can be considered as a persistent device.

Regards,

-- Hideyuki

Joshua D. Drake wrote:
>> BTW, Joshua, could you please let me know or give me any pointers for
>> the reason why fsync=off option exists on PostgreSQL although PostgreSQL
>> 
>
> A couple of reasons that I can think of. One would be data loads or
> restoring from backup. Another would be on data that you can afford to
> throw away.
>
>
>   
>> developers does not allow sacrificing data integrity ?
>> If the reason is famous and clear in the community, I am sorry for
>> bothering you.
>> 
>
> No bother at all! We invite all good ideas and I am glad to see more
> from our eastern community participate.
>
> Another option you might want to look at to further give yourself a
> boost in performance is full_page_writes.
>
> Joshua D. Drake
>
>
>
>   
>> -- Hideyuki
>>
>> Joshua D. Drake wrote:
>> 
>>> Hideyuki Kawashima wrote:
>>>   
>>>   
 Hello PostgreSQL Hackers,

 I have made a modification of PostgreSQL which accelerates INSERT/UPDATE 
 using UPS. The name of the software is "Sigres", and the philosophy is 
 considering a battery supplied memory as a persistent device instead of a 
 disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .

 In the maximum case, Sigres is 7 times faster than PostgreSQL default 
 (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 
 10% faster than PostgreSQL without fsync (fsync=off).
 
 
>>> Interesting and what happens when the UPS fails? My main concern is that
>>> one of the purposes of PostgreSQL is data integrity. I am all for every
>>> performance enhancement we can achieve, that does *not* sacrifice that.
>>>
>>> Sincerely,
>>>
>>> Joshua D. Drake
>>>
>>>   
>>>   
 The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. 
 The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In 
 addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this 
 point, Sigres is different from just simply setting fsync=off.)

 Although I think Sigres can be considered as one of the future directions 
 of PostgreSQL, I do not know whether this software can be accepted or not. 
 Could you please give me some comments ?

 Best Regards,

 -- Hideyuki Kawashima 
 Assistant Professor, University of Tsukuba



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

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

-- 
筑波大学大学院システム情報工学研究科 講師 川島英之
〒305-8573 つくば市天王台1-1-1  TEL: 029-853-5322
#2月より所属が変わりました



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

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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake

> BTW, Joshua, could you please let me know or give me any pointers for
> the reason why fsync=off option exists on PostgreSQL although PostgreSQL

A couple of reasons that I can think of. One would be data loads or
restoring from backup. Another would be on data that you can afford to
throw away.


> developers does not allow sacrificing data integrity ?
> If the reason is famous and clear in the community, I am sorry for
> bothering you.

No bother at all! We invite all good ideas and I am glad to see more
from our eastern community participate.

Another option you might want to look at to further give yourself a
boost in performance is full_page_writes.

Joshua D. Drake



> 
> 
> -- Hideyuki
> 
> Joshua D. Drake wrote:
>> Hideyuki Kawashima wrote:
>>   
>>> Hello PostgreSQL Hackers,
>>>
>>> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE 
>>> using UPS. The name of the software is "Sigres", and the philosophy is 
>>> considering a battery supplied memory as a persistent device instead of a 
>>> disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>>>
>>> In the maximum case, Sigres is 7 times faster than PostgreSQL default 
>>> (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 
>>> 10% faster than PostgreSQL without fsync (fsync=off).
>>> 
>> Interesting and what happens when the UPS fails? My main concern is that
>> one of the purposes of PostgreSQL is data integrity. I am all for every
>> performance enhancement we can achieve, that does *not* sacrifice that.
>>
>> Sincerely,
>>
>> Joshua D. Drake
>>
>>   
>>> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. 
>>> The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In 
>>> addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this 
>>> point, Sigres is different from just simply setting fsync=off.)
>>>
>>> Although I think Sigres can be considered as one of the future directions 
>>> of PostgreSQL, I do not know whether this software can be accepted or not. 
>>> Could you please give me some comments ?
>>>
>>> Best Regards,
>>>
>>> -- Hideyuki Kawashima 
>>> Assistant Professor, University of Tsukuba
>>>
>>>
>>>
>>> ---(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
>>>
>>> 
>>
>>   
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Hideyuki Kawashima
Joshua,

Thanks for your comments !

Right. As you pointed out, Sigres cannot recover if UPS fails.
Therefore, I think currently Sigres cannot be used for mission critical
applications.
Sigres keeps data integrity *only when* UPS works.

On the other hand, some users such as researchers of sensor networks
would like store
and analyze 10^6 Hz sensor data insertions in real-time, and obviously
research usages are not mission critical. Thus Sigres may be accepted
for researchers, but I have no prospect now since I have just started to
distributing Sigres for research institutes in Japan.

BTW, Joshua, could you please let me know or give me any pointers for
the reason why fsync=off option exists on PostgreSQL although PostgreSQL
developers does not allow sacrificing data integrity ?
If the reason is famous and clear in the community, I am sorry for
bothering you.


-- Hideyuki

Joshua D. Drake wrote:
> Hideyuki Kawashima wrote:
>   
>> Hello PostgreSQL Hackers,
>>
>> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE 
>> using UPS. The name of the software is "Sigres", and the philosophy is 
>> considering a battery supplied memory as a persistent device instead of a 
>> disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>>
>> In the maximum case, Sigres is 7 times faster than PostgreSQL default 
>> (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% 
>> faster than PostgreSQL without fsync (fsync=off).
>> 
>
> Interesting and what happens when the UPS fails? My main concern is that
> one of the purposes of PostgreSQL is data integrity. I am all for every
> performance enhancement we can achieve, that does *not* sacrifice that.
>
> Sincerely,
>
> Joshua D. Drake
>
>   
>> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. 
>> The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In 
>> addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this 
>> point, Sigres is different from just simply setting fsync=off.)
>>
>> Although I think Sigres can be considered as one of the future directions of 
>> PostgreSQL, I do not know whether this software can be accepted or not. 
>> Could you please give me some comments ?
>>
>> Best Regards,
>>
>> -- Hideyuki Kawashima 
>> Assistant Professor, University of Tsukuba
>>
>>
>>
>> ---(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
>>
>> 
>
>
>   


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


Re: [HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Joshua D. Drake
Hideyuki Kawashima wrote:
> Hello PostgreSQL Hackers,
> 
> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE 
> using UPS. The name of the software is "Sigres", and the philosophy is 
> considering a battery supplied memory as a persistent device instead of a 
> disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
> 
> In the maximum case, Sigres is 7 times faster than PostgreSQL default 
> (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% 
> faster than PostgreSQL without fsync (fsync=off).

Interesting and what happens when the UPS fails? My main concern is that
one of the purposes of PostgreSQL is data integrity. I am all for every
performance enhancement we can achieve, that does *not* sacrifice that.

Sincerely,

Joshua D. Drake

> 
> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The 
> exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, 
> in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres 
> is different from just simply setting fsync=off.)
> 
> Although I think Sigres can be considered as one of the future directions of 
> PostgreSQL, I do not know whether this software can be accepted or not. Could 
> you please give me some comments ?
> 
> Best Regards,
> 
> -- Hideyuki Kawashima 
> Assistant Professor, University of Tsukuba
> 
> 
> 
> ---(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
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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


Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy -> strlcpy (not complete)

2007-02-10 Thread Bruce Momjian

Woh.  Peter, you realize one of the reasons we use StrNCpy as a macro is
for performance.  I don't see strlcpy as a macro.  Are you going to
change all call locations to strlcpy?  If so, have you measured the
performance impact?

---

Peter Eisentraut wrote:
> Log Message:
> ---
> StrNCpy -> strlcpy (not complete)
> 
> Modified Files:
> --
> pgsql/src/backend/bootstrap:
> bootstrap.c (r1.229 -> r1.230)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/bootstrap.c.diff?r1=1.229&r2=1.230)
> pgsql/src/backend/libpq:
> crypt.c (r1.72 -> r1.73)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/crypt.c.diff?r1=1.72&r2=1.73)
> hba.c (r1.159 -> r1.160)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/hba.c.diff?r1=1.159&r2=1.160)
> ip.c (r1.39 -> r1.40)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/ip.c.diff?r1=1.39&r2=1.40)
> pgsql/src/backend/nodes:
> print.c (r1.83 -> r1.84)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/print.c.diff?r1=1.83&r2=1.84)
> pgsql/src/backend/postmaster:
> pgarch.c (r1.28 -> r1.29)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgarch.c.diff?r1=1.28&r2=1.29)
> postmaster.c (r1.518 -> r1.519)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c.diff?r1=1.518&r2=1.519)
> pgsql/src/backend/tcop:
> postgres.c (r1.521 -> r1.522)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c.diff?r1=1.521&r2=1.522)
> pgsql/src/backend/utils/misc:
> guc-file.l (r1.46 -> r1.47)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc-file.l.diff?r1=1.46&r2=1.47)
> pgsql/src/bin/initdb:
> initdb.c (r1.131 -> r1.132)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c.diff?r1=1.131&r2=1.132)
> pgsql/src/bin/pg_ctl:
> pg_ctl.c (r1.77 -> r1.78)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c.diff?r1=1.77&r2=1.78)
> pgsql/src/bin/pg_dump:
> pg_dumpall.c (r1.89 -> r1.90)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dumpall.c.diff?r1=1.89&r2=1.90)
> pgsql/src/bin/pg_resetxlog:
> pg_resetxlog.c (r1.56 -> r1.57)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_resetxlog/pg_resetxlog.c.diff?r1=1.56&r2=1.57)
> pgsql/src/interfaces/libpq:
> fe-auth.c (r1.122 -> r1.123)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c.diff?r1=1.122&r2=1.123)
> fe-connect.c (r1.342 -> r1.343)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-connect.c.diff?r1=1.342&r2=1.343)
> pgsql/src/timezone:
> pgtz.c (r1.49 -> r1.50)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c.diff?r1=1.49&r2=1.50)
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend

-- 
  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 3: Have you checked our extensive FAQ?

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


[HACKERS] Acclerating INSERT/UPDATE using UPS

2007-02-10 Thread Hideyuki Kawashima
Hello PostgreSQL Hackers,

I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using 
UPS. The name of the software is "Sigres", and the philosophy is considering a 
battery supplied memory as a persistent device instead of a disk. You can 
download Sigres from http://sourceforge.jp/projects/sigres/ .

In the maximum case, Sigres is 7 times faster than PostgreSQL default 
(fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% 
faster than PostgreSQL without fsync (fsync=off).

The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The 
exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in 
XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is 
different from just simply setting fsync=off.)

Although I think Sigres can be considered as one of the future directions of 
PostgreSQL, I do not know whether this software can be accepted or not. Could 
you please give me some comments ?

Best Regards,

-- Hideyuki Kawashima 
Assistant Professor, University of Tsukuba



---(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] 10 weeks to feature freeze (Pending Work)

2007-02-10 Thread Rick Gigger

Jim Nasby wrote:

On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote:

On Jan 26, 2:38 pm, [EMAIL PROTECTED] (Tom Lane) wrote:

Rick Gigger <[EMAIL PROTECTED]> writes:

I thought that the following todo item just barely missed 8.2:
"Allow a warm standby system to also allow read-only statements [pitr]


No, it's a someday-wishlist item; the work involved is not small.


Slony1 has supported log-shipping replication for about a year now. It
provides similar functionality.


Not really

1) It's not possible for a PITR 'slave' to fall behind to a state where 
it will never catch up, unless it's just on inadequate hardware. Same 
isn't true with slony.

2) PITR handles DDL seamlessly
3) PITR is *much* simpler to configure and maintain


Which is why I was hoping for a PITR based solution.  Oh well, I will 
have to figure out what is my best option now that I know it will not be 
available any time in the near future.


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


Re: [HACKERS] XML export

2007-02-10 Thread Peter Eisentraut
Andrew Dunstan wrote:
> How do you treat columns whose names are not legal XML names?

There are escape mechanisms in place.  You can verify yourself how they 
work using

select xmlelement(name "something unusual");

> I'm glad to see you treat NULL as an attribute - that's definitely
> the right way I think.

The standard provides for the option of representing them the way I 
showed or omitting them.

> Have you thought about possibly using a standard encoding (e.g.
> base64) for bytea? Not sure what the standard says on encoding.

It says to use base64 or hex.  You can also verify that yourself using

select xmlelement(name foo, bytea 'something');

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

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


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

2007-02-10 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> key constraints loaded from dumps from pre-7.3 databases will no longer
>> work.  Those dumps just contain "CREATE CONSTRAINT TRIGGER" commands
>> which will not provide enough information.  We can make the triggers
>> throw errors suggesting that the user drop the triggers and perform
>> ALTER TABLE ADD CONSTRAINT.  Is that enough, or do we need to try
>> harder?

> Further in their right mind is trying to do a 24x7 shop on 7.3. They
> could always dump to 8.1 and then to 8.3.

Actually that wouldn't help: you'd still have a CREATE CONSTRAINT TRIGGER
-based foreign key.  The only thing that'd really fix it is having used
the old contrib "adddepend" utility at some point along the line.  Since
we never forced people to do that, it's fairly likely that some never
did.

[Thinks for a bit...]  It would still work to run adddepend over the
schema even after having loaded it into 8.3, assuming that adddepend
hasn't suffered bit-rot.  We dropped it from contrib because no one
was maintaining it anymore, but AFAIR there was no evidence that it's
actively broken.  So maybe we can just point to that for anyone who
comes along with an upgrade problem.

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

2007-02-10 Thread Joshua D. Drake
> After digging around in the code for awhile I realized that there's a
> potentially bigger backwards-compatibility issue here: if we make the
> RI triggers dependent on finding a pg_constraint entry, then foreign
> key constraints loaded from dumps from pre-7.3 databases will no longer
> work.  Those dumps just contain "CREATE CONSTRAINT TRIGGER" commands
> which will not provide enough information.  We can make the triggers
> throw errors suggesting that the user drop the triggers and perform
> ALTER TABLE ADD CONSTRAINT.  Is that enough, or do we need to try
> harder?

I think it is reasonable to expect that we can not support 7.3 dumps in
that manner considering we are talking about 8.3 ;). We can't be
backward compatible forever.

Further in their right mind is trying to do a 24x7 shop on 7.3. They
could always dump to 8.1 and then to 8.3.

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

2007-02-10 Thread Tom Lane
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 9 Feb 2007, Tom Lane wrote:
>> I am pretty strongly tempted to stop storing anything in tgargs for RI
>> triggers --- it's ugly, and updating the info during RENAME commands
>> is a pain in the rear.

> I'd say we probably want to keep the tgargs info for at least a version or
> two after changing the implementation.  Getting rid of using the args info
> sounds like a good idea.

After digging around in the code for awhile I realized that there's a
potentially bigger backwards-compatibility issue here: if we make the
RI triggers dependent on finding a pg_constraint entry, then foreign
key constraints loaded from dumps from pre-7.3 databases will no longer
work.  Those dumps just contain "CREATE CONSTRAINT TRIGGER" commands
which will not provide enough information.  We can make the triggers
throw errors suggesting that the user drop the triggers and perform
ALTER TABLE ADD CONSTRAINT.  Is that enough, or do we need to try
harder?

It would probably be possible to teach pg_dump to cons up ADD CONSTRAINT
commands when dumping from an old server, but I think it would be a lot
of work (certainly we punted on that idea back in the 7.3 devel cycle)
and I'm not sure there are enough people running pre-7.3 PG for it to
be worth the effort to provide an automated solution.

Comments?

regards, tom lane

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


Re: [HACKERS] XML export

2007-02-10 Thread Andrew Dunstan



Peter Eisentraut wrote:


Below are examples of what it can do.  I'm thinking about hosting this
on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.



  


How do you treat columns whose names are not legal XML names?

I'm glad to see you treat NULL as an attribute - that's definitely the 
right way I think.


Have you thought about possibly using a standard encoding (e.g. base64) 
for bytea? Not sure what the standard says on encoding.


cheers

andrew

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


Re: [HACKERS] XML export

2007-02-10 Thread Stefan Kaltenbrunner
Peter Eisentraut wrote:
> The issue of XML export has been discussed a few times throughout
> history.  Right now you've got the HTML output in psql.  A few
> people have proposed "real" XML output formats in psql or elsewhere.
> 
> I dug out some old code today that implements what SQL/XML has to say
> on the matter and fitted the code to work with the current XML support
> in the backend.
> 
> Below are examples of what it can do.  I'm thinking about hosting this
> on PgFoundry, but if the crowd thinks this should be somewhere else,
> short of the moon, let me know.

I'm not really a XML fan - but nevertheless having something like this
in core sounds useful.

Stefan

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

   http://archives.postgresql.org


Re: [HACKERS] XML export

2007-02-10 Thread Dave Page
Joshua D. Drake wrote:
> Peter Eisentraut wrote:
>> The issue of XML export has been discussed a few times throughout
>> history.  Right now you've got the HTML output in psql.  A few
>> people have proposed "real" XML output formats in psql or elsewhere.
>>
>> I dug out some old code today that implements what SQL/XML has to say
>> on the matter and fitted the code to work with the current XML support
>> in the backend.
>>
>> Below are examples of what it can do.  I'm thinking about hosting this
>> on PgFoundry, but if the crowd thinks this should be somewhere else,
>> short of the moon, let me know.
>>
> 
> Integrated, native XML support can only help PostgreSQL. IMO, I want
> this in core.

+1

Regards, Dave.

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


Re: [HACKERS] XML export

2007-02-10 Thread Theo Schlossnagle


On Feb 10, 2007, at 2:35 PM, Joshua D. Drake wrote:


Peter Eisentraut wrote:

The issue of XML export has been discussed a few times throughout
history.  Right now you've got the HTML output in psql.  A few
people have proposed "real" XML output formats in psql or elsewhere.

I dug out some old code today that implements what SQL/XML has to say
on the matter and fitted the code to work with the current XML  
support

in the backend.

Below are examples of what it can do.  I'm thinking about hosting  
this

on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.



Integrated, native XML support can only help PostgreSQL. IMO, I want
this in core.


Agreed.  In the server would be more useful to more people I think.   
It would be really convenient to be able to have "no effort" XML  
results sets to queries.


// Theo Schlossnagle
// [EMAIL PROTECTED]: http://omniti.com
// Esoteric Curio: http://www.lethargy.org/~jesus/


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


Re: [HACKERS] XML export

2007-02-10 Thread Joshua D. Drake
Peter Eisentraut wrote:
> The issue of XML export has been discussed a few times throughout
> history.  Right now you've got the HTML output in psql.  A few
> people have proposed "real" XML output formats in psql or elsewhere.
> 
> I dug out some old code today that implements what SQL/XML has to say
> on the matter and fitted the code to work with the current XML support
> in the backend.
> 
> Below are examples of what it can do.  I'm thinking about hosting this
> on PgFoundry, but if the crowd thinks this should be somewhere else,
> short of the moon, let me know.
> 

Integrated, native XML support can only help PostgreSQL. IMO, I want
this in core.

Sincerely,

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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


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

2007-02-10 Thread Tom Lane
Stephan Szabo <[EMAIL PROTECTED]> writes:
> The reason I was wondering is that it uses tgnargs == 4 as the check, and
> if we change the meanings of tgnargs, we'd need to change the check.

Sure, it'd be looking for a zero-length conkeys array instead.

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

2007-02-10 Thread Stephan Szabo
On Sat, 10 Feb 2007, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > One side question is what should we do about the
> > places in the current system where it checks for the key sets being empty?
>
> I don't see that this affects that either way.  I can't quite imagine
> what the semantics would be, though --- there's no such thing as a
> unique constraint with no columns, so how can there be an RI constraint
> with none?

Well, the code currently has checks with comments based on SQL3
text AFAICT.
/* --
 * SQL3 11.9 
 *  General rules 2) a):
 *  If Rf and Rt are empty (no columns to compare given)
 *  constraint is true if 0 < (SELECT COUNT(*) FROM T)
 *
 *  Note: The special case that no columns are given cannot
 *  occur up to now in Postgres, it's just there for
 *  future enhancements.
 * --
 */
The reason I was wondering is that it uses tgnargs == 4 as the check, and
if we change the meanings of tgnargs, we'd need to change the check.
Personally, I think we should probably just pull out the special case for
now, but thought it should be brought up.

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


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

2007-02-10 Thread Tom Lane
Stephan Szabo <[EMAIL PROTECTED]> writes:
> I assume you're speaking of the version where we just change the
> constraints to use statements with the OPERATOR() syntax and potential
> casts rather than the discussion at the end about changing the pk checks
> to avoid planning entirely?

Yeah, we might get around to doing that someday but I'm not excited
about it right now.  (I'm mainly doing this because it fits in with the
operator-family work I've been doing --- that also got rid of some
unsupportable assumptions about operators being named "=" ...)

> I'd say we probably want to keep the tgargs info for at least a version or
> two after changing the implementation.  Getting rid of using the args info
> sounds like a good idea.

We whack the catalogs around in incompatible ways in every release.  I'm
willing to keep filling tgargs if someone can point to a real use-case,
but not just because there might be code out there somewhere using it.

> One side question is what should we do about the
> places in the current system where it checks for the key sets being empty?

I don't see that this affects that either way.  I can't quite imagine
what the semantics would be, though --- there's no such thing as a
unique constraint with no columns, so how can there be an RI constraint
with none?

regards, tom lane

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


[HACKERS] XML export

2007-02-10 Thread Peter Eisentraut
The issue of XML export has been discussed a few times throughout
history.  Right now you've got the HTML output in psql.  A few
people have proposed "real" XML output formats in psql or elsewhere.

I dug out some old code today that implements what SQL/XML has to say
on the matter and fitted the code to work with the current XML support
in the backend.

Below are examples of what it can do.  I'm thinking about hosting this
on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.


regression=# select table_to_xml('select * from emp');
 table_to_xml
---
 

   
 sharon
 25
 (15,12)
 1000
 sam
   

...

   
 linda
 19
 (0.9,6.1)
 100
 
   

 

(1 row)

As a use case of sorts, I've got an XSLT stylesheet that can convert
this to HTML tables.

regression=# select table_to_xmlschema('select * from emp');
   table_to_xmlschema
-
 

   

 
   
 
   
 

 
   
 
 
   
 

 

 

 
   
 
 
 
 
 
   
 

 
   
 
   
 

 

 
(1 row)


I also have a table function which can convert both of these back into
an table, so that would be XML import.  But that doesn't work quite yet.

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

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

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


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

2007-02-10 Thread Stephan Szabo

On Fri, 9 Feb 2007, Tom Lane wrote:

> Almost a year ago, we talked about the problem that referential
> integrity should be selecting comparison operators on the basis
> of b-tree index opclasses, instead of assuming that the appropriate
> operator is always named "=":
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php
> http://archives.postgresql.org/pgsql-hackers/2006-03/msg00161.php
>
> I'm about to go off and implement that at last.  To refresh folks'
> memory, what I think we agreed to was that at the time of definition
> of a foreign-key constraint, we should identify the specific equality
> operator to be used for (each column of) the constraint.  The method
> for doing this is to be:
>
> * First, identify the unique index that is relied on to enforce
> uniqueness of the PK entries (we do this already of course).
>
> * Look to see if there is an equality operator in this index's
> opfamily accepting exactly the PK and FK data types (ie, "PK = FK").
> If so, use that.
>
> * Else, check to see if there is an implicit promotion from the FK
> datatype to the PK datatype.  If so, use the equality operator
> "PK = PK", which must exist since the opfamily supports an index
> on the PK datatype.
>
> * Else fail (this means that the present warning about "inefficient"
> foreign keys will become a hard error).

I assume you're speaking of the version where we just change the
constraints to use statements with the OPERATOR() syntax and potential
casts rather than the discussion at the end about changing the pk checks
to avoid planning entirely?

> My intention is that we'd record pg_depend entries making the RI
> constraint dependent on not only the index, but the specific operators
> to use.  This would not have been too critical a year ago given that
> opclasses were effectively immutable; but in the current opfamily design
> it's entirely likely that we'd select cross-type equality operators that
> are considered "loose" and potentially droppable from the opfamily.
> So we need dependencies to prevent the operators from disappearing out
> from under us.  (Come to think of it, we might want to record
> dependencies on the casts too, if we're using implicit casts?)

I think we probably should, so the above seems reasonable to me.

> * Add an oid[] column to pg_constraint that stores the equality operator
> OIDs for a foreign-key constraint, in the same column order as conkey[]
> and confkey[].
>
> * Add an OID column to pg_trigger giving the OID of the constraint
> owning the trigger (or 0 if none).  Add this information to struct
> Trigger as well, so that it gets passed to trigger functions.
>
> Given the pg_constraint OID, the RI triggers could fetch the constraint
> row and look at conkey[], confkey[], and the new operator oid[] array
> to determine what they need to know.
>
> This would actually mean that they don't need pg_trigger.tgargs at all.
> I am pretty strongly tempted to stop storing anything in tgargs for RI
> triggers --- it's ugly, and updating the info during RENAME commands
> is a pain in the rear.  On the other hand removing it might break
> client-side code that expects to look at tgargs to learn about FK
> constraints.  I'd personally think that pg_constraint is a lot easier to
> work with, but there might be some code out there left over from way
> back before pg_constraint existed --- anyone know of any such issue?

I'd say we probably want to keep the tgargs info for at least a version or
two after changing the implementation.  Getting rid of using the args info
sounds like a good idea.  One side question is what should we do about the
places in the current system where it checks for the key sets being empty?
AFAIK, we still don't actually support letting you define a constraint
that way, and I haven't heard any complaints about that, and I'm not even
sure if that actually made it into the spec proper.

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


Re: [HACKERS] Ooops ... seems we need a re-release pronto

2007-02-10 Thread Tom Lane
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Shouldn't we at least add the one or two exemplary statements that 
> failed so we have some sort of coverage of the problem?

We could, but I'm unexcited about it.  The known failures are an
extremely narrow case: we're trying to evaluate expressions (either
CHECK constraints or index expressions) over a tuple proposed to be
inserted into a relation.  But the TupleDesc that's been supplied to
the evaluator is not the tuple descriptor of the target relation,
it's a descriptor generated on-the-fly from the target list of the
plan tree (by ExecTypeFromTL).  And the target list includes some
constants, and our nodetree representation for constants fails to
preserve typmod knowledge, and so ExecTypeFromTL produces atttypmod
-1 for this column, and the security check didn't like that because
the Var it was checking had a nondefault typmod.

When the first reports came in, I thought seriously about fixing it
by forcing the target relation's real tupdesc (from its relcache entry)
to be used in this context instead of a generated tupdesc.  I concluded
that it was too likely that there were other cases where we were
evaluating expressions against generated tuples, and we had to back off
the strength of the security check instead.  I do not actually have any
specific examples, but I think it's fairly pointless to add a regression
test that covers this one narrow scenario when there are probably lots
of others.

I'm not especially a fan of the testing philosophy that says you
memorialize each individual past mistake as a permanent regression test
--- I think that just bloats the tests, and test bloat is a bad thing
because it discourages people from running the tests.  (MySQL's
regression tests currently require about an hour on a fast machine.
Somehow this has not helped them to achieve a low bug rate...)  I do
agree with adding a test when you think it is likely to be able to catch
a whole class of errors, or even a specific error if it seems especially
likely to recur, but right now I'm not seeing how we do that here.

BTW, I think a good case could be made that the core of the problem
is exactly that struct Const doesn't carry typmod, and thus that we
lose information about constructs like 'foo'::char(7).  We should fix
that, and also anywhere else in the expression tree structure where
we are discarding knowledge about the typmod of a result.  This has
got some urgency because of Teodor's recent work on allowing user
defined types to have typmods --- we can expect massive growth in the
number of scenarios where it matters.

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] Ooops ... seems we need a re-release pronto

2007-02-10 Thread Peter Eisentraut
Tom Lane wrote:
> Jim Nasby <[EMAIL PROTECTED]> writes:
> > On Feb 6, 2007, at 12:40 PM, Tom Lane wrote:
> >> ... massive expansion of the tests doesn't seem justified
> >
> > What about the idea that's been floated in the past about a --
> > extensive mode for regression testing that would (generally) only
> > be used by the build farm. That would mean others wouldn't have to
> > suffer through extremely long make check's.
> >
> > Or is there another reason not to expand the tests?
>
> I'm not concerned so much about the runtime as the development and
> maintenance effort...

Shouldn't we at least add the one or two exemplary statements that 
failed so we have some sort of coverage of the problem?

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

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


Re: [HACKERS] [PATCHES] How can I use 2GB of shared buffers on Windows?

2007-02-10 Thread Magnus Hagander
Takayuki Tsunakawa wrote:
> From: "Magnus Hagander" <[EMAIL PROTECTED]>
>> Right. Which is why you're likely to see better performance if you
> keep
>> shared buffers smaller. There is something in dealing with it that's
>> slow on win32, per reports from the field. It needs to be
> investigated
>> further...
>> We've had reports that it's slow with large shared_buffers, yes.
> 
> That's a shocking news.  I'm sad.

It shouldn't make you sad until it has been proven that it's actually a
problem. And if we can do something about it :-)


> I wonder whether the field you are talking about set Windows to use
> more memory for programs than for filesystem cache, which is
> selectable from [System] applet of Control Panel (Oh, I wonder how my
> machine is set in this respect... have to check.)  If filesystem cache
> is preferred, the following senario may be possible:

Could be, I haven't run the tests myself, and it was tests on production
systems, and not actual benchmark runs.


>> Are you sure you're not running this on for example
>> IDE disks with write-cache that lies? Windows will write through
> that
>> write-cache even if the disk lies, whereas most linux versions
> won't. At
>> least that used to be the case not too long ago, but there has also
> been
>> talking about fixign that in linux, so maybe that's done...
> 
> I'm using a PC server whose disks are all SCSI.  It has no IDE disk.

There goes that idea :-) Then you need to dig further into why it's such
a big difference.


>> Also note that when you run pg_bench on the local machine, you take
> a
>> much higher hit from the fact that context switching between
> processes
>> is a lot more expensive on Windows than it is on Linux. But it
> shouldn't
>> be big enough to explain the huge difference you had in your test.
> 
> Yes, I suspect it, too.  So, Oracle uses one multi-threaded server
> process on Windows, while it employs multi-process architecture.  SQL
> Server is of course multi-threaded.  SRA's original PostgreSQL for
> Windows (based on 7.x) was also multi-threaded.

Right. The windows MM and scheduler system is definitely geared for
multithreaded. But that would make pg on win32 too different from pg on
unix to be maintained without a significantly larger effort than today,
so unless you can find some non-intrusive way to sneak it in (which I
doubt), that's just not going to happen.

That said, the context switching overhead shouldn't be *that* large. but
it'd be interesting to see what the performance difference would be for
the same machine with pg_bench running on a different machine across a
fast network connection.

(BTW, another difference is that pg_bench on unix would be using unix
domain sockets and on windows it would be using tcp/ip. But that really
shouldn't make such a huge difference either, but I guess it would add a
bit to the factor)

//Magnus

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

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