Re: [HACKERS] Functional dependencies and GROUP BY

[Alex Hunsaker]

On Fri, Jun 25, 2010 at 14:06, Peter Eisentraut  wrote:
> Second version:

Hi!

Ive looked this over.  Looks great!  I have some nits about the
documentation and comments ( non issues like referencing primary keys
when it really means not null unique indexes :-P ), but on the whole
it works and looks good.

The only corner case I have run into is creating a view with what I
would call an implicit 'not null' constraint.  Demonstration below:

create table nn (a int4 not null, b int4, unique (a));
select * from nn group by a; -- should this work? I think not?
a | b
---+---
(0 rows)

create view vv as select a, b from nn group by a;
select * from vv;
 a | b
---+---
(0 rows)

=# alter table nn alter column a drop not null;
=# select * from nn group by a; -- ok, broken makes sense
ERROR:  column "nn.b" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: SELECT * from nn group by a;

=# select * from vv; -- yipes should be broken?
 a | b
---+---
(0 rows)

Im thinking we should not allow the "select * from nn group by a;" to
work.  Thoughts?

(FYI I do plan on doing some performance testing with large columns
later, any other requests?)

-- 
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] SHOW TABLES

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> Why must the backslash commands be more powerful than any alternative
> we might come up with?

Because they encode alot of information in a character- something which
is next to impossible to do in "english".

Consider 'standard' perl vs. perl w/ 'use English;'.  The former is much
more condesned and the latter is much more verbose.  Which would you
want to use on a daily basis and which would you like to have in an
application someone else may have to support some day?  Next question:
how long do you really think you're going to be around? :)

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Fri, Jul 16, 2010 at 1:52 PM, Heikki Linnakangas
 wrote:
> That's for MySQL. I come from a DB2 background, and when I started using
> psql years ago, I often typed "LIST TABLES" without thinking much about it.
> Not SHOW TABLES, but LIST TABLES.
>
> I bet Oracle users coming to PostgreSQL will try "DESC". Not SHOW TABLES. As
> Simon listed, every DBMS out there has a different syntax for this.
>
> I have nothing against SHOW TABLES (it might cause conflicts in grammar
> though), but if we're going to cater to people migrating from MySQL, I feel
> we should cater to people migrating from other products too. But surely
> we're not going to implement 10 different syntaxes for the same thing! We
> could, however, give a hint in the syntax error in all those cases. That way
> we're not on the hook to maintain them forever, and we will be doing people
> a favor by introducing them to the backslash commands or information schema,
> which are more powerful.

One advantage of using LIST is that LIST doesn't already mean
something else, which would simplify the grammar handling.

LIST [SYSTEM | ALL] 
DESCRIBE 

Why must the backslash commands be more powerful than any alternative
we might come up with?

-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Tom Lane]

Andres Freund  writes:
> Just to help me: The primary reasons for using SnapshotNow is speed and in 
> some cases correctness (referential integrity). Right? Any other reasons?

Well, the main point for system catalog accesses is that you *must* have
an up-to-date view of the table schemas.  As an example, if someone just
added an index to an existing table, it would not do for an INSERT to
fail to update that index --- no matter whether it's from a serializable
transaction or not.  So the DDL-executing transaction must hold a lock
that would block any operation that had better be able to see what it
did, and once another transaction has acquired the lock that lets it go
ahead with another operation, it had better see the results of the DDL
transaction.

However that argument mostly applies to what the executor does.  A plan
could still be usable despite having been made against a now-obsolete
version of the table schema.

In the case at hand, I think most constraint-adding situations would
require at least ShareLock, because they had better block execution of
INSERT/UPDATE/DELETE operations that could fail to honor the constraint
if they didn't see it in the catalogs.  But AFAICS, addition of a
constraint need not block SELECT, and it need not invalidate existing
plans.

CREATE INDEX uses ShareLock because it's okay to run multiple CREATE
INDEXes in parallel (thanks to some rather dodgy coding of the catalog
updates).  For other cases of constraint additions, it might not be
practical to run two constraint additions in parallel.  In that case we
could use ShareRowExclusive instead, which is self-exclusive but is not
any stronger than Share from the perspective of DML commands.  Since
it's not, I'm unconvinced that it's worth taking any great pains to try
to make constraint additions run in parallel.

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] SHOW TABLES

[Tim Landscheidt]

"Kevin Grittner"  wrote:

>>>  postgres=# SHOW ME THE MONEY;
>>> WARNING: THE MONEY is deprecated in this version of Postgres and
>>> may be discarded in a future version
>>> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

>> Funny, but no longer true:

>> http://www.postgresql.org/docs/8.4/static/datatype-money.html

>> (although I wish we would get rid of the type)

> I hadn't been aware it was ever deprecated.  It has the advantage
> over numeric of using straight integer arithmetic for addition and
> subtraction, which are by far the most common operations on money,
> while allowing a decimal fraction without rounding problems.  I'd
> been thinking about migrating our money columns to it (subject to
> some benchmarking first, to see how much it actually helped).  It
> would seem odd for a database to tout its ability to deal with such
> data types as geometric shapes and global positioning, etc., which
> then didn't have such a common type as money.  In my experience,
> many business applications deal with money.

One major flaw I see is that the fractional precision is
fixed. Not only petrol stations split cents.

Tim


-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

On Saturday 17 July 2010 01:53:24 Robert Haas wrote:
> On Jul 16, 2010, at 6:01 PM, Tom Lane  wrote:
> > Andres Freund  writes:
> >> What could the join removal path (and similar places) *possibly* do
> >> against such a case? Without stopping to use SnapshotNow I dont see any
> >> way :-(
> > 
> > But the planner, along with most of the rest of the backend, *does* use
> > SnapshotNow when examining the system catalogs.
> > 
> > I share your feeling of discomfort but so far I don't see a hole in
> > Simon's argument. 
Neither do I.

> > Adding a constraint should never make a
> > previously-correct plan incorrect.  Removing one is a very different
> > story, but he says he's not changing that case.  (Disclaimer: I have
> > not read the patch.)
> 
> Perhaps we should start by deciding whether Andres' case is a bug in the
> first place, and then we can argue about whether it's a join-removal bug,
> a lock-weakening bug, or a preexisting bug.
The case where its possible to produce such a case *after* having used/locked 
all participating relations is new I think. 
Being able to create invalid results by doing DDL in another connection on 
not-yet-used tables is at least as old as constraint exclusion. Its a bit 
easier to work around, but thats it.

So I personally would not consider this patch as having a bug anymore 
(thinking helps...).

Whether the general issue is a bug or a to-be-more-exhausitive-documented-
gotcha I have no idea. I know two people having hit it in production - I dont 
think its a that common issue though. Starting with the fact that not that 
many people use serializable.

Just to help me: The primary reasons for using SnapshotNow is speed and in 
some cases correctness (referential integrity). Right? Any other reasons?

Andres


-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Robert Haas]

On Jul 16, 2010, at 6:01 PM, Tom Lane  wrote:
> Andres Freund  writes:
>> What could the join removal path (and similar places) *possibly* do against 
>> such a case? Without stopping to use SnapshotNow I dont see any way :-(
> 
> But the planner, along with most of the rest of the backend, *does* use
> SnapshotNow when examining the system catalogs.
> 
> I share your feeling of discomfort but so far I don't see a hole in
> Simon's argument.  Adding a constraint should never make a
> previously-correct plan incorrect.  Removing one is a very different
> story, but he says he's not changing that case.  (Disclaimer: I have
> not read the patch.)

Perhaps we should start by deciding whether Andres' case is a bug in the first 
place, and then we can argue about whether it's a join-removal bug, a 
lock-weakening bug, or a preexisting bug.

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

Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Tom Lane]

Andres Freund  writes:
> What could the join removal path (and similar places) *possibly* do against 
> such a case? Without stopping to use SnapshotNow I dont see any way :-(

But the planner, along with most of the rest of the backend, *does* use
SnapshotNow when examining the system catalogs.

I share your feeling of discomfort but so far I don't see a hole in
Simon's argument.  Adding a constraint should never make a
previously-correct plan incorrect.  Removing one is a very different
story, but he says he's not changing that case.  (Disclaimer: I have
not read the patch.)

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] SHOW TABLES

[Dimitri Fontaine]

Le 16 juil. 2010 à 18:42, Kevin Grittner a écrit :
> Like \d, these server-side stored procedures can return a number of
> result sets.  Like Robert, I'm skeptical of implementing a
> server-side solution for PostgreSQL which doesn't do the same.  I'm
> not clear on whether that's even possible without a new version of
> wire protocol, though.

Well, I think we shouldn't mix it all. My view on that is that we need some 
easy simple commands in the backend, none of them on its own would mimic \d.

Consider this psql command: psql -E -c '\d'. What I think is that each query 
you see there could easily become a SHOW subsyntax (from memory, we probably 
would have SHOW TABLE, SHOW INDEXES ON , SHOW TRIGGERS ON , SHOW 
CONSTRAINTS ON , etc).

Now, psql would be free to implement its \d in terms of those new queries 
rather than the full SQL ones it has now, that would be a good first client. Oh 
and that means the design is about all done already. And that we still are in 
the one command - one resultset interface. Meaning any libpq driver knows how 
to deal with the resultset, and that's not parsing text.

I'm all with Simon here, it's not about offering any new capability that we 
don't already have, it's about having it handy from anywhere. So let's just 
have an easy syntax in the backend to do all the catalog 'magic' querying psql 
does, but one query at a time.

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte






-- 
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] log files and permissions

[Tom Lane]

Martin Pihlak  writes:
> Thanks, somehow I missed that we can already specify octal integers
> as GUC-s. I now converted the log_file_mode to integer and dropped
> the assign_log_file_mode function.

Applied with a few corrections.  The noncosmetic changes were:

* prevent Log_file_mode from disabling S_IWUSR permissions --- we had
better be able to write the files no matter what.

* save and restore errno across ereport() call; needed since some
callers look at errno after a failure.

* make unix_socket_permissions print its value in octal, for consistency
  with log_file_mode.

BTW, I'm not 100% convinced that having the octal show-functions is
a good idea, mainly because they aren't consistent with the other
columns in pg_settings:

regression=# select * from pg_settings where name = 'log_file_mode';
 name  | setting | unit |   category   |
short_desc| 
  extra_desc
| co
ntext | vartype | source  | min_val | max_val | enumvals | boot_val | reset_val 
| sourcefile | sourceline 
---+-+--+--+
--+-

+---
--+-+-+-+-+--+--+---
++
 log_file_mode | 0600|  | Reporting and Logging / Where to Log | Sets th
e file permissions for log files. | The parameter value is expected to be a nume
ric mode specification in the form accepted by the chmod and umask system calls.
 (To use the customary octal format the number must start with a 0 (zero).) | si
ghup  | integer | default | 0   | 511 |  | 384  | 384   
||   
(1 row)

I guess this is not strictly incorrect, as long as you understand what
the leading '0' means per C conventions, but it looks a bit weird.
However, we're not going to be able to improve on this without a lot more
hackery than I think it's worth.

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] SHOW TABLES

[Kevin Grittner]

"Joshua D. Drake"  wrote:
> On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote:
 
>>  postgres=# SHOW ME THE MONEY;
>> WARNING: THE MONEY is deprecated in this version of Postgres and
>> may be discarded in a future version
>> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.
> 
> Funny, but no longer true:
> 
> http://www.postgresql.org/docs/8.4/static/datatype-money.html
> 
> (although I wish we would get rid of the type)
 
I hadn't been aware it was ever deprecated.  It has the advantage
over numeric of using straight integer arithmetic for addition and
subtraction, which are by far the most common operations on money,
while allowing a decimal fraction without rounding problems.  I'd
been thinking about migrating our money columns to it (subject to
some benchmarking first, to see how much it actually helped).  It
would seem odd for a database to tout its ability to deal with such
data types as geometric shapes and global positioning, etc., which
then didn't have such a common type as money.  In my experience,
many business applications deal with money.
 
-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] SHOW TABLES

[Stephen Frost]

* Heikki Linnakangas (heikki.linnakan...@enterprisedb.com) wrote:
> I'm not sure I buy that, but even if it's true, it doesn't seem fair to  
> do a favor to one group of users, leaving the rest stranded and excluded  
> forever. Even if SHOW TABLES has a bigger mind-share than the others,  
> surely the others are not negligible either.

Have to say that I don't believe we're under any obligation to be "fair"
to the users of various other RDBMS'.  I hate MySQL with a passion, and
originally came from an Oracle background, but I have to say that
'show tables;' makes a heck of alot more sense to me than 'desc'.

> I'm suggesting that we should just add the hint for all of those and be  
> done with it.

I do think it'd be useful to have a top-level set of 'show' commands.  I
agree with the others that the approach of saying "well, if you just
query pg_class joined against pg_namespace and filter out what you don't
want", etc, etc, is way more complicated than it really needs to be.  I
can think of some applications where I would have actually used it
(simple perl scripts and the like).

I'm not sure how I feel about something like "select * from (show
tables) where table_name = 'blah';"...

> :-). They're not that bad IMHO. \d is short, which is nice. \d and \df  
> are the commands I routinely use and remember, for anything more  
> advanced I have to resort to \h. The SHOW TABLES command wouldn't do  
> more than that anyway.

I don't find them all that bad either, really.  I do find myself doing
things like "psql -c '\d';" in scripts and whatnot on occation, which
isn't exactly ideal either. :)

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote:
> On 17 July 2010 07:26, Joshua D. Drake  wrote:
> > Yes. We should provide a single, well described grammar for interacting
> > with objects in the database regardless of client. I should be able to
> > open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
> > fall out.
> 
> postgres=# SHOW ME THE MONEY;
> WARNING: THE MONEY is deprecated in this version of Postgres and may
> be discarded in a future version
> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

Funny, but no longer true:

http://www.postgresql.org/docs/8.4/static/datatype-money.html

(although I wish we would get rid of the type)

JD

> 

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


-- 
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] SHOW TABLES

[Brendan Jurd]

On 17 July 2010 07:26, Joshua D. Drake  wrote:
> Yes. We should provide a single, well described grammar for interacting
> with objects in the database regardless of client. I should be able to
> open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
> fall out.

postgres=# SHOW ME THE MONEY;
WARNING: THE MONEY is deprecated in this version of Postgres and may
be discarded in a future version
HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

-- 
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] SHOW TABLES

[Joshua D. Drake]

On Fri, 2010-07-16 at 19:32 +0100, Simon Riggs wrote:

> That's a very sensible suggestion, we should give a hint for all common
> commands SHOW, LIST, etc., even though we pick just one to implement.
> 
> > That way we're not on the hook to maintain them forever, and we 
> > will be 
> 
> > doing people a favor by introducing them to the backslash 
> > commands
> 
> That's a sentence I never thought to see written down

No kidding. 

We are not helping users by introducing them to \d commands. 

I will repeat what I said at the beginning of this postgres vs.
postgresql thread:

Yes. We should provide a single, well described grammar for interacting
with objects in the database regardless of client. I should be able to
open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
fall out.

The discussions of \ commands and psql are irrelevant to this thread.


Joshua D. Drake


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


-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

On Friday 16 July 2010 22:24:32 Simon Riggs wrote:
> On Fri, 2010-07-16 at 21:38 +0200, Andres Freund wrote:
> > boom
> 
> Your test case would still occur in the case where the first query had
> not been executed against the same table. So the test case illustrates a
> failing of join removal, not of this patch.
Well, yes. Thats a well known (and documented) issue of pg's serialized 
transactions - which you can protect against quite easily (see the trunctate 
docs for example).
The difference is that I know of no sensible way you sensibly could protect 
against such issues with the patch applied while its easy before(LOCK TABLE 
... IN  SHARE MODE for all used tables).
I know of several sites which have *long* running serialized transactions open 
for analysis and I know there have been other cases of it.

Sure its not that bad, but at least it needs to get documented imho. Likely 
others should chime in here ;-)

What could the join removal path (and similar places) *possibly* do against 
such a case? Without stopping to use SnapshotNow I dont see any way :-(


Andres

-- 
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] Performance Enhancement/Fix for Array Utility Functions

[Tom Lane]

Daniel Farina  writes:
> Generally I think the delimited untoasting of metadata from arrays
> separately from the payload is Not A Bad Idea.

I looked at this patch a bit.  I agree that it could be a big win for
large external arrays, but ...

1. As-is, it's a significant *pessimization* for small arrays, because
the heap_tuple_untoast_attr_slice code does a palloc/copy even when one
is not needed because the data is already not toasted.  I think there
needs to be a code path that avoids that.

2. Arrays that are large enough to be pushed out to toast storage are
almost certainly going to get compressed first.  The potential win in
this case is very limited because heap_tuple_untoast_attr_slice will
fetch and decompress the whole thing.  Admittedly this is a limitation
of the existing code and not a fault of the patch proper, but still, if
you want to make something that's generically useful, you need to do
something about that.  Perhaps pglz_decompress() could be extended with
an argument to say "decompress no more than this much" --- although that
would mean adding another test to its inner loop, so we'd need to check
for performance degradation.  I'm also unsure how to predict how much
compressed data needs to be read in to get at least N bytes of
decompressed data.

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] SHOW TABLES

[Tim Landscheidt]

Simon Riggs  wrote:

> [...]
> Light switches are usually at shoulder height next to a door. Our light
> switches are 2 metres up, on the far side of the room. People are sick
> of banging their knees on furniture while trying to grope for the light.
> The light switch isn't so much hard to use, its just in the wrong place.
> We must envisage what it is to be a person that doesn't know where the
> switch is, or have forgotten. We don't need a programmable light switch
> API, or a multi-function light remote control. Just a switch by all of
> the doors.

> (Oh, they're probably not called lights outside UK; room lamps maybe?)

Wow, the British must have shrunk a lot since my last vis-
it - here light switches are mounted not more than 105 cm
from the floor :-) (barrier-free not more than 85 cm).

  I guess the problem shown by others in this thread is that
there doesn't seem to be a "usually" with regard to "\d"
equivalents either.

Tim


-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Simon Riggs]

On Fri, 2010-07-16 at 21:38 +0200, Andres Freund wrote:
> boom

Your test case would still occur in the case where the first query had
not been executed against the same table. So the test case illustrates a
failing of join removal, not of this patch.

-- 
 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

On Friday 16 July 2010 21:15:44 Simon Riggs wrote:
> On Fri, 2010-07-16 at 21:10 +0200, Andres Freund wrote:
> > On Friday 16 July 2010 20:41:44 Andres Freund wrote:
> > > >> ! */
> > > >> !case AT_AddColumn:  /* may
> > > >> rewrite heap, in some cases and visible to SELECT */ !
> > > >> 
> > > >>case AT_DropColumn: /* change
> > > >> 
> > > >> visible to SELECT */ !case
> > > >> AT_AddColumnToView:/* CREATE VIEW */ !   
> > > >> case AT_AlterColumnType:/* must rewrite heap */ !
> > > >> 
> > > >>case AT_DropConstraint: /* as DROP INDEX
> > > >>*/
> > > >> 
> > > >> !case AT_AddOids:
> > > >> !case AT_DropOids:   /*
> > > >> calls AT_DropColumn */ !case
> > > >> AT_EnableAlwaysRule:   /* as DROP INDEX */ !
> > > >> 
> > > >>case AT_EnableReplicaRule:  /* as DROP INDEX
> > > >>*/
> > > >> 
> > > >> !case AT_EnableRule: /* as
> > > >> DROP INDEX */
> > 
> > Another remark:
> > 
> > Imho it would be usefull to keep that list in same order as in the enum -
> > currently its hard to make sure no case is missing.
> 
> Not really; the default case is to reject, so any full test suite will
> pick that up.
> 
> The cases are ordered by resulting lock type, which seemed the best way
> to check we didn't accidentally assign an incorrect lock type.
Well, I meant ordering it correctly inside the locktypes, sorry for the 
inprecision.

Andres

-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

On Friday 16 July 2010 21:12:33 Simon Riggs wrote:
> On Fri, 2010-07-16 at 20:41 +0200, Andres Freund wrote:
> > You argue above that you cant change SET [NOT] NULL to be less
> > restrictive because it might change plans - isnt that true for some of
> > the above cases as well?
> > 
> > For example UNIQUE/PRIMARY might make join removal possible - which could
> > only be valid after "invalid" tuples where deleted earlier in that
> > transaction. Another case which it influences are grouping plans...
> 
> This is only for adding a constraint, not removing it. Join removal
> would be possible after the ALTER finishes, but won't change plans
> already in progress. The idea is to minimise the impact, not maximise
> the benefit of the newly added constraint; I don't think we should block
> all queries just because a few might benefit.
Its not about benefit, its about correctness:

CREATE TABLE testsnap(t int);
INSERT INTO testsnap VALUES(1),(1);


T1:
test=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
Time: 0.853 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 
USING(t);   
   QUERY PLAN
 
-
  Merge Left Join  (cost=337.49..781.49 rows=28800 width=4) (actual 
time=0.090..0.118 rows=4 loops=1)
Merge Cond: (t1.t = t2.t)
->  Sort  (cost=168.75..174.75 rows=2400 width=4) (actual time=0.049..0.051 
rows=2 loops=1)
  Sort Key: t1.t
  Sort Method:  quicksort  Memory: 25kB
  ->  Seq Scan on testsnap t1  (cost=0.00..34.00 rows=2400 width=4) 
(actual time=0.018..0.023 rows=2 loops=1)
->  Sort  (cost=168.75..174.75 rows=2400 width=4) (actual time=0.026..0.033 
rows=3 loops=1)
  Sort Key: t2.t
  Sort Method:  quicksort  Memory: 25kB
  ->  Seq Scan on testsnap t2  (cost=0.00..34.00 rows=2400 width=4) 
(actual time=0.005..0.009 rows=2 loops=1)
  Total runtime: 0.279 ms
 (11 rows)


T2:
test=# DELETE FROM testsnap;
DELETE 2
Time: 1.184 ms
test=# ALTER TABLE testsnap ADD CONSTRAINT t unique(t);
NOTICE:  0: ALTER TABLE / ADD UNIQUE will create implicit index "t" for 
table "testsnap"
LOCATION:  DefineIndex, indexcmds.c:471
ALTER TABLE
Time: 45.639 ms

T1:
Time: 1.948 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 
USING(t);
  QUERY PLAN
 
-
  Seq Scan on testsnap t1  (cost=0.00..1.02 rows=2 width=4) (actual 
time=0.013..0.016 rows=2 loops=1)
  Total runtime: 0.081 ms
 (2 rows)

Time: 2.004 ms
test=#

boom.



Andres

-- 
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] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

[Heikki Linnakangas]

On 16/07/10 11:13, Fujii Masao wrote:

On Thu, Jul 1, 2010 at 1:09 PM, Fujii Masao  wrote:

Thanks for reminding me. I attached the updated patch.


This patch left uncommitted for half a month. No one is interested in
the patch?


Sorry for the lack of interest ;-)


The patch adds the document about the relationship between a restartpoint
and checkpoint_segments parameter.


Thanks, committed with minor editorialization

--
  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] patch: to_string, to_array functions

[Brendan Jurd]

On 17 July 2010 04:52, Pavel Stehule  wrote:
> 2010/7/16 Brendan Jurd :
>> Also, if we're going to make the function non-strict, we need to
>> consider how to respond when the user specifies NULL for the other
>> arguments.  If the field separator is NULL, bearing in mind that NULL
>> can't match any string, I would expect that to_array would return the
>> undivided string as a single array element, and that to_string would
>> throw an error:
>>
>
> ok, it has a sense.
>
> other question is empty string as separator - but I think, it can has
> same behave like string_to_array and array_to_string functions.
>

Agreed.  Those behaviours seem sensible.

>> If the first argument is NULL for either function, I think it would be
>> reasonable to return NULL.  But I could be convinced that we should
>> throw an error in that case too.
>>
>
> I agree - I prefer a NULL
>
> Thank You very much

No worries; I will await a revised patch from you which updates these
behaviours -- please incorporate the doc/comment changes I posted
earlier -- I will then do a further review before handing off to a
committer.

Cheers,
BJ

-- 
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] SHOW TABLES

[Heikki Linnakangas]

On 16/07/10 21:32, Simon Riggs wrote:

On Fri, 2010-07-16 at 20:52 +0300, Heikki Linnakangas wrote:

I have nothing against SHOW TABLES


...but SHOW wins, based on numbers of people expecting that


I'm not sure I buy that, but even if it's true, it doesn't seem fair to 
do a favor to one group of users, leaving the rest stranded and excluded 
forever. Even if SHOW TABLES has a bigger mind-share than the others, 
surely the others are not negligible either.



, but if we're going to cater to people migrating from MySQL, I
feel we should cater to people migrating from other products too. But
surely we're not going to implement 10 different syntaxes for the same
thing! We could, however, give a hint in the syntax error in all those
cases.


That's a very sensible suggestion, we should give a hint for all common
commands SHOW, LIST, etc., even though we pick just one to implement.


I'm suggesting that we should just add the hint for all of those and be 
done with it.



doing people a favor by introducing them to the backslash
commands


That's a sentence I never thought to see written down


:-). They're not that bad IMHO. \d is short, which is nice. \d and \df 
are the commands I routinely use and remember, for anything more 
advanced I have to resort to \h. The SHOW TABLES command wouldn't do 
more than that anyway.



--
  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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Simon Riggs]

On Fri, 2010-07-16 at 21:10 +0200, Andres Freund wrote:
> On Friday 16 July 2010 20:41:44 Andres Freund wrote:
> > >> ! */
> > >> !case AT_AddColumn:  /* may
> > >> rewrite heap, in some cases and visible to SELECT */ !
> > >>case AT_DropColumn: /* change
> > >> visible to SELECT */ !case
> > >> AT_AddColumnToView:/* CREATE VIEW */ !case
> > >> AT_AlterColumnType:/* must rewrite heap */ !
> > >>case AT_DropConstraint: /* as DROP INDEX */
> > >> !case AT_AddOids:
> > >> !case AT_DropOids:   /* calls
> > >> AT_DropColumn */ !case
> > >> AT_EnableAlwaysRule:   /* as DROP INDEX */ !
> > >>case AT_EnableReplicaRule:  /* as DROP INDEX */
> > >> !case AT_EnableRule: /* as DROP
> > >> INDEX */
> Another remark:
> 
> Imho it would be usefull to keep that list in same order as in the enum - 
> currently its hard to make sure no case is missing.

Not really; the default case is to reject, so any full test suite will
pick that up.

The cases are ordered by resulting lock type, which seemed the best way
to check we didn't accidentally assign an incorrect lock type.

-- 
 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Simon Riggs]

On Fri, 2010-07-16 at 20:41 +0200, Andres Freund wrote:

> You argue above that you cant change SET [NOT] NULL to be less
> restrictive because it might change plans - isnt that true for some of the 
> above cases as well?
> 
> For example UNIQUE/PRIMARY might make join removal possible - which could
> only be valid after "invalid" tuples where deleted earlier in that
> transaction. Another case which it influences are grouping plans...

This is only for adding a constraint, not removing it. Join removal
would be possible after the ALTER finishes, but won't change plans
already in progress. The idea is to minimise the impact, not maximise
the benefit of the newly added constraint; I don't think we should block
all queries just because a few might benefit.

> So I think the only case where its actually possible to lower the
> level is CONSTR_EXCLUSION and _FOREIGN.
> The latter might get impossible soon by planner improvements (Peter's
> functional dependencies patch for example).

Same, I don't see why it would block queries.


-- 
 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

On Friday 16 July 2010 20:41:44 Andres Freund wrote:
> >> ! */
> >> !case AT_AddColumn:  /* may
> >> rewrite heap, in some cases and visible to SELECT */ !
> >>case AT_DropColumn: /* change
> >> visible to SELECT */ !case
> >> AT_AddColumnToView:/* CREATE VIEW */ !case
> >> AT_AlterColumnType:/* must rewrite heap */ !
> >>case AT_DropConstraint: /* as DROP INDEX */
> >> !case AT_AddOids:
> >> !case AT_DropOids:   /* calls
> >> AT_DropColumn */ !case
> >> AT_EnableAlwaysRule:   /* as DROP INDEX */ !
> >>case AT_EnableReplicaRule:  /* as DROP INDEX */
> >> !case AT_EnableRule: /* as DROP
> >> INDEX */
Another remark:

Imho it would be usefull to keep that list in same order as in the enum - 
currently its hard to make sure no case is missing.

Andres

-- 
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 (for 9.1) string functions

[Pavel Stehule]

Hello

I have a one idea nonstandard enhancing of sprintf - relatie often job
is a quoting in PostgreSQL. So sprintf should have a special formats
for quoted values. What do you think about

%lq ... literal quoted
%iq ... ident quoted

??

Regards

Pavel

2010/7/13 Pavel Stehule :
> Hello
>
> 2010/7/13 Itagaki Takahiro :
>> 2010/7/13 Pavel Stehule :
>>> so this is actualised patch:
>>> * concat_sql removed
>>> * left, right, reverse and concat are in core
>>> * printf and concat_ws are in contrib
>>> * format show "" as NULL string
>>> * removed an using of wide chars
>>
>> I think function codes in the core (concat, format, left, right,
>> and reverse) are ready for committers. They also have docs, but
>> the names are not listed in Index page (bookindex.html).
>> Please add
>>   
>>    funcname
>>   
>> in func.sgml for each new function.
>>
>
> fixed
>> However, I have a couple of comments to stringfunc module. sprintf()
>> and concat_ws() are not installed by default, but provided by the module.
>>
>>> todo:
>>> NULL handling for printf function
>>
>> I like  for null arguments. It is just same as format() and RAISE.
>
> done
>
>>
>> === Questions ===
>> * concat_ws() transforms NULLs into empty strings.
>> Is it an intended behavior and compatible with MySQL?
>> Note that string_agg() doesn't add separators to NULLs.
>>
>
> no I was  wrong - original concat_ws just ignore NULL - fixed, now
> concat_ws has same behave like original.
>
>>  =# SELECT coalesce(concat_ws(',', 'A', NULL, 'B'), '(null)');
>>   coalesce
>>  --
>>   A,,B
>>  (1 row)
>>
>> * concat_ws() returns NULL when the separator is NULL.
>> Is it an intended behavior and compatible with MySQL?
>>
>>  =# SELECT coalesce(concat_ws(NULL, 'A', NULL, 'B'), '(null)');
>>   coalesce
>>  --
>>   (null)
>>  (1 row)
>>
>> === Trivial issues ===
>> * Some function prototypes are declared but not used.
>>  We can just remove them.
>>  - mb_string_info()
>>  - stringfunc_concat(PG_FUNCTION_ARGS);
>>  - stringfunc_left(PG_FUNCTION_ARGS);
>>  - stringfunc_right(PG_FUNCTION_ARGS);
>>  - stringfunc_reverse(PG_FUNCTION_ARGS);
>>
>> * Some error messages need to be improved.
>>  For example, "1th" is wrong.
>>    =# select sprintf('>>>%*s<<<', NULL, 'abcdef');
>>    ERROR:  null value not allowed
>>    HINT:  width (1th) arguments is NULL
>
> have you a some idea about it?
>
>>
>> * sprintf() has some typos in error messages
>>  For example, "sprinf".
>>
>
> fixed
>
>> --
>> Itagaki Takahiro
>>
>
> Regards
>
> Pavel
>

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

Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Andres Freund]

Hi Simon,

Your patch implements part of a feature I desire greatly - thanks!

Some comments:

On Thursday 15 July 2010 11:24:27 Simon Riggs wrote:
>> ! LOCKMODE
>> ! AlterTableGreatestLockLevel(List *cmds)
>> ! {
>> !ListCell   *lcmd;
>> !LOCKMODE lockmode = ShareUpdateExclusiveLock;  /* default for compiler 
>> */
Actually its not only for the compiler, its necessary for correctness
as you omit the default at least in the AT_AddConstraint case.

>> !
>> !foreach(lcmd, cmds)
>> !{
>> !AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lcmd);
>> !LOCKMODE cmd_lockmode  = AccessExclusiveLock; /* default for 
>> compiler */
>> !
>> !switch (cmd->subtype)
>> !{
>> !/*
>> ! * Need AccessExclusiveLock for these subcommands 
>> because they
>> ! * affect or potentially affect both read and write 
>> operations.
>> ! *
>> ! * New subcommand types should be added here by default.
>> ! */
>> !case AT_AddColumn:  /* may rewrite 
>> heap, in some cases and visible to SELECT */
>> !case AT_DropColumn: /* change 
>> visible to SELECT */
>> !case AT_AddColumnToView:/* CREATE VIEW */
>> !case AT_AlterColumnType:/* must rewrite heap */
>> !case AT_DropConstraint: /* as DROP INDEX */
>> !case AT_AddOids:
>> !case AT_DropOids:   /* calls 
>> AT_DropColumn */
>> !case AT_EnableAlwaysRule:   /* as DROP INDEX */
>> !case AT_EnableReplicaRule:  /* as DROP INDEX */
>> !case AT_EnableRule: /* as DROP 
>> INDEX */
>> !case AT_DisableRule:/* as DROP INDEX */
>> !case AT_ChangeOwner:/* change visible to 
>> SELECT */
>> !case AT_SetTableSpace:  /* must rewrite heap */
>> !case AT_DropNotNull:/* may change some SQL 
>> plans */
>> !case AT_SetNotNull:
>> !cmd_lockmode = AccessExclusiveLock;
>> !break;
>> !
>> !/*
>> ! * These subcommands affect write operations only.
>> ! */
>> !case AT_ColumnDefault:
>> !case AT_ProcessedConstraint:/* becomes 
>> AT_AddConstraint */
>> !case AT_AddConstraintRecurse:   /* becomes 
>> AT_AddConstraint */
>> !case AT_EnableTrig:
>> !case AT_EnableAlwaysTrig:
>> !case AT_EnableReplicaTrig:
>> !case AT_EnableTrigAll:
>> !case AT_EnableTrigUser:
>> !case AT_DisableTrig:
>> !case AT_DisableTrigAll:
>> !case AT_DisableTrigUser:
>> !case AT_AddIndex:   /* from 
>> ADD CONSTRAINT */
>> !cmd_lockmode = ShareRowExclusiveLock;
>> !break;
>> !
>> !case AT_AddConstraint:
>> !if (IsA(cmd->def, Constraint))
>> !{
>> !Constraint *con = (Constraint *) 
>> cmd->def;
>> !
>> !switch (con->contype)
>> !{
>> !case CONSTR_EXCLUSION:
>> !case CONSTR_PRIMARY:
>> !case CONSTR_UNIQUE:
>> !/*
>> ! * Cases essentially 
>> the same as CREATE INDEX. We
>> ! * could reduce the 
>> lock strength to ShareLock if we
>> ! * can work out how to 
>> allow concurrent catalog updates.
>> ! */
>> !cmd_lockmode = 
>> ShareRowExclusiveLock;
>> !break;
>> !case CONSTR_FOREIGN:
>> !/*
>> ! * We add triggers to 
>> both tables when we add a
>> ! * Foreign Key, so the 
>> lock level must be at least
>> ! * as strong as CREATE 
>> TRIGGER.
>> !

Re: [HACKERS] patch: to_string, to_array functions

[Pavel Stehule]

2010/7/16 Brendan Jurd :
> On 17 July 2010 02:15, Pavel Stehule  wrote:
>> 2010/7/16 Brendan Jurd :
>>> Regarding the behaviour of the third argument (null_string), I was a
>>> little surprised by the results when I passed in a NULL.
>>>
>>
>> I didn't thinking about NULL as separator before. Current behave isn't
>> practical. When default separator is empty string, then NULL can be
>> used as ignore NULLs - so it can emulate current string_to_array and
>> array_to_string behave. It can be, because NULL can't be a separator
>> ever.
>>
>> select to_string(array[1,2,3,null,5], ',') -> 1,2,3,,5
>> select to_string(array[1,2,3,null,5], ',', null) -> 1,2,3,5
>>
>> maybe - next idea and maybe better - we can check NOT NULL for
>> separator and to add other parameter with default = false -
>> ignore_null
>>
>> select to_string(array[1,2,3,null,5], ',', ignore_null := true) -> 1,2,3,5
>>
>> what do you think?
>
> I don't have any problem with null_string = NULL in to_string taking
> the meaning "skip over NULL elements".  It's a slightly strange
> outcome but it's more useful than returning NULL, and I do like that
> it gives us a path to the current array_to_string() treatment even if
> those functions are ultimately deprecated.  I think adding a fourth
> keyword argument might be sacrificing a little too much convenience in
> the calling convention.
>
> As for to_array, null_string = NULL should mean that there is no
> string which should result in a NULL element.  So I would be happy to
> see the following set of behaviours:
>
> to_string(array[1, 2, 3, 4, 5], ',', null) = '1,2,3,4,5'
> to_string(array[1, 2, 3, null, 5], ',', null) = '1,2,3,5'
> to_array('1,2,3,,5', ',', null) = '{1,2,3,"",5}'
>
> Also, if we're going to make the function non-strict, we need to
> consider how to respond when the user specifies NULL for the other
> arguments.  If the field separator is NULL, bearing in mind that NULL
> can't match any string, I would expect that to_array would return the
> undivided string as a single array element, and that to_string would
> throw an error:
>

ok, it has a sense.

other question is empty string as separator - but I think, it can has
same behave like string_to_array and array_to_string functions.

> to_array('1,2,3,4,5', null) = '{"1,2,3,4,5"}'
> to_string(array[1,2,3,4,5], null) = ERROR: the field separator for
> to_string may not be NULL
>
> If the first argument is NULL for either function, I think it would be
> reasonable to return NULL.  But I could be convinced that we should
> throw an error in that case too.
>

I agree - I prefer a NULL

Thank You very much

Pavel

> Cheers,
> BJ
>

-- 
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] reducing NUMERIC size for 9.1

[Tom Lane]

Robert Haas  writes:
> I'm not entirely happy with the way I handled the variable-length
> struct, although I don't think it's horrible, either. I'm willing to
> rework it if someone has a better idea.

I don't like the way you did that either (specifically, not the kluge
in NUMERIC_DIGITS()).  It would probably work better if you declared
two different structs, or a union of same, to represent the two layout
cases.

A couple of other thoughts:

n_sign_dscale is now pretty inappropriately named, probably better to
change the field name.  This will also help to catch anything that's
not using the macros.  (Renaming the n_weight field, or at least burying
it in an extra level of struct, would be helpful for the same reason.)

It seems like you've handled the NAN case a bit awkwardly.  Since the
weight is uninteresting for a NAN, it's okay to not store the weight
field, so I think what you should do is consider that the dscale field
is still full-width, ie the format of the first word remains old-style
not new-style.  I don't remember whether dscale is meaningful for a NAN,
but if it is, your approach is constraining what is possible to store,
and is also breaking compatibility with old databases.

Also, I wonder whether you can do anything with depending on the actual
bit values of the flag bits --- specifically, it's short header format
iff first bit is set.  The NUMERIC_HEADER_SIZE macro in particular could
be made more efficient with that.

The sign extension code in the NUMERIC_WEIGHT() macro seems a bit
awkward; I wonder if there's a better way.  One solution might be to
offset the value (ie, add or subtract NUMERIC_SHORT_WEIGHT_MIN) rather
than try to sign-extend per se.

Please do NOT commit this:

(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
!errmsg("value overflows numeric format %x w=%d 
s=%u",
!   result->n_sign_dscale,
!   NUMERIC_WEIGHT(result), 
NUMERIC_DSCALE(result;

or at least hide it in "#ifdef DEBUG_NUMERIC" or some such.

Other than that the code changes look pretty clean, I'm mostly just
dissatisfied with the access macros.

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] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 20:52 +0300, Heikki Linnakangas wrote:
> On 16/07/10 20:11, Rob Wultsch wrote:
> > On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas  wrote:
> >> For committers.
> >
> > Perhaps this discussions should be moved to the General list in order
> > to poll the userbase.
> >
> > My .02 is that SHOW commands (even if they are not compatible) would
> > make it much easier for me to make an argument to my boss to at least
> > consider moving off another open source database. The show commands
> > are in *very* widespread use by the MySQL community even after ~5
> > years of having the i_s. The Drizzle team (a radical fork of MySQL)
> > very briefly considered removing the SHOW commands and the unanimous
> > objections that followed caused that idea to scrapped.
> 
> That's for MySQL. I come from a DB2 background, and when I started using 
> psql years ago, I often typed "LIST TABLES" without thinking much about 
> it. Not SHOW TABLES, but LIST TABLES.

> I bet Oracle users coming to PostgreSQL will try "DESC". Not SHOW 
> TABLES. As Simon listed, every DBMS out there has a different syntax for 
> this.

Agreed

> I have nothing against SHOW TABLES 

...but SHOW wins, based on numbers of people expecting that

> (it might cause conflicts in grammar 
> though)

We don't have t handle it in the grammar. There are no parameters called
"tables", "databases" etc

> , but if we're going to cater to people migrating from MySQL, I 
> feel we should cater to people migrating from other products too. But 
> surely we're not going to implement 10 different syntaxes for the same 
> thing! We could, however, give a hint in the syntax error in all those 
> cases.

That's a very sensible suggestion, we should give a hint for all common
commands SHOW, LIST, etc., even though we pick just one to implement.

> That way we're not on the hook to maintain them forever, and we 
> will be 

> doing people a favor by introducing them to the backslash 
> commands

That's a sentence I never thought to see written down

> or information schema, which are more powerful.

and this in no way detracts from that power and standardisation.

-- 
 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] SHOW TABLES

[Rob Wultsch]

On Fri, Jul 16, 2010 at 10:52 AM, Heikki Linnakangas
 wrote:
> On 16/07/10 20:11, Rob Wultsch wrote:
>>
>> On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas
>>  wrote:
>>>
>>> For committers.
>>
>> Perhaps this discussions should be moved to the General list in order
>> to poll the userbase.
>>
>> My .02 is that SHOW commands (even if they are not compatible) would
>> make it much easier for me to make an argument to my boss to at least
>> consider moving off another open source database. The show commands
>> are in *very* widespread use by the MySQL community even after ~5
>> years of having the i_s. The Drizzle team (a radical fork of MySQL)
>> very briefly considered removing the SHOW commands and the unanimous
>> objections that followed caused that idea to scrapped.
>
> That's for MySQL. I come from a DB2 background, and when I started using
> psql years ago, I often typed "LIST TABLES" without thinking much about it.
> Not SHOW TABLES, but LIST TABLES.
>
> I bet Oracle users coming to PostgreSQL will try "DESC". Not SHOW TABLES. As
> Simon listed, every DBMS out there has a different syntax for this.
>
> I have nothing against SHOW TABLES (it might cause conflicts in grammar
> though), but if we're going to cater to people migrating from MySQL, I feel
> we should cater to people migrating from other products too. But surely
> we're not going to implement 10 different syntaxes for the same thing! We
> could, however, give a hint in the syntax error in all those cases. That way
> we're not on the hook to maintain them forever, and we will be doing people
> a favor by introducing them to the backslash commands or information schema,
> which are more powerful.
>
> --
>  Heikki Linnakangas
>  EnterpriseDB   http://www.enterprisedb.com
>

desc[ribe] also works in MySQL.

Perhaps describe would be a good option:
describe tables
describe table  (or perhaps descrive ?)
describe schemas
etc


-- 
Rob Wultsch
wult...@gmail.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] Synchronous replication

[Heikki Linnakangas]

On 14/07/10 09:50, Fujii Masao wrote:

TODO

The patch have no features for performance improvement of synchronous
replication. I admit that currently the performance overhead in the
master is terrible. We need to address the following TODO items in the
subsequent CF.

* Change the poll loop in the walsender
* Change the poll loop in the backend
* Change the poll loop in the startup process
* Change the poll loop in the walreceiver


I was actually hoping to see a patch for these things first, before any 
of the synchronous replication stuff. Eliminating the polling loops is 
important, latency will be laughable otherwise, and it will help the 
synchronous case too.



* Perform the WAL write and replication concurrently
* Send WAL from not only disk but also WAL buffers


IMHO these are premature optimizations that we should not spend any 
effort on now. Maybe later, if ever.


--
  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] Synchronous replication

[Heikki Linnakangas]

On 16/07/10 20:26, Dimitri Fontaine wrote:

Le 16 juil. 2010 à 12:43, Heikki 
Linnakangas  a écrit :


On 16/07/10 10:40, Fujii Masao wrote:

So we should always prevent the standby from applying any WAL in pg_xlog
unless walreceiver is in progress. That is, if there is no WAL available
in the archive, the standby ignores pg_xlog and starts walreceiver
process to request for WAL streaming.


That completely defeats the purpose of storing streamed WAL in pg_xlog in the 
first place. The reason it's written and fsync'd to pg_xlog is that if the 
standby subsequently crashes, you can use the WAL from pg_xlog to reapply the 
WAL up to minRecoveryPoint. Otherwise you can't start up the standby anymore.


I guess we know for sure that this point has been fsync()ed on the Master, or 
that we could arrange it so that we know that?


At the moment we only stream WAL that's already been fsync()ed on the 
master, so we don't have this problem, but Fujii is proposing to change 
that.


I think that's a premature optimization, and we should not try to change 
that. There is no evidence from field (granted, streaming replication is 
a new feature) or from performance tests that it is a problem in 
practice, or that sending WAL earlier would help. Let's concentrate on 
the bare minimum required to make synchronous replication work.


--
  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] putting plproxy in pg_pltemplate

[Dimitri Fontaine]

Le 16 juil. 2010 à 13:13, Hannu Krosing  a écrit :

> Hi
> 
> Should we put some externally managed languages , like pl/proxy also in
> pgtemplate, so that CREATE LANGUAGE would work on them ?

I still  to manage an extension patch. It should be easy for plproxy author (hi 
Marko) to care for pltemplate entry, I hope

-- 
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] SHOW TABLES

[Heikki Linnakangas]

On 16/07/10 20:11, Rob Wultsch wrote:

On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas  wrote:

For committers.


Perhaps this discussions should be moved to the General list in order
to poll the userbase.

My .02 is that SHOW commands (even if they are not compatible) would
make it much easier for me to make an argument to my boss to at least
consider moving off another open source database. The show commands
are in *very* widespread use by the MySQL community even after ~5
years of having the i_s. The Drizzle team (a radical fork of MySQL)
very briefly considered removing the SHOW commands and the unanimous
objections that followed caused that idea to scrapped.


That's for MySQL. I come from a DB2 background, and when I started using 
psql years ago, I often typed "LIST TABLES" without thinking much about 
it. Not SHOW TABLES, but LIST TABLES.


I bet Oracle users coming to PostgreSQL will try "DESC". Not SHOW 
TABLES. As Simon listed, every DBMS out there has a different syntax for 
this.


I have nothing against SHOW TABLES (it might cause conflicts in grammar 
though), but if we're going to cater to people migrating from MySQL, I 
feel we should cater to people migrating from other products too. But 
surely we're not going to implement 10 different syntaxes for the same 
thing! We could, however, give a hint in the syntax error in all those 
cases. That way we're not on the hook to maintain them forever, and we 
will be doing people a favor by introducing them to the backslash 
commands or information schema, which are more powerful.


--
  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] SHOW TABLES

[Bruce Momjian]

Chris Browne wrote:
>  - I'd sure like to be able to write queries that *don't* involve
>array smashing or using "grep" on \z output to analyze object
>permissions.

The \z output is an embarrassment, no question about it in my mind.

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

  + None of us is going to be here forever. +

-- 
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] SHOW TABLES

[Kevin Grittner]

"Marc G. Fournier"  wrote:
 
> Haven't experienced Sybase for 2 years in my last job, I can tell
> you that the sp_* commands are definitely non-intuitive :(
 
In general, I'd agree; although I think I got used to them about as
fast as the PostgreSQL backslash commands.  In the particular case
of sp_help I would disagree; once you've heard that, it's pretty
easy to remember and it works for tables, views, stored procedures,
logs, rules, defaults, triggers, referential constraints, encryption
keys, and check constraints.
 
You type:
 
sp_help 
 
And you get information back which is both reasonably
human-digestable based on the formatting of result sets in whatever
client you're using, and reasonably machine-digestable based on
looking at the column headers of the result sets.
 
-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] Synchronous replication

[Dimitri Fontaine]

Le 16 juil. 2010 à 12:43, Heikki Linnakangas 
 a écrit :

> On 16/07/10 10:40, Fujii Masao wrote:
>> So we should always prevent the standby from applying any WAL in pg_xlog
>> unless walreceiver is in progress. That is, if there is no WAL available
>> in the archive, the standby ignores pg_xlog and starts walreceiver
>> process to request for WAL streaming.
> 
> That completely defeats the purpose of storing streamed WAL in pg_xlog in the 
> first place. The reason it's written and fsync'd to pg_xlog is that if the 
> standby subsequently crashes, you can use the WAL from pg_xlog to reapply the 
> WAL up to minRecoveryPoint. Otherwise you can't start up the standby anymore.

I guess we know for sure that this point has been fsync()ed on the Master, or 
that we could arrange it so that we know that?

Re: [HACKERS] SHOW TABLES

[Chris Browne]

si...@2ndquadrant.com (Simon Riggs) writes:
> Just for the record, I've never ever met anyone that said "Oh, this
> \d syntax makes so much sense. I'm a real convert to Postgres now
> you've shown me this". The reaction is always the opposite one;
> always negative. Which detracts from our efforts elsewhere.

If we're opening up the code to change this, it makes a lot of sense
to try to Do It Really Right so that we're not going over this again
and again.

I think we're seeing several things that suck, and I'm quite sure I
have not yet heard an answer that resolves it all.  Things that have
become clear:

 1.  \d isn't exactly the most intuitive thing ever

 And it's pretty clear that we have been heading into some
 increasingly cryptic bits of fruit salad of
 \dfzb+-meta-bucky-alt-foo

 Having SHOW THIS and SHOW THAT which are a bit more readily
 guessed would be somewhat nice.

 2.  information_schema doesn't have some useful things that we'd like
 it to have

 Listing databases would be nice.  Unfortunately, "ANSI didn't
 define a way to do that, so we can't add it."

 Alas, I don't see a good way to improve on this :-(

 3.  The \? commands are *solely* for psql, and it would be nice to
 have the Improvement work on server side so it's not only usable
 with the one client.

 4.  It would be Mighty Useful for whatever extensions get defined
 server-side to also be "relational" so that they can be usefully
 scripted in ways NOT vulnerable to screen size, output hackery,
 and such.

 - I've seen too many QA scripts that do awk parsing of output of
   psql "\d" commands that are vulnerable to all kinds of awfulness.
   Add an "updated-on" column to the output, and suddenly everything
   breaks.

 - I'd sure like to be able to write queries that *don't* involve
   array smashing or using "grep" on \z output to analyze object
   permissions.

 - \? output is often *not* amenable to this, as it sometimes has
   extra bits of data hierarchy in it.  And "array aggregation."

There's a certain risk of things being overspecified such that there's
*no* solution, but I don't think that forcibly *has* to happen.

But the answers I'm seeing thus far run slipshod across too many of
these things, so I don't see that we have arrived at actual solutions
yet.
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/linuxdistributions.html
Rules of the Evil Overlord #77. "If I have a fit of temporary insanity
and decide to give  the hero the chance to reject a  job as my trusted
lieutentant,  I will  retain enough  sanity to  wait until  my current
trusted  lieutenant  is  out  of  earshot before  making  the  offer."


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

[HACKERS] Review: Patch for phypot - Pygmy Hippotause

[Andrew Geery]

This is a review of the phypot - Pygmy Hippotause patch:
http://archives.postgresql.org/message-id/4a9897e1.8090...@netspace.net.au
submitted by Paul Matthews.

Contents & Purpose
==
The purpose of the patch is to compute a hypotenuse with higher
precision than the current implementation (the HYPOT macro in
src/include/utils/geo_decls.h).  The initial impetus for the patch
goes back to this message
[http://archives.postgresql.org/pgsql-hackers/2009-08/msg01579.php].
The new phypot function (in src/backend/utils/adt/geo_ops.c) is well
documented in the function header comments and matches the discussion
on the wikipedia page [http://en.wikipedia.org/wiki/Hypot].  It is
envisioned that the new phypot function will eventually be replaced by
the standard C99 hypot function.  This message
[http://archives.postgresql.org/pgsql-hackers/2009-08/msg01580.php]
discusses why the standard c99 hypot function can't be used
(PostgreSQL targets c89, not c99 -- although other messages in the
thread make it sound like the hypot function is ubiquitous).

Initial Run
===
The patch is in context diff form and applies cleanly to the current CVS HEAD.
There are no tests.
There is no documentation, outside of the code comments, as this is an
internal function.

A couple of nitpicking items:
(1) the phypot function is declared as static, but it is not defined that way
(2) to better match the style of the rest of the geo_ops.c file:
        (a) put the function return type on its own line
        (b) don't put spaces after a "(" and before a ")" [e.g.,
if-statements, function declaration]
        (c) put a space between the keyword "if" and the opening "("
        (d) put spaces around arithmetic operators

Performance
===
The two concerns about the patch in the mail archives
[http://archives.postgresql.org/message-id/4b83ee3102250002f...@gw.wicourts.gov]
are that
(1) since there is more logic in the new function than in the original
marco, it might be slower; and
(2) despite the fact that it is a better implementation of the
hypotenuse functionality, it might break existing applications which
are depending on the existing computation

For (1), I wrote a small c program that executed the original HYPOT
macro in a loop 100 million times and I did the same with the new
phypot function.  The new phypot function is, on average, about twice
as slow as the original HYPOT macro.  The HYPOT macro executed 100
million times in 11 seconds and the phypot function executed the same
number of times in 22 seconds.  With both -O2 and -O3, the HYPOT macro
executed in 8 seconds and the phypot in 18.

For (2), I wrote a small c program that executed the original HYPOT
macro and the new phypot function in a loop 100 million times on
random numbers and compared at what precision the two calculations
started to differ.  I found that the difference in the two
calculations were always less than 0.01.  However, about a third
of the calculations differed at one more magnitude of precision (that
is, there were differences in the calculations that were greater than
0.001).

Conclusion
==
I like that the patch provides greater precision.  However, I am
unclear as to how significant the slow down is in the new function
(it's certainly not very significant for small iterations), nor how
significant the difference in the calculations is between the existing
macro and the new function.

Thanks
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] SHOW TABLES

[Marc G. Fournier]

On Fri, 16 Jul 2010, Simon Riggs wrote:


SQLServer and Sybase use sp_ procedures for this


Haven't experienced Sybase for 2 years in my last job, I can tell you that 
the sp_* commands are definitely non-intuitive :(



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
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] SHOW TABLES

[Rob Wultsch]

On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas  wrote:
> For committers.

Perhaps this discussions should be moved to the General list in order
to poll the userbase.

My .02 is that SHOW commands (even if they are not compatible) would
make it much easier for me to make an argument to my boss to at least
consider moving off another open source database. The show commands
are in *very* widespread use by the MySQL community even after ~5
years of having the i_s. The Drizzle team (a radical fork of MySQL)
very briefly considered removing the SHOW commands and the unanimous
objections that followed caused that idea to scrapped.

-- 
Rob Wultsch
wult...@gmail.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] psql auto-completion for multiple where clauses

[Tom Lane]

"Greg Sabino Mullane"  writes:
> No: there is only a small number of words that we go back through, 
> so the above will not work as we cannot get back to the name of the table 
> from the right side of the AND. The way to fix that is to redesign our 
> tab-completion system such that it knows about a greater number of words, 
> perhaps even the complete statement.

Yeah, tab_complete.c is really a pretty awful kluge.  One thing that
might be interesting is to make it use psql's lexer, which I think
wasn't there at all when the completion code was started.  But that just
takes care of word-recognition issues, it won't do anything at the
semantic level.  I hesitate to think of trying to incorporate the
backend grammar too :-(

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] putting plproxy in pg_pltemplate

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie jul 16 12:49:25 -0400 2010:
> Peter Eisentraut  writes:
> > On fre, 2010-07-16 at 14:13 +0300, Hannu Krosing wrote:
> >> Should we put some externally managed languages , like pl/proxy also in
> >> pgtemplate, so that CREATE LANGUAGE would work on them ?
> 
> > This has been rejected several times before.  See:
> > http://archives.postgresql.org/pgsql-hackers/2009-01/msg00050.php
> 
> Note that there's nothing stopping a DBA from putting new entries in
> pg_pltemplate within his installation.  Peter's points are valid reasons
> why we should be wary of putting entries into our distribution --- it's
> hard to control this sort of stuff over the timescale of a PG major
> release.  But per-installation it doesn't seem unreasonable.

Since a week ago, PL/php ships a small install.sql script that adds a
pg_pltemplate entry.  This seems easy enough for the DBA.

-- 
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] reducing NUMERIC size for 9.1

[Hitoshi Harada]

2010/7/16 Brendan Jurd :
> On 16 July 2010 03:47, Robert Haas  wrote:
>> You might also look at testing with pg_column_size().
>>
>
> pg_column_size() did return the results I was expecting.
> pg_column_size(0::numeric) is 8 bytes on 8.4 and it's 6 bytes on HEAD
> with your patch.
>
> However, even with 1 million rows of 0::numeric in my test table,
> there was no difference at all in the on-disk relation size (36290560
> with 36249600 in the table and 32768 in the fsm).
>
> At this scale we should be seeing around 2 million bytes saved, but
> instead the tables are identical.  Is there some kind of disconnect in
> how the new short numeric is making it to the disk, or perhaps another
> effect interfering with my test?

What about large ARRAY of numeric type? Once upon a time I develop
tinyint for myself, the array size could get reduced.

Regards,



-- 
Hitoshi Harada

-- 
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] SHOW TABLES

[Robert Haas]

On Jul 16, 2010, at 7:43 AM, Bruce Momjian  wrote:
> Andrew Dunstan wrote:
>> 
>> Bruce Momjian wrote:
>>> I assume SHOW TABLES would only be useful for interactive terminal
>>> sesssions, not for application code (which should use
>>> information_schema), so what non-psql interactive terminal programs are
>>> there?
>>> 
>>> 
>> 
>> I think your assumption is questionable.
>> 
>> Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings 
>> (for good or ill). That's why any suggestion that we should return 
>> anything other than a resultset seems like a really terrible idea to me.
> 
> If they are writing an application, finding the query to show all tables
> is the least of their problems.  I don't see how SHOW TABLE
> significantly helps in that case, except make things 0.001% easier,
> while creating duplicate functionality in Postgres.

Many years ago I needed to write a program that needed to be able to fetch a 
list of tables in the DB, and then a list of attributes for each table. It took 
me at least a full day and I almost gave up and abandoned PostgreSQL as a 
result.  I think calling this duplicate functionality is ridiculous.  Sure, 
it's possible. In fact, it's very easy.

For committers.

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

Re: [HACKERS] ERROR: argument to pg_get_expr() must come from system catalogs

[Tom Lane]

Robert Haas  writes:
> On Jul 16, 2010, at 2:27 AM, Heikki Linnakangas 
>  wrote:
>> If we continue with the approach I took, we should implement the suggestion 
>> to create a new data type for this in 9.1. That would be more waterproof 
>> than the changes I made, if we introduce new ways to call functions in the 
>> future.

> The downside is that it might cause the approach used in the older releases 
> to get less testing.

I hope we can get a better fix into the next 9.0 beta, so it will get
some field testing before any back-branch minor releases happen.

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] putting plproxy in pg_pltemplate

[Tom Lane]

Peter Eisentraut  writes:
> On fre, 2010-07-16 at 14:13 +0300, Hannu Krosing wrote:
>> Should we put some externally managed languages , like pl/proxy also in
>> pgtemplate, so that CREATE LANGUAGE would work on them ?

> This has been rejected several times before.  See:
> http://archives.postgresql.org/pgsql-hackers/2009-01/msg00050.php

Note that there's nothing stopping a DBA from putting new entries in
pg_pltemplate within his installation.  Peter's points are valid reasons
why we should be wary of putting entries into our distribution --- it's
hard to control this sort of stuff over the timescale of a PG major
release.  But per-installation it doesn't seem unreasonable.

regards, tom lane

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

Re: [HACKERS] patch: to_string, to_array functions

[Brendan Jurd]

On 17 July 2010 02:15, Pavel Stehule  wrote:
> 2010/7/16 Brendan Jurd :
>> Regarding the behaviour of the third argument (null_string), I was a
>> little surprised by the results when I passed in a NULL.
>>
>
> I didn't thinking about NULL as separator before. Current behave isn't
> practical. When default separator is empty string, then NULL can be
> used as ignore NULLs - so it can emulate current string_to_array and
> array_to_string behave. It can be, because NULL can't be a separator
> ever.
>
> select to_string(array[1,2,3,null,5], ',') -> 1,2,3,,5
> select to_string(array[1,2,3,null,5], ',', null) -> 1,2,3,5
>
> maybe - next idea and maybe better - we can check NOT NULL for
> separator and to add other parameter with default = false -
> ignore_null
>
> select to_string(array[1,2,3,null,5], ',', ignore_null := true) -> 1,2,3,5
>
> what do you think?

I don't have any problem with null_string = NULL in to_string taking
the meaning "skip over NULL elements".  It's a slightly strange
outcome but it's more useful than returning NULL, and I do like that
it gives us a path to the current array_to_string() treatment even if
those functions are ultimately deprecated.  I think adding a fourth
keyword argument might be sacrificing a little too much convenience in
the calling convention.

As for to_array, null_string = NULL should mean that there is no
string which should result in a NULL element.  So I would be happy to
see the following set of behaviours:

to_string(array[1, 2, 3, 4, 5], ',', null) = '1,2,3,4,5'
to_string(array[1, 2, 3, null, 5], ',', null) = '1,2,3,5'
to_array('1,2,3,,5', ',', null) = '{1,2,3,"",5}'

Also, if we're going to make the function non-strict, we need to
consider how to respond when the user specifies NULL for the other
arguments.  If the field separator is NULL, bearing in mind that NULL
can't match any string, I would expect that to_array would return the
undivided string as a single array element, and that to_string would
throw an error:

to_array('1,2,3,4,5', null) = '{"1,2,3,4,5"}'
to_string(array[1,2,3,4,5], null) = ERROR: the field separator for
to_string may not be NULL

If the first argument is NULL for either function, I think it would be
reasonable to return NULL.  But I could be convinced that we should
throw an error in that case too.

Cheers,
BJ

-- 
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] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 12:25 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:
> > 
> > > Really?  What are the other syntaxes?
> > 
> > SHOW TABLES
> 
> That is MySQL?  Do does every other RDBMs also use that, as David
> suggested?

He didn't say it was exactly that syntax. We must retain some common
sense in the discussion.

DB2 uses LIST TABLES
SQLServer and Sybase use sp_ procedures for this
Informix uses INFO TABLES
Ingres uses HELP and HELP TABLE foo
Teradata uses SHOW TABLE foo but no syntax meaning "all tables"

So I think David's actual response was appropriate and accurate: its a
common thing to have easily guessable commands for this.

Search Google for " SHOW TABLES" and you'll see that a
lot of people look for and expect this kind of command to exist.

-- 
 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] SHOW TABLES

[Kevin Grittner]

Bruce Momjian  wrote:
 
> What are the other syntaxes?
 
For Sybase ASE sp_help and other stored procedures, see:
 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1550/html/sprocs/X85190.htm
 
Like \d, these server-side stored procedures can return a number of
result sets.  Like Robert, I'm skeptical of implementing a
server-side solution for PostgreSQL which doesn't do the same.  I'm
not clear on whether that's even possible without a new version of
wire protocol, though.
 
-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] Status report on writeable CTEs

[Marko Tiikkaja]

On 7/16/10 7:15 PM +0300, Hitoshi Harada wrote:

2010/7/17 Marko Tiikkaja:

I thought about this, but I don't necessarily like the idea of overloading
executor nodes.


Neither do I have good shape for this solution. Maybe it's not good
idea. But my concern is adding DtScanNode, which looks similar to
MaterialNode. Of course each purpose is different, but quite big part
will overlap each other, I think.


The way I see it is that reading from a tuplestore is so simple that we 
shouldn't be trying to merge together nodes just on that basis.  It 
seems to me that we'd have to add CteScan and WorkTableScan nodes there 
too and at that point it would become complicated.



I didn't look at this because I thought using a "tuplestore receiver" in the
portal logic was simple enough.  Any thoughts on how this would work?


It's just deconstructing queries like:

WITH t AS (INSERT INTO x ... RETURING *)
SELECT * FROM t;

to

CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
SELECT * FROM t;


That's an idea.  Can we somehow avoid name clashes with user-defined 
temporary tables?



Another concern is tuplestore's memory exhausting. Tuplestore holds
tuples in memory as far as the estimated memory usage is within
work_mem (for *each* not total of all tuplestores!), but if you create
dozens of tuplestore (and it's quite possible in wCTE use cases) we
will possibly fail into memory overflow problems.


That doesn't seem very different from a big SELECT query, except with 
wCTEs, you actually *know* how many times the work_mem can be used 
before you run the query and can adjust work_mem accordingly.


That said, I personally could live with a separate GUC for just 
adjusting the work_mem of "wcte tuplestores".  Another option would be 
to unconditionally force the tuplestores to disk, but that sounds painful.



Regards,
Marko Tiikkaja

--
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] SHOW TABLES

[Bruce Momjian]

Simon Riggs wrote:
> On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:
> 
> > Really?  What are the other syntaxes?
> 
> SHOW TABLES

That is MySQL?  Do does every other RDBMs also use that, as David
suggested?

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

  + None of us is going to be here forever. +

-- 
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] SHOW TABLES

[Aidan Van Dyk]

* Simon Riggs  [100716 12:24]:
> On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:
> 
> > Really?  What are the other syntaxes?
> 
> SHOW TABLES

Obviously, only for some $value of $other...

The 3 database I have access to:

[DataDirect][ODBC SQL Server Driver][SQL Server]Could not find stored 
procedure 'SHOW'.
[ISQL]ERROR: Could not SQLExecute

Error: near "show": syntax error

ERROR:  unrecognized configuration parameter "tables"

So it's obviously not universal...

a.

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:

> Really?  What are the other syntaxes?

SHOW TABLES

-- 
 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] dividing money by money

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> The only way I'd be willing to label those things immutable was if
>> we did something to lock down lc_monetary for the life of a
>> database (ie, make it work more like lc_collate does now).  Which
>> might be a good idea, but it's not how it works today.
 
> Interesting.  In general, what is involved in locking something like
> this down for the life of a database?

IIRC, the main pain point is providing an option for CREATE DATABASE
to set the value.  If you chase down all the references to lc_collate
you'll get the picture.

It'd probably be worth doing if money were less deprecated, but right
now I can't get excited about it.

Actually ... the thing that might turn money into a less deprecated type
is if you could set lc_monetary per column.  I wonder whether Peter's
collation hack could be extended to deal with 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] SHOW TABLES

[Bruce Momjian]

David Fetter wrote:
> On Fri, Jul 16, 2010 at 12:04:01PM -0400, Bruce Momjian wrote:
> > Marc G. Fournier wrote:
> > > On Fri, 16 Jul 2010, Bruce Momjian wrote:
> > > 
> > > >> There are many tools that can access Postgres. Some are libpq programs,
> > > >> though there are command line versions in every environment: java,
> > > >> python, etc..
> > > >
> > > > Yeah, but do enough people use them to warrant putting this in the
> > > > backend?
> > > 
> > > I may have lost the gist of this question, but ... how can they use them 
> > > if they don't exist?
> > 
> > Clarification, do enough people use non-psql command line tools to
> > warrant putting this in the backend?
> 
> Yes.  Such backend stuff is in every RDBMS except ours.

Really?  What are the other syntaxes?

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

  + None of us is going to be here forever. +

-- 
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] Status report on writeable CTEs

[Hitoshi Harada]

2010/7/17 Marko Tiikkaja :
> On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:
>>
>> 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
>> is exsiting one that work with single tuplestore, it might be sane to
>> modify this so that it accepts tuplestore from Query instead of its
>> child node.
>
> I thought about this, but I don't necessarily like the idea of overloading
> executor nodes.

Neither do I have good shape for this solution. Maybe it's not good
idea. But my concern is adding DtScanNode, which looks similar to
MaterialNode. Of course each purpose is different, but quite big part
will overlap each other, I think.

>> 2. Use temp table instead of tuplestore list. Since we agreed we need
>> to execute each plan one by one starting and shutting down executor,
>> it now looks very simple strategy.
>
> I didn't look at this because I thought using a "tuplestore receiver" in the
> portal logic was simple enough.  Any thoughts on how this would work?

It's just deconstructing queries like:

WITH t AS (INSERT INTO x ... RETURING *)
SELECT * FROM t;

to

CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
SELECT * FROM t;

While the second statement is not implemented yet, it will be so simpler.

Another concern is tuplestore's memory exhausting. Tuplestore holds
tuples in memory as far as the estimated memory usage is within
work_mem (for *each* not total of all tuplestores!), but if you create
dozens of tuplestore (and it's quite possible in wCTE use cases) we
will possibly fail into memory overflow problems.

>> I'm not familiar with the long discussion on this feature so not sure
>> they are possible, but ISTM  they are enough to be discussed (or
>> discussed already?).
>
> We haven't discussed this part of the design yet..  Now is a good time to do
> it.

Yeah, we should.  Anyone has another idea? Or adding DtScanNode for
this features is fair enough?


Regards,


-- 
Hitoshi Harada

-- 
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: to_string, to_array functions

[Pavel Stehule]

Hello

2010/7/16 Brendan Jurd :
> On 6 May 2010 04:42, Pavel Stehule  wrote:
>> attached patch contains to_string and to_array functions. These
>> functions are equivalent of array_to_string and string_to_array
>> function with maybe more correct NULL handling.
>
> Hi Pavel,
>
> I am reviewing your patch for the commitfest.
>
> Overall the patch looks good, although there were some bogus
> whitespace changes in the patch and some messy punctuation/grammar in
> some of the code comments.  I also thought it was worth mentioning in
> the docs the default value for null_string is ''.  I made an attempt
> to clean those items up and have attached a v2 of the patch.
>
> Regarding the behaviour of the third argument (null_string), I was a
> little surprised by the results when I passed in a NULL.
>
> postgres=# select to_string(array['a', 'b', 'c', 'd'], '/', NULL);
>  to_string
> ---
>
> Now, if the array had some NULL elements in it, I could understand why
> the resulting string would be NULL (because str || NULL is NULL), but
> in this case there are no NULLs.  Why is the result NULL?  Surely it
> should be 'a/b/c/d' regardless of how the third parameter is set?
>
> In the reverse case:
>
> postgres=# select to_array('a/b/c/d', '/', NULL);
>  to_array
> --
>
> (1 row)
>

I didn't thinking about NULL as separator before. Current behave isn't
practical. When default separator is empty string, then NULL can be
used as ignore NULLs - so it can emulate current string_to_array and
array_to_string behave. It can be, because NULL can't be a separator
ever.

select to_string(array[1,2,3,null,5], ',') -> 1,2,3,,5
select to_string(array[1,2,3,null,5], ',', null) -> 1,2,3,5

maybe - next idea and maybe better - we can check NOT NULL for
separator and to add other parameter with default = false -
ignore_null

select to_string(array[1,2,3,null,5], ',', ignore_null := true) -> 1,2,3,5

what do you think?

Regards

Pavel

> Again I find this a bit weird.  I have left the null_string NULL,
> which means it is unknown.  It can't possibly match any value in the
> string, so effectively passing in a NULL null_string should mean that
> the user doesn't want any string items whatsoever to translate into
> NULLs in the resulting array.  I would expect this call to return
> {a,b,c,d}.
>
> Cheers,
> BJ
>

-- 
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] SHOW TABLES

[David E. Wheeler]

On Jul 16, 2010, at 9:09 AM, David Fetter wrote:

>> Clarification, do enough people use non-psql command line tools to
>> warrant putting this in the backend?
> 
> Yes.  Such backend stuff is in every RDBMS except ours.

I admit that I had to do a *lot* of work to write the schema-testing functions 
for pgTAP. Getting information about functions is especially hairy (I poached a 
view from newsysviews to get what I needed).

I'd love a cleaner way to get at this information.

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] SHOW TABLES

[David Fetter]

On Fri, Jul 16, 2010 at 12:04:01PM -0400, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > On Fri, 16 Jul 2010, Bruce Momjian wrote:
> > 
> > >> There are many tools that can access Postgres. Some are libpq programs,
> > >> though there are command line versions in every environment: java,
> > >> python, etc..
> > >
> > > Yeah, but do enough people use them to warrant putting this in the
> > > backend?
> > 
> > I may have lost the gist of this question, but ... how can they use them 
> > if they don't exist?
> 
> Clarification, do enough people use non-psql command line tools to
> warrant putting this in the backend?

Yes.  Such backend stuff is in every RDBMS except ours.

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] reducing NUMERIC size for 9.1

[David E. Wheeler]

On Jul 16, 2010, at 9:04 AM, Bruce Momjian wrote:

>> What are the implications for pg_upgrade? Will a database with values
>> created before the patch continue to work after the patch has been
>> applied (as happened with the new hstore in 9.0), or will pg_upgrade
>> need to be taught how to upgrade the old storage format?
> 
> Robert told me the old format continues to work in the upgraded
> databases.

Awesome. rhaas++

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] reducing NUMERIC size for 9.1

[Bruce Momjian]

David E. Wheeler wrote:
> On Jul 16, 2010, at 6:17 AM, Thom Brown wrote:
> 
> > Joy! :)  Nice patch Robert.
> 
> Indeed.
> 
> What are the implications for pg_upgrade? Will a database with values
> created before the patch continue to work after the patch has been
> applied (as happened with the new hstore in 9.0), or will pg_upgrade
> need to be taught how to upgrade the old storage format?

Robert told me the old format continues to work in the upgraded
databases.

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

  + None of us is going to be here forever. +

-- 
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] SHOW TABLES

[Bruce Momjian]

Marc G. Fournier wrote:
> On Fri, 16 Jul 2010, Bruce Momjian wrote:
> 
> >> There are many tools that can access Postgres. Some are libpq programs,
> >> though there are command line versions in every environment: java,
> >> python, etc..
> >
> > Yeah, but do enough people use them to warrant putting this in the
> > backend?
> 
> I may have lost the gist of this question, but ... how can they use them 
> if they don't exist?

Clarification, do enough people use non-psql command line tools to
warrant putting this in the backend?

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

  + None of us is going to be here forever. +

-- 
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] reducing NUMERIC size for 9.1

[David E. Wheeler]

On Jul 16, 2010, at 6:17 AM, Thom Brown wrote:

> Joy! :)  Nice patch Robert.

Indeed.

What are the implications for pg_upgrade? Will a database with values created 
before the patch continue to work after the patch has been applied (as happened 
with the new hstore in 9.0), or will pg_upgrade need to be taught how to 
upgrade the old storage format?

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] SHOW TABLES

[Marc G. Fournier]

On Fri, 16 Jul 2010, Bruce Momjian wrote:


There are many tools that can access Postgres. Some are libpq programs,
though there are command line versions in every environment: java,
python, etc..


Yeah, but do enough people use them to warrant putting this in the
backend?


I may have lost the gist of this question, but ... how can they use them 
if they don't exist?



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
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] Status report on writeable CTEs

[Marko Tiikkaja]

On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:

Sorry it's not relevant to the topic you post but ..


Relevant enough :-)


.. it seems you're
going to add new executor node called DtScanNode and let it hold
tuplestore passed by the Query indicated by index number. However, I
suppose there are other ways:

1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
is exsiting one that work with single tuplestore, it might be sane to
modify this so that it accepts tuplestore from Query instead of its
child node.


I thought about this, but I don't necessarily like the idea of 
overloading executor nodes.



2. Use temp table instead of tuplestore list. Since we agreed we need
to execute each plan one by one starting and shutting down executor,
it now looks very simple strategy.


I didn't look at this because I thought using a "tuplestore receiver" in 
the portal logic was simple enough.  Any thoughts on how this would work?



I'm not familiar with the long discussion on this feature so not sure
they are possible, but ISTM  they are enough to be discussed (or
discussed already?).


We haven't discussed this part of the design yet..  Now is a good time 
to do it.



Regards,
Marko Tiikkaja

--
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] SHOW TABLES

[David Fetter]

On Fri, Jul 16, 2010 at 11:44:58AM -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote:
> > 
> > > I assume SHOW TABLES would only be useful for interactive terminal
> > > sesssions, not for application code (which should use
> > > information_schema), so what non-psql interactive terminal programs
> > > are there?
> > 
> > My original thought was around the newbie experience: they connect to
> > PostgreSQL and then nothing. No sensible commands work, typing
> > "help" doesn't work, nor does typing "quit". Few simple commands they've
> 
> Well, "help" does work now, for some definition of work:
> 
>   $ psql test
>   hpsql (9.1devel)
>   Type "help" for help.
>   
>   test=> help
>   You are using psql, the command-line interface to PostgreSQL.
>   Type:  \copyright for distribution terms
>  \h for help with SQL commands
>  \? for help with psql commands
>  \g or terminate with semicolon to execute query
>  \q to quit
> 
> > learnt elsewhere work either.
> > 
> > We need a way to respond sensibly to common user input.
> > 
> > "Terminal program" is the bit of thinking that is askew there. The
> > question is "what other non-psql interactive programs are there"?
> > Lots.
> > 
> > There are many tools that can access Postgres. Some are libpq programs,
> > though there are command line versions in every environment: java,
> > python, etc..
> 
> Yeah, but do enough people use them to warrant putting this in the
> backend?

Yes.

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] SHOW TABLES

[Bruce Momjian]

Simon Riggs wrote:
> On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote:
> 
> > I assume SHOW TABLES would only be useful for interactive terminal
> > sesssions, not for application code (which should use
> > information_schema), so what non-psql interactive terminal programs
> > are there?
> 
> My original thought was around the newbie experience: they connect to
> PostgreSQL and then nothing. No sensible commands work, typing
> "help" doesn't work, nor does typing "quit". Few simple commands they've

Well, "help" does work now, for some definition of work:

$ psql test
hpsql (9.1devel)
Type "help" for help.

test=> help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

> learnt elsewhere work either.
> 
> We need a way to respond sensibly to common user input.
> 
> "Terminal program" is the bit of thinking that is askew there. The
> question is "what other non-psql interactive programs are there"?
> Lots.
> 
> There are many tools that can access Postgres. Some are libpq programs,
> though there are command line versions in every environment: java,
> python, etc..

Yeah, but do enough people use them to warrant putting this in the
backend?

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

  + None of us is going to be here forever. +

-- 
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] SHOW TABLES

[Simon Riggs]

On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote:

> I assume SHOW TABLES would only be useful for interactive terminal
> sesssions, not for application code (which should use
> information_schema), so what non-psql interactive terminal programs
> are there?

My original thought was around the newbie experience: they connect to
PostgreSQL and then nothing. No sensible commands work, typing
"help" doesn't work, nor does typing "quit". Few simple commands they've
learnt elsewhere work either.

We need a way to respond sensibly to common user input.

"Terminal program" is the bit of thinking that is askew there. The
question is "what other non-psql interactive programs are there"?
Lots.

There are many tools that can access Postgres. Some are libpq programs,
though there are command line versions in every environment: java,
python, etc..

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

[HACKERS] Re: [COMMITTERS] pgsql: Ensure that top level aborts call XLogSetAsyncCommit().

[Simon Riggs]

On Fri, 2010-07-16 at 17:03 +0900, Fujii Masao wrote:

> >> This commit changed XLogSetAsyncCommitLSN() so that it's called
> >> for abort case. So we need to change the comment of the function
> >> as follows:
> >
> > Agreed, will fix.
> >
> > Will also rename function to better document its new role.
> 
> New function name occurred to you?

Thanks for the ping, though not forgotten. 

I expect to make the change for next beta.

-- 
 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] SHOW TABLES

[Steve Atkins]

On Jul 16, 2010, at 8:11 AM, Simon Riggs wrote:

> On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote:
> 
>> The problem is people are stating different requirements.
>> 
>> - to make it easy for new users of psql
>> - to simplify fetching basic database information from any client application
>> - to ease transition between MySQL and PostgreSQL
> 
> Close, but I didn't state any of those as you have them.
> 
> I want to make it easy for newbies to get access to obvious things like
> a list of tables, from *any* interactive application, wherever they
> exist. There are many and various apps and not all of them work the
> same. (The Windows installer ships two, for example). It would be nice
> to tell people "just type SHOW TABLES" and have it be true 100% of the
> time. They can remember that, or at least will try it if they can't
> remember anything at all about our RDBMS.

In pretty much any GUI application the expected way to see a list
of tables is not going to involve typing anything anywhere. Either
the list of tables is going to be shown all the time (common) or
there'll be a menu or toolbar option to show them. 

There may not be anywhere obvious to type in a command, and if there is
the output of a server-side implementation of show tables would
likely be displayed like the contents of a table, rather than as
names of tables - so all the metadata is going to be off. Things
like the context menu for each row of the result having operations
for modifying the contents of a table, rather than the operations
for modifying a table. It'll offer DML operations where you'd expect,
and want, DDL in other words.

Cheers,
  Steve


-- 
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] Status report on writeable CTEs

[Hitoshi Harada]

2010/7/13 Marko Tiikkaja :
> Hi,
>
>
> I've been working on writeable CTEs during the last couple of months, but
> right now it looks like I'm going to miss the first commit fest for 9.1.  I
> was trying to make it work by expanding all wCTEs to their own Queries
> during the rewrite stage (a very crude patch trying to do that for regular
> CTEs attached), but I don't think that it's a good way of approaching the
> problem.  Consider:

Sorry it's not relevant to the topic you post but it seems you're
going to add new executor node called DtScanNode and let it hold
tuplestore passed by the Query indicated by index number. However, I
suppose there are other ways:

1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
is exsiting one that work with single tuplestore, it might be sane to
modify this so that it accepts tuplestore from Query instead of its
child node.
2. Use temp table instead of tuplestore list. Since we agreed we need
to execute each plan one by one starting and shutting down executor,
it now looks very simple strategy.

I'm not familiar with the long discussion on this feature so not sure
they are possible, but ISTM  they are enough to be discussed (or
discussed already?).


Regards,


-- 
Hitoshi Harada

-- 
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] psql auto-completion for multiple where clauses

[Thom Brown]

On 16 July 2010 16:04, Greg Sabino Mullane  wrote:
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
>> Someone highlighed on IRC that after the first WHERE clause,
>> autocomplete no longer works.
> ...
>> SELECT * FROM tab_completion WHERE id = 2 AND s
> ...
>> Is there any chance of improving this so it would work for more than 1
>> WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
>> all, but seems to be fine for ORDER BY.
>
> No: there is only a small number of words that we go back through,
> so the above will not work as we cannot get back to the name of the table
> from the right side of the AND. The way to fix that is to redesign our
> tab-completion system such that it knows about a greater number of words,
> perhaps even the complete statement.
>

Yay for complete overhauls!  Okay, fair enough.  Bit unintuitive
behaviour though.

>> SELECT * FROM tab_completion WHERE id = 2 AND b
>>
>> Since there is no column beginning with "b", it might be an idea to
>> get it to match "bark bark" instead.  It might help alleviate what may
>> be a gotcha for some.
>
> This one is more doable, assuming we are really talking about:
>
> SELECT * FROM tab_completion WHERE b
>
> Keep in mind it will show up in a list if you do the following:
>
> SELECT * FROM tab_completion WHERE 
>
>

Doesn't it do that already?

Thom

-- 
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] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote:

> The problem is people are stating different requirements.
> 
> - to make it easy for new users of psql
> - to simplify fetching basic database information from any client application
> - to ease transition between MySQL and PostgreSQL

Close, but I didn't state any of those as you have them.

I want to make it easy for newbies to get access to obvious things like
a list of tables, from *any* interactive application, wherever they
exist. There are many and various apps and not all of them work the
same. (The Windows installer ships two, for example). It would be nice
to tell people "just type SHOW TABLES" and have it be true 100% of the
time. They can remember that, or at least will try it if they can't
remember anything at all about our RDBMS.

Not trying to ease the transition between MySQL and PostgreSQL, it is
about making things obvious for overworked sysadmins and DBAs. Many
people are familiar with MySQL and many people use both. There are also
dozens of legacy RDBMS for DBAs to remember: Sybase, DB2, Informix,
Teradata, Ingres, MySQL and many others. Providing obvious commands that
help people who have never connected or only connect sporadically would
do much to help our cause. We are widely regarded as unhelpful,
"difficult to get started" etc.. If we had a dollar for every person
that has shouted "OMG what is the damn command on Postgres?" it would
easily fund this development.

This is not about simplifying things. It is about being obvious. 

Light switches are usually at shoulder height next to a door. Our light
switches are 2 metres up, on the far side of the room. People are sick
of banging their knees on furniture while trying to grope for the light.
The light switch isn't so much hard to use, its just in the wrong place.
We must envisage what it is to be a person that doesn't know where the
switch is, or have forgotten. We don't need a programmable light switch
API, or a multi-function light remote control. Just a switch by all of
the doors.

(Oh, they're probably not called lights outside UK; room lamps maybe?)

-- 
 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] dividing money by money

[Kevin Grittner]

Tom Lane  wrote:
 
> The only way I'd be willing to label those things immutable was if
> we did something to lock down lc_monetary for the life of a
> database (ie, make it work more like lc_collate does now).  Which
> might be a good idea, but it's not how it works today.
 
Interesting.  In general, what is involved in locking something like
this down for the life of a database?
 
-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] psql auto-completion for multiple where clauses

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> Someone highlighed on IRC that after the first WHERE clause,
> autocomplete no longer works.
...
> SELECT * FROM tab_completion WHERE id = 2 AND s
...
> Is there any chance of improving this so it would work for more than 1
> WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
> all, but seems to be fine for ORDER BY.

No: there is only a small number of words that we go back through, 
so the above will not work as we cannot get back to the name of the table 
from the right side of the AND. The way to fix that is to redesign our 
tab-completion system such that it knows about a greater number of words, 
perhaps even the complete statement.

> SELECT * FROM tab_completion WHERE id = 2 AND b
>
> Since there is no column beginning with "b", it might be an idea to
> get it to match "bark bark" instead.  It might help alleviate what may
> be a gotcha for some.

This one is more doable, assuming we are really talking about:

SELECT * FROM tab_completion WHERE b

Keep in mind it will show up in a list if you do the following:

SELECT * FROM tab_completion WHERE 


- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201007161102
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkxAdOkACgkQvJuQZxSWSsiMpACgvheNYe35eXugYQrR3fZ7AYl2
ZWoAnAwzDPREKuxrJzZK45TpInUCh03w
=E6eG
-END PGP SIGNATURE-



-- 
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] dividing money by money

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> * The cast functions were marked immutable, which is wrong because
>> they depend on the setting of lc_monetary.  The right marking is
>> "stable".
 
> Is there any impact of the change to lc_monetary which would matter
> besides the number of decimal positions?  If that changes, isn't
> every money amount in the database instantly made incorrect?

Yeah, which is why I didn't feel that this was something that really
needed back-patching, even though the markings have been wrong since
the lc_monetary dependency was introduced.

> If so,
> I'm dubious that marking this as stable is worthwhile -- if someone
> is making a change like that, they will need to update all money
> amounts in the database; reindexing would be the least of their
> problems.  Or am I missing some other effect?

Well, whether people change the value in practice or not, it's still
wrong to mark the functions more optimistically than the rules say.
The only way I'd be willing to label those things immutable was if we
did something to lock down lc_monetary for the life of a database (ie,
make it work more like lc_collate does now).  Which might be a good
idea, but it's not how it works today.

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] psql auto-completion for multiple where clauses

[Thom Brown]

Someone highlighed on IRC that after the first WHERE clause,
autocomplete no longer works.

An example:

CREATE TABLE tab_completion (
id serial,
stuff text,
meow boolean
);

SELECT * FROM tab_completion WHERE id = 2 AND s

This would output a blank line.

Is there any chance of improving this so it would work for more than 1
WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
all, but seems to be fine for ORDER BY.

While we're at it, how about a smarter auto-complete for
quote-required column names (or whichever term I was supposed to use
just then):

CREATE TABLE tab_completion (
id serial,
stuff text,
"bark bark" boolean
);

SELECT * FROM tab_completion WHERE id = 2 AND b

Since there is no column beginning with "b", it might be an idea to
get it to match "bark bark" instead.  It might help alleviate what may
be a gotcha for some.

Thanks

Thom

-- 
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] dividing money by money

[Tom Lane]

Andy Balholm  writes:
> On Jul 15, 2010, at 7:25 PM, Tom Lane wrote:
>> * I didn't like this bit in cash_numeric():
>> 
>> result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint;
>> 
>> Not only is that unwarranted chumminess with the implementation of
>> numeric, it's flat-out wrong.  If the result isn't exactly the right
>> number of digits (say, it's 12.3399 instead of the desired 12.34)
>> this just hides the extra digits, it doesn't make the result correct.
>> The right way is to use numeric_round(), which not only sets the dscale
>> where we want it but rounds off any inaccuracy that might have crept in
>> from the division.

> Sorry about that. Is there documentation anywhere for backend
> functions and types?

Nothing at that level of detail, unfortunately, beyond the code itself.
If you'd read the comments near the head of numeric.c, maybe the mistake
would've been apparent to you, or maybe not.

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] dividing money by money

[Tom Lane]

"Kevin Grittner"  writes:
> Peter Eisentraut  wrote:
>> I didn't see any discussion about why this should return float8
>> rather than numeric.  It seems wrong to use float8 for this.
 
> That discussion took place several months ago on the -bugs list. 
> I'll paste some links from a quick search of the archives below. 
> Since multiplication of money is by float8 and not numeric, it
> ultimately seemed more consistent to me to have the results of
> division be float8.  I felt that as long as we had a cast between
> money and numeric, someone could always cast to numeric if they
> wanted that style of division.

Yeah.  The other argument that I found convincing was that if the
operator was defined to yield numeric, people might think that
the result was exact ... which of course it won't be, either way.
Choosing float8 helps to remind the user it's an approximate quotient.

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

[Jan Urbański]

Hi,

here's a review of the \sf and \ef [num] patch from
http://archives.postgresql.org/message-id/162867791003290927y3ca44051p80e697bc6b19d...@mail.gmail.com

== Formatting ==

The patch has some small tabs/spaces and whitespace  issues and it 
applies with some offsets, I ran pgindent and rebased against HEAD, 
attaching the resulting patch for your convenience.


== Functionality ==

The patch adds the following features:
 * \e file.txt num  ->  starts a editor for the current query buffer 
and puts the cursor on the [num] line
 * \ef func num -> starts a editor for a function and puts the cursor 
on the [num] line

 * \sf func -> shows a full CREATE FUNCTION statement for the function
 * \sf+ func -> the same, but with line numbers
 * \sf[+] func num -> the same, but only from line num onward

It only touches psql, so no performance or backend stability worries.

In my humble opinion, only the \sf[+] is interesting, because it gives 
you a copy/pasteable version of the function definition without opening 
up an editor, and I can find that useful (OTOH: you can set PSQL_EDITOR 
to cat and get the same effect with \ef... ok, just joking). Line 
numbers are an extra touch, personally it does not thrill me too much, 
but I've nothing against it.


The number variants of \e and \ef work by simply executing $EDITOR +num 
file. I tried with some editors that came to my mind, and not all of 
them support it (most do, though):


 * emacs and emacsclient work
 * vi works
 * nano works
 * pico works
 * mcedit works
 * kwrite does not work
 * kedit does not work

not sure what other people (or for instance Windows people) use. Apart 
from no universal support from editors, it does not save that many 
keystrokes - at most a couple. In the end you can usually easily jump to 
the line you want once you are inside your dream editor.


My recommendation would be to only integrate the \sf[+] part of the 
patch, which will have the additional benefit of making it much smaller 
and cleaner (will avoid the grotty splitting of the number from the 
function name, for instance). But I'm just another user out there, maybe 
others will find uses for the other cases.


I would personally not add the leading and trailing newlines to \sf 
output, but that's a question of taste.


Docs could use some small grammar fixes, but other than that they're fine.

== Code ==

In \sf code there just a strncmp, so this works:
\sfblablabla funcname

The error for an empty \sf is not great, it should probably look more like
\sf: missing required argument
following the examples of \pset, \copy or \prompt.

Why is lnptr always being passed as a pointer? Looks like a unnecessary 
complication and one more variable to care about. Can't we just pass lineno?


== End ==

Cheers,
Jan
*** doc/src/sgml/ref/psql-ref.sgml
--- /tmp/CUVdHd_psql-ref.sgml	2010-07-16 13:31:53.362662393 +0200
***
*** 1329,1335 
  
  

! \edit (or \e)  filename 
  
  
  
--- 1329,1335 
  
  

! \edit (or \e)  filename   linenumber 
  
  
  
***
*** 1359,1370 
  systems, notepad.exe on Windows systems.
  
  
  

  
  

! \ef  function_description 
  
  
  
--- 1359,1376 
  systems, notepad.exe on Windows systems.
  
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor.
+ 
  

  
  

! \ef  function_description   linenumber  
  
  
  
***
*** 1387,1392 
--- 1393,1405 
   If no function is specified, a blank CREATE FUNCTION
   template is presented for editing.
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor. It count lines from start of function body, not from
+ start of text.
+ 
  

  
***
*** 2106,2111 
--- 2119,2136 
  
  

+ \sf[+] function_description  linenumber  
+ 
+ 
+ 
+  This command fetches and shows the definition of the named function,
+  in the form of a CREATE OR REPLACE FUNCTION command.
+  If the form \sf+ is used, then lines are numbered.
+ 
+ 
+   
+ 
+   
  \t
  
  
***
*** 2113,2118 
--- 2138,2149 
  footer. This command is equivalent to \pset
  tuples_only and is provided for convenience.
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor.
+ 
  

  
*** src/bin/psql/command.c
--- /tmp/uM1Twe_command.c	2010-07-16 13:31:53.36075 +0200
***
*** 57,63 
  			 PsqlScanState scan_state,
  			

Re: [HACKERS] patch: to_string, to_array functions

[Brendan Jurd]

On 6 May 2010 04:42, Pavel Stehule  wrote:
> attached patch contains to_string and to_array functions. These
> functions are equivalent of array_to_string and string_to_array
> function with maybe more correct NULL handling.

Hi Pavel,

I am reviewing your patch for the commitfest.

Overall the patch looks good, although there were some bogus
whitespace changes in the patch and some messy punctuation/grammar in
some of the code comments.  I also thought it was worth mentioning in
the docs the default value for null_string is ''.  I made an attempt
to clean those items up and have attached a v2 of the patch.

Regarding the behaviour of the third argument (null_string), I was a
little surprised by the results when I passed in a NULL.

postgres=# select to_string(array['a', 'b', 'c', 'd'], '/', NULL);
 to_string
---

Now, if the array had some NULL elements in it, I could understand why
the resulting string would be NULL (because str || NULL is NULL), but
in this case there are no NULLs.  Why is the result NULL?  Surely it
should be 'a/b/c/d' regardless of how the third parameter is set?

In the reverse case:

postgres=# select to_array('a/b/c/d', '/', NULL);
 to_array
--

(1 row)

Again I find this a bit weird.  I have left the null_string NULL,
which means it is unknown.  It can't possibly match any value in the
string, so effectively passing in a NULL null_string should mean that
the user doesn't want any string items whatsoever to translate into
NULLs in the resulting array.  I would expect this call to return
{a,b,c,d}.

Cheers,
BJ
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 70dab53..2256d9c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9544,6 +9544,12 @@ SELECT NULLIF(value, '(none)') ...
 string_to_array
   
   
+to_array
+  
+  
+to_string
+  
+  
 unnest
   
 
@@ -9686,6 +9692,28 @@ SELECT NULLIF(value, '(none)') ...

 
  
+  to_array(text, text 
, text)
+ 
+
+text[]
+splits string into array elements using supplied delimiter and 
null string (defaults to the empty string)
+to_array('1,2,3,,5', ',')
+{1,2,3,4,NULL,5}
+   
+   
+
+ 
+  to_string(anyarray, 
text , text)
+ 
+
+text
+concatenates array elements using supplied delimiter and null 
string (defaults to the empty string)
+to_string(ARRAY[1, 2, 3, NULL, 5], ',', 
'*')
+1,2,3,*,5
+   
+   
+
+ 
   unnest(anyarray)
  
 
diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index 8852326..24a79e8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -487,3 +487,11 @@ COMMENT ON FUNCTION ts_debug(text) IS
 CREATE OR REPLACE FUNCTION
   pg_start_backup(label text, fast boolean DEFAULT false)
   RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
+
+CREATE OR REPLACE FUNCTION
+  to_string(v anyarray, fldsep text, null_string text DEFAULT '')
+  RETURNS text STRICT IMMUTABLE LANGUAGE internal AS 'to_string';
+
+CREATE OR REPLACE FUNCTION
+  to_array(inputstr text, fldsep text, null_string text DEFAULT '')
+  RETURNS text[] STRICT IMMUTABLE LANGUAGE internal AS 'to_array';
diff --git a/src/backend/utils/adt/array_userfuncs.c 
b/src/backend/utils/adt/array_userfuncs.c
index 00e9c54..7f88b82 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -407,9 +407,11 @@ ArrayType *
 create_singleton_array(FunctionCallInfo fcinfo,
   Oid element_type,
   Datum element,
+  bool isNull,
   int ndims)
 {
Datum   dvalues[1];
+   boolnulls[1];
int16   typlen;
booltypbyval;
chartypalign;
@@ -429,6 +431,7 @@ create_singleton_array(FunctionCallInfo fcinfo,
ndims, MAXDIM)));
 
dvalues[0] = element;
+   nulls[0] = isNull;
 
for (i = 0; i < ndims; i++)
{
@@ -462,7 +465,7 @@ create_singleton_array(FunctionCallInfo fcinfo,
typbyval = my_extra->typbyval;
typalign = my_extra->typalign;
 
-   return construct_md_array(dvalues, NULL, ndims, dims, lbs, element_type,
+   return construct_md_array(dvalues, nulls, ndims, dims, lbs, 
element_type,
  typlen, typbyval, 
typalign);
 }
 
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index af28c15..676fdc1 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -2965,16 +2965,22 @@ split_text(PG_FUNCTION_ARGS)
 }
 
 /*
- * text_to_array
- * parse input string

Re: [HACKERS] imessages up-date

[Markus Wanner]

Hi,

On 07/16/2010 04:01 PM, Kevin Grittner wrote:
> Since these two patches were posted before the commit fest started,
> and are prerequisites for six properly submitted patches, I'm going
> with the "spirit of the law" and saying it's OK to add them.  Does
> the application allow that?

Yes, it does. I just added those two (under miscellaneous).

Markus

-- 
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] dividing money by money

[Andy Balholm]

On Jul 15, 2010, at 7:25 PM, Tom Lane wrote:

> * I didn't like this bit in cash_numeric():
> 
>   result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint;
> 
> Not only is that unwarranted chumminess with the implementation of
> numeric, it's flat-out wrong.  If the result isn't exactly the right
> number of digits (say, it's 12.3399 instead of the desired 12.34)
> this just hides the extra digits, it doesn't make the result correct.
> The right way is to use numeric_round(), which not only sets the dscale
> where we want it but rounds off any inaccuracy that might have crept in
> from the division.

Sorry about that. Is there documentation anywhere for backend functions and 
types? I couldn't find any, so I just looked through numeric.h to see what 
looked like it might work. I didn't find numeric_round, since it's declared in 
builtins.h.
-- 
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] imessages up-date

[Kevin Grittner]

Markus Wanner  wrote:
> On 07/15/2010 10:37 PM, Alvaro Herrera wrote:
 
>> BTW I think this patch series makes sense, though I haven't
>> looked at it in detail.  I guess it means I'll have to have a
>> look at the IMessages stuff as well.
> 
> Yes, only after adding these patches to the commit fest, I
> realized that I'dd have to add the dynshmem and imessages patches
> for bgworker to be of any use.
 
> @Kevin: how do we proceed WRT the commit fest? Having bgworker in
> there, but not its dependencies (dynshmem and imessages) is what
> I'd call a conflict. OTOH, both of those patches have been
> published way before the commit fest started as well.
 
Since these two patches were posted before the commit fest started,
and are prerequisites for six properly submitted patches, I'm going
with the "spirit of the law" and saying it's OK to add them.  Does
the application allow that?
 
-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] dividing money by money

[Kevin Grittner]

Peter Eisentraut  wrote:
 
> I didn't see any discussion about why this should return float8
> rather than numeric.  It seems wrong to use float8 for this.
 
That discussion took place several months ago on the -bugs list. 
I'll paste some links from a quick search of the archives below. 
Since multiplication of money is by float8 and not numeric, it
ultimately seemed more consistent to me to have the results of
division be float8.  I felt that as long as we had a cast between
money and numeric, someone could always cast to numeric if they
wanted that style of division.
 
http://archives.postgresql.org/pgsql-bugs/2010-03/msg00233.php
http://archives.postgresql.org/pgsql-bugs/2010-03/msg00241.php
http://archives.postgresql.org/pgsql-bugs/2010-03/msg00244.php
http://archives.postgresql.org/pgsql-bugs/2010-03/msg00245.php
http://archives.postgresql.org/pgsql-bugs/2010-04/msg6.php
 
-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] ERROR: argument to pg_get_expr() must come from system catalogs

[Tom Lane]

Heikki Linnakangas  writes:
> On 13/07/10 21:36, Tom Lane wrote:
>> I wasn't terribly happy with that approach to begin with.  I think we
>> need to rethink.

> Do you want to go ahead with your plan of changing what's passed in 
> FuncInfo? I won't object if you want to do it, but I wouldn't feel 
> comfortable with backporting such big changes myself.

I will take a look at it, but not right away.  Since we have no
near-term plans for new minor releases, I don't think it's urgent.

> If we continue with the approach I took, we should implement the 
> suggestion to create a new data type for this in 9.1. That would be more 
> waterproof than the changes I made, if we introduce new ways to call 
> functions in the future.

Agreed, that seems like a better solution going forward than either of
the others.

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] dividing money by money

[Kevin Grittner]

Tom Lane  wrote:
 
> * I didn't like this bit in cash_numeric():
> 
>   result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint;
> 
> Not only is that unwarranted chumminess with the implementation of
> numeric, it's flat-out wrong.  If the result isn't exactly the
> right number of digits (say, it's 12.3399 instead of the
> desired 12.34) this just hides the extra digits, it doesn't make
> the result correct.  The right way is to use numeric_round(),
> which not only sets the dscale where we want it but rounds off any
> inaccuracy that might have crept in from the division.
 
Thanks.  Duly noted.
 
> * The cast functions were marked immutable, which is wrong because
> they depend on the setting of lc_monetary.  The right marking is
> "stable".
 
Is there any impact of the change to lc_monetary which would matter
besides the number of decimal positions?  If that changes, isn't
every money amount in the database instantly made incorrect?  If so,
I'm dubious that marking this as stable is worthwhile -- if someone
is making a change like that, they will need to update all money
amounts in the database; reindexing would be the least of their
problems.  Or am I missing some other effect?
 
-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] reducing NUMERIC size for 9.1

[Thom Brown]

On 16 July 2010 14:14, Brendan Jurd  wrote:
> On 16 July 2010 22:51, Richard Huxton  wrote:
>> On 16/07/10 13:44, Brendan Jurd wrote:>
>>> At this scale we should be seeing around 2 million bytes saved, but
>>> instead the tables are identical.  Is there some kind of disconnect in
>>> how the new short numeric is making it to the disk, or perhaps another
>>> effect interfering with my test?
>>
>> You've probably got rows being aligned to a 4-byte boundary. You're probably
>> not going to see any change unless you have a couple of 1-byte columns that
>> get placed after the numeric. If you went from 10 bytes down to 8, that
>> should be visible.
>
> Ah, thanks for the hint Richard.  I didn't see any change with two
> 1-byte columns after the numeric, but with four such columns I did
> finally see a difference.
>
> Test script:
>
> BEGIN;
>
> CREATE TEMP TABLE foo (a numeric, b bool, c bool, d bool, e bool);
>
> INSERT INTO foo (a, b, c, d, e)
> SELECT 0::numeric, false, true, i % 2 = 0, i % 2 = 1
> FROM generate_series(1, 100) i;
>
> SELECT pg_total_relation_size('foo'::regclass);
>
> ROLLBACK;
>
> Results:
>
> 8.4: 44326912
> HEAD with patch: 36290560
>
> That settles my concern and I'm happy to pass this along to a commiter.
>
> Cheers,
> BJ
>

Joy! :)  Nice patch Robert.

Thom

-- 
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] reducing NUMERIC size for 9.1

[Brendan Jurd]

On 16 July 2010 22:51, Richard Huxton  wrote:
> On 16/07/10 13:44, Brendan Jurd wrote:>
>> At this scale we should be seeing around 2 million bytes saved, but
>> instead the tables are identical.  Is there some kind of disconnect in
>> how the new short numeric is making it to the disk, or perhaps another
>> effect interfering with my test?
>
> You've probably got rows being aligned to a 4-byte boundary. You're probably
> not going to see any change unless you have a couple of 1-byte columns that
> get placed after the numeric. If you went from 10 bytes down to 8, that
> should be visible.

Ah, thanks for the hint Richard.  I didn't see any change with two
1-byte columns after the numeric, but with four such columns I did
finally see a difference.

Test script:

BEGIN;

CREATE TEMP TABLE foo (a numeric, b bool, c bool, d bool, e bool);

INSERT INTO foo (a, b, c, d, e)
SELECT 0::numeric, false, true, i % 2 = 0, i % 2 = 1
FROM generate_series(1, 100) i;

SELECT pg_total_relation_size('foo'::regclass);

ROLLBACK;

Results:

8.4: 44326912
HEAD with patch: 36290560

That settles my concern and I'm happy to pass this along to a commiter.

Cheers,
BJ

-- 
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] SHOW TABLES

[Thom Brown]

On 16 July 2010 13:49, Bruce Momjian  wrote:
> Bruce Momjian wrote:
>> Andrew Dunstan wrote:
>> >
>> >
>> > Bruce Momjian wrote:
>> > > I assume SHOW TABLES would only be useful for interactive terminal
>> > > sesssions, not for application code (which should use
>> > > information_schema), so what non-psql interactive terminal programs are
>> > > there?
>> > >
>> > >
>> >
>> > I think your assumption is questionable.
>> >
>> > Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings
>> > (for good or ill). That's why any suggestion that we should return
>> > anything other than a resultset seems like a really terrible idea to me.
>>
>> If they are writing an application, finding the query to show all tables
>> is the least of their problems.  I don't see how SHOW TABLE
>> significantly helps in that case, except make things 0.001% easier,
>> while creating duplicate functionality in Postgres.
>
> What would be interesting is if SHOW TABLES was psql-only, and showed
> the output in multi-column format, like ls -C.  That would a a new
> display format and new useful functionality.
>
> --

The problem is people are stating different requirements.

- to make it easy for new users of psql
- to simplify fetching basic database information from any client application
- to ease transition between MySQL and PostgreSQL

The outcome would depend on what's needed.  Like providing a
pg_user_tables view for people to select from, using LIST TABLES as a
more meaningful alternative to SHOW TABLES, providing hints for MySQL
users using psql... etc.

Thom

-- 
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] SHOW TABLES

[Pavel Stehule]

2010/7/16 Bruce Momjian :
> Andrew Dunstan wrote:
>>
>>
>> Bruce Momjian wrote:
>> > I assume SHOW TABLES would only be useful for interactive terminal
>> > sesssions, not for application code (which should use
>> > information_schema), so what non-psql interactive terminal programs are
>> > there?
>> >
>> >
>>
>> I think your assumption is questionable.
>>
>> Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings
>> (for good or ill). That's why any suggestion that we should return
>> anything other than a resultset seems like a really terrible idea to me.
>
> If they are writing an application, finding the query to show all tables
> is the least of their problems.  I don't see how SHOW TABLE
> significantly helps in that case, except make things 0.001% easier,
> while creating duplicate functionality in Postgres.

I am thinking same too. Maybe somebody will be happy, bacause they can
to write SHOW TABLES, but they will be unsatisfied when will try to
write SHOW TABLES WHERE ... so only full support of MySQL syntax has
sense. Some only text version of SHOW TABLES command isn't sense for
me - it can do only more problems with incompatibility.

Still I thinking about top level hook - so these and similar commands
can be implemented inside external modules.

I have a different opinion on DESCRIBE command. This really can help.
But it must not be a command. "describe" function is enought -

select describe(oid);
select describe_table(name);
...

Regards

Pavel Stehule

>
> --
>  Bruce Momjian          http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + None of us is going to be here forever. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

Bruce Momjian wrote:
> Andrew Dunstan wrote:
> > 
> > 
> > Bruce Momjian wrote:
> > > I assume SHOW TABLES would only be useful for interactive terminal
> > > sesssions, not for application code (which should use
> > > information_schema), so what non-psql interactive terminal programs are
> > > there?
> > >
> > >   
> > 
> > I think your assumption is questionable.
> > 
> > Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings 
> > (for good or ill). That's why any suggestion that we should return 
> > anything other than a resultset seems like a really terrible idea to me.
> 
> If they are writing an application, finding the query to show all tables
> is the least of their problems.  I don't see how SHOW TABLE
> significantly helps in that case, except make things 0.001% easier,
> while creating duplicate functionality in Postgres.

What would be interesting is if SHOW TABLES was psql-only, and showed
the output in multi-column format, like ls -C.  That would a a new
display format and new useful functionality.

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

  + None of us is going to be here forever. +

-- 
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] reducing NUMERIC size for 9.1

[Richard Huxton]

On 16/07/10 13:44, Brendan Jurd wrote:


pg_column_size() did return the results I was expecting.
pg_column_size(0::numeric) is 8 bytes on 8.4 and it's 6 bytes on HEAD
with your patch.



At this scale we should be seeing around 2 million bytes saved, but
instead the tables are identical.  Is there some kind of disconnect in
how the new short numeric is making it to the disk, or perhaps another
effect interfering with my test?


You've probably got rows being aligned to a 4-byte boundary. You're 
probably not going to see any change unless you have a couple of 1-byte 
columns that get placed after the numeric. If you went from 10 bytes 
down to 8, that should be visible.


--
  Richard Huxton
  Archonet Ltd

--
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] SHOW TABLES

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Bruce Momjian wrote:
> > I assume SHOW TABLES would only be useful for interactive terminal
> > sesssions, not for application code (which should use
> > information_schema), so what non-psql interactive terminal programs are
> > there?
> >
> >   
> 
> I think your assumption is questionable.
> 
> Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings 
> (for good or ill). That's why any suggestion that we should return 
> anything other than a resultset seems like a really terrible idea to me.

If they are writing an application, finding the query to show all tables
is the least of their problems.  I don't see how SHOW TABLE
significantly helps in that case, except make things 0.001% easier,
while creating duplicate functionality in Postgres.

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

  + None of us is going to be here forever. +

-- 
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] reducing NUMERIC size for 9.1

[Brendan Jurd]

On 16 July 2010 03:47, Robert Haas  wrote:
> On Jul 15, 2010, at 11:58 AM, Brendan Jurd  wrote:
>> I dropped one thousand numerics with value zero into a table and
>> checked the on-disk size of the relation with your patch and on a
>> stock 8.4 instance.  In both cases the result was exactly the same.
>>
>> Shouldn't the table be smaller with your patch?  Or is there something
>> wrong with my test?
>
> Well, on that test, you'll save only 2000 bytes, which is less than a full 
> block, so there's no guarantee the difference would be noticeable at the 
> relation level.  Scale it up by a factor of 10 and the difference should be 
> measurable.
>
> You might also look at testing with pg_column_size().
>

pg_column_size() did return the results I was expecting.
pg_column_size(0::numeric) is 8 bytes on 8.4 and it's 6 bytes on HEAD
with your patch.

However, even with 1 million rows of 0::numeric in my test table,
there was no difference at all in the on-disk relation size (36290560
with 36249600 in the table and 32768 in the fsm).

At this scale we should be seeing around 2 million bytes saved, but
instead the tables are identical.  Is there some kind of disconnect in
how the new short numeric is making it to the disk, or perhaps another
effect interfering with my test?

Cheers,
BJ

-- 
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] SHOW TABLES

[Markus Wanner]

> I have to agree with Simon here. \d is ridiculous for the common user.

+1

Regards

Markus

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