So basically, glibc's qsort is bad enough that even a
10%-more-comparisons advantage doesn't save it.
Do those numbers look very different if you have lots of
columns or if you're sorting on something like an array or a ROW?
Imho, that also is an argument for using our own qsort.
It can
Ok. So what you want is something that checks that it's
=1.875 but specifically not 2.1?
Might be a while before I can submit an updated patch for
that, may need to rewrite the whole script in perl to do that
:-( .bat files are horribly limited in what they can do.
Since we are on NT
-Original Message-
From: Magnus Hagander [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 04, 2006 6:01 PM
To: Zeugswetter Andreas ADI SD
Cc: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] [PATCHES] vcbuild bison check
Ok. So what you want is something that checks
And given that, they're going to get the latest by default,
or 1.875 if they read the (currently being written) README.
The point was, that = 2.2 won't be allowed when it comes out for win32,
even if it should work.
Andreas
---(end of
I'm still interested to experiment with MemSet-then-strlcpy
for namestrcpy, but given the LENCPY results this may be a loser too.
Um, why not strlcpy then MemSet the rest ?
Andreas
---(end of broadcast)---
TIP 4: Have you searched our list
Magnus, is this the right fix?
Well, actually msdn states:
Return Value
If successful, _setmode returns the previous translation mode. A return
value of -1 indicates an error
So, shouldn't we be testing for -1 instead of 0 ?
The thing is probably academic, since _setmode is only supposed
I'm still interested to experiment with MemSet-then-strlcpy for
namestrcpy, but given the LENCPY results this may be a loser too.
Um, why not strlcpy then MemSet the rest ?
That's what strncpy() is supposed to be doing.
Yes, but it obviously does not in some ports, and that was the
The existing geometric containment tests seem to be nonstrict, so
if
we wanted to leave room to add strict ones later, it might be best
to
settle on
x @= yx contains or equals y
x =@ yx is contained in or equals y
reserving @ and @ for future strict
x @ y means x is contained in y
ltree @ ltree
If you consider ltree entries to be sets containing all their
children
then those sound consistent.
Now we get to decide whether @ was better than the now proposed @
:-)
I like @. (or we stay clear by using the inet ops)
With this approach, you still have to update your rules if
you want to support RETURNING on your views --- but if you
don't update them, you don't have a security hole. Basically
the standard setup for an updatable view would use
ON INSERT DO INSTEAD INSERT INTO ... RETURNING ...
I'd wish that we reconsider when and how prepared statements are
used. The JDBC interface and PL/pgSQL are frequently noticed
perpetrators, but the problem is really all over the place.
AFAIK those are the only two places where preparation is
the default
... what else were you
My bet is something depending on GetTickCount to measure elapsed
time
(and no, it's not used in the core Postgres code, but you've got
plenty of other possible culprits in that stack).
This doesn't quite make sense. The only reason we have to reboot is
because PostgreSQL no longer
Anyway, your plan assumes that you have information to work
with. The current system plans prepared queries with no
information at all about parameters and people are advocating
to keep it that way. I think a good first step would be the
plan on first execution, like Oracle does.
Yup,
I don't chime in very often, but I do think the refusal to
incorporate
hints into the planner system is fantastically stubborn and
nonsensical.
What is actually fantastically nonsensical about this is that
the issues I outlined about prepared statements would merely
become worse if
How about prepared means really prepared... in the sense of
parsed, analyzed all sensible plans, and save a meta-plan which
based
on current statistics and parameter values chooses one of the
considered (and cached) plans ?
I don't think this could solve one particularly frequent
What bothers me about what we have now is that we have optional
keywords before and after INDEX, rather than only between
CREATE and INDEX.
Yeah, putting them both into that space seems consistent to
me, and it will fix the problem of making an omitted index
name look like a valid
precedent syntax (Oracle, Informix) uses the keyword ONLINE
at the end:
CREATE INDEX blabla_x0 ON blabla (a,b) ONLINE;
That was what the patch originally used, but it was changed
because it made difficult for psql to auto-complete that.
That is imho not enough of a reason to divert.
Is it not possible to brute force this adding an AM method to insert
without the uniqueness check?
Hm. Actually there already is a feature of aminsert to allow
suppressing the unique check, but I'm not sure whether using
it for RECENTLY_DEAD tuples helps. Seems like we have to
I noticed a minor annoyance while testing: when the system is
completely idle, you get a forced segment switch every
checkpoint_timeout seconds, even though there is nothing
useful to log. The checkpoint code is smart enough not to do
a checkpoint if nothing has happened since the last
= select now()coalesce('Jul 14 2006 9:16:47AM');
The only bug I see here is that implicit coercions to text
are a bad idea :-( --- IMHO it would be better if your first
query failed instead of giving you unexpected behavior.
:-) We know that you think that Tom, but a lot of us do not
I've seen a few EAV designs in practice. They've all been
problematic. I'd like to have a better way of dealing with
them. Which is why I'm tentatively suggesting support for
inheritance and constraints in views. If there's some other
way to achieve constraint based exclusion across a
I've seen a few EAV designs in practice. They've all been
problematic.
I'd like to have a better way of dealing with them. Which is why I'm
tentatively suggesting support for inheritance and constraints in
views. If there's some other way to achieve constraint based
exclusion
across
Will this patch make it into 8.2?
http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php
It's a really nice feature, would be extremly useful with tools
like pgpool.
No, it will not because RESET CONNECTION can mess up interface code
that doesn't want the
If you can open a command shell you can get the OS version with the
'ver' command under Windows:
C:\ver
Microsoft Windows XP [Version 5.1.2600]
How do you do this from a program though. Under UNIX uname() is a
function call as well as a program. It returns the os name,
- add a new boolean to pg_operator to allow us to define which
operators offer true equality ...
This would be useful for other purposes too, as we keep coming up
against what's the equality operator for this datatype problems.
However, the restriction to true equality, such that
There is a problem of implication here, AFAICS:
When a user SQL asks
WHERE col1 = 7
which equality level is meant when several exist?
Well, the operator must be unique, so there is no problem.
Unique in the sense that an operator with the same name ('=' in this
case)
and argument
OK, but the point of the question is that constantly updating
a single row steadily degrades performance, would
delete/insery also do the same?
Yes, there is currently no difference (so you should do the update).
Of course performance only degrades if vaccuum is not setup correctly.
Is there a difference in PostgreSQL performance between these two
different strategies:
if(!exec(update foo set bar='blahblah' where name = 'xx'))
exec(insert into foo(name, bar) values('xx','blahblah'); or
In pg, this strategy is generally more efficient, since a pk failing
Here is an overview of the SITC method:
http://momjian.us/cgi-bin/pgsitc
A pretty fundamental problem is that the method assumes it's
OK to change the CTID of a live tuple (by swapping its item
pointer with some expired version). It is not --- this will break:
I am having
And anyway, ctid is a usable unique row identifier only within
read-only transactions, or not ?
actually for as long as no vacuum comes along. This would change
with SITC. (Maybe it would help to only reuse old versions of the same
row,
then anybody holding a ctid would at least be still
Very nice explanation, thanks Alvaro.
2. Mark frozen databases specially somehow.
To mark databases frozen, we need a way to mark tables as frozen.
How do we do that? As I explain below, this allows some nice
optimizations, but it's a very tiny can full of a huge amount of
Suggest that we prevent write operations on Frozen tables by
revoking
all INSERT, UPDATE or DELETE rights held, then enforcing a check
during GRANT to prevent them being re-enabled. Superusers would need
to check every time. If we dont do this, then we will have two
On 6/25/2006 10:12 PM, Bruce Momjian wrote:
When you are using the update chaining, you can't mark that index
row
as dead because it actually points to more than one row on the
page,
some are non-visible, some are visible.
Back up the truck ... you mean in the current code base we
head of the chain yet. With an index scan, finding the head is
easy,
but for a sequential scan, it seems more difficult, and we don't
have
any free space in the tail of the chain to maintain a pointer to the
head.
Thinking some more, there will need to be a bit to uniquely
identify
back and forth the data between an archive table and the live
table, based on how active the groups are, I can't imagine
any other way of partitioning it. And that would also mean
some quite big load given the pretty high dynamics of the groups.
You said the activity comes in bursts per
This could be a solution... but then I'm not sure how well would do
queries which need the first 10 records based on some criteria which
does not include the group id. I guess limit queries across the
union
of the partitions don't work too well for now, and we do
have such queries.
Each time the record is updated, a new version is created, thus
lengthening the correct version search each time row is accessed,
until, of course, the next vacuum comes along and corrects the
index
to point to the latest version of the record.
Is that a fair explanation?
No,
Indeed, I've been wondering lately if we shouldn't resurrect
LET_OS_MANAGE_FILESIZE and make that the default on systems with
largefile support. If nothing else it would cut down on open/close
overhead on very large relations.
I'd still put some limit on the filesize, else you cannot
Tom Lane [EMAIL PROTECTED] writes:
Indeed, I've been wondering lately if we shouldn't resurrect
LET_OS_MANAGE_FILESIZE and make that the default on systems with
largefile support. If nothing else it would cut down on open/close
overhead on very large relations.
I'd still put some
I would like to see some checking of this, though. Currently
I'm doing testing of PostgreSQL under very large numbers of
connections (2000+) and am finding that there's a huge volume
of xlog output ... far more than
comparable RDBMSes. So I think we are logging stuff we
don't really
This bothers me a bit, because in
fact the effects if any of the tested query would have been rolled
back. Not sure we have any choice though. If we expose the error
then we'll have problems with clients not showing the EXPLAIN
results.
I think we should leave it in top level,
I think that the ONLY was wrong from day one :-(
Well, sure, but until we have an implementation that actually
*works* across multiple tables, it has to be there so that we
can at least consistently support the current single-table
semantics. Until we have some form of cross-table
This bothers me a bit, because in
fact the effects if any of the tested query would have been
rolled back. Not sure we have any choice though. If we
expose the error then we'll have problems with clients not
showing the EXPLAIN results.
I think we should leave it in top level, throw
On a separate note. The one major remaining piece here is in
constraints. I'm thinking what I have to check is that every
constraint present on the parent table is present on the
child tables. And I'm thinking I should do that by looking at
the constraint's textual definition (consrc).
Or maybe I should insist that a matching constraint name be present
*and* that the source text match? That's more of a pain to code
though.
That could also break some partitioning schemes; I don't
think it's a given that parents and children have matching
constraints, and afaik a
But that's entirely inconsistent with the way inherited tables
work
in general.
I don't see any basis for that conclusion. The properties of a
table
are set when it's created and you need to do pretty explicit ALTERs
to
change them.
It just seems weird for:
CREATE TABLE
The solution to the foreign key problem seems easy if I
modify PostgreSQL implementation and take off the ONLY word
from the SELECT query, but it's not an option for me, as I'm
I think that the ONLY was wrong from day one :-(
The default in other areas is table including childs.
(Not
While I don't think that making WITH a fully reserved word would
cause
any great damage, I'm unwilling to do it just to save a couple of
lines
of code.
I think we should go on and do promote WITH to a reserved keyword now
Oracle, MS-SQL, DB2, MySQL and Informix also have WITH reserved,
Added to TODO:
o Add ALTER TABLE tab ADD/DROP INHERITS parent
Sounds good, more inline with add/drop constraint.
pg_attribute.attislocal has to be set to 'false' for ADD,
and
attislocal: If you set this to False, you wouldn't be able to set it
back again.
Just curious,
AFAICS the problem is not restricted to LIKE, we can easily find a lot
of
similar problems caused by the actual parameters. For example, SeqScan
vs.
IndexScan vs. BitmapIndexScan for a range query. So an improvement is
definitely needed.
Another way is to generate a plan on the fly. What we
table of another table. I propose a TODO item to allow this:
ALTER TABLE childN INHERITS ( parent1, ... );
We don't need a disinherit do we?
I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition
to existing
1) Use n sort areas for n tapes making everything purely sequential
access.
Some time ago testing I did has shown, that iff the IO block size is
large enough
(256k) it does not really matter that much if the blocks are at random
locations.
I think that is still true for current model disks.
So
Certainly, if you can't prototype a convincing performance win using
that algorithm, it's unlikely to be worth anyone's time to
look harder.
That should be easily possible with LZO. It would need to be the lib
that
we can optionally link to (--with-lzo), since the lib is GPL.
lzo even
Unfortunatly, the interface provided by pg_lzcompress.c is probably
insufficient for this purpose. You want to be able to compress tuples
as they get inserted and start a new block once the output reaches a
I don't think anything that compresses single tuples without context is
going to be a
Very clear. The issue is that I can't find any of these emitted by a
pg_dump version who's native backend doesn't understand them.
I assume that it is expected that a cross-db dump/reload will generate
errors, and it is done rarely for upgrades, but I assume same-version
dump/restore is
I thought the suggested procedure (see migration doc) was to use the
new pg_dump to dump the older db version, so why backpatch ?
Uh, you can suggest it, but I would guess 50% do it, and once the
old
database is gone, there is no way to re-do the dump.
But you can still load the dump
Given that any time that happens we end up caring much less about
CPU
usage and much more about disk IO, for any of these cases that use
non-random access, compressing the data before sending it to disk
would
potentially be a sizeable win.
Note however that what the code thinks is a
Personally, I believe it would be worth it - but only to a few. And
these most of these few are likely using Oracle. So, no gain unless
you can convince them to switch back... :-)
We do know that the benefit for commercial databases that use raw and
file system storage is that raw
These are all minor abberations though, on the whole the estimates
are
pretty good. Perhaps you need to tweak the values of random_page_cost
and similar variables.
Thank You, It's general problem or only mine? I have 100%
standard current PC.
The default random_page_cost assumes some
Something else worth considering is not using the normal
catalog methods
for storing information about temp tables, but hacking that together
would probably be a rather large task.
But the timings suggest, that it cannot be the catalogs in the worst
case
he showed.
0.101 ms BEGIN
1.451
I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers
to
determine when the throughput starts to level out or drop
I think for an even better comparison you should scale wal_buffers
down with increasing XLOG_BLCKSZ, so that the xlog buffer has a
fixed
size in kb.
I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop
I think for an even better comparison you should scale wal_buffers
down with increasing XLOG_BLCKSZ, so that the xlog buffer has a fixed
size in kb.
Reasonable
4. Find the option for disabling strict alias and get configure to
add
that.
You'll still lose performance, but the option is -qalias=noansi.
My old xlc does not show that option, it is unfortunately version
specific.
The currently compatible option to turn it off would be -qnoansialias
I ran afoul of these rules the other day when compiling pgsql 8.1 on
AIX. The configure scripts are set up to look for xlc instead of
cc, and that command invokes cc with -qalias=ansi, the ANSI-strict
pointer aliasing mode.
Can you please explain what exactly was not working ?
xlc has in the
[ shrug... ] Worksforme.
There is a short interval at the end of the first CREATE INDEX on
the
table where the problem would happen if another CREATE INDEX tries
to
modify the pg_class row before the first one's committed.
I did a pg_dumpall and removed the index creation commands.
Wes, you could most likely solve your immediate problem if you did
an
analyze before creating the indexes.
I can try that. Is that going to be a reasonable thing to do when
there's
100 million rows per table? I obviously want to minimize the number
of
sequential passes through the
Apparently it won't work at all if TMP isn't set?
I'm not *too* concerned about that, since TMP is normally set by the
OS
itself. There's one set in the system environment (to
c:\windows\temp
or whatrever) and then it's overridden by one set by the OS when it
loads a user profile.
Well, the psqlODBC driver apparently ran into a number of problems
with
libpq that resulted in them not using it for their purpose.
Given libpq primary purpose is to connect to PostgreSQL, it failing at
that is
something that should be fixed.
I think you are forgetting, that e.g. a JDBC
The attached patch allows SET CONSTRAINTS to take a schema
qualified
constraint name (myschema.t1_fk_t2) and when given a bare
constraint name
it uses the search_path to determine the matching constraint
instead of
the previous behavior of disabling all identically named
constraints.
Plan C would be to say that we don't need to preserve SELECT * FROM
seqname, but I'll bet there would be some hollering.
I'd like to hear this hollering first, before we create tons of views
:-)
Imho it is not a problem to remove it, I am for Plan C.
(Those with need for the select can still
Two pass will create the count of subfiles proportional to:
Subfile_count = original_stream_size/sort_memory_buffer_size
The merge pass requires (sizeof record * subfile_count) memory.
That is true from an algorithmic perspective. But to make the
merge efficient you would need to have
Two pass will create the count of subfiles proportional to:
Subfile_count = original_stream_size/sort_memory_buffer_size
The merge pass requires (sizeof record * subfile_count) memory.
That is true from an algorithmic perspective. But to make the merge
efficient you would
I'll look into it, but I was already wondering if we shouldn't bound
the number of tapes somehow. It's a bit hard to believe that 28000
tapes is a sane setting.
Well, since they are not actually tapes, why not?
I wonder what the OS does when we repeatedly open and close those files
This amounts to an assumption that you have infinite work_mem, in
which
case you hardly need an external sort at all. If your
work_mem is in
fact finite, then at some point you need more than two passes. I'm
not
really interested in ripping out support for sort
operations that
But you could do the indexes first and remember how far you
can vacuum
the heap later.
But the indexes _can't_ be done first; you _first_ need to
know which tuples are dead, which requires looking at the
table itself.
If we already had the all tuples visible bitmap I think we could
But what about index clearing? When do you scan each index?
At the end of each iteration (or earlier, depending on
maintenance_work_mem). So for each iteration you would need
to scan the indexes.
Maybe we could make maintenance_work_mem be the deciding
factor; after scanning the
I thought we had sufficiently destroyed that reuse a tuple
meme yesterday. You can't do that: there are too many
aspects of the system design that are predicated on the
assumption that dead tuples do not come back to life. You
have to do the full vacuuming bit (index entry removal,
I think you must keep the header because the tuple might be
part of an update chain (cf vacuuming bugs we repaired just a
few months ago).
t_ctid is potentially interesting data even in a certainly-dead tuple.
yes, I'd still want to keep the full header.
Andreas' idea is possibly doable
3) For every privilege descriptor in CPD whose action is
INSERT, UPDATE,
or REFERENCES without a column name, privilege descriptors are also
created and added to CPD for each column C in O for which
A holds the
corresponding privilege with grant option. For each such column, a
OTOH DB2 and SQLServer take block level
read locks, so they can do this too, but at major loss of
concurrency
and threat of deadlock.
Note, that in the usual committed read isolation, they do not need to
read lock a row ! e.g. Informix only verifies, that it could lock the
row
(that
DB2:
Uncommitted Read (UR) mode Dirty read isn't the default, or
the recommended lock level for most apps. I was considering
Cursor Stability mode (or higher), which is the default
Sorry, they call it read committed but actually do cursor stability,
which does keep one lock on the last
With AIX 5, the easiest way to get a shared object is to pass
-bexpall
to the linker. This results in all symbols being exported.
Yes, that is another reason not to use this broken switch.
And last time I checked (AIX 4.3.3), -bexpall did not export all needed
symbols
(e.g. globals) from the
The instant someone touches a block it would no longer be marked as
frozen (vacuum or analyze or other is not required) and count(*)
would
visit the tuples in the block making the correct decision at that
time.
Hmm, so the idea would be that if a block no longer contained any
tuples
Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.
update
if no rows updated
insert
if duplicate key
update
if no rows updated goto insert
That is why you have the loop. This is not a problem with above code,
Since that costs, I guess I would make it optional and combine it
with
materialized views that are automatically used at runtime, and can
at
the same time answer other aggregates or aggregates for groups.
create materialized view xx_agg enable query rewrite as select
count(*), sum
This is because lost+found exists. Since lost+found would be a
reasonably common directory to find at a mount-point on Unix-like
OSs*, would it make sense for CREATE TABLESPACE to ignore it if
present?
No. There is no reason to use a volume's root directory as a
tablespace;
especially
The problem I try to solve is something along: a bunch of clients try
to update a count, and ONE of them must initialize the count if it
does not exist... this can't be done with current infrastructure without
race conditions.
The solution without merge but a unique key in other db's is:
87 matches
Mail list logo
