Re: [HACKERS] Documenting serializable vs snapshot isolation levels

[Simon Riggs]

On Fri, 2009-01-02 at 13:47 -0500, Tom Lane wrote:
> "Kevin Grittner"  writes:
> >> What do you mean by referential integrity?  I don't believe you can
> >> construct a foreign key problem at any transaction isolation level.
>  
> > I mean that if someone attempts to maintain referential integrity with
> > SQL code, without using explicit locks, it is not reliable. 
> > Presumably the implementation of foreign keys in PostgreSQL takes this
> > into account and blocks the kind of behavior shown below.  This
> > behavior would not occur with true serializable transactions.
> 
> IIRC the RI code has to fudge the normal serializable-snapshot behavior
> in order to guarantee no constraint violation --- it has to be aware of
> concurrent changes that would otherwise be invisible to a serializable
> transaction.

...just to add that this is exactly as required by SQL Standard, i.e. RI
works in Read Committed mode even within a Serializable transaction.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Latest version of Hot Standby patch

[Simon Riggs]

On Fri, 2009-01-02 at 17:35 +, Simon Riggs wrote:

> > I use latest CVS version. I tried to apply the patches and I have the
> > following error :
> 
> Thanks, will fix.

Fixed various bit rots and re-packaged. v6a now up, v6 unlinked.

Thanks,

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Significantly larger toast tables on 8.4?

[Simon Riggs]

On Fri, 2009-01-02 at 19:27 -0700, Alex Hunsaker wrote:
> On Fri, Jan 2, 2009 at 18:30, Tom Lane  wrote:
> > "Alex Hunsaker"  writes:
> >> On Fri, Jan 2, 2009 at 11:44, Tom Lane  wrote:
> >>> An easy way to prove or disprove the point would be to go into
> >>> src/backend/utils/adt/pg_lzcompress.c, and change the second entry
> >>> in strategy_default_data from "1024 * 1024" to "INT_MAX",
> >
> >> And the toast file size is *drum roll* 167M.
> >
> > Hmmm ... so that's a lot closer to the original 145M, but it still
> > seems like there's something else going on.  It looks like the other
> > thing we changed that might result in not compressing things was to
> > increase the third entry (minimum compression rate) from 20% to 25%.
> > Could you try it with that value also changed back?
> 
> With it back to 20% its now back to 145M.

Perspective on this is that the numbers don't sound too bad if we put a
big M behind them, but lets imagine that's a G or even a T. Those
numbers look pretty sad then. We must retain the option to compress and
even better, options to control the compression.

Please, please remember that the world genuinely does wish to store
multiple Terabytes of data and they won't do it with Postgres unless we
act sensibly on this point. Our open source software *enables* massive
deployment of database technology.

Oracle charge money for their Advanced Compression option, so if lets
avoid anything that will immediately justify that cost.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Significantly larger toast tables on 8.4?

[Stephen R. van den Berg]

Tom Lane wrote:
>and I'm still pretty worried about the longevity of any knob we put in
>here.  But we might not have a lot of choice.

>It would be fairly easy, I think, to add some reloption fields that
>would let these parameters be controlled on a per-table level.
>Per-column would be much more painful; do we really need that?

+1

Per table sounds fine for now.  Per column would be nice, but can be worked
around if absolutely necessary by splitting tables.

To avoid having to add another parameter later, I *would* suggest to use
something like:

ALTER TABLE mytable SET COMPRESSIONLEVEL = 9;

Where it can range from 0 (= no compression), to 9 (= maximum compression).
The current algorithm could then either be as simplistic as to kick in
anytime COMPRESSIONLEVEL>=1, and not to compress when COMPRESSIONLEVEL==0.
More advanced algorithms and decisions can be implemented later.

Obviously the algorithm should ideally use the one-dimensional knob to
more or less deliver IO-bound (de)compression at level one, and CPU-bound
(de)compression at level nine.

This kind of one-dimensional knob is well understood by many compression
tools and libraries and users, so it'd make sense to provide something similar
to the DBA.
-- 
Sincerely,
   Stephen R. van den Berg.

Expect the unexpected!

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

Re: [HACKERS] reloptions and toast tables

[Dave Page]

On Wed, Dec 31, 2008 at 9:45 PM, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> Peter Eisentraut wrote:
>>> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
 ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
 ALTER TABLE foo SET (toast.autovacuum_enabled = false);
 ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
 ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>>>
>>> The last two don't appear to allow setting TOAST and non-TOAST options in 
>>> one
>>> go.  I think it would be handy to allow that, though.
>
>> Agreed -- so I'm now playing with this version:
>
>> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
>
>> So the grammar modifications needed to accept that are attached.  The
>> support code is a lot messier than I'd like :-(
>
> This is not only really ugly, but 100% toast-specific.  The
> qualified-name approach ("toast.autovacuum_enabled") has at least
> a chance of being good for something else.  Or just make it
> toast_autovacuum_enabled and do the translation magic at some low
> level in the statement execution code.

Are we expecting this patch (or whatever it turns into) to go into
8.4? It was marked as WIP when feature freeze started and clearly
still is quite undefined at this stage.

The reason I raise this is that this is precisely the sort of patch
that has a major knock-on effect to the tools the many people expect
to be able to use with a new version of the server as soon as it's
released. Obviously we need our own freeze and beta periods prior to
that time which is already extremely tight as we wait for last minute
changes in the server that need support.  The last thing we need is
for something like the per-table vacuum settings interface to
redefined right before beta as that is likely to require a fair amount
of re-working.

This is something I think we need to be more mindful of as our
project, it's surrounding eco-system of tools and users expectations
grow.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

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

Re: [HACKERS] generic reloptions improvement

[KaiGai Kohei]

Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>> Tom Lane wrote:
>>> Alvaro Herrera  writes:
 I'm intending to have a new routine which would reserve a value at
 runtime.  This value would be later be passed by the AM to create new
 options on the table.
>>> What do you mean by "at runtime"?  Surely the value would have to remain
>>> stable across database restarts, since it's going to relate to stuff
>>> that is in catalog entries.
>> No, there's no need for the value to be stable across restart; what's
>> stored in catalogs is the option name, which is linked to the kind
>> number only in the parser table.
> 
> So this is an updated patch.  This now allows a user-defined AM to
> create new reloptions and pass them down to the parser for parsing and
> checking.  I also attach a proof-of-concept patch that adds three new
> options to btree (which do nothing apart from logging a message at
> insert time).  This patch demonstrates the coding pattern that a
> user-defined AM should follow to add and use new storage options.
> 
> The main thing I find slightly hateful about this patch is that the code
> to translate from the returned relopt_value array and the fixed struct
> is rather verbose; and that the AM needs to duplicate the code in
> default_reloptions.  I don't find it ugly enough to warrant objecting to
> the patch as a whole however.
> 
> The neat thing about this code is that the parsing and searching is done
> only once, when the relcache entry is loaded.  Later accesses to the
> option values themselves is just a struct access, and thus plenty quick.

This patch does not support reloptions in string expression, like:

  CREATE TABLE t1 (
  a int,
  b text
  ) WITH (default_row_acl='{yamada=r/kaigai}');

Do you have any plan to support reloptions in string?

Thanks,
-- 
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] generic reloptions improvement

[Simon Riggs]

On Tue, 2008-12-30 at 12:31 -0300, Alvaro Herrera wrote:
> Alvaro Herrera wrote:
> > Tom Lane wrote:
> > > Alvaro Herrera  writes:
> > 
> > > > I'm intending to have a new routine which would reserve a value at
> > > > runtime.  This value would be later be passed by the AM to create new
> > > > options on the table.
> > > 
> > > What do you mean by "at runtime"?  Surely the value would have to remain
> > > stable across database restarts, since it's going to relate to stuff
> > > that is in catalog entries.
> > 
> > No, there's no need for the value to be stable across restart; what's
> > stored in catalogs is the option name, which is linked to the kind
> > number only in the parser table.
> 
> So this is an updated patch.  This now allows a user-defined AM to
> create new reloptions and pass them down to the parser for parsing and
> checking.  I also attach a proof-of-concept patch that adds three new
> options to btree (which do nothing apart from logging a message at
> insert time).  This patch demonstrates the coding pattern that a
> user-defined AM should follow to add and use new storage options.
> 
> The main thing I find slightly hateful about this patch is that the code
> to translate from the returned relopt_value array and the fixed struct
> is rather verbose; and that the AM needs to duplicate the code in
> default_reloptions.  I don't find it ugly enough to warrant objecting to
> the patch as a whole however.
> 
> The neat thing about this code is that the parsing and searching is done
> only once, when the relcache entry is loaded.  Later accesses to the
> option values themselves is just a struct access, and thus plenty quick.

I very much like the idea of adding new/custom options to tables. There
are many uses for that.

What you have here looks fairly hard to program for normal users. I
don't want to reject the feature, but the proposal you have here isn't
the best it could be...

Can we have something like customer variable classes, but just for
reloptions? 

e.g. WITH (mymodule.my_option_name = X)
e.g. WITH (funky_trigger.coolness = 25)

We can then create new custom reloptions in roughly the same way we can
create custom variable classes, or ignore them if module not loaded.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] generic reloptions improvement

[Alvaro Herrera]

KaiGai Kohei wrote:
> Alvaro Herrera wrote:

> > So this is an updated patch.  This now allows a user-defined AM to
> > create new reloptions and pass them down to the parser for parsing and
> > checking.
> 
> This patch does not support reloptions in string expression, like:

No, it doesn't.  I asked about it some time ago and nobody answered.  If
people feel it is important, I can implement it.

-- 
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] generic reloptions improvement

[Alvaro Herrera]

Simon Riggs wrote:

> I very much like the idea of adding new/custom options to tables. There
> are many uses for that.

Hmm, like what?  I'm not sure how would it work for tables; you'd have
to add the options during _PG_init or something like that, and I haven't
tried it.  It's mainly for user-defined AMs that this was done.

> What you have here looks fairly hard to program for normal users. I
> don't want to reject the feature, but the proposal you have here isn't
> the best it could be...

Agreed ...

> Can we have something like customer variable classes, but just for
> reloptions? 
> 
> e.g. WITH (mymodule.my_option_name = X)
> e.g. WITH (funky_trigger.coolness = 25)
> 
> We can then create new custom reloptions in roughly the same way we can
> create custom variable classes, or ignore them if module not loaded.

I'm now playing with adding "namespaces" to the options, but that's for
handling options for toast tables.  I'm not really sure how would it
work for regular options.

-- 
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] reloptions and toast tables

[Alvaro Herrera]

Dave Page wrote:

> Are we expecting this patch (or whatever it turns into) to go into
> 8.4? It was marked as WIP when feature freeze started and clearly
> still is quite undefined at this stage.

Right.  This is a fair objection.  I started just by reviewing the
autovacuum-in-reloptions patch, but it turned out to be unworkable in
quite some ways, so I'm reworking it.

I need some more opinions on whether I should continue working here, or
stop and leave it for 8.5.

-- 
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] [BUGS] BUG #4599: bugfix for contrib/dblink module

[Tom Lane]

Joe Conway  writes:
> On line 795 below, fail should get set to PG_GETARG_BOOL(1). However, as 
> line 842 is about to be executed, fail is still set to true, even though 
> PG_GETARG_BOOL(1) is clearly false. Any ideas?

I can't duplicate that here, but my first reaction on studying this code
is "ick!".  Having a non-set-returning function calling the SRF
infrastructure (and not bothering to clean it up on exit, either) is
just horrid --- I have no idea what side-effects that might have, but at
the very least there's going to be a memory leak.  Trying to implement
three significantly different functions as one function with a maze of
if's is not good style in any case.

I think you should break those three functions apart.  There is no value
in having send_query share any code with the others.  It might be
feasible to have the other two share a subroutine that collects the
result data.

regards, tom lane

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

Re: [HACKERS] generic reloptions improvement

[Simon Riggs]

On Sat, 2009-01-03 at 12:20 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> 
> > I very much like the idea of adding new/custom options to tables. There
> > are many uses for that.
> 
> Hmm, like what?  I'm not sure how would it work for tables; you'd have
> to add the options during _PG_init or something like that, and I haven't
> tried it.  It's mainly for user-defined AMs that this was done.

I understand and agree with your intended use. I see others as well and
would like to cater for them all in a generic way that will have many
uses over next 10-20 years, many of which I haven't thought of yet.

Custom variable classes are often useful, but they are system wide. It
would be good to be able to use table-level options and have them work
very similarly to something we already have. Table-level options are
just an obvious "normalisation" of how we handle parameters.

If you really can't see a use for this, OK, then: Please can you put in
a plugin API for user defined reloptions as well as what you are
proposing. We discussed this before in late July/early Aug on thread
"Uncopied parameters..."

> > Can we have something like customer variable classes, but just for
> > reloptions? 
> > 
> > e.g. WITH (mymodule.my_option_name = X)
> > e.g. WITH (funky_trigger.coolness = 25)
> > 
> > We can then create new custom reloptions in roughly the same way we can
> > create custom variable classes, or ignore them if module not loaded.
> 
> I'm now playing with adding "namespaces" to the options, but that's for
> handling options for toast tables.  I'm not really sure how would it
> work for regular options.

toast.option_x
btree.option_y
autovacuum.option_z

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] generic reloptions improvement

[KaiGai Kohei]

Alvaro Herrera wrote:

KaiGai Kohei wrote:

Alvaro Herrera wrote:



So this is an updated patch.  This now allows a user-defined AM to
create new reloptions and pass them down to the parser for parsing and
checking.

This patch does not support reloptions in string expression, like:


No, it doesn't.  I asked about it some time ago and nobody answered.  If
people feel it is important, I can implement it.


Oh, I missed to see the message.

If it is provided for v8.4, I'm happy at least.
The Row-level ACLs need its reloption to specify default ACLs in string
expression. Currently, it modifies "reloptions.c", but using it on common
framework will be more appropriate implementation.

Thanks,
--
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] generic reloptions improvement

[Alvaro Herrera]

KaiGai Kohei wrote:
> Alvaro Herrera wrote:

>> No, it doesn't.  I asked about it some time ago and nobody answered.  If
>> people feel it is important, I can implement it.
>
> Oh, I missed to see the message.
>
> If it is provided for v8.4, I'm happy at least.
> The Row-level ACLs need its reloption to specify default ACLs in string
> expression. Currently, it modifies "reloptions.c", but using it on common
> framework will be more appropriate implementation.

Ok, I see it now.  I will have to implement string reloptions then.
Thanks for the notice.

-- 
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] generic reloptions improvement

[Alvaro Herrera]

Simon Riggs wrote:


> If you really can't see a use for this, OK, then: Please can you put in
> a plugin API for user defined reloptions as well as what you are
> proposing. We discussed this before in late July/early Aug on thread
> "Uncopied parameters..."

Hmm, I was just looking at the CREATE TABLE LIKE code yesterday; I
didn't remember that discussion.  I'll have a more detailed look.

> > I'm now playing with adding "namespaces" to the options, but that's for
> > handling options for toast tables.  I'm not really sure how would it
> > work for regular options.
> 
> toast.option_x
> btree.option_y
> autovacuum.option_z

autovacuum as a namespace doesn't work, because we need to have
autovacuum options for toast too.  If we went down this route we would
need to have two name levels.

-- 
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] Custom PGC_POSTMASTER GUC variables ... feasible?

[Tom Lane]

I wrote:
> ... This doesn't actually work in the current system.

I have a solution for this, I think.  I propose that guc.c only allow
custom PGC_POSTMASTER variables to be created during
process_shared_preload_libraries().  (The implementation for this
would involve exporting a bool flag that process_shared_preload_libraries
sets while it's running.)  That would come down to two cases:

1. A loadable library is being preloaded into the postmaster.  This
happens only at postmaster startup, so it's clearly okay to create
a PGC_POSTMASTER variable then.

2. A loadable library is being loaded during startup of a child process
in the EXEC_BACKEND case.  Since the shared_preload_libraries list is
itself a PGC_POSTMASTER variable, it can't have been changed since
postmaster start.  Therefore, the library we are loading is also present
in the postmaster and the no-change-after-start rule has been enforced
on the variable all along.  We're just instantiating it in the current
process, and we can trust that the value we inherited matches other
processes.

You could break this if you tried hard enough, like replace a library
with a new version underneath a running EXEC_BACKEND system, where
the new copy creates a PGC_POSTMASTER variable that the original
didn't.  But that requires superuser privileges so it's not a security
hazard, just a don't-do-that issue.  (There are plenty of other ways
such a replacement could break things, anyhow.)

What this would mean is that a library that needs to define a
PGC_POSTMASTER variable would need to fail if loaded with an ordinary
LOAD command.  The most graceful way to do that is for it to examine the
process_shared_preload_libraries_in_progress flag for itself in its
_PG_init hook, and if not set report a warning and exit without doing
anything.  If it fails to do so, and control actually gets to the point
of guc.c having to reject the PGC_POSTMASTER variable creation, I think
we'd better make that be a FATAL error.  The reason is that the
noncooperative library may be partially hooked into the backend already
and so its behavior is likely to be messed up.

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] reloptions and toast tables

[Tom Lane]

Alvaro Herrera  writes:
> I need some more opinions on whether I should continue working here, or
> stop and leave it for 8.5.

Bruce and I were just talking yesterday about the need to start closing
down this commitfest.  I'm not sure what the schedule is going to end
up being; but if you can't see a pretty short path to finishing whatever
development still needs doing, my advice is to set it aside for 8.5.

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] Custom PGC_POSTMASTER GUC variables ... feasible?

[Alex Hunsaker]

On Sat, Jan 3, 2009 at 09:37, Tom Lane  wrote:
> I wrote:
>> ... This doesn't actually work in the current system.
>
> I have a solution for this, I think.  I propose that guc.c only allow
> custom PGC_POSTMASTER variables to be created during
> process_shared_preload_libraries().

Sounds simple enough.

> You could break this if you tried hard enough, like replace a library
> with a new version underneath a running EXEC_BACKEND system, where
> the new copy creates a PGC_POSTMASTER variable that the original
> didn't.  But that requires superuser privileges so it's not a security
> hazard, just a don't-do-that issue.  (There are plenty of other ways
> such a replacement could break things, anyhow.)

Right I agree this is a non-issue.  For that matter if I really wanted
to muck with it I could just set
process_shared_preload_libraries_in_progress = true in my _PG_init
function.  And I guess if anyone thinks thats a problem we can mark
the flag as static and only export a function for reading the value.

> What this would mean is that a library that needs to define a
> PGC_POSTMASTER variable would need to fail if loaded with an ordinary
> LOAD command.  The most graceful way to do that is for it to examine the
> process_shared_preload_libraries_in_progress flag for itself in its
> _PG_init hook, and if not set report a warning and exit without doing
> anything.  If it fails to do so, and control actually gets to the point
> of guc.c having to reject the PGC_POSTMASTER variable creation, I think
> we'd better make that be a FATAL error.  The reason is that the
> noncooperative library may be partially hooked into the backend already
> and so its behavior is likely to be messed up.

Agreed.

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

Re: [HACKERS] [BUGS] BUG #4599: bugfix for contrib/dblink module

[Joe Conway]

Tom Lane wrote:

I think you should break those three functions apart.  There is no value
in having send_query share any code with the others.  It might be
feasible to have the other two share a subroutine that collects the
result data.


OK, will do. I applied the simple fix to the 8.2 and 8.3 branches, but 
will do refactoring per your suggestion on cvs tip.


Thanks,

Joe

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

Re: [HACKERS] contrib/pg_stat_statements 1226

[Alex Hunsaker]

On Fri, Jan 2, 2009 at 20:20, Tom Lane  wrote:
> I wrote:
>> * the startup/shutdown hooks will be installed in the postmaster
>> process, but the patch expects them to be executed in a child process.
>> I think nothing will happen.
>
> OK, I figured out what is happening there: the patch makes it work by
> means of this expedient:



> I find this mighty Rube Goldbergian.  We have a startup hook which is
> declared in include/storage/ipc.h, but defined and called in bootstrap.c
> (whence it will actually be executed down inside the startup process).
> We have a shutdown hook which is also declared in include/storage/ipc.h,
> but defined and called in bgwriter.c (executed in the bgwriter process,
> of course).  And to make those hooks work in the EXEC_BACKEND case, we
> have a kluge inserted in proc.c, miles away from where the existing
> process_shared_preload_libraries() calls are (in postmaster.c), and with
> extremely high probability of someday resulting in duplicate preload
> operations if the postmaster.c code gets shuffled.

Kudos to Itagaki-san for getting that to work?

> As for the shutdown hook, I don't think we need it at all in this
> design.  When loaded into the postmaster process, pg_stat_statements can
> insert itself into the on_proc_exit or on_shmem_exit hook lists ... it
> doesn't need a private hook.

Ok cool.

> The right way to make that happen is to rearrange the coding in
> SubPostmasterMain() so that process_shared_preload_libraries is
> done in all cases, just after the read_nondefault_variables call.

This should also fix the rmg hooks patch on EXEC_BACKEND.

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

Re: [HACKERS] Custom PGC_POSTMASTER GUC variables ... feasible?

[Tom Lane]

"Alex Hunsaker"  writes:
> Right I agree this is a non-issue.  For that matter if I really wanted
> to muck with it I could just set
> process_shared_preload_libraries_in_progress = true in my _PG_init
> function.  And I guess if anyone thinks thats a problem we can mark
> the flag as static and only export a function for reading the value.

Yeah, I thought about that and decided to leave it as a variable ---
if anyone actually has a good reason to do it, they have an (ugly)
workaround available this way.  We're only trying to catch errors
of omission, not prevent C-level code from subverting the system
if it wants to.

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] contrib/pg_stat_statements 1226

[Tom Lane]

ITAGAKI Takahiro  writes:
> "Alex Hunsaker"  wrote:
>> How about just pg_stat_statements.track ?

> I renamed the variables to:
> - pg_stat_statements.limit
> - pg_stat_statements.track
> - pg_stat_statements.saved_file

Hmm, this has got a problem:

regression=# show pg_stat_statements.limit ;
ERROR:  syntax error at or near "limit"
LINE 1: show pg_stat_statements.limit ;
^

LIMIT is a reserved word ...

I thought max_statements was fine, but if people think that's too long
I guess we could go with just "max".

I'm not enamored of "saved_file" either, it seems like the wrong
part of speech somehow.  Maybe "save_in_file"?

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] reloptions and toast tables

[Magnus Hagander]

Tom Lane wrote:
> Alvaro Herrera  writes:
>> I need some more opinions on whether I should continue working here, or
>> stop and leave it for 8.5.
> 
> Bruce and I were just talking yesterday about the need to start closing
> down this commitfest.  I'm not sure what the schedule is going to end
> up being; but if you can't see a pretty short path to finishing whatever
> development still needs doing, my advice is to set it aside for 8.5.

I agree in principle. OTOH, the current behavior can almost be defined
as a bug, since we can't restore any changes you make to the autovacuum
configuration. I know it's by design, but it makes it pretty fragile to
use the ability to change the configuration at all...

//Magnus


-- 
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_stats queries versus per-database encodings

[Tom Lane]

I notice that the pg_stat_statements patch is applying pg_mbcliplen()
to query strings, in the fond illusion that it knows what encoding
they are in.

This brings up a bigger issue, namely that pg_stat_activity isn't
exactly encoding-proof either --- whatever encoding is in use in a
particular database is what query strings from backends in that database
will be stored in.  Readers in another database will be exposed to
strings that probably aren't encoded correctly for their DB.

We could attack this by including source database's encoding in the
shared-memory entries, and performing a conversion on the fly when
reading out the data.  However, what happens if the conversion fails?
Seems like this provides a way for users to hide their queries from
the DBA ... just include a comment with some characters that are
untranslatable.

Thoughts?

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] Latest version of Hot Standby patch

[Guillaume Lelarge]

Simon Riggs a écrit :
> On Fri, 2009-01-02 at 17:35 +, Simon Riggs wrote:
> 
>>> I use latest CVS version. I tried to apply the patches and I have the
>>> following error :
>> Thanks, will fix.
> 
> Fixed various bit rots and re-packaged. v6a now up, v6 unlinked.
> 

Thanks. I only did a few checks and it worked great for me. I will try
to put some more time on it.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.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] New patch for Column-level privileges

[Alex Hunsaker]

On Fri, Jan 2, 2009 at 16:11, Stephen Frost  wrote:
> * Stephen Frost (sfr...@snowman.net) wrote:
>>   Please find attached an updated patch for column-level privileges
>>   which incorporates Alvaro's suggested changes and is updated to the
>>   latest CVS HEAD.

Hi!

This gives me
aclchk.c: In function 'ExecuteGrantStmt':
aclchk.c:276: warning: 'errormsg_col' may be used uninitialized in this function

Now it looks bogos but why not just move errmsg_col down to where we
actually use it?  Or am I missing something?

*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
***
*** 273,279  ExecuteGrantStmt(GrantStmt *stmt)
InternalGrant istmt;
ListCell   *cell;
const char *errormsg;
-   const char *errormsg_col;
AclMode all_privileges;

/*
--- 273,278 
***
*** 324,330  ExecuteGrantStmt(GrantStmt *stmt)
case ACL_OBJECT_RELATION:
all_privileges = ACL_ALL_RIGHTS_RELATION | 
ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for 
relation");
-   errormsg_col = gettext_noop("invalid privilege type %s 
for column");
break;
case ACL_OBJECT_SEQUENCE:
all_privileges = ACL_ALL_RIGHTS_SEQUENCE;
--- 323,328 
***
*** 362,368  ExecuteGrantStmt(GrantStmt *stmt)
/* keep compiler quiet */
all_privileges = ACL_NO_RIGHTS;
errormsg = NULL;
-   errormsg_col = NULL;
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
 (int) stmt->objtype);
}
--- 360,365 
***
*** 404,409  ExecuteGrantStmt(GrantStmt *stmt)
--- 401,408 
{
/* Column-level privileges given. */
ListCell *cell_obj;
+   const char *errormsg_col = 
gettext_noop("invalid privilege type
%s for column");
+   

/* Must be of objtype ACL_OBJECT_RELATION for 
column
 * level privileges */


fixwarning.patch
Description: Binary data

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

Re: [HACKERS] parallel restore

[Andrew Dunstan]

I wrote:


Attached is the latest parallel restore patch. I think this is getting 
fairly close.


Includes help text, docco and some extra error checking.

  


I propose to commit this unless someone wants more time for reviewing.

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] New patch for Column-level privileges

[Stephen Frost]

Alex,

* Alex Hunsaker (bada...@gmail.com) wrote:
> Now it looks bogos but why not just move errmsg_col down to where we
> actually use it?  Or am I missing something?

Honestly, I think at this point is makes the most sense to just remove
it entirely, which I've done in the attached patch.

Thanks,

Stephen


colprivs_wip.2009010301.diff.gz
Description: Binary data


signature.asc
Description: Digital signature

Re: [HACKERS] posix_fadvise v22

[Peter Eisentraut]

On Friday 02 January 2009 06:49:57 Greg Stark wrote:
> The guc run-time check is checking for known-buggy versions of glibc  
> using sysconf to check what version of glibc you have.

Could you please mention the bug number in the relevant source code comments?

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

Re: [HACKERS] incoherent view of serializable transactions

[Peter Eisentraut]

On Wednesday 31 December 2008 02:33:26 Kevin Grittner wrote:
> I'm still working on section "Serializable Isolation versus True
> Serializability", but here are all the changes I can see which precede
> it.  Has the review of the SQL specs convinced everyone that this much
> is appropriate?

I don't agree with these changes.  You make it sound like serializability is 
an additional condition on the serializable isolation level on top of the 
no-phantom-reads condition.  I think that is not true, both mathematically 
and from the wording of the SQL standard.  It is an equivalent condition or a 
consequence, depending on how you view it.

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

Re: [HACKERS] incoherent view of serializable transactions

[Peter Eisentraut]

On Tuesday 30 December 2008 19:28:01 Kevin Grittner wrote:
> Here we go.  I've labeled the transactions consistently with new
> thread I started trying to characterize the full scope of issues and
> workarounds.

OK, I believe it now. :-)

The missing predicate locking is again at fault here.  Because this ...

> -- connection 1 (start of T0)
> start transaction isolation level serializable;
> insert into receipt values (3, (select deposit_date from ctl where k =
> 'receipt'), 4.00);

... would lock ctl where k = 'receipt' ...

> -- connection 2 (T1)
> start transaction isolation level serializable;
> update ctl set deposit_date = date '2008-12-23' where k = 'receipt';
> commit transaction;

... and then this would have to wait ...

> -- connection 2 (TN version 2)
> start transaction isolation level serializable;
> insert into receipt_totals
>   select r.deposit_date, c.deposit_date, sum(amount)
> from ctl c join receipt r
>   on ( r.deposit_date < c.deposit_date
>and not exists
>(
>  select * from receipt r2
>where r2.deposit_date < c.deposit_date
>  and r2.deposit_date > r.deposit_date
>)
>  )
> group by r.deposit_date, c.deposit_date;
> commit transaction;

... and this could never be executed before T0 commits.

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

Re: [HACKERS] Frames vs partitions: is SQL2008 completely insane?

[Peter Eisentraut]

On Saturday 27 December 2008 20:32:10 Ron Mayer wrote:
> ISTM ISO should hire you guys (or the postgres project as a whole)
> to proof-read their specs before they publish them.

The way it really works though, effectively, is that vendors hire ISO to 
publish their specs.

Having a few inconsistencies in 2000 pages of language specification with 20 
years of legacy around it isn't so bad IMO, considering that there are really 
only a handful of guys working on this with any intensity.

If we cared enough, we could submit these sorts of issues to the committee for 
clarification or correction.  If anyone is convinced enough about this 
particular case, I can try to relay it and see what happens.

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

Re: [HACKERS] dblink vs SQL/MED

[Joe Conway]

Joe Conway wrote:

Peter Eisentraut wrote:
Martin had sent some code for that with his original patch series.  I 
or someone can review that next.


Here is what I would propose (still needs documentation and regression 
test changes, but wanted feedback first). I think it is better to keep 
the changes to dblink very simple.


The attached now includes documentation and regression test changes. It 
also includes the refactoring to pull dblink_send_query() out of 
dblink_record_internal() and the fix for the bug reported by Oleksiy 
Shchukin.


After looking for an already existing dblink rconn, the passed connstr 
is checked to see if it matches a valid foreign data server prior to 
being used as a connstr. If so, a connstr is constructed from the 
foreign server and user mapping options (for current user). The 
resulting connstr is then treated exactly as if it were one that was 
passed directly to dblink.


Two specific questions on this approach:
1. This implies that the exact same dblink_connstr_check() is performed
   on a predefined foreign server and user mapping as a raw connstr --
   is this desireable? I'm not entirely clear on the intended purpose
   and use of foreign data wrappers yet.


On the one hand, why be any less stringent on an fdw server than any 
other connect string? But on the other hand, the fdw server definition 
has supposedly been vetted by a superuser. Any thoughts of this?



2. It seems like get_connect_string() is generically useful to any
   client of postgresql_fdw.c -- should it go there instead of dblink?


I'm pretty sure get_connect_string() should be moved to postgresql_fdw.c 
-- objections?


Thanks,

Joe
Index: contrib/dblink/dblink.c
===
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.77
diff -c -r1.77 dblink.c
*** contrib/dblink/dblink.c	1 Jan 2009 17:23:31 -	1.77
--- contrib/dblink/dblink.c	3 Jan 2009 21:25:34 -
***
*** 46,51 
--- 46,52 
  #include "catalog/pg_type.h"
  #include "executor/executor.h"
  #include "executor/spi.h"
+ #include "foreign/foreign.h"
  #include "lib/stringinfo.h"
  #include "miscadmin.h"
  #include "nodes/execnodes.h"
***
*** 77,83 
  /*
   * Internal declarations
   */
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get);
  static remoteConn *getConnectionByName(const char *name);
  static HTAB *createConnHash(void);
  static void createNewConnection(const char *name, remoteConn * rconn);
--- 78,84 
  /*
   * Internal declarations
   */
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async);
  static remoteConn *getConnectionByName(const char *name);
  static HTAB *createConnHash(void);
  static void createNewConnection(const char *name, remoteConn * rconn);
***
*** 96,101 
--- 97,103 
  static void dblink_connstr_check(const char *connstr);
  static void dblink_security_check(PGconn *conn, remoteConn *rconn);
  static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
+ static char *get_connect_string(const char *servername);
  
  /* Global */
  static remoteConn *pconn = NULL;
***
*** 165,171 
  			} \
  			else \
  			{ \
! connstr = conname_or_str; \
  dblink_connstr_check(connstr); \
  conn = PQconnectdb(connstr); \
  if (PQstatus(conn) == CONNECTION_BAD) \
--- 167,175 
  			} \
  			else \
  			{ \
! connstr = get_connect_string(conname_or_str); \
! if (connstr == NULL) \
! 	connstr = conname_or_str; \
  dblink_connstr_check(connstr); \
  conn = PQconnectdb(connstr); \
  if (PQstatus(conn) == CONNECTION_BAD) \
***
*** 210,215 
--- 214,220 
  Datum
  dblink_connect(PG_FUNCTION_ARGS)
  {
+ 	char	   *conname_or_str = NULL;
  	char	   *connstr = NULL;
  	char	   *connname = NULL;
  	char	   *msg;
***
*** 220,235 
  
  	if (PG_NARGS() == 2)
  	{
! 		connstr = text_to_cstring(PG_GETARG_TEXT_PP(1));
  		connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
  	}
  	else if (PG_NARGS() == 1)
! 		connstr = text_to_cstring(PG_GETARG_TEXT_PP(0));
  
  	if (connname)
  		rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
    sizeof(remoteConn));
  
  	/* check password in connection string if not superuser */
  	dblink_connstr_check(connstr);
  	conn = PQconnectdb(connstr);
--- 225,245 
  
  	if (PG_NARGS() == 2)
  	{
! 		conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(1));
  		connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
  	}
  	else if (PG_NARGS() == 1)
! 		conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
  
  	if (connname)
  		rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
    sizeof(remoteConn));
  
+ 	/* first check for valid foreign data server */
+ 	connstr = get_connect_string(conname_or_str);
+ 	if (connstr ==

Re: [HACKERS] Significantly larger toast tables on 8.4?

[Peter Eisentraut]

On Friday 02 January 2009 22:23:13 Stephen R. van den Berg wrote:
> Three things:
> a. Shouldn't it in theory be possible to have a decompression algorithm
>which is IO-bound because it decompresses faster than the disk can
>supply the data?  (On common current hardware).
> b. Has the current algorithm been carefully benchmarked and/or optimised
>and/or chosen to fit the IO-bound target as close as possible?
> c. Are there any well-known pitfalls/objections which would prevent me from
>changing the algorithm to something more efficient (read: IO-bound)?

copyright licenses and patents

Which doesn't mean changing anything is impossible, but it is tricky in those 
nontechnical ways.

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

Re: [SPAM] Re: [HACKERS] posix_fadvise v22

[Gregory Stark]

Peter Eisentraut  writes:

> On Friday 02 January 2009 06:49:57 Greg Stark wrote:
>> The guc run-time check is checking for known-buggy versions of glibc  
>> using sysconf to check what version of glibc you have.
>
> Could you please mention the bug number in the relevant source code comments?

It's Debian bug# 312406 which was fixed in Debian release 2.3.5-3. So it's
probably one of these but searching for posix_fadvise doesn't find anything in
their bug tracker:

Version 2.3.6

* The following bugs are resolved with this release:

  38, 253, 549, 622, 653, 721, 758, 851, 877, 915, 934, 955, 961,
  1016, 1037, 1076, 1079, 1080, 1081, 1082, 1083, 1084, 1085, 1086,
  1087, 1088, 1090, 1091, 1092, 1093, 1094, 1095, 1096, 1097, 1098,
  1099, 1100, 1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108, 1109,
  1110, , 1112, 1113, 1125, 1137, 1138, 1249, 1250, 1251, 1252,
  1253, 1254, 1350, 1358, 1394, 1438, 1498, 1534

  Visit  for the details of each bug.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres 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] incoherent view of serializable transactions

[Gregory Stark]

Peter Eisentraut  writes:

> On Wednesday 31 December 2008 02:33:26 Kevin Grittner wrote:
>> I'm still working on section "Serializable Isolation versus True
>> Serializability", but here are all the changes I can see which precede
>> it.  Has the review of the SQL specs convinced everyone that this much
>> is appropriate?
>
> I don't agree with these changes.  You make it sound like serializability is 
> an additional condition on the serializable isolation level on top of the 
> no-phantom-reads condition.  I think that is not true, both mathematically 
> and from the wording of the SQL standard.  It is an equivalent condition or a 
> consequence, depending on how you view it.

The standard explicitly says that the no-phantom-reads condition is a
consequence of the serializability constraint. Did you miss that whole
discussion this past week?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS 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] Significantly larger toast tables on 8.4?

[Peter Eisentraut]

On Saturday 03 January 2009 03:36:16 Tom Lane wrote:
> "Stephen R. van den Berg"  writes:
> > - I currently have difficulty imagining applications that actually do
> >   lots of substring extractions from large compressible fields.
>
> The code that's in there to make this happen was written by people who
> needed the feature.  They're going to be upset with you if you propose
> disabling it.

I think what he is saying is that it is the less likely use case and should 
therefore tend to be not the default.

Also note that the code in there was written about 8 years ago, when dealing 
with "large" data was an entirely different game.  People where happy to 
access more than 8 kB then.

I would in fact imagine that substring operations are more likely to happen 
with data smaller than 1 MB, and less likely with data larger than 1 MB, 
instead of the other way around, which is currently implemented.  The main 
sensible way to access text fields larger than 1 MB is with text search, as 
was pointed out.  And large bytea fields are probably media files that are 
probably already compressed and have no sensible use for substring 
operations.

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

Re: [HACKERS] incoherent view of serializable transactions

[Peter Eisentraut]

On Sunday 04 January 2009 00:53:51 Gregory Stark wrote:
> Peter Eisentraut  writes:
> > On Wednesday 31 December 2008 02:33:26 Kevin Grittner wrote:
> >> I'm still working on section "Serializable Isolation versus True
> >> Serializability", but here are all the changes I can see which precede
> >> it.  Has the review of the SQL specs convinced everyone that this much
> >> is appropriate?
> >
> > I don't agree with these changes.  You make it sound like serializability
> > is an additional condition on the serializable isolation level on top of
> > the no-phantom-reads condition.  I think that is not true, both
> > mathematically and from the wording of the SQL standard.  It is an
> > equivalent condition or a consequence, depending on how you view it.
>
> The standard explicitly says that the no-phantom-reads condition is a
> consequence of the serializability constraint. Did you miss that whole
> discussion this past week?

The SQL standard says it in about three different ways, and textbooks might 
define it an three more ways.  That still doesn't negate my concern with the 
way his patch phrases the issue.

A language lawyer might also point out that the note that contains 
the "explicitness" isn't actually part of the formal standard.  The only 
thing that the standard formally defines are the excluded phenomena.

More to the point, think about how a user might want to think about these 
issues.

"The standard also requires that serializable transactions behave as though 
[...]" --- User: The standard requires it, but is it also implemented?  
(Apparently not, but that is explained somewhere else.)

"is a natural consequence of the fact" --- There is nothing natural about any 
of this.  Why is it a consequence and how?

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

[HACKERS] Time to finalize patches for 8.4 beta

[Bruce Momjian]

Now that we are two months into the final commit fest, it is time to
finalize all the open patches so we can target a February beta.

The two major outstanding patches are:

o  SE-PostgreSQL:  The author has done an outstanding job of
reworking the patch so the burden is now on the community.  We have to
decide if we want to add this amount of new code to have both SQL row
permissions and SE-Linux support.

o Recovery, Replication, Hot Standby:  We need a _final_ version
of any patches that are targeted for 8.4.  There is so much activity in
this area I am unclear what is ready for 8.4.

I think the remaining patches can be addressed pretty easily but we need
final versions from any authors who are still adjusting them.

Let's see what we can get done in the next two weeks and reevaluate.

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

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Significantly larger toast tables on 8.4?

[Philip Warner]

Tom Lane wrote:
> It would be fairly easy, I think, to add some reloption fields that
> would let these parameters be controlled on a per-table level.
> Per-column would be much more painful; do we really need that?
>   

Another +1 on the per-table setting. Or a config file setting to disable
this for the instance.

We have a 200GB DB that is mostly large text (>1MB) that is not searched
with substr. If we see a blowout in size of even 3x, we will not be able
to upgrade due to disk space limitations (at least without paying for a
lot of disks on mirror servers and hot-standy servers).



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

Re: [HACKERS] generic reloptions improvement

[Alvaro Herrera]

KaiGai Kohei wrote:

> If it is provided for v8.4, I'm happy at least.
> The Row-level ACLs need its reloption to specify default ACLs in string
> expression. Currently, it modifies "reloptions.c", but using it on common
> framework will be more appropriate implementation.

Modified to add a string type.

Note that the real difficulty is what to do with the string in
default_reloptions (or the amoptions routine).  I see that your patch
has already dealt with that, so it should be pretty easy for you; for
any reloption that wants to be stored in rel->rd_options, it will be
considerably more difficult (due to memory allocation).

Some notes about this patch:

- the string type handling (basically all the new code) is untested.
I'll have a look tomorrow at the btree test code I sent the other day to
add a string option and see how it goes.

- I have added some macros to deal with options in the most common
scenario, which is that they get stored in a predefined struct.  This
hides part of the complexity in writing an amoptions routine.

- there's no way to define custom reloptions as requested by Simon.  I
don't have any ideas on how to do that at this time.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/access/common/reloptions.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.12
diff -c -p -r1.12 reloptions.c
*** src/backend/access/common/reloptions.c	1 Jan 2009 17:23:34 -	1.12
--- src/backend/access/common/reloptions.c	4 Jan 2009 03:07:38 -
***
*** 15,20 
--- 15,23 
  
  #include "postgres.h"
  
+ #include "access/gist_private.h"
+ #include "access/hash.h"
+ #include "access/nbtree.h"
  #include "access/reloptions.h"
  #include "catalog/pg_type.h"
  #include "commands/defrem.h"
***
*** 22,29 
--- 25,357 
  #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/guc.h"
+ #include "utils/memutils.h"
  #include "utils/rel.h"
  
+ /*
+  * Contents of pg_class.reloptions
+  *
+  * To add an option:
+  *
+  * (i) decide on a class (integer, real, bool, string), name, default value,
+  * upper and lower bounds (if applicable).
+  * (ii) add a record below.
+  * (iii) add it to StdRdOptions if appropriate
+  * (iv) add a block to the appropriate handling routine (probably
+  * default_reloptions)
+  * (v) don't forget to document the option
+  *
+  * Note that we don't handle "oids" in relOpts because it is handled by
+  * interpretOidsOption().
+  */
+ 
+ static relopt_bool boolRelOpts[] =
+ {
+ 	/* list terminator */
+ 	{ { NULL } }
+ };
+ 
+ static relopt_int intRelOpts[] =
+ {
+ 	{
+ 		{
+ 			"fillfactor",
+ 			"Packs table pages only to this percentage",
+ 			RELOPT_KIND_HEAP
+ 		},
+ 		HEAP_DEFAULT_FILLFACTOR, HEAP_MIN_FILLFACTOR, 100
+ 	},
+ 	{
+ 		{
+ 			"fillfactor",
+ 			"Packs btree index pages only to this percentage",
+ 			RELOPT_KIND_BTREE
+ 		},
+ 		BTREE_DEFAULT_FILLFACTOR, BTREE_MIN_FILLFACTOR, 100
+ 	},
+ 	{
+ 		{
+ 			"fillfactor",
+ 			"Packs hash index pages only to this percentage",
+ 			RELOPT_KIND_HASH
+ 		},
+ 		HASH_DEFAULT_FILLFACTOR, HASH_MIN_FILLFACTOR, 100
+ 	},
+ 	{
+ 		{
+ 			"fillfactor",
+ 			"Packs gist index pages only to this percentage",
+ 			RELOPT_KIND_GIST
+ 		},
+ 		GIST_DEFAULT_FILLFACTOR, GIST_MIN_FILLFACTOR, 100
+ 	},
+ 	/* list terminator */
+ 	{ { NULL } }
+ };
+ 
+ static relopt_real realRelOpts[] =
+ {
+ 	/* list terminator */
+ 	{ { NULL } }
+ };
+ 
+ static relopt_string stringRelOpts[] = 
+ {
+ 	/* list terminator */
+ 	{ { NULL } }
+ };
+ 
+ static relopt_gen **relOpts = NULL;
+ static int last_assigned_kind = RELOPT_KIND_LAST_DEFAULT + 1;
+ 
+ static int		num_custom_options = 0;
+ static relopt_gen **custom_options = NULL;
+ 
+ static void parse_one_reloption(relopt_value *option, char *text_str,
+ 	int text_len, bool validate);
+ 
+ /*
+  * initialize_reloptions
+  * 		initialization routine, must be called at backend start
+  *
+  * Initialize the relOpts array and fill each variable's type and name length.
+  */
+ void
+ initialize_reloptions(void)
+ {
+ 	int		i;
+ 	int		j = 0;
+ 
+ 	for (i = 0; boolRelOpts[i].gen.name; i++)
+ 		j++;
+ 	for (i = 0; intRelOpts[i].gen.name; i++)
+ 		j++;
+ 	for (i = 0; realRelOpts[i].gen.name; i++)
+ 		j++;
+ 	for (i = 0; stringRelOpts[i].gen.name; i++)
+ 		j++;
+ 	j += num_custom_options;
+ 
+ 	if (relOpts)
+ 		pfree(relOpts);
+ 	relOpts = MemoryContextAlloc(TopMemoryContext,
+  (j + 1) * sizeof(relopt_gen *));
+ 
+ 	j = 0;
+ 	for (i = 0; boolRelOpts[i].gen.name; i++)
+ 	{
+ 		relOpts[j] = &boolRelOpts[i].gen;
+ 		relOpts[j]->type = RELOPT_TYPE_BOOL;
+ 		relOpts[j]->namelen = strlen(relOpts[j]->name);
+ 		j++;
+ 	}
+ 
+ 	for (i = 0; intRelOpts[i].gen.name; i++)
+ 	{
+ 		relOpts[j] = &intRelOpts[i].gen;
+ 		relOpts[

Re: [HACKERS] Significantly larger toast tables on 8.4?

[Alex Hunsaker]

On Sat, Jan 3, 2009 at 20:47, Philip Warner  wrote:
> Tom Lane wrote:
>> It would be fairly easy, I think, to add some reloption fields that
>> would let these parameters be controlled on a per-table level.
>> Per-column would be much more painful; do we really need that?
>>
>
> Another +1 on the per-table setting. Or a config file setting to disable
> this for the instance.
>
> We have a 200GB DB that is mostly large text (>1MB) that is not searched
> with substr. If we see a blowout in size of even 3x, we will not be able
> to upgrade due to disk space limitations (at least without paying for a
> lot of disks on mirror servers and hot-standy servers).

Well I *really* doubt unless your text is extremely redundant you will
see a large increase if any.  Even if you dont search by substr,
fetching the data is quite could be quite a bit faster.  Depending on
how beefy the cpu's on the machine are.  A quick benchmark here says
by as much 200x! (30tps vs 6000tps).  Thats just a simple select on a
dual 2ghz opteron.

For the record I just imported a production database that sits at
about ~20G right now with *zero* size increase (rounding to the
nearest gigabyte).  That's with basically the exact same schema just
different data.

I don't suppose you could export some random rows and see if you see
any size increase for your data?  My gut says you wont see an
increase.

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

Re: [HACKERS] Significantly larger toast tables on 8.4?

[Philip Warner]

Alex Hunsaker wrote:
> For the record I just imported a production database that sits at
> about ~20G right now with *zero* size increase (rounding to the
> nearest gigabyte).  That's with basically the exact same schema just
> different data.
>
>   

Guessing you don't have many plain text rows  > 1M.

> I don't suppose you could export some random rows and see if you see
> any size increase for your data?  My gut says you wont see an
> increase.
>   

Will see what I can do.


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

Re: [HACKERS] Significantly larger toast tables on 8.4?

[Alex Hunsaker]

On Sat, Jan 3, 2009 at 21:56, Philip Warner  wrote:
> Alex Hunsaker wrote:
>> For the record I just imported a production database that sits at
>> about ~20G right now with *zero* size increase (rounding to the
>> nearest gigabyte).  That's with basically the exact same schema just
>> different data.
>>
> Guessing you don't have many plain text rows  > 1M.

Probably not.

>> I don't suppose you could export some random rows and see if you see
>> any size increase for your data?  My gut says you wont see an
>> increase.
>>
>
> Will see what I can do.


Actually assuming they dont have any multibyte chars you should just
be able to do something like the below on your existing database.

-- show anything we save a megabyte on

select die_id, pg_size_pretty(savings) from
( select length(debug) - pg_column_size(debug) as savings, die_id from
fooa) as foo
where savings > 1024*1024 order by savings desc;

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