Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-11-25 Thread Etsuro Fujita
Hi Hanada-san,

(2011/11/16 1:55), Shigeru Hanada wrote:
 Attached are revised version of pgsql_fdw patches.

I'm still under reviewing, so the following is not all.  I'm sorry.
estimate_costs() have been implemented to ask a remote postgres server
for the result of EXPLAIN for a remote query to get its costs such as
startup_cost and total_cost.  I think this approach is the most accurate
way to get its costs.  However, I think it would be rather costly.  And
I'm afraid of that it might work only for pgsql_fdw. Because, even if we
are able to obtain such a cost information by EXPLAINing a remote query
at a remote server where a DBMS different from postgres runs, it might
be difficult to incorporate such a cost information with the postgres
cost model due to their possible inconsistency that such a cost
information provided by the EXPLAIN command in the other DBMS might have
different meanings (or different scales) from that provided by the
EXPLAIN command in postgres.  So, I think it might be better to estimate
such costs by pgsql_fdw itself without EXPLAINing on the assumption that
a remote postgres server has the same abilities for query optimization,
which is less costly and widely applicable to the other DBMSs, while it,
of course, only works once we have statistics and/or index information
for foreign tables.  But AFAIK we eventually want to have those, so I'd
like to propose to use the proposed approach until that time.

Best regards,
Etsuro Fujita

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


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

2011-11-25 Thread Tomas Vondra
On 25 Listopad 2011, 2:44, Robert Haas wrote:
 2011/11/10 Tomas Vondra t...@fuzzy.cz:
 Is there any particular reason why there's not a backend start hook,
 executed right after a backend is initialized? I've tried a very simple
 PoC (basically just a new hook definition, called from PostgresMain(),
 see the after-logon-hook.diff (and a simple module that uses it is in
 logon.c).

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

 [ catching up on some old email ]

 I've thought of this before, but I'm not exactly clear on what the use
 cases are.

You mean for a startup hook or after logon trigger?

The startup hook is useful for initializing an extension written in C,
when the extension was loaded from postgresql.conf. If you need to perform
the initialization for each db separately (so that you can decide whether
to apply the extension to the user/database), you need to do that after
the backend starts.

The logon trigger is useful e.g. for a VPD (Virtual Private Database) the
way Oracle does it. A logon trigger is the natural place where to
initialize the application context etc.

I'm not saying there are no workarounds (different, a bit awkward
solutions) for both cases.

 The particular place where you've put this hook doesn't look right to
 me.  I think it would get re-executed after each ERROR.

Yes, I've noticed that too. It should be probably moved out of the
infinite loop.

 Also, if you're going to insist that the triggers be written in C
 (blech!) then there's not much point in including a TRY/CATCH block
 here.  Let the user do that themselves if they are so inclined; it's
 not free.

I don't insist on writing all logon triggers in C - I see those two
solutions (hook vs. trigger) rather separate, although there are tasks
that may be solved using any of them. My current need nicely matches the
startup hook, that's why I proposed only this.

The logon trigger would require much more work (catalogue, syntax, ...).

Tomas


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


Re: [HACKERS] Storing hot members of PGPROC out of the band

2011-11-25 Thread Robert Haas
On Thu, Nov 24, 2011 at 11:54 PM, Pavan Deolasee
pavan.deola...@gmail.com wrote:
 +1. These are independent patches and should be pursued like that.
 BTW, I reviewed the pgxact-v2.patch and I have no objections to that
 and it looks good to go in. Thanks Robert for making the necessary
 changes and also running the benchmark tests.

I ran some write tests with master, pgxact-v2, and
pgxact-v2+recentglobalxmin.  shared_buffers = 8GB,
maintenance_work_mem = 1GB, synchronous_commit = off,
checkpoint_segments = 300, checkpoint_timeout = 15min,
checkpoint_completion_target = 0.9, wal_writer_delay = 20ms.  Seven
five-minute runs at scale factor 100 for each configuration.

Here's the executive summary: On the read-only test,
recentglobalxmin.patch was only a win at the very highest concurrency
I tested (80 clients); on the read-write test, it starts to show a
benefit at much lower concurrencies (32 clients, certainly, perhaps
even 8 clients, on unlogged tables).  However, pgxact-v2.patch seems
to be a win on both read and write tests and at any concurrency level,
including the single-client case.

== 1 client, unlogged tables ==
master: low 671.861618 median 677.324867 high 765.824313 (but the
second highest was only 679.491822)
pgxact-v2: low 663.901614 median 689.496716 high 696.812065
pgxact-v2+recentglobalxmin: low 665.554342 median 685.401979 high 688.832906

== 8 clients, unlogged tables ==
master: low 4722.011063 median 4758.201239 high 4920.130891
pgxact-v2: low 4684.759859 median 4840.081663 high 4979.036845
pgxact-v2+recentglobalxmin: low 4723.743270 median 4856.513904 high 4997.528399

== 32 clients, unlogged tables ==
master: low 10878.959662 median 10901.523672 high 10934.699151
pgxact-v2: low 17944.914228 median 18060.058996 high 19281.541088
pgxact-v2+recentglobalxmin: low 18894.860512 median 19637.190567 high
19817.089456

== 80 clients, unlogged tables ==
master: low 7872.934292 median 7897.811216 high 7909.410723
pgxact-v2: low 12032.684380 median 12397.316995 high 13279.998414
pgxact-v2+recentglobalxmin: low 16964.227483 median 17801.478747 high
18107.646170

== 1 client, permanent tables ==
master: low 625.502929 median 628.442284 high 677.451660
pgxact-v2: low 636.755782 median 640.083573 high 645.273888
pgxact-v2+recentglobalxmin: low 633.320412 median 636.898945 high 637.886099

== 8 clients, permanent tables ==
master: low 4497.012143 median 4624.844801 high 4849.233268
pgxact-v2: low 4561.914897 median 4625.443111 high 4776.095552
pgxact-v2+recentglobalxmin: low 4469.742226 median 4789.249847 high 4824.033794

== 32 clients, permanent tables ==
master: low 10468.362239 median 10511.425102 high 10531.069684
pgxact-v2: low 12821.732396 median 14500.067726 high 14546.538281
pgxact-v2+recentglobalxmin: low 14907.122746 median 15129.665408 high
15186.743199

== 80 clients, permanent tables ==
master: low 7601.067552 median 7612.898321 high 7631.487355
pgxact-v2: low 11712.895410 median 12004.807309 high 12512.078569
pgxact-v2+recentglobalxmin: low 15186.695057 median 15810.452158 high
16166.272699

I don't see much reason to wait around any further on the core patch
(pgact-v2.patch) so I'll commit that now.

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

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


Re: [HACKERS] proposal: use errcontext for custom exception too

2011-11-25 Thread Robert Haas
On Fri, Nov 25, 2011 at 1:14 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
 A some option via #option or GUC has sense for lower levels like
 NOTICE or WARNING.

I think what we discussed before was adding some bit of optional
syntax to RAISE that would indicate that the user wants CONTEXT
suppressed.

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

2011-11-25 Thread Dean Rasheed
On 24 November 2011 21:50, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Excerpts from Alvaro Herrera's message of vie nov 11 00:32:33 -0300 2011:
 Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011:
 
  On 10 November 2011 23:56, Thom Brown t...@linux.com wrote:

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

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

 I have just pushed a fix for this.  Thanks for the report and sorry for
 the delay.


There is a similar problem with NOT VALID check constraints on
domains. These are still being dumped as part of the CREATE DOMAIN
statement, which is invalid syntax, so they need to be dumped
separately from the domain creation, and presumably also after any
data for tables that use them.

Regards,
Dean

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


Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-25 Thread Alvaro Herrera

Excerpts from Greg Smith's message of mié nov 16 04:37:43 -0300 2011:
 On 10/05/2011 03:02 AM, Greg Smith wrote:
  Presumably you meant to ask if this makes sense to show when cost 
  accounting isn't enabled, because the code doesn't do that right now.  
  No cost accounting, no buffer usage/write rate data as this was 
  submitted.
 
 This is done in the attached update.  I just made the page accounting 
 happen all the time, regardless of whether the costs were being 
 accumulated.  Added a read rate too, which is how fast reads happened 
 from the OS cache to shared_buffers.  Simple test case generates a 600MB 
 pgbench_accounts database and wipes out enough to take a while to clean 
 up; it needs log_autovacuum_min_duration  = 0 and then:
 
 $ createdb pgbench
 $ pgbench -i -s 10 pgbench
 $ psql -d pgbench -c delete from pgbench_accounts where aid20
 
 LOG:  automatic vacuum of table pgbench.public.pgbench_accounts: index 
 scans: 1
  pages: 0 removed, 16394 remain
  tuples: 19 removed, 640011 remain
  buffer usage: 13742 hits, 2708 misses, 1058 dirtied
  avg read rate: 3.067 MiB/s, avg write rate: 1.198 MiB/s
  system usage: CPU 0.05s/0.61u sec elapsed 6.89 sec

I was about to commit this when I noticed that the avg values may not be
all that representative of reality after all; consider that it's
computed across the whole duration of the vacuuming operation, including
the index scans ... it'd be possibly useful to keep separate timings for
the heap scan (which is likely to use I/O more quickly) from index
scans.  That way you can tune for the max, not a possibly misleading
average.  That's a much larger change though, so I'm not going to get
into it.

Does anybody else think this would be worthwhile?  If so we can stick it
into the TODO with an easy tag for someone to tackle -- seems like a
useful first project.


One funny thing in the test I did was that the buffer count might add to
a much larger amount than total disk pages:

LOG:  automatic vacuum of table alvherre.public.foo: index scans: 4
pages: 0 removed, 8850 remain
tuples: 68 removed, 132 remain
buffer usage: 14675 hits, 33857 misses, 20274 dirtied
avg read rate: 2.823 MiB/s, avg write rate: 1.690 MiB/s
system usage: CPU 1.26s/8.08u sec elapsed 93.69 sec

The table and index:

alvherre=# select relname, relpages from pg_class where relname like 'foo%';
 relname | relpages 
-+--
 foo | 8850
 foo_idx | 5487
(2 filas)

My guess is that this is roughly counting three heap scans plus the four
index scans mentioned in the log report (there were so many because I
reduced maintenance_work_mem to its 1 MB minimum):

alvherre=# select 5487 * 4 + 8850 * 3;
 ?column? 
--
48498
(1 fila)

alvherre=# select 14675 + 33857;
 ?column? 
--
48532
(1 fila)



My test case was

create table foo (a int);
insert into foo select * from generate_series (1, 200);
create index foo_idx on foo (a);
delete from foo where a % 6 in (1,2);

I then checkpointed before autovac had the chance to process the table,
just to see a higher number of pages dirtied.  Note how the number of
pages dirtied is also much higher the total number of existing pages!

I'm going to push this now anyway, thanks.

-- 
Álvaro Herrera alvhe...@commandprompt.com
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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

2011-11-25 Thread Dean Rasheed
On 25 November 2011 16:16, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 24 November 2011 21:50, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Excerpts from Alvaro Herrera's message of vie nov 11 00:32:33 -0300 2011:
 Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011:
 
  On 10 November 2011 23:56, Thom Brown t...@linux.com wrote:

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

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

 I have just pushed a fix for this.  Thanks for the report and sorry for
 the delay.


 There is a similar problem with NOT VALID check constraints on
 domains. These are still being dumped as part of the CREATE DOMAIN
 statement, which is invalid syntax, so they need to be dumped
 separately from the domain creation, and presumably also after any
 data for tables that use them.

 Regards,
 Dean


Looking back at Thom's original example, it seems odd to allow this
syntax at all:

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

It's not documented, but is currently allowed. However, since all data
subsequently added to the table is checked against the constraint, the
constraint is guaranteed to be valid, so there seems to be no point in
allowing it to be declared NOT VALID.

Regards,
Dean

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


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

2011-11-25 Thread Tom Lane
Tomas Vondra t...@fuzzy.cz writes:
 On 25 Listopad 2011, 2:44, Robert Haas wrote:
 I've thought of this before, but I'm not exactly clear on what the use
 cases are.

 The startup hook is useful for initializing an extension written in C,
 when the extension was loaded from postgresql.conf. If you need to perform
 the initialization for each db separately (so that you can decide whether
 to apply the extension to the user/database), you need to do that after
 the backend starts.

If you need that, just load the extension with local_preload_libraries.

regards, tom lane

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


Re: [HACKERS] proposal: use errcontext for custom exception too

2011-11-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Thu, Nov 24, 2011 at 12:30 PM, Pavel Stehule pavel.steh...@gmail.com 
 wrote:
 There are small issue in PL/pgSQL and custom exceptions. Custom
 exception doesn't set a CONTEXT field. I propose change this behave
 for WARNING or EXCEPTION level. The goal is same behave for custom
 exception and builtin exception and it can help to identify a RAISE
 statement that is responsible to exception.

 That seems completely arbitrary.  I think we discussed before
 providing an option to allow the user to control this, which seems
 better than implementing some hardcoded rule that may or may not be
 what a given user wants.

Note also that the current behavior *is* what people want; at least,
we have seen no field complaints about the lack of first-level CONTEXT
for RAISE notices, and plenty of complaints from people who think
there's still too much cruft automatically attached to RAISE output.
If anything, what's been requested is a way to suppress even more
context, not a policy decision to force more of 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] proposal: use errcontext for custom exception too

2011-11-25 Thread Pavel Stehule
2011/11/25 Tom Lane t...@sss.pgh.pa.us:
 Robert Haas robertmh...@gmail.com writes:
 On Thu, Nov 24, 2011 at 12:30 PM, Pavel Stehule pavel.steh...@gmail.com 
 wrote:
 There are small issue in PL/pgSQL and custom exceptions. Custom
 exception doesn't set a CONTEXT field. I propose change this behave
 for WARNING or EXCEPTION level. The goal is same behave for custom
 exception and builtin exception and it can help to identify a RAISE
 statement that is responsible to exception.

 That seems completely arbitrary.  I think we discussed before
 providing an option to allow the user to control this, which seems
 better than implementing some hardcoded rule that may or may not be
 what a given user wants.

 Note also that the current behavior *is* what people want; at least,
 we have seen no field complaints about the lack of first-level CONTEXT
 for RAISE notices, and plenty of complaints from people who think
 there's still too much cruft automatically attached to RAISE output.
 If anything, what's been requested is a way to suppress even more
 context, not a policy decision to force more of it.


People usually don't like verbose output in interactive mode in
console. CONTEXT for RAISE NOTICE is not necessary.  If you have a
small functions, then CONTEXT for RAISE EXCEPTION is not necessary
too. But if you have a functions with hundreds lines, then more
informations about origin of exception is welcome. There is workaround
- with one statement function (RAISE stmt wrapper) I have a expected
behave - but it's not clean RAISE EXCEPTION 'some message' is more
readable than PERFORM elog('some message', ..) and log is not too
readable too.

postgres=# SELECT yyy();
CONTEXT:  SQL statement SELECT xxx()
PL/pgSQL function yyy line 3 at PERFORM

I can understand to motivation decrease verbosity, but there is clean
request simply identification a source of exception (exception, not
notification). Some RAISE stmt option should be - but for NOTICE
level NO_CONTEXT is optimal, and for EXCEPTION NO_CONTEXT is
suboptimal. It has sense just for WARNING level.

Regards

Pavel

-- 
Sent 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] optional cleaning queries stored in pg_stat_statements

2011-11-25 Thread Jeff Janes
On Sat, Nov 5, 2011 at 8:42 PM, Tomas Vondra t...@fuzzy.cz wrote:
 Dne 6.11.2011 03:16, Peter Geoghegan napsal(a):
 2011/11/6 Tomas Vondra t...@fuzzy.cz:
 Hi everyone,

 The patch implements a simple cleaning that replaces the parameter
 values with generic strings - e.g. numbers are turned to :n, so the
 queries mentioned above are turned to

   SELECT abalance FROM pgbench_accounts WHERE aid = :n

 and thus tracked as a single query in pg_stat_statements.

 I'm a couple of days away from posting a much better principled
 implementation of pg_stat_statements normalisation. To normalise, we
 perform a selective serialisation of the query tree, which is hashed.

 OK, my patch definitely is not the only possible and if there's a way to
 get more info from the planner, the results surely might be better. My
 goal was to provide a simple patch that solves the problem better then
 I'll be more than happy to remove mine.

Hi Tomas,

Given Peter's patch on the same subject, should we now mark this one
as rejected in the commitfest app?
https://commitfest.postgresql.org/action/patch_view?id=681

Thanks,

Jeff

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


Re: [HACKERS] pg_upgrade relation OID mismatches

2011-11-25 Thread Bruce Momjian
Bruce Momjian wrote:
 Bruce Momjian wrote:
  Heikki Linnakangas wrote:
   On 24.11.2011 07:01, Bruce Momjian wrote:
Bruce Momjian wrote:
OK, that is a heap table.  My only guess is that the heap is being
created without binary_upgrade_next_heap_pg_class_oid being set.
Looking at the code, I can't see how the heap could be created without
this happening.  Another idea is that pg_dumpall isn't output the 
proper
value, but again, how is this data type different from the others.
   
I have reproduced the failure and found it was code I added to pg_dump
back in 9.0.  The code didn't set the index oid for exclusion constraint
indexes.  Once these were added to the regression tests for range types
recently, pg_upgrade threw an error.
   
My assumption is that anyone trying to use an exclusion constraint with
pg_upgrade will get the same type of error.
   
Patch attached.  Should it be backpatched to 9.0 and 9.1?
   
   If I understood correctly, pg_upgrade of a database with exclusion 
   constraints won't work without this patch? In that case, it should be 
   backpatched.
  
  Yes, that is my guess.  I will test it today or tomorrow.  I am
  surprised we had _no_ exclusion constraint tests in the regression tests
  until now.
 
 I do see EXCLUDE constraints in 9.0, so I need to do some more research:
 
   CREATE TABLE circles (
 c1 CIRCLE,
 c2 TEXT,
 EXCLUDE USING gist
   (c1 WITH , (c2::circle) WITH )
   WHERE (circle_center(c1)  '(0,0)')
   );
 
 It seems it is only the range-type EXCLUDE constraints that are causing
 a problem.

OK, it turns out that exclusion contraints used in pre-9.2 regression
tests were deleted before the regression tests finished, which means
they were not tested by me.  (This might be a good reason _not_ to have
the regression tests removing objects before the scripts exit.)

I have applied the posted patch to 9.0, 9.1, and 9.2.  The good news is
that no one reported this failure in the field, and it would have
generated an error, rather than silently failing.

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

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

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


Re: [HACKERS] pg_upgrade relation OID mismatches

2011-11-25 Thread Alvaro Herrera

Excerpts from Bruce Momjian's message of vie nov 25 17:05:09 -0300 2011:

 OK, it turns out that exclusion contraints used in pre-9.2 regression
 tests were deleted before the regression tests finished, which means
 they were not tested by me.  (This might be a good reason _not_ to have
 the regression tests removing objects before the scripts exit.)

Yeah, I vote for most/all tests keeping their objects instead of
dropping them, where sensible.  Also, some tests use a single table name
and create it and drop it just to create it again later with different
properties (I think the alter_table test is like this).  We should
avoid that.

-- 
Álvaro Herrera alvhe...@commandprompt.com
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] pg_dump vs malloc

2011-11-25 Thread Bruce Momjian
Bruce Momjian wrote:
 I developed the attached patch to handle this.  I moved the catalog code
 from common.c into dumpcatalog.c, so there are just memory routines now
 in common.c.  I created new memory routines in pg_dumpall.c because
 there is no AH structure in pg_dumpall.c.  I then modified all the calls
 to use the new routines, and removed the NULL return checks that were no
 longer necessary.

Applied.

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

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

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


Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-11-25 Thread Jeff Janes
On Mon, Oct 24, 2011 at 7:46 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:

 Thanks! Here's an updated version of the patch, fixing that, and all the
 other issues pointed out this far.

 I extracted the code that sets oid and tuple headers, and invokes the
 toaster, into a new function that's shared by heap_insert() and
 heap_multi_insert(). Tom objected to merging heap_insert() and
 heap_multi_insert() into one complicated function, and I think he was right
 on that, but sharing this code to prepare a tuple still makes sense. IMHO it
 makes heap_insert() slightly more readable too.

Hi Heikki,

Thanks for this patch.  Doing bulk copies in parallel for me is now
limited by the IO subsystem rather than the CPU.

This patch, commit number d326d9e8ea1d69, causes fillfactor to be
ignored for the copy command.  Is this acceptable collateral damage?

This can be seen by using pgbench -i -s50 -F50 to create the table
combined with and select
pg_size_pretty(pg_table_size('pgbench_accounts')), or by using the
relation_free_space extension to pageinspect proposed elsewhere.

Thanks,

Jeff

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


Re: [HACKERS] xpath_table

2011-11-25 Thread Bruce Momjian
Robert Haas wrote:
 On Tue, Nov 22, 2011 at 6:19 PM, magti...@juno.com magti...@juno.com wrote:
  I noticed in the Postgres 9.1 manual that the xml2 module has been removed.
 
 No, it hasn't.  We talked about it, but we didn't do it.  We're still
 planning to remove it three releases ago.
 
 http://www.postgresql.org/docs/9.1/static/xml2.html

The hold-up has been that the built-in xml type still does not do
everything the old xml2 module did.  I don't remember the details.

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

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

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


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

2011-11-25 Thread Alvaro Herrera

Excerpts from Dean Rasheed's message of vie nov 25 13:16:29 -0300 2011:

 There is a similar problem with NOT VALID check constraints on
 domains. These are still being dumped as part of the CREATE DOMAIN
 statement, which is invalid syntax, so they need to be dumped
 separately from the domain creation, and presumably also after any
 data for tables that use them.

Doh, thanks.  I just pushed a patch that should close this bug.  I
didn't do anything in particular to ensure that the constraint is dumped
after tables that use it, but pg_dump does it that way anyway -- I think
the reason is that separate constraints are always loaded at the end of
the dump, after all data has been loaded.  (AFAIK FKs have always worked
like this, because it's much faster to load them after the data has been
imported.)

-- 
Álvaro Herrera alvhe...@commandprompt.com
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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

2011-11-25 Thread Alvaro Herrera

Excerpts from Dean Rasheed's message of vie nov 25 13:45:34 -0300 2011:

 Looking back at Thom's original example, it seems odd to allow this
 syntax at all:
 
 CREATE TABLE a (
num integer,
CONSTRAINT meow CHECK ((num  20)) NOT VALID
 );
 
 It's not documented, but is currently allowed. However, since all data
 subsequently added to the table is checked against the constraint, the
 constraint is guaranteed to be valid, so there seems to be no point in
 allowing it to be declared NOT VALID.

Hah ... interesting.  Not sure it's worth fussing about this.  If the
user shoots himself in the foot by declaring an unvalidated constraint,
which is not even documented, are we really at fault?

-- 
Álvaro Herrera alvhe...@commandprompt.com
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] Inserting heap tuples in bulk in COPY

2011-11-25 Thread Jeff Janes
On Fri, Nov 25, 2011 at 12:53 PM, Jeff Janes jeff.ja...@gmail.com wrote:

 Hi Heikki,

 Thanks for this patch.  Doing bulk copies in parallel for me is now
 limited by the IO subsystem rather than the CPU.

 This patch, commit number d326d9e8ea1d69, causes fillfactor to be
 ignored for the copy command.  Is this acceptable collateral damage?

Having looked into it a little bit, I think this might be an acceptable fix.

Cheers,

Jeff


bulkwal_copy_1.patch
Description: Binary data

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


[HACKERS] Re: Add pg_upgrade ENABLE_SAME_CATVERSION_UPGRADES macro for testing to

2011-11-25 Thread Peter Eisentraut
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5df1403b0f2b44235c8f401bd49dab9a8cf6bf90

This patch doesn't do what it says.  What it does is allow downgrades.
Upgrades to the same catversion already worked.


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


[HACKERS] separate initdb -A options for local and host

2011-11-25 Thread Peter Eisentraut
I think it would be useful to have separate initdb -A options for local
and host entries.  In 9.1, we went out of our way to separate the peer
and ident methods, but we have moved the confusion into the initdb -A
option, where ident sometimes means peer, and peer sometimes means
ident.  Moreover, having separate options would allow what I think
would be a far more common use case, namely having local peer and host
something other than ident, such as md5.

I'm thinking, we could keep the existing -A option, but add long options
such as --auth-local and --auth-host, to specify more detail.



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


[HACKERS] disable prompting by default in createuser

2011-11-25 Thread Peter Eisentraut
I propose that we change createuser so that it does not prompt for
anything by default.  We can arrange options so that you can get prompts
for whatever is missing, but by default, a call to createuser should
just run CREATE USER with default options.  The fact that createuser
issues prompts is always annoying when you create setup scripts, because
you have to be careful to specify all the necessary options, and they
are inconsistent and different between versions (although the last
change about that was a long time ago), and the whole behavior seems
contrary to the behavior of all other utilities.  I don't think there'd
be a real loss by not prompting by default; after all, we don't really
want to encourage users to create more superusers, do we?  Comments?



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


[HACKERS] Re: Add pg_upgrade ENABLE_SAME_CATVERSION_UPGRADES macro for testing to

2011-11-25 Thread Bruce Momjian
Peter Eisentraut wrote:
 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5df1403b0f2b44235c8f401bd49dab9a8cf6bf90
 
 This patch doesn't do what it says.  What it does is allow downgrades.
 Upgrades to the same catversion already worked.

Oh, you are correct.  Macro patch removed.

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

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

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


Re: [HACKERS] disable prompting by default in createuser

2011-11-25 Thread Robert Haas
On Fri, Nov 25, 2011 at 6:28 PM, Peter Eisentraut pete...@gmx.net wrote:
 I propose that we change createuser so that it does not prompt for
 anything by default.  We can arrange options so that you can get prompts
 for whatever is missing, but by default, a call to createuser should
 just run CREATE USER with default options.  The fact that createuser
 issues prompts is always annoying when you create setup scripts, because
 you have to be careful to specify all the necessary options, and they
 are inconsistent and different between versions (although the last
 change about that was a long time ago), and the whole behavior seems
 contrary to the behavior of all other utilities.  I don't think there'd
 be a real loss by not prompting by default; after all, we don't really
 want to encourage users to create more superusers, do we?  Comments?

+1.  I've never found the current behavior to be other than an
annoyance.  I suggest --interactive or something like that to enable
prompting.

-- 
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] Measuring relation free space

2011-11-25 Thread Jeff Janes
On Mon, Nov 14, 2011 at 2:02 PM, Jaime Casanova ja...@2ndquadrant.com wrote:
 On Wed, Nov 9, 2011 at 7:58 AM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:

 Excerpts from Jaime Casanova's message of mar nov 08 18:12:25 -0300 2011:
 On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander mag...@hagander.net wrote:
 
  Looks pretty useful.

 thanks for the review, attached is a new version of it

 Note that AFAIK you shouldn't update the 1.0 extension script ... you
 have to create a 1.1 version (or whatever), update the default version
 in the control file, and create an 1.0--1.1 script to upgrade from the
 original version to 1.1.


 good point... fixed that...
 a question i have is: are we supposed to let the old script (1.0) around?

Since the syntax to install a non-default version is supported, I
would argue the old script should be kept.
CREATE extension pageinspect with version 1.0

This patch applies and builds cleanly.  It works either for CREATE
EXTENSION from scratch, or for updating from the prior version with
ALTER EXTENSION..UPDATE.

It seems to be using the buffer ring strategy as advertised.

It reports space that is free exclusively for updates as being free.
In other words, it considers space free even if it is reserved against
inserts in deference to fillfactor.  This is in contrast to
pg_freespace, which only reports space available for inserts as being
available.  I think this is reasonable behavior, but it is subtle and
should perhaps be documented.  (Is it common to use fill factors other
than the default in the first place?  Do we assume that people using
fillfactor are sophisticated enough not to shot themselves in the
foot?)

As noted by Greg, the documentation calls it total amount of free
free [sic] space when that is not what is reported.  However, it also
is not reporting a percentage, but rather a decimal fraction.  The
reported value should be multiplied by 100, especially if the docs are
going to be changed to call it a percentage.

Unless I am missing something, all indexes are handled via a procedure
designed for BTree indices, GetBTRelationFreeSpace.  I don't know
that the ultimate behavior of this is wrong, but it seems unusual.  If
I get some more time, I will try to explore what is actually going on
when called on other types of indexes.

I have no insight into how to handle toast tables, or non-superusers.
I had thought that toast tables had names of their own which could be
used, but I could not figure out how to do that.

Even if there are other ways to get approximately the same
information, this functionality seems to be a natural thing to have in
the pageinspect extension.

Cheers,

Jeff

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


Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-25 Thread Robert Haas
On Fri, Nov 25, 2011 at 11:39 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 I'm going to push this now anyway, thanks.

This patch adds a count of the number of buffers dirtied to VACUUM,
but it strikes me that it would be useful to add similar tracking to
pgBufferUsage.  Attached is a patch for that.  You can see the new
counters through pg_stat_statements or with EXPLAIN (ANALYZE,
BUFFERS).  This is useful because the number of buffers that a query
*writes* doesn't necessarily have much to do with anything - it may
end up writing buffers dirtied by other queries while being read-only
itself, or conversely it may not write anything at all even though it
dirties quite a bit.

Thoughts?  Comments?  Objections?

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


dirty_buffers.patch
Description: Binary data

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


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

2011-11-25 Thread Jaime Casanova
On Fri, Nov 25, 2011 at 4:28 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 Excerpts from Dean Rasheed's message of vie nov 25 13:45:34 -0300 2011:

 Looking back at Thom's original example, it seems odd to allow this
 syntax at all:

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

 It's not documented, but is currently allowed. However, since all data
 subsequently added to the table is checked against the constraint, the
 constraint is guaranteed to be valid, so there seems to be no point in
 allowing it to be declared NOT VALID.

 Hah ... interesting.  Not sure it's worth fussing about this.  If the
 user shoots himself in the foot by declaring an unvalidated constraint,
 which is not even documented, are we really at fault?


i can't find anything about this in the standard, so i guess even if
the standard allows us to turn checks off. ours is not standard syntax
so, IMHO, it should be only in ALTER TABLE.

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

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


Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-11-25 Thread Jeff Davis
On Wed, 2011-11-09 at 20:24 +0400, Alexander Korotkov wrote:
 New version of GiST for range types patch is here. This version seems
 to be complete and ready for review.
 
There's been some significant change in rangetypes_gist.c, can you
please rebase this patch?

I like the patch conceptually, though I'm still working through the
details.

Regards,
Jeff Davis



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