Re: [HACKERS] Partitioning option for COPY

[Emmanuel Cecchet]

Jan Urbański wrote:

 o) my main concern is still valid: the design was never agreed upon.
The approach of using inheritance info for automatic partitioning is, at
least IMHO, too restricted. Regular INSERTs won't get routed to child
tables. Data from writable CTEs won't get routed. People wanting to do
partitioning on something else that constraints are stuffed.
  
Well, this patch does not claim to implement partitioning for Postgres, 
it just offers partitioning as an option for COPY (and COPY only) based 
on the existing mechanism in Postgres.
I have already participated in lengthy and relatively sterile 
discussions on how to implement a full-blown partitioning but we never 
reached the beginning of an agreement and it was decided that a 
step-by-step approach would be better. I will propose another 
implementation of partitioning in COPY once Postgres has another 
representation than constraints on child tables to implement it.

I strongly suspect the patch will get rejected on the grounds of lack of
community agreement on partitioning, but I'd hate to see your work
wasted. It's not too late to open a discussion on how automatic
partitioning could work (or start working out a common proposal with the
people discussing in the "Syntax for partitioning" thread).
  
This is not my call. Right now the syntax for partitioning does not 
change anything to Postgres, it just adds syntactic sugar on top of the 
existing implementation. It will not route anything or answer any of the 
needs you mentioned in your previous point.

Marking as Waiting on Author, although I'd really like to see a solid
design being agreed upon, and then the code.
  
You are asking the wrong person if you want me to lead the partitioning 
design discussions. I already tried once and I was unsuccessful. As 
nothing as changed I don't see why I would be more successful this time.


Emmanuel

--
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.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] Partitioning option for COPY

[Greg Smith]

Jan Urbański wrote:

 o) my main concern is still valid: the design was never agreed upon.
The approach of using inheritance info for automatic partitioning is, at
least IMHO, too restricted. Regular INSERTs won't get routed to child
tables. Data from writable CTEs won't get routed. People wanting to do
partitioning on something else that constraints are stuffed.
  
Whether or not the other paths to load data are supported, COPY is the 
one you have to get right before this sort of feature is useful to the 
sort of use-cases that need partitioning the most.  While your concerns 
are valid, I hope Emmanuel doesn't take your feedback the wrong way.  
I'll take a working prototype that needs improvement over a paper design 
with no implementation anytime.  He's coming at this bottom-up starting 
with the little details that needs to be right, the partitioning syntax 
patch is starting at the top and working downward, there seems to be 
clear progress being made toward the eventual goal somewhere in the 
middle of all that here.  Considering how long this area has been bogged 
down in discussion without action, I'm rather glad we're seeing working 
proof of concepts bits rather than just talking about things.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


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

Re: [HACKERS] Unicode UTF-8 table formatting for psql text output

[Tom Lane]

Roger Leigh  writes:
> Attached is an updated patch with a couple of tweaks to ensure output
> is formatted and spaced correctly when border=0, which was off in the
> last patch.

Applied wih minor editorialization.  Notably, I renamed the
backwards-compatible option from "ascii-old" to "old-ascii",
because the original submission failed to preserve the documented
behavior that the options could be abbreviated to one letter.

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] Ignoring white space in regression tests really a good idea?

[Alex Hunsaker]

On Sat, Nov 21, 2009 at 16:07, David E. Wheeler  wrote:
> On Nov 22, 2009, at 7:49 AM, Tom Lane wrote:
>
>> I'm thinking maybe we should remove -w.  Comments?
>
> Have you tried it on the existing tests to see what happens?

For me there I get:
*the breaking in create_cast
*various things in foreign_key due to the tests not having spaces
after commas i.e:
! DETAIL:  Key (ftest1,ftest2)=(2,2) is not present in table "pktable".
! DETAIL:  Key (ftest1, ftest2)=(100, 2) is not present in table "pktable".
* exta space after a "
! CONTEXT:  SQL statement "SELECT  1/ $1 "
! CONTEXT:  SQL statement "SELECT 1/$1"

All those things are easily fixable so it seems worth it to me +1

-- 
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] UTF8 with BOM support in psql

[Peter Eisentraut]

On mån, 2009-11-16 at 22:37 +0200, Peter Eisentraut wrote:
> On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote:
> > Sure. Client encoding is declared in body of a file, but BOM is
> > in head of the file. So, we should always ignore BOM sequence
> > at the file head no matter what client encoding is used.
> > 
> > The attached patch replace BOM with while spaces, but it does not
> > change client encoding automatically. I think we can always ignore
> > client encoding at the replacement because SQL command cannot start
> > with BOM sequence. If we don't ignore the sequence, execution of
> > the script must fail with syntax error.
> 
> OK, I think the consensus here is:
> 
> - Eat BOM at beginning of file (as you implemented)
> 
> - Only when client encoding is UTF-8 --> please fix that
> 
> I'm not sure if replacing a BOM by three spaces is a good way to
> implement "eating", because it might throw off a column indicator
> somewhere, say, but I couldn't reproduce a problem.  Note that the U
> +FEFF character is defined as *zero-width* non-breaking space.

I have committed a change that implements the above.



-- 
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] Partitioning option for COPY

[Jan Urbański]

Emmanuel Cecchet wrote:
> Hi Jan,
> 
> Here is the updated patch.
> Note that the new code in trigger is a copy/paste of the before row
> insert trigger code modified to use the pointers of the after row
> trigger functions.

Hi,

ok, this version applied, compiled and ran the regression tests fine. I
tried a few things and was not able to break it this time.

A couple of nitpicks first:

 o) the route_tuple_to_child recurses to child tables of child tables,
which is undocumented and requires a check_stack_depth() call if it's
really desirable

 o) the error messages given when a trigger modifies the tuple should be
one sentence, I suggest dropping the "Aborting insert" part

 o) there are two places with "Close the relation but keep the lock"
comments. Why is in necessary to keep the locks? I confess I don't know
why *wouldn't* it be necessary, but maybe the comment could explain
that? Or is it just my lack of understanding and it should be obvious
that the lock needs to be kept?

 o) the result of relation_open is explicitly cast to Relation, the
result of try_relation_open is not (a minor gripe)

And a couple of more important things:

 o) the code added in trigger.c (ExecARInsertTriggersNow) is copy/pasted
from just above, I guess there was a reason why you needed that code,
but I also suspect that's a string indication that something's wrong
with the abstractions in your patch. Again I don't really know how else
you could achieve what you want. It just looks fishy if you need to
modify trigger.c to add an option to COPY.

 o) publicizing ExecRelCheck might also indicate a problem, but I guess
that can be defended, as the patch is basically based on using that
function for each incoming tuple

 o)  the LRU OID cache is a separate optimisation that could be
separated from the patch. I didn't do any performance tests, and I trust
that a cache like that helps with some workloads, but I think we could
do a better effort that a simplistic cache like that. Also, I'm not 100%
sure it's OK to just stick it into CacheMemoryContext... Maybe it could
go into the COPY statement context? You said you don't want to start
with a cold cache always, but OTOH if you're loading into different
tables in the same backend, the cache will actually hurt...

[thinks of something really bad... types up a quick test...]

Oh, actually, the cache is outright *wrong*, as the attached test6.sql
shows. Ugh, let's just forget about that LRU cache for now.

 o) the patch could use some more docs, especially about descending into
child tables.

 o) my main concern is still valid: the design was never agreed upon.
The approach of using inheritance info for automatic partitioning is, at
least IMHO, too restricted. Regular INSERTs won't get routed to child
tables. Data from writable CTEs won't get routed. People wanting to do
partitioning on something else that constraints are stuffed.

I strongly suspect the patch will get rejected on the grounds of lack of
community agreement on partitioning, but I'd hate to see your work
wasted. It's not too late to open a discussion on how automatic
partitioning could work (or start working out a common proposal with the
people discussing in the "Syntax for partitioning" thread).

Marking as Waiting on Author, although I'd really like to see a solid
design being agreed upon, and then the code.

Cheers,
Jan
drop table parent cascade;
drop table parent2 cascade;

create table parent(i int);
create table c1 (check (i > 0 and i <= 1)) inherits (parent);

create table parent2(i int);
create table c12 (check (i > 0 and i <= 1)) inherits (parent2);

set copy_partitioning_cache_size = 1;

copy parent from stdin with (partitioning);
1
\.

copy parent2 from stdin with (partitioning);
1
\.

-- all tuples went to parent !
select * from parent;
-- is empty
select * from parent2;

-- 
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] DEFAULT of domain ignored in plpgsql (8.4.1)

[Peter Eisentraut]

On lör, 2009-11-21 at 22:20 +0100, Florian G. Pflug wrote:
> > I'm inclined to leave it alone.  It complicates the mental model, and
> >  frankly attaching defaults to domains was not one of the SQL
> > committee's better ideas anyway.  It's *fundamentally*
> > non-orthogonal.
> 
> I've always though of domains as being a kind of subtype of it's base
> type. In this picture, DEFAULTs for domains correspond to overriding the
> default constructor of the type (thinking C++ now), and seem like a
> natural thing to have. But maybe that's more a C++ programmers than a
> database designers point of view...

There are other things in the SQL standard to do that, like types with
inheritance and types with constructors.  We have already overextended
domains enough beyond what the SQL standards says, mostly because these
other things that are the correct solution are not implemented yet.



-- 
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] Ignoring white space in regression tests really a good idea?

[David E. Wheeler]

On Nov 22, 2009, at 7:49 AM, Tom Lane wrote:

> I'm thinking maybe we should remove -w.  Comments?

Have you tried it on the existing tests to see what happens?

David

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

[HACKERS] Ignoring white space in regression tests really a good idea?

[Tom Lane]

pg_regress compares expected and actual output using "diff -w"
(a/k/a --ignore-all-space).  We have always done this, and I think
the idea was to avoid getting a lot of useless diff noise when the
only real difference is that one column value in a tabular display is
wider than expected.  I'm wondering if it's such a good idea though.
I just noticed that -w was masking a couple of recent changes in the
regression outputs, notably this one in create_cast:

  SELECT 1234::int4::casttesttype; -- No cast yet, should fail
  ERROR:  cannot cast type integer to casttesttype
  LINE 1: SELECT 1234::int4::casttesttype;
!^
  CREATE CAST (int4 AS casttesttype) WITH INOUT;
  SELECT 1234::int4::casttesttype; -- Should work now
   casttesttype 
--- 53,59 
  SELECT 1234::int4::casttesttype; -- No cast yet, should fail
  ERROR:  cannot cast type integer to casttesttype
  LINE 1: SELECT 1234::int4::casttesttype;
!  ^
  CREATE CAST (int4 AS casttesttype) WITH INOUT;
  SELECT 1234::int4::casttesttype; -- Should work now
   casttesttype 

If one supposes that one part of what this test is doing is checking
which part of the construct is pointed to by the error cursor, then
using -w in the check renders it useless.

I'm thinking maybe we should remove -w.  Comments?

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] DEFAULT of domain ignored in plpgsql (8.4.1)

[Florian G. Pflug]

Gurjeet Singh wrote:
On Sat, Nov 21, 2009 at 7:26 AM, Josh Berkus > wrote: However, there are some other

issues to be resolved:

(1) what should be the interaction of DEFAULT parameters and domains 
with defaults?


The function's DEFAULT parameter should take precedence over the
default of the domain.


I think Josh was pondering whether

create domain myint as int default 0;
create function f(i myint) ...;

should behave like

create function f(i myint default 0) ...;

and hence call f(0) if you do "select f();", or instead
raise an error because no f with zero parameters is defined (as it does
now).

I'd say no, because "no default" should be treated the same as "default
null", so for consistency we'd then have to also support

create function g(i int) ...;
select g();

And of course throw an error if there was another function defined as
create function g() ...;

This way leads to madness...

If one really wanted to do that, there'd have to be an OPTIONAL clause
for function parameters that works like DEFAULT, but doesn't take a
default value and instead uses the type's default (NULL except for
domains with DEFAULT clause). But I wouldn't got that far, personally...

best regards,
Florian Pflug




smime.p7s
Description: S/MIME Cryptographic Signature

Re: [HACKERS] DEFAULT of domain ignored in plpgsql (8.4.1)

[Florian G. Pflug]

Tom Lane wrote:

Josh Berkus  writes:

(2) this change, while very useful, does change what had been a
simple rule ("All variables are NULL unless specifically set
otherwise") into a conditional one ("All variables are NULL unless
set otherwise OR unless they are declared as domain types with
defaults").  Do people feel that the new behavior would be
sufficiently intuitive to avoid user confusion?


I'm inclined to leave it alone.  It complicates the mental model, and
 frankly attaching defaults to domains was not one of the SQL
committee's better ideas anyway.  It's *fundamentally*
non-orthogonal.


I've always though of domains as being a kind of subtype of it's base
type. In this picture, DEFAULTs for domains correspond to overriding the
default constructor of the type (thinking C++ now), and seem like a
natural thing to have. But maybe that's more a C++ programmers than a
database designers point of view...

I've just checked how rowtypes behave, and while the "set to null unless
specifically set otherwise" rule kind of holds for them, their NULL
value seems to be special-cased enough to blur the line quite a bit

create or replace function myt() returns t as $body$
declare
  r t;
begin
  raise notice 'r: %, r is null: %', r, (r is null);
  return r;
end;
$body$ language plpgsql immutable;
select myt(),myt() is null;

gives:

NOTICE:  r: (,), r is null: t
NOTICE:  r: (,), r is null: t
 myt | ?column?
-+--
 (,) | f

Strange I think... And at least half of an exception to the simple
"always null unless specifically set otherwise" rule

It also seems that while domain DEFAULTs are ignored, the resulting
(null-initialized) variable is still checked against the domain's
constraints, including a potential NOT NULL constraint

create domain myint as int not null;
create or replace function myint() returns myint as $body$
declare
  i myint;
begin
  return i;
end;
$body$ language plpgsql immutable;

raises

ERROR:  domain myint does not allow null values
CONTEXT:  PL/pgSQL function "myint" line 3 during statement block local
variable initialization

This has the potential to cause some headache I think if you use domains
to prohibit NULL values because they make no semantic sense for your
application, and depend on DEFAULT to fill in some other value (like an
empty string or an empty array).

best regards,
Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Tom Lane wrote:
>>> There's no equivalent of XLogArchivingActive()?
> 
>> XLogArchivingMode() == false enables us to skip WAL-logging in
>> operations like CLUSTER or COPY, which is a big optimization. I don't
>> see anything like that in Hot Standby. There is a few small things that
>> could be skipped, but nothing noticeable.
> 
> Huh?  Surely HS requires XLogArchivingMode as a prerequisite ...

Oh, sure! But there's no switch that needs to be enabled in the master
in addition to that.

> (Do we have a list of open issues somewhere, so that this
> won't get forgotten?)

I'm keeping one as I bump into things. I'll post it again soon.
Actually, I should probably put it on the wiki.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] [INTERFACES] ecpg & 8.3 -> 8.4 migration

[Mark Richardson]

I'm pretty sure the problem I found is related to this, but I found that ecpg 
doesn't process booleans correctly- this was in a old version of postgres (I 
think it was 7.4.2).  I traced it down in the code, and there is a section that 
defines the values to be "yes" or "no", but then further processing looks for 
"true" or "false", so the end result is that a boolean is ALWAYS false, because 
the 2 filters don't match.
 
If you're interested in more detail, I have code fixes (they are at work so 
I'll send on Monday).
Mark

--- On Fri, 11/20/09, Tom Lane  wrote:


From: Tom Lane 
Subject: Re: [INTERFACES] ecpg & 8.3 -> 8.4 migration
To: "Bosco Rama" 
Cc: pgsql-interfa...@postgresql.org, pgsql-hackers@postgresql.org, "Michael 
Meskes" 
Date: Friday, November 20, 2009, 5:02 PM


Bosco Rama  writes:
> According to the PG docs 'server' is a *non-reserved*
> keyword.  Does ecpg treat it as a reserved word?

Upon poking around in the ecpg sources, I discover that it has its own
list of "unreserved" keywords and pays no attention whatsoever to the
core grammar's unreserved_keyword list.  I can hardly find words to
express my dissatisfaction with that.  The unreserved_keyword list might
not be the single most-likely-to-change production in the core grammar,
but it's got to be right up there in the top two or three.  This little
shortcut means that all the work done last year to generate the ecpg
grammar from core was practically useless --- we still have to worry
about manually maintaining the ecpg grammar any time we change core.

This has *got* to be fixed.  The lack of obvious feedback about keywords
not included in any keyword list means that manual maintenance is
guaranteed to fail, as indeed it's already done, per this report.

            regards, tom lane

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



  

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Merlin Moncure :
> On Sat, Nov 21, 2009 at 3:32 PM, Pavel Stehule  
> wrote:
>> 2009/11/21 Tom Lane :
>>> Pavel Stehule  writes:
 Hypothetically - when we are able to pass any value to DO script, then
 I don't see problem. If I use Andrew's design - ${shellvar} and add it
 to psql parser, then I could to write
>>>
 \set par1 world
>>>
 do $$
   begin
     raise notice 'Helo, % and %', $1, $2;
   end;
 $$ using :par1, ${USER};
>>>
>>> Ick.  Double, triple ick.  It is astonishing to me how many people think
>>> that the solution to today's problem is always to invent some weird new
>>> syntax to plaster over SQL.  Which for some reason invariably involves
>>> dollar signs and/or curly braces ... there isn't even any originality
>>> involved :-(.
>>>
>>> Maybe we should accept one of these proposals, just so that it usurps
>>> that part of the syntax space forever and we can reject the next ten bad
>>> ideas out-of-hand.  Of course, if the SQL committee ever gets around to
>>> defining curly braces as doing something, we'll be screwed.
>>>
>>> colon-foo is bad enough.  Let's not add more.
>>
>> I have a no problem. Syntax should be defined later. there is simple
>> workaround (using shellvariables):
>>
>> psql ... -v user = $USER
>>
>>
>> I repeat it again and finish:
>>
>> proposal is related only to DO statement (what is Pg specific).
>> Doesn't propose psql changes, doesn't propose PL changes.
>
> I code all day long by editing various text files and pasting some/all
> of them into psql.  Once in a while I \i a file from psql or cat foo |
> psql.  Only the last method is addressed by using a wrapper script or
> "bash/psql -c"...wrappers aren't really a general solution.   If psql
> could introduce variables into arbitrary sql somehow, I could load a
> bunch of variables in .psqlrc, have constants set up, etc.
>
> For example, when I have a constant (let's say, and advisory lock#) I
> want to define in a plpgsql function, I have a choice between three
> not very pleasant options:
> *) simply hard code the value
> *) look it up every time from a global control table
> *) wrap it in an immutable sql function
>
> I don't care much about the syntax, but it would be absolutely
> wonderful if psql could operate on a function body in some sort of
> regular way before it gets to the backend.  if it solves
> parameterizing 'do' statements, so much the better.

you need C preprocessor  or Oracle's packages ;)

Oracle's packages would be better.

Pavel

>
> merlin
>

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Tom Lane]

Heikki Linnakangas  writes:
> Tom Lane wrote:
>> There's no equivalent of XLogArchivingActive()?

> XLogArchivingMode() == false enables us to skip WAL-logging in
> operations like CLUSTER or COPY, which is a big optimization. I don't
> see anything like that in Hot Standby. There is a few small things that
> could be skipped, but nothing noticeable.

Huh?  Surely HS requires XLogArchivingMode as a prerequisite ...

> It's great from usability point of view that you don't need to enable it
> beforehand,

... which also means that I don't understand this statement.

> Anyway, I think I have enough courage now to just rip out the VACUUM
> FULL support from HS. If VACUUM FULL is still there when we're ready to
> go to beta, we can introduce a "do you want VACUUM FULL or hot standby?"
> switch in the master, or some other ugly workaround.

Agreed, we have more than enough worries without worrying about making
that work.  (Do we have a list of open issues somewhere, so that this
won't get forgotten?)

regards, tom lane

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Merlin Moncure]

On Sat, Nov 21, 2009 at 3:32 PM, Pavel Stehule  wrote:
> 2009/11/21 Tom Lane :
>> Pavel Stehule  writes:
>>> Hypothetically - when we are able to pass any value to DO script, then
>>> I don't see problem. If I use Andrew's design - ${shellvar} and add it
>>> to psql parser, then I could to write
>>
>>> \set par1 world
>>
>>> do $$
>>>   begin
>>>     raise notice 'Helo, % and %', $1, $2;
>>>   end;
>>> $$ using :par1, ${USER};
>>
>> Ick.  Double, triple ick.  It is astonishing to me how many people think
>> that the solution to today's problem is always to invent some weird new
>> syntax to plaster over SQL.  Which for some reason invariably involves
>> dollar signs and/or curly braces ... there isn't even any originality
>> involved :-(.
>>
>> Maybe we should accept one of these proposals, just so that it usurps
>> that part of the syntax space forever and we can reject the next ten bad
>> ideas out-of-hand.  Of course, if the SQL committee ever gets around to
>> defining curly braces as doing something, we'll be screwed.
>>
>> colon-foo is bad enough.  Let's not add more.
>
> I have a no problem. Syntax should be defined later. there is simple
> workaround (using shellvariables):
>
> psql ... -v user = $USER
>
>
> I repeat it again and finish:
>
> proposal is related only to DO statement (what is Pg specific).
> Doesn't propose psql changes, doesn't propose PL changes.

I code all day long by editing various text files and pasting some/all
of them into psql.  Once in a while I \i a file from psql or cat foo |
psql.  Only the last method is addressed by using a wrapper script or
"bash/psql -c"...wrappers aren't really a general solution.   If psql
could introduce variables into arbitrary sql somehow, I could load a
bunch of variables in .psqlrc, have constants set up, etc.

For example, when I have a constant (let's say, and advisory lock#) I
want to define in a plpgsql function, I have a choice between three
not very pleasant options:
*) simply hard code the value
*) look it up every time from a global control table
*) wrap it in an immutable sql function

I don't care much about the syntax, but it would be absolutely
wonderful if psql could operate on a function body in some sort of
regular way before it gets to the backend.  if it solves
parameterizing 'do' statements, so much the better.

merlin

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Tom Lane :
> Pavel Stehule  writes:
>> Hypothetically - when we are able to pass any value to DO script, then
>> I don't see problem. If I use Andrew's design - ${shellvar} and add it
>> to psql parser, then I could to write
>
>> \set par1 world
>
>> do $$
>>   begin
>>     raise notice 'Helo, % and %', $1, $2;
>>   end;
>> $$ using :par1, ${USER};
>
> Ick.  Double, triple ick.  It is astonishing to me how many people think
> that the solution to today's problem is always to invent some weird new
> syntax to plaster over SQL.  Which for some reason invariably involves
> dollar signs and/or curly braces ... there isn't even any originality
> involved :-(.
>
> Maybe we should accept one of these proposals, just so that it usurps
> that part of the syntax space forever and we can reject the next ten bad
> ideas out-of-hand.  Of course, if the SQL committee ever gets around to
> defining curly braces as doing something, we'll be screwed.
>
> colon-foo is bad enough.  Let's not add more.

I have a no problem. Syntax should be defined later. there is simple
workaround (using shellvariables):

psql ... -v user = $USER


I repeat it again and finish:

proposal is related only to DO statement (what is Pg specific).
Doesn't propose psql changes, doesn't propose PL changes.

good night
Pavel


>
>                        regards, tom lane
>

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Tom Lane wrote:
>>> I don't see much problem with rejecting VAC FULL in a HS master,
>>> whether or not it gets removed altogether.  Why not just do that
>>> rather than write a lot of kluges?
> 
>> Hmm. At the moment, no action is required in the master to allow hot
>> standby in the slave, except for turning on archiving. The additional
>> overhead of the extra logging that's needed in the master is small
>> enough that there has been no need for a switch.
> 
> There's no equivalent of XLogArchivingActive()?  I think there probably
> should be.  I find it really hard to believe that there won't be any
> places where we need to know that we're an HS master.  The original
> design of WAL archiving didn't think we needed to know we were archiving
> WAL, either, and look how many cases there are for that now.

XLogArchivingMode() == false enables us to skip WAL-logging in
operations like CLUSTER or COPY, which is a big optimization. I don't
see anything like that in Hot Standby. There is a few small things that
could be skipped, but nothing noticeable.

It's great from usability point of view that you don't need to enable it
beforehand, especially because HS is also very useful when doing PITR
from a backup; it's too late to turn on the switch at that point. As
soon as we have a good reason to introduce a switch, let's do so, but
not before that.

If we want cop out of VACUUM FULL and HS issues, maybe we could just
kick out all clients from the standby when we see a WAL record from
VACUUM FULL. Not very elegant, but should work.

Anyway, I think I have enough courage now to just rip out the VACUUM
FULL support from HS. If VACUUM FULL is still there when we're ready to
go to beta, we can introduce a "do you want VACUUM FULL or hot standby?"
switch in the master, or some other ugly workaround.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] [INTERFACES] ecpg & 8.3 -> 8.4 migration

[Michael Meskes]

On Fri, Nov 20, 2009 at 07:02:11PM -0500, Tom Lane wrote:
> Upon poking around in the ecpg sources, I discover that it has its own
> list of "unreserved" keywords and pays no attention whatsoever to the
> core grammar's unreserved_keyword list.  I can hardly find words to
> express my dissatisfaction with that.  The unreserved_keyword list might

I cannot agree more. Sorry about that, it seems this one got forgotten. I
wasn't aware of this blunder.

Thanks for fixing it Tom.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org
VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Andrew Dunstan]

Tom Lane wrote:

In any case I don't think that "getting stuff from
psql variables into a DO script" is the way to define the problem.
It's "getting stuff from shell variables into a DO script" that is the
real-world problem. 


Indeed. But setting psql variables from the command line is easy. We 
have a nifty switch for it. So we could reduce one problem to the other.



 Maybe psql is the wrong tool altogether.


  


Yeah. The workaround is to use some sort of wrapper, in shell script, 
perl or whatever. Maybe we should just let sleeping dogs lie.


I think you and I are in agreement though that an SQL level mechanism is 
not the way to go, at least for now.


cheers

andrew

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Tom Lane]

Pavel Stehule  writes:
> Hypothetically - when we are able to pass any value to DO script, then
> I don't see problem. If I use Andrew's design - ${shellvar} and add it
> to psql parser, then I could to write

> \set par1 world

> do $$
>   begin
> raise notice 'Helo, % and %', $1, $2;
>   end;
> $$ using :par1, ${USER};

Ick.  Double, triple ick.  It is astonishing to me how many people think
that the solution to today's problem is always to invent some weird new
syntax to plaster over SQL.  Which for some reason invariably involves
dollar signs and/or curly braces ... there isn't even any originality
involved :-(.

Maybe we should accept one of these proposals, just so that it usurps
that part of the syntax space forever and we can reject the next ten bad
ideas out-of-hand.  Of course, if the SQL committee ever gets around to
defining curly braces as doing something, we'll be screwed.

colon-foo is bad enough.  Let's not add more.

regards, tom lane

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Tom Lane]

Heikki Linnakangas  writes:
> Tom Lane wrote:
>> I don't see much problem with rejecting VAC FULL in a HS master,
>> whether or not it gets removed altogether.  Why not just do that
>> rather than write a lot of kluges?

> Hmm. At the moment, no action is required in the master to allow hot
> standby in the slave, except for turning on archiving. The additional
> overhead of the extra logging that's needed in the master is small
> enough that there has been no need for a switch.

There's no equivalent of XLogArchivingActive()?  I think there probably
should be.  I find it really hard to believe that there won't be any
places where we need to know that we're an HS master.  The original
design of WAL archiving didn't think we needed to know we were archiving
WAL, either, and look how many cases there are for that now.

regards, tom lane

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> So I guess what I'm asking is: Does anyone see any show-stoppers in
>> removing VACUUM FULL, and does anyone want to step up to the plate and
>> promise to do it before release?
> 
> I don't see much problem with rejecting VAC FULL in a HS master,
> whether or not it gets removed altogether.  Why not just do that
> rather than write a lot of kluges?

Hmm. At the moment, no action is required in the master to allow hot
standby in the slave, except for turning on archiving. The additional
overhead of the extra logging that's needed in the master is small
enough that there has been no need for a switch.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] Proposal: USING clause for DO statement

[Pavel Stehule]

>
> The main limitation of this type of approach is that it's hard to
> properly quote a variable value that might contain any random character
> sequence.  However, that's also true of the variable-interpolation stuff
> Pavel was proposing.  In any case I don't think that "getting stuff from
> psql variables into a DO script" is the way to define the problem.
> It's "getting stuff from shell variables into a DO script" that is the
> real-world problem.

I am probably out, Tom

Hypothetically - when we are able to pass any value to DO script, then
I don't see problem. If I use Andrew's design - ${shellvar} and add it
to psql parser, then I could to write

\set par1 world

do $$
  begin
raise notice 'Helo, % and %', $1, $2;
  end;
$$ using :par1, ${USER};

> Maybe psql is the wrong tool altogether.

why - psql is very good tool. I am able to do all what I need - but
sometimes I have to use shell expansion - it's need quoting, and the
code isn't much readable. With parameters we can to separate code from
values - and an code should very clean.

Pavel

>
>                        regards, tom lane
>

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Tom Lane]

Heikki Linnakangas  writes:
> So I guess what I'm asking is: Does anyone see any show-stoppers in
> removing VACUUM FULL, and does anyone want to step up to the plate and
> promise to do it before release?

I don't see much problem with rejecting VAC FULL in a HS master,
whether or not it gets removed altogether.  Why not just do that
rather than write a lot of kluges?

regards, tom lane

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Tom Lane]

Andrew Dunstan  writes:
> See my earlier comment:
>> Perhaps part of the problem is that psql can't interpolate its 
>> variable into strings. Solving that might lessen the impetus for this, 
>> and have other uses besides.

It seems to me that this is sliding down the wrong slope.  You're
basically assuming that psql itself is or should be a programming
language.  It's not.  The variable mechanism is an enormous kluge
with limited flexibility caused by a horrid syntax; and psql hasn't
got any control structures at all.

I think Petr was on the right track.  What people really want is not
psql scripts but plpgsql scripts.  DO effectively gives that to them,
with a few characters' worth of overhead.  The problem they have to
solve is to interpolate actual-parameter values into such a script;
but it's not clear to me that that's noticeably harder than getting
such values into a psql script.  I foresee people doing things like

psql -c 'DO $$'"declare x int := $SHELLVARIABLE; ... "'$$;' ...

The fact that $ is special to the shell as well as to DO is
kind of a PITA here, but it's not that hard to work around.

The main limitation of this type of approach is that it's hard to
properly quote a variable value that might contain any random character
sequence.  However, that's also true of the variable-interpolation stuff
Pavel was proposing.  In any case I don't think that "getting stuff from
psql variables into a DO script" is the way to define the problem.
It's "getting stuff from shell variables into a DO script" that is the
real-world problem.  Maybe psql is the wrong tool altogether.

regards, tom lane

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

Re: [HACKERS] Hot standby and removing VACUUM FULL

[Greg Smith]

Heikki Linnakangas wrote:

So I guess what I'm asking is: Does anyone see any show-stoppers in
removing VACUUM FULL
Here's the disclaimers attached to the new VACUUM REPLACE implementation 
from Itagaki:


"We still need traditional VACUUM FULL behavior for system catalog 
because we cannot change relfilenode for them. Also, VACUUM FULL REPLACE 
is not always better than traditional VACUUM FULL; the new version 
requires additional disk space and might be slower if we have a few dead 
tuples."


That first part seems like it would limit the ability to completely 
discard the current behavior.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Merlin Moncure :
> On Sat, Nov 21, 2009 at 1:24 PM, Pavel Stehule  
> wrote:
>> 2009/11/21 Merlin Moncure :
>>> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan  
>>> wrote:
> Perhaps part of the problem is that psql can't interpolate its variable
> into strings. Solving that might lessen the impetus for this, and have 
> other
> uses besides.
>> I am not sure, if I understand well. Can you show some use cases, please?
>
> If I understand Andrew correctly,
>
> \set msg world
>
> DO $$
> BEGIN
>  RAISE NOTICE 'Hello, %', :msg;
> END
> $$;
>

This is base of my proposal :). But we cannot do it directly:

a) the body of DO statement is black box for psql parser,
b) psql does know nothing about used PL language - without this
knowledge cannot do correct substitution: plpgsq use ', java use "",
perl use ""

these points are reason, why I propose "external USING clause" - it
outside blackbox, it is common for all PL

Point b is solved via using a real params - not substitution.

Regards
Pavel

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Merlin Moncure]

On Sat, Nov 21, 2009 at 1:24 PM, Pavel Stehule  wrote:
> 2009/11/21 Merlin Moncure :
>> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan  wrote:
 Perhaps part of the problem is that psql can't interpolate its variable
 into strings. Solving that might lessen the impetus for this, and have 
 other
 uses besides.
> I am not sure, if I understand well. Can you show some use cases, please?

If I understand Andrew correctly,

\set msg world

DO $$
BEGIN
 RAISE NOTICE 'Hello, %', :msg;
END
$$;

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Merlin Moncure :
> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan  wrote:
>>> Perhaps part of the problem is that psql can't interpolate its variable
>>> into strings. Solving that might lessen the impetus for this, and have other
>>> uses besides.
>
> +1!
>
> This would have a _lot_ of uses.
>

I am not sure, if I understand well. Can you show some use cases, please?

Pavel

> merlin
>

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Merlin Moncure]

On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan  wrote:
>> Perhaps part of the problem is that psql can't interpolate its variable
>> into strings. Solving that might lessen the impetus for this, and have other
>> uses besides.

+1!

This would have a _lot_ of uses.

merlin

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

[HACKERS] Hot standby and removing VACUUM FULL

[Heikki Linnakangas]

VACUUM FULL does a peculiar hack: once it's done moving tuples, and
before it truncates the relation, it calls RecordTransactionCommit to
mark the transaction as committed in clog and WAL, but the transaction
is still kept open in proc array. After it's done with truncating and
other cleanup, normal transaction commit performs
RecordTransactionCommit again as part of normal commit processing.

That causes some headaches for Hot Standby, ie. it needs to know to not
release locks yet when it sees the first commit record. At the moment,
it simply ignores the first commit record, but that's very dangerous. If
failover happens after the standby has seen the truncation record, but
not the 2nd commit record for the transaction, all data moved from the
truncated part will lost.

I'm sure that's fixable, but I believe we're quite committed to removing
VACUUM FULL altogether in this release. If that's going to happen, I can
just remove all the VACUUM FULL related code from the Hot Standby patch
now and not spend any more time on it.

I'm a bit hesitent to do that because that basically leaves me in the
exact situation I said before I wanted to avoid: If I commit Hot Standby
without dealing with VACUUM FULL, and no-one gets around to remove
VACUUM FULL (and more importantly, provide some replacement for it), I
will be forced to do it myself or fix Hot Standby to work with it after
all. I might be involved in the VACUUM FULLectomy anyway, but I don't
want to be pushed against the wall on it.

So I guess what I'm asking is: Does anyone see any show-stoppers in
removing VACUUM FULL, and does anyone want to step up to the plate and
promise to do it before release?

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] Proposal: USING clause for DO statement

[Andrew Dunstan]

Pavel Stehule wrote:

I would to use this
statement for some non trivial maintenance tasks - and I would to use
external parameter (from command line). My question is - what is a
good way for passing some value (from command line) to DO statement
body? I accept any mechanism.

  


See my earlier comment:

Perhaps part of the problem is that psql can't interpolate its 
variable into strings. Solving that might lessen the impetus for this, 
and have other uses besides.



cheers

andrew


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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Petr Jelinek :
> I don't see point in having parameters for DO as an utility command. If you
> need to reuse some value you can define those variables at the beginning of
> code block in the language itself (in DECLARE section in plpgsql for
> example), defining them in outer SQL command does not really help anything.

no - this isn't a problem. Current design of DO statement allows only
single-use using. I cannot to wrap "do" statement. I would to use this
statement for some non trivial maintenance tasks - and I would to use
external parameter (from command line). My question is - what is a
good way for passing some value (from command line) to DO statement
body? I accept any mechanism.

> Now, if/when we add support to put DO inside standard sql query, it would be
> vastly more useful to be able to use parameters. But adding that support
> will be probably least of our problems if we try to do that, and we might
> even want to use different syntax/behavior then, so I would really not rush
> with this.

It doesn't need too much, Petr - I thing, so all necessary is done.
And this proposal doesn't do incompatible changes in syntax. It add
only new optional clause.


>
> --
> Regards
> Petr Jelinek (PJMODOS)
>
>

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Petr Jelinek]

I don't see point in having parameters for DO as an utility command. If 
you need to reuse some value you can define those variables at the 
beginning of code block in the language itself (in DECLARE section in 
plpgsql for example), defining them in outer SQL command does not really 
help anything. Now, if/when we add support to put DO inside standard sql 
query, it would be vastly more useful to be able to use parameters. But 
adding that support will be probably least of our problems if we try to 
do that, and we might even want to use different syntax/behavior then, 
so I would really not rush with this.


--
Regards
Petr Jelinek (PJMODOS)


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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Tom Lane :
> Pavel Stehule  writes:
>> 2009/11/21 Andrew Dunstan :
>>> One possible problem: what type would these anonymous params be?
>
>> It is solved long time - without specification, any parameter is
>> 'unknown text'.
>
> Nonsense.
>

ok.

> We do have the ability to infer parameter types when parsing a SQL
> statement. That does not extend to any random PL being able to do it.
> In fact, NONE of them can do it, not even plpgsql.  They all expect
> incoming parameter types to be predetermined.
>

When we use DO statement from clients without possibility to specify
type, an usedr have  to specify type with explicit casting. Inside DO
statement or in USING clause. Then any outer value has type or is
unknown. When is unknown - then any usage needs explicit casting
inside DO body. When has type - there are no problem. But we will have
a way for passing a parameters.

an sample should be

DO $$  $$ USING psqlvar::text, psqlothervar numeric;

I don't expect typed variables inside psql. It's not impossible, but
why? There are pgScript and DO statement. Theoretically we can
identify the most common type for untyped variable in compile stage of
any PL. It have to be a similar mechanism like now - with existing
hooks it could not be a problem, but I don't thing it is necessary
(for DO stament - functions are different, because are registered in
moment when some related objects doesn't exist). Explicit casting in
USING clause is enough. It is safe and simply. Explicit casting is
less work then some bash regxep substitution alchemy.

> Without types *and* names, there is no point in considering parameters.
> And the problem with that, which is why we didn't put parameters into
> DO in the first place, is that it raises the minimum notational bar
> quite a lot.  You might as well go ahead and define a temporary
> function.
>

I understand. It's reason why I don't propose named parameters.

p.s. I don't see sense of temporary functions, when we have a
anonymous block. All temporary objects are problematic - I have not a
reason, thing about temporary functions some else.

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

Re: [HACKERS] xpath_table equivalent

[Andrew Dunstan]

I wrote:


The nice thing about XMLTABLE is that it adds xquery support. I think 
the majority of xquery engines seem to be written in Java. XQuilla is 
C++. I'm not sure if our licensing is compatible, but it I would love 
the irony of using Berkeley DB XML (formerly Sleepycat) now that its 
owned by Oracle.





XQuery is a whole other question. Adding another library dependency is 
something we try to avoid. Zorba  might 
work, but it appears to have its own impressive list of dependencies 
(why does it require both libxml2 and xerces-c? That looks a bit 
redundant.)


Even if we did implement XMLTABLE, I think I'd probably be inclined to 
start by limiting it to plain XPath, without the FLWOR stuff. I think 
that would satisfy the vast majority of needs, although you might feel 
differently. (Do a Google for XMLTABLE - every example I found uses 
plain XPath expressions.)





I did look at this a bit further. Sadly, XQilla's XSLT support is stated 
to be of alpha quality, and missing some quite necessary features (e.g. 
xsl:output). That pretty much rules out for now Xerces-C+XQilla as an 
alternative xml stack to libxml2+libxslt, ISTM.


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] DEFAULT of domain ignored in plpgsql (8.4.1)

[Gurjeet Singh]

On Sat, Nov 21, 2009 at 7:26 AM, Josh Berkus  wrote:

>
> > Would a patch that changes that have any chance of being accepted? Or is
> > the gain (not having to repeat the DEFAULT clause, and being able to
> > maintain it at one place instead of many) considered too small compared
> > to the risk of breaking existing code?
>
> I don't think there's a lot of risk of code breakage; few people use
> domains, fewer use them with defaults, and you might be the only one
> using them as variable types.  And there are going to be more
> substantial backwards compat issues with the lexer changes anyway.  As
> long as we remember to flag the compatibility issue in the release
> notes, I don't see it as a problem.
>
> However, there are some other issues to be resolved:
>
> (1) what should be the interaction of DEFAULT parameters and domains
> with defaults?
>

The function's DEFAULT parameter should take precedence over the default of
the domain.


>
> (2) this change, while very useful, does change what had been a simple
> rule ("All variables are NULL unless specifically set otherwise") into a
> conditional one ("All variables are NULL unless set otherwise OR unless
> they are declared as domain types with defaults").  Do people feel that
> the new behavior would be sufficiently intuitive to avoid user confusion?
>

I see this as a straight-forward extension to what we've had till now; and I
bet some users would've definitely expected them to work this way in the
past..


>
> (3) Last I checked, there were still several places in which domains did
> not behave consistently in stored procedures.  I think that Elein had
> some unfinished patches in this regard -- you'll want to search the
> archives and the TODO list.
>

One thing to remember is that, that this behavior should be supported in all
PLs that support domain types as variables.

Best regards,
-- 
Lets call it Postgres

EnterpriseDB  http://www.enterprisedb.com

gurjeet[.sin...@enterprisedb.com

singh.gurj...@{ gmail | hotmail | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [HACKERS] DEFAULT of domain ignored in plpgsql (8.4.1)

[Tom Lane]

Josh Berkus  writes:
> (2) this change, while very useful, does change what had been a simple
> rule ("All variables are NULL unless specifically set otherwise") into a
> conditional one ("All variables are NULL unless set otherwise OR unless
> they are declared as domain types with defaults").  Do people feel that
> the new behavior would be sufficiently intuitive to avoid user confusion?

I'm inclined to leave it alone.  It complicates the mental model, and
frankly attaching defaults to domains was not one of the SQL committee's
better ideas anyway.  It's *fundamentally* non-orthogonal.

regards, tom lane

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Tom Lane]

Pavel Stehule  writes:
> 2009/11/21 Andrew Dunstan :
>> One possible problem: what type would these anonymous params be?

> It is solved long time - without specification, any parameter is
> 'unknown text'.

Nonsense.

We do have the ability to infer parameter types when parsing a SQL
statement. That does not extend to any random PL being able to do it.
In fact, NONE of them can do it, not even plpgsql.  They all expect
incoming parameter types to be predetermined.

Without types *and* names, there is no point in considering parameters.
And the problem with that, which is why we didn't put parameters into
DO in the first place, is that it raises the minimum notational bar
quite a lot.  You might as well go ahead and define a temporary
function.

regards, tom lane

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Andrew Dunstan]

Pavel Stehule wrote:

p.s. Maybe it is premature - We had to live with EXECUTE (without
USING clause) twelve years. But an life should be comfortable. I don't
would to wait twelve years :)
  



I think you should take heed of Tom's words:


I think adding onto DO capabilities is something we could do later
if demand warrants.  I'd prefer to underdesign it for starters than
to encrust it with features that might not be needed.


That doesn't mean waiting 12 years, but it does mean taking it a step at 
a time. This feature hasn't even had one release in the field yet.


Perhaps part of the problem is that psql can't interpolate its variable 
into strings. Solving that might lessen the impetus for this, and have 
other uses besides.


cheers

andrew





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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Andrew Dunstan :
>
>
> Pavel Stehule wrote:
>>
>> What do you thing about this proposal?
>>
>>
>>
>
> I think it's premature. Before we start adding bells and whistles to the
> feature, let's give it a turn in the field.

why? It thing so not. My opinion - it is incomplete. It has similar
function like function without parameters now. It's good for some
static task (single-use). But it isn't practical for using in shell
scripts. Do you have a other mechanism for parametrisation? I would to
use it from bash - and I need some mechanism for passing an parameter
from command line to DO statement. I

>
> One possible problem: what type would these anonymous params be? (And, BTW,
> don't kid yourself that there would not very soon be pressure to name them).
>

It is solved long time - without specification, any parameter is
'unknown text'. It is based on PQexecParam functionality. In this
case, the situation is simpler - we know value - it same as EXECUTE
statement in plpgsql - type is defined by call. So we knows params (it
is supported by protocol), PL languages support params. So it needs
only some relation. It's need a few lines more in executor (copy a
four pointers and one int) and some in PL (local variable declaring
and copy values) + 5 lines in gram.y

Pavel

p.s. Maybe it is premature - We had to live with EXECUTE (without
USING clause) twelve years. But an life should be comfortable. I don't
would to wait twelve years :)


> cheers
>
> andrew
>

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

Re: [HACKERS] Proposal: USING clause for DO statement

[Andrew Dunstan]

Pavel Stehule wrote:

What do you thing about this proposal?


  


I think it's premature. Before we start adding bells and whistles to the 
feature, let's give it a turn in the field.


One possible problem: what type would these anonymous params be? (And, 
BTW, don't kid yourself that there would not very soon be pressure to 
name them).


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] [PATCH] hstore documentation update

[David E. Wheeler]

From: David E. Wheeler 

As I threatened when I reviewed hstore in the last two commit
fests, I've finally seen may way to edit the documentation. This
is mostly word-smithing, making sure that all `>`s are encoded,
making sure that various text is properly tagged with ``
and `` tags, plus an extra note or two. I submit this
patch for the next CommitFest (though I don't know how much CFing
is needed for a pure documenation patch).

Best,

David

---
 doc/src/sgml/hstore.sgml |  190 +-
 1 files changed, 102 insertions(+), 88 deletions(-)

diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index f237be7..fcff6e3 100644
*** a/doc/src/sgml/hstore.sgml
--- b/doc/src/sgml/hstore.sgml
***
*** 8,69 
   
  
   
!   This module implements a data type hstore for storing sets of
!   (key,value) pairs within a single PostgreSQL data field.
This can be useful in various scenarios, such as rows with many attributes
that are rarely examined, or semi-structured data.  Keys and values are
!   arbitrary text strings.
   
  
   
hstore External Representation
  

!The text representation of an hstore value includes zero
!or more key => value
!items, separated by commas.  For example:
  
 
! k => v
! foo => bar, baz => whatever
! "1-a" => "anything at all"
 
  
!The order of the items is not considered significant (and may not be
!reproduced on output).  Whitespace between items or around the
!=> sign is ignored.  Use double quotes if a key or
!value includes whitespace, comma, = or >.
!To include a double quote or a backslash in a key or value, precede
!it with another backslash.

  

!A value (but not a key) can be a SQL NULL.  This is represented as
  
 
! key => NULL
 
  
!The NULL keyword is not case-sensitive.  Again, use
!double quotes if you want the string null to be treated
!as an ordinary data value.

  


!Keep in mind that the above format, when used to input hstore values,
!applies before any required quoting or escaping. If you
!are passing an hstore literal via a parameter, then no additional
!processing is needed. If you are passing it as a quoted literal
!constant, then any single-quote characters and (depending on the
!setting of standard_conforming_strings) backslash characters
!need to be escaped correctly. See .


  

!Double quotes are always used to surround key and value
!strings on output, even when this is not strictly necessary.

  
   
--- 8,83 
   
  
   
!   This module implements the hstore data type for storing sets of
!   key/value pairs within a single PostgreSQL value.
This can be useful in various scenarios, such as rows with many attributes
that are rarely examined, or semi-structured data.  Keys and values are
!   simply text strings.
   
  
   
hstore External Representation
  

! 
!The text representation of an hstore, used for input and output,
!includes zero or more key =>
!value pairs separated by commas. Some examples:
  
 
! k => v
! foo => bar, baz => whatever
! "1-a" => "anything at all"
 
  
!The order of the pairs is not significant (and may not be reproduced on
!output). Whitespace between pairs or around the => sign is
!ignored. Double-quote keys and values that include whitespace, commas,
!=s or >s. To include a double quote or a
!backslash in a key or value, escape it with a backslash.

  

!Each key in an hstore is unique. If you declare an hstore
!with duplicate keys, only one will be stored in the hstore and
!there is no guarantee as to which will be kept:
  
 
! % select 'a=>1,a=>2'::hstore;
!   hstore
! --
!  "a"=>"1"
 
+   
  
!   
!A value (but not a key) can be an SQL NULL. For example:
! 
!
! key => NULL
!
! 
!The NULL keyword is case-insensitive. Double-quote the
!NULL to treat it as the ordinary string "NULL".

  


!Keep in mind that the hstore text format, when used for input,
!applies before any required quoting or escaping. If you are
!passing an hstore literal via a parameter, then no additional
!processing is needed. But if you're passing it as a quoted literal
!constant, then any single-quote characters and (depending on the setting of
!the standard_conforming_strings configuration parameter)
!backslash characters need to be escaped correctly. See
! for more on the handling of string
!constants.


  

!On output, double quotes always surround keys and values, even when it's
!not strictly necessary.

  
   
***
*** 87,128 
  
   
hstore -> text
!   get value for key (null if not present)
'a=>x, b=>y'::hstore -> 'a'
x
   
  
   
hstore -> text[]
!   

Re: [HACKERS] [patch] pg_ctl init extension

[Peter Eisentraut]

On lör, 2009-11-14 at 14:50 +0100, Zdenek Kotala wrote:
> Peter Eisentraut píše v so 14. 11. 2009 v 10:41 +0200:
> > On tor, 2009-09-17 at 21:43 +0200, Zdenek Kotala wrote:
> > > Attached patch extends pg_ctl command with init option. 
> > > 
> > > pg_ctl -D /var/lib/postgres [-s] init
> > > 
> > > This should replace usage of initdb command which has problematic name
> > > as we already discussed several times. Initdb binary will be still
> > > there, but it can be renamed and move into execlib dir in the future.
> > > 
> > > Patch does not contains documentation changes. They will depends on
> > > decision which database initialization method will be preferred.
> > 
> > OK, let's see.  The patch is pretty straightforward, but does anyone
> > else actually want this?  Comments?
> > 
> 
> Maybe we could ask on general where is more admins. I will send voting
> email.

I think this is over now.  There was some support, some "don't care, but
could make sense", and no one violently objecting, so please finish the
patch up with documentation, and it can go in as far as I'm concerned.

Someone was proposing that pg_ctl initdb be an alias to pg_ctl init.
Perhaps you could add that.


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

Re: [HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

2009/11/21 Pavel Stehule :
> Hello,
>
> I am still thinking, about using DO statement from psql console. I am
> missing any parametrisation. It could not be a problem. All pl have a
> support for parameters, we have a libpq function PQexecParams - so we
> need only some "USING" clause. I propose following syntax (and using
> from client environments)
>
> DO $$ body $$ USING expr [ ,expr [...]];
>
> body should to have a unnamed parameters in syntax related to any PL.
> I'll use plpgsql for this moment.
>
> so some the most simply sample should look like:
>
> DO $$
> BEGIN
>  RAISE NOTICE 'Hello, %', $1;
> END
> $$ USING 'World';
>
> From psql:
>
> \set message World
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING :message;
>
> From pgscript:
>
> SET @message = 'World';
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING @message;
>
> From C
>
> values[0] = "World";
> result = PQexecParams(cn, "DO $$BEGIN RAISE NOTICE 'Hello, %', $1;
> END;$$ USING $1",
>                                                 1,
>                                                  NULL, values,
>                                                  NULL, NULL,
>                                                  0);
>

last note - type of parameter is taken from a value. When value will
be NULL, then type will be unknown.

Pavel

> What do you thing about this proposal?
>
> 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

[HACKERS] Proposal: USING clause for DO statement

[Pavel Stehule]

Hello,

I am still thinking, about using DO statement from psql console. I am
missing any parametrisation. It could not be a problem. All pl have a
support for parameters, we have a libpq function PQexecParams - so we
need only some "USING" clause. I propose following syntax (and using
from client environments)

DO $$ body $$ USING expr [ ,expr [...]];

body should to have a unnamed parameters in syntax related to any PL.
I'll use plpgsql for this moment.

so some the most simply sample should look like:

DO $$
BEGIN
  RAISE NOTICE 'Hello, %', $1;
END
$$ USING 'World';

>From psql:

\set message World
DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING :message;

>From pgscript:

SET @message = 'World';
DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING @message;

>From C

values[0] = "World";
result = PQexecParams(cn, "DO $$BEGIN RAISE NOTICE 'Hello, %', $1;
END;$$ USING $1",
 1,
  NULL, values,
  NULL, NULL,
  0);

What do you thing about this proposal?

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] DEFAULT of domain ignored in plpgsql (8.4.1)

[Grzegorz Jaskiewicz]

On 21 Nov 2009, at 02:56, Josh Berkus wrote:

> 
>> Would a patch that changes that have any chance of being accepted? Or is
>> the gain (not having to repeat the DEFAULT clause, and being able to
>> maintain it at one place instead of many) considered too small compared
>> to the risk of breaking existing code?
> 
> I don't think there's a lot of risk of code breakage; few people use
> domains, fewer use them with defaults, and you might be the only one
> using them as variable types.  And there are going to be more
> substantial backwards compat issues with the lexer changes anyway.  As
> long as we remember to flag the compatibility issue in the release
> notes, I don't see it as a problem.

we use domains with defaults, a lot. That's one of the purposes of domains, to 
have certain type, constraint, and default. 


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