[PATCHES] WITH RECURSIVE patches 0803

[Tatsuo Ishii]

Hi,

Included are the latest WITH RECURSIVE patches against CVS HEAD.
The main differences from previous patches include:

- Allow multiple query names (mutual recursion is not still allowed)

  These are some examples from the regression test:

WITH RECURSIVE
x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS (values (1))
 SELECT * FROM x;

WITH RECURSIVE
   x(id) AS
 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
   y(id) AS
 (SELECT * FROM x UNION ALL SELECT * FROM x),
   z(id) AS 
 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
 SELECT * FROM z;

- Fix some cases where target list has subquries

WITH RECURSIVE t(id) AS (
SELECT (VALUES(1))
UNION ALL
SELECT id+1 FROM t WHERE id < 5
)
SELECT * FROM t;

Remaining works:

1) write sgml docs

2) write README

Currently I'm writing 2) based on the one posted before.

BTW, I'm traveling to the United States from Aug 4 to Aug 12. I'm
going to join Linux World, PGDay and pgpool party at Bruce's. I hope
to meet many community poeple soon!
--
Tatsuo Ishii
SRA OSS, Inc. Japan


recursive_query.patch.gz
Description: Binary data

-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [PATCHES] WIP: Transportable Optimizer Mode

[Simon Riggs]

Chris,

Thanks for all of those changes... added as suggested (in next version)

On Wed, 2008-07-30 at 14:58 -0400, chris wrote:

> It's not clear to me that the plugin is actually working.
> 
> When I run EXPLAIN against tables in "tomtestdb", I get results
> consistent with an unanalyzed table.  So possibly the "hook" isn't
> being used.  Perhaps I'm using it wrongly; perhaps what I have
> documented above may suggest to you what's broken.

The plugin works, but it currently extracts 0 for number of tuples. So
you need to override that to give a different answer.

I was going to add an SQL function to estimate the number of tuples in
the same way as the optimizer does. That way we get the same answer from
the EXPLAIN as we would have got on the main server and we don't need to
run select count(*) against each table (unless we want to).

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [PATCHES] pg_dump additional options for performance

[Tom Lane]

chris <[EMAIL PROTECTED]> writes:
> Do we need to wait until a fully-parallelizing pg_restore is
> implemented before adding this functionality to pg_dump?

They're independent problems ... and I would venture that parallel
dump is harder.

> Further, it's actually not obvious that we *necessarily* care about
> parallelizing loading data.  The thing that happens every day is
> backups.

Maybe so, but I would say that routine backups shouldn't be designed
to eat 100% of your disk bandwidth anyway --- they'd be more like
background tasks.

regards, tom lane

-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [HACKERS][PATCHES] odd output in restore mode

[Andrew Dunstan]

Simon Riggs wrote:

Well, this is a strange conclusion, leaving me slightly bemused.

The discussion between Andrew and I at PGcon concluded that we would
* document which other tools to use
* remove the delay

Now we have rejected the patch which does that, but then re-requested
the exact same thing again.

The patch interprets "remove the delay" as "remove the delay in a way
which will not screw up existing users of pg_standby when they upgrade".
Doing that requires us to have a configurable delay, which defaults to
the current behaviour, but that can be set to zero (the recommended
way). Which is what the patch implements.

Andrew, Heikki: ISTM its time to just make the changes yourselves. This
is just going round and round to no benefit. This doesn't warrant such a
long discussion and review process.
  


You ought to know by now that the length and ferocity of the discussion 
bears no relation at all to the importance of the subject ;-)


Personally, I think it's reasonable to provide the delay as long as it's 
switchable, although I would have preferred zero to be the default. If 
we remove it altogether then we force bigger changes on people who are 
currently using Windows copy. But I can live with that since changing 
their archive_command is the better path by far anyway, either to use 
Gnu cp or the copy / rename trick.


cheers

andrew



--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [HACKERS][PATCHES] odd output in restore mode

[Simon Riggs]

On Thu, 2008-07-31 at 12:32 -0400, Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> > Martin Zaun wrote:
> >> With these avenues to be explored, can the pg_standby patch on the
> >> CommitFest wiki be moved to the "Returned with Feedback" section?
> 
> > Yes, I think we can conclude that we don't want this patch as it is. 
> > Instead, we want a documentation patch that describes the problem, 
> > mentioning that GNU cp is safe, or you can use the copy+rename trick.
> 
> Right, after which we remove the presently hacked-in delay.
> 
> I've updated the commitfest page accordingly.

Well, this is a strange conclusion, leaving me slightly bemused.

The discussion between Andrew and I at PGcon concluded that we would
* document which other tools to use
* remove the delay

Now we have rejected the patch which does that, but then re-requested
the exact same thing again.

The patch interprets "remove the delay" as "remove the delay in a way
which will not screw up existing users of pg_standby when they upgrade".
Doing that requires us to have a configurable delay, which defaults to
the current behaviour, but that can be set to zero (the recommended
way). Which is what the patch implements.

Andrew, Heikki: ISTM its time to just make the changes yourselves. This
is just going round and round to no benefit. This doesn't warrant such a
long discussion and review process.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [PATCHES] pg_dump additional options for performance

[chris]

[EMAIL PROTECTED] (Tom Lane) writes:
> Simon Riggs <[EMAIL PROTECTED]> writes:
>> I want to dump tables separately for performance reasons. There are
>> documented tests showing 100% gains using this method. There is no gain
>> adding this to pg_restore. There is a gain to be had - parallelising
>> index creation, but this patch doesn't provide parallelisation.
>
> Right, but the parallelization is going to happen sometime, and it is
> going to happen in the context of pg_restore.  So I think it's pretty
> silly to argue that no one will ever want this feature to work in
> pg_restore.

"Never" is a long time, agreed.

> To extend the example I just gave to Stephen, I think a fairly probable
> scenario is where you only need to tweak some "before" object
> definitions, and then you could do
>
> pg_restore --schema-before-data whole.dump >before.sql
> edit before.sql
> psql -f before.sql target_db
> pg_restore --data-only --schema-after-data -d target_db whole.dump
>
> which (given a parallelizing pg_restore) would do all the time-consuming
> steps in a fully parallelized fashion.

Do we need to wait until a fully-parallelizing pg_restore is
implemented before adding this functionality to pg_dump?

The particular extension I'm interested in from pg_dump, here, is the
ability to dump multiple tables concurrently.  I've got disk arrays
with enough I/O bandwidth that this form of parallelization does
provide a performance benefit.

The result of that will be that *many* files are generated, and I
don't imagine we want to change pg_restore to try to make it read from
multiple files concurrently.

Further, it's actually not obvious that we *necessarily* care about
parallelizing loading data.  The thing that happens every day is
backups.  I care rather a lot about optimizing that; we do backups
each and every day, and optimizations to that process will accrue
benefits each and every day.

In contrast, restoring databases does not take place every day.  When
it happens, yes, there's considerable value to making *that* go as
quickly as possible, but I'm quite willing to consider optimizing that
to be separate from optimizing backups.

I daresay I haven't used pg_restore any time recently, either.  Any
time we have thought about using it, we've concluded that the
perceivable benefits were actually more of a mirage.
-- 
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/lsf.html
Rules  of the  Evil Overlord  #145. "My  dungeon cell  decor  will not
feature exposed pipes.  While they add to the  gloomy atmosphere, they
are good  conductors of vibrations and  a lot of  prisoners know Morse
code." 

-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [PATCHES] [HACKERS] Hint Bits and Write I/O

[Simon Riggs]

On Sat, 2008-08-02 at 00:24 -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > I think it makes sense to commit this patch now, per previous
> > discussions on which we have agreed to make incremental changes.
> 
> Yeah, but at the same time there is merit in the argument that the
> proposed patch hasn't actually been proven to be usable for anything.
> I would be a lot happier if there were even a trivial proof-of-concept
> plugin example submitted with it, just to prove that there were no
> showstopper problems in the plugin design, like failure to pass
> essential information or not getting the locking straight.

Plugins were my other patch. I did originally submit a version with
changes, but this patch was specifically a version with *no* external
behaviour changes, to form a base from which various people's ideas
might be explored.

> > I'm just wondering if the change of usage_count from 16 to 8 bits was
> > discussed and agreed?
> 
> Umm ... it was not, but given that we have logic in there to limit the
> usage_count to 5 or so, it's hard to argue that there's a big problem.

It was discussed and it was Tom's suggestion to do this. I agreed!

> I confess to not having read the patch in detail --- where did the other
> 8 bits go to?

Keeping track of the number of hints set on a block since last write.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches