Re: [HACKERS] generic options for explain

[Pavel Stehule]

2009/5/25 Tom Lane :
> Greg Smith  writes:
>> On Sun, 24 May 2009, Pavel Stehule wrote:
>>> we should have a secondary function explain_query(query_string,
>>> option) that returns setof some.
>
>> +1.  The incremental approach here should first be adding functions that
>> actually do the work required.  Then, if there's a set of those that look
>> to be extremely useful, maybe at that point it's worth talking about how
>> to integrate them into the parser.  Starting with the parser changes
>> rather than the parts that actually do the work is backwards.  If you do
>> it the other way around, at all times you have a patch that actually
>> provides immediate useful value were it to be committed.
>
>> Something that returns a setof can also be easily used to implement the
>> "dump EXPLAIN to a table" feature Josh Tolley brought up (which is another
>> common request in this area).
>
> A serious problem with EXPLAIN via a function returning set, or with
> putting the result into a table, is that set results are logically
> unordered, just as table contents are.  So from a strict point of view
> this only makes sense when the output format is designed to not depend
> on row ordering to convey information.  We could certainly invent such
> a format, but I think it's a mistake to go in this direction for
> EXPLAIN output that is similar to the current output.

I don't expect so functional EXPLAIN will be used by users directly.
It' data source for some "GUI". And currently with CTE, there are not
problem transform query to similar output like current EXPLAIN. I am
able to understand some new parameters for explain statement (when
result will be directly read by user), but some output options (or
formating options) I would to see in some other functions.

regards
Pavel Stehule

>
>                        regards, tom lane
>

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

Re: [HACKERS] proposal: SQL parser integration to PL/pgSQL

[Pavel Stehule]

2009/5/24 Tom Lane :
> Pavel Stehule  writes:
>> ==Steps==
>> 1. add hook to analyser (transform stage) to substitute unknown
>> columnref by param - when analyser detect unknown columnref, then call
>> callback, that returns possible para node or NULL (when external
>> environment doesn't have a variable). Returned param should be typed
>> or unknown (for polymorphic params).
>
> IMHO the hook definition should support both the case of external
> variables taking precedence over query variables and vice versa.
> I don't think the core parser should be forcing that decision.  In any
> case we'd probably need both options for plpgsql, so as to be able to
> support both traditional and Oracle-compatible behavior.

good idea

>
> I'd be inclined to do that by letting the hook function interpose
> itself between the parser and the regular transformColumnRef processing,
> so that it can call the regular transformColumnRef processing either
> before or after doing its external lookups.  Giving it control only
> after the regular processing fails would mean there's no way to let
> external variables take precedence.
>

ok

>> 2. add special modes to sql parser:
>
> None of those seem like a good idea to me.  The only part that seems
> useful is warning about conflicts between external variables and query
> variables.  That can be implemented by the hook function itself, if we
> define the hook behavior as above.
>

there is minimal one necessary - for polymorphic variables, we know
name, but we don't know type. And without types, we can't to transform
correctly functions.

regards
Pavel Stehule


>                        regards, tom lane
>

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

Re: [HACKERS] generic options for explain

[Joshua Tolley]

On Sun, May 24, 2009 at 06:53:29PM -0400, Tom Lane wrote:
> Greg Smith  writes:
> > On Sun, 24 May 2009, Pavel Stehule wrote:
> >> we should have a secondary function explain_query(query_string,
> >> option) that returns setof some.
> 
> > +1.  The incremental approach here should first be adding functions that 
> > actually do the work required.  Then, if there's a set of those that look 
> > to be extremely useful, maybe at that point it's worth talking about how 
> > to integrate them into the parser.  Starting with the parser changes 
> > rather than the parts that actually do the work is backwards.  If you do 
> > it the other way around, at all times you have a patch that actually 
> > provides immediate useful value were it to be committed.
> 
> > Something that returns a setof can also be easily used to implement the 
> > "dump EXPLAIN to a table" feature Josh Tolley brought up (which is another 
> > common request in this area).
> 
> A serious problem with EXPLAIN via a function returning set, or with
> putting the result into a table, is that set results are logically
> unordered, just as table contents are.  So from a strict point of view
> this only makes sense when the output format is designed to not depend
> on row ordering to convey information.  We could certainly invent such
> a format, but I think it's a mistake to go in this direction for
> EXPLAIN output that is similar to the current output.

The Oracle version, as it fills the table of explain results, gives each number
an id and the id of its parent row, which behavior we could presumably copy.
I'm definitely keen to keep a human-readable EXPLAIN such as we have now, to
augment the table-based proposal, but a table would provide the more flexible
output we'd need for more detailed reporting, a simple interface for
applications to consume the EXPLAIN data without human intervention, and a
convenient platform from whence the data can be transformed to XML, JSON, etc.
for those that are so inclined.

- Josh / eggyknap


signature.asc
Description: Digital signature

[HACKERS] No sanity checking performed on binary TIME parameters.

[Andrew McNamara]

When submitting a query via the V3 binary protocol (PQexecParams,  
paramFormats[n]=1), it appears the PostgreSQL server performs no range  
checking on the passed values. Passing values greater than 24 hours  
results in unpredictable results (dumps that cannot be restored,  
strange output when printing the column in psql, etc). Tested with  
version 8.1 and 8.2 (integer_datetimes is false).


Using my python ocpgdb module (http://code.google.com/p/ocpgdb/):

>>> db.execute('select %s::time::text', DateTimeDelta(0,23,59,59))
[('23:59:59',)]
>>> db.execute('select %s::time::text', DateTimeDelta(0,28,0,0))
[('K|\x1f',)]

ocpgdb has a lower-level API which is a thin layer on top of libpq -  
exercising this directly to rule out any problems with the  
mx.DateTime.DateTimeDelta class yields the same results:


>>> import struct
>>> import ocpgdb, oclibpq
>>> db=oclibpq.PgConnection('')
>>> list(db.execute('select $1::time::text', [(ocpgdb.pgoid.time,  
struct.pack('!d', 23*60*60))]))
[(0x42a4a0>,)]
>>> list(db.execute('select $1::time::text', [(ocpgdb.pgoid.time,  
struct.pack('!d', 48*60*60))]))
[(0x42a500>,)]


Apologies if this bug has already been addressed - I didn't find any  
references to it while googling.


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

Re: [HACKERS] generic options for explain

[Greg Smith]

On Sun, 24 May 2009, Tom Lane wrote:


A serious problem with EXPLAIN via a function returning set, or with
putting the result into a table, is that set results are logically
unordered, just as table contents are.


Fair enough; I think Pavel and myself were presuming an implied "line 
number" in the output there that, as you point out, doesn't actually 
exist.  The actual implementation detail there doesn't really change the 
spirit of what I was trying to suggest though:  that this feature should 
get designed starting with the expected output and how to generate it, 
then work backwards towards how you pass it parameters.


On that topic, I though Tom Raney was the latest to update code for the 
XML output at least.  Code and a presentation going over everything is at 
http://web.cecs.pdx.edu/~raneyt/gsoc/ and there's a video at 
http://www.postgresqlconference.org/2008/west/talks/


A note about that got sent to this list at one point but I don't see any 
follow-up: 
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00802.php


Anybody have a better idea of what happened with that project than me?

--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 6:05 PM, Greg Smith  wrote:
> On Sun, 24 May 2009, Pavel Stehule wrote:
>
>> we should have a secondary function explain_query(query_string,
>> option) that returns setof some.
>
> +1.  The incremental approach here should first be adding functions that
> actually do the work required.  Then, if there's a set of those that look to
> be extremely useful, maybe at that point it's worth talking about how to
> integrate them into the parser.  Starting with the parser changes rather
> than the parts that actually do the work is backwards.  If you do it the
> other way around, at all times you have a patch that actually provides
> immediate useful value were it to be committed.

Well, perhaps I ought to be asking what sort of features people would
like to see, other than variant output formats?  Maybe if we can
develop some kind of wish list for EXPLAIN, it will become more
obvious what the best option syntax is.

...Robert

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

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 6:53 PM, Tom Lane  wrote:
> Greg Smith  writes:
>> On Sun, 24 May 2009, Pavel Stehule wrote:
>>> we should have a secondary function explain_query(query_string,
>>> option) that returns setof some.
>
>> +1.  The incremental approach here should first be adding functions that
>> actually do the work required.  Then, if there's a set of those that look
>> to be extremely useful, maybe at that point it's worth talking about how
>> to integrate them into the parser.  Starting with the parser changes
>> rather than the parts that actually do the work is backwards.  If you do
>> it the other way around, at all times you have a patch that actually
>> provides immediate useful value were it to be committed.
>
>> Something that returns a setof can also be easily used to implement the
>> "dump EXPLAIN to a table" feature Josh Tolley brought up (which is another
>> common request in this area).
>
> A serious problem with EXPLAIN via a function returning set, or with
> putting the result into a table, is that set results are logically
> unordered, just as table contents are.  So from a strict point of view
> this only makes sense when the output format is designed to not depend
> on row ordering to convey information.  We could certainly invent such
> a format, but I think it's a mistake to go in this direction for
> EXPLAIN output that is similar to the current output.

The current output format basically prints out the node type and then
a collection of properties that are associated with that node, where
applicable: join type, scan direction, strategy, relation, alias,
startup cost, total cost, rows, width, loops, filter, join filter,
various types of condition (index/recheck/tid/merge/hash), sort key.
However, we tend to omit certain fields (particularly scan direction
and alias) when they contain information that isn't sufficiently
interesting.  That's probably not a good idea for anything that's
intended for machine-parsing; I think for XML or JSON or
output-to-a-table we'd want to include those fields whenever possible.

With that minor complaint (and the difficulty of figuring out how to
avoid duplicating large portions of explain.c), XML or JSON output
doesn't seem that difficult.  In JSON there aren't too many ways of
doing this that make sense.  I would guess we'd want something like
this:

{ "node" : "Hash Join", "jointype" : "Inner", "startup_cost" :
"11.49", "total_cost" : "92.59", "rows" : "1877", "width" : "325",
   'outer' : { "node" : "Seq Scan",  }
   'inner' : { "node" : "Hash",  }
}

XML, being XML, has 10 ways of doing something when 1 is more than
sufficient, so there are a couple of ways we could go.  I think this
is my favorite.


   
   
  
   


or you could just use one node type:


   
   
  
   


...and then there's this style:


  inner
  11.49
  ...
  
 ...
  
  
  ...
  


...which is incredibly long and redundant, but some people who use XML
like such things.  I'm sure there are other possibilities as well.

With respect to table output things are a little bit less
straightforward.  There are two issues.  With XML or JSON, any
properties that aren't relevant to a particular node can be omitted
altogether, whereas for a table the column list has to be consistent
throughout.  We can just leave the unused columns as NULL, but it will
be a fairly wide table.  Also, with apologies to Josh Berkus, we'll
need to introduce some sort of non-natural primary key to allow
children to be related to parents, because unlike XML and JSON,
there's no built-in way to make one object the child of another.

All that having been said, making EXPLAIN into a query (so that you
can do INSERT INTO foo (EXPLAIN SELECT query...) might be useful to
some people even without changing the output format at all.  I think
you could throw a windowing function in there to at least tag each
line with its original position in the output, and some people might
want just that.

...Robert

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

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 4:59 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Sun, May 24, 2009 at 3:09 PM, Tom Lane  wrote:
>>> You know about SET LOCAL, no?  I don't think this argument is very
>>> convincing.
>
>> I completely fail to see how that helps me.
>
> Mainly, what it does is avoid having to know exactly what the old
> setting was.

Ah, OK, I see what you're going for.   That's not really what I'm
complaining about with that syntax, though

...Robert

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

Re: [HACKERS] pull raw text of a message by message-id

[Andrew Dunstan]

Tom Lane wrote:

Alvaro Herrera  writes:
  

There already is one :-)  Look at the headers -- after the Message-Id there is
a link which says "text/plain".



On the couple of messages I tried this on, that link leads to a page
saying "not found" :-(


  


Really? I just tried 4 pages picked at random off the -hackers list  
 and all 
the "text/plain" links worked as expected. I even went back to something 
in 2000 and it still worked.


cheers

andrew

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

Re: [HACKERS] WAL archive, warm backup and read-only slave

[Tom Lane]

pg...@mohawksoft.com writes:
> How difficult would it be, and does anyone think it is possible to have a
> continuous "restore_command" ala pg_standby running AND have the database
> operational in a "read-only" mode?

See all the discussion of "hot standby" over the past eight or so months.

regards, tom lane

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

[HACKERS] WAL archive, warm backup and read-only slave

[pgsql]

How difficult would it be, and does anyone think it is possible to have a
continuous "restore_command" ala pg_standby running AND have the database
operational in a "read-only" mode?



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

Re: [HACKERS] pull raw text of a message by message-id

[Tom Lane]

Andrew Dunstan  writes:
> Tom Lane wrote:
>> On the couple of messages I tried this on, that link leads to a page
>> saying "not found" :-(

> Really? I just tried 4 pages picked at random off the -hackers list  
>  and all 
> the "text/plain" links worked as expected.

I retried the messages I looked at before, and they work now, so I guess
the update script hadn't finished?

regards, tom lane

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

Re: [HACKERS] generic options for explain

[Tom Lane]

Greg Smith  writes:
> On Sun, 24 May 2009, Pavel Stehule wrote:
>> we should have a secondary function explain_query(query_string,
>> option) that returns setof some.

> +1.  The incremental approach here should first be adding functions that 
> actually do the work required.  Then, if there's a set of those that look 
> to be extremely useful, maybe at that point it's worth talking about how 
> to integrate them into the parser.  Starting with the parser changes 
> rather than the parts that actually do the work is backwards.  If you do 
> it the other way around, at all times you have a patch that actually 
> provides immediate useful value were it to be committed.

> Something that returns a setof can also be easily used to implement the 
> "dump EXPLAIN to a table" feature Josh Tolley brought up (which is another 
> common request in this area).

A serious problem with EXPLAIN via a function returning set, or with
putting the result into a table, is that set results are logically
unordered, just as table contents are.  So from a strict point of view
this only makes sense when the output format is designed to not depend
on row ordering to convey information.  We could certainly invent such
a format, but I think it's a mistake to go in this direction for
EXPLAIN output that is similar to the current output.

regards, tom lane

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

Re: [HACKERS] [PATCH] cleanup hashindex for pg_migrator hashindex compat mode (for 8.4)

[Tom Lane]

Kenneth Marshall  writes:
> On Sat, May 23, 2009 at 02:52:49PM -0400, Zdenek Kotala wrote:
>>> Attached patch cleanups hash index headers to allow compile hasham for
>>> 8.3 version. It helps to improve pg_migrator with capability to migrate
>>> database with hash index without reindexing.

> How does that work with the updated hash functions without a reindex?

I looked at this patch and I don't see how it helps pg_migrator at all.
It's just pushing some code and function declarations around.

The rearrangement might be marginally nicer from a code beautification
point of view --- right now we're a bit inconsistent about whether
datatype-specific hash functions live in hashfunc.c or in the datatype's
utils/adt/ file.  But I'm not sure that removing hashfunc.c altogether is
an appropriate solution to that, not least because of the loss of CVS
history for the functions.  I'd be inclined to leave the core hash_any()
code where it is, if not all of these functions altogether.

What does seem useful is to refactor the headers so that datatype hash
functions don't need to include all of the AM's implementation details.
But this patch seems to do both more and less than that --- I don't
think it's gotten rid of all external #includes of access/hash.h, and
in any case moving the function code is not necessary to that goal.

In any case, the barriers to implementing 8.3-style hash indexes in 8.4
are pretty huge: you'd need to duplicate not only the hash AM code, but
also all the hash functions, and therefore all of the hash pg_amop and
pg_amproc entries.  Given the close-to-zero usefulness of hash indexes
in production installations, I don't think it's worth the trouble.  It
would be much more helpful to look into supporting 8.3-compatible GIN
indexes.

regards, tom lane

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

Re: [HACKERS] integer overflow in reloption.h

[Tom Lane]

Zdenek Kotala  writes:
> The problem is on the following lines

> typedef enum relopt_kind
> {
> ...
> RELOPT_KIND_MAX = (1 << 31)
> }

> enum is int datatype and 1 << 31 == -2147483648. It is reason why
> compiler (sun studio) complains.

> Is possible to change it to 1 << 30 to stop compiler generates noise?

Yeah, but we also have to fix the code that uses it.  Done.

regards, tom lane

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

Re: [HACKERS] pg_class and enum types

[Gevik Babakhani]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Gevik Babakhani wrote:


select * from pg_class where relname='test_type'
  


  
It's not so much that enum types are handled specially, but that 
composite types are. :-)



Relations (tables) have always had both pg_class and pg_type entries.
The pg_class entry denotes the relation proper, the pg_type entry
denotes the relation's rowtype.

Composite types have the same two entries, there's just a different
notion of which one is primary.

(The reason a composite type has to have a pg_class entry is that
it has pg_attribute entries, and those have to have something in
pg_class for their attrelid to link to.)

regards, tom lane

  

Thank you :)

--
Regards,
Gevik


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

Re: [pgsql-www] [HACKERS] pull raw text of a message by message-id

[Alvaro Herrera]

Tom Lane escribió:
> Alvaro Herrera  writes:
> > There already is one :-)  Look at the headers -- after the Message-Id there 
> > is
> > a link which says "text/plain".
> 
> On the couple of messages I tried this on, that link leads to a page
> saying "not found" :-(

Probably you tried messages that hadn't been indexed.  I had only
generated pgsql-hackers, and even then not the most recent ones; but I
just added the generation step to the script that runs every 10 minutes,
so all recent messages should be indexed on all lists.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] generic options for explain

[Greg Smith]

On Sun, 24 May 2009, Pavel Stehule wrote:


we should have a secondary function explain_query(query_string,
option) that returns setof some.


+1.  The incremental approach here should first be adding functions that 
actually do the work required.  Then, if there's a set of those that look 
to be extremely useful, maybe at that point it's worth talking about how 
to integrate them into the parser.  Starting with the parser changes 
rather than the parts that actually do the work is backwards.  If you do 
it the other way around, at all times you have a patch that actually 
provides immediate useful value were it to be committed.


Something that returns a setof can also be easily used to implement the 
"dump EXPLAIN to a table" feature Josh Tolley brought up (which is another 
common request in this area).


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] pull raw text of a message by message-id

[Tom Lane]

Alvaro Herrera  writes:
> There already is one :-)  Look at the headers -- after the Message-Id there is
> a link which says "text/plain".

On the couple of messages I tried this on, that link leads to a page
saying "not found" :-(

regards, tom lane

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

[HACKERS] A couple of regression test anomalies

[Tom Lane]

During PGCon, Teodor asked me about a couple of odd behaviors in the
regression tests --- the output from Peter's code coverage tests was
showing that some GIN code he expected to be exercised wasn't.
After some poking around, I think we've narrowed it down to two
separate issues:

1. In create_index.sql, there's some tests that try to force use of
a plain indexscan, and then force use of a bitmapscan for the same
queries.  Since GIN indexes no longer have amgettuple support, the
first group of these tests are dead code --- they end up executing
seqscans anyway.

We could rip out the dead tests.  Or we could leave them there, on the
grounds that GIN amgettuple support might reappear someday.  If we
do the latter, I'd be inclined to add comments to the test script
explaining the situation.

2. In tsearch.sql, there are multiple places where the test is trying
to exercise some just-created index.  This works as expected if the
tests are run serially, but the indexes are (usually) not used if the
tests are run in parallel.  The reason is that the table was UPDATEd
earlier in the test, so it has broken HOT chains, and other tests that
are run concurrently with this one usually have open transactions that
are older than the index.

There are a number of possible responses to #2, for instance:

2A.  We could opine that this is not a bug; the whole point of running
parallel tests is to exercise code paths that are not taken in the
serial tests.

2B.  We could try to avoid the broken-HOT-chain behavior, either by not
doing UPDATEs on the test table or by running this test by itself rather
than as part of a parallel group.

2C.  We could try to force both code paths to be tested in either test
mode.  However I'm not sure how we could do that in serial test mode.

2D.  We could try to refine the indcheckxmin behavior so that it notices
that the indexes can be used anyway in this case.  That would likely be
a bit too tricky for late beta, though, even if possible at all.

After some thought I'm leaning to the "not a bug" school of thought,
but I could probably be convinced otherwise.

Comments?

regards, tom lane

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

Re: [HACKERS] generic options for explain

[Joshua Tolley]

On Sun, May 24, 2009 at 11:57:13AM -0400, Andrew Dunstan wrote:
>
>
> Robert Haas wrote:
 EXPLAIN ('hash_detail', 'on') query...
   
>>
>> Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
>> follow my own syntax.
>>
>>   
>>> I am sorry - this is really strange syntax . Who will use this syntax?
>>> For some parser is little bit better function call, than parametrized
>>> statement. Some dificulties with options should be fixed with named
>>> param (we are speaking about 8.5).
>>>
>>> select explain_xml("select ...", true as hash_detail, ...)
>>> 
>>
>> See to me THAT is a really strange syntax, so I guess we need some more 
>> votes.
>>
>>
>>   
>
> Both of these seem both odd an unnecessary. Why not just have a setting  
> called, say, explain_format which governs the output?
>
>set explain_format = 'xml, verbose';
>explain select * from foo;
>
> No new function or syntax would be required.

A further possibility: Oracle's equivalent of EXPLAIN doesn't actually output
anything to the screen, but rather fills in a (temporary?) table somewhere with
details of the query plan. I mostly found this irritating when working with
Oracle, because each time I used it I had to look up an example query to
generate output like PostgreSQL's EXPLAIN, which is generally what I really
wanted. But since we'd still have the old EXPLAIN behavior available, perhaps
something such as an Oracle-like table filler would be useful.

Such a proposal doesn't answer the need to allow users to specify, for
performance and other reasons, the precise subset of statistics they're
interested in; for whatever it's worth, my current favorite contender in that
field is EXPLAIN (a, b, c) .

- Josh / eggyknap


signature.asc
Description: Digital signature

Re: [HACKERS] pg_class and enum types

[Tom Lane]

Andrew Dunstan  writes:
> Gevik Babakhani wrote:
>> select * from pg_class where relname='test_type'

> It's not so much that enum types are handled specially, but that 
> composite types are. :-)

Relations (tables) have always had both pg_class and pg_type entries.
The pg_class entry denotes the relation proper, the pg_type entry
denotes the relation's rowtype.

Composite types have the same two entries, there's just a different
notion of which one is primary.

(The reason a composite type has to have a pg_class entry is that
it has pg_attribute entries, and those have to have something in
pg_class for their attrelid to link to.)

regards, tom lane

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

Re: [HACKERS] pg_class and enum types

[Gevik Babakhani]

Andrew Dunstan wrote:



Gevik Babakhani wrote:
I was wondering why there is no pg_class record for the enum types. 
Do we treat the enum types differently?




Why do you think we should? What would the record look like?

cheers

andrew
I am not implying whether we  should or we should  not.  I was just  
looking  for the  logic behind it.

Re-reading what I just wrote with your reply gives me hint.

Thanx.

--
Regards,
Gevik


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

Re: [HACKERS] pull raw text of a message by message-id

[Alvaro Herrera]

Robert Haas escribió:

> Oh, this is awesome.  It would be handy to include a link on the HTML
> version of the page for when people are trying to pull down a patch
> that someone has foolishly included in the body of the email rather
> than attaching it.

There already is one :-)  Look at the headers -- after the Message-Id there is
a link which says "text/plain".

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] pg_class and enum types

[Andrew Dunstan]

Gevik Babakhani wrote:

Robert Haas wrote:
On Sun, May 24, 2009 at 4:37 PM, Gevik Babakhani  
wrote:
 
I was wondering why there is no pg_class record for the enum types. 
Do we

treat the enum types differently?



Because types are stored in pg_type, not pg_class?

...Robert
  

That is certainly not true check the following...

create type test_type as
(
field1 integer,
field2 varchar
);

select * from pg_class where relname='test_type'


It's not so much that enum types are handled specially, but that 
composite types are. :-)


There is no pg_class entry for int either.

cheers

andrew

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

Re: [HACKERS] pg_class and enum types

[Andrew Dunstan]

Gevik Babakhani wrote:
I was wondering why there is no pg_class record for the enum types. Do 
we treat the enum types differently?




Why do you think we should? What would the record look like?

cheers

andrew

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

Re: [HACKERS] pg_class and enum types

[Gevik Babakhani]

Robert Haas wrote:

On Sun, May 24, 2009 at 4:37 PM, Gevik Babakhani  wrote:
  

I was wondering why there is no pg_class record for the enum types. Do we
treat the enum types differently?



Because types are stored in pg_type, not pg_class?

...Robert
  

That is certainly not true check the following...

create type test_type as
(
field1 integer,
field2 varchar
);

select * from pg_class where relname='test_type'

--
Regards,
Gevik


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

Re: [HACKERS] generic options for explain

[Tom Lane]

Robert Haas  writes:
> On Sun, May 24, 2009 at 3:09 PM, Tom Lane  wrote:
>> You know about SET LOCAL, no?  I don't think this argument is very
>> convincing.

> I completely fail to see how that helps me.

Mainly, what it does is avoid having to know exactly what the old
setting was.

regards, tom lane

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

Re: [HACKERS] pg_class and enum types

[Robert Haas]

On Sun, May 24, 2009 at 4:37 PM, Gevik Babakhani  wrote:
> I was wondering why there is no pg_class record for the enum types. Do we
> treat the enum types differently?

Because types are stored in pg_type, not pg_class?

...Robert

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

[HACKERS] pg_class and enum types

[Gevik Babakhani]

I was wondering why there is no pg_class record for the enum types. Do 
we treat the enum types differently?


--
Regards,
Gevik


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

Re: [HACKERS] proposal: SQL parser integration to PL/pgSQL

[Tom Lane]

Pavel Stehule  writes:
> ==Steps==
> 1. add hook to analyser (transform stage) to substitute unknown
> columnref by param - when analyser detect unknown columnref, then call
> callback, that returns possible para node or NULL (when external
> environment doesn't have a variable). Returned param should be typed
> or unknown (for polymorphic params).

IMHO the hook definition should support both the case of external
variables taking precedence over query variables and vice versa.
I don't think the core parser should be forcing that decision.  In any
case we'd probably need both options for plpgsql, so as to be able to
support both traditional and Oracle-compatible behavior.

I'd be inclined to do that by letting the hook function interpose
itself between the parser and the regular transformColumnRef processing,
so that it can call the regular transformColumnRef processing either
before or after doing its external lookups.  Giving it control only
after the regular processing fails would mean there's no way to let
external variables take precedence.

> 2. add special modes to sql parser:

None of those seem like a good idea to me.  The only part that seems
useful is warning about conflicts between external variables and query
variables.  That can be implemented by the hook function itself, if we
define the hook behavior as above.

regards, tom lane

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

Re: [HACKERS] Oracle to Postgres : create type as object in Postgres

[Ben Ali Rachid]

>No, we don't have any concept of member functions.  Just create the

>composite type (I'm assuming it needs to be composite) and then create

>functions that take it as parameter.

>

>Note that because PG allows function overloading, there's no conflict

>between, say, to_string(D_Temp_Element) and to_string(Some_Other_Type).

>If you were only using member functions to prevent that kind of

>conflict, I don't think you need to worry too much.


OK thanks.




>PS: this is not an appropriate question for -hackers.  Please direct

>simple usage questions to -general in future.


Sorry.



  

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 3:09 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I wouldn't mind having a GUC to set the *default* explain behavior -
>> but I'd still like to be able to override it for a particular command
>> if I so choose.  And that's not going to be possible with your syntax:
>> if explain_format is set to 'xml, verbose' and I want plain text
>> output for one command, how do I get it?  Presumably I have to change
>> explain_format, run my EXPLAIN, and then change it back again.  Blech!
>
> You know about SET LOCAL, no?  I don't think this argument is very
> convincing.

I completely fail to see how that helps me.  It's not faster or fewer
commands to type:

begin transaction; set local whatever; explain blah; commit transaction;

than it is to type:

set whatever; explain blah; set oldwhatever;

> On the other side of the coin, I'm strongly against inventing more than
> one new output format for EXPLAIN, and so any argument that depends on
> examples such as "xml vs json" is falling on deaf ears here.  I think
> that we only need an XML option, and so EXPLAIN ANALYZE XML ... doesn't
> seem untenable.  What other options than those do you really need?
> Not ones to add or remove output fields; we'd expect the client to
> ignore fields it doesn't care about.

It's not just about me; we've had many requests for new EXPLAIN features.

Personally, I'd like to see the number of buckets and batches that a
hash join uses (predicted and actual), and maybe (with ANALYZE) the
number of hash collisions.  I'd like to see memory utilization
statistics (peak memory utilization of hash join, for example).  Other
people have requested I/O statistics (which you objected to on the
grounds that it would be too much overhead, so clearly if we're ever
going to do it it will have to be optional), and most recently number
of tuples discarded by the filter condition.  We've also had requests
to suppress some information (like costs) for planner regression
testing.

I really don't see the point in restricting the syntax of EXPLAIN in
this way.  I don't know exactly what sorts of useful options people
will come up with, but I do know that as long as we have an extremely
limiting options syntax, they can all be shot down on the grounds that
including them in the default output is too cumbersome for regular use
(which is absolutely true).  On the other hand, I think it's
incredibly naive to suppose that EXPLAIN XML is going to make anyone
very happy.  There are only two ways this can work out:

1. We'll make EXPLAIN XML output everything and the kitchen sink.  In
this case, we'll soon get complaints along the lines of: "I can't use
regular EXPLAIN because it doesn't include the field that I need, but
the output of EXPLAIN XML is so voluminous that I can't read through
it by hand."
-or-
2. We'll be very restrictive about adding fields to EXPLAIN XML just
as we are now for plain EXPLAIN, in which case we haven't solved
anything.

I think XML output format is a complete distraction from the real
issue here, which is that there are certain pieces of information that
are sometimes useful but are not useful enough to justify including
them in the EXPLAIN output 100% of the time.  By just punting all that
stuff to EXPLAIN XML, we're just saying that we're not interested in
creating a workable set of options to allow users to pick and choose
the information they care about - so instead we're going to dump a
huge chunk of unreadable XML and then make it the user's problem to
find a tool that will extract the details that they care about.  Boo,
hiss.

Anyway, I'm suprised by the reaction to this patch, but I'll drop it.
I would like to make the EXPLAIN syntax more powerful for command-line
use, and I'd implement XML format and JSON along the way just for
completeness.  But I don't have much interest in creating an XML
output format that is the ONLY way of getting more information,
because I'm a command-line user and it does me no good at all.  :-(

...Robert

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

Re: [HACKERS] generic options for explain

[Tom Lane]

Robert Haas  writes:
> I wouldn't mind having a GUC to set the *default* explain behavior -
> but I'd still like to be able to override it for a particular command
> if I so choose.  And that's not going to be possible with your syntax:
> if explain_format is set to 'xml, verbose' and I want plain text
> output for one command, how do I get it?  Presumably I have to change
> explain_format, run my EXPLAIN, and then change it back again.  Blech!

You know about SET LOCAL, no?  I don't think this argument is very
convincing.

On the other side of the coin, I'm strongly against inventing more than
one new output format for EXPLAIN, and so any argument that depends on
examples such as "xml vs json" is falling on deaf ears here.  I think
that we only need an XML option, and so EXPLAIN ANALYZE XML ... doesn't
seem untenable.  What other options than those do you really need?
Not ones to add or remove output fields; we'd expect the client to
ignore fields it doesn't care about.

regards, tom lane

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

Re: [HACKERS] pull raw text of a message by message-id

[Robert Haas]

On Sun, May 24, 2009 at 11:34 AM, Alvaro Herrera
 wrote:
> Tom Lane escribió:
>> Robert Haas  writes:
>> > We currently have a (really handy) facility to pull a message from the
>> > archives by message-ID, eg:
>>
>> > http://archives.postgresql.org/message-id/603c8f070905202040v66cd3054t434c0b73aa844...@mail.gmail.com
>>
>> > Could we possibly get a similar type of link that pulls the raw
>> > contents of the message, without any HTML markup?
>>
>> +1, but this would be better brought up on pgsql-www.
>
> I've been playing with this a bit and I've set up an experimental script.
> Please give it a try and let me know how it goes.
> http://archives.postgresql.org/msgtxt.php?id=MESSAGE-ID
>
> For example, this message is at:
> http://archives.postgresql.org/msgtxt.php?id=8797.1242906...@sss.pgh.pa.us
>
> The archives are now regenerating, which will take a couple of hours, to add
> a "text/plain" link at the right of the Message-Id line on the headers that
> will take you to this unadorned text copy.
>
> Note that I haven't set up an automatic run of the script that creates the
> text/plain copies yet, so this will only work for messages on pgsql-hackers
> that were posted before now.  I'll work on that later.
>
> Keep in mind that this is all experimental, so don't be surprised if things
> break horribly (but report them back).

Oh, this is awesome.  It would be handy to include a link on the HTML
version of the page for when people are trying to pull down a patch
that someone has foolishly included in the body of the email rather
than attaching it.

This will also be of great assistance in writing commitfest management
tools (on which point, I'm working on getting the stuff I finished
during PGcon up so people can criticize it :-) but I haven't quite
gotten there yet).

...Robert

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

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 11:57 AM, Andrew Dunstan  wrote:
>> Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
>> follow my own syntax.
>>> I am sorry - this is really strange syntax . Who will use this syntax?
>>> For some parser is little bit better function call, than parametrized
>>> statement. Some dificulties with options should be fixed with named
>>> param (we are speaking about 8.5).
>>> select explain_xml("select ...", true as hash_detail, ...)
>> See to me THAT is a really strange syntax, so I guess we need some more
>> votes.
> Both of these seem both odd an unnecessary. Why not just have a setting
> called, say, explain_format which governs the output?
>
>   set explain_format = 'xml, verbose';
>   explain select * from foo;
>
> No new function or syntax would be required.

Well, then you have to issue two commands to do one thing.  I don't
much like the practice of using GUCs to control behavior that you may
only want for the duration of one statement.

The fundamental problem here, at least as it seems to me, is that we
want more options for EXPLAIN, but the current syntax won't support
it, because it requires making everything a keyword (and fixing the
order).  So we can either add enough punctuation to de-confuse the
parser (which is what I tried to do) or we can switch to a totally
different method of controlling EXPLAIN behavior (as you and Pavel are
advocating).

I wouldn't mind having a GUC to set the *default* explain behavior -
but I'd still like to be able to override it for a particular command
if I so choose.  And that's not going to be possible with your syntax:
if explain_format is set to 'xml, verbose' and I want plain text
output for one command, how do I get it?  Presumably I have to change
explain_format, run my EXPLAIN, and then change it back again.  Blech!

My initial thought was to just use a list of key words for the
parameters, like this:

EXPLAIN (XML, VERBOSE) query...

...but I decided that wasn't a great idea, because it means that every
parameter has to be a boolean, which is probably more limiting than we
want to be.  It also gets confusing because some parameters (like
"XML" and "JSON") are incompatible while others (like "HASH_DETAIL"
and "MERGE_DETAIL") can be used together, but there's not a lot of
clue in the syntax itself that this is the case.  On the other hand,
if you write:

EXPLAIN (FORMAT 'XML', FORMAT 'JSON') ...
EXPLAIN (HASH_DETAIL 'ON', MERGE_DETAIL 'ON') ...

...it's obvious that the first case is qualitatively different from
the second one.  I think this makes it easier to code the behavior in
a way that is consistent and correct, and also easier to document for
the benefit of our users.  Now, we can do this part of it even if we
ultimately end up with a different overall syntax, for example:

explain_query(...query..., 'xml' as format, true as hash_detail)

I do suspect this will end up being a more complicated patch, and as I
said before, I don't really see the point.

As a point of historical interest, way back when (early 1997), we used
to support this syntax:

EXPLAIN WITH [COST|PLAN|FULL] query...

...but of course that's now impossible because WITH can be the first
word of a select query.

...Robert

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

Re: [HACKERS] Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?

[Tom Lane]

Dmitry Koterov  writes:
> No, I meant that in case of the row (1, NULL, NULL, 2, 3, NULL):
> - the corresponding NULL bitmap is (100110...)
> - the corresponding tuple is (1, 2, 3)
> - t_natts=3 (if I am not wrong here)

You are wrong --- t_natts would be six here.  In general the length of
the null bitmap in a tuple (if it has one at all) is always exactly
equal to its t_natts value.

regards, tom lane

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

Re: [HACKERS] Oracle to Postgres : create type as object in Postgres

[Tom Lane]

Ben Ali Rachid  writes:
> I've a Oracle type that I must translate to Postgres. This Oracle type is 
> like below :

> CREATE OR REPLACE
> type D_Temp_Element as object
> (
>    MEMBER FUNCTION to_string return Varchar2,
>    MEMBER FUNCTION duration return D_Interval,
>    ...
> ) ;

> How can I do that in Postgres ? Is there something that be equivalent ?

No, we don't have any concept of member functions.  Just create the
composite type (I'm assuming it needs to be composite) and then create
functions that take it as parameter.

Note that because PG allows function overloading, there's no conflict
between, say, to_string(D_Temp_Element) and to_string(Some_Other_Type).
If you were only using member functions to prevent that kind of
conflict, I don't think you need to worry too much.

regards, tom lane

PS: this is not an appropriate question for -hackers.  Please direct
simple usage questions to -general in future.

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

Re: [HACKERS] [PATCH] cleanup hashindex for pg_migrator hashindex compat mode (for 8.4)

[Kenneth Marshall]

On Sat, May 23, 2009 at 02:52:49PM -0400, Zdenek Kotala wrote:
> I forgot to fix contrib. Updated patch attached.
> 
>   Zdenek
> 
> Zdenek Kotala pe v p?? 22. 05. 2009 v 16:23 -0400:
> > Attached patch cleanups hash index headers to allow compile hasham for
> > 8.3 version. It helps to improve pg_migrator with capability to migrate
> > database with hash index without reindexing.
> > 
> > I discussed this patch year ago with Alvaro when we tried to cleanup
> > include bloating problem. It should reduce also number of including.
> > 
> > The main point is that hash functions for datatypes are now in related
> > data files in utils/adt directory. hash_any() and hash_uint32 it now in
> > utils/hashfunc.c.
> > 
> > It would be nice to have this in 8.4 because it allows to test index
> > migration functionality.
> > 
> > Thanks Zdenek
> > 

How does that work with the updated hash functions without a reindex?

Regards,
Ken

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

Re: [HACKERS] generic options for explain

[Andrew Dunstan]

Robert Haas wrote:

EXPLAIN ('hash_detail', 'on') query...
  


Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
follow my own syntax.

  

I am sorry - this is really strange syntax . Who will use this syntax?
For some parser is little bit better function call, than parametrized
statement. Some dificulties with options should be fixed with named
param (we are speaking about 8.5).

select explain_xml("select ...", true as hash_detail, ...)



See to me THAT is a really strange syntax, so I guess we need some more votes.


  


Both of these seem both odd an unnecessary. Why not just have a setting 
called, say, explain_format which governs the output?


   set explain_format = 'xml, verbose';
   explain select * from foo;

No new function or syntax would be required.

cheers

andrew

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

Re: [HACKERS] pull raw text of a message by message-id

[Alvaro Herrera]

Tom Lane escribió:
> Robert Haas  writes:
> > We currently have a (really handy) facility to pull a message from the
> > archives by message-ID, eg:
> 
> > http://archives.postgresql.org/message-id/603c8f070905202040v66cd3054t434c0b73aa844...@mail.gmail.com
> 
> > Could we possibly get a similar type of link that pulls the raw
> > contents of the message, without any HTML markup?
> 
> +1, but this would be better brought up on pgsql-www.

I've been playing with this a bit and I've set up an experimental script.
Please give it a try and let me know how it goes.
http://archives.postgresql.org/msgtxt.php?id=MESSAGE-ID

For example, this message is at:
http://archives.postgresql.org/msgtxt.php?id=8797.1242906...@sss.pgh.pa.us

The archives are now regenerating, which will take a couple of hours, to add
a "text/plain" link at the right of the Message-Id line on the headers that
will take you to this unadorned text copy.

Note that I haven't set up an automatic run of the script that creates the
text/plain copies yet, so this will only work for messages on pgsql-hackers
that were posted before now.  I'll work on that later.

Keep in mind that this is all experimental, so don't be surprised if things
break horribly (but report them back).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[HACKERS] Oracle to Postgres : create type as object in Postgres

[Ben Ali Rachid]

Hello, 

I've a Oracle type that I must translate to Postgres. This Oracle type is like 
below :

CREATE OR REPLACE
type D_Temp_Element as object
(
   MEMBER FUNCTION to_string return Varchar2,
   MEMBER FUNCTION duration return D_Interval,
   ...
) ;

How can I do that in Postgres ? Is there something that be equivalent ?
Thanks.

Rachid



  

Re: [HACKERS] generic options for explain

[Pavel Stehule]

2009/5/24 Robert Haas :
>>> EXPLAIN ('hash_detail', 'on') query...
>
> Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
> follow my own syntax.
>
>> I am sorry - this is really strange syntax . Who will use this syntax?
>> For some parser is little bit better function call, than parametrized
>> statement. Some dificulties with options should be fixed with named
>> param (we are speaking about 8.5).
>>
>> select explain_xml("select ...", true as hash_detail, ...)
>
I prefere little bit different syntax for named params like param =
value, or param => value, but syntax with AS is one confirmed.


> See to me THAT is a really strange syntax, so I guess we need some more votes.

ok
>
> ...Robert
>

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

Re: [HACKERS] generic options for explain

[Robert Haas]

>> EXPLAIN ('hash_detail', 'on') query...

Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
follow my own syntax.

> I am sorry - this is really strange syntax . Who will use this syntax?
> For some parser is little bit better function call, than parametrized
> statement. Some dificulties with options should be fixed with named
> param (we are speaking about 8.5).
>
> select explain_xml("select ...", true as hash_detail, ...)

See to me THAT is a really strange syntax, so I guess we need some more votes.

...Robert

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

Re: [HACKERS] generic options for explain

[Pavel Stehule]

2009/5/24 Robert Haas :
> On Sun, May 24, 2009 at 8:44 AM, Dave Page  wrote:
>> On Sun, May 24, 2009 at 12:31 AM, Pavel Stehule  
>> wrote:
>>> Hello
>>>
>>> why we develop a new syntax?
>>>
>>> we should have a secondary function explain_query(query_string,
>>> option) that returns setof some. Next function should be
>>> explain_query_xml. I thing so for typical use EXPLAIN statement is
>>> enough. And for machine procession some new function should be
>>> perfect.
>
> I don't understand - why do we want to switch from a command interface
> to a functional interface?  I was thinking:
>
> EXPLAIN (format 'xml') query...
> EXPLAIN (format 'json') query...

Personally, I thing, so this format is strange - and is some MySQLism.
There are statements like SHOW TABLES and similar. My proposal doesn't
change of current EXPLAIN. The sence is don't more complicate current
syntax and do things simply. Function has more advantage then one:

a) don't need changes in parser
b) simply add new variants
c) simply add new parameters

>
> What you're proposing will certainly be harder to code as well as more
> different from what we have now.  My approach has the advantage of
> being able to leverage the main parser to parse both the query and the
> options; with this approach, you'll need to pass the query text back
> to the main parser and then write separate code of some sort to parse
> the options.  Seems like extra work for no gain.
>
> The only possible advantage I can see of a functional interface is
> that the current implementation of EXPLAIN is treated as a utility
> command, so you can't do something like this:
>
> INSERT INTO foo (EXPLAIN SELECT 1);
>
> In my mind, however, fixing that would be preferable to (and probably
> easier than) changing the whole syntax.
>
>> I agree. We shouldn't be overloading EXPLAIN with a new option every
>> time someone thinks of something new they'd like to see. XML EXPLAIN
>> output would obviously be extensible and machine readable. We could
>> easily produce a library of XSLT stylesheets to format the output in
>> different ways without cluttering the server with extra code. The
>> benefits to applications that want to read the output are also pretty
>> obvious.
>
> Well, the major benefit of the existing EXPLAIN output is that you
> don't need an XSLT stylesheet to read it.  You can just be sitting
> there in psql and do an EXPLAIN, and look at the results, and solve
> your problem.  As we add options to EXPLAIN, I would like to see us
> support those in both the plaintext output and the XML output (and
> hopefully the JSON output) using the same syntax.
>
> If, for example, I need more information on what my hash joins are
> doing, I'd like to be able to do:
>
> EXPLAIN ('hash_detail', 'on') query...
>

I am sorry - this is really strange syntax . Who will use this syntax?
For some parser is little bit better function call, than parametrized
statement. Some dificulties with options should be fixed with named
param (we are speaking about 8.5).

select explain_xml("select ...", true as hash_detail, ...)

Pavel

> ...and have it tell me the # of buckets and batches for each hash join
> and whatever other information might be useful in that context.  I
> *don't* want to have the solution to that problem be: run
> explain_xml() on your query, then install an XSLT parser, then use
> this pregenerated XSLT stylesheet to extract your data from the XML
> document you got back from step #1, then view the resulting HTML in
> your web browser.  That may be a good solution for someone, but it
> certainly doesn't sound convenient for anyone who works primarily at
> the command line.
>
> It's also worth noting that there are some kinds of statistics (for
> example, I/O statistics) which can't be gathered without significantly
> impacting the performance of the query.  So you can't just take the
> approach of turning on every possible instrumentation when XML output
> is requested.
>
> I do agree with your point that even with an extensible options
> syntax, we can't just add an option for any old  thing that someone
> wants.  We should try to come up with a somewhat comprehensive list of
> types of instrumentation that someone might want and then group them
> into categories, with one toggle per category.
>
> ...Robert
>

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

Re: [HACKERS] generic options for explain

[Robert Haas]

On Sun, May 24, 2009 at 8:44 AM, Dave Page  wrote:
> On Sun, May 24, 2009 at 12:31 AM, Pavel Stehule  
> wrote:
>> Hello
>>
>> why we develop a new syntax?
>>
>> we should have a secondary function explain_query(query_string,
>> option) that returns setof some. Next function should be
>> explain_query_xml. I thing so for typical use EXPLAIN statement is
>> enough. And for machine procession some new function should be
>> perfect.

I don't understand - why do we want to switch from a command interface
to a functional interface?  I was thinking:

EXPLAIN (format 'xml') query...
EXPLAIN (format 'json') query...

What you're proposing will certainly be harder to code as well as more
different from what we have now.  My approach has the advantage of
being able to leverage the main parser to parse both the query and the
options; with this approach, you'll need to pass the query text back
to the main parser and then write separate code of some sort to parse
the options.  Seems like extra work for no gain.

The only possible advantage I can see of a functional interface is
that the current implementation of EXPLAIN is treated as a utility
command, so you can't do something like this:

INSERT INTO foo (EXPLAIN SELECT 1);

In my mind, however, fixing that would be preferable to (and probably
easier than) changing the whole syntax.

> I agree. We shouldn't be overloading EXPLAIN with a new option every
> time someone thinks of something new they'd like to see. XML EXPLAIN
> output would obviously be extensible and machine readable. We could
> easily produce a library of XSLT stylesheets to format the output in
> different ways without cluttering the server with extra code. The
> benefits to applications that want to read the output are also pretty
> obvious.

Well, the major benefit of the existing EXPLAIN output is that you
don't need an XSLT stylesheet to read it.  You can just be sitting
there in psql and do an EXPLAIN, and look at the results, and solve
your problem.  As we add options to EXPLAIN, I would like to see us
support those in both the plaintext output and the XML output (and
hopefully the JSON output) using the same syntax.

If, for example, I need more information on what my hash joins are
doing, I'd like to be able to do:

EXPLAIN ('hash_detail', 'on') query...

...and have it tell me the # of buckets and batches for each hash join
and whatever other information might be useful in that context.  I
*don't* want to have the solution to that problem be: run
explain_xml() on your query, then install an XSLT parser, then use
this pregenerated XSLT stylesheet to extract your data from the XML
document you got back from step #1, then view the resulting HTML in
your web browser.  That may be a good solution for someone, but it
certainly doesn't sound convenient for anyone who works primarily at
the command line.

It's also worth noting that there are some kinds of statistics (for
example, I/O statistics) which can't be gathered without significantly
impacting the performance of the query.  So you can't just take the
approach of turning on every possible instrumentation when XML output
is requested.

I do agree with your point that even with an extensible options
syntax, we can't just add an option for any old  thing that someone
wants.  We should try to come up with a somewhat comprehensive list of
types of instrumentation that someone might want and then group them
into categories, with one toggle per category.

...Robert

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

Re: [HACKERS] generic options for explain

[Dave Page]

On Sun, May 24, 2009 at 12:31 AM, Pavel Stehule  wrote:
> Hello
>
> why we develop a new syntax?
>
> we should have a secondary function explain_query(query_string,
> option) that returns setof some. Next function should be
> explain_query_xml. I thing so for typical use EXPLAIN statement is
> enough. And for machine procession some new function should be
> perfect.

I agree. We shouldn't be overloading EXPLAIN with a new option every
time someone thinks of something new they'd like to see. XML EXPLAIN
output would obviously be extensible and machine readable. We could
easily produce a library of XSLT stylesheets to format the output in
different ways without cluttering the server with extra code. The
benefits to applications that want to read the output are also pretty
obvious.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

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

Re: [HACKERS] generic options for explain

[Andres Freund]

Hi Robert,

On 05/24/2009 02:47 AM, Robert Haas wrote:

Well, here we are!  Yet another thread about some piece of information
that's omitted from EXPLAIN and can't easily be added because there
are a zillion things we want to add to EXPLAIN and it's not OK to bury
the user[1]!  I've long been of the opinion that the right way to fix
this problem is to extend the syntax with some sort of extensible
options syntax[2].  The current "EXPLAIN [ANALYZE] [VERBOSE]"
syntax does not scale to large numbers of options - it requires that
the options occur in a fixed order, and that the option names all be
keywords.  Having gotten throughly fed up with having this
conversation for the ump-teenth time, I wrote a patch to introduce
just such a syntax.  See attached.



What I did is borrowed the generic options stuff that Peter Eisentraut
introduced for FOREIGN DATA WRAPPER et. al, so you can write:



EXPLAIN (option_name1 "option_value1", option_name2 "option_value2") query
e.g. EXPLAIN (ANALYZE "on") query
Beeing the latest cause for the frustration leading to this patch I 
obviously would like something like that - and I would gladly implement 
some additional stats suggested by others(if implementable in a 
reasonable timeframe) if this approach is agreed uppon.



- I noticed that we currently acce  pt as a top-level SQL command an
arbitrarily parenthesized SELECT statement, like ((SELECT 3)).  But
you can't put parentheses around any other type of statement.  Even
more oddly, we also accept things like (SELECT 3) ORDER BY 1, which to
me makes no sense at all.

I would guess that stems from supporting syntax like:
(SELECT 1)
UNION
(SELECT 2)
ORDER BY

and not wanting to introduce a special path for
(SELECT 1)
ORDER BY

For additional stats to be kept another discussion about appropriate, 
extensible representation suitable for different output formats probably 
would be needed - but thats a discussion for another day.


Andres

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

Re: [HACKERS] information_schema.columns changes needed for OLEDB

[Gevik Babakhani]

Josh Berkus wrote:

On 5/23/09 7:37 PM, Konstantin Izmailov wrote:

Number 4 is actually numeric_precision (I typed incorrectly). My
recollection is that numeric_precision sometimes expressed in radix 2
and it caused issues for Windows apps.

I agree on other issues. I was curious if database can help OLEDB driver
(to make it simpler). Anyway it can emulate values for specific Windows
apps on the fly. Thank you!


You could, of course, create your own ms_information_schema which had 
ms_friendly views of the IS.


This is what I have done for a past project. I do not think we should 
part from the standard SQL schema in order to satisfy a certain third 
party component.
If the information_schema does not provide all the information, one 
could always query the pg_* tables for needed data.


--
Regards,
Gevik


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

[HACKERS] proposal: SQL parser integration to PL/pgSQL

[Pavel Stehule]

==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).

2. add special modes to sql parser:
* that allows identify unknown columnref, but don't try to identify
functions, operators - but SRF function's should be identified,
because generate values (same with relations).

* that allows identify potential conflict's identifiers (maybe hook on
transformColumnRef?)

3. with this we should ensure some levels of SQL validation in
PL/pgSQL function:

* Full with warnings [FWW]- this identify identifier's collision (SQL,
PL/pgSQL),
* Full without warnings [FWOW] - this identify unknown functions,
unknown relations,
* Syntax - this identify correct syntax and unknown relation (ignore
check functions and operators),
* None - some "heuristic" minimalistic validation (like current) isn't
compatible and isn't usable.

4. For validation function user could to choose validation's level (GUC1),
5. For run - system is in [FWW] or [FWOW] mode (GUC2) - plans are
generate in compilation time for first run (?? we have to store
parseTree to protect from double query parsing).

==Issues==
* invasive patch - any relation changes (alter table add [drop]
column) should need "recompilation" (not only plan invalidation), new
dependency
* maybe little bit slower first run plpgsql functions,
* some new bugs
* developers have to change some habits - for full validatin should be
necessary creating "skeleton" functions.

==Benefits==
* identifier collisions should be detected clearly and early,
* SQL statements should be fully checked,
* some bugs will be displayed with clean messaage,
* more natural behave for people from Oracle, DB2
* allows named params for SQL

Note: this proposal is related to
http://archives.postgresql.org/pgsql-patches/2007-11/msg00253.php

Notes, comments?

Regards
Pavel Stehule

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