On 7.1.x it definitely gets slower even for indexscans. e.g. 60 updates/sec
dropping to 30 then to 20 over time.
Is this fixed for 7.2?
If not, is it possible to make the pointer point to the latest row instead
of the most obsolete one, and having the newer rows point to the older
ones,
At 10:48 AM 4/18/02 -0400, mlw wrote:
Bruce Momjian wrote:
Have you tried reducing 'random_page_cost' in postgresql.conf. That
should solve most of your problems if you would like more index scans.
My random page cost is 1 :-)
What happens when you set random page cost to 1? Between an
At 12:19 PM 4/25/02 +0900, Curt Sampson wrote:
Grabbing bigger chunks is always optimal, AFICT, if they're not
*too* big and you use the data. A single 64K read takes very little
longer than a single 8K read.
Yes I agree that if sequential scans are done reading ahead helps.
And often doesn't
At 04:01 PM 4/25/02 -0300, Marc G. Fournier wrote:
My guess is that we should implement #1 and see what feedback we get in
7.3.
IMHO, it hasn't been thought out well enough to be implemented yet ... the
options have been, but which to implement haven't ... right now, #1 is
proposing to
At 10:34 AM 4/26/02 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
Coz some things should not be rolled back. So you guys might come up
with a
different keyword for it.
CONFIG: for non transactional stuff that can appear as SQL statements.
SET: for stuff that can
At 11:49 AM 4/26/02 -0400, Tom Lane wrote:
I'm still looking for an example of something that is (a) reasonable
to set on a per-backend basis, and (b) not reasonable to roll back
if it's set in a transaction that fails.
The way I see it is if (a) and you don't want it rolled back, you could put
Hi Tom,
(Please correct me where I'm wrong)
Is it possible to reduce the performance impact of dead tuples esp when the
index is used? Right now performance goes down gradually till we vacuum
(something like a 1/x curve).
My limited understanding of current behaviour is the search for a
At 02:10 PM 5/1/02 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
My limited understanding of current behaviour is the search for a valid
row's tuple goes from older tuples to newer ones via forward links
No. Each tuple is independently indexed and independently visited
Not tested: but how about the string being
foo'; DROP TABLE T1; foo
Would the last ' be eaten up then resulting in no error?
Also normally a \ would be quoted by \\ right? Would a foo\ result in an
unquoted \ ? An unquoted backslash may allow some possibilities.
There could be other ways to
At 12:49 AM 5/2/02 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
But does Postgresql visit the older tuples first moving to the newer ones,
or the newer ones first?
It's going to visit them *all*. Reordering won't improve the
performance.
Ack! I thought it went through them
Oops. How about:
foo'; DROP TABLE t1; -- foo
The last ' gets removed, leaving -- (81a2).
So you get:
select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2)
Would that work? Or do you need to put a semicolon after the --?
Alternatively would select (0x81a2) be a syntax error? If it isn't then
I hope you won't make this standard practice. Because there are quite
significant differences that make upgrading from 7.1.x to 7.2 troublesome.
I can't name them offhand but they've appeared on the list from time to time.
For 6.5.x to 7.1.x I believe there are smaller differences, even so
At 06:58 AM 5/14/02 +0100, Oliver Elphick wrote:
retarget a dump script to be reloaded in some other schema. If the
dump is cluttered with umpteen thousand copies of the schema name
that's going to be difficult.
sed -e 's/ old_schema\./ new_schema./g'
I don't think you should allow the
How about the postgresql logo - is there a source vector/postscript of it
so that he can blow it up without res loss and print it? The logo designer
may still have the source files.
Cheerio,
Link.
At 02:56 AM 5/18/02 -0300, Marc G. Fournier wrote:
Not that I'm aware of anyone making ...
On
What are the benefits of SASL+Postgresql compared to Postgresql over plain SSL?
Coz Postgresql already supports SSL right?
Cheerio,
Link.
At 03:11 PM 5/18/02 -0600, Bear Giles wrote:
If it's being used in Sendmail, Cyrus IMAP and OpenLDAP, with preliminary
work (sponsored by Carnegie Mellon
At 01:11 AM 5/20/02 -0600, Bear Giles wrote:
What are the benefits of SASL+Postgresql compared to Postgresql over
plain SSL?
The anticipated benefit of SASL is that it would replace all of the
current authetication code with a set of standard plugins. The
authority problem would be reduced
At 01:20 PM 6/3/02 +0200, Zeugswetter Andreas SB SD wrote:
for two things, one for escaping single quotes and for escaping standard
C characters, like \n. While we can use the standard-supported '' to
insert single quotes, what should we do with \n? The problem is
switching to standard
At 09:58 PM 6/4/02 +0200, Peter Eisentraut wrote:
Lincoln Yeoh writes:
But for the ANSI standard how does one stuff \r\n\t and other control
characters into the database?
If there's no way other than actually sending the control characters then
that is a bad idea especially from
connections grin...
At 07:10 PM 6/6/02 +0200, Peter Eisentraut wrote:
Lincoln Yeoh writes:
However raw control characters can still cause problems in the various
stages from the source to the DB.
I still don't see why. You are merely speculating about implementation
fallacies that aren't
At 01:49 PM 6/20/02 +0100, Tony Griffiths(RA) wrote:
a) The client-side programmer has to be responsible for parsing the
returned string, which could cause problems if the output format of the
ADT is changed, and
b) The impedance mismatch is much greater than that of the built-in types.
One
At 10:59 PM 14-04-2001 -0400, Lamar Owen wrote:
http://www.crn.com/Sections/Fast_Forward/fast_forward.asp?ArticleID=25670
Marc will be pleased to note that the PostgreSQL project came out of the
FreeBSD project, and is Great Bridge's database. Gotta love
journalistic license.
Reporter must
At 08:38 PM 15-04-2001 -0700, you wrote:
On Sun, Apr 15, 2001 at 10:05:46PM -0400, Vince Vielhaber wrote:
On Mon, 16 Apr 2001, Lincoln Yeoh wrote:
Maybe you guys should get some Great Bridge marketing/PR person to handle
stuff like this.
After reading Ned's comments I figured that's how
At 03:09 PM 23-04-2001 -0300, you wrote:
Anyone thought of implementing this, similar to how sendmail does it? If
load n, refuse connections?
Basically, if great to set max clients to 256, but if load hits 50 as a
result, the database is near to useless ... if you set it to 256, and 254
idle
At 11:28 PM 24-04-2001 -0300, The Hermit Hacker wrote:
I have a Dual-866, 1gig of RAM and strip'd file systems ... this past
week, I've hit many times where CPU usage is 100%, RAM is 500Meg free and
disks are pretty much sitting idle ...
It turns out, in this case, that vacuum was in order (i
At 08:39 AM 26-04-2001 -0400, mlw wrote:
I am getting a bit concerned about Postgres 7.1 performance with multiple
connections. Postgres does not seem to scaling very well. Below there is a
list
of outputs from pgbench with different number of clients, you will see that
My postmaster start line
At 03:44 PM 27-04-2001 -0300, The Hermit Hacker wrote:
On Fri, 27 Apr 2001, Bruce Momjian wrote:
On Fri, 27 Apr 2001, Bruce Momjian wrote:
Huh? *raised eyebrow* This is a standalone application that they've
donated to the project ... nothing that can be added to any of our
At 02:09 AM 5/4/01 -0500, Thomas Swan wrote:
I think it's worth noting that Oracle has been petitioning the
kernel developers for better raw device support: in other words,
the ability to write directly to the hard disk and bypassing the
filesystem all together.
But there could be other
At 05:59 PM 7/6/01 -0400, Bruce Momjian wrote:
OK, I just talked to Tom on the phone and here is his idea for 7.2. He
says he already posted this, but I missed it.
His idea is that in 7.2 VACUUM will only move rows within pages. It
will also store unused space locations into shared memory to
At 06:10 PM 18-07-2001 -0400, Lamar Owen wrote:
applications :-) I guess I'll just need to switch to proper SERIALs and
PRIMARY KEYs. Of course, if I wanted to be stubborn, I'd just use the GUC
option to enable OIDs system-wide by default
The default 32 bit serial primary key isn't
At 07:02 PM 06-08-2001 -0400, Tom Lane wrote:
pseudo-type should generate an int8 instead of int4 column. On
compatibility grounds, it might be better to leave it generating int4,
and invent a second pseudo-type SERIAL8 that is just the same except
for making an int8 column. I'm more worried
At 11:20 AM 8/19/01 -0700, Vadim Mikheev wrote:
Well, ability to lock only unlocked rows in select for update is useful,
of course. But uniq features of user'locks are:
1. They don't interfere with normal locks hold by session/transaction.
2. Share lock is available.
3. User can lock *and unlock
At 09:39 AM 20-08-2001 -0700, Mikheev, Vadim wrote:
If it does then one of the things I'd use it for is to insert
unique data without having to lock the table or rollback on
failed insert (unique index still kept as a guarantee).
(Classic example how could be used SAVEPOINTs -:))
I guess so.
At 03:05 PM 27-08-2001 -0400, Alex Pilosov wrote:
On Thu, 23 Aug 2001 [EMAIL PROTECTED] wrote:
THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL
1. I cant get a clear answer on what kind of data type to use for my large
text string? TEXT, ???, ??? or something about TOAST
I have seen in
At 11:55 AM 28-08-2001 +0200, Karel Zak wrote:
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
bad chars and internaly for data storage save less space than text
with base64. Of course it doesn't
At 11:55 AM 28-08-2001 +0200, Karel Zak wrote:
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
Another point:
I have no problems with base64[1]. However I was thinking that it might be
far easier for
I had a similar issue.
I needed to make sure I had a unique row- insert if not there, update if
there.
So I resorted to locking the whole table, then select, then insert/update.
What Tom told me to do was to use lock table tablename in exclusive mode
for my case.
This blocks select for
Hi has anyone tried Intel's compiler yet?
http://developer.intel.com/software/products/eval/
Just wondering what would happen.
Cheerio,
Link.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
At 04:50 PM 9/29/01 -0400, Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
On some operating systems, only one child at a time can accept() on the
socket. On these, you have to lock around the call to accept().
But how do you know the client wants the database you have forked? They
At 08:16 PM 30-09-2001 -0600, Steve Wolfe wrote:
How hard would it be to pre-fork an extra backend for the database a
user just requested so if they next user asks for the same database, the
backend would already be started?
Perhaps I'm missing something, but it seems to me that the
Hi people,
Is it possible for Postgresql to bind to one IP address?
I'm trying to run multiple postgresql installations on one server.
The unix socket could be named accordingly:
Postgresql config bound to a particular port and all IPs.
.s.PGSQL.portnumber
Postgresql config bound to a
I'll take a shot at improving the documentation for bytea. I'm hoping
documentation patches are accepted during beta though ;-)
Also, FWIW, 7.2 includes bytea support for LIKE, NOT LIKE, LIKE ESCAPE,
||, trim(), substring(), position(), length(), indexing, and various
comparators.
Cool!
At 10:02 AM 9/27/01 -0400, mlw wrote:
D. Hageman wrote:
I agree with everything you wrote above except for the first line. My
only comment is that process boundaries are only *truely* a powerful
barrier if the processes are different pieces of code and are not
dependent on each other in
At 10:18 AM 15-10-2001 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
Create a small program that makes a few connections to postgresql, does
some initialization, preconnects to various DBs (or maybe limited to one DB
specified on startup), and listens on one port/socket
At 11:16 PM 03-10-2001 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
Is it possible for Postgresql to bind to one IP address?
See 'virtual_host' GUC parameter.
regards, tom lane
Thanks!
I'm using a redhat style postgresql init and somehow postgresql
How would authentication and access control be done with a preforking
backend? I personally find a preforking backend desirable, but that's just me.
But if people really want preforking how about not doing it in the backend.
Create a small program that makes a few connections to postgresql,
You can also use stunnel for SSL. Preferable to having SSL in postgresql
I'd think.
Cheerio,
Link.
At 03:38 PM 3/16/02 -0500, Tom Lane wrote:
FWIW, I was not in favor of the SSL addition either, since (just as you
say) it does nothing that couldn't be done with an SSH tunnel. If I had
Note if you are trying to run more than one postgresql you also have to
prevent the unix socket files from clashing.
On Wed, 27 Mar 2002, Alastair D'Silva wrote:
Is there any way to force PostgreSQL to bind to a specific
IP address?
There doesn't seem to be anything about this in
Just curious - why is solaris qsort that way? Any good reasons? I saw a
very old post by a solaris guy, but it didn't seem very convincing.
By the way are there faster sorts which Postgresql can use for its sorting
other than quick sort? e.g. BSD 4.4 radixsort (which DJB seems to keep
going
At 05:33 PM 10/19/2005 -0700, Dann Corbit wrote:
If there is a significant performance benefit to not expanding text
columns in comparison operations, then it seems it should be OK.
I probably read the standard wrong, but it seems to me that varchar, char,
and bpchar columns should all
Hi,
Has anyone any input to offer on adding an arbitrary locking feature?
Where
GETLOCK "string" will lock on "string", the lock being only released at the
end of a transaction.
While the lock is held, other processes trying to do GETLOCK "string" will
block until the lock is released.
This
At 09:20 AM 11-01-2001 -0800, Mikheev, Vadim wrote:
In contrast the current alternatives appear to be either LOCK
the entire table (preventing ALL inserts and selects),
SHARE ROW EXCLUSIVE mode doesn't prevent selects...
Sorry, I meant all inserts and selects on the locked table. At least so
At 01:26 PM 11-01-2001 -0500, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
GETLOCK "string" will lock on "string", the lock being only released at the
end of a transaction.
However, the whole thing strikes me as more of an ugly kluge than a
clean solution
At 09:38 AM 11-01-2001 -0800, Adam Haberlach wrote:
We do something like this with listen/notify pairs. To syncronize
two clients, we have them each listen for the other's token string,
send a notify, and then block on select(), checking for incoming
notifications. When they get the
At 10:02 AM 1/25/01 -0500, you wrote:
When Postgresql 6.5 came out it, it was VERY MUCH better ( many many
thanks
to the developers and all involved). And I'm waiting for a solid 7.1 to
fix
that 8KB issue.
Technically..
= BLCKSZ (can be up to 32k)
I've been using PostgreSQL with a 32k
At 04:17 PM 2/16/01 -0500, Tom Lane wrote:
Vadim says (and I agree) that we really ought to implement a new
lightweight lock manager that would fall between spinlocks and regular
locks in terms of overhead and functionality. But it's not reasonable
Will there be an arbitrary user locking
Oops.
I rechecked the start up script, and the 7.0.3 doesn't have fsync off or
whatever. Dunno why I thought it was on (heh maybe because it was a lot
faster than 6.5.3!).
Hmm, this means 7.0.3 is quite fast...
Cheerio,
Link.
Just another data point.
I downloaded a snapshot yesterday - Changelogs dated Feb 20 17:02
It's significantly slower than "7.0.3 with fsync off" for one of my webapps.
7.0.3 with fsync off gets me about 55 hits per sec max (however it's
interesting that the speed keeps dropping with continued
At 05:07 PM 2/24/01 -0500, Tom Lane wrote:
is not a defined concept according to SQL. Even if we allowed queries
such as you've described, the results would not be well-defined, but
would change at the slightest provocation. The implementation feels
itself entitled to rearrange tuple order
At 04:58 PM 25-02-2001 -0500, Tom Lane wrote:
There's no LIMIT clause in UPDATE. You could do something like
Oh. I thought 7.1 had that.
BEGIN
SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1;
UPDATE todo SET pid = $mypid WHERE taskid = $selectedid;
COMMIT
At 11:16 PM 25-02-2001 -0500, Tom Lane wrote:
Right. Only the first row is locked, but that doesn't help any. "order
by random" sounds like it might be a good answer, if there aren't many
rows that need to be sorted.
Yep. I'll just see what happens in the testing stages.
What would happen
At 08:59 PM 11-03-2001 -0500, Bruce Momjian wrote:
How about "Connection terminated by administrator", or something like
that.
I prefer something closer to the truth.
e.g.
"Received SIGTERM, cancelling query and exiting"
(assuming it actually cancels the query).
But maybe I'm weird.
Cheerio,
At 05:28 PM 3/27/03 +0800, Christopher Kings-Lynne wrote:
There's no select * from table where pkey=x for insert; which would
block
on uncommitted inserts/updates of pkey=x and other selects for
insert/update.
How about user locks? Isn't there something in contrib/ for that??? I
could do a
AFAIK the except select won't see other inserts in uncommitted
transactions. If those transactions are committed you will end up with the
same problem. You can try it yourself, by manually doing two separate
transactions in psql.
You either have to lock the whole table, or lock at the
At 11:48 PM 8/27/2007, Trevor Talbot wrote:
On 8/27/07, Jonah H. Harris [EMAIL PROTECTED] wrote:
On 8/27/07, Tom Lane [EMAIL PROTECTED] wrote:
that and the lack of evidence that they'd actually gain anything
I find it somewhat ironic that PostgreSQL strives to be fairly
non-corruptable,
64 matches
Mail list logo
