(that only an administrator can set) to control
the maximum number of advisory locks a given backend can take at any
one time? Then it becomes the DBA's problem (and solution) if someone
manages to run the database out of shared memory through this
mechanism.
--
Kevin Brown
re there other ways of getting this information from the system? If
not, then I'd consider this proposed change important.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
and shared
libraries -- they're now just libraries. The only difference is how
you link to them.
What IBM has done here is very sensible, and is really what the other
Unixes should have done from the beginning: a library is just a
library, and what differs is how
I'd have to examine the assembly to
know for sure). On the 1.5 GHz P4 system, each iteration takes 8.49
nanoseconds. And on the 2.8 GHz Xeon system, each iteration takes
5.01 nanoseconds.
That seems reasonably fast to me...
--
Kevin Brown [E
ut this should be independent of how foreign
key failures are handled once they're detected. In other words, what
you're experiencing is the perfomance hit that comes from evaluating
the constraints, not from reporting the errors afterwards.
--
Kevin Brown
tes a
> bit one way or the other.
Hmm...but if you're going to do that, why not do that now: push the
beta date back by, say, a month (or however long you had in mind) for
this cycle. That way, the two major patches that are likely to be
dropped for this cycle stand a chance to make it into
diting our source code is approximately equal to the
> influence of Pluto's gravitational field on your mood when you got out
> of bed this morning.
I always wondered what affected his mood when he got out of bed this
morning. Now I know!
It's amazing the things y
method on a production
system, though, as well as how much of a performance impact the
measurements have on overall operation...
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/
;s what's likely to have happened here. The
kernel's page size is 4k, as is the default block size used by XFS.
Tracking this one down any further is going to be nigh unto
impossible, I think.
There have been a pile of fixes to XFS in 2.6.16, so I'll start using
that,
azebo.sysexperts.com/~kevin/postgresql
The files are bad-page-info.txt and bad-page.txt.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
hem here.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
x27;stagezilla'
database after shutting down PostgreSQL so that I can do additional
examination of the datafiles if that proves useful.
Additionally, if you'd like to see the output of pg_filedump, I'll be
happy to post it here (or email it separately). I
ce command (e.g., VACUUM INDEX) that
acquires the appropriate lock (AccessExclusive, I'd expect) on the
index to prevent exactly the issues you're concerned about?
If it's fast enough even on large tables, it would be a nice
alternative to REINDEX, I'd think.
block against
the REINDEX until that lock is granted. A true deadlock won't happen
against common operations unless REINDEX promotes its lock again to
something stronger than ShareLock, and that's easy to avoid: just have
REINDEX promote directly from AccessShareLock to the s
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > - when requesting a type of lock, one must first acquire all lesser
> > lock types against the object in order of strength. Hence, one must
> > acquire AccessShareLock before acquiring AccessExclusiv
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > I guess the real question here is: is it possible to, in code,
> > guarantee the order of lock acquisition by any given transaction?
>
> Yes, but not in our code :-(. This is largely determined by what th
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> Even ignoring that, you *still* have a lock upgrade problem
> >> in this sketch.
>
> > Hmm, well, I can see a deadlock potential for those operations that
> > have
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > In the above for large relations, the bulk of the REINDEX should
> > happen without any locks being held by the REINDEX operation.
>
> As I just pointed out to Greg, the arm-waving notion that you can &quo
bably implies a lot more
than one would think at first glance, so this is certainly an issue
that should be thought all the way through.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
n't a common operation even if it is a
critical one, so acquisition of more than the usual number of locks
here shouldn't be a big deal.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Martijn van Oosterhout wrote:
> None of this applies to PostgreSQL because we open the modules
> directly, and don't rely on the linker loader.
Ah, right. I forgot the context was the server, not one of the
utilities...
Sorry for the waste of bandwidth...
--
library with the soname specified by the
shared object. I don't know if that just causes the dynamic linker to
look for a file with the specified soname or if it will actually
examine the shared object under consideration to make sure it has the
DT_SONAME fie
(it may or may not attempt to continue to read
what's being sent to it). Some pagers (like "more") will just exit.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Andrew - Supernews wrote:
> On 2005-10-19, Kevin Brown <[EMAIL PROTECTED]> wrote:
> > Making assumptions about what the pager will do upon receipt of SIGINT
> > is folly as well.
> >
> > Setting up SIGINT to be ignored may be the right answer (I don't
>
[EMAIL PROTECTED] wrote:
> On Thu, Oct 20, 2005 at 03:42:10PM -0700, Kevin Brown wrote:
> > Martijn van Oosterhout wrote:
> > > You can't do a pclose in a signal handler, it's not one of the
> > > "reeentrant safe" functions and could lead to deadloc
andled in a reasonably timely fashion.
Additionally the normal SIGINT signal handler (the one that gets
invoked when the pager is turned off) can be called from the cleanup
handler in order to maintain the proper semantics.
--
Kevin Brown
Martijn van Oosterhout wrote:
> On Tue, Oct 18, 2005 at 09:32:25PM -0700, Kevin Brown wrote:
> > So I think the right answer here is for psql to handle SIGINT
> > internally by doing a pclose() first (and at this point, it probably
> > should ignore SIGINT altogether), then
d alter the stack such
that the resulting core would be less useful. I'd rather have to
clean up the terminal manually than have an unusable core file.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)--
ail table/structure, if one
doesn't already exist (and if it already exists, then clearly the ACLs
should be storing the id of the role holding the grant, since the
audit structure will separately record the user/role issuing the
grant).
--
Kevin Brown
Just increment the marker value each time
you write the page (you'll have to read it from the page prior to
incrementing it, of course).
Other than that, torn page detection is really just a special case of
page corruption detection.
--
Kevin Brown
d be no significant performance penalty involved, relative
to the time it takes for the first sync() to complete.
Thoughts?
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
immediately setuid() to the postgres
account, so that accessing the database itself isn't enough to allow
you to compromise the master password. If they can grab the master
password with that setup, then they have root on the box anyway and
can do anything they want.
--
Kevin Brown
Greg Stark wrote:
>
> Kevin Brown <[EMAIL PROTECTED]> writes:
>
> > Hence, it makes sense to go ahead and run the query, but issue a
> > warning at the very beginning, e.g. "WARNING: query JOINs tables > of tables> without otherwise referencing or making us
hand it such a reference.
MS SQL Server does not allow such references either, yielding
"columnname is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.".
Can't comment about DB2.
--
Kevin Brow
ause excessively poor performance of the query".
That said, the real question is whether or not it's worth putting in
the effort to detect this condition and issue the warning. I'd say
probably not, but if this is a big enough itch for someone then
that occurs in the
tables.
So: will autovacuum be coded to explicitly look for transaction
wraparound, or to automatically vacuum every N number of transactions
(e.g., 500 million)?
--
Kevin Brown [EMAIL PROTECTED]
---(end o
the specific situation we're
discussing, however: the current ARC implementation is apparently not
showing itself to be a clearly superior approach, so some other
approach is probably warranted.
--
Kevin Brown [EMAIL PROTECTED]
---
ELECT table_schema || '.' || table_name FROM
information_schema.tables WHERE table_schema IN ('public', 'postgres'))
TO (SELECT usename from PG_USER WHERE usecatupd = true);
Actually, it would be very nice if all DDL statements could work that
way.
hey're not suing in any other countries.
If I sound bitter and cynical, well, there's lots of good reason for
it. You need only look around, at least if you're in the U.S.
--
Kevin Brown [EMAIL PROTECTED]
---(e
gt; APPDATA/postgresql/pgpass.txt
> > >>> APPDATA/postgresql/psqlrc.txt
>
> Another idea is to use *.conf.
For what it's worth, I always thought that text configuration files on
Windows platforms generally used the '.ini' extension.
used. Someone who is savvy enough to
define EDITOR is also savvy enough to throw together a simple batch
file which invokes his real editor with whatever options he wants. It
seems to me that being able to properly specify the path of the
program to use with a minimum of fuss (hence no strange quoting
request of the DBA, preferably through a GUC,
and preferably with a relevant GUC for each operation.
Since WAL archiving requires that the operations in question be WAL
logged, the GUCs that control WAL logging of those statements would
clearly be ineffective if W
ust run it and interrupt it with ctrl-c. It should print
something the first time around, and actually be interrupted the
second time.
So if Unixware doesn't have sigaction() or it's not being picked up by
autoconf then yeah, he'll have big problems...
--
Kevin Brown
heir values from that table, and then the values in
postgresql.conf or the command line would be the default that's used
if there's nothing in the table (and if you really want fine-grained
control of this process, you could stick a boolean column in the tabl
e database to do any conversion into the column target type based on
the original specified default value, and not some intermediate form
that exists only because of the history of the column's datatype.
So, my vote is for the form to be ('TODO'::varchar)::varchar(4) after
the ALTER
y passing it down to the child
> intentionally wouldn't be a common case. I'll put the change in.
Since program authors who would care about this one way or another
probably won't be expecting this behavior, it should also be
documented reasonably well -- something which I'm
TABLE, even if those people have a decent amount of PG
experience.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > ... But what we're talking about
> > here is brand new functionality for which the language hasn't been
> > defined yet.
>
> You're missing the point, which is that there *is* a preceden
bility with something. But what we're talking about
here is brand new functionality for which the language hasn't been
defined yet. It would be a bit unfortunate to introduce inconsistencies
where they're not needed, wouldn't you say?
--
Kevin Brown
I change the
tablespace of an index?" -- ALTER INDEX is. And the reason is simple:
we use CREATE INDEX to create an index and DROP INDEX to drop one -- we
don't use ALTER TABLE subcommands to create or drop indexes. Why, then,
should modification of an index's properties be treated any
m...not ALTER INDEX? Now that there's an operation that actually
modifies an index instead of the table itself, should there be an ALTER
INDEX? It would be cleaner and more consistent, IMO...
--
Kevin Brown [EMAIL PROTECTED]
asked to stop short of where the table gets deleted) nor
> for the case where there's been filesystem damage.
But doesn't PITR assume that a full filesystem-level restore of the
database as it was prior to the events in the first event log being
replayed has been
r we
> could just skip logged operations on files within the tablespace, but
> that feels pretty uncomfortable to me --- it amounts to deliberately
> discarding data ...
>
> Any thoughts?
How is a dropped table handled by the recovery code? Doesn't it present
the same sort of issue
Larry Rosenman wrote:
> I had to hack on the code some more for FreeBSD:
> (the realloc call needed the multiplication). I ran this same code
> on UnixWare.
I feel like a moron, having missed that. Probably explains the "bad
file number" error I was getting on AIX, too...
backend isn't
> in itself a nasty performance hit, quite aside from its effect on how
> many normal files we can open.
I imagine this could easily be tested. I rather doubt that the
performance hit would be terribly large, but we certainly shou
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > The goal here is simply to make it obvious to a system administrator where
> > the PG data directory that a given postmaster is using resides.
>
> Why would it not be sufficient to add a read-only GUC
ld have to
explicitly specify the data directory on the command line. That seems
easy enough to do: #ifdef is your friend.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the
tor who wants to make
sure that a configuration file has to explicitly be targetted at the data
directory can do so. End result: if you use the -D option on the command
line with an inappropriate -C option, the postmaster will refuse to run.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
me of the version string parameter is). That way, even if you screw
up on the command line, you won't hose a database by starting the wrong
version of the postmaster against it. Not sure if this would break
anything, though.
--
Kevin Brown [EMAIL PRO
#x27;s side of the connection and let the
connection-dropping logic kick in automatically, no?
Thoughts? Am I completely off my rocker here? :-)
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
using JFS.
I'll run the test program and report my results with it as well, so
we'll be able to see if there's any consistency between it and the live
database.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broa
imal than doing nothing. The only question, of course,
is whether or not it's worth going to the effort when it may or may not
gain you a whole lot. Answering that is going to require some
experimentation with such an automatic configuration system.
--
Kevin Brown
any open files
Was able to use 1997 file descriptors after opening 2 shared libs
[EMAIL PROTECTED]:~$ uname -a
AIX m048 1 5 0001063A4C00
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TI
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > I originally thought that each shared library that was loaded would eat
> > a file descriptor (since I thought it would be implemented via mmap())
> > but that doesn't seem to be the case, at least under
Merlin Moncure wrote:
> Kevin Brown wrote:
>
> > I have no idea whether or not this approach would work in Windows.
>
> The win32 API has ReadFileScatter/WriteFileScatter, which was developed
> to handle these types of problems. These two functions were added for
> th
architecture of PG requires it somehow.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > Well, running out of space in the list isn't that much of a problem. If
> > the backends run out of list space (and the max size of the list could
> > be a configurable thing, either as a percenta
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > Instead, have each backend maintain its own separate list in shared
> > memory. The only readers of a given list would be the backend it belongs
> > to and the bgwriter, and the only time bgwriter attempts
or the rows it inserts)?
I don't know what kind of schema you're using, but it takes perhaps a
couple of hours to insert 2.5 million rows on my system. But the rows
in my schema may be much smaller than yours.
--
Kevin Brown
Some Moron at sysexperts.com wrote:
> At checkpoint time, for each backend list, the bgwriter grabs a write
> lock on the list, copies it into its own memory space, truncates the
> list, and then releases the read lock.
Sigh. I meant to say that it then releases the *write* lock.
instantaneously since their data has already been written (so there
won't be any dirty blocks in those files). I suppose it's possible that
on some OSes fsync()s could interfere with a running sync(), but for
those OSes we can just drop back do doing only fsync()s.
As usual, I could be
cify a file type
to further narrow the search (thus 'locate --type file "core"' will find
all regular files named 'core' in the database).
I'll be happy to share my code with anyone who's interested.
--
Kevin Brown
irrelevant. The command provides useful information. But see
below.
> Making "pseudo sql" will encourage more developers to
> (and I'd apologize for this if it weren't true) code in Postgres the same
> lazy way they code in MySQL.
This is a strawman argument, although
Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> You don't. As I said, any physical backup is going to be
> >> all-or-nothing. These techniques are not a replacement for pg_dump.
>
> > But this is just a
segments
would not be picked up by existing backends -- only new ones -- and then
only if the postmaster is the process that allocates them.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2
that cannot be simultaneously
accessed by any query (corrections welcome), there isn't any reason in
principle that the WAL files cannot also be created on a per-database
basis.
I'm sure, of course, that doing so would bring with it a new set of
problems and tradeoff
ore real-world testing within a given block of time, on
average, because a lot more people pick up the releases than the CVS
snapshots or even release candidates..
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
Yet
Linux kernel releases are much more frequent than PostgreSQL releases.
One difference is that the Linux community makes a distinction between
development releases and stable releases. The amount of time between
stable releases is probably about the same as it is for Po
hat the default behavior should be for each
option. Clearly for non-gcc compilers, the default should be -O only
since they probably can't simultaneously handle -g. But gcc builds are
an exception, one which I think is worth considering. Hence my opinion
that for gcc builds, the default sho
would most other things. So even if you might be inclined to strip
most of your binaries, you might think twice about doing the same for
the PG binaries.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
ulting core file. The -g option makes that possible for optimized
code when compiling with gcc.
Is there any way we can have configure put -g in when it detects gcc?
--
Kevin Brown [EMAIL PROTECTED]
---(
language to be any better for performing certain
kinds of queries against data than some other, more directed language?
Say what you want about SQL, but at least it was designed with querying
table-based data in mind and is at least somewhat good at its job.
--
Kevin Brown
Bruce Momjian wrote:
> Kevin Brown wrote:
> > Actually, all that's really necessary is the ability to call a stored
> > procedure when some event occurs. The stored procedure can take it from
> > there, and since it can be written in C it can do anything the postgres
&g
ded is the ability to call an external
> program when some even occurs, like a database write failure.
Actually, all that's really necessary is the ability to call a stored
procedure when some event occurs. The stored procedure can take it from
there, and since it can be written in C it ca
reads a row that has a commit ID greater than the reader's
transaction ID, it throws a serialization error. It's probably sufficient
to store the commit ID along with the transaction ID of the committer
in the transaction log as well as in sha
of the ON MODIFY triggers.
A rollback would, of course, not have any effect on the data in those
rows since there weren't any real changes. This "fix" won't work,
of course, if the serialization code is so broken that it doesn't work
properly even in t
ithin
the same transaction. If it's not, then the RI mechanism is broken and
needs to be fixed at that level.
But if PG exhibits exactly the same bug this thread refers to regardless
of whether a row is examined/modified via directly issued SQL or via
the RI mechanism then the problem lies not w
r you're a superuser or the owner of
the backend) and sends the appropriate signal to the target backend?
That would make it possible for users to kill their own runaway queries
without having to run as whatever user PG is running as.
--
Kevin Brown [EMAI
consistent with the meaning of NULL: "no value",
and would also yield the desired effect in the example select (no
matches).
Of course, I could always be off my rocker here. :-)
--
Kevin Brown [EMAIL PROTECTED]
---(en
;t already). It's doing
basically the right thing, at any rate: directly comparing the actual
fields that are relevant. Does this test represent a significant
performance hit?
--
Kevin Brown [EMAIL PROTECTED]
--
don't make constraint names unique
within a schema?
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
ing at the current DateStyle or, failing that, by
applying the recognition in a well-defined order). In my case I can
probably code around it but it does require some extra effort. But I
can easily imagine situations in which that wouldn't be an option.
Whatever happened to "be libe
Bruce Momjian wrote:
> Kevin Brown wrote:
> > The two approaches aren't necessarily mutually exclusive (though SQL99
> > compliance on constraint names would obviously make it unnecessary to
> > specify a tablename along with a constraint name), so I see little
> >
angement is obviously untenable,
because it allows you to create a situation (multiple constraints by
the same name) that you can't reasonably extricate yourself from.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
ish_stem.c
>
> That will fail for a more basic reason: not all compilers support the -o
> option.
...jaw drops to ground...
I'm astounded. There are such compilers still in use?? Which ones?
--
Kevin Brown [EMAIL PROTECTED]
have that option than not.
Overcommit isn't really necessary today because of the huge amount of
memory that you can put into a system for cheap (HP servers excluded,
they want some serious cash for memory).
--
Kevin Brown [EMAIL PROTECTED]
so that users can see their own
commands), and 7.4 incorporates the patch. You can probably retrieve
it by searching the pgsql-patches archives, but I can also email the
patch to whomever's interested.
--
Kevin Brown [EMAIL PROTECTED]
-
The Hermit Hacker wrote:
> On Wed, 25 Jun 2003, Kevin Brown wrote:
>
> > So...would it make sense to create a gborg project to which people who
> > have written their own test suites can contribute whatever code and data
> > they feel comfortable releasing? As a gb
tional advantage that the session
will continue (and can be reattached to) even if your terminal window
dies for whatever reason.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
e the actual text of the query being executed
and perhaps the file and line number of the code that threw the error.
A stack trace could be useful in the most extreme cases (and, obviously,
only when verbosity is maximized), too, but that may be too much to
ask for. :-)
--
Kevin Brown
1 - 100 of 215 matches
Mail list logo
