> 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 broadcast)
> -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 c
> 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
> > 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.
I
> > > 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
> 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 ar
> 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 suppose
> >> 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 @> a
> 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
> > > > "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 clea
> > 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
> 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.
Yu
> >> 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 PostgreS
> >>> 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
> > 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 particularl
> > 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 d
> > 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
> > 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 t
> 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 l
> > > 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 w
> > 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
>
> 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
> > => 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 n
> >> 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 retu
> 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 t
> > > - 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" eq
> 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.
Andr
> > >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,
> > 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
> > 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
> > > 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
> >
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
>
> > 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
> i
> > 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
> > 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 que
> 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 pe
> >> 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 exp
"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 s
> > 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
> 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 r
> >> 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
> > 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
> 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
> 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, thro
> > 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-tab
> 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.
(N
> > > 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:
>
> > 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 res
> 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
> 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 c
> > 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 inh
> 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
> > 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
> 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
> 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
> 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 all
> >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 assume
> > 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 r
> > 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 think
> 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.
> > > 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
> > siz
> 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 wal
> 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 h
> > 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 -qnoansi
> > 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 t
> > [ 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
> >> 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
> 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
constr
> 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 stil
> > 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
> > > 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
> > effici
> 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 en
> > 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
> operati
> > 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 thin
> > 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 scanni
> 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 doa
> 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 remova
> 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,
> 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
> > 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
(tha
> 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 th
> > 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(*
> 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
> > 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
tu
> 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:
upda
> > 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;
> esp
87 matches
Mail list logo
