Re: [HACKERS] How about closing some Open Items?

[Simon Riggs]

On Tue, 2010-06-08 at 15:46 -0400, Tom Lane wrote:
> Josh Berkus  writes:
> > Let's get 9.0 out the door, hey?
> 
> What we actually need is some testing effort.  The lack of bug reports
> against Hot Standby, in particular, is proof positive that no meaningful
> testing is happening.

Absence of evidence is not evidence of absence. Please don't confuse
things by claiming "proof positive" does, or even can, exist here.

A lack of bugs usually indicates there are no bugs in the areas being
tested. Which can also mean the areas being tested don't cover the full
code or that tests of anything are not being performed.

I raised the topic of how to increase the amount of testing earlier; my
proposed solution was more betas before we go live. We just guaranteed
even less testing for HS by not fixing things for Beta2.

Would you like me to patch, or are you still intending to look at
max_standby_delay yourself?

> (If you think it means HS is bug-free, I have a
> nice bridge I'd like to interest you in.)

How should we proceed?

-- 
 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] [PATCH] Fix leaky VIEWs for RLS

[KaiGai Kohei]

(2010/06/08 11:15), Robert Haas wrote:
> 2010/6/7 KaiGai Kohei:
>> Our headache is on functions categorized to middle-threat. It enables to
>> leak the given arguments using error messages. Here are several ideas,
>> but they have good and bad points.
> 
> I think we are altogether off in the weeds here.  We ought to start
> with an implementation that pushes nothing down, and then try to
> figure out how much we can relax that without too much compromising
> security.
> 

The attached patch tries to prevent pushing down anything into subqueries
from outside of them.

The distribute_qual_to_rels() tries to distribute the given qualifier
into a certain scanning-plan based on the dependency of qualifier.

E.g) SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.x WHERE 
f_policy(t1.a)) WHERE f_user(t2.x);

In this case, f_user() function depends on only t2 table, so it is
reasonable to attach on the scanning plan of t2 from perspective of
performance.

However, f_user() may have a side-effect which writes arguments into
somewhere. If here is such a possibility, f_user() should not be called
before the joined tuples being filtered by f_policy().

In the case when we can ensure all functions within the qualifier are
enough trustable, we don't need to prevent them to be pushed down.
But the algorithm to determine it is under discussion. So, right now,
we prevent all the possible pushing down.

Example.1)  CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON a = x WHERE 
f_policy(a);
SELECT * FROM v1 WHERE f_malicious(b);

 * without this patch
postgres=# EXPLAIN SELECT * FROM v1 WHERE f_malicious(b);
QUERY PLAN
---
 Hash Join  (cost=639.01..667.29 rows=137 width=72)
   Hash Cond: (t2.x = t1.a)
   ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=637.30..637.30 rows=137 width=36)
 ->  Seq Scan on t1  (cost=0.00..637.30 rows=137 width=36)
   Filter: (f_policy(a) AND f_malicious(b))
(6 rows)

 * with this patch
postgres=# EXPLAIN SELECT * FROM v1 WHERE f_malicious(b);
QUERY PLAN
---
 Hash Join  (cost=334.93..468.44 rows=137 width=72)
   Hash Cond: (t2.x = t1.a)
   Join Filter: f_malicious(t1.b)
   ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=329.80..329.80 rows=410 width=36)
 ->  Seq Scan on t1  (cost=0.00..329.80 rows=410 width=36)
   Filter: f_policy(a)
(7 rows)

It prevents to push down f_malicious() inside of the join loop.


Example.2)  CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON a = x WHERE 
f_policy(a);
SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE f_malicious(b);

  * without this patch
postgres=# EXPLAIN SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE 
f_malicious(b);
  QUERY PLAN

---
 Hash Join  (cost=669.01..697.29 rows=137 width=108)
   Hash Cond: (t3.s = t1.a)
   ->  Seq Scan on t3  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=667.29..667.29 rows=137 width=72)
 ->  Hash Join  (cost=639.01..667.29 rows=137 width=72)
   Hash Cond: (t2.x = t1.a)
   ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=637.30..637.30 rows=137 width=36)
 ->  Seq Scan on t1  (cost=0.00..637.30 rows=137 
width=36)
   Filter: (f_policy(a) AND f_malicious(b))
(10 rows)

  * with this patch
postgres=# EXPLAIN SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE 
f_malicious(b);
  QUERY PLAN

---
 Hash Join  (cost=470.15..498.43 rows=137 width=108)
   Hash Cond: (t3.s = t1.a)
   ->  Seq Scan on t3  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=468.44..468.44 rows=137 width=72)
 ->  Hash Join  (cost=334.93..468.44 rows=137 width=72)
   Hash Cond: (t2.x = t1.a)
   Join Filter: f_malicious(t1.b)
   ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=329.80..329.80 rows=410 width=36)
 ->  Seq Scan on t1  (cost=0.00..329.80 rows=410 
width=36)
   Filter: f_policy(a)
(11 rows)

It also prevents f_malisious() to be pushed down into the join loop within view,
but we can push it down into same level of the query.


Please note that it specially handles equality operator at the bottom half of
the distribute_qual_to_rels(), so this patch does not care about these cases.
However, I'm not in hust

Re: [HACKERS] SR slaves and .pgpass

[Fujii Masao]

On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan  wrote:
> There is precedent for .pgpass being a bit ambiguous. See the way
> "localhost" is used.

OK. The attached patch allows us to use "replication" in the database
field of the .pgpass file, for the replication connection.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


pgpass_v1.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

[HACKERS] LLVM / clang

[P. Caillaud]

Hello,

I'd like to experiment on compiling postgres with LLVM (either llvm-gcc or  
clang) on Linux, is it supported ? Where should I start ?


Thanks ;)


--
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] SR slaves and .pgpass

[Andrew Dunstan]

Fujii Masao wrote:

Tom's proposal is very small, but we cannot distinguish the password
for replication purpose from that for the real database named "replication".
Is this OK? I can live with this as far as it's documented.

  


There is precedent for .pgpass being a bit ambiguous. See the way 
"localhost" is used.


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] Command to prune archive at restartpoints

[Robert Haas]

On Tue, Jun 8, 2010 at 10:18 PM, Takahiro Itagaki
 wrote:
>
> Robert Haas  wrote:
>> I think we're replacing restartpoint_command, not recovery_end_command.
>
> Ah, sorry. I did the same replacement for restartpoint_command
> in _, -, and camel case words.

Gah.  Perhaps one of these days we will stop spelling every identifier
in multiple different ways.

> BTW, should we also have a release note for the command?
> I added a simple description for it in the patch.

Yeah, it should be definitely mentioned in the release notes somewhere, I think.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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: Checkpoint_segment and wal_keep_segments on standby

[Fujii Masao]

On Wed, Jun 2, 2010 at 10:24 PM, Fujii Masao  wrote:
> On Wed, Jun 2, 2010 at 8:40 PM, Heikki Linnakangas
>  wrote:
>> On 02/06/10 06:23, Fujii Masao wrote:
>>>
>>> On Mon, May 31, 2010 at 7:17 PM, Fujii Masao
>>>  wrote:

 4) Change it so that checkpoint_segments takes effect in standby mode,
 but not during recovery otherwise
>>>
>>> I revised the patch to achieve 4). This will enable checkpoint_segments
>>> to trigger a restartpoint like checkpoint_timeout already does, in
>>> standby mode (i.e., streaming replication or file-based log shipping).
>>
>> Hmm, XLogCtl->Insert.RedoRecPtr is not updated during recovery, so this
>> doesn't work.
>
> Oops! I revised the patch, which changes CreateRestartPoint() so that
> it updates XLogCtl->Insert.RedoRecPtr.

This is one of open items. Please review the patch I submitted, and
please feel free to comment!

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Command to prune archive at restartpoints

[Takahiro Itagaki]

Robert Haas  wrote:
> I think we're replacing restartpoint_command, not recovery_end_command.

Ah, sorry. I did the same replacement for restartpoint_command
in _, -, and camel case words.

BTW, should we also have a release note for the command?
I added a simple description for it in the patch.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



estartpoint-to-archive_cleanup.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] SR slaves and .pgpass

[Fujii Masao]

On Tue, Jun 8, 2010 at 1:13 PM, Tom Lane  wrote:
> Fujii Masao  writes:
>> Hmm.. is it worth going back to my proposal?
>
> I don't recall exactly what proposal you might be referring to, but

http://archives.postgresql.org/pgsql-hackers/2010-01/msg00400.php

> I'm hesitant to put any large amount of work into hacking .pgpass
> processing for this.  The whole business of replication authorization
> is likely to get revisited in 9.1, no?  I think a cheap-and-cheerful
> solution is about right for the moment.

Fair enough. My proposal patch might be too large to apply at this
point.

>> -   snprintf(conninfo_repl, sizeof(conninfo_repl), "%s 
>> replication=true", conninfo);
>> +   snprintf(conninfo_repl, sizeof(conninfo_repl), "%s 
>> database=replication replication=true", conninfo);

Tom's proposal is very small, but we cannot distinguish the password
for replication purpose from that for the real database named "replication".
Is this OK? I can live with this as far as it's documented.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Command to prune archive at restartpoints

[Fujii Masao]

On Wed, Jun 9, 2010 at 10:45 AM, Takahiro Itagaki
 wrote:
>
> Robert Haas  wrote:
>
>> On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane  wrote:
>> > Andrew Dunstan  writes:
>> >> I prefer archive_cleanup_command. We should name things after their
>> >> principal function, not an implementation detail, IMNSHO.
>> >
>> > Weak preference for archive_cleanup_command here.
>>
>> OK, sounds like we have consensus on that.  Who wants to do it?
>
> Do we just need to replace all of them? If so, patch attached.
> I replaced 3 terms: recovery_end_command, recovery-end-command,
> and recoveryEndCommand.

s/recovery_end_command/restartpoint_command?

I prefer restartpoint_command over archive_cleanup_command because
not only restartpoint_command but also recovery_end_command is used
for archive cleanup.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Command to prune archive at restartpoints

[Robert Haas]

On Tue, Jun 8, 2010 at 9:45 PM, Takahiro Itagaki
 wrote:
> Robert Haas  wrote:
>
>> On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane  wrote:
>> > Andrew Dunstan  writes:
>> >> I prefer archive_cleanup_command. We should name things after their
>> >> principal function, not an implementation detail, IMNSHO.
>> >
>> > Weak preference for archive_cleanup_command here.
>>
>> OK, sounds like we have consensus on that.  Who wants to do it?
>
> Do we just need to replace all of them? If so, patch attached.
> I replaced 3 terms: recovery_end_command, recovery-end-command,
> and recoveryEndCommand.

I think we're replacing restartpoint_command, not recovery_end_command.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] [BUGS] Invalid YAML output from EXPLAIN

[Robert Haas]

On Tue, Jun 8, 2010 at 9:37 AM, Greg Sabino Mullane  wrote:
> When I get some free time, I'll make a patch to implement as much of
> the spec as we sanely can.

Saying that you'll fix it but not on any particular timetable is
basically equivalent to saying that you're not willing to fix it at
all.  We are trying to get a release out the door.  I'm not trying to
be rude, but it's frustrating to me when people object to having their
code ripped out but also won't commit to getting it fixed in a timely
fashion.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Command to prune archive at restartpoints

[Takahiro Itagaki]

Robert Haas  wrote:

> On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane  wrote:
> > Andrew Dunstan  writes:
> >> I prefer archive_cleanup_command. We should name things after their
> >> principal function, not an implementation detail, IMNSHO.
> >
> > Weak preference for archive_cleanup_command here.
> 
> OK, sounds like we have consensus on that.  Who wants to do it?

Do we just need to replace all of them? If so, patch attached.
I replaced 3 terms: recovery_end_command, recovery-end-command,
and recoveryEndCommand.


Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



archive_cleanup_command.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] How about closing some Open Items?

[Robert Haas]

On Tue, Jun 8, 2010 at 6:20 PM, Josh Berkus  wrote:
>>  If I simply start overhauling the list without explaining what I
>> perceive to be the systematic problems with it, then I will get yelled
>> at for failing to follow community process.
>
> Nonsense.  The one good thing about admin stuff in this community is
> since nobody wants to do it, you seldom get critiqued for how you did
> it.  ;-)

OK, list cleaned up.  What would be really helpful now is if someone
could try to figure out new items that have come up recently that may
need to be added to the list.

> Brownie points granted.  Although I'd think actual brownies or cookies
> would be better. ;-)

You can email me off-list for my address.  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Command to prune archive at restartpoints

[Robert Haas]

On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane  wrote:
> Andrew Dunstan  writes:
>> I prefer archive_cleanup_command. We should name things after their
>> principal function, not an implementation detail, IMNSHO.
>
> Weak preference for archive_cleanup_command here.

OK, sounds like we have consensus on that.  Who wants to do it?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Tue, Jun 8, 2010 at 6:35 PM, Tom Lane  wrote:
> Simon Riggs  writes:
>> On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote:
>>> OK, yes, I see what you're getting at now.  There are two possible
>>> ways to do freeze the tuples and keep the xmin: we can either rely on
>>> the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can
>>> additionally have a HEAP_XMIN_FROZEN bit as you propose here.  I am
>>> not sure which way is better.
>
>> Doing it at tuple level is more flexible and allows more aggressive
>> freezing. It also works better with existing tuple visibility code.
>
> I agree, relying on a page-level bit (or field) is unpleasant in a
> number of ways.
>
> But none of this accomplishes a damn thing towards the original goal,
> which was to avoid an extra disk write associated with freezing (not
> to mention an extra write for setting the transaction-committed hint
> bit).  Setting a bit is no cheaper from that standpoint than changing
> the xmin field.

Except for insert-only tables, I don't believe this is true.  If you
freeze all tuples by the time the pages are marked all-visible,
perhaps via the xmin-preserving mechanism Simon suggested, then you
can use the visibility map to skip anti-wraparound vacuum as well as
regular vacuum.  That sounds to me like it's accomplishing something.
Is it a complete solution? No.  Is it better than what we have now?
Yes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Alvaro Herrera]

Excerpts from Tom Lane's message of mar jun 08 18:35:00 -0400 2010:

> But none of this accomplishes a damn thing towards the original goal,
> which was to avoid an extra disk write associated with freezing (not
> to mention an extra write for setting the transaction-committed hint
> bit).  Setting a bit is no cheaper from that standpoint than changing
> the xmin field.

... unless the bit is outside the page itself -- so we get back to the
idea of a freeze map.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Command to prune archive at restartpoints

[Andrew Dunstan]

Tom Lane wrote:

As for the language choice, my first thought is +1 for perl over shell,
mainly because it might be directly useful to people on Windows while
shell never would be.  On the other hand, if it's possible to do a
useful one-liner in shell then let's do it that way.
  


I don't think it is, reasonably. But here is fairly minimal version that 
might suit the docs:


   use strict;
   my ($dir, $num) = @ARGV;
   foreach my $file (glob("$dir/*"))
   {
   my $name = basename($file);
   unlink $file if (-f $file && $name =~ /^[0-9A-Z]{24}$/ && $name lt 
$num);
   }
 




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] Command to prune archive at restartpoints

[Tom Lane]

Andrew Dunstan  writes:
> I prefer archive_cleanup_command. We should name things after their 
> principal function, not an implementation detail, IMNSHO.

Weak preference for archive_cleanup_command here.

> More importantly, we should include an example in the docs. I created 
> one the other day  when this was actually bothering me a bit (see 
> ).
>  
> That seemed to work ok, but maybe it's too long, and maybe people would 
> prefer a shell script to perl.

Short is good.  Maybe you could remove the logging stuff from the
example.

As for the language choice, my first thought is +1 for perl over shell,
mainly because it might be directly useful to people on Windows while
shell never would be.  On the other hand, if it's possible to do a
useful one-liner in shell then let's do it that way.

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] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Simon Riggs  writes:
> On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote:
>> OK, yes, I see what you're getting at now.  There are two possible
>> ways to do freeze the tuples and keep the xmin: we can either rely on
>> the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can
>> additionally have a HEAP_XMIN_FROZEN bit as you propose here.  I am
>> not sure which way is better.

> Doing it at tuple level is more flexible and allows more aggressive
> freezing. It also works better with existing tuple visibility code.

I agree, relying on a page-level bit (or field) is unpleasant in a
number of ways.

But none of this accomplishes a damn thing towards the original goal,
which was to avoid an extra disk write associated with freezing (not
to mention an extra write for setting the transaction-committed hint
bit).  Setting a bit is no cheaper from that standpoint than changing
the xmin 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] hot_standby = on

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  
Well, yes. But then to stop that you could just lock users out using 
pg_hba.conf, no? It just doesn't seem to be buying all that much to me. 



The main reason to turn it off is to disable a whole lot of very poorly
tested code, and thereby improve the reliability of your warm standby
server.  There might be (almost certainly are) significant performance
benefits as well.  I think it'll be at least a couple of release cycles
before any sane DBA would turn it on on standbys where he didn't
positively need it.


  


OK, then we need to say something like that. Right now we're not giving 
any guidance that I can see.


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] Command to prune archive at restartpoints

[Andrew Dunstan]

Robert Haas wrote:

On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark  wrote:
  

On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs  wrote:


On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:

  

One awkward omission in the new built-in standby mode, mainly used for
streaming replication, is that there is no easy way to delete old
archived files like you do with the %r parameter to restore_command.


Would it be better to call this "archive_cleanup_command"? That might
help people understand the need for and the use of this parameter.
  

This is bikeshedding but fwiw I like Simon's suggestion.



So, this thread is hanging out on our list of open items for 9.0.  My
personal opinion on it is that I don't really care much one way or the
other.  archive_cleanup_command does seem easier to understand, but
restartpoint_command has the advantage of describing exactly when it
gets run from a technical perspective, which might be a good thing,
too.  Since nobody's felt motivated to do anything about this for two
and a half months and we've now been through two betas with it the way
it is, I'm inclined to say we should just leave it alone.  On the
other hand, both of the people who voted in favor of changing it are
committers, and if one of them feels like putting in the effort to
change it, it won't bother me much, except that I feel it should get
done RSN.  But one way or the other we need to make a decision and get
this off the list.

  


I prefer archive_cleanup_command. We should name things after their 
principal function, not an implementation detail, IMNSHO.


More importantly, we should include an example in the docs. I created 
one the other day  when this was actually bothering me a bit (see 
). 
That seemed to work ok, but maybe it's too long, and maybe people would 
prefer a shell script to perl.


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] How about closing some Open Items?

[Josh Berkus]

> Whoa, there.  There is not any way of moving forward with this list
> without explaining why the list in its present form is not too useful.

Ah, the way I read your post was "the list is not useful therefore I
will ignore it."  From that perspective it was important for me to
respond to you lest other hackers make the same excuse to ignore the
open items list.

So, miscommunication over!

>  If I simply start overhauling the list without explaining what I
> perceive to be the systematic problems with it, then I will get yelled
> at for failing to follow community process.

Nonsense.  The one good thing about admin stuff in this community is
since nobody wants to do it, you seldom get critiqued for how you did
it.  ;-)

> So I agree with your statement that we need to improve the quality of
> the list, which is why I just spent three hours working on the items
> where I could discern a clear action item as well as doing some
> cleanup.  Like most PG hackers, I am extremely busy, which is why I
> would appreciate any help that you or any other community member would
> care to offer to help get the list cleaned up.

Yeah, I was going through the list today trying to see if stuff needed
to be removed or added, which is why I noticed the almost total lack of
movement since the 26th.

>  Failing that, or in
> addition, I would appreciate feedback on what I believe to be a
> legitimate complaint about the documentation items on the list,
> namely, that they're mostly unimportant things that should probably
> just be dropped unless or until the people who originally raised the
> issues feel like pursuing them.

Well, if something is too trivial to be worth fixing, that's a reason to
remove it.

> I am trying to solve a problem, not pick a fight.  I responded to your
> original post on this topic by dropping what I was planning to do this
> afternoon to work on this, and I'd like a few brownie points for that.

Brownie points granted.  Although I'd think actual brownies or cookies
would be better. ;-)

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] Idea for getting rid of VACUUM FREEZE on cold pages

[Simon Riggs]

On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote:
> OK, yes, I see what you're getting at now.  There are two possible
> ways to do freeze the tuples and keep the xmin: we can either rely on
> the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can
> additionally have a HEAP_XMIN_FROZEN bit as you propose here.  I am
> not sure which way is better.

Doing it at tuple level is more flexible and allows more aggressive
freezing. It also works better with existing tuple visibility code.

-- 
 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] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Tue, Jun 8, 2010 at 5:08 PM, Simon Riggs  wrote:
>> > Why not just have a separate flag for HEAP_XMIN_FROZEN, that way we can
>> > keep the xmin but also can see it is frozen?
>>
>> We could do that, but I think the point of this exercise is to reduce
>> I/O - specifically, I/O caused by anti-wraparound vacuums - and I'm
>> not clear how such a flag would help with that.
>
> Hmmm: You suggested a variant of this idea, so whatever reasoning was
> behind your suggestion would be shared here, surely?
>
> Tom has been saying we cannot freeze early because we need to keep
> xmins. I agree with that. This suggestion shows it is possible to freeze
> a tuple AND keep its xmin. So that removes the argument that we should
> freeze more aggressively (whenever we write the block) and can thus
> reduce longer term I/O costs.

OK, yes, I see what you're getting at now.  There are two possible
ways to do freeze the tuples and keep the xmin: we can either rely on
the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can
additionally have a HEAP_XMIN_FROZEN bit as you propose here.  I am
not sure which way is better.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] How about closing some Open Items?

[Robert Haas]

On Tue, Jun 8, 2010 at 5:13 PM, Josh Berkus  wrote:
> On 6/8/10 2:01 PM, Robert Haas wrote:
>> I appreciate the effort that went into making this list, but it's not
>> very useful to work off of any more.  The problem is that the set of
>> things that is listed here is pretty erratic - there are actual
>> problems that are not on this list, and there are things on this list
>> that are 100% unimportant.
>
> So?  Improve the quality of the list then.  Bashing the quality of
> another community member's efforts is not at all helpful, and certainly
> does nothing to move us towards 9.0.

Whoa, there.  There is not any way of moving forward with this list
without explaining why the list in its present form is not too useful.
 If I simply start overhauling the list without explaining what I
perceive to be the systematic problems with it, then I will get yelled
at for failing to follow community process.  On the other hand, if I
point out the problem, apparently that's bashing another community
member.  I attempted to avoid that pitfall by saying "I appreciate the
effort that went into making this list" (because I do) and I tried to
make it clear that I felt it had been useful at one point by saying
"any more".   The fact that the list is not as useful now is not
because Selena sucks (she doesn't, by the way! - Hi Selena!) but
because Selena overhauled this list on the 19th of May and cleaned it
up some more on the 26th, and it's now the 8th of June, and not enough
systematic effort has been put into keeping it up to date.  It's not
Selena's job to keep the list up to date, but it's a fact of life that
if neither she nor anyone else does, it's going to become less useful.

So I agree with your statement that we need to improve the quality of
the list, which is why I just spent three hours working on the items
where I could discern a clear action item as well as doing some
cleanup.  Like most PG hackers, I am extremely busy, which is why I
would appreciate any help that you or any other community member would
care to offer to help get the list cleaned up.  Failing that, or in
addition, I would appreciate feedback on what I believe to be a
legitimate complaint about the documentation items on the list,
namely, that they're mostly unimportant things that should probably
just be dropped unless or until the people who originally raised the
issues feel like pursuing them.

I am trying to solve a problem, not pick a fight.  I responded to your
original post on this topic by dropping what I was planning to do this
afternoon to work on this, and I'd like a few brownie points for that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] hstore ==> and deprecate =>

[David E. Wheeler]

On Jun 8, 2010, at 12:34 PM, Merlin Moncure wrote:

> hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
> hstore_in and the operator don't have to be the same, but requiring
> three letter token for the two most high traffic operations w/hstore
> seems off to me.
> 
> # is currently used for bitwise xor/geo

Not at all the same.

What about ~> ?

Best,

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] Command to prune archive at restartpoints

[Simon Riggs]

On Tue, 2010-06-08 at 17:17 -0400, Robert Haas wrote:
> On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark  wrote:
> > On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs  wrote:
> >> On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
> >>
> >>> One awkward omission in the new built-in standby mode, mainly used for
> >>> streaming replication, is that there is no easy way to delete old
> >>> archived files like you do with the %r parameter to restore_command.
> >>
> >> Would it be better to call this "archive_cleanup_command"? That might
> >> help people understand the need for and the use of this parameter.
> >
> > This is bikeshedding but fwiw I like Simon's suggestion.
> 
> So, this thread is hanging out on our list of open items for 9.0.  My
> personal opinion on it is that I don't really care much one way or the
> other.  archive_cleanup_command does seem easier to understand, but
> restartpoint_command has the advantage of describing exactly when it
> gets run from a technical perspective, which might be a good thing,
> too.  Since nobody's felt motivated to do anything about this for two
> and a half months and we've now been through two betas with it the way
> it is, I'm inclined to say we should just leave it alone.  On the
> other hand, both of the people who voted in favor of changing it are
> committers, and if one of them feels like putting in the effort to
> change it, it won't bother me much, except that I feel it should get
> done RSN.  But one way or the other we need to make a decision and get
> this off the list.

Yes, restartpoint_command is exactly correct, and I do understand it; I
just don't think anyone else will. If there's another use for a
restartpoint_command other than for clearing up an archive, then it
would be sufficient to destroy the name change idea. 

-- 
 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] How about closing some Open Items?

[David Fetter]

On Tue, Jun 08, 2010 at 03:46:06PM -0400, Tom Lane wrote:
> Josh Berkus  writes:
> > Let's get 9.0 out the door, hey?
> 
> What we actually need is some testing effort.  The lack of bug
> reports against Hot Standby, in particular, is proof positive that
> no meaningful testing is happening.  (If you think it means HS is

s/HS/any software, no matter how trivial/

> bug-free, I have a nice bridge I'd like to interest you in.)

Is it the one from Lower Manhattan to Brooklyn?  I've got all kinds of
cool ideas about what to do with it...

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] hot_standby = on

[Tom Lane]

Andrew Dunstan  writes:
> Well, yes. But then to stop that you could just lock users out using 
> pg_hba.conf, no? It just doesn't seem to be buying all that much to me. 

The main reason to turn it off is to disable a whole lot of very poorly
tested code, and thereby improve the reliability of your warm standby
server.  There might be (almost certainly are) significant performance
benefits as well.  I think it'll be at least a couple of release cycles
before any sane DBA would turn it on on standbys where he didn't
positively need it.

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] Command to prune archive at restartpoints

[Robert Haas]

On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark  wrote:
> On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs  wrote:
>> On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
>>
>>> One awkward omission in the new built-in standby mode, mainly used for
>>> streaming replication, is that there is no easy way to delete old
>>> archived files like you do with the %r parameter to restore_command.
>>
>> Would it be better to call this "archive_cleanup_command"? That might
>> help people understand the need for and the use of this parameter.
>
> This is bikeshedding but fwiw I like Simon's suggestion.

So, this thread is hanging out on our list of open items for 9.0.  My
personal opinion on it is that I don't really care much one way or the
other.  archive_cleanup_command does seem easier to understand, but
restartpoint_command has the advantage of describing exactly when it
gets run from a technical perspective, which might be a good thing,
too.  Since nobody's felt motivated to do anything about this for two
and a half months and we've now been through two betas with it the way
it is, I'm inclined to say we should just leave it alone.  On the
other hand, both of the people who voted in favor of changing it are
committers, and if one of them feels like putting in the effort to
change it, it won't bother me much, except that I feel it should get
done RSN.  But one way or the other we need to make a decision and get
this off the list.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] How about closing some Open Items?

[Josh Berkus]

On 6/8/10 2:01 PM, Robert Haas wrote:
> I appreciate the effort that went into making this list, but it's not
> very useful to work off of any more.  The problem is that the set of
> things that is listed here is pretty erratic - there are actual
> problems that are not on this list, and there are things on this list
> that are 100% unimportant.

So?  Improve the quality of the list then.  Bashing the quality of
another community member's efforts is not at all helpful, and certainly
does nothing to move us towards 9.0.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] Idea for getting rid of VACUUM FREEZE on cold pages

[Simon Riggs]

On Tue, 2010-06-08 at 16:58 -0400, Robert Haas wrote:
> On Tue, Jun 8, 2010 at 4:55 PM, Simon Riggs  wrote:
> > On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote:
> >> Jan Wieck  writes:
> >> > On 6/2/2010 3:10 PM, Alvaro Herrera wrote:
> >> >> I'd prefer a setting that would tell the system to freeze all tuples
> >> >> that fall within a safety range whenever any tuple in the page is frozen
> >> >> -- weren't you working on a patch to do this?  (was it Jeff Davis?)
> >>
> >> > I just see a lot of cost caused by this "safety range". I yet have to
> >> > see its real value, other than "feel good".
> >>
> >> Jan, you don't know what you're talking about.  I have repeatedly had
> >> cases where being able to look at xmin was critical to understanding
> >> a bug.  I *will not* hold still for a solution that effectively reduces
> >> min_freeze_age to zero.
> >
> > Recent history shows Tom's view to be the most useful one: its useful to
> > keep seeing the xmin. The last time we altered the way we set hint bits
> > we caused multiple data loss bugs doing it. We will need to debug things
> > and the WAL is always long gone (great idea though).
> >
> > Why not just have a separate flag for HEAP_XMIN_FROZEN, that way we can
> > keep the xmin but also can see it is frozen?
> 
> We could do that, but I think the point of this exercise is to reduce
> I/O - specifically, I/O caused by anti-wraparound vacuums - and I'm
> not clear how such a flag would help with that.

Hmmm: You suggested a variant of this idea, so whatever reasoning was
behind your suggestion would be shared here, surely?

Tom has been saying we cannot freeze early because we need to keep
xmins. I agree with that. This suggestion shows it is possible to freeze
a tuple AND keep its xmin. So that removes the argument that we should
freeze more aggressively (whenever we write the block) and can thus
reduce longer term I/O costs.

-- 
 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] hot_standby = on

[Robert Haas]

On Tue, Jun 8, 2010 at 4:54 PM, Andrew Dunstan  wrote:
> Robert Haas wrote:
>> On Thu, Jun 3, 2010 at 11:15 PM, Andrew Dunstan
>>  wrote:
>>> The docs don't seem to contain any discussion I could find on why one
>>> might
>>> not want hot_standby on. Maybe it's just too obvious to most people, but
>>> this seems to be a bit lacking in the docs.
>> Well, if you don't want your slave to process queries, then you
>> wouldn't turn it on, presumably.
>
> Well, yes. But then to stop that you could just lock users out using
> pg_hba.conf, no? It just doesn't seem to be buying all that much to me. It's
> not a big deal, I was just curious. There are all these new knobs to play
> with ...

Well, yeah, you could do it that way, too, but that might not be
convenient - consider a failover setup where clients try to connect to
each IP in turn.  You want the standby to refuse connections until it
becomes the master, but then start accepting them.

I'm going to remove this from the list of open items for 9.0 since, as
you say, it's not a big deal.  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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/secondary/master/slave/standby

[Simon Riggs]

On Tue, 2010-06-08 at 16:47 -0400, Robert Haas wrote:
> On Thu, May 27, 2010 at 9:22 AM, Heikki Linnakangas
>  wrote:
> > On 27/05/10 12:39, Dimitri Fontaine wrote:
> >>
> >> Greg Stark  writes:
> >>>
> >>> Fwiw I like the word "replica" but I don't see an obvious choice of
> >>> word to pair it with
> >>
> >> I guess it's replica / origin, per choice of Jan Wieck to be found in
> >> our catalogs:
> >>
> >>  http://www.postgresql.org/docs/current/static/catalog-pg-trigger.html
> >>
> >>   tgenabledchar
> >>
> >>   Controls in which session_replication_role modes the trigger fires.
> >>   O = trigger fires in "origin" and "local" modes, D = trigger is
> >>   disabled, R = trigger fires in "replica" mode, A = trigger fires
> >>   always.
> >>
> >> So that's origin/replica, master/slave, primary/standby, master/standby.
> >
> > master/standby is my favorite, and I believe we have a rough consensus on
> > that.
> >
> > I started to search/replace primary -> master, but started to have second
> > thoughts when I got to the section in the docs about standby servers:
> >
> > http://developer.postgresql.org/pgdocs/postgres/warm-standby.html
> >
> > Somehow that just doesn't sound as good after s/primary/master, the first
> > sentence in particular. I think the reason is that "master" brings to mind
> > an active connection between the master and standby, while "primary" sounds
> > more loosely-coupled.
> >
> > Perhaps we should use master/standby when discussing streaming replication,
> > and primary/standby when talking about a standby setup in general, possibly
> > using file-based log shipping. The distinction is quite vague, so we'll have
> > to document both terms as synonyms of each other.
> 
> I agree.  I think it might make sense to try to standardize on the use
> of "master" in messages (and GUC variable names) but insisting that we
> can never say "primary" in the docs would make them read very oddly, I
> think.

The reasons the two sets of terms exist is that they aren't completely
opposed. Master/slave is talking about who makes the changes and who
accepts them, whereas primary/standby is talking about who is currently
active and who is available to become active if required. Slony also
talks about origin/subscriber. SR also uses sender/receiver.

Which metaphor we use depends upon which aspect of the system we use. We
could have chosen not to introduce send/receive, but its so obvious and
natural for SR that its worth introducing new terms.

Master/standby sounds like a mixed metaphor to me and harder to
understand as a result.

-- 
 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] How about closing some Open Items?

[Robert Haas]

On Tue, Jun 8, 2010 at 1:32 PM, Josh Berkus  wrote:
> However, I haven't see much progress in the last 10 days on closing any
> of these issues, and quite a few people have moved on to 9.1 features.

I appreciate the effort that went into making this list, but it's not
very useful to work off of any more.  The problem is that the set of
things that is listed here is pretty erratic - there are actual
problems that are not on this list, and there are things on this list
that are 100% unimportant.  In particular, pretty much everything in
the docs section is crap.  Just because two people discussed something
at some point in the last six months doesn't make it an open issue.
Most of those conversations were not pursued because they were not
deemed important enough to pursuing, and I think only one of them has
an actual patch associated with it, viz:

ALTER TABLE .. DISABLE/ENABLE TRIGGER are out of date

I took a look at that patch, and I expect it's probably correct, but I
haven't actually looked at the code to verify that it's correct, so I
didn't apply it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Tue, Jun 8, 2010 at 4:55 PM, Simon Riggs  wrote:
> On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote:
>> Jan Wieck  writes:
>> > On 6/2/2010 3:10 PM, Alvaro Herrera wrote:
>> >> I'd prefer a setting that would tell the system to freeze all tuples
>> >> that fall within a safety range whenever any tuple in the page is frozen
>> >> -- weren't you working on a patch to do this?  (was it Jeff Davis?)
>>
>> > I just see a lot of cost caused by this "safety range". I yet have to
>> > see its real value, other than "feel good".
>>
>> Jan, you don't know what you're talking about.  I have repeatedly had
>> cases where being able to look at xmin was critical to understanding
>> a bug.  I *will not* hold still for a solution that effectively reduces
>> min_freeze_age to zero.
>
> Recent history shows Tom's view to be the most useful one: its useful to
> keep seeing the xmin. The last time we altered the way we set hint bits
> we caused multiple data loss bugs doing it. We will need to debug things
> and the WAL is always long gone (great idea though).
>
> Why not just have a separate flag for HEAP_XMIN_FROZEN, that way we can
> keep the xmin but also can see it is frozen?

We could do that, but I think the point of this exercise is to reduce
I/O - specifically, I/O caused by anti-wraparound vacuums - and I'm
not clear how such a flag would help with that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Simon Riggs]

On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote:
> Jan Wieck  writes:
> > On 6/2/2010 3:10 PM, Alvaro Herrera wrote:
> >> I'd prefer a setting that would tell the system to freeze all tuples
> >> that fall within a safety range whenever any tuple in the page is frozen
> >> -- weren't you working on a patch to do this?  (was it Jeff Davis?)
> 
> > I just see a lot of cost caused by this "safety range". I yet have to 
> > see its real value, other than "feel good".
> 
> Jan, you don't know what you're talking about.  I have repeatedly had
> cases where being able to look at xmin was critical to understanding
> a bug.  I *will not* hold still for a solution that effectively reduces
> min_freeze_age to zero.

Recent history shows Tom's view to be the most useful one: its useful to
keep seeing the xmin. The last time we altered the way we set hint bits
we caused multiple data loss bugs doing it. We will need to debug things
and the WAL is always long gone (great idea though).

Why not just have a separate flag for HEAP_XMIN_FROZEN, that way we can
keep the xmin but also can see it is frozen?

We already WAL-log certain flag settings, so why not this one 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] hot_standby = on

[Andrew Dunstan]

Robert Haas wrote:

On Thu, Jun 3, 2010 at 11:15 PM, Andrew Dunstan
 wrote:
  

The docs don't seem to contain any discussion I could find on why one might
not want hot_standby on. Maybe it's just too obvious to most people, but
this seems to be a bit lacking in the docs.



Well, if you don't want your slave to process queries, then you
wouldn't turn it on, presumably.

  


Well, yes. But then to stop that you could just lock users out using 
pg_hba.conf, no? It just doesn't seem to be buying all that much to me. 
It's not a big deal, I was just curious. There are all these new knobs 
to play with ...


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/secondary/master/slave/standby

[Robert Haas]

On Thu, May 27, 2010 at 9:22 AM, Heikki Linnakangas
 wrote:
> On 27/05/10 12:39, Dimitri Fontaine wrote:
>>
>> Greg Stark  writes:
>>>
>>> Fwiw I like the word "replica" but I don't see an obvious choice of
>>> word to pair it with
>>
>> I guess it's replica / origin, per choice of Jan Wieck to be found in
>> our catalogs:
>>
>>  http://www.postgresql.org/docs/current/static/catalog-pg-trigger.html
>>
>>   tgenabled    char
>>
>>   Controls in which session_replication_role modes the trigger fires.
>>   O = trigger fires in "origin" and "local" modes, D = trigger is
>>   disabled, R = trigger fires in "replica" mode, A = trigger fires
>>   always.
>>
>> So that's origin/replica, master/slave, primary/standby, master/standby.
>
> master/standby is my favorite, and I believe we have a rough consensus on
> that.
>
> I started to search/replace primary -> master, but started to have second
> thoughts when I got to the section in the docs about standby servers:
>
> http://developer.postgresql.org/pgdocs/postgres/warm-standby.html
>
> Somehow that just doesn't sound as good after s/primary/master, the first
> sentence in particular. I think the reason is that "master" brings to mind
> an active connection between the master and standby, while "primary" sounds
> more loosely-coupled.
>
> Perhaps we should use master/standby when discussing streaming replication,
> and primary/standby when talking about a standby setup in general, possibly
> using file-based log shipping. The distinction is quite vague, so we'll have
> to document both terms as synonyms of each other.

I agree.  I think it might make sense to try to standardize on the use
of "master" in messages (and GUC variable names) but insisting that we
can never say "primary" in the docs would make them read very oddly, I
think.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] hot_standby = on

[Robert Haas]

On Thu, Jun 3, 2010 at 11:15 PM, Andrew Dunstan
 wrote:
> The docs don't seem to contain any discussion I could find on why one might
> not want hot_standby on. Maybe it's just too obvious to most people, but
> this seems to be a bit lacking in the docs.

Well, if you don't want your slave to process queries, then you
wouldn't turn it on, presumably.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] hstore ==> and deprecate =>

[Michael Glaesemann]

On Jun 8, 2010, at 16:17 , Robert Haas wrote:

>> (That's enough bikeshedding for me.)
> 
> Test first, then post?  :-)

What? :) If I was productively contributing, I wouldn't be bikeshedding, now 
would I? 

Michael Glaesemann
grzm seespotcode net




-- 
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] hstore ==> and deprecate =>

[Michael Glaesemann]

On Jun 8, 2010, at 15:38 , Robert Haas wrote:

> On Tue, Jun 8, 2010 at 3:34 PM, Merlin Moncure  wrote:
>> hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
>> hstore_in and the operator don't have to be the same, but requiring
>> three letter token for the two most high traffic operations w/hstore
>> seems off to me.
>> 
>> # is currently used for bitwise xor/geo
> 
> I'm happy to do whatever the consensus is.  I thought it would be
> easier to remember if the two operators were spelled at least somewhat
> similarly, but I just work here.

Perhaps 
->
Would a colon work?

(That's enough bikeshedding for me.)

Michael Glaesemann
grzm seespotcode net




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

Re: [HACKERS] [PATCH] Add _PG_init to PL language handler documentation

[Robert Haas]

On Wed, May 26, 2010 at 11:24 PM, Joshua Tolley  wrote:
> On Wed, May 26, 2010 at 03:47:25PM -0400, Robert Haas wrote:
>> On Tue, May 25, 2010 at 4:34 AM, Jonathan Leto  wrote:
>> > This tiny doc patch adds _PG_init to the skeleton example code for a
>> > PL. The information is quite valuable to PL authors, who might miss it
>> > when it is described in the shared library documentation.
>>
>> I'm not sure it does much good to add it to the template without any
>> explanation of what it does.  What might make more sense is to add a
>> cross-reference to the section on dynamic loading, where this is
>> documented.
>
> +1. How about the attached (which, incidentally, tested successfully on my
> box, because I've managed to achieve doc building nirvana through blindly
> flailing about until it worked...)?

I've committed a doc change along these lines, but I thought your
version was a little wordy and maybe not in the best spot, so I did it
a bit differently.  Hopefully it's good - if not, I can always change
it again...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] hstore ==> and deprecate =>

[Robert Haas]

On Tue, Jun 8, 2010 at 3:56 PM, Michael Glaesemann  wrote:
> On Jun 8, 2010, at 15:38 , Robert Haas wrote:
>> On Tue, Jun 8, 2010 at 3:34 PM, Merlin Moncure  wrote:
>>> hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
>>> hstore_in and the operator don't have to be the same, but requiring
>>> three letter token for the two most high traffic operations w/hstore
>>> seems off to me.
>>>
>>> # is currently used for bitwise xor/geo
>>
>> I'm happy to do whatever the consensus is.  I thought it would be
>> easier to remember if the two operators were spelled at least somewhat
>> similarly, but I just work here.
>
> Perhaps
> ->

That's already in use to mean something else.

> Would a colon work?

Nope.

ERROR:  syntax error at or near ":"

> (That's enough bikeshedding for me.)

Test first, then post?  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] hstore ==> and deprecate =>

[Andrew Dunstan]

Tom Lane wrote:

Pavel Stehule  writes:
  

p.s. I hope so in 9.1 will be complete hstore module marked as deprecated



Really?  And replaced with what?  And why wouldn't the replacement use
the same operator names?


  


Yeah. I'll be looking for throats to cut if that happens. I have several 
apps that use it extensively. If anything, I want it brought into core - 
it has wide ranging usefulness.


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] How about closing some Open Items?

[Tom Lane]

Josh Berkus  writes:
> Let's get 9.0 out the door, hey?

What we actually need is some testing effort.  The lack of bug reports
against Hot Standby, in particular, is proof positive that no meaningful
testing is happening.  (If you think it means HS is bug-free, I have a
nice bridge I'd like to interest you in.)

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] hstore ==> and deprecate =>

[Tom Lane]

Pavel Stehule  writes:
> p.s. I hope so in 9.1 will be complete hstore module marked as deprecated

Really?  And replaced with what?  And why wouldn't the replacement use
the same operator names?

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] hstore ==> and deprecate =>

[Pavel Stehule]

2010/6/8 Merlin Moncure :
> On Tue, Jun 8, 2010 at 3:07 PM, Robert Haas  wrote:
>> I believe that the consensus was mostly in favor of deprecating => as
>> an operator name, with the intent to abolish it completely in a future
>> release.  Attached is a patch to implement ==> as an alternative
>> operator name for hstore, and to make the backend throw a warning when
>> => is used as an operator name.
>>
>> One wart is that => is used not only as a SQL-level operator, but also
>> by hstore_in() when interpreting hstore-type literals, and by
>> hstore_out() when generating them.  My gut feeling is that we should
>> leave this part alone and only muck with the SQL operator, but perhaps
>> someone will care to argue the point.
>>
>> http://archives.postgresql.org/pgsql-hackers/2010-05/msg01501.php
>
> hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
> hstore_in and the operator don't have to be the same, but requiring
> three letter token for the two most high traffic operations w/hstore
> seems off to me.

I am for Robert's proposal - ===> is one char longer - but
significantly readable

Regards

Pavel

p.s. I hope so in 9.1 will be complete hstore module marked as deprecated


>
> # is currently used for bitwise xor/geo
>
> merlin
>
> --
> 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] hstore ==> and deprecate =>

[Robert Haas]

On Tue, Jun 8, 2010 at 3:34 PM, Merlin Moncure  wrote:
> On Tue, Jun 8, 2010 at 3:07 PM, Robert Haas  wrote:
>> I believe that the consensus was mostly in favor of deprecating => as
>> an operator name, with the intent to abolish it completely in a future
>> release.  Attached is a patch to implement ==> as an alternative
>> operator name for hstore, and to make the backend throw a warning when
>> => is used as an operator name.
>>
>> One wart is that => is used not only as a SQL-level operator, but also
>> by hstore_in() when interpreting hstore-type literals, and by
>> hstore_out() when generating them.  My gut feeling is that we should
>> leave this part alone and only muck with the SQL operator, but perhaps
>> someone will care to argue the point.
>>
>> http://archives.postgresql.org/pgsql-hackers/2010-05/msg01501.php
>
> hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
> hstore_in and the operator don't have to be the same, but requiring
> three letter token for the two most high traffic operations w/hstore
> seems off to me.
>
> # is currently used for bitwise xor/geo

I'm happy to do whatever the consensus is.  I thought it would be
easier to remember if the two operators were spelled at least somewhat
similarly, but I just work here.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] _bt_parent_deletion_safe() isn't safe

[Tom Lane]

I wrote:
> I realized this while thinking about Jeff Amiel's report here:
> http://archives.postgresql.org/pgsql-general/2010-06/msg00351.php
> I can't prove that this is what's causing his crashes, but it could
> produce the symptom he's reporting.

Actually, no it can't: the case I'm envisioning should lead to throwing
this error:

elog(ERROR, "failed to delete rightmost child %u of block %u in index 
\"%s\"",
 target, parent, RelationGetRelationName(rel));

a bit further up.  That's annoying enough, but it's not a PANIC.

A search of the archives produces no evidence that anyone has ever
reported the "failed to delete rightmost child" error from the field.
So while I still think this is a bug that needs to be fixed, it may
be lower priority than I thought initially.

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] hstore ==> and deprecate =>

[Merlin Moncure]

On Tue, Jun 8, 2010 at 3:07 PM, Robert Haas  wrote:
> I believe that the consensus was mostly in favor of deprecating => as
> an operator name, with the intent to abolish it completely in a future
> release.  Attached is a patch to implement ==> as an alternative
> operator name for hstore, and to make the backend throw a warning when
> => is used as an operator name.
>
> One wart is that => is used not only as a SQL-level operator, but also
> by hstore_in() when interpreting hstore-type literals, and by
> hstore_out() when generating them.  My gut feeling is that we should
> leave this part alone and only muck with the SQL operator, but perhaps
> someone will care to argue the point.
>
> http://archives.postgresql.org/pgsql-hackers/2010-05/msg01501.php

hm.  any chance of a  shorter operator, like '#'?  I kinda agree that
hstore_in and the operator don't have to be the same, but requiring
three letter token for the two most high traffic operations w/hstore
seems off to me.

# is currently used for bitwise xor/geo

merlin

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

[HACKERS] hstore ==> and deprecate =>

[Robert Haas]

I believe that the consensus was mostly in favor of deprecating => as
an operator name, with the intent to abolish it completely in a future
release.  Attached is a patch to implement ==> as an alternative
operator name for hstore, and to make the backend throw a warning when
=> is used as an operator name.

One wart is that => is used not only as a SQL-level operator, but also
by hstore_in() when interpreting hstore-type literals, and by
hstore_out() when generating them.  My gut feeling is that we should
leave this part alone and only muck with the SQL operator, but perhaps
someone will care to argue the point.

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01501.php

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


hstore_op.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

[HACKERS] _bt_parent_deletion_safe() isn't safe

[Tom Lane]

The btree page deletion logic has a restriction that it cannot delete
the rightmost child page of any non-leaf btree page (see nbtree/README
for explanations).  This is checked by _bt_parent_deletion_safe(),
which claims

 * Note: it's OK to release page locks after checking, because a safe
 * deletion can't become unsafe due to concurrent activity.  A non-rightmost
 * page cannot become rightmost unless there's a concurrent page deletion,
 * but only VACUUM does page deletion and we only allow one VACUUM on an index
 * at a time.  An only child could acquire a sibling (of the same parent) only
 * by being split ... but that would make it a non-rightmost child so the
 * deletion is still safe.

This analysis missed a case, though.  What if an insertion into some
nearby leaf page causes a split, and the resulting insertion into the
parent page causes it to split, and we choose a split point just after
the downlink for the page that VACUUM is trying to delete?  That will
leave the deletion target as the rightmost child, and we're screwed.

I realized this while thinking about Jeff Amiel's report here:
http://archives.postgresql.org/pgsql-general/2010-06/msg00351.php
I can't prove that this is what's causing his crashes, but it could
produce the symptom he's reporting.  And it'd also explain the
observation that the crash doesn't recur when autovacuum tries again,
since at that time it'll see the page as a rightmost child and not try
to delete it.  Maybe the reason he's seeing it repeatedly is that in his
installation the deletions lag behind insertions at about the right rate
for the problem case to occur.

Right at the moment I'm not seeing a fix other than to have page
deletion hold lock on the parent page till it's done.  That's unpleasant
from a concurrency standpoint.  Anybody see a better way?

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] Parameters of GiST indexes

[Robert Haas]

On Tue, Jun 8, 2010 at 8:53 AM, Greg Stark  wrote:
> On Tue, Jun 8, 2010 at 12:40 PM, Robert Haas  wrote:
>> On Sun, Jun 6, 2010 at 10:24 AM, Alexander Korotkov
>>  wrote:
>>> I think that such parameters don't have optimal value for all the cases;
>>
>> What makes you think that?
>
> Actually the whole signature method is a bit of a crock.

My point was just that we're unlikely to make any changes to the code
without, say, some performance results.  I suspect we're unlikely to
make the exact change being asked for in any case, but the point is
that if you think something isn't right, it's good to back up that
position with some data

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

[HACKERS] How about closing some Open Items?

[Josh Berkus]

All,

Selena (and others) have done a terrific job on the Open Items list for
9.0.:

http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items

However, I haven't see much progress in the last 10 days on closing any
of these issues, and quite a few people have moved on to 9.1 features.

If you are the author of a feature for 9.0, please examine the open
items list ASAP and close any issues associated with your feature!  If
you are a hacker, please do what you can to fix bugs.

Let's get 9.0 out the door, hey?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] PlPython bug in 9.0/8.4.4

[Tom Lane]

Teodor Sigaev  writes:
> The way to reproduce:

Well, of course plpython's error handling is fundamentally brain dead.

The immediate problem here seems to be that the PLy_error_in_progress
struct is stored in a memory context that's been cleared by the time
that control gets to the place in PLy_procedure_call() where we try to
re-throw it.  It might be that we need to copy the ErrorData into a
less short-lived context than the one that's current when
PLy_spi_prepare is called.  Or maybe PLy_function_handler just needs
to think a bit harder about where it has to check PLy_error_in_progress
and/or where it's safe to shut down the SPI call.  Or all of the above.

Really I'd like to see that whole mess thrown out and rewritten.
pltcl and plperl got rid of static error state years ago, because of
essentially this same type of risk.

> 8.4.4 haves essentially the same bug.

I'm sure it goes all the way back.

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] Functional dependencies and GROUP BY

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Hm.  The problem with that is that one of the benefits we'd like to get
> from this is an efficiency win: the generated plan ought to only group
> by the PK, not uselessly sort/group by everything in the row.  I suppose
> we could have the planner reverse-engineer its way to that, but it seems
> awfully slow and clunky to add on the extra columns and then reason our
> way to removing them again.

That's certainly a good point.  Another issue that I realized when
thinking about this again- if someone wanted to *drop* a column that's
part of a PK (since it turned out to not be necessary, for example), and
then wanted to recreate the rule based on what was stored in the
catalog, they wouldn't be able to without modifying it, and that's
certainly be annoying too.

Guess my 2c would be for creating the dependency.  I really dislike the
idea of the rule just all of a sudden breaking.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Functional dependencies and GROUP BY

[Tom Lane]

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Perhaps the correct fix would be to mark stored query trees as having a
>> dependency on the index, so that dropping the index/constraint would
>> force a drop of the rule too.

> Alternatively, we could rewrite the rule (not unlike what we do for
> "SELECT *") to actually add on the other implicitly grouped-by columns..
> I don't know if that's better or worse than creating a dependency,
> since if the constraint were dropped/changed, people might expect the
> rule's output to change.

Hm.  The problem with that is that one of the benefits we'd like to get
from this is an efficiency win: the generated plan ought to only group
by the PK, not uselessly sort/group by everything in the row.  I suppose
we could have the planner reverse-engineer its way to that, but it seems
awfully slow and clunky to add on the extra columns and then reason our
way to removing them again.

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] Functional dependencies and GROUP BY

[Greg Stark]

On Tue, Jun 8, 2010 at 3:21 PM, Tom Lane  wrote:

> The question is why bother to recognize *any* cases of this form.
> I find it really semantically ugly to have the parser effectively
> doing one deduction of this form when the main engine for that type
> of deduction is elsewhere; so unless there is a really good argument
> why we have to do this case (and NOT "it was pretty easy"), I don't
> want to do it.

Well it does appear to be there:

4.18.11 Known functional dependencies in the result of a 
...
If AP is an equality AND-component of the  simply
contained in the  and one comparand of AP is a column
reference CR, and the other comparand of AP is a , then let
CRC be the counterpart of CR in R. {}   {CRC} is a known functional
dependency in R, where {} denotes the empty set.

NOTE 43 — An SQL-implementation may also choose to recognize {}
{CRC} as a known functional dependency if the other comparand is a
deterministic expression containing no column references.
...


Since Peter's not eager to implement the whole section -- which does
seem pretty baroque -- it's up to us to draw the line where we stop
coding and declare it good enough. I think we're all agreed that
grouping by a pk is clearly the most important case. It may be
important to get some other cases just so that the PK property carries
through other clauses such as joins and group bys. But ultimately the
only thing stopping us from implementing the whole thing is our
threshold of pain for writing and maintaining the extra code.

-- 
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] Functional dependencies and GROUP BY

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Perhaps the correct fix would be to mark stored query trees as having a
> dependency on the index, so that dropping the index/constraint would
> force a drop of the rule too.  Just pushing the check to plan time, as
> I suggested yesterday, isn't a very nice fix because it would result
> in the rule unexpectedly starting to fail at execution.

Alternatively, we could rewrite the rule (not unlike what we do for
"SELECT *") to actually add on the other implicitly grouped-by columns..
I don't know if that's better or worse than creating a dependency,
since if the constraint were dropped/changed, people might expect the
rule's output to change.  Of course, as you mention, the alternative
would really be for the rule to just start failing..  Still, if I wanted
to change the constraint, it'd be alot nicer to just be able to change
it and, presuming I'm just adding a column to it or doing some other
change which wouldn't invalidate the rule, not have to drop/recreate
the rule.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Functional dependencies and GROUP BY

[Marko Tiikkaja]

On 6/8/10 5:21 PM +0300, Tom Lane wrote:

Peter Eisentraut  writes:

On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:

In addition, what if y is implicitly a constant? For example,

SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;



Yes, as I said, my implementation is incomplete in the sense that it
only recognizes some functional dependencies.  To recognize the sort of
thing you show,  you would need some kind of complex deduction or proof
engine, and that doesn't seem worthwhile, at least for me, at this
point.


The question is why bother to recognize *any* cases of this form.
I find it really semantically ugly to have the parser effectively
doing one deduction of this form when the main engine for that type
of deduction is elsewhere; so unless there is a really good argument
why we have to do this case (and NOT "it was pretty easy"), I don't
want to do it.

As far as I recall, at least 99% of the user requests for this type
of behavior, maybe 100%, would be satisfied by recognizing the
group-by-primary-key case.  So I think we should do that and be happy.


+1


Regards,
Marko Tiikkaja

--
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] Functional dependencies and GROUP BY

[Tom Lane]

Peter Eisentraut  writes:
> On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
>> In addition, what if y is implicitly a constant? For example,
>> 
>> SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;

> Yes, as I said, my implementation is incomplete in the sense that it
> only recognizes some functional dependencies.  To recognize the sort of
> thing you show,  you would need some kind of complex deduction or proof
> engine, and that doesn't seem worthwhile, at least for me, at this
> point.

The question is why bother to recognize *any* cases of this form.
I find it really semantically ugly to have the parser effectively
doing one deduction of this form when the main engine for that type
of deduction is elsewhere; so unless there is a really good argument
why we have to do this case (and NOT "it was pretty easy"), I don't
want to do it.

As far as I recall, at least 99% of the user requests for this type
of behavior, maybe 100%, would be satisfied by recognizing the
group-by-primary-key case.  So I think we should do that and be happy.

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] Functional dependencies and GROUP BY

[Greg Stark]

On Tue, Jun 8, 2010 at 3:05 PM, Tom Lane  wrote:
> Well, no, any cached plan will get invalidated if the index goes away.
> The big problem with this implementation is that you could create a
> *rule* (eg a view) containing a query whose validity depends on the
> existence of an index.  Dropping the index will not cause the rule
> to be invalidated.

Hm, I was incorrectly thinking of this as analogous to the cases of
plans that could be optimized based on the existence of a constraint.
For example removing columns from a sort key because they're unique.
But this is different because not just the plan but the validity of
the query itself is dependent on the constraint.


-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Jan Wieck]

On 6/8/2010 8:27 AM, Greg Stark wrote:

On Tue, Jun 8, 2010 at 2:26 AM, Stephen Frost  wrote:

Just an off-the-wall thought, but, would it be possible to have a tool
which read WAL backwards and compared entries in the WAL against entries
on disk?  I realize that you'd only see one version of a particular
block and then have to skip any updates which are earlier than it, but
it seems like you could cover a pretty large chunk of the recent changes
to the database using this approach..


I assume you mean back out the changes incrementally until you find a
full_page_write and see if it matches? And continue comparing with
full_page_writes once per checkpoint? I don't think the WAL has enough
information to replay backwards though. For example vacuum cleanup
records just list the tids to remove. They don't have the contents to
replace there.


You can't back out changes. WAL does not contain before images.


Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

--
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] Functional dependencies and GROUP BY

[Tom Lane]

Greg Stark  writes:
> On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane  wrote:
>> The main objection to this is the same one I've had all along: it makes
>> the syntactic validity of a query dependent on what indexes exist for
>> the table.  At minimum, that means that enforcing the check at parse
>> time is the Wrong Thing.

> It also needs to ensure that the plan is invalidated if the constraint
> is dropped, which I assume amounts to the same thing.

Well, no, any cached plan will get invalidated if the index goes away.
The big problem with this implementation is that you could create a
*rule* (eg a view) containing a query whose validity depends on the
existence of an index.  Dropping the index will not cause the rule
to be invalidated.

Perhaps the correct fix would be to mark stored query trees as having a
dependency on the index, so that dropping the index/constraint would
force a drop of the rule too.  Just pushing the check to plan time, as
I suggested yesterday, isn't a very nice fix because it would result
in the rule unexpectedly starting to fail at execution.

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] [BUGS] Invalid YAML output from EXPLAIN

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> But YAML is not human-readable.  There are human-readable subsets of
> it, but the general serializers do not produce them, and specific
> serializers are difficult to get right (as we've seen).

No, it *is* human readable. Indeed, that's one of the things that 
differentiates it from JSON: readability is the main goal, whereas 
JSON's goals are different. The readablity necessarily makes 
the parsing rules more complex, but that's the implicit tradeoff.
(Did you miss the part where the other Greg is sending explain 
plans via email?)

> What does your parser do with this (equivalent but shorter) 
> YAML output?
>
> - Plan: !!map
>&0 Node Type: Sort
>&1 Startup Cost: 4449.30
>&2 Total Cost: 4496.80
>&3 Plan Rows: &5 19000
>&4 Plan Width: &6 268
>   Sort Key: ["zip"]
>Plans: !!seq
>  - *0: Seq Scan
>Parent Relationship: Outer
>Relation Name: &7 customers
>Alias: *7
>*1: 0.00
>*2: 726.00
>*3: *5
>*4: *6
>Filter: (customerid > 1000)

But we're not using alias nodes (nor would we ever want to), so I'm not 
sure what the point of your contrived example is. That's shorter, but 
certainly not easier to read by human /or/ machine.

> Looking at the spec, it's rather difficult to come up with a readable
> subset which can parsed easily and is general in the sense that it can
> express empty strings, strings with embedded newlines, and so on.
> YAML's rules for dealing with whitespace are fairly complex, but are
> probably needed to get a more compact notation than JSON.

I'll state that both embedded newlines and column names and values with 
funny characters like '*' and '|' are rare events, and the great majority 
of things you'll see in an explain plan are plain ol' ASCII, in which 
YAML produces a very good representation. But you are right that we need 
to make sure we are handling the whitespace correctly.

When I get some free time, I'll make a patch to implement as much of 
the spec as we sanely can. As I said before, I don't think we need to 
strive for putting everything we possibly can into "plain scalar" 
objects, as we can cover 99% of the cases easy enough and fall back to 
'when in doubt, quote' for the rest.

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

iEYEAREDAAYFAkwOR2gACgkQvJuQZxSWSshkVwCgzqunUkawnBRGwOV8msQPudN8
UmkAoM1wz+wFCEz34CMJ7VH+S7T3mc43
=8OjG
-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] Idea for getting rid of VACUUM FREEZE on cold pages

[Stephen Frost]

* Greg Stark (gsst...@mit.edu) wrote:
> I assume you mean back out the changes incrementally until you find a
> full_page_write and see if it matches?

To be honest, you're already assuming I know more about how this all
works than I do. :)  The gist of my thought was simply- we write out
block changes to the WAL, including data in many cases.  If we were to
look at the very end of the WAL, at the last piece of data written
there, and the data files have supposedly been flushed, then what's in
the WAL at that point should match what's in the data files, right?  If
it doesn't, that'd be bad.

> And continue comparing with
> full_page_writes once per checkpoint?

If we could only do it when there's a full page write, then perhaps that
would work as well, but I thought we tracked them at a lower level.  In
any case, the idea is the same- compare what's in WAL to what's supposed
to be on disk, and alarm whenever there's a clear error.

> I don't think the WAL has enough
> information to replay backwards though. For example vacuum cleanup
> records just list the tids to remove. They don't have the contents to
> replace there.

Right, you couldn't actually move the database backwards in time using
this tool (because we only write out new data, we don't write out what
was in that block/page before the write)- that isn't the idea or intent.
It would just be a tool that someone could run against a database where
they've detected corruption (or, I dunno, more frequently, to perhaps
catch corruption faster?), to see if the problem is a PG bug or a
hardware/kernel/etc issue.  In fact, if you could somehow do this
against a backup that's been taken using pg_start_backup/pg_stop_backup,
that would be pretty awesome.

I know that if such a tool existed, I'd be happy to run it as part of my
regular backup routines- I *always* have all the WALs from my last
backup to my next backup (and typically farther back than that, eg: if I
run full backups weekly, I'll have 4 full backups + all 4 weeks of WALs,
to be able to replay back to any point in the month..).

The big question that I have is- would this actually be productive?
Would it actually be able to catch hardware corruption or help at all
with PG bugs?  Those are the things I'm not really sure about.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Parameters of GiST indexes

[Greg Stark]

On Tue, Jun 8, 2010 at 12:40 PM, Robert Haas  wrote:
> On Sun, Jun 6, 2010 at 10:24 AM, Alexander Korotkov
>  wrote:
>> I think that such parameters don't have optimal value for all the cases;
>
> What makes you think that?

Actually the whole signature method is a bit of a crock. I posted
about this previously as a tangent on a thread about bloom filters but
I can't find it now.

In short the "signature" is actually a degenerate bloom filter with
one hash function. Sizing bloom filters and choosing the number of
hash functions is a solved problem and while we don't necessarily have
all the input variables needed to do it optimally it's clear that a
single hash function is virtually never going to be ideal and these
filters are very small leading to high false positive rates.

To improve matters I think you need to know the number of distinct
values that are going to appear in an array. That's something the user
would have to provide or we would have to calculate due an ANALYZE
run. Then we can select the ideal number of hash functions and size
the array to target a chosen false-positive rate.

To *really* improve matters the index structure has to be adjusted to
allow for variable size arrays. Then we can use large filters at
higher index levels and smaller filters at lower levels where they
hold fewer values. I don't see how to make that work offhand though
unless we rescan the heap tuples when we grow the arrays.



-- 
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] Functional dependencies and GROUP BY

[Greg Stark]

On Tue, Jun 8, 2010 at 4:16 AM, Tom Lane  wrote:
> Peter Eisentraut  writes:
>> I have developed a patch that partially implements the "functional
>> dependency" feature that allows some columns to be omitted from the
>> GROUP BY clause if it can be shown that the columns are functionally
>> dependent on the columns in the group by clause and therefore guaranteed
>> to be unique per group.
>
> The main objection to this is the same one I've had all along: it makes
> the syntactic validity of a query dependent on what indexes exist for
> the table.  At minimum, that means that enforcing the check at parse
> time is the Wrong Thing.

It also needs to ensure that the plan is invalidated if the constraint
is dropped, which I assume amounts to the same thing.




-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Greg Stark]

On Tue, Jun 8, 2010 at 2:26 AM, Stephen Frost  wrote:
> Just an off-the-wall thought, but, would it be possible to have a tool
> which read WAL backwards and compared entries in the WAL against entries
> on disk?  I realize that you'd only see one version of a particular
> block and then have to skip any updates which are earlier than it, but
> it seems like you could cover a pretty large chunk of the recent changes
> to the database using this approach..

I assume you mean back out the changes incrementally until you find a
full_page_write and see if it matches? And continue comparing with
full_page_writes once per checkpoint? I don't think the WAL has enough
information to replay backwards though. For example vacuum cleanup
records just list the tids to remove. They don't have the contents to
replace there.



-- 
greg

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

[HACKERS] Git: Unable to get pack file

[Leonardo F]

Hi,


I tried getting the source using:

git clone http://git.postgresql.org/git/postgresql.git postgresql-git

but after a while (252MB) I always get:

[...]
Getting pack 61e1395a5bdacda95de5432123a0f8124fff05e6
which contains 476418893d3a2f366f47dbe4ce6d7522cc427545
error: Unable to get pack file 
http://git.postgresql.org/git/postgresql.git/objects/pack/pack-61e1395a5bdacda95de5432123a0f8124fff05e6.pack
couldn't connect to host
error: Unable to find 476418893d3a2f366f47dbe4ce6d7522cc427545 under 
http://git.postgresql.org/git/postgresql.git
Cannot obtain needed blob 476418893d3a2f366f47dbe4ce6d7522cc427545
while processing commit ee6ddc7575ab1ce16d8f4eb2cdbcc525d43a6437.
fatal: Fetch failed.



I'm behind proxy/firewall, so I used the "http://"; protocol.

wget 
http://git.postgresql.org/git/postgresql.git/objects/pack/pack-61e1395a5bdacda95de5432123a0f8124fff05e6.pack

works... 

What am I doing wrong?




-- 
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] Parameters of GiST indexes

[Robert Haas]

On Sun, Jun 6, 2010 at 10:24 AM, Alexander Korotkov
 wrote:
> I think that such parameters don't have optimal value for all the cases;

What makes you think that?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] pg_stat_transaction patch

[Joel Jacobson]

Hi Takahiro,

Here is an updated version of the patch.

Thanks Magnus H for the help :)

1.4: Ported to head. Updated tests. Removed pg_stat_report.


2010/5/25 Takahiro Itagaki 

>
> Joel Jacobson  wrote:
>
> > I applied all the changes on 9.0beta manually and then it compiled
> without
> > any assertion failures.
> >
> > I also changed the oids to a different unused range, since the ones I
> used
> > before had been taken in 9.0beta1.
>
> Thanks, but you still need to test your patch:
>
>  - You need to check your patch with "make check", because it requires
>   adjustments in "rule" test; Your pg_stat_transaction_function is the
>   longest name in the system catalog.
>
>  - You need to configure postgres with --enable-cassert to enable internal
>   varidations. The attached test case failed with the following TRAP.
> TRAP: FailedAssertion("!(entry->trans == ((void *)0))", File: "pgstat.c",
> Line: 715)
> TRAP: FailedAssertion("!(tabstat->trans == trans)", File: "pgstat.c", Line:
> 1758)
>
> > I suspect it is because get_tabstat_entry for some reason returns NULL,
> in
> > for example pg_stat_get_transaction_tuples_inserted(PG_FUNCTION_ARGS).
> >
> > Does the function look valid? If you can find the error in it, the other
> > functions probably have the same problem.
>
> For the above trap, we can see the comment:
>/* Shouldn't have any pending transaction-dependent counts */
> We don't expect to read stats entries during transactions. I'm not sure
> whether accessing transitional stats during transaction is safe or not.
>
> We might need to go other directions, for example:
>  - Use "session stats" instead "transaction stats". You can see the same
>information in difference of counters between before and after the
>transaction.
>  - Export pgBufferUsage instead of relation counters. They are
>buffer counters for all relations, but we can obviously export
>them because they are just plain variables.
>
> Regards,
> ---
> Takahiro Itagaki
> NTT Open Source Software Center
>
>
>


-- 
Best regards,

Joel Jacobson
Glue Finance

E: j...@gluefinance.com
T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box  549
114 11  Stockholm
Sweden

Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden


pg_stat_transaction-1.4.tar.gz
Description: GNU Zip compressed 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] Re: [RFC][PATCH]: CRC32 is limiting at COPY/CTAS/INSERT ... SELECT + speeding it up

[Pierre C]

The linux kernel also uses it when it's availabe, see e.g.  
http://tomoyo.sourceforge.jp/cgi-bin/lxr/source/arch/x86/crypto/crc32c-intel.c


If you guys are interested I have a Core i7 here, could run a little  
benchmark.


--
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] Functional dependencies and GROUP BY

[Rob Wultsch]

On Mon, Jun 7, 2010 at 6:41 PM, Stephen Frost  wrote:
> * Peter Eisentraut (pete...@gmx.net) wrote:
>> This is frequently requested by MySQL converts (and possibly others).
>
> I'd certainly love to see it- but let's not confuse people by implying
> that it would actually act the way MySQL does.  It wouldn't, because
> what MySQL does is alot closer to 'distinct on' and is patently insane
> to boot.  Again, I'd *love* to see this be done in PG, but when we
> document it and tell people about it, *please* don't say it's similar in
> any way to MySQL's "oh, we'll just pick a random value from the columns
> that aren't group'd on" implementation.


Preface: I work as a MySQL DBA (yeah, yeah, laugh it up...).

It has been my experience that the vast majority of the time when a
MySQL users make use of the "fine feature" which allows them to use
unaggregated columns which is not present in the GROUP BY clause in an
aggregating query they have made an error because they do not
understand GROUP BY. I have found this lack of understanding to be
very wide spread across the MySQL developer and *DBA* communities. I
also would really hesitate to compare this useful feature to the *fine
feature* present in MySQL. Due to a long standing bug
(http://bugs.mysql.com/bug.php?id=8510) it really is not possible to
get MySQL to behave sanely. It is my impression that many programs of
significant size that interact with MySQL have errors because of this
issue and it would be good to not claim to have made Postgres
compatible.

That said, I imagine if this feature could make it into the Postgres
tree it would be very useful.

Would I be correct in assuming that while this feature would make
query planning more expensive, it would also often decrease the cost
of execution?

Best,

Rob Wultsch
wult...@gmail.com

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

[HACKERS] PlPython bug in 9.0/8.4.4

[Teodor Sigaev]

The way to reproduce:

CREATE OR REPLACE FUNCTION foobar(a integer[])
RETURNS SETOF int8 AS
$$
def getplan(name, query, args):
if SD.has_key(name):
plpy.warning("Using cached plan %s" % name)
return SD[name]

plpy.warning("Prepare plan %s" % name);
plan = plpy.prepare(query, args)
plpy.warning("Plan %s prepared" % name);
SD[name] = plan
return plan

for s in a:
userPlan = getplan('fooplan', 'select 1', []);
rrr =  plpy.execute(plan, [])  # variable plan isn't defined!
ret = s
yield ret
$$
LANGUAGE 'plpythonu';

On 9.0beta2 (instead of correct message ": global 
name 'plan' is not defined"):

postgres=#  select foobar('{1,2,3}');
WARNING:  Prepare plan fooplan
CONTEXT:  PL/Python function "foobar"
WARNING:  PL/Python: plpy.SPIError: unrecognized error in PLy_spi_prepare
CONTEXT:  PL/Python function "foobar"
ERROR:  error fetching next item from iterator
CONTEXT:  PL/Python function "foobar"
postgres=#  select foobar('{1,2,3}');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>

Log:
TRAP: FailedAssertion("!(edata->elevel == 20)", File: "elog.c", Line: 1280)

8.4.4 haves essentially the same bug.

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
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] Functional dependencies and GROUP BY

[Peter Eisentraut]

On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
> > Also, when a column is compared with a constant, it can appear
> > ungrouped:
> >
> > SELECT x, y FROM tab2 WHERE y = 5 GROUP BY x;
> 
> I don't see why it should be allowed. I see the insist that y must be
> unique value so it is ok to be ungrouped but the point of discussion
> is far from that; Semantically y is not grouping key.

I'm not sure what your argument is.  If y is uniquely determined within
each group, then it's OK for it to be ungrouped.  What other criteria do
you have in mind for determining that instead?  It looks like you are
going by aesthetics. ;-)

> In addition, what if y is implicitly a constant? For example,
> 
> SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;
> 
> or there should be more complicated example including JOIN cases. I
> don't believe we can detect all of such cases. If the simple case is
> allowed, users don't understand why the complicated case doesn't allow
> sometimes. So it'll not be consistent.

Yes, as I said, my implementation is incomplete in the sense that it
only recognizes some functional dependencies.  To recognize the sort of
thing you show,  you would need some kind of complex deduction or proof
engine, and that doesn't seem worthwhile, at least for me, at this
point.


-- 
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] [BUGS] Invalid YAML output from EXPLAIN

[Florian Weimer]

* Greg Smith:

> Florian Weimer wrote:
>> It has been claimed before that YAML is a superset of JSON, so why
>> can't the YAML folks use the existing JSON output instead?
>>   
>
> Because JSON just crosses the line where it feels like there's so much
> markup that people expect a tool is necessary to read it, which has
> always been the issue with XML too--bad human readability.

But YAML is not human-readable.  There are human-readable subsets of
it, but the general serializers do not produce them, and specific
serializers are difficult to get right (as we've seen).

> EXPLAIN (FORMAT YAML) SELECT * FROM customers WHERE customerid>1000
> ORDER BY zip;
> QUERY PLAN
> -
> - Plan:+
> Node Type: Sort+
> Startup Cost: 4449.30  +
> Total Cost: 4496.80+
> Plan Rows: 19000   +
> Plan Width: 268+
> Sort Key:  +
>   - zip+
> Plans: +
>   - Node Type: Seq Scan+
> Parent Relationship: Outer +
> Relation Name: customers   +
> Alias: customers   +
> Startup Cost: 0.00 +
> Total Cost: 726.00 +
> Plan Rows: 19000   +
> Plan Width: 268+
> Filter: (customerid > 1000)

What does your parser do with this (equivalent but shorter) YAML
output?

- Plan: !!map
&0 Node Type: Sort
&1 Startup Cost: 4449.30
&2 Total Cost: 4496.80
&3 Plan Rows: &5 19000
&4 Plan Width: &6 268
Sort Key: ["zip"]
Plans: !!seq
  - *0: Seq Scan
Parent Relationship: Outer
Relation Name: &7 customers
Alias: *7
*1: 0.00
*2: 726.00
*3: *5
*4: *6
Filter: (customerid > 1000)

Looking at the spec, it's rather difficult to come up with a readable
subset which can parsed easily and is general in the sense that it can
express empty strings, strings with embedded newlines, and so on.
YAML's rules for dealing with whitespace are fairly complex, but are
probably needed to get a more compact notation than JSON.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

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