date:20111110

On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost  wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Stephen Frost  writes:
>> > A LOT of catalog bulk..?  Am I missing something here?
>>
>> What I'm missing is what actual benefit we get from spending the extra
>> space.  (No, I don't believe that changing the defaults is something
>> that users commonly will or should do; it's certainly not the case to
>> optimize for.)
>
> Typical database *users*?  No.  A DBA or SA?  Certainly, and we already
> provide a way to do that, in part.  Supporting it for the 'default
> defaults' would be nice as would support for default privileges for
> schemas (rather than just objects that go *in* schemas).
>
> Certainly a big one that people get caught by is our default of execute
> to public on functions..  Most of our privileges are set up as minimal
> access to others, functions are an oddity in that regard.  Rather than
> fight the battle of what the default *should* be for functions, we could
> just give the DBA the ability to configure it for their database.

Sure, let's do.  But that hardly means that we need to store useless
catalog records in every database with the DBA doesn't do that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] type privileges and default privileges

2011-11-10 Thread Stephen Frost

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > A LOT of catalog bulk..?  Am I missing something here?
> 
> What I'm missing is what actual benefit we get from spending the extra
> space.  (No, I don't believe that changing the defaults is something
> that users commonly will or should do; it's certainly not the case to
> optimize for.)

Typical database *users*?  No.  A DBA or SA?  Certainly, and we already
provide a way to do that, in part.  Supporting it for the 'default
defaults' would be nice as would support for default privileges for
schemas (rather than just objects that go *in* schemas).

Certainly a big one that people get caught by is our default of execute
to public on functions..  Most of our privileges are set up as minimal
access to others, functions are an oddity in that regard.  Rather than
fight the battle of what the default *should* be for functions, we could
just give the DBA the ability to configure it for their database.

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [HACKERS] type privileges and default privileges

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> To actually get rid of acldefault, we'd have to do that not only for
>> types but for all objects with ACLs.  That's a LOT of catalog bulk,
>> and like Robert I'm not seeing much benefit.  It's not unreasonable
>> to want the typical case to be small and fast.

> A LOT of catalog bulk..?  Am I missing something here?

What I'm missing is what actual benefit we get from spending the extra
space.  (No, I don't believe that changing the defaults is something
that users commonly will or should do; it's certainly not the case to
optimize for.)

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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

2011-11-10 Thread Alvaro Herrera


Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011:
> 
> On 10 November 2011 23:56, Thom Brown  wrote:

> > The dump correctly contains:
> >
> > CREATE TABLE a (
> >    num integer,
> >    CONSTRAINT meow CHECK ((num < 20)) NOT VALID
> > );
> 
> Actually I mean incorrectly contains, because the constraint needs
> adding after the data insertion, not as part of the create table
> statement.

Interesting, thanks -- I'll look into it.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] type privileges and default privileges

2011-11-10 Thread Stephen Frost

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Robert Haas  writes:
> > On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut  wrote:
> >> No, I'm pondering having pg_default_acl initialized so that newly
> >> created types have explicit USAGE privileges in their typacl column, so
> >> acldefault() wouldn't be needed.  (And builtin types would have their
> >> typacl initialized analogously.)  I suppose this is how we might have
> >> done it if we had invented ALTER DEFAULT PRIVILEGES first.
> 
> > I'm not convinced.  That's a lot of catalog clutter for no benefit.
> 
> To actually get rid of acldefault, we'd have to do that not only for
> types but for all objects with ACLs.  That's a LOT of catalog bulk,
> and like Robert I'm not seeing much benefit.  It's not unreasonable
> to want the typical case to be small and fast.

A LOT of catalog bulk..?  Am I missing something here?  Aren't we just
talking about 16 bytes, or so, per catalog entry?  On one of my larger
databases, with ~250k catalog records, we're talking about 4MB.  That
catalog is currently 130MB.  An empty database has, what, 5000 objects?
That'd be ~80KB?

My thought would be to have a place where we keep what the 'default
default' ACL is for each object type (yes, with the idea that it could
be modified, I'd see that as a nice feature and I don't think we need
to worry, as much, about performance of DDL..) and then we populate the
ACL for each created object with the 'default default' ACL.  Perhaps we
integrate this with the existing default permissions system..  This
isn't a new idea to me, to be honest..

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [HACKERS] Syntax for partitioning

2011-11-10 Thread Daniel Farina

On Thu, Nov 10, 2011 at 1:19 PM, Dimitri Fontaine
 wrote:
> Now the aim would be to be able to implement the operation you describe
> by using the new segment map, which is an index pointing to sequential
> ranges of on-disk blocks where the data is known to share a common key
> range over the columns you're segmenting on.  I would imagine this SQL:
>
>  TRUNCATE foo WHERE year < 2009;
>
> As the on-disk location of the data that qualify this WHERE clause is
> known, it could be possible to (predicate) lock it and bulk remove it,
> unlinking whole segments (1GB) at a time when relevant.

I am basically in agreement with you.  After wanting better
partitioning (Oracle-style) in Postgres for some time just to be free
of the mechanically painful table-inheritance version, I have come
around to thinking it's basically a bad idea, but one that with a
little bit of finessing can be made a good idea.

The reason I have started to think this is because of an old feature
that works very well: CREATE INDEX.  In spite of what people might
think, I think it's pretty clear that CREATE INDEX is not DDL: it's
actually physical advice to the system.  I have seen the
fourth-generation-language promise delivered upon quite a few times in
production, now: we witness an access pattern that becomes
problematic, we run CREATE INDEX CONCURRENTLY, the problem is solved
without any change to the application, and the index definition is
backported to our application bootstrapping process.  It would be hard
for me to understate how valuable this has been to avoid both
premature optimization and excessive panic when dealing with change.

Similar to the overall project stance on query hints, I don't think
Postgres should retreat on its ground from being a 4GL system.  I
think both indexes and a hypothetical partitioning feature should be
clearly isolated as directives to the system about how to physically
organize and access data, and any partitioning feature that creates
new relation namespace entries and expects you to manipulate them to
gain the benefits seems like extra, non-desirable surface area to me.

I think this becomes especially apparent once one considers on-line
repartitioning (I am exposing a bias here, but any feature in Postgres
that cannot be done concurrently -- like VACUUM FULL -- is very
dangerous to both me and my customers, whereas it may not be useless
or dangerous to a build-your-own data warehouse).  It feels like it
would be desirable to have the physical partitions exist in an
inconsistent-state whereby they are being brought into alignment with
the newly desired physical description.

Finally, I think a legitimate objection to this inclination is that it
can be really easy to issue a DELETE that is usually fast, but when
any mistake or change creeps in becomes very slow: I have heard from
some friends making heavy use of table partitioning via inheritance
that one of the problems is not quite exactly matching the table
constraint, and then hosing their hardware. As a result, they mangle
partitions explicitly in the application to prevent foot-gunning.
That's clearly lame (and they know it), but I think may indicate a
need to instead allow for some kind of physical-access-method
assertion checking quite apart from the logical content of the query
that can deliver a clear, crisp error to application developers if a
preferred access pattern is not usable.  My experience suggests that
while solving problems is good, turning problems into flat-out errors
is *nearly* as good, and worth some more investigation.

-- 
fdr

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

Re: [HACKERS] LOCK_DEBUG is busted

Robert Haas  writes:
> ...  What I think it's mostly doing at this point is making it
> more difficult to make further changes - you do whatever you want to
> do, and then you have to go figure out what to do about the crazy
> LOCK_DEBUG stuff that no one uses.

[ shrug... ]  If you're sufficiently exercised about it to take it
out entirely, I won't stand in the way.  I have not found it to be
an impediment myself, though.

regards, tom lane

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

Re: [HACKERS] proposal : backend startup hook / after logon trigger

2011-11-10 Thread Euler Taveira de Oliveira

On 10-11-2011 21:12, Tomas Vondra wrote:
> I occasionally need to perform some action whenever a user connects, and
> there's nothing like an "AFTER LOGON" trigger (available in some other
> databases).
> 
Are you proposing an on-logon hook or an on-connect trigger? It is two
separate things. The former can't solve some tasks (e.g. execute whatever pl
code) and the latter can't be implemented with a simple hook (you will have to
propose a syntax and offer some machinery to execute the pl code).

Of course, if you want to propose any of these ideas, keep in mind that a
symmetric functionality (e.g. on-logoff hook or on-disconnect trigger)  should
be implemented too.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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

Re: [HACKERS] LOCK_DEBUG is busted

On Thu, Nov 10, 2011 at 5:04 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> It's possible to compile the source tree with LOCK_DEBUG defined, but
>> the resulting postgres promptly dumps core, due to the fact that
>> user_lockmethod doesn't supply any value for trace_flag; thus, the
>> first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer.
>> This is the result of the following commit:
>
>> commit 0180bd6180511875db046bf8ddcaa633a2952dfd
>
> +1 for just reverting that commit.  I'm not sure how much use the
> LOCK_DEBUG infrastructure has in exactly its current form, but I can
> certainly imagine wanting to use it or some variant of it to debug
> tough problems.  If it's gone entirely, people would have to reinvent
> most of it for that type of debugging.  On the other side of the coin,
> I don't have a clear enough use-case for it to want to spend time
> right now on redesigning it, nor a clear idea of exactly what changes
> might make it more useful.  So I think we should just revert and
> not spend additional effort now.

I don't feel like it accomplishes much of anything that can't be
trivially accomplished by throwing in a couple of ad-hoc elog() calls
wherever you happen to want them.  I experimented with this when
developing the fastlock patches and found it didn't tell me what I
wanted to know, so just stuck in debugging code in the places that
were relevant to my patch's then-current problems.  Once those bugs
were fixed, I took the debugging code back out.  I think the author of
this code did pretty much the same thing, but then developed the
pretension that the particular places he stuck the elog() calls in
would be generally relevant, which I don't believe to be the case.  Or
maybe they were relevant at one time, but this code has been with us
for an awfully long time, and I think it's considerably outlived its
usefulness.  What I think it's mostly doing at this point is making it
more difficult to make further changes - you do whatever you want to
do, and then you have to go figure out what to do about the crazy
LOCK_DEBUG stuff that no one uses.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] proposal: psql concise mode

2011-11-10 Thread Dickson S. Guedes

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10-11-2011 21:42, Josh Kupershmidt wrote:
> On Thu, Nov 10, 2011 at 3:41 PM, Bruce Momjian  wrote:
>> Have you tried \d+ with this psql mode:
>>
>>\pset format wrapped
>>
>> It wraps the data so it fits on the screen --- it is my default in my
>> .psqlrc.
> 
> I think that's one of the many psql features I haven't experimented
> with, thanks for the suggestion. It looks OK for some things, but I
> find the column-wrapping behavior can be rather illegible, e.g.
> 
> create table test (
>   some_column_name serial PRIMARY KEY,
>   another_column_name integer NOT NULL,
>   another_col integer, username text
> );
> 
> tmp=# \d+ test
>   Table "public.test"
>  Column |  Type   |  Modifiers   | Storage | Stats target | 
> Description
> +-+--+-+--+-
>  some_column_na.| integer | not null def.| plain   |  |
> .me | |.ault nextval.| |  |
> | |.('test_some_.| |  |
> | |.column_name_.| |  |
> | |.seq'::regcla.| |  |
> | |.ss)  | |  |
>  another_column.| integer | not null | plain   |  |
> ._name  | |  | |  |
>  another_col| integer |  | plain   |  |
>  username   | text|  | extende.|  |
> | |  |.d   |  |
> 
> 
> 
> That wrapping is pretty ugly, and the culprit is all the wasted
> horizontal space for "Stats Target" and "Description" in this case
> (and probably for many users, who never set either column modifier).
> That output might be much nicer if, instead of "Modifiers", "Column",
> and "Storage" getting squeezed, the empty "Stats Target" and
> "Description" column headers got squeezed instead, giving the
> populated columns more horizontal space.

I'm not convinced yet, because a single comment in only one column is
enough to show the "Description"'s column again.

IMHO it seems to me that the only gain of this new proposed behaviour
only happens when there is no especial information about the columns,
like comments or stat target, and I don't know if this is a common
situation.

[]s
- --
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
http://github.net/guedes - twitter: @guediz
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJOvGpeAAoJEBa5zL7BI5C7V6cH/3chliawlBO0Sf0HB2DSmHeK
AyfA+LPiKRa9Egnlt7jHIAqdiplVkfUkaP8P2L2OryexStb8eXtAWeRJjHdR11di
X15M6551vHf7fqwmCpHtNebACXDdrQefHuw9MFtPYe4jaJwy1kU7IyLQcpRjNA0s
mvPJKrH08WqdFIw0DgiZi1+EFZE2Swr/zAKbNU2snGhKA0w2juHpoBOHmlfxmXQq
Z2zWvins8nqG1lpAhhD1QZzImGpWf/W7hiXDoP2BGo9wYjU38obbVdZJHNAey75B
9C4f75vQH4MRGy/wWYEPxttLoBerQaVedfEFPyHIOoqUOpIMJeLLqbk6mY5AIDM=
=5Oez
-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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

2011-11-10 Thread Thom Brown

On 10 November 2011 23:56, Thom Brown  wrote:
> On 18 July 2011 02:46, Alvaro Herrera  wrote:
>> Excerpts from Robert Haas's message of dom jul 17 20:36:49 -0400 2011:
>>
>>> > Does git allow for additional commit fields? That would allow for easy 
>>> > tracking without much additional burden on committers.
>>>
>>> I mean, there's git notes, but that's not exactly what we're looking
>>> for here, and I don't see how it would easy the burden on committers
>>> anyway, and it doesn't solve the problem of not being able to change
>>> things after the fact.
>>
>> Eh, git notes *can* be changed after the fact, and are *not* append
>> only.  And as the committer who started this discussion in the first
>> place, I don't have any problem with having to edit them separately from
>> the commit message, which is a tiny portion of the work involved in
>> figuring out the patch, anyway.
>>
>> What's not clear to me, is whether they are sent to the remote when you
>> invoke git push.  I'm not clear on whether this needing a separate
>> command or more arguments to push, or it's just not possible.
>>
>>> I think this is a clear-cut case of needing some sort of web
>>> application to manage this.  I'd even be willing to help fill in the
>>> relevant info.  But I'm not going to write it myself...
>>
>> Having a web app would work for me, but a larger job than just using git
>> notes.  So if the notes really work, +1 to them from me.
>
> I've only just noticed that this still doesn't work for me:
>
> test6=# CREATE TABLE a (num INT);
> CREATE TABLE
> test6=# INSERT INTO a (num) VALUES (90);
> INSERT 0 1
> test6=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num < 20) NOT VALID;
> ALTER TABLE
> test6=# \q
> toucan:~ thom$ createdb test7
> toucan:~ thom$ pg_dump -f /tmp/test.sql test6
> toucan:~ thom$ psql test7 < /tmp/test.sql
>
> SET
> SET
> SET
> SET
> SET
> CREATE EXTENSION
> COMMENT
> SET
> SET
> SET
> CREATE TABLE
> ALTER TABLE
> ERROR:  new row for relation "a" violates check constraint "meow"
> CONTEXT:  COPY a, line 1: "90"
> STATEMENT:  COPY a (num) FROM stdin;
> ERROR:  new row for relation "a" violates check constraint "meow"
> CONTEXT:  COPY a, line 1: "90"
> REVOKE
> REVOKE
> GRANT
> GRANT
>
> The dump correctly contains:
>
> CREATE TABLE a (
>    num integer,
>    CONSTRAINT meow CHECK ((num < 20)) NOT VALID
> );

Actually I mean incorrectly contains, because the constraint needs
adding after the data insertion, not as part of the create table
statement.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] proposal: psql concise mode

2011-11-10 Thread Josh Kupershmidt

On Thu, Nov 10, 2011 at 6:12 PM, Tom Lane  wrote:
>> As I suggested, many more unexpected failures (e.g. \dnS+) pop up when
>> talking to a 7.3 server. It's not a big deal, but it'd be nice if we
>> could instead error out with a "sorry, we're too lazy to try to
>> support 7.3" on the meta-commands which fail thusly, and make the
>> various "else" clauses more explicit about just how far back their
>> support really goes.
>
> Probably not worth the trouble ... how many pre-7.4 servers are still in
> the wild, and of those, how many might somebody try to talk to with a
> modern psql?
>
> The more realistic direction of future change, I think, is that we move
> up the cutoff version so we can take out some code, rather than add
> more.  At the moment I'd find it a hard sell to drop support for 8.1 or
> later; so maybe there's not enough removable code to make it worth any
> effort.  But in a few more years it'd be worth doing.

I am 100% on board with dropping support for such old servers whenever
feasible, so as to cut down on the cruft in psql -- that's the only
reason I cared to go poking at this at all. I would suggest we bump
the minimum supported server version for psql up to 8.0 at some point
in the not-too-distant future, perhaps even for 9.2.

> What *would* be worth doing today, IMO, is ripping out pg_dump's support
> for servers older than 7.3 or 7.4; in particular getting rid of its
> kluges for server versions without pg_depend info.

Yeah, that was another can of worms I had in the back of my mind. I
think there's a good case for maintaining longer backwards
compatibility in pg_dump vs. psql, to help people upgrade an ancient
server to a modern one. But certainly, anything older than 7.3 or 7.4
is pushing the boundaries in terms of being supported.

Jsoh

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

Re: [HACKERS] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

2011-11-10 Thread Thom Brown

On 18 July 2011 02:46, Alvaro Herrera  wrote:
> Excerpts from Robert Haas's message of dom jul 17 20:36:49 -0400 2011:
>
>> > Does git allow for additional commit fields? That would allow for easy 
>> > tracking without much additional burden on committers.
>>
>> I mean, there's git notes, but that's not exactly what we're looking
>> for here, and I don't see how it would easy the burden on committers
>> anyway, and it doesn't solve the problem of not being able to change
>> things after the fact.
>
> Eh, git notes *can* be changed after the fact, and are *not* append
> only.  And as the committer who started this discussion in the first
> place, I don't have any problem with having to edit them separately from
> the commit message, which is a tiny portion of the work involved in
> figuring out the patch, anyway.
>
> What's not clear to me, is whether they are sent to the remote when you
> invoke git push.  I'm not clear on whether this needing a separate
> command or more arguments to push, or it's just not possible.
>
>> I think this is a clear-cut case of needing some sort of web
>> application to manage this.  I'd even be willing to help fill in the
>> relevant info.  But I'm not going to write it myself...
>
> Having a web app would work for me, but a larger job than just using git
> notes.  So if the notes really work, +1 to them from me.

I've only just noticed that this still doesn't work for me:

test6=# CREATE TABLE a (num INT);
CREATE TABLE
test6=# INSERT INTO a (num) VALUES (90);
INSERT 0 1
test6=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num < 20) NOT VALID;
ALTER TABLE
test6=# \q
toucan:~ thom$ createdb test7
toucan:~ thom$ pg_dump -f /tmp/test.sql test6
toucan:~ thom$ psql test7 < /tmp/test.sql

SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
ERROR:  new row for relation "a" violates check constraint "meow"
CONTEXT:  COPY a, line 1: "90"
STATEMENT:  COPY a (num) FROM stdin;
ERROR:  new row for relation "a" violates check constraint "meow"
CONTEXT:  COPY a, line 1: "90"
REVOKE
REVOKE
GRANT
GRANT

The dump correctly contains:

CREATE TABLE a (
num integer,
CONSTRAINT meow CHECK ((num < 20)) NOT VALID
);

And the COPY command is:

COPY a (num) FROM stdin;
90
\.

So this is broken.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] psql expanded auto

2011-11-10 Thread Noah Misch

On Tue, Nov 08, 2011 at 06:36:52AM +0200, Peter Eisentraut wrote:
> Here is an updated patch that addresses all the issues you pointed out.

Looks ready to me.  Thanks.

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL

Andrew Dunstan  writes:
> On 11/10/2011 04:59 PM, Tom Lane wrote:
>> Nonetheless, it's solving the wrong problem.  Any program that is being
>> written today to read EXPLAIN output should be written to read one of
>> the machine-readable formats.

> Umm, it *does* handle all the formats:

The point I'm trying to make is that translating the parts that handle
text into another language seems like wasted effort.  depesz has a
specific problem to solve, namely wanting to accept emailed input from
users who are likely to send any format; and that requires him to expend
a lot of effort that is not likely to be necessary in any other setting.

regards, tom lane

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

Re: [HACKERS] proposal: psql concise mode

2011-11-10 Thread Josh Kupershmidt

On Thu, Nov 10, 2011 at 3:41 PM, Bruce Momjian  wrote:
> Have you tried \d+ with this psql mode:
>
>        \pset format wrapped
>
> It wraps the data so it fits on the screen --- it is my default in my
> .psqlrc.

I think that's one of the many psql features I haven't experimented
with, thanks for the suggestion. It looks OK for some things, but I
find the column-wrapping behavior can be rather illegible, e.g.

create table test (
  some_column_name serial PRIMARY KEY,
  another_column_name integer NOT NULL,
  another_col integer, username text
);

tmp=# \d+ test
  Table "public.test"
 Column |  Type   |  Modifiers   | Storage | Stats target | Description
+-+--+-+--+-
 some_column_na.| integer | not null def.| plain   |  |
.me | |.ault nextval.| |  |
| |.('test_some_.| |  |
| |.column_name_.| |  |
| |.seq'::regcla.| |  |
| |.ss)  | |  |
 another_column.| integer | not null | plain   |  |
._name  | |  | |  |
 another_col| integer |  | plain   |  |
 username   | text|  | extende.|  |
| |  |.d   |  |

That wrapping is pretty ugly, and the culprit is all the wasted
horizontal space for "Stats Target" and "Description" in this case
(and probably for many users, who never set either column modifier).
That output might be much nicer if, instead of "Modifiers", "Column",
and "Storage" getting squeezed, the empty "Stats Target" and
"Description" column headers got squeezed instead, giving the
populated columns more horizontal space.

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL




On 11/10/2011 05:26 PM, Greg Smith wrote:
  I know some of the earlier versions of XML EXPLAIN included a "DTD" 
option to output that, but I don't see that in the committed code.  
I'm not sure where that is at actually; it's a good question.


The only reference to doing this I found was Andrew's blog:  
http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html 
where he talks about there being a RELAXNG specification for the XML 
output.  I can't find where that came from either.  Andrew?



I created one at some stage, but I it's rather bitrotted. Essentially I 
think we decided that we were going to be pretty free to whack around 
the format and having a spec wasn't going to be terribly helpful.


cheers

andrew

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL




On 11/10/2011 04:59 PM, Tom Lane wrote:

Andrew Dunstan  writes:


Pg--Explain is extremely well written, and should be easily translatable
to Java if you really need to. The whole thing is less than 2000 lines,
and a large part of that is comments.

Nonetheless, it's solving the wrong problem.  Any program that is being
written today to read EXPLAIN output should be written to read one of
the machine-readable formats.




Umm, it *does* handle all the formats:

   $ wc -l ../Pg--Explain/lib/Pg/Explain.pm ../Pg--Explain/lib/Pg/Explain/*
  248 ../Pg--Explain/lib/Pg/Explain.pm
   75 ../Pg--Explain/lib/Pg/Explain/FromJSON.pm
  182 ../Pg--Explain/lib/Pg/Explain/From.pm
  202 ../Pg--Explain/lib/Pg/Explain/FromText.pm
  109 ../Pg--Explain/lib/Pg/Explain/FromXML.pm
   77 ../Pg--Explain/lib/Pg/Explain/FromYAML.pm
  785 ../Pg--Explain/lib/Pg/Explain/Node.pm
  292 ../Pg--Explain/lib/Pg/Explain/StringAnonymizer.pm
 1970 total


One of the obvious reasons for handling text is to deal with old servers 
before we had machine readable output.


cheers

andrew

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

[HACKERS] proposal : backend startup hook / after logon trigger

2011-11-10 Thread Tomas Vondra

Hi,

I occasionally need to perform some action whenever a user connects, and
there's nothing like an "AFTER LOGON" trigger (available in some other
databases).

Is there any particular reason why there's not a "backend start hook",
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).

This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?

Obviously this is a bit low-level approach, as most of the time the
triggers are implemented in a PL. But who says you can't call a PL from
the C module ...

Tomas
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
new file mode 100644
index 976a832..35f1926
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*** int max_stack_depth = 100;
*** 104,110 
  /* wait N seconds to allow attach from a debugger */
  int   PostAuthDelay = 0;
  
! 
  
  /* 
   *private variables
--- 104,110 
  /* wait N seconds to allow attach from a debugger */
  int   PostAuthDelay = 0;
  
! backend_startup_hook_type backend_startup_hook = NULL;
  
  /* 
   *private variables
*** PostgresMain(int argc, char *argv[], con
*** 3750,3755 
--- 3750,3769 
if (!ignore_till_sync)
send_ready_for_query = true;/* initially, or after error */
  
+   if (backend_startup_hook != NULL)
+   {
+   PG_TRY();
+   {
+   backend_startup_hook(MyProcPid, MyDatabaseId, dbname, 
username);
+   }
+   PG_CATCH();
+   {
+   elog(FATAL, "Error calling after-logon trigger");
+   proc_exit(0);
+   }
+   PG_END_TRY();
+   }
+ 
/*
 * Non-error queries loop here.
 */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
new file mode 100644
index 9d19417..3971268
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*** extern bool is_authenticated_user_replic
*** 388,391 
--- 388,396 
  extern bool BackupInProgress(void);
  extern void CancelBackup(void);
  
+ typedef void (*backend_startup_hook_type) (pid_t pid, Oid databaseOid,
+const char * dbname, const char * 
username);
+ 
+ extern backend_startup_hook_type backend_startup_hook;
+ 
  #endif   /* MISCADMIN_H */
#include 
#include 
#include 
#include 
#include 
#include 

#include "postgres.h"
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/numeric.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "catalog/pg_type.h"

#include "miscadmin.h"

static backend_startup_hook_type prev_backend_startup_hook = NULL;

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

void _PG_init(void);
void _PG_fini(void);

static
void my_startup_hook (pid_t pid, Oid databaseOid,
  const char * dbname, const char * username);

/*
 * Module load callback
 */
void
_PG_init(void)
{

	prev_backend_startup_hook = backend_startup_hook;
	backend_startup_hook = &my_startup_hook;

}

void
_PG_fini(void)
{

	backend_startup_hook = prev_backend_startup_hook;

}



static
void my_startup_hook (pid_t pid, Oid databaseOid,
  const char * dbname, const char * username) {
	if (! strcmp(dbname, "testdb")) {
		elog(ERROR, "startup hook is failing");
	} else {
		elog(NOTICE, "startup hook finished OK");
	}
}
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: psql concise mode

Josh Kupershmidt  writes:
> FWIW, I just played around with 7.4 and 7.3 servers. (I had some bad
> memories of the older tarballs not building, but that must have been
> only on OS X -- I can build at least back to 7.3 on this Ubuntu 11.04
> machine.)

> Most meta-commands worked alright on 7.4, or at least failed
> gracefully. The ones I saw which failed unexpectedly were \sf and \ef,
> which complained:
>   ERROR:  function pg_catalog.pg_get_functiondef(integer) does not exist

> I think we need a server version check for these two meta-commands,
> unless someone cares to make them work on < 8.4, trivial patch
> attached.

Agreed, we should check that.  Will commit.

> As I suggested, many more unexpected failures (e.g. \dnS+) pop up when
> talking to a 7.3 server. It's not a big deal, but it'd be nice if we
> could instead error out with a "sorry, we're too lazy to try to
> support 7.3" on the meta-commands which fail thusly, and make the
> various "else" clauses more explicit about just how far back their
> support really goes.

Probably not worth the trouble ... how many pre-7.4 servers are still in
the wild, and of those, how many might somebody try to talk to with a
modern psql?

The more realistic direction of future change, I think, is that we move
up the cutoff version so we can take out some code, rather than add
more.  At the moment I'd find it a hard sell to drop support for 8.1 or
later; so maybe there's not enough removable code to make it worth any
effort.  But in a few more years it'd be worth doing.

What *would* be worth doing today, IMO, is ripping out pg_dump's support
for servers older than 7.3 or 7.4; in particular getting rid of its
kluges for server versions without pg_depend info.

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] LOCK_DEBUG is busted

On Thu, Nov 10, 2011 at 5:07 PM, Bruce Momjian  wrote:
> Tom Lane wrote:
>> Robert Haas  writes:
>> > It's possible to compile the source tree with LOCK_DEBUG defined, but
>> > the resulting postgres promptly dumps core, due to the fact that
>> > user_lockmethod doesn't supply any value for trace_flag; thus, the
>> > first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer.
>> > This is the result of the following commit:
>>
>> > commit 0180bd6180511875db046bf8ddcaa633a2952dfd
>>
>> +1 for just reverting that commit.  I'm not sure how much use the
>> LOCK_DEBUG infrastructure has in exactly its current form, but I can
>> certainly imagine wanting to use it or some variant of it to debug
>> tough problems.  If it's gone entirely, people would have to reinvent
>> most of it for that type of debugging.  On the other side of the coin,
>> I don't have a clear enough use-case for it to want to spend time
>> right now on redesigning it, nor a clear idea of exactly what changes
>> might make it more useful.  So I think we should just revert and
>> not spend additional effort now.
>
> I am confused.   I thought it was lock_debug referencing user locks that
> was broken.  Does lock_debug need user locks?

It supports tracing them.

The point is, they're not gone.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Parsing output of EXPLAIN command in PostgreSQL

2011-11-10 Thread Andreas Karlsson


On 2011-11-10 23:42, Andreas Karlsson wrote:

Hi,

I recommend using the XML, JSON or YAML version of the plan, whichever
is easiest in your programming language to parse. I do not think anyone
has written a formal schema yet for the XML but it still should be much
easier to parse than rolling your own parser for the human readable format.

But if you insist on parsing the human readable format, e.g. to support
versions before 9.1, I recommend that you take a look at
http://explain.depesz.com/ which supports parsing text plans in addition
to XML and JSON. The site is open source and written in Perl.

The source code can be found at:

https://github.com/depesz/Pg--Explain


Sorry, I did not see the other thread about the same thing. Let's keep 
all discussion there.


--
Andreas Karlsson

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

Re: [HACKERS] Disable OpenSSL compression

Bruce Momjian  writes:
> Tom Lane wrote:
>> Huh?  You put it in the connection string, typically.  This is not
>> different from how you'd specify sslmode to start with.

> Well, you are saying the client is more flexible, but if the client is a
> binary, it isn't flexible without an environment variable to control it.

As long as the client can take a connection string, it's perfectly
flexible.  If it can't, this is just one more reason why it should
be fixed to do so.

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] Parsing output of EXPLAIN command in PostgreSQL

2011-11-10 Thread Andreas Karlsson


On 2011-11-10 17:23, Αναστάσιος Αρβανίτης wrote:

Also another option I am considering is to use EXPLAIN [query] FORMAT XML which 
is available in PostgreSQL 9.1. However, in that case it
would better to have the XML Schema of the generated plans available.

Is there any other solution I am not aware of?


Hi,

I recommend using the XML, JSON or YAML version of the plan, whichever 
is easiest in your programming language to parse. I do not think anyone 
has written a formal schema yet for the XML but it still should be much 
easier to parse than rolling your own parser for the human readable format.


But if you insist on parsing the human readable format, e.g. to support 
versions before 9.1, I recommend that you take a look at 
http://explain.depesz.com/ which supports parsing text plans in addition 
to XML and JSON. The site is open source and written in Perl.


The source code can be found at:

https://github.com/depesz/Pg--Explain

Best regards,
Andreas

--
Andreas Karlsson

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

Re: [HACKERS] Disable OpenSSL compression

Tom Lane wrote:
> Bruce Momjian  writes:
> > How is the compression connection parameter set?  It seems odd for it to
> > be compiled into the application because the application could be run on
> > different networks.  I don't know of any way to inject connection
> > options from outside the application like libpq's PGOPTIONS.
> 
> Huh?  You put it in the connection string, typically.  This is not
> different from how you'd specify sslmode to start with.

Well, you are saying the client is more flexible, but if the client is a
binary, it isn't flexible without an environment variable to control it.

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

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

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL

2011-11-10 Thread Greg Smith

On 11/10/2011 11:10 AM, Αναστάσιος Αρβανίτης wrote:

I'm developing an application that requires parsing of
execution plans (those produced as output by issuing an EXPLAIN [query]
command). Are you aware of any Java library that I could use for this
purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.

There's also a little Javascript program that consumes the JSON version at:

http://www.postgresonline.com/journal/archives/171-pgexplain90formats_part1.html
http://www.postgresonline.com/journal/archives/174-pgexplain90formats_part2.html

Also another option I am considering is to use EXPLAIN [query] FORMAT XML which
is available in PostgreSQL 9.1. However, in that case it
would better to have the XML Schema of the generated plans available.

That's the easiest way to solve this problem in Java, and in that case
most of the text-based code in Pg--Explain will just be a distraction.
I know some of the earlier versions of XML EXPLAIN included a "DTD"
option to output that, but I don't see that in the committed code. I'm
not sure where that is at actually; it's a good question.

The only reference to doing this I found was Andrew's blog:
http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html
where he talks about there being a RELAXNG specification for the XML
output. I can't find where that came from either. Andrew?

--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

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

Re: [HACKERS] Disable OpenSSL compression

Bruce Momjian  writes:
> How is the compression connection parameter set?  It seems odd for it to
> be compiled into the application because the application could be run on
> different networks.  I don't know of any way to inject connection
> options from outside the application like libpq's PGOPTIONS.

Huh?  You put it in the connection string, typically.  This is not
different from how you'd specify sslmode to start with.

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] type privileges and default privileges

Robert Haas  writes:
> On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut  wrote:
>> No, I'm pondering having pg_default_acl initialized so that newly
>> created types have explicit USAGE privileges in their typacl column, so
>> acldefault() wouldn't be needed.  (And builtin types would have their
>> typacl initialized analogously.)  I suppose this is how we might have
>> done it if we had invented ALTER DEFAULT PRIVILEGES first.

> I'm not convinced.  That's a lot of catalog clutter for no benefit.

To actually get rid of acldefault, we'd have to do that not only for
types but for all objects with ACLs.  That's a LOT of catalog bulk,
and like Robert I'm not seeing much benefit.  It's not unreasonable
to want the typical case to be small and fast.

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] LOCK_DEBUG is busted

Tom Lane wrote:
> Robert Haas  writes:
> > It's possible to compile the source tree with LOCK_DEBUG defined, but
> > the resulting postgres promptly dumps core, due to the fact that
> > user_lockmethod doesn't supply any value for trace_flag; thus, the
> > first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer.
> > This is the result of the following commit:
> 
> > commit 0180bd6180511875db046bf8ddcaa633a2952dfd
> 
> +1 for just reverting that commit.  I'm not sure how much use the
> LOCK_DEBUG infrastructure has in exactly its current form, but I can
> certainly imagine wanting to use it or some variant of it to debug
> tough problems.  If it's gone entirely, people would have to reinvent
> most of it for that type of debugging.  On the other side of the coin,
> I don't have a clear enough use-case for it to want to spend time
> right now on redesigning it, nor a clear idea of exactly what changes
> might make it more useful.  So I think we should just revert and
> not spend additional effort now.

I am confused.   I thought it was lock_debug referencing user locks that
was broken.  Does lock_debug need user locks?

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

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

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

Re: [HACKERS] LOCK_DEBUG is busted

Robert Haas wrote:
> Now, whether or not this facility is well designed is a worthwhile
> question.  Trace_lock_oidmin seems pretty sketchy to me, especially
> because it's blindly applied to even to lock tags where the second
> field isn't a relation - i.e. SET_LOCKTAG_TRANSACTION sets it to zero,
> SET_LOCKTAG_VIRTUALTRANSACTION sets it to the localTransactionId,
> SET_LOCKTAG_OBJECT sets it to the classId member of the objectaddress,
> and advisory locks set it to 32 bits of the user's chosen locktag.  So
> by default, with trace_userlocks turned on and no other changes,
> pg_advisory_lock(16384,0) produces output like that shown above and
> pg_advisory_lock(16383,0) is met with silence.  So maybe we should
> just rip some or all of this stuff out instead of worrying too much
> about it.

Please rip out whatever I missed.  Thanks.  The user locks were the old
lock type before we had advisor locks, as far as I remember.

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

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

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

Re: [HACKERS] LOCK_DEBUG is busted

Robert Haas  writes:
> It's possible to compile the source tree with LOCK_DEBUG defined, but
> the resulting postgres promptly dumps core, due to the fact that
> user_lockmethod doesn't supply any value for trace_flag; thus, the
> first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer.
> This is the result of the following commit:

> commit 0180bd6180511875db046bf8ddcaa633a2952dfd

+1 for just reverting that commit.  I'm not sure how much use the
LOCK_DEBUG infrastructure has in exactly its current form, but I can
certainly imagine wanting to use it or some variant of it to debug
tough problems.  If it's gone entirely, people would have to reinvent
most of it for that type of debugging.  On the other side of the coin,
I don't have a clear enough use-case for it to want to spend time
right now on redesigning it, nor a clear idea of exactly what changes
might make it more useful.  So I think we should just revert and
not spend additional effort now.

regards, tom lane

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL

Andrew Dunstan  writes:
> On 11/10/2011 04:29 PM, Robert Haas wrote:
>> 2011/11/10 ÁíáóôÜóéïò Áñâáíßôçò:
>>> Is there any other solution I am not aware of?

>> Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
>> too, but that's in C++.

> Pg--Explain is extremely well written, and should be easily translatable 
> to Java if you really need to. The whole thing is less than 2000 lines, 
> and a large part of that is comments.

Nonetheless, it's solving the wrong problem.  Any program that is being
written today to read EXPLAIN output should be written to read one of
the machine-readable formats.  If you insist on reading the text format,
don't be surprised when (not if) it breaks in future releases, and don't
expect any sympathy from these quarters.

It really shouldn't be that difficult to come by pre-fab code to read
at least one of XML, JSON, or YAML into a suitable data structure.

Now, if you're looking for something that "understands" the resulting
data structure in more than a superficial fashion, that's a different
question.  But you'd need to get a lot more specific about what you
need it to do.

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] const correctness

Bruce Momjian  wrote:

>> No, version 2 of the patch used the strchr() technique and has
>> *zero* new functions and *zero* new macros.
> 
> Right.  I was referring to the non-strchr() approach in the
> initial patch.

I'm sorry that I misunderstood you.

So, I don't think I've heard any argument against version 2 of this
patch.  Does anyone oppose this version?  Is any committer willing
to commit it?  I'm not sure there's much point putting it into the
CF application, since in spot-checks of object files I thought were
most likely to be affected, I found that identical object code was
generated.  It seems to be strictly a matter of whether the code is
more or less readily understood with the patch applied.

-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] Disable OpenSSL compression

Tom Lane wrote:
> "Albe Laurenz"  writes:
> > Tom Lane wrote:
> >> A GUC is entirely, completely, 100% the wrong answer.  It has no way
> >> to deal with the fact that some clients may need compression and others
> >> not.
> 
> > You can force a certain SSL cipher on the client, why not a compression
> > setting?
> 
> To my mind, the argument for the ssl_cipher setting is to allow the DBA
> to enforce a site-wide security policy to the effect of "we consider
> only these ciphers strong enough for production use".  It's a pretty
> weak argument (especially since the setting is not cognizant of where
> the connection is coming from), but at least it's an argument.
> 
> There's no comparable security argument for an ssl_compression setting.
> If anything, a security-minded DBA might wish to insist on compression
> being *on*, but you aren't proposing to give him control in that
> direction (and AFAICT openssl doesn't offer a force-on flag for it).
> 
> But in any case, my objection is that there's no adequate use-case
> for this GUC, because it's much more sensible to set it from the client
> side.  We have too many GUCs already --- Josh B regularly goes on the
> warpath looking for ones we can remove.  This one should never get in
> there to start with.

How is the compression connection parameter set?  It seems odd for it to
be compiled into the application because the application could be run on
different networks.  I don't know of any way to inject connection
options from outside the application like libpq's PGOPTIONS.  Would we
add a libpq environment variable for this, like PGUSER?

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

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

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

Re: [HACKERS] Parsing output of EXPLAIN command in PostgreSQL




On 11/10/2011 04:29 PM, Robert Haas wrote:

2011/11/10 Αναστάσιος Αρβανίτης:

I'm developing an application that requires parsing of
execution plans (those produced as output by issuing an EXPLAIN [query]
command). Are you aware of any Java library that I could use for this
purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl.

Also another option I am considering is to use EXPLAIN [query] FORMAT XML which 
is available in PostgreSQL 9.1. However, in that case it
would better to have the XML Schema of the generated plans available.

Is there any other solution I am not aware of?

Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
too, but that's in C++.



Pg--Explain is extremely well written, and should be easily translatable 
to Java if you really need to. The whole thing is less than 2000 lines, 
and a large part of that is comments.


cheers

andrew

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

Re: [HACKERS] const correctness

Kevin Grittner wrote:
> Bruce Momjian  wrote:
>  
> > I realize the patch only added 1-2 new const functions
>  
> No, version 2 of the patch used the strchr() technique and has
> *zero* new functions and *zero* new macros.

Right.  I was referring to the non-strchr() approach in the initial
patch.

> > but this is only a small area of the code being patched --- a full
> > solution would have many more complex duplicates, and awkward
> > changes as we add features.
>  
> I'm not convinced of that, and I don't think it really has a bearing
> on doing where it can be done with no new functions and no changes
> to the code other than adding "const" to existing lines of code.

Right, again I was referring to the non-strchr() approach, e.g. new
functions.

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

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

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

Re: [HACKERS] const correctness

Bruce Momjian  wrote:

> I realize the patch only added 1-2 new const functions

No, version 2 of the patch used the strchr() technique and has
*zero* new functions and *zero* new macros.

> but this is only a small area of the code being patched --- a full
> solution would have many more complex duplicates, and awkward
> changes as we add features.

I'm not convinced of that, and I don't think it really has a bearing
on doing where it can be done with no new functions and no changes
to the code other than adding "const" to existing lines of code.

-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] Parsing output of EXPLAIN command in PostgreSQL

2011/11/10 Αναστάσιος Αρβανίτης :
> I'm developing an application that requires parsing of
> execution plans (those produced as output by issuing an EXPLAIN [query]
> command). Are you aware of any Java library that I could use for this
> purpose? I found https://github.com/depesz/Pg--Explain but it is built in 
> Perl.
>
> Also another option I am considering is to use EXPLAIN [query] FORMAT XML 
> which is available in PostgreSQL 9.1. However, in that case it
> would better to have the XML Schema of the generated plans available.
>
> Is there any other solution I am not aware of?

Not that I know of.  I think pgAdmin can parse the EXPLAIN output,
too, but that's in C++.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] type privileges and default privileges

On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut  wrote:
> On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote:
>> Peter Eisentraut  writes:
>> > Let me put this differently.  Should we either continue to hardcode the
>> > default privileges in the acldefault() function, or should we instead
>> > initialize the system catalogs with an entry in pg_default_acl as though
>> > ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
>> > executed?
>>
>> If you're proposing to replace acldefault() with a catalog lookup,
>> I vote no.  I think that's a performance hit with little redeeming
>> social value.
>
> No, I'm pondering having pg_default_acl initialized so that newly
> created types have explicit USAGE privileges in their typacl column, so
> acldefault() wouldn't be needed.  (And builtin types would have their
> typacl initialized analogously.)  I suppose this is how we might have
> done it if we had invented ALTER DEFAULT PRIVILEGES first.

I'm not convinced.  That's a lot of catalog clutter for no benefit.

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

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

[HACKERS] LOCK_DEBUG is busted

It's possible to compile the source tree with LOCK_DEBUG defined, but
the resulting postgres promptly dumps core, due to the fact that
user_lockmethod doesn't supply any value for trace_flag; thus, the
first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer.
This is the result of the following commit:

commit 0180bd6180511875db046bf8ddcaa633a2952dfd
Author: Bruce Momjian 
Date:   Thu Oct 13 19:59:13 2011 -0400

Remove all "traces" of trace_userlocks, because userlocks were removed
in PG 8.2.

As far as I can see, that commit was just wrong and should be
reverted.  I believe that Bruce's motivation for this commit was the
following sentence from the documentation:

-User locks were removed as of PostgreSQL version 8.2.  This option
-currently has no effect.

The trouble with this is that it's just not true.  With that commit
reverted and LOCK_DEBUG defined in pg_config_manual.h:

rhaas=# set trace_userlocks=1;
LOG:  LockReleaseAll: lockmethod=2
STATEMENT:  set trace_userlocks=1;
LOG:  LockReleaseAll done
STATEMENT:  set trace_userlocks=1;
SET
rhaas=# select pg_advisory_lock(31,31);
LOG:  LockAcquire: lock [16384,31] ExclusiveLock
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  LockAcquire: new: lock(0x103ad37c0)
id(16384,31,31,2,8,2) grantMask(0) req(0,0,0,0,0,0,0)=0
grant(0,0,0,0,0,0,0)=0 wait(0) type(ExclusiveLock)
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  LockAcquire: new: proclock(0x103b590b8) lock(0x103ad37c0)
method(2) proc(0x103d72b30) hold(0)
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  LockCheckConflicts: no conflict: proclock(0x103b590b8)
lock(0x103ad37c0) method(2) proc(0x103d72b30) hold(0)
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  GrantLock: lock(0x103ad37c0) id(16384,31,31,2,8,2)
grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0)
type(ExclusiveLock)
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  LockReleaseAll: lockmethod=2
STATEMENT:  select pg_advisory_lock(31,31);
LOG:  LockReleaseAll done
STATEMENT:  select pg_advisory_lock(31,31);
 pg_advisory_lock
--

(1 row)

Now, whether or not this facility is well designed is a worthwhile
question.  Trace_lock_oidmin seems pretty sketchy to me, especially
because it's blindly applied to even to lock tags where the second
field isn't a relation - i.e. SET_LOCKTAG_TRANSACTION sets it to zero,
SET_LOCKTAG_VIRTUALTRANSACTION sets it to the localTransactionId,
SET_LOCKTAG_OBJECT sets it to the classId member of the objectaddress,
and advisory locks set it to 32 bits of the user's chosen locktag.  So
by default, with trace_userlocks turned on and no other changes,
pg_advisory_lock(16384,0) produces output like that shown above and
pg_advisory_lock(16383,0) is met with silence.  So maybe we should
just rip some or all of this stuff out instead of worrying too much
about it.  If we're not going to do that, then we should revert the
above commit, so that it works again, at least as much as it did
before.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] foreign key locks, 2nd attempt

Alvaro Herrera wrote:
> 
> Excerpts from Bruce Momjian's message of jue nov 10 16:59:20 -0300 2011:
> > Alvaro Herrera wrote:
> > > Hello,
> > > 
> > > After some rather extensive rewriting, I submit the patch to improve
> > > foreign key locks.
> > > 
> > > To recap, the point of this patch is to introduce a new lock tuple mode,
> > > that lets the RI code obtain a lighter lock on tuples, which doesn't
> > > conflict with updates that do not modify the key columns.
> > 
> > What kind of operations benefit from a non-key lock like this?
> 
> I'm not sure I understand the question.
> 
> With this patch, a RI check does "SELECT FOR KEY SHARE".  This means the
> tuple is locked with that mode until the transaction finishes.  An
> UPDATE that modifies the referenced row will not conflict with that lock.
> 
> An UPDATE that modifies the key columns will be blocked, just as now.
> Same with a DELETE.

OK, so it prevents non-key data modifications from spilling to the
referred rows --- nice.

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

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

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

Re: [HACKERS] Syntax for partitioning

2011-11-10 Thread Dimitri Fontaine

Jeff Janes  writes:
> shouldn't it need a DBA to declare it?  How is the system supposed to
> anticipate that at some point years in the future I will want to run
> the command sequence "create foo_archive as select from foo where
> year<2009; delete from foo where year<2009", or its partition-based
> equivalent, and have it operate on several billion rows cleanly and
> quickly?  I don't think we can expect the system to anticipate what it
> has never before experienced.  This is the DBA's job.

Well, the not-fully spelled out proposal would be to still work it out
from a list of columns picked by the DBA.  I though that an existing
index would be best, but maybe just columns would be good.

I guess it's already time to play loose and invent some SQL syntax to
make it easier talking about the same thing:

  ALTER TABLE foo SEGMENT ON (year, stamp);

Now the aim would be to be able to implement the operation you describe
by using the new segment map, which is an index pointing to sequential
ranges of on-disk blocks where the data is known to share a common key
range over the columns you're segmenting on.  I would imagine this SQL:

  TRUNCATE foo WHERE year < 2009;

As the on-disk location of the data that qualify this WHERE clause is
known, it could be possible to (predicate) lock it and bulk remove it,
unlinking whole segments (1GB) at a time when relevant.

> While automatic clustering would be nice, it isn't the same thing as
> partitioning.

That has been my initial reaction to that kind of ideas too.  After some
more time brewing the ideas, I'm not convinced that the use cases that
usually drives you to the latter can't be solved with the former.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] unaccent extension missing some accents

Bruce Momjian  writes:
> Tom Lane wrote:
>> However, the bigger picture is that OS X's UTF8 locales are broken
>> through-and-through, and most of their other problems are not feasible
>> to work around.

> If Apple's low-level code came from FreeBSD and NetBSD, how did they get
> so broken?

AFAIK, they're broken in the BSDen too, or at least were when Apple
branched off from whichever BSD they started from (which was years ago).
There may be a better solution available upstream by now, but it doesn't
appear to me that Apple has any interest in fixing this area.

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] unaccent extension missing some accents

Tom Lane wrote:
> J Smith  writes:
> > I've attached a patch against master for unaccent.c that uses swscanf
> > along with char2wchar and wchar2char instead of sscanf directly to
> > initialize the unaccent extension and it appears to fix the problem in
> > both the master and 9.1 branches.
> 
> swscanf doesn't seem like an acceptable approach: it's a function that
> is relied on nowhere else in PG, so it adds new portability risks of its
> own.  It doesn't exist on some platforms that we support (like the one
> I'm typing this message on) and there's no real good reason to assume
> that it's not broken in its own ways on others.
> 
> If you really want to pursue this, I'd suggest parsing the line
> manually, perhaps via strchr searches for \t and \n.  It likely wouldn't
> be very many more lines than what you've got here.
> 
> However, the bigger picture is that OS X's UTF8 locales are broken
> through-and-through, and most of their other problems are not feasible
> to work around.  So basically you can't use them for anything
> interesting, and it's not clear that it's worth putting any time into
> solving individual problems.  In the particular case here, the issue
> presumably is that sscanf is relying on isspace() ... but we rely on
> isspace() directly, in quite a lot of places, so how much is it going
> to fix to dodge it right here?

If Apple's low-level code came from FreeBSD and NetBSD, how did they get
so broken?

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

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

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

Re: [HACKERS] const correctness

Kevin Grittner wrote:
> Tom Lane  wrote:
>  
> > The problem with it of course is that mistaken use could have the
> > effect of casting-away-const, which is exactly what we hoped to
> > prevent.  Still, there may not be a better solution.
>  
> Yeah, I've come to the conclusion that the compiler doesn't do the
> apparently-available optimizations using const precisely because it
> is so easy to cast away the property maliciously or accidentally.

Right.  The compiler would have to look at the function code, and all
functions called by that function, to determine if const was honored ---
not something that is easily done.

I agree that the strchr() approach is best.  I realize the patch only
added 1-2 new const functions, but this is only a small area of the code
being patched --- a full solution would have many more complex
duplicates, and awkward changes as we add features.

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

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

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

Re: [HACKERS] foreign key locks, 2nd attempt

2011-11-10 Thread Alvaro Herrera


Excerpts from Bruce Momjian's message of jue nov 10 16:59:20 -0300 2011:
> Alvaro Herrera wrote:
> > Hello,
> > 
> > After some rather extensive rewriting, I submit the patch to improve
> > foreign key locks.
> > 
> > To recap, the point of this patch is to introduce a new lock tuple mode,
> > that lets the RI code obtain a lighter lock on tuples, which doesn't
> > conflict with updates that do not modify the key columns.
> 
> What kind of operations benefit from a non-key lock like this?

I'm not sure I understand the question.

With this patch, a RI check does "SELECT FOR KEY SHARE".  This means the
tuple is locked with that mode until the transaction finishes.  An
UPDATE that modifies the referenced row will not conflict with that lock.

An UPDATE that modifies the key columns will be blocked, just as now.
Same with a DELETE.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] proposal: psql concise mode

Robert Haas wrote:
> On Sun, Nov 6, 2011 at 3:29 PM, Josh Kupershmidt  wrote:
> > On Sun, Nov 6, 2011 at 1:16 PM, Dickson S. Guedes  
> > wrote:
> >>> test=# \d+ foo
> >>> ? ? ? ? ? ? ? ? ? ? ? ? Table "public.foo"
> >>> ?Column | ?Type ? | Storage
> >>> +-+-
> >>> ?a ? ? ?| integer | plain
> >>> ?b ? ? ?| integer | plain
> >>> Has OIDs: no
> >>
> >> Using your example, what if column 'b' has a comment and 'a' not? How
> >> the above output will be displayed?
> >
> > Then the comments would be displayed as they previously were, like so:
> >
> > ? ? ? ? ? ? ? ? ? ? ? ? Table "public.foo"
> > ?Column | ?Type ? | Storage | Description
> > +-+-+-
> > ?a ? ? ?| integer | plain ? |
> > ?b ? ? ?| integer | plain ? | some comment
> > Has OIDs: no
> 
> I don't strongly object to this, but I wonder how useful it will
> really be in practice.  It strikes me as the sort of advanced psql
> hackery that only a few people will use, and only some of those will
> gain any benefit.  Empty columns don't really take up that much screen
> width, and even one value in any given column will require its
> inclusion anyway.  I can also see myself turning it on and then going
> - oh, wait, is that column not there, or did it just disappear because
> I'm in concise mode?
> 
> Not saying we shouldn't do it, just some food for thought.

Have you tried \d+ with this psql mode:

\pset format wrapped

It wraps the data so it fits on the screen --- it is my default in my
.psqlrc.

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

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

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

Re: [HACKERS] foreign key locks, 2nd attempt

2011-11-10 Thread Christopher Browne

On Thu, Nov 10, 2011 at 3:29 PM, Kevin Grittner
 wrote:
> Christopher Browne  wrote:
>
>> There's value in having an "immutability" constraint on a column,
>> where, in effect, you're not allowed to modify the value of the
>> column, once assigned.
>
> +1  We would definitely use such a feature, should it become
> available.

Added to TODO list.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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

Re: [HACKERS] foreign key locks, 2nd attempt

Christopher Browne  wrote:
 
> There's value in having an "immutability" constraint on a column,
> where, in effect, you're not allowed to modify the value of the
> column, once assigned.
 
+1  We would definitely use such a feature, should it become
available.
 
-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] const correctness

Tom Lane  wrote:

> The problem with it of course is that mistaken use could have the
> effect of casting-away-const, which is exactly what we hoped to
> prevent.  Still, there may not be a better solution.

Yeah, I've come to the conclusion that the compiler doesn't do the
apparently-available optimizations using const precisely because it
is so easy to cast away the property maliciously or accidentally.

-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] pg_upgrade automatic testing

Peter Eisentraut wrote:
> On m?n, 2011-09-19 at 07:06 +0300, Peter Eisentraut wrote:
> > I found a simpler way to get this working.  Just hack up the catalogs
> > for the new path directly.  So I can now run this test suite against
> > older versions as well, like this:
> > 
> > contrib/pg_upgrade$ make installcheck oldsrc=somewhere oldbindir=elsewhere
> 
> Any comments on how to proceed with this?  I think it has been useful in
> detecting pg_upgrade breakage a few times already, so I'd like to commit
> it and start using it.

I don't have a problem with adding it.

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

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

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

Re: [HACKERS] foreign key locks, 2nd attempt

2011-11-10 Thread Pavel Stehule

2011/11/10 Christopher Browne :
> On Sun, Nov 6, 2011 at 2:28 AM, Jeroen Vermeulen  wrote:
>> On 2011-11-04 01:12, Alvaro Herrera wrote:
>>
>>> I would like some opinions on the ideas on this patch, and on the patch
>>> itself.  If someone wants more discussion on implementation details of
>>> each part of the patch, I'm happy to provide a textual description --
>>> please just ask.
>>
>> Jumping in a bit late here, but thanks for working on this: it looks like it
>> could solve some annoying problems for us.
>>
>> I do find myself idly wondering if those problems couldn't be made to go
>> away more simply given some kind of “I will never ever update this key”
>> constraint.  I'm having trouble picturing the possible lock interactions as
>> it is.  :-)
>
> +1 on that, though I'd make it more general than that.  There's value
> in having an "immutability" constraint on a column, where, in effect,
> you're not allowed to modify the value of the column, once assigned.
> That certainly doesn't prevent issuing DELETE + INSERT to get whatever
> value you want into place, but that's a big enough hoop to need to
> jump through to get rid of some nonsensical updates.
>
> And if the target of a foreign key constraint consists of immutable
> columns, then, yes, indeed, UPDATE on that table no longer conflicts
> with references.
>
> In nearly all cases, I'd expect that SERIAL would be reasonably
> followed by IMMUTABLE.
>
> create table something_assigned (
>   something_id serial immutable primary key,
>   something_identifier text not null unique
> );

I like this idea  - it can solve two problem

Regards

Pavel Stehule

> --
> When confronted by a difficult problem, solve it by reducing it to the
> question, "How would the Lone Ranger handle this?"
>
> --
> 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] foreign key locks, 2nd attempt

2011-11-10 Thread Christopher Browne

On Sun, Nov 6, 2011 at 2:28 AM, Jeroen Vermeulen  wrote:
> On 2011-11-04 01:12, Alvaro Herrera wrote:
>
>> I would like some opinions on the ideas on this patch, and on the patch
>> itself.  If someone wants more discussion on implementation details of
>> each part of the patch, I'm happy to provide a textual description --
>> please just ask.
>
> Jumping in a bit late here, but thanks for working on this: it looks like it
> could solve some annoying problems for us.
>
> I do find myself idly wondering if those problems couldn't be made to go
> away more simply given some kind of “I will never ever update this key”
> constraint.  I'm having trouble picturing the possible lock interactions as
> it is.  :-)

+1 on that, though I'd make it more general than that.  There's value
in having an "immutability" constraint on a column, where, in effect,
you're not allowed to modify the value of the column, once assigned.
That certainly doesn't prevent issuing DELETE + INSERT to get whatever
value you want into place, but that's a big enough hoop to need to
jump through to get rid of some nonsensical updates.

And if the target of a foreign key constraint consists of immutable
columns, then, yes, indeed, UPDATE on that table no longer conflicts
with references.

In nearly all cases, I'd expect that SERIAL would be reasonably
followed by IMMUTABLE.

create table something_assigned (
   something_id serial immutable primary key,
   something_identifier text not null unique
);
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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

Re: [HACKERS] type privileges and default privileges

On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote:
> Peter Eisentraut  writes:
> > Let me put this differently.  Should we either continue to hardcode the
> > default privileges in the acldefault() function, or should we instead
> > initialize the system catalogs with an entry in pg_default_acl as though
> > ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
> > executed?
> 
> If you're proposing to replace acldefault() with a catalog lookup,
> I vote no.  I think that's a performance hit with little redeeming
> social value.

No, I'm pondering having pg_default_acl initialized so that newly
created types have explicit USAGE privileges in their typacl column, so
acldefault() wouldn't be needed.  (And builtin types would have their
typacl initialized analogously.)  I suppose this is how we might have
done it if we had invented ALTER DEFAULT PRIVILEGES first.

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

Re: [HACKERS] pl/python custom datatype parsers

On tis, 2011-11-08 at 16:08 -0500, Andrew Dunstan wrote:
> 
> On 03/01/2011 11:50 AM, Peter Eisentraut wrote:
> > On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote:
> >> I believe it's (b). But as we don't have time for that discussion that
> >> late in the release cycle, I think we need to consider it identical to (c).
> > As I previously mentioned, I think that there should be an SQL-level way
> > to tie together languages and types.  I previously mentioned the
> > SQL-standard command CREATE TRANSFORM as a possibility.  I've had this
> > on my PL/Python TOTHINK list for a while.  Thankfully you removed all
> > the items ahead of this one, so I'll think of something to do in 9.2.
> >
> > Of course we'll be able to use the actual transform code that you
> > already wrote.
> >
> 
> Peter,
> 
> Did you make any progress on this?

No, but it's still somewhere on my list.  I saw your blog post related
to this.

I think the first step would be to set up some catalog infrastructure
(without DDL commands and all that overhead), and try to adapt the big
"case" statement of an existing language to that, and then check whether
that works, performance, etc.

Some other concerns of the top of my head:

- Arrays: Would probably not by handled by that.  So this would not be
able to handle, for example, switching the array handling behavior in
PL/Perl to ancient compatible mode.

- Range types: no idea

I might work on this, but not before December, would be my guess.


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

Re: [HACKERS] const correctness

"Kevin Grittner"  writes:
> Tom mentioned the strchr() function, which does do that.  I don't
> actually find that surprising given my understanding of the
> semantics.  That means that the function is promising not to modify
> the character array, but is not asserting that it knows the
> character array to be immutable.  Makes sense to me.  It's up to the
> caller to assign it to a "const char *" if it knows it passed in an
> immutable object.
 
The problem with it of course is that mistaken use could have the
effect of casting-away-const, which is exactly what we hoped to prevent.
Still, there may not be a better solution.

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] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)

Steve Singer  writes:
> On 11-11-10 02:00 PM, Tom Lane wrote:
>> ... Oh, I see, the problem is that&labels[-1] might not compare to
>> &labels[0] the way we want.  I think only the first hunk of your
>> patch is actually necessary.

> Yes the problem is still fixed if I only apply the first hunk.

OK, everything seems satisfactorily explained then.  Will commit the
fix, thanks for the report!

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] const correctness

Peter Eisentraut  writes:
> On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote:
>> Now admittedly you can hack it, in the same
>> spirit as the C library functions that are declared to take const
>> pointers and return non-const pointers to the very same data 

> Which C library functions do that?

strchr() is the classic example, but I believe there are some 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] foreign key locks, 2nd attempt

Alvaro Herrera wrote:
> Hello,
> 
> After some rather extensive rewriting, I submit the patch to improve
> foreign key locks.
> 
> To recap, the point of this patch is to introduce a new lock tuple mode,
> that lets the RI code obtain a lighter lock on tuples, which doesn't
> conflict with updates that do not modify the key columns.

What kind of operations benefit from a non-key lock like this?

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

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

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

Re: [HACKERS] const correctness

Peter Eisentraut  wrote:
> On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote:
>> Now admittedly you can hack it, in the same
>> spirit as the C library functions that are declared to take const
>> pointers and return non-const pointers to the very same data 
> 
> Which C library functions do that?

Tom mentioned the strchr() function, which does do that.  I don't
actually find that surprising given my understanding of the
semantics.  That means that the function is promising not to modify
the character array, but is not asserting that it knows the
character array to be immutable.  Makes sense to me.  It's up to the
caller to assign it to a "const char *" if it knows it passed in an
immutable object.

-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] -Wcast-qual cleanup, part 1

On mån, 2011-11-07 at 10:07 -0500, Tom Lane wrote:
> >> 2. Macros accessing  structures should come in two variants: a
> "get"
> >> version, and a "set"/anything else version, so that the "get"
> version
> >> can preserve the const qualifier.
> 
> I'm not prepared to buy into that as a general coding rule.
> 
> Maybe it would be better to just add -Wno-cast-qual to CFLAGS. 

OK, I understand the concerns that have been raised here and in the
other thread.  I'll work instead on removing "lying" const qualifiers on
the upper layers that were the causes of attempting to push the consts
down.


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

Re: [HACKERS] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)

2011-11-10 Thread Steve Singer


On 11-11-10 02:00 PM, Tom Lane wrote:

Steve Singer  writes:

The man page for malloc on AIX is pretty clear on what happens when you
try to malloc 0 bytes.  It returns NULL.


Yes, that's a pretty common behavior for malloc(0).  It should not cause
a problem here AFAICS.

... Oh, I see, the problem is that&labels[-1] might not compare to
&labels[0] the way we want.  I think only the first hunk of your
patch is actually necessary.

regards, tom lane



Yes the problem is still fixed if I only apply the first hunk.



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

Re: [HACKERS] const correctness

On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote:
> Now admittedly you can hack it, in the same
> spirit as the C library functions that are declared to take const
> pointers and return non-const pointers to the very same data 

Which C library functions do that?


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

Re: [HACKERS] IDLE in transaction introspection

Bruce Momjian  writes:
> Well, we could use an optional "details" string for that.  If not, we
> are still using the magic-string approach, which I thought we didn't
> like.

No, we're not using magic strings, we're using an enum --- maybe not an
officially declared enum type, but it's a column with a predetermined
set of possible values.  It would be a magic string if it were still in
the "query" field and thus confusable with user-written queries.

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] const correctness

Florian Pflug  wrote:
> On Nov9, 2011, at 22:54 , Kevin Grittner wrote:
>> Tom Lane  wrote:
>> 
>>> I don't doubt that just duplicating macros and inlineable
>>> functions is a wash performance-wise (in fact, in principle it
>>> shouldn't change the generated code at all).
>> 
>> I had the impression that compilers these days could sometimes
>> better optimize across calls to functions with const parameters,
>> because previously-referenced elements of the structures could be
>> trusted to be unchanged across the call.  I'm not talking about
>> calls to the inlineable function or macros themselves, but the
>> higher level functions which can then use const.
> 
> I don't think that's true. Const (for pointer types) generally
> only means "you cannot modify the value through *this* pointer.
> But there may very well be other pointers to the same object, and
> those may very well be used to modify the value at any time.
> 
> So unless both the calling and the called function are in the same
> compilation unit, the compiler needs to assume that any non-local
> (and even local values whose address was taken previously) value
> in the calling function may change as a result of the function
> call. Or at least I think so.

You two seem to be right.  I checked some generated code where I
would have expected it to help if it was ever going to, and the
generated code was absolutely identical.  It appears that the *only*
real argument for this is to document the function's contract. 
Whether the benefit of that outweighs any distraction it causes
seems to be the key argument to be had here.

> If we're concerned about helping the compiler produce better code,
> I think we should try to make our code safe under strict aliasing
> rules. AFAIK, that generally helps much more than
> const-correctness. (Dunno how feasible that is, though)

I hacked my configure file to use strict aliasing and -O3, and my
usual set of regression tests passed.  (make check-world, make
installcheck-world against a cluster with
default_transaction_isolation = 'serializable' and
max_prepared_transactions = 10, and make -C src/test/isolation
installcheck against the same cluster)

I did get 10 warnings like this:

  warning: dereferencing type-punned pointer will break
strict-aliasing rules

I haven't yet compared code or run benchmarks.

Since 9.2 seems to be shaping up mainly as a performance release,
now might be a good time to review these compile options to see how
far we can now safely push them.

-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] Is there a good reason we don't have INTERVAL 'infinity'?

Brar Piening wrote:
> 
> Josh Berkus wrote:
> > Hackers,
> >
> > Is there a reason why INTERVAL 'infinity' is not implemented?  That is,
> > an interval which is larger than all defined intervals, and which added
> > to any timestamp turns it into 'infinity'.
> >
> > Or is it just Round TUITs?
> 
> Probably the latter.
> There is even a function |isfinite(interval)| which doesn't seem to do 
> anything useful.
> See complaint in 
> http://archives.postgresql.org/message-id/200101241913.f0ojduu45...@hub.org
> Although the operation used in this complaint isn't obviously defined 
> there certainly are operations that are defined like infinity + infinity 
> = infinity.
> See http://de.wikipedia.org/wiki/Unendlichkeit#Analysis
> (Sorry for linking the german wikipedia - the english text is ways less 
> verbose on this.)

TODO has:

Allow infinite intervals just like infinite timestamps 

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

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

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

Re: [HACKERS] IDLE in transaction introspection

Tom Lane wrote:
> Bruce Momjian  writes:
> > It might be cleaner to use booleans:
> > active: t/f
> > in transaction: t/f
> 
> I don't think so, because that makes some very strict assumptions that
> there are exactly four interesting states (an assumption that isn't
> even true today, to judge by the activity strings we're using now).

Well, we could use an optional "details" string for that.  If not, we
are still using the magic-string approach, which I thought we didn't
like.

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

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

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

Re: [HACKERS] IDLE in transaction introspection

Bruce Momjian  writes:
> It might be cleaner to use booleans:
>   active: t/f
>   in transaction: t/f

I don't think so, because that makes some very strict assumptions that
there are exactly four interesting states (an assumption that isn't
even true today, to judge by the activity strings we're using now).

regards, tom lane

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

Re: [HACKERS] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)

Steve Singer  writes:
> The man page for malloc on AIX is pretty clear on what happens when you 
> try to malloc 0 bytes.  It returns NULL.

Yes, that's a pretty common behavior for malloc(0).  It should not cause
a problem here AFAICS.

... Oh, I see, the problem is that &labels[-1] might not compare to
&labels[0] the way we want.  I think only the first hunk of your
patch is actually necessary.

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] IDLE in transaction introspection

Scott Mead wrote:
> On Wed, Nov 2, 2011 at 4:12 AM, Albe Laurenz wrote:
> 
> > Andrew Dunstan wrote:
> > > On 11/01/2011 09:52 AM, Tom Lane wrote:
> > >> I'm for just redefining the query field as "current or last
> > >> query".
> > >
> > > +1
> > >
> > >> I could go either way on whether to rename it.
> > >
> > > Rename it please. "current_query" will just be wrong. I'd be inclined
> > > just to call it "query" or "query_string" and leave it to the docs to
> > > define the exact semantics.
> >
> > +1 for renaming, +1 for a state column.
> > I think it is overkill to keep a query history beyond that -- if you
> > want that,
> > you can resort to the log files.
> >
> >
> ISTM that we're all for:
> 
>creating a new column: state
>renaming current_query => query
> 
>State will display , ,  in transaction, etc...
>query will display the last query that was executed.
> 
> I've written this up in the attached patch, looking for feedback. (NB:
> Originally I was using 9.1.1 release, I just did a git clone today to
> generate this).

It might be cleaner to use booleans:

active: t/f
in transaction: t/f

or maybe instead of 'active':

idle:   t/f
in transaction: t/f

That avoids the magic string values for the state column.  Those are
much easier to query against too:

WHERE NOT idle;

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

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

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

Re: [HACKERS] IDLE in transaction introspection

2011-11-10 Thread Scott Mead

On Nov 5, 2011 9:02 AM, "Greg Smith"  wrote:
>
> On 11/04/2011 05:01 PM, Tom Lane wrote:
>>
>> Scott Mead  writes:
>>
>>>
>>>I leave the waiting flag in place for posterity.  With this in mind,
is
>>> the consensus:
>>>RUNNING
>>> or
>>>ACTIVE
>>>
>>
>> Personally, I'd go for lower case.
>>
>
>
> I was thinking it would be nice if this state looked like the WAL sender
state values in pg_stat_replication, which are all lower case.  For
comparison those states are:
>
> startup
> backup
> catchup
> streaming

+1, it'll be easier to query against.
>
> --
> Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)

2011-11-10 Thread Steve Singer


On 11-11-09 06:35 PM, Tom Lane wrote:

Steve Singer  writes:

I've tracked the issue down to collectSecLabels in pg_dump.c



SELECT label, provider, classoid, objoid, objsbid FROM
pg_catalog.pg_seclabel;



returns 0 rows.



The code in collectSecLabels() is not prepared to deal with a zero row
result and tries to malloc 0 bytes.


pg_seclabel is almost always empty, so I'm not convinced that you've
identified your problem correctly.

regards, tom lane



The attached patch seems to fix the issue.

The man page for malloc on AIX is pretty clear on what happens when you 
try to malloc 0 bytes.  It returns NULL.
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fce9d3b..9e31767 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*** findSecLabels(Archive *fout, Oid classoi
*** 11760,11766 
  	/* Get security labels if we didn't already */
  	if (nlabels < 0)
  		nlabels = collectSecLabels(fout, &labels);
! 
  	/*
  	 * Do binary search to find some item matching the object.
  	 */
--- 11760,11770 
  	/* Get security labels if we didn't already */
  	if (nlabels < 0)
  		nlabels = collectSecLabels(fout, &labels);
! 	if (nlabels == 0)
! 	{
! 		*items=NULL;
! 		return 0;
! 	}
  	/*
  	 * Do binary search to find some item matching the object.
  	 */
*** collectSecLabels(Archive *fout, SecLabel
*** 11858,11875 
  	i_objsubid = PQfnumber(res, "objsubid");
  
  	ntups = PQntuples(res);
! 
! 	labels = (SecLabelItem *) malloc(ntups * sizeof(SecLabelItem));
! 
! 	for (i = 0; i < ntups; i++)
  	{
! 		labels[i].label = PQgetvalue(res, i, i_label);
! 		labels[i].provider = PQgetvalue(res, i, i_provider);
! 		labels[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
! 		labels[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
! 		labels[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
  	}
  
  	/* Do NOT free the PGresult since we are keeping pointers into it */
  	destroyPQExpBuffer(query);
  
--- 11862,11889 
  	i_objsubid = PQfnumber(res, "objsubid");
  
  	ntups = PQntuples(res);
! 	if ( ntups == 0)
  	{
! 		labels = NULL;
  	}
+ 	else
+ 	{
+ 		labels = (SecLabelItem *) malloc(ntups * sizeof(SecLabelItem));
+ 		if (labels == NULL )
+ 		{
+ 			write_msg(NULL, "out of memory");
+ 			exit(1);
+ 		}
  
+ 		for (i = 0; i < ntups; i++)
+ 		{
+ 			labels[i].label = PQgetvalue(res, i, i_label);
+ 			labels[i].provider = PQgetvalue(res, i, i_provider);
+ 			labels[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
+ 			labels[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
+ 			labels[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
+ 		}
+ 	}
  	/* Do NOT free the PGresult since we are keeping pointers into it */
  	destroyPQExpBuffer(query);
  

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

Re: [HACKERS] warning in pg_upgrade

2011-11-10 Thread Αναστάσιος Αρβανίτης

Robert Haas wrote:
> On Thu, Nov 3, 2011 at 3:45 PM, Kevin Grittner
>  wrote:
> > "Kevin Grittner"  wrote:
> >> Untested patch attached for purposes of discussion.
> >
> > I got in a little testing on it -- not only does this patch
> > eliminate the compile-time warning, but if you try to run pg_upgrade
> > when another session has removed your current working directory, you
> > get a reasonable message instead of the program attempting to
> > proceed with undefined (potential garbage) for a working directory.
> 
> Committed.  Also fixed another compiler warning that popped up for me.

Thanks Kevin and Robert.

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

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

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

Re: [HACKERS] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

2011-11-10 Thread Nikhil Sontakke

> Um ... why would we do this only for tables, and not for creations of
> other sorts of objects that belong to schemas?
>
>
Right, we need to do it for other objects like functions etc. too.


> Also, if we are going to believe that this is a serious problem, what
> of ALTER ... SET SCHEMA?
>
>
I admit, I hadn't thought of this.


> Also, the proposed solution is pretty silly on its face, because it has
> not removed the race condition only made the window somewhat narrower.
> You would have to acquire the lock as part of the initial schema lookup,
> not lock the OID after the fact.  And could we please not do something
> as silly as translate the OID back to a string and then look up that
> string a second time?
>
>
The comment mentions that part is a kluge but that we get to re-use the
existing function because of it. The get_object_address function will bail
out anyways if the schema has vanished from down under and it does lock it
up immediately after it's found to be valid.


> (To be clear, I don't particularly believe that this is a problem worthy
> of spending code space and cycles on.  But if it's deemed to be a
> problem, I want to see a solution that's actually watertight.)
>
>
Got the message.

Regards,
Nikhils

[HACKERS] Parsing output of EXPLAIN command in PostgreSQL

I'm developing an application that requires parsing of 
execution plans (those produced as output by issuing an EXPLAIN [query] 
command). Are you aware of any Java library that I could use for this 
purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. 


Also another option I am considering is to use EXPLAIN [query] FORMAT XML which 
is available in PostgreSQL 9.1. However, in that case it 
would better to have the XML Schema of the generated plans available.

Is there any other solution I am not aware of?


Thank you


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

Re: [HACKERS] Disable OpenSSL compression

2011-11-10 Thread Marko Kreen

On Thu, Nov 10, 2011 at 5:18 PM, Albe Laurenz  wrote:
> I could go and try to convince Npgsql and JDBC to accept patches to
> do that on the client side, but that would be more effort than I
> want to invest.  But then there's still closed source software like
> Devart dotConnect...

Are you certain Java/C# even support SSL compression?
Quick grep over some old jdk code I had around
did not find it...

In any case, the connection libraries do not need to match
such optional features.

-- 
marko

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

[HACKERS] Parsing output of EXPLAIN command in PostgreSQL

2011-11-10 Thread Αναστάσιος Αρβανίτης

I'm developing an application that requires parsing of 
execution plans (those produced as output by issuing an EXPLAIN [query] 
command). Are you aware of any Java library that I could use for this 
purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. 


Also another option I am considering is to use EXPLAIN [query] FORMAT XML which 
is available in PostgreSQL 9.1. However, in that case it 
would better to have the XML Schema of the generated plans available.

Is there any other solution I am not aware of?

Thank you


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

Re: [HACKERS] Disable OpenSSL compression

On Thu, Nov 10, 2011 at 11:01 AM, Tom Lane  wrote:
> But in any case, my objection is that there's no adequate use-case
> for this GUC, because it's much more sensible to set it from the client
> side.  We have too many GUCs already --- Josh B regularly goes on the
> warpath looking for ones we can remove.  This one should never get in
> there to start with.

Of course, we also have no shortage of connection parameters.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Disable OpenSSL compression

"Albe Laurenz"  writes:
> Tom Lane wrote:
>> A GUC is entirely, completely, 100% the wrong answer.  It has no way
>> to deal with the fact that some clients may need compression and others
>> not.

> You can force a certain SSL cipher on the client, why not a compression
> setting?

To my mind, the argument for the ssl_cipher setting is to allow the DBA
to enforce a site-wide security policy to the effect of "we consider
only these ciphers strong enough for production use".  It's a pretty
weak argument (especially since the setting is not cognizant of where
the connection is coming from), but at least it's an argument.

There's no comparable security argument for an ssl_compression setting.
If anything, a security-minded DBA might wish to insist on compression
being *on*, but you aren't proposing to give him control in that
direction (and AFAICT openssl doesn't offer a force-on flag for it).

But in any case, my objection is that there's no adequate use-case
for this GUC, because it's much more sensible to set it from the client
side.  We have too many GUCs already --- Josh B regularly goes on the
warpath looking for ones we can remove.  This one should never get in
there to start with.

> I could go and try to convince Npgsql and JDBC to accept patches to
> do that on the client side, but that would be more effort than I
> want to invest.  But then there's still closed source software like
> Devart dotConnect...

This argument reads as nothing except "I'm too lazy to solve it right,
so I want you to accept a wrong solution".

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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

2011-11-10 Thread Jan Kundrát

On 11/10/11 16:05, Tom Lane wrote:
> I agree with Jan that this is probably useful; I'm pretty sure there
> have been requests for it before.  We just have to make sure that the
> length of the message stays in bounds.
> 
> One tip for keeping the length down: there is no value in repeating
> information from the primary error message, such as the name of the
> constraint.

Thanks to your comments and suggestions, I appreciate the time of the
reviewers.

Attached is a second version of this patch which keeps the size of the
output at 64 characters per column (which is an arbitrary value defined
as a const int, which I hope matches your style). Longer values have
their last three characters replaced by "...", so there's no way to
distinguish them from a legitimate string that ends with just that.
There's also no escaping of special-string values, similar to how the
BuildIndexValueDescription operates.

Cheers,
Jan

-- 
Trojita, a fast e-mail client -- http://trojita.flaska.net/
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 504f4de..9c2b285 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1364,10 +1364,42 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
const char *failed;
 
if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) != 
NULL)
+   {
+   StringInfoData buf;
+   int natts = rel->rd_att->natts;
+   int i;
+   initStringInfo(&buf);
+   for (i = 0; i < natts; ++i)
+   {
+   char *val;
+   Oid foutoid;
+   bool typisvarlena;
+   size_t fieldlen;
+   const int cutofflen = 64;
+   
getTypeOutputInfo(rel->rd_att->attrs[i]->atttypid, &foutoid, &typisvarlena);
+   if (slot->tts_isnull[i])
+   val = "NULL";
+   else
+   val = OidOutputFunctionCall(foutoid, 
slot->tts_values[i]);
+   if (i > 0)
+   appendStringInfoString(&buf, ", ");
+   fieldlen = strlen(val);
+   if (fieldlen > cutofflen)
+   {
+   appendBinaryStringInfo(&buf, val, 
cutofflen - 3);
+   appendStringInfoString(&buf, "...");
+   }
+   else
+   {
+   appendStringInfoString(&buf, val);
+   }
+   }
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
 errmsg("new row for relation \"%s\" 
violates check constraint \"%s\"",
-   
RelationGetRelationName(rel), failed)));
+   
RelationGetRelationName(rel), failed),
+errdetail("Failing row: (%s).", 
buf.data)));
+   }
}
 }
 


signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

Robert Haas  writes:
> On Thu, Nov 10, 2011 at 10:05 AM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> Well, if we're going to try to emit some context here, I'd suggest
>>> that we try to output only the columns implicated in the CHECK
>>> constraint, rather than the whole tuple.

>> I think that's likely to be impractical, or at least much more trouble
>> than the feature is worth.  Also, if you might emit only a subset of
>> columns, then you have to label them, a la the FK error messages:
>>Key (x,y,z) = (this,that,theother)
>> That's going to make the line length problem worse not better.

> Depends.  A lot of CHECK constraints may only reference one column:
> CHECK (a > 0).  The whole record is likely to be a lot longer than
> (a)=(-32768), and frankly tuples without column names aren't that
> readable anyway.

Well, the other concern here is: how much context does it take to
identify the problematic row?  It's entirely likely that showing only
the value of "a" isn't enough to solve the user's problem anyhow.
So I think the argument for showing a subset of columns is quite weak.

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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

Nikhil Sontakke  writes:
> PFA, a patch against git head. We take the AccessShareLock lock on the
> schema in DefineRelation now.

Um ... why would we do this only for tables, and not for creations of
other sorts of objects that belong to schemas?

Also, if we are going to believe that this is a serious problem, what
of ALTER ... SET SCHEMA?

Also, the proposed solution is pretty silly on its face, because it has
not removed the race condition only made the window somewhat narrower.
You would have to acquire the lock as part of the initial schema lookup,
not lock the OID after the fact.  And could we please not do something
as silly as translate the OID back to a string and then look up that
string a second time?

(To be clear, I don't particularly believe that this is a problem worthy
of spending code space and cycles on.  But if it's deemed to be a
problem, I want to see a solution that's actually watertight.)

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] MPI programming in postgreSQL backend source code

2011-11-10 Thread Rudyar



do you have more documentation about OPENMP and PostgreSQL?

El 09-11-2011 20:12, Greg Smith escribió:

 On 11/09/2011 04:10 PM, Rudyar Cortés wrote:

 I'm a new programmer in postgreSQL source code..
 Is possible use MPI functions in postgreSQL source code?


 To do this the proper way, you would need to modify the database's
 "configure" step to:

 -Check if the OpenMPI libraries are available and include the
 necessary bits.  For example, this is in one of the projects I work on:

 #ifdef _OPENMP
 extern int omp_get_num_threads();
 #endif

 Some form of that test and defining the functions available would be
 needed for what you want.

 -Link OpenMPI in.  At the gcc level you'll need "-fopenmp" to start.

 Then you could start using OpenMPI functions in database code.  You
 might hack the build steps to do this in a simpler way, too, rather
 than fight with configure the proper way.

 Since a lot of the MPI functions aim at specific types of thread use
 and I/O, it would be a major effort to utilize the library for too
 many things.  The existing notion of how processes are created and
 managed is scattered throughout the PostgreSQL code.  And the I/O
 parts of the database are buried through a few layers of indirection.




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

Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

On Thu, Nov 10, 2011 at 10:05 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Nov 10, 2011 at 5:40 AM, Jan Kundrát  wrote:
>>> Would you object to a patch which outputs just the first 8kB of each
>>> column? Having at least some form of context is very useful in my case.
>
>> Well, if we're going to try to emit some context here, I'd suggest
>> that we try to output only the columns implicated in the CHECK
>> constraint, rather than the whole tuple.
>
> I think that's likely to be impractical, or at least much more trouble
> than the feature is worth.  Also, if you might emit only a subset of
> columns, then you have to label them, a la the FK error messages:
>        Key (x,y,z) = (this,that,theother)
> That's going to make the line length problem worse not better.

Depends.  A lot of CHECK constraints may only reference one column:
CHECK (a > 0).  The whole record is likely to be a lot longer than
(a)=(-32768), and frankly tuples without column names aren't that
readable anyway.

I'd argue that to some degree, CHECK constraints, like UNIQUE
constraints, probably tend to be placed primarily on relatively short
columns.  Now, UNIQUE constraints have a hard limitation, because a
too-large value won't fit into an index block.  And certainly you
could do CHECK (document_is_valid_json(mumbleblump)).  But many things
that contain large amounts of text will just be free text fields, they
won't be part of any constraint, and including them will just make
things unreadable.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Disable OpenSSL compression

2011-11-10 Thread Albe Laurenz

Tom Lane wrote:
>>> Is the following proposal acceptable:
>>>
>>> - Add a GUC ssl_compression, defaulting to "on".
>>> - Add a client option "sslcompression" and an environment variable
>>> PGSSLCOMPRESSION, defaulting to "1".

> A GUC is entirely, completely, 100% the wrong answer.  It has no way
to
> deal with the fact that some clients may need compression and others
> not.

If you leave the GUC at its default value, you can control compression
on the client side.

You can force a certain SSL cipher on the client, why not a compression
setting?

> It should be a client option, full stop.  The fact that that will be
> more work to implement does not make "kluge it at the server" the
right
> answer.

I could go and try to convince Npgsql and JDBC to accept patches to
do that on the client side, but that would be more effort than I
want to invest.  But then there's still closed source software like
Devart dotConnect...

In my environment it would make sense to control the setting on the
server side, because all our database clients connect via LAN, and
network bandwidth is not the bottleneck in our database applications.

Yours,
Laurenz Albe

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

Re: [HACKERS] Disable OpenSSL compression

2011-11-10 Thread Magnus Hagander

On Thursday, November 10, 2011, Andrew Dunstan wrote:

>
>
> On 11/08/2011 12:39 PM, Tom Lane wrote:
>
>> Jeroen Vermeulen  writes:
>>
>>> Another reason why I believe compression is often used with encryption
>>> is to maximize information content per byte of data: harder to guess,
>>> harder to crack.  Would that matter?
>>>
>> Yes, it would.  There's a reason why the OpenSSL default is what it is.
>>
>>
>>
>
>
> An interesting data point on this is that RedHat's nss_compat_ossl package
> doesn't support SSL compression at all  wiki/Nss_compat_ossl >,
> and it's supposed to be a path to FIPS 140 compliance: <
> http://fedoraproject.org/**wiki/FedoraCryptoConsolidation
> **>. The latter URL, incidentally, contains a lot of good information,
> and lays out many of the reasons why I'd like to see us support NSS as an
> alternative to OpenSSL, notwithstanding the supposed dirtiness of its API.
> I imagine this would be of interest to commercial Postgres vendors also.


Interesting points. I hadn't really considered it from the FIPS perspective.

I thought the main idea was that if we want to support another one it's
probably going to be GnuTLS because that one offers key-file-compatibility
with OpenSSL, which NSS doesnät.

//Magnus



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

Robert Haas  writes:
> On Thu, Nov 10, 2011 at 5:40 AM, Jan Kundrát  wrote:
>> Would you object to a patch which outputs just the first 8kB of each
>> column? Having at least some form of context is very useful in my case.

> Well, if we're going to try to emit some context here, I'd suggest
> that we try to output only the columns implicated in the CHECK
> constraint, rather than the whole tuple.

I think that's likely to be impractical, or at least much more trouble
than the feature is worth.  Also, if you might emit only a subset of
columns, then you have to label them, a la the FK error messages:
Key (x,y,z) = (this,that,theother)
That's going to make the line length problem worse not better.

I concur with just length-limiting the dumped values, and in fact would
prefer a limit much more draconian than 8K.  Don't we limit the key
lengths to 1K or so in FK and unique-key messages?  If the goal is to
identify the problematic line, I would think that a few dozen bytes per
column would be plenty.

> I'm not sure whether
> emitting only a certain amount of output (either total, or for each
> column) can be made to work nicely, or whether the feature overall is
> something we want.  It seems like a trade-off between possibly useful
> context and possibly annoying log clutter, and I guess I don't have a
> strong opinion on which way to go with it.

I agree with Jan that this is probably useful; I'm pretty sure there
have been requests for it before.  We just have to make sure that the
length of the message stays in bounds.

One tip for keeping the length down: there is no value in repeating
information from the primary error message, such as the name of the
constraint.

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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

2011-11-10 Thread Kääriäinen Anssi


"""
What I want to find in the end is something which tells me "this row
causes the error". Unfortunately, as the new row of the table with the
constraint is not yet on disk, it doesn't really have its own ctid, and
therefore I cannot report that. (Which makes sense, obviously.)
"""

Would an error with the row's PK value be useful? Something like "row
with primary key 'pk_val' fails check 'foo_check'". That would be limited
in size, yet give some context.

There are two problems I can see:
  - The PK value doesn't necessarily identify the row in any useful
manner (SERIAL primary key in INSERT).
  - The table might lack PK constraint (skip the detail in this case?)

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

Re: [HACKERS] Disable OpenSSL compression




On 11/08/2011 12:39 PM, Tom Lane wrote:

Jeroen Vermeulen  writes:

Another reason why I believe compression is often used with encryption
is to maximize information content per byte of data: harder to guess,
harder to crack.  Would that matter?

Yes, it would.  There's a reason why the OpenSSL default is what it is.





An interesting data point on this is that RedHat's nss_compat_ossl 
package doesn't support SSL compression at all 
, and it's supposed to be 
a path to FIPS 140 compliance: 
. The latter 
URL, incidentally, contains a lot of good information, and lays out many 
of the reasons why I'd like to see us support NSS as an alternative to 
OpenSSL, notwithstanding the supposed dirtiness of its API. I imagine 
this would be of interest to commercial Postgres vendors also.


cheers

andrew

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

Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

2011-11-10 Thread Jan Kundrát

On 11/10/11 13:04, Robert Haas wrote:
> Well, if we're going to try to emit some context here, I'd suggest
> that we try to output only the columns implicated in the CHECK
> constraint, rather than the whole tuple.  I'm not sure whether
> emitting only a certain amount of output (either total, or for each
> column) can be made to work nicely, or whether the feature overall is
> something we want.  It seems like a trade-off between possibly useful
> context and possibly annoying log clutter, and I guess I don't have a
> strong opinion on which way to go with it.

OK, let me start with some background on why I actually want to have
such a feature.  The project which we're working on [1] (and [2] for
some context about why the hell we bother) allows users to define layout
of their DB tables using standard CREATE TABLE ... stanzas, including
various triggers, check constraints etc etc.  What our project does is
generating plenty of stored procedures which essentially built a
version-control infrastructure around the user-specified table layout.

Our workflow utilizes something similar to the concept of a working copy
in Subversion. It means that any modifications that users perform are
executed on an extra table (the history one) which does not enforce any
user-specified constraints. It's only at the time of a commit, where
data is moved by `UPDATE tabl SELECT ... FROM tbl_history where revision
= $pending_changeset` to its final destination and all the checks,
triggers and constraints are enforced.

The issue which we've hit is that when the user has specified a CHECK
constraint and tries to save many rows at once, we don't have any
information about what went wrong besides the name of the check which
failed.  It's better than nothing, but given that Pg provides very
similar information for UNIQUE columns, it looked like a good feature to
implement.

What I want to find in the end is something which tells me "this row
causes the error". Unfortunately, as the new row of the table with the
constraint is not yet on disk, it doesn't really have its own ctid, and
therefore I cannot report that. (Which makes sense, obviously.) I also
realize that our use case is a bit esoteric and very far from the
mainstream Postgres applications, but I believe that simply having
detailed error messages is a good thing overall. Of course it's clearly
possible that we're doing it completely wrong, so if someone has a
suggestion or would like to chat about that, I'm all ears (feel free to
go off-list here).

Now I realize that there might be some concerns about error log
cluttering etc. On the other hand, I'd take it for granted that it's a
good idea to include at least *some* context in the error messages (and
I assume that's what the detail field is for). If it's acceptable for
UNIQUE constraints to show the index values (which are enough to
identify the troublesome row), it seems to me that extending this to
CHECKs is a natural further development and leads to better consistency.

As I've said earlier, I'm not at all familiar with Postgres' internals,
so before I go ahead and spend another night finding out how to look at
the table/check metadata and print just the columns which are referenced
by a CHECK, if that's even possible, I'd like to know whether such a
patch would be welcome and accepted or not :).

Again, a big thank you for your review -- it's much appreciated.

Cheers,
Jan

[1] https://projects.flaska.net/projects/deska
[2]
https://projects.flaska.net/attachments/download/74/2011-11-10-deska-18e4c5b.pdf

-- 
Trojita, a fast e-mail client -- http://trojita.flaska.net/

signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message