Re: [HACKERS] review: More frame options in window functions

[Hitoshi Harada]

2010/1/23 Robert Haas :
> On Tue, Jan 19, 2010 at 3:02 PM, Hitoshi Harada  wrote:
>> 2010/1/19 Hitoshi Harada :
>>> Yeah, that's my point, too. The planner has to distinguish "four" from
>>> sort pathkeys and to teach the executor the simple information which
>>> column should be used to determine frame. I was bit wrong because some
>>> of current executor code isn't like it, like using ordNumCols == 0 to
>>> know whether partition equals to frame, though
>>
>> And here's another version to fix this problem (I hope). Now the
>> planner distinguish sort column from actual significant pathkeys. I
>> tested it on both of 32bit and 64bit Linux.
>
> Would it make sense to pull some of the infrastructure bits out of
> this patch and commit those bits separately, so as to reduce the size
> of the main patch?  In particular, the AggGetMemoryContext() stuff
> looks like a good candidate for that treatment.

Fair enough. Attached contains that part only. I'll search more parts
like what you suggest, but there seems to be few parts because for
example the change of parser affects all the road to the executor.

> Why did you change BETWEEN from a TYPE_FUNC_NAME_KEYWORD to a 
> COL_NAME_KEYWORD?

Introducing new frame option syntax, shift/reduce conflict came happened.
http://archives.postgresql.org/message-id/e08cc0400911240908s7efaea85wc8505d228220b...@mail.gmail.com
http://archives.postgresql.org/message-id/6363.1229890...@sss.pgh.pa.us

Tom suggested it as RESERVED_KEYWORD a year ago, but COL_NAME_KEYWORD
works as well which is slightly more weak (ie more chances that you
can use the word) than RESERVED_KEYWORD. I'm not completely sure which
is better, though.

Regards,

-- 
Hitoshi Harada


agg_memorycontext.20100123.patch
Description: Binary data

-- 
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] Largeobject Access Controls (r2460)

[KaiGai Kohei]

(2010/01/23 5:12), Tom Lane wrote:
> KaiGai Kohei  writes:
>> The attached patch is a revised version.
> 
> I'm inclined to wonder whether this patch doesn't prove that we've
> reached the end of the line for the current representation of blobs
> in pg_dump archives.  The alternative that I'm thinking about is to
> treat each blob as an independent object (hence, with its own TOC
> entry).  If we did that, then the standard pg_dump mechanisms for
> ownership, ACLs, and comments would apply, and we could get rid of
> the messy hacks that this patch is just adding to.  That would also
> open the door to future improvements like being able to selectively
> restore blobs.  (Actually you could do it immediately if you didn't
> mind editing a -l file...)  And it would for instance allow loading
> of blobs to be parallelized.

I also think it is better approach than the current blob representation.

> Now the argument against that is that it won't scale terribly well
> to situations with very large numbers of blobs.  However, I'm not
> convinced that the current approach of cramming them all into one
> TOC entry scales so well either.  If your large objects are actually
> large, there's not going to be an enormous number of them.  We've
> heard of people with many tens of thousands of tables, and pg_dump
> speed didn't seem to be a huge bottleneck for them (at least not
> in recent versions).  So I'm feeling we should not dismiss the
> idea of one TOC entry per blob.

Even if the database contains massive number of large objects, all the
pg_dump has to manege on RAM is its metadata, not data contents.
If we have one TOC entry per blob, the amount of total i/o size between
server and pg_dump is not different from the current approach.

If we assume one TOC entry consume 64 bytes of RAM, it needs 450MB of
RAM for 7 million BLOBs.
In the recent computers, is it unacceptable pain?
If you try to dump TB class database, I'd like to assume the machine
where pg_dump runs has adequate storage and RAM.

Thanks,
-- 
KaiGai Kohei 

-- 
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] restructuring "alter table" privilege checks (was: remove redundant ownership checks)

[KaiGai Kohei]

(2010/01/23 1:27), Robert Haas wrote:

So, what if we treat these two cases separately?  The part-B checks -
on the other operations involved in ALTER TABLE - are by definition
idiosyncratic.  What type of object we're checking and what permission
we're checking for is inextricably bound up with what kind of ALTER
TABLE operation we're trying to perform.  So, that's going to be hard
to centralize.  But the part-A checks - on the relation itself - seem
like they could be consolidated.  The reason why they are spread out
right now is mostly because of relatively minor deviations from what
ATSimplePermissions does.


I agree with the top half of this proposition. ALTER TABLE has most
various kind of options in PostgreSQL, so some of options are not
feasible to complete all the needed checks in ATPrepCmd() stage,
such as AT_AddIndex.

However, it is unclear for me whether the revised ATSimplePermissions()
provide cleaner code than currently we have, because it also needs
a big switch ... case statement within.

Am I misunderstanding something?


A1. ATSimplePermissionsRelationOrIndex(), which a few of the ALTER
TABLE subcommands use, is exactly the same as ATSimplePermissions(),
except that it allows indices as well.
A2. ATSetStatistics() and ATSetDistinct() are also similar, but they
both allow indices and also skip the defenses against system table
modification.
A3. ATExecChangeOwner() emits a warning indices and then performs no
action (for backwards compatibility), rather than emitting an error as
ATSimplePermissions() would do.  It also doesn't check permission if
the old and new owner are the same.
A4. ATExecEnableDisableTrigger() and ATExecEnableDisableRule() call,
respectively, EnableDisableTrigger and EnableDisableRule, each of
which does a redundant permissions check.

I believe that everything else just calls ATSimplePermissions(),
though it's possible I've missed something.  It strikes me that if we
changed the signature for ATSimplePermissions, we could eliminate
A1-A3 (and A4 is trivial):

static void ATSimplePermissions(Relation rel, AlterTableCmd *cmd);

The plan would be to move the knowledge of which operations require
special treatment (allowing indices, system tables, etc.) into
ATSimplePermissions() and then just calling it unconditionally for ALL
object types.  ATSimplePermissionsRelationOrIndex() would go away.
ATExecChangeOwner() would require some refactoring, but I think it
would end up being simpler than it is now.  I also think it would be
more clear which checks are being applied to which object types.


I have a different plan to clean up these differences, especially A1 and A2.

The existing ATSimplePermissions() can be also divided into three parts,
as source code comments mentioned.
 Aa) It ensures the relation has an expected relkind
 Ab) It ensures ownership of the relation to be altered
 Ac) It ensures the relation is not system catalog, if not allowSystemTableMods.

If we provide these three checks in separated helper function, like:
 Aa) ATCheckRelkind(Relation rel, bool viewOK, bool indexOK)
 Ab) ATCheckOwnership(Relation rel)
 Ac) ATCheckNoCatalog(Relation rel)

The above A1 and A2 can be rewritten using combination of them, then we can
eliminate these functions to apply special treatments.
For example, the A2 can be replaced by ATCheckRelkind(rel, false, true) and
ATCheckOwnership(rel).

I think it allows to put a logic to decide whether we should apply permission
checks at the ATPrepCmd() stage, or not, in the existing swich ... case branch.

For some of exceptions, we can apply checks for them in the later stage
after the code gathered enough information to make access control decision.


Just to enumerate the part-B permissions checks, that is, permissions
checks on objects other than the table to which ALTER TABLE is being
directly applied, the ones I found were:

B1. ATAddForeignKeyConstrants() checks for REFERENCES permission on
the two tables involved.
B2. ATExecDropColumn() and ATExecDropConstraint() check for permission
to perform the drop on the child tables to which they decide to
recurse.
B3. ATExecAddInherit() checks permissions on the new parent.
B4. ATPrepSetTablespace() checks for permission to use the new tablespace.
B5. ATExecAddIndex() calls DefineIndex(), which also checks for rights
on the new namespace.

B2 and B3 are actually implemented at present using
ATSimplePermissions, and I think we could keep it that way under the
proposed signature change, with only minor refactoring.  The others
are basically all special cases, but there aren't actually that many
of them.

It may also be worth refactoring is ATAddCheckConstraint(), which
currently does its own recursion only so that the constraint name at
the top of the inheritance hierarchy propagates all the way down
unchanged.  I wonder if it would be cleaner/possible to work out the
constraint name earlier and then just use the standard recursion
mechanism.


Isn't it possible to check whether the g

Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Grzegorz Jaskiewicz]

think also how people use SQL word , when calling ms sql server. They would 
just say 'sql server' , and to some I had to explain that the little greedy 
company didn't actually invented sql, hence it should be called ms sql 
server... 
so, -1 for dropping SQL word from me. 

... and maybe the shed should be yellow, and with flat roof...


-- 
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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Pavel Stehule]

2010/1/23 Andrew Chernow :
> Tom Lane wrote:
>>
>> "David E. Wheeler"  writes:
>>>
>>> On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote:

 MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in
 their name? I think it is the opposite. SQL in the name almost grants
 legitimacy to them as products. Dropping the SQL has the potential to
 increase confusion. What is a Postgres? :-)
>>
>>> Something that comes after black, but before white.
>>
>> Yeah.  As best I can tell, most newbies think that PostgreSQL means
>> Postgre-SQL --- they're not too sure what "Postgre" is, but they guess
>> it must be the specific name of the product.  And that annoys those of
>> us who would rather they pronounced it "Postgres".  But in terms of
>> recognizability of the product it's not a liability.  The business about
>> pronunciation is a red herring.  It's just as unclear whether MySQL is
>
> My personal experience has shown that people not familiar with the project
> can't remember it's name (even 10 minutes after I said it).  It doesn't
> really roll off your tongue, unless you count tree nodes in your sleep.
>  This "may" have an affect on the project's reach.
>
> I am not really advocating a name change, but if a different name makes
> postgresql more popular, however silly that may seem, then I am all for it.
> This is a difficult marketing decision.

I am not sure so different name makes postgresql more popular - it is
marketing for short-live products. If I can speak some: for Czech
language - the pronunciation of PostgreSQL in Czech isn't any problem.
PostgreSQL is mark with very good reputation - and some pople will go
from Oracle or MySQL, I'll have a better job then to explain so
Postgres is PostgreSQL.

so for me -1

Pavel

>
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Andrew Chernow]

Tom Lane wrote:

"David E. Wheeler"  writes:

On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote:

MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in their 
name? I think it is the opposite. SQL in the name almost grants legitimacy to 
them as products. Dropping the SQL has the potential to increase confusion. 
What is a Postgres? :-)



Something that comes after black, but before white.


Yeah.  As best I can tell, most newbies think that PostgreSQL means
Postgre-SQL --- they're not too sure what "Postgre" is, but they guess
it must be the specific name of the product.  And that annoys those of
us who would rather they pronounced it "Postgres".  But in terms of
recognizability of the product it's not a liability.  The business about
pronunciation is a red herring.  It's just as unclear whether MySQL is


My personal experience has shown that people not familiar with the project can't 
remember it's name (even 10 minutes after I said it).  It doesn't really roll 
off your tongue, unless you count tree nodes in your sleep.  This "may" have an 
affect on the project's reach.


I am not really advocating a name change, but if a different name makes 
postgresql more popular, however silly that may seem, then I am all for it. 
This is a difficult marketing decision.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Tom Lane]

"David E. Wheeler"  writes:
> On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote:
>> MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in 
>> their name? I think it is the opposite. SQL in the name almost grants 
>> legitimacy to them as products. Dropping the SQL has the potential to 
>> increase confusion. What is a Postgres? :-)

> Something that comes after black, but before white.

Yeah.  As best I can tell, most newbies think that PostgreSQL means
Postgre-SQL --- they're not too sure what "Postgre" is, but they guess
it must be the specific name of the product.  And that annoys those of
us who would rather they pronounced it "Postgres".  But in terms of
recognizability of the product it's not a liability.  The business about
pronunciation is a red herring.  It's just as unclear whether MySQL is
to be pronounced my-se-quel or my-ess-cue-ell, but how many people have
you heard claiming that's a lousy name?

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] Improving the accuracy of estimate_num_groups()

[Tom Lane]

I was just poking at the test case provided by Allen Johnson in bug
#5294.  The essence of the complaint is that the planner is choosing
a sort-and-GroupAggregate plan when a HashAggregate-and-then-sort
plan would be faster, because the aggregation steps are roughly the
same speed either way while post-aggregation sorting is a lot faster
because it has fewer rows to process.  The reason the planner makes
the wrong choice is that it doesn't think the aggregation will reduce
the number of rows.  And that can be blamed on estimate_num_groups(),
which is a largely heuristic affair anyway but seems to fall down
particularly badly on the number of groups in a grouped join query.

In a join situation, what estimate_num_groups() does is mostly to
compute the product of its estimates of the number of groups in each
input relation.  Of course that's often a huge overestimate.  This is
masked to some extent by clamping the result to be at most the estimate
of the unaggregated join size, which is why we get exactly the same
pre-aggregation and post-aggregation rowcount estimates in Allen's
example.  But we need to do better if we're to have any hope of making
intelligent choices about this.

The only bit of intelligence estimate_num_groups() adds for join cases
is that it throws away any grouping variables that have been found to be
equal to other grouping variables; that is, given
select ... from ... where a.x = b.y group by a.x, b.y
the estimate will be the smaller of the number of x or y values
rather than their product.  However, that doesn't help in the least
for Allen's example, because only one of each pair of join keys
appears among the grouping columns.

For cases involving equated grouping columns in the same relation,
we don't use that heuristic anyway; what we do is compute the product of
the number of values and then reduce that by the estimated selectivity
of the available restriction clauses.  That seems to work reasonably
well, or at least better than what is happening at the join level.
So it strikes me that maybe we should delete the drop-equal-variables
heuristic altogether (basically, reduce add_unique_group_var() to just
lappend) and then multiply the ending number-of-groups estimate by the
selectivity of the join clauses.  In this way we take some account of
join clauses that aren't equating one grouping column to another,
whereas right now they're completely ignored.

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] Miscellaneous changes to plperl [PATCH]

[Alex Hunsaker]

On Thu, Jan 14, 2010 at 09:07, Tim Bunce  wrote:
> - Allow (ineffective) use of 'require' in plperl
>    If the required module is not already loaded then it dies.
>    So "use strict;" now works in plperl.

[ BTW I think this is awesome! ]

Id vote for use warnings; as well.

> - Stored procedure subs are now given names.
>    The names are not visible in ordinary use, but they make
>    tools like Devel::NYTProf and Devel::Cover _much_ more useful.

This needs to quote at least '.  Any others you can think of?  Also I
think we should sort the imports in ::mkfunsort so that they are
stable.

The cleanups were nice, the code worked as described.  Other than the
quoting issue it looks good to me.  Find below an incremental patch
that fixes the items above.

diff --git a/src/pl/plperl/plc_perlboot.pl b/src/pl/plperl/plc_perlboot.pl
index daef469..fa5df0a 100644
--- a/src/pl/plperl/plc_perlboot.pl
+++ b/src/pl/plperl/plc_perlboot.pl
@@ -27,16 +27,14 @@ sub ::mkfuncsrc {
my $BEGIN = join "\n", map {
my $names = $imports->{$_} || [];
"$_->import(qw(@$names));"
-   } keys %$imports;
+   } sort keys %$imports;
$BEGIN &&= "BEGIN { $BEGIN }";

$name =~ s/\\//g;
$name =~ s/::|'/_/g; # avoid package delimiters
+   $name =~ s/'/\'/g;

-   my $funcsrc;
-   $funcsrc .= qq[ undef *{'$name'}; *{'$name'} = sub { $BEGIN
$prolog $src } ];
-   #warn "plperl mkfuncsrc: $funcsrc\n";
-   return $funcsrc;
+   return qq[ undef *{'$name'}; *{'$name'} = sub { $BEGIN $prolog $src } ];
 }

 # see also mksafefunc() in plc_safe_ok.pl
diff --git a/src/pl/plperl/plc_safe_ok.pl b/src/pl/plperl/plc_safe_ok.pl
index 8d35357..79d64ce 100644
--- a/src/pl/plperl/plc_safe_ok.pl
+++ b/src/pl/plperl/plc_safe_ok.pl
@@ -25,6 +25,7 @@ $PLContainer->share(qw[&elog &return_next
 $PLContainer->permit(qw[caller]);
 ::safe_eval(q{
require strict;
+   require warnings;
require feature if $] >= 5.01;
1;
 }) or die $@;


plperl_misc_inc_ah.patch
Description: Binary data

-- 
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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[David E. Wheeler]

On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote:

> MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in 
> their name? I think it is the opposite. SQL in the name almost grants 
> legitimacy to them as products. Dropping the SQL has the potential to 
> increase confusion. What is a Postgres? :-)

Something that comes after black, but before white.

David
-- 
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] commit fests

[Robert Haas]

On Fri, Jan 22, 2010 at 7:50 PM, Tom Lane  wrote:
> Peter Eisentraut  writes:
>> On fre, 2010-01-22 at 18:05 -0500, Robert Haas wrote:
>>> Any ideas?
>
>> The lower bound on the release cycle is about 12 months right now
>> because we intend to support old versions for 5 years, and 5 or 6
>> branches at once is about the most anyone can handle.  That formula is
>> tough to change.
>
> Another problem is that it's very debatable whether users (as opposed
> to developers) want a fast release cycle.  Some of that reluctance to
> update might dissipate if we had a better upgrade-in-place story, but
> by no means all of it.  People don't want to have to retest their
> applications every six months.

I didn't mean to imply that we should try to release every 6 months,
if that's what it sounded like.  I think annual release cycles are
fine.  I don't like the idea of letting it slip to 15 or 18 months,
though.

> I agree with trying to cut down the submission-to-commit delay, but
> the release cycle length is not primarily determined by what patch
> authors would like ...

It seems to me that the CommitFest process is pretty darn effective at
reducing the submission-to-commit delay, except when you miss the last
one for the release - then it sucks hard.

I prefer annual release cycles as a user, not just as a developer.  If
I start a new project now, it'll be based on 8.4.2.  If 8.4 had never
happened and 8.3 were still the current release, any new project I
started would have to be based on 8.3.  Of course, I still have
several systems running 8.3 (and I think even 8.2) that are chugging
along just fine; they lose nothing from 8.4 having been released.

...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] commit fests

[Andrew Dunstan]

Tom Lane wrote:

Another problem is that it's very debatable whether users (as opposed
to developers) want a fast release cycle.  Some of that reluctance to
update might dissipate if we had a better upgrade-in-place story, but
by no means all of it.  People don't want to have to retest their
applications every six months.


  


Or even more than once every few years. This is a consideration that is 
often ignored in our discussions, unfortunately.


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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Mark Mielke]

On 01/22/2010 10:57 AM, Aidan Van Dyk wrote:

* Brendan Jurd  [100122 10:29]:

   

Holy query language, Batman!

Do you mean to tell me that the "uninformed masses" you interact with
have an understanding of what "SQL" means?

I am skeptical of this claim, but if true, you must have access to the
most spectacularly informed "uninformed masses" on the planet.
 

I can't speak for Mark, but the "uniformed masses" I interact with tend
to be the guys looking for (and authorizing) solutions in small-medium
business segment...  And Microsoft has done the "education" for us and
automatically associated this unknown "SQL"  with "a big database"...
So despite that they have no idea what "SQL" actually means, or where it
came from, it's got the desired association.

So, my neck of the woods ain't necessarily yours, but...
   


Exactly. People know where SQL fits in the product map. They probably do 
NOT know what it stands for, but they don't really care. They pay 
professional technical people to understand the details.


How many people know what SONAR, RADAR, or SCUBA stand for? This doesn't 
seem to stop them from being able to use the word effectively.


MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in 
their name? I think it is the opposite. SQL in the name almost grants 
legitimacy to them as products. Dropping the SQL has the potential to 
increase confusion. What is a Postgres? :-)


Cheers,
mark


--
Mark Mielke


--
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] commit fests

[Tom Lane]

Peter Eisentraut  writes:
> On fre, 2010-01-22 at 18:05 -0500, Robert Haas wrote:
>> Any ideas?

> The lower bound on the release cycle is about 12 months right now
> because we intend to support old versions for 5 years, and 5 or 6
> branches at once is about the most anyone can handle.  That formula is
> tough to change.

Another problem is that it's very debatable whether users (as opposed
to developers) want a fast release cycle.  Some of that reluctance to
update might dissipate if we had a better upgrade-in-place story, but
by no means all of it.  People don't want to have to retest their
applications every six months.

I agree with trying to cut down the submission-to-commit delay, but
the release cycle length is not primarily determined by what patch
authors would like ...

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] commit fests

[Peter Eisentraut]

On fre, 2010-01-22 at 18:05 -0500, Robert Haas wrote:
> and start talking about
> how we can create an environment where patch authors can get their
> work committed reasonably quickly (assuming it's good, of course) and
> released within some reasonable time frame after that (like, say,
> within a year from commit) - because I think those things are
> important to the health of the project, and even though FWIH things
> are much better than in pre-CommitFest days, I still think there's
> quite a bit of room for improvement.
> 
> Any ideas?

The lower bound on the release cycle is about 12 months right now
because we intend to support old versions for 5 years, and 5 or 6
branches at once is about the most anyone can handle.  That formula is
tough to change.


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

[HACKERS] Sugerencia de opcion

[Informatica-Cooperativa Cnel. Oviedo]

Buenos Dias todos, 

                            Soy un usuario de postgres de Paraguay,
consulto sobre la posibilidad de inclucion en la futura version la
siguiente sentencia(Uso de alias en la condicion HAVING ):


    SELECT id, sum(salario) as SumaSalario
    FROM salarios
    GROUP BY id
    HAVING SumaSalario>500;


 Saludos,



Edgar Villalba. (edgvill)
Paraguay
   

Re: [HACKERS] commit fests

[Robert Haas]

On Fri, Jan 22, 2010 at 5:15 PM, Dimitri Fontaine
 wrote:
> Robert Haas  writes:
>> I think feature freeze should be the beginning of the last CommitFest,
>> not the end.
>
> So you still want 3 CF per cycle rather than 4?
>  http://archives.postgresql.org/pgsql-hackers/2009-12/msg02355.php
>
> 3 CF and a FixFest… -1 from me, if there's an open vote to be made here.

What I really want is for people to be able to get their patches and
committed in a reasonably timely fashion.  That means I'd like
releases to be reasonably frequent - like annually - and I'd like the
time for which nobody can get anything committed to be relatively
short.  Between the start of the last CommitFest for 8.4 and the first
CommitFest for 8.5, 8 months went by.  That is a darn long time, and I
think it's hurting the project.  It's certainly annoying me, if that
counts for anything.

It appeared to me that Hot Standby, Streaming Replication, and
SE-PostgreSQL basically made no progress (or negative progress, in the
case of the third one) during that time.  While I don't want to
venture too far into the realm of speculation, I believe that this may
be partly because (1) there was no chance they'd get committed and (2)
nobody was reviewing them and providing feedback.  And I think there
are a lot of other people who just didn't really start to get serious
about finishing their patches until after they got some feedback from
the July CommitFest - a lot of what got marked RWF in July got
committed in September.  I think those people were totally right to
blow off trying to get anything done from whenever they wrote the
patch until July, but I also think that it stinks that we ask people
to work that way.

And then there's the actual release schedule.  Let's think about what
will happen if 9.0 isn't released until September.  First of all,
patches that I wrote in February or March of 2009 will be show up in a
released version 18 months later.  That is quite a long time.
Secondly, if the 9.1 cycle turns out to be the same length as the 9.0
cycle, then 9.1 will be released in November or December of 2011,
which means that any patches I write now will wait almost 2 years to
make a released version.  That is a REALLY long time, and I'm
skeptical that releasing around the holidays is going to be a success
anyhow.  Admittedly, this is all speculative - and just for the
record, if we're able to put out a release in early July as we did for
8.4, I'll be quite happy.

I understand that the majority of the community (or at least a
majority of the vocal community) is not in favor of the relatively
rigid time-based releases for which I am advocating.  But I don't
think I am alone in the above-stated frustrations, either.  What I'd
really like is to stop arguing about the number of CommitFests per
cycle and the exact charter of each CommitFest and start talking about
how we can create an environment where patch authors can get their
work committed reasonably quickly (assuming it's good, of course) and
released within some reasonable time frame after that (like, say,
within a year from commit) - because I think those things are
important to the health of the project, and even though FWIH things
are much better than in pre-CommitFest days, I still think there's
quite a bit of room for improvement.

Any ideas?

...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] Largeobject Access Controls (r2460)

[Kevin Grittner]

"Kevin Grittner"  wrote:
> I'll get started.
 
After a couple false starts, the creation of the millions of tables
is underway.  At the rate it's going, it won't finish for 8.2 hours,
so I'll have to come in and test the dump tomorrow morning.
 
-Kevin

-- 
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] commit fests

[Dimitri Fontaine]

Robert Haas  writes:
> I think feature freeze should be the beginning of the last CommitFest,
> not the end.

So you still want 3 CF per cycle rather than 4?
  http://archives.postgresql.org/pgsql-hackers/2009-12/msg02355.php

3 CF and a FixFest… -1 from me, if there's an open vote to be made here.

Regards,
-- 
dim

-- 
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_listener entries deleted under heavy NOTIFY load only on Windows

[Radu Ilie]

On a Windows server under heavy load of NOTIFY events, entries in
pg_listener table for some events are deleted. It is like UNLISTEN was
called.

PostgreSQL version: 8.3.9.
Operating System: Windows XP.

PostgreSQL believes that if it fails to notify a listener (by signaling
the respective backend), then the backend doesn't exist anymore and so
it should get rid of the pg_listener entry. The relevant code is in
src/backend/commands/async.c, function Send_Notify:

if (kill(listenerPID, SIGUSR2) < 0)
{
/*
* Get rid of pg_listener entry if it refers to a PID that no
* longer exists.  Presumably, that backend crashed without
* deleting its pg_listener entries. This code used to only
* delete the entry if errno==ESRCH, but as far as I can see
* we should just do it for any failure (certainly at least
* for EPERM too...)
*/
simple_heap_delete(lRel, &lTuple->t_self);
}

The problem is that under Windows, kill can fail even if the process is
still alive. PostgreSQL uses named pipes under Windows to send signals
to backends. The present implementation has a bug that causes a client
to fail to write data to the named pipe, even though the server process
is alive. This is because the server doesn't maintain the named pipe at
all times. The bug is present in file src/backend/port/win32/signal.c,
function pg_signal_thread.

The server code stays in a loop in which it continuously creates an
instance of the named pipe (via CreateNamedPipe) and waits for a client
process to connect (via ConnectNamedPipe). Once a client connects, the
communication with the client is handled in a new thread, with the
thread procedure pg_signal_dispatch_thread. This function is very
simple: it reads one byte from the named pipe, it writes it back and
(very important) closes the handle to the named pipe instance. The main
loop creates another instance of the named pipe and waits for another
client to connect.

Now imagine that the server is under heavy load. There are dozens of
backends and threads running and the CPU usage is close to 100%. The
following succession of events is possible:

1. Server signal handling thread (in function pg_signal_thread) creates
the first, one and only instance of the named pipe via CreateNamedPipe.
2. Server code starts waiting for clients to connect with
ConnectNamedPipe.
3. Client wishes to make a transaction on the named pipe and calls
CallNamedPipe (in file src/port/kill.c, function pgkill).
4. Server code returns from ConnectNamedPipe. It creates a new thread
with the thread procedure pg_signal_dispatch_thread.
5. The signal dispatch thread is scheduled for execution and it runs to
completion. As you can see, the last thing it does related to the named
pipe is to close the handle via CloseHandle (in function
pg_signal_dispatch_thread). This closes the last instance of the named
pipe. The named pipe is gone. There is no more named pipe. The signal
handling thread was not yet scheduled by the operating system for
execution and thus didn't have an opportunity to call CreateNamedPipe.
6. Another client (or the same one, it doesn't matter) tries to write to
the named pipe via CallNamedPipe. The call returns ERROR_FILE_NOT_FOUND,
because the named pipe is gone. The client believes the backend is gone
and it removes the entry from pg_listener.
7. The signal handling thread (in function pg_signal_thread) is finally
scheduled for execution and it calls CreateNamedPipe. We now have an
instance of the named pipe available.

So we end up with the server backend alive, the named pipe is there, but
the row is gone from pg_listener. This is easy to reproduce under
Windows. I used the scripts posted by Steve Marshall in a similar thread
from 01/15/2009 and the problem appears within one minute all the time.
For testing I used a Windows XP machine with 2 cores and 2GB of RAM. The
CPU usage was over 70% during the trials.

The solution is to create a new instance of the named pipe before
launching the signal dispatch thread. This means changing the code in
src/backend/port/win32/signal.c to look like this:

@@ -250,6 +250,7 @@
{
charpipename[128];
HANDLE  pipe = pgwin32_initial_signal_pipe;
+   HANDLE  new_pipe = pgwin32_initial_signal_pipe;

snprintf(pipename, sizeof(pipename), ".\\pipe\\pgsignal_%u",
GetCurrentProcessId());

@@ -275,6 +276,10 @@
fConnected = ConnectNamedPipe(pipe, NULL) ? TRUE :
(GetLastError() == ERROR_PIPE_CONNECTED);
if (fConnected)
{
+   new_pipe = CreateNamedPipe(pipename,
PIPE_ACCESS_DUPLEX,
+  PIPE_TYPE_MESSAGE |
PIPE_READMODE_MESSAGE | PIPE_WAIT,
+
PIPE_UNLIMITED_INSTANCES, 16, 16, 1000, NULL);
+
hThread = CreateThread(NULL, 0,

(LPTHREAD_START_ROUTINE) pg_signal_dispatch_thread,

(LPVOID) pipe, 0, NULL);
@@ -288,8 +293,7 @@
/* Connection failed. Cleanup and try again */

Re: [HACKERS] Largeobject Access Controls (r2460)

[Kevin Grittner]

Tom Lane  wrote:
 
> Empty is fine.
 
I'll get started.
 
-Kevin

-- 
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] Largeobject Access Controls (r2460)

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> Do you have the opportunity to try an experiment on hardware
>> similar to what you're running that on?  Create a database with 7
>> million tables and see what the dump/restore times are like, and
>> whether pg_dump/pg_restore appear to be CPU-bound or
>> memory-limited when doing it.
 
> If these can be empty (or nearly empty) tables, I can probably swing
> it as a background task.  You didn't need to match the current 1.3
> TB database size I assume?

Empty is fine.

>> If they aren't, we could conclude that millions of TOC entries
>> isn't a problem.
 
> I'd actually be rather more concerned about the effects on normal
> query plan times, or are you confident that won't be an issue?

This is only a question of what happens internally in pg_dump and
pg_restore --- I'm not suggesting we change anything on the database
side.

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] Largeobject Access Controls (r2460)

[Kevin Grittner]

Tom Lane  wrote:
 
> Do you have the opportunity to try an experiment on hardware
> similar to what you're running that on?  Create a database with 7
> million tables and see what the dump/restore times are like, and
> whether pg_dump/pg_restore appear to be CPU-bound or
> memory-limited when doing it.
 
If these can be empty (or nearly empty) tables, I can probably swing
it as a background task.  You didn't need to match the current 1.3
TB database size I assume?
 
> If they aren't, we could conclude that millions of TOC entries
> isn't a problem.
 
I'd actually be rather more concerned about the effects on normal
query plan times, or are you confident that won't be an issue?
 
> A compromise we could consider is some sort of sub-TOC-entry
> scheme that gets the per-BLOB entries out of the main speed
> bottlenecks, while still letting us share most of the logic.  For
> instance, I suspect that the first bottleneck in pg_dump would be
> the dependency sorting, but we don't really need to sort all the
> blobs individually for that.
 
That might also address the plan time issue, if it actually exists.
 
-Kevin

-- 
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] Largeobject Access Controls (r2460)

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> We've heard of people with many tens of thousands of
>> tables, and pg_dump speed didn't seem to be a huge bottleneck for
>> them (at least not in recent versions).  So I'm feeling we should
>> not dismiss the idea of one TOC entry per blob.
>> 
>> Thoughts?

> I suspect that 7 million BLOBs (and growing fast) would be a problem
> for this approach.  Of course, if we're atypical, we could stay with
> bytea if this changed.  Just a data point.

Do you have the opportunity to try an experiment on hardware similar to
what you're running that on?  Create a database with 7 million tables
and see what the dump/restore times are like, and whether
pg_dump/pg_restore appear to be CPU-bound or memory-limited when doing
it.  If they aren't, we could conclude that millions of TOC entries
isn't a problem.

A compromise we could consider is some sort of sub-TOC-entry scheme that
gets the per-BLOB entries out of the main speed bottlenecks, while still
letting us share most of the logic.  For instance, I suspect that the
first bottleneck in pg_dump would be the dependency sorting, but we
don't really need to sort all the blobs individually for that.

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] Largeobject Access Controls (r2460)

[Kevin Grittner]

Tom Lane  wrote:
 
> Now the argument against that is that it won't scale terribly well
> to situations with very large numbers of blobs.  However, I'm not
> convinced that the current approach of cramming them all into one
> TOC entry scales so well either.  If your large objects are
> actually large, there's not going to be an enormous number of
> them.  We've heard of people with many tens of thousands of
> tables, and pg_dump speed didn't seem to be a huge bottleneck for
> them (at least not in recent versions).  So I'm feeling we should
> not dismiss the idea of one TOC entry per blob.
> 
> Thoughts?
 
We've got a "DocImage" table with about 7 million rows storing PDF
documents in a bytea column, approaching 1 TB of data.  (We don't
want to give up ACID guarantees, replication, etc. by storing them
on the file system with filenames in the database.)  This works
pretty well, except that client software occasionally has a tendency
to run out of RAM.  The interface could arguably be cleaner if we
used BLOBs, but the security issues have precluded that in
PostgreSQL.
 
I suspect that 7 million BLOBs (and growing fast) would be a problem
for this approach.  Of course, if we're atypical, we could stay with
bytea if this changed.  Just a data point.
 
-Kevin
 
cir=> select count(*) from "DocImage";
  count
-
 6891626
(1 row)

cir=> select pg_size_pretty(pg_total_relation_size('"DocImage"'));
 pg_size_pretty

 956 GB
(1 row)


-- 
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] Largeobject Access Controls (r2460)

[Tom Lane]

KaiGai Kohei  writes:
> The attached patch is a revised version.

I'm inclined to wonder whether this patch doesn't prove that we've
reached the end of the line for the current representation of blobs
in pg_dump archives.  The alternative that I'm thinking about is to
treat each blob as an independent object (hence, with its own TOC
entry).  If we did that, then the standard pg_dump mechanisms for
ownership, ACLs, and comments would apply, and we could get rid of
the messy hacks that this patch is just adding to.  That would also
open the door to future improvements like being able to selectively
restore blobs.  (Actually you could do it immediately if you didn't
mind editing a -l file...)  And it would for instance allow loading
of blobs to be parallelized.

Now the argument against that is that it won't scale terribly well
to situations with very large numbers of blobs.  However, I'm not
convinced that the current approach of cramming them all into one
TOC entry scales so well either.  If your large objects are actually
large, there's not going to be an enormous number of them.  We've
heard of people with many tens of thousands of tables, and pg_dump
speed didn't seem to be a huge bottleneck for them (at least not
in recent versions).  So I'm feeling we should not dismiss the
idea of one TOC entry per blob.

Thoughts?

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] review: More frame options in window functions

[Robert Haas]

On Tue, Jan 19, 2010 at 3:02 PM, Hitoshi Harada  wrote:
> 2010/1/19 Hitoshi Harada :
>> Yeah, that's my point, too. The planner has to distinguish "four" from
>> sort pathkeys and to teach the executor the simple information which
>> column should be used to determine frame. I was bit wrong because some
>> of current executor code isn't like it, like using ordNumCols == 0 to
>> know whether partition equals to frame, though
>
> And here's another version to fix this problem (I hope). Now the
> planner distinguish sort column from actual significant pathkeys. I
> tested it on both of 32bit and 64bit Linux.

Would it make sense to pull some of the infrastructure bits out of
this patch and commit those bits separately, so as to reduce the size
of the main patch?  In particular, the AggGetMemoryContext() stuff
looks like a good candidate for that treatment.

Why did you change BETWEEN from a TYPE_FUNC_NAME_KEYWORD to a COL_NAME_KEYWORD?

...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] Git out of sync vs. CVS

[Tom Lane]

"Kevin Grittner"  writes:
> So add me to the list of people who think that if
> these are going to be recurring, we should look at moving from cvs
> to git as soon as 9.0 is released.

The gating factor is not release schedule; it is the still-unaddressed
tasks that must be done before we can consider moving.
http://wiki.postgresql.org/wiki/Switching_PostgreSQL_from_CVS_to_Git

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] quoting psql varible as identifier

[Robert Haas]

On Fri, Jan 22, 2010 at 7:19 AM, Pavel Stehule  wrote:
> here is new variant. Add scan_state flag "valid" and enhance
> protection against execution broken statement.

This doesn't make sense to me.  You've changed the way \set is handled
- in a way that doesn't seem particularly appropriate to me - but most
of the other backslash commands are unmodified - but then there's this
hack at the bottom that overrides the return value if
psql_scan_is_valid() fails.  And then there's this:

-   /* we need not do psql_scan_reset() here */
+   psql_scan_reset(scan_state);

It's extremely unclear what the rationale for this change is.

Basically, you need to either improve the comments in here so that
someone can understand what is going on, or you need to submit it with
some detailed documentation explaining the rationale behind each
change and why it is right, or more than likely both.  But I think the
whole approach is likely off-base and you need to go back and think
about a cleaner way to get this done.

...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] Review: listagg aggregate

[David E . Wheeler]

Pavel,

My review of your listagg patch.

Submission Review
-
* The diff is a context diff and applies cleanly to HEAD (with just two hunks 
offset by 2 lines each).

* There is documentation, though I'm not sure it needs to be mentioned in the 
string functions documentation. No harm in it, I guess.

  I would like to see an example, though, and the documentation does not 
currently explain what each of the parameters are for. In fact, it looks like 
all the existing aggregates take only one parameter, so there was not 
previously a need to explain it. But listagg() has an optional second param. I 
think that the description should explain what it's for.

* There are tests and they look fine.

Usability Review

* The patch does in fact implement the aggregate function it describes, and OH 
YES do we want it (I've written my own in SQL a few times).

* No, we don't already have it.

* Yes it follows community-agreed behavior. I'm assuming that there is no 
special parsing of aggregate functions, so the simple use of commas to separate 
the two parameters is appropriate, rather than using a keyword like MySQL's 
SEPARATOR in the group_concat() aggregate.

* No need to have pg_dump support, no dangers that I can see, looks like all 
the bases have been covered.

Feature Test

* Everything built cleanly, but I got an OID dupe error when I tried to init 
the DB. Looks like 2997 and 2998 have been used for something else since you 
created the patch. I changed them to 2995 and 2996 and then it worked.
* The feature appears to work. I didn't see any tests for encodings or other 
data types, so I ran a few myself and they work fine:

postgres=# select listagg(a, U&'-\0441\043B\043E\043D-') from 
(values(''),(''),(''
 listagg  
--
 -слон--слон-
(1 row)

postgres=# select listagg(a, U&'\2014') from 
(values(U&'\0441\043B\043E\043D'),(U&'d\0061t\+61'),(U&'\0441\043B\043E\043D'))
 AS g(a);
listagg 

 слон—data—слон
(1 row)


postgres=# select listagg(a::text) from (values(1),(2),(3)) AS g(a);
 listagg 
-
 123
(1 row)


Performance Review
--

No performance issues, except that it should be faster than a custom aggregate 
that does the same thing. To test, I created a quick custom aggregate (no 
second argument, alas, so listagg() is more flexible) like so:

CREATE OR REPLACE FUNCTION a2s(ANYARRAY)
RETURNS TEXT LANGUAGE SQL AS $$
SELECT array_to_string($1, ',');
$$;

   CREATE AGGREGATE string_accum (
SFUNC= array_accum,
BASETYPE = ANYELEMENT,
STYPE= ANYARRAY,
INITCOND = '{}',
FINALFUNC = a2s 
);

Then I ran some simple tests (thanks for the clue, depesz):

postgres=# select count(*) from (select string_accum(a) from 
(values(''),(''),('')) AS g(a), generate_series(1,1) i) AS x(i);
 count 
---
 1
(1 row)

Time: 1365.382 ms

postgres=# select count(*) from (select listagg(a) from 
(values(''),(''),('')) AS g(a), generate_series(1,1) i) AS x(i);
 count 
---
 1
(1 row)

Time: 17.989 ms

So overall, it looks like listagg() is 1-2 orders of magnitude faster. YMMV, 
and my system is built with --enable-cassert and --enable-debug. Still, good 
job.

Coding Review
-

* Is varchar.c really the best place to put the ListAggState struct and the 
listagg() function? I grepped the source for array_agg() and it's in 
src/backend/utils/adt/array_userfuncs.c. Maybe there's an equivalent file for 
string functions? Otherwise, the style of the C code looks fine to my untrained 
eye.

  Actually, shouldn't it return text rather than varchar?

* Does it really require four functions to do its work? Might there be some way 
to use the array_agg() C functions and then just a different final function to 
turn it into a string (using the internal array_to_string() function, perhaps)? 
I'm not at all sure about it, but given how little code was required to create 
the same basic functionality in SQL, I'm surprised that the C implementation 
requires four functions (accumStringResult(), listagg1_transfn(), 
listagg2_transfn(), and listagg_finalfn()). Maybe they're required to make it 
fast and avoid the overhead of an array?

* No compiler warnings, I never made it crash, good comments, does what it says 
on the tin. I doubt that there are any portability issues, as the code seems to 
use standard PostgreSQL internal macros and functions.

Architecture Review
---

* No dependencies, things seem to make sense overall, notwithstanding my 
questions in the Coding Review.

Review Review
-

The only thing I haven't covered so far is the name. I agree with Tom's 
assertion that the name is awful. Sure there may be a precedent in Oracle, but 
I hardly find that convincing (some of the big corporations seem to do a really 
shitty job nami

Re: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Tom Lane]

Leonardo F  writes:
> Would it make sense to use
> FormIndexDatum
> to get the index value to be used by tuplesort?

That would probably work.  You might want to look at the code associated
with the recently-added exclusion constraint feature; that also has a
need to reproduce index entries sometimes.

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] attoptions

[Robert Haas]

On Thu, Jan 21, 2010 at 10:24 AM, Alex Hunsaker  wrote:
> On Thu, Jan 21, 2010 at 07:30, Robert Haas  wrote:
>> On Thu, Jan 21, 2010 at 12:57 AM, Alex Hunsaker  wrote:
>>> Seems to me a comment about the above might be nice.  Something like
>>> /* Things after here are should always be default null */ in
>>> pg_attribute.h ?
>>
>> Well... that wouldn't actually be a correct summary, so no.  The point
>> is that variable-length fields are not used in tuple descriptors.
>> I'll add a comment about that.
>
> Yes that sounds much better, I was struggling to find the words.  What
> I was trying to express was something along the lines of you cant have
> a non null value after these because you cant statically initialize
> them in genbki.

Committed.   Thanks for the very thorough review.

...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] About "Our CLUSTER implementation is pessimal" patch

[Leonardo F]

> Note you should be looking at pg_am.amcanorder, not
> hardwiring knowledge of particular index types.


Ok. 

Would it make sense to use

FormIndexDatum

to get the index value to be used by tuplesort?

I'm having trouble avoiding the call to _bt_mkscankey_nodata
to get the scanKeys... that relates to btree only, I can't find 
the "general" function...




-- 
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] [COMMITTERS] pgsql: Replace ALTER TABLE ...

[Robert Haas]

2010/1/22 Tom Lane :
> Robert Haas  writes:
>> 2010/1/22 Devrim GÜNDÜZ :
>>> I think this broke doc builds:
>
>> Dang, how'd that happen?  I could have sworn I tested this.
>
> Some versions of docbook are pickier than others --- maybe yours
> didn't complain?

It's complaining now, so I don't think that's it.  I think maybe I
just got confused because I rebuilt the docs for my libpq patch about
10 times yesterday, and I ASSUMED that because I hadn't changed the
docs for this patch in a long time I didn't need to retest.  But maybe
I never did test them after all.  I spent the whole morning making
sure it didn't break with -DCLOBBER_CACHE_ALWAYS=1.  One of these days
I'll manage to do one of these that doesn't require 2 or 3 follow-on
commits...

...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] About "Our CLUSTER implementation is pessimal" patch

[Tom Lane]

Leonardo F  writes:
> gist -> how can I get something "comparable" by tuplesort? Or should I rule it
>out from the seq scan + sort path?

Rule it out.  Note you should be looking at pg_am.amcanorder, not
hardwiring knowledge of particular index types.

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] [COMMITTERS] pgsql: Replace ALTER TABLE ...

[Tom Lane]

Robert Haas  writes:
> 2010/1/22 Devrim GÜNDÜZ :
>> I think this broke doc builds:

> Dang, how'd that happen?  I could have sworn I tested this.

Some versions of docbook are pickier than others --- maybe yours
didn't complain?

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] [COMMITTERS] pgsql: Replace ALTER TABLE ...

[Robert Haas]

2010/1/22 Devrim GÜNDÜZ :
> On Fri, 2010-01-22 at 16:40 +, Robert Haas wrote:
>> Log Message:
>> ---
>> Replace ALTER TABLE ... SET STATISTICS DISTINCT with a more general
>> mechanism.
>
> I think this broke doc builds:

Dang, how'd that happen?  I could have sworn I tested this.

Will fix, sorry.

...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] [COMMITTERS] pgsql: Replace ALTER TABLE ...

[Devrim GÜNDÜZ]

On Fri, 2010-01-22 at 16:40 +, Robert Haas wrote:
> Log Message:
> ---
> Replace ALTER TABLE ... SET STATISTICS DISTINCT with a more general
> mechanism. 

I think this broke doc builds:

openjade  -wall -wno-unused-param -wno-empty -wfully-tagged -D . -D . -c 
/usr/share/sgml/docbook/dsssl-stylesheets/catalog -d stylesheet.dsl -t sgml -i 
output-html -V html-index postgres.sgml
openjade:ref/alter_table.sgml:162:156:E: end tag for "LITERAL" omitted, but 
OMITTAG NO was specified
openjade:ref/alter_table.sgml:162:10: start tag was here
openjade:ref/alter_table.sgml:162:166:E: end tag for element "LITERAL" which is 
not open
openjade:ref/alter_table.sgml:164:13:E: document type does not allow element 
"LISTITEM" here
openjade:ref/alter_table.sgml:191:17:E: end tag for "TERM" omitted, but OMITTAG 
NO was specified
openjade:ref/alter_table.sgml:163:4: start tag was here
openjade:ref/alter_table.sgml:191:17:E: end tag for "VARLISTENTRY" which is not 
finished
gmake: *** [HTML.index] Error 1
gmake: *** Deleting file `HTML.index'

-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] ECPG patch 4.1, out-of-scope cursor support in native mode

[Boszormenyi Zoltan]

Michael Meskes írta:
>> diff -dcrpN 
>> pgsql.orig/src/interfaces/ecpg/test/expected/compat_informix-struct.c 
>> pgsql.4.1/src/interfaces/ecpg/test/expected/compat_informix-struct.c
>> ...
>> +/* Test DECLARE ... SELECT ... INTO with struct type */
>> + 
>> +ECPGset_var( 0, &( myvar ), __LINE__);\
>> +  ECPGset_var( 1, &( mynullvar ), __LINE__);\
>> +  ECPG_informix_reset_sqlca(); /* declare mycur cursor for select * from a1 
>> */
>> + #line 45 "struct.pgc"
>> + 
>> +{ ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_normal, "declare mycur cursor 
>> for select * from a1", ECPGt_EOIT, 
>> +ECPGt_int,&(myvar.id),(long)1,(long)1,sizeof(int), 
>> ...
>> 
>
> Why does the preproc spit out ECPGset_var's but no ECPGget_var's in this test 
> case? 
>   

Because there's no ECPGget_var()s emitted for
- global variables
- variables in the same function

ECPGget_var() is only used in case the cursor declaration
used INTO/USING and it's in a different function from
the one where OPEN/FETCH/CLOSE reside. But this
cannot be determined easily, e.g. short of making ECPG
a two-pass precompiler, so ECPGset_var() is always used.

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
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] Access to dynamic SQL in PL/pgSQL

[Tom Lane]

Simon Riggs  writes:
> So a "dynamic query hook". When called, the hook would give access to
> the query text actually executed. Existing plugins wouldn't know about
> the hook, so would never call it, so the language run time would bypass
> that hook altogether. Newer versions of plugins would be able to decide
> whether to add-in code for the new hook based upon the version number.
> Neat solution, nothing against it at all. 

> Would such a solution be backpatchable? I wasn't sure what your last
> sentence meant.

It doesn't seem backpatchable to me, because there is no mechanism
beyond PG_MODULE_MAGIC that would ensure that plpgsql.so and the plugin
have the same idea about the contents of struct PLpgSQL_plugin.  In
hindsight it might've been a good idea if that struct contained a
version number, so if we're gonna change it I'd vote for adding one.

What my previous comment was meant to say was that this approach doesn't
seem practically different from the other as far as backwards
compatibility goes.  To wit: I don't think we can back-patch it in the
community sources, but there is nothing stopping you from back-patching
in a custom release where you have some confidence that compatible
versions of plpgsql.so and plugin will be used together.

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] psql tab completion recently broken?

[Kevin Grittner]

"Kevin Grittner"  wrote:
> I just updated my source code and it no longer seems to work to type
> the following, and I'm pretty sure I was using it with a checkout
> from less than 24 hours ago:
 
Never mind.  My mistake.
 
-Kevin

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

[HACKERS] psql tab completion recently broken?

[Kevin Grittner]

I just updated my source code and it no longer seems to work to type
the following, and I'm pretty sure I was using it with a checkout
from less than 24 hours ago:
 
select * from pg_lo[Tab]

-Kevin

-- 
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] Access to dynamic SQL in PL/pgSQL

[Simon Riggs]

On Fri, 2010-01-22 at 11:33 -0500, Tom Lane wrote:
> Simon Riggs  writes:
> > Anyway, there already was another way of doing this, so it shall be done
> > that way instead. The beauty of a pluggable architecture is that one
> > does not need approval to implement customer solutions.
> 
> If you're talking about a bundle that you're shipping to customers,
> of course you can do whatever you want, since you can ensure that
> your plpgsql.so and your plugin are compatible.  

> Stuff we release
> into the wide world doesn't have that luxury.  We can't assume much
> more than what PG_MODULE_MAGIC will enforce for us, and that means
> ABI breaks within a major release series are dangerous.

Understood

> But to get back to the point, what have you got against adding
> another plugin call within the statements that build dynamic
> queries?  It seems like a much cleaner solution to me, and not
> any different from the standpoint of back-portability.

So a "dynamic query hook". When called, the hook would give access to
the query text actually executed. Existing plugins wouldn't know about
the hook, so would never call it, so the language run time would bypass
that hook altogether. Newer versions of plugins would be able to decide
whether to add-in code for the new hook based upon the version number.
Neat solution, nothing against it at all. 

Would such a solution be backpatchable? I wasn't sure what your last
sentence meant.

-- 
 Simon Riggs   www.2ndQuadrant.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] restructuring "alter table" privilege checks (was: remove redundant ownership checks)

[Tom Lane]

Robert Haas  writes:
>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
 I don't particularly like this patch, mainly because I disagree with
 randomly removing permissions checks without any sort of plan about
 where they ought to go.

> [ a plan for rearranging ALTER TABLE's checks ]

Works for me.  All I asked for was plan first, code second, and you've
satisfied the precondition.

(I would venture that changing this stuff is probably 9.1 material at
this point, though.)

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] About "Our CLUSTER implementation is pessimal" patch

[Leonardo F]

So, if I'm not mistaken:


hash indexes -> can't be used in CLUSTER
gin indexes -> can't be used in CLUSTER

that leaves:

btree -> ok
expression btree -> I have to find a way to compute the expression for
  each tuple: hints?
gist -> how can I get something "comparable" by tuplesort? Or should I rule it
   out from the seq scan + sort path?



Leonardo




-- 
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] warn in plperl logs as... NOTICE??

[Andrew Dunstan]

Alexey Klyukin wrote:

On Jan 22, 2010, at 4:38 PM, Robert Haas wrote:

  

On Fri, Jan 22, 2010 at 9:13 AM, Alexey Klyukin  wrote:


I think elog(WARNING) is less surprising for the end-user, unless there's an 
objection strong enough to include it into the documentation :)
  

I think the main possible objection would what Simon just wrote on the
other thread - that it's been this way for a while, and while someone
might think that a different decision about how to handle it would
have been better, there may be people counting on the current behavior
who will have to spend time and perhaps money making changes if we
change it.



Well, then we have to choose between a fixed number of unhappy users in the past and potentially increasing number of unhappy users in the future (if we admit the fact that this behavior is illogical).  IMO if something behaves counterintuitively to most users the behavior should be at least documented, if not fixed.  



  


Well, as Tim Bunce pointed out, if we get his on_init patch users would 
be able to choose which behaviour they wanted. So we don't necessarily 
have to choose between what people think conforms to POLA and backwards 
compatibility.


Right now I'm a bit hung on that patch because of the "lost GUC 
placeholder" issue mentioned elsewhere - everything I have thought of so 
far that might overcome it has been unspeakably ugly :-(


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] Access to dynamic SQL in PL/pgSQL

[Tom Lane]

Simon Riggs  writes:
> Anyway, there already was another way of doing this, so it shall be done
> that way instead. The beauty of a pluggable architecture is that one
> does not need approval to implement customer solutions.

If you're talking about a bundle that you're shipping to customers,
of course you can do whatever you want, since you can ensure that
your plpgsql.so and your plugin are compatible.  Stuff we release
into the wide world doesn't have that luxury.  We can't assume much
more than what PG_MODULE_MAGIC will enforce for us, and that means
ABI breaks within a major release series are dangerous.

But to get back to the point, what have you got against adding
another plugin call within the statements that build dynamic
queries?  It seems like a much cleaner solution to me, and not
any different from the standpoint of back-portability.

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: commit fests (was Re: [HACKERS] primary key error message)

[Robert Haas]

On Fri, Jan 22, 2010 at 11:19 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> I'm not sure whether you're stating a position that's been agreed to
>> by -core or some other group, or just expressing your own opinion, but
>> I think feature freeze should be the beginning of the last CommitFest,
>> not the end.
>
> I think traditionally we understood "feature freeze" to be the point at
> which we stopped *committing* new features, not the point at which it
> was too late to *submit* them.  So by that definition feature freeze
> starts at the end of the last CF.

OK, fair enough.

> I agree with Peter that things are a bit different in the CF process.
> Rather than a binary frozen-or-not state, we now have a gradual
> congealing (if you will), where the size of an acceptable new feature
> gets smaller as we get towards the end of the development cycle.

Yeah, and I have no problem with that.  I think I've already beaten
this horse to death, though, so I won't re-explain what I do think.

...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] restructuring "alter table" privilege checks (was: remove redundant ownership checks)

[Robert Haas]

On Thu, Dec 17, 2009 at 10:09 PM, Stephen Frost  wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> KaiGai Kohei  writes:
>> > [ patch to remove EnableDisableRule's permissions check ]
>>
>> I don't particularly like this patch, mainly because I disagree with
>> randomly removing permissions checks without any sort of plan about
>> where they ought to go.
>
> The goal of this was to increase consistancy with the rest of the code,
> in particular, ATPrepCmd checks ownership rights on the table, and
> anything which wants to check permissions beyond that has to do it
> independently.  Do I like that?  No, not really.

After you posted this, I agreed with you a time or two that this was
kooky, but I spent some time yesterday and today reading through
tablecmds.c, and I've somewhat revised my opinion.  It seems to me
that the ALTER TABLE permissions checks can be basically divided into
two parts: (A) checks on the relation that is being altered, and (B)
checks on other objects that are involved in whatever operation is
being performed.

Right now, the part-A logic is partially centralized in ATPrepCmd()
and partially spread throughout the rest of the code, and the part-B
logic is all over the place.  If we want ALL the permissions checking
for any given ALTER TABLE command to happen in ONE place, we're going
to have to do one of two things, neither of which is currently making
sense to me.  One, we could move all of the checks that are now done
in ATPrepCmd() down into the constituent ATPrep* functions and do all
the checks at once at the point when we have enough information.  But
that almost seems like it's going backwards - we're spreading out
checks that are now (kind of) centralized.  And, as Tom has pointed
out, it means doing more work before we decide whether we even had
permission to take the relation lock.  Two, we could try to pull all
the permission checks that are NOT in ATPrepCmd() back in.  But that
hardly seems feasible - we don't have enough information at that
point.

So, what if we treat these two cases separately?  The part-B checks -
on the other operations involved in ALTER TABLE - are by definition
idiosyncratic.  What type of object we're checking and what permission
we're checking for is inextricably bound up with what kind of ALTER
TABLE operation we're trying to perform.  So, that's going to be hard
to centralize.  But the part-A checks - on the relation itself - seem
like they could be consolidated.  The reason why they are spread out
right now is mostly because of relatively minor deviations from what
ATSimplePermissions does.

A1. ATSimplePermissionsRelationOrIndex(), which a few of the ALTER
TABLE subcommands use, is exactly the same as ATSimplePermissions(),
except that it allows indices as well.
A2. ATSetStatistics() and ATSetDistinct() are also similar, but they
both allow indices and also skip the defenses against system table
modification.
A3. ATExecChangeOwner() emits a warning indices and then performs no
action (for backwards compatibility), rather than emitting an error as
ATSimplePermissions() would do.  It also doesn't check permission if
the old and new owner are the same.
A4. ATExecEnableDisableTrigger() and ATExecEnableDisableRule() call,
respectively, EnableDisableTrigger and EnableDisableRule, each of
which does a redundant permissions check.

I believe that everything else just calls ATSimplePermissions(),
though it's possible I've missed something.  It strikes me that if we
changed the signature for ATSimplePermissions, we could eliminate
A1-A3 (and A4 is trivial):

static void ATSimplePermissions(Relation rel, AlterTableCmd *cmd);

The plan would be to move the knowledge of which operations require
special treatment (allowing indices, system tables, etc.) into
ATSimplePermissions() and then just calling it unconditionally for ALL
object types.  ATSimplePermissionsRelationOrIndex() would go away.
ATExecChangeOwner() would require some refactoring, but I think it
would end up being simpler than it is now.  I also think it would be
more clear which checks are being applied to which object types.

Just to enumerate the part-B permissions checks, that is, permissions
checks on objects other than the table to which ALTER TABLE is being
directly applied, the ones I found were:

B1. ATAddForeignKeyConstrants() checks for REFERENCES permission on
the two tables involved.
B2. ATExecDropColumn() and ATExecDropConstraint() check for permission
to perform the drop on the child tables to which they decide to
recurse.
B3. ATExecAddInherit() checks permissions on the new parent.
B4. ATPrepSetTablespace() checks for permission to use the new tablespace.
B5. ATExecAddIndex() calls DefineIndex(), which also checks for rights
on the new namespace.

B2 and B3 are actually implemented at present using
ATSimplePermissions, and I think we could keep it that way under the
proposed signature change, with only minor refactoring.  The others
are basically all special cases, but there aren

Re: [HACKERS] Access to dynamic SQL in PL/pgSQL

[Simon Riggs]

On Fri, 2010-01-22 at 10:56 -0500, Tom Lane wrote:
> Simon Riggs  writes:
> > It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> > statement using a PL/pgSQL plugin.
> 
> > In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> > statement type, evaluate the SQL and free memory again before the plugin
> > gains control again.
> 
> > It seems simple to attach querystr to PLpgSQL_execstate and free it
> > after the plugin has seen it. The difference in lifetime of the memory
> > allocation is very small.
> 
> That seems like a complete crock --- you're talking about leaving a
> dangling pointer to transient data in a permanent data structure.

No, I wouldn't call it that. You read the bit where I said "free"?

> In most contexts it would be difficult to be sure if the pointer was
> valid or not.
> 
> If we need this it would be better to provide another plugin hook call
> during the execution of a statement that uses a dynamic query.
> 
> > Would a patch to make this simple change be acceptable? It would need to
> > be backpatched to 8.1 also?
> 
> As for the first, I vote "not like that", and as for backpatching,
> you're out of your mind.

I think by most people's standards asking for acceptance here really is
insane, I agree.

> This would be an incompatible ABI change,
> and we don't lightly make those in stable branches.  Even if we were
> willing to take the risk, how would a plugin know if it were dealing
> with a version of plpgsql that had this field?

ISTM there are ways though I think what you mean is that they would be
unpalatable.


Anyway, there already was another way of doing this, so it shall be done
that way instead. The beauty of a pluggable architecture is that one
does not need approval to implement customer solutions.

-- 
 Simon Riggs   www.2ndQuadrant.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: commit fests (was Re: [HACKERS] primary key error message)

[Tom Lane]

Robert Haas  writes:
> I'm not sure whether you're stating a position that's been agreed to
> by -core or some other group, or just expressing your own opinion, but
> I think feature freeze should be the beginning of the last CommitFest,
> not the end.

I think traditionally we understood "feature freeze" to be the point at
which we stopped *committing* new features, not the point at which it
was too late to *submit* them.  So by that definition feature freeze
starts at the end of the last CF.

I agree with Peter that things are a bit different in the CF process.
Rather than a binary frozen-or-not state, we now have a gradual
congealing (if you will), where the size of an acceptable new feature
gets smaller as we get towards the end of the development cycle.

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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Aidan Van Dyk]

* Brendan Jurd  [100122 10:29]:
 
> Holy query language, Batman!
> 
> Do you mean to tell me that the "uninformed masses" you interact with
> have an understanding of what "SQL" means?
> 
> I am skeptical of this claim, but if true, you must have access to the
> most spectacularly informed "uninformed masses" on the planet.

I can't speak for Mark, but the "uniformed masses" I interact with tend
to be the guys looking for (and authorizing) solutions in small-medium
business segment...  And Microsoft has done the "education" for us and
automatically associated this unknown "SQL"  with "a big database"...
So despite that they have no idea what "SQL" actually means, or where it
came from, it's got the desired association.

So, my neck of the woods ain't necessarily yours, but...

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Access to dynamic SQL in PL/pgSQL

[Tom Lane]

Simon Riggs  writes:
> It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> statement using a PL/pgSQL plugin.

> In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> statement type, evaluate the SQL and free memory again before the plugin
> gains control again.

> It seems simple to attach querystr to PLpgSQL_execstate and free it
> after the plugin has seen it. The difference in lifetime of the memory
> allocation is very small.

That seems like a complete crock --- you're talking about leaving a
dangling pointer to transient data in a permanent data structure.
In most contexts it would be difficult to be sure if the pointer was
valid or not.

If we need this it would be better to provide another plugin hook call
during the execution of a statement that uses a dynamic query.

> Would a patch to make this simple change be acceptable? It would need to
> be backpatched to 8.1 also?

As for the first, I vote "not like that", and as for backpatching,
you're out of your mind.  This would be an incompatible ABI change,
and we don't lightly make those in stable branches.  Even if we were
willing to take the risk, how would a plugin know if it were dealing
with a version of plpgsql that had this field?

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] Standby server lagging behind

[Greg Stark]

On Fri, Jan 22, 2010 at 2:37 PM, Kevin Grittner
 wrote:
>
> I think I have some recall of improvements to that in the 8.4
> release, so an upgrade might help.

There was a standalone program which you can call from your recovery
script to prefetch the blocks needed to replay the next log file
before starting to apply it. I find this message but there may be a
more recent version if you search:
http://archives.postgresql.org/pgsql-hackers/2008-11/msg01807.php

Otherwise I think there were some performance improvements to replay
but I don't recall which versions which improvements were in.

-- 
greg

-- 
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] plpythonu DO support (inline call handler)

[Peter Eisentraut]

On tis, 2009-11-17 at 18:48 +0200, Valtonen, Hannu wrote:
> The attached patch adds support for DO clause in plpythonu. It was 
> heavily inspired by the plperl and plpgsql inline handler code.

committed



-- 
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] warn in plperl logs as... NOTICE??

[Alexey Klyukin]

On Jan 22, 2010, at 4:38 PM, Robert Haas wrote:

> On Fri, Jan 22, 2010 at 9:13 AM, Alexey Klyukin  wrote:
>> I think elog(WARNING) is less surprising for the end-user, unless there's an 
>> objection strong enough to include it into the documentation :)
> 
> I think the main possible objection would what Simon just wrote on the
> other thread - that it's been this way for a while, and while someone
> might think that a different decision about how to handle it would
> have been better, there may be people counting on the current behavior
> who will have to spend time and perhaps money making changes if we
> change it.

Well, then we have to choose between a fixed number of unhappy users in the 
past and potentially increasing number of unhappy users in the future (if we 
admit the fact that this behavior is illogical).  IMO if something behaves 
counterintuitively to most users the behavior should be at least documented, if 
not fixed.  

--
Alexey Klyukin  http://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

Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Brendan Jurd]

2010/1/23 Mark Mielke :
> Calling it
> "PostgreSQL", makes it very clear to the uninformed masses where the product
> fits in a product map. Tell an executive of a company "Postgres", and they
> would ask "what is it?" Tell them "PostgreSQL", and they'll say "is that
> like Oracle?" The second is hugely more valuable.

Holy query language, Batman!

Do you mean to tell me that the "uninformed masses" you interact with
have an understanding of what "SQL" means?

I am skeptical of this claim, but if true, you must have access to the
most spectacularly informed "uninformed masses" on the planet.

Cheers,
BJ

-- 
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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Mark Mielke]

On 01/22/2010 09:52 AM, Greg Sabino Mullane wrote:


Well, this *was* posted to -hackers and not -advocacy, but
advocacy, mind share, and many other non-hacking-on-the-base-code things
matter too. And frankly, our name is one of our *top* problems.
Perhaps you've never had to explain to non-technical people how to
pronounce it? Or sheepishly explained why we have such a lame,
geeky sounding portmanteau? Or assured people that saying "Postgres"
is perfectly fine, and that everyone says it that way anyway?
   


I do not read -advocacy, so I probably missed the "important" discussion 
on this subject...


I cannot see how the current name is a "top" problem in any priority 
scheme I care about. I like the current name, and the *infrequent* time 
the question comes up, it gives me the opportunity to summarize the 
history of PostgreSQL, and show people how PostgreSQL is a mature 
product that has earned a place in software history.


How this could be a problem? I don't understand. I do not believe people 
would choose or not choose a product based on whether they happen to 
pronounce it correctly from the start.


Most importantly, changing the name back to "Postgres" does not actually 
make the product better in any material way, nor does it improve 
understanding of what the product does. Having "SQL" in the name, makes 
it clear what the product is. We use Atlassian products, and one of the 
first complaints we get is that people don't implicitly know what 
products like "Bamboo", "Confluence", "Crucible", "FishEye", or "JIRA" 
do. They cannot map the products in their head because they have no 
context. Calling it "PostgreSQL", makes it very clear to the uninformed 
masses where the product fits in a product map. Tell an executive of a 
company "Postgres", and they would ask "what is it?" Tell them 
"PostgreSQL", and they'll say "is that like Oracle?" The second is 
hugely more valuable.


I don't want to open the discussion, because I like things the way they 
are, and think the PostgreSQL developers are doing an excellent job on 
the high priority items. PostgreSQL is really one of the greatest open 
source projects out there. I love it!


I just can't see a statement like "our name is one of our *top* 
problems" go by uncontested. It is false in every way I can think of 
considering it. Perhaps *some* people have an issue with it. Perhaps 
these people are enough to pressure a change against the rest who care 
more about performance, reliability, and features, than a name. But, 
ultimately, the people working on the performance, reliability, and 
features, are the people that are making PostgreSQL the success that it 
is today. The name will not and should not increase adoption. Well, at 
least in my not so humble opinion.


Back to the exciting live standby features and such please! I'm very 
much looking forward to seeing them in a release. *These* features, I 
can "sell" from an advocacy perspective. :-)


Cheers,
mark

--
Mark Mielke


--
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] primary key error message

[Peter Eisentraut]

On fre, 2010-01-22 at 15:10 +, Simon Riggs wrote:
> I merely ask that you consider the non-zero cost of such changes as
> well
> as the benefit. Not all change is worthwhile, even if the change can
> be
> made quickly and with little effect on the stability of the software.

Right, that's why I asked for comments, and the comments were clear. :)


-- 
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] primary key error message

[Simon Riggs]

On Fri, 2010-01-22 at 16:50 +0200, Peter Eisentraut wrote:
> On fre, 2010-01-22 at 14:22 +, Simon Riggs wrote:
> > "Stable software" isn't just software that doesn't break, it requires
> > IIABDFI as well.
> 
> Yeah, I don't buy that.  That would mean that you can never do
> maintenance, refactoring, or polishing.  You basically just wait for the
> system to die a death somewhere between unmaintainability and ugliness.

I merely ask that you consider the non-zero cost of such changes as well
as the benefit. Not all change is worthwhile, even if the change can be
made quickly and with little effect on the stability of the software.

-- 
 Simon Riggs   www.2ndQuadrant.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] ECPG patch 4.1, out-of-scope cursor support in native mode

[Michael Meskes]

> diff -dcrpN 
> pgsql.orig/src/interfaces/ecpg/test/expected/compat_informix-struct.c 
> pgsql.4.1/src/interfaces/ecpg/test/expected/compat_informix-struct.c
> ...
> + /* Test DECLARE ... SELECT ... INTO with struct type */
> + 
> + ECPGset_var( 0, &( myvar ), __LINE__);\
> +  ECPGset_var( 1, &( mynullvar ), __LINE__);\
> +  ECPG_informix_reset_sqlca(); /* declare mycur cursor for select * from a1 
> */
> + #line 45 "struct.pgc"
> + 
> + { ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_normal, "declare mycur cursor 
> for select * from a1", ECPGt_EOIT, 
> + ECPGt_int,&(myvar.id),(long)1,(long)1,sizeof(int), 
> ...

Why does the preproc spit out ECPGset_var's but no ECPGget_var's in this test 
case? 

Michael

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

-- 
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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160   


> As far as I can see, there is absolutely zero reason to care about
> whether the product is called Postgres or PostgreSQL. 

Sorry, but names matter. Advocacy matters. Please take a look in 
the archives on why this is so before making such a blanket  
statement.   

> If it were called WeGrindUpTheBonesOfSmallChildrenSQL, maybe a 
> change would be worth considering. 

Actually, that would be an improvement, because at least that's 
intuitively pronounceable, if a bit long. :)

> As it is, I submit that the product name is not on in the top 
> 10,000 things we should be worried about fixing   

Well, this *was* posted to -hackers and not -advocacy, but 
advocacy, mind share, and many other non-hacking-on-the-base-code things 
matter too. And frankly, our name is one of our *top* problems.  
Perhaps you've never had to explain to non-technical people how to   
pronounce it? Or sheepishly explained why we have such a lame,   
geeky sounding portmanteau? Or assured people that saying "Postgres" 
is perfectly fine, and that everyone says it that way anyway?

>, even if there were a consensus that it were a good idea 
> (which there isn't)  

I beg to differ, the change has very wide support, including among 
members of -core. Please read the archives.

> and even if -core had not already made a decision on this point (which
> they have).

They punted, but there is no reason we can't revisit the topic. They
are certainly allowed to change their minds. :)

> What I think we SHOULD be worrying about right now is getting 9.0
> out the door, and I am 100% opposed to letting ourselves getting
> sucked into this or any other discussion which is likely to
> make that take longer than it likely already will.

What makes you think this is all a zero-sum game? You are free not to
get "sucked into this discussion", but remember that this is a
volunteer project, consisting of people with many and varied skills.
There are a small handful of people who are responsible for getting 9.0
out the door. There are thousands of other people who are working on
other Postgres-related things, including, at times, advocacy.

I'll move this over to -advocacy where it belongs, along with some
more concrete discussion of how we would make the name change,
when and if it happens.

- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201001220952
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAktZu6AACgkQvJuQZxSWSshZKACfWaOxQh9mRvhI0VvFfTRaQ48T
C3sAn343Nanez3hXI+t1f+xl0YAIMcX3
=lETk
-END PGP SIGNATURE-



-- 
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] primary key error message

[Peter Eisentraut]

On fre, 2010-01-22 at 14:22 +, Simon Riggs wrote:
> "Stable software" isn't just software that doesn't break, it requires
> IIABDFI as well.

Yeah, I don't buy that.  That would mean that you can never do
maintenance, refactoring, or polishing.  You basically just wait for the
system to die a death somewhere between unmaintainability and ugliness.


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

Re: commit fests (was Re: [HACKERS] primary key error message)

[Robert Haas]

2010/1/22 Devrim GÜNDÜZ :
> On Fri, 2010-01-22 at 09:10 -0500, Robert Haas wrote:
>> I'm not sure whether you're stating a position that's been agreed to
>> by -core or some other group, or just expressing your own opinion, but
>> I think feature freeze should be the beginning of the last CommitFest,
>> not the end.
>
> Was is declared somewhere before commitfests began? If not, I would
> agree with Peter here: Beta is the feature freeze. IIRC it has always
> been so.

I don't know.  I'm not trying to fabricate positions out of whole
cloth; I'm just saying what I thought we were doing.  Obviously at
least some other people have a different understanding.  We don't
really have much of this documented, to my knowledge, so there's
nothing that I can go read and say, ok, this is what the procedure is.
Which is kind of unfortunate, actually...

...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] warn in plperl logs as... NOTICE??

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

>>> Why does warn; in plperl log as NOTICE in Postgres?

> *shrug* I don't have a strong opinion about it, and it's pretty easy to
> change, if there's a consensus we should. I have certainly found over
> the years that perl warnings from some modules can be annoyingly
> verbose, which is probably why the original patch didn't make them have
> a higher level in Postgres. If this were a big issue we'd have surely
> heard about it before now - there are plenty of plperl users out there.

As a heavy user of Pl/Perl[U], this behavior has always surprised me,
but never enough to bother complaining about it. However, count me
as a +1 to make warn == elog(WARNING)

- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201001220941
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAktZuUEACgkQvJuQZxSWSsji4gCgwR8UEE4CimL8tAKnu0ZU+y+w
nxkAn3MCOVCGr6BuCiRcjxrQ2/KCe/Bn
=5Ukn
-END PGP SIGNATURE-



-- 
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] warn in plperl logs as... NOTICE??

[Robert Haas]

On Fri, Jan 22, 2010 at 9:13 AM, Alexey Klyukin  wrote:
> I think elog(WARNING) is less surprising for the end-user, unless there's an 
> objection strong enough to include it into the documentation :)

I think the main possible objection would what Simon just wrote on the
other thread - that it's been this way for a while, and while someone
might think that a different decision about how to handle it would
have been better, there may be people counting on the current behavior
who will have to spend time and perhaps money making changes if we
change it.

...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] Standby server lagging behind

[Kevin Grittner]

"Huda Booley (h...@careerjunction.co.za)"
 wrote:
 
> These get copied to the standby so its all there, just not being
> applied fast enough.
 
If the files are there and are not applying fast enough, it's
probably because on the source machine you can have multiple
connections submitting data modifications, keeping multiple CPUs and
spindles busy all at the same time.  When playing back the WAL files
on the warm standby, you have one connection which has a harder time
keeping a lot of resources busy.
 
I think I have some recall of improvements to that in the 8.4
release, so an upgrade might help.  Other than that, I think it's
mostly a matter of your hardware and OS.  Make sure you have a RAID
controller with battery backed cache configured for write-back,
spread the database across as many spindles as you can, don't worry
so much about how *many* CPUs you have as how *fast* they are.
 
I've never actually had this problem, so take my advice on it with
a grain of salt.
 
-Kevin

-- 
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] primary key error message

[Robert Haas]

On Fri, Jan 22, 2010 at 9:22 AM, Simon Riggs  wrote:
> On Thu, 2010-01-21 at 23:22 +0200, Peter Eisentraut wrote:
>> On tor, 2010-01-21 at 15:51 -0500, Tom Lane wrote:
>> > Robert Haas  writes:
>> > > On Thu, Jan 21, 2010 at 3:30 PM, Peter Eisentraut  
>> > > wrote:
>> > >> Here is a small patch that changes the error message
>> > >>
>> > >>  duplicate key value violates unique constraint "%s"
>> > >>
>> > >> into
>> > >>
>> > >>  duplicate key value violates primary key "%s"
>> > >>
>> > >> when the constraint is in fact a primary key.
>> > >>
>> > >> Comments?
>> >
>> > > Why bother?  And why bother now, when we're in the middle of the last
>> > > CommitFest and trying to move toward a release?
>> >
>> > This patch fails to cover all cases (index build being the obvious
>> > omission, but I think there might be other paths as well where the
>> > information is not so readily available).
>>
>> This is the user-visible error message, and that's the only place it's
>> generated.
>
> In general, I agree that some error messages could be better.
>
> OTOH this kind of gradual and minor creep between releases is very
> annoying for our users, since you cannot rely on things remaining the
> same between releases. That costs people lots of money and isn't worth
> the marginal benefit, or alternatively prevents upgrades because of the
> need for application and admin tool recoding and retesting. "Stable
> software" isn't just software that doesn't break, it requires IIABDFI as
> well.

Well put.

...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: commit fests (was Re: [HACKERS] primary key error message)

[Devrim GÜNDÜZ]

On Fri, 2010-01-22 at 09:10 -0500, Robert Haas wrote:

> I'm not sure whether you're stating a position that's been agreed to
> by -core or some other group, or just expressing your own opinion, but
> I think feature freeze should be the beginning of the last CommitFest,
> not the end.

Was is declared somewhere before commitfests began? If not, I would
agree with Peter here: Beta is the feature freeze. IIRC it has always
been so.
-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: commit fests (was Re: [HACKERS] primary key error message)

[Andrew Dunstan]

Robert Haas wrote:

On Fri, Jan 22, 2010 at 8:40 AM, Peter Eisentraut  wrote:
  

On tor, 2010-01-21 at 18:05 -0500, Andrew Dunstan wrote:


Well, we used to have the idea of a feature freeze ... is that going
to apply at the end of the commitfest?
  

Feature freeze was used to discourage the submission of very big patches
shortly before beta.  The commit fest process has IMO alleviated this
concern.  Beta is still the definite cutoff; and the closer we get to
beta, the smaller the acceptable changes become.  I think that formula
basically applies throughout the entire cycle.



I'm not sure whether you're stating a position that's been agreed to
by -core or some other group, or just expressing your own opinion, but
I think feature freeze should be the beginning of the last CommitFest,
not the end.
  



The commitfest is a useful procedural tool, but I think attempts to turn 
it into something more prescriptive are likely to meet significant 
resistance. Even the old feature freeze was a bit porous, especially 
early on during the freeze, when small, low impact patches were not met 
with cries of "you're six days past the deadline".


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] primary key error message

[Simon Riggs]

On Thu, 2010-01-21 at 23:22 +0200, Peter Eisentraut wrote:
> On tor, 2010-01-21 at 15:51 -0500, Tom Lane wrote:
> > Robert Haas  writes:
> > > On Thu, Jan 21, 2010 at 3:30 PM, Peter Eisentraut  wrote:
> > >> Here is a small patch that changes the error message
> > >> 
> > >>  duplicate key value violates unique constraint "%s"
> > >> 
> > >> into
> > >> 
> > >>  duplicate key value violates primary key "%s"
> > >> 
> > >> when the constraint is in fact a primary key.
> > >> 
> > >> Comments?
> > 
> > > Why bother?  And why bother now, when we're in the middle of the last
> > > CommitFest and trying to move toward a release?
> > 
> > This patch fails to cover all cases (index build being the obvious
> > omission, but I think there might be other paths as well where the
> > information is not so readily available).
> 
> This is the user-visible error message, and that's the only place it's
> generated.

In general, I agree that some error messages could be better. 

OTOH this kind of gradual and minor creep between releases is very
annoying for our users, since you cannot rely on things remaining the
same between releases. That costs people lots of money and isn't worth
the marginal benefit, or alternatively prevents upgrades because of the
need for application and admin tool recoding and retesting. "Stable
software" isn't just software that doesn't break, it requires IIABDFI as
well.

-- 
 Simon Riggs   www.2ndQuadrant.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] warn in plperl logs as... NOTICE??

[Alexey Klyukin]

On Jan 22, 2010, at 2:55 AM, Andrew Dunstan wrote:

> 
> 
> Tom Lane wrote:
>> Andrew Dunstan  writes:
>>  
>>> Jim Nasby wrote:
>>>
 Why does warn; in plperl log as NOTICE in Postgres?
  
>> 
>>  
>>> Where would you like the warning to go? This has been this way for nearly 5 
>>> years, it's not new (and before that the warning didn't go anywhere).
>>>
>> 
>> I think he's suggesting that it ought to translate as elog(WARNING)
>> not elog(NOTICE).
>> 
>>  
>>  
> 
> *shrug* I don't have a strong opinion about it, and it's pretty easy to 
> change, if there's a consensus we should. I have certainly found over the 
> years that perl warnings from some modules can be annoyingly verbose, which 
> is probably why the original patch didn't make them have a higher level in 
> Postgres. If this were a big issue we'd have surely heard about it before now 
> - there are plenty of plperl users out there.

I think elog(WARNING) is less surprising for the end-user, unless there's an 
objection strong enough to include it into the documentation :)

--
Alexey Klyukin  http://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

Re: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Greg Stark]

On Thu, Jan 21, 2010 at 4:19 PM, Tom Lane  wrote:
> You're poking into a data structure you shouldn't be poking into:
>
> /* Plans are opaque structs for standard users of SPI */
> typedef struct _SPI_plan *SPIPlanPtr;
>
> I hardly think that keeping yourself at arm's length from the planner
> by getting cozy with SPI internals is a net improvement in modularity.


You could do it without poking into the SPI structure. You could
define a planner_hook which calls the regular planner and then just
use SPI to drive the parser etc and invoke your planner hook. The
planner hook could inspect the plan, even modify it.

One of the plans I considered early on which I skipped on because I
thought it might get too complex was to add special syntax like
"SELECT __heaptuple__ from table ORDER BY ..." and then just run it
through the regular executor (and do some unspecified hackery around
the visibility rules).

Part of the reason I'm interested in using the regular planner as much
as possible is that if you have a partial index it could be a huge win
to use some unexpected different index to scan the part of the table
you need.




-- 
greg

-- 
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] Fix auto-prepare #2

[Michael Meskes]

Takahiro-san,

> Good. I think the patch is ready to commit.

Thanks for reviewing it. I just committed the patch.

> A comment for committer (Michael?) :
> I was cofused by the AddStmtToCache's 2nd argument "char *stmtID"
> because it doesn't have a const. Should it be "const char *" ?
> If the argument has a const, callers assume that they can pass
> a not-strdup'ed string as the argument.

Valid point, I can see no reason for not making this a "const char *". So let's
try.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

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

Re: commit fests (was Re: [HACKERS] primary key error message)

[Robert Haas]

On Fri, Jan 22, 2010 at 8:40 AM, Peter Eisentraut  wrote:
> On tor, 2010-01-21 at 18:05 -0500, Andrew Dunstan wrote:
>> Well, we used to have the idea of a feature freeze ... is that going
>> to apply at the end of the commitfest?
>
> Feature freeze was used to discourage the submission of very big patches
> shortly before beta.  The commit fest process has IMO alleviated this
> concern.  Beta is still the definite cutoff; and the closer we get to
> beta, the smaller the acceptable changes become.  I think that formula
> basically applies throughout the entire cycle.

I'm not sure whether you're stating a position that's been agreed to
by -core or some other group, or just expressing your own opinion, but
I think feature freeze should be the beginning of the last CommitFest,
not the end.

...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: commit fests (was Re: [HACKERS] primary key error message)

[Peter Eisentraut]

On tor, 2010-01-21 at 19:45 -0500, Robert Haas wrote:
> Well, that does seem to be endorsing a sort of two-tiered system.

In those words, yes, it's a multi-tiered system.  The aim of the commit
fests is to make the "lower" tier more effective, but not necessarily to
bring the "upper" tier to a near halt.

> If I submit a patch and nobody looks at it, I can decide that silence
> means approval and commit it.  If someone who is not a committer does
> the same thing, it dies, no matter how technically excellent it is.  I
> am no longer in a position to be bothered by that, but I think if I
> were not a committer I might be.  I wonder what others think about
> this.

Well, you have worked hard to get to that position, so those are the
perks.

> There's another issue, too.  If a committer submits a patch, everybody
> else who cares about the issue has to drop what they're doing and look
> at it.  Because if they don't, there's a good chance that in 24 hours
> plus or minus, it'll be in the tree.  Several patches have blown by me
> in the last month or two - already committed before I got around to
> reading them, and I might have had an opinion on them, but it's too
> late to do anything about it now.  I mean, it's not, really: I could
> still ask for something to be changed, but it's an uphill battle at
> this point.

That would seem to ask that all committers funnel their patches through
the commit fest process.  That might technically and morally be the
right thing, but it would probably not be a popular or realistic
proposal.


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

Re: commit fests (was Re: [HACKERS] primary key error message)

[Peter Eisentraut]

On tor, 2010-01-21 at 18:05 -0500, Andrew Dunstan wrote:
> Well, we used to have the idea of a feature freeze ... is that going
> to apply at the end of the commitfest?

Feature freeze was used to discourage the submission of very big patches
shortly before beta.  The commit fest process has IMO alleviated this
concern.  Beta is still the definite cutoff; and the closer we get to
beta, the smaller the acceptable changes become.  I think that formula
basically applies throughout the entire cycle.


-- 
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] warn in plperl logs as... NOTICE??

[Tim Bunce]

On Thu, Jan 21, 2010 at 07:55:09PM -0500, Andrew Dunstan wrote:
> Tom Lane wrote:
> >Andrew Dunstan  writes:
> >>Jim Nasby wrote:
> >>>Why does warn; in plperl log as NOTICE in Postgres?
> >
> >>Where would you like the warning to go? This has been this way
> >>for nearly 5 years, it's not new (and before that the warning
> >>didn't go anywhere).
> >
> >I think he's suggesting that it ought to translate as elog(WARNING)
> >not elog(NOTICE).
> 
> *shrug* I don't have a strong opinion about it, and it's pretty easy
> to change, if there's a consensus we should. I have certainly found
> over the years that perl warnings from some modules can be
> annoyingly verbose, which is probably why the original patch didn't
> make them have a higher level in Postgres. If this were a big issue
> we'd have surely heard about it before now - there are plenty of
> plperl users out there.

I've no particular opinion either way on this. I can't resist the
tempation, however, to point out that this is an example the kind of
site-preference that could be handled via plperl.on_perl_init:

plperl.on_perl_init='$SIG{__WARN__} = sub { elog(WARNING, shift) }'
or
plperl.on_perl_init='use lib "/MyApp/lib"; use MyApp::PLPerlInit;'

You could get more fancy and employ some logic to using WARNING for the
first instance of any given message text and NOTICE for subsequent ones.

Tim.

-- 
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] quoting psql varible as identifier

[Pavel Stehule]

Hello

here is new variant. Add scan_state flag "valid" and enhance
protection against execution broken statement.

Regards
Pavel Stehule

2010/1/22 Pavel Stehule :
> 2010/1/22 Robert Haas :
>> On Thu, Jan 21, 2010 at 2:25 PM, Pavel Stehule  
>> wrote:
>>> 2010/1/21 Robert Haas :
 On Thu, Jan 21, 2010 at 12:53 PM, Pavel Stehule  
 wrote:
> add to state structure field like lexer_error. This field will be
> checked before execution
> it could be ugly for metacommands, there will be lot of new checks :(

 Eh?  The only places where we should need new tests are the places
 that check PQExpBufferBroken() now - there are only 6 calls to that
 function in src/bin/psql and not all of them need to be changed.  The
 places that do need to be changed will need to be modified to check
 PQExpBufferBroken() || lexer_coughed_up_a_lung.
>>>
>>> no, it is only 6 calls because we don't check psql_scan_slash_option result.
>>
>> psql_scan_slash_option() already has a way to signal errors - it can
>> return NULL.  Type any backslash command followed by a single quote...
>
> NULL means "no value". But I thing, so there is only one important -
> \set. For other can be enough some error message and empty value.
>
>>
>> I'm not saying I love the way those errors are handled, but if we make
>> this patch about revising the way psql does error handling, this is
>> not going to get committed for this release...  what we need to do is
>> fit what we're trying to do into the existing model.
>>
>
> I try to find some simple and I'll send a patch.
>
> Pavel
>
>> ...Robert
>>
>


variables-escape.diff
Description: Binary data

-- 
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] Access to dynamic SQL in PL/pgSQL

[Simon Riggs]

On Fri, 2010-01-22 at 13:39 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> > statement using a PL/pgSQL plugin.
> > 
> > In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> > statement type, evaluate the SQL and free memory again before the plugin
> > gains control again.
> > 
> > It seems simple to attach querystr to PLpgSQL_execstate and free it
> > after the plugin has seen it. The difference in lifetime of the memory
> > allocation is very small.
> 
> Is this for pl/debugger? How would you use the query string there?

Yes, PL/debugger would have access to the text of the dynamic SQL
string.

-- 
 Simon Riggs   www.2ndQuadrant.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] Access to dynamic SQL in PL/pgSQL

[Heikki Linnakangas]

Simon Riggs wrote:
> It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> statement using a PL/pgSQL plugin.
> 
> In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> statement type, evaluate the SQL and free memory again before the plugin
> gains control again.
> 
> It seems simple to attach querystr to PLpgSQL_execstate and free it
> after the plugin has seen it. The difference in lifetime of the memory
> allocation is very small.

Is this for pl/debugger? How would you use the query string there?

-- 
  Heikki Linnakangas
  EnterpriseDB   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] Streaming Replication on win32

[Heikki Linnakangas]

Marko Kreen wrote:
> On 1/22/10, Dimitri Fontaine  wrote:
>> Heikki Linnakangas  writes:
>>  > The problem only applies to libpq calls from the backend. Client apps
>>  > are not affected, only backend modules. If there's any other modules out
>>  > there that use libpq, then yes, those have a problem too.
>>
>>
>> plproxy comes to mind.
> 
> Thats interesting.  PL/Proxy deos not use PQexec, it uses async
> execution and waits on sockets with plain select() called
> from code compiled with backend headers.
> 
> So it seems to be already using pgwin32_select().  Or not?

Yes. I just grepped plproxy source code and there's indeed no blocking
libpq calls there.

-- 
  Heikki Linnakangas
  EnterpriseDB   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] Access to dynamic SQL in PL/pgSQL

[Simon Riggs]

On Fri, 2010-01-22 at 11:41 +0100, Pavel Stehule wrote:
> 2010/1/22 Simon Riggs :
> >
> > It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> > statement using a PL/pgSQL plugin.
> >
> > In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> > statement type, evaluate the SQL and free memory again before the plugin
> > gains control again.
> 
> can you show some example, please?

exec_stmt_dynexecute() evaluates querystr at start and pfrees it before
end of exec_stmt_dynexecute(). So plugin never gets to see the SQL
executed.

-- 
 Simon Riggs   www.2ndQuadrant.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] Access to dynamic SQL in PL/pgSQL

[Pavel Stehule]

2010/1/22 Simon Riggs :
>
> It's not currently possible to access the SQL used in a dynamic PL/pgSQL
> statement using a PL/pgSQL plugin.
>
> In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
> statement type, evaluate the SQL and free memory again before the plugin
> gains control again.

can you show some example, please?

Pavel

>
> It seems simple to attach querystr to PLpgSQL_execstate and free it
> after the plugin has seen it. The difference in lifetime of the memory
> allocation is very small.
>
> Would a patch to make this simple change be acceptable? It would need to
> be backpatched to 8.1 also?
>
> --
>  Simon Riggs           www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

[HACKERS] Access to dynamic SQL in PL/pgSQL

[Simon Riggs]

It's not currently possible to access the SQL used in a dynamic PL/pgSQL
statement using a PL/pgSQL plugin.

In src/pl/plpgsql/src/pl_exec.c's exec_stmt() we call each dynamic
statement type, evaluate the SQL and free memory again before the plugin
gains control again.

It seems simple to attach querystr to PLpgSQL_execstate and free it
after the plugin has seen it. The difference in lifetime of the memory
allocation is very small.

Would a patch to make this simple change be acceptable? It would need to
be backpatched to 8.1 also?

-- 
 Simon Riggs   www.2ndQuadrant.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] Streaming Replication on win32

[Marko Kreen]

On 1/22/10, Dimitri Fontaine  wrote:
> Heikki Linnakangas  writes:
>
>  > Joe Conway wrote:
>  >> OK, so now I see why we want this fixed for dblink and walreceiver, but
>  >> doesn't this approach leave every other WIN32 libpq client out in the
>  >> cold? Is there nothing that can be done for the general case, or is it a
>  >> SMOP?
>  >
>  > The problem only applies to libpq calls from the backend. Client apps
>  > are not affected, only backend modules. If there's any other modules out
>  > there that use libpq, then yes, those have a problem too.
>
>
> plproxy comes to mind.

Thats interesting.  PL/Proxy deos not use PQexec, it uses async
execution and waits on sockets with plain select() called
from code compiled with backend headers.

So it seems to be already using pgwin32_select().  Or not?

-- 
marko

-- 
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] Streaming Replication on win32

[Dimitri Fontaine]

Heikki Linnakangas  writes:

> Joe Conway wrote:
>> OK, so now I see why we want this fixed for dblink and walreceiver, but
>> doesn't this approach leave every other WIN32 libpq client out in the
>> cold? Is there nothing that can be done for the general case, or is it a
>> SMOP?
>
> The problem only applies to libpq calls from the backend. Client apps
> are not affected, only backend modules. If there's any other modules out
> there that use libpq, then yes, those have a problem too.

plproxy comes to mind.

-- 
dim

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

[HACKERS] Standby server lagging behind

[Huda Booley (h...@careerjunction.co.za)]

Hi

We are running postgres8.3.9 and have enabled log shipping replication. Our 
standby server is about 1000 files BEHIND - everything I've read indicates that 
the standby server should be very close behind the live server - at the rate 
we're going it will take 8 hours to apply all the logs to standby! How can I 
speed up the recovery process on the standby server?

Some info
Live server -16CPU, 80G Ram
we write about 2to3 16MB WAL files per minute
These get copied to the standby so its all there, just not being applied fast 
enough.


Any advice would be most welcome

Ta:)


Huda Booley
DBA | CareerJunction | Better jobs. More often.
Web: www.careerjunction.co.za |Email: 
h...@careerjunction.co.za
Phone: +27 21 818 8635 | Mobile: +27 82 9587818 | Fax: +27 21 818 8609


Disclaimer
This message contains information intended solely for the addressee, which is 
confidential or private in nature. If you are not the intended recipient, you 
may not peruse, use, disseminate, distribute or copy this message or any file 
attached to this message. Any such unauthorised use, is prohibited and may be 
unlawful. If you have received this message in error, please notify the sender 
immediately by e-mail, facsimile or telephone and thereafter delete the 
original message from your machine.
Furthermore, the information contained in this message, and any attachments 
thereto, is for information purposes only and may contain the personal views 
and opinions of the author, which are not necessarily the views and opinions of 
CareerJunction or its subsidiaries and associated companies. CareerJunction 
therefore does not accept liability for any claims, loss or damages of 
whatsoever nature, arising as a result of the reliance on such information by 
anyone.
Whilst all reasonable steps are taken to ensure the accuracy and integrity of 
information transmitted electronically and to preserve the confidentiality 
thereof, CareerJunction accepts no liability or responsibility whatsoever if 
information or data is, for whatever reason, incorrect, corrupted or does not 
reach its intended destination.

Re: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Leonardo F]

> > So my proposal would be: do the test seq_scan vs sort/index_scan only for
> > regular btree index, and integrate that test in the planner.
> 
> Keep in mind that this patch was after the deadline for 9.0, so there
> is probably not a huge rush to get this done.


That's true; I'll try to get the whole thing done then... 




-- 
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] 8.5 vs. 9.0

[Andreas Joseph Krogh]

On Friday 22. January 2010 01.22.09 Tom Lane wrote:
> "Larry Rosenman"  writes:
> > On Thu, January 21, 2010 5:53 pm, Andreas Joseph Krogh wrote:
> >> Care to shed some light on what features (yes, we users care about
> >> features) warrant this major version-bump? Is there a link somewhere?
> 
> > AFAIR, it was stated if Hot Standby AND Streaming Replication hit the
> > tree, the release number would go to 9.0.
> 
> Yeah.  The question of "when do we call it 9.0" has come up multiple
> times over the past few release cycles, and "when we get built-in
> replication" has always been one of the more popular answers.  If HS+SR
> aren't enough to justify a major version bump, I'm not sure what would be.
> 
> The other bit of rationale for this is that HS+SR are likely to induce a
> certain amount of, um, instability.  Labeling the release with a dot-oh
> version number will help to set people's expectations about that.  For
> comparison's sake, one of the main reasons for calling 8.0 8.0 was the
> native Windows port, and it certainly took a while for that to settle
> down.

Thank you for the enlightening reply.

-- 
Andreas Joseph Krogh 
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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