On Wed, Sep 2, 2009 at 06:25, Tom Lane wrote:
> Magnus Hagander writes:
>>> and when i try to recover them via an analyze; (on all tables on the
>>> database) the result is nothing...
>>> i have to exexute the analyze commands twice to compute the statistics
>
>> pg_stat_* are not directly affecte
On Tue, Sep 01, 2009 at 05:49:25PM +0100, Sam Mason wrote:
> PG could maybe throw an error to tell you this is
> what is happening?
Would something like the included patch be accepted?
--
Sam http://samason.me.uk/
*** src/backend/utils/adt/char.c~ 2009-01-01 17:23:49.0 +
--- src/b
Tom Lane wrote:
> With the fix I was just about to apply, all four cases give the first
> set of results. This clearly satisfies the principle of least
> astonishment, at least more nearly than what we have; but it equally
> clearly is *not* going to restore 8.4 to work just like 8.3.
Right, 8.3
On Tue, Sep 1, 2009 at 9:51 AM, Dmitry wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5027
> Logged by: Dmitry
> Email address: mas...@hsdesign.ru
> PostgreSQL version: 8.3.5
> Operating system: ALT Linux
> Description: SQL query error?
> Details
On Fri, Aug 28, 2009 at 15:38, Alvaro Herrera wrote:
> Magnus Hagander escribió:
>> On Fri, Aug 28, 2009 at 05:03, Alvaro Herrera
>> wrote:
>
>> > It would be good, but currently that list comes from a database that
>> > cannot handle external lists. Since that database also powers the
>> > searc
Jeff Davis wrote:
> On Tue, 2009-09-01 at 13:49 -0500, Kevin Grittner wrote:
>> I figured that; I'm just trying to understand what seems to me like
>> an odd wart on the type system. I figure I must be missing
>> something important, so I'd kinda like to find out what that is.
>
> If I understan
"Kevin Grittner" writes:
> Jeff Davis wrote:
>> If I understand your question, you're comparing:
>>
>> (a) leaving a literal as "unknown" until you've finished
>> inferring types (current behavior)
>> (b) casting every unknown to text immediately, and then trying to
>> infer the types
> No, t
Tom Lane wrote:
> "Kevin Grittner" writes:
>> No, that's not it. I'm wondering why it isn't treated as text.
>> Period. Full stop. Nothing to infer.
>
> Because then we would have to provide implicit casts from text to
> everything else, which would be horribly dangerous.
I would like th
Sam Mason writes:
> On Tue, Sep 01, 2009 at 05:49:25PM +0100, Sam Mason wrote:
>> PG could maybe throw an error to tell you this is
>> what is happening?
> Would something like the included patch be accepted?
ISTM this was debated once before and rejected. However, there's a
proposal over here
On Wed, 2009-09-02 at 08:57 -0500, Kevin Grittner wrote:
> > (a) leaving a literal as "unknown" until you've finished
> > inferring types (current behavior)
> > (b) casting every unknown to text immediately, and then trying to
> > infer the types
>
> No, that's not it. I'm wonde
On Wed, Sep 2, 2009 at 3:44 PM, Jeff Davis wrote:
> On Wed, 2009-09-02 at 08:57 -0500, Kevin Grittner wrote:
>> > (a) leaving a literal as "unknown" until you've finished
>> > inferring types (current behavior)
>> > (b) casting every unknown to text immediately, and then trying to
>> >
Jeff Davis wrote:
> I disagree that using implicit casts to make up for a lack of an
> "unknown" type will improve matters
I certainly never meant to imply that additional implicit casts should
be added. I apologize for not being more clear about that.
Thanks again for helping fill in the b
Heikki Linnakangas writes:
> Tom Lane wrote:
>> With the fix I was just about to apply, all four cases give the first
>> set of results. This clearly satisfies the principle of least
>> astonishment, at least more nearly than what we have; but it equally
>> clearly is *not* going to restore 8.4 t
On Wed, Sep 02, 2009 at 03:50:05PM +0100, Greg Stark wrote:
> Perhaps we should stop thinking of "unknown" as, er, "unknown" and
> think of it as "text literal". A text literal has implicit casts to
> every data type but a normal text string has to be explicitly cast.
How does that help things? Y
On Wed, Sep 2, 2009 at 11:55 AM, Sam Mason wrote:
> In fact it doesn't seem to want to play ball at all. Even given the
> apparently unambiguous:
>
> SELECT 1+add(1,2);
> or
> SELECT 'hi'||add(1,2);
>
> It doesn't get anywhere. No need for "text 'hi'" in the second one
> because || isn't define
Tom Lane wrote:
> Heikki Linnakangas writes:
> > Tom Lane wrote:
> >> With the fix I was just about to apply, all four cases give the first
> >> set of results. This clearly satisfies the principle of least
> >> astonishment, at least more nearly than what we have; but it equally
> >> clearly is
On Wed, Sep 02, 2009 at 12:36:00PM -0400, Robert Haas wrote:
> On Wed, Sep 2, 2009 at 11:55 AM, Sam Mason wrote:
> > In fact it doesn't seem to want to play ball at all. Even given the
> > apparently unambiguous:
> >
> > SELECT 1+add(1,2);
> > or
> > SELECT 'hi'||add(1,2);
> >
> > It doesn't g
Alvaro Herrera writes:
> Tom Lane wrote:
>> What is interesting is that the CASE in the OP's original submission
>> is apparently only there to dodge the visible-since-8.0 version of
>> the problem; at least I can't see that it does anything else useful.
>> The complaint apparently is not so much
Sam Mason writes:
> ... For example:
> CREATE FUNCTION add(int,int) RETURNS int LANGUAGE sql
> AS $$ SELECT $1 + $2; $$;
> CREATE FUNCTION add(int,int,int DEFAULT NULL) RETURNS text LANGUAGE sql
> AS $$ SELECT ($1 + $2)::text; $$;
> What type should it attribute to the result of:
Tom Lane wrote:
> It's interesting that you want to go in 100% the opposite direction
> from Kevin, who seems to want to eliminate type inference
> altogether. Maybe our current compromise isn't too bad, if it makes
> everybody unhappy in opposite directions ;-)
Well, it's probably worth noti
On Wed, Sep 02, 2009 at 01:19:07PM -0400, Tom Lane wrote:
> Sam Mason writes:
> > ... For example:
>
> > CREATE FUNCTION add(int,int) RETURNS int LANGUAGE sql
> > AS $$ SELECT $1 + $2; $$;
>
> > CREATE FUNCTION add(int,int,int DEFAULT NULL) RETURNS text LANGUAGE sql
> > AS $$ SELECT
Sam Mason wrote:
> If we did follow Kevin's request directly, should we also be
> specifying the type of NULL?
I don't *think* the SQL standard requires that, and barring that I
don't see any compelling reason to type NULL. One problem I do see
with the current scheme, however, is that NULL *
"Kevin Grittner" writes:
> What I'm most concerned about are the corner cases where strict typing
> would give one non-error result and the inferred typing results in an
> error or a different result from the strict typing. I'm willing to
> argue that those are bugs, at least when the strongly ty
Tom Lane wrote:
> "Kevin Grittner" writes:
>> What I'm most concerned about are the corner cases where strict
>> typing would give one non-error result and the inferred typing
>> results in an error or a different result from the strict typing.
>> I'm willing to argue that those are bugs, at lea
On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > If we did follow Kevin's request directly, should we also be
> > specifying the type of NULL?
>
> I don't *think* the SQL standard requires that, and barring that I
> don't see any compelling reason to type NU
I wrote:
> A simple, self-contained example derived from the OP:
>
> test=# create table t (c "char");
> CREATE TABLE
> test=# insert into t values ('a');
> INSERT 0 1
> test=# select case when c = 'a' then 'Hey' else c end from t;
> c
> ---
> H
> (1 row)
>
> test=# select case when c = 'a'
Sam Mason wrote:
> you were requiring the types of literals that happened to be
> enclosed in quotes to have their type ascribed, so why not the NULL
> literal?
Well, unless things have changed in recent versions of the standard
and I've missed the change, a series of characters enclosed in
a
"Kevin Grittner" wrote:
> Well, unless things have changed in recent versions of the standard
> and I've missed the change, a series of characters enclosed in
> apostrophes is what the standard calls a "character string literal"
> and defines it to be be related to character based types such as
On Wed, Sep 02, 2009 at 01:27:35PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > you were requiring the types of literals that happened to be
> > enclosed in quotes to have their type ascribed, so why not the NULL
> > literal?
>
> Well, unless things have changed in recent versions of the
"Kevin Grittner" writes:
> And I'm not even sure how I'd explain the rules to someone.
text is preferred to "char" which is preferred to unknown.
This particular example would be less confusing if 'Hey'::"char"
threw an error, but that behavior is the result of an ancient
(bad?) decision in the
"Kevin Grittner" writes:
> Sam Mason wrote:
>> If we did follow Kevin's request directly, should we also be
>> specifying the type of NULL?
> I don't *think* the SQL standard requires that, and barring that I
> don't see any compelling reason to type NULL.
Actually, AFAICS the SQL spec *does*
Tom Lane wrote:
> "Kevin Grittner" writes:
>> And I'm not even sure how I'd explain the rules to someone.
>
> text is preferred to "char" which is preferred to unknown.
>
> This particular example would be less confusing if 'Hey'::"char"
> threw an error, but that behavior is the result of an a
On Wed, Sep 02, 2009 at 01:55:28PM -0500, Kevin Grittner wrote:
> So the behavior of the "char" type is anomalous in this regard? Other
> character-based types behave like varchar (which has the behavior I
> would expect here)? That is encouraging. Why isn't the behavior of
> "char" in this rega
On Wed, Sep 02, 2009 at 01:37:20PM -0500, Kevin Grittner wrote:
> That still seems to be the case in the draft of the 2003 standard I
> have:
>
> ::=
>
> |
> |
> |
> |
> |
> |
> ::=
> [ ]
> [ ... ]
> [ { [ ... ]
> }... ]
>
> The ball's in your court to
On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > If we did follow Kevin's request directly, should we also be
> > specifying the type of NULL?
>
> I don't *think* the SQL standard requires that, and barring that I
> don't see any compelling reason to type NU
Sam Mason wrote:
> I'd always thought '2001-01-01' was a valid date literal, seems the
> standard has required it to be prefixed by DATE at least back to
> SQL92.
Yep. I don't know if it would be remotely feasible, but the
implementation which seems like it would be "standard-safe" but still
Sam Mason wrote:
> CREATE VIEW v (c) AS
> SELECT NULL;
>
> PG allows it, but the resulting view seems somewhat unusable.
I'm not sure whether the only place the standard doesn't require a
cast is on assignment, but this is one place that the standard clearly
does require a cast, and I'm
The following bug has been logged online:
Bug reference: 5030
Logged by: Armando Taffarel Neto
Email address: taffa...@solis.coop.br
PostgreSQL version: 8.4.0
Operating system: Linux Ubuntu 9.04
Description:Problem on "RETURN QUERY EXECUTE" when a column is
dropped fr
On Wed, Sep 02, 2009 at 02:41:32PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
>
> > I'd always thought '2001-01-01' was a valid date literal, seems the
> > standard has required it to be prefixed by DATE at least back to
> > SQL92.
>
> Yep. I don't know if it would be remotely feasible,
"Kevin Grittner" writes:
> Yep. I don't know if it would be remotely feasible, but the
> implementation which seems like it would be "standard-safe" but still
> give reasonable concessions to those wanting to skip the extra
> keystrokes of declaring the type of literals which are not character
>
On Wed, Sep 02, 2009 at 02:59:54PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > CREATE VIEW v (c) AS
> > SELECT NULL;
> >
> > PG allows it, but the resulting view seems somewhat unusable.
>
> I'm not sure whether the only place the standard doesn't require a
> cast is on assignment
Tom Lane wrote:
> "Kevin Grittner" writes:
>> Yep. I don't know if it would be remotely feasible, but the
>> implementation which seems like it would be "standard-safe" but
>> still give reasonable concessions to those wanting to skip the
>> extra keystrokes of declaring the type of literals wh
On Wed, Sep 2, 2009 at 12:25 PM, Armando Taffarel
Neto wrote:
>
> SELECT * FROM test_foo();
> ERROR: structure of query does not match function result type
> DETAIL: Number of returned columns (1) does not match expected column count
> (2).
> CONTEXT: PL/pgSQL function "test_foo" line 5 at RETUR
Sam Mason wrote:
> On Wed, Sep 02, 2009 at 02:59:54PM -0500, Kevin Grittner wrote:
>> Sam Mason wrote:
>> > CREATE VIEW v (c) AS
>> > SELECT NULL;
>> >
>> > PG allows it, but the resulting view seems somewhat unusable.
>>
>> I'm not sure whether the only place the standard doesn't requi
Tom Lane wrote:
> "Kevin Grittner" writes:
>> go with the suggestion of having a character string literal type,
>> and change the semantics such that if there is a valid
>> interpretation of the statement with the character string literal
>> taken as text, it should be used; if not, resolve by
Sam Mason writes:
> On Wed, Sep 02, 2009 at 02:59:54PM -0500, Kevin Grittner wrote:
>> I'm not sure whether the only place the standard doesn't require a
>> cast is on assignment, but this is one place that the standard clearly
>> does require a cast, and I'm all for that.
> I'm probably missing
"Kevin Grittner" writes:
> Because COALESCE(NULL, NULL) has given no indication that it is
> character based, while 'x' is defined by the standard to be a
> character string literal. The two uses of "unknown" in typing seem to
> be solving different problems. Perhaps using the same flag for both
Tom Lane wrote:
> In a formal sense the type information available is the same, ie,
> none.
Well, in the sense that an international standard is formal, there is
a formal difference, in that one has no type information and the other
is a character string. However:
> The argument that SQL sa
So one of the elephants in the room in this (rather dead-end)
discussion is that one of the things "unknown" is good for is the fact
that most clients don't bind their parameter types to specific types.
Doing so is extremely cumbersome in just about every interface because
it forces you to think ab
"Kevin Grittner" writes:
> I think that the current approach leaves a small number of corner
> cases where we break SQL compliance. I think it's worthwhile trying
> to fix that. Whether that's best done by identifying the individual
> corners and fixing them independently as aberrations, or impl
Tom Lane wrote:
> One other point worth making is that we don't always consider SQL
> compliance to be a hard requirement that trumps every other
> consideration.
Point noted.
> An example is case-folding of identifiers; it's been pretty well
> agreed that between readability and backwards-c
On Wed, Sep 2, 2009 at 3:34 PM, Sam Mason wrote:
> On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote:
>> Sam Mason wrote:
>> > If we did follow Kevin's request directly, should we also be
>> > specifying the type of NULL?
>>
>> I don't *think* the SQL standard requires that, and barri
The following bug has been logged online:
Bug reference: 5031
Logged by: Mark Douglas
Email address: m...@steelhousemedia.com
PostgreSQL version: 8.4.0
Operating system: Ubunto Linux
Description:DATE_TRUNC returns the wrong value when specifying MONTH
Details:
The f
The following bug has been logged online:
Bug reference: 5032
Logged by: Keith Cascio
Email address: ke...@cs.ucla.edu
PostgreSQL version: 8.4.0
Operating system: CentOS 5.3 (Linux)
Description:unexpected syntax error for plpgsql function returns
table
Details:
Do t
Hello
it's not bug - PostgreSQL doesn't support parameter placeholder on
this position. Use dynamic query instead - plpgsql statement EXECUTE.
regards
Pavel Stehule
2009/9/3 Keith Cascio :
>
> The following bug has been logged online:
>
> Bug reference: 5032
> Logged by: Keith Casc
"Mark Douglas" writes:
> The following use of DATE_TRUNC returns the wrong value. I called the
> function on 2009-09-02. It should return '2009-09-01 00:00:00' for the
> following usage:
> SELECT DATE_TRUNC('MONTH', CURRENT_DATE);
> It instead returns '2009-08-31 17:00:00.
Really? What timezon
Mark Douglas writes:
> I have my timezone set to GMT so there really shouldn't be any time zone
> adjustments.
Okay ...
postgres=# set timezone = GMT;
SET
postgres=# SELECT DATE_TRUNC('MONTH', CURRENT_DATE);
date_trunc
2009-09-01 00:00:00+00
(1 row)
I su
Pavel,
On Thu, 3 Sep 2009, Pavel Stehule wrote:
> it's not bug - PostgreSQL doesn't support parameter placeholder on this
> position. Use dynamic query instead - plpgsql statement EXECUTE.
Thank you for your reply. I appreciate your suggestion, but it still seems
like
a bug to me. Please co
I have my timezone set to GMT so there really shouldn't be any time zone
adjustments.
Mark
On 9/2/09 10:01 PM, "Tom Lane" wrote:
"Mark Douglas" writes:
> The following use of DATE_TRUNC returns the wrong value. I called the
> function on 2009-09-02. It should return '2009-09-01 00:00:00' for
59 matches
Mail list logo
