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

2012-02-27 Thread Simon Riggs
On Tue, Feb 28, 2012 at 12:28 AM, Noah Misch  wrote:
> On Mon, Feb 27, 2012 at 02:13:32PM +0200, Heikki Linnakangas wrote:
>> On 23.02.2012 18:01, Alvaro Herrera wrote:
>>> As far as complexity, yeah, it's a lot more complex now -- no question
>>> about that.
>>
>> How about assigning a new, real, transaction id, to represent the group
>> of transaction ids. The new transaction id would be treated as a
>> subtransaction of the updater, and the xids of the lockers would be
>> stored in the multixact-members slru. That way the multixact structures
>> wouldn't need to survive a crash; you don't care about the shared
>> lockers after a crash, and the xid of the updater would be safely stored
>> as is in the xmax field.
>>
>> That way you wouldn't need to handle multixact wraparound, because we
>> already handle xid wraparound, and you wouldn't need to make multixact
>> slrus crash-safe.
>>
>> Not sure what the performance implications would be. You would use up
>> xids more quickly, which would require more frequent anti-wraparound
>> vacuuming. And if we just start using real xids as the key to
>> multixact-offsets slru, we would need to extend that a lot more often.
>> But I feel it would probably be acceptable.
>
> When a key locker arrives after the updater and creates this implicit
> subtransaction of the updater, how might you arrange for the xid's clog status
> to eventually get updated in accordance with the updater's outcome?

Somewhat off-topic, but just seen another bad case of FK lock contention.

Thanks for working on this everybody.

-- 
 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] swapcache-style cache?

2012-02-27 Thread Andrea Suisani

On 02/28/2012 04:52 AM, Rob Wultsch wrote:

On Wed, Feb 22, 2012 at 2:31 PM, james  wrote:

Has anyone considered managing a system like the DragonFLY swapcache for a
DBMS like PostgreSQL?



https://www.facebook.com/note.php?note_id=388112370932



in the same vein:

http://bcache.evilpiepirate.org/

from the main page:

"Bcache is a patch for the Linux kernel to use SSDs to cache other block 
devices. It's analogous to L2Arc for ZFS,
but Bcache also does writeback caching, and it's filesystem agnostic. It's 
designed to be switched on with a minimum
of effort, and to work well without configuration on any setup. By default it 
won't cache sequential IO, just the random
reads and writes that SSDs excel at. It's meant to be suitable for desktops, 
servers, high end storage arrays, and perhaps
even embedded."

it was submitted to linux kernel mailing list a bunch of time, the last one:

https://lkml.org/lkml/2011/9/10/13


Andrea

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


Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2012-02-27 Thread Shigeru Hanada
(2012/02/25 7:31), Peter Eisentraut wrote:
> Could we name this "postgresql_fdw" instead?  We already have several
> ${productname}_fdw out there, and I don't want to get in the business of
> having to guess variant spellings.

I worry name conflict with existing postgresql_fdw_validator, which is
implemented in backend binary and used by contrib/dblink.  I thought
that we should use another name for PostgreSQL FDW unless we can change
specification of dblink connection string.

-- 
Shigeru Hanada

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


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

2012-02-27 Thread Shigeru Hanada
2012/2/28 Tatsuo Ishii :
> Are you suggesting log_statement? I don't think it's a solution by
> following reasons:
>
> 1) it's slow to enable that on busy systems
> 2) tables affected by cascading delete/update/drop is not logged in
>   PostgreSQL log

What about reading archived WAL files?  They would contain every
delete/update/drop including cascading ones, though it might be too late
because WAL files are not available until archived.

xlogdump would help reading WAL files.

https://github.com/snaga/xlogdump

-- 
Shigeru Hanada


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

2012-02-27 Thread Fujii Masao
On Sun, Feb 26, 2012 at 8:53 AM, Euler Taveira de Oliveira
 wrote:
> 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.

After this patch will have been committed, it would be better to change
pg_xlogfile_name() and pg_xlogfile_name_offset() so that they use
the validate_xlog_location() function to validate the input.

Regards,

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

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


Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Ants Aasma
On Feb 27, 2012 10:36 PM, "Greg Smith"  wrote:
> One of the reasons I drilled right into this spot is because of fears
that running the writer more often would sprout regressions in TPS.  I
can't explain exactly why exactly having backends write their own buffers
out at the latest possible moment works significantly better in some cases
here.  But that fact isn't new to 9.2; it's just has a slightly higher
potential to get in the way, now that the writing happens during the sync
phase.

My hypothesis for the TPS regression is that it is due to write combining.
When the workload is mainly bound by I/O, every little bit that can be
saved helps the bottomline. Larger scalefactors don't get the benefit
because there is less write combining going on overall.

Anyway, most people don't run their databases at 100% load. At lesser loads
bgwriter should help end user latency. Is there a standard benchmark to
measure that?

--
Ants Aasma


Re: [HACKERS] Checkpointer vs pg_stat_bgwriter

2012-02-27 Thread Fujii Masao
On Mon, Feb 27, 2012 at 4:24 AM, Magnus Hagander  wrote:
> Hi!
>
> I admit to not having actually tested this since I don't have a good
> cluster to test it on right now, but from what I can tell the code in
> the new checkpointer process only sends statistics to the collector
> once the checkpoint is finished (checkpointer.c, line 549). The 9.1
> and earlier sent this every time they entered a delay state (in
> BgWriterNap() called from CheckpointWriteDelay()).
>
> So in 9.1 and earlier we could see how a checkpoint wrote things as it
> was running, but in 9.2 we'll get it all as one big block at the end
> of the checkpoint - which can be a lot later in the spread case.
>
> Am I reading the code right?
>
> And if so, was this an intentional change, and if so why? To me it
> seems like a loss of functionality that should be fixed..

I agree that this should be fixed.

Regards,

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

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


[HACKERS] misleading error message from connectMaintenanceDatabase()

2012-02-27 Thread Josh Kupershmidt
I noticed a misleading error message recently while using createdb. Try:

test=# CREATE ROLE dummy NOLOGIN;

Now, attempt to use createdb as that role. Here's 9.1.1:

$ createdb -Udummy testdb
createdb: could not connect to database postgres: FATAL:  role "dummy"
is not permitted to log in

And here is git head:

$ createdb -Udummy testdb
createdb: could not connect to databases "postgres" or "template1"
Please specify an alternative maintenance database.
Try "createdb --help" for more information.

Although I guess you could argue the latter message is technically
correct, since "could not connect" is true, the first error message
seems much more helpful. Plus, "Please specify an alternative
maintenance database" is a rather misleading hint to be giving in this
situation.

This seems to be happening because connectMaintenanceDatabase() is
passing fail_ok = true to connectDatabase(), which in turn just
returns NULL and doesn't print a PQerrorMessage() for the failed conn.
So connectMaintenanceDatabase() has no idea why the connection really
failed.

A simple fix would be just to pass fail_ok = false for the last
connectDatabase() call inside connectMaintenanceDatabase(), and give
up on trying to tack on a likely-misleading hint about the maintenance
database. Patch attached. This leads to:

$ createdb -Udummy testdb
createdb: could not connect to database template1: FATAL:  role
"dummy" is not permitted to log in

which is almost the same as the 9.1.1 output, with the exception that
"template1" is mentioned by default instead of the "postgres"
database.

Josh


connectMDB_error.diff
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] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Pavel Stehule
2012/2/28 Tom Lane :
> Christopher Browne  writes:
>> On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane  wrote:
>>> So, whatever the desirability of having them run as table owner,
>>> we can't just up and change that.
>
>> I'm inclined to hold to the argument that it Works Properly Now, and
>> that we shouldn't break it by changing it.
>
> I would say the same, or at least that any argument for changing it is
> probably not strong enough to trump backwards compatibility.
>

+1

> However, Peter seems to think the other way is required by standard.
> We can get away with defining whatever behavior we want for triggers
> that invoke functions, since that syntax is nonstandard anyway.  But,
> if you remember the original point of this thread, it was to add syntax
> that is pretty nearly equivalent to the spec's.  If we're going to do
> that, it had better also have semantics similar to the spec's.
>
> So (assuming Peter has read the spec correctly) I'm coming around to the
> idea that the anonymous trigger functions created by this syntax ought
> to be "SECURITY DEFINER table_owner".
>

It should be strange if using two forms of one code can have two
relative different behave.

Actually we are in opposition to spec, because it expect SECURITY
DEFINER for all stored procedures.  All logic about rights are
consistent now and I am not for changes in this area.

Regards

Pavel

>                        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

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


Re: [HACKERS] psql filename completion: quoting

2012-02-27 Thread Alvaro Herrera

Excerpts from Noah Misch's message of sáb ene 14 12:20:18 -0300 2012:
> Occasionally, I have a SQL file destined for psql's \i command whose name
> contains a space.  Less often, I'll have a .csv destined for \copy with the
> same problem.  psql's filename completion does not handle these well.  It
> completes on the literal name, but the commands will only recognize quoted
> names.  For example, given a file "foo bar", "\i f" will complete to "\i
> foo bar", which will not execute.  If I type "\i 'f", completion will not
> help at all.
> 
> The attached patch wraps rl_filename_completion_function() to dequote on input
> and enquote on output.  Now, "\i f" and "\i 'f" will both complete
> to "\i 'foo bar'", which executes as expected.  The new code handles embedded
> whitespace, quotes, and backslashes.

Nice -- thanks, pushed.

> tab-complete.c works in terms of whitespace-separated words.  As such, "\i
> 'foo b" does not complete, because tab-complete.c has no notion of quotes
> affecting token boundaries.  It thinks "'foo" is one token and "b" is another.

Yeah, it's a bit annoying if you have both "a b" and "a c".  If you have
a file named a\b, attempting to complete past a\\ doesn't work either.
(If you have 'a\\ it does work, however).

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

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


Re: [HACKERS] swapcache-style cache?

2012-02-27 Thread Rob Wultsch
On Wed, Feb 22, 2012 at 2:31 PM, james  wrote:
> Has anyone considered managing a system like the DragonFLY swapcache for a
> DBMS like PostgreSQL?
>

https://www.facebook.com/note.php?note_id=388112370932

-- 
Rob Wultsch
wult...@gmail.com

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


Re: [HACKERS] swapcache-style cache?

2012-02-27 Thread Greg Smith

On 02/27/2012 03:24 PM, Jan Lentfer wrote:

And, yes.. it does effect pgsql performance on read loads seriously.

See BSD Mag 5/2011
http://bsdmag.org/magazine/1691-embedded-bsd-freebsd-alix

and
http://www.shiningsilence.com/dbsdlog/2011/04/12/7586.html


Caching on the read-only pgbench is a well defined workload at this 
point.  If your database fits in RAM, once it's all in there additional 
caching doesn't help.  If the database is much larger than the cache, 
the cache barely helps there too; you'll still be facing mostly cache 
misses.  The case in the middle is the one where an additional layer of 
cache really helps.  Read-heavy systems where the working set of the 
database is larger than RAM, but not significantly larger than the extra 
cache, will benefit the most here.


Your test results are in that zone, with 2GB RAM < 5.6GB database < 16GB 
cache.  Having a database slightly larger than physical RAM is where the 
big win with SSD normally shows up at.  Moving the whole database from a 
regular drive to SSD might get as much as a 5X speedup, you're seeing a 
3X to 4X one with the swap cache in the middle.


Having the OS manage all that, to keep the most relevant data on the 
SSD, is a cool feature.  Some systems won't benefit at all though, and 
your test is showing near the best case possible for this feature.  As 
you should, of course.


Anyway, the question upthread was whether the database should manage 
something like this on its own.  I suggested it could be done perfectly 
fine by the OS, without any database knowledge of what is going on. 
Your results seem to validate that idea.


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

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


[HACKERS] Hot Standby Failover Scenario

2012-02-27 Thread Lucky Haryadi
Hi everybody.

I want to ask about hot-standby related issues. First of all, maybe I will
describe my scenario of Postgres master-slave.

1. There are Master A and Slave B in different location, assumed different
region of nation.
2. Configuring Master A and Slave B to become hot-standby is same as
described in documentations.
3. When Master A fails to service, the database will failovered to Slave B
by triggering with trigger file.
4. As soon as Slave B become standalone pg server, run pg_start_backup(),
so that all transactions will only be recorded to WAL files.
5. Applications swinged to Standalone B, until Server A recovery is done.
6. When Server A has recovered (but still offline), run pg_stop_backup()
and copy all WAL files from B to A.
7. Once the WAL files copied to A, set A's configuration back to Master and
B to Slave again (for B, change recovery.done to recovery.conf and remove
the trigger file).
8. Bring up A, restart B and all applications will be swinged back to A.

I've tried these methods with no luck. Before A fails to service, condition
is A has 10 million records, and B has 10 million records too. Then I
failovered to B, manually, simulating that A failed to service. I run
pg_start_backup() and inserting bunch of data, let say the current
condition is A still 10 million, B 20 million. So I tried to copy WAL files
from B to A and hope that when A up again, the records will intact to B, A
20 million and B 20 million and hot-standby streaming will run as well. But
my experiments failed to do so.
I've checked the log and found that the timeline is invalid. On Slave B's
log, it appeared that timeline of primary server (Master A) does not match
target timeline of standby server. Can anyone suggest for this case? Any
suggestions will be greatly appreciated. Thank you.


Re: [HACKERS] Speed dblink using alternate libpq tuple storage

2012-02-27 Thread Kyotaro HORIGUCHI
Hello.

I will show you fixed version patch later, please wait for a
while.

==
> It's more important to not destabilize V3 code.

Ok, I withdraw that agreeing with that point. And I noticed that
the proposal before is totally a crap becuase I have mixed up
asyncStatus with resultStatus in it.

> And error from row processor is not something special from
> other errors.  Why does it need special state?

I'm sorry to have upset the discussion. What I wanted there is a
means other than exceptions to exit out of PQexec() by row
processor trigger without discarding the result built halfway,
like async.

> I just asked you to replace ->rowProcessorErrMsg with ->errMsg
> to get rid of unnecessary field.

Ok, I will remove extra code.

> Also, with the PQgetRow() patch, the need for doing complex processing
> under callback has decreased and the need to set error outside callback
> has increased.
> 
> As a bonus, such generic error-setting function would lose any extra
> special state introduced by row-processor patch.

That sounds nice. I will show you the patch without it for the
present, then try to include.

> Previously I mentioned that callback would need to have additional
> PGconn* argument to make connection available to callback to use such
> generic error setting function, but now I think it does not need it -
> simple callbacks don't need to set errors and complex callback can make
> the PGconn available via Param.  Eg. the internal callback should set
> Param to PGconn, instead keeping NULL there.

I agree with it.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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


Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Alvaro Herrera

Excerpts from Tom Lane's message of lun feb 27 20:49:36 -0300 2012:

> So (assuming Peter has read the spec correctly) I'm coming around to the
> idea that the anonymous trigger functions created by this syntax ought
> to be "SECURITY DEFINER table_owner".

I don't remember all the details, but I had a look at this in the
standard about a year ago and the behavior it mandated wasn't trivially
implemented using our existing mechanism.  I mentioned the issue of a
stack of user authorizations that is set up whenever a "routine"
(function) is entered, during last year's PGCon developer's meeting.  I
intended to have a look at implementing that, but I haven't done
anything yet.  What was clear to me was that once I explained the
problem, everyone seemed to agree that fixing it required more than some
trivial syntax rework.

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

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


Re: [HACKERS] Command Triggers

2012-02-27 Thread Alvaro Herrera

Excerpts from Tom Lane's message of lun feb 27 20:54:41 -0300 2012:
> Andres Freund  writes:
> > On Tuesday, February 28, 2012 12:30:36 AM Tom Lane wrote:
> >> Ugh ... if that's currently allowed, we definitely need to fix it.
> 
> > Btw, whats the danger youre seing?
> 
> Well, I'm not sure that it would actively break anything, but we
> definitely meant to disallow the case.  Also, I seem to recall some
> places that intuit a relation's shared marker in the opposite direction
> (if it's in pg_global it must be shared), and that could definitely
> cause issues if we treat a rel as shared when it isn't.

The list of shared rels is hardcoded -- see IsSharedRelation.

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

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread anara...@anarazel.de


Tom Lane  schrieb:

>Andres Freund  writes:
>> I refreshed the patch so it works again on current HEAD. Basically
>some 
>> trivial fixes and dfd26f9c5f371437f243249025863ea9911aacaa. The
>latter doesn't 
>> seem necessary to me after the changes, so I simply ditched it. Am I
>missing 
>> something?
>
>No, that was only needed because execMain.c was overriding somebody
>else's tuple receiver.  If you're putting the right receiver into the
>QueryDesc to start with, it shouldn't be necessary.
>
>I'm confused by this though:
>
>> This basically includes a revert of
>d8fb1f9adbddd1eef123d66a89a9fc0ecd96f60b
>
>I don't find that commit ID anywhere.
That should have been the aforementioned commit. Must have screwed up the 
copy&paste buffer. Sorry for that.

Andres

Please excuse the brevity and formatting - I am writing this on my mobile phone.

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

2012-02-27 Thread Noah Misch
On Mon, Feb 27, 2012 at 02:13:32PM +0200, Heikki Linnakangas wrote:
> On 23.02.2012 18:01, Alvaro Herrera wrote:
>> As far as complexity, yeah, it's a lot more complex now -- no question
>> about that.
>
> How about assigning a new, real, transaction id, to represent the group  
> of transaction ids. The new transaction id would be treated as a  
> subtransaction of the updater, and the xids of the lockers would be  
> stored in the multixact-members slru. That way the multixact structures  
> wouldn't need to survive a crash; you don't care about the shared  
> lockers after a crash, and the xid of the updater would be safely stored  
> as is in the xmax field.
>
> That way you wouldn't need to handle multixact wraparound, because we  
> already handle xid wraparound, and you wouldn't need to make multixact  
> slrus crash-safe.
>
> Not sure what the performance implications would be. You would use up  
> xids more quickly, which would require more frequent anti-wraparound  
> vacuuming. And if we just start using real xids as the key to  
> multixact-offsets slru, we would need to extend that a lot more often.  
> But I feel it would probably be acceptable.

When a key locker arrives after the updater and creates this implicit
subtransaction of the updater, how might you arrange for the xid's clog status
to eventually get updated in accordance with the updater's outcome?

Thanks,
nm

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

2012-02-27 Thread Tom Lane
Andres Freund  writes:
> I refreshed the patch so it works again on current HEAD. Basically some 
> trivial fixes and dfd26f9c5f371437f243249025863ea9911aacaa. The latter 
> doesn't 
> seem necessary to me after the changes, so I simply ditched it. Am I missing 
> something?

No, that was only needed because execMain.c was overriding somebody
else's tuple receiver.  If you're putting the right receiver into the
QueryDesc to start with, it shouldn't be necessary.

I'm confused by this though:

> This basically includes a revert of d8fb1f9adbddd1eef123d66a89a9fc0ecd96f60b

I don't find that commit ID anywhere.

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] Command Triggers

2012-02-27 Thread Tom Lane
Andres Freund  writes:
> On Tuesday, February 28, 2012 12:30:36 AM Tom Lane wrote:
>> Ugh ... if that's currently allowed, we definitely need to fix it.

> Btw, whats the danger youre seing?

Well, I'm not sure that it would actively break anything, but we
definitely meant to disallow the case.  Also, I seem to recall some
places that intuit a relation's shared marker in the opposite direction
(if it's in pg_global it must be shared), and that could definitely
cause issues if we treat a rel as shared when it isn't.

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] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Tom Lane
Christopher Browne  writes:
> On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane  wrote:
>> So, whatever the desirability of having them run as table owner,
>> we can't just up and change that.

> I'm inclined to hold to the argument that it Works Properly Now, and
> that we shouldn't break it by changing it.

I would say the same, or at least that any argument for changing it is
probably not strong enough to trump backwards compatibility.

However, Peter seems to think the other way is required by standard.
We can get away with defining whatever behavior we want for triggers
that invoke functions, since that syntax is nonstandard anyway.  But,
if you remember the original point of this thread, it was to add syntax
that is pretty nearly equivalent to the spec's.  If we're going to do
that, it had better also have semantics similar to the spec's.

So (assuming Peter has read the spec correctly) I'm coming around to the
idea that the anonymous trigger functions created by this syntax ought
to be "SECURITY DEFINER table_owner".

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] Command Triggers

2012-02-27 Thread Andres Freund
On Tuesday, February 28, 2012 12:30:36 AM Tom Lane wrote:
> Andres Freund  writes:
> > Sorry for letting this slide.
> > 
> > Is it worth adding this bit to OpenIntoRel? Not sure if there is danger
> > in allowing anyone to create shared tables
> > 
> > /* In all cases disallow placing user relations in pg_global */
> > if (tablespaceId == GLOBALTABLESPACE_OID)
> > 
> > ereport(ERROR,
> > 
> > (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> > 
> >  errmsg("only shared relations can be placed in 
> > pg_global
> > 
> > tablespace")));
> 
> Ugh ... if that's currently allowed, we definitely need to fix it.
Btw, whats the danger youre seing?

Andres

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


Re: [HACKERS] Command Triggers

2012-02-27 Thread Andres Freund
On Tuesday, February 28, 2012 12:30:36 AM Tom Lane wrote:
> Andres Freund  writes:
> > Sorry for letting this slide.
> > 
> > Is it worth adding this bit to OpenIntoRel? Not sure if there is danger
> > in allowing anyone to create shared tables
> > 
> > /* In all cases disallow placing user relations in pg_global */
> > if (tablespaceId == GLOBALTABLESPACE_OID)
> > 
> > ereport(ERROR,
> > 
> > (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> > 
> >  errmsg("only shared relations can be placed in 
> > pg_global
> > 
> > tablespace")));
> 
> Ugh ... if that's currently allowed, we definitely need to fix it.
> But I'm not sure OpenIntoRel is the right place.  I'd have expected
> the test to be at some lower level, like heap_create_with_catalog
> or so.
Its definitely allowed right now:

test-upgrade=# CREATE TABLE foo TABLESPACE pg_global AS SELECT 1;
SELECT 1
Time: 354.097 ms

The analogous check for the missing one in OpenIntoRel for plain relations is 
in defineRelation. heap_create_with_catalog only contains the inverse check:

/*
 * Shared relations must be in pg_global (last-ditch check)
 */
if (shared_relation && reltablespace != GLOBALTABLESPACE_OID)
elog(ERROR, "shared relations must be placed in pg_global 
tablespace");


Moving it there sounds like a good idea without any problem I can see right 
now. Want me to prepare a patch or is it just the same for you if you do it 
yourself?

Andres

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


Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Christopher Browne
On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane  wrote:
> "Kevin Grittner"  writes:
>> As far as I can tell, triggers run as the user performing the
>> operation which fires the trigger, not as the owner of the table.
>  > Can anyone provide an example of a trigger running as the table
>> owner?  Is there a bug here?  Something for the docs?
>
> A quick look into trigger.c shows that there is no attempt to switch
> current userid, so we were clearly all wrong about that.  Not sure
> why everyone recollected the opposite.
>
> On reflection, there's a fairly clear reason why not to switch userid:
> it would break triggers that do something like what's shown in the very
> first example in the plpgsql trigger documentation:
>
>        -- Remember who changed the payroll when
>        NEW.last_date := current_timestamp;
>        NEW.last_user := current_user;
>        RETURN NEW;
>
> So, whatever the desirability of having them run as table owner,
> we can't just up and change that.  At minimum we'd need to provide
> some function to get at the "calling userid" (or perhaps make that
> a new trigger argument?) and have a reasonable grace period for
> people to change over to using that.

I'm inclined to hold to the argument that it Works Properly Now, and
that we shouldn't break it by changing it.

The user *can* be changed, by running a security definer trigger function.

The behaviour that is under consideration seems to be to use something
akin to "security definer as table owner".  If someone *wants* that,
then they can readily accomplish that TODAY by altering the function
to make it a SECURITY DEFINER, and change owner to the table owner.

But if we change to have that be the default, it's nowhere near as
easy to unravel it, and to get to the situation where the trigger runs
with the security context of the user that ran the query.  SECURITY
DEFINER is more static than that.

> This might be something to consider in the adjacent thread about command
> triggers, too --- who do they run as, and if it's not the calling user,
> how do they find out who that is?

I'm inclined to hold to the same position on that.

- If you *WANT* the command trigger to run as user "frotz", then have
it be a security definer function owned by "frotz."
- Otherwise, it runs with the privileges of the calling user.

That doesn't seem woefully wrong to me.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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


Re: [HACKERS] Command Triggers

2012-02-27 Thread Tom Lane
Andres Freund  writes:
> Sorry for letting this slide.

> Is it worth adding this bit to OpenIntoRel? Not sure if there is danger in 
> allowing anyone to create shared tables

>   /* In all cases disallow placing user relations in pg_global */
>   if (tablespaceId == GLOBALTABLESPACE_OID)
>   ereport(ERROR,
>   (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
>errmsg("only shared relations can be placed in 
> pg_global 
> tablespace")));

Ugh ... if that's currently allowed, we definitely need to fix it.
But I'm not sure OpenIntoRel is the right place.  I'd have expected
the test to be at some lower level, like heap_create_with_catalog
or so.

regards, tom lane

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


Re: [HACKERS] Command Triggers

2012-02-27 Thread Andres Freund
On Sunday, December 04, 2011 02:09:08 AM Andres Freund wrote:
> First, does anybody think it would be worth getting rid of the duplication 
> from OpenIntoRel (formerly from execMain.c) in regard to DefineRelation()?
> I noticed that there already is some diversion between both. E.g. CREATE
> TABLE  frak TABLESPACE pg_global AS SELECT 1; is possible while it would
> be forbidden via a plain CREATE TABLE. (I will send a fix for this
> separately).
Sorry for letting this slide.

Is it worth adding this bit to OpenIntoRel? Not sure if there is danger in 
allowing anyone to create shared tables

/* In all cases disallow placing user relations in pg_global */
if (tablespaceId == GLOBALTABLESPACE_OID)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 errmsg("only shared relations can be placed in 
pg_global 
tablespace")));


Andres

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


Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Tom Lane
"Kevin Grittner"  writes:
> As far as I can tell, triggers run as the user performing the
> operation which fires the trigger, not as the owner of the table.
 > Can anyone provide an example of a trigger running as the table
> owner?  Is there a bug here?  Something for the docs?

A quick look into trigger.c shows that there is no attempt to switch
current userid, so we were clearly all wrong about that.  Not sure
why everyone recollected the opposite.

On reflection, there's a fairly clear reason why not to switch userid:
it would break triggers that do something like what's shown in the very
first example in the plpgsql trigger documentation:

-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;

So, whatever the desirability of having them run as table owner,
we can't just up and change that.  At minimum we'd need to provide
some function to get at the "calling userid" (or perhaps make that
a new trigger argument?) and have a reasonable grace period for
people to change over to using that.

This might be something to consider in the adjacent thread about command
triggers, too --- who do they run as, and if it's not the calling user,
how do they find out who that is?

regards, tom lane

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


Re: [HACKERS] [PATCH] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Tom Lane
Robert Haas  writes:
> On Mon, Feb 27, 2012 at 10:17 AM, Hannes Frederic Sowa  wrote:
>> As with recent changes to `standard_conforming_strings' the paragraph about
>> backslash escaping in the description of `LIKE' is only confusing. Thus I
>> attached a patch to remove it.

> I think I agree with removing this paragraph; it made sense when
> standard_conforming_strings=off was the default, but that's not so
> anymore.  We could come up with some alternative text to insert here
> but I think that might be unnecessarily long-winded.

On a closer look, I see three different places in the
functions-matching.html page that talk about doubling backslashes, of
which this one might be the least bogus, since it at least provides
a cross-reference to someplace where you would read about
standard_conforming_strings.

I suggest replacing the first and third cases with something along the
lines of

Note: if you have standard_conforming_strings turned off, any
backslashes you write in literal string constants will need to be
doubled.  See Section 4.1.2.1 for more information.

The second case is just a parenthetical comment and perhaps could be
removed.

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] Trigger execution role (was: Triggers with DO functionality)

2012-02-27 Thread Kevin Grittner
> Kevin Grittner wrote:
>> Tom Lane  wrote: 
>>> By default, a trigger function runs as the table owner, ie it's
>>> implicitly SEC DEF to the table owner.
>>  
>> Really?  That's certainly what I would *want*, but it's not what
>> I've seen.
>>
>> [self-contained example of that not happening]
 
Christopher Browne  wrote:
> 
> Yeah, not quite consistent with what I've seen.
 
Peter Eisentraut  wrote: 
> 
> Yes, you're right
 
As far as I can tell, triggers run as the user performing the
operation which fires the trigger, not as the owner of the table.
 
Can anyone provide an example of a trigger running as the table
owner?  Is there a bug here?  Something for the docs?
 
Test case (slightly modified) in runnable format, rather than a
copy/paste of a run:
 
create user bob;
create user ted;
--
set role bob;
create table t (id int not null primary key, val text);
create table s (id int not null primary key, val text not null);
grant select, insert, update, delete on t to ted;
grant select on s to ted;
create function t_ins_func() returns trigger language plpgsql as
$$
begin
  raise notice 'role = ''%''', current_user;
  if new.val is not null then
insert into s (id, val) values (new.id, new.val);
  end if;
  return new;
end;
$$;
create trigger t_ins_trig before insert on t
  for each row execute procedure t_ins_func();
--
reset role; set role ted;
insert into t values (1, null);
select * from s;
select * from t;
insert into t values (2, 'two');
 
-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] default values in inheritance hierarchies

2012-02-27 Thread Tom Lane
=?ISO-8859-2?Q?Miroslav_=A9imul=E8=EDk?=  writes:
> Is there any way how to force UPDATE to use default value of ID column from
> table a2 when updating rows in this table, without using triggers?

No.  The update is expanded using the default expression applicable to
the table that is named in the update command (ie, the parent).

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

2012-02-27 Thread Christopher Browne
On Mon, Feb 27, 2012 at 1:50 PM, David E. Wheeler  wrote:
> On Feb 26, 2012, at 4:53 AM, Peter Eisentraut wrote:
>
>>> I also liked Kevin's suggestion of DISCREET
>>
>> That would probably create too much confusion with "discrete".
>
> SECRETE?

BOUND?  GAGGED?
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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


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

2012-02-27 Thread Tatsuo Ishii
> Would looking into currently held locks help ? You might get some false
> positive because the transaction may have acquired a lock, but did not do
> any modification. But if you can live with that, it might be worth
> considering.

The locks disappear after corresponding sessions ends or the
transaction ends so I don't think I can use them for my particular
purpose.
--
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] Avoid FK validations for no-rewrite ALTER TABLE ALTER TYPE

2012-02-27 Thread Alvaro Herrera

Excerpts from Noah Misch's message of jue ene 26 12:00:49 -0300 2012:
> 
> On Wed, Jan 25, 2012 at 10:39:56PM -0500, Noah Misch wrote:
> > In any event, the patch needed a rebase, so I've attached it rebased and 
> > with
> > that comment edited to reference ri_GenerateQualCollation(), that being the
> > most-relevant source for the assumption in question.
> 
> Commit 9d35116611e6a1fc10f2298944fbf0e4e1a826be invalidated the test case 
> hunks
> again.  We'll need to either remove the test cases, as Robert chose to do for
> that other patch, or bolster them per
> http://archives.postgresql.org/message-id/20120126115536.GD15670%40tornado.leadboat.com

Committed, removing the tests.

I also chose to update catversion, even though I can't figure out how to
make a Constraint node be stored anywhere.  Maybe it's not even
possible, but then I thought maybe I'm just lacking imagination.

Thanks!

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

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


Re: [HACKERS] swapcache-style cache?

2012-02-27 Thread Jan Lentfer

Am 23.02.2012 21:57, schrieb Greg Smith:

On 02/22/2012 05:31 PM, james wrote:

Has anyone considered managing a system like the DragonFLY swapcache for
a DBMS like PostgreSQL?

ie where the admin can assign drives with good random read behaviour
(but perhaps also-ran random write) such as SSDs to provide a cache for
blocks that were dirtied, with async write that hopefully writes them
out before they are forcibly discarded.


We know that battery-backed write caches are extremely effective for
PostgreSQL writes. I see most of these tiered storage ideas as acting
like a big one of those, which seems to hold in things like SAN storage
that have adopted this sort of technique already. A SSD is quite large
relative to a typical BBWC.

[...]


-Ultimately all this data needs to make it out to real disk. The funny
thing about caches is that no matter how big they are, you can easily
fill them up if doing something faster than the underlying storage can
handle.


[...]


I don't think the idea of a swapcache is without merit; there's surely
some applications that will benefit from it. It's got a lot of potential
as a way to absorb short-term bursts of write activity. And there are
some applications that could benefit from having a second tier of read
cache, not as fast as RAM but larger and faster than real disk seeks. In
all of those potential win cases, though, I don't see why the OS
couldn't just manage the whole thing for us.


First off, thank's very much for mentioning DragonFly's swapcache on 
this mailing list, which takes the burden off me/us to self-advertise 
this feature :)


But swapcache is clearly not meant or designed to speed up any write 
activity by caching writes and delaying the write to the "target 
storage" to a later point in time. Swapcache does not affect writes in 
any way, actually.
Swapcache does its writing when a clean VM page hits the inactive VM 
page queue. VM pages related to filesystem writes are dirty, the write 
occurs normally, then they become clean.  But they still have to cycle 
into the VM page inactive queue before swapcache will touch them (write 
them out to swap).


So, basically it is designed to speed up Metadata reads, and if 
configured to do so, data reads.


So, it can take some read load burden of the disk subsystem and free the 
disk subsystem for more write activity, but that would be just a side 
effect, not a design goal.


And, yes.. it does effect pgsql performance on read loads seriously.

See BSD Mag 5/2011
http://bsdmag.org/magazine/1691-embedded-bsd-freebsd-alix

and
http://www.shiningsilence.com/dbsdlog/2011/04/12/7586.html

Jan



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


Re: [HACKERS] default values in inheritance hierarchies

2012-02-27 Thread Miroslav Šimulčík
Sorry, I have accidentaly sent incomplet mail.

Here is the rest:

The following sequence of commands will end up with error, because of
duplicate ID in table a2:

insert into a2(data2, data) values(456, 'jkl');
insert into a2(data2, data) values(789, 'mno');

Is there any way how to force UPDATE to use default value of ID column from
table a2 when updating rows in this table, without using triggers?

Thank you

Best regards
Miroslav Simulcik

2012/2/27 Miroslav Šimulčík 

> Hi,
>
> I have two tables defined for example like this:
>
> create table a1 (id serial primary key, data text);
> create table a2 (id serial primary key, data2 integer) inherits (a1);
>
> The point is, that I want to have two tables with inheritance relation
> between them, but each with its own id column (overriden in child table).
>
> Then I execute this sequence of commands:
>
> insert into a1(data) values('abc');
> insert into a2(data2, data) values(123, 'def');
> update a1 set id = default, data = 'ghi';
>
> I need new IDs on each update, because I store old rows in another table
> to keep history of changes. Problem is that update uses values from
> sequence belonging to table a1 when updating rows in table a2.
>
> So the content of tables after operations is:
>
> select * from only a1;
>  id | data
> +--
>   2 | ghi
>
> select * from a2;
>  id | data | data2
> +--+---
>   3 | ghi  |   123
>
> The following sequence of commands ends up wit
>


[HACKERS] default values in inheritance hierarchies

2012-02-27 Thread Miroslav Šimulčík
Hi,

I have two tables defined for example like this:

create table a1 (id serial primary key, data text);
create table a2 (id serial primary key, data2 integer) inherits (a1);

The point is, that I want to have two tables with inheritance relation
between them, but each with its own id column (overriden in child table).

Then I execute this sequence of commands:

insert into a1(data) values('abc');
insert into a2(data2, data) values(123, 'def');
update a1 set id = default, data = 'ghi';

I need new IDs on each update, because I store old rows in another table to
keep history of changes. Problem is that update uses values from sequence
belonging to table a1 when updating rows in table a2.

So the content of tables after operations is:

select * from only a1;
 id | data
+--
  2 | ghi

select * from a2;
 id | data | data2
+--+---
  3 | ghi  |   123

The following sequence of commands ends up wit


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Greg Smith

On 02/27/2012 03:39 PM, Tom Lane wrote:

Greg Smith  writes:

Here are some Debian/Ubuntu platforms that all run into the other problem:
Ubuntu 9.04, openjade 1.4devel1-19:  flow error
Debian Squeeze, openjade 1.4devel1-19 : flow error



I always assumed that the reason this didn't work, but the Fedoras did,
was because of a difference between 1.3 and 1.4.


Nah, I don't think so.  The oldest reports of this in our archives are
from 7.4 era:
http://archives.postgresql.org/pgsql-docs/2003-12/msg00024.php
which could not have been openjade 1.4.


I'm not so sure about that...that's 2003, and I've found RPM packages 
including 1.4-devel going back to what looks like late 2001: 
http://rpm.pbone.net/index.php3/stat/4/idpl/3369792/dir/turbolinux/com/OpenJade-1.4devel-2.i586.rpm.html


The Postgres documentation documentation says "Downgrading to openjade 
1.3 should get rid of this error." right now; at least I had a good 
basis for the assumption I was making.  Other ideas suggesting that's 
the case include the discussion you were involved in at 
http://sourceforge.net/mailarchive/message.php?msg_id=26959520 as well 
as the long open bug at 
https://bugs.launchpad.net/ubuntu/+source/openjade/+bug/12431 (which has 
a small test case showing the same error).


Actually, check this out.  I've attached the test.docbook from that 
Ubuntu bug report.  If I just run docbook2pdf on my Debian system, it 
works fine:


$ docbook2pdf test.docbook
Using catalogs: /etc/sgml/catalog
Using stylesheet: /usr/share/docbook-utils/docbook-utils.dsl#print
Working on: /home/gsmith/test.docbook
jade:/usr/share/sgml/declaration/xml.dcl:31:27:W: characters in the 
document character set with numbers exceeding 65535 not supported

Done.

And I can view the resulting PDF.  But the test case in the bug report 
says it's reproducible using "-p" to specify an alternate parser.  If I 
do that, sure enough the bug pops up:


$ docbook2pdf -p /usr/bin/openjade-1.4devel test.docbook
Using catalogs: /etc/sgml/catalog
Using stylesheet: /usr/share/docbook-utils/docbook-utils.dsl#print
Working on: /home/gsmith/test.docbook
/usr/bin/openjade-1.4devel:/usr/share/sgml/docbook/stylesheet/dsssl/modular/print/dbttlpg.dsl:2722:6:E: 
flow object not accepted by port; only display flow objects accepted

Done.

That's despite the fact that my installed openjade is the same one I'm 
referring to with the alternate parser:


$ which openjade
/usr/bin/openjade
$ ls -l /usr/bin/openjade
lrwxrwxrwx 1 root root 26 Jan 10 04:57 /usr/bin/openjade -> 
/etc/alternatives/openjade

$ ls -l /etc/alternatives/openjade
lrwxrwxrwx 1 root root 26 Jan 10 04:57 /etc/alternatives/openjade -> 
/usr/bin/openjade-1.4devel


And the bug is still there without the symlink coming into place:

$ docbook2pdf -p /usr/bin/openjade test.docbook
Using catalogs: /etc/sgml/catalog
Using stylesheet: /usr/share/docbook-utils/docbook-utils.dsl#print
Working on: /home/gsmith/test.docbook
/usr/bin/openjade:/usr/share/sgml/docbook/stylesheet/dsssl/modular/print/dbttlpg.dsl:2722:6:E: 
flow object not accepted by port; only display flow objects accepted

Done.

Now that's just bizarre--that the problem comes and goes based on 
whether you specific the parser by name.  The same test works fine in 
all incarnations on my SL6 system, so this simple case seems to follow 
the systems where the problem is and isn't in my testing so far.  Makes 
me wonder if this is more of a memory stomping type of bug rather than a 
functional one.  If this simple test case holds up as representative of 
the PostgreSQL problem, that would seem an easier thing to get fixed 
upstream.


There is an open bug in this area for Debian too: 
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=521148


That gets reproduced like this (file test2.docbook also attached):

$ openjade -t tex -d /usr/share/docbook-utils/docbook-utils.dsl#print 
/usr/share/sgml/declaration/xml.dcl test2.docbook
openjade:test2.docbook:6:12:E: document type does not allow element 
"glossary" here

openjade:test2.docbook:6:12:E: end tag for "glossary" which is not finished
openjade:test2.docbook:6:12:E: end tag for "glossary" which is not finished
openjade:test2.docbook:7:10:E: end tag for "chapter" which is not finished
openjade:/usr/share/sgml/docbook/stylesheet/dsssl/modular/print/dbcompon.dsl:205:3:E: 
flow object not accepted by port; only display flow objects accepted


This suggests whatever is going wrong here doesn't just impact PDF 
creation.  And this one also works fine on the openjade 1.3 based SL6 
server here, just need a different path for the library files:


$ openjade -t tex -d 
/usr/share/sgml/docbook/utils-0.6.14/docbook-utils.dsl#print 
/usr/share/sgml/xml.dcl test2.docbook


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

http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd";>



  Title
  Subtitle



http://www.oasis-open.org/docbook/xml/

Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Dimitri Fontaine
Tom Lane  writes:
> FWIW, I agree with Thom on this.  If we do it as you suggest, I
> confidently predict that it will be less than a year before we seriously
> regret it.  Given all the discussion around this, it's borderline insane
> to believe that the set of parameters to be passed to command triggers
> is nailed down and won't need to change in the future.

I agree with the analysis…

> As for special coding of support, it hardly seems onerous when every
> language that has triggers at all has got some provision for the
> existing trigger parameters.  A bit of copying and pasting should get
> the job done.

But had been (too easily) convinced not to take that route.  You changed
my mind already, I'll see about changing the code too tomorrow (a cold
is having me out of steam for tonight).

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

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Tom Lane
Dimitri Fontaine  writes:
> Thom Brown  writes:
>> I've got a question regarding the function signatures required for
>> command triggers, and apologies if it's already been discussed to
>> death (I didn't see all the original conversations around this).
>> These differ from regular trigger functions which don't require any
>> arguments, and instead use special variables.  Why aren't we doing the
>> same for command triggers?  So instead of having the parameters

> Basically so that we don't have to special code support for each and
> every language out there.

FWIW, I agree with Thom on this.  If we do it as you suggest, I
confidently predict that it will be less than a year before we seriously
regret it.  Given all the discussion around this, it's borderline insane
to believe that the set of parameters to be passed to command triggers
is nailed down and won't need to change in the future.

As for special coding of support, it hardly seems onerous when every
language that has triggers at all has got some provision for the
existing trigger parameters.  A bit of copying and pasting should get
the job done.

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] possible new option for wal_sync_method

2012-02-27 Thread Andres Freund
Hi,

On Friday, February 17, 2012 01:17:27 AM Dan Scales wrote:
> Good point, thanks.  From the ext3 source code, it looks like
> ext3_sync_file() does a blkdev_issue_flush(), which issues a flush to the
> block device, whereas simple direct IO does not.  So, that would make
> this wal_sync_method option less useful, since, as you say, the user
> would have to know if the block device is doing write caching.
The experiments I know which played with disabling write caches nearly always 
had the result that write caching as worth the overhead of syncing.

> For the numbers I reported, I don't think the performance gain is from
> not doing the block device flush.  The system being measured is a Fibre
> Channel disk which should have a fully-nonvolatile disk array.  And
> measurements using systemtap show that blkdev_issue_flush() always takes
> only in the microsecond range.
Well, I think it has some io queue implications which could explain some of 
the difference. With that regard I think it heavily depends on the kernel 
version as thats an area which had loads of pretty radical changes in nearly 
every release since 2.6.32.

> I think the overhead is still from the fact that ext3_sync_file() waits
> for the current in-flight transaction if there is one (and does an
> explicit device flush if there is no transaction to wait for.)  I do
> think there are lots of meta-data operations happening on the data files
> (especially for a growing database), so the WAL log commit is waiting for
> unrelated data operations.  It would be nice if there a simple file
> system operation that just flushed the cache of the block device
> containing the filesystem (i.e. just does the blkdev_issue_flush() and
> not the other things in ext3_sync_file()).
I think you are right there. I think the metadata issue could be relieved a 
lot by doing the growing of files in way much larger bits than currently. I 
have seen profiles which indicated that lots of time was spent on increasing 
the file size. I would be very interested in seing how much changes in that 
area would benefit real-world benchmarks.

> The ext4_sync_file() code looks fairly similar, so I think it may have
> the same problem, though I can't be positive.  In that case, this
> wal_sync_method option might help ext4 as well.
The journaling code for ext4 is significantly different so I think it very 
well might play a role here - although youre probably right and it wont be in 
*_sync_file.

> With respect to sync_file_range(), the Linux code that I'm looking at
> doesn't really seem to indicate that there is a device flush (since it
> never calls a f_op->fsync_file operation).  So sync_file_range() may be
> not be as useful as thought.
Hm, need to check that. I thought it invoked that path somewhere.

> By the way, all the numbers were measured with "data=writeback,
> barrier=1" options for ext3.  I don't think that I have seen a
> significant different when the DBT2 workload for ext3 option
> data=ordered.
You have not? Interesting again because I have seen results that differed by a 
magnitude.

> I will measure all these numbers again tonight, but with barrier=0, so as
> to try to confirm that the write flush itself isn't costing a lot for
> this configuration.
Got any result so far?

Thanks,

Andres

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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Tom Lane
Greg Smith  writes:
> Here are some Debian/Ubuntu platforms that all run into the other problem:
> Ubuntu 9.04, openjade 1.4devel1-19:  flow error
> Debian Squeeze, openjade 1.4devel1-19 : flow error

> I always assumed that the reason this didn't work, but the Fedoras did, 
> was because of a difference between 1.3 and 1.4.

Nah, I don't think so.  The oldest reports of this in our archives are
from 7.4 era:
http://archives.postgresql.org/pgsql-docs/2003-12/msg00024.php
which could not have been openjade 1.4.

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] Initial 9.2 pgbench write results

2012-02-27 Thread Greg Smith

On 02/27/2012 08:08 AM, Robert Haas wrote:

OK, fair point.  But I don't think any of us - Greg included - have an
enormously clear idea why turning the background writer off is
improving performance in some cases.  I think we need to understand
that better before we start changing things.


Check out 
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00895.php for 
proof this is not a new observation.


The fact that there are many workloads where the background writer just 
gets in the way was clear since the 8.3 development four years ago.  One 
of my guiding principles then was to err on the side of doing less in 
the default configuration.  The defaults in 8.3 usually do less than the 
8.2 configuration, given a reasonable shared_buffers size.


Since then we've found a few cases where it measurably helps.  The 
examples on my recent graphs have a few such tests.  Simon has mentioned 
seeing big gains during recovery from having 2 processes pushing I/O out.


One of the reasons I drilled right into this spot is because of fears 
that running the writer more often would sprout regressions in TPS.  I 
can't explain exactly why exactly having backends write their own 
buffers out at the latest possible moment works significantly better in 
some cases here.  But that fact isn't new to 9.2; it's just has a 
slightly higher potential to get in the way, now that the writing 
happens during the sync phase.


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

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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Tom Lane
Greg Smith  writes:
> I just tried building postgres-US.pdf on a RHEL-derived system, 
> Scientific Linux 6 using openjade-1.3.2-36.el6.  That gave me lots of 
> "Overfull hbox" errors, then died like this:

> ! pdfTeX error (ext4): \pdfendlink ended up in different nesting level 
> than \pdfstartlink.

This one is a known issue having to do with  text that crosses a
page boundary.  It's fairly annoying but we know how to work around it:
tweak the text to avoid having a  fall right there, or better
shorten the  text enough so it doesn't cross a line end.

If you got as far as the TeX run, though, you're not seeing the openjade
crash.  I rather suspect that one is at bottom a similar sort of issue,
but since it provides no feedback at all about where in the document it
is, it's hard to do much with it.

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] overriding current_timestamp

2012-02-27 Thread David E. Wheeler
On Feb 27, 2012, at 11:43 AM, Peter Eisentraut wrote:

>> Why not? Not challenging your assertion here, just curious why it’s 
>> different.
> 
> Because it's not actually a function, it's hardcoded in the grammar to
> call pg_catalog.now().

Ah, I see. Pity.

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


Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut
On mån, 2012-02-27 at 11:40 -0800, David E. Wheeler wrote:
> On Feb 27, 2012, at 11:39 AM, Peter Eisentraut wrote:
> 
> >> I use it for NOW() all the time.
> > 
> > But it won't work for current_timestamp.
> 
> Why not? Not challenging your assertion here, just curious why it’s different.

Because it's not actually a function, it's hardcoded in the grammar to
call pg_catalog.now().


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


Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread David E. Wheeler
On Feb 27, 2012, at 11:39 AM, Peter Eisentraut wrote:

>> I use it for NOW() all the time.
> 
> But it won't work for current_timestamp.

Why not? Not challenging your assertion here, just curious why it’s different.

David


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


Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut
On mån, 2012-02-27 at 08:48 -0800, David E. Wheeler wrote:
> On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:
> 
> >> I create a "mock" schema, add the function to it, and then put it in the 
> >> search_path ahead of pg_catalog.
> > 
> > That doesn't work for current_timestamp and similar built-in functions,
> > because they are always mapped to the pg_catalog schema.
> 
> I use it for NOW() all the time.

But it won't work for current_timestamp.


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

2012-02-27 Thread Andres Freund
On Monday, February 27, 2012 08:30:31 PM Thom Brown wrote:
> On 27 February 2012 19:19, Dimitri Fontaine  wrote:
> > Thom Brown  writes:
> >> 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.
> > 
> > You won't believe it:  CTAS is not implemented as a DDL.  Andres did
> > some work about that and sent a patch that received positive reviews by
> > both Tom and Robert, once that's in I can easily add support for the
> > command.
I actually don't think anybody actually reviewed the patch so far. Tom and I 
discussed the implementation strategy beforehand a bit though.

> > Thanks Andres :)
Youre welcome. Thanks for your awesome work that actually made it necessary ;)

> I don't see it anywhere in the commitfest.  Has it been properly submitted?
I actually always viewed it as a part of the Dim's patch which is why I didn't 
submit it as a separate patch. Maybe that was a mistake...

http://archives.postgresql.org/message-
id/201112112346.07611.and...@anarazel.de contains the latest revision.




Andres

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Dimitri Fontaine
Thom Brown  writes:
> CREATE COMMAND TRIGGER test_cmd_trg
> BEFORE CREATE SCHEMA,
>   CREATE OPERATOR,
>   CREATE COLLATION,
>   CREATE CAST
> EXECUTE PROCEDURE my_func();
>
> I couldn't drop it completely unless I specified all of those commands.  Why?

Because I couldn't find a nice enough way to implement that given the
shared infrastructure Robert and Kaigai did put into place to handle
dropping of objects.  It could be that I didn't look hard enough, I'll
be happy to get back that feature.

> Incidentally, I've noticed the DROP COMMAND TRIGGER has a mistake in the 
> syntax.

Thanks, fix will be in the next version.

> The documentation also needs to cover the pg_cmdtrigger catalogue as
> it's not mentioned anywhere.

That too, working on it now, adding forgotten forms you reported and
more, adding regression tests, fixing weird cases, getting there :)

> I'm enjoying playing with this feature though btw. :)

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

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Dimitri Fontaine
Thom Brown  writes:
> I've got a question regarding the function signatures required for
> command triggers, and apologies if it's already been discussed to
> death (I didn't see all the original conversations around this).
> These differ from regular trigger functions which don't require any
> arguments, and instead use special variables.  Why aren't we doing the
> same for command triggers?  So instead of having the parameters

Basically so that we don't have to special code support for each and
every language out there.

> Disadvantages are that there's more maintenance overhead for
> supporting multiple languages using special variables.

Lots of, so I've been told, enough of it for not taking this choice
seriously.  I'll admit I didn't personally looked at what it would
entail implementation wise.

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

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Thom Brown
On 27 February 2012 19:19, Dimitri Fontaine  wrote:
> Thom Brown  writes:
>> 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.
>
> You won't believe it:  CTAS is not implemented as a DDL.  Andres did
> some work about that and sent a patch that received positive reviews by
> both Tom and Robert, once that's in I can easily add support for the
> command.
>
> Thanks Andres :)

I don't see it anywhere in the commitfest.  Has it been properly submitted?

-- 
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] How to know a table has been modified?

2012-02-27 Thread Lennin Caro
   you're right, changes in cascading tables are not logged.




Ing. Lennin Caro Pérez

Usuario:GNU/LINUX

PHP Developer

PostgreSQL DBA

Oracle DBA

Linux  counter id 474393

--- On Mon, 2/27/12, Tatsuo Ishii  wrote:

From: Tatsuo Ishii 
Subject: Re: [HACKERS] How to know a table has been modified?
To: lennin.c...@yahoo.com
Cc: kevin.gritt...@wicourts.gov, pgsql-hackers@postgresql.org
Date: Monday, February 27, 2012, 4:05 PM

Are you suggesting log_statement? I don't think it's a solution by
following reasons:

1) it's slow to enable that on busy systems
2) tables affected by cascading delete/update/drop is not logged in
   PostgreSQL log
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> check the log of postgresql, there you can take the table name and the date 
> of the modification
> 
> 
> Ing. Lennin Caro Pérez
> 
> Usuario:GNU/LINUX
> 
> PHP Developer
> 
> PostgreSQL DBA
> 
> Oracle DBA
> 
> Linux  counter id 474393
> 
> --- On Mon, 2/27/12, Tatsuo Ishii  wrote:
> 
> From: Tatsuo Ishii 
> Subject: Re: [HACKERS] How to know a table has been modified?
> To: kevin.gritt...@wicourts.gov
> Cc: pgsql-hackers@postgresql.org
> Date: Monday, February 27, 2012, 12:04 PM
> 
>>> For TRIGGER, I cannot thinking of any way. Any idea will be
>>> welcome.
>>  
>> It would require creating "cooperating" triggers in the database and
>> having a listener, but you might consider the
>> triggered_change_notifications() trigger function included in 9.2. 
>> It works at least as far back as 9.0; I haven't tried it any further
>> back.
> 
> Thanks for the info. It's a little bit overkill for my purpose though.
> (on busy systems, the notification would be too frequent).
> 
> I would think that creating a small routine periodically consults
> pg_stat_all_tables view and records the last update datetime for each
> table (unfortunately the view does not have last modification date).
> --
> 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] Command Triggers, patch v11

2012-02-27 Thread Dimitri Fontaine
Thom Brown  writes:
> SELECT * INTO badname FROM goodname;

Again, see Andres' patch about that.

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

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-27 Thread Dimitri Fontaine
Thom Brown  writes:
> 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.

You won't believe it:  CTAS is not implemented as a DDL.  Andres did
some work about that and sent a patch that received positive reviews by
both Tom and Robert, once that's in I can easily add support for the
command.

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

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


Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Simon Riggs
On Mon, Feb 27, 2012 at 1:08 PM, Robert Haas  wrote:
> On Mon, Feb 27, 2012 at 3:50 AM, Simon Riggs  wrote:
>> That isn't the case. We have evidence that the current knobs are
>> hugely ineffective in some cases.
>>
>> Turning the bgwriter off is hardly "adjusting a setting", its
>> admitting that there is no useful setting.
>>
>> I've suggested changes that aren't possible by tuning the current knobs.
>
> OK, fair point.  But I don't think any of us - Greg included - have an
> enormously clear idea why turning the background writer off is
> improving performance in some cases.  I think we need to understand
> that better before we start changing things.

I wasn't suggesting we make a change without testing. The theory that
the bgwriter is doing too much work needs to be tested, so we need a
proposal for how to reduce that work in a coherent way so we can test,
which is what I've given. Other proposals are also possible.

-- 
 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] pgstat documentation tables (pdf OK on centos)

2012-02-27 Thread Erik Rijkers
On Mon, February 27, 2012 18:30, Magnus Hagander wrote:
>>
 does that work for others, or did we break something globally in it?
>>>

FWIW: I build A4 pdf's for HEAD often (say, weekly), on centos 5; it has always 
worked this last
year or so (I did tweak tex (?) parameters, long ago).

I built the A4 pdf just now again without problem.

Centos 5.7, openjade 1.3.2-27.

Here are some observations on these pg-stat tables:

Table 27-1. Standard Statistics views: looks OK in the pdf (HEAD).

Table 27-2 has some of the longer GUC/function names sticking out of the table 
frame.  not pretty
but it still works, they are still readable as a whole.

Table 27-3 (pg_stat_bgwriter_view)  it does not look good: some GUC/function 
names not only stick
outsize the table frame but are truncated (and thus become unreadable).


I think it would be better if somehow the widths of the 3 table-columns 
('Column', 'Type',
'Description') would not be 3 columns of the same width, like they seem to be 
now: the 'Type'
column  take fully one third of page-width, for short words like 'text' and 
'bigint'.  The
'Column' values too are almost always more narrow than 1/3 of the page.


Erik Rijkers



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


Re: [HACKERS] leakproof

2012-02-27 Thread Pavel Stehule
2012/2/27 David E. Wheeler :
> On Feb 26, 2012, at 4:53 AM, Peter Eisentraut wrote:
>
>>> I also liked Kevin's suggestion of DISCREET
>>
>> That would probably create too much confusion with "discrete".
>
> SECRETE?

next one

MUTE ?

Regards

Pavel

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

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


Re: [HACKERS] leakproof

2012-02-27 Thread David E. Wheeler
On Feb 26, 2012, at 4:53 AM, Peter Eisentraut wrote:

>> I also liked Kevin's suggestion of DISCREET
> 
> That would probably create too much confusion with "discrete".

SECRETE?

David


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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Greg Smith

On 02/27/2012 12:22 PM, Tom Lane wrote:

FWIW, all the recent reports of this seem to be on Ubuntu or Debian.
Don't know if that means few of us use Fedora or if it means Fedora has
fixed this.


I just tried building postgres-US.pdf on a RHEL-derived system, 
Scientific Linux 6 using openjade-1.3.2-36.el6.  That gave me lots of 
"Overfull hbox" errors, then died like this:


! pdfTeX error (ext4): \pdfendlink ended up in different nesting level 
than \pd

fstartlink.

   \endgroup \set@typeset@protect
l.895119 ...rticular type modifier value.\endPar{}
  \endNode{}\Node%
!  ==> Fatal error occurred, no output PDF file produced!


Here are some Debian/Ubuntu platforms that all run into the other problem:

Ubuntu 9.04, openjade 1.4devel1-19:  flow error
Debian Squeeze, openjade 1.4devel1-19 : flow error

I always assumed that the reason this didn't work, but the Fedoras did, 
was because of a difference between 1.3 and 1.4.  Maybe a forward port 
of that 1.3 patch would help.  I'm not sure what state RHEL6 is really 
in though, I don't fully trust my SL6 system yet for this task.


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

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


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

2012-02-27 Thread Pavan Deolasee
On Mon, Feb 27, 2012 at 9:35 PM, Tatsuo Ishii  wrote:

> Are you suggesting log_statement? I don't think it's a solution by
> following reasons:
>
> 1) it's slow to enable that on busy systems
> 2) tables affected by cascading delete/update/drop is not logged in
>   PostgreSQL log
>
>
Would looking into currently held locks help ? You might get some false
positive because the transaction may have acquired a lock, but did not do
any modification. But if you can live with that, it might be worth
considering.

Thanks,
Pavan

-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 18:28, Magnus Hagander  wrote:
> On Mon, Feb 27, 2012 at 18:22, Tom Lane  wrote:
>> Magnus Hagander  writes:
>>> On Mon, Feb 27, 2012 at 18:00, Tom Lane  wrote:
 Something else to keep in mind is that PDF-format docs are not terribly
 forgiving of wide tables --- have you looked at what these look like in
 PDF?
>>
>>> I have not, and I can't seem to build them:
>>> openjade  -D . -D . -c
>>> /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d
>>> ./stylesheet.dsl -t tex -V tex-backend -i output-print -i
>>> include-index -V texpdf-output -V '%paper-type%'=A4 -o
>>> postgres-A4.tex-pdf postgres.sgml
>>> openjade:./stylesheet.dsl:678:2:E: flow object not accepted by port;
>>> only display flow objects accepted
>>
>> Oh, not that again :-(.  We've never figured out exactly what triggers
>> that AFAIR.
>
> :-O
>
>>> does that work for others, or did we break something globally in it?
>>
>> FWIW, I built all the back-branch versions in both US and A4 formats
>> last week on Fedora 16.  Don't recall trying HEAD though.
>
> I only tried HEAD. Trying 9.1 now, and I get the same crash there. Can
> you (or someone) try it on Fedora with the latest tip of 9.1,
> including the release notes, if those broke it?

actually, that can't be it - Devrim built the 9.1 PDFs fine on a
Fedora box, and there hasn't been any activity on 9.1 stable since.
Must be the Ubuntu-is-broken thing then :(

-- 
 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] pgstat documentation tables

2012-02-27 Thread Tom Lane
Magnus Hagander  writes:
> On Mon, Feb 27, 2012 at 18:22, Tom Lane  wrote:
>> FWIW, I built all the back-branch versions in both US and A4 formats
>> last week on Fedora 16.  Don't recall trying HEAD though.

> I only tried HEAD. Trying 9.1 now, and I get the same crash there. Can
> you (or someone) try it on Fedora with the latest tip of 9.1,
> including the release notes, if those broke it?

What I was building *was* the release notes, specifically to see if it
would work or not, because we've had problems before with the PDF builds
failing unexpectedly.  Should be exactly the same docs as 9.1 branch
tip.  (Rechecks...) In fact, I later rebuilt postgres-US.pdf from the
9.1.3 tarball, so that definitely was the released bits.

This doesn't really prove that Fedora has a fix for the bug, of course.
We have noted before that Fedora's style sheets sometimes result in page
breaks at different places from other distros, and it would not be
terribly surprising if this bug were page-break-sensitive.

regards, tom lane

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


Re: [HACKERS] [PATCH] Cleanup: use heap_open/heap_close consistently

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 11:59 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> I'm almost inclined to think that
>> we should be trying to get rid of heap_open() altogether; there are
>> already plenty of places that assume that opening the relation is as
>> good as opening the heap, so I don't think there'd be any real loss of
>> abstraction.
>
> Or, perhaps, restrict it to open actual heaps (ie, relkind 'r')?

That carries a significant risk of breaking third-party code; or even
core code.  I'm almost positive that there is core code that relies on
heap_open's failure to reject all relkinds other than 'r'.  We can go
through all the callers and audit them, but there's a non-trivial risk
of breaking something.

> I think that if you count, you'll find the vast majority of heap_open
> calls are really opening system catalogs.  So I'd just as soon have
> a relkind check there for sanity's sake, not to mention that renaming
> them all creates a lot of unnecessary code churn.

The code churn does suck.  I have to admit, though, that I'd really
like to get out from under the pairing requirement: we've pretty much
already committed ourselves to a future where heap_close() can never
be anything more than relation_close().  Like it or not, that die is
cast.  In doing the DDL refactoring that I undertook this release
cycle, I found that it was often necessary to switch from using
heap_openrv() to RangeVarGetRelid + relation_open().  That of course
means tracking down the corresponding heap_close() calls and making
them relation_close().  I think it might be better to bite the bullet
and just do that across the board.  In the long run I think life will
be simpler with just one way to do it (Perl slogans nonwithstanding).

> IMO it would be sensible for heap_open to insist on a heap, index_open
> to insist on an index, and for anything else, use relation_open and
> BYO relkind check.  There are a few common patterns (eg "does relation
> have storage") that we should abstract somehow, but it might be better
> to provide separate relkind-check routines than to invent xxx_open.

Definitely.

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

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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 18:22, Tom Lane  wrote:
> Magnus Hagander  writes:
>> On Mon, Feb 27, 2012 at 18:00, Tom Lane  wrote:
>>> Something else to keep in mind is that PDF-format docs are not terribly
>>> forgiving of wide tables --- have you looked at what these look like in
>>> PDF?
>
>> I have not, and I can't seem to build them:
>> openjade  -D . -D . -c
>> /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d
>> ./stylesheet.dsl -t tex -V tex-backend -i output-print -i
>> include-index -V texpdf-output -V '%paper-type%'=A4 -o
>> postgres-A4.tex-pdf postgres.sgml
>> openjade:./stylesheet.dsl:678:2:E: flow object not accepted by port;
>> only display flow objects accepted
>
> Oh, not that again :-(.  We've never figured out exactly what triggers
> that AFAIR.

:-O

>> does that work for others, or did we break something globally in it?
>
> FWIW, I built all the back-branch versions in both US and A4 formats
> last week on Fedora 16.  Don't recall trying HEAD though.

I only tried HEAD. Trying 9.1 now, and I get the same crash there. Can
you (or someone) try it on Fedora with the latest tip of 9.1,
including the release notes, if those broke it?


>> I'm not sure I've ever tried to built it on Ubuntu 12.10 before, so it
>> could be that..
>
> FWIW, all the recent reports of this seem to be on Ubuntu or Debian.
> Don't know if that means few of us use Fedora or if it means Fedora has
> fixed this.  I wonder a bit about this patch Fedora is carrying:
> http://pkgs.fedoraproject.org/gitweb/?p=openjade.git;a=blob;f=openjade-1.3.2-gcc46.patch;h=962ac19ffea6bb6434ebb1a482d5604088192c7a;hb=fce180174c911b5982a5b1a45e09912c910e9bbf
> which is stated to be for a recent gcc change, but it does seem to
> have to do with creation of flow objects ...

My ubuntu has gcc 4.6.1, so AFAICT it would be affected.


-- 
 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] pgstat documentation tables

2012-02-27 Thread Tom Lane
Magnus Hagander  writes:
> On Mon, Feb 27, 2012 at 18:00, Tom Lane  wrote:
>> Something else to keep in mind is that PDF-format docs are not terribly
>> forgiving of wide tables --- have you looked at what these look like in
>> PDF?

> I have not, and I can't seem to build them:
> openjade  -D . -D . -c
> /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d
> ./stylesheet.dsl -t tex -V tex-backend -i output-print -i
> include-index -V texpdf-output -V '%paper-type%'=A4 -o
> postgres-A4.tex-pdf postgres.sgml
> openjade:./stylesheet.dsl:678:2:E: flow object not accepted by port;
> only display flow objects accepted

Oh, not that again :-(.  We've never figured out exactly what triggers
that AFAIR.

> does that work for others, or did we break something globally in it?

FWIW, I built all the back-branch versions in both US and A4 formats
last week on Fedora 16.  Don't recall trying HEAD though.

> I'm not sure I've ever tried to built it on Ubuntu 12.10 before, so it
> could be that..

FWIW, all the recent reports of this seem to be on Ubuntu or Debian.
Don't know if that means few of us use Fedora or if it means Fedora has
fixed this.  I wonder a bit about this patch Fedora is carrying:
http://pkgs.fedoraproject.org/gitweb/?p=openjade.git;a=blob;f=openjade-1.3.2-gcc46.patch;h=962ac19ffea6bb6434ebb1a482d5604088192c7a;hb=fce180174c911b5982a5b1a45e09912c910e9bbf
which is stated to be for a recent gcc change, but it does seem to
have to do with creation of flow objects ...

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] pgstat documentation tables

2012-02-27 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 18:00, Tom Lane  wrote:
> Robert Haas  writes:
>> On Mon, Feb 27, 2012 at 11:32 AM, Magnus Hagander  
>> wrote:
>>> On Mon, Feb 27, 2012 at 14:36, Robert Haas  wrote:
 Yeah, that's one thing I don't like about what you actually did,
 either - it made some of the tables much wider.
>
>>> Uh, can you give me an example of one? While they will all become
>>> wider if you actually need to, I don't see any of those tables as wide
>>> enough to cause any trouble that automatic linebreaks don't fix?
>
>> It's not so much that they become wide enough to line-wrap; it's just
>> that it's harder to read.
>
> Something else to keep in mind is that PDF-format docs are not terribly
> forgiving of wide tables --- have you looked at what these look like in
> PDF?

I have not, and I can't seem to build them:
openjade  -D . -D . -c
/usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d
./stylesheet.dsl -t tex -V tex-backend -i output-print -i
include-index -V texpdf-output -V '%paper-type%'=A4 -o
postgres-A4.tex-pdf postgres.sgml
openjade:./stylesheet.dsl:678:2:E: flow object not accepted by port;
only display flow objects accepted
make: *** [postgres-A4.tex-pdf] Segmentation fault
make: *** Deleting file `postgres-A4.tex-pdf'


does that work for others, or did we break something globally in it?

I'm not sure I've ever tried to built it on Ubuntu 12.10 before, so it
could be that..

(and same error on -US)

-- 
 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] pgstat documentation tables

2012-02-27 Thread Tom Lane
Robert Haas  writes:
> On Mon, Feb 27, 2012 at 11:32 AM, Magnus Hagander  wrote:
>> On Mon, Feb 27, 2012 at 14:36, Robert Haas  wrote:
>>> Yeah, that's one thing I don't like about what you actually did,
>>> either - it made some of the tables much wider.

>> Uh, can you give me an example of one? While they will all become
>> wider if you actually need to, I don't see any of those tables as wide
>> enough to cause any trouble that automatic linebreaks don't fix?

> It's not so much that they become wide enough to line-wrap; it's just
> that it's harder to read.

Something else to keep in mind is that PDF-format docs are not terribly
forgiving of wide tables --- have you looked at what these look like in
PDF?

regards, tom lane

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


Re: [HACKERS] [PATCH] Cleanup: use heap_open/heap_close consistently

2012-02-27 Thread Tom Lane
Robert Haas  writes:
> I'm almost inclined to think that
> we should be trying to get rid of heap_open() altogether; there are
> already plenty of places that assume that opening the relation is as
> good as opening the heap, so I don't think there'd be any real loss of
> abstraction.

Or, perhaps, restrict it to open actual heaps (ie, relkind 'r')?

I think that if you count, you'll find the vast majority of heap_open
calls are really opening system catalogs.  So I'd just as soon have
a relkind check there for sanity's sake, not to mention that renaming
them all creates a lot of unnecessary code churn.

IMO it would be sensible for heap_open to insist on a heap, index_open
to insist on an index, and for anything else, use relation_open and
BYO relkind check.  There are a few common patterns (eg "does relation
have storage") that we should abstract somehow, but it might be better
to provide separate relkind-check routines than to invent xxx_open.

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] overriding current_timestamp

2012-02-27 Thread David E. Wheeler
On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> Sorry, starting at slide 480.
> 
> That presentation only goes to slide 394.

Crimony, sorry, this presentation:

  http://www.slideshare.net/justatheory/test-drivern-database-development

Note that I put pg_catalog at the end of the search_path, so that my mocked 
function will be found before it gets found in pg_catalog. If you don't add it 
to the end, it's implicitly the first item in the search path.

Best,

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


Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread David E. Wheeler
On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> I create a "mock" schema, add the function to it, and then put it in the 
>> search_path ahead of pg_catalog.
> 
> That doesn't work for current_timestamp and similar built-in functions,
> because they are always mapped to the pg_catalog schema.

I use it for NOW() all the time.

David


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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 11:32 AM, Magnus Hagander  wrote:
> On Mon, Feb 27, 2012 at 14:36, Robert Haas  wrote:
>> On Mon, Feb 27, 2012 at 5:22 AM, Magnus Hagander  wrote:
>>> The problem with a separate column is that it makes the table very
>>> wide (some of those functions have very long name).
>>
>> Yeah, that's one thing I don't like about what you actually did,
>> either - it made some of the tables much wider.
>
> Uh, can you give me an example of one? While they will all become
> wider if you actually need to, I don't see any of those tables as wide
> enough to cause any trouble that automatic linebreaks don't fix?

It's not so much that they become wide enough to line-wrap; it's just
that it's harder to read.  For example, table 27-5,
pg_stat_database_conflicts view, is now about twice the width that it
was before, and the additional sentence is mostly junk, because 95% of
people reading this won't care a whit.  Plus, for those who do care,
the phrasing of the sentence (this value can also be returned
directly...) isn't really altogether clear, at least IMHO.

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

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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 14:36, Robert Haas  wrote:
> On Mon, Feb 27, 2012 at 5:22 AM, Magnus Hagander  wrote:
>> The problem with a separate column is that it makes the table very
>> wide (some of those functions have very long name).
>
> Yeah, that's one thing I don't like about what you actually did,
> either - it made some of the tables much wider.

Uh, can you give me an example of one? While they will all become
wider if you actually need to, I don't see any of those tables as wide
enough to cause any trouble that automatic linebreaks don't fix?


>>> sure.  Or maybe we could have a separate table that just gives the
>>> equivalences between stats table-column pairs and functions.  Of those
>>> ideas, I think I like "separate table in the same column" the best.
>>
>> That one would at least work. You mean basically:
>>
>> |pg_stat_database.xact_commit|pg_stat_get_db_xact_commit|
>> |pg_stat_database.xact_rollback|pg_stat_get_db_xact_rollback|
>>
>> etc etc for each column/function, right?
>
> Yeah.

Ok, I'll see if I can put something like that together and see what it
looks like. Unless somebody else objects to that way?


>>> Also, I wonder if we should promote section 27.2.2.1. Other Statistics
>>> Functions to 27.2.3.
>>
>> I was considering that, but given that 27.2.2 is "viewing statistics",
>> it does seem like a sub-section to that.. Though maybe if we make the
>> lis tof views into their own section *as well*, so we have one sectoin
>> for "how to view it", one for "views" and one for "other functions" it
>> would make more sense.
>
> My thought was that a good half of those "other statistics functions"
> don't have all that much to do with viewing anything, so the logical
> argument for that section to need to be under "viewing statistics"
> seems a bit weak.

True.

-- 
 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] Cleanup: use heap_open/heap_close consistently

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 5:45 AM, Marti Raudsepp  wrote:
> Here's a tiny cleanup: currently get_tables_to_cluster uses
> heap_open() to open the relation, but then closes it with
> relation_close(). Currently relation_close=heap_close, but it seems
> like good idea to be consistent -- in case these functions need to
> diverge in the future.

I'm inclined to fix this in the opposite way as what you've proposed:
replace heap_open() with relation_open(), rather than relation_close()
with heap_close().  The only thing heap_open() does that
relation_open() doesn't do is check the relkind, which is superfluous
here anyway; and if the check weren't superfluous it would most likely
be wrong, because heap_open rejects only some, not all, of the things
that aren't heaps.  During some of the DDL cleanup that I've been
doing during the 9.2 cycle, I've already found some cases where
careless use of heap_open rather than or in addition to an explicit
relkind check led to crappy error messages; the idea that there is
some systematic usefulness to a function that rejects indexes and
composite types (but not views, foreign tables, or sequences) doesn't
seem to be well-founded; the actual needs of people opening relations
are much more variable than that.  I'm almost inclined to think that
we should be trying to get rid of heap_open() altogether; there are
already plenty of places that assume that opening the relation is as
good as opening the heap, so I don't think there'd be any real loss of
abstraction.

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

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


Re: [HACKERS] Website stylesheet for local docs

2012-02-27 Thread Magnus Hagander
On Mon, Feb 27, 2012 at 16:20, Tom Lane  wrote:
> Magnus Hagander  writes:
>> On Mon, Feb 27, 2012 at 04:37, Robert Haas  wrote:
>>> Why not change the default?  Does anyone really prefer the bare bones
>>> doc output?
>
>> Yes, Peter made a point about preferring that back when we changed the
>> developer docs to be on the main website (how it got worse but at
>> least he could work on his local build).
>
> FWIW, I don't especially like the website style either --- it's too busy
> calling attention to itself with colored backgrounds etc.

There we go, at least two people, and people who do a lot of builds
and checks of the docs, like the current format. So I think that's a
good argument to keep the current format the default, and just add a
target like my suggestion as an *option* :-)


-- 
 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] How to know a table has been modified?

2012-02-27 Thread Tatsuo Ishii
Are you suggesting log_statement? I don't think it's a solution by
following reasons:

1) it's slow to enable that on busy systems
2) tables affected by cascading delete/update/drop is not logged in
   PostgreSQL log
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> check the log of postgresql, there you can take the table name and the date 
> of the modification
> 
> 
> Ing. Lennin Caro Pérez
> 
> Usuario:GNU/LINUX
> 
> PHP Developer
> 
> PostgreSQL DBA
> 
> Oracle DBA
> 
> Linux  counter id 474393
> 
> --- On Mon, 2/27/12, Tatsuo Ishii  wrote:
> 
> From: Tatsuo Ishii 
> Subject: Re: [HACKERS] How to know a table has been modified?
> To: kevin.gritt...@wicourts.gov
> Cc: pgsql-hackers@postgresql.org
> Date: Monday, February 27, 2012, 12:04 PM
> 
>>> For TRIGGER, I cannot thinking of any way. Any idea will be
>>> welcome.
>>  
>> It would require creating "cooperating" triggers in the database and
>> having a listener, but you might consider the
>> triggered_change_notifications() trigger function included in 9.2. 
>> It works at least as far back as 9.0; I haven't tried it any further
>> back.
> 
> Thanks for the info. It's a little bit overkill for my purpose though.
> (on busy systems, the notification would be too frequent).
> 
> I would think that creating a small routine periodically consults
> pg_stat_all_tables view and records the last update datetime for each
> table (unfortunately the view does not have last modification date).
> --
> 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

-- 
Sent 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] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Florian Weimer
* Tom Lane:

> Hannes Frederic Sowa  writes:
>> As with recent changes to `standard_conforming_strings' the paragraph 
>> about backslash escaping in the description of `LIKE' is only confusing. 
>> Thus I attached a patch to remove it.
>
> The para is still relevant if you don't have standard_conforming_strings
> on.

And if you aren't using parametrized queries.  The old text is a bit
misleading even with standard_conforming_strings set to off.  It is
technically correct because it refers to the SQL statement parser which
is run on parameters, but this seems a pretty fine distinction.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

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


Re: [HACKERS] WARNING: concurrent insert in progress within table "resource"

2012-02-27 Thread Pavel Stehule
yes

2012/2/27 Robert Haas :
> On Sun, Feb 26, 2012 at 10:04 AM, Pavel Stehule  
> wrote:
>> Hello
>>
>> I tested creating some larger indexes
>>
>> There was a warning:
>>
>> postgres=# CREATE INDEX idx_resource_name ON resource (name, tid);
>> WARNING:  concurrent insert in progress within table "resource"
>>
>> I am sure so there was only one active session - so this warning is strange.
>>

sure

I tried generate data from
http://archives.postgresql.org/pgsql-performance/2012-02/msg00210.php
example

I used pg from yesterday GIT repository

and my configuration was

shared buffers 500MB
maintenance_work_mem 200MB
wal_buffers 64MB
checkpoint_segments 32


>>
>> postgres=# select version();
>>                                                 version
>> ──
>>  PostgreSQL 9.2devel on i686-pc-linux-gnu, compiled by gcc (GCC) 4.5.1
>> 20100924 (Red Hat 4.5.1-4), 32-bit
>> (1 row)
>>
>> Regards
>>
>> Pavel
>
> That seems bad.  But can you provide any more details about how to reproduce 
> it?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

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


Re: [HACKERS] [PATCH] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Tom Lane
Hannes Frederic Sowa  writes:
> As with recent changes to `standard_conforming_strings' the paragraph 
> about backslash escaping in the description of `LIKE' is only confusing. 
> Thus I attached a patch to remove it.

The para is still relevant if you don't have standard_conforming_strings
on.  It could probably use work but I don't think simply removing it
is appropriate.

regards, tom lane

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


Re: [HACKERS] [PATCH] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 10:17 AM, Hannes Frederic Sowa  wrote:
> As with recent changes to `standard_conforming_strings' the paragraph about
> backslash escaping in the description of `LIKE' is only confusing. Thus I
> attached a patch to remove it.

I think I agree with removing this paragraph; it made sense when
standard_conforming_strings=off was the default, but that's not so
anymore.  We could come up with some alternative text to insert here
but I think that might be unnecessarily long-winded.

Other opinions?

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

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


Re: [HACKERS] WARNING: concurrent insert in progress within table "resource"

2012-02-27 Thread Robert Haas
On Sun, Feb 26, 2012 at 10:04 AM, Pavel Stehule  wrote:
> Hello
>
> I tested creating some larger indexes
>
> There was a warning:
>
> postgres=# CREATE INDEX idx_resource_name ON resource (name, tid);
> WARNING:  concurrent insert in progress within table "resource"
>
> I am sure so there was only one active session - so this warning is strange.
>
>
> postgres=# select version();
>                                                 version
> ──
>  PostgreSQL 9.2devel on i686-pc-linux-gnu, compiled by gcc (GCC) 4.5.1
> 20100924 (Red Hat 4.5.1-4), 32-bit
> (1 row)
>
> Regards
>
> Pavel

That seems bad.  But can you provide any more details about how to reproduce it?

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

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


Re: [HACKERS] Website stylesheet for local docs

2012-02-27 Thread Tom Lane
Magnus Hagander  writes:
> On Mon, Feb 27, 2012 at 04:37, Robert Haas  wrote:
>> Why not change the default?  Does anyone really prefer the bare bones
>> doc output?

> Yes, Peter made a point about preferring that back when we changed the
> developer docs to be on the main website (how it got worse but at
> least he could work on his local build).

FWIW, I don't especially like the website style either --- it's too busy
calling attention to itself with colored backgrounds etc.

regards, tom lane

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


[HACKERS] [PATCH] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Hannes Frederic Sowa

Hi!

As with recent changes to `standard_conforming_strings' the paragraph 
about backslash escaping in the description of `LIKE' is only confusing. 
Thus I attached a patch to remove it.


Greetings,

  Hannes

--
Hannes Sowa   
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e8e637b..4b582f7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3383,18 +3383,6 @@ cast(-44 as bit(12))   11010100

 

-Note that the backslash already has a special meaning in string literals,
-so to write a pattern constant that contains a backslash you must write two
-backslashes in an SQL statement (assuming escape string syntax is used, see
-).  Thus, writing a pattern that
-actually matches a literal backslash means writing four backslashes in the
-statement.  You can avoid this by selecting a different escape character
-with ESCAPE; then a backslash is not special to
-LIKE anymore. (But backslash is still special to the
-string literal parser, so you still need two of them to match a backslash.)
-   
-
-   
 It's also possible to select no escape character by writing
 ESCAPE ''.  This effectively disables the
 escape mechanism, which makes it impossible to turn off the

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


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

2012-02-27 Thread Lennin Caro
check the log of postgresql, there you can take the table name and the date of 
the modification


Ing. Lennin Caro Pérez

Usuario:GNU/LINUX

PHP Developer

PostgreSQL DBA

Oracle DBA

Linux  counter id 474393

--- On Mon, 2/27/12, Tatsuo Ishii  wrote:

From: Tatsuo Ishii 
Subject: Re: [HACKERS] How to know a table has been modified?
To: kevin.gritt...@wicourts.gov
Cc: pgsql-hackers@postgresql.org
Date: Monday, February 27, 2012, 12:04 PM

>> For TRIGGER, I cannot thinking of any way. Any idea will be
>> welcome.
>  
> It would require creating "cooperating" triggers in the database and
> having a listener, but you might consider the
> triggered_change_notifications() trigger function included in 9.2. 
> It works at least as far back as 9.0; I haven't tried it any further
> back.

Thanks for the info. It's a little bit overkill for my purpose though.
(on busy systems, the notification would be too frequent).

I would think that creating a small routine periodically consults
pg_stat_all_tables view and records the last update datetime for each
table (unfortunately the view does not have last modification date).
--
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-27 Thread Sandro Santilli
On Sun, Feb 26, 2012 at 09:50:04PM -0500, Robert Haas wrote:
> On Sun, Feb 26, 2012 at 10:36 AM, Peter Eisentraut  wrote:
> > On lör, 2012-02-25 at 14:21 +0100, Christoph Berg wrote:
> >> 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/...'.
> >
> > Yeah, of course, the extension path is not related to the data
> > directory.  So we do need some kind of path setting, just like
> > dynamic_library_path.
> 
> That logic seems sound to me, so +1.

+1 here as well. I may attempt to produce a patch if this gets consensus.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


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

2012-02-27 Thread Sandro Santilli
On Fri, Feb 24, 2012 at 08:21:05PM +0200, Peter Eisentraut wrote:
> On fre, 2012-02-24 at 17:26 +0100, Sandro Santilli wrote:
> > We don't initdb with PostGIS regression testing framework
> > but I've considered doing it for this specific case and it stroke me
> > that even then we couldn't control SHAREDIR.
> 
> I would always create a new instance using initdb for test runs.  It's
> easy and avoids all these problems.

Doesn't avoid the SHAREDIR problem, that's what I'm saying.
SHAREDIR is a compile-time setting in PostgreSQL.

--strk;

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


Re: [HACKERS] Patch to allow domains over composite types

2012-02-27 Thread Yeb Havinga

On 2012-02-27 12:49, Asif Rehman wrote:

Hi Yeb Havinga,

I was digging archives to see anyone worked on supporting domain's 
over composite type and found your patch, but that was pulled back. 
According to commitfest comments it needs some more work...


There were some issues with using the domains from pl/pgsql, which could 
probably made to work, but I didn't investigate it because at the time 
the use case for which is was needed was solved in a different way.


Are you going to submit the updated patch?


There is no updated patch, sorry.

regards,
Yeb


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


Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread Pavel Stehule
2012/2/27 Alvaro Herrera :
>
> Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:
>>
>> 2012/2/27 Alvaro Herrera :
>> >
>> > Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
>> > -0300 2012:
>> >> On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
>> >> > Hello
>> >> >
>> >> > I rechecked Depesz's article -
>> >> > http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
>> >> >
>> >> > The behave of current HEAD is different than behave described in 
>> >> > article.
>> >> >
>> >> > "alter table a validate constraint a_a_check" needs a access exclusive
>> >> > locks and blocks table modification - I tested inserts.
>> >
>> >> yes, looks like we have revert to access exclusive lock:
>> >
>> > See commits
>> > 2c3d9db56d5d49bdc777b174982251c01348e3d8
>> > and
>> > a195e3c34f1eeb6a607c342121edf48e49067ea9
>>
>>  this block a sense of NOT VALIDATE constraints.
>
> Yeah :-(
>
>> Is it final behave or will be fixed on 9.2?
>
> It's final for 9.2 AFAIK.  It's supposed to get fixed during the 9.3
> timeframe.

ok

thank you for info

Pavel

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

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


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

2012-02-27 Thread 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] VACUUM ANALYZE is faster than ANALYZE?

2012-02-27 Thread 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] check constraint validation takes access exclusive locks

2012-02-27 Thread Alvaro Herrera

Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:
> 
> 2012/2/27 Alvaro Herrera :
> >
> > Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
> > -0300 2012:
> >> On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
> >> > Hello
> >> >
> >> > I rechecked Depesz's article -
> >> > http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
> >> >
> >> > The behave of current HEAD is different than behave described in article.
> >> >
> >> > "alter table a validate constraint a_a_check" needs a access exclusive
> >> > locks and blocks table modification - I tested inserts.
> >
> >> yes, looks like we have revert to access exclusive lock:
> >
> > See commits
> > 2c3d9db56d5d49bdc777b174982251c01348e3d8
> > and
> > a195e3c34f1eeb6a607c342121edf48e49067ea9
> 
>  this block a sense of NOT VALIDATE constraints.

Yeah :-(

> Is it final behave or will be fixed on 9.2?

It's final for 9.2 AFAIK.  It's supposed to get fixed during the 9.3
timeframe.

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

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


Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread Pavel Stehule
2012/2/27 Alvaro Herrera :
>
> Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
> -0300 2012:
>> On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
>> > Hello
>> >
>> > I rechecked Depesz's article -
>> > http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
>> >
>> > The behave of current HEAD is different than behave described in article.
>> >
>> > "alter table a validate constraint a_a_check" needs a access exclusive
>> > locks and blocks table modification - I tested inserts.
>
>> yes, looks like we have revert to access exclusive lock:
>
> See commits
> 2c3d9db56d5d49bdc777b174982251c01348e3d8
> and
> a195e3c34f1eeb6a607c342121edf48e49067ea9

 this block a sense of NOT VALIDATE constraints. Is it final behave or
will be fixed on 9.2?

Regards

Pavel



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

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


Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 5:22 AM, Magnus Hagander  wrote:
> The problem with a separate column is that it makes the table very
> wide (some of those functions have very long name).

Yeah, that's one thing I don't like about what you actually did,
either - it made some of the tables much wider.

>> sure.  Or maybe we could have a separate table that just gives the
>> equivalences between stats table-column pairs and functions.  Of those
>> ideas, I think I like "separate table in the same column" the best.
>
> That one would at least work. You mean basically:
>
> |pg_stat_database.xact_commit|pg_stat_get_db_xact_commit|
> |pg_stat_database.xact_rollback|pg_stat_get_db_xact_rollback|
>
> etc etc for each column/function, right?

Yeah.

>> Also, I wonder if we should promote section 27.2.2.1. Other Statistics
>> Functions to 27.2.3.
>
> I was considering that, but given that 27.2.2 is "viewing statistics",
> it does seem like a sub-section to that.. Though maybe if we make the
> lis tof views into their own section *as well*, so we have one sectoin
> for "how to view it", one for "views" and one for "other functions" it
> would make more sense.

My thought was that a good half of those "other statistics functions"
don't have all that much to do with viewing anything, so the logical
argument for that section to need to be under "viewing statistics"
seems a bit weak.

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

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


Re: [HACKERS] restrict modification of column values in BR triggers

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 5:35 AM, Miroslav Šimulčík
 wrote:
> is there any way to prevent role from modifing values of some columns of NEW
> row in before row triggers? I revoked insert privilege from these columns to
> ensure that only default value can be inserted, but it is still posible to
> modify values being inserted using before row triggers. I can't revoke
> trigger privilege on that table, because this role must be able to create
> triggers on this table.

No, or at least I don't think so.  If you give someone trigger
privileges on your table, that's pretty much game over.  The trigger
functions they create will run as you.

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

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


Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread Alvaro Herrera

Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 
2012:
> On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
> > Hello
> > 
> > I rechecked Depesz's article -
> > http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
> > 
> > The behave of current HEAD is different than behave described in article.
> > 
> > "alter table a validate constraint a_a_check" needs a access exclusive
> > locks and blocks table modification - I tested inserts.

> yes, looks like we have revert to access exclusive lock:

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

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

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


Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 3:50 AM, Simon Riggs  wrote:
> That isn't the case. We have evidence that the current knobs are
> hugely ineffective in some cases.
>
> Turning the bgwriter off is hardly "adjusting a setting", its
> admitting that there is no useful setting.
>
> I've suggested changes that aren't possible by tuning the current knobs.

OK, fair point.  But I don't think any of us - Greg included - have an
enormously clear idea why turning the background writer off is
improving performance in some cases.  I think we need to understand
that better before we start changing things.

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

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


Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread hubert depesz lubaczewski
On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
> Hello
> 
> I rechecked Depesz's article -
> http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
> 
> The behave of current HEAD is different than behave described in article.
> 
> "alter table a validate constraint a_a_check" needs a access exclusive
> locks and blocks table modification - I tested inserts.
> 
> Is it expected behave.
> 
> session one:
> 
> postgres=# create table a(a int);
> CREATE TABLE
> postgres=# alter table a add check (a > 0) not valid;
> ALTER TABLE
> postgres=# begin;
> BEGIN
> postgres=# alter table a validate constraint a_a_check;
> ALTER TABLE
> 
> session two:
> 
> postgres=# update a set a = 100; -- it waits to commit in session one

yes, looks like we have revert to access exclusive lock:

$ begin;
BEGIN
Time: 0.352 ms

*$ ALTER TABLE test2 ADD CHECK ( field >= 0 ) NOT VALID;
ALTER TABLE
Time: 0.662 ms

*$ select * from pg_locks where pid = pg_backend_pid();
   locktype│ database │ relation │  page  │ tuple  │ virtualxid │ 
transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │   
 mode │ granted │ fastpath
───┼──┼──┼┼┼┼───┼─┼┼──┼┼──┼─┼─┼──
 relation  │16387 │11070 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
AccessShareLock │ t   │ t
 virtualxid│   [null] │   [null] │ [null] │ [null] │ 2/174  │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
ExclusiveLock   │ t   │ t
 transactionid │   [null] │   [null] │ [null] │ [null] │ [null] │   
854 │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ ExclusiveLock   
│ t   │ f
 relation  │16387 │18653 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
AccessExclusiveLock │ t   │ f
(4 rows)

Time: 0.921 ms

Relation 18653 is table test2, of course.

*$ commit;
COMMIT

$ begin;
BEGIN
Time: 0.271 ms

*$ ALTER TABLE test2 VALIDATE CONSTRAINT test2_field_check;
ALTER TABLE
Time: 286.035 ms

*$ select * from pg_locks where pid = pg_backend_pid();
   locktype│ database │ relation │  page  │ tuple  │ virtualxid │ 
transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │   
 mode │ granted │ fastpath
───┼──┼──┼┼┼┼───┼─┼┼──┼┼──┼─┼─┼──
 relation  │16387 │11070 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
AccessShareLock │ t   │ t
 virtualxid│   [null] │   [null] │ [null] │ [null] │ 2/175  │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
ExclusiveLock   │ t   │ t
 transactionid │   [null] │   [null] │ [null] │ [null] │ [null] │   
855 │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ ExclusiveLock   
│ t   │ f
 relation  │16387 │18653 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
AccessExclusiveLock │ t   │ f
(4 rows)

Time: 0.631 ms

And it clearly shows that validation of constraint did lock the table
using AccessExclusiveLock, which kinda defeats the purpose of
INVALID/VALIDATE.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut
On tor, 2012-02-23 at 10:55 -0800, David E. Wheeler wrote:
> On Feb 23, 2012, at 10:54 AM, David E. Wheeler wrote:
> 
> > I create a "mock" schema, add the function to it, and then put it in the 
> > search_path ahead of pg_catalog. See the example starting at slide 48 on 
> > http://www.slideshare.net/justatheory/pgtap-best-practices.
> 
> Sorry, starting at slide 480.

That presentation only goes to slide 394.


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


Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut
On tor, 2012-02-23 at 10:54 -0800, David E. Wheeler wrote:
> I create a "mock" schema, add the function to it, and then put it in the 
> search_path ahead of pg_catalog.

That doesn't work for current_timestamp and similar built-in functions,
because they are always mapped to the pg_catalog schema.


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


Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-02-27 Thread Peter Geoghegan
On 27 February 2012 06:23, Tom Lane  wrote:
> I think that what Peter is on about in
> http://archives.postgresql.org/pgsql-hackers/2012-02/msg01152.php
> is the question of what location to use for the *result* of
> 'literal string'::typename, assuming that the type's input function
> doesn't complain.  Generally we consider that we should use the
> leftmost token's location for the location of any expression composed
> of more than one input token.  This is of course the same place for
> 'literal string'::typename, but not for the alternate syntaxes
> typename 'literal string' and cast('literal string' as typename).
> I'm not terribly impressed by the proposal to put in an arbitrary
> exception to that general rule for the convenience of this patch.

Now, you don't have to be. It was a mistake on my part to bring the
current user-visible behaviour into this. I don't see that there is
necessarily a tension between your position that we should blame the
leftmost token's location, and my contention that the Const "location"
field shouldn't misrepresent the location of certain Consts involved
in coercion post-analysis.

Let me put that in concrete terms. In my working copy of the patch, I
have made some more changes to the core system (mostly reverting
things that turned out to be unnecessary), but I have also made the
following change:

*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
*** coerce_type(ParseState *pstate, Node *no
*** 280,293 
newcon->constlen = typeLen(targetType);
newcon->constbyval = typeByVal(targetType);
newcon->constisnull = con->constisnull;
!   /* Use the leftmost of the constant's and coercion's locations 
*/
!   if (location < 0)
!   newcon->location = con->location;
!   else if (con->location >= 0 && con->location < location)
!   newcon->location = con->location;
!   else
!   newcon->location = location;
!
/*
 * Set up to point at the constant's text if the input routine 
throws
 * an error.
--- 280,286 
newcon->constlen = typeLen(targetType);
newcon->constbyval = typeByVal(targetType);
newcon->constisnull = con->constisnull;
!   newcon->location = con->location;
/*
 * Set up to point at the constant's text if the input routine 
throws
 * an error.
*

This does not appear to have any user-visible effect on caret position
for all variations in coercion syntax, while giving me everything that
I need. I had assumed that we were relying on things being this way,
but apparently this is not the case. The system is correctly blaming
the coercion token when it finds the coercion is at fault, and the
const token when it finds the Const node at fault, just as it did
before. So this looks like a case of removing what amounts to dead
code.

> Especially not when the only reason it's needed is that Peter is
> doing the fingerprinting at what is IMO the wrong place anyway.
> If he were working on the raw grammar output it wouldn't matter
> what parse_coerce chooses to do afterwards.

Well, I believe that your reason for preferring to do it at that stage
was that we could not capture all of the system's "normalisation
smarts", like the fact that the omission of noise words isn't a
differentiator, so we might as well not have any. This was because
much of it - like the recognition of the equivalence of explicit joins
and queries with join conditions in the where clause - occurs within
the planner. We can't have it all, so we might as well not have any.
My solution here is that we be sufficiently vague about the behaviour
of normalisation that the user has no reasonable basis to count on
that kind of more advanced reduction occurring.

I did very seriously consider hashing the raw parse tree, but I have
several practical reasons for not doing so. Whatever way you look at
it, hashing there is going to result in more code, that is more ugly.
There is no uniform parent node that I can tag with a query_id. There
has to be more modifications to the core system so that queryId value
is carried around more places and persists for longer. The fact that
I'd actually be hashing different structs at different times (that
tree is accessed through a Node pointer) would necessitate lots of
redundant code that operated on each of the very similar structs in an
analogous way. The fact is that waiting until after parse analysis has
plenty of things to recommend it, and yes, the fact that we already
have working code with extensive regression tests is one of them.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgres

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

2012-02-27 Thread Heikki Linnakangas

On 23.02.2012 18:01, Alvaro Herrera wrote:


Excerpts from Tom Lane's message of jue feb 23 12:28:20 -0300 2012:


Alvaro Herrera  writes:

Sure.  The problem is that we are allowing updated rows to be locked (and
locked rows to be updated).  This means that we need to store extended
Xmax information in tuples that goes beyond mere locks, which is what we
were doing previously -- they may now have locks and updates simultaneously.



(In the previous code, a multixact never meant an update, it always
signified only shared locks.  After a crash, all backends that could
have been holding locks must necessarily be gone, so the multixact info
is not interesting and can be treated like the tuple is simply live.)


Ugh.  I had not been paying attention to what you were doing in this
patch, and now that I read this I wish I had objected earlier.


Uhm, yeah, a lot earlier -- I initially blogged about this in August
last year:
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/

and in several posts in pgsql-hackers.


This
seems like a horrid mess that's going to be unsustainable both from a
complexity and a performance standpoint.  The only reason multixacts
were tolerable at all was that they had only one semantics.  Changing
it so that maybe a multixact represents an actual updater and maybe
it doesn't is not sane.


As far as complexity, yeah, it's a lot more complex now -- no question
about that.


How about assigning a new, real, transaction id, to represent the group 
of transaction ids. The new transaction id would be treated as a 
subtransaction of the updater, and the xids of the lockers would be 
stored in the multixact-members slru. That way the multixact structures 
wouldn't need to survive a crash; you don't care about the shared 
lockers after a crash, and the xid of the updater would be safely stored 
as is in the xmax field.


That way you wouldn't need to handle multixact wraparound, because we 
already handle xid wraparound, and you wouldn't need to make multixact 
slrus crash-safe.


Not sure what the performance implications would be. You would use up 
xids more quickly, which would require more frequent anti-wraparound 
vacuuming. And if we just start using real xids as the key to 
multixact-offsets slru, we would need to extend that a lot more often. 
But I feel it would probably be acceptable.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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


  1   2   >