Re: [HACKERS] Where's the docs?

[Peter Eisentraut]

On Wed, 2009-10-28 at 00:25 -0400, Tom Lane wrote:
> Josh Berkus  writes:
> > OK, this is the genuine failure; the syntax is missing for column triggers:
> 
> > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
> > EXECUTE PROCEDURE function_name ( arguments )
> 
> It's embedded in "event", which isn't spelled out here.

I know this is a bit suboptimal, but I couldn't think of a better way
without cluttering up to many things.


-- 
Sent 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 config files in a directory

[Greg Stark]

On Tue, Oct 27, 2009 at 8:40 PM, Josh Berkus  wrote:
> You're hearing from the people who are working on tools: requiring that
> any tool parse a hand-written config file is a non-starter.

It can be done, pgadmin actually does it currently. But I totally
agree it's a bad idea.

But the difficulty of parsing the handwritten stuff is not the only
reason it's a bad idea. Any time you have multiple pieces of software,
to say nothing of humans, editing the same file you're going to have
headaches. They need to agree on everything and be able to handle
anything any other program generates. Such a file would be a kind of
API itself.

It's much simpler and more reliable to have each program generate a
separate file. Each program can just do its calculations and dump out
a file with those variables. It doesn't have to worry about the
ordering, indentation, or precise formatting. It can put whatever
comments it wants to explain how they're calculated or to warn that
it's an auto-generated file and any changes will be replaced the next
time it's generated. It doesn't have to worry about anything else
parsing or making sense of the file except the database server itself.

-- 
greg

-- 
Sent 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 config files in a directory

[Greg Smith]

On Tue, 27 Oct 2009, Robert Haas wrote:

I guess I didn't consider the possibility that someone might reuse an 
8.4 postgresql.conf on an 8.5 server.  That could be awkward.


Happens all the time, and it ends up causing problems like people still 
having settings for GUCs that doesn't even exist anymore.  You know how we 
could make this problem less likely to bite people?  By putting everything 
the user wants to customize that isn't done by initdb into another file. 
Then they can just move that file into the new version.  That's the 
direction we're trying to move here, except much slower than you're 
suggesting because we've already through about some of these gotchas. 
Obviously you could do the same thing by completely gutting the whole 
postgresql.conf, but I was hoping for a step in the right direction that 
doesn't require something that drastic yet.


The length of this thread has already proven why it's not worth even 
trying to completely trim the file down.  Had you never brought that up 
this discussion would be done already.  If you have a strong feeling about 
this, write a patch and submit it; I'm not going to talk about this 
anymore.


I was thinking that the algorithm would be something like: "Read the old 
postgresql.conf and write it back out to a new file line by line


This sounds familiar...oh, that's right, this is almost the same algorithm 
pgtune uses.  And it sucks, and it's a pain to covert the tool into C 
because of it, and the fact that you have to write this sort of boring 
code before you can do a single line of productive work is one reason why 
we don't have more tools available; way too much painful grunt work to 
write.



True, but actually having a good SET PERSISTENT command would solve
most of this problem, because the tools could just use that.


The system running the tool and the one where the changes are being made 
are not the same.  The database isn't necessarily even up when the tool is 
being run yet.  The main overlap here is that one of the output formats 
available to future tools could be a series of SET PERSISTENT commands one 
could then run elsewhere, which is already on my pgtune roadmap when it's 
possible to implement.


You're doing a good job of reminding me why I didn't have a good vision of 
where this all needed to go until after I wrote a working tuning tool, to 
get a feel for the painful parts.  I wish I could share all of the 
postgresql.conf files I've seen so you could better appreciate how people 
torture the poor file in the field.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] Where's the docs?

[Tom Lane]

Josh Berkus  writes:
> OK, this is the genuine failure; the syntax is missing for column triggers:

> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
> EXECUTE PROCEDURE function_name ( arguments )

It's embedded in "event", which isn't spelled out here.

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] Where's the docs?

[Josh Berkus]

Tom,

>> I'm looking at the "developer docs" on our site, and I couldn't find any
>> docs for the following features:
> 
>> Column Triggers
> 
> http://developer.postgresql.org/pgdocs/postgres/sql-cre

atetrigger.html

OK, this is the genuine failure; the syntax is missing for column triggers:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE function_name ( arguments )

Does a doc patch already exist for this, or do I need to write one?

>> Calling Named Function parameters
> 
> http://developer.postgresql.org/pgdocs/postgres/sql-syntax-calling-funcs.html
> 
>> DEFAULT privileges
> 
> http://developer.postgresql.org/pgdocs/postgres/sql-alterdefaultprivileges.html

Thanks for these, I wasn't finding them.  Problem of no search on the
developer docs ...

--Josh Berkus


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

Re: [HACKERS] SE-PgSQL developer documentation (Re: Reworks for Access Control facilities (r2363))

[KaiGai Kohei]

Robert Haas wrote:
> 2009/10/27 KaiGai Kohei :
>> - no statement support to specify security context.
>>  (It makes impossible to add support in pg_dump. Is it really OK?)
> 
> I doubt that anything without pg_dump support would be even vaguely OK...

In my previous experience, it enabled to reduce 300-400 lines of the patch.
But here is no more sense than the 300-400 lines.

In my honest, I like to include a feature to specify an explicit security
context in the patch from the begining.
(It also allows to attach test cases with more variations.)
-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

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

Re: [HACKERS] Where's the docs?

[Tom Lane]

Josh Berkus  writes:
> I'm looking at the "developer docs" on our site, and I couldn't find any
> docs for the following features:

> Column Triggers

http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html

> Calling Named Function parameters

http://developer.postgresql.org/pgdocs/postgres/sql-syntax-calling-funcs.html

> DEFAULT privileges

http://developer.postgresql.org/pgdocs/postgres/sql-alterdefaultprivileges.html

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 config files in a directory

[Josh Berkus]

On 10/27/09 8:24 PM, Robert Haas wrote:
> read the old postgresql.conf and
> write it back out to a new file line by line.  If, in the process of
> doing this, you find a setting for the variable you're trying to
> change, then write out the new line in place of the original line. 

You've hit the problem on the head right there.  The requirement to do
something like that is *exactly* the problem which makes writing
config-management tools hard/impossible.

If you require that a tool (or SET PERISTENT) parse through a file in
order to change one setting, then you've just doubled or tripled the
code size of the tool, as well as added a host of failure conditions
which wouldn't have existed otherwise.

You're hearing from the people who are working on tools: requiring that
any tool parse a hand-written config file is a non-starter.

--Josh Berkus

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

[HACKERS] Where's the docs?

[Josh Berkus]

Folks,

I'm looking at the "developer docs" on our site, and I couldn't find any
docs for the following features:

Column Triggers
Calling Named Function parameters
DEFAULT privileges

... without docs, we really can't expect people to test them.  Do we
have partial docs for these?  Am I not looking in the right place?

--Josh Berkus

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

Re: [HACKERS] SE-PgSQL developer documentation (Re: Reworks for Access Control facilities (r2363))

[Robert Haas]

2009/10/27 KaiGai Kohei :
> - no statement support to specify security context.
>  (It makes impossible to add support in pg_dump. Is it really OK?)

I doubt that anything without pg_dump support would be even vaguely OK...

...Robert

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

Re: [HACKERS] Parsing config files in a directory

[Robert Haas]

On Tue, Oct 27, 2009 at 10:53 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I guess all I'm saying is that if we took the approach of making SET
>> PERSISTENT rewrite postgresql.conf, we actually could let people do it
>> either way they pleased without the complexity of having multiple
>> files.
>
> You keep saying that, but what you don't seem to get is that that
> amounts to telling the people who want to go slow that they should
> go jump in a lake.  The moment they even experiment with SET PERSISTENT,
> everything they've ever done with postgresql.conf goes up in smoke.
> This is not going to be acceptable.

I guess I didn't consider the possibility that someone might reuse an
8.4 postgresql.conf on an 8.5 server.  That could be awkward.  But
even if they do, it's not *nearly* as bad as "everything they've ever
done with postgresql.conf goes up in smoke".  I was thinking that the
algorithm would be something like: "Read the old postgresql.conf and
write it back out to a new file line by line.  If, in the process of
doing this, you find a setting for the variable you're trying to
change, then write out the new line in place of the original line.  If
you subsequently find anymore output lines that set that same
variable, then skip writing them to the output file altogether.  If
you get to the end of the file without finding the setting you're
trying to change, then append a new line setting that variable to the
specified value."

If someone uses this on a setting that already exists - uncommented -
in their legacy postgresql.conf file, then at most they will lose any
comment on the same line as the setting they changed.  The new setting
will end up on the same line in the new file as the old one, with all
the same comments around it.

If someone uses this on a setting that does not exist in their legacy
postgresql.conf file, the new setting will just be appended to the
end, while the rest of the file will remain unchanged.  At worst,
they'll be surprised that the setting ended up not where they were
expecting it to be - but if they were expecting SET PERSISTENT to
notice where the commented-out version of the setting was and put the
new value there, they're going to be disappointed under any
implementation we're likely to settle on (having the new setting in a
different file altogether doesn't seem better, at least not to me).

> Furthermore, that approach is not especially simple from the tools'
> standpoint either, because then the minimum bar for doing anything at
> all is the ability to parse postgresql.conf, remove conflicting old
> settings, and add your own.  Even without any comment support, that is
> an order of magnitude harder than just dropping a prebuilt file into a
> directory, which is feasible for at least some use-cases with the
> directory approach.

True, but actually having a good SET PERSISTENT command would solve
most of this problem, because the tools could just use that.  The
ability to just drop in a file is superficially attractive, but I
think it's a red herring.  Whatever settings the tool sets will still
potentially be set elsewhere in other files (maybe even files created
by other tools).  So tool A comes and drops in a file that sets
work_mem, checkpoint_segments, and wal_buffers, and then tool B comes
along and sets max_connections, a couple of autovacuum settings, and
checkpoint_segments again.  Then tool C comes along and does something
else again, and now the DBA wants to change a setting with SET
PERSISTENT, while the other DBA edits postgresql.conf using $EDITOR.

We may have tools to tell you which file is providing the value for
any particular parameter, but I don't think they're going to make this
kind of situation un-confusing.  Normally, you're going to want the
value for any particular parameter to be the one the value to which it
was most recently changed, and that just won't be the case with this
setup, at least not without some pretty substantial gymnastics.

...Robert

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

Re: [HACKERS] SE-PgSQL developer documentation (Re: Reworks for Access Control facilities (r2363))

[KaiGai Kohei]

Now I'm working on writing a documentation from the viewpoint of
developers as follows (It is a work in progress):

http://code.google.com/p/sepgsql/source/browse/trunk/sepgsql/src/backend/security/sepgsql/README

Is there any differences between what I want to describe and what you
want to know? If so, I'll add or modify the documentation.

As I noted before, I decided to slim up my patch on the next commit fest
to reduce the burden of reviewing.

The following functionalities will be separated from the prior version.
- no access controls on procedures.
  -> so, the slim version only support checks on databases, schemas,
 tables and columns.
- no statement support to specify security context.
  (It makes impossible to add support in pg_dump. Is it really OK?)
- no userspace caches for access control decision
- no system catalog changes to store its security context
  -> I'll adopt an approach that Stephen Frost suggested before.
 It manages a pair of OID and security context in separated system catalog.

Is there any comment? Or, more detailed introductions are necessary?

Thanks,

KaiGai Kohei wrote:
> KaiGai Kohei wrote:
>> After the long trial-and-errors, we learned a few approaches which
>> use common entry points for both of DAC and MAC were rocky-path more
>> than what we initially imagined.
>> So, we came back to the original design. It deploys MAC hooks on
>> the strategic points of core routines. On the other hand, people
>> complained about this approach without clear documentation, because
>> most of people are not familiar to both of SELinux and PgSQL.
>> Heikki suggested that a clear developer documentation should be
>> provided to understand pgsql-hackers this new concept.
>> (And, Peter has also suggested before a developer documentation will
>> be a good source of user documentations.)
>>
>> I plan to submit SE-PgSQL/lite patch with developer documentations
>> on the next commit-fest.
>> I can understand what I want to develop and the purpose of codes.
>> However, it may not match with what you want to know.
>>
>> So, I'd like to ask what should be included within the developer
>> documentation at first prior to making a documentation.
>>
>> I plans the developer documentation should be put as a REAME file,
>> not a SGML documentation or a certain wiki page.
>> And I think it should contain the following items.
>>
>> * overview
>>  - general overview of SE-PgSQL
>>  - introduction of SELinux specific terms (such as "security context")
>>
>> * internal architecture
>>  - the purpose of sub-components (such as management of security
>>context, caches of access control decision and so on)
>>  - differences from similar permissions in DAC
>>
>> * object classes and permissions defined in SELinux model
>>  - list of them and when/where they should be checked.
>>
>> * specification of SE-PgSQL hooks
>>   (It should be put on the source code comments for easy maintenance.)
>>  - what this hook does, what arguments are required, what result will
>>be returned.
>>
>> * code examples
>>  - a few examples to add MAC checks within 3rd party modules.
>>
>> Do you have any comments? What should be added to? or removed from?
> 
> I guess it was too abstract to suggest anything.
> Anyway, I'll begin to describe the developer documentation base on the 
> chapters.
> If necessary, we can fix it up later.
> 
> In addition, I determined the initial patch only covers access controls on
> the four object classes (database, schema, table and column) to reduce burdens
> of reviewing.
> We also can add support for other object classes (such as procedure) later.
> 
> Thanks,


-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Robert Haas]

On Tue, Oct 27, 2009 at 10:18 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> For things like autovacuum options, the actual representation probably
>> doesn't matter much because I'm guessing that the amount of work being
>> done by vacuum dwarfs the parsing cost, and it's a background task
>> anyway.  But this seems like a less solid argument for things like
>> fillfactor and the proposed per-tablespace
>> seq_page_cost/random_page_cost, which will be accessed by many queries
>> and in the latter case often more than once.  Ideally (or so it seems
>> to me) you'd like to fetch those things out of Form_pg_whatever rather
>> than parsing text strings to get at 'em.
>
> I think efficiency arguments here are hogwash.  In the first place,
> we'd certainly cache the results someplace (relcache or something like
> it) if retrieve performance seems to be a bottleneck at all.  In the
> second place, composite types are so hugely inefficient as to swamp any
> gain you'd get from the columns being the right type once you got at
> them.  (atoi and friends are cheap by comparison.)

We must be talking about different things, because I can't believe
this is true of what I'm thinking about.  I'm not suggesting having a
column called reloptions of composite type; I'm suggesting that an
option like fillfactor would have its very own table column, just like
relpages or relhasindex.  Surely that's gotta be faster than text; it
overlays onto a C struct, which is about as fast as it gets.

I agree that caching mitigates many of the problems with this from an
efficiency standpoint, possibly to the point where it isn't worth
caring about.  But it does seem grotty, and I feel like it has to cost
something: we read in an array of text[] and convert it to a C struct,
which is exactly the form it would already be in if we just made it
part of Form_pg_class in the first place.  The only way I can think
that the current representation could be faster is that when there are
NO reloptions at all, the parsing step can be skipped, and yet overall
Form_pg_class is smaller than it would be otherwise, which is of some
miniscule benefit.

> It's possible that changing this is worthwhile on logical cleanliness
> grounds; but I think it will be a net loss in efficiency, and definitely
> a net loss in terms of code complexity at the C level.

One of my concerns about the current representation is that it doesn't
seem easily generalizable to objects that are not in pg_class, such as
tablespaces.  I fear that supporting spcoptions as text[] along the
lines of reloptions will require quite a bit of refactoring to avoid
code duplication, whereas adding a few new columns to pg_tablespace
and maybe making a syscache for it looks pretty simple.  On the other
hand, that would leave us with completely different representations
for essentially the same sort of data, which isn't particularly
appealing either.

...Robert

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

Re: [HACKERS] Parsing config files in a directory

[Tom Lane]

Robert Haas  writes:
> I guess all I'm saying is that if we took the approach of making SET
> PERSISTENT rewrite postgresql.conf, we actually could let people do it
> either way they pleased without the complexity of having multiple
> files.

You keep saying that, but what you don't seem to get is that that
amounts to telling the people who want to go slow that they should
go jump in a lake.  The moment they even experiment with SET PERSISTENT,
everything they've ever done with postgresql.conf goes up in smoke.
This is not going to be acceptable.

Furthermore, that approach is not especially simple from the tools'
standpoint either, because then the minimum bar for doing anything at
all is the ability to parse postgresql.conf, remove conflicting old
settings, and add your own.  Even without any comment support, that is
an order of magnitude harder than just dropping a prebuilt file into a
directory, which is feasible for at least some use-cases with the
directory approach.

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 config files in a directory

[Robert Haas]

On Tue, Oct 27, 2009 at 9:05 PM, Josh Berkus  wrote:
>> The Apache model is definitely the first of these, AFAICS.  The
>> proposals on this thread mostly seem to be an amalgam of both, which
>> doesn't strike me as a terribly good idea, but evidently I'm in the
>> minority.
>
> Well, an individual DBA would not want to do it both ways.  But we
> should *allow* both ways rather than trying to mandate how the files get
> created or what their names are.

I guess all I'm saying is that if we took the approach of making SET
PERSISTENT rewrite postgresql.conf, we actually could let people do it
either way they pleased without the complexity of having multiple
files.

The only reason we can't do that today is because postgresql.conf
contains unparseable comments.  The only way to fix that problem
completely is, as Tom says, to remove the ability to have comments.
But that seems like overkill.  If we simply removed most of the
comments that are there by default, then we could say: "You can edit
this file with a text editor.  Or you can edit it using SET
PERSISTENT.  Or you can do both.  But if you do both, your comments
may end up getting moved relative to your settings.  So if you care
about that, then don't use SET PERSISTENT.  In fact, if you want,
there's a GUC called enable_set_persistent that you can set to false."

That seems like a win for everyone.  People who want to use a text
editor can do so.  People who want to use SET PERSISTENT can do so.
People who want to do both can do so, too, and without the confusion
of having two different places for settings one of which will override
the other.  I think the only people who will be unhappy are (1) people
who like the current really long postgresql.conf [but the previous
discussion of this topic suggested there weren't too many of those]
and (2) people who want to edit postgresql.conf by hand AND want to
edit it with SET PERSISTENT AND can't stand having their comments
shuffled around relative to their settings [but these people will
never be happy no matter what we do].

But evidently this is not such a good idea as I think it is, or else
I've been explaining it really, really badly.

...Robert

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

[HACKERS] pg_read_file() and non-ascii input file

[Itagaki Takahiro]

pg_read_file() takes byte-offset and length as arguments,
but we don't check the result text with pg_verify_mbstr().
Should pg_read_file() return bytea instead of text or adding
some codes to verify the input? Only superusers are allowed
to use the function, but it is still dangerous.

If we leave the result in text type and add verifier, we also need to
consider how to handle multi-byte text. Offset and length should not
split one multi-byte character. We can assume the offset as a correct
boundary if we can trust users, but no one knows correct length before
the function call.

An idea is to have binary and text versions of pg_read_file:
  * pg_read_binary_file(filename, offset, length) : bytea
  * pg_read_text_file(filename, offset) : ROW( text, nextline_offset )
  -- it returns the next line starting with 'offset'.
but such changes could bring on compatibility problems.

Comments, better ideas?

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Tom Lane]

Robert Haas  writes:
> For things like autovacuum options, the actual representation probably
> doesn't matter much because I'm guessing that the amount of work being
> done by vacuum dwarfs the parsing cost, and it's a background task
> anyway.  But this seems like a less solid argument for things like
> fillfactor and the proposed per-tablespace
> seq_page_cost/random_page_cost, which will be accessed by many queries
> and in the latter case often more than once.  Ideally (or so it seems
> to me) you'd like to fetch those things out of Form_pg_whatever rather
> than parsing text strings to get at 'em.

I think efficiency arguments here are hogwash.  In the first place,
we'd certainly cache the results someplace (relcache or something like
it) if retrieve performance seems to be a bottleneck at all.  In the
second place, composite types are so hugely inefficient as to swamp any
gain you'd get from the columns being the right type once you got at
them.  (atoi and friends are cheap by comparison.)

It's possible that changing this is worthwhile on logical cleanliness
grounds; but I think it will be a net loss in efficiency, and definitely
a net loss in terms of code complexity at the C level.

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] per-tablespace random_page_cost/seq_page_cost

[Robert Haas]

On Tue, Oct 27, 2009 at 9:20 PM, David Fetter  wrote:
> On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
>> Robert Haas  writes:
>> > I confess that I'm a bit mystified about the design of the
>> > reloptions stuff. It seems kind of odd to store structured data as
>> > text[]; it's kind of the opposite of what I would normally
>> > recommend as good database design.
>>
>> It's definitely a bit EAV-ish :-(.  But I don't see any particularly
>> easy way to modify it to store bool/int/float parameters in their
>> native types; do you?
>
> More columns, each of the correct type, with the table constraint that
> at most one may be populated is how I handle stuff like that.

I don't see why we'd need to constrain more than one from being
populated, but yeah, that's basically what I was thinking: one column
per parameter, of the appropriate type.  That might not be such a good
model if the number of possible options was really large, but at this
point there's no reason to believe that will be the case for either
reloptions or the proposed spcoptions.

For things like autovacuum options, the actual representation probably
doesn't matter much because I'm guessing that the amount of work being
done by vacuum dwarfs the parsing cost, and it's a background task
anyway.  But this seems like a less solid argument for things like
fillfactor and the proposed per-tablespace
seq_page_cost/random_page_cost, which will be accessed by many queries
and in the latter case often more than once.  Ideally (or so it seems
to me) you'd like to fetch those things out of Form_pg_whatever rather
than parsing text strings to get at 'em.

...Robert

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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Alvaro Herrera]

David Fetter escribió:
> On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
> > Robert Haas  writes:
> > > I confess that I'm a bit mystified about the design of the
> > > reloptions stuff. It seems kind of odd to store structured data as
> > > text[]; it's kind of the opposite of what I would normally
> > > recommend as good database design.
> > 
> > It's definitely a bit EAV-ish :-(.  But I don't see any particularly
> > easy way to modify it to store bool/int/float parameters in their
> > native types; do you?
> 
> More columns, each of the correct type, with the table constraint that
> at most one may be populated is how I handle stuff like that.

So we would have "spcintoptions", "spcfloatoptions", and so on?  (I
don't see the need for the table constraint in this case.)

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

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

Re: [HACKERS] [ADMIN] pg_standby doesnt't work

[Fujii Masao]

Hi,

On Tue, Oct 27, 2009 at 4:53 PM, Andreas Schmidt  wrote:
> Hi, thank you for your response.
> But does this mean, that only WinXP is no supported or doesn't it work at 
> all? I would need it on a Windows2003Server???

According to the previous post, pg_standby in v8.3.7 would work file.
How about using that?
http://archives.postgresql.org/pgsql-bugs/2009-08/msg00094.php

Regards,

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

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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[David Fetter]

On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
> Robert Haas  writes:
> > I confess that I'm a bit mystified about the design of the
> > reloptions stuff. It seems kind of odd to store structured data as
> > text[]; it's kind of the opposite of what I would normally
> > recommend as good database design.
> 
> It's definitely a bit EAV-ish :-(.  But I don't see any particularly
> easy way to modify it to store bool/int/float parameters in their
> native types; do you?

More columns, each of the correct type, with the table constraint that
at most one may be populated is how I handle stuff like that.

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

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Tom Lane]

Robert Haas  writes:
> I confess that I'm a bit mystified about the design of the reloptions
> stuff. It seems kind of odd to store structured data as text[]; it's
> kind of the opposite of what I would normally recommend as good
> database design.

It's definitely a bit EAV-ish :-(.  But I don't see any particularly
easy way to modify it to store bool/int/float parameters in their native
types; do you?

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 config files in a directory

[Josh Berkus]

Robert,

> The Apache model is definitely the first of these, AFAICS.  The
> proposals on this thread mostly seem to be an amalgam of both, which
> doesn't strike me as a terribly good idea, but evidently I'm in the
> minority.

Well, an individual DBA would not want to do it both ways.  But we
should *allow* both ways rather than trying to mandate how the files get
created or what their names are.

--Josh Berkus


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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Robert Haas]

On Mon, Oct 26, 2009 at 11:07 PM, Tom Lane  wrote:
> Andres Freund  writes:
>> On Tuesday 27 October 2009 00:42:39 Tom Lane wrote:
>>> I think a design that is limited to a prespecified set of GUCs is
>>> broken by definition.  It'd be better to make it work like
>>> ALTER DATABASE SET.
>
>> How should that work if there are conflicting settings in two tablespaces 
>> when
>> tables from both are used?
>
> Well, most of the settings that would be sensible for this are used in
> cost estimates that are basically per-table or per-index, so I don't
> think it's a huge problem in practice.  But I should clarify my comment:
> the set of GUCs used this way must not be wired into the catalog
> structure.  I think that the code will only pay attention to certain
> GUCs that are valid in-context, but we shouldn't have to redesign the
> catalog each time we add one.

These don't exactly fit into the GUC framework because AIUI a GUC is a
global variable, and the function of the GUC machinery is simply to
make sure that the global variable in question is set to the right
value at the right time.  These are really more like reloptions (that
may happen to have the same name as GUCs, I suppose) - always in
effect, but only for a particular object.

I confess that I'm a bit mystified about the design of the reloptions
stuff. It seems kind of odd to store structured data as text[]; it's
kind of the opposite of what I would normally recommend as good
database design.

...Robert

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

Re: [HACKERS] Parsing config files in a directory

[Robert Haas]

On Tue, Oct 27, 2009 at 2:59 PM, Greg Stark  wrote:
> On Tue, Oct 27, 2009 at 11:06 AM, Robert Haas  wrote:
>> IME, the use case for multiple Apache configuration files is that
>> there are bits of configuration that support particular modules which
>> packagers want installed only in conjunction with the corresponding
>> modules - it has nothing to do with being able to automate config-file
>> updates, or at least I am not aware that it does.
>
> That sounds like automated config file updates to me. Individual
> modules are being installed and uninstalled and automatically updating
> the configuration to handle the modules.

Well, OK, fair enough.  I guess my point is that there are two things
that you might want:

- multiple config files separated by domain (e.g. this is the config
file for autoexplain, and this one is for vacuum)
- multiple config files separated by how they are updated (e.g. this
config file is only for people with text editors, and this one is for
people using SET PERSISTENT)

The Apache model is definitely the first of these, AFAICS.  The
proposals on this thread mostly seem to be an amalgam of both, which
doesn't strike me as a terribly good idea, but evidently I'm in the
minority.

...Robert

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

Re: [HACKERS] Proposal: String key space for advisory locks

[Christophe Pettus]

On Oct 27, 2009, at 4:37 PM, Merlin Moncure wrote:

'as is', advisory locks is a fantastic feature that can be used for
signaling, mutexing, etc that are relatively difficult things to do in
the transactional world of sql.  My main gripe is that the 'shared id'
method for doing record pessimistic locks is basically a nuclear
missile pointed at your shared buffers if you don't have lot of
discipline in the queries that lock IDs.  Maybe this argues for more
of a 'sql exposed' pessimistic lock feature that operates on similar
level as 'for update'...I'm not sure...curious what thoughts you have
about improving them.


Advisory locks have, as you say, a raft of very useful characteristics:

1. Enforced as much or as little as you wish, depending on your  
application design.

2. Race-condition-free.
3. Cleaned up automatically on session end.

Of course, 2^64 potential entries are enough for anyone.  The  
usability issue comes when you have multiple domains that you want to  
apply advisory locks to in a single database.  For example, if you  
have multiple tables (one of which, just for example, has a character  
pk), and perhaps some inter-client mutex signaling for things that are  
outside of a transactional model ("this client is importing a file  
from an outside source, so don't you do it"), it's unappealing to have  
to come up with ways of representing those in a 64-bit namespace.


Hashing isn't a terrible solution, assuming collisions don't become an  
issue; a well-designed hashtext64() would help a lot.

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Proposal: String key space for advisory locks

[Merlin Moncure]

On Tue, Oct 27, 2009 at 12:43 PM, Josh Berkus  wrote:
> Merlin,
>
>> Why even bother with a hash function when you can just have multiple
>> table pull from a shared sequence?  AFAICT, this solves the OP's
>> problem with no downsides (I used the approach with excellent results
>> in a ported cobol app which had pessimistic locking requirement).
>
> Well, if you have enough tables, the sequence itself becomes a
> bottleneck

I wonder if that's a legacy problem...I tested on our development
server w/pgbench -f and measured that nextval('s') scaled almost
linearly (I tested up to 900 clients) at about 70% of 'select 0'. (28k
tps on 4 core dell server vs 40k peak).  pgbench does have it's own
scaling problems though.  Since I happen to be working on a project
that relies heavily on high traffic sequences, do you have any
specific insights on known scaling problems with sequences?

> It also offends my sense of good database design, but that's another
> issue entirely.

I basically agree.

> More importantly, I think the issues raised here cause developers not to
> use advisory locks and instead use solutions more subject to race
> conditions, like a locking table.  Advisory locks could be a really cool
> feature for developers if it was just a bit more usable.

'as is', advisory locks is a fantastic feature that can be used for
signaling, mutexing, etc that are relatively difficult things to do in
the transactional world of sql.  My main gripe is that the 'shared id'
method for doing record pessimistic locks is basically a nuclear
missile pointed at your shared buffers if you don't have lot of
discipline in the queries that lock IDs.  Maybe this argues for more
of a 'sql exposed' pessimistic lock feature that operates on similar
level as 'for update'...I'm not sure...curious what thoughts you have
about improving them.

merlin

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

Re: [HACKERS] xpath_table equivalent

[Scott Bailey]

Chris Graner wrote:

Hello,

I've been reading over the documentation to find an alternative to the 
deprecated xpath_table functionality. I think it may be a possibility 
but I'm not seeing a clear alternative.


Thanks,

Chris Graner


The standard is XMLTABLE and is implemented by both db2 and oracle but 
is on our list of unimplemented features. I would love to see this 
implemented in Postgres. I recall it coming up here before. But I don't 
think it went beyond discussing which xquery library we could use.


Scott Bailey

--
Sent 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 config files in a directory

[Dimitri Fontaine]

--  
dim


Le 27 oct. 2009 à 18:21, Tom Lane  a écrit :


Greg Smith  writes:

... One file per GUC is certainly never going to fly though, it's
been hard enough getting people to accept going from one file to  
more than

one.


One thing that concerns me a bit about the lack of consensus on that
is what will happen if different config-adjustment tools adopt  
different
philosophies.  If Dimitri writes a tool that drops settings into per- 
GUC

files, and you write one that puts them all in persistent.conf, and
somebody tries to use both those tools, no good will come of it.


Right, that's why Greg Stark convinced me that we're defining an API  
here, not   just a facility.




If we forgot about the config-dir idea and just had one file that was
meant to be hacked by automated tools, the problem would go away.
However I suspect that that proposal won't fly, so we ought to think
about providing some guidance to tools writers about what to do.
Is there any consensus on how multiple config files actually get used
over in the Apache/etc world?


What they have is different contexts where to apply the same settings.  
You basically write one file per context.


As you are saying that does not translate well to our case where we  
want one context and N tools.


I don't see that opening the possibility to edit the same GUC in more  
than exactly 2 places is giving us anything. First the tool location,  
then the local DBA hand maintained file. Which still could include  
other files, as Kevin does, for park managment purpose.


The DBA friendly option is the existing include directive. includedir  
buys nothing in my mind. We want a tool API and the first tool to  
expose it in the form of SET PERSISTENT. I think :)




--
Sent 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 config files in a directory

[Dimitri Fontaine]

Hi,

Phone quoting again...

--  
dim


Le 27 oct. 2009 à 18:06, Greg Smith  a écrit :


On Tue, 27 Oct 2009, Dimitri Fontaine wrote:


I parse the current status as always reading files in the
postgresql.conf.d directory located in the same place as the current
postgresql.conf file.


Way upthread I pointed out that what some packagers have really  
wanted for a while now is to put the local postgresql.conf changes  
into /etc rather than have them live where the database does.   
Allowing the directory to be customized makes that possible.  The  
idea is to improve flexiblity and options for DBAs and packagers as  
long as it's not difficult to implement the idea, and allowing for a  
relocatable config directory isn't that hard.


Well choising where to store postgresql.conf is already possible and  
what debian is doing. My proposal is to build on this: add .d and you  
find the directory.






Tom had a reserve about allowing the user the control the overloading
behavior, but it appears that what we're trying to provide is a way  
for
tools not to fight against DBA but help him/her. So Greg Stark's  
idea do

sounds better: .d/ files are read first in alphabetical order,
then postgresql.conf is read. If the DBA want to manually edit the
configuration and be sure his edit will have effect, he just edits
postgresql.conf. No wondering.


We're trying to make allowances and a smooth upgrade path for old- 
school users who don't want to use this approach.  At the same time,  
let's be clear:  people who do that are going to find themselves  
increasingly cut-off from recommended pracice moving forward.  I  
want to make it possible for them to continue operating as they have  
been, while making it obvious that approach is on its way out.


Historic file loaded last fullfills the need in my mind.



If you want a future where it's easier for tools to operate, the  
config directory goes last and overrides anything put in the primary  
postgresql.conf in the default config.  Having it inserted as an  
explicit includedir line lets the DBA move it to the front  
themselves if they want to.  One thing we cannot do is make the  
includedir line implicit.  It must be the case that someone who  
opens a new postgresql.conf file and browses it sees exactly what's  
being done, so they can disable it or move the order it happens in  
around.




include directive or hardwired documented rule: in either case you  
know what happens when. In one case you can choose, at the expense of  
having to discover local setup rather than knowing your docs.


What I have in mind is for SET PERSISTENT to warn users when settings  
source is postgresql.conf.



The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.


This is being left to the author of the code to decide.  There's  
reason to believe that *.conf is going to be hard enough to  
implement, and that's acceptable.  If it turns out that it's easier  
than expected to make a full regex syntax possible here, maybe this  
should get revisited on next review.


Yes. But full regexp makes it harder for tools than hardwired rules.




Then the pg_settings view could also embed the comments.


That whole bit you outlined is an interesting idea, but it doesn't  
impact this patch so I'd rather not see it drag discussion out  
further right now.


Ok if the goal is include dir.

If tools and modules are concerned, it Will be easier to SET  
persistent variable classes then create files like  
preprepare.at_init.conf e.g.


This problem should be seen as an API problem for only automated  
tools, I think, like Greg Stark said.





00-initdb.conf if you want some bikesheding to happen


That's a future patch anyway, we can bikeshed more after it's been  
submitted.  One file per GUC is certainly never going to fly though,  
it's been hard enough getting people to accept going from one file  
to more than one.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com  
Baltimore, MD


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

Re: [HACKERS] Extraneous newlines in logfile from vacuumdb

[Tom Lane]

Tim Landscheidt  writes:
> log file entries from running vacuumdb are "double-spaced",
> as vacuumdb ends the commands with ";\n" (cf. attached mini-
> patch). Is there a deeper meaning in that, or could it be
> trimmed?

There are a LOT of clients that tend to send queries with trailing
newlines; I'm pretty sure that both pg_dump and psql have instances of
that, for example.  If we were to do something about this, which I'm
entirely unconvinced we should, it'd be better to strip trailing
whitespace from the logged query inside the backend.

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] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Tom Lane]

I wrote:
> Robert Haas  writes:
>> On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane  wrote:
>>> Right, the case would be something like

>>> select * from
>>>   (select * from foo order by x limit n) ss
>>> for update of ss;

>> That's a pretty odd construction.

> Dunno why you think that.  That's exactly what one would write if one
> wanted certain operations to execute in a different order than they're
> defined to execute in within a single query level.  We have not
> previously been very clear about the order of operations for FOR UPDATE
> locking relative to other steps, but now we will be.

Actually ... it strikes me that there is another way we could approach
this.  Namely, leave the semantics as-is (FOR UPDATE runs last) and
document that you can do

select * from
  (select * from foo for update) ss
order by x limit n;

if you need FOR UPDATE to run before sorting.  Or perhaps better,
redefine the ordering as ORDER BY then FOR UPDATE then LIMIT.  Swapping
FOR UPDATE and LIMIT has no performance cost and eliminates the worse of
the two complaints in the documentation, without breaking any working
queries AFAICS.  If you have the case where you want to cope with
concurrent updates to the sort key, then you can write the more
complicated query, and it's gonna cost ya.  But that's not a typical
usage, as proven by the fact that it took years to realize there was
a problem there.  So we shouldn't optimize for that usage at the expense
of cases where the sort key isn't expected to change.

It could be argued that this approach doesn't satisfy the principle of
least astonishment as well as doing FOR UPDATE first, but on reflection
I'm not sure I buy that.  The traditional definition has been that we
only lock the rows that are actually returned, and putting FOR UPDATE
underneath the sort will break that expectation.  If it's only underneath
LIMIT we can still meet that expectation.

So I'm liking this more the more I think about it ... and it's also
significantly less work than the other way would be.

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 config files in a directory

[Greg Smith]

On Tue, 27 Oct 2009, Greg Stark wrote:


If they all had to edit the same file then they have to deal with
writing out values and also reading them back. Everyone would need a
config file parser and have to make deductions about what other tools
were trying to do and how to interact with them.


Exactly, that's the situation we're trying to escape from now in a 
nutshell.


To answer Tom's question about providing better guidelines for tool 
authors, I was hoping to provide the first such tool and submit a patch 
for refactoring initdb using the same approach before 8.5 is done.  I'd 
rather see that nailed down with a concrete proof of concept attached that 
implements a candidate approach by example rather than to just talk about 
it in general.  I don't think that needs to hold up work on this patch 
though, particularly given that I'm dependent on this one being committed 
for my plan to work.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Tom Lane]

Robert Haas  writes:
> On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane  wrote:
>> Right, the case would be something like
>> 
>>select * from
>>  (select * from foo order by x limit n) ss
>>for update of ss;

> That's a pretty odd construction.

Dunno why you think that.  That's exactly what one would write if one
wanted certain operations to execute in a different order than they're
defined to execute in within a single query level.  We have not
previously been very clear about the order of operations for FOR UPDATE
locking relative to other steps, but now we will be.

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] Delete cascade with three levels bug ?

[Tom Lane]

I wrote:
> The crash occurs because we queue a deferred trigger here ...
> when we are not inside any AfterTriggerBeginQuery/AfterTriggerEndQuery
> pair.  Normally _SPI_pquery() would take care of that detail, but it's
> been specifically told not to by the RI trigger code (notice the
> fire_triggers=0 arguments).  It is not immediately obvious that an
> RI trigger query could never cause AFTER triggers to be queued, so
> I think this is at least a latent bug, even if this particular symptom
> involves intentional misconfiguration of standard triggers.

After further study I've gone back to the let's-just-make-the-assert-an-elog
camp.  The reason we have an issue is exactly that the cascade-delete
trigger assumes that it will never be deferred, which is how come it
can get away with telling SPI to not deal with any invoked AFTER triggers
immediately.  There isn't any good reason to support deferring that
trigger, nor is there any way to get into the state short of clueless
superuser manipulation of the catalogs.

I did look at modifying AfterTriggerFireDeferred, but concluded that's
not a great solution on two grounds:

* there doesn't seem to be any way to do it without taking a noticeable
performance hit (in particular, an extra useless scan over the deferred
trigger queue to verify everything's been fired);

* the crash occurs all the way back to 8.0, and such a fix would be a
great deal more trouble to back-port.

So elog it is ...

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] xpath_table equivalent

[Chris Graner]

Hello,

I've been reading over the documentation to find an alternative to the
deprecated xpath_table functionality. I think it may be a possibility but
I'm not seeing a clear alternative.

Thanks,

Chris Graner

Re: [HACKERS] Parsing config files in a directory

[Tom Lane]

Greg Stark  writes:
> I still think a simple hard coded rule is more useful and than allowing
> sysadmins to specify any regexp or glob and then having modules or
> tools not know what's allowed or not.

Yeah.  Considering that the entire argument for this feature is to
simplify matters for automated config tools, I can't understand why
we'd be adding details that complicate life for them without buying
any significant benefit.

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 config files in a directory

[Greg Stark]

On Tue, Oct 27, 2009 at 11:06 AM, Robert Haas  wrote:
> IME, the use case for multiple Apache configuration files is that
> there are bits of configuration that support particular modules which
> packagers want installed only in conjunction with the corresponding
> modules - it has nothing to do with being able to automate config-file
> updates, or at least I am not aware that it does.

That sounds like automated config file updates to me. Individual
modules are being installed and uninstalled and automatically updating
the configuration to handle the modules.

It's also not just modules, it's things like virtual sites. So for
example in Debian if you install a package which includes a web
interface it installs a configuration file for that web interface
under the appropriate directory.

I don't see the problem Tom describes. Clearly there's a conflict and
the settings from one of the files will have higher priority than the
other, but at least the two sets of settings will be kept separate.
Neither module will have to deal with rereading its output to see if
it has been mysteriously changed by another program. A good tool might
still want to check the settings in the running database to see if its
file is the source of the current value to give the user feedback.

If they all had to edit the same file then they have to deal with
writing out values and also reading them back. Everyone would need a
config file parser and have to make deductions about what other tools
were trying to do and how to interact with them.

I didn't realize Apache supported wildcards, and looking at Debian's
run-parts it looks like it's more liberal than I realized (though
still more conservative than the *.conf people keep insisting on). I
still think a simple hard coded rule is more useful and than allowing
sysadmins to specify any regexp or glob and then having modules or
tools not know what's allowed or not.

-- 
greg

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

Re: [HACKERS] Delete cascade with three levels bug ?

[Tom Lane]

Marcelo Costa  writes:
> [ trying to defer RI_FKey_cascade_del trigger crashes the backend ]

I looked at this a bit more and think that there's more to it than pilot
error.  The crash occurs because we queue a deferred trigger here:

#0  AfterTriggerSaveEvent (relinfo=0x40181778, event=1, row_trigger=1 '\001', 
oldtup=0x4018c1b8, newtup=0x0, recheckIndexes=0x0, modifiedCols=0x0) at 
trigger.c:3890
#1  0x2db9c0 in ExecARDeleteTriggers (estate=0x40181778, relinfo=0x40181778, 
tupleid=0x4018c1b8) at trigger.c:1896
#2  0x317124 in ExecDelete (tupleid=0x7b03c388, planSlot=0x4018b2c0, 
epqstate=0x4018b664, estate=0x401816a8) at nodeModifyTable.c:368
#3  0x317894 in ExecModifyTable (node=0x4018b618) at nodeModifyTable.c:738
#4  0x2fe01c in ExecProcNode (node=0x4018b618) at execProcnode.c:359
#5  0x2fc034 in ExecutePlan (estate=0x401816a8, planstate=0x4018b618, 
operation=CMD_DELETE, sendTuples=0 '\000', numberTuples=0, 
direction=ForwardScanDirection, dest=0x40003a80) at execMain.c:1188
#6  0x2fadf0 in standard_ExecutorRun (queryDesc=0x4017e2a0, 
direction=ForwardScanDirection, count=0) at execMain.c:278
#7  0x2fac94 in ExecutorRun (queryDesc=0x40181778, 
direction=ForwardScanDirection, count=1) at execMain.c:227
#8  0x3268c8 in _SPI_pquery (queryDesc=0x4017e2a0, fire_triggers=0 '\000', 
tcount=0) at spi.c:2009
#9  0x326520 in _SPI_execute_plan (plan=0x40180288, paramLI=0x4017e280, 
snapshot=0x0, crosscheck_snapshot=0x0, read_only=0, fire_triggers=0 '\000', 
tcount=0) at spi.c:1831
#10 0x32383c in SPI_execute_snapshot (plan=0x40180288, Values=0x7b03be88, 
Nulls=0x7b03bf88 " ", snapshot=0x0, crosscheck_snapshot=0x0, read_only=0, 
fire_triggers=0, tcount=0) at spi.c:441
#11 0x493d08 in ri_PerformCheck (qkey=0x7b03bb20, qplan=0x40180288, 
fk_rel=0x40133600, pk_rel=0x401262f0, old_tuple=0x7b03b5a8, new_tuple=0x0, 
detectNewRows=1, expect_OK=8, constrname=0x7b03b8cc "fk_son_father") at 
ri_triggers.c:3350
#12 0x4901d4 in RI_FKey_cascade_del (fcinfo=0x40181778) at ri_triggers.c:1166
#13 0x2db140 in ExecCallTriggerFunc (trigdata=0x7b03b588, tgindx=0, 
finfo=0x7b03b850, instr=0x0, per_tuple_context=0x40153b60) at trigger.c:1631
#14 0x2dccc8 in AfterTriggerExecute (event=0x40170678, rel=0x401262f0, 
trigdesc=0x1, finfo=0x4016a0d0, instr=0x0, per_tuple_context=0x40153b60) at 
trigger.c:2830
#15 0x2dd038 in afterTriggerInvokeEvents (events=0x40170a58, firing_id=1, 
estate=0x40169e40, delete_ok=1 '\001') at trigger.c:3009
#16 0x2dd5ac in AfterTriggerFireDeferred () at trigger.c:3247
#17 0x1fd230 in CommitTransaction () at xact.c:1581
#18 0x1fdfe8 in CommitTransactionCommand () at xact.c:2323
#19 0x3ed0e8 in finish_xact_command () at postgres.c:2372
#20 0x3eae20 in exec_simple_query (query_string=0x400625b8 "DELETE FROM father 
WHERE co_father = 1;") at postgres.c:1039
#21 0x3ef344 in PostgresMain (argc=2, argv=0x51, username=0x4005f9d8 
"postgres") at postgres.c:3573

when we are not inside any AfterTriggerBeginQuery/AfterTriggerEndQuery
pair.  Normally _SPI_pquery() would take care of that detail, but it's
been specifically told not to by the RI trigger code (notice the
fire_triggers=0 arguments).  It is not immediately obvious that an
RI trigger query could never cause AFTER triggers to be queued, so
I think this is at least a latent bug, even if this particular symptom
involves intentional misconfiguration of standard triggers.

The two reasonable fixes seem to be to change our minds about not
letting SPI fire deferred triggers in this usage, or to add code to
AfterTriggerFireDeferred to invoke AfterTriggerBegin/EndQuery.
I do not recall what the reasoning was for deferring triggers during
ri_triggers calls, and am afraid to monkey with that behavior unless
someone can lay out an argument why it's okay to change.  So modifying
AfterTriggerFireDeferred seems like the right 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] Parsing config files in a directory

[Joshua D. Drake]

On Tue, 2009-10-27 at 13:21 -0400, Tom Lane wrote:
> Greg Smith  writes:
> > ... One file per GUC is certainly never going to fly though, it's 
> > been hard enough getting people to accept going from one file to more than 
> > one.
> 
> One thing that concerns me a bit about the lack of consensus on that
> is what will happen if different config-adjustment tools adopt different
> philosophies.  If Dimitri writes a tool that drops settings into per-GUC
> files, and you write one that puts them all in persistent.conf, and
> somebody tries to use both those tools, no good will come of it.
> 
> If we forgot about the config-dir idea and just had one file that was
> meant to be hacked by automated tools, the problem would go away.
> However I suspect that that proposal won't fly, so we ought to think
> about providing some guidance to tools writers about what to do.
> Is there any consensus on how multiple config files actually get used
> over in the Apache/etc world?

Apache has an include functionality that supports wildcards etc... so I
can do:

include "conf/*.conf"

And it just parses them.

Joshua D. Drake


> 
>   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 config files in a directory

[Robert Haas]

On Tue, Oct 27, 2009 at 1:21 PM, Tom Lane  wrote:
> Greg Smith  writes:
>> ... One file per GUC is certainly never going to fly though, it's
>> been hard enough getting people to accept going from one file to more than
>> one.
>
> One thing that concerns me a bit about the lack of consensus on that
> is what will happen if different config-adjustment tools adopt different
> philosophies.  If Dimitri writes a tool that drops settings into per-GUC
> files, and you write one that puts them all in persistent.conf, and
> somebody tries to use both those tools, no good will come of it.
>
> If we forgot about the config-dir idea and just had one file that was
> meant to be hacked by automated tools, the problem would go away.
> However I suspect that that proposal won't fly, so we ought to think
> about providing some guidance to tools writers about what to do.
> Is there any consensus on how multiple config files actually get used
> over in the Apache/etc world?

IME, the use case for multiple Apache configuration files is that
there are bits of configuration that support particular modules which
packagers want installed only in conjunction with the corresponding
modules - it has nothing to do with being able to automate config-file
updates, or at least I am not aware that it does.

I think that might be somewhat less of an issue for us, but I'm not
sure. I think we have fewer settings that are absolutely required to
get the system up than Apache.  An unscientific survey of one server I
use shows 16 lines of uncommented configuration in postgresql.conf,
vs. 224 in httpd.conf and 95 more in httpd.conf.d/*.conf

...Robert

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

Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Robert Haas]

On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane  wrote:
>>> What I am thinking we should do is define that FOR UPDATE happens before
>>> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from
>>> an outer query level, it happens after the sub-select's ORDER BY or
>>> LIMIT.  The first provision fixes the bugs noted in our documentation,
>>> and the second one allows people to get back the old behavior if they
>>> need it for performance.  This also seems reasonably non-astonishing
>>> from a semantic viewpoint.
>
>> When you refer to an "outer query level", is that the same thing as a
>> sub-select?  If so, I think I agree that the behavior is
>> non-astonishing.
>
> Right, the case would be something like
>
>        select * from
>          (select * from foo order by x limit n) ss
>        for update of ss;
>
> If you try this in any existing release it will just fail, because the
> planner knows that it hasn't got a way to execute FOR UPDATE in a
> subquery.

That's a pretty odd construction.

In some sense I don't like the proposed behavior, because it's
imaginable that someone would use this syntax without realizing that
it could produce wrong answers.  My own gut instinct would be to
always push down the FOR UPDATE as being a clearer way to convey what
was meant - but we've already established that not everyone's gut
instincts agree with mine, and if someone does write this, they might
easily fail to understand the risk that it poses.

I'm not sure what to do about it, though.  Not giving people ANY way
to recover the old behavior is a little troubling.

...Robert

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

Re: [HACKERS] FOR UPDATE versus WITH --- change 8.4 too?

[Robert Haas]

On Tue, Oct 27, 2009 at 1:14 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> If it doesn't have any effect anyway, what's the virtue of back-patching it?
>
> Because 8.4 just fails in cases where we can easily allow it to work
> according to the new definition.  Right now, if you want to use FOR
> UPDATE in a query that has WITHs, you have to carefully write
> FOR UPDATE OF x,y,z,... for all the non-WITH relations.

Oh, I see.  Well, that seems like it makes sense to me, then.

...Robert

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

Re: [HACKERS] Delete cascade with three levels bug ?

[Tom Lane]

Robert Haas  writes:
> On Tue, Oct 27, 2009 at 10:41 AM, Euler Taveira de Oliveira
>> BTW, is it worth preventing such a crash putting an elog message in 
>> trigger.c?

> It doesn't seem right to allow a catalog change that results in an
> assertion failure.  Seems like we should either prevent the catalog
> change, or have an elog() there rather than Assert().

We can't prevent a superuser from making stupid catalog changes.  I
agree though that it's not very nice to Assert or dump core afterwards.
Changing the assert to an elog seems like an appropriate answer,
assuming that this isn't masking some more significant issue.

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] Extraneous newlines in logfile from vacuumdb

[Tim Landscheidt]

Hi,

log file entries from running vacuumdb are "double-spaced",
as vacuumdb ends the commands with ";\n" (cf. attached mini-
patch). Is there a deeper meaning in that, or could it be
trimmed?

TIA,
Tim
Index: vacuumdb.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v
retrieving revision 1.27
diff -u -r1.27 vacuumdb.c
--- vacuumdb.c	16 Oct 2009 10:38:25 -	1.27
+++ vacuumdb.c	24 Oct 2009 17:45:02 -
@@ -203,7 +203,7 @@
 		appendPQExpBuffer(&sql, " ANALYZE");
 	if (table)
 		appendPQExpBuffer(&sql, " %s", table);
-	appendPQExpBuffer(&sql, ";\n");
+	appendPQExpBuffer(&sql, ";");
 
 	conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
 	if (!executeMaintenanceCommand(conn, sql.data, echo))

-- 
Sent 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 config files in a directory

[Tom Lane]

Greg Smith  writes:
> ... One file per GUC is certainly never going to fly though, it's 
> been hard enough getting people to accept going from one file to more than 
> one.

One thing that concerns me a bit about the lack of consensus on that
is what will happen if different config-adjustment tools adopt different
philosophies.  If Dimitri writes a tool that drops settings into per-GUC
files, and you write one that puts them all in persistent.conf, and
somebody tries to use both those tools, no good will come of it.

If we forgot about the config-dir idea and just had one file that was
meant to be hacked by automated tools, the problem would go away.
However I suspect that that proposal won't fly, so we ought to think
about providing some guidance to tools writers about what to do.
Is there any consensus on how multiple config files actually get used
over in the Apache/etc world?

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] FOR UPDATE versus WITH --- change 8.4 too?

[Andres Freund]

On Tuesday 27 October 2009 18:02:53 Robert Haas wrote:
> On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane  wrote:
> > In yesterday's discussions about FOR UPDATE there was some mention of
> > making it not propagate into WITH subqueries:
> > http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
> > That is, given
> >  WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
> > should foo be locked FOR UPDATE or not?  The current behavior is that
> > the code attempts to propagate FOR UPDATE into the WITH, and fails
> > (the parser rejects it in some cases, and the planner in others ---
> > AFAICT there is no case where it actually works).  This is pretty
> > useless, and it's also at odds with the philosophy we adopted that WITH
> > queries execute independently of the primary query.  So I think there
> > was consensus to change it to have FOR UPDATE ignore WITH references.
> >
> > What I'm wondering at the moment is if there's any objection to
> > back-patching the change into 8.4.  Given the lack of any way to have a
> > working query depend on this behavior, it doesn't seem that there could
> > be a problem, but can anyone think of an objection I missed?
> 
> If it doesn't have any effect anyway, what's the virtue of back-patching
>  it?
> 
> It seems like we might want to throw an error rather than silently
> ignoring it, but that obviously wouldn't be back-patchable.
Because it makes it impossible to use SELECT FOR UPDATE with a CTE atm? Which 
very well can be considered a bug.

Andres

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

Re: [HACKERS] Parsing config files in a directory

[Josh Berkus]

Peter,

> Right, but you'll notice that Josh already got his way into how the
> current postgresql.conf is laid out and how the documentation is
> structured.  I can't find anything in the documentation anymore.  Just
> as a side note ... when we start giving people new ways to access the
> configuration settings, they might also like a documentation layout that
> matches their thinking.

Yeah, and I'd like to reorganize it again, since "Client connection
defaults" has become the trash-heap of GUCs.

How, exactly, would you "match their thinking?"  Whose thinking are you
matching exactly?

The categorization of the GUCs matched *my* thinking as of 8.0.  It's
kind of out of date now, but a *lot* of people found it helpful,
especially compared to "historical ordering" which was what we had
before.   I've continued to categorize GUCs by functional area in my
tutorials, and literally hundreds of people have found it helpful.

I agree that the Docs need an alpha index of settings as well as the
current categorical organization, but as previously discussed as long as
there are no editors I can use which like our project dialect of SGML,
constructing such an index is going to be up to you.

Or you could order them alphabetically and provide a categorical index
-- that might be better, actually, because it would make it easier to
re-categorize or multiple-categorize.  As long as people can look them
up both ways, it doesn't really matter.  You're the Doc master, go for it.

I'll continue to release *my* documentation on the GUCs in database
format, which is really the only thing which allows as much flexibility
as needed.

--Josh Berkus




-- 
Sent 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 config files in a directory

[Greg Smith]

On Tue, 27 Oct 2009, Kevin Grittner wrote:

I have 200 clusters.  I understand the proposal.  I see no benefit to 
me.


-Kevin, the troglodyte  ;-)


It looks like we'll have to settle this the only way your kind understands 
then:  a battle to the death using clubs.  See you at the next conference!


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] FOR UPDATE versus WITH --- change 8.4 too?

[Tom Lane]

Robert Haas  writes:
> If it doesn't have any effect anyway, what's the virtue of back-patching it?

Because 8.4 just fails in cases where we can easily allow it to work
according to the new definition.  Right now, if you want to use FOR
UPDATE in a query that has WITHs, you have to carefully write
FOR UPDATE OF x,y,z,... for all the non-WITH relations.

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 config files in a directory

[Greg Smith]

On Tue, 27 Oct 2009, Dimitri Fontaine wrote:


I parse the current status as always reading files in the
postgresql.conf.d directory located in the same place as the current
postgresql.conf file.


Way upthread I pointed out that what some packagers have really wanted for 
a while now is to put the local postgresql.conf changes into /etc rather 
than have them live where the database does.  Allowing the directory to be 
customized makes that possible.  The idea is to improve flexiblity and 
options for DBAs and packagers as long as it's not difficult to implement 
the idea, and allowing for a relocatable config directory isn't that hard.



Tom had a reserve about allowing the user the control the overloading
behavior, but it appears that what we're trying to provide is a way for
tools not to fight against DBA but help him/her. So Greg Stark's idea do
sounds better: .d/ files are read first in alphabetical order,
then postgresql.conf is read. If the DBA want to manually edit the
configuration and be sure his edit will have effect, he just edits
postgresql.conf. No wondering.


We're trying to make allowances and a smooth upgrade path for old-school 
users who don't want to use this approach.  At the same time, let's be 
clear:  people who do that are going to find themselves increasingly 
cut-off from recommended pracice moving forward.  I want to make it 
possible for them to continue operating as they have been, while making it 
obvious that approach is on its way out.


If you want a future where it's easier for tools to operate, the config 
directory goes last and overrides anything put in the primary 
postgresql.conf in the default config.  Having it inserted as an explicit 
includedir line lets the DBA move it to the front themselves if they want 
to.  One thing we cannot do is make the includedir line implicit.  It must 
be the case that someone who opens a new postgresql.conf file and browses 
it sees exactly what's being done, so they can disable it or move the 
order it happens in around.



The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.


This is being left to the author of the code to decide.  There's reason to 
believe that *.conf is going to be hard enough to implement, and that's 
acceptable.  If it turns out that it's easier than expected to make a full 
regex syntax possible here, maybe this should get revisited on next 
review.



Then the pg_settings view could also embed the comments.


That whole bit you outlined is an interesting idea, but it doesn't impact 
this patch so I'd rather not see it drag discussion out further right now.



00-initdb.conf if you want some bikesheding to happen


That's a future patch anyway, we can bikeshed more after it's been 
submitted.  One file per GUC is certainly never going to fly though, it's 
been hard enough getting people to accept going from one file to more than 
one.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Tom Lane]

Robert Haas  writes:
> On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane  wrote:
>> What I am thinking we should do is define that FOR UPDATE happens before
>> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from
>> an outer query level, it happens after the sub-select's ORDER BY or
>> LIMIT.  The first provision fixes the bugs noted in our documentation,
>> and the second one allows people to get back the old behavior if they
>> need it for performance.  This also seems reasonably non-astonishing
>> from a semantic viewpoint.

> When you refer to an "outer query level", is that the same thing as a
> sub-select?  If so, I think I agree that the behavior is
> non-astonishing.

Right, the case would be something like

select * from
  (select * from foo order by x limit n) ss
for update of ss;

If you try this in any existing release it will just fail, because the
planner knows that it hasn't got a way to execute FOR UPDATE in a
subquery.

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 config files in a directory

[Kevin Grittner]

Greg Smith  wrote:
> On Mon, 26 Oct 2009, Kevin Grittner wrote:
> 
>> for our 72 production servers for county Circuit Court systems, we
>> copy an identical postgresql.conf file to each county, with the
>> last line being an include to an overrides conf file in /etc/.
>> For most counties that file is empty.
 
> That's exactly a use case the "parsing config files in a directory" 
> feature aims to make easier to manage.  You can just mix and match
> files 
 
Mixing and matching files in a subdirectory would not make the control
and auditing of a small number of cluster-specific overrides to a
single standard configuration file easier.
 
I wasn't arguing against adding the feature, since it appears to be
useful for some environments; I was responding to Robert's musing
about it helping his single-cluster environment, but not knowing about
the impact on those with larger numbers of clusters.  I have 200
clusters.  I understand the proposal.  I see no benefit to me.
 
-Kevin, the troglodyte  ;-)

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

Re: [HACKERS] FOR UPDATE versus WITH --- change 8.4 too?

[Robert Haas]

On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane  wrote:
> In yesterday's discussions about FOR UPDATE there was some mention of
> making it not propagate into WITH subqueries:
> http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
> That is, given
>  WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
> should foo be locked FOR UPDATE or not?  The current behavior is that
> the code attempts to propagate FOR UPDATE into the WITH, and fails
> (the parser rejects it in some cases, and the planner in others ---
> AFAICT there is no case where it actually works).  This is pretty
> useless, and it's also at odds with the philosophy we adopted that WITH
> queries execute independently of the primary query.  So I think there
> was consensus to change it to have FOR UPDATE ignore WITH references.
>
> What I'm wondering at the moment is if there's any objection to
> back-patching the change into 8.4.  Given the lack of any way to have a
> working query depend on this behavior, it doesn't seem that there could
> be a problem, but can anyone think of an objection I missed?

If it doesn't have any effect anyway, what's the virtue of back-patching it?

It seems like we might want to throw an error rather than silently
ignoring it, but that obviously wouldn't be back-patchable.

...Robert

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

Re: [HACKERS] Should we warn users about SETs which have no effect?

[Josh Berkus]

On 10/27/09 9:36 AM, Josh Berkus wrote:
> Hackers,
> 
> There are some GUCs you can set in a context which will accept them, yet
> they have no effect when you do.  For example, I can call SET
> statement_timeout inside a function, and it has no effect whatsoever.
> 
> I'm wondering if we should be throwing a warning in these cases.  And
> how many such cases there are.  Thoughts?

H, clarification: this applies to SET LOCAL, specifically.

---Josh

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

Re: [HACKERS] Delete cascade with three levels bug ?

[Robert Haas]

On Tue, Oct 27, 2009 at 10:41 AM, Euler Taveira de Oliveira
 wrote:
> Marcelo Costa escreveu:
>> Hi, hackers
>>
>> I have a problem at PostgreSQL 8.3.5 (Slackware Server and Win 2003
>> Server) SO independent.
>>
>> When run the scripts below I receive the error:
>>
> This is not a bug. There are many ways to shoot yourself in the foot; and it
> is one of them...
>
>> UPDATE pg_trigger SET tgdeferrable = TRUE, tginitdeferred = TRUE;
>>
> This query will defer all of the triggers, including the cascade ones. If you
> compiles with --enable-cassert you will see:
>
> TRAP: FailedAssertion("!(afterTriggers->query_depth >= 0)", Arquivo:
> "/a/pgsql/src/backend/commands/trigger.c", Linha: 3893)
>
> Why don't you use the syntax (DEFERRABLE and INITIALLY DEFERRED)? That will do
> the Right Thing (TM).
>
> BTW, is it worth preventing such a crash putting an elog message in trigger.c?

It doesn't seem right to allow a catalog change that results in an
assertion failure.  Seems like we should either prevent the catalog
change, or have an elog() there rather than Assert().

...Robert

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

Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Robert Haas]

On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane  wrote:
> I wrote:
>>> Robert Haas  writes:
 Could the desired behavior be obtained using a CTE?
>
>>> Nope, we push FOR UPDATE into WITHs too.  I don't really see any way to
>>> deal with this without some sort of semantic changes.
>
>> ... although on reflection, I'm not sure *why* we push FOR UPDATE into
>> WITHs.  That seems a bit antithetical to the position we've evolved that
>> WITH queries are executed independently of the outer query.
>
>> If we removed that bit of behavior, which hopefully is too new for much
>> code to depend on, then the old FOR-UPDATE-last behavior could be
>> attained via a WITH.  And we'd not have to risk touching the interaction
>> between plain subqueries and FOR UPDATE, which is something that seems
>> much more likely to break existing apps.
>
> On further investigation, this still seems like a good change to make,
> but it doesn't solve the problem at hand.  If we make FOR UPDATE not
> propagate into WITH then the effect of
>
> with w as (select ... order by x limit n) select * from w for update
>
> is not going to be to lock just the rows pulled from the WITH; it's
> going to be to not lock any rows at all.  So we're still up against a
> performance problem if we make these otherwise-desirable changes in plan
> node order.
>
> I realized just now that the backwards-compatibility problem is not
> nearly as bad as I thought it was, because a lot of the syntaxes
> we might want to change the behavior of will fail outright in 8.4
> and before.  We had this little bit in preptlist.c:
>
>        /*
>         * Currently the executor only supports FOR UPDATE/SHARE at top level
>         */
>        if (root->query_level > 1)
>            ereport(ERROR,
>                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>            errmsg("SELECT FOR UPDATE/SHARE is not allowed in subqueries")));
>
> and that is the error you will get if you have FOR UPDATE in or applying
> to a sub-select that does not get flattened into the calling query.
> So in particular, a sub-select using ORDER BY or LIMIT has never been
> compatible with FOR UPDATE at all, and that means we can define the
> behavior of that combination freely.
>
> What I am thinking we should do is define that FOR UPDATE happens before
> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from
> an outer query level, it happens after the sub-select's ORDER BY or
> LIMIT.  The first provision fixes the bugs noted in our documentation,
> and the second one allows people to get back the old behavior if they
> need it for performance.  This also seems reasonably non-astonishing
> from a semantic viewpoint.
>
> Actually implementing this will be more than a one-line change, but it
> doesn't seem too terribly difficult --- we'll just need to modify the
> query representation of FOR UPDATE enough that we can remember which
> case we had.

When you refer to an "outer query level", is that the same thing as a
sub-select?  If so, I think I agree that the behavior is
non-astonishing.

...Robert

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

Re: [HACKERS] Proposal: String key space for advisory locks

[Josh Berkus]

Merlin,

> Why even bother with a hash function when you can just have multiple
> table pull from a shared sequence?  AFAICT, this solves the OP's
> problem with no downsides (I used the approach with excellent results
> in a ported cobol app which had pessimistic locking requirement).

Well, if you have enough tables, the sequence itself becomes a
bottleneck (yes, I've had this happen in an app where all tables shared
one sequence).  There's also the fact that such a solution is extremely
hard to retrofit onto an existing application.

It also offends my sense of good database design, but that's another
issue entirely.

More importantly, I think the issues raised here cause developers not to
use advisory locks and instead use solutions more subject to race
conditions, like a locking table.  Advisory locks could be a really cool
feature for developers if it was just a bit more usable.

But, as others have pointed out, increasing the size of the lock
namespace would cause huge issues elsewhere.

--Josh Berkus

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

[HACKERS] Should we warn users about SETs which have no effect?

[Josh Berkus]

Hackers,

There are some GUCs you can set in a context which will accept them, yet
they have no effect when you do.  For example, I can call SET
statement_timeout inside a function, and it has no effect whatsoever.

I'm wondering if we should be throwing a warning in these cases.  And
how many such cases there are.  Thoughts?

--Josh Berkus

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

Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue

[Jeff Davis]

On Mon, 2009-10-26 at 17:23 +, Dean Rasheed wrote:
> If it's of any relevance, I'm currently using an optimised build, with
> assert checking off.
> [Linux x86_64, 2 core Intel Core2]

Ok, I'm able to reproduce it now. Thanks for looking into it!

Regards,
Jeff Davis


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

Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order

[Tom Lane]

I wrote:
>> Robert Haas  writes:
>>> Could the desired behavior be obtained using a CTE?

>> Nope, we push FOR UPDATE into WITHs too.  I don't really see any way to
>> deal with this without some sort of semantic changes.

> ... although on reflection, I'm not sure *why* we push FOR UPDATE into
> WITHs.  That seems a bit antithetical to the position we've evolved that
> WITH queries are executed independently of the outer query.

> If we removed that bit of behavior, which hopefully is too new for much
> code to depend on, then the old FOR-UPDATE-last behavior could be
> attained via a WITH.  And we'd not have to risk touching the interaction
> between plain subqueries and FOR UPDATE, which is something that seems
> much more likely to break existing apps.

On further investigation, this still seems like a good change to make,
but it doesn't solve the problem at hand.  If we make FOR UPDATE not
propagate into WITH then the effect of

with w as (select ... order by x limit n) select * from w for update

is not going to be to lock just the rows pulled from the WITH; it's
going to be to not lock any rows at all.  So we're still up against a
performance problem if we make these otherwise-desirable changes in plan
node order.

I realized just now that the backwards-compatibility problem is not
nearly as bad as I thought it was, because a lot of the syntaxes
we might want to change the behavior of will fail outright in 8.4
and before.  We had this little bit in preptlist.c:

/*
 * Currently the executor only supports FOR UPDATE/SHARE at top level
 */
if (root->query_level > 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("SELECT FOR UPDATE/SHARE is not allowed in subqueries")));

and that is the error you will get if you have FOR UPDATE in or applying
to a sub-select that does not get flattened into the calling query.
So in particular, a sub-select using ORDER BY or LIMIT has never been
compatible with FOR UPDATE at all, and that means we can define the
behavior of that combination freely.

What I am thinking we should do is define that FOR UPDATE happens before
ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from
an outer query level, it happens after the sub-select's ORDER BY or
LIMIT.  The first provision fixes the bugs noted in our documentation,
and the second one allows people to get back the old behavior if they
need it for performance.  This also seems reasonably non-astonishing
from a semantic viewpoint.

Actually implementing this will be more than a one-line change, but it
doesn't seem too terribly difficult --- we'll just need to modify the
query representation of FOR UPDATE enough that we can remember which
case we had.

Comments?

regards, tom lane

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

Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost

[Euler Taveira de Oliveira]

Alvaro Herrera escreveu:
> Tom Lane escribió:
>> Greg Stark  writes:
>>> Still far from convinced on that one. But effective_io_concurrency
>>> should be included even in the first pass.
>> I think a design that is limited to a prespecified set of GUCs is
>> broken by definition.  It'd be better to make it work like
>> ALTER DATABASE SET.
> 
> Well, not exactly like ALTER DATABASE SET because those are now stored
> in pg_db_role_setting.  But a new spcoptions column storing an array of
> key/value pairs seems a reasonable way to do it.
> 
+1. That's what I have in mind too.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/

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

[HACKERS] FOR UPDATE versus WITH --- change 8.4 too?

[Tom Lane]

In yesterday's discussions about FOR UPDATE there was some mention of
making it not propagate into WITH subqueries:
http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
That is, given
  WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
should foo be locked FOR UPDATE or not?  The current behavior is that
the code attempts to propagate FOR UPDATE into the WITH, and fails
(the parser rejects it in some cases, and the planner in others ---
AFAICT there is no case where it actually works).  This is pretty
useless, and it's also at odds with the philosophy we adopted that WITH
queries execute independently of the primary query.  So I think there
was consensus to change it to have FOR UPDATE ignore WITH references.

What I'm wondering at the moment is if there's any objection to
back-patching the change into 8.4.  Given the lack of any way to have a
working query depend on this behavior, it doesn't seem that there could
be a problem, but can anyone think of an objection I missed?

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] Delete cascade with three levels bug ?

[Euler Taveira de Oliveira]

Marcelo Costa escreveu:
> Hi, hackers
> 
> I have a problem at PostgreSQL 8.3.5 (Slackware Server and Win 2003
> Server) SO independent.
> 
> When run the scripts below I receive the error:
> 
This is not a bug. There are many ways to shoot yourself in the foot; and it
is one of them...

> UPDATE pg_trigger SET tgdeferrable = TRUE, tginitdeferred = TRUE;
> 
This query will defer all of the triggers, including the cascade ones. If you
compiles with --enable-cassert you will see:

TRAP: FailedAssertion("!(afterTriggers->query_depth >= 0)", Arquivo:
"/a/pgsql/src/backend/commands/trigger.c", Linha: 3893)

Why don't you use the syntax (DEFERRABLE and INITIALLY DEFERRED)? That will do
the Right Thing (TM).

BTW, is it worth preventing such a crash putting an elog message in trigger.c?


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/

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

[HACKERS] Delete cascade with three levels bug ?

[Marcelo Costa]

Hi, hackers

I have a problem at PostgreSQL 8.3.5 (Slackware Server and Win 2003 Server)
SO independent.

When run the scripts below I receive the error:

---
testes=# DELETE FROM pai WHERE co_pai = 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
---

The script creates the father table, son and grandson. All conected with FK
Cascade.

I changed the comportment of the pg_trigger to postpone the validation of
constraint
(make compatible with 7.4 version because we migrate it).

When I delete the father register the next error ocourr.

PS1.: On Windows need reinicialize the service.
PS2.: On Linux all connection down but not is needed reinicialize the
service.
PS3.: I make a test on 8.4 version and the same error ocourr.


---Scripts---
CREATE TABLE father
(
 co_father double precision NOT NULL,
 no_description character varying(50) NOT NULL,
 CONSTRAINT pk_father PRIMARY KEY (co_father)
)
WITH (OIDS=TRUE);
ALTER TABLE father OWNER TO postgres;


CREATE TABLE son
(
 co_son double precision NOT NULL,
 co_father double precision NOT NULL,
 no_description character varying(50) NOT NULL,
 CONSTRAINT pk_son PRIMARY KEY (co_son)
)
WITH (OIDS=TRUE);
ALTER TABLE son OWNER TO postgres;

CREATE TABLE grandson
(
 co_grandson double precision NOT NULL,
 co_son double precision NOT NULL,
 no_description character varying(50) NOT NULL,
 CONSTRAINT pk_grandson PRIMARY KEY (co_grandson)
)
WITH (OIDS=TRUE);
ALTER TABLE son OWNER TO postgres;

ALTER TABLE son
 ADD CONSTRAINT fk_son_father FOREIGN KEY (co_father)
 REFERENCES father (co_father) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE grandson
 ADD CONSTRAINT fk_grandson_son FOREIGN KEY (co_son)
 REFERENCES son (co_son) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE;

UPDATE pg_constraint SET condeferred  = TRUE, condeferrable = TRUE;
UPDATE pg_trigger SET tgdeferrable = TRUE, tginitdeferred = TRUE;

INSERT INTO father VALUES(1, 'FATHER-1');

INSERT INTO son VALUES(1,1, 'Son FATHER-1');
INSERT INTO son VALUES(2,1, 'Son FATHER-1');
INSERT INTO son VALUES(3,1, 'Son FATHER-1');

INSERT INTO grandson VALUES(1,1, 'Grandson FATHER-1');
INSERT INTO grandson VALUES(2,2, 'Grandson FATHER-1');
INSERT INTO grandson VALUES(3,3, 'Grandson FATHER-1');

DELETE FROM father WHERE co_father = 1


My LOGS
---

0LOG:  0: server process (PID 23470) was terminated by signal 11:
Segmentation fault
0LOCATION:  LogChildExit, postmaster.c:2529
0LOG:  0: terminating any other active server processes
0LOCATION:  HandleChildCrash, postmaster.c:2374
0FATAL:  57P03: the database system is in recovery mode
0LOCATION:  ProcessStartupPacket, postmaster.c:1648
0LOG:  0: all server processes terminated; reinitializing
0LOCATION:  PostmasterStateMachine, postmaster.c:2690
0LOG:  0: database system was interrupted; last known up at 2009-10-27
11:43:37 BRST
0LOCATION:  StartupXLOG, xlog.c:4836
0DEBUG:  0: checkpoint record is at 1/1C865BD4
0LOCATION:  StartupXLOG, xlog.c:4906
0DEBUG:  0: redo record is at 1/1C865BD4; shutdown TRUE
0LOCATION:  StartupXLOG, xlog.c:4932
0DEBUG:  0: next transaction ID: 0/27113; next OID: 67190
0LOCATION:  StartupXLOG, xlog.c:4936
0DEBUG:  0: next MultiXactId: 1; next MultiXactOffset: 0
0LOCATION:  StartupXLOG, xlog.c:4939
0LOG:  0: database system was not properly shut down; automatic recovery
in progress
0LOCATION:  StartupXLOG, xlog.c:5003
0LOG:  0: record with zero length at 1/1C865C14
0LOCATION:  ReadRecord, xlog.c:3126
0LOG:  0: redo is not required
0LOCATION:  StartupXLOG, xlog.c:5146
0DEBUG:  0: transaction ID wrap limit is 2147484026, limited by database
"template1"
0LOCATION:  SetTransactionIdLimit, varsup.c:283
0LOG:  0: database system is ready to accept connections
0LOCATION:  reaper, postmaster.c:2156

-

Has I know, the deletion cascade with three levels don't give this problem.
The same think occourr if I change the pg_trigger comportment to postpone
validation. This is right?

Sorry, but what wrap limit do ?

Thanks in advanced.

Sincerely,
-- 
Marcelo Costa

-
“You can't always get what want”,

Doctor House in apology to Mike Jagger

Re: [HACKERS] "toast.fillfactor" is documented but not recognized?

[Alvaro Herrera]

Itagaki Takahiro wrote:
> 
> Jeff Davis  wrote:
> 
> > http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
> > Looking at that page briefly I would assume that it could be set.
> 
> Oops, I forgot to fix the description when I disable toast.fillfactor.
> Please just remove it from documentation.

My mistake actually.  Fixed.

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

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

Re: [HACKERS] Proposal: String key space for advisory locks

[Merlin Moncure]

On Mon, Oct 26, 2009 at 4:30 PM, Josh Berkus  wrote:
>
>> Why aren't you satisfied with hashtext('foo') ?
>
> Collisions, mostly.

Why even bother with a hash function when you can just have multiple
table pull from a shared sequence?  AFAICT, this solves the OP's
problem with no downsides (I used the approach with excellent results
in a ported cobol app which had pessimistic locking requirement).

merlin

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

Re: [HACKERS] Proposal: String key space for advisory locks

[Kenneth Marshall]

On Mon, Oct 26, 2009 at 06:35:13PM -0700, Christophe Pettus wrote:
>
> On Oct 26, 2009, at 5:24 PM, Itagaki Takahiro wrote:
>
>> Hmmm, hashtext() returns int32. ,
>> Can you reduce the collision issue if we had hashtext64()?
>
> That would certainly reduce the chance of a collison considerably, assuming 
> the right algorithm.
>
> --
> -- Christophe Pettus
>x...@thebuild.com
>
The current hash function can already support generating a 64-bit
hash value by using both the b and c values. The function is called
hashlittle2 and has this comment in the original Bob Jenkins 2006
code:

/*
 * hashlittle2: return 2 32-bit hash values
 *
 * This is identical to hashlittle(), except it returns two 32-bit hash
 * values instead of just one.  This is good enough for hash table
 * lookup with 2^^64 buckets, or if you want a second hash if you're not
 * happy with the first, or if you want a probably-unique 64-bit ID for
 * the key.  *pc is better mixed than *pb, so use *pc first.  If you want
 * a 64-bit value do something like "*pc + (((uint64_t)*pb)<<32)".
 */

This should be a simple change. It would be worth running it by
the developer community to figure out how to add this functionality
in a way that will make 64-bit hashes available easily to other
code in the DB, perhaps even using them in very large hash indexes.

Regards,
Ken

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

Re: [HACKERS] half OOT, plv8js group created ^^

[Sam Mason]

On Tue, Oct 27, 2009 at 08:30:16AM -0400, Andrew Dunstan wrote:
> If someone is going to work on a JS engine for PostgreSQL (which I think 
> is a good idea, actually) I want them to work on one that is likely to 
> succeed.

The project (at the moment) just seems to be a set of pointers to code
I threw together a couple of years ago to experiment with extending
Postgres.  It's based on an oldish release of Spidermonkey and it'll
be interesting to see where things go with this.  It would be nice to
see it picked up by someone as priorities changed and I lost personal
interest in it.

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

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

Re: [HACKERS] half OOT, plv8js group created ^^

[Andrew Dunstan]

Merlin Moncure wrote:

On Mon, Oct 26, 2009 at 11:18 PM, Andrew Dunstan  wrote:
  

Kiswono Prayogo wrote:


plv8js is a procedural language add-on for PostgreSQL, which means you
can define Javascript functions that run inside a PostgreSQL server
using google V8 Engine.

anyone who want to contribute in plv8js please visit this group ^^
http://code.google.com/p/plv8js/
i'm still new in postgresql and v8, need MASSIVELY LOT of study and
help ^^, thank you..
  

Well, I see you are apparently ignoring the advice we already gave you that
V8 is not likely to be the best fit for a PostgreSQL JS procedural language,
so that's not a good start.



that's a little harsh...he was mostly given some vague advice
("spidermonkey might be better...").  Maybe it isn't the best, but
that doesn't mean it's not worth trying!


  



Very well, if I was harsh I apologise, but maybe you should look at the 
embedding guide here  and 
decide how well it is likely to work for PostgreSQL in pure C. The 
advice was not just "spidermonkey might be better". It was that a C++ 
oriented API like this (throwing exceptions among other things) was not 
likely to work, or at least to work as well as a pure C API.


And, frankly, pure PL speed in a language like JS, that is likely to be 
used mainly as glue, is not likely to be a concern for most uses.


If someone is going to work on a JS engine for PostgreSQL (which I think 
is a good idea, actually) I want them to work on one that is likely to 
succeed.


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] half OOT, plv8js group created ^^

[Merlin Moncure]

On Mon, Oct 26, 2009 at 11:18 PM, Andrew Dunstan  wrote:
> Kiswono Prayogo wrote:
>>
>> plv8js is a procedural language add-on for PostgreSQL, which means you
>> can define Javascript functions that run inside a PostgreSQL server
>> using google V8 Engine.
>>
>> anyone who want to contribute in plv8js please visit this group ^^
>> http://code.google.com/p/plv8js/
>> i'm still new in postgresql and v8, need MASSIVELY LOT of study and
>> help ^^, thank you..
>
> Well, I see you are apparently ignoring the advice we already gave you that
> V8 is not likely to be the best fit for a PostgreSQL JS procedural language,
> so that's not a good start.

that's a little harsh...he was mostly given some vague advice
("spidermonkey might be better...").  Maybe it isn't the best, but
that doesn't mean it's not worth trying!

merlin

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

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

[Roger Leigh]

On Mon, Oct 26, 2009 at 11:33:40PM +, Roger Leigh wrote:
> On Mon, Oct 26, 2009 at 07:19:24PM -0400, Tom Lane wrote:
> > Roger Leigh  writes:
> > > On Mon, Oct 26, 2009 at 01:33:19PM -0400, Tom Lane wrote:
> > >> Yeah.  We can do what we like with the UTF8 format but I'm considerably
> > >> more worried about the aspect of making random changes to the
> > >> plain-ASCII output.
> > 
> > > I checked (using strace)
> > > gnumeric (via libgda and gnome-database-properties)
> > > openoffice (oobase)
> > 
> > Even if that were the entire universe of programs we cared about,
> > whether their internal ODBC logic goes through psql isn't really
> > the point here.  What I'm worried about is somebody piping the
> > text output of psql into another program.
> > 
> > > On a related note, there's something odd with the pager code.
> > 
> > Hm, what platform are you testing that on?
> 
> Debian GNU/Linux (unstable)
> linux2.6.30
> eglibc   2.10.1
> libreadline6 6.0.5
> libncurses5  5.7
> gcc  4.3.4
> 
> This is the trace of the broken write:
> 
> 16206 write(1, "  Name   \342\224\202  Owner   \342\224"..., 102) = 
> 102
> 16206 write(1, 
> "\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342
> \224"..., 256) = 256
> 16206 write(1, 
> "\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\n", 18) 
> = 18

Further tracing showed this to be a bug in the "util-linux" version of
"more" which had a static 256 byte line buffer.  The above was a red
herring--it's writing to a pipe.  I've sent a patch to fix this by
increasing the buffer size.


-- 
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?   http://gutenprint.sourceforge.net/
   `-GPG Public Key: 0x25BFB848   Please GPG sign your mail.


signature.asc
Description: Digital signature

Re: [HACKERS] License clarification: BSD vs MIT

[Dave Page]

On Mon, Oct 26, 2009 at 7:02 PM, Chris Browne  wrote:
> dp...@pgadmin.org (Dave Page) writes:
>> As Tom says though, the effect this has on users is zero. The licence
>> is still the same as its always been, regardless of what we say it is
>> based on or looks like.
>
> There may be a fairly miniscule one...
>
> There do exist "GPL zealots" that bash, as "not free" (in the sense
> that people are doubtless well aware of), stuff licensed under a "BSD
> license."

Having run into such people before, I was going to make some
obsersations on that comment, but on second though I think such
endangerment of one's own life is reckless and unnecessary :-p

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PGDay.EU 2009 Conference: http://2009.pgday.eu/start

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

Re: [HACKERS] License clarification: BSD vs MIT

[Chris Browne]

dp...@pgadmin.org (Dave Page) writes:
> As Tom says though, the effect this has on users is zero. The licence
> is still the same as its always been, regardless of what we say it is
> based on or looks like.

There may be a fairly miniscule one...

There do exist "GPL zealots" that bash, as "not free" (in the sense
that people are doubtless well aware of), stuff licensed under a "BSD
license."

There may be some non-zero advantage to saying "MIT style," in that
this changes coasts ;-) and takes a micro-step away from the political
aspects of "BSD vs GPL."

But I'm not disagreeing with you, by any means!  :-)
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/emacs.html
"I really only meant to point out how nice InterOp was for someone who
doesn't  have the weight of the  Pentagon behind him.   I really don't
imagine that the Air Force will ever be  able to operate like a small,
competitive enterprise like GM or IBM." -- Kent England

-- 
Sent 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 config files in a directory

[Dimitri Fontaine]

Hi,

Greg Smith  writes:
> It sounds like there's a consensus brewing here on what should get done with
> this particular patch now.  Let me try to summarize:
>
> -The new feature should be activated by allowing you to specify a directory
> to include in the postgresql.conf like this:
>
>   includedir 'conf'

I parse the current status as always reading files in the
postgresql.conf.d directory located in the same place as the current
postgresql.conf file.

Tom had a reserve about allowing the user the control the overloading
behavior, but it appears that what we're trying to provide is a way for
tools not to fight against DBA but help him/her. So Greg Stark's idea do
sounds better: .d/ files are read first in alphabetical order,
then postgresql.conf is read. If the DBA want to manually edit the
configuration and be sure his edit will have effect, he just edits
postgresql.conf. No wondering.

> -Within that directory, only file names of the form "*.conf" will be
> processed.  More flexibility is hard to implement and of questionable
> value.

The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.

> -The order they are processed in will be alphabetical.  This allows (but
> doesn't explictly require) using the common convention of names like
> "99name" to get a really obvious ordering.

Yes.

> -The default postgresql.conf should be updated to end with the sample
> includedir statement shown above.  This will make anything that goes into
> there be processed after the main file, and therefore override anything in
> it.

No, hardwired knowledge makes life easier without trading
capabilities. Loading is deterministic:
 1. files in postgresql.conf.d in alphabetical order
 2. postgresql.conf

This way the directory is for tools or common setup and the file for
local editing by the admin.

> -An intended purpose here is making tools easier to construct.  It's
> impractical to expect every tool that touches files in the config directory
> to do an exhaustive sweep to find every other place there might be a
> conflict and comment them all out.  The fact that pg_settings shows users
> the exact file and line they setting that is the active one is a good enough
> tool to allow DBAs to work through most of the problem cases.

If we want to insist on having both user comments and settings in the
files in postgresql.conf.d, I still think the best is to have there GUC
named file. First line contains current value. Rest of the file is
comments. Now you can even have SET PERSISTENT ... WITH COMMENT ... and
COMMENT ON GUC ...;

Then the pg_settings view could also embed the comments.

> And as far as how it impacts planning:
>
> -A future patch to initdb could move the changes it makes from the primary
> file to one in the config directory.  It might make sense to use a name like
> 00initdb.conf to encourage a known good naming practice for files in the
> config directory; that doesn't need to get nailed down now though.

00-initdb.conf if you want some bikesheding to happen :)

> -This patch makes it easier to envision implementing a smaller default
> postgresql.conf, but it doesn't require such a change to be useful.

The postgresql.conf file could remain the same or not, and still is
intended for manual editing only.

> -SET PERSISTENT is still a bit away.  This patch assists in providing a
> cleaner preferred way to implement that, and certainly doesn't make it
> harder to build.

If we stick to « dba is not supposed to manually edit any file in the
directory or things will get broken », then have either a
99-persistent.conf file or the one-file-per-GUC approach. The former
sounds easy to implement if we drop comments out of tool scope, the
latter is more flexible but looks ugly to most... (but you're forbidden
to have a look there).

>  The issue of how to handle backing out changes that result
> in a non-functional server configuration is still there.  And there's some
> support for the idea that the SQL interface should do more sanity checks to
> make sure its setting changes aren't being overridden by config files parsed
> later than we might expect from external tuning tools.

In the one-file-per-GUC / do-not-edit-any-postgresql.conf.d-file idea,
it's possible to have a convention for tools to manage history of settings.

Regards,
-- 
dim

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

Re: [HACKERS] Parsing config files in a directory

[Simon Riggs]

On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
> It sounds like there's a consensus brewing here on what should get done 
> with this particular patch now.  Let me try to summarize:

+1

-- 
 Simon Riggs   www.2ndQuadrant.com


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

Re: [HACKERS] [ADMIN] pg_standby doesnt't work

[Andreas Schmidt]

Hi, thank you for your response.
But does this mean, that only WinXP is no supported or doesn't it work at all? 
I would need it on a Windows2003Server???

Best regards,

murphy


-Ursprüngliche Nachricht-
Von: Fujii Masao [mailto:masao.fu...@gmail.com] 
Gesendet: Montag, 26. Oktober 2009 03:36
An: Andreas Schmidt
Cc: pgsql-ad...@postgresql.org; PostgreSQL-development
Betreff: Re: [ADMIN] pg_standby doesnt't work

Hi,

On Mon, Oct 26, 2009 at 12:34 AM, Andreas Schmidt  wrote:
> I'm testing serveral days a replication-system with PostgreSQL, but I get
> allways the same error.
>
> 2009-10-25 15:44:45 CET FATAL:  XX000: could not restore file
> "0001.history" from archive: return code -1073741811
>
> The restore_command is:
>
> restore_command = 'pg_standby.exe -d -s 5 -t C:\pgsql.trigger.5442
> w:\wal-archive %f %p %r 2>>pg_log\pg_standby.log'
>
> The system is runnig on WinXP (during this test), the database-version is
> 8.3.8. I've tried out the pg_standby-version from PG8.4, but there is no
> difference in the result.
>
> So I can't understand what this return-code means, (and) I've never seen a
> file named 0001.history. It definitely doesn't exist on my master and so
> my archive_command can't replicate to the slave.

This is the same problem that was reported before.
http://archives.postgresql.org/pgsql-bugs/2009-08/msg3.php

The cause is the signals support for pg_standby on win32.
We should get rid of that.

Regards,

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

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

Re: [HACKERS] Parsing config files in a directory

[Peter Eisentraut]

On Mon, 2009-10-26 at 14:13 -0700, Greg Stark wrote:
> On Mon, Oct 26, 2009 at 1:40 PM, Josh Berkus  wrote:
> >
> > Different issue, really, which is that some people (including me) would
> > like to break up PostgreSQL configuration into 7 or 8 files based on
> > functional area (e.g. memory.conf, logging.conf, custom_options.conf
> > ...).  I do this with my Apache configs, and find it vastly more
> > manageable than one big file, especially under SCM.If I write a
> > config management tool, my tool will also do this.
> 
> This actually seems like a bad idea to me. It's fine for something
> like apache virtual hosts where there's no ambiguity and in any case
> it's you organizing it and you reading it back out. But for a tool to
> do this is only going to lead to confusion when my thinking of where
> to find the variables differs from yours.

Right, but you'll notice that Josh already got his way into how the
current postgresql.conf is laid out and how the documentation is
structured.  I can't find anything in the documentation anymore.  Just
as a side note ... when we start giving people new ways to access the
configuration settings, they might also like a documentation layout that
matches their thinking.


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