On Thu, 25 Apr 2002, Bruce Momjian wrote:
> WAL files are kept only until an fsync(), checkpoint, then reused.
One could keep them longer though, if one really wanted to.
> Also, the info is tied to direct locations in the file. You could do
> this for hot backup, but it would require quite bi
On Fri, 26 Apr 2002, Marc G. Fournier wrote:
> NOTE that I *do* think that #1 is what *should* happen, but there should
> be some way of turning off that behaviour so that we don't screw up ppl
> expecting "Oracles behaviour" ...
I don't think this follows. If it's only for people's expectations
Curt Sampson wrote:
> On Thu, 25 Apr 2002, mlw wrote:
>
> > ...but my gut tells me that using 16K blocks will increase performance
> > over 8K. Aleady I have seen a sequential scan of a large table go from 20
> > seconds using 8K to 17.3 seconds using 16K.
>
> You should be able to get the same
On Thu, 25 Apr 2002, mlw wrote:
> ...but my gut tells me that using 16K blocks will increase performance
> over 8K. Aleady I have seen a sequential scan of a large table go from 20
> seconds using 8K to 17.3 seconds using 16K.
You should be able to get the same performance increase with 8K
block
Marc G. Fournier wrote:
> > Yes, let's find out what the others do. I don't see DROP TABLE
> > rollbacking as totally different. How is it different from SET?
>
> SET currently has an "accepted behaviour" with other DBMSs, or, at least,
> with Oracle, and that is to ignore the rollback ...
>
>
At 04:01 PM 4/25/02 -0300, Marc G. Fournier wrote:
> > My guess is that we should implement #1 and see what feedback we get in
> > 7.3.
>
>IMHO, it hasn't been thought out well enough to be implemented yet ... the
>options have been, but which to implement haven't ... right now, #1 is
>proposing t
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> >
> > >
> > > Marc is suggesting we may want to match Oracle somehow.
> > >
> > > I just want to have our SET work on a sane manner.
> >
> > Myself, I wonder why Oracle went the rou
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Actually, this brings up a different point. We use 8k blocks now
> because at the time PostgreSQL was developed, it used BSD file systems,
> and those prefer 8k blocks, and there was some concept that an 8k write
> was atomic, though with 512 byte disk
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Also keep in mind most disks have 512 byte blocks, so even if the file
> system is 8k, the disk block sizes are different. A given 8k or 1k file
> system block may not even be all in the same cylinder.
Right. Though much of the time they will be in th
Bruce Momjian <[EMAIL PROTECTED]> writes:
> I hate to do that because it makes people think something special is
> happening for hash, but it isn't. We could throw an elog(NOTICE)
> stating that hash is not recommended and btree is faster, or something
> like that.
I think the only action called
For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT
NULL (specific type of check constraint) I intend to create (as per
suggestion) pg_constraint.
conrelid
conname
contype ('c'heck, 'f'oreign key, ???)
conkey (int2vector of columns of relid, like pg_index.indkey)
connum int4 --
On Thu, 25 Apr 2002, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > 1. Theoretical proof: two components of the delay in retrieving a
> > block from disk are the disk arm movement and the wait for the
> > right block to rotate under the head.
>
> > When retrieving, say, eight adja
Neil Conway wrote:
> On Thu, 25 Apr 2002 16:38:00 -0400 (EDT)
> "Bruce Momjian" <[EMAIL PROTECTED]> wrote:
> >
> > Nice report. I think we should start thinking of hiding the hash option
> > from users, or warn them more forcefully, rather than hold it out as a
> > possible option for them.
>
>
Vince Vielhaber wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
>
> >
> > Marc is suggesting we may want to match Oracle somehow.
> >
> > I just want to have our SET work on a sane manner.
>
> As do I. But to Marc's suggestion, we discussed an oracle compatibility
> factor in the past and it
Marc G. Fournier wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
>
> >
> > Marc is suggesting we may want to match Oracle somehow.
> >
> > I just want to have our SET work on a sane manner.
>
> Myself, I wonder why Oracle went the route they went ... does anyone have
> access to a Sybase / In
I have been doing some benchmarking. The idea is to measure the difference
between PostgreSQL 7.2.1 compiled for an 8K block or a 16K block. pgbench is
almost useless for measuring changes in performance, I can't seem to get any
real consistency, so I used the osdb to measure performance.
In the
Nice test. Would you test simultaneous 'dd' on the same file, perhaps
with a slight delay between to the two so they don't read each other's
blocks?
seek() in the file will turn off read-ahead in most OS's. I am not
saying this is a major issue for PostgreSQL but the numbers would be
interesti
On Thu, 25 Apr 2002, Bruce Momjian wrote:
>
> Marc is suggesting we may want to match Oracle somehow.
>
> I just want to have our SET work on a sane manner.
Myself, I wonder why Oracle went the route they went ... does anyone have
access to a Sybase / Informix system, to confirm how they do it?
Tom Lane wrote:
> ...
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > 3. Proof by testing. I wrote a little ruby program to seek to a
> > random point in the first 2 GB of my raw disk partition and read
> > 1-8 8K blocks of data. (This was done as one I/O request.) (Using
> > the raw disk partition
On Thu, 25 Apr 2002, Bruce Momjian wrote:
>
> Marc is suggesting we may want to match Oracle somehow.
>
> I just want to have our SET work on a sane manner.
As do I. But to Marc's suggestion, we discussed an oracle compatibility
factor in the past and it was dismissed. I seem to recall someone
The idea behind hte bitmap (correct me if I'm wrong) is that when larger
allocationsa re asked for they can be quickly found and there is no need
to maintain the coalescing of smaller adjacent blocks into larger ones.
I don't know if pg does this or not, but thats the only sane reason I
can co
On Thu, 25 Apr 2002 15:07:44 EDT, Tom Lane wrote:
> F Harvell <[EMAIL PROTECTED]> writes:
> > This also poses the biggest problem in terms of legacy compatibility.
> > Perhaps the answer is to add a runtime config option (and default it
> > to ANSI) and possibly deprecate the C escaping.
>
> Whil
Marc is suggesting we may want to match Oracle somehow.
I just want to have our SET work on a sane manner.
---
Vince Vielhaber wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
>
> > Marc G. Fournier wrote:
> > > > My gu
Michael Loftis <[EMAIL PROTECTED]> writes:
> I don't know about PGs implementation but since I assume oyu all
> inhereted atleast part of it from the berkely boys you should be in very
> solid form.
One would have thought so, wouldn't one? AFAIK the hash index code is
lock-stock-and-barrel st
On Thu, 25 Apr 2002 16:38:00 -0400 (EDT)
"Bruce Momjian" <[EMAIL PROTECTED]> wrote:
>
> Nice report. I think we should start thinking of hiding the hash option
> from users, or warn them more forcefully, rather than hold it out as a
> possible option for them.
Why not do something Peter E. sugg
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > > My guess is that we should implement #1 and see what feedback we get in
> > > 7.3.
> >
> > IMHO, it hasn't been thought out well enough to be implemented yet ... the
> > options have been, but which to implement haven't ...
Nice report. I think we should start thinking of hiding the hash option
from users, or warn them more forcefully, rather than hold it out as a
possible option for them.
People think hash is best for equals-only queries, and btree for others,
and we can now see this clearly isn't the case.
Marc G. Fournier wrote:
> > My guess is that we should implement #1 and see what feedback we get in
> > 7.3.
>
> IMHO, it hasn't been thought out well enough to be implemented yet ... the
> options have been, but which to implement haven't ... right now, #1 is
> proposing to implement something t
Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > How many pre-7.2 clients are actually out there? If 'crypt' authentication
> > is deprecated in 7.2, is there any chance it will be removed in
> > 7.3? If it is, it should be safe to switch to the scheme I mentioned
> > in my previous e
Tom Lane wrote:
> F Harvell <[EMAIL PROTECTED]> writes:
> > This also poses the biggest problem in terms of legacy compatibility.
> > Perhaps the answer is to add a runtime config option (and default it
> > to ANSI) and possibly deprecate the C escaping.
>
> While I wouldn't necessarily object to
Tom Lane wrote:
>Michael Loftis <[EMAIL PROTECTED]> writes:
>
>>[ on hash vs btree indexing ]
>>Most of the time until the btree gets deep they are nearly equivalent.
>>When the tree ends up becoming many levels deep it can take longer to
>>walk than the hash.
>>
>
>Maybe. I've just complete
Michael Loftis <[EMAIL PROTECTED]> writes:
> [ on hash vs btree indexing ]
> Most of the time until the btree gets deep they are nearly equivalent.
> When the tree ends up becoming many levels deep it can take longer to
> walk than the hash.
Maybe. I've just completed a simple benchmark of btr
F Harvell <[EMAIL PROTECTED]> writes:
> This also poses the biggest problem in terms of legacy compatibility.
> Perhaps the answer is to add a runtime config option (and default it
> to ANSI) and possibly deprecate the C escaping.
While I wouldn't necessarily object to a runtime option, I do obje
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > Okay, based on this, I'm pseudo-against ... I think, for reasons of
> > reducing headaches for ppl posting, there should be some sort of 'SET
> > oracle_quirks' operation that would allow for those with largish legacy
> > app
Neil Conway <[EMAIL PROTECTED]> writes:
> How many pre-7.2 clients are actually out there? If 'crypt' authentication
> is deprecated in 7.2, is there any chance it will be removed in
> 7.3? If it is, it should be safe to switch to the scheme I mentioned
> in my previous email, which is both less c
Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > IMHO, there are two separate processes going on here:
>
> The connection you are missing is that hashed password storage is
> incompatible with crypt-style password transmission. If we force
> hashed storage then the only password tra
On Thu, 25 Apr 2002 13:32:27 -0400
"Tom Lane" <[EMAIL PROTECTED]> wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > IMHO, there are two separate processes going on here:
>
> The connection you are missing is that hashed password storage is
> incompatible with crypt-style password transmission.
Marc G. Fournier wrote:
> Okay, based on this, I'm pseudo-against ... I think, for reasons of
> reducing headaches for ppl posting, there should be some sort of 'SET
> oracle_quirks' operation that would allow for those with largish legacy
> apps trying to migrate over to do so without having to c
OK, I remember now. 'Password' is fine for MD5-encrypted pg_shadow
because you are using the password supplied over the wire to compare to
the md5. (Of couse, no one should be using 'password'.)
It is 'crypt' that is the problem. You get a random salted crypted
password from the user, and you
Bruce Momjian wrote:
> Now, with larger RAM and disk sizes, it may be time to consider larger
> page sizes, like 32k pages. That reduces the granularity of the cache,
> but it may have other performance advantages that would be worth it.
>
> What people are actually suggesting with the read-ahea
On Thu, 25 Apr 2002, Mike Mascari wrote:
> Bruce Momjian wrote:
> >
> > Marc G. Fournier wrote:
> > >
> > > Just curious here, but has anyone taken the time to see how others are
> > > doing this? For instance, if we go with 1, are going against how everyone
> > > else handles it? IMHO, its not
On Thu, 25 Apr 2002 01:50:32 -0400 (EDT)
"Bruce Momjian" <[EMAIL PROTECTED]> wrote:
> Neil Conway wrote:
> > Hi all,
> >
> > Why does the password_encryption GUC variable default to false?
> >
> > AFAICT there shouldn't be any issues with client compatibility -- in
> > fact, I'd be inclined to r
Neil Conway <[EMAIL PROTECTED]> writes:
> IMHO, there are two separate processes going on here:
The connection you are missing is that hashed password storage is
incompatible with crypt-style password transmission. If we force
hashed storage then the only password transmission style available
to
On Thu, 25 Apr 2002 10:41:56 EDT, Bruce Momjian wrote:
> Andrew Pimlott wrote:
> > I posted this some time ago to pgsql-bugs[1], to no response. So
> > I'll venture to try here.
> >
> > Postgres breaks the standard for string literals by supporting
> > C-like escape sequences. This causes pain
Bruce Momjian wrote:
>
> Marc G. Fournier wrote:
> >
> > Just curious here, but has anyone taken the time to see how others are
> > doing this? For instance, if we go with 1, are going against how everyone
> > else handles it? IMHO, its not a popularity contest ...
>
> Yes, good point. I don'
On Thu, 2002-04-25 at 12:47, Curt Sampson wrote:
> On Thu, 25 Apr 2002, Lincoln Yeoh wrote:
>
> > I think the raw partitions will be more trouble than they are worth.
> > Reading larger chunks at appropriate circumstances seems to be the "low
> > hanging fruit".
>
> That's certainly a good start
Marc G. Fournier wrote:
>
> Just curious here, but has anyone taken the time to see how others are
> doing this? For instance, if we go with 1, are going against how everyone
> else handles it? IMHO, its not a popularity contest ...
Yes, good point. I don't know that they use SET, but if they
Actually, this brings up a different point. We use 8k blocks now
because at the time PostgreSQL was developed, it used BSD file systems,
and those prefer 8k blocks, and there was some concept that an 8k write
was atomic, though with 512 byte disk blocks, that was incorrect. (We
knew that at the
On Thu, 25 Apr 2002 09:04:07 -0400
"mlw" <[EMAIL PROTECTED]> wrote:
> I am going to compare a 16KB PostgreSQL system to an 8KB system. I am working
> on the assumption that 16K takes about as long to read as 8K, and That the CPU
> overhead of working with a 16K block is not too significant.
>
>
Michael Loftis wrote:
> A Block-sized read will not be broken up. But if you're reading ina
> size bigger than the underlying systems block sizes then it can get
> broken up.
>
> So yes a sequential read will get broken up. A single read request for
> a block may or may not get broken up.
Curt Sampson wrote:
> 3. Proof by testing. I wrote a little ruby program to seek to a
> random point in the first 2 GB of my raw disk partition and read
> 1-8 8K blocks of data. (This was done as one I/O request.) (Using
> the raw disk partition I avoid any filesystem buffering.) Here are
> typica
Andrew Pimlott wrote:
> I posted this some time ago to pgsql-bugs[1], to no response. So
> I'll venture to try here.
>
> Postgres breaks the standard for string literals by supporting
> C-like escape sequences. This causes pain for people trying to
> write portable applications. Is there any h
Mike Biamonte wrote:
>
>
> Caveat: I'm not a pg hacker, I apologize
> in advance if this is a dumb question, but
> it has been nagging at me, and I don't
> know who else to ask.
>
> If the WAL is a record of all transactions,
> and if the checkpoint process can be managed
> tightly, is it possi
Hello,
there's a new archive available with a bugfix for handling null values.
Thanks to Steve Head for reporting this.
http://ads.ufp.de/projects/Pg/
Regards
--
Andreas 'ads' Scherbaum
---(end of broadcast)
Sander Steffann wrote:
> > What about a SET variable that controls the behaviour of
> > SET variables :-)
>
> Or two commands for the same thing:
> - a SET command that behaves as it does now
> - a TSET command that is transaction-aware
>
> Ouch... :-)
> Sander
Naw, that's far too eas
Curt Sampson <[EMAIL PROTECTED]> writes:
> 1. Theoretical proof: two components of the delay in retrieving a
> block from disk are the disk arm movement and the wait for the
> right block to rotate under the head.
> When retrieving, say, eight adjacent blocks, these will be spread
> across no mor
Jean-Paul ARGUDO wrote:
>
> > I know with toast, block size is no longer an issue, but 8K is not a lot these
> > days, and it seems like a lot of syscall and block management overhead could be
> > reduced by doubling it. Any comments?
>
> IMHO, I think this would enhance performances only if tup
I posted this some time ago to pgsql-bugs[1], to no response. So
I'll venture to try here.
Postgres breaks the standard for string literals by supporting
C-like escape sequences. This causes pain for people trying to
write portable applications. Is there any hope for an option to
follow the st
Caveat: I'm not a pg hacker, I apologize
in advance if this is a dumb question, but
it has been nagging at me, and I don't
know who else to ask.
If the WAL is a record of all transactions,
and if the checkpoint process can be managed
tightly, is it possible to copy the WAL
files from a master D
I am going to compare a 16KB PostgreSQL system to an 8KB system. I am working
on the assumption that 16K takes about as long to read as 8K, and That the CPU
overhead of working with a 16K block is not too significant.
I know with toast, block size is no longer an issue, but 8K is not a lot these
On Thu, Apr 25, 2002 at 12:42:00PM +0100, Lee Kindness wrote:
> Assuming the following fetch statement in embedded SQL/C:
>
> EXEC SQL FETCH ALL IN selectFromTable_cur INTO
> :array1,
> :array2;
>
> is memory automatically allocated (by experimentation I guess so)?
Only if the poin
> What about a SET variable that controls the behaviour of
> SET variables :-)
Or two commands for the same thing:
- a SET command that behaves as it does now
- a TSET command that is transaction-aware
Ouch... :-)
Sander
---(end of broadcast)---
Just curious here, but has anyone taken the time to see how others are
doing this? For instance, if we go with 1, are going against how everyone
else handles it? IMHO, its not a popularity contest ...
Personally, I do agree with #1, but I'm curious as to how those coming
from other DBMS are go
Assuming the following fetch statement in embedded SQL/C:
EXEC SQL FETCH ALL IN selectFromTable_cur INTO
:array1,
:array2;
is memory automatically allocated (by experimentation I guess so)?
Should the input pointers be NULL initialised? How should the memory
be freed?
Assuming
On Thu, 25 Apr 2002, Lincoln Yeoh wrote:
> I think the raw partitions will be more trouble than they are worth.
> Reading larger chunks at appropriate circumstances seems to be the "low
> hanging fruit".
That's certainly a good start. I don't know if the raw partitions
would be more trouble than
On Thu, 25 Apr 2002, Curt Sampson wrote:
> Here's the ratio table again, with another column comparing the
> aggregate number of requests per second for one process and four
> processes:
>
Just for interest, I ran this again with 20 processes working
simultaneously. I did six runs at each blockr
At 12:19 PM 4/25/02 +0900, Curt Sampson wrote:
>Grabbing bigger chunks is always optimal, AFICT, if they're not
>*too* big and you use the data. A single 64K read takes very little
>longer than a single 8K read.
Yes I agree that if sequential scans are done reading ahead helps.
And often doesn't
On Thu, 25 Apr 2002, Bruce Momjian wrote:
> Well, we are guilty of trying to push as much as possible on to other
> software. We do this for portability reasons, and because we think our
> time is best spent dealing with db issues, not issues then can be deal
> with by other existing software, a
On Wed, 24 Apr 2002, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > Grabbing bigger chunks is always optimal, AFICT, if they're not
> > *too* big and you use the data. A single 64K read takes very little
> > longer than a single 8K read.
>
> Proof?
Well, there are various sorts o
69 matches
Mail list logo
