Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread Dimitri Fontaine
> I definitely agree that PL/pgsql could be more usable.  Or if not,
> then some other PL with a better overall design could be more usable.
> I am not entirely sure how to create such a thing, however.

Would the standard plpsm be just that? Pavel maintains a pg implémentation of 
it, I believe.

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


Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread Pavel Stehule
> Really?
>
> FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP
>
> I mean, doing everything is sort of clunky in PL/pgsql, but this
> doesn't seem particularly bad as PL/pgsql idioms go.
>

this simple construction can take much more memory than other. I
proposed two or three years ago FOREACH statement

FOREACH var IN array LOOP END LOOP

this statement can be implemented very efective - and I think it can
be joined to some form of string_to_array function, because var
specify target element type.

FOREACH var IN parse('',...) LOOP END LOOP

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] review: psql: edit function, show function commands patch

2010-08-11 Thread Pavel Stehule
2010/8/11 Robert Haas :
> On Wed, Aug 11, 2010 at 12:28 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane  wrote:
 BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag
 seems grossly overdesigned.  It would be clearer, shorter, and faster if
 you just had a strncmp test for "AS $function" there.
>>
>>> As far as I can see, the only purpose of that code is to support the
>>> desire to have \sf+ display  rather than a line number for the
>>> lines that FOLLOW the function body.  But I'm wondering if we should
>>> just forget about that and let the numbering run continuously from the
>>> first "AS $function" line to end of file.  That would get rid of a
>>> bunch of rather grotty code in the \sf patch, also.
>>
>> Oh?  Considering that in the standard pg_get_functiondef output, the
>> ending $function$ delimiter is always on the very last line, that sounds
>> pretty useless.  +1 for just numbering forward from the start line.
>
> OK.
>
>> BTW, the last I looked, \sf+ was using what I thought to be a quite ugly
>> and poorly-considered formatting for the line number.  I would suggest
>> eight blanks for a header line and "%-7d " as the prefix format for a
>> numbered line.  The reason for making sure the prefix is 8 columns rather
>> than some other width is to not mess up tab-based formatting of the
>> function body.  I would also prefer a lot more visual separation between
>> the line number and the code than "%4d " will offer; and as for the
>> stars, they're just useless and distracting.
>
> I don't have a strong preference, but that seems reasonable.  I
> suggest that we punt the \sf portion of this patch back for rework for
> the next CommitFest, and focus on getting the \e and \ef changes
> committed.  I think the \sf code can be a lot simpler if we get rid of
> the code that's intended to recognize the ending delimeter.
>

the proposed changes are not complex, and there are not reason to move
\sf to next commitfest. I am thinking about little bit simplification
- there can by only one cycle without two. After \e commiting there
are other complex code. If some code isn't  clean, then it is because
there are  \o and pager support.

> Another thought is that we might want to add a comment to
> pg_get_functiondef() noting that anyone changing the output format
> should be careful not to break the line-number-finding form of \ef in
> the process.
>

+1

Regards

Pavel Stehule

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

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


Re: [HACKERS] MERGE command for inheritance

2010-08-11 Thread Boxuan Zhai
On Thu, Aug 12, 2010 at 12:49 AM, Greg Smith  wrote:

> Tom Lane wrote:
>
>> Do we really think this is anywhere near committable now?
>>
>>
>
> There's a relatively objective standard for the first thing needed for
> commit--does it work?--in the form of the regression tests Simon put
> together before development.  I just tried the latest merge_v102.patch
> (regression diff attached) to see how that's going.  There are still a
> couple of errors in there.  It looks to me like the error handling and
> related DO NOTHING support are the next pair of things that patch needs work
> on.  I'd rather see that sorted out than to march onward to inheritance
> without the fundamentals even nailed down yet.
>
>

Sorry for the mismatch problem of regress. In fact, I am still unable to
make the regression test run on my machine. When I try the command
pg_regreess in /src/test/regress/, it always gives a FATAL error:

FATAL:  parameter "port" cannot be changed without restarting the server
psql: FATAL:  parameter "port" cannot be changed without restarting the
server
command failed: ""C:/msys/1.0/local/pgsql/bin//psql" -X -c "DROP DATABASE IF
EXISTS \"regression\"" "postgres""

However, I can run this command directly in the MinGW command line
interface. I guess this is because the psql_command() function has some
check before accept commands. And the MinGW environment  cannot pass these
checks.

All the SQL commands in the .sql file have been tested by hand. And they are
all correct. However, the .out file is not automatic generated by pgsql.

I may need to find a linux system to try to generate the correct .out file
some other time. Or, would someone help me to generate an .out file through
pg_regress?


> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us 
>
>
> ***
> /home/postgres/pgwork/repo/git/postgresql/src/test/regress/expected/merge.out
>   2010-08-11 12:23:50.0 -0400
> ---
> /home/postgres/pgwork/repo/git/postgresql/src/test/regress/results/merge.out
>2010-08-11 12:33:27.0 -0400
> ***
> *** 44,57 
>  WHEN MATCHED THEN
>UPDATE SET balance = t.balance + s.balance
>  ;
> ! SELECT * FROM target;
> !  id | balance
> ! +-
> !   1 |  10
> !   2 |  25
> !   3 |  50
> ! (3 rows)
> !
>  ROLLBACK;
>  -- do a simple equivalent of an INSERT SELECT
>  BEGIN;
> --- 44,50 
>  WHEN MATCHED THEN
>UPDATE SET balance = t.balance + s.balance
>  ;
> ! NOTICE:  one tuple is ERROR
>  ROLLBACK;
>  -- do a simple equivalent of an INSERT SELECT
>  BEGIN;
> ***
> *** 61,66 
> --- 54,61 
>  WHEN NOT MATCHED THEN
>INSERT VALUES (s.id, s.balance)
>  ;
> + NOTICE:  one tuple is ERROR
> + NOTICE:  one tuple is ERROR
>  SELECT * FROM target;
>   id | balance
>  +-
> ***
> *** 102,107 
> --- 97,103 
>  WHEN MATCHED THEN
>DELETE
>  ;
> + NOTICE:  one tuple is ERROR
>  SELECT * FROM target;
>   id | balance
>  +-
> ***
> *** 165,176 
>  ERROR:  multiple actions on single target row
>
>  ROLLBACK;
> !
>  -- This next SQL statement
>  --  fails according to standard
>  --  suceeds in PostgreSQL implementation by simply ignoring the second
>  --  matching row since it activates no WHEN clause
>  BEGIN;
>  MERGE into target t
>  USING (select * from source) AS s
>  ON t.id = s.id
> --- 161,175 
>  ERROR:  multiple actions on single target row
>
>  ROLLBACK;
> ! ERROR:  syntax error at or near "ERROR"
> ! LINE 1: ERROR:  multiple actions on single target row
> ! ^
>  -- This next SQL statement
>  --  fails according to standard
>  --  suceeds in PostgreSQL implementation by simply ignoring the second
>  --  matching row since it activates no WHEN clause
>  BEGIN;
> + ERROR:  current transaction is aborted, commands ignored until end of
> transaction block
>  MERGE into target t
>  USING (select * from source) AS s
>  ON t.id = s.id
> ***
> *** 179,184 
> --- 178,184 
>  WHEN NOT MATCHED THEN
>INSERT VALUES (s.id, s.balance)
>  ;
> + ERROR:  current transaction is aborted, commands ignored until end of
> transaction block
>  ROLLBACK;
>  -- Now lets prepare the test data to generate 2 non-matching rows
>  DELETE FROM source WHERE id = 3 AND balance = 5;
> ***
> *** 188,195 
>  +-
>2 |   5
>3 |  20
> -   4 |   5
>4 |  40
>  (4 rows)
>
>  -- This next SQL statement
> --- 188,195 
>  +-
>2 |   5
>3 |  20
>4 |  40
> +   4 |   5
>  (4 rows)
>
>  -- This next SQL statement
> ***
> *** 203,216 
>  WHEN NOT MATCHED THEN
>INSERT VALUES (s.id, s.balance)
>  ;
>  SELECT * FROM target;
>   id | balance
>  +-
>1 |  10
>2 |  20
>3 |  30
> -   4 |   5
>4 |  40
>  (

Re: [HACKERS] RecordTransactionCommit() and SharedInvalidationMessages

2010-08-11 Thread Fujii Masao
On Wed, Aug 11, 2010 at 11:35 PM, Heikki Linnakangas
 wrote:
> On 11/08/10 16:46, Robert Haas wrote:
>>
>> On Wed, Aug 11, 2010 at 1:17 AM, Fujii Masao
>>  wrote:
>>>
>>> On Tue, Aug 10, 2010 at 9:30 AM, Robert Haas
>>>  wrote:

 It appears to me that RecordTransactionCommit() only needs to WAL-log
 shared invalidation messages when wal_level is hot_standby, but I
 don't see a guard to prevent it from doing it in all cases.
>>>
>>> Perhaps right. During not hot standby, there is no backend which the
>>> startup process should send invalidation message to in the standby.
>>> So, ISTM we don't need to log invalidation message when wal_level is
>>> not hot_standby.
>>
>> The fix looks pretty simple (see attached), although I don't have any
>> clear idea how to test it.
>
> Should use XLogStandbyInfoActive() macro, for the sake of consistency.

And, RelcacheInitFileInval should be initialized with false just in case.

>> I guess the question is whether we should
>> back-patch this to 9.0.  It isn't technically necessary for
>> correctness, but the whole point of introducing the wal_level GUC was
>> to insulate people not running Hot Standby from possible bugs in the
>> Hot Standby code, as well as to avoid unnecessary WAL bloat, so on
>> balance I'm inclined to think we should go ahead and back-patch it.
>
> +1 for backpatching. Keeping the branches closer to each other makes
> backporting any future fixes easier too.

+1

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] review: psql: edit function, show function commands patch

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> I suggest that we punt the \sf portion of this patch back for rework for
> the next CommitFest, and focus on getting the \e and \ef changes
> committed.  I think the \sf code can be a lot simpler if we get rid of
> the code that's intended to recognize the ending delimeter.

I've committed the \e/\ef part after some further tweaking.  I concur
with marking the \sf part as Returned With Feedback.

> Another thought is that we might want to add a comment to
> pg_get_functiondef() noting that anyone changing the output format
> should be careful not to break the line-number-finding form of \ef in
> the process.

Done.

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] review: psql: edit function, show function commands patch

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 6:21 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> ...  If you're still unhappy with it, you're going to need to
>> be more specific, or hack on it yourself.
>
> I'm doing another pass over this.  I notice that the documentation
> claims the syntax of \e is "\e [FILE] [LINE]", but what is actually
> implemented is "\e [FILE [LINE]]", ie it is not possible to specify a
> line number without a file.  Now, it seems to me that specifying a line
> number in the query buffer would actually be a pretty darn useful thing
> to do, if you'd typed in a large query and the backend had spit back
> "LINE 42: some problem or other".  So I think we should fix it so that
> case works and the documentation isn't lying.  This would require
> interpreting \e followed by a digit string as a line number not a file
> ... anybody have a problem with that?  If you're really eager to edit a
> numerically-named file you could fake it out with "\e 1234 1".

Or \e ./1234

It's a minor incompatibility, but it's probably reasonable to allow that.

> BTW, there doesn't seem to be a need to do anything similar for \ef.
> It does have the ability to omit a func name, but then you get a blank
> CREATE FUNCTION template you're going to have to fill in, so there's
> no advantage to positioning the cursor beyond the first line to start.

Hmm, OK.

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

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


Re: [HACKERS] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread David Fetter
On Wed, Aug 11, 2010 at 07:39:37PM -0400, Andrew Dunstan wrote:
> On 08/11/2010 07:33 PM, David Fetter wrote:
> >>I would be curious to the benefit of putting it in core. I have no
> >>problem with the type but in core?
> >If it's not in core, the vast majority of users will not have it
> >installed, and nothing, in core or otherwise, will be able to count on
> >it.
> 
> You could say that about almost any feature. PostgreSQL is designed
> to be modular, and we can hardly credibly use that as an argument
> against ourselves.
> 
> A convincing argument would be that there is another feature we want
> in core that needs or at least could benefit from it.

EXPLAIN (FORMAT JSON) would benefit right away, as its overall code
would be much more likely to be (and stay) correct.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread Bruce Momjian
Andrew Dunstan wrote:
> 
> 
> On 08/11/2010 07:33 PM, David Fetter wrote:
> >> I would be curious to the benefit of putting it in core. I have no
> >> problem with the type but in core?
> > If it's not in core, the vast majority of users will not have it
> > installed, and nothing, in core or otherwise, will be able to count on
> > it.
> >
> >
> 
> 
> You could say that about almost any feature. PostgreSQL is designed to 
> be modular, and we can hardly credibly use that as an argument against 
> ourselves.
> 
> A convincing argument would be that there is another feature we want in 
> core that needs or at least could benefit from it.

I would say that JSON is no longer a niche data format, which would
suggest its inclusion in core.

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

  + It's impossible for everything to be true. +

-- 
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] string_to_array with an empty input string

2010-08-11 Thread Peter Geoghegan
On 11 August 2010 21:52, Tom Lane  wrote:
> Peter Geoghegan  writes:
>> What's wrong with something like array_is_empty(anyarray) returns
>> boolean?
>
> What's that got to do with iterating over an array?

Only that I'm of the opinion that we'd be well served by more array
convenience functions, including convenience functions like the
proposed array_is_empty(), and, perhaps, functions that exist for the
express purpose of iterating over arrays more tersely in plpgsql.


> We could certainly
> provide it if it were commonly useful, but I'm not convinced of that.

Fair enough. I agree that it isn't *commonly* useful. I just don't
think that we're disadvantaged by being more inclusive, while still
bringing benefits to a minority of applications and users.


On 11 August 2010 23:06, Robert Haas  wrote:
>> Iterating over an array is a simple thing. We should make simple things easy.
>
> I definitely agree that PL/pgsql could be more usable.  Or if not,
> then some other PL with a better overall design could be more usable.
> I am not entirely sure how to create such a thing, however.

Nor am I. However, I think that richer array manipulation facilities
are low-hanging fruit.

-- 
Regards,
Peter Geoghegan

-- 
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: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 07:33 PM, David Fetter wrote:

I would be curious to the benefit of putting it in core. I have no
problem with the type but in core?

If it's not in core, the vast majority of users will not have it
installed, and nothing, in core or otherwise, will be able to count on
it.





You could say that about almost any feature. PostgreSQL is designed to 
be modular, and we can hardly credibly use that as an argument against 
ourselves.


A convincing argument would be that there is another feature we want in 
core that needs or at least could benefit from 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] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread David Fetter
On Wed, Aug 11, 2010 at 03:40:36PM -0700, Joshua D. Drake wrote:
> On Wed, 2010-08-11 at 15:27 -0700, David Fetter wrote:
> >  
> > > I've been developing it as a contrib module because:
> > > * I'd imagine it's easier than developing it as a built-in
> > > datatype right away (e.g. editing a .sql.in file versus editing
> > > pg_type.h ).
> > > * As a module, it has PGXS support, so people can try it out
> > > right away rather than having to recompile PostgreSQL.
> > > 
> > > I, for one, think it would be great if the JSON datatype were
> > > all in core :-)  However, if and how much JSON code should go
> > > into core is up for discussion.  Thoughts, anyone?
> > 
> > +1 for putting it in core in 9.1 :)
> 
> I would be curious to the benefit of putting it in core. I have no
> problem with the type but in core?

If it's not in core, the vast majority of users will not have it
installed, and nothing, in core or otherwise, will be able to count on
it.

As this is really pretty green-field stuff, it's super unlikely to
break extant code. :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread Joshua D. Drake
On Wed, 2010-08-11 at 15:27 -0700, David Fetter wrote:
>  
> > I've been developing it as a contrib module because:
> > * I'd imagine it's easier than developing it as a built-in datatype
> > right away (e.g. editing a .sql.in file versus editing pg_type.h ).
> > * As a module, it has PGXS support, so people can try it out right
> > away rather than having to recompile PostgreSQL.
> > 
> > I, for one, think it would be great if the JSON datatype were all in
> > core :-)  However, if and how much JSON code should go into core is up
> > for discussion.  Thoughts, anyone?
> 
> +1 for putting it in core in 9.1 :)

I would be curious to the benefit of putting it in core. I have no
problem with the type but in core?

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-11 Thread David Fetter
On Sat, Jul 24, 2010 at 06:57:18PM -0400, Joseph Adams wrote:
> Update: I'm in the middle of cleaning up the JSON code (
> http://git.postgresql.org/gitweb?p=json-datatype.git;a=summary if you
> want to see the very latest ), so I haven't addressed all of the major
> problems with it yet.
> 
> On Fri, Jul 23, 2010 at 2:34 PM, Robert Haas  wrote:
> > - I was under the impression that we wanted EXPLAIN (FORMAT JSON) to
> > return type json, but that's obviously not going to be possible if all
> > of this is contrib.  We could (a) move it all into core, (b) move the
> > type itself and its input and output functions into core and leave the
> > rest in contrib [or something along those lines], or (c) give up using
> > it as the return type for EXPLAIN (FORMAT JSON).
> 
> I've been developing it as a contrib module because:
> * I'd imagine it's easier than developing it as a built-in datatype
> right away (e.g. editing a .sql.in file versus editing pg_type.h ).
> * As a module, it has PGXS support, so people can try it out right
> away rather than having to recompile PostgreSQL.
> 
> I, for one, think it would be great if the JSON datatype were all in
> core :-)  However, if and how much JSON code should go into core is up
> for discussion.  Thoughts, anyone?

+1 for putting it in core in 9.1 :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] review: psql: edit function, show function commands patch

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> ...  If you're still unhappy with it, you're going to need to
> be more specific, or hack on it yourself.

I'm doing another pass over this.  I notice that the documentation
claims the syntax of \e is "\e [FILE] [LINE]", but what is actually
implemented is "\e [FILE [LINE]]", ie it is not possible to specify a
line number without a file.  Now, it seems to me that specifying a line
number in the query buffer would actually be a pretty darn useful thing
to do, if you'd typed in a large query and the backend had spit back
"LINE 42: some problem or other".  So I think we should fix it so that
case works and the documentation isn't lying.  This would require
interpreting \e followed by a digit string as a line number not a file
... anybody have a problem with that?  If you're really eager to edit a
numerically-named file you could fake it out with "\e 1234 1".

BTW, there doesn't seem to be a need to do anything similar for \ef.
It does have the ability to omit a func name, but then you get a blank
CREATE FUNCTION template you're going to have to fill in, so there's
no advantage to positioning the cursor beyond the first line to start.

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] string_to_array with an empty input string

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 4:21 PM, Peter Geoghegan
 wrote:
> On 11 August 2010 18:53, Robert Haas  wrote:
>>> I think that there's a need for additional built-in array functions,
>>> including one to succinctly test if an array has no elements.
>>
>> What do you propose?  I think the easiest ways to do it right now are:
>>
>> array_length(arr, 1) is null
>>
>> or just using an equality test, like this:
>>
>> arr = '{}'::int[]
>
> What's wrong with something like array_is_empty(anyarray) returns
> boolean? I don't know why we're so apparently averse to creating
> built-in convenience functions. It's quite easy to forget the intent
> of either of those two statements.

Nothing's wrong with it, but the second one seems pretty hard to
forget the intent of... at least to me.

>>> Iterating through an array with plpgsql, for example, is more clunky
>>> than it should be.
>>
>> Really?
>>
>> FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP
>>
>> I mean, doing everything is sort of clunky in PL/pgsql, but this
>> doesn't seem particularly bad as PL/pgsql idioms go.
>
> Right. I agree that many of the idioms are on the clunky side, but I
> think that the fact that my original remarks about iterating over
> arrays generated discussion is a bit telling. unnest() was only
> introduced in PG 8.4.

True... but now we have it.

> Iterating over an array is a simple thing. We should make simple things easy.

I definitely agree that PL/pgsql could be more usable.  Or if not,
then some other PL with a better overall design could be more usable.
I am not entirely sure how to create such a thing, however.

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

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


Re: [HACKERS] review: xml_is_well_formed

2010-08-11 Thread Mike Fowler

On 11/08/10 21:27, Tom Lane wrote:

Robert Haas  writes:

On Mon, Aug 9, 2010 at 10:41 AM, Robert Haas  wrote:

There's also the fact that it would probably end up parsing the data
twice.  Given xmloption, I'm inclined to think Tom has it right:
provided xml_is_well_formed() that follows xmloption, plus a specific
version for each of content and document.



Another reasonable option here would be to forget about having
xml_is_well_formed() per se and ONLY offer
xml_is_well_formed_content() and xml_is_well_formed_document().


We already have xml_is_well_formed(); just dropping it doesn't seem like
a helpful choice.


As a project management note, this CommitFest is over in 4 days, so
unless we have a new version of this patch real soon now we need to
defer it to the September 15th CommitFest


Yes.  Mike, are you expecting to submit a new version before the end of
the week?



Yes and here it is, apologies for the delay. I have re-implemented 
xml_is_well_formed such that it is sensitive to the XMLOPTION. The 
additional _document and _content methods are now present. Tests and 
documentation adjusted to suit.


Regards,

--
Mike Fowler
Registered Linux user: 379787
*** a/contrib/xml2/pgxml.sql.in
--- b/contrib/xml2/pgxml.sql.in
***
*** 5,18  SET search_path = public;
  
  --SQL for XML parser
  
- CREATE OR REPLACE FUNCTION xml_is_well_formed(text) RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
- 
  -- deprecated old name for xml_is_well_formed
  CREATE OR REPLACE FUNCTION xml_valid(text) RETURNS bool
! AS 'MODULE_PATHNAME', 'xml_is_well_formed'
! LANGUAGE C STRICT IMMUTABLE;
  
  CREATE OR REPLACE FUNCTION xml_encode_special_chars(text) RETURNS text
  AS 'MODULE_PATHNAME'
--- 5,14 
  
  --SQL for XML parser
  
  -- deprecated old name for xml_is_well_formed
  CREATE OR REPLACE FUNCTION xml_valid(text) RETURNS bool
! AS 'xml_is_well_formed'
! LANGUAGE INTERNAL STRICT IMMUTABLE;
  
  CREATE OR REPLACE FUNCTION xml_encode_special_chars(text) RETURNS text
  AS 'MODULE_PATHNAME'
*** a/contrib/xml2/uninstall_pgxml.sql
--- b/contrib/xml2/uninstall_pgxml.sql
***
*** 29,33  DROP FUNCTION xml_encode_special_chars(text);
  
  -- deprecated old name for xml_is_well_formed
  DROP FUNCTION xml_valid(text);
- 
- DROP FUNCTION xml_is_well_formed(text);
--- 29,31 
*** a/contrib/xml2/xpath.c
--- b/contrib/xml2/xpath.c
***
*** 27,33  PG_MODULE_MAGIC;
  
  /* externally accessible functions */
  
- Datum		xml_is_well_formed(PG_FUNCTION_ARGS);
  Datum		xml_encode_special_chars(PG_FUNCTION_ARGS);
  Datum		xpath_nodeset(PG_FUNCTION_ARGS);
  Datum		xpath_string(PG_FUNCTION_ARGS);
--- 27,32 
***
*** 71,97  pgxml_parser_init(void)
  }
  
  
- /* Returns true if document is well-formed */
- 
- PG_FUNCTION_INFO_V1(xml_is_well_formed);
- 
- Datum
- xml_is_well_formed(PG_FUNCTION_ARGS)
- {
- 	text	   *t = PG_GETARG_TEXT_P(0);		/* document buffer */
- 	int32		docsize = VARSIZE(t) - VARHDRSZ;
- 	xmlDocPtr	doctree;
- 
- 	pgxml_parser_init();
- 
- 	doctree = xmlParseMemory((char *) VARDATA(t), docsize);
- 	if (doctree == NULL)
- 		PG_RETURN_BOOL(false);	/* i.e. not well-formed */
- 	xmlFreeDoc(doctree);
- 	PG_RETURN_BOOL(true);
- }
- 
- 
  /* Encodes special characters (<, >, &, " and \r) as XML entities */
  
  PG_FUNCTION_INFO_V1(xml_encode_special_chars);
--- 70,75 
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 8625,8630  SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Tor
--- 8625,8736 
   supports XPath, which is a subset of XQuery.
  
 
+ 
+
+ xml_is_well_formed
+ 
+ 
+  xml_is_well_formed
+  well formed
+ 
+ 
+ 
+ xml_is_well_formed(text)
+ 
+ 
+ 
+  The function xml_is_well_formed evaluates whether
+  the text is well formed XML content, returning
+  a boolean. It is useful for predetermining whether a cast to the XML type
+  will succeed, and therefore honours the current value of the
+  XMLOPTION session configuration parameter.
+ 
+ 
+ Example:
+ 
+ 
+ 
+ In addition to the structure checks, the function ensures that namespaces are correcty matched.
+ 
+ 
+
+ 
+
+ xml_is_well_formed_document
+ 
+ 
+  xml_is_well_formed_document
+  well formed
+ 
+ 
+ 
+ xml_is_well_formed_document(text)
+ 
+ 
+ 
+ This function is similar to xml_is_well_formed,
+ differing only in the handling of the XMLOPTION. The
+ xml_is_well_formed_document ignores XMLOPTION
+ and assumes that it is currently set to DOCUMENT. Therfore
+ it assists in predetermining whether a cast to the XML document type
+ will succeed.
+ 
+
+ 
+
+ xml_is_well_formed_content
+ 
+ 
+  xml_is_well_formed_content
+  well formed
+ 
+ 
+ 
+ xml_is_well_formed_content(text)
+ 
+ 
+ 
+ This function is similar to xml_is_well_formed,
+ diffe

Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Christopher Browne
On Wed, Aug 11, 2010 at 5:16 PM, Tom Lane  wrote:
> Andrew Dunstan  writes:
>> On 08/11/2010 04:47 PM, Tom Lane wrote:
>>> I prefer the change-the-default approach mainly because it wouldn't
>>> require any documentation,
>
>> Yeah. The other advantage is that we would not need to wait until we had
>> got everyone to update their versions of the buildfarm code.
>
> Um.  That's actually a pretty darn strong point, considering how
> slow some buildfarm owners are to update the script :-(

Well, it's a convenient time to get such changes in, now, because
folks are going to need to update their scripts rather soon as a
consequence of the Git migration :-).
-- 
http://linuxfinances.info/info/linuxdistributions.html

-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Andrew Dunstan  writes:
> On 08/11/2010 04:47 PM, Tom Lane wrote:
>> I prefer the change-the-default approach mainly because it wouldn't
>> require any documentation,

> Yeah. The other advantage is that we would not need to wait until we had 
> got everyone to update their versions of the buildfarm code.

Um.  That's actually a pretty darn strong point, considering how
slow some buildfarm owners are to update the script :-(

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] Git conversion progress report and call for testing assistance

2010-08-11 Thread Heikki Linnakangas

On 21/07/10 23:40, Magnus Hagander wrote:

I've also set up the git server and the scripts around it, that we can
eventually use. This includes commit email sending, commit policy enforcement
(no merge commits, correct author/committer tag etc) and proper access control
(a modified version of the one on git.postgresql.org - since we definitely
don't want any external dependencies for the main repository).

This is all available for testing now.


Seems to work great. I've got a local clone of that set up now, with 
workdirs for backbranches (per the "Committing Using a Single Clone and 
multiple workdirs" instructions I added to the Committing_with_Git wiki 
page).


I also tested with a bunch of funny commits, including merge commits, 
non-fast-forward commits and a commit with bogus Committer, and the 
post-commit hook correctly rejected all those.



Marc has set up a mailinglist at pgsql-committers-t...@postgresql.org where
commit messages from the new system is sent. If you care about what they look
like, subscribe there and wait for one to show up :-) Subscription is done
the usual way.


My posts to that lists are been stalled, but looking at the commit mail 
in the "stalled post" reply it looks OK.


Good work! Thanks for spending the time on this, I very much prefer 
working with git nowadays.


--
  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] string_to_array with an empty input string

2010-08-11 Thread Tom Lane
Peter Geoghegan  writes:
> What's wrong with something like array_is_empty(anyarray) returns
> boolean?

What's that got to do with iterating over an array?  We could certainly
provide it if it were commonly useful, but I'm not convinced of 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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 04:47 PM, Tom Lane wrote:

Andrew Dunstan  writes:

Another way would be to have pg_regress honour an environment var like
PG_REGRESS_PORT, which the buildfarm script could use.

Yeah, that would work too.  (Is it portable to Windows, though?)


Should be


I prefer the change-the-default approach mainly because it wouldn't
require any documentation, whereas it'd be a bit hard to argue that
environment variables etc shouldn't be documented ...




Yeah. The other advantage is that we would not need to wait until we had 
got everyone to update their versions of the buildfarm code. So I agree 
this is the nicest solution.


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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Andrew Dunstan  writes:
> Another way would be to have pg_regress honour an environment var like 
> PG_REGRESS_PORT, which the buildfarm script could use.

Yeah, that would work too.  (Is it portable to Windows, though?)

I prefer the change-the-default approach mainly because it wouldn't
require any documentation, whereas it'd be a bit hard to argue that
environment variables etc shouldn't be documented ...

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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 04:17 PM, Tom Lane wrote:

We should have the
buildfarm configuration such that any one run uses the same port number
for both installed and uninstalled regression tests.  If Peter is dead
set on not changing pg_regress's default, then changing the makefiles to
enable use of the --port switch is the way to do that.




If you really want it to use the exact same port, then we'll probably 
need to change the Makefiles regardless of how we eventually decide to 
set the default.


Another way would be to have pg_regress honour an environment var like 
PG_REGRESS_PORT, which the buildfarm script could use.


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] review: xml_is_well_formed

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> On Mon, Aug 9, 2010 at 10:41 AM, Robert Haas  wrote:
>> There's also the fact that it would probably end up parsing the data
>> twice.  Given xmloption, I'm inclined to think Tom has it right:
>> provided xml_is_well_formed() that follows xmloption, plus a specific
>> version for each of content and document.

> Another reasonable option here would be to forget about having
> xml_is_well_formed() per se and ONLY offer
> xml_is_well_formed_content() and xml_is_well_formed_document().

We already have xml_is_well_formed(); just dropping it doesn't seem like
a helpful choice.

> As a project management note, this CommitFest is over in 4 days, so
> unless we have a new version of this patch real soon now we need to
> defer it to the September 15th CommitFest

Yes.  Mike, are you expecting to submit a new version before the end of
the week?

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] string_to_array with an empty input string

2010-08-11 Thread Peter Geoghegan
On 11 August 2010 18:53, Robert Haas  wrote:
>> I think that there's a need for additional built-in array functions,
>> including one to succinctly test if an array has no elements.
>
> What do you propose?  I think the easiest ways to do it right now are:
>
> array_length(arr, 1) is null
>
> or just using an equality test, like this:
>
> arr = '{}'::int[]

What's wrong with something like array_is_empty(anyarray) returns
boolean? I don't know why we're so apparently averse to creating
built-in convenience functions. It's quite easy to forget the intent
of either of those two statements.

>> Iterating through an array with plpgsql, for example, is more clunky
>> than it should be.
>
> Really?
>
> FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP
>
> I mean, doing everything is sort of clunky in PL/pgsql, but this
> doesn't seem particularly bad as PL/pgsql idioms go.

Right. I agree that many of the idioms are on the clunky side, but I
think that the fact that my original remarks about iterating over
arrays generated discussion is a bit telling. unnest() was only
introduced in PG 8.4.

Iterating over an array is a simple thing. We should make simple things easy.

-- 
Regards,
Peter Geoghegan

-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Andrew Dunstan  writes:
> You original email said:
> For some historic reasons, I have my local scripts set up so that
> they build development instances using the hardcoded port 65432.

> I think my response would be "Don't do that".

Yeah ... or at least use a different port per branch.  Or make use of
the ability to force pg_regress to use a nondefault port (which I still
say we need to make accessible through "make check", whether or not the
buildfarm does it that way).

> Having said that, maybe we could reasonably use something like 
> DEF_PGPORT + 10 * major_version + minor_version in the calculation and 
> advise buildfarm members with multiple animals to keep their port ranges 
> say, 200 or more apart.

I think that just makes it more prone to failure.  We should have the
buildfarm configuration such that any one run uses the same port number
for both installed and uninstalled regression tests.  If Peter is dead
set on not changing pg_regress's default, then changing the makefiles to
enable use of the --port switch is the way to 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] "micro bucket sort" ...

2010-08-11 Thread PostgreSQL - Hans-Jürgen Schönig
as tom pointed out - this is not possible.
there is no limit 20 in my case - i just used it to indicate that limiting does 
not make the index scan possible which it does in some other cases.
the partial sort thing simon pointed out is what is needed at this point.

many thanks,

hans



On Aug 11, 2010, at 5:29 PM, Alvaro Herrera wrote:

> Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010:
> 
>> same with limit ...
>> 
>> 
>> test=# explain analyze select * from t_test order by x, y limit 20;
> 
> But if you put the limit in a subquery which is ordered by the
> known-indexed condition, it is very fast:
> 
> alvherre=# explain analyze select * from (select * from t_test order by x 
> limit 20) f order by x, y;
>   QUERY PLAN  
>   
> ─
> Sort  (cost=1.24..1.29 rows=20 width=8) (actual time=0.252..0.296 rows=20 
> loops=1)
>   Sort Key: t_test.x, t_test.y
>   Sort Method:  quicksort  Memory: 26kB
>   ->  Limit  (cost=0.00..0.61 rows=20 width=8) (actual time=0.051..0.181 
> rows=20 loops=1)
> ->  Index Scan using idx_a on t_test  (cost=0.00..30408.36 
> rows=100 width=8) (actual time=0.046..0.098 rows=20 loops=1)
> Total runtime: 0.425 ms
> (6 filas)
> 
> 
> I guess it boils down to being able to sort a smaller result set.
> 
> -- 
> Álvaro Herrera 
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> 


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de


-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 02:12 PM, Peter Eisentraut wrote:



Even if you don't, changing this would only mean that you
couldn't safely run "make check" concurrently in multiple branches.

That's exactly the point.  The original discussion is here:
http://archives.postgresql.org/message-id/491d9935.9010...@gmx.net




You original email said:

   For some historic reasons, I have my local scripts set up so that
   they build development instances using the hardcoded port 65432.


I think my response would be "Don't do that".

Having said that, maybe we could reasonably use something like 
DEF_PGPORT + 10 * major_version + minor_version in the calculation and 
advise buildfarm members with multiple animals to keep their port ranges 
say, 200 or more apart.


But maybe we should just stick with my earlier advice :-)

cheers

andrew




Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 02:39 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 11:35 AM, Andrew Dunstan wrote:


for i in select array_subscripts(myarray, 1) loop ...

That's not a built-in function AFAIK.

Pavel pointed out to me only yesterday that it is:

   
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS




except that you mis-spelled it: It's generate_subscripts(), not 
array_subscripts().


I guess I must have missed that in the 8.4 features.

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 #5607: memmory leak in ecpg

2010-08-11 Thread Kevin Grittner
[moving discussion to -hackers]

Michael Meskes  wrote:
> "Kevin Grittner"  schrieb:
>>"Marcelo Mas"  wrote:
>> 
>>> Valgrind reports memmory leak when getting decimal data.
>> 
>>I wonder how much overlap there is between this and the patch for
>>fixing ECPG memory leaks offered by Zoltán Böszörményi three days
>>ago.
 
>>http://archives.postgresql.org/pgsql-hackers/2010-08/msg00115.php
 
> I guess the described problems are identical. Feel free to apply
> the memleal patch.
 
Is someone dealing with this?  Should this be on the "PostgreSQL 9.0
Open Items" Wiki page?
 
-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] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler
On Aug 11, 2010, at 11:35 AM, Andrew Dunstan wrote:

>> for i in select array_subscripts(myarray, 1) loop ...
> 
> That's not a built-in function AFAIK.

Pavel pointed out to me only yesterday that it is:

  
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

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] string_to_array with an empty input string

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 01:59 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote:


for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...

works well

for i in select array_subscripts(myarray, 1) loop ...




That's not a built-in function AFAIK.

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] 16/32/48/64 bytes integers

2010-08-11 Thread Heikki Linnakangas

On 11/08/10 21:19, Daniel Oliveira wrote:

I wishing to create real big numbers, but I'm facing some difficulties.

Is possible to setup an integer type of more than 8 bytes (i.e. 16/32/48/64
bytes)?


No. Not unless you write your own datatype.

Use numeric, it scales up to ridiculously large numbers.

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


[HACKERS] 16/32/48/64 bytes integers

2010-08-11 Thread Daniel Oliveira
Hello,

I wishing to create real big numbers, but I'm facing some difficulties.

Is possible to setup an integer type of more than 8 bytes (i.e. 16/32/48/64
bytes)?

Can I setup a value as large as I want?

How I should acess them using PG_RETURN_xxx and PG_GETARG_xxx macros?

Thanks in advance,

Daniel Oliveira


Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 11:47 -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote:
> >> How about just this:
> >>port = 0xC000 | (DEF_PGPORT & 0x3FFF);
> 
> > The version number was put in there intentionally, for developers who
> > work on multiple branches at once.  That's the whole reason this code
> > exists.  Please don't remove it.
> 
> I work on multiple branches all day every day.  This wouldn't hinder
> me in the slightest, because I use a different DEF_PGPORT for each
> branch.  If you don't, it's hard to see how you manage to deal with
> multiple branches on one machine ... do you not ever actually install
> them?

No, not nearly as much as I run "make check".

> Even if you don't, changing this would only mean that you
> couldn't safely run "make check" concurrently in multiple branches.

That's exactly the point.  The original discussion is here:
http://archives.postgresql.org/message-id/491d9935.9010...@gmx.net



-- 
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] MERGE command for inheritance

2010-08-11 Thread Alvaro Herrera
Excerpts from Heikki Linnakangas's message of mié ago 11 10:52:24 -0400 2010:
> On 11/08/10 17:45, Simon Riggs wrote:

> > We've seen it time and time again
> > that big projects that aim to deliver towards end of a release cycle
> > interfere with dev of other projects and leave loose ends from
> > unforeseen interactions. There's no need for that.
> 
> I don't understand what you're saying, we're not in the end of a release 
> cycle.

This patch still needs a lot of work before it's anywhere close to
committable.  I agree with Simon that it is preferrable to clean it up
to make it committable *without* the burden of extra features.  If
Boxuan continues to add more features, it will be end-of-release before
it is possible to think about committing it.

It seems better to have merge-no-inheritance in 9.1 than nothing.  If we
can get the inheritance case working for 9.1, that's even better, but I
don't think it needs to be a hard requirement.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 11:53 -0400, Andrew Dunstan wrote:
> > The version number was put in there intentionally, for developers
> who
> > work on multiple branches at once.  That's the whole reason this
> code
> > exists.  Please don't remove it.
> >
> 
> Do they run "make check" by hand simultaneously on multiple branches? 
> That's the only way you'd get a collision here, I think.

That is exactly what I'm talking about.


-- 
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] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler
On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote:

> for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...
> 
> works well

for i in select array_subscripts(myarray, 1) loop ...

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] string_to_array with an empty input string

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 01:54 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 10:53 AM, Robert Haas wrote:


Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

That tends to over-flatten if you have nested arrays and just want to iterate 
over the top level. In that case you must use generate_subscripts().



for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...

works well

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] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler
On Aug 11, 2010, at 10:53 AM, Robert Haas wrote:

>> Iterating through an array with plpgsql, for example, is more clunky
>> than it should be.
> 
> Really?
> 
> FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP
> 
> I mean, doing everything is sort of clunky in PL/pgsql, but this
> doesn't seem particularly bad as PL/pgsql idioms go.

That tends to over-flatten if you have nested arrays and just want to iterate 
over the top level. In that case you must use generate_subscripts().

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] string_to_array with an empty input string

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 1:32 PM, Peter Geoghegan
 wrote:
>> Yeah, I think David's examples are talking about the behavior of join,
>> but we're trying to decide what split should do.  I think the main
>> argument for making it return NULL is that you can then fairly easily
>> use COALESCE() to get whatever you want.  That's a bit more difficult
>> if you use return any other value.
>
> I think that there's a need for additional built-in array functions,
> including one to succinctly test if an array has no elements.

What do you propose?  I think the easiest ways to do it right now are:

array_length(arr, 1) is null

or just using an equality test, like this:

arr = '{}'::int[]

> Iterating through an array with plpgsql, for example, is more clunky
> than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

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

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


Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler

On Aug 11, 2010, at 9:40 AM, Robert Haas wrote:

> Yeah, I think David's examples are talking about the behavior of join,
> but we're trying to decide what split should do.  

Right, sorry about that.

> I think the main
> argument for making it return NULL is that you can then fairly easily
> use COALESCE() to get whatever you want.  That's a bit more difficult
> if you use return any other value.  But I think your argument that an
> empty array is better than a one-element array containing an empty
> string is very much correct, as between those options.

I prefer an empty array.

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] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler
On Aug 11, 2010, at 9:36 AM, Tom Lane wrote:

>> 
>> I believe those are all "", rather than '"' + undef + '"'.
> 
> If you believe my previous opinion that the design center for these
> functions is arrays of numbers, then a zero-entry text[] array is what
> you want, because you can successfully cast it to a zero-entry array of
> integers or floats or whatever.  Returning a single empty string will
> make those cases fail.  So at the moment I'm on the side of the fence
> that says zero-entry array is the best answer.

Seems to be precedent for that:

% perl -E 'say scalar @{[ split ",", ""]}'
0

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] string_to_array with an empty input string

2010-08-11 Thread Peter Geoghegan
> Yeah, I think David's examples are talking about the behavior of join,
> but we're trying to decide what split should do.  I think the main
> argument for making it return NULL is that you can then fairly easily
> use COALESCE() to get whatever you want.  That's a bit more difficult
> if you use return any other value.

I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.
Iterating through an array with plpgsql, for example, is more clunky
than it should be.

-- 
Regards,
Peter Geoghegan

-- 
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] MERGE command for inheritance

2010-08-11 Thread Greg Smith

Tom Lane wrote:

Do we really think this is anywhere near committable now?
  


There's a relatively objective standard for the first thing needed for 
commit--does it work?--in the form of the regression tests Simon put 
together before development.  I just tried the latest merge_v102.patch 
(regression diff attached) to see how that's going.  There are still a 
couple of errors in there.  It looks to me like the error handling and 
related DO NOTHING support are the next pair of things that patch needs 
work on.  I'd rather see that sorted out than to march onward to 
inheritance without the fundamentals even nailed down yet.


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

*** 
/home/postgres/pgwork/repo/git/postgresql/src/test/regress/expected/merge.out   
2010-08-11 12:23:50.0 -0400
--- 
/home/postgres/pgwork/repo/git/postgresql/src/test/regress/results/merge.out
2010-08-11 12:33:27.0 -0400
***
*** 44,57 
  WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.balance
  ;
! SELECT * FROM target;
!  id | balance 
! +-
!   1 |  10
!   2 |  25
!   3 |  50
! (3 rows)
! 
  ROLLBACK;
  -- do a simple equivalent of an INSERT SELECT
  BEGIN;
--- 44,50 
  WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.balance
  ;
! NOTICE:  one tuple is ERROR
  ROLLBACK;
  -- do a simple equivalent of an INSERT SELECT
  BEGIN;
***
*** 61,66 
--- 54,61 
  WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
  ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
  SELECT * FROM target;
   id | balance 
  +-
***
*** 102,107 
--- 97,103 
  WHEN MATCHED THEN
DELETE
  ;
+ NOTICE:  one tuple is ERROR
  SELECT * FROM target;
   id | balance 
  +-
***
*** 165,176 
  ERROR:  multiple actions on single target row
   
  ROLLBACK;
! 
  -- This next SQL statement
  --  fails according to standard
  --  suceeds in PostgreSQL implementation by simply ignoring the second
  --  matching row since it activates no WHEN clause
  BEGIN;
  MERGE into target t
  USING (select * from source) AS s
  ON t.id = s.id
--- 161,175 
  ERROR:  multiple actions on single target row
   
  ROLLBACK;
! ERROR:  syntax error at or near "ERROR"
! LINE 1: ERROR:  multiple actions on single target row
! ^
  -- This next SQL statement
  --  fails according to standard
  --  suceeds in PostgreSQL implementation by simply ignoring the second
  --  matching row since it activates no WHEN clause
  BEGIN;
+ ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
  MERGE into target t
  USING (select * from source) AS s
  ON t.id = s.id
***
*** 179,184 
--- 178,184 
  WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
  ;
+ ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
  ROLLBACK;
  -- Now lets prepare the test data to generate 2 non-matching rows
  DELETE FROM source WHERE id = 3 AND balance = 5;
***
*** 188,195 
  +-
2 |   5
3 |  20
-   4 |   5
4 |  40
  (4 rows)
  
  -- This next SQL statement
--- 188,195 
  +-
2 |   5
3 |  20
4 |  40
+   4 |   5
  (4 rows)
  
  -- This next SQL statement
***
*** 203,216 
  WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
  ;
  SELECT * FROM target;
   id | balance 
  +-
1 |  10
2 |  20
3 |  30
-   4 |   5
4 |  40
  (5 rows)
  
  ROLLBACK;
--- 203,218 
  WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
  ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
  SELECT * FROM target;
   id | balance 
  +-
1 |  10
2 |  20
3 |  30
4 |  40
+   4 |   5
  (5 rows)
  
  ROLLBACK;
***
*** 225,239 
  WHEN NOT MATCHED AND s.balance > 100 THEN
INSERT VALUES (s.id, s.balance)
  ;
  SELECT * FROM target;
   id | balance 
  +-
1 |  10
2 |  20
3 |  30
! |
! |
! (5 rows)
  
  ROLLBACK;
  -- This next SQL statement suceeds, but does nothing since there are
--- 227,243 
  WHEN NOT MATCHED AND s.balance > 100 THEN
INSERT VALUES (s.id, s.balance)
  ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
  SELECT * FROM target;
   id | balance 
  +-
1 |  10
2 |  20
3 |  30
! (3 rows)
  
  ROLLBACK;
  -- This next SQL statement suceeds, but does nothing since there are
***
*** 249,262 
  WHEN NOT MATCHED
DO NOTHING
  ;
  SELECT * FROM target;
!  id | balance 
! +-
!   1 |  10
!   2 |  20
!   3 |  

Re: [HACKERS] review: xml_is_well_formed

2010-08-11 Thread Robert Haas
On Mon, Aug 9, 2010 at 10:41 AM, Robert Haas  wrote:
> On Mon, Aug 9, 2010 at 10:20 AM, Peter Eisentraut  wrote:
>> On lör, 2010-08-07 at 16:47 +0100, Mike Fowler wrote:
>>> To be honest I'm happiest with returning a boolean, even if there is
>>> some confusion over content only being valid. Though changing the
>>> return
>>> value to DOCUMENT/CONTENT/NULL makes things a touch more explicit,
>>> the
>>> same results can be achieved by simply running:
>>>
>>> SELECT data::xml FROM mixed WHERE xml_is_well_formed(data) AND
>>> data::xml IS DOCUMENT;
>>
>> Note that this wouldn't necessarily work because it is not guaranteed
>> that the well-formedness test is executed before the cast to xml.  SQL
>> doesn't short-circuit left to right.  (A CASE expression could work.)
>
> There's also the fact that it would probably end up parsing the data
> twice.  Given xmloption, I'm inclined to think Tom has it right:
> provided xml_is_well_formed() that follows xmloption, plus a specific
> version for each of content and document.

Another reasonable option here would be to forget about having
xml_is_well_formed() per se and ONLY offer
xml_is_well_formed_content() and xml_is_well_formed_document().

As a project management note, this CommitFest is over in 4 days, so
unless we have a new version of this patch real soon now we need to
defer it to the September 15th CommitFest (of course not precluding
the possibility that someone will pick it up and commit it sooner, but
we're not going to postpone 9.1alpha1 for this patch).

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

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


Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 12:36 PM, Tom Lane  wrote:
> "David E. Wheeler"  writes:
>> On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:
>>> So maybe we need to revisit the issue.  Pavel was claiming that
>>> switching to a zero-element array result was a no-brainer, but evidently
>>> it isn't so.  Is anybody still excited about the alternatives?
>
>> % perl -E 'say q{"}, join(",", ""), q{"}'
>> ""
>> % ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
>> ""
>> % python -c 'print "\"" + ",".join([""]) + "\""'
>> ""
>
>> I believe those are all "", rather than '"' + undef + '"'.
>
> If you believe my previous opinion that the design center for these
> functions is arrays of numbers, then a zero-entry text[] array is what
> you want, because you can successfully cast it to a zero-entry array of
> integers or floats or whatever.  Returning a single empty string will
> make those cases fail.  So at the moment I'm on the side of the fence
> that says zero-entry array is the best answer.

Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do.  I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want.  That's a bit more difficult
if you use return any other value.  But I think your argument that an
empty array is better than a one-element array containing an empty
string is very much correct, as between those options.

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

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


Re: [HACKERS] review: psql: edit function, show function commands patch

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 12:28 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane  wrote:
>>> BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag
>>> seems grossly overdesigned.  It would be clearer, shorter, and faster if
>>> you just had a strncmp test for "AS $function" there.
>
>> As far as I can see, the only purpose of that code is to support the
>> desire to have \sf+ display  rather than a line number for the
>> lines that FOLLOW the function body.  But I'm wondering if we should
>> just forget about that and let the numbering run continuously from the
>> first "AS $function" line to end of file.  That would get rid of a
>> bunch of rather grotty code in the \sf patch, also.
>
> Oh?  Considering that in the standard pg_get_functiondef output, the
> ending $function$ delimiter is always on the very last line, that sounds
> pretty useless.  +1 for just numbering forward from the start line.

OK.

> BTW, the last I looked, \sf+ was using what I thought to be a quite ugly
> and poorly-considered formatting for the line number.  I would suggest
> eight blanks for a header line and "%-7d " as the prefix format for a
> numbered line.  The reason for making sure the prefix is 8 columns rather
> than some other width is to not mess up tab-based formatting of the
> function body.  I would also prefer a lot more visual separation between
> the line number and the code than "%4d " will offer; and as for the
> stars, they're just useless and distracting.

I don't have a strong preference, but that seems reasonable.  I
suggest that we punt the \sf portion of this patch back for rework for
the next CommitFest, and focus on getting the \e and \ef changes
committed.  I think the \sf code can be a lot simpler if we get rid of
the code that's intended to recognize the ending delimeter.

Another thought is that we might want to add a comment to
pg_get_functiondef() noting that anyone changing the output format
should be careful not to break the line-number-finding form of \ef in
the process.

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

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


Re: [HACKERS] string_to_array with an empty input string

2010-08-11 Thread Tom Lane
"David E. Wheeler"  writes:
> On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:
>> So maybe we need to revisit the issue.  Pavel was claiming that
>> switching to a zero-element array result was a no-brainer, but evidently
>> it isn't so.  Is anybody still excited about the alternatives?

> % perl -E 'say q{"}, join(",", ""), q{"}'
> ""
> % ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
> ""
> % python -c 'print "\"" + ",".join([""]) + "\""'
> ""

> I believe those are all "", rather than '"' + undef + '"'.

If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever.  Returning a single empty string will
make those cases fail.  So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.

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] review: psql: edit function, show function commands patch

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane  wrote:
>> BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag
>> seems grossly overdesigned.  It would be clearer, shorter, and faster if
>> you just had a strncmp test for "AS $function" there.

> As far as I can see, the only purpose of that code is to support the
> desire to have \sf+ display  rather than a line number for the
> lines that FOLLOW the function body.  But I'm wondering if we should
> just forget about that and let the numbering run continuously from the
> first "AS $function" line to end of file.  That would get rid of a
> bunch of rather grotty code in the \sf patch, also.

Oh?  Considering that in the standard pg_get_functiondef output, the
ending $function$ delimiter is always on the very last line, that sounds
pretty useless.  +1 for just numbering forward from the start line.

BTW, the last I looked, \sf+ was using what I thought to be a quite ugly
and poorly-considered formatting for the line number.  I would suggest
eight blanks for a header line and "%-7d " as the prefix format for a
numbered line.  The reason for making sure the prefix is 8 columns rather
than some other width is to not mess up tab-based formatting of the
function body.  I would also prefer a lot more visual separation between
the line number and the code than "%4d " will offer; and as for the
stars, they're just useless and distracting.

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] string_to_array with an empty input string

2010-08-11 Thread David E. Wheeler
On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:

> I had forgotten that discussion.  It looks like we trailed off without
> any real consensus: there was about equal sentiment for an array with
> zero elements and an array with one empty-string element.  We ended
> up leaving it alone because (a) that wouldn't break anything and (b)
> you could use COALESCE() to substitute whichever behavior your
> application needed for the case.
> 
> So maybe we need to revisit the issue.  Pavel was claiming that
> switching to a zero-element array result was a no-brainer, but evidently
> it isn't so.  Is anybody still excited about the alternatives?

% perl -E 'say q{"}, join(",", ""), q{"}'
""
% ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
""
% python -c 'print "\"" + ",".join([""]) + "\""'
""

I believe those are all "", rather than '"' + undef + '"'.

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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
"Kevin Grittner"  writes:
> Peter Eisentraut  wrote:
>> Why not just compare pg_backend_pid() with postmaster.pid?
 
> See the prior discussion in the archives.  We started with that and
> found problems, to which Tom suggested a random number as the best
> solution.

I think Peter's idea is a bit different though.  The previous concern
was about what information would be okay to expose in a pg_ping response
packet, which presumably would be available to anybody who could open a
connection to the postmaster port.  What he's suggesting is to
crosscheck against data that is available after a successful login.
That eliminates the security complaint.

It strikes me we could do something without adding a postmaster-PID
SQL function, too.  What about doing SHOW DATA_DIRECTORY and comparing
that to what pg_regress expects?

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] Regression tests versus the buildfarm environment

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 11:48 -0400, Tom Lane wrote:
> How's that help?  pg_backend_pid isn't going to return the
> postmaster's
> PID ... maybe we could add a new function that does return the
> postmaster's PID, though.

Hmm, is there a portable way to find the parent PID of some other
process, given its PID?


-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 11:42 AM, Peter Eisentraut wrote:

On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote:

One of us is missing something. I didn't say to run the checks using

the

configured port. I had in mind something like:
  port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT)&   0x3FFF);

Oh, I see, modify the DEF_PGPORT don't just use it as-is.  OK, except
that I think something like the above is still pretty risky for the
buildfarm, because you would still have conflicts for assorted
combinations of version numbers and branch_port settings.

How about just this:

  port = 0xC000 | (DEF_PGPORT&  0x3FFF);

The version number was put in there intentionally, for developers who
work on multiple branches at once.  That's the whole reason this code
exists.  Please don't remove it.



Do they run "make check" by hand simultaneously on multiple branches? 
That's the only way you'd get a collision here, I think.


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] Regression tests versus the buildfarm environment

2010-08-11 Thread Kevin Grittner
Peter Eisentraut  wrote:
> On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote:
>> BTW, I don't know why anyone would think that "a random number"
>> would offer any advantage here.  I'd use the postmaster PID,
>> which is guaranteed to be unique across the space that you're
>> worried about.  In fact, you could implement this off the
>> existing postmaster.pid, no need for any new file.  What's
>> lacking is the pg_ping protocol.
> 
> Why not just compare pg_backend_pid() with postmaster.pid?
 
See the prior discussion in the archives.  We started with that and
found problems, to which Tom suggested a random number as the best
solution.  Let's at least start any further discussion informed by
what's gone before; if there was a flaw in the reasoning, please
point that out.
 
-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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Peter Eisentraut  writes:
> On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote:
>> How about just this:
>>  port = 0xC000 | (DEF_PGPORT & 0x3FFF);

> The version number was put in there intentionally, for developers who
> work on multiple branches at once.  That's the whole reason this code
> exists.  Please don't remove it.

I work on multiple branches all day every day.  This wouldn't hinder
me in the slightest, because I use a different DEF_PGPORT for each
branch.  If you don't, it's hard to see how you manage to deal with
multiple branches on one machine ... do you not ever actually install
them?  Even if you don't, changing this would only mean that you
couldn't safely run "make check" concurrently in multiple branches.

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] assertions and constraint triggers

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 10:47 -0400, Tom Lane wrote:
> > I thought the point of ASSERTIONs was that you could write a thing
> such as:
> > CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
> > Enforcing that kind of constraints without true serializability
> seems 
> > impractical.
> 
> Enforcing that kind of constraint seems impractical with or without
> serializability.  You need some optimization method that avoids the
> need
> to do full-table scans after every update, or it's not going to be
> useful for any real-world situation.  Without a scheme that can do
> incremental checking for some useful class of assertion expressions,
> this isn't going to go far.

I'm not sure how great a use case there is for an assertion of the kind
"this table must contain at least 30 million rows".  But I think there
are many uses cases for checks like that on small and rarely changing
tables.


-- 
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] Inconsistent ::bit(N) and get_bit()?

2010-08-11 Thread Robert Haas
2010/8/11 Boszormenyi Zoltan :
> Shouldn't it at least be documented in more depth? Say, get_bit(, N)
> provides the Nth bit (0-based) counting from the leftmost bit?
> I would certainly appreciate a warning spelled out about this
> so if you convert a number to bitstring of length N and you want the
> Mth bit (according to any programming language) then you need to use
> get_bit(..., N-1-M).

The fact that bit-strings subscript from the left rather than from the
right seems pretty odd to me, but it is documented.  I wouldn't object
to adding a note to somewhere around here, if we can think of a
suitable way to word it:

http://www.postgresql.org/docs/9.0/static/functions-bitstring.html

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

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


Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Peter Eisentraut  writes:
> On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote:
>> BTW, I don't know why anyone would think that "a random number" would
>> offer any advantage here.  I'd use the postmaster PID, which is
>> guaranteed to be unique across the space that you're worried about.
>> In fact, you could implement this off the existing postmaster.pid,
>> no need for any new file.  What's lacking is the pg_ping protocol.

> Why not just compare pg_backend_pid() with postmaster.pid?

How's that help?  pg_backend_pid isn't going to return the postmaster's
PID ... maybe we could add a new function that does return the
postmaster's PID, 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] assertions and constraint triggers

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 13:23 +0300, Marko Tiikkaja wrote:
> But you'd have to somehow make the constraints to be checked 
> with true serializability, and that part of the original suggestion 
> seemed to be completely missing.  Not sure how hard that would be
> though.

I don't think somehow running the constraint checks at a different
transaction isolation level than the rest of the transaction is
sensible.  I imagine the solution would look similar to how foreign keys
do it: take a lock on the rows that are required for constraint
satisfaction.  For general assertions, this would require predicate
locking.  But also notice that for the (SELECT count(*) FROM tbl) = N
case, this is the same as a table lock.  I don't think there is any
magic around it.


-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote:
> BTW, I don't know why anyone would think that "a random number" would
> offer any advantage here.  I'd use the postmaster PID, which is
> guaranteed to be unique across the space that you're worried about.
> In fact, you could implement this off the existing postmaster.pid,
> no need for any new file.  What's lacking is the pg_ping protocol.

Why not just compare pg_backend_pid() with postmaster.pid?


-- 
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] Regression tests versus the buildfarm environment

2010-08-11 Thread Peter Eisentraut
On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote:
> > One of us is missing something. I didn't say to run the checks using
> the 
> > configured port. I had in mind something like:
> 
> >  port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) &  0x3FFF);
> 
> Oh, I see, modify the DEF_PGPORT don't just use it as-is.  OK, except
> that I think something like the above is still pretty risky for the
> buildfarm, because you would still have conflicts for assorted
> combinations of version numbers and branch_port settings.
> 
> How about just this:
> 
>  port = 0xC000 | (DEF_PGPORT & 0x3FFF);

The version number was put in there intentionally, for developers who
work on multiple branches at once.  That's the whole reason this code
exists.  Please don't remove it.


-- 
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] MERGE command for inheritance

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> On Wed, Aug 11, 2010 at 11:23 AM, Simon Riggs  wrote:
>> Well, if we go off chasing this particular goose then we will set
>> ourselves back at least one commitfest. I'd rather work towards having a
>> fully committable patch without inheritance sooner than an even bigger
>> patch arriving later in the cycle, which could make things difficult for
>> us.

> Let's give Boxuan a little time to work and see what he comes up with.
>  Maybe it won't be too bad.

I tend to agree with Simon's argument here: if the patch is near
committable then it'd be better to get it committed and work on
correcting this omission afterwards.  I'm not sure about the truth of
the "if" part, 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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 10:23 AM, Robert Haas wrote:



Or we could do something like

 port = 0xC000 ^ (DEF_PGPORT&  0x7FFF);

which is absolutely guaranteed not to conflict with DEF_PGPORT, at the
cost of possibly shifting into the 32K-48K port number range if you
had set DEF_PGPORT above 48K.

I like XOR a lot better than OR.



For years we told people to make sure they picked 4 digit port numbers 
for the buildfarm, and while I removed that note recently it can be put 
back. So I don't think there's much danger - let's got with XOR.



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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> On Wed, Aug 11, 2010 at 10:15 AM, Tom Lane  wrote:
>> Or we could do something like
>> 
>>     port = 0xC000 ^ (DEF_PGPORT & 0x7FFF);
>> 
>> which is absolutely guaranteed not to conflict with DEF_PGPORT, at the
>> cost of possibly shifting into the 32K-48K port number range if you
>> had set DEF_PGPORT above 48K.

> I like XOR a lot better than OR.

Yeah, on reflection that seems better.  Barring objection I will see
about making this happen in all live branches.

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] "micro bucket sort" ...

2010-08-11 Thread Tom Lane
Alvaro Herrera  writes:
> Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 
> 2010:
>> test=# explain analyze select * from t_test order by x, y limit 20;

> But if you put the limit in a subquery which is ordered by the
> known-indexed condition, it is very fast:

> alvherre=# explain analyze select * from (select * from t_test order by x 
> limit 20) f order by x, y;

That's not guaranteed to give you the right 20 rows, though.  Consider
the case where there are > 20 rows having the minimal x value.

regards, tom lane

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


Re: [HACKERS] MERGE command for inheritance

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 11:23 AM, Simon Riggs  wrote:
> Well, if we go off chasing this particular goose then we will set
> ourselves back at least one commitfest. I'd rather work towards having a
> fully committable patch without inheritance sooner than an even bigger
> patch arriving later in the cycle, which could make things difficult for
> us.

Let's give Boxuan a little time to work and see what he comes up with.
 Maybe it won't be too bad.

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

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


Re: [HACKERS] "micro bucket sort" ...

2010-08-11 Thread Alvaro Herrera
Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010:

> same with limit ...
> 
> 
> test=# explain analyze select * from t_test order by x, y limit 20;

But if you put the limit in a subquery which is ordered by the
known-indexed condition, it is very fast:

alvherre=# explain analyze select * from (select * from t_test order by x limit 
20) f order by x, y;
   QUERY PLAN   
 
─
 Sort  (cost=1.24..1.29 rows=20 width=8) (actual time=0.252..0.296 rows=20 
loops=1)
   Sort Key: t_test.x, t_test.y
   Sort Method:  quicksort  Memory: 26kB
   ->  Limit  (cost=0.00..0.61 rows=20 width=8) (actual time=0.051..0.181 
rows=20 loops=1)
 ->  Index Scan using idx_a on t_test  (cost=0.00..30408.36 
rows=100 width=8) (actual time=0.046..0.098 rows=20 loops=1)
 Total runtime: 0.425 ms
(6 filas)


I guess it boils down to being able to sort a smaller result set.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
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] MERGE command for inheritance

2010-08-11 Thread Simon Riggs
On Wed, 2010-08-11 at 11:03 -0400, Tom Lane wrote:
> Simon Riggs  writes:
> > On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote:
> >> I concur that Boxuan's suggested "difficult" approach seems like the 
> >> right one. 
> 
> > Right, but you've completely ignored my proposal: lets do this in two
> > pieces. Get what we have now ready to commit, then add support for
> > partitioning later, as a second project.
> 
> Do we really think this is anywhere near committable now?
> 
> If it's committable in every other respect, I could see just having it
> throw a NOT_IMPLEMENTED error when the target table has children.
> I thought we were still a very long way from that though.

Well, if we go off chasing this particular goose then we will set
ourselves back at least one commitfest. I'd rather work towards having a
fully committable patch without inheritance sooner than an even bigger
patch arriving later in the cycle, which could make things difficult for
us.

I cite recent big patch experience as admissible evidence, m'lord.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


-- 
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] MERGE Specification

2010-08-11 Thread Peter Eisentraut
On fre, 2010-08-06 at 08:12 +0100, Simon Riggs wrote:
> Given that Peter is now attending SQL Standards meetings, I would
> suggest we leave out my suggestion above, for now. We have time to
> raise this at standards meetings and influence the outcome and then
> follow later.

I'm not actually attending any (further) meetings, because no one has
agreed to fund it 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] MERGE command for inheritance

2010-08-11 Thread Tom Lane
Simon Riggs  writes:
> On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote:
>> I concur that Boxuan's suggested "difficult" approach seems like the 
>> right one. 

> Right, but you've completely ignored my proposal: lets do this in two
> pieces. Get what we have now ready to commit, then add support for
> partitioning later, as a second project.

Do we really think this is anywhere near committable now?

If it's committable in every other respect, I could see just having it
throw a NOT_IMPLEMENTED error when the target table has children.
I thought we were still a very long way from that 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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Vik Reykja  writes:
> We just put in the possibility to name the client connections.  Would it be
> interesting to be able to name the server installation itself?

Wouldn't do anything for this problem --- it would just introduce
something else the buildfarm would have to worry about uniqueness of.

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] Bug / shortcoming in has_*_privilege

2010-08-11 Thread Tom Lane
Robert Haas  writes:
> On Wed, Aug 11, 2010 at 8:51 AM, Simon Riggs  wrote:
>> It clearly rates higher in importance than most of the things on the
>> open items list of late...

> First, I don't think that's true.  WALreceiver crashing on AIX, the
> backup procedure in the manual possibly being wrong, and the
> documentation failing to be installed sometimes all seem like they are
> clearly more serious issues than this.  I am sort of wondering why no
> one is working on those issues; apparently, nobody other than me minds
> if it takes another three months to get 9.0 out the door.

Quite.  At this point, the only things that should be on the open items
list are things that would be release stoppers, which is to say things
that are regressions from prior releases or design errors that we don't
want to ever get into a release.  This item is not a bug but a feature
omission, and one of rather long standing.

> Frankly, I
> think the ExplainOnePlan bit is more important, too, although I'm
> starting to think we should fix that for 9.1 rather than 9.0.

See above.  We are not changing that in 9.0 anymore.

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] MERGE command for inheritance

2010-08-11 Thread Heikki Linnakangas

On 11/08/10 17:45, Simon Riggs wrote:

It seems clear that your work in this area will interfere with the work
on partitioning and insert routing.


Nothing concrete has come out of that work yet. And we should have MERGE 
work with inherited tables, regardless of any future work that may 
happen with partitioning.



We've seen it time and time again
that big projects that aim to deliver towards end of a release cycle
interfere with dev of other projects and leave loose ends from
unforeseen interactions. There's no need for that.


I don't understand what you're saying, we're not in the end of a release 
cycle.


--
  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] string_to_array with an empty input string

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 10:32 AM, Tom Lane  wrote:
> Greg Stark  writes:
>> Ideally you really want string_to_array(array_to_string(x, ':'),':')
>> to return x. There's no safe return value to pick for the cases where
>> x=[''] and x=[] that will make this work.
>
> It's easy to see that string_to_array/array_to_string are *not* usable
> as general-purpose serialize/deserialize operations, so sweating over
> corner cases like this one seems a bit pointless.  The design center
> for 'em seems to be array elements that are numbers, so there's no
> issue with empty strings and no great difficulty in picking delimiters
> and null representations that can't appear in the data.  I think they're
> essentially worthless for arrays of text.

array_to_string() is quite useful for arrays of text; I use it to
generate human-readable output, by setting the delimiter to ', '.

Whether string_to_array() is useful is another matter.  It probably is
in some cases, but putting parsing logic into the database layer tends
to be a bit klunky, unless you know from context that you needn't
worry about the error cases.

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

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


Re: [HACKERS] assertions and constraint triggers

2010-08-11 Thread Tom Lane
Marko Tiikkaja  writes:
> On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote:
>> Thinking about SQL assertions (check constraints that are independent of
>> one particular table), do you think it would be reasonable to implement
>> those on top of constraint triggers?  On creation you'd hook up a
>> trigger to each of the affected tables.  And the trigger function runs
>> the respective check expression.  Conceptually, this doesn't seem to be
>> very far away from foreign key constraints after all.

> I thought the point of ASSERTIONs was that you could write a thing such as:
> CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
> Enforcing that kind of constraints without true serializability seems 
> impractical.

Enforcing that kind of constraint seems impractical with or without
serializability.  You need some optimization method that avoids the need
to do full-table scans after every update, or it's not going to be
useful for any real-world situation.  Without a scheme that can do
incremental checking for some useful class of assertion expressions,
this isn't going to go far.

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] [ADMIN] postgres 9.0 crash when bringing up hot standby

2010-08-11 Thread Alanoly Andrews
Ok..in response to the questions from Heikki,

1. Yes, "contrib/dblink" does work. Here's the output from the command used to 
"make" dblink:
  postgres:thimar> /usr/bin/gmake -C contrib/dblink install
  gmake: Entering directory 
`/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink'
  /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/lib'
  /bin/sh ../../config/install-sh -c -d 
'/dinabkp/faouzis/local2/pgsql/share/contrib'
  /bin/sh ../../config/install-sh -c -m 755  dblink.so 
'/dinabkp/faouzis/local2/pgsql/lib/dblink.so'
  /bin/sh ../../config/install-sh -c -m 644 ./uninstall_dblink.sql 
'/dinabkp/faouzis/local2/pgsql/share/contrib'
  /bin/sh ../../config/install-sh -c -m 644 dblink.sql 
'/dinabkp/faouzis/local2/pgsql/share/contrib'
  gmake: Leaving directory 
`/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink'

2. I don't have records of the build logs for the regular postgres executables 
(which contains the libpqwalreceiver) but can do a new compile/make if that is 
required. But they were compiled and installed using the regular make files 
supplied along with the postgres source code. The following flags were added 
during the compilation:

   --without-readline --without-zlib--enable-debug --enable-cassert 
--enable-thread-safety

Thanks.

Alanoly.

-Original Message-
From: Robert Haas [mailto:robertmh...@gmail.com] 
Sent: Wednesday, August 11, 2010 10:13 AM
To: Heikki Linnakangas
Cc: Alanoly Andrews; pgsql-ad...@postgresql.org; PostgreSQL-development
Subject: Re: [HACKERS] [ADMIN] postgres 9.0 crash when bringing up hot standby

On Fri, Aug 6, 2010 at 3:53 PM, Heikki Linnakangas
 wrote:
> So, loading libpqwalreceiver library crashes. It looks like it might be
> pthread-related. Perhaps something wrong with our makefiles, causing
> libpqwalreceiver to be built with wrong flags? Does contrib/dblink work? If
> you look at the build log, what is the command line used to compile
> libpqwalreceiver, and what is the command line used to build other
> libraries, like contrib/dblink?

I haven't seen any response to this from the OP, but it seems
worrisome.  Has anyone else tested a Hot Standby configuraration -
successfully or otherwise - on AIX?

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

This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.
 
Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.



-- 
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] MERGE command for inheritance

2010-08-11 Thread Simon Riggs
On Wed, 2010-08-11 at 22:09 +0800, Boxuan Zhai wrote:
 
> One more thing I want to point out is that, the INSERT is also an
> inheritable action in MERGE. For a plain INSERT command, all the
> inserted tuples are put in the target table ONLY. It is easy to
> understand. We don't want to duplicate all the new tuples in all
> children tables. However, in MERGE command, an INSERT action is
> activated by the tuples fitting its matching conditions. The main plan
> of a MERGE command will scan all the tuples in target relation and its
> children tables. If one tuple in a child table meets the
> requirements of INSERT actions, the insertion should be taken on the
> child table itself rather than its ancestor.  

It seems clear that your work in this area will interfere with the work
on partitioning and insert routing. We've seen it time and time again
that big projects that aim to deliver towards end of a release cycle
interfere with dev of other projects and leave loose ends from
unforeseen interactions. There's no need for that.

> PS: Since I have taken this project, I will do my best to make it
> perfect. I will keep working on MERGE until it is really finished,
> even after the gSoC. (unless you guys has other plans).

You can make things perfect in more than one phase, as indeed you
already are: concurrent locking has already been placed out of scope of
your current work.

I don't question your good intentions to both complete this work and do
it on time. I question the need for us to rely on that. I also question
the ability of the community to deliver super-size features in a single
release. Breaking things down is always the best way.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


-- 
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] string_to_array with an empty input string

2010-08-11 Thread Tom Lane
Greg Stark  writes:
> There's already been one rather-long thread on this topic.
> http://thread.gmane.org/gmane.comp.db.postgresql.general/121450

> In there I argue for the empty array interpretation and Tom goes back
> and forth a few times. I'm not sure where that thread ended though.

I had forgotten that discussion.  It looks like we trailed off without
any real consensus: there was about equal sentiment for an array with
zero elements and an array with one empty-string element.  We ended
up leaving it alone because (a) that wouldn't break anything and (b)
you could use COALESCE() to substitute whichever behavior your
application needed for the case.

So maybe we need to revisit the issue.  Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so.  Is anybody still excited about the alternatives?

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] RecordTransactionCommit() and SharedInvalidationMessages

2010-08-11 Thread Heikki Linnakangas

On 11/08/10 16:46, Robert Haas wrote:

On Wed, Aug 11, 2010 at 1:17 AM, Fujii Masao  wrote:

On Tue, Aug 10, 2010 at 9:30 AM, Robert Haas  wrote:

It appears to me that RecordTransactionCommit() only needs to WAL-log
shared invalidation messages when wal_level is hot_standby, but I
don't see a guard to prevent it from doing it in all cases.


Perhaps right. During not hot standby, there is no backend which the
startup process should send invalidation message to in the standby.
So, ISTM we don't need to log invalidation message when wal_level is
not hot_standby.


The fix looks pretty simple (see attached), although I don't have any
clear idea how to test it.


Should use XLogStandbyInfoActive() macro, for the sake of consistency.


I guess the question is whether we should
back-patch this to 9.0.  It isn't technically necessary for
correctness, but the whole point of introducing the wal_level GUC was
to insulate people not running Hot Standby from possible bugs in the
Hot Standby code, as well as to avoid unnecessary WAL bloat, so on
balance I'm inclined to think we should go ahead and back-patch it.


+1 for backpatching. Keeping the branches closer to each other makes 
backporting any future fixes easier too.


--
  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] string_to_array with an empty input string

2010-08-11 Thread Tom Lane
Greg Stark  writes:
> Ideally you really want string_to_array(array_to_string(x, ':'),':')
> to return x. There's no safe return value to pick for the cases where
> x=[''] and x=[] that will make this work.

It's easy to see that string_to_array/array_to_string are *not* usable
as general-purpose serialize/deserialize operations, so sweating over
corner cases like this one seems a bit pointless.  The design center
for 'em seems to be array elements that are numbers, so there's no
issue with empty strings and no great difficulty in picking delimiters
and null representations that can't appear in the data.  I think they're
essentially worthless for arrays of text.

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] assertions and constraint triggers

2010-08-11 Thread Kevin Grittner
Marko Tiikkaja  wrote:
> On 8/11/10 1:18 PM +0300, Peter Eisentraut wrote:
>> On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote:
>>> Enforcing that kind of constraints without true serializability
>>> seems impractical.
>>
>> Yes, but that is being worked on, I understand.
> 
> Correct.  But you'd have to somehow make the constraints to be
> checked with true serializability, and that part of the original
> suggestion seemed to be completely missing.  Not sure how hard
> that would be though.
 
I keep bumping into use cases where cool things could be done if you
could be sure that *all* transactions were being run at the fully
serializable transaction isolation level.  Perhaps we could look at
a GUC (or initdb option, if people fear the consequences of changes
in an existing database) which not only defaults to serializable,
but silently ignores requests for other levels.  If we only allowed
these constraints to be used in a database which was configured this
way, they would work fine.
 
Enforcing *part* of a transaction under full serializable isolation
seems totally infeasible, unless someone has a clever idea I'm
missing.
 
-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] [ADMIN] postgres 9.0 crash when bringing up hot standby

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 10:20 AM, Alanoly Andrews  wrote:
> Ok..in response to the questions from Heikki,
>
> 1. Yes, "contrib/dblink" does work. Here's the output from the command used 
> to "make" dblink:
>      postgres:thimar> /usr/bin/gmake -C contrib/dblink install
>      gmake: Entering directory 
> `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink'
>      /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/lib'
>      /bin/sh ../../config/install-sh -c -d 
> '/dinabkp/faouzis/local2/pgsql/share/contrib'
>      /bin/sh ../../config/install-sh -c -m 755  dblink.so 
> '/dinabkp/faouzis/local2/pgsql/lib/dblink.so'
>      /bin/sh ../../config/install-sh -c -m 644 ./uninstall_dblink.sql 
> '/dinabkp/faouzis/local2/pgsql/share/contrib'
>      /bin/sh ../../config/install-sh -c -m 644 dblink.sql 
> '/dinabkp/faouzis/local2/pgsql/share/contrib'
>      gmake: Leaving directory 
> `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink'

Unfortunately that only shows the install, not the link - it must have
been built earlier.  Can you do "make clean" in that just that one
directory, and then "make install" again?

> 2. I don't have records of the build logs for the regular postgres 
> executables (which contains the libpqwalreceiver) but can do a new 
> compile/make if that is required. But they were compiled and installed using 
> the regular make files supplied along with the postgres source code. The 
> following flags were added during the compilation:
>
>   --without-readline --without-zlib    --enable-debug --enable-cassert 
> --enable-thread-safety

It'd be nice to see the whole build log, if it's not too much trouble
to regenerate it.

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

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


Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 10:15 AM, Tom Lane  wrote:
> Andrew Dunstan  writes:
>> On 08/11/2010 09:43 AM, Tom Lane wrote:
>>> Andrew Dunstan  writes:
 Why not just add the configured port (DEF_PGPORT) into the calculation
 of the port to run on?
>
>>> No, that would be just about the worst possible choice.  It'd be
>>> guaranteed to fail in the standard scenario that you are running
>>> "make check" before updating an existing installation.
>
>> One of us is missing something. I didn't say to run the checks using the
>> configured port. I had in mind something like:
>
>>      port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) &  0x3FFF);
>
> Oh, I see, modify the DEF_PGPORT don't just use it as-is.  OK, except
> that I think something like the above is still pretty risky for the
> buildfarm, because you would still have conflicts for assorted
> combinations of version numbers and branch_port settings.
>
> How about just this:
>
>     port = 0xC000 | (DEF_PGPORT & 0x3FFF);
>
> If anyone was actually using a DEF_PGPORT above 0xC000, this would mean
> that they couldn't run "make check" on the same machine as their running
> installation (at least not without adjusting pg_regress's port choice,
> which I still think we need to tweak the makefiles to make easier).
> But for ordinary buildfarm usage, this would be guaranteed not to
> conflict as long as you'd chosen nonconflicting branch_ports for all
> your branches and animals.
>
> Or we could do something like
>
>     port = 0xC000 ^ (DEF_PGPORT & 0x7FFF);
>
> which is absolutely guaranteed not to conflict with DEF_PGPORT, at the
> cost of possibly shifting into the 32K-48K port number range if you
> had set DEF_PGPORT above 48K.

I like XOR a lot better than OR.

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

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


Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Andrew Dunstan  writes:
> On 08/11/2010 09:43 AM, Tom Lane wrote:
>> Andrew Dunstan  writes:
>>> Why not just add the configured port (DEF_PGPORT) into the calculation
>>> of the port to run on?

>> No, that would be just about the worst possible choice.  It'd be
>> guaranteed to fail in the standard scenario that you are running
>> "make check" before updating an existing installation.

> One of us is missing something. I didn't say to run the checks using the 
> configured port. I had in mind something like:

>  port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) &  0x3FFF);

Oh, I see, modify the DEF_PGPORT don't just use it as-is.  OK, except
that I think something like the above is still pretty risky for the
buildfarm, because you would still have conflicts for assorted
combinations of version numbers and branch_port settings.

How about just this:

 port = 0xC000 | (DEF_PGPORT & 0x3FFF);

If anyone was actually using a DEF_PGPORT above 0xC000, this would mean
that they couldn't run "make check" on the same machine as their running
installation (at least not without adjusting pg_regress's port choice,
which I still think we need to tweak the makefiles to make easier).
But for ordinary buildfarm usage, this would be guaranteed not to
conflict as long as you'd chosen nonconflicting branch_ports for all
your branches and animals.

Or we could do something like

 port = 0xC000 ^ (DEF_PGPORT & 0x7FFF);

which is absolutely guaranteed not to conflict with DEF_PGPORT, at the
cost of possibly shifting into the 32K-48K port number range if you
had set DEF_PGPORT above 48K.

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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> BTW, I don't know why anyone would think that "a random number"
>> would offer any advantage here.  I'd use the postmaster PID, which
>> is guaranteed to be unique across the space that you're worried
>> about.
 
> Well, in the post I cited, it was you who argued that the PID was a
> bad choice, suggested a random number, and stated "That would have a
> substantially lower collision probability than PID, if the number
> generation process were well designed; and it wouldn't risk exposing
> anything sensitive in the ping response."

Hmm. I don't remember why we'd think that the postmaster PID was
sensitive information ... but if you take that as true, then yeah
it couldn't be included in a pg_ping response.

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] MERGE command for inheritance

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 10:09 AM, Boxuan Zhai  wrote:
> PS: Since I have taken this project, I will do my best to make it perfect.
> I will keep working on MERGE until it is really finished, even after the
> gSoC. (unless you guys has other plans).

That is great to hear!

FWIW, I agree with Heikki that we should try to have the inheritance
stuff working properly in the first version.

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

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


Re: [HACKERS] [ADMIN] postgres 9.0 crash when bringing up hot standby

2010-08-11 Thread Robert Haas
On Fri, Aug 6, 2010 at 3:53 PM, Heikki Linnakangas
 wrote:
> So, loading libpqwalreceiver library crashes. It looks like it might be
> pthread-related. Perhaps something wrong with our makefiles, causing
> libpqwalreceiver to be built with wrong flags? Does contrib/dblink work? If
> you look at the build log, what is the command line used to compile
> libpqwalreceiver, and what is the command line used to build other
> libraries, like contrib/dblink?

I haven't seen any response to this from the OP, but it seems
worrisome.  Has anyone else tested a Hot Standby configuraration -
successfully or otherwise - on AIX?

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

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


Re: [HACKERS] MERGE command for inheritance

2010-08-11 Thread Boxuan Zhai
On Wed, Aug 11, 2010 at 4:45 PM, Simon Riggs  wrote:

>  On Tue, 2010-08-10 at 17:15 +0300, Heikki Linnakangas wrote:
> > On 10/08/10 12:38, Boxuan Zhai wrote:
> > > The difficult way is to generate the plans for children table in
> planner, as
> > > the other commands like UPDATE and DELETE. However, because the
> structure of
> > > MERGE plan is much more complex than the ordinary ModifyTable plans,
> this
> > > job may not as simple as we expected. We need to adjust both the main
> plan
> > > and the
> > > merge actions to fit the children tables, which is not straight
> forward.
> >
> > This the approach you'll have to take. But actually, I'm surprised it
> > doesn't happen to just work already. It should be opaque to the merge
> > facility that the reference to the parent target table has inherited
> > child tables - expanding the inherited table to scans of all the
> > children should already be handled by the planner.
>
> The support for UPDATE and SELECT of partitioned cases is very different
> in the planner and was handled as separate implementation projects.
>
> If we want a working MERGE in the next release, I suggest that we break
> down this project in the same way and look at partitioned target tables
> as a separate project.
>
> One reason for suggesting this is that all MERGE statements have a
> source table, whereas UPDATE and DELETEs did not always. The plan for a
> simple UPDATE and DELETE against a partitioned table is simple, but the
> plan (and performance) of a joined UPDATE or DELETE is not good:
>
> postgres=# explain update p set col2 = x.col2 from x where x.col1 =
> p.col1;
>QUERY
> PLAN
> ---
>  Update  (cost=299.56..1961.18 rows=68694 width=20)
>   ->  Merge Join  (cost=299.56..653.73 rows=22898 width=20)
> Merge Cond: (public.p.col1 = x.col1)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=10)
>   Sort Key: public.p.col1
>   ->  Seq Scan on p  (cost=0.00..31.40 rows=2140 width=10)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=14)
>   Sort Key: x.col1
>   ->  Seq Scan on x  (cost=0.00..31.40 rows=2140 width=14)
>   ->  Merge Join  (cost=299.56..653.73 rows=22898 width=20)
> Merge Cond: (public.p.col1 = x.col1)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=10)
>   Sort Key: public.p.col1
>   ->  Seq Scan on p1 p  (cost=0.00..31.40 rows=2140
> width=10)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=14)
>   Sort Key: x.col1
>   ->  Seq Scan on x  (cost=0.00..31.40 rows=2140 width=14)
>   ->  Merge Join  (cost=299.56..653.73 rows=22898 width=20)
> Merge Cond: (public.p.col1 = x.col1)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=10)
>   Sort Key: public.p.col1
>   ->  Seq Scan on p2 p  (cost=0.00..31.40 rows=2140
> width=10)
> ->  Sort  (cost=149.78..155.13 rows=2140 width=14)
>   Sort Key: x.col1
>   ->  Seq Scan on x  (cost=0.00..31.40 rows=2140 width=14)
>
> Those plans could use some love and attention before forcing Boxuan to
> implement that.
>
>

It seems that we have not decided whether to put the inheritance for MERGE
off for a latter implementation. But, I think we can discuss how to do it
now.

 First of all, the inheritance of MERGE should not be implemented in the
rule-like way. I agree that the easy way I proposed is not consistent with
the general inheritance process in postgres.

The normal way of doing this is to handle it in planner, to be more
specific, we need to extend the function "inheritance_planner()" for
processing MERGE queries.

For UPDATE and DELETE commands (INSERT is not an inheritable command), if
"inheritance_planner" finds that the target table has children tables, it
will generate a list of queries. These queries are almost the same as the
original query input by user, except for the different target
relations. Each child table has it corresponding query in this list.

This list of queries will then be processed by "grouping_planner()" and
transformed into a list of plans. One most important work finished in
this function is to extend the target list of target relations to make sure
that all attributes of a target relation appears in the final result tuple
of its plan.

As for MERGE command, we need to do the same thing. But, since the main
query body is a LEFT JOIN query between source table and target table, the
top-level target list is a combination of all the attributes from source
table and target table. Thus, when we extend the target list, we should only
extent the part of target relations, and keep the source table part
untouched.

Once a main query in this style has been transformed to plan, we need to
prepare the merge actions for it too. That is, extend the target list of all
UPDATE and INSERT actions for the

Re: [HACKERS] Regression tests versus the buildfarm environment

2010-08-11 Thread Kevin Grittner
Tom Lane  wrote:
 
> BTW, I don't know why anyone would think that "a random number"
> would offer any advantage here.  I'd use the postmaster PID, which
> is guaranteed to be unique across the space that you're worried
> about.
 
Well, in the post I cited, it was you who argued that the PID was a
bad choice, suggested a random number, and stated "That would have a
substantially lower collision probability than PID, if the number
generation process were well designed; and it wouldn't risk exposing
anything sensitive in the ping response."
 
-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] Regression tests versus the buildfarm environment

2010-08-11 Thread Andrew Dunstan



On 08/11/2010 09:43 AM, Tom Lane wrote:

Andrew Dunstan  writes:

On 08/11/2010 12:42 AM, Tom Lane wrote:

...  However, it does seem like we ought to be able to
do something about two buildfarm critters defaulting to the same choice
of port number.

Why not just add the configured port (DEF_PGPORT) into the calculation
of the port to run on?

No, that would be just about the worst possible choice.  It'd be
guaranteed to fail in the standard scenario that you are running
"make check" before updating an existing installation.


One of us is missing something. I didn't say to run the checks using the 
configured port. I had in mind something like:


port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) &  0x3FFF);


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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> A look at the code shows that it is merely trying to run psql, and
>> if psql reports that it can connect to the specified port, then
>> pg_regress thinks the postmaster started OK.  Of course, psql was
>> really reporting that it could connect to the other instance's
>> postmaster.
 
> Clearly picking unique ports for `make check` is the ultimate
> solution, but I'm curious whether this would have been caught sooner
> with less effort if the pg_ctl TODO titled "Have the postmaster
> write a random number to a file on startup that pg_ctl checks
> against the contents of a pg_ping response on its initial connection
> (without login)" had been implemented.

It would certainly make the failure more transparent.  As I mentioned,
there are previous buildfarm failures that look like they might be
caused by a similar conflict, but it's seldom possible to be sure.
A cross-check like that would be much safer.

BTW, I don't know why anyone would think that "a random number" would
offer any advantage here.  I'd use the postmaster PID, which is
guaranteed to be unique across the space that you're worried about.
In fact, you could implement this off the existing postmaster.pid,
no need for any new file.  What's lacking is the pg_ping protocol.

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] Inconsistent ::bit(N) and get_bit()?

2010-08-11 Thread Boszormenyi Zoltan
Hi,

I just came across the following confusing thing.

zozo=# create table bit_test(i integer);
CREATE TABLE
zozo=# insert into bit_test values (1), (2), (3);
INSERT 0 3
zozo=# select i, i::bit(2), get_bit(i::bit(2), 1) as bit1,
get_bit(i::bit(2), 0) as bit0 from bit_test;
 i | i  | bit1 | bit0
---++--+--
 1 | 01 |1 |0
 2 | 10 |0 |1
 3 | 11 |1 |1
(3 rows)

So, conversion from int to bitstring creates a readable bitstring, i.e.
the least significant bit is the rightmost one. But get_bit() on the same
bit string works in the opposite order. The only description about get_bit
I found in the 9.0beta docs are in
http://www.postgresql.org/docs/9.0/static/functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER

-
FunctionReturn Type Description Example Result


||
|get_bit|(string, offset)   int Extract bit from string
get_bit(E'Th\\000omas'::bytea, 45)  1

-
||
and in
http://www.postgresql.org/docs/9.0/static/functions-bitstring.html

-
||The following functions work on bit strings as well as binary strings:
|get_bit|, |set_bit|.
-

Shouldn't it at least be documented in more depth? Say, get_bit(, N)
provides the Nth bit (0-based) counting from the leftmost bit?
I would certainly appreciate a warning spelled out about this
so if you convert a number to bitstring of length N and you want the
Mth bit (according to any programming language) then you need to use
get_bit(..., N-1-M).

Best regards,
Zoltán Böszörményi


-- 
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] RecordTransactionCommit() and SharedInvalidationMessages

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 1:17 AM, Fujii Masao  wrote:
> On Tue, Aug 10, 2010 at 9:30 AM, Robert Haas  wrote:
>> It appears to me that RecordTransactionCommit() only needs to WAL-log
>> shared invalidation messages when wal_level is hot_standby, but I
>> don't see a guard to prevent it from doing it in all cases.
>
> Perhaps right. During not hot standby, there is no backend which the
> startup process should send invalidation message to in the standby.
> So, ISTM we don't need to log invalidation message when wal_level is
> not hot_standby.

The fix looks pretty simple (see attached), although I don't have any
clear idea how to test it.  I guess the question is whether we should
back-patch this to 9.0.  It isn't technically necessary for
correctness, but the whole point of introducing the wal_level GUC was
to insulate people not running Hot Standby from possible bugs in the
Hot Standby code, as well as to avoid unnecessary WAL bloat, so on
balance I'm inclined to think we should go ahead and back-patch it.

Other opinions?

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


record_transaction_commmit.patch
Description: Binary 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] Regression tests versus the buildfarm environment

2010-08-11 Thread Kevin Grittner
Tom Lane  wrote:
 
> A look at the code shows that it is merely trying to run psql, and
> if psql reports that it can connect to the specified port, then
> pg_regress thinks the postmaster started OK.  Of course, psql was
> really reporting that it could connect to the other instance's
> postmaster.
 
Clearly picking unique ports for `make check` is the ultimate
solution, but I'm curious whether this would have been caught sooner
with less effort if the pg_ctl TODO titled "Have the postmaster
write a random number to a file on startup that pg_ctl checks
against the contents of a pg_ping response on its initial connection
(without login)" had been implemented.
 
http://archives.postgresql.org/pgsql-bugs/2009-10/msg00110.php
 
It sounds like it's related; but was curious to confirm.
 
-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] Regression tests versus the buildfarm environment

2010-08-11 Thread Tom Lane
Andrew Dunstan  writes:
> On 08/11/2010 12:42 AM, Tom Lane wrote:
>> ...  However, it does seem like we ought to be able to
>> do something about two buildfarm critters defaulting to the same choice
>> of port number.

> Why not just add the configured port (DEF_PGPORT) into the calculation 
> of the port to run on?

No, that would be just about the worst possible choice.  It'd be
guaranteed to fail in the standard scenario that you are running
"make check" before updating an existing installation.

I think what we want to do here is arrange for the buildfarm script to
select the same port that it's going to use later for an "installed"
postmaster, but it has to go via a different path than DEF_PGPORT.

The first thought that comes to mind is to adjust the makefiles
like this:

ifdef REGRESSION_TEST_PORT
... add --port $(REGRESSION_TEST_PORT) to pg_regress flags ...
endif

and then the buildfarm script could use

make REGRESSION_TEST_PORT=nnn check

But I'm not sure what the cleanest way is if we have to pass that
down from the top-level makefile.  Make doesn't pass down variables
automatically does it?

Another possibility is to allow a regression test port number to
be configured via configure; though that seems like a slightly
larger change than I'd want to push into the back branches.

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] Develop item from TODO list

2010-08-11 Thread Kevin Grittner
Robert Haas  wrote:
 
> What does the TODO list item mean by "and actions"?
 
Things like ON DELETE CASCADE versus ON DELETE RESTRICT?
 
-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] MERGE command for inheritance

2010-08-11 Thread Simon Riggs
On Wed, 2010-08-11 at 15:53 +0300, Heikki Linnakangas wrote:
> On 11/08/10 14:44, Simon Riggs wrote:
> > On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote:
> >
> >> I concur that Boxuan's suggested "difficult" approach seems like the
> >> right one.
> >
> > Right, but you've completely ignored my proposal: lets do this in two
> > pieces. Get what we have now ready to commit, then add support for
> > partitioning later, as a second project.
> 
> It seems like a pretty serious omission. What would you do, thrown a 
> "MERGE to inherited tables not implemented" error?

It's not a "serious omission" to do work in multiple phases. I have not
proposed that we neglect that work, only that it happens afterwards.
Phasing work often allows the whole to be delivered quicker and it
reduces the risk that we end up with nothing at all or spaghetti code
through rushing things.

We have already split MERGE into two phases from its original scope,
where the majority thought for many years that MERGE without concurrent
locking was unacceptable. Splitting MERGE into 3 phases now is hardly an
earth shaking proposal.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


-- 
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] Develop item from TODO list

2010-08-11 Thread Robert Haas
On Wed, Aug 11, 2010 at 7:47 AM, Viktor Valy  wrote:
> We have chosen another item from the list:
> "Allow ALTER TABLE to change constraint deferrability and actions"

I believe that is not done.  What does the TODO list item mean by "and actions"?

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

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


  1   2   >