Re: [HACKERS] Odd error when using UNION and COLLATE

[Bruce Momjian]

On Wed, Jul 20, 2016 at 06:03:08PM -0400, Tom Lane wrote:
> Greg Stark  writes:
> > But I think I agree that it's surprising that the collate clause isn't
> > working in the ORDER BY on a column produced by a UNION. Certainly
> > that's where people usually want to put it.
> 
> See this ancient comment in transformSetOperationStmt:
> 
>  * For now, we don't support resjunk sort clauses on the output of a
>  * setOperation tree --- you can only use the SQL92-spec options of
>  * selecting an output column by name or number.  Enforce by checking that
>  * transformSortClause doesn't add any items to tlist.
> 
> Perhaps sometime we ought to make an effort to relax that.

Oh, I didn't see that above the error block.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Odd error when using UNION and COLLATE

[Bruce Momjian]

On Wed, Jul 20, 2016 at 10:55:38PM +0100, Greg Stark wrote:
> On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian  wrote:
> > SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
> 
> 
> ::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;

Oh, collate on the string, before AS. I never thought of that.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Odd error when using UNION and COLLATE

[Tom Lane]

Greg Stark  writes:
> But I think I agree that it's surprising that the collate clause isn't
> working in the ORDER BY on a column produced by a UNION. Certainly
> that's where people usually want to put it.

See this ancient comment in transformSetOperationStmt:

 * For now, we don't support resjunk sort clauses on the output of a
 * setOperation tree --- you can only use the SQL92-spec options of
 * selecting an output column by name or number.  Enforce by checking that
 * transformSortClause doesn't add any items to tlist.

Perhaps sometime we ought to make an effort to relax that.

regards, tom lane


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

Re: [HACKERS] Odd error when using UNION and COLLATE

[Greg Stark]

Actually there's nothing about UNION here. It's true for any column alias:

::***> select 'a-c' AS x ORDER BY x COLLATE "C" ;
ERROR:  42703: column "x" does not exist
LINE 2: select 'a-c' AS x ORDER BY x COLLATE "C" ;
   ^
LOCATION:  errorMissingColumn, parse_relation.c:2892
Time: 0.204 ms

Also you don't need WITH, just an old-fashioned inline view:

::***> select * from (select 'a-c'::text AS x) as subquery ORDER BY x
COLLATE "C" ;
┌─┐
│  x  │
├─┤
│ a-c │
└─┘
(1 row)


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

Re: [HACKERS] Odd error when using UNION and COLLATE

[David G. Johnston]

On Wed, Jul 20, 2016 at 5:38 PM, Bruce Momjian  wrote:

> I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
> we don't allow a_expr in a UNION.  Perhaps we are too strict here, but I
> can't tell.
>

​ORDER BY 1 COLLATE "C" is indeed an expression - the number no longer
refers to a column position but it is a constant.  The presence or absence
of UNION doesn't factor into things here - the expression itself is useless
on its face.​

This one is a bit different in cause but I suspect is working as well as
can be expected.

SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";

​David J.​

Re: [HACKERS] Odd error when using UNION and COLLATE

[Greg Stark]

On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian  wrote:
> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";


::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;
┌─┐
│  x  │
├─┤
│ a-c │
│ ab  │
└─┘
(2 rows)


But I think I agree that it's surprising that the collate clause isn't
working in the ORDER BY on a column produced by a UNION. Certainly
that's where people usually want to put it.

-- 
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] Odd error when using UNION and COLLATE

[Bruce Momjian]

Seems you can't use UNION and COLLATE in the same SELECT statement;  you
have to put the UNION inside of WITH and then do the COLLATE outside:

 test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C";
 ERROR:  collations are not supported by type integer
 LINE 1: ... 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C...
 ^


 test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
 ERROR:  invalid UNION/INTERSECT/EXCEPT ORDER BY clause
 LINE 1: ...CT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE ...
 ^
 DETAIL:  Only result column names can be used, not expressions or functions.
 HINT:  Add the expression/function to every SELECT, or move the UNION into a 
FROM clause.


 test=> WITH d AS (SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x) SELECT * FROM 
d ORDER BY x COLLATE "C";
   x
 -
  a-c
  ab
 (2 rows)

I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
we don't allow a_expr in a UNION.  Perhaps we are too strict here, but I
can't tell.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


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

[HACKERS] Odd error during vacuum

[Christopher Kings-Lynne]

Hi all,

I got these odd messages while doing a vacuum in 7.1.3 0 - any idea what
they mean? I assume it's not fatal as they're just notices, but I've never
had them before and haven't had them since.

NOTICE:  RegisterSharedInvalid: SI buffer overflow
NOTICE:  InvalidateSharedInvalid: cache state reset

Chris


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] Odd error during vacuum

[Gavin Sherry]

On Thu, 11 Apr 2002, Christopher Kings-Lynne wrote:

 Hi all,
 
 I got these odd messages while doing a vacuum in 7.1.3 0 - any idea what
 they mean? I assume it's not fatal as they're just notices, but I've never
 had them before and haven't had them since.
 
 NOTICE:  RegisterSharedInvalid: SI buffer overflow
 NOTICE:  InvalidateSharedInvalid: cache state reset

This just means that the cache invalidation buffer got overloaded and was
reset. Its not really a problem (except in terms of performance). I would
say that if you haven't seen this before your database is getting more
usage and/or more data.

To fix this increase shared_buffers.

Gavin



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Odd error during vacuum

[Tom Lane]

Gavin Sherry [EMAIL PROTECTED] writes:
 NOTICE:  RegisterSharedInvalid: SI buffer overflow
 NOTICE:  InvalidateSharedInvalid: cache state reset

 To fix this increase shared_buffers.

AFAIK shared_buffers has no direct effect on the rate of SI overruns.
I suppose it might have an indirect effect just by improving overall
performance...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] Odd error...

[Philip Warner]

At 22:12 16/07/01 -0500, Dominic J. Eidson wrote:
morannon:~pg_dump -t bboard openacs | less
getTables(): SELECT (for VIEW ec_subsubcategories_augmented) returned NULL
oid
SELECT was: SELECT definition as viewdef, (select oid from pg_rewrite
where rulename='_RET' || viewname) as view_oid from pg_views where
viewname = 'ec_subsubcategories_augmented';

Any ideas what would cause this?

Probably the length of the view name; which version are you running? I
haven't look at PG for a while, but I thought this was fixed in 7.1.2





Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Odd error...

[Dominic J. Eidson]

On Tue, 17 Jul 2001, Philip Warner wrote:

 At 22:12 16/07/01 -0500, Dominic J. Eidson wrote:
 morannon:~pg_dump -t bboard openacs | less
 getTables(): SELECT (for VIEW ec_subsubcategories_augmented) returned NULL
 oid
 SELECT was: SELECT definition as viewdef, (select oid from pg_rewrite
 where rulename='_RET' || viewname) as view_oid from pg_views where
 viewname = 'ec_subsubcategories_augmented';
 
 Any ideas what would cause this?
 
 Probably the length of the view name; which version are you running? I
 haven't look at PG for a while, but I thought this was fixed in 7.1.2

openacs=# select version();
   version   
-
 PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)

(pretty sure that's 7.1.0, btw)

openacs=# SELECT definition as viewdef, (select oid from pg_rewrite where 
rulename='_RET' || viewname) as view_oid from pg_views where viewname = 
'ec_subsubcategories_augmented';   
  
   
   
 viewdef   
   
 | view_oid
-+--
 SELECT subsubs.subsubcategory_id, subsubs.subcategory_id, 
subsubs.subsubcategory_name, subsubs.sort_key, subsubs.last_modified, 
subsubs.last_modifying_user, subsubs.modified_ip_address, subs.subcategory_name, 
cats.category_id, cats.category_name FROM ec_subsubcategories subsubs, 
ec_subcategories subs, ec_categories cats WHERE ((subsubs.subcategory_id = 
subs.subcategory_id) AND (subs.category_id = cats.category_id)); |

As you can see, it gets the viewdef part fine, but not the select oid
from pg_rewrite where ...  part.


-- 
Dominic J. Eidson
Baruk Khazad! Khazad ai-menu! - Gimli
---
http://www.the-infinite.org/  http://www.the-infinite.org/~dominic/


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Odd error...

[Tom Lane]

Dominic J. Eidson [EMAIL PROTECTED] writes:
 On Tue, 17 Jul 2001, Philip Warner wrote:
 Any ideas what would cause this?
 
 Probably the length of the view name; which version are you running? I
 haven't look at PG for a while, but I thought this was fixed in 7.1.2

  PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2

IIRC, that was a post-7.1 bug fix.  Update to 7.1.2, or shorten your
view name by a few characters.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] Odd error...

[Dominic J. Eidson]

morannon:~pg_dump -t bboard openacs | less
getTables(): SELECT (for VIEW ec_subsubcategories_augmented) returned NULL oid
SELECT was: SELECT definition as viewdef, (select oid from pg_rewrite
where rulename='_RET' || viewname) as view_oid from pg_views where
viewname = 'ec_subsubcategories_augmented';

Any ideas what would cause this?



-- 
Dominic J. Eidson
Baruk Khazad! Khazad ai-menu! - Gimli
---
http://www.the-infinite.org/  http://www.the-infinite.org/~dominic/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])