Re: [HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)

[Andrew Chernow]

PGconn *PQconnectParams(const char **params)

Where "params" is an array with an even number of parameters, forming
key/value pairs. Usage example:



Maybe use the term properties (props for short) or options instead of params? 
Params is already in heavy use.  How about PQconnectProps(...) or 
PQconnectOptions(...)?



Another idea is to use an array of PQconninfoOption structs:

PQconn *PQconnectParams(PQconninfoOption *params);



this sounds like a good idea, specially if we add new parameters to


Here's another idea, parallel arrays:

PGconn *PQconnectProps(const char **names, const char **values);
PGconn *PQconnectOptions(const char **names, const char **values);

To build on the struct idea, maybe PGprop or PGoption instead of 
PQconninfoOption.  Also, add an argument specifying the number of props/options.


PGconn *PQconnectProps(const PGprop *props, int nProps);
PGconn *PQconnectOptions(const PGoption *options, int nOptions);


any one have a preference here?



I like the struct approach.  I personally prefer specifying the element count of 
an array, rather than using a NULL terminating element.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

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

Re: [HACKERS] RfD: more powerful "any" types

[Hannu Krosing]

On Thu, 2009-09-10 at 00:31 +0200, Dimitri Fontaine wrote:
> Hi,
> 
> Tom Lane  writes:
> > Hannu Krosing  writes:
> >> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by
> >> the number in parentheses
> >
> > Yeah, that idea occurred to me too.  The immediate practical problem is
> > that we don't store a typmod for function argument/result types.
> > I guess we could look into doing that ...
> 
> But still, it looks terrible...
> 
> On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote:
> > That's beginning to sound a bit like a generics feature.  E.g.,
> >
> > CREATE FUNCTION the_same(arg_a T, arg_b T) RETURNS bool AS $$
> > SELECT arg_a IS DISTINCT FROM arg_b;
> > $$;
> 
> And even if we don't want to go this far (I'd be in favor of going there
> FWIW), we could maybe have a syntax allowing the users to name or
> declare the any types he'll need?
> 
> CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y)
>  RETURNS anyelement y[]
> AS $$
> ... 
> $$;
> 
> Now we have anyelement and anyelement2, but without the ugly names or
> the typmod feature stretching, and we can even have any number of user
> defined anyelement types. That behave just like anyelement.
> 
> Then, maybe we need VARIADIC anyelement any[] to declare the function as able
> to cope with a variable length list of all different kinds of elements?

maybe just let users say what they mean, so first time we have "any" and
if we need more then we say "same_as(...)"

so your example becomes

 
CREATE FUNCTION foo(a any, b same_type_as(a), c any)
  RETURNS same_type_as(c)[]
 AS $$
 ... 
 $$;

or the same using positional arguments

CREATE FUNCTION foo(a any, b same_type_as(1), c any)
  RETURNS same_type_as(3)[]
 AS $$
 ... 
 $$;

this then gets transformed at parse time to whatever internal
representation of type sameness we use.

-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



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

[HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)

[Jaime Casanova]

On Mon, Jul 6, 2009 at 10:00 AM, Heikki
Linnakangas wrote:
>
> Could we
> have a version of PQconnectdb() with an API more suited for setting the
> params programmatically? The PQsetdbLogin() approach doesn't scale as
> parameters are added/removed in future versions, but we could have
> something like this:
>
> PGconn *PQconnectParams(const char **params)
>
> Where "params" is an array with an even number of parameters, forming
> key/value pairs. Usage example:
>
[...]
>
> Another idea is to use an array of PQconninfoOption structs:
>
> PQconn *PQconnectParams(PQconninfoOption *params);
>

this sounds like a good idea, specially if we add new parameters to
the conninfo string and want postgresql's client applications to use
them.

any one have a preference here?

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
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] Elementary dependency look-up

[Josh Williams]

On Wed, 2009-09-09 at 11:30 -0500, decibel wrote:
> On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:
> > How is this better than just reading the information directly from
> > pg_depend?
> 
> pg_depend is very difficult to use. You have to really, really know  
> the catalogs to be able to figure it out. Part of the problem is  
> (afaik) there's nothing that documents every kind of record/ 
> dependency you might find in there.

Exactly - these functions were designed around making that easier for
the end user.  The less poking around in system catalogs a user has to
do the better.

Yeah, the documentation about what can be found in pg_depend is
scattered at best, though then again there doesn't seem to be a whole
lot in there that's of much interest to end users...  Actually, apart
from pg_get_serial_sequence() do we have anything else that utilizes
dependency data to show the user information?

> What might be more useful is a view that takes the guesswork out of  
> using pg_depend. Namely, convert (ref)classid into a catalog table  
> name (or better yet, what type of object it is), (ref)objid into an  
> actual object name, and (ref)objsubid into a real name.

Makes sense, would be much more future-proof.  It shouldn't be difficult
to put in some intelligence to figure out the type of object, such as
looking at relkind if (ref)classid = pg_class.

It might be a little difficult to maintain, depending on what else finds
its way into the system catalogs later (but then, probably not much more
so than INFORMATION SCHEMA is.)  Would that be preferable, over a couple
additional functions?

- Josh Williams



-- 
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] Ragged CSV import

[Tom Lane]

Robert Haas  writes:
> On Wed, Sep 9, 2009 at 11:01 PM, Tom Lane wrote:
>> The thought that comes to mind for me is something "in front of" copy,
>> that is, give it the text of each line and let it do a text-to-text
>> transformation before COPY chews on it.

> That seems to me to be a whole lot less useful.  As I see it, the
> whole point of any enhancement in this area is to reuse the parsing
> code.   If I have a CSV file (or some other format COPY understands),
> I don't want to have to write my own parser for that format in order
> to do some simple data transformation (like dropping columns >n).

I'm unconvinced.  A significant fraction of the COPY customization
requests that I can remember had to do with cleaning up input that
didn't entirely match COPY's idea of what valid CSV is.  If it's close
enough that COPY will successfully parse it as N columns, you can stick
it into an N-column text table and do your transformations afterwards.

Thinking a bit more generally, it seems to me that as COPY IN is
currently constituted, there are really four distinguishable bits
of functionality in a pipeline:

1. Reading from FE/BE protocol (or file) and conversion to database
character set.

2. Parsing the text into a set of de-escaped field values.

3. Converting the field values to internal Datum form (requires
knowledge of a set of target data types).

4. Bulk insertion into the target table.

It is not hard to come up with problems that are most easily solved
by injecting some sort of filtering between any pair of these steps.
You're suggesting a filter between 2 and 3, I suggested one between
1 and 2, and the well-known trick of inserting into a temp table
and then filtering to a final table amounts to a filter between
3 and 4.

We've had proposals before to come up with a form of COPY that can be
used as a data source in an INSERT/SELECT context.  That would
automatically provide a way to filter between steps 3 and 4 --- or maybe
better, insist that the COPY-thingy produces just text fields, and leave
both steps 3 and 4 to be done by the INSERT/SELECT.  With that, the only
missing functionality would be a textual filter between steps 1 and 2.

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] CommitFest 2009-09 Plans and Call for Reviewers

[David Fetter]

On Wed, Sep 09, 2009 at 09:13:40PM -0400, Robert Haas wrote:
> Folks,
> 
> CommitFest 2009-09 is now only days away!  I have been having
> discussions with a number of people, and the result of those
> discussions is that I have agreed to manage the next CommitFest
> insofar as patch assignment is concerned.  Selena Deckelmann,
> Stephen Frost, and Brendan Jurd have agreed to assist with what
> we're calling patch-chasing: that is, making sure that discussions
> around a patch don't die, and that it gets marked as Commited,
> Returned with Feedback, or Rejected in a timely fashion, so that the
> CommitFest overall gets closed out in a timely fashion.  We're
> tentatively looking at dividing up responsibility for patch-chasing
> by topic, as follows:
> 
> Brendan Jurd: SQL Features, Procedural Languages Stephen Frost:
> Security, Contrib, Miscellaneous Selena Deckelmann: Performance,
> Clients
> 
> Hopefully this plan is acceptable to everyone.  If not, please feel
> free to reply here.
> 
> And that brings me to the next topic.  Although I have received many
> endorsements of the way that I managed the last CommitFest, for
> which I am grateful, I think that the real key to a good CommitFest
> is to have a good group of reviewers.  We had an excellent group of
> reviewers for the last CommitFest and I am hoping that all of those
> people will volunteer again.  Of course, we also need new reviewers!
> 
> http://wiki.postgresql.org/wiki/Reviewing_a_Patch

I'm in :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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] Ragged CSV import

[Tom Lane]

Andrew Dunstan  writes:
> you mean some sort of filter mechanism?

> COPY  FILTER function_name ( args) ... ?

> That might work. Then we could provide a couple builtin and people could 
> write others in C or PL/dujour.

Yeah, that's pretty much what I was thinking, although exactly where the
filter happens and what its arguments/results are is open to debate.

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] Ragged CSV import

[Andrew Dunstan]

Tom Lane wrote:

What you're talking about is a
fairly specialized single-purpose feature, which nonetheless is going to
require a lot of infrastructure (for example, teaching psql's \copy
about it).


Well, that's hardly a lot.



 Perhaps, for approximately the same amount of overhead,
we could provide something more general.
  



Ok.


I don't agree with the idea of injecting something "behind" copy though.
The thought that comes to mind for me is something "in front of" copy,
that is, give it the text of each line and let it do a text-to-text
transformation before COPY chews on it.
  



you mean some sort of filter mechanism?

COPY  FILTER function_name ( args) ... ?

That might work. Then we could provide a couple builtin and people could 
write others in C or PL/dujour.


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

[Andrew Gierth]

> "Robert" == Robert Haas  writes:

 >> Just to pick up on some points from the discussion:
 >> 
 >> 1. LATERAL has to be explicit because it changes the scope of
 >> references.  For example, in:
 >> ... (select ... FROM (select a AS b), (select b)) ...
 >> the "b" in the second subselect could be an outer reference, but
 >> it can't be a reference to the first subquery; whereas in:
 >> ... (select ... FROM (select a AS b), LATERAL (select b)) ...
 >> the "b" in the second subselect refers to the result of the first
 >> subselect.

 Robert> Can you provide a more complete example?  I'm unable to
 Robert> construct a working example of this type.  For example:

 Robert> rhaas=# select (select 1 from (select a as b) x, (select b) y) from t1;
 Robert> ERROR:  subquery in FROM cannot refer to other relations of same query
 Robert> level at character 50

That looks like a bug to me. The spec is explicit that the inner definition
of b is not in scope in the second subquery, and therefore that should parse
as an outer reference.

 >> 2. LATERAL in general constrains both the join order and the join
 >> plan, assuming any lateral references are actually made.

 Robert> Peter seemed to be saying that LATERAL() must syntactically
 Robert> follow the same-level FROM items to which it refers.  Is that
 Robert> your understanding also?

LATERAL references must be to items defined in the same FROM clause and
to the left of the LATERAL.

The relevant language of the spec seems to be:

  a) If TR is contained in a  FC with no intervening
 , then the scope clause SC of TR is the  or innermost  that
 contains FC.  The scope of a range variable of TR is the , , , , and
  of SC, together with every 
 that is simply contained in FC and is preceded by TR, and every
  that is simply contained in FC and is
 preceded by TR, and the  of all s
 contained in SC that contain TR. If SC is the 
 that is the  of a simple table query STQ,
 then the scope of a range variable of TR also includes the  of STQ.

 >> 4. LATERAL allows some optimizations that aren't currently done, either
 >> by explicitly rewriting the query, or (in theory) the optimizer itself
 >> could consider a lateral plan (I believe Oracle does this). This would
 >> apply to queries of this form:
 >> 
 >> SELECT ... FROM t1 LEFT JOIN (t2 JOIN t3 ON (t2.a=t3.a)) on (t1.a=t2.a);
 >> 
 >> which currently forces the t2/t3 join to occur first even where t1 is
 >> small; this could be rewritten with LATERAL as:
 >> 
 >> SELECT ...
 >>FROM t1
 >>LEFT JOIN LATERAL (select * from t2 join t3 on (t2.a=t3.a)
 >>where t2.a=t1.a) s
 >>ON true;

 Robert> Well, you haven't actually commuted the joins here - how do
 Robert> you have in mind for PostgreSQL to execute this?  I'm
 Robert> guessing that it's something like a nest loop with t1 as the
 Robert> outer side and the lateral subquery as the inner side, so
 Robert> that the executor repeatedly executes
 Robert> "select * from t2 join t3 on t2.a = t3.a where t2.a = $1"?

Yup.

The current execution plans for this type of query are completely
disastrous if t1 is small (or qualified so as to be small) and t2 and
t3 are large. Having LATERAL would allow the query to be rewritten to
perform reasonably; a bonus would be for the planner to consider the
lateral join automatically without requiring it to be explicitly
requested.

-- 
Andrew (irc:RhodiumToad)

-- 
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] Ragged CSV import

[Robert Haas]

On Wed, Sep 9, 2009 at 11:01 PM, Tom Lane wrote:
> Andrew Dunstan  writes:
>> Robert Haas wrote:
>>> I wonder whether it would be appropriate to do something like
>>> implement a method by which copy could return text[] and then one
>>> could write wrappers around that functionality to do this as well as
>>> other things.
>
>> Anything along these lines in C is likely to be far larger than what I
>> had in mind, which was a fairly minor enhancement.
>
> I think Robert's got a point though.  What you're talking about is a
> fairly specialized single-purpose feature, which nonetheless is going to
> require a lot of infrastructure (for example, teaching psql's \copy
> about it).  Perhaps, for approximately the same amount of overhead,
> we could provide something more general.
>
> I don't agree with the idea of injecting something "behind" copy though.
> The thought that comes to mind for me is something "in front of" copy,
> that is, give it the text of each line and let it do a text-to-text
> transformation before COPY chews on it.

That seems to me to be a whole lot less useful.  As I see it, the
whole point of any enhancement in this area is to reuse the parsing
code.   If I have a CSV file (or some other format COPY understands),
I don't want to have to write my own parser for that format in order
to do some simple data transformation (like dropping columns >n).

Previous agreements nonwithstanding, I think letting COPY do general
transformations is a great idea.  But I'm a lot more skeptical about
one specific transformation without some framework of which that case
is the first instance.

...Robert

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

Re: [HACKERS] Ragged CSV import

[Andrew Dunstan]

Robert Haas wrote:

However, I'm skeptical about whether the specific
thing you want to do after parsing (namely, drop excess columns,
null-fill missing ones) is sufficiently common to warrant a feature to
do only that.  YMMV, of course.


  


So might my experience. I can tell you that I have been asked about this 
dozens of times since implementing CSV import, in various forums. From 
day one people have wanted a way of handling CSVs which were not 
strictly regular. Pretty much every time the request has been something 
fairly close to this.


As for the feature not being large enough, we have a regrettable 
(IMNSHO) tendency in this project to take simple proposals and embelish 
them with many bells and whistles. Personally, I'm much more of a fan of 
incremental development.


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] Ragged CSV import

[Tom Lane]

Andrew Dunstan  writes:
> Robert Haas wrote:
>> I wonder whether it would be appropriate to do something like
>> implement a method by which copy could return text[] and then one
>> could write wrappers around that functionality to do this as well as
>> other things.  

> Anything along these lines in C is likely to be far larger than what I 
> had in mind, which was a fairly minor enhancement.

I think Robert's got a point though.  What you're talking about is a
fairly specialized single-purpose feature, which nonetheless is going to
require a lot of infrastructure (for example, teaching psql's \copy
about it).  Perhaps, for approximately the same amount of overhead,
we could provide something more general.

I don't agree with the idea of injecting something "behind" copy though.
The thought that comes to mind for me is something "in front of" copy,
that is, give it the text of each line and let it do a text-to-text
transformation before COPY chews on it.

Any of this is getting into territory we had previously agreed not to
let COPY venture into, ie general purpose data transformation.  I'm not
sure I want to cross that bridge and only get "ignore extra columns" out
of 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] Ragged CSV import

[Robert Haas]

On Wed, Sep 9, 2009 at 10:40 PM, Andrew Dunstan wrote:
> Robert Haas wrote:
>>
>> I wonder whether it would be appropriate to do something like
>> implement a method by which copy could return text[] and then one
>> could write wrappers around that functionality to do this as well as
>> other things.
>
> It's not a function ... writing a copy() function returning text[] (or setof
> text[], which would be better) in plperlu would be trivial. But it couldn't
> read from stdin or be able to be fed data from a client in copy-in mode.

Yes, setof text[] is what I meant.  Making it be able to read from
stdin or be fed data from a client in copy-in mode would be the whole
point of the feature, I suppose.

> And even if we have it what does the sql look like to insert the values into
> some table with n columns?

INSERT INTO table
SELECT t[1], t[2], t[3], t[4], ..., t[n]
FROM (...however we get the copy results...)

...although I'm not entirely sure that's compatible with the
client/server protocol.

> Anything along these lines in C is likely to be far larger than what I had
> in mind, which was a fairly minor enhancement.

It wouldn't be the first time that someone was told that a particular
enhancement didn't have enough value and that they had to do something
larger if they wanted it in core, but on the other hand, I am only
expressing my opinion, which is not binding on you or anyone else, nor
even a fully-fleshed-out proposal.  All I'm saying is that it seems to
me that there is value in being able to use the CSV (or other) parsing
code, but have some way to make modifications to how/where the data is
actually inserted.  However, I'm skeptical about whether the specific
thing you want to do after parsing (namely, drop excess columns,
null-fill missing ones) is sufficiently common to warrant a feature to
do only that.  YMMV, of course.

...Robert

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

Re: [HACKERS] Ragged CSV import

[Andrew Dunstan]

Robert Haas wrote:

I wonder whether it would be appropriate to do something like
implement a method by which copy could return text[] and then one
could write wrappers around that functionality to do this as well as
other things.  
  


It's not a function ... writing a copy() function returning text[] (or 
setof text[], which would be better) in plperlu would be trivial. But it 
couldn't read from stdin or be able to be fed data from a client in 
copy-in mode.


And even if we have it what does the sql look like to insert the values 
into some table with n columns?


Anything along these lines in C is likely to be far larger than what I 
had in mind, which was a fairly minor enhancement.


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

[HACKERS] CommitFest 2009-09 Plans and Call for Reviewers

[Robert Haas]

Folks,

CommitFest 2009-09 is now only days away!  I have been having
discussions with a number of people, and the result of those
discussions is that I have agreed to manage the next CommitFest
insofar as patch assignment is concerned.  Selena Deckelmann, Stephen
Frost, and Brendan Jurd have agreed to assist with what we're calling
patch-chasing: that is, making sure that discussions around a patch
don't die, and that it gets marked as Commited, Returned with
Feedback, or Rejected in a timely fashion, so that the CommitFest
overall gets closed out in a timely fashion.  We're tentatively
looking at dividing up responsibility for patch-chasing by topic, as
follows:

Brendan Jurd: SQL Features, Procedural Languages
Stephen Frost: Security, Contrib, Miscellaneous
Selena Deckelmann: Performance, Clients

Hopefully this plan is acceptable to everyone.  If not, please feel
free to reply here.

And that brings me to the next topic.  Although I have received many
endorsements of the way that I managed the last CommitFest, for which
I am grateful, I think that the real key to a good CommitFest is to
have a good group of reviewers.  We had an excellent group of
reviewers for the last CommitFest and I am hoping that all of those
people will volunteer again.  Of course, we also need new reviewers!

http://wiki.postgresql.org/wiki/Reviewing_a_Patch
http://wiki.postgresql.org/wiki/RRReviewers
And of course my personal favorite:
http://wiki.postgresql.org/images/5/58/11_eggyknap-patch-review.pdf

Please send me an email (without copying the list) if you are
available to review; feel free to include any information that might
be helpful in assigning you an appropriate patch.

...Robert

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

Re: [HACKERS] Bug in aggregates in windowing context

[David Fetter]

On Wed, Sep 09, 2009 at 08:45:06PM -0400, Robert Haas wrote:
> On Wed, Sep 9, 2009 at 6:24 PM, David Fetter wrote:
> > Anyhow, I think it's a bug and needs back-patching.
> 
> I suspect if it were as easy as removing the error test it would
> have been done already.  Perhaps you'd care to submit a patch?

Working on that at the moment :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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] Bug in aggregates in windowing context

[Robert Haas]

On Wed, Sep 9, 2009 at 6:24 PM, David Fetter wrote:
> Anyhow, I think it's a bug and needs back-patching.

I suspect if it were as easy as removing the error test it would have
been done already.  Perhaps you'd care to submit a patch?

...Robert

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

Re: [HACKERS] Ragged CSV import

[Robert Haas]

On Wed, Sep 9, 2009 at 7:41 PM, Andrew Dunstan wrote:
>
>
> Robert Haas wrote:
>>
>> I agree that ignoring extra columns is a bad idea, but I don't even
>> like the idea of ignoring missing columns.  It doesn't seem like a
>> good idea to take a spreadsheet and feed it into COPY without doing
>> any validation anyway, and this is the kind of thing that is trivial
>> to clean up with a thin layer of Perl or your scripting language of
>> choice.
>>
>>
>
> If it's an optional feature then I don't see why there is a problem. What
> skin is it off anyone else's nose but those whose choose this behaviour?

I have to admit I'm usually an advocate of that way of thinking, so
maybe I am all wet.  I suppose it comes down to whether you think this
particular feature is something with broad applicability, or whether
there are 100 other equally plausible features.

I wonder whether it would be appropriate to do something like
implement a method by which copy could return text[] and then one
could write wrappers around that functionality to do this as well as
other things.  For example, suppose you wanted to have rows of the
form:

A,B,C,X1,Y1,X2,Y2,X3,Y3

...which gets transformed into an insert of (A,B,C) into a main table
and (A,X1,Y1), (A,X2,Y2), (A,X3,Y3) into a side table.  (I have actual
knowledge of a widely-deployed system produced by a large company that
outputs data in a format similar to this, though the actual format is
considerably more complex.)

...Robert

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

Re: [HACKERS] RfD: more powerful "any" types

[Robert Haas]

On Wed, Sep 9, 2009 at 3:23 PM, Tom Lane wrote:
> Robert Haas  writes:
>> What we need is a system where base types are represented
>> by an OID, but derived types (list and functional types) are built up
>> using type constructors that take other types as arguments.
>
> This is SQL, not Haskell.  What you suggest seems about two orders of
> magnitude more complex than real-world applications could justify.
>
> (so where is pl/haskell, anyway?)

There are languages much less obscure than Haskell that support
passing functions as arguments to other functions, such as C.   While
C doesn't support user-defined type constructors, it does support one
built-in type constructor - you can declare a function argument as
taking arbitrary argument types and returning an arbitrary type.  C++
supports user-defined type constructors via the template mechanism.

The scripting languages generally do not guarantee type-safety for
functions passed as arguments, but they do let you pass them.
However, I can't really imagine how we could get away with such a
system in SQL, due to security concerns.

...Robert

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

Re: [HACKERS] Ragged CSV import

[Andrew Dunstan]

Robert Haas wrote:

I agree that ignoring extra columns is a bad idea, but I don't even
like the idea of ignoring missing columns.  It doesn't seem like a
good idea to take a spreadsheet and feed it into COPY without doing
any validation anyway, and this is the kind of thing that is trivial
to clean up with a thin layer of Perl or your scripting language of
choice.

  


If it's an optional feature then I don't see why there is a problem. 
What skin is it off anyone else's nose but those whose choose this 
behaviour?


I am perfectly familiar with Perl and so is the client that requested 
this feature. They are quite technically savvy. They are using a 
scripting solution now but they find it cumbersome.


As for general validation, the requestor's application in fact loads the 
spreadsheet into a temp table of text columns and then subjects it to a 
large variety of complex business rule checking before adding the data 
to the main tables. It is a whole lot faster and cleaner to do it that 
way than before the data is loaded at all. That's why they aren't 
concerned about missing columns.


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] Bug in aggregates in windowing context

[Tom Lane]

David Fetter  writes:
> A bug that's documented in the source code is still a bug.

Sorry, but we are not going to define every unimplemented feature in
SQL:2008 as being a back-patchable bug fix candidate.  As for the "it's
not documented" argument, I direct you to section 4.2.8, which is pretty
explicit about what the allowed syntax is.  (And btw, I'm equally
uninterested in the other omissions in that compared to SQL:2008.
We did what we could for 8.4.)

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] Ragged CSV import

[Robert Haas]

On Wed, Sep 9, 2009 at 4:13 PM, Tom Lane wrote:
> Andrew Dunstan  writes:
>> I have received a requirement for the ability to import ragged CSV
>> files, i.e. files that contain variable numbers of columns per row. The
>> requirement is that extra columns would be ignored and missing columns
>> filled with NULL. The client wanting this has wrestled with some
>> preprocessors to try to get what they want, but they would feel happier
>> with this built in. This isn't the first time I have received this
>> request since we implemented CSV import. People have complained on
>> numerous occasions about the strictness of the import routines w.r.t.
>> the number of columns.
>
> Hmm.  Accepting too few columns and filling with nulls isn't any
> different than what INSERT has always done.  But ignoring extra columns
> seems like a different ballgame.  Can you talk your client out of that
> one?  It just seems like a bad idea.

I agree that ignoring extra columns is a bad idea, but I don't even
like the idea of ignoring missing columns.  It doesn't seem like a
good idea to take a spreadsheet and feed it into COPY without doing
any validation anyway, and this is the kind of thing that is trivial
to clean up with a thin layer of Perl or your scripting language of
choice.

...Robert

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

Re: [HACKERS] Bug in aggregates in windowing context

[David Fetter]

On Wed, Sep 09, 2009 at 06:35:05PM -0400, Tom Lane wrote:
> David Fetter  writes:
> > SELECT count(DISTINCT i) OVER () FROM (VALUES (1),(2),(3),(1)) AS s(i);
> > ERROR:  DISTINCT is not implemented for window functions
> 
> Which part of that message isn't clear to you?

A bug that's documented in the source code is still a bug.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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

[HACKERS] Ragged CSV import

[Andrew Dunstan]

I have received a requirement for the ability to import ragged CSV 
files, i.e. files that contain variable numbers of columns per row. The 
requirement is that extra columns would be ignored and missing columns 
filled with NULL. The client wanting this has wrestled with some 
preprocessors to try to get what they want, but they would feel happier 
with this built in. This isn't the first time I have received this 
request since we implemented CSV import. People have complained on 
numerous occasions about the strictness of the import routines w.r.t. 
the number of columns.


I don't think that providing a CSV import mode for reading these files 
will be very difficult or invasive. If it's not acceptable, I will 
provide it to the client as a patch, and I will be retrofitting it to 
8.4 anyway. But I think it's not an unreasonable request.


Thoughts?

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] Bug in aggregates in windowing context

[Tom Lane]

David Fetter  writes:
> SELECT count(DISTINCT i) OVER () FROM (VALUES (1),(2),(3),(1)) AS s(i);
> ERROR:  DISTINCT is not implemented for window functions

Which part of that message isn't clear to you?

> Anyhow, I think it's a bug and needs back-patching.

"Not implemented" is not a bug.  Nice try though.

regards, tom lane

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

Re: [HACKERS] RfD: more powerful "any" types

[Dimitri Fontaine]

Hi,

Tom Lane  writes:
> Hannu Krosing  writes:
>> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by
>> the number in parentheses
>
> Yeah, that idea occurred to me too.  The immediate practical problem is
> that we don't store a typmod for function argument/result types.
> I guess we could look into doing that ...

But still, it looks terrible...

On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote:
> That's beginning to sound a bit like a generics feature.  E.g.,
>
> CREATE FUNCTION the_same(arg_a T, arg_b T) RETURNS bool AS $$
> SELECT arg_a IS DISTINCT FROM arg_b;
> $$;

And even if we don't want to go this far (I'd be in favor of going there
FWIW), we could maybe have a syntax allowing the users to name or
declare the any types he'll need?

CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y)
 RETURNS anyelement y[]
AS $$
... 
$$;

Now we have anyelement and anyelement2, but without the ugly names or
the typmod feature stretching, and we can even have any number of user
defined anyelement types. That behave just like anyelement.

Then, maybe we need VARIADIC anyelement any[] to declare the function as able
to cope with a variable length list of all different kinds of elements?

Regards,
-- 
dim

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

[HACKERS] Bug in aggregates in windowing context

[David Fetter]

Folks,

Elein Mustain mentioned this, and I came up with a short repro.  The
SQL standard does not distinguish between what's available to
aggregates normally and in the windowing context.  However...

SELECT count(DISTINCT i) FROM (VALUES (1),(2),(3),(1)) AS s(i);
 count 
---
 3
(1 row)

SELECT count(DISTINCT i) OVER () FROM (VALUES (1),(2),(3),(1)) AS s(i);
ERROR:  DISTINCT is not implemented for window functions
LINE 1: SELECT count(DISTINCT i) OVER () FROM (VALUES (1),(2),(3),(1...
   ^

I see the error set up in src/backend/parser/parse_func.c, but no real
reasoning why.

Anyhow, I think it's a bug and needs back-patching.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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] [PATCH] plpythonu datatype conversion improvements

[Hannu Krosing]

On Wed, 2009-05-27 at 14:25 -0700, Caleb Welton wrote:
> Yes, in Python >= 2.4 there is the Decimal datatype.  
> 
> However, unlike the other mappings employed by plpythonu, Decimal
> requires an import statement to be in scope.

adding it as already-imported module should not be hard 

I think that moving to saner mappings should at least be discussed

and even if it is not in scope for the user-defined function body there
is nothing that prevents one from using it for conversion.

The Decimal _type_ needs not to be in scope for using Decimal
_instances_

maybe this should/could be controlled by a GUC.



btw, can we currently use funtions in setting GUC parameters ?

if we can , then we could define some python environment initializing
function and then do

ALTER USER xxx SET pyinit = initialise_python_for_xxx()


> -Caleb
> 
> On 5/27/09 2:07 PM, "Tom Lane"  wrote:
> 
> Peter Eisentraut  writes:
> > On Wednesday 27 May 2009 21:53:31 Caleb Welton wrote:
> >> ... My own
> >> feeling on the matter is that PyFloat is the wrong mapping
> for numeric, but
> >> I didn't want to muddy this patch by changing that.
> 
> > Yeah, that one had me wondering for a while as well, but as
> you say it is
> > better to address that separately.
> 
> That was making me itch as well, in my very cursory look at
> the patch.
> Does Python have a saner mapping for it?
> 
> regards, tom lane
> 
-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



-- 
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] RfD: more powerful "any" types

[decibel]

On Sep 9, 2009, at 2:36 PM, Alvaro Herrera wrote:

Robert Haas escribió:


Really, I think we need a type system that doesn't try to represent
every type as a 32-bit integer.  Right now, for example, there's no
reasonable way to write a function that takes another function as an
argument.


Function references would be neat -- I remember wanting to use these a
couple of times (map/reduce?)



Yeah, I recall having a want for that as well, though I can't  
remember what the use case was now. :/


Though that kind of flexibility is probably the most complete  
solution, going with the idea of anyelement(N) might be a lot more  
practical...

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
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] Ragged CSV import

[Andrew Dunstan]

Dann Corbit wrote:

Perhaps something like SQL Server's BCP format files could be used.
http://support.microsoft.com/kb/67409
http://technet.microsoft.com/en-us/library/ms178129.aspx
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm
  



This looks vastly more complex than anything I am interested in doing.

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] Ragged CSV import

[Kevin Grittner]

"Dann Corbit"  wrote: 
> Kevin Grittner
 
>> It's been a while since I've had a need for something like this,
>> but of the copy features not currently available in PostgreSQL,
>> the two most useful are to read in only some of the defined
>> columns, and to output to a separate disk file any rows which
>> failed to match the expected format.
>> The latter would not cause the copy to fail unless the count of
>> such rows exceeded a user-specified threshold.
 
> Perhaps something like SQL Server's BCP format files could be
> used.
> http://support.microsoft.com/kb/67409
> http://technet.microsoft.com/en-us/library/ms178129.aspx
> http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
> http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm
 
That is what I was thinking of when I wrote the above, although in
the Sybase incarnation, before Microsoft split off on their own.  (I
see they haven't changed sybchar from the Sybase name yet)
 
My reservation about referencing it is that it includes so many
bells and whistles that it's not as easy to use as it might be, even
with the "wizard" to generate the format description files.  The
other problem with it is that it was far and away the *least* stable
part of the DBMS.  You could count on it being broken in any version
until six months to a year into that version's life.  We eventually
moved away from it because of the instability -- we could write code
from scratch each time with more confidence of correct behavior.  I
think keeping it a little simpler might help with keeping it stable.
 
-Kevin

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

Re: [HACKERS] COALESCE and NULLIF semantics

[Sam Mason]

On Wed, Sep 09, 2009 at 10:25:34AM -0400, Tom Lane wrote:
> Now admittedly there's probably not any major technical obstacle to
> making a runtime conversion happen --- it's merely delayed invocation of
> the destination type's input function.  But I find it really ugly from a
> theoretical point of view.  Doing calculations with "unknown" values
> just seems wrong.

It's pretty grim.  This seems to be some strange halfway house on the
way to real type-inference, with broken semantics to boot.  How would it
prevent weirdos like:

  SELECT 'msg'||v, date_trunc('year',v), v+10
  FROM (SELECT 'hi ho') x(v);

If Kevin wants something like this it should either be real
type-inference or leave it as it is.  The problem is that each variable
should have exactly one type, type inference lets you delay finding that
type until later down the tree and then back-propagate it back up again.
Your suggestions seems to allow variables to have as many types as it
wants.

-- 
  Sam  http://samason.me.uk/

-- 
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] Ragged CSV import

[Hannu Krosing]

On Wed, 2009-09-09 at 16:34 -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Andrew Dunstan  writes:
> > >> I have received a requirement for the ability to import ragged CSV 
> > >> files, i.e. files that contain variable numbers of columns per row.
> > 
> > BTW, one other thought about this: I think the historical reason for
> > COPY being strict about the number of incoming columns was that it
> > provided a useful cross-check that the parsing hadn't gone off into
> > the weeds.  We have certainly seen enough examples where the reported
> > manifestation of, say, an escaping mistake was that COPY saw the row
> > as having too many or too few columns.  So being permissive about it
> > would lose some error detection capability.  I am not clear about
> > whether CSV format is sufficiently more robust than the traditional
> > COPY format to render this an acceptable loss.  Comments?
> 
> I think accepting less columns and filling with nulls should be
> protected enough for this not to be a problem; if the parser goes nuts,
> it will die eventually.  Silently dropping excessive trailing columns
> does not seem acceptable though; you could lose entire rows and not
> notice.

Maybe we could put a catch-all "text" or even "text[]" column at as the
last one of the table and gather all extra columns there ?

> -- 
> Alvaro Herrerahttp://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.

-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



-- 
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] RfD: more powerful "any" types

[Sam Mason]

On Wed, Sep 09, 2009 at 03:23:52PM -0400, Tom Lane wrote:
> Robert Haas  writes:
> > What we need is a system where base types are represented
> > by an OID, but derived types (list and functional types) are built up
> > using type constructors that take other types as arguments.
> 
> This is SQL, not Haskell.  What you suggest seems about two orders of
> magnitude more complex than real-world applications could justify.

Parametric polymorphism seems to have made it well into the mainstream
now, Java, C# and many other "mainstream" languages have got it, it's
not just ML and Haskell any more.  Excuses of it being of esoteric
academic interest alone seem a little misplaced.

It would also tidy up a lot of the features that already exist in PG.
Arrays in PG already effectively have a type parameter, why not extend
this to normal user's code?  Even staying within the types in PG,
I've wanted to use the geometric functions parametrized over integer
and numeric types before, fixing them to double precision types seems
unfortunate.

-- 
  Sam  http://samason.me.uk/

-- 
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] Ragged CSV import

[Andrew Dunstan]

Josh Berkus wrote:

On 9/9/09 12:59 PM, Andrew Dunstan wrote:
  

I have received a requirement for the ability to import ragged CSV
files, i.e. files that contain variable numbers of columns per row. The
requirement is that extra columns would be ignored and missing columns
filled with NULL. The client wanting this has wrestled with some
preprocessors to try to get what they want, but they would feel happier
with this built in. This isn't the first time I have received this
request since we implemented CSV import. People have complained on
numerous occasions about the strictness of the import routines w.r.t.
the number of columns.



Would this just work on columns on the end, or would it work on the
basis of parsing the CSV header and matching columns?

While the former functionality would be relatively simple, I think the
latter is what people really want.

  


No, it's the former, and that's exactly what the client requested. I'm 
not talking about parsing the header line - that's a much larger can of 
worms.


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] RfD: more powerful "any" types

[Hannu Krosing]

On Wed, 2009-09-09 at 21:57 +0200, Pavel Stehule wrote:
> 2009/9/9 Alvaro Herrera :
> > Pavel Stehule escribió:
> >> 2009/9/9 Alvaro Herrera :
> >
> >> > I already published a pseudo-sprintf function in the wiki here:
> >> > http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
> >> > better, not just the same hacks.
> >> >
> >> > I don't see any good reason that the function needs to be far from core.
> >>
> >> what is use case? Why you need sprintf function, when you have ||
> >> operator. This functionality is redundant and out of standard. What I
> >> know, only MySQL has similar function.
> >
> > Extensive use of || turns into horrible messes quickly.  sprintf() makes
> > this kind of thing much cleaner.  You could use strcat/strcpy in C too,
> > but do you?  You could argue that sprintf is redundant in C, yet it
> > turns out to be extremely useful.
> >
> 
> Yes, I agree. But this functionality you will use only in plpgsql
> language. I thing, so there could be some library that should be
> separated from standard functions. It would be nice, when people
> clearly understand if use some enhancing functionality or some base
> sql functionality.

There is lots of stuff in postgreSQL, especially functions, that is not
"some base sql functionality", yet is in core.

> 
> we could to have schema plpgsql. And there could be function subst,
> 
> then in your plpgsql proc you can call
> 
> if (...) then
>   message := plpgsql.subst('some message:% ...', some value, ...);
>   
> 
> if you would, then you can add plpgsql schema to search path.
> 
> I dislike to use name sprintf, because this or similar function isn't
> real sprintf function - it doesn't use compatible format string with
> sprintf function.

call it format(txt, variadic "any") - that's what it does

> regards
> Pavel Stehule
> 
> > One use case is using it for error messages in RAISE/USING.  Yes, I am
> > aware you can use concatenation there.
> >
> > --
> > Alvaro Herrerahttp://www.CommandPrompt.com/
> > 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] Ragged CSV import

[Kevin Grittner]

Josh Berkus  wrote:
 
> Would this just work on columns on the end, or would it work on the
> basis of parsing the CSV header and matching columns?
> 
> While the former functionality would be relatively simple, I think
the
> latter is what people really want.
 
It's been a while since I've had a need for something like this, but
of
the copy features not currently available in PostgreSQL, the two most
useful are to read in only some of the defined columns, and to output
to
a separate disk file any rows which failed to match the expected
format.
The latter would not cause the copy to fail unless the count of such
rows exceeded a user-specified threshold.
 
-Kevin

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

Re: [HACKERS] Ragged CSV import

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  
I have received a requirement for the ability to import ragged CSV 
files, i.e. files that contain variable numbers of columns per row. The 
requirement is that extra columns would be ignored and missing columns 
filled with NULL. The client wanting this has wrestled with some 
preprocessors to try to get what they want, but they would feel happier 
with this built in. This isn't the first time I have received this 
request since we implemented CSV import. People have complained on 
numerous occasions about the strictness of the import routines w.r.t. 
the number of columns.



Hmm.  Accepting too few columns and filling with nulls isn't any
different than what INSERT has always done.  But ignoring extra columns
seems like a different ballgame.  Can you talk your client out of that
one?  It just seems like a bad idea.
  


No, that's critical. The application this is wanted for uploads data 
that users put in spreadsheets. The users apparently expect that they 
will be able to put comments on some rows off to the right of the data 
they want loaded, and have it ignored.


To answer your other point made later, my intention was to make this 
optional behaviour, not default behaviour.  I agree that it would be too 
slack for default behaviour. Yes, we have quite a few options, but 
that's not surprising in dealing with a format that is at best 
ill-defined and which we do not control.



As for the "numerous occasions", maybe I've not been paying attention,
but I don't recall any ...

  



The requests have been made on IRC, at conferences, in private emails.

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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Hannu Krosing :
> On Wed, 2009-09-09 at 21:57 +0200, Pavel Stehule wrote:
>> 2009/9/9 Alvaro Herrera :
>> > Pavel Stehule escribió:
>> >> 2009/9/9 Alvaro Herrera :
>> >
>> >> > I already published a pseudo-sprintf function in the wiki here:
>> >> > http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
>> >> > better, not just the same hacks.
>> >> >
>> >> > I don't see any good reason that the function needs to be far from core.
>> >>
>> >> what is use case? Why you need sprintf function, when you have ||
>> >> operator. This functionality is redundant and out of standard. What I
>> >> know, only MySQL has similar function.
>> >
>> > Extensive use of || turns into horrible messes quickly.  sprintf() makes
>> > this kind of thing much cleaner.  You could use strcat/strcpy in C too,
>> > but do you?  You could argue that sprintf is redundant in C, yet it
>> > turns out to be extremely useful.
>> >
>>
>> Yes, I agree. But this functionality you will use only in plpgsql
>> language. I thing, so there could be some library that should be
>> separated from standard functions. It would be nice, when people
>> clearly understand if use some enhancing functionality or some base
>> sql functionality.
>
> There is lots of stuff in postgreSQL, especially functions, that is not
> "some base sql functionality", yet is in core.
>
>>
>> we could to have schema plpgsql. And there could be function subst,
>>
>> then in your plpgsql proc you can call
>>
>> if (...) then
>>   message := plpgsql.subst('some message:% ...', some value, ...);
>>   
>>
>> if you would, then you can add plpgsql schema to search path.
>>
>> I dislike to use name sprintf, because this or similar function isn't
>> real sprintf function - it doesn't use compatible format string with
>> sprintf function.
>
> call it format(txt, variadic "any") - that's what it does

why not?

this function should be in contrib - as variadic function sample.

Pavel

>
>> regards
>> Pavel Stehule
>>
>> > One use case is using it for error messages in RAISE/USING.  Yes, I am
>> > aware you can use concatenation there.
>> >
>> > --
>> > Alvaro Herrera                                http://www.CommandPrompt.com/
>> > 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] Ragged CSV import

[Josh Berkus]

On 9/9/09 12:59 PM, Andrew Dunstan wrote:
> 
> I have received a requirement for the ability to import ragged CSV
> files, i.e. files that contain variable numbers of columns per row. The
> requirement is that extra columns would be ignored and missing columns
> filled with NULL. The client wanting this has wrestled with some
> preprocessors to try to get what they want, but they would feel happier
> with this built in. This isn't the first time I have received this
> request since we implemented CSV import. People have complained on
> numerous occasions about the strictness of the import routines w.r.t.
> the number of columns.

Would this just work on columns on the end, or would it work on the
basis of parsing the CSV header and matching columns?

While the former functionality would be relatively simple, I think the
latter is what people really want.

-- 
Josh Berkus
PostgreSQL Experts Inc.
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] Ragged CSV import

[Alvaro Herrera]

Tom Lane wrote:
> Andrew Dunstan  writes:
> >> I have received a requirement for the ability to import ragged CSV 
> >> files, i.e. files that contain variable numbers of columns per row.
> 
> BTW, one other thought about this: I think the historical reason for
> COPY being strict about the number of incoming columns was that it
> provided a useful cross-check that the parsing hadn't gone off into
> the weeds.  We have certainly seen enough examples where the reported
> manifestation of, say, an escaping mistake was that COPY saw the row
> as having too many or too few columns.  So being permissive about it
> would lose some error detection capability.  I am not clear about
> whether CSV format is sufficiently more robust than the traditional
> COPY format to render this an acceptable loss.  Comments?

I think accepting less columns and filling with nulls should be
protected enough for this not to be a problem; if the parser goes nuts,
it will die eventually.  Silently dropping excessive trailing columns
does not seem acceptable though; you could lose entire rows and not
notice.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] RfD: more powerful "any" types

[Tom Lane]

Hannu Krosing  writes:
> maybe we could (re/ab)use parametrized types and define

> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by
> the number in parentheses

Yeah, that idea occurred to me too.  The immediate practical problem is
that we don't store a typmod for function argument/result types.
I guess we could look into doing 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] RfD: more powerful "any" types

[Hannu Krosing]

On Wed, 2009-09-09 at 15:10 -0400, Robert Haas wrote:
> On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane wrote:
> > Alvaro Herrera  writes:
> >> Another possible example is sprintf:
> >
> >> create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
> >> returns text
> >
> >> In order for this to work in general, we'd need FUNC_MAX_ARGS different
> >> types, which is currently defined as 100 in our code.
> >
> > But here, "any" would work perfectly fine, since there's no need for
> > any two arguments to be tied to each other or the result.
> >
> > Given that we've got away so far with only 1 instance of anyelement,
> > I'm not really convinced that there's a market for more than anyelement2
> > (and anyarray2, etc).
> 
> I'm going to go out on a limb and say that if we're going to bother
> changing the status quo, we ought to create a reasonable number of
> these - maybe, say, four.  I can't see needing a hundred of these, but
> I don't think that we should assume that our inability to think of a
> use for more than two at the moment implies that there can never be
> one.
> 
> Really, I think we need a type system that doesn't try to represent
> every type as a 32-bit integer.  Right now, for example, there's no
> reasonable way to write a function that takes another function as an
> argument.  What we need is a system where base types are represented
> by an OID, but derived types (list and functional types) are built up
> using type constructors that take other types as arguments.

There is nothing that prevents us from representing those by an OID as
well.

Though how to define and store those in pg_type is another issue. 

>   So you
> could have a types like list(integer) or list(anyelement) or
> function(integer,bool) [meaning either taking an integer and returning
> a bool, or the other way around, depending on your notational
> preference].  Then you can have functions with complex types like:
> 
> maplist : 
> function(anyelement,anyelement2,function(list(anyelement),list(anyelement2)))
> 
> This would have the fringe benefit of eliminating types like anyarray
> (which is just list(anyelement)) and the need to list every type twice
> in pg_type, once for the base type and once for the derived array
> type.
> 
> 
> 
> ...Robert

-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



-- 
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] Ragged CSV import

[Tom Lane]

Andrew Dunstan  writes:
>> I have received a requirement for the ability to import ragged CSV 
>> files, i.e. files that contain variable numbers of columns per row.

BTW, one other thought about this: I think the historical reason for
COPY being strict about the number of incoming columns was that it
provided a useful cross-check that the parsing hadn't gone off into
the weeds.  We have certainly seen enough examples where the reported
manifestation of, say, an escaping mistake was that COPY saw the row
as having too many or too few columns.  So being permissive about it
would lose some error detection capability.  I am not clear about
whether CSV format is sufficiently more robust than the traditional
COPY format to render this an acceptable loss.  Comments?

(One possible answer to this is to make the behavior optional, though
surely COPY has got too many options 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] RfD: more powerful "any" types

[Hannu Krosing]

On Wed, 2009-09-09 at 09:39 -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > Well, so far we've only seen use cases in this thread that either
> > already work or that are not well-defined. ;-)
> 
> Well, yeah, the question is can we extract a clear TODO item here.
> 
> I think there are two somewhat orthogonal issues:
> 
> 1. Is a completely unconstrained argument type (ie "any") of any real
> use to PL functions, and if so how can we expose that usefulness?
> The only clear thing to do with such an argument is IS NULL/IS NOT NULL
> tests, which might or might not be worth the trouble.
> 
> 2. Is there any use for arguments with type constraints not covered
> by the existing ANYFOO rules, and if so what do we add for that?
> 
> One comment on point 2 is that it was foreseen from the beginning
> that there would be need for ANYELEMENT2 etc, and I'm actually rather
> surprised that we've gone this long without adding them. 

Where we could need anyelement2 and enyelement3 is if we need the
sameness of any 2 parameters or OUT parameter types

maybe we could (re/ab)use parametrized types and define

anyelement(1), anyelement(2), ..., anyelement(N) and then match them by
the number in parentheses

>  Alvaro made
> a good point about not wanting to multiply the various hard-wired
> OID references, but perhaps some judicious code refactoring could
> prevent a notational disaster.
> 
>   regards, tom lane

-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training



-- 
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] Ragged CSV import

[Alvaro Herrera]

Tom Lane wrote:

> As for the "numerous occasions", maybe I've not been paying attention,
> but I don't recall any ...

I don't know about numerous, but I've seen it in the spanish list; for
example:

http://archives.postgresql.org/pgsql-es-ayuda/2007-03/msg00901.php

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] suggestion to improve planer

[Ľubomír Varga]

On Wednesday 09 September 2009 14:11:41 Peter Eisentraut wrote:
> On Thu, 2009-09-03 at 10:35 +0200, Ľubomír Varga wrote:
> > Hi.
> >
> > I hope, that this is right mailing list.
> >
> > SELECT date, value FROM t_event
> > WHERE t_event.id in (SELECT id FROM t_event
> > WHERE date < '2009-08-25'
> > ORDER BY date DESC LIMIT 1)
> > ORDER BY date;
> > cost 6.4
> >
> > SELECT date, value FROM t_event
> > WHERE t_event.id = (SELECT id FROM t_event
> > WHERE date < '2009-08-25'
> > ORDER BY date DESC LIMIT 1)
> > ORDER BY date;
> > cost 6.36..6.37
> >
> >
> > Why that two query dont have equal cost? If it is not problem, try add
> > some planer code to recognize that sublesect HAVE TO return just one row
> > (limit 1) and in plan could be used filter/index scan instead of hash
> > aggregate.
>
> Well, there is always a tradeoff between more planner analysis and more
> complicated and slow planning.  Seeing that the cost estimates are close
> enough for practical purposes, it doesn't seem worthwhile to fix
> anything here.
>
> >  I have
> > also some complex query examples where cost difference is more visible.
>
> Having real examples where a change might actually improve runtime is
> always more interesting than an academic exercise like the above.

Oka, real world example is attached as txt file. There are 3x2 queries and its 
costs on my system/database. System/database is also real, and production. 
First set for short time interval and second for long time interval.

Main poblem is, that I want to generate some statistical report and I dont 
know for how many "devices" Iam going to generate it for. So if I make some 
program, there will be something like:
..."t_device.imei in (" + this.getDevicesImeis() + ")"...
If i have only one device, there could be "=" instead of "in".

PS: Iam very confused about actual costs. I dont have exact query on which I 
have seen that "in" for one device in array, is much worse than "=" for one 
device. Actual costs are somehow different and "=" select gives about ten 
time worse cost.

So probably just ignore my mail and keep planner as is.
My version of postgre: "PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by 
GCC x86_64-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1-r3)"

-- 
Odborník na všetko je zlý odborník. Ja sa snažím byť výnimkou potvrdzujúcou 
pravidlo.
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
 UNION
 SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk = (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
 ORDER BY spz_number, date;
cost 2573



explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
 UNION
 SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk in (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
 ORDER BY spz_number, date;
cost 2109



explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10 
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
 UNION
 SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FR

Re: [HACKERS] Ragged CSV import

[Tom Lane]

Andrew Dunstan  writes:
> I have received a requirement for the ability to import ragged CSV 
> files, i.e. files that contain variable numbers of columns per row. The 
> requirement is that extra columns would be ignored and missing columns 
> filled with NULL. The client wanting this has wrestled with some 
> preprocessors to try to get what they want, but they would feel happier 
> with this built in. This isn't the first time I have received this 
> request since we implemented CSV import. People have complained on 
> numerous occasions about the strictness of the import routines w.r.t. 
> the number of columns.

Hmm.  Accepting too few columns and filling with nulls isn't any
different than what INSERT has always done.  But ignoring extra columns
seems like a different ballgame.  Can you talk your client out of that
one?  It just seems like a bad idea.

As for the "numerous occasions", maybe I've not been paying attention,
but I don't recall any ...

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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Alvaro Herrera :
> Pavel Stehule escribió:
>> 2009/9/9 Alvaro Herrera :
>
>> > I already published a pseudo-sprintf function in the wiki here:
>> > http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
>> > better, not just the same hacks.
>> >
>> > I don't see any good reason that the function needs to be far from core.
>>
>> what is use case? Why you need sprintf function, when you have ||
>> operator. This functionality is redundant and out of standard. What I
>> know, only MySQL has similar function.
>
> Extensive use of || turns into horrible messes quickly.  sprintf() makes
> this kind of thing much cleaner.  You could use strcat/strcpy in C too,
> but do you?  You could argue that sprintf is redundant in C, yet it
> turns out to be extremely useful.
>

Yes, I agree. But this functionality you will use only in plpgsql
language. I thing, so there could be some library that should be
separated from standard functions. It would be nice, when people
clearly understand if use some enhancing functionality or some base
sql functionality.

we could to have schema plpgsql. And there could be function subst,

then in your plpgsql proc you can call

if (...) then
  message := plpgsql.subst('some message:% ...', some value, ...);
  

if you would, then you can add plpgsql schema to search path.

I dislike to use name sprintf, because this or similar function isn't
real sprintf function - it doesn't use compatible format string with
sprintf function.

regards
Pavel Stehule

> One use case is using it for error messages in RAISE/USING.  Yes, I am
> aware you can use concatenation there.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> 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] RfD: more powerful "any" types

[Alvaro Herrera]

Pavel Stehule escribió:
> 2009/9/9 Alvaro Herrera :

> > I already published a pseudo-sprintf function in the wiki here:
> > http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
> > better, not just the same hacks.
> >
> > I don't see any good reason that the function needs to be far from core.
> 
> what is use case? Why you need sprintf function, when you have ||
> operator. This functionality is redundant and out of standard. What I
> know, only MySQL has similar function.

Extensive use of || turns into horrible messes quickly.  sprintf() makes
this kind of thing much cleaner.  You could use strcat/strcpy in C too,
but do you?  You could argue that sprintf is redundant in C, yet it
turns out to be extremely useful.

One use case is using it for error messages in RAISE/USING.  Yes, I am
aware you can use concatenation there.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Robert Haas :
> On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane wrote:
>> Alvaro Herrera  writes:
>>> Another possible example is sprintf:
>>
>>> create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
>>> returns text
>>
>>> In order for this to work in general, we'd need FUNC_MAX_ARGS different
>>> types, which is currently defined as 100 in our code.
>>
>> But here, "any" would work perfectly fine, since there's no need for
>> any two arguments to be tied to each other or the result.
>>
>> Given that we've got away so far with only 1 instance of anyelement,
>> I'm not really convinced that there's a market for more than anyelement2
>> (and anyarray2, etc).
>
> I'm going to go out on a limb and say that if we're going to bother
> changing the status quo, we ought to create a reasonable number of
> these - maybe, say, four.  I can't see needing a hundred of these, but
> I don't think that we should assume that our inability to think of a
> use for more than two at the moment implies that there can never be
> one.
>
> Really, I think we need a type system that doesn't try to represent
> every type as a 32-bit integer.  Right now, for example, there's no
> reasonable way to write a function that takes another function as an
> argument.  What we need is a system where base types are represented
> by an OID, but derived types (list and functional types) are built up
> using type constructors that take other types as arguments.  So you
> could have a types like list(integer) or list(anyelement) or
> function(integer,bool) [meaning either taking an integer and returning
> a bool, or the other way around, depending on your notational
> preference].  Then you can have functions with complex types like:
>
> maplist : 
> function(anyelement,anyelement2,function(list(anyelement),list(anyelement2)))
>
> This would have the fringe benefit of eliminating types like anyarray
> (which is just list(anyelement)) and the need to list every type twice
> in pg_type, once for the base type and once for the derived array
> type.

it would be nice, but probably it could significant increase parsing
query time. And this is +/- equal to what my transformationHook does.

regards
Pavel Stehule

>
> 
>
> ...Robert
>
> --
> 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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Alvaro Herrera :
> Pavel Stehule escribió:
>> 2009/9/9 Alvaro Herrera :
>> > Pavel Stehule escribió:
>> >
>> >> we are able to write sprintf(text, variadic "any") returns text, but only 
>> >> in C
>> >
>> > Hmm, should we provide that function in core?
>>
>> We should it, but I prefer some pgfoundry or contrib package. sprintf
>> is really far to SQL. What more, we knows types, so some format tags
>> are useless. Using original sprintf function is possible, but needs
>> lot of code, because you need to transform PostgreSQL types to C
>> types, and we have not any helping function for this task.
>>
>> Some similar to plpgsql's RAISE statement is some +/- 20 rows
>
> I already published a pseudo-sprintf function in the wiki here:
> http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
> better, not just the same hacks.
>
> I don't see any good reason that the function needs to be far from core.

what is use case? Why you need sprintf function, when you have ||
operator. This functionality is redundant and out of standard. What I
know, only MySQL has similar function.

Please, try to compile and run sprintf function from attachment

postgres=# select sprintf('1:% 2:% 3:%', 10,null, 'kuku');
  sprintf

 1:10 2:NULL 3:kuku
(1 row)

postgres=# select sprintf('Today is %, I am %.', current_date, current_user);
 sprintf
--
 Today is 2009-09-09, I am pavel.
(1 row)

Regards
Pavel

>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>


sprintf.tgz
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] RfD: more powerful "any" types

[Alvaro Herrera]

Robert Haas escribió:

> Really, I think we need a type system that doesn't try to represent
> every type as a 32-bit integer.  Right now, for example, there's no
> reasonable way to write a function that takes another function as an
> argument.

Function references would be neat -- I remember wanting to use these a
couple of times (map/reduce?)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] CTE bug?

[Tom Lane]

David Fetter  writes:
> On Wed, Sep 09, 2009 at 03:00:39PM -0400, Tom Lane wrote:
>> David Fetter  writes:
>>> Should the outer query be able to reference further-in CTEs?
>> 
>> No, why would you expect that?

> No particular reason, I suppose.  I'm not clear on what the standard
> says about this.

The standard says that the scope of a WITH is the  it precedes.

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] RfD: more powerful "any" types

[Tom Lane]

Robert Haas  writes:
> What we need is a system where base types are represented
> by an OID, but derived types (list and functional types) are built up
> using type constructors that take other types as arguments.

This is SQL, not Haskell.  What you suggest seems about two orders of
magnitude more complex than real-world applications could justify.

(so where is pl/haskell, anyway?)

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] CTE bug?

[David Fetter]

On Wed, Sep 09, 2009 at 03:00:39PM -0400, Tom Lane wrote:
> David Fetter  writes:
> > Should the outer query be able to reference further-in CTEs?
> 
> No, why would you expect that?

No particular reason, I suppose.  I'm not clear on what the standard
says about this.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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] RfD: more powerful "any" types

[Robert Haas]

On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane wrote:
> Alvaro Herrera  writes:
>> Another possible example is sprintf:
>
>> create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
>> returns text
>
>> In order for this to work in general, we'd need FUNC_MAX_ARGS different
>> types, which is currently defined as 100 in our code.
>
> But here, "any" would work perfectly fine, since there's no need for
> any two arguments to be tied to each other or the result.
>
> Given that we've got away so far with only 1 instance of anyelement,
> I'm not really convinced that there's a market for more than anyelement2
> (and anyarray2, etc).

I'm going to go out on a limb and say that if we're going to bother
changing the status quo, we ought to create a reasonable number of
these - maybe, say, four.  I can't see needing a hundred of these, but
I don't think that we should assume that our inability to think of a
use for more than two at the moment implies that there can never be
one.

Really, I think we need a type system that doesn't try to represent
every type as a 32-bit integer.  Right now, for example, there's no
reasonable way to write a function that takes another function as an
argument.  What we need is a system where base types are represented
by an OID, but derived types (list and functional types) are built up
using type constructors that take other types as arguments.  So you
could have a types like list(integer) or list(anyelement) or
function(integer,bool) [meaning either taking an integer and returning
a bool, or the other way around, depending on your notational
preference].  Then you can have functions with complex types like:

maplist : 
function(anyelement,anyelement2,function(list(anyelement),list(anyelement2)))

This would have the fringe benefit of eliminating types like anyarray
(which is just list(anyelement)) and the need to list every type twice
in pg_type, once for the base type and once for the derived array
type.



...Robert

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Tom Lane]

Stephen Frost  writes:
> In general, I think that sounds like a good idea.  At the same time, I
> wouldn't be against changing the specific 'ALL' special-case comparison
> in 8.4.2, using the argument that not many people have moved to it yet
> and it's pretty far out there for an 'ALL' database to exist anyway..

I don't think this is back-patch material.  We've had what, one
complaint in twelve years?  The odds of causing a problem seem
higher than the odds of preventing 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] CTE bug?

[Tom Lane]

David Fetter  writes:
> Should the outer query be able to reference further-in CTEs?

No, why would you expect 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] [PATCH] plpythonu datatype conversion improvements

[Peter Eisentraut]

On mån, 2009-08-31 at 23:41 +0300, Peter Eisentraut wrote:
> On sön, 2009-08-16 at 02:44 +0300, Peter Eisentraut wrote:
> > The remaining problem is that the patch loses domain checking on the
> > return types, because some paths no longer go through the data type's
> > input function.  I have marked these places as FIXME, and the regression
> > tests also contain a failing test case for this.
> > 
> > What's needed here, I think, is an API that takes a datum plus type
> > information and checks whether the datum is valid within the domain.  I
> > haven't found one that is exported, but maybe someone could give a tip.
> 
> Got that fixed now.  Updated patch is attached.  I will sleep over it,
> but I think it's good to go.

committed


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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Alvaro Herrera  writes:
> > Andrew Dunstan wrote:
> >> It will affect any dbname or username in mixed or upper case, not just
> >> ALL, won't it?
> 
> > No, I am suggesting to change only the comparisons to the literals
> > "all", "sameuser", "samegroup" and "samerole".
> 
> Hmm.  These words are effectively keywords, so +1 for treating them
> case-insensitively, as we do in SQL.  But I wonder whether there isn't
> an argument for making the comparisons of role and database names
> behave more like SQL, too --- that is FOO matches foo but not "FOO".

In general, I think that sounds like a good idea.  At the same time, I
wouldn't be against changing the specific 'ALL' special-case comparison
in 8.4.2, using the argument that not many people have moved to it yet
and it's pretty far out there for an 'ALL' database to exist anyway..

Might be too much for a point-release. :/

Just my 2c.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] RfD: more powerful "any" types

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > BTW does "any" match other pseudotypes?  Would I be able to pass a
> > cstring into "any"?  That would create a large security hole I think.
> 
> How so?  'Cause you can do that now with anyelement.

Hmm, it doesn't seem to be allowed?

alvherre=# create function anyelem2 (anyelement) returns int language plpgsql 
as $$ begin return 1; end $$;
CREATE FUNCTION
alvherre=# select anyelem2(textout('oh'));
ERROR:  PL/pgSQL functions cannot accept type cstring
CONTEXTO:  compilation of PL/pgSQL function "anyelem2" near line 0


(BTW I find it a bit funny that lines are counted from 0.  I never
noticed that before).


> cstring is only a pseudotype for historical reasons, anyway --- there's
> nothing about it now that's not a real type.  I think we just have it
> that way to discourage people from storing it in tables.

Wow, it has I/O functions and all.  Amazing, I wasn't aware of that.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] CTE bug?

[David Fetter]

On Tue, Sep 08, 2009 at 11:37:14PM -0400, Tom Lane wrote:
> I wrote:
> > David Fetter  writes:
> >> WITH RECURSIVE t(j) AS (
> >> WITH RECURSIVE s(i) AS (
> >> VALUES (1)
> >> UNION ALL
> >> SELECT i+1 FROM s WHERE i < 10
> >> ) SELECT i AS j FROM s
> >> UNION ALL
> >> SELECT j+1 FROM t WHERE j < 10
> >> )
> >> SELECT * FROM t;
> >> ERROR:  relation "s" does not exist
> >> LINE 6: ) SELECT i AS j FROM s
> >>  ^
> >> Shouldn't this work?
> 
> > Huh, nice test case.  It looks like it's trying to do the "throwaway
> > parse analysis" of the nonrecursive term (around line 200 of
> > parse_cte.c) without having analyzed the inner WITH clause.  We could
> > probably fix it by doing a throwaway analysis of the inner WITH too
> > ... but ... that whole throwaway thing is pretty ugly and objectionable
> > from a performance standpoint anyhow.  I wonder if it wouldn't be better
> > to refactor so that transformSetOperationStmt knows when it's dealing
> > with the body of a recursive UNION and does the analyzeCTETargetList
> > business after having processed the first UNION arm.
> 
> I've committed a fix along those lines.  Too late for 8.4.1
> unfortunately :-(.  In the meantime, you could work around the
> problem in this particular case with some more parentheses:
> 
> WITH RECURSIVE t(j) AS (
>   (
> WITH RECURSIVE s(i) AS (
> VALUES (1)
> UNION ALL
> SELECT i+1 FROM s WHERE i < 10
> ) SELECT i AS j FROM s
>   )
> UNION ALL
> SELECT j+1 FROM t WHERE j < 10
> )
> SELECT * FROM t;
> 
>   regards, tom lane

I tested this with deeper-nested structures, and ran across another question:

Should the outer query be able to reference further-in CTEs?

WITH RECURSIVE s(i) AS (
WITH RECURSIVE t(j) AS (
VALUES(1)
UNION ALL
SELECT j+1 FROM t WHERE j < 10
)
SELECT j AS i FROM t
UNION ALL
SELECT i+1 FROM s WHERE i < 10
)
SELECT * FROM s,t;
ERROR:  relation "t" does not exist
LINE 11: SELECT * FROM s,t;
 ^
Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

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] RfD: more powerful "any" types

[James Pye]

On Sep 8, 2009, at 10:48 AM, David Fetter wrote:

I'd like to see pseudo-types like ANYNUMERIC, and allow it to take an
array decorator, which would really help for math-ish functions.  Not
sure where that fits in this discussion.


Perhaps typcategory could be leveraged here?


..Tho, if I understand the general direction, I think it would be  
along the lines of type classes/interfaces..


--
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] RfD: more powerful "any" types

[Alvaro Herrera]

Pavel Stehule escribió:
> 2009/9/9 Alvaro Herrera :
> > Pavel Stehule escribió:
> >
> >> we are able to write sprintf(text, variadic "any") returns text, but only 
> >> in C
> >
> > Hmm, should we provide that function in core?
> 
> We should it, but I prefer some pgfoundry or contrib package. sprintf
> is really far to SQL. What more, we knows types, so some format tags
> are useless. Using original sprintf function is possible, but needs
> lot of code, because you need to transform PostgreSQL types to C
> types, and we have not any helping function for this task.
> 
> Some similar to plpgsql's RAISE statement is some +/- 20 rows

I already published a pseudo-sprintf function in the wiki here:
http://wiki.postgresql.org/wiki/Sprintf  I'm looking for something
better, not just the same hacks.

I don't see any good reason that the function needs to be far from core.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] RfD: more powerful "any" types

[James Pye]

On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote:

That's beginning to sound a bit like a generics feature.  E.g.,

CREATE FUNCTION the_same(arg_a T, arg_b T) RETURNS bool AS $$
SELECT arg_a IS DISTINCT FROM arg_b;
$$;


mmm, yeah... ISTM that expansion in this area should probably head  
toward generics..


Does SQL spec such a thing?

--
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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Alvaro Herrera :
> Pavel Stehule escribió:
>
>> we are able to write sprintf(text, variadic "any") returns text, but only in 
>> C
>
> Hmm, should we provide that function in core?

We should it, but I prefer some pgfoundry or contrib package. sprintf
is really far to SQL. What more, we knows types, so some format tags
are useless. Using original sprintf function is possible, but needs
lot of code, because you need to transform PostgreSQL types to C
types, and we have not any helping function for this task.

Some similar to plpgsql's RAISE statement is some +/- 20 rows

regards
Pavel Stehule


>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> 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] RfD: more powerful "any" types

[Alvaro Herrera]

Pavel Stehule escribió:

> we are able to write sprintf(text, variadic "any") returns text, but only in C

Hmm, should we provide that function in core?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 Tom Lane :
> "David E. Wheeler"  writes:
>> Yes, that sounds about right. Is that not basically what Alvaro was
>> looking for to start with? And is there an "any" array that could work
>> for variadic functions like sprintf(), as well?
>
> Well, no, because arrays are inherently all the same element type.
> You could try to do sprintf as
>
>        sprintf(text, variadic anyarray) returns text
>
> but this constrains all the arguments to be the same type, which is
> not what you want.  The variadic mechanism doesn't have the ability
> to deal with what you're suggesting, and I'm not sure we want to try
> to make it do that.

variadic "any" isn't transformed to array.

we are able to write sprintf(text, variadic "any") returns text, but only in C

regards
Pavel Stehule

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

-- 
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] RfD: more powerful "any" types

[Pavel Stehule]

2009/9/9 David E. Wheeler :
> On Sep 9, 2009, at 10:17 AM, Tom Lane wrote:
>
>> Well, none, *if* it's defined to have exactly the same runtime behavior
>> as anyelement does.  It sounded like you were arguing for something
>> looser.  We could certainly define it as being just like anyelement
>> but not constrained to match any other argument or result (and, hence,
>> not usable as a result type).
>
> Yes, that sounds about right. Is that not basically what Alvaro was looking
> for to start with? And is there an "any" array that could work for variadic
> functions like sprintf(), as well?

no - because PostgreSQL doesn't support multitype array. So VARIADIC
"any" isn't transformed to array and arguments are accessable via
FunctionCallInfo structure. I thing, so this functionality is out of
plpgsql or sql language, but when we are able to transform
FunctionCallInfo to some perl or python structures, this can be
accessed from plperl or plpythonu.

Regards
Pavel Stehule


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

-- 
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] corrupted double-linked list

[Tom Lane]

Maarten =?ISO-8859-1?Q?Foqu=E9?=  writes:
> The program crashes on PQclear(resu) with the following:
> *** glibc detected *** ./incident_relay: corrupted double-linked list:
> 0x09c459c8 ***

This looks like a memory-stomp type of bug.  There's no reason to assume
that PQclear is the cause; it's more likely the victim of a stomp that
occurred sometime earlier.  I'd suggest running the app under valgrind
or electric fence or some other memory-overrun-catcher.

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] RfD: more powerful "any" types

[Tom Lane]

"David E. Wheeler"  writes:
> Yes, that sounds about right. Is that not basically what Alvaro was  
> looking for to start with? And is there an "any" array that could work  
> for variadic functions like sprintf(), as well?

Well, no, because arrays are inherently all the same element type.
You could try to do sprintf as

sprintf(text, variadic anyarray) returns text

but this constrains all the arguments to be the same type, which is
not what you want.  The variadic mechanism doesn't have the ability
to deal with what you're suggesting, and I'm not sure we want to try
to make it do 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] RfD: more powerful "any" types

[Tom Lane]

Alvaro Herrera  writes:
> BTW does "any" match other pseudotypes?  Would I be able to pass a
> cstring into "any"?  That would create a large security hole I think.

How so?  'Cause you can do that now with anyelement.

cstring is only a pseudotype for historical reasons, anyway --- there's
nothing about it now that's not a real type.  I think we just have it
that way to discourage people from storing it in tables.

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] RfD: more powerful "any" types

[David E. Wheeler]

On Sep 9, 2009, at 10:22 AM, Tom Lane wrote:


Well, only if you write your functions in C. I'd like to be able to
write sprintf() in PL/pgSQL. Or PL/Perl, for that matter.


I think you're confusing the point with a secondary issue, which is  
what

access we provide to these pseudotypes in PLs.  To write sprintf in a
PL, you'd at least need the ability to cast "any" to text.  I guess  
you
can do that with anyelement, though, so maybe there is nothing much  
here

except an overly restrictive safety check.



Yes, exactly.

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] RfD: more powerful "any" types

[David E. Wheeler]

On Sep 9, 2009, at 10:17 AM, Tom Lane wrote:

Well, none, *if* it's defined to have exactly the same runtime  
behavior

as anyelement does.  It sounded like you were arguing for something
looser.  We could certainly define it as being just like anyelement
but not constrained to match any other argument or result (and, hence,
not usable as a result type).


Yes, that sounds about right. Is that not basically what Alvaro was  
looking for to start with? And is there an "any" array that could work  
for variadic functions like sprintf(), as well?


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] RfD: more powerful "any" types

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > Another possible example is sprintf:
> 
> > create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
> > returns text
> 
> > In order for this to work in general, we'd need FUNC_MAX_ARGS different
> > types, which is currently defined as 100 in our code.
> 
> But here, "any" would work perfectly fine, since there's no need for
> any two arguments to be tied to each other or the result.

Yup.

BTW does "any" match other pseudotypes?  Would I be able to pass a
cstring into "any"?  That would create a large security hole I think.

> Given that we've got away so far with only 1 instance of anyelement,
> I'm not really convinced that there's a market for more than anyelement2
> (and anyarray2, etc).

Well, if we have something general like a constrained "any", then I
agree.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] RfD: more powerful "any" types

[Tom Lane]

"David E. Wheeler"  writes:
> On Sep 9, 2009, at 10:15 AM, Tom Lane wrote:
>> But here, "any" would work perfectly fine, since there's no need for
>> any two arguments to be tied to each other or the result.

> Well, only if you write your functions in C. I'd like to be able to  
> write sprintf() in PL/pgSQL. Or PL/Perl, for that matter.

I think you're confusing the point with a secondary issue, which is what
access we provide to these pseudotypes in PLs.  To write sprintf in a
PL, you'd at least need the ability to cast "any" to text.  I guess you
can do that with anyelement, though, so maybe there is nothing much here
except an overly restrictive safety check.

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] RfD: more powerful "any" types

[Tom Lane]

"David E. Wheeler"  writes:
> On Sep 9, 2009, at 10:04 AM, Tom Lane wrote:
>> Well, yeah: it looks like a fertile source of security holes, not to
>> mention implementation difficulties (plpgsql really wants well-typed
>> expressions...).  What you can do at the C level is not necessarily
>> sane to give to PL authors.  I'm willing to consider a carefully  
>> spec'd out proposal in this area, but "open the floodgates" ain't it.

> Security holes? Huh? What security holes would there be that you don't  
> already have with anyelement?

Well, none, *if* it's defined to have exactly the same runtime behavior
as anyelement does.  It sounded like you were arguing for something
looser.  We could certainly define it as being just like anyelement
but not constrained to match any other argument or result (and, hence,
not usable as a result type).

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] RfD: more powerful "any" types

[David E. Wheeler]

On Sep 9, 2009, at 10:15 AM, Tom Lane wrote:

In order for this to work in general, we'd need FUNC_MAX_ARGS  
different

types, which is currently defined as 100 in our code.


But here, "any" would work perfectly fine, since there's no need for
any two arguments to be tied to each other or the result.


Well, only if you write your functions in C. I'd like to be able to  
write sprintf() in PL/pgSQL. Or PL/Perl, for that matter.


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] RfD: more powerful "any" types

[Tom Lane]

Alvaro Herrera  writes:
> Another possible example is sprintf:

> create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
> returns text

> In order for this to work in general, we'd need FUNC_MAX_ARGS different
> types, which is currently defined as 100 in our code.

But here, "any" would work perfectly fine, since there's no need for
any two arguments to be tied to each other or the result.

Given that we've got away so far with only 1 instance of anyelement,
I'm not really convinced that there's a market for more than anyelement2
(and anyarray2, etc).

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] RfD: more powerful "any" types

[Alvaro Herrera]

Tom Lane wrote:

> In an example like
> 
>   create function foo (anyelement, anyelement2, anyelement2)
>   returns anyarray2
> 
> the second and third arguments would be tied to be of the same type,
> and the result would be an array of that type; whereas the first
> argument's type is unrelated.

Another possible example is sprintf:

create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
returns text

In order for this to work in general, we'd need FUNC_MAX_ARGS different
types, which is currently defined as 100 in our code.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] RfD: more powerful "any" types

[David E. Wheeler]

On Sep 9, 2009, at 10:04 AM, Tom Lane wrote:


Well, yeah: it looks like a fertile source of security holes, not to
mention implementation difficulties (plpgsql really wants well-typed
expressions...).  What you can do at the C level is not necessarily
sane to give to PL authors.  I'm willing to consider a carefully  
spec'd

out proposal in this area, but "open the floodgates" ain't it.


Security holes? Huh? What security holes would there be that you don't  
already have with anyelement?


The difference between allowing ANYELEMENT2, ANYELEMENT3,  
ANYELEMENT .

++$i and allowing "any" escapes me.


In an example like

create function foo (anyelement, anyelement2, anyelement2)
returns anyarray2

the second and third arguments would be tied to be of the same type,
and the result would be an array of that type; whereas the first
argument's type is unrelated.  "any" doesn't give you any inter- 
argument

constraints nor any way to define the result type in terms of the
argument types.  For a possibly realistic example, consider a function
defined as "locate the element of an array that equals the search
argument, and return the corresponding element of a second array,  
which

is possibly of a different type".  This could be defined as

create function search_array (val anyelement,
  search_array anyarray,
  result_array anyarray2)
returns anyelement2

but "any" isn't nearly expressive enough.


I see. Yes, that is nice. Thanks for the examples.

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] RfD: more powerful "any" types

[Tom Lane]

"David E. Wheeler"  writes:
> On Sep 9, 2009, at 6:39 AM, Tom Lane wrote:
>> 1. Is a completely unconstrained argument type (ie "any") of any real
>> use to PL functions, and if so how can we expose that usefulness?
>> The only clear thing to do with such an argument is IS NULL/IS NOT  
>> NULL tests, which might or might not be worth the trouble.

> If you can pass an "any" to pg_typeof(), it's possible for functions  
> to determine the types of arguments themselves and then to decide what  
> to do with them (cast, etc.). I can see no reason not to give this  
> ability to function authors, can you?

Well, yeah: it looks like a fertile source of security holes, not to
mention implementation difficulties (plpgsql really wants well-typed
expressions...).  What you can do at the C level is not necessarily
sane to give to PL authors.  I'm willing to consider a carefully spec'd
out proposal in this area, but "open the floodgates" ain't it.

> The difference between allowing ANYELEMENT2, ANYELEMENT3, ANYELEMENT .  
> ++$i and allowing "any" escapes me.

In an example like

create function foo (anyelement, anyelement2, anyelement2)
returns anyarray2

the second and third arguments would be tied to be of the same type,
and the result would be an array of that type; whereas the first
argument's type is unrelated.  "any" doesn't give you any inter-argument
constraints nor any way to define the result type in terms of the
argument types.  For a possibly realistic example, consider a function
defined as "locate the element of an array that equals the search
argument, and return the corresponding element of a second array, which
is possibly of a different type".  This could be defined as

create function search_array (val anyelement,
  search_array anyarray,
  result_array anyarray2)
returns anyelement2

but "any" isn't nearly expressive enough.

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] corrupted double-linked list

[Maarten Foqué]

Hello everyone,

I ran into a problem writing a program using libpq, and after postin on
a forum and asking it on the irc channel I was redirected here.

I'll try to keep it simple so you don't have to read to much to start:

The program is asynchronous using libev, using sockets and notifications
to wake up.
Notifications is done with connection conn_async, queries are done using
conn_block with PQexec.
PGresult *res; is defined global.

When a notification arrives, the callback goes something like:

PGresult *res2;

res2 = PQexec(conn_async, "select stuff");  // yes, this is the only
time I use conn_async for a query

for( i "PostgreSQL 8.3.4 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.3.0 20080428 (Red Hat 4.3.0-8)"  (Fedora 9)

My system (running the program):
Linux 2.6.26.8-57.fc8 #1 SMP Thu Dec 18 19:19:45 EST 2008 i686 athlon
i386 GNU/Linux

Thanks in advance,
Maarten

P.S.: I'm not subscribed to the list.


-- 
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] RfD: more powerful "any" types

[decibel]

On Sep 9, 2009, at 8:39 AM, Tom Lane wrote:

Peter Eisentraut  writes:

Well, so far we've only seen use cases in this thread that either
already work or that are not well-defined. ;-)


Well, yeah, the question is can we extract a clear TODO item here.

I think there are two somewhat orthogonal issues:

1. Is a completely unconstrained argument type (ie "any") of any real
use to PL functions, and if so how can we expose that usefulness?
The only clear thing to do with such an argument is IS NULL/IS NOT  
NULL

tests, which might or might not be worth the trouble.



Part of that should be providing a means to determine what the  
underlying type of an "any" is. Having that would allow functions to  
take actions appropriate to different types.

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
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] RfD: more powerful "any" types

[David E. Wheeler]

On Sep 9, 2009, at 6:39 AM, Tom Lane wrote:


1. Is a completely unconstrained argument type (ie "any") of any real
use to PL functions, and if so how can we expose that usefulness?
The only clear thing to do with such an argument is IS NULL/IS NOT  
NULL

tests, which might or might not be worth the trouble.


If you can pass an "any" to pg_typeof(), it's possible for functions  
to determine the types of arguments themselves and then to decide what  
to do with them (cast, etc.). I can see no reason not to give this  
ability to function authors, can you?



2. Is there any use for arguments with type constraints not covered
by the existing ANYFOO rules, and if so what do we add for that?

One comment on point 2 is that it was foreseen from the beginning
that there would be need for ANYELEMENT2 etc, and I'm actually rather
surprised that we've gone this long without adding them.  Alvaro made
a good point about not wanting to multiply the various hard-wired
OID references, but perhaps some judicious code refactoring could
prevent a notational disaster.


The difference between allowing ANYELEMENT2, ANYELEMENT3, ANYELEMENT .  
++$i and allowing "any" escapes me.


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] Elementary dependency look-up

[decibel]

On Sep 9, 2009, at 8:05 AM, Peter Eisentraut wrote:

On Thu, 2009-09-03 at 20:45 -0400, Josh Williams wrote:

The patch adds two new functions to the backend, pg_get_owner_object
and
pg_get_owner_column.  These look up the requested object in the
pg_depend table, looking for an 'a' type dependency to another
relation,
and resolve either the relation or column names to text.


How is this better than just reading the information directly from
pg_depend?



pg_depend is very difficult to use. You have to really, really know  
the catalogs to be able to figure it out. Part of the problem is  
(afaik) there's nothing that documents every kind of record/ 
dependency you might find in there.


What might be more useful is a view that takes the guesswork out of  
using pg_depend. Namely, convert (ref)classid into a catalog table  
name (or better yet, what type of object it is), (ref)objid into an  
actual object name, and (ref)objsubid into a real name.

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
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] [rfc] unicode escapes for extended strings

[Marko Kreen]

Unicode escapes for extended strings.

On 4/16/09, Marko Kreen  wrote:
> Reasons:
>
>  - More people are familiar with \u escaping, as it's standard
>   in Java/C#/Python, probably more..
>  - U& strings will not work when stdstr=off.
>
>  Syntax:
>
>   \u  - 16-bit value
>   \U  - 32-bit value
>
>  Additionally, both \u and \U can be used to specify UTF-16 surrogate
>  pairs to encode characters with value > 0x.  This is exact behaviour
>  used by Java/C#/Python.  (except that Java does not have \U)

v3 of the patch:

- convert to new reentrant lexer API
- add lexer targets to avoid fallback to default
- completely disallow \U\u without proper number of hex values
- fix logic bug in surrogate pair handling

-- 
marko
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 7637eab..b6f26cc 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -394,6 +394,14 @@ SELECT 'foo'  'bar';
 
 hexadecimal byte value

+   
+
+ \u,
+ \U
+ (x = 0 - 9, A - F)
+
+16 or 32-bit hexadecimal Unicode character value.
+   
   
   
  
@@ -407,6 +415,14 @@ SELECT 'foo'  'bar';
 
 
 
+	 The Unicode escape syntax works fully only when the server encoding is UTF8.
+	 When other server encodings are used, only code points in the ASCII range
+	 (up to \u007F) can be specified.  Both \u and \U
+	 can also be used to specify UTF-16 surrogate pair to escape characters
+	 with value larger than \u.
+	
+
+
  It is your responsibility that the byte sequences you create are
  valid characters in the server character set encoding.  When the
  server encoding is UTF-8, then the alternative Unicode escape
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index f404f9d..8ca3007 100644
--- a/src/backend/parser/scan.l
+++ b/src/backend/parser/scan.l
@@ -97,6 +97,8 @@ static void check_escape_warning(base_yyscan_t yyscanner);
 extern int	base_yyget_column(yyscan_t yyscanner);
 extern void base_yyset_column(int column_no, yyscan_t yyscanner);
 
+static void addunicode(pg_wchar c, yyscan_t yyscanner);
+
 %}
 
 %option reentrant
@@ -134,6 +136,7 @@ extern void base_yyset_column(int column_no, yyscan_t yyscanner);
  *   $foo$ quoted strings
  *   quoted identifier with Unicode escapes
  *   quoted string with Unicode escapes
+ *   Unicode surrogate escape in extended string
  */
 
 %x xb
@@ -145,6 +148,7 @@ extern void base_yyset_column(int column_no, yyscan_t yyscanner);
 %x xdolq
 %x xui
 %x xus
+%x xeu
 
 /*
  * In order to make the world safe for Windows and Mac clients as well as
@@ -223,6 +227,8 @@ xeinside		[^\\']+
 xeescape		[\\][^0-7]
 xeoctesc		[\\][0-7]{1,3}
 xehexesc		[\\]x[0-9A-Fa-f]{1,2}
+xeunicode		[\\](u[0-9A-Fa-f]{4}|U[0-9A-Fa-f]{8})
+xeunicodebad	[\\]([uU])
 
 /* Extended quote
  * xqdouble implements embedded quote, 
@@ -535,6 +541,51 @@ other			.
 {xeinside}  {
 	addlit(yytext, yyleng, yyscanner);
 }
+{xeunicode} {
+	pg_wchar c = strtoul(yytext+2, NULL, 16);
+
+	check_escape_warning(yyscanner);
+
+	/*
+	 * handle UTF-16 surrogates:
+	 *   [0xD800..0xDC00) - first elem.
+	 *   [0xDC00..0xE000) - second elem.
+	 */
+	if (c >= 0xD800 && c < 0xE000)
+	{
+		if (c >= 0xDC00)
+			yyerror("invalid Unicode surrogate pair");
+
+		yyextra->utf16_top_part = ((c & 0x3FF) << 10) + 0x1;
+		BEGIN(xeu);
+	}
+	else
+		addunicode(c, yyscanner);
+}
+{xeunicode} {
+	pg_wchar c = strtoul(yytext+2, NULL, 16);
+
+	if (c < 0xDC00 || c >= 0xE000)
+		yyerror("invalid Unicode surrogate pair");
+
+	c = (c & 0x3FF) + yyextra->utf16_top_part;
+
+	addunicode(c, yyscanner);
+
+	BEGIN(xe);
+}
+.			|
+\n			|
+<>	{ yyerror("invalid Unicode surrogate pair"); }
+
+{xeunicodebad}	{
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_ESCAPE_SEQUENCE),
+ errmsg("invalid Unicode escape"),
+ errhint("Unicode escapes must be full-length: \\u or \\U."),
+ lexer_errposition()));
+	}
+
 {xeescape}  {
 	if (yytext[1] == '\'')
 	{
@@ -1263,3 +1314,21 @@ base_yyfree(void *ptr, base_yyscan_t yyscanner)
 	if (ptr)
 		pfree(ptr);
 }
+
+static void
+addunicode(pg_wchar c, base_yyscan_t yyscanner)
+{
+	char buf[8];
+
+	if (c == 0 || c > 0x10)
+		yyerror("invalid Unicode escape value");
+	if (c > 0x7F)
+	{
+		if (GetDatabaseEncoding() != PG_UTF8)
+			yyerror("Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8");
+		yyextra->saw_non_ascii = true;
+	}
+	unicode_to_utf8(c, (unsigned char *)buf);
+	addlit(buf, pg_mblen(buf), yyscanner);
+}
+
diff --git a/src/include/parser/gramparse.h b/src/include/parser/gramparse.h
index a54a1b1..0ef9bf4 100644
--- a/src/include/parser/gramparse.h
+++ b/src/include/parser/gramparse.h
@@ -71,6 +71,9 @@ typedef stru

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Tom Lane]

Alvaro Herrera  writes:
> Andrew Dunstan wrote:
>> It will affect any dbname or username in mixed or upper case, not just
>> ALL, won't it?

> No, I am suggesting to change only the comparisons to the literals
> "all", "sameuser", "samegroup" and "samerole".

Hmm.  These words are effectively keywords, so +1 for treating them
case-insensitively, as we do in SQL.  But I wonder whether there isn't
an argument for making the comparisons of role and database names
behave more like SQL, too --- that is FOO matches foo but not "FOO".

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] More robust pg_hba.conf parsing/error logging

[Rafael Martinez]

Alvaro Herrera wrote:
> Rafael Martinez wrote:
> 
>> Shouldn't 'all' be a reserved word?, it has a special meaning when used
>> in pg_hba.conf.
> 
> No, it works fine with a line like this:
> 
> local   "all"   all   md5
> 

Ok, then "all" and "ALL" should be valid values but not all and ALL
(without "")

regards
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

-- 
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] More robust pg_hba.conf parsing/error logging

[Alvaro Herrera]

Rafael Martinez wrote:

> Shouldn't 'all' be a reserved word?, it has a special meaning when used
> in pg_hba.conf.

No, it works fine with a line like this:

local   "all"   all md5

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] More robust pg_hba.conf parsing/error logging

[Alvaro Herrera]

Andrew Dunstan wrote:

> Alvaro Herrera wrote:

> >Surely if you want to designate a database named ALL you should use
> >quotes, same as if you wanted to designate a database named all (see my
> >other followup).
> 
> OK, but if we move to using pg_strcasecmp() that would be a
> behaviour change, so I think we couldn't do it before 8.5, in case
> someone is relying on it.

Yeah, I think so.  It doesn't seem like this is backpatchable (I lean
towards doubting that anyone is using a database named ALL, but still).

> It will affect any dbname or username in mixed or upper case, not just
> ALL, won't it?

No, I am suggesting to change only the comparisons to the literals
"all", "sameuser", "samegroup" and "samerole".

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Rafael Martinez]

Andrew Dunstan wrote:
> 
> 
> Rafael Martinez wrote:
>>
>> Or throw an error saying 'ALL' is not a valid value and *not* reload the
>> pg_hba.conf file.   
> 
> 
> But it's not invalid. It would designate a database or user named "ALL".
> That might be a silly thing to do, but that's another question.
> 

Shouldn't 'all' be a reserved word?, it has a special meaning when used
in pg_hba.conf.

regards
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

-- 
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] More robust pg_hba.conf parsing/error logging

[Andrew Dunstan]

Alvaro Herrera wrote:

Andrew Dunstan wrote:

  

Rafael Martinez wrote:


Or throw an error saying 'ALL' is not a valid value and *not* reload the
pg_hba.conf file.
  

But it's not invalid. It would designate a database or user named
"ALL". That might be a silly thing to do, but that's another
question.



Surely if you want to designate a database named ALL you should use
quotes, same as if you wanted to designate a database named all (see my
other followup).

  


OK, but if we move to using pg_strcasecmp() that would be a behaviour 
change, so I think we couldn't do it before 8.5, in case someone is 
relying on it. It will affect any dbname or username in mixed or upper 
case, not just ALL, won't it?


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] COALESCE and NULLIF semantics

[Tom Lane]

"Kevin Grittner"  writes:
> OK.  The only time it would be different from current behavior is when
> all parameters are of unknown type -- the result would be unknown
> rather than text:

The difficulty with that is that it implies eventually having to coerce
from unknown to something else, only at runtime instead of parse time.
There is not actually any such thing as a runtime coercion from unknown.
What there is is parse-time determination of the type of a literal
constant.

Now admittedly there's probably not any major technical obstacle to
making a runtime conversion happen --- it's merely delayed invocation of
the destination type's input function.  But I find it really ugly from a
theoretical point of view.  Doing calculations with "unknown" values
just seems wrong.  As an example consider

INSERT INTO tab (date_column) VALUES(COALESCE('2009-09-09', 'boo'));

If we made it work like you suggest, the system would never notice
that 'boo' is not a legal value of type date.  I don't find that
to be a good idea.

For NULLIF the concept fails entirely, because you *can not* compare two
values without having determined what data type you intend to treat them
as.  Ex: is '007' different from '7'?

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] Disable and enable of table and column constraints

[Kevin Grittner]

Alvaro Herrera  wrote:
 
> Both DB2 and Oracle have an ENFORCE setting for constraints, and a
> MySQL blog hinted some time ago that it might be in SQL 201x.
 
If I remember correctly, Sybase never checks the existing data when
you add a constraint of any type (except for a unique constraint or
primary key).
 
That has occasionally been useful to me when a business rule has been
identified which we want to enforce in an existing database, but there
hasn't yet been enforcement of that rule.  You can "plug the leak"
first, then list the legacy problems and get those on a list for
cleanup.  If you insist that all preexisting bad data must be cleaned
up before you can prevent more bad data from going in, you might never
*get* clean because of a steady dribble of additional bad data while
you are attempting cleanup.
 
(Well, OK, you could always enforce the rule at some other layer and
hope to get enough traction to correct the problems, but it is nice to
have help from the DBMS in this regard, without having to code
triggers to get there.)
 
-Kevin

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Alvaro Herrera]

Andrew Dunstan wrote:

> Rafael Martinez wrote:
> >
> >Or throw an error saying 'ALL' is not a valid value and *not* reload the
> >pg_hba.conf file.
> 
> But it's not invalid. It would designate a database or user named
> "ALL". That might be a silly thing to do, but that's another
> question.

Surely if you want to designate a database named ALL you should use
quotes, same as if you wanted to designate a database named all (see my
other followup).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Alvaro Herrera]

Rafael Martinez wrote:

> Problem:
> - 
> If you define in pg_hba.conf a database or a user value with 'ALL'
> instead of 'all', you will lose access to *all* databases involved. The
> reload process will not report anything about 'ALL' been an invalid
> value and the new pg_hba.conf will be reloaded.
> 
> This is the only thing in the log file:
> "LOG:  received SIGHUP, reloading configuration files"

Aye, that's surprising.  I think the correct fix here is to change the
strcmp comparisons to pg_strcasecmp() in several places in hba.c.

(BTW the business about appending newlines to special tokens in
next_token() seems ugly and underdocumented.)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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

[HACKERS] ToDo: array aggregates

[Pavel Stehule]

Hello

I thing so there are lot of aggregates based on generating array. We
have fast array_agg function, but we cannot be same effective with
custom aggregates. So my proposal is creating some new kind of
aggregates, that are based on arrays. The primary goal is getting same
speed as array_agg has.

Example: Median - http://wiki.postgresql.org/wiki/Aggregate_Median

CREATE OR REPLACE FUNCTION _final_median(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
 SELECT val
 FROM unnest($1) val
 ORDER BY 1
 LIMIT  2 - MOD(array_upper($1, 1), 2)
 OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE median(numeric) (
  SFUNC=array_append,
  STYPE=_numeric[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);

This function is slower than array_agg because we use sfunc
array_append. If could to use array_agg as base with enhancing final
function then we could this task faster.

Regards
Pavel Stehule

-- 
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] More robust pg_hba.conf parsing/error logging

[Andrew Dunstan]

Rafael Martinez wrote:


Or throw an error saying 'ALL' is not a valid value and *not* reload the
pg_hba.conf file. 
  



But it's not invalid. It would designate a database or user named "ALL". 
That might be a silly thing to do, but that's another question.


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