Tom Lane [EMAIL PROTECTED] writes:
Mario Weilguni [EMAIL PROTECTED] writes:
Why is floor not working anymore?
Mph. Seems we have floor(numeric) but not floor(float8), and the latter
is what you need here.
Sorry, I missed much of the casting discussion -- but is there a
reason why we
Did anybody think about threaded sorting so far?
Assume an SMP machine. In the case of building an index or in the case
of sorting a lot of data there is just one backend working. Therefore
just one CPU is used.
What about starting a thread for every temporary file being created?
This way
On 4 Oct 2002 at 9:46, Hans-Jürgen Schönig wrote:
Did anybody think about threaded sorting so far?
Assume an SMP machine. In the case of building an index or in the case
of sorting a lot of data there is just one backend working. Therefore
just one CPU is used.
What about starting a
I am using postgres 7.2, and have rule on a table which causes a notify if
an insert/update/delete is performed on the table.
The table is very very small.
When performing a simple (very simple) update on the table this takes about
3 secs, when I remove the rule it is virtually instantaneous.
The
Mike == Mike Mascari [EMAIL PROTECTED] writes:
Mike Tom Lane wrote:
Yury Bokhoncovich [EMAIL PROTECTED] writes:
As reported by my friend: Oracle 8.1.7 (ver.9 behaves the same way):
[ to_char(sysdate) advances in a transaction ]
Now I'm really confused; this directly
Neil Conway [EMAIL PROTECTED] writes:
Sorry, I missed much of the casting discussion -- but is there a
reason why we can't cast from float8 - numeric implicitely? IIRC the
idea was to allow implicit casts from lower precision types to higher
precision ones.
The implicit casting hierarchy is
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes:
Did anybody think about threaded sorting so far?
Assume an SMP machine. In the case of building an index or in the case
of sorting a lot of data there is just one backend working. Therefore
just one CPU is used.
What about
I wouldn't hold your breath for any form of threading. Since PostgreSQL
is process based, you might consider having a pool of sort processes
which address this but I doubt you'll get anywhere talking about threads
here.
Greg
On Fri, 2002-10-04 at 02:46, Hans-Jürgen Schönig wrote:
Did anybody
Steve King [EMAIL PROTECTED] writes:
I am using postgres 7.2, and have rule on a table which causes a notify if
an insert/update/delete is performed on the table.
The table is very very small.
When performing a simple (very simple) update on the table this takes about
3 secs, when I remove
On Thu, 03 Oct 2002 14:50:00 -0400, Tom Lane [EMAIL PROTECTED]
wrote:
indexCorrelation is calculated by dividing the correlation of the
first index column by the number of index columns.
Yeah, I concluded later that that was bogus. I've been thinking of
just using the correlation of the first
On Fri, 2002-10-04 at 09:40, Hans-Jürgen Schönig wrote:
I had a brief look at the code used for sorting. It is very well
documented so maybe it is worth thinking about a parallel algorithm.
When talking about threads: A pool of processes for sorting? Maybe this
could be useful but I
On Fri, 2002-10-04 at 10:37, Hans-Jürgen Schönig wrote:
My concern was that a process model might be a bit too slow for that but
if we had processes in memory this would be wonderful thing.
Yes, that's the point of having a pool. The idea is not only do you
avoid process creation and
On Thu, 3 Oct 2002 10:45:08 -0600 (MDT), scott.marlowe
[EMAIL PROTECTED] wrote:
effective cache size is the default (i.e. commented out)
The default is 1000, meaning ca. 8 MB, which seems to be way too low.
If your server is (almost) exclusively used by Postgres, try setting
it to represent
On Fri, 2002-10-04 at 12:26, Bruce Momjian wrote:
Added to TODO:
* Allow sorting to use multiple work directories
Why wouldn't that fall under the table space effort???
Greg
signature.asc
Description: This is a digitally signed message part
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Bingo! Want to increase sorting performance, give it more I/O
bandwidth, and it will take 1/100th of the time to do threading.
Added to TODO:
* Allow sorting to use multiple work directories
Yeah, I like that. Actually it
Hello hackers,
I'm thinking about ALTER TABLE ... ADD COLUMN working properly when
child tables already contain the column.
I have two proposals. First one:
There are two cases: one when specifying ALTER TABLE ONLY, and other
when specifying recursive (not ONLY).
In the first (ONLY) case,
Hello hackers,
I'm looking at implementing the btree reorganizer described in On-line
reorganization of sparsely-..., ACM SIGMOD proceedings 1996, by Zou and
Salzberg. It seems to me I'll have to add some amount of lock types
in the lock manager. Does that bother you?
--
Alvaro Herrera
Alvaro Herrera [EMAIL PROTECTED] writes:
I'm thinking about ALTER TABLE ... ADD COLUMN working properly when
child tables already contain the column.
There are two cases: one when specifying ALTER TABLE ONLY, and other
when specifying recursive (not ONLY).
I think ALTER TABLE ONLY ... ADD
Alvaro Herrera [EMAIL PROTECTED] writes:
I'm looking at implementing the btree reorganizer described in On-line
reorganization of sparsely-..., ACM SIGMOD proceedings 1996, by Zou and
Salzberg. It seems to me I'll have to add some amount of lock types
in the lock manager. Does that bother
I wrote:
... most file systems can't process fsync's
simultaneous with other writes, so those writes block because the file
system grabs its own internal locks.
tom lane replies:
Oh? That would be a serious problem, but I've never heard that asserted
before. Please provide some
On Fri, Oct 04, 2002 at 05:57:02PM -0400, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
I'm thinking about ALTER TABLE ... ADD COLUMN working properly when
child tables already contain the column.
There are two cases: one when specifying ALTER TABLE ONLY, and other
when
Curtis Faith [EMAIL PROTECTED] writes:
It looks to me like BufferAlloc will simply result in a call to
BufferReplace smgrblindwrt write for md storage manager objects.
This means that a process will block while the write of dirty cache
buffers takes place.
I think Tom was suggesting that
Greg Copeland wrote:
-- Start of PGP signed section.
On Fri, 2002-10-04 at 12:26, Bruce Momjian wrote:
Added to TODO:
* Allow sorting to use multiple work directories
Why wouldn't that fall under the table space effort???
Yes, but we make it a separate item so we are sure that is
After some research I still hold that fsync blocks, at least on
FreeBSD. Am I missing something?
Here's the evidence:
Code from: /usr/src/sys/syscalls/vfs_syscalls
int
fsync(p, uap)
struct proc *p;
struct fsync_args /* {
syscallarg(int) fd;
} */ *uap;
{
On Fri, 2002-10-04 at 18:03, Neil Conway wrote:
Curtis Faith [EMAIL PROTECTED] writes:
It looks to me like BufferAlloc will simply result in a call to
BufferReplace smgrblindwrt write for md storage manager objects.
This means that a process will block while the write of dirty cache
Bruce Momjian [EMAIL PROTECTED] writes:
Yes, I realize it is during parsing. I was just wondering if making
constants coming in from the parser NUMERIC is a performance hit?
Offhand I don't see a reason to think that coercing to NUMERIC (and then
something else) is slower than coercing to
I see. I just always assumed that it would be done as part of table
space effort as it's such a defacto feature.
I am curious as to why no one has commented on the other rather obvious
performance enhancement which was brought up in this thread. Allowing
for parallel sorting seems rather
Greg Copeland wrote:
-- Start of PGP signed section.
I see. I just always assumed that it would be done as part of table
space effort as it's such a defacto feature.
I am curious as to why no one has commented on the other rather obvious
performance enhancement which was brought up in this
Neil Conway [EMAIL PROTECTED] writes:
Curtis Faith [EMAIL PROTECTED] writes:
It looks to me like BufferAlloc will simply result in a call to
BufferReplace smgrblindwrt write for md storage manager objects.
This means that a process will block while the write of dirty cache
buffers takes
I resent this since it didn't seem to get to the list.
After some research I still hold that fsync blocks, at least on
FreeBSD. Am I missing something?
Here's the evidence:
Code from: /usr/src/sys/syscalls/vfs_syscalls
int
fsync(p, uap)
struct proc *p;
struct fsync_args /* {
On Thu, 3 Oct 2002, Tom Lane wrote:
That seems a little weird. Does Perl really expect people to do that
(ie, is it a documented part of some API)? I wonder whether there is
some other action that we're supposed to take instead, but are
missing...
Not that I know of: clearing out the $@
John Worsley [EMAIL PROTECTED] writes:
Yeah, that's a cleaner solution. I take it anything pstrdup'd by
PostgreSQL gets freed automatically by the backend?
Pretty much. The only situation where it wouldn't be is if
CurrentMemoryContext is pointing at TopMemoryContext or another
long-lived
Curtis Faith [EMAIL PROTECTED] writes:
After some research I still hold that fsync blocks, at least on
FreeBSD. Am I missing something?
Here's the evidence:
[ much snipped ]
vp = (struct vnode *)fp-f_data;
vn_lock(vp, LK_EXCLUSIVE | LK_RETRY, p);
Hm, I take it a
Well, that's why I was soliciting developer input as to exactly what
goes on with sorts. From what I seem to be hearing, all sorts result in
temp files being created and/or used. If that's the case then yes, I
can understand the fixation. Of course that opens the door for it being
a horrible
Bruce Momjian [EMAIL PROTECTED] writes:
Tom, what temp files do we use that aren't for sorting; I forgot.
MATERIALIZE plan nodes are the only thing I can think of offhand that
uses a straight temp file. But ISTM that if this makes sense for
our internal temp files, it makes sense for
Greg Copeland wrote:
-- Start of PGP signed section.
Well, that's why I was soliciting developer input as to exactly what
goes on with sorts. From what I seem to be hearing, all sorts result in
temp files being created and/or used. If that's the case then yes, I
can understand the fixation.
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Tom, what temp files do we use that aren't for sorting; I forgot.
MATERIALIZE plan nodes are the only thing I can think of offhand that
uses a straight temp file. But ISTM that if this makes sense for
our internal temp files, it
It appears the fsync problem is pervasive. Here's Linux 2.4.19's
version from fs/buffer.c:
lock- down(inode-i_sem);
ret = filemap_fdatasync(inode-i_mapping);
err = file-f_op-fsync(file, dentry, 1);
if (err !ret)
ret = err;
err
On Fri, 2002-10-04 at 14:31, Bruce Momjian wrote:
We use tape sorts, ala Knuth, meaning we sort in memory as much as
possible, but when there is more data than fits in memory, rather than
swapping, we write to temp files then merge the temp files (aka tapes).
Right, which is what I originally
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
... But ISTM that if this makes sense for
our internal temp files, it makes sense for user-created temp tables
as well.
Yes, I was thinking that, but of course, those are real tables, rather
than just files. Not sure how clean it
Bruce Momjian wrote:
I am again confused. When we do write(), we don't have to lock
anything, do we? (Multiple processes can write() to the same file just
fine.) We do block the current process, but we have nothing else to do
until we know it is written/fsync'ed. Does aio more easily
On 3 Oct 2002 at 18:53, Manfred Koizar wrote:
On Thu, 03 Oct 2002 21:47:03 +0530, Shridhar Daithankar
[EMAIL PROTECTED] wrote:
I believe that was vacuum analyze only.
Well there is
VACUUM [tablename];
and there is
ANALYZE [tablename];
And
VACUUM ANALYZE
I'd give you the first and third of those. As Andrew noted, the
argument that it's more standard-compliant is not very solid.
The standard doesn't say anything about transaction in this regard.
Yes, it sais statement.
Note also, that a typical SELECT only session would not advance
Howdy All,
You have to explicitly commit transactions in oracle using SQL*Plus.
However, DUAL (eg. SELECT current_timestamp FROM DUAL;) is special in this
case. It is a table in the sys schema, used for selecting constants,
pseudo-columns, etc.
I'm not sure if this helps but see:
Hello Peter,
Tuesday, October 01, 2002, 1:42:46 AM, you wrote:
PE Bruce Momjian writes:
Peter, the author is questioning why his Makefile changes were wrong.
Would you elaborate?
PE Because we rely on the built-in library lookup functionality instead of
PE hardcoding the full file name.
The original tester says this is an anonymous procedure.
On 30 Sep 2002 at 15:07, Bruce Momjian wrote:
It is not clear to me; is this its own transaction or a function
call?
--
-
Dan Langille wrote:
And just
Hi Justin,
--On Donnerstag, 3. Oktober 2002 09:23 +1000 Justin Clift
[EMAIL PROTECTED] wrote:
Hi Michael,
Michael Paesold wrote:
snip
Hi Justin,
I am from Austria, and I would like to help. I could provide a German
translation. The Babelfish's translation is really funny. Machine
Hi Justin,
Good point. For the moment we've whipped up that MS Excel document
(created in OpenOffice of course) of all the English text strings in the
site and emailed it to the volunteers. :)
Btw. did you ever unzip the native OpenOffice (aka StarOffice)
file?
So far community members
Can you comment on the tools you are using to do the insertions (Perl,
Java?) and the distribution of data (all random, all static), and the
transaction scope (all inserts in one transaction, each insert as a
single transaction, some group of inserts as a transaction).
I'd be curious what
Hi Michael,
yeah, I got :-)
I'm busy reviewing :-)
Regards
Tino
--On Donnerstag, 3. Oktober 2002 21:54 +0200 Michael Paesold
[EMAIL PROTECTED] wrote:
Tino Wildenhain [EMAIL PROTECTED] wrote:
Hi Justin,
Good point. For the moment we've whipped up that MS Excel document
(created in
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes:
Note also, that a typical SELECT only session would not advance
CURRENT_TIMESTAMP at all in the typical autocommit off mode that
the Spec is all about.
True, but the spec also says to default to serializable transaction
mode. So in a
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I am confused how yours differs from mine. I don't see how the last
matching tagged query would not be from an INSTEAD rule.
You could have both INSTEAD and non-INSTEAD rules firing for the same
original query. If the
Curtis Faith wrote:
Bruce Momjian wrote:
I may be missing something here, but other backends don't block while
one writes to WAL.
I don't think they'll block until they get to the fsync or XLogWrite
call while another transaction is fsync'ing.
I'm no Unix filesystem expert but I don't
Hans-Jürgen Schönig wrote:
Did anybody think about threaded sorting so far?
Assume an SMP machine. In the case of building an index or in the case
of sorting a lot of data there is just one backend working. Therefore
just one CPU is used.
What about starting a thread for every temporary
Tom Lane wrote:
Moving to the left requires an explicit cast (or at least an assignment
to a column). I know this looks strange to someone who knows that our
numeric type beats float4/float8 on both range and precision, but it's
effectively mandated by the SQL spec. Any combination of exact
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I am confused how yours differs from mine. I don't see how the last
matching tagged query would not be from an INSTEAD rule.
You could have both INSTEAD and non-INSTEAD rules firing for the
Bruce Momjian [EMAIL PROTECTED] writes:
Do we know that defaulting floating constants will not be a performance
hit?
Uh ... what's your concern exactly? The datatype coercion (if any) will
happen once at parse time, not at runtime.
regards, tom lane
Bruce Momjian [EMAIL PROTECTED] wrote:
Yes, clearly, we will need to have all three time values available to
users. With three people now suggesting we don't change, I will just
add to TODO:
Add now(transaction|statement|clock) functionality
Is that good?
CURRENT_TIMESTAMP etc. are
On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian
[EMAIL PROTECTED] wrote:
so I propose we handle
INSTEAD rules this way: that we return the oid and tuple count of the
last INSTEAD rule query with a tag matching the main query.
Bruce, this won't work for this example
CREATE RULE
I wrote:
I'm no Unix filesystem expert but I don't see how the OS can
handle multiple writes and fsyncs to the same file descriptors without
blocking other processes from writing at the same time. It may be that
there are some clever data structures they use but I've not seen huge
I, probably, will have a chance to work with postgres on Linux IA-64.
Is there any optimization for postgresql ?
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical
Tom Lane [EMAIL PROTECTED] wrote:
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes:
Note also, that a typical SELECT only session would not advance
CURRENT_TIMESTAMP at all in the typical autocommit off mode that
the Spec is all about.
True, but the spec also says to default to
On Fri, 4 Oct 2002, Bruce Momjian wrote:
Hans-Jürgen Schönig wrote:
Did anybody think about threaded sorting so far?
Assume an SMP machine. In the case of building an index or in the case
of sorting a lot of data there is just one backend working. Therefore
just one CPU is used.
Tom Lane wrote:
You can create as many rules as you want. One reasonably likely
scenario is that you have a view, you make an ON INSERT DO INSTEAD
rule to support insertions into the view (by inserting into some
underlying table(s) instead), and then you add some not-INSTEAD
rules to
Manfred Koizar wrote:
On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian
[EMAIL PROTECTED] wrote:
so I propose we handle
INSTEAD rules this way: that we return the oid and tuple count of the
last INSTEAD rule query with a tag matching the main query.
Bruce, this won't work for this
Michael Paesold wrote:
Tom Lane [EMAIL PROTECTED] wrote:
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes:
Note also, that a typical SELECT only session would not advance
CURRENT_TIMESTAMP at all in the typical autocommit off mode that
the Spec is all about.
True, but the spec
Curtis Faith wrote:
Yes, I can see some contention, but what does aio solve?
Well, theoretically, aio lets the file system handle the writes without
requiring any locks being held by the processes issuing those reads.
The disk i/o scheduler can therefore issue the writes using
Hans-Jürgen Schönig wrote:
Threads are bad - I know ...
I like the idea of a pool of processes instead of threads - from my
point of view this would be useful.
I am planning to run some tests (GEQO, AIX, sorts) as soon as I have
time to do so (still too much work ahead before :( ...).
scott.marlowe wrote:
We haven't thought about it yet because there are too many buggy thread
implementations. We are probably just now getting to a point where we
can consider it. However, lots of databases have moved to threads for
all sorts of things and ended up with a royal mess of
Bruce Momjian [EMAIL PROTECTED] wrote:
That is a very good point. At least with serializable transactions it
seems
perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you
think
about read-commited level? Can time be commited? ;-)
It would be even more surprising to new
Bruce Momjian wrote:
scott.marlowe wrote:
snip
It seems like sometimes we consider these issues more from the one or two
SCSI drives perspective insted of the big box o drives perspective.
Yes, it is mostly for non-RAID drives, but also, sometimes single drives
can be faster. When you
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Do we know that defaulting floating constants will not be a performance
hit?
Uh ... what's your concern exactly? The datatype coercion (if any) will
happen once at parse time, not at runtime.
Yes, I realize it is during parsing.
Oleg Bartunov wrote:
I, probably, will have a chance to work with postgres on Linux IA-64.
Is there any optimization for postgresql ?
None we know of. I think people have already gotten it working.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED]
Hi Justin,
you want probably use the language-negotiation
rather then a query variable :-)
Regards
Tino
--On Donnerstag, 3. Oktober 2002 08:53 +1000 Justin Clift
[EMAIL PROTECTED] wrote:
Hi everyone,
Have just put together a prototype page to show off the multi-lingual
capabilities that
Hi everyone,
Have just put together a prototype page to show off the multi-lingual
capabilities that the Advocacy sites' infrastructure has:
http://advocacy.postgresql.org/?lang=de
The text was translated to german via Altavista's Babelfish, so it's
probably only about 80% accurate, but it
Bruce Momjian [EMAIL PROTECTED] writes:
Bingo! Want to increase sorting performance, give it more I/O
bandwidth, and it will take 1/100th of the time to do threading.
Added to TODO:
* Allow sorting to use multiple work directories
Yeah, I like that. Actually it should apply to all
Greg Copeland [EMAIL PROTECTED] writes:
... I can understand why
addressing the seemingly more common I/O bound case would receive
priority, however, I'm at a loss as to why the other would be completely
ignored.
Bruce already explained that we avoid threads because of portability and
On Fri, 2002-10-04 at 15:07, Tom Lane wrote:
the sort comparison function can be anything, including user-defined
code that does database accesses or other interesting stuff. This
This is something that I'd not considered.
would mean that the sort auxiliary process would have to adopt the
Curtis Faith [EMAIL PROTECTED] writes:
... most file systems can't process fsync's
simultaneous with other writes, so those writes block because the file
system grabs its own internal locks.
Oh? That would be a serious problem, but I've never heard that asserted
before. Please provide some
... most file systems can't process fsync's
simultaneous with other writes, so those writes block because the file
system grabs its own internal locks.
Oh? That would be a serious problem, but I've never heard that asserted
before. Please provide some evidence.
On a filesystem
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes:
To make this competitive, the WAL writes would need to be improved to
do more than one block (up to 256k or 512k per write) with one write call
(if that much is to be written for this tx to be able to commit).
This should actually not be
Tom Lane writes:
$ man flock
No manual entry for flock.
$
HPUX has generally taken the position of adopting both BSD and SysV
features, so if it doesn't exist here, it's not portable to older
Unixen ...
If only local locking is at issue then finding any one of fcntl()
locking, flock(),
Giles Lean [EMAIL PROTECTED] wrote:
Tom Lane writes:
$ man flock
No manual entry for flock.
$
HPUX has generally taken the position of adopting both BSD and SysV
features, so if it doesn't exist here, it's not portable to older
Unixen ...
If only local locking is at issue
Hmmm ... if you were willing to dedicate a half meg or meg of shared
memory for WAL buffers, that's doable.
Yup, configuring Informix to three 2 Mb buffers (LOGBUF 2048) here.
However, this would only be a win if you had few and large transactions.
Any COMMIT will force a write of
Curtis Faith writes:
I'm no Unix filesystem expert but I don't see how the OS can handle
multiple writes and fsyncs to the same file descriptors without
blocking other processes from writing at the same time.
Why not? Other than the necessary synchronisation for attributes such
as file
Michael Paesold wrote:
Giles Lean [EMAIL PROTECTED] wrote:
Mind you NFS users are currently entirely unprotected from someone
starting a postmaster on a different NFS client using the same data
directory right now, which file locking would prevent. So there is
some win for NFS users as well as
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
... But ISTM that if this makes sense for
our internal temp files, it makes sense for user-created temp tables
as well.
Yes, I was thinking that, but of course, those are real tables, rather
than just files.
Tom Lane wrote:
Curtis Faith [EMAIL PROTECTED] writes:
After some research I still hold that fsync blocks, at least on
FreeBSD. Am I missing something?
Here's the evidence:
[ much snipped ]
vp = (struct vnode *)fp-f_data;
vn_lock(vp, LK_EXCLUSIVE | LK_RETRY,
OK, are we agreed to leave CURRENT_TIMESTAMP/now() alone and just add
now(string)? If no one replies, I will assume that is a yes and I
will add it to TODO.
---
Michael Paesold wrote:
Bruce Momjian [EMAIL PROTECTED]
89 matches
Mail list logo
