### Re: [HACKERS] [v9.2] Fix Leaky View Problem

```Hi Robert,

If you can make this work, I think it could be a pretty sweet plannner
optimization even apart from the implications for security views.
Consider a query of this form:

A LEFT JOIN B LEFT JOIN C

where B is a view defined as:

B1 JOIN B2 JOIN B3 LEFT JOIN B4 LEFT JOIN B5

Now let's suppose that from_collapse_limit/join_collapse_limit are set
low enough that we decline to fold these subproblems together.  If
there happens to be a qual B.x = 1, where B.x is really B1.x, then the
generated plan sucks, because it will basically lose the ability to
filter B1 early, very possibly on, say, a unique index.  Or at least a
highly selective index.

I tried to reproduce the scenario with enough small from/join_collapse_limit
(typically 1), but it allows to push down qualifiers into the least scan plan.

E.g)
mytest=# SET from_collapse_limit = 1;
mytest=# SET join_collapse_limit = 1;
mytest=# CREATE VIEW B AS SELECT B1.* FROM B1,B2,B3 WHERE B1.x = B2.x
AND B2.x = B3.x;
mytest=# EXPLAIN SELECT * FROM A,B,C WHERE A.x=B.x AND B.x=C.x AND f_leak(B.y);
QUERY PLAN

Merge Join  (cost=381.80..9597.97 rows=586624 width=108)
Merge Cond: (a.x = b1.x)
-  Merge Join  (cost=170.85..290.46 rows=7564 width=72)
Merge Cond: (a.x = c.x)
-  Sort  (cost=85.43..88.50 rows=1230 width=36)
Sort Key: a.x
-  Seq Scan on a  (cost=0.00..22.30 rows=1230 width=36)
-  Sort  (cost=85.43..88.50 rows=1230 width=36)
Sort Key: c.x
-  Seq Scan on c  (cost=0.00..22.30 rows=1230 width=36)
-  Materialize  (cost=210.95..528.56 rows=15510 width=44)
-  Merge Join  (cost=210.95..489.78 rows=15510 width=44)
Merge Cond: (b1.x = b3.x)
-  Merge Join  (cost=125.52..165.40 rows=2522 width=40)
Merge Cond: (b1.x = b2.x)
-  Sort  (cost=40.09..41.12 rows=410 width=36)
Sort Key: b1.x
-  Seq Scan on b1  (cost=0.00..22.30
rows=410 width=36)
Filter: f_leak(y)
-  Sort  (cost=85.43..88.50 rows=1230 width=4)
Sort Key: b2.x
-  Seq Scan on b2  (cost=0.00..22.30
rows=1230 width=4)
-  Sort  (cost=85.43..88.50 rows=1230 width=4)
Sort Key: b3.x
-  Seq Scan on b3  (cost=0.00..22.30 rows=1230 width=4)
(25 rows)

In this example, f_leak() takes an argument come from B1 table within B view,
and it was correctly distributed to SeqScan on B1.

From perspective of the code, the *_collapse_limit affects the contents of
joinlist being returned from deconstruct_jointree() whether its sub-portion is
flatten, or not.
However, the qualifiers are distributed on distribute_restrictinfo_to_rels() to
RelOptInfo based on its dependency of relations being referenced by
arguments. Thus, the above f_leak() was distributed to B1, not B, because
its arguments come from only B1.

I agree with the following approach to tackle this problem in 100%.
However, I'm unclear how from/join_collapse_limit affects to keep
sub-queries unflatten. It seems to me it is determined based on
the result of is_simple_subquery().

1. Let quals percolate down into subqueries.
2. Add the notion of a security view, which prevents flattening and
disables the optimization of patch #1
3. Add the notion of a leakproof function, which can benefit from the
optimization of #1 even when the view involved is a security view as
introduced in #2

Thanks,

2011/10/11 Robert Haas robertmh...@gmail.com:
On Mon, Oct 10, 2011 at 4:28 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
I agreed. We have been on the standpoint that tries to prevent
leakable functions to reference a portion of join-tree being already
flatten, however, it has been a tough work.
It seems to me it is much simple approach that enables to push
down only non-leaky functions into inside of sub-queries.

An idea is to add a hack on distribute_qual_to_rels() to relocate
a qualifier into inside of the sub-query, when it references only
a particular sub-query being come from a security view, and
when the sub-query satisfies is_simple_subquery(), for example.

If you can make this work, I think it could be a pretty sweet plannner
optimization even apart from the implications for security views.
Consider a query of this form:

A LEFT JOIN B LEFT JOIN C

where B is a view defined as:

B1 JOIN B2 JOIN B3 LEFT JOIN B4 LEFT JOIN B5

Now let's suppose that from_collapse_limit/join_collapse_limit are set
low enough that we decline to fold these subproblems together.  If
there happens to be a qual B.x = 1, where B.x is really B1.x, then the
generated plan sucks, because it will basically lose ```

### Re: [HACKERS] Pushing ScalarArrayOpExpr support into the btree index AM

```Noah Misch n...@leadboat.com writes:
On Sat, Oct 15, 2011 at 02:58:45PM -0400, Tom Lane wrote:
[algorithm for a regular index scan satisfying key IN (...)]

Sounds sensible.  The algorithm applies to more than ScalarArrayOpExpr; is it
not the ability to handle an OR'ed list of ScanKey instead of an AND'ed one?

No, because it's restricted to all the elements having the same operator
and same index column; it's not a generic OR.

regards, tom lane

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

```

### [HACKERS] proposal: set GUC variables for single query

```Hi,

this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated. By setting them back I mean respecting the
previously set values, regardless of their source (set in run-time,
per-role settings, postgresql.conf settings).

An example of where this would be useful: an application maintains a
persistent connection to the database and answers requests for data from
a bunch of clients. Each connected client has a preferred timezone and
would like to get results in that timezone. Currently the application
has to either sprinkle each query with AT TIME ZONE or wrap the queries
in BEGIN; SET LOCAL TIMEZONE ..; query; COMMIT. It gets more complex
when things like pgbouncer come into play.

Another example is a one-off query that should use a different
statement_timeout than the server has configured or a REINDEX command
that would like to use more maintenance_work_mem.

It mostly falls into the realm of syntax sugar, but as more than one
person felt it's a good idea, I thought I'd float it around here.

I poked a little bit at the grammar to see where could it fit and didn't
have much success of doing it without a new reserved keyword. Supposing
the idea gets some traction, any suggestions for the syntax?

Cheers,
Jan

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes:
this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated.

Doesn't SET LOCAL cover this use-case pretty well already?

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] LIMITing number of results in a VIEW with global variables

```On Oct15, 2011, at 14:52 , Thomas Girault wrote:
Alternatively, we could also set the alpha value before the query :

SELECT set_alpha(0.1); SELECT age, young(age) FROM employees WHERE
young(age);

That's certainly much safer.

I would be very interested to know if there is smarter way to set global
variables.

The closest thing to global variables are GUC settings. These can be
set globally in postgres.conf, per user and/or per-database via
ALTER ROLE/DATABSE ... [IN DATABASE ...], per session with SET, per
function via CREATE FUNCTION ... SET and finally per subtransaction
with SET LOCAL. Modules can add their own GUC settings - you should be
able to find an example in one of the modules in contrib/

trigram module contains something very similar. Just don't try to
modify the value mid-query.

I can sort the results in the view 'sorted_employees' according to
value MU of a fuzzy predicate thanks to fuzzy2bool cast function.

CREATE OR REPLACE VIEW sorted_employees AS
SELECT *, get_mu() as mu
FROM employees
ORDER BY mu DESC;

Are you aware that an ORDER BY clause in a VIEW is only going to work
if you do SELECT .. .FROM view.

I It really the first time I am using views, I didn't know that !

Hm, I think I didn't explain that to well, so to avoid giving you a false
impression here's another try.

A SELECT without an ORDER BY clause attached to the *outmost* level never
guarantees any particular ordering of the result, nor any particular
relationship between the ordering of the SELECT's data sources and the
ordering of the SELECT's result. The only exception are SELECT's of the
form select ... from view where view has an ORDER BY attached to
the outmost level. From that, it follows that an ORDER BY in views used
in SELECTs more complex than the above is usually useless.

Things are different for views that combine ORDER BY and LIMIT, of course.
Then, not only the order of the view's results changes, but also it's output
set. Which of course affects every statement which uses the view.

best regards,
Florian Pflug

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```On 16 October 2011 16:44, Jan Urbański wulc...@wulczer.org wrote:
Hi,

this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated. By setting them back I mean respecting the
previously set values, regardless of their source (set in run-time,
per-role settings, postgresql.conf settings).

An example of where this would be useful: an application maintains a
persistent connection to the database and answers requests for data from
a bunch of clients. Each connected client has a preferred timezone and
would like to get results in that timezone. Currently the application
has to either sprinkle each query with AT TIME ZONE or wrap the queries
in BEGIN; SET LOCAL TIMEZONE ..; query; COMMIT. It gets more complex
when things like pgbouncer come into play.

Another example is a one-off query that should use a different
statement_timeout than the server has configured or a REINDEX command
that would like to use more maintenance_work_mem.

It mostly falls into the realm of syntax sugar, but as more than one
person felt it's a good idea, I thought I'd float it around here.

I poked a little bit at the grammar to see where could it fit and didn't
have much success of doing it without a new reserved keyword. Supposing
the idea gets some traction, any suggestions for the syntax?

--
Thom Brown
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] proposal: set GUC variables for single query

```On 16/10/11 17:49, Tom Lane wrote:
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes:
this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated.

Doesn't SET LOCAL cover this use-case pretty well already?

It does to a certain degree. If you have a bunch of statements in a
transaction and want to execute one of them with a different timezone
setting, you have to do the SET/RESET dance. In theory you should also
first grab the current value to set it back afterwards, in case someone
else did SET LOCAL before you, but I'll admin that's far-fetched.

The main use case would be apps running behing pgbouncer and using
statement pooling, and plain convenience.

I'd find it useful myself, but for now I'm making do with SET LOCAL and
it works fine. I'm bringing it up because it appears in the TODO created
at the PL Summit:

* Further discussion of per-statement config parameters for things
like timezone - Jan Urbanski

Tryin' to do my bit and all ;)

Jan

--
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] Pushing ScalarArrayOpExpr support into the btree index AM

```Florian Pflug f...@phlo.org writes:
On Oct15, 2011, at 20:58 , Tom Lane wrote:
So, at least as far as btrees are concerned, it seems like I implemented
the ScalarArrayOpExpr logic at the wrong level and it ought to be pushed
down into the index AM.  The above rules seem pretty btree-specific, so
I don't think that we ought to have the main executor doing any of this.
I envision doing this by marking btree as supporting ScalarArrayOpExpr
scankeys directly, so that the executor does nothing special with them,
and the planner treats them the same as regular scalar indexquals.

Hm, would this make it possible to teach the array GIN ops to also handle
ScalarArrayOpExpr?

Hmm, maybe.  In principle the index AM can always do this at least as
efficiently as the executor can, and maybe there's actual wins to be had
in GIST and GIN.  So another route to getting rid of the executor-level
support would be to implement ScalarArrayOpExpr in all the AMs.  I'm not
personally volunteering to do that though.

ARRAY[\$1] @ \$2 AND \$1 = ANY(\$2)
into an (inlineable) SQL function to make it use a (btree) index if
\$1 is a scalar-values field (and \$1 constant array) and a GIN index if \$2
is a GIN-indexed array-values field (and \$2 a constant array). Which of
course sucks from an efficiency POV.

That doesn't seem like the same thing at all, because the indexed column
is on different sides of the expression in the two cases.  The situation
that I'm worried about is indexedcolumn op ANY(arrayconstant), and
what you're bringing up is constant op ANY(indexedarraycolumn).  To
fit the latter into the existing opclass infrastructure, we'd have to
somehow teach the planner that constant op ANY(indexedarraycolumn)
is interchangeable with indexedarraycolumn @ constant, for pairs of
operators where that's indeed the case.  Seems like that'd be a lot
messier than the use-case warrants.

regards, tom lane

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

```

### [HACKERS] (patch) regression diffs on collate.linux.utf8 test

```On master, I see a minor test error (at least on my machine) as well as
a diff. Patch attached.

Regards,
Jeff Davis
*** a/src/test/regress/expected/collate.linux.utf8.out
--- b/src/test/regress/expected/collate.linux.utf8.out
***
*** 395,401  SELECT relname FROM pg_class WHERE relname ~* '^abc';
(0 rows)

-- to_char
! SET lc_time TO 'tr_TR';
SELECT to_char(date '2010-04-01', 'DD TMMON ');
to_char
-
--- 395,401
(0 rows)

-- to_char
! SET lc_time TO 'tr_TR.UTF-8';
SELECT to_char(date '2010-04-01', 'DD TMMON ');
to_char
-
***
*** 967,972  CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
--- 967,973
ERROR:  parameter lc_ctype must be specified
CREATE COLLATION testx (locale = 'nonsense'); -- fail
ERROR:  could not create locale nonsense: No such file or directory
+ DETAIL:  The operating system could not find any locale data for the locale name nonsense.
CREATE COLLATION test4 FROM nonsense;
ERROR:  collation nonsense for encoding UTF8 does not exist
CREATE COLLATION test5 FROM test0;
*** a/src/test/regress/sql/collate.linux.utf8.sql
--- b/src/test/regress/sql/collate.linux.utf8.sql
***
*** 146,152  SELECT relname FROM pg_class WHERE relname ~* '^abc';

-- to_char

! SET lc_time TO 'tr_TR';
SELECT to_char(date '2010-04-01', 'DD TMMON ');
SELECT to_char(date '2010-04-01', 'DD TMMON ' COLLATE tr_TR);

--- 146,152

-- to_char

! SET lc_time TO 'tr_TR.UTF-8';
SELECT to_char(date '2010-04-01', 'DD TMMON ');
SELECT to_char(date '2010-04-01', 'DD TMMON ' COLLATE tr_TR);

--
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 for new feature: Buffer Cache Hibernation

```On Fri, Oct 14, 2011 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Right.  I think this one falls into my class #2, ie, we have no idea how
to implement it usefully.  Doesn't (necessarily) mean that the core
concept is without merit.

Hm. given that we have an implementation I wouldn't say we have *no*
clue.  But there are certainly some parts we don't have consensus yet
on. But then working code sometimes trumps a lack of absolute
consensus.

But just for the sake of argument I'm not sure that the implementation
of dumping the current contents of the buffer cache is actually
optimal. It doesn't handle resizing the buffer cache after a restart
for example which I think would be a significant case. There could be
other buffer cache algorithm parameters users might change -- though I
don't think we really have any currently.

If we had --to take it to an extreme-- a record of every buffer
request prior to the shutdown then we could replay that log virtually
with the new buffer cache size and know what buffers the new buffer
cache size would have had in it.

I'm not sure if there's any way to gather that data efficiently, and
if we could if there's any way to bound the amount of data we would
have to retain to anything less than nigh-infinite volumes, and if we
could if there's any way to limit that has to be replayed on restart.
But my point is that there may be other more general options than
snapshotting the actual buffer cache of the system shutting down.

--
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] Underspecified window queries in regression tests

```On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
We could hack around this by adding more columns to the result so that
an index-only scan doesn't work.  But I wonder whether it wouldn't be
smarter to add ORDER BY clauses to the window function calls.  I've been
known to argue against adding just-in-case ORDER BYs to the regression
tests in the past; but these cases bother me more because a plan change
will not just rearrange the result rows but change their contents,
making it really difficult to verify that nothing's seriously wrong.

I'm not sure if it applies to this case but I recall I was recently
running queries on Oracle that included window functions and it
wouldn't even let me run them without ORDER BY clauses in the window
definition. I don't know if it cleverly determines that the ORDER BY
will change the results or if Oracle just requires ORDER BY on all
window definitions or what.

--
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] Pushing ScalarArrayOpExpr support into the btree index AM

```On Oct16, 2011, at 19:09 , Tom Lane wrote:
Florian Pflug f...@phlo.org writes:
On Oct15, 2011, at 20:58 , Tom Lane wrote:
So, at least as far as btrees are concerned, it seems like I implemented
the ScalarArrayOpExpr logic at the wrong level and it ought to be pushed
down into the index AM.  The above rules seem pretty btree-specific, so
I don't think that we ought to have the main executor doing any of this.
I envision doing this by marking btree as supporting ScalarArrayOpExpr
scankeys directly, so that the executor does nothing special with them,
and the planner treats them the same as regular scalar indexquals.

Hm, would this make it possible to teach the array GIN ops to also handle
ScalarArrayOpExpr?

Hmm, maybe.  In principle the index AM can always do this at least as
efficiently as the executor can, and maybe there's actual wins to be had
in GIST and GIN.  So another route to getting rid of the executor-level
support would be to implement ScalarArrayOpExpr in all the AMs.  I'm not
personally volunteering to do that though.

Hm, that sounds like we ought to leave the existing infrastructure in
the main executor in place until we have GIN and GIST support.

ARRAY[\$1] @ \$2 AND \$1 = ANY(\$2)
into an (inlineable) SQL function to make it use a (btree) index if
\$1 is a scalar-values field (and \$1 constant array) and a GIN index if \$2
is a GIN-indexed array-values field (and \$2 a constant array). Which of
course sucks from an efficiency POV.

That doesn't seem like the same thing at all, because the indexed column
is on different sides of the expression in the two cases.  The situation
that I'm worried about is indexedcolumn op ANY(arrayconstant), and
what you're bringing up is constant op ANY(indexedarraycolumn).

Hm, true

To fit the latter into the existing opclass infrastructure, we'd have to
somehow teach the planner that constant op ANY(indexedarraycolumn)
is interchangeable with indexedarraycolumn @ constant, for pairs of
operators where that's indeed the case.  Seems like that'd be a lot
messier than the use-case warrants.

That exactly was what convinced me previously that there's no easy way
the planner, need to know about these equivalences.

Couldn't we teach the main executor to push a ScalarArrayOpExpr down
into the index AM if the operator belongs to the index's opclass, one
side is indexed, and the other is constant?

best regards,
Florian Pflug

--
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 for new feature: Buffer Cache Hibernation

```Greg Stark st...@mit.edu writes:
On Fri, Oct 14, 2011 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Right.  I think this one falls into my class #2, ie, we have no idea how
to implement it usefully.  Doesn't (necessarily) mean that the core
concept is without merit.

Hm. given that we have an implementation I wouldn't say we have *no*
clue.  But there are certainly some parts we don't have consensus yet
on. But then working code sometimes trumps a lack of absolute
consensus.

In this context working means shows a significant performance
benefit, and IIRC we don't have a demonstration of that.  Anyway this
was all discussed back in May.

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] Pushing ScalarArrayOpExpr support into the btree index AM

```Florian Pflug f...@phlo.org writes:
On Oct16, 2011, at 19:09 , Tom Lane wrote:
That doesn't seem like the same thing at all, because the indexed column
is on different sides of the expression in the two cases.  The situation
that I'm worried about is indexedcolumn op ANY(arrayconstant), and
what you're bringing up is constant op ANY(indexedarraycolumn).

Couldn't we teach the main executor to push a ScalarArrayOpExpr down
into the index AM if the operator belongs to the index's opclass, one
side is indexed, and the other is constant?

Well, no, unless you're proposing to somehow implement the constant op
ANY(indexedarraycolumn) case in all the AMs.  I don't see any practical
way to do it in btree, for one.

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] Underspecified window queries in regression tests

```On Oct16, 2011, at 20:04 , Greg Stark wrote:
On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
We could hack around this by adding more columns to the result so that
an index-only scan doesn't work.  But I wonder whether it wouldn't be
smarter to add ORDER BY clauses to the window function calls.  I've been
known to argue against adding just-in-case ORDER BYs to the regression
tests in the past; but these cases bother me more because a plan change
will not just rearrange the result rows but change their contents,
making it really difficult to verify that nothing's seriously wrong.

I'm not sure if it applies to this case but I recall I was recently
running queries on Oracle that included window functions and it
wouldn't even let me run them without ORDER BY clauses in the window
definition. I don't know if it cleverly determines that the ORDER BY
will change the results or if Oracle just requires ORDER BY on all
window definitions or what.

I was about to point out that whether or not ORDER BY is required probably
ought to depend on whether the window function acts on the frame, or the
whole partition. And that oracle quite likely knows that for the individual
window functions while we don't.

But, actually, our documentation states in 3.5, Window Functions, that

By default, if ORDER BY is supplied then the frame consists of all rows
from the start of the partition up through the current row, plus any
following rows that are equal to the current row according to the ORDER BY
clause. When ORDER BY is omitted the default frame consists of all rows
in the partition.

Either I'm confused, or that doesn't match the observed regression test
failure.

I did a few experiments. Assume that table d contains three rows with
v=1, v=2 and v=3.

This case seems to work (the frame is always the whole partition, even
though the frame_clause says between unbounded preceding and current row):

postgres=# select array_agg(v) over (range between unbounded preceding
and current row) from d;
array_agg
---
{1,2,3}
{1,2,3}
{1,2,3}

Once one adds an ORDER BY, the frame_clause is taken into account, so
that works too:

postgres=# select array_agg(v) over (order by v range between unbounded
preceding
and current row) from d;
array_agg
---
{1}
{1,2}
{1,2,3}

But some frame clauses (row 1 preceding, for example) have an effect despite
there being no ORDER BY, like here:

postgres=# select array_agg(v) over (rows 1 preceding) from d;
array_agg
---
{1}
{1,2}
{2,3}

ISTM that we probably should ignore frame clauses, unless there's an ORDER BY.
Or maybe even throw an error?

best regards,
Florian Pflug

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```Jan Urbański wulc...@wulczer.org writes:
this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated. By setting them back I mean respecting the
previously set values, regardless of their source (set in run-time,
per-role settings, postgresql.conf settings).

+1 on the use case, allowing to do that in the statement itself would be
a nice convenience.

It mostly falls into the realm of syntax sugar, but as more than one
person felt it's a good idea, I thought I'd float it around here.

I poked a little bit at the grammar to see where could it fit and didn't
have much success of doing it without a new reserved keyword. Supposing
the idea gets some traction, any suggestions for the syntax?

I think it would fit quite well within our extending of the WITH syntax.

WITH
work_mem = '1GB',
timezone = 'Europe/Amsterdam',
foo AS (
SELECT something
)
SELECT toplevel FROM foo;

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] (patch) regression diffs on collate.linux.utf8 test

```Jeff Davis pg...@j-davis.com writes:
On master, I see a minor test error (at least on my machine) as well as
a diff. Patch attached.

Hmm, yeah, I forgot to fix this regression test when I added that DETAIL
line.  However, I don't see the need for fooling with the lc_time value?

regards, tom lane

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```Dimitri Fontaine dimi...@2ndquadrant.fr writes:
I think it would fit quite well within our extending of the WITH syntax.

WITH
work_mem = '1GB',
timezone = 'Europe/Amsterdam',
foo AS (
SELECT something
)
SELECT toplevel FROM foo;

That looks pretty non-future-proof to me.  WITH is a SQL-standard
syntax, it's not an extension that we control.

regards, tom lane

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```Tom Lane t...@sss.pgh.pa.us writes:
That looks pretty non-future-proof to me.  WITH is a SQL-standard
syntax, it's not an extension that we control.

Now that you mention it, the following might actually already work:

WITH settings AS (
SELECT set_config('timezone', 'Europe/Amsterdam', t),
set_config('work_mem', '1 GB', t)
),
foo AS (
SELECT …
)
INSERT INTO bar SELECT * FROM foo;

So maybe what we need is to only change the is_local parameter to the
function set_config() so that we can have the setting last for only the
current statement?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] proposal: set GUC variables for single query

```Dimitri Fontaine dimi...@2ndquadrant.fr writes:
Now that you mention it, the following might actually already work:

WITH settings AS (
SELECT set_config('timezone', 'Europe/Amsterdam', t),
set_config('work_mem', '1 GB', t)
),
foo AS (
SELECT â¦
)
INSERT INTO bar SELECT * FROM foo;

Only for small values of work ... you won't be able to affect planner
settings that way, nor can you assume that that WITH item is executed
before all else.  See recent thread pointing out that setting values
mid-query is unsafe.

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: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

```On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote:

The first thing caught my eye in existing GiST code is idea of
subtype_float. float8 has limited precision and can't respresent, for
example, varlena values good enough. Even if we have large int8 value
we can loose lower bits, but data distribution can be so that these
bits are valuable. Wouldn't it better to have function like
subtype_diff_float which returns difference between two values of
subtype as an float? Using of such function could make penalty more
sensible to even small difference between values, and accordingly more
relevant.

I started implementing subtype_diff, and I noticed that it requires
defining an extra function for each range type. Previously, the numeric
types could just use a cast, which was convenient for user-defined range
types.

If you have any other ideas to make that cleaner, please let me know.
Otherwise I'll just finish implementing subtype_diff.

Regards,
Jeff Davis

--
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] Underspecified window queries in regression tests

```Hitoshi Harada umi.tan...@gmail.com writes:
2011/10/15 Tom Lane t...@sss.pgh.pa.us:
I can't recall whether there was some good reason for underspecifying
these test queries.  It looks like all the problematic ones were added in
commit ec4be2ee6827b6bd85e0813c7a8993cfbb0e6fa7 Extend the set of frame
options supported for window functions, which means it was either me or
Hitoshi-san who wrote them that way, but memory is not serving this
afternoon.

I don't remember if I wrote that part or not, but I like to add
explicit ORDER BY to the test cases. It doesn't appear that some
reason stopped us to specify it. So +1 for adding the clauses.

I looked at this more closely and realized that the reason for doing it
like that was to test window frames defined using ROWS rather than
RANGE.  If we fully specify the window function's input ordering then
there's no very interesting distinction between the two, because no rows
will have any peers.  So adding ORDER BY would in fact reduce the scope
of the tests.

At this point I'm inclined to leave it alone.  Maybe we could think of
some other test cases (perhaps using some other function than SUM) which
would both exercise the difference between RANGE and ROWS mode, and not
be sensitive to the detailed input ordering.  But I doubt it's really
worth the trouble.

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] Underspecified window queries in regression tests

```Florian Pflug f...@phlo.org writes:
But some frame clauses (row 1 preceding, for example) have an effect despite
there being no ORDER BY, like here:

Yeah, why did you expect differently?  Without ORDER BY, all rows are
peers in the frame ordering, so there's no way for a RANGE spec to
select less than the whole partition.  But with ROWS, you can select
less than that.

In general it's not that hard to create nondeterministic window-function
queries, since the SQL standard doesn't require you to specify a unique
ordering for the window function's input rows.  Even in RANGE mode there
are plenty of functions that are sensitive to the exact ordering, eg
first_value/last_value.  I guess the committee felt that locking this
down would restrict the feature too much.

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] Underspecified window queries in regression tests

```On Oct17, 2011, at 00:14 , Tom Lane wrote:
Florian Pflug f...@phlo.org writes:
But some frame clauses (row 1 preceding, for example) have an effect despite
there being no ORDER BY, like here:

Yeah, why did you expect differently?  Without ORDER BY, all rows are
peers in the frame ordering, so there's no way for a RANGE spec to
select less than the whole partition.  But with ROWS, you can select
less than that.

I was confused by

When an aggregate function is used as a window function, it aggregates over
the rows within the current row's window frame. [3.5, Window Functions].

combined with the part I quoted before, which was

By default, if ORDER BY is supplied then the frame consists of all rows
from the start of the partition up through the current row, plus any
following rows that are equal to the current row according to the ORDER BY
clause. When ORDER BY is omitted the default frame consists of all rows
in the partition. [9.19, Window Functions, Last Paragraph]

But, reading those parts again, I realize the it says When ORDER BY is omitted
the *default* frame consists ... , and that the second quote is followed
by a footnote which says

There are options to define the window frame in other ways, but this tutorial
does not cover them. See Section 4.2.8 for details. [3.5, Window Functions]

So it was just me being thick. Sorry for the noise.

best regards,
Florian Pflug

--
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] Underspecified window queries in regression tests

```Florian Pflug f...@phlo.org writes:
... reading those parts again, I realize the it says When ORDER BY is omitted
the *default* frame consists ... , and that the second quote is followed
by a footnote which says

There are options to define the window frame in other ways, but this
tutorial
does not cover them. See Section 4.2.8 for details. [3.5, Window Functions]

So it was just me being thick. Sorry for the noise.

Hmm.  Maybe the use of a footnote there is too subtle, and we should
instead have that text in-line (probably in parentheses)?  Or we could
use a note, but that's probably too much emphasis.

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] Underspecified window queries in regression tests

```2011/10/17 Tom Lane t...@sss.pgh.pa.us:
2011/10/15 Tom Lane t...@sss.pgh.pa.us:
I can't recall whether there was some good reason for underspecifying
these test queries.  It looks like all the problematic ones were added in
commit ec4be2ee6827b6bd85e0813c7a8993cfbb0e6fa7 Extend the set of frame
options supported for window functions, which means it was either me or
Hitoshi-san who wrote them that way, but memory is not serving this
afternoon.

I don't remember if I wrote that part or not, but I like to add
explicit ORDER BY to the test cases. It doesn't appear that some
reason stopped us to specify it. So +1 for adding the clauses.

I looked at this more closely and realized that the reason for doing it
like that was to test window frames defined using ROWS rather than
RANGE.  If we fully specify the window function's input ordering then
there's no very interesting distinction between the two, because no rows
will have any peers.  So adding ORDER BY would in fact reduce the scope
of the tests.

At this point I'm inclined to leave it alone.  Maybe we could think of
some other test cases (perhaps using some other function than SUM) which
would both exercise the difference between RANGE and ROWS mode, and not
be sensitive to the detailed input ordering.  But I doubt it's really
worth the trouble.

Ah, you mentioned about ORDER BY in window specification (OVER
clause). I thought it was query's ORDER BY. Yes, it affects in RANGE
case, and we don't have rich frame support of RANGE (like n PRECEDING
...) so the case ORDER BY affects result is limited. Agree with
leaving it alone.

Regards,
--

--
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] Adding CORRESPONDING to Set Operations

```CORRESPONDING clause take 2

After realizing that modifying prepunion.c to include a custom subquery
is not easy(incomprehensible to me) as it sounds and turning into a
hassle after making several uninformed changes, I decided to go with
modifying analyze.c.

The incomprehensible part is constructing a custom subquery as a
SubqueryScan.

Anyway I managed to implement the clause as a Subquery in analyze.c.

In the method transformSetOperationTree, if the node is a setoperation and
contains a corresponding clause, i.e. CORRESPONDING, or CORRESPONDING
BY(columns...),
we determine the common column names. Column ordering in select statements
are not important to the CORRESPONDING. With the common column names
in hand, we create a RangeSubselect node accordingly and replace the original
statement op-larg with the new RangeSubselect. RangeSubselect in turn has the
original op-larg as a from clause. We do the same to op-rarg too.

There were no changes done in prepunion.c

There are documentation changes and one regression test in the patch.

Best Regards,

Kerem KAT
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1225,1230
--- 1225,1233
primaryEXCEPT/primary
/indexterm
indexterm zone=queries-union
+primaryCORRESPONDING/primary
+   /indexterm
+   indexterm zone=queries-union
primaryset union/primary
/indexterm
indexterm zone=queries-union
***
*** 1241,1249
The results of two queries can be combined using the set operations
union, intersection, and difference.  The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
--- 1244,1252
The results of two queries can be combined using the set operations
union, intersection, and difference.  The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
***
*** 1283,1288
--- 1286,1299
/para

para
+ literalCORRESPONDING/ returns all columns that are in both replaceablequery1/ and replaceablequery2/ with the same name.
+   /para
+
+   para
+ literalCORRESPONDING BY/ returns all columns in the column list that are also in both replaceablequery1/ and replaceablequery2/ with the same name.
+   /para
+
+   para
In order to calculate the union, intersection, or difference of two
queries, the two queries must be quoteunion compatible/quote,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***
*** 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
--- 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( replaceable class=PARAMETERexpression/replaceable ) ] ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***
*** 2507,2512
--- 2507,2513
```

### Re: [HACKERS] Underspecified window queries in regression tests

```2011/10/17 Greg Stark st...@mit.edu:
On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
We could hack around this by adding more columns to the result so that
an index-only scan doesn't work.  But I wonder whether it wouldn't be
smarter to add ORDER BY clauses to the window function calls.  I've been
known to argue against adding just-in-case ORDER BYs to the regression
tests in the past; but these cases bother me more because a plan change
will not just rearrange the result rows but change their contents,
making it really difficult to verify that nothing's seriously wrong.

I'm not sure if it applies to this case but I recall I was recently
running queries on Oracle that included window functions and it
wouldn't even let me run them without ORDER BY clauses in the window
definition. I don't know if it cleverly determines that the ORDER BY
will change the results or if Oracle just requires ORDER BY on all
window definitions or what.

AFAIK, the current standard doesn't tell clearly if all/some window
functions require ORDER BY clause in window specifications. Some
window functions like rank and row_number is meaningless if it is
omitted, so some implementation doesn't allow it omitted. And I
believe Oracle implemented it before the standard, so that'd be why
details are different from spec. We designed it per spec and omitting
the clause doesn't violate any part of the standard.

Regards,
--

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Now that you mention it, the following might actually already work:

WITH settings AS (
SELECT set_config('timezone', 'Europe/Amsterdam', t),
set_config('work_mem', '1 GB', t)
),
foo AS (
SELECT …
)
INSERT INTO bar SELECT * FROM foo;

Only for small values of work ... you won't be able to affect planner
settings that way, nor can you assume that that WITH item is executed
before all else.  See recent thread pointing out that setting values
mid-query is unsafe.

I previously floated the idea of using a new keyword, possibly LET,
for this, like this:

LET var = value [, ...] IN query

I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes.  I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] proposal: set GUC variables for single query

```Robert Haas robertmh...@gmail.com writes:
I previously floated the idea of using a new keyword, possibly LET,
for this, like this:

LET var = value [, ...] IN query

I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes.  I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.

Well, syntax aside, the real issue here is that GUC doesn't have
any notion of a statement-lifespan setting, and adding one would require
considerable logical complexity, depending on exactly what you wanted to
define as a statement.  I don't think an adequate case has been
made that SET LOCAL is insufficient.

regards, tom lane

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

```

### Re: [HACKERS] proposal: set GUC variables for single query

```

On 10/16/2011 08:59 PM, Tom Lane wrote:

Robert Haasrobertmh...@gmail.com  writes:

I previously floated the idea of using a new keyword, possibly LET,
for this, like this:
LET var = value [, ...] IN query
I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes.  I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.

Well, syntax aside, the real issue here is that GUC doesn't have
any notion of a statement-lifespan setting, and adding one would require
considerable logical complexity, depending on exactly what you wanted to
define as a statement.  I don't think an adequate case has been
made that SET LOCAL is insufficient.

I agree. But if we are going to go there I vastly prefer Robert's
suggestion of a separate syntactical structure. Mixing this up with WITH
would just be an awful mess, and cause endless confusion.

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] proposal: set GUC variables for single query

```On Sun, Oct 16, 2011 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
I previously floated the idea of using a new keyword, possibly LET,
for this, like this:

LET var = value [, ...] IN query

I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes.  I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.

Well, syntax aside, the real issue here is that GUC doesn't have
any notion of a statement-lifespan setting, and adding one would require
considerable logical complexity, depending on exactly what you wanted to
define as a statement.  I don't think an adequate case has been
made that SET LOCAL is insufficient.

when the feature gets used?  I suspect the latter, which is no
different from anything else we have.  We do already have at least one
other case that seems similar to me: you can apply a setting using
ALTER FUNCTION .. SET; the new value is applied when you enter the
function and restored on exit.  I'd imagine that this would have
similar semantics.  In terms of what qualifies as a statement, I would
rather imagine that it would only be worthwhile to apply this to
queries rather than fooling around with utility statements.  I mean,
it would be nice if it Just Worked Anywhere, but that's likely to be a
lot more work (and grammar conflicts) than we want to deal with.

Anyway, the judgement of whether or not SET LOCAL is sufficient is in
the end a value judgement, and I'm not going to pretend that my
opinion is superior to all others.  My personal experience, however,
is that I've never used or wanted SET LOCAL, but I've wanted a
single-statement equivalent a few times.  So for me personally, having
this in lieu of SET LOCAL would be an improvement from a usability
perspective.  YMMV, of course.

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

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

```

```On Fri, Oct 14, 2011 at 11:12 AM, Robert Haas robertmh...@gmail.com wrote:
On Wed, Oct 12, 2011 at 10:20 PM, Josh Kupershmidt schmi...@gmail.com wrote:
On the third hand, Josh's previous batch of changes to clean up
psql's behavior in this area are clearly a huge improvement: you can
now display the comment for nearly anything by running the appropriate
\dfoo command for whatever the object type is.  So ... is this still
a good idea, or should we just forget about it?

I think this question is a part of a broader concern, namely do we
information which is already available in different ways through psql
commands, or by manually digging around in the catalogs? I believe
there are at least several examples of existing views we maintain
which are very similar to pg_comments: pg_seclabel seems quite
similar, for instance.

That's one's a direct analogue, but I don't want to overbroaden the
issue.  I guess it just seems to me that if no one's going to champion

Hearing no cries of oh, yes, please, I'm marking this Returned with
Feedback for now.  We can always revisit it if we hear that more
people want it.

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

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

```