Re: [HACKERS] WIP: URI connection string support for libpq

[Alexander Shulgin]

On 02/25/2012 09:37 PM, Cédric Villemain wrote:


I've not followed all the mails about this feature but I don't find it is a
nice syntax too.

"?dbname=other" looks like dbname is an argument, but dbname is a requirement
for postgresql connexion.


Ugh, not really.  AFAIK, dbname is a connection option which defaults to 
$USER, unless overridden on command line or in the environment (or via a 
service file.)


--
Alex

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

[HACKERS] How to know a table has been modified?

[Tatsuo Ishii]

I'm working on implementing query cache in pgpool-II. I want to know
if a table has been modified because pgpool-II has to invalidate cache
if corresponding table is modified. For DDL/DML it would be doable
since pgpool-II knows all SQLs sent from clients. Problem is, implicit
table modifications done by CASCADE, TRIGGERS and so on.

create table t1(i int, j int);
create table t2(i int references t1.i);
drop table t1 cascade;

In this example, if t1 is dropped, t2 is dropped as well. So query
cache corresponding to t1 and t2 should be invalidated. The only way I
could thinking of is, looking into pg_depend. I would like to know if
there's any better/convenient way to know it.

For TRIGGER, I cannot thinking of any way. Any idea will be welcome.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [HACKERS] Checkpoint sync pause

[Jeff Janes]

On Sun, Feb 12, 2012 at 10:49 PM, Amit Kapila  wrote:
>>> Without sorted checkpoints (or some other fancier method) you have to
>>> write out the entire pool before you can do any fsyncs.  Or you have
>>> to do multiple fsyncs of the same file, with at least one occurring
>>> after the entire pool was written.  With a sorted checkpoint, you can
>>> start issuing once-only fsyncs very early in the checkpoint process.
>>> I think that on large servers, that would be the main benefit, not the
>>> actually more efficient IO.  (On small servers I've seen sorted
>>> checkpoints be much faster on shutdown checkpoints, but not on natural
>>> checkpoints, and presumably this improvement *is* due to better
>>> ordering).
>
>>> On your servers, you need big delays between fsyncs and not between
>>> writes (as they are buffered until the fsync).  But in other
>>> situations, people need the delays between the writes.  By using
>>> sorted checkpoints with fsyncs between each file, the delays between
>>> writes are naturally delays between fsyncs as well.  So I think the
>>> benefit of using sorted checkpoints is that code to improve your
>>> situations is less likely to degrade someone else's situation, without
>>> having to introduce an extra layer of tunables.
>
> What I understood is that you are suggesting, it is better to do sorted
> checkpoints which essentially means flush nearby buffers together.

More importantly, you can issue an fsync after all pages for any given
file are written, thus naturally spreading out the fsyncs instead of
reserving them to until the end, or some arbitrary fraction of the
checkpoint cycle.  For this purpose, the buffers only need to be
sorted by physical file they are in, not by block order within the
file.

> However if does this way, might be it will violate Oracle Patent
> (20050044311 - Reducing disk IO by full-cache write-merging). I am not very
> sure about it. But you can refer it once.

Thank you.  I was not aware of it, and am constantly astonished what
kinds of things are patentable.

>>> I think the linked list is a bit of a red herring.  Many of the
>>> concepts people discuss implementing on the linked list could just as
>>> easily be implemented with the clock sweep.  And I've seen no evidence
>>> at all that the clock sweep is the problem.  The LWLock that protects
>>> can obviously be a problem, but that seems to be due to the overhead
>>> of acquiring a contended lock, not the work done under the lock.
>>> Reducing the lock-strength around this might be a good idea, but that
>>> reduction could be done just as easily (and as far as I can tell, more
>>> easily) with the clock sweep than the linked list.
>
> with clock-sweep, there are many chances that backend needs to traverse more
> to find a suitable buffer.

Maybe, but I have not seen any evidence that this is the case.  My
analyses, experiments, and simulations show that when the buffer
allocations are high, the mere act of running the sweep that often
keeps average useagecount low, so the average sweep is very short.

> However, if clean buffer is put in freelist, it can be directly picked from
> there.

Not directly, you have to take a lock.

Cheers,

Jeff

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

Re: [HACKERS] Initial 9.2 pgbench write results

[Jeff Janes]

On Tue, Feb 14, 2012 at 12:25 PM, Greg Smith  wrote:
> On 02/14/2012 01:45 PM, Greg Smith wrote:
>>
>> scale=1000, db is 94% of RAM; clients=4
>> Version TPS
>> 9.0  535
>> 9.1  491 (-8.4% relative to 9.0)
>> 9.2  338 (-31.2% relative to 9.1)
>
>
> A second pass through this data noted that the maximum number of buffers
> cleaned by the background writer is <=2785 in 9.0/9.1, while it goes as high
> as 17345 times in 9.2.

There is something strange about the data for Set 4 (9.1) at scale 1000.

The number of buf_alloc varies a lot from run to run in that series
(by a factor of 60 from max to min).

But the TPS doesn't vary by very much.

How can that be?  If a transaction needs a page that is not in the
cache, it needs to allocate a buffer.  So the only thing that could
lower the allocation would be a higher cache hit rate, right?  How
could there be so much variation in the cache hit rate from run to run
at the same scale?


Cheers,

Jeff

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

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 16:36, Thom Brown  wrote:
> On 25 February 2012 14:30, Thom Brown  wrote:
>> On 25 February 2012 13:28, Thom Brown  wrote:
>>> On 25 February 2012 13:15, Thom Brown  wrote:
 On 25 February 2012 12:42, Thom Brown  wrote:
> On 25 February 2012 12:07, Thom Brown  wrote:
>> On 25 February 2012 12:00, Dimitri Fontaine  
>> wrote:
>>
>> D'oh, just as I sent some more queries...
>>
>>> Thom Brown  writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show
>>>
>>> Any reason why?  I don't suppose it's really important one way or the
>>> other, so I'm waiting on some more voices before working on it.
>>
>> Just so it's easy to scan.  If someone is looking for CREATE CAST,
>> they'd kind of expect it near the drop of the CREATE list, but it's
>> actually toward the bottom.  It just looks random at the moment.
>>
 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?
>>>
>>> Well you can only alter a command that you were successful in creating,
>>> right?  So I'm not sure that's needed here.  By that count though, I
>>> maybe should remove the supported command list from DROP COMMAND TRIGGER
>>> reference page?
>>
>> Sure, that would be more consistent.  You're right, it's not needed.
>> It just seemed odd that one of the statements lacked what both others
>> had.
>
> Yet another comment... (I should have really started looking at this
> at an earlier stage)
>
> It seems that if one were to enforce a naming convention for relations
> as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
> circumvented by someone using CREATE TABLE name AS...
>
> test=# CREATE TABLE badname (id int, a int, b text);
> ERROR:  invalid relation name: badname
> test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
> SELECT 1
>
> This doesn't even get picked up by ANY COMMAND.

 CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
 expect ALTER COMMAND TRIGGER to output too for when individual
 commands are disabled etc.
>>>
>>> Just found another case where a table can be created without a command
>>> trigger firing:
>>>
>>> SELECT * INTO badname FROM goodname;
>>
>> Right, hopefully this should be my last piece of list spam for the
>> time being. (apologies, I thought I'd just try it out at first, but
>> it's ended up being reviewed piecemeal)
>
> I was wrong.. a couple of corrections to my own response:
>
>> On CREATE COMMAND TRIGGER page:
>>
>> “The trigger will be associated with the specified command and will
>> execute the specified function function_name when that command is
>> run.”
>> should be:
>> “The trigger will be associated with the specified commands and will
>> execute the specified function function_name when those commands are
>> run.”
>
> Actually, perhaps "...when any of those commands..."
>
>> On ALTER COMMAND TRIGGER page:
>>
>> “ALTER COMMAND TRIGGER name ON command SET enabled”
>> should be:
>> “ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”
>
> This one is nonsense, so please ignore it.

Further testing reveals a problem with FTS configurations when using
the example function provided in the docs:

test=# CREATE TEXT SEARCH CONFIGURATION test (
  PARSER = "default"
);
ERROR:  invalid relation name:
test=# CREATE TEXT SEARCH CONFIGURATION fr_test (
  PARSER = "default"
);
ERROR:  invalid relation name:

The 2nd one should work as it matches the naming convention checked in
the function.  The ALTER and DROP equivalents appear to be fine
though.

DROP CAST shares a similar issue too:

test=# DROP CAST (bigint as int4);
ERROR:  invalid relation name: �

The odd thing about this one is that CREATE CAST shouldn't match on
name at all, but it creates a cast successfully, whereas DROP CAST
disagrees with the name.

Command triggers for CREATE TYPE don't work, but fine for ALTER TYPE
and DROP TYPE.

Also command triggers for DROP CONVERSION aren't working.  A glance at
pg_cmdtrigger shows that the system views the command as "DROP
CONVERSION_P".

What is DROP ASSERTION?  It's showing as a valid command for a command
trigger, but it's not documented.

I've noticed that ALTER  name OWNER TO role doesn't result in
any trigger being fired except for tables.

ALTER OPERATOR FAMILY  RENAME TO ... doesn't fire command triggers.

ALTER OPERATOR CLASS with RENAME TO or OWNER TO doesn't fire command
triggers, but with SET SCHEMA it does.

And there's no command trigger available for ALTER VIEW.

I'll hold off on testing any further until a new patch is available.

Re: [HACKERS] xlog location arithmetic

[Euler Taveira de Oliveira]

On 25-02-2012 09:23, Magnus Hagander wrote:
> Do we even *need* the validate_xlog_location() function? If we just
> remove those calls, won't we still catch all the incorrectly formatted
> ones in the errors of the sscanf() calls? Or am I too deep into
> weekend-mode and missing something obvious?
> 
sscanf() is too fragile for input sanity check. Try
pg_xlog_location_diff('12/3', '-10/0'), for example. I won't object removing
that function if you protect xlog location input from silly users.


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

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

Re: [HACKERS] Memory usage during sorting

[Jeff Janes]

On Tue, Feb 14, 2012 at 1:44 AM, Hitoshi Harada  wrote:
> On Sat, Feb 11, 2012 at 11:34 AM, Jeff Janes  wrote:
>> On Wed, Feb 8, 2012 at 1:01 AM, Hitoshi Harada  wrote:
>>> On Sun, Jan 15, 2012 at 4:59 PM, Jeff Janes  wrote:

 The attached patch allows it to reuse that memory.  On my meager
 system it reduced the building of an index on an integer column in a
 skinny 200 million row totally randomly ordered table by about 3% from
 a baseline of 25 minutes.

>>>
>>> Just to give a standard review, this patch is one line change and
>>> applies cleanly, builds ok.
>>>
>>> I'm not pretty sure what exactly you're trying to accomplish, but it
>>> seems to me that it's avoiding the first dumptuples cycle by forcing
>>> availMem = 0 even if it's negative.
>>
>> Yes.  Currently when it switches to the TSS_BUILDRUNS part of a
>> tape-sort, it starts by calling WRITETUP a large number of time
>> consecutively, to work off the memory deficit incurred by the 3 blocks
>> per tape of tape overhead, and then after that calls WRITETUP about
>> once per puttuple..   Under my patch, it would only call WRITETUP
>> about once per puttuple, right from the beginning.
>>
>>> I read your comments as it'd be
>>> avoiding to alternate reading/writing back and force with scattered
>>> memory failing memory cache much during merge phase, but actually it
>>> doesn't affect merge phase but only init-dump phase, does it?
>>
>> It effects the building of the runs.  But this building of the runs is
>> not a simple dump, it is itself a mini merge phase, in which it merges
>> the existing in-memory priority queue against the still-incoming
>> tuples from the node which invoked the sort.  By using less memory
>> than it could, this means that the resulting runs are smaller than
>> they could be, and so will sometimes necessitate an additional layer
>> of merging later on.   (This effect is particularly large for the very
>> first run being built.  Generally by merging incoming tuples into the
>> memory-tuples, you can create runs that are 1.7 times the size of fits
>> in memory.  By wasting some memory, we are getting 1.7 the size of a
>> smaller starting point.  But for the first run, it is worse than that.
>>  Most of the benefit that leads to that 1.7 multiplier comes at the
>> very early stage of each run-build.  But by initially using the full
>> memory, then writing out a bunch of tuples without doing any merge of
>> the incoming, we have truncated the part that gives the most benefit.)
>>
>> My analysis that the "freed" memory is never reused (because we refuse
>> to reuse it ourselves and it is too fragmented to be reused by anyone
>> else, like the palloc or VM system) only applies to the run-building
>> phase.  So never was a bit of an overstatement.  By the time the last
>> initial run is completely written out to tape, the heap used for the
>> priority queue should be totally empty.  So at this point the
>> allocator would have the chance to congeal all of the fragmented
>> memory back into larger chunks, or maybe it parcels the allocations
>> back out again in an order so that the unused space is contiguous and
>> could be meaningfully paged out.
>>
>> But, it is it worth worrying about how much we fragment memory and if
>> we overshoot our promises by 10 or 20%?
>>
>>> If so,
>>> I'm not so convinced your benchmark gave 3 % gain by this change.
>>> Correct me as I'm probably wrong.
>>
>> I've now done more complete testing.  Building an index on an
>> 200,000,000 row table with an integer column populated in random order
>> with integers from 1..500,000,000, non-unique, on a machine with 2GB
>> of RAM and 600MB of shared_buffers.
>>
>> It improves things by 6-7 percent at the end of working mem size, the
>> rest are in the noise except at 77936 KB, where it reproducibly makes
>> things 4% worse, for reasons I haven't figured out.  So maybe the best
>> thing to do is, rather than micromanaging memory usage, simply don't
>> set maintenance_work_mem way to low.  (But, it is the default).
>
> I've tested here with only a million rows mix of integer/text (table
> size is 80MB or so) with default setting, running CREATE INDEX
> continuously, but couldn't find performance improvement.  The number
> varies from -2% to +2%, which I think is just error.
>
> While I agree with your insist that avoiding the first dump would make
> sense, I guess it depends on situations; if the dump goes with lots of
> tuples (which should happen when availMem is big), writing tuples a
> lot at a time will be faster than writing little by little later.
>
> I'm not sure about the conclusion, but given this discussion, I'm
> inclined to mark this Returned with Feedback.

OK, thanks.  Does anyone have additional feed-back on how tightly we
wish to manage memory usage?  Is trying to make us use as much memory
as we are allowed to without going over a worthwhile endeavor at all,
or is it just academic nitpicking?

Also, since the default 

Re: [HACKERS] FDW system columns

[Thom Brown]

On 14 November 2011 13:07, Thom Brown  wrote:
> 2011/11/14 Shigeru Hanada 
>>
>> (2011/11/14 11:25), Robert Haas wrote:
>> > My vote is to nuke 'em all.  :-)
>>
>> +1.
>>
>> IIRC, main purpose of supporting tableoid for foreign tables was to be
>> basis of foreign table inheritance, which was not included in 9.1, and
>> we have not supported it yet.  Other system columns are essentially
>> garbage, but they survived at 9.1 development because (maybe) it seemed
>> little odd to have system columns partially at that time.
>>
>> So, IMHO removing all system columns from foreign tables seems
>> reasonable, unless it doesn't break any external tool seriously (Perhaps
>> there would be few tools which assume that foreign tables have system
>> columns).
>>
>> If there seems to be a consensus on removing system column from foreign
>> tables, I'd like to work on this issue.  Attached is a halfway patch,
>> and ISTM there is no problem so far.
>
>
> I can say that at least PgAdmin doesn't use these columns.

So we still have all of these columns for foreign tables.  I've tested
Hanada-san's patch and it removes all of the system columns.  Could we
consider applying it, or has a use-case for them since been
discovered?

-- 
Thom

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

Re: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)

[Jeff Janes]

On Tue, Feb 21, 2012 at 5:34 PM, Fujii Masao  wrote:
> On Tue, Feb 21, 2012 at 8:19 PM, Fujii Masao  wrote:
>> On Sat, Feb 18, 2012 at 12:36 AM, Heikki Linnakangas
>>  wrote:
>>> Attached is a new version, fixing that, and off-by-one bug you pointed out
>>> in the slot wraparound handling. I also moved code around a bit, I think
>>> this new division of labor between the XLogInsert subroutines is more
>>> readable.
>
> When I ran the long-running performance test, I encountered the following
> panic error.
>
>    PANIC:  could not find WAL buffer for 0/FF00

I too see this panic when the system survives long enough to get to
that log switch.

But I'm also still seeing (with version 9) the assert failure at
"xlog.c", Line: 2154 during the end-of-recovery checkpoint.

Here is a set up for repeating my tests.  I used this test simply
because I had it sitting around after having written it for other
purposes.  Indeed I'm not all that sure I should publish it.
Hopefully other people will write other tests which exercise other
corner cases, rather than exercising the same ones I am.

The patch creates a guc which causes the md writer routine to panic
and bring down the database, triggering recovery, after a given number
for writes.  In this context probably any other method of forcing a
crash and recovery would be just as good as this specific method of
crashing.

The choice of 400 for the cutoff for crashing is based on:

1) If the number is too low, you re-crash within recovery so you never
get a chance to inspect the database.  In my hands, recovery doesn't
need to do more than 400 writes. (I don't know how to make the
database use different guc setting during recovery than it did before
the crash).

2) If the number is too high, it takes too long for a crash to happen
and I'm not all that patient.

Some of the changes to postgresql.conf.sample are purely my
preferences and have nothing in particular to do with this set up.
But archive_timeout = 30 is necessary in order to get checkpoints, and
thus mdwrites, to happen often enough to trigger crashes often enough
to satisfy my impatience.

The Perl script exercises the integrity of the database by launching
multiple processes (4 by default) to run updates and memorize what
updates they have run.  After a crash, the Perl processes all
communicate their data up to the parent, which consolidates that
information and then queries the post-recovery database to make sure
it agrees.  Transactions that are in-flight at the time of a crash are
indeterminate.  Maybe the crash happened before the commit, and maybe
it happened after the commit but before we received notification of
the commit.  So whichever way those turn out, it is not proof of
corruption.

With the xloginsert-scale-9.patch, the above features are not needed
because the problem is not that the database is incorrect after
recovery, but that the database doesn't recover in the first place. So
just running pgbench would be good enough to detect that.  But in
earlier versions this feature did detect incorrect recovery.

This logs an awful lot of stuff, most of which merely indicates normal
operation.  The problem is that corruption is rare, so if you wait
until you see corruption before turning on logging, then you have to
wait l long time to get another instance of corruption so you can
dissect the log information.  So, I just log everything all of the
time.
A warning from 'line 63' which is not marked as in-flight indicates
database corruption.  A warning from 'line 66' indicates even worse
corruption. A failure of the entire outer script to execute for the
expected number of iterations (i.e. failure of the warning issued on
'line 18' to show up 100 times) indicates the database failed to
restart.

Also attached is a bash script that exercises the whole thing.  Note
that it has various directories hard coded that really ought not be,
and that it has no compunctions about calling rm -r /tmp/data.  I run
it is as "./do.sh >& log" and then inspect the log file for unusual
lines.

To run this, you first have to apply your own xlog patch, and apply my
crash-inducing patch, and build and install the resulting pgsql.  And
edit the shell script to point to it, etc..  The whole thing is a bit
of a idiosyncratic mess.

Cheers,

Jeff


crash_REL9_2CF4.patch
Description: Binary data


count.pl
Description: Binary data


do.sh
Description: Bourne shell script

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

Re: [HACKERS] VACUUM ANALYZE is faster than ANALYZE?

[Cédric Villemain]

Le mercredi 22 février 2012 20:12:35, Pavel Stehule a écrit :
> 2012/2/22 Kevin Grittner :
> > Pavel Stehule  wrote:
> >> usual pattern in our application is
> >> 
> >> create table xx1 as select 
> >> analyze xx1
> >> create table xx2 as select  from xx1, 
> >> analyze xx2
> >> create table xx3 as select ... from xx3, 
> >> analyze xx3
> >> create table xx4 as select ... from xx1, ...
> >> 
> >> tables xx** are use as cache.
> >> 
> >> so we have to refresh statistic early.
> >> 
> >> in this situation - and I found so in this case VACUUM ANALYZE is
> >> faster (30%) than ANALYZE. Size of xx** is usually between 500Kb
> >> and 8Kb
> >> 
> >> This is not usual pattern for OLTP - Application is strictly OLAP.
> > 
> > Is the VACUUM ANALYZE step faster, or is the overall job faster if
> > VACUUM ANALYZE is run?  You may be running into the need to rewrite
> > pages at an inopportune time or order without the VACUUM.  Have you
> > tried getting a time VACUUM FREEZE ANALYZE on these cache tables
> > instead of plain VACUUM ANALYZE?
> > 
> > -Kevin
> 
> vacuum freeze analyze is slower as expected. vacuum analyze is little
> bit faster or same in any step then analyze.
> 
> I expected so just analyze should be significantly faster and it is not.
> 
> Tom's demonstration is enough for me. ANALYZE doesn't read complete
> table, but uses random IO. VACUUM ANALYZE reads complete table, but it
> uses seq IO and vacuum is fast (because it does nothing) in our case.

VACUUM does read the 1st block to be sure readahead is done when ANALYSE does 
not.
For ANALYZE, maybe it is interesting to issue a read on the first block or use 
POSIX_FADVISE to (try) to force a readahead of the table when it is small 
enough (so ANALYSE can start working while blocks are read and put in cache).

That's being said, I am surprised that the pattern "create table...analyze 
create table analyze" of such smalls ones make the data being flush from OS 
cache so quickly that they need to be read again from disk.
Pavel, can you check the cache status of the tables just before the analyze ? 
(you can use OS tools or pgfincore extension for that)

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

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

Re: [HACKERS] WIP: URI connection string support for libpq

[Cédric Villemain]

Le vendredi 24 février 2012 14:18:44, Florian Weimer a écrit :
> * Alex Shulgin:
> >> It's ugly, but it's standard practice, and seems better than a separate
> >> -d parameter (which sort of defeats the purpose of URIs).
> > 
> > Hm, do you see anything what's wrong with "?dbname=other" if you don't
> > like a separate -d?
> 
> It's not nice URI syntax, but it's better than an out-of-band mechanism.

I've not followed all the mails about this feature but I don't find it is a 
nice syntax too.

"?dbname=other" looks like dbname is an argument, but dbname is a requirement 
for postgresql connexion.


-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

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

Re: [HACKERS] CLOG contention, part 2

[Simon Riggs]

On Wed, Feb 8, 2012 at 11:26 PM, Robert Haas  wrote:

> Given that, I obviously cannot test this at this point,

Patch with minor corrections attached here for further review.

> but let me go
> ahead and theorize about how well it's likely to work.  What Tom
> suggested before (and after some reflection I think I believe it) is
> that the frequency of access will be highest for the newest CLOG page
> and then drop off for each page further back you go.  Clearly, if that
> drop-off is fast - e.g. each buffer further backward is half as likely
> to be accessed as the next newer one - then the fraction of accesses
> that will hit pages that are far enough back to benefit from this
> optimization will be infinitesmal; 1023 out of every 1024 accesses
> will hit the first ten pages, and on a high-velocity system those all
> figure to have been populated since the last checkpoint.

That's just making up numbers, so its not much help. The "theory"
would apply to one workload but not another, so may well be true for
some workload but I doubt whether all databases work that way. I ask
accept the "long tail" distribution as being very common, we just
don't know how long that tail is "typically" or even if there is a
dominant single use case.

> The best
> case for this patch should be an access pattern that involves a very
> long tail;

Agreed


> actually, pgbench is a pretty good fit for that

Completely disagree, as described in detail in the other patch about
creating a realistic test environment for this patch.

pgbench is *not* a real world test.

pgbench loads all the data in one go, then pretends the data got their
one transaction at a time. So pgbench with no mods is actually the
theoretically most unreal imaginable. You have to run pgbench for 1
million transactions before you even theoretically show any gain from
this patch, and it would need to be a long test indeed before the
averaged effect of the patch was large enough to avoid the zero
contribution from the first million transacts.

The only real world way to test this patch is to pre-create the
database using a scale factor of >100 using the modified pgbench, then
run a test. That correctly simulates the real world situation where
all data arrived in single transactions.


> assuming
> the scale factor is large enough.  For example, at scale factor 100,
> we've got 10,000,000 tuples: choosing one at random, we're almost
> exactly 90% likely to find one that hasn't been chosen in the last
> 1,024,576 tuples (i.e. 32 CLOG pages @ 32K txns/page).  In terms of
> reducing contention on the main CLOG SLRU, that sounds pretty
> promising, but depends somewhat on the rate at which transactions are
> processed relative to the frequency of checkpoints, since that will
> affect how many pages back you have go to use the history path.

> However, there is a potential fly in the ointment: in other cases in
> which we've reduced contention at the LWLock layer, we've ended up
> with very nasty contention at the spinlock layer that can sometimes
> eat more CPU time than the LWLock contention did.   In that light, it
> strikes me that it would be nice to be able to partition the
> contention N ways rather than just 2 ways.  I think we could do that
> as follows.  Instead of having one control lock per SLRU, have N
> locks, where N is probably a power of 2.  Divide the buffer pool for
> the SLRU N ways, and decree that each slice of the buffer pool is
> controlled by one of the N locks.  Route all requests for a page P to
> slice P mod N.  Unlike this approach, that wouldn't completely
> eliminate contention at the LWLock level, but it would reduce it
> proportional to the number of partitions, and it would reduce spinlock
> contention according to the number of partitions as well.  A down side
> is that you'll need more buffers to get the same hit rate, but this
> proposal has the same problem: it doubles the amount of memory
> allocated for CLOG.  Of course, this approach is all vaporware right
> now, so it's anybody's guess whether it would be better than this if
> we had code for it.  I'm just throwing it out there.

We've already discussed that and my patch for that has already been
rules out by us for this CF.

A much better take is to list what options for scaling we have:
* separate out the history
* partition access to the most active parts

For me, any loss of performance comes from two areas:
(1) concurrent access to pages
(2) clog LRU is dirty and delays reading in new pages

For the most active parts, (1) is significant. Using partitioning at
the page level will be ineffective in reducing contention because
almost all of the contention is on the first 1-2 pages. If we do
partitioning, it should be done by *striping* the most recent pages
across many locks, as I already suggested. Reducing page size would
reduce page contention but increase number of new page events and so
make (2) more important. Increasing page size will amplify (1).

(2) is less signif

Re: [HACKERS] COPY with hints, rebirth

[Simon Riggs]

On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner
 wrote:
> Simon Riggs  wrote:
>
>> This patch extends that and actually sets the tuple header flag as
>> HEAP_XMIN_COMMITTED during the load.
>
> Fantastic!
>
> So, without bulk-load conditions, a long-lived tuple in PostgreSQL
> is written to disk at least five times[1]:
>
> (1) The WAL record for the inserted tuple is written.
> (2) The inserted tuple is written.
> (3) The HEAP_XMIN_COMMITTED bit is set and the tuple is re-written
>    in place some time after the inserting transaction's COMMIT.
> (4) The WAL record for the "freeze" in write 5 is written.
> (5) The xmin is set to frozen and the tuple is rewritten in place
>    some time after every other connection can see it.
>
> Prior to your patch, bulk load omitted write 1.  With your patch we
> will also omit write 3.

Yes, well explained.

> Since you've just been looking at this area, do you have any
> thoughts about writes 4 and 5 being rendered unnecessary by writing
> bulk-loaded tuples with a frozen xmin, and having transactions with
> a snapshot which doesn't include the bulk load's transaction just
> not seeing the table?  (Or am I just dreaming about those?)

Setting straight to frozen breaks MVCC, unless/until we use MVCC for
catalog access because we can see the table immediately and then read
the contents as if they had always been there.

I think we could add that as an option on COPY, since "breaking MVCC"
in that way is only a bad thing if it happens accidentally without the
user's permission and knowledge - which they may wish to give in many
cases, such as reloading a database from a dump.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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

Re: [HACKERS] COPY with hints, rebirth

[Kevin Grittner]

Simon Riggs  wrote:
 
> This patch extends that and actually sets the tuple header flag as
> HEAP_XMIN_COMMITTED during the load.
 
Fantastic!
 
So, without bulk-load conditions, a long-lived tuple in PostgreSQL
is written to disk at least five times[1]:
 
(1) The WAL record for the inserted tuple is written.
(2) The inserted tuple is written.
(3) The HEAP_XMIN_COMMITTED bit is set and the tuple is re-written
in place some time after the inserting transaction's COMMIT.
(4) The WAL record for the "freeze" in write 5 is written.
(5) The xmin is set to frozen and the tuple is rewritten in place
some time after every other connection can see it.
 
Prior to your patch, bulk load omitted write 1.  With your patch we
will also omit write 3.
 
Since you've just been looking at this area, do you have any
thoughts about writes 4 and 5 being rendered unnecessary by writing
bulk-loaded tuples with a frozen xmin, and having transactions with
a snapshot which doesn't include the bulk load's transaction just
not seeing the table?  (Or am I just dreaming about those?)
 
-Kevin
 
[1] If you are archiving, it could be more.

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

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

[Kevin Grittner]

Vik Reykja  wrote:
> Kevin Grittner wrote:
> 
>> One of the problems that Florian was trying to address is that
>> people often have a need to enforce something with a lot of
>> similarity to a foreign key, but with more subtle logic than
>> declarative foreign keys support.  One example would be the case
>> Robert has used in some presentations, where the manager column
>> in each row in a project table must contain the id of a row in a
>> person table *which has the project_manager boolean column set to
>> TRUE*.  Short of using the new serializable transaction isolation
>> level in all related transactions, hand-coding enforcement of
>> this useful invariant through trigger code (or application code
>> enforced through some framework) is very tricky.  The change to
>> SELECT FOR UPDATE that Florian was working on would make it
>> pretty straightforward.
> 
> I'm not sure what Florian's patch does, but I've been trying to
> advocate syntax like the following for this exact scenario:
> 
> foreign key (manager_id, true) references person (id, is_manager)
> 
> Basically, allow us to use constants instead of field names as
> part of foreign keys.
 
Interesting.  IMV, a declarative approach like that is almost always
better than the alternatives, so something like this (possibly with
different syntax) would be another step in the right direction.  I
suspect that there will always be a few corner cases where the
business logic required is too esoteric to be handled by a
generalized declarative construct, so I think Florian's idea still
has merit -- especially if we want to ease the transition to
PostgreSQL for large shops using other products.
 
> I have no idea what the implementation aspect of this is,
> but I need the user aspect of it and don't know the best way to
> get it.
 
There are those in the community who make their livings by helping
people get the features they want.  If you have some money to fund
development, I would bet you could get this addressed -- it sure
sounds reasonable to me.
 
-Kevin

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

Re: [HACKERS] Fix PL/Python metadata when there is no result

[Jean-Baptiste Quenot]

2012/2/24 Peter Eisentraut :
> On fre, 2012-02-10 at 17:44 +0100, Jean-Baptiste Quenot wrote:
>>
>> Please find attached a patch that solves this issue.  Instead of a PG
>> crash, we get the following message:
>>
>> ERROR:  plpy.Error: no result fetched
>
> Hmm, should it be an error or just return None?  Python DB-API
> cursor.description returns None if no result set was returned.

IMO raising an error is much better because:

1) It is not a valid usecase to retrieve result metadata when no rows
are expected to be returned

2) The various metadata methods return a sequence.  Checking for null
value in this case is not a very good programming style.  I expect to
find an empty list when no data is available.

Cheers,
-- 
Jean-Baptiste Quenot

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

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 14:30, Thom Brown  wrote:
> On 25 February 2012 13:28, Thom Brown  wrote:
>> On 25 February 2012 13:15, Thom Brown  wrote:
>>> On 25 February 2012 12:42, Thom Brown  wrote:
 On 25 February 2012 12:07, Thom Brown  wrote:
> On 25 February 2012 12:00, Dimitri Fontaine  
> wrote:
>
> D'oh, just as I sent some more queries...
>
>> Thom Brown  writes:
>>> Is there any reason why the list of commands that command triggers can
>>> be used with isn't in alphabetical order?  Also it appears to show
>>
>> Any reason why?  I don't suppose it's really important one way or the
>> other, so I'm waiting on some more voices before working on it.
>
> Just so it's easy to scan.  If someone is looking for CREATE CAST,
> they'd kind of expect it near the drop of the CREATE list, but it's
> actually toward the bottom.  It just looks random at the moment.
>
>>> The ALTER COMMAND TRIGGER page also doesn't show which commands it can
>>> be used against.  Perhaps, rather than repeat the list, there could be
>>> a note to say that a list of valid commands can be found on the CREATE
>>> COMMAND TRIGGER page?
>>
>> Well you can only alter a command that you were successful in creating,
>> right?  So I'm not sure that's needed here.  By that count though, I
>> maybe should remove the supported command list from DROP COMMAND TRIGGER
>> reference page?
>
> Sure, that would be more consistent.  You're right, it's not needed.
> It just seemed odd that one of the statements lacked what both others
> had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.
>>>
>>> CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
>>> expect ALTER COMMAND TRIGGER to output too for when individual
>>> commands are disabled etc.
>>
>> Just found another case where a table can be created without a command
>> trigger firing:
>>
>> SELECT * INTO badname FROM goodname;
>
> Right, hopefully this should be my last piece of list spam for the
> time being. (apologies, I thought I'd just try it out at first, but
> it's ended up being reviewed piecemeal)

I was wrong.. a couple of corrections to my own response:

> On CREATE COMMAND TRIGGER page:
>
> “The trigger will be associated with the specified command and will
> execute the specified function function_name when that command is
> run.”
> should be:
> “The trigger will be associated with the specified commands and will
> execute the specified function function_name when those commands are
> run.”

Actually, perhaps "...when any of those commands..."

> On ALTER COMMAND TRIGGER page:
>
> “ALTER COMMAND TRIGGER name ON command SET enabled”
> should be:
> “ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”

This one is nonsense, so please ignore it.

-- 
Thom

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

Re: [HACKERS] pgstat documentation tables

[Magnus Hagander]

On Mon, Jan 16, 2012 at 02:03, Greg Smith  wrote:
> On 01/15/2012 12:20 PM, Tom Lane wrote:
>>
>> Please follow the style already used for system catalogs; ie I think
>> there should be a summary table with one entry per view, and then a
>> separate description and table-of-columns for each view.
>
>
> Yes, that's a perfect precedent.  I think the easiest path forward here is
> to tweak the updated pg_stat_activity documentation, since that's being
> refactoring first anyway.  That can be reformatted until it looks just like
> the system catalog documentation.  And then once that's done, the rest of
> them can be converted over to follow the same style.  I'd be willing to work
> on doing that in a way that improves what is documented, too.  The
> difficulty of working with the existing tables has been the deterrent for
> improving that section to me.

I've applied a patch that does this now. Hopefully, I didn't create
too many spelling errors or such :-)

I also applied a separate patch that folded the list of functions into
the list of views, since that's where they are called, as a way to
reduce duplicate documentation. I did it as a spearate patch to make
it easier to back out if people think that was a bad idea...

I didn't add any new documentation at this point - I wanted to get
this patch in before it starts conflicting with work others are
potentially doing. I also noticed there were some stats functions that
were undocumented - the view fields were listed, but not the functions
themselves. I haven't added those either...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 13:28, Thom Brown  wrote:
> On 25 February 2012 13:15, Thom Brown  wrote:
>> On 25 February 2012 12:42, Thom Brown  wrote:
>>> On 25 February 2012 12:07, Thom Brown  wrote:
 On 25 February 2012 12:00, Dimitri Fontaine  wrote:

 D'oh, just as I sent some more queries...

> Thom Brown  writes:
>> Is there any reason why the list of commands that command triggers can
>> be used with isn't in alphabetical order?  Also it appears to show
>
> Any reason why?  I don't suppose it's really important one way or the
> other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

>> The ALTER COMMAND TRIGGER page also doesn't show which commands it can
>> be used against.  Perhaps, rather than repeat the list, there could be
>> a note to say that a list of valid commands can be found on the CREATE
>> COMMAND TRIGGER page?
>
> Well you can only alter a command that you were successful in creating,
> right?  So I'm not sure that's needed here.  By that count though, I
> maybe should remove the supported command list from DROP COMMAND TRIGGER
> reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.
>>>
>>> Yet another comment... (I should have really started looking at this
>>> at an earlier stage)
>>>
>>> It seems that if one were to enforce a naming convention for relations
>>> as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
>>> circumvented by someone using CREATE TABLE name AS...
>>>
>>> test=# CREATE TABLE badname (id int, a int, b text);
>>> ERROR:  invalid relation name: badname
>>> test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
>>> SELECT 1
>>>
>>> This doesn't even get picked up by ANY COMMAND.
>>
>> CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
>> expect ALTER COMMAND TRIGGER to output too for when individual
>> commands are disabled etc.
>
> Just found another case where a table can be created without a command
> trigger firing:
>
> SELECT * INTO badname FROM goodname;

Right, hopefully this should be my last piece of list spam for the
time being. (apologies, I thought I'd just try it out at first, but
it's ended up being reviewed piecemeal)

On CREATE COMMAND TRIGGER page:

“The trigger will be associated with the specified command and will
execute the specified function function_name when that command is
run.”
should be:
“The trigger will be associated with the specified commands and will
execute the specified function function_name when those commands are
run.”

“A command trigger's function must return void, the only it can aborts
the execution of the command is by raising an exception.”
should be:
“A command trigger's function must return void.  It can then only
abort the execution of the command by raising an exception.”

Remove:
“For a constraint trigger, this is also the name to use when modifying
the trigger's behavior using SET CONSTRAINTS.”

Remove:
“That leaves out the following list of non supported commands.”

s/exercize/exercise/

“that's the case for VACUUM, CLUSTER CREATE INDEX CONCURRENTLY, and
REINDEX DATABASE.”
should be:
“that's the case for VACUUM, CLUSTER, CREATE INDEX CONCURRENTLY, and
REINDEX DATABASE.”

I don’t understand this sentence:
“Triggers on ANY command support more commands than just this list,
and will only provide the command tag argument as NOT NULL.”


On ALTER COMMAND TRIGGER page:

“ALTER COMMAND TRIGGER name ON command SET enabled”
should be:
“ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”


On DROP COMMAND TRIGGER page:

There’s a mention of CASCADE and RESTRICT.  I don’t know of any object
which could be dependant on a command trigger, so I don’t see what
these are for.


An oddity I’ve noticed is that you can add additional commands to an
existing command trigger, and you can also have them execute a
different function to the other commands referenced in the same
trigger.

-- 
Thom

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

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 13:15, Thom Brown  wrote:
> On 25 February 2012 12:42, Thom Brown  wrote:
>> On 25 February 2012 12:07, Thom Brown  wrote:
>>> On 25 February 2012 12:00, Dimitri Fontaine  wrote:
>>>
>>> D'oh, just as I sent some more queries...
>>>
 Thom Brown  writes:
> Is there any reason why the list of commands that command triggers can
> be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.
>>>
>>> Just so it's easy to scan.  If someone is looking for CREATE CAST,
>>> they'd kind of expect it near the drop of the CREATE list, but it's
>>> actually toward the bottom.  It just looks random at the moment.
>>>
> The ALTER COMMAND TRIGGER page also doesn't show which commands it can
> be used against.  Perhaps, rather than repeat the list, there could be
> a note to say that a list of valid commands can be found on the CREATE
> COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?
>>>
>>> Sure, that would be more consistent.  You're right, it's not needed.
>>> It just seemed odd that one of the statements lacked what both others
>>> had.
>>
>> Yet another comment... (I should have really started looking at this
>> at an earlier stage)
>>
>> It seems that if one were to enforce a naming convention for relations
>> as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
>> circumvented by someone using CREATE TABLE name AS...
>>
>> test=# CREATE TABLE badname (id int, a int, b text);
>> ERROR:  invalid relation name: badname
>> test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
>> SELECT 1
>>
>> This doesn't even get picked up by ANY COMMAND.
>
> CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
> expect ALTER COMMAND TRIGGER to output too for when individual
> commands are disabled etc.

Just found another case where a table can be created without a command
trigger firing:

SELECT * INTO badname FROM goodname;

-- 
Thom

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

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

[Christoph Berg]

Re: Peter Eisentraut 2012-02-24 <1330107599.32452.15.ca...@vanquo.pezone.net>
> On fre, 2012-02-24 at 11:53 -0500, Tom Lane wrote:
> > > We have the same problem with testing extensions at build-time in
> > the
> > > Debian packages. The server's SHAREDIR /usr/share/postgresql/... is
> > > only writable by root, while the build is running as buildd user, so
> > > there is no way to do "create extension whatimbuildingrightnow" to
> > be
> > > able to run regression tests, even if this is a cluster I have just
> > > created with initdb.
> > 
> > This seems like nonsense.  If the build process has installed the
> > software, you surely have got permissions to write in that directory.
> 
> The build process just installs the software in a fake root where it
> will be wrapped up by the packaging software.
> 
> > If you haven't installed the software, you need to do testing in a
> > temporary installation per "make check", and we are able to test
> > extensions that way too.
> > 
> That looks like the right answer.

Well, I'm trying to invoke the extension's "make check" target at
extension build time. I do have a temporary installation I own
somehwere in my $HOME, but that is still trying to find extensions in
/usr/share/postgresql/9.1/extension/*.control, because I am using the
system's postgresql version. The build process is not running as root,
so I cannot do an install of the extension to its final location.
Still it would be nice to run regression tests. All that seems to be
missing is the ability to put

extension_control_path = /home/buildd/tmp/extension

into the postgresql.conf of the temporary PG installation, or some
other way like "CREATE EXTENSION foobar WITH CONTROL '/home/buildd/...'.

Christoph
-- 
c...@df7cb.de | http://www.df7cb.de/


signature.asc
Description: Digital signature

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 12:42, Thom Brown  wrote:
> On 25 February 2012 12:07, Thom Brown  wrote:
>> On 25 February 2012 12:00, Dimitri Fontaine  wrote:
>>
>> D'oh, just as I sent some more queries...
>>
>>> Thom Brown  writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show
>>>
>>> Any reason why?  I don't suppose it's really important one way or the
>>> other, so I'm waiting on some more voices before working on it.
>>
>> Just so it's easy to scan.  If someone is looking for CREATE CAST,
>> they'd kind of expect it near the drop of the CREATE list, but it's
>> actually toward the bottom.  It just looks random at the moment.
>>
 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?
>>>
>>> Well you can only alter a command that you were successful in creating,
>>> right?  So I'm not sure that's needed here.  By that count though, I
>>> maybe should remove the supported command list from DROP COMMAND TRIGGER
>>> reference page?
>>
>> Sure, that would be more consistent.  You're right, it's not needed.
>> It just seemed odd that one of the statements lacked what both others
>> had.
>
> Yet another comment... (I should have really started looking at this
> at an earlier stage)
>
> It seems that if one were to enforce a naming convention for relations
> as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
> circumvented by someone using CREATE TABLE name AS...
>
> test=# CREATE TABLE badname (id int, a int, b text);
> ERROR:  invalid relation name: badname
> test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
> SELECT 1
>
> This doesn't even get picked up by ANY COMMAND.

CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
expect ALTER COMMAND TRIGGER to output too for when individual
commands are disabled etc.

-- 
Thom

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

Re: [HACKERS] Website stylesheet for local docs

[Pavel Stehule]

2012/2/25 Magnus Hagander :
> I've asked for this a few times before, but it seems others aren't as
> keen on it as me :-) Personally, I find the docs easier to read when
> formatted with the new website styles that Thom put together, and I
> also like to see things the way they're going to look when they go up
> there.
>
> Attached patch makes it possible to say "make STYLE=website" for the
> docs, which will then simply replace the stylesheet reference with one
> that goes to fetch docs.css on the website. I'm not suggesting we
> change the default or anything, just making it reasonably easy to get
> it done for one-off builds.
>
> I don't really speak the DSSSL naugage, so there might be a better way
> of doing it..

+1

Pavel

>
> Comments?
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

[HACKERS] Website stylesheet for local docs

[Magnus Hagander]

I've asked for this a few times before, but it seems others aren't as
keen on it as me :-) Personally, I find the docs easier to read when
formatted with the new website styles that Thom put together, and I
also like to see things the way they're going to look when they go up
there.

Attached patch makes it possible to say "make STYLE=website" for the
docs, which will then simply replace the stylesheet reference with one
that goes to fetch docs.css on the website. I'm not suggesting we
change the default or anything, just making it reasonably easy to get
it done for one-off builds.

I don't really speak the DSSSL naugage, so there might be a better way
of doing it..

Comments?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/Makefile b/doc/src/sgml/Makefile
index e6c8a49..19e640b 100644
--- a/doc/src/sgml/Makefile
+++ b/doc/src/sgml/Makefile
@@ -89,6 +89,9 @@ man-stamp: stylesheet-man.xsl postgres.xml
 .PHONY: draft
 
 JADE.html.call = $(JADE) $(JADEFLAGS) $(SPFLAGS) $(SGMLINCLUDE) $(CATALOG) -d stylesheet.dsl -t sgml -i output-html
+ifeq ($(STYLE),website)
+JADE.html.call += -V website-stylesheet
+endif
 
 # The draft target creates HTML output in draft mode, without index (for faster build).
 draft: postgres.sgml $(ALMOSTALLSGML) stylesheet.dsl
diff --git a/doc/src/sgml/stylesheet.dsl b/doc/src/sgml/stylesheet.dsl
index 232fa58..4179643 100644
--- a/doc/src/sgml/stylesheet.dsl
+++ b/doc/src/sgml/stylesheet.dsl
@@ -29,6 +29,7 @@
 
 
 (define draft-mode  #f)
+(define website-stylesheet  #f)
 
 (define pgsql-docs-list "pgsql-d...@postgresql.org")
 
@@ -190,7 +191,7 @@
 (define %root-filename% "index")
 (define %link-mailto-url%   (string-append "mailto:" pgsql-docs-list))
 (define %use-id-as-filename%#t)
-(define %stylesheet%"stylesheet.css")
+(define %stylesheet%(if website-stylesheet "http://www.postgresql.org/media/css/docs.css"; "stylesheet.css"))
 (define %graphic-default-extension% "gif")
 (define %gentext-nav-use-ff%#t)
 (define %body-attr% '())

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

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 12:07, Thom Brown  wrote:
> On 25 February 2012 12:00, Dimitri Fontaine  wrote:
>
> D'oh, just as I sent some more queries...
>
>> Thom Brown  writes:
>>> Is there any reason why the list of commands that command triggers can
>>> be used with isn't in alphabetical order?  Also it appears to show
>>
>> Any reason why?  I don't suppose it's really important one way or the
>> other, so I'm waiting on some more voices before working on it.
>
> Just so it's easy to scan.  If someone is looking for CREATE CAST,
> they'd kind of expect it near the drop of the CREATE list, but it's
> actually toward the bottom.  It just looks random at the moment.
>
>>> The ALTER COMMAND TRIGGER page also doesn't show which commands it can
>>> be used against.  Perhaps, rather than repeat the list, there could be
>>> a note to say that a list of valid commands can be found on the CREATE
>>> COMMAND TRIGGER page?
>>
>> Well you can only alter a command that you were successful in creating,
>> right?  So I'm not sure that's needed here.  By that count though, I
>> maybe should remove the supported command list from DROP COMMAND TRIGGER
>> reference page?
>
> Sure, that would be more consistent.  You're right, it's not needed.
> It just seemed odd that one of the statements lacked what both others
> had.

Yet another comment... (I should have really started looking at this
at an earlier stage)

It seems that if one were to enforce a naming convention for relations
as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
circumvented by someone using CREATE TABLE name AS...

test=# CREATE TABLE badname (id int, a int, b text);
ERROR:  invalid relation name: badname
test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
SELECT 1

This doesn't even get picked up by ANY COMMAND.

-- 
Thom

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

Re: [HACKERS] xlog location arithmetic

[Magnus Hagander]

On Fri, Feb 10, 2012 at 09:32, Fujii Masao  wrote:
> On Fri, Feb 10, 2012 at 7:00 AM, Euler Taveira de Oliveira
>  wrote:
>> On 08-02-2012 09:35, Fujii Masao wrote:
>>
>> Fujii, new patch attached. Thanks for your tests.
>
> Thanks for the new patch!
>
>>> But another problem happened. When I changed pg_proc.h so that the unused
>>> OID was assigned to pg_xlog_location_diff(), and executed the above again,
>>> I encountered the segmentation fault:
>>>
>> I reproduced the problems in my old 32-bit laptop. I fixed it casting to
>> int64. I also updated the duplicated OID.
>
> Yep, in the updated patch, I could confirm that the function works fine 
> without
> any error in my machine. The patch looks fine to me except the following minor
> comments:

I started working on this one to commit it, and came up with a few things more.

Do we even *need* the validate_xlog_location() function? If we just
remove those calls, won't we still catch all the incorrectly formatted
ones in the errors of the sscanf() calls? Or am I too deep into
weekend-mode and missing something obvious?

I've also removed tabs in the documentation, fixed the merge confllict
in pg_proc.h that happened during the wait, and fixed some indentation
(updated patch with these changes attached).

But I'm going to hold off committing it until someone confirms I'm not
caught too deeply in weekend-mode and am missing something obvious in
the comment above about validate_xlog_location.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e8e637b..4ae76e2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14454,11 +14454,15 @@ SELECT set_config('log_statement_stats', 'off', false);

 pg_xlogfile_name_offset

+   
+pg_xlog_location_diff
+   
 

 The functions shown in  assist in making on-line backups.
-These functions cannot be executed during recovery.
+These functions cannot be executed during recovery (except
+pg_xlog_location_diff).

 

@@ -14526,6 +14530,13 @@ SELECT set_config('log_statement_stats', 'off', false);
text, integer
Convert transaction log location string to file name and decimal byte offset within file
   
+  
+   
+pg_xlog_location_diff(location text, location text)
+   
+   numeric
+   Calculate the difference between two transaction log locations
+  
  
 

@@ -14619,6 +14630,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());

 

+pg_xlog_location_diff calculates the difference in bytes
+between two transaction log locations. It can be used with
+pg_stat_replication or some functions shown in
+ to get the replication lag.
+   
+
+   
 For details about proper usage of these functions, see
 .

diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 2e10d4d..b8f8152 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -26,6 +26,7 @@
 #include "replication/walreceiver.h"
 #include "storage/smgr.h"
 #include "utils/builtins.h"
+#include "utils/numeric.h"
 #include "utils/guc.h"
 #include "utils/timestamp.h"
 
@@ -465,3 +466,87 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_BOOL(RecoveryInProgress());
 }
+
+static void
+validate_xlog_location(char *str)
+{
+#define MAXLSNCOMPONENT		8
+
+	int			len1,
+len2;
+
+	len1 = strspn(str, "0123456789abcdefABCDEF");
+	if (len1 < 1 || len1 > MAXLSNCOMPONENT || str[len1] != '/')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for transaction log location: \"%s\"", str)));
+
+	len2 = strspn(str + len1 + 1, "0123456789abcdefABCDEF");
+	if (len2 < 1 || len2 > MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for transaction log location: \"%s\"", str)));
+}
+
+/*
+ * Compute the difference in bytes between two WAL locations.
+ */
+Datum
+pg_xlog_location_diff(PG_FUNCTION_ARGS)
+{
+	text	   *location1 = PG_GETARG_TEXT_P(0);
+	text	   *location2 = PG_GETARG_TEXT_P(1);
+	char	   *str1,
+			   *str2;
+	XLogRecPtr	loc1,
+loc2;
+	Numeric		result;
+
+	/*
+	 * Read and parse input
+	 */
+	str1 = text_to_cstring(location1);
+	str2 = text_to_cstring(location2);
+
+	validate_xlog_location(str1);
+	validate_xlog_location(str2);
+
+	if (sscanf(str1, "%X/%X", &loc1.xlogid, &loc1.xrecoff) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+		   errmsg("could not parse transaction log location \"%s\"", str1)));
+	if (sscanf(str2, "%X/%X", &loc2.xlogid, &loc2.xrecoff) != 2)
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+		   errmsg("could not parse transaction log location \"%s\"", str2)));
+
+	/*
+	 * Sanity check
+	 */
+	if (loc1.xre

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 25 February 2012 12:00, Dimitri Fontaine  wrote:

D'oh, just as I sent some more queries...

> Thom Brown  writes:
>> Is there any reason why the list of commands that command triggers can
>> be used with isn't in alphabetical order?  Also it appears to show
>
> Any reason why?  I don't suppose it's really important one way or the
> other, so I'm waiting on some more voices before working on it.

Just so it's easy to scan.  If someone is looking for CREATE CAST,
they'd kind of expect it near the drop of the CREATE list, but it's
actually toward the bottom.  It just looks random at the moment.

>> The ALTER COMMAND TRIGGER page also doesn't show which commands it can
>> be used against.  Perhaps, rather than repeat the list, there could be
>> a note to say that a list of valid commands can be found on the CREATE
>> COMMAND TRIGGER page?
>
> Well you can only alter a command that you were successful in creating,
> right?  So I'm not sure that's needed here.  By that count though, I
> maybe should remove the supported command list from DROP COMMAND TRIGGER
> reference page?

Sure, that would be more consistent.  You're right, it's not needed.
It just seemed odd that one of the statements lacked what both others
had.

Thanks

-- 
Thom

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

Re: [HACKERS] Command Triggers, patch v11

[Thom Brown]

On 24 February 2012 23:43, Thom Brown  wrote:
> On 24 February 2012 23:01, Thom Brown  wrote:
>> On 24 February 2012 22:39, Thom Brown  wrote:
>>> On 24 February 2012 22:32, Thom Brown  wrote:
 On 24 February 2012 22:04, Dimitri Fontaine  wrote:
> Hi,
>
> Please find attached the latest version of the command triggers patch,
> in context diff format, with support for 79 commands and documentation
> about why only those, and with some limitations explained.
>
> I also cleaned up the node function support business that was still in
> there from the command rewriting stuff that we dropped, and did a merge
> from tonight's master branch (one of a few clean merges).
>
> This is now the whole of it, and I continue being available to make any
> necessary change, although I expect only minor changes now.  Thanks to
> all reviewers and participants into the previous threads, you all have
> allowed me to reach the current point by your precious advice, comments
> and interest.
>
> The patch implements :
>
>  - BEFORE/AFTER ANY command triggers
>  - BEFORE/AFTER command triggers for 79 documented commands
>  - regression tests exercised by the serial schedule only
>  - documentation updates with examples
>
> That means you need to `make installcheck` here. Installing the tests in
> the parallel schedule does not lead to consistent output as installing a
> command trigger will impact any other test using that command, and the
> output becomes subject to the exact ordering of the concurrent tests.
>
> The only way for a BEFORE command triggers to change the command's
> behaviour is by raising an exception that aborts the whole transaction.
>
> Command triggers are called with the following arguments:
>
>  - the “event” (similar to TG_WHEN, either 'BEFORE' or 'AFTER')
>  - the command tag (the real one even when an ANY trigger is called)
>  - the object Id if available (e.g. NULL for a CREATE statement)
>  - the schema name (can be NULL)
>  - the object name (can be NULL)
>
> When the trigger's procedure we're calling is written in C, then another
> argument is passed next, which is the parse tree Node * pointer.
>
> I've been talking with Marko Kreen about supporting ALTER TABLE and such
> commands automatically in Londiste: given that patch, it requires
> writing code in C that will rewrite the command string.  It so happens
> that I already have worked on that code, so we intend on bringing
> support for ALTER TABLE and other commands in Skytools 3 for 9.2.
>
> I think the support code should be made into an extension that both
> Skytools and Slony would be able to share. The extension code will be
> able to adapt to major versions changes as they are released.  Bucardo
> would certainly be interested too, we could NOTIFY it from such an
> extension.  The design is yet to be done here, but it's clearly possible
> to implement such a feature given the current patch.
>
> The ANY trigger support is mainly there to allow people to stop any DDL
> traffic on their databases, then allowing it explicitly with an ALTER
> COMMAND TRIGGER ... SET DISABLE when appropriate only.  To that
> end, the ANY command trigger is supporting more commands than you can
> attach specific triggers too.
>
> It's also possible to ENABLE a command trigger on the REPLICA only
> thanks to the session_replication_role GUC.  Support for command
> triggers on an Hot Standby node is not provided in this patch.

 Hi Dimitri,

 I just tried building the docs with your patch and it appears
 doc/src/sgml/ref/allfiles.sgml hasn't been updated with the necessary
 references for alterCommandTrigger, createCommandTrigger and
 dropCommandTrigger.

 Also in ref/alter_command_trigger.sgml, you define SQL-CREATETRIGGER.
 Shouldn't this be SQL-CREATECOMMANDTRIGGER?  And there also appears to
 be orphaned text in the file too, such as "Forbids the execution of
 any DDL command".  And there's a stray  on line 299.

 I attach updated versions of both of those files, which seems to fix
 all these problems.
>>>
>>> I've just noticed there's an issue with
>>> doc/src/sgml/ref/alter_command_trigger.sgml too.  It uses >> zone="sql-altertrigger"> which should be sql-altercommandtrigger. (as
>>> attached)
>>
>> And upon trying to test the actual feature, it didn't work for me at
>> all.  I thought I had applied the patch incorrectly, but I hadn't, it
>> was the documentation showing the wrong information.  The CREATE
>> COMMAND TRIGGER page actually just says CREATE TRIGGER BEFORE
>> COMMAND , which isn't the correct syntax.
>>
>> Also the examples on the page are incorrect in the same regard.  When
>> I tested it with the correction, I got an error saying

Re: [HACKERS] Checking pg_hba.conf in the child process

[Magnus Hagander]

On Sat, Feb 25, 2012 at 00:45, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> Excerpts from Bruce Momjian's message of vie feb 24 19:19:10 -0300 2012:
>>> In looking over our authentication code, I noticed that we create the
>>> child process before we check any of the pg_hba.conf file.  Now, I
>>> realize we can't do authentication in the postmaster because of possible
>>> delay, and checking the user name and database name filters is just work
>>> that is better done in the child, but checking the IP address might
>>> prevent unauthorized clients from causing excessive process creation on
>>> the server.  I know we have listen_addresses, but that defaults to "*"
>>> on the click-through installers, and not everybody knows how to set up a
>>> firewall.
>
>> Hm, one thing to keep in mind is that we allow hostnames there.  It'd be
>> a pain to have postmaster hang while resolving names.
>
> Yes.  This cure would be a lot worse than the disease.  Bruce ought to
> remember that we intentionally moved all that logic *out* of the
> postmaster process, years ago, precisely because it was too hard to
> ensure that the postmaster wouldn't block and thus create DOS conditions
> of another sort.

As long as the block would only look at the IP it would also be
trivial - and more efficient - to do the same blocking in the
firewall, either local host firewall rules or the network firewall
depending on deployment...

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

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

Re: [HACKERS] Patch for BUG #6480, psql incorrect indent for inherited tables names with UTF-8 NLS

[Sergey Burladyan]

Alvaro Herrera  writes:

> I'm sorry, but the releases are already tagged :-(  So they will contain
> the buggy output for a while yet.

Ah, I see, ok, wait next! :)

-- 
Sergey Burladyan

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