> > Seems like a better solution. I can finish the patch pretty soon. I need
> > to contact the original author, who has disappeared, but I'll send it over
> > to you.
> Sounds good. I wondered where he'd gone to.
Still here :-)
Just got swamped with too much work that the tablesample
patch got p
On Fri, 2005-11-18 at 11:51 +1100, Gavin Sherry wrote:
> Seems like a better solution. I can finish the patch pretty soon. I need
> to contact the original author, who has disappeared, but I'll send it over
> to you.
Sounds good. I wondered where he'd gone to.
Sampling would be useful for 8,2
Be
[EMAIL PROTECTED]:08]~:90>grep search_path fritz-20051106.sql
ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public';
Trying that command in psql...
decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd,
page_log, public';
NOTICE: schema "decibel, rrs, rrd, pag
Hello,
It appears that predicate indexes won't uses indexes on int8 columns
unless they are casted:
test=# select count(*) from test_key ;
count
120617
(1 row)
test=# alter table test_key ALTER column id set statistics 25;
ALTER TABLE
test=# analyze test_key ;
test=# create index fo
I think the important thing to keep track of is a single bit:
Which the following apply?
a) all of the tuples in this block are visible
b) at least one tuple in this block is in-doubt and may need to be vacuumed
That isn't enough to calculate count(*) on its own but it means you could s
[EMAIL PROTECTED] wrote:
> Probably obvious, and already mentioned, count(*) isn't the only query
> that would benefit from visibility information in the index. It's
> rather unfortunate that MVCC requires table lookups, when all values
> queried or matched are found in the index key itself. The id
On Thu, 17 Nov 2005, Bruce Momjian wrote:
> Unless you have a table lock, INSERT has to be before UPDATE, think
> UPDATE, UPDATE (both fail), INSERT, INSERT.
No matter what operation you start with you need a loop that try
insert/update until one of them succeed like in this example:
http://www
>From a 7.4 dump:
[EMAIL PROTECTED]:08]~:90>grep search_path fritz-20051106.sql
ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public';
Trying that command in psql...
decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd,
page_log, public';
NOTICE: schema "deci
I agree, keeping it clustered would be very nice.
On 11/17/05, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Simon Riggs wrote:> On Thu, 2005-11-17 at 10:58 -0500, Tom Lane wrote:> > Simon Riggs <
[EMAIL PROTECTED]> writes:>> The use case exists and the technique is low overhead, but the main> question
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
> I will fix this tomorrow - it's about a 4 line fix. I've missed the details
> - we're just using an unquoted NULL in array literals?
Right. Case-insensitive, double-quote it if you want the literal string
instead.
regression=# select array[1,null,3]
Tom Lane said:
> I think plperl should be fixed to translate undef to NULL when
> returning an array, but currently it translates to an empty string:
>
> pl_regression=# CREATE OR REPLACE function returns_array() returns
> text[] as $$ pl_regression$# return ['a,b','c"d',undef,'e-f']; $$
> LANGUAGE
On Thu, Nov 17, 2005 at 08:41:51PM -0500, Tom Lane wrote:
> I think plperl should be fixed to translate undef to NULL when returning
> an array, but currently it translates to an empty string:
I'll take a look at this if nobody else steps up. It might just
be a minor change to this part of plperl
Tom Lane wrote:
> Csaba Nagy <[EMAIL PROTECTED]> writes:
> > OK, in this case I don't care about either MERGE or REPLACE, but for an
> > UPSERT which does the locking :-)
>
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn
[EMAIL PROTECTED] wrote:
> On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
> > REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
> > transaction which is supposed to not fail but instead do locking to
> > ensure that it doesn't fail. This requires predicate locking to b
Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.
---
Zeugswetter Andreas DCP SD wrote:
> > The problem I try to solve is something along: a bunch of clients try
Well yeah it'd be open source. If you license it BSD then anyone can do
anything with it. In your situation I'd probably recommend that you
don't release it... I'd also be hesitant to accept it :)
Chris
Javier Soltero wrote:
Hi Chris,
Hyperic would be happy to donate an exported version o
Simon Riggs wrote:
> On Thu, 2005-11-17 at 10:58 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
>
> The use case exists and the technique is low overhead, but the main
> question is: Does anybody think this behaviour would be beneficial for
> them? (I'm actually in two minds mys
I think plperl should be fixed to translate undef to NULL when returning
an array, but currently it translates to an empty string:
pl_regression=# CREATE OR REPLACE function returns_array() returns text[] as $$
pl_regression$# return ['a,b','c"d',undef,'e-f']; $$ LANGUAGE plperl;
CREATE FUNCTION
p
Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> If we can't use an OID to identify a data type
>> completely, we're going to have lots of problems.
> You only really need two pieces of information to uniquely identify an
> array data type -- the OID of the (leaf-node) scalar elements,
Probably obvious, and already mentioned, count(*) isn't the only query
that would benefit from visibility information in the index. It's
rather unfortunate that MVCC requires table lookups, when all values
queried or matched are found in the index key itself. The idea of an
all index table is appea
On Fri, 18 Nov 2005, Simon Riggs wrote:
> >From here, another proposal. We have a GUC called count_uses_estimate
> that is set to off by default. If set to true, then a count(*) will use
> the planner logic to estimate number of rows in the table and return
> that as the answer, rather than actual
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
... My hope was that eventually anyarray I/O functions
could eliminate the need to create an array type for every data type you
wanted to use as an array element.
Interesting thought, but then how do you declare the type of an array
colum
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Thursday, November 17, 2005 4:17 PM
> To: Simon Riggs
> Cc: Kevin Grittner; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Improving count(*)
>
> Simon Riggs <[EMA
Simon Riggs <[EMAIL PROTECTED]> writes:
> From here, another proposal. We have a GUC called count_uses_estimate
> that is set to off by default. If set to true, then a count(*) will use
> the planner logic to estimate number of rows in the table and return
> that as the answer, rather than actually
Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> How about changing them so that the lower bound of the right-hand array
>> is preserved, rather than decreased by one?
> That seems reasonable. I'll do it if you'd like...
I'll take care of it --- I'm still hacking in that general area a
On Thu, 2005-11-17 at 16:30 -0600, Kevin Grittner wrote:
> In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL
> Server) the leaf level of the narrowest index on the table is scanned,
> following a linked list of leaf pages. Leaf pages can be pretty dense
> under Sybase, because t
Tom Lane wrote:
AFAICS the only cases that give rise to arrays with lower bounds other
than one are:
* direct entry of a literal with explicit lower bound;
* assignment to a subscript or slice below 1;
* array_prepend (and the N/N+1-dimension case of array_cat).
I don't t
Andrew Dunstan wrote:
It does seem a bit inconsistent that psql wouldn't connect to the
specified database in order to do -l, if one is specified.
Anyone want to look and see if it's easy to change?
options.action == ACT_LIST_DB && options.dbname == NULL ?
"postgres" : options.d
Martijn van Oosterhout writes:
> Now, lets say you add a field to the tuple which you the position of
> the index entry. You can only reasonably do this for one index, say the
> primary key. Now you have a two-way link the updating becomes much
> quicker, at the cost of even more overhead.
I thin
In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL
Server) the leaf level of the narrowest index on the table is scanned,
following a linked list of leaf pages. Leaf pages can be pretty dense
under Sybase, because they do use prefix compression. A count(*)
on a table with 100 mi
On Fri, 18 Nov 2005 05:29 am, Tom Lane wrote:
> It does seem a bit inconsistent that psql wouldn't connect to the
> specified database in order to do -l, if one is specified.
> Anyone want to look and see if it's easy to change?
It also breaks the ability to psql -l against older installations: e.
On Thu, Nov 17, 2005 at 09:34:08PM +, Simon Riggs wrote:
> Adding visibility to an index would add substantial bulk to any index.
> If we could do this at the same time as adding leading key, full field
> compression (*not* prefix compression), then it might be worth doing.
I think the single
On Thu, 2005-11-17 at 10:58 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > When a table has been CLUSTERed on a particular index AND that index
> > values is monotonically increasing, then it would be a bad move to use
> > blocks from the FSM since this would tend to destroy t
On Thu, 2005-11-17 at 16:34 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > Bearing in mind other RDBMS' approach is to count the number of rows in
> > an index, their cost is probably about the same as scanning table
> > blocks/10 very roughly - so the cost is far from zero fo
Simon Riggs <[EMAIL PROTECTED]> writes:
> Bearing in mind other RDBMS' approach is to count the number of rows in
> an index, their cost is probably about the same as scanning table
> blocks/10 very roughly - so the cost is far from zero for them.
Really? The impression I get is that people who a
On Thu, 2005-11-17 at 14:46 -0500, Jonah H. Harris wrote:
> Nice suggestion, I think it's workable but (like all other methods)
> has some technical/pseudo-political challenges.
>
> I'm still voting for my old, "Much Ado About COUNT(*)" topic; adding
> visibiility to the indexes and counting th
On Thu, Nov 17, 2005 at 02:55:09PM -0500, Rod Taylor wrote:
> On Thu, 2005-11-17 at 20:38 +0100, Martijn van Oosterhout wrote:
> > It's an interesting idea, but you still run into the issue of
> > visibility. If two people start a transaction, one of them inserts a
> > row and then both run a selec
A couple clarifications:
There were only a few network sockets open.
I'm told that the eventlog was reviewed for any events which
mgiht be related to the failures before it was cleared. They
found none, so that makes it fairly certain there was no 2020
event.
-Kevin
>>> "Kevin Grittner" <[EMA
On Thu, 2005-11-17 at 20:38 +0100, Martijn van Oosterhout wrote:
> On Thu, Nov 17, 2005 at 07:28:10PM +, Simon Riggs wrote:
> > One of the major complaints is always "Select count(*) is slow".
> >
> > I have a somewhat broadbrush idea as to how we might do this (for larger
> > tables).
>
> I
There weren't a large number of connections -- it seemed to be
that the one big update query, by itself, would do this. It seemed
to get through a lot of rows before failing. This table is normally
"insert only" -- so it would likely be getting most or all of the space
for inserting the updated r
Simon,
Nice suggestion, I think it's workable but (like all other methods) has some technical/pseudo-political challenges.
I'm still voting for my old, "Much Ado About COUNT(*)" topic; adding visibiility to the indexes and counting them like the other RDBMS vendors. True, it would add storage
On Thu, Nov 17, 2005 at 07:28:10PM +, Simon Riggs wrote:
> One of the major complaints is always "Select count(*) is slow".
>
> I have a somewhat broadbrush idea as to how we might do this (for larger
> tables).
It's an interesting idea, but you still run into the issue of
visibility. If two
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> I don't have a lot of use for arguments that go "we should remove any
>> functionality that's not in the spec" ... ISTM that variable lower
>> bounds are clearly useful for some applications, and even if they had
>> bu
One of the major complaints is always "Select count(*) is slow".
I have a somewhat broadbrush idea as to how we might do this (for larger
tables).
Previously, we've said that maintaining a running count of the tuples in
a table is hard, or at least costly. However, maintaining a partial
count ma
> None of this seems material, however. It's pretty clear that
> the problem was exhaustion of the Windows page pool. Our
> Windows experts have reconfigured the machine (which had been
> tuned for Sybase ASE). Their changes have boosted the page
> pool from 20,000 entries to 180,000 entries
> >>> Tom Lane <[EMAIL PROTECTED]> >>>
> "Kevin Grittner" <[EMAIL PROTECTED]> writes:
> > None of this seems material, however. It's pretty clear that the
> > problem was exhaustion of the Windows page pool.
> > ...
> > If we don't want to tell Windows users to make highly technical
> > changes
Tom Lane wrote:
"Michael Paesold" <[EMAIL PROTECTED]> writes:
It's the '-l' option (list all databases) that does not honor the database
given on the command line.
This does not work, if the postgres database is dropped in 8.1:
psql -l template1
It does seem a bit incons
"Michael Paesold" <[EMAIL PROTECTED]> writes:
> It's the '-l' option (list all databases) that does not honor the database
> given on the command line.
> This does not work, if the postgres database is dropped in 8.1:
> psql -l template1
It does seem a bit inconsistent that psql wouldn't connect
I'm not an expert on that, but it seems reasonable to me that the
page pool would free space as the I/O system caught up with
the load. Also, I'm going on what was said by Qingqing and
in one of the pages he referenced:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274310
-Kevin
>>>
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> None of this seems material, however. It's pretty clear that the
> problem was exhaustion of the Windows page pool.
> ...
> If we don't want to tell Windows users to make highly technical
> changes to the Windows registry in order to use PostgreSQL,
>
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
> REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
> transaction which is supposed to not fail but instead do locking to
> ensure that it doesn't fail. This requires predicate locking to be
> efficient because you want to
1) We run a couple Java applications on the same box to provide
middle tier access. When the box is heavily loaded, I think I've
seen about 80% PostgreSQL, 20% Java load.
2) I checked that no antivirus software was running, and had the
techs pare down the services running on that box to the absol
On 11/16/05, Rod Taylor <[EMAIL PROTECTED]> wrote:
> On Mon, 2005-11-14 at 14:51 -0500, Tom Lane wrote:
> > Merlin Moncure <[EMAIL PROTECTED]> writes:
> > > esp=# select prl_combined_key, prl_seq_no, xmin, xmax, lastmod from
> > > parts_order_line_file where prl_combined_key = ' 00136860' and
> > >
Simon Riggs wrote:
> When a table has been CLUSTERed on a particular index AND that index
> values is monotonically increasing, then it would be a bad move to use
> blocks from the FSM since this would tend to destroy the natural
> clustering sequence.
>
> The index values will be monotonically in
Simon Riggs <[EMAIL PROTECTED]> writes:
> When a table has been CLUSTERed on a particular index AND that index
> values is monotonically increasing, then it would be a bad move to use
> blocks from the FSM since this would tend to destroy the natural
> clustering sequence.
By the time there are an
When a table has been CLUSTERed on a particular index AND that index
values is monotonically increasing, then it would be a bad move to use
blocks from the FSM since this would tend to destroy the natural
clustering sequence.
The index values will be monotonically increasing if a datatype is
defin
Joshua D. Drake wrote:
psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
I can't test createdb here but on the psql... what happens if you just:
psql -Upostgres template1?
It's the '-l' option (list all databases) that does not honor the database
given on the command
* Tom Lane ([EMAIL PROTECTED]) wrote:
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn say MERGE does.
> (I haven't bothered to look at the 2003 spec, I'm assuming they read it
> correctly.) What we *have* been asked for,
John Hansen wrote:
On a fresh installation of postgrsql 8.1if you drop the
'postgres' database,
psql, createdb, etc. no longer works.
psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
I can't test createdb here but on the psql... what happens if you just:
psql
"John Hansen" <[EMAIL PROTECTED]> writes:
> Maybe it should fallback to template1 if the postgres database doesn't
> exist?
[ shrug... ] template1 can be dropped, too. More to the point, if
they fall back to connecting to template1, then we lose the very thing
we wanted to gain, which is that th
Csaba Nagy <[EMAIL PROTECTED]> writes:
> OK, in this case I don't care about either MERGE or REPLACE, but for an
> UPSERT which does the locking :-)
This is exactly the point --- pretty much nobody has come to us and
asked for a feature that does what Peter and Martijn say MERGE does.
(I haven't b
"Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes:
>> No. There is no reason to use a volume's root directory as a
>> tablespace;
>> especially so since the root directory ought to be owned by root
> That is not so on AIX. Only the moint point (the dir in the parent) is
> root.
> Once mount
On 11/17/05, Martijn van Oosterhout wrote:
> On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
> > On a fresh installation of postgrsql 8.1 if you drop the
> > 'postgres' database,
> > psql, createdb, etc. no longer works.
> >
> > psql -l; ignores -dtemplate1, and createdb doesn't
Martijn van Oosterhout wrote:
On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
On a fresh installation of postgrsql 8.1if you drop the
'postgres' database,
psql, createdb, etc. no longer works.
psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
""Magnus Hagander"" <[EMAIL PROTECTED]> wrote
>
> Seems like we could just retry when we get this failure. The question is
> we need to do a small amount of sleep before we do? Also, we can't just
> retry forever, there has to be some kind of end to it...
> (If you read the SQL kb, it can be read
Martijn van Oosterhout wrote:
What distribution? I've never seen this "postgres" database you speak
of. It certainly not on any systems I've used.
It's new in 8.1 and is used as the default connection database for createdb,
etc.
Best Regards,
Michael Paesold
---(en
On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
> On a fresh installation of postgrsql 8.1 if you drop the
> 'postgres' database,
> psql, createdb, etc. no longer works.
>
> psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
What distribution? I've never see
On a fresh installation of postgrsql 8.1if you drop the
'postgres' database,
psql, createdb, etc. no longer works.
psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
Maybe it should fallback to template1 if the postgres database doesn't
exist?
... John
OK, in this case I don't care about either MERGE or REPLACE, but for an
UPSERT which does the locking :-)
Cheers,
Csaba.
On Thu, 2005-11-17 at 13:32, Martijn van Oosterhout wrote:
> On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote:
> > Yes, these algorithms are clear to me, but they don
On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote:
> Yes, these algorithms are clear to me, but they don't work for batch
> updates in postgres without savepoints before each row insert/update,
> which is not good for performance (not to mention on older postgres
> versions without savepoi
Yes, these algorithms are clear to me, but they don't work for batch
updates in postgres without savepoints before each row insert/update,
which is not good for performance (not to mention on older postgres
versions without savepoint support it won't work at all). If there is a
way of no race condi
> 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
Well, from my point of view it is more than delete and insert. That I
can do right now with existing infrastructure. 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 cur
[copying this one over to hackers]
> Our DBAs reviewed the Microsoft documentation you referenced,
> modified the registry, and rebooted the OS. We've been
> beating up on the database without seeing the error so far.
> We'll keep at it for a while.
Very interesting. As this seems to be a re
75 matches
Mail list logo
