On Tue, 26 Apr 2005, Tom Lane wrote:
> Pavel Stehule <[EMAIL PROTECTED]> writes:
> > I tested bitmap scan and maybe I didnt find good examples, but with bitmap
> > scan is slower than hashjoin. Only when I use non otiptimized SELECT bps
> > was little bit faster. All my SELECTs are equal.
>
> B
Tom Lane <[EMAIL PROTECTED]> writes:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > If when we have partitions, that'll be good enough. If partitions aren't
> > available this would be quite painful to anyone with large tables --
> > much as the days of old used to be painful for ANALYZE.
>
> Yeah
Quoting Andrew Dunstan <[EMAIL PROTECTED]>:
> After some more experimentation, I'm wondering about some sort of
> adaptive algorithm, a bit along the lines suggested by Marko
Ristola, but limited to 2 rounds.
>
> The idea would be that we take a sample (either of fixed size, or
> some sm
Security holes? Explain yourself please.
No ownership, and no permissions...
As for the latter point, ISTM the todo item is "fix pg_dumpall" more
than "eliminate large objects". Certainly the fix isn't easy, but
that isn't an argument to cut and run.
I did have a plan to do this for 8.1, but so f
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> I wonder if there's any use for an allow_large_objects = true/false GUC
> parameter?
> It'd be nice to be able to switch it off as part of site policy so that
> the security holes in it aren't able to be exposed, plus you can
> guarantee as
Rod Taylor <[EMAIL PROTECTED]> writes:
> If when we have partitions, that'll be good enough. If partitions aren't
> available this would be quite painful to anyone with large tables --
> much as the days of old used to be painful for ANALYZE.
Yeah ... I am very un-enthused about these suggestions
Rod Taylor <[EMAIL PROTECTED]> writes:
> I eventually clued in and made a TOC and removed all of the Slony items,
> but I'm still curious to know what exactly pg_restore had been doing for
> the last hour or so.
You tell us ;-). You've got the test case, attach to it with a debugger
and find out
Brent Verner <[EMAIL PROTECTED]> writes:
> | I also wonder what happens when
> | the client and server disagree on the meaning of a filter name.
> How this is any different than saying "...when the client and
> server disagree on the meaning of a ProtocolVersion.", which is
> how ssl support is
On Apr 26, 2005, at 5:02 PM, Jan Wieck wrote:
The multi-action rules usually come into play when someone attempts to
make join-views updatable. Not an easy problem, granted, but most of
the time I have found a combination of rules together with ON
UPDATE/DELETE CASCADE constraints or even user d
I wonder if there's any use for an allow_large_objects = true/false GUC
parameter?
It'd be nice to be able to switch it off as part of site policy so that
the security holes in it aren't able to be exposed, plus you can
guarantee as the site admin that pg_dumpall will produce a complete
dump.
[2005-04-25 18:34] Tom Lane said:
| Brent Verner <[EMAIL PROTECTED]> writes:
| > I'd like to introduce the concept of (dynamically loaded) stream
| > filters that would be used to wrap calls to send/recv by the FE/BE
| > protocol.
| You certainly don't get to have any help
| from the database,
On Tue, 2005-04-26 at 19:28 -0400, Greg Stark wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
>
> > On Tue, 2005-04-26 at 19:03 -0400, Greg Stark wrote:
> > > This one looks *really* good.
> > >
> > > http://www.aladdin.cs.cmu.edu/papers/pdfs/y2001/dist_sampl.pdf
> > >
> > > It does require a
On 4/26/2005 5:58 PM, Tom Lane wrote:
David Wheeler <[EMAIL PROTECTED]> writes:
No, you can have multiple queries--you just have to understand that
those that come first might have an effect on those that come later.
... which indeed can be a feature, not a bug, depending on what you're
doing ...
On Apr 26, 2005, at 10:35 AM, Tom Lane wrote:
Once you've got such an infrastructure, it makes sense to allow an
interactive mode that automatically puts such things around each
statement. But I can't really see the argument for using such a
behavior in a script. Scripts are too stupid.
Would it
Rod Taylor <[EMAIL PROTECTED]> writes:
> On Tue, 2005-04-26 at 19:03 -0400, Greg Stark wrote:
> > This one looks *really* good.
> >
> > http://www.aladdin.cs.cmu.edu/papers/pdfs/y2001/dist_sampl.pdf
> >
> > It does require a single full table scan
>
> Ack.. Not by default please.
>
> I have
This one looks *really* good.
http://www.aladdin.cs.cmu.edu/papers/pdfs/y2001/dist_sampl.pdf
It does require a single full table scan but it works in O(n) time and
constant space and it guarantees the confidence intervals for the estimates it
provides like the histograms do for regular range s
On Apr 26, 2005, at 2:58 PM, Tom Lane wrote:
... which indeed can be a feature, not a bug, depending on what you're
doing ...
Absolutely. An INSERT rule I have looks like this:
CREATE RULE insert_one AS
ON INSERT TO one WHERE NEW.id IS NULL
DO INSTEAD (
INSERT INTO _simple (id, guid, state, name,
David Wheeler <[EMAIL PROTECTED]> writes:
> No, you can have multiple queries--you just have to understand that
> those that come first might have an effect on those that come later.
... which indeed can be a feature, not a bug, depending on what you're
doing ...
regards,
Hannu Krosing <[EMAIL PROTECTED]> writes:
> Could I avoid having a transaction at all?
Not really; too much of the database access infrastructure is tied to
transaction stuff ... even facilities as basic as memory management.
> As VACUUM is not "transactional" in the sense that it does not change
Rob Butler <[EMAIL PROTECTED]> writes:
> For this particular scenario, can't you just create
> two ON DELETE rules? The first would delete from b,
> the second from a. Perhaps an example with a scenario
> like this can be added to the doc's?
No, that doesn't work any more than the other way.
On Apr 26, 2005, at 2:43 PM, Rob Butler wrote:
For this particular scenario, can't you just create
two ON DELETE rules? The first would delete from b,
the second from a. Perhaps an example with a scenario
like this can be added to the doc's?
No, that approach has the same problem. Once the first
For this particular scenario, can't you just create
two ON DELETE rules? The first would delete from b,
the second from a. Perhaps an example with a scenario
like this can be added to the doc's?
So, the short answer is you can only perform one query
in a rule, but you can have multiple rules de
Simon Riggs wrote:
The comment
* Every value in the sample appeared more than once. Assume
* the column has just these values.
doesn't seem to apply when using larger samples, as Josh is using.
Looking at Josh's application it does seem likely that when taking a
sample, all site
On Mon, 2005-04-25 at 17:10 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Mon, 2005-04-25 at 11:23 -0400, Tom Lane wrote:
> >> It's not just the scan --- you also have to sort, or something like
> >> that, if you want to count distinct values. I doubt anyone is really
> >
Simon,
> Could it be that we have overlooked this simple explanation and that the
> Haas and Stokes equation is actually quite good, but just not being
> applied?
That's probably part of it, but I've tried Haas and Stokes on a pure random
sample and it's still bad, or more specifically overly co
On Sun, 2005-04-24 at 00:48 -0400, Tom Lane wrote:
> Josh Berkus writes:
> > Overall, our formula is inherently conservative of n_distinct. That is, I
> > believe that it is actually computing the *smallest* number of distinct
> > values which would reasonably produce the given sample, rather
On Apr 26, 2005, at 11:20 AM, Tom Lane wrote:
The problem is that OLD is effectively a macro for the view, and once
you've deleted one of the rows, that ID is no longer present anywhere
in
the view. Sometimes you can work around this by making the join an
outer join, but that's certainly a kluge.
On E, 2005-04-25 at 11:11 -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > On Sun, Apr 24, 2005 at 12:02:37PM +0300, Hannu Krosing wrote:
> >> Must some locks also be released an reaquired inside this loop, or is
> >> there something else I should keep in mind when trying to
On Apr 26, 2005, at 12:35 PM, Tom Lane wrote:
One possibility for an alternative mechanism is to allow triggers on
views --- but I'm not sure exactly how this would work, or if it would
solve all the problems. At the very least it would answer the "data
stability" issue, since I suppose the trigge
Jan Wieck <[EMAIL PROTECTED]> writes:
> On 4/26/2005 3:01 PM, Rob Butler wrote:
>> Are rules even needed anymore? Can't you do this all
>> with triggers? If you want to "DO INSTEAD" just use a
>> row based trigger, and return null. Or is this less
>> efficient?
> On INSERT, yes, on UPDATE, how
On 4/26/2005 3:01 PM, Rob Butler wrote:
Are rules even needed anymore? Can't you do this all
with triggers? If you want to "DO INSTEAD" just use a
row based trigger, and return null. Or is this less
efficient?
On INSERT, yes, on UPDATE, how so?
Jan
Later
Rob
--- David Wheeler <[EMAIL PROTECTED]>
Are rules even needed anymore? Can't you do this all
with triggers? If you want to "DO INSTEAD" just use a
row based trigger, and return null. Or is this less
efficient?
Later
Rob
--- David Wheeler <[EMAIL PROTECTED]> wrote:
> On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
>
> > Well, they handl
I took a pg_dump -Fc from a Pg 8.0.1 installation on (Solaris / Sun)
with Slony installed and attempted to restore to 8.0.2 on Linux / x86
without Slony installed.
pg_restore skipped all of the Slony related items, but then it
immediately jumped up to 700MB of memory usage and 99% CPU, but the DB
David Wheeler <[EMAIL PROTECTED]> writes:
> On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
>> Well, they handle simple situations OK, but we keep seeing people get
>> burnt as soon as they venture into interesting territory.
> [ snip ]
> Ah, yes, you're right, that is...unexpected. Perhaps OLD can
On Apr 25, 2005, at 11:00 PM, Tom Lane wrote:
DO INSTEAD means that the *original* query will not execute; it does
not suppress actions produced by other rules.
Ah!
If we did not define
it that way, I think your example would have to error out --- how
would you choose which INSTEAD rule wins?
The d
On Apr 25, 2005, at 11:37 PM, Tom Lane wrote:
(I have been thinking more and more that we should consider a wholesale
redesign of the rule mechanism, because it sure seems not to answer the
needs/expectations of a lot of people out there. But I am not talking
about marginal questions like what INS
It's interesting, that Tom's example behaves different on my notebook:
8.02 (default optimization)
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
QUERY PLAN
I didn't vacuum tenk1 after loading into 8.02, so optimizer was optimistic
and used index. After vacuuming I got what's Tom get.
On Tue, 26 Apr 2005, Tom Lane wrote:
In 8.0 this looks like
regression=# explain analyze select * from tenk1 where hundred between 1 and 10
and thousand between 1 and 10
On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
Well, they handle simple situations OK, but we keep seeing people get
burnt as soon as they venture into interesting territory. For
instance,
if the view is a join, you can't easily make a rule that turns a delete
into deletions of both joined rows. A
David Wheeler <[EMAIL PROTECTED]> writes:
> On Apr 25, 2005, at 11:37 PM, Tom Lane wrote:
>> (I have been thinking more and more that we should consider a wholesale
>> redesign of the rule mechanism, because it sure seems not to answer the
>> needs/expectations of a lot of people out there.
> I th
Andrew Dunstan <[EMAIL PROTECTED]> writes:
>>> \begin_ignore_error
>>> DROP TABLE foo;
>>> \end_ignore_error
> I meant it's a lot to type ;-)
Well, that's just a matter of choosing good (ie short) names for the
backslash commands. I was trying to be clear rather than proposing
names I would actu
Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
I would far rather see people code explicit markers around statements
whose failure can be ignored. That is, a script that needs this
behavior ought to look like
BEGIN;
\begin_ignore_error
DROP TABLE foo;
\end_igno
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I would far rather see people code explicit markers around statements
>> whose failure can be ignored. That is, a script that needs this
>> behavior ought to look like
>>
>> BEGIN;
>> \begin_ignore_error
>> DROP TABLE foo;
>> \end_ig
Pavel Stehule <[EMAIL PROTECTED]> writes:
> I tested bitmap scan and maybe I didnt find good examples, but with bitmap
> scan is slower than hashjoin. Only when I use non otiptimized SELECT bps
> was little bit faster. All my SELECTs are equal.
Bitmap scans can't possibly be any faster for cases
Tom Lane wrote:
Richard Huxton writes:
Michael Paesold wrote:
I just don't see why non-interactive mode does need such a switch
because there is no way to check if there was an error. So just put two
queries there and hope one will work?
DROP TABLE foo;
CREATE TABLE foo...
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> BEGIN;
>> \begin_ignore_error
>> DROP TABLE foo;
>> \end_ignore_error
>> CREATE ...
>> ...
>> COMMIT;
> That seems awful noisy. Why not just:
>BEGIN:
>DROP TABLE foo;
>ERROR: table foo does not exist;
>CONTINUE;
>
Philip Warner <[EMAIL PROTECTED]> writes:
> Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm
> told ;-).
Sure, but pg_dump scripts don't try to execute as a single transaction.
None of this discussion applies to the behavior outside an explicit
transaction block.
I'll have a look.
Tom Lane wrote:
Say guys, could you look into why btree_gist is failing its timetz
regression test in the 8.0 branch, if built with
--enable-integer-datetimes? Several of the buildfarm machines
are showing this, and I've reproduced it locally.
When I saw it I said to myself, "Oh,
Say guys, could you look into why btree_gist is failing its timetz
regression test in the 8.0 branch, if built with
--enable-integer-datetimes? Several of the buildfarm machines
are showing this, and I've reproduced it locally.
When I saw it I said to myself, "Oh, I forgot to back-patch the code
At 12:28 AM 27/04/2005, Tom Lane wrote:
Can you show a plausible use-case for such a thing?
A not-uncommon case in other DBs is to handle insert/update code where
insert is the most likely result. Not sure if this is relevant to scripts:
Begin;
...do stuff...
insert into
update...
...more st
In article <[EMAIL PROTECTED]>,
Richard Huxton writes:
>> I just don't see why non-interactive mode does need such a switch
>> because there is no way to check if there was an error. So just put
>> two queries there and hope one will work?
> DROP TABLE foo;
> CREATE TABLE foo...
Ah, my pet peev
I would far rather see people code explicit markers around statements
whose failure can be ignored. That is, a script that needs this
behavior ought to look like
BEGIN;
\begin_ignore_error
DROP TABLE foo;
\end_ignore_error
CREATE ...
...
COM
Richard Huxton writes:
> Michael Paesold wrote:
>> I just don't see why non-interactive mode does need such a switch
>> because there is no way to check if there was an error. So just put two
>> queries there and hope one will work?
> DROP TABLE foo;
> CREATE TABLE foo...
Unconvincing. What i
Richard Huxton wrote:
Michael Paesold wrote:
But people (like me for example) will want to enable this behaviour by
default. So they (me too) will put the option in .psqlrc. It is then
enabled "by default". But then many of my scripts will destroy data
instead of just erroring out.
I just don't
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes:
> To reiterate my opinion, I think the behavior should be the same
> for interactive and non-interactive sessions. Not only will it
> prevent nasty surprises, but unless we make a third 'setting',
> there will be no way to enable this in non-interac
Michael Paesold wrote:
But people (like me for example) will want to enable this behaviour by
default. So they (me too) will put the option in .psqlrc. It is then
enabled "by default". But then many of my scripts will destroy data
instead of just erroring out.
I just don't see why non-interactiv
Greg Sabino Mullane wrote:
To reiterate my opinion, I think the behavior should be the same
for interactive and non-interactive sessions. Not only will it
prevent nasty surprises, but unless we make a third 'setting',
there will be no way to enable this in non-interactive scripts,
which is somethin
Hello,
I get success, with my hyphotetic sample on big table (10 rec) Bitmap
index scan is really usefull.
I tested select count(*) from foo where
v IN (11,11,23,11,11,11,11,11,22,71,11)
Some notes.
1. with bitmap index scan 7.16 ms
2. without bis 165.731 ms (seq. scan)
3. list o
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
I tested bitmap scan and maybe I didnt find good examples, but with bitmap
scan is slower than hashjoin. Only when I use non otiptimized SELECT bps
was little bit faster. All my SELECTs are equal.
bsp off bsp on (ms)
SELECT 1 39.798 37.993
SELECT 2 0.310
Ok!
The Links your posted are great and i guessing it will help me a lot!
But the other Question (if Tablepartitioning under Developemt and will
it be supported generally)
is still alive.
Josh
Qingqing Zhou wrote:
<[EMAIL PROTECTED]> writes
I have done the following Post to PSQL Performance, b
<[EMAIL PROTECTED]> writes
> I have done the following Post to PSQL Performance, but Richard Huxton
> say it
> might be a better Idea to post it again on you List so i forward the
> message.
...
> It is exiting to see thadt Verison 8.0 has Tablespaces like ORACLE and
> DB/2,
> but i need Partition
Hi PSQL Hackers,
I have done the following Post to PSQL Performance, but Richard Huxton
say it
might be a better Idea to post it again on you List so i forward the
message.
[EMAIL PROTECTED] wrote:
Hi all,
Ia a Guy from Germany an a strong Postgres believer!
It is the best OpenSource Database i
63 matches
Mail list logo
