[HACKERS] Online base backup from the hot-standby
( Quotation from http://archives.postgresql.org/pgsql-hackers/2011-05/msg01396.php ) STEP1: Make startup process to acquire backup-end-position from not only backup-end record but also backup-history-file . * startup process allows to acquire backup-end-position from backup-history-file . I have created a patch to the above-mentioned content. Please check it. Jun Ishizuka NTT Software Corporation TEL：045-317-7018 E-Mail: ishizuka@po.ntts.co.jp standby_online_backup_01.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] Online base backup from the hot-standby
On 14.06.2011 09:03, Jun Ishiduka wrote: ( Quotation from http://archives.postgresql.org/pgsql-hackers/2011-05/msg01396.php ) STEP1: Make startup process to acquire backup-end-position from not only backup-end record but also backup-history-file . * startup process allows to acquire backup-end-position from backup-history-file . I have created a patch to the above-mentioned content. I still think that's headed in the wrong direction. (http://archives.postgresql.org/pgsql-hackers/2011-05/msg01405.php) -- 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
Re: [HACKERS] Online base backup from the hot-standby
I still think that's headed in the wrong direction. (http://archives.postgresql.org/pgsql-hackers/2011-05/msg01405.php) Please check these mails, and teach the reason for content of the wrong direction. (http://archives.postgresql.org/pgsql-hackers/2011-06/msg00209.php) (http://archives.postgresql.org/pgsql-hackers/2011-05/msg01566.php) Jun Ishizuka NTT Software Corporation TEL：045-317-7018 E-Mail: ishizuka@po.ntts.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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 8:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: I've already implemented some simple qual pushdown in the redis FDW, and am planning to do something similar for MySQL - however I won't be surprised if I have to rewrite redisGetQual in https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for example. OK, *now* we're on the same page. This sort of experimentation is exactly what I'm talking about: we'll certainly want to rewrite the code once we have better infrastructure, but it's necessary to write some throwaway code while we're learning what infrastructure FDWs want. (I find it a bit weird btw that you seem to be doing that at execution time not plan time...) That's largely because as we discussed a while back (http://archives.postgresql.org/pgsql-hackers/2011-04/msg00084.php), persisting data from plan time to scan time isn't exactly clean. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] 9.1 beta1 error
On 14 June 2011 05:17, Franklin Haut franklin.h...@gmail.com wrote: hi people, postgresql goes down on a WindowsXP SP3 machine. there a script to reproduce. (on XP with 8.4 works fine) CREATE DATABASE teste WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'Portuguese_Brazil.1252' LC_CTYPE = 'Portuguese_Brazil.1252' CONNECTION LIMIT = -1; CREATE TABLE public.s230chr ( codemp INTEGER, codfil INTEGER, codass INTEGER, codseq INTEGER, PRIMARY KEY(codemp, codfil, codass, codseq) ) WITH OIDS ALTER TABLE public.s230chr ADD COLUMN ho1ser TIME(0) WITHOUT TIME ZONE; ALTER TABLE public.s230chr ADD COLUMN ho2ser TIME(0) WITHOUT TIME ZONE; ALTER TABLE public.s230chr ADD COLUMN ho3ser TIME(0) WITHOUT TIME ZONE; ALTER TABLE public.s230chr ADD COLUMN ho4ser TIME(0) WITHOUT TIME ZONE; ALTER TABLE public.s230chr ADD COLUMN cobhte BOOLEAN; INSERT into s230chr (codemp, codfil, codass, codseq, ho1ser, ho2ser) values (1,1,1,1,'08:00', '11:00'); -- ERROR on next query select sum( extract ( EPOCH FROM coalesce( (ho2ser - ho1ser), '00:00:00') + coalesce( (ho4ser - ho3ser), '00:00:00') ) / 3600 ) as tthora from s230chr where codemp = 1 and codfil = 1 and codass = 1 and cobhte IS NULL ...and what's the error? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] ECPG parse.pl and parse2.pl
On Mon, Jun 13, 2011 at 10:59:38PM +0300, Peter Eisentraut wrote: I propose that we move parse2.pl to parse.pl and only keep the one. I agree, thanks for taking care. Originally I wanted to keep parse.pl around for a bit, so I could run both and compare the output over the development cycle. But with 9.1 being so close, there's no need for the old script anymore. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] per-column generic option
Hi,
I would like to propose support for per-column generic option, which is
defined in the SQL/MED standard. In 9.0 release, support for foreign
tables and per-table generic option have been added, but support for
per-column generic option hasn't.
Please examine the description below and attached patch
per_column_option_v1.patch. Any comments or questions are welcome.
Possible use cases
~~
Purpose of per-column generic option is passing column-specific settings
to the foreign-data wrapper.
1) For file_fdw, per-column generic option can be used to represent
per-column COPY option FORCE_NOT_NULL with boolean value (currently
file_fdw doesn't support FORCE_NOT_NULL option).
2) For postgresql_fdw (even though it has not been implemented yet),
per-column generic option could be used to represent the name of the
column on the foreign side. It is similar to per-table generic option
such as nspname and relname for namespace name/relation name,
proposed in the last development cycles. Such option would be named
attname after pg_attribute.attname.
Catalog design
~~
This proposal requires changing some catalogs.
1) To store per-column generic options, new attribute attfdwoptions
(text[]) was added at tail of pg_attribute. This is similar to the
generic option of other FDW objects such as FDW, server, user mapping
and foreign table. Existing attribute attoptions is not used for
generic options.
2) To conform the SQL/MED standard, an information_schema view
COLUMN_OPTIONS was added. Also underlying view
_pg_foreign_table_columns was added to show only columns which current
user has any access privilege. This fashion is same as other FDW views.
Syntax design
~
Per-column generic options can be operated via CREATE FOREIGN TABLE
statement and ALTER FOREIGN TABLE statement. Similar to other generic
options, ADD/SET/DROP can be specified for ALTER FOREIGN TABLE.
1) In CREATE FOREIGN TABLE statement, per-column generic options can be
specified in a column definition without operation qualifier such as
SET, ADD and DROP; all options are treated as ADD. Similar to other FDW
objects, multiple options can be specified for one column by separating
option-value pairs with comma.
-- multiple options can be specified for one column at once
CREATE FOREIGN TABLE foo (
c1 int OPTIONS (opt1 'value1'),
c2 text OPTIONS (opt2 'values2', opt3 'value3'),
c3 date OPTIONS (opt4 'value4) NOT NULL
) SERVER server;
To avoid syntax conflict between OPTIONS (...) and DEFAULT b_expr
(b_expr can end with a token OPTION), I placed OPTIONS (...) between
data type and any other column qualifier such as default values and
constraints.
The SQL/MED standard doesn't consider any column qualifier other than
data type, so it defines the syntax simply as below. I think new syntax
conforms the standard...
CREATE FOREIGN TABLE foo (
{ column_name data_type [ OPTIONS ( option 'value' [, ...] ) ] }
[, ... ]
) SERVER server [ OPTIONS (...) ]
Please note that CREATE FOREIGN TABLE shares the columnDef, a syntax
element for a column definition, with CREATE TABLE. I thought that they
should so, and I didn't introduce separated syntax for foreign tables.
2) Similar to other FDW objects' ALTER statement, ALTER FOREIGN TABLE
ALTER COLUMN accepts ADD/SET/DROP operation for each option. DROP
requires only option name.
ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (SET opt1 'VALUE1');-- should be set in advance
ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (ADD opt2 'VALUE1', DROP opt1);
Similar to other ALTER FOREIGN TABLE commands, ALTER COLUMN ... OPTIONS
(...) can be contained in the list of ALTER commands.
ALTER FOREIGN TABLE foo
ALTER COLUMN col1 OPTIONS (opt1 'val1'),
ALTER COLUMN col2 SET NOT NULL;
psql support
1) psql should support describing per-column generic options, so \dec
command was added. If the form \dec+ is used, generic options are also
displayed. Output sample is:
postgres=# \dec csv_branches
List of foreign table columns
Schema |Table | Column
+--+--
public | csv_branches | bid
public | csv_branches | bbalance
public | csv_branches | filler
(3 rows)
postgres=# \dec+ csv_branches
List of foreign table columns
Schema |Table | Column |Options
+--+--+
public | csv_branches | bid | {force_not_null=false}
public | csv_branches | bbalance | {force_not_null=true}
public | csv_branches | filler |
(3 rows)
Here I found an inconsistency about privilege to see generic options
(not only column but also FDW and server et al). The
information_schema.*_options only shows options which are associated to
objects that current user can access, but \de*+ doesn't have such
restriction. \de* commands should be fixed to hide forbidden
Re: [HACKERS] pg_trgm: unicode string not working
On Jun14, 2011, at 07:15 , Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote: No, it does not. Under what circumstances should I issue a suggestion to reindex, and what should the text be? It sounds like GIN indexes need to be reindexed after upgrading from 9.1 to = 9.1. Only if you care whether they work for corner cases such as empty arrays ... corner cases which didn't work before 9.1, so very likely you don't care. We also already say To fix this, do REINDEX INDEX ... in the errhint of old GIN indexes do not support whole-index scans nor searches for nulls. best regards, Florian Pflug -- 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] SSI patch renumbered existing 2PC resource managers??
On 13.06.2011 22:33, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: On 13.06.2011 21:31, Tom Lane wrote: So far as I can tell, that breaks pg_upgrade (if there are any open prepared transactions) for no redeeming social benefit. Surely pg_upgrade can't work anyway if there's any open prepared transactions in the database. We're not going to guarantee to keep all the data structures we write in two-phase state files unchanged over major releases. If pg_upgrade is not checking for prepared transcations at the moment, such a check should probably should be added. No, pg_upgrade should not be unilaterally refusing that. The correct way to deal with this consideration is to change the TWOPHASE_MAGIC number when we make a change in on-disk 2PC state. Which wasn't done in the SSI patch. We can either change that now, or undo the unnecessary change in existing RM IDs. I vote for the latter. Ok, I've renumbered the existing RMs back the way they were. I nevertheless don't think it's worthwhile to try to migrate 2pc state files in pg_upgrade. More than likely, it's a mistake on part of the admin anyway if there is a prepared transaction open at that point. -- 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
Re: [HACKERS] [PATCH] Bug in XPATH() if expression returns a scalar value
On Jun13, 2011, at 21:24 , Peter Eisentraut wrote:
On ons, 2011-06-08 at 10:14 +0200, Florian Pflug wrote:
But then you lose the ability to evaluate user-supplied
XPath expressions, because there's no way of telling which of these
function to use.
Perhaps having both variants, one type-safe and one not, would work. I
don't agree with doing away with type-safety completely for the sake of
convenience.
In theory, I agree.
In practice, however, XPath 1.0 isn't very strongly typed itself. The
built-in types are all auto-converted into one another (As if
string(), number(), boolean() had been called). Also, only three of
the functions defined by XPath 1.0 seem interesting. Here's the break-down
The functions returning string are
string(): Converts arbitrary values to strings
local-name(): Name of node-set's first top-level w/o namespace prefix
namespace-uri(): Namespace of node-set's first top-level
name(): Namespace prefix and name of node-set's first top-level node
concat()
starts-with()
contains()
substring-before()
substring-after()
substring()
string-length()
translate()
For all of these function postgres provides corresponding SQL functions,
which the exception of
local-name()
namespace-uri()
name()
In fact, these three functions are the raison d'être for my patch and this
thread.
I needed to find the name of a tag returned by an XPath expression, and to my
dismay discovered that XPATH('local-name(...)', ...) returns an empty array. The
only reason I added support for boolean and numeric XPath expressions at all was
for the sake of completeness.
Here's the rest of the functions defined by XPath 1.0. I'm convinces that none
of them are particularly useful as top-level functions, and therefore believe
that adding XPATH_BOOLEAN() and XPATH_NUMBER() is largely overkill.
The functions returning number are
number(): Converts arbitrary values to numbers
last()
position()
count()
sum(): Sum over a node-set after implicit conversion of nodes to numbers
floor()
ceiling()
round()
operators +, -, *, div, mod
The functions returning boolean are
boolean(): Converts arbitrary to boolean
not()
true()
false()
operators or, and, =, !=, =, , =,
best regards,
Florian Pflug
--
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] Boolean operators without commutators vs. ALL/ANY
On Jun13, 2011, at 16:19 , Andrew Dunstan wrote: On 06/13/2011 10:07 AM, Robert Haas wrote: Some languages use =~ and some use just ~... I was just wondering if anyone thought the commutator of =~ was ~=... My feeling is it's a bit dangerous. It's too easy to fat-finger the reverse op, and get something quite unintended. Well, but with ~ you need to *remember* that the regexp goes on the right side and the text on the left. That seems worse than the risk of fat-fingering =~ and getting ~=. At, at least, have looked up the argument order of ~ countless in the past... best regards, Florian Pflug -- 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] Boolean operators without commutators vs. ALL/ANY
On Jun13, 2011, at 05:44 , Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: (B) There should be a way to use ANY()/ALL() with the array elements becoming the left arguments of the operator. It seems to me that if we provided some way of handling this, your first proposal would be moot; and I have to say I like the idea of allowing this a lot more than tinkering with the operator names. There are syntactic reasons not to do that. It'd be a lot easier just to provide a commutator operator for ~. My suggestion would be the add a commutator for ~ as a short-term solution (preferably in 9.1). Since ~ doesn't inspire any obvious names for a possible commutator, I suggest adding =~ and ~=. Is there any support for that proposal? In the long term, I'd like to add support for (ANY() op expr) (Note the enclosing parens). I've checked that this works grammar-wise, but haven't no idea how much tweaking the executor needs to support that... best regards, Florian Pflug -- 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] SSI work for 9.1
On 10.06.2011 19:05, Kevin Grittner wrote:
I found that pgindent would like to tweak whitespace in three places
in that patch, and I found an unnecessary include that I would like
to remove. Normally, I would post a new version of the patch with
those adjustments, but there's been a disquieting tendency for
people to not look at what's actually happening with revisions of
this patch and act like the sky is falling with each refinement.
Let me be clear: each posted version of this patch has been safe and
has been an improvement on what came before. Dan and I didn't
disagree about what to do at any point; Dan figured out what to do
with two calls I left alone because I faded before I could work out
how to deal with them. Essentially we collaborated on-list rather
than discussing things off-list and posting the end result. Perhaps
that was a bad choice, but time was short and I had hopes that a
change people had requested could be included in beta2.
I did some further changes, refactoring SkipSerialization so that it's
hopefully more readable, and added a comment about the side-effects. See
attached. Let me know if I'm missing something.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 01a492e..b947c11 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -274,7 +274,8 @@ heapgetpage(HeapScanDesc scan, BlockNumber page)
else
valid = HeapTupleSatisfiesVisibility(loctup, snapshot, buffer);
- CheckForSerializableConflictOut(valid, scan-rs_rd, loctup, buffer);
+ CheckForSerializableConflictOut(valid, scan-rs_rd, loctup,
+ buffer, snapshot);
if (valid)
scan-rs_vistuples[ntup++] = lineoff;
@@ -469,7 +470,8 @@ heapgettup(HeapScanDesc scan,
snapshot,
scan-rs_cbuf);
-CheckForSerializableConflictOut(valid, scan-rs_rd, tuple, scan-rs_cbuf);
+CheckForSerializableConflictOut(valid, scan-rs_rd, tuple,
+scan-rs_cbuf, snapshot);
if (valid key != NULL)
HeapKeyTest(tuple, RelationGetDescr(scan-rs_rd),
@@ -478,7 +480,7 @@ heapgettup(HeapScanDesc scan,
if (valid)
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, snapshot);
LockBuffer(scan-rs_cbuf, BUFFER_LOCK_UNLOCK);
return;
}
@@ -747,7 +749,7 @@ heapgettup_pagemode(HeapScanDesc scan,
if (valid)
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
scan-rs_cindex = lineindex;
return;
}
@@ -755,7 +757,7 @@ heapgettup_pagemode(HeapScanDesc scan,
else
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
scan-rs_cindex = lineindex;
return;
}
@@ -1470,9 +1472,9 @@ heap_fetch(Relation relation,
valid = HeapTupleSatisfiesVisibility(tuple, snapshot, buffer);
if (valid)
- PredicateLockTuple(relation, tuple);
+ PredicateLockTuple(relation, tuple, snapshot);
- CheckForSerializableConflictOut(valid, relation, tuple, buffer);
+ CheckForSerializableConflictOut(valid, relation, tuple, buffer, snapshot);
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
@@ -1588,11 +1590,12 @@ heap_hot_search_buffer(ItemPointer tid, Relation relation, Buffer buffer,
/* If it's visible per the snapshot, we must return it */
valid = HeapTupleSatisfiesVisibility(heapTuple, snapshot, buffer);
- CheckForSerializableConflictOut(valid, relation, heapTuple, buffer);
+ CheckForSerializableConflictOut(valid, relation, heapTuple, buffer,
+ snapshot);
if (valid)
{
ItemPointerSetOffsetNumber(tid, offnum);
- PredicateLockTuple(relation, heapTuple);
+ PredicateLockTuple(relation, heapTuple, snapshot);
if (all_dead)
*all_dead = false;
return true;
@@ -1750,7 +1753,7 @@ heap_get_latest_tid(Relation relation,
* result candidate.
*/
valid = HeapTupleSatisfiesVisibility(tp, snapshot, buffer);
- CheckForSerializableConflictOut(valid, relation, tp, buffer);
+ CheckForSerializableConflictOut(valid, relation, tp, buffer, snapshot);
if (valid)
*tid = ctid;
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 0208765..31d705c 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -126,7 +126,7 @@ do { \
} while(0)
static IndexScanDesc index_beginscan_internal(Relation indexRelation,
- int nkeys, int norderbys);
+ int nkeys, int norderbys, Snapshot snapshot);
/*
@@ -234,7 +234,7 @@ index_beginscan(Relation heapRelation,
{
IndexScanDesc scan;
- scan = index_beginscan_internal(indexRelation, nkeys,
Re: [HACKERS] lazy vxid locks, v1
On Mon, Jun 13, 2011 at 8:10 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Sun, Jun 12, 2011 at 2:39 PM, Robert Haas robertmh...@gmail.com wrote:
...
Profiling reveals that the system spends enormous amounts of CPU time
in s_lock. LWLOCK_STATS reveals that the only lwlock with significant
amounts of blocking is the BufFreelistLock;
This is curious. Clearly the entire working set fits in RAM, or you
wouldn't be getting number like this. But does the entire working set
fit in shared_buffers? If so, you shouldn't see any traffic on
BufFreelistLock once all the data is read in. I've only seen
contention here when all data fits in OS cache memory but not in
shared_buffers.
Yeah, that does seem odd:
rhaas=# select pg_size_pretty(pg_database_size(current_database()));
pg_size_pretty
1501 MB
(1 row)
rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts'));
pg_size_pretty
1281 MB
(1 row)
rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts_pkey'));
pg_size_pretty
214 MB
(1 row)
rhaas=# show shared_buffers;
shared_buffers
8GB
(1 row)
--
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] 9.1 beta1 error
On Tue, Jun 14, 2011 at 1:17 AM, Franklin Haut franklin.h...@gmail.com wrote: hi people, postgresql goes down on a WindowsXP SP3 machine. there a script to reproduce. (on XP with 8.4 works fine) There is a crash related to time zone handling that was fixed in 9.1beta2. -- 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] Make relation_openrv atomic wrt DDL
On Mon, Jun 13, 2011 at 4:04 PM, Noah Misch n...@leadboat.com wrote: On Mon, Jun 13, 2011 at 08:21:05AM -0400, Robert Haas wrote: On Mon, Jun 13, 2011 at 1:12 AM, Noah Misch n...@leadboat.com wrote: This probably would not replace a backend-local counter of processed messages for RangeVarLockRelid()'s purposes. ?It's quite possibly a good way to reduce SInvalReadLock traffic, though. I was imagining one shared global counter, not one per backend, and thinking that each backend could do something like: volatile uint32 *the_global_counter = global_counter; uint32 latest_counter; mfence(); latest_counter = *the_global_counter; if (latest_counter != previous_value_of_global_counter || myprocstate-isReset) really_do_it(); previous_value_of_global_counter = latest_counter; I'm not immediately seeing why that wouldn't work for your purposes as well. That takes us back to the problem of answering the (somewhat rephrased) question Did any call to AcceptInvalidationMessages() between code point A and code point B call really_do_it()? in a way not prone to breaking when new calls to AcceptInvalidationMessages(), perhaps indirectly, get added. That's what the local counter achieved. To achieve that, previous_value_of_global_counter would need to be exposed outside sinval.c. That leaves us with a backend-local counter updated in a different fashion. I might be missing something... I see your point. -- 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
[HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
The attached patch is a preparation to rework implementation of DROP statement using a common code. That intends to apply get_object_address() to resolve name of objects to be removed, and eventually minimizes the number of places to put permission checks. Its first step is an enhancement of get_object_address; to accept 'missing_ok' argument to handle cases when IF EXISTS clause is supplied. If 'missing_ok' was true and the supplied name was not found, the patched get_object_address() returns an ObjectAddress with InvalidOid as objectId. If 'missing_ok' was false, its behavior is not changed. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp pgsql-v9.2-drop-reworks-part-0.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
Re: [HACKERS] pg_trgm: unicode string not working
On Tue, Jun 14, 2011 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not sure that pg_upgrade is a good vehicle for dispensing such advice, anyway. At least in the Red Hat packaging, end users will never read what it prints, unless maybe it fails outright and they're trying to debug why. In my experience to date, that happens 100% of the time. :-( -- 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] Creating new remote branch in git?
On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011: Andrew Dunstan wrote: Is putting remotes in your ~/.gitconfig good practice? I certainly don't have any in mine. Putting 'github' in there allows me to push/pull from github branches without having to specify the github URL. I think his point is that they are more properly specified in each repo's .git/config file, not the global $HOME/.gitconfig. If you were to check out some other, unrelated project, you could end up pushing unrelated branches to PG's repo ... Not sure if this is really possible, but it certainly seems scary to do things that way. I understand now --- that it is risky to create an origin branch in ~/.gitconfig. I am now using an alias: [alias] pgclone = clone ssh://g...@gitmaster.postgresql.org/postgresql.git I assume the 'github' branch in ~/.gitconfig is fine. That, too, would better off inside $REPO/.git/config, although it's certainly less risky than the other one. It doesn't make much sense to have an upstream that applies across every repository you have checked out. -- 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] SSI patch renumbered existing 2PC resource managers??
Heikki Linnakangas wrote: Ok, I've renumbered the existing RMs back the way they were. Don't we also need something like the attached? -Kevin ssi-twophase-c.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] per-column generic option
I haven't looked at the patch yet, but here are a few comments on the design, which overall looks good. 2011/6/14 Shigeru Hanada shigeru.han...@gmail.com: 1) psql should support describing per-column generic options, so \dec command was added. If the form \dec+ is used, generic options are also displayed. Output sample is: I would not add a new backslash command for this - it's unlikely to be useful to see this information across all tables. It would be more helpful to somehow (not sure of the details) incorporate this into the output of running \d on a foreign table. Here I found an inconsistency about privilege to see generic options (not only column but also FDW and server et al). The information_schema.*_options only shows options which are associated to objects that current user can access, but \de*+ doesn't have such restriction. \de* commands should be fixed to hide forbidden objects? It's less important whether \de* is consistent with information_schema in this regard than it is whether it is consistent with other psql backslash commands, e.g. \dv or \db or \dC. AFAIK those commands do not filter by privilege. 1) Is generic options proper term to mean FDW-specific option associated to a FDW object? It's used in the SQL/MED standard, but seems not popular... FDW option would be better than generic option? I think FDW option is much clearer. -- 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] SSI patch renumbered existing 2PC resource managers??
On 14.06.2011 15:14, Kevin Grittner wrote: Heikki Linnakangas wrote: Ok, I've renumbered the existing RMs back the way they were. Don't we also need something like the attached? Yes. I just committed a fix for that after noticing that the buildfarm didn't like it. Sorry.. -- 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
Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values''
On Mon, Jun 13, 2011 at 4:10 PM, richhguard-monot...@yahoo.co.uk wrote: Apologies - I meant to CC in the list but forgot. I have gone through and changed all the related functions except ``update_attstats''. Do you have any advice of how to handle the inner loops, such as those initializing ``stakindN''. The entries before can be handled just like in this patch, by using the symbolic constants. Again, this is based on master and all existing tests pass. Anything you're not sure about, just leave alone. -- 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] Boolean operators without commutators vs. ALL/ANY
On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug f...@phlo.org wrote: On Jun13, 2011, at 05:44 , Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: (B) There should be a way to use ANY()/ALL() with the array elements becoming the left arguments of the operator. It seems to me that if we provided some way of handling this, your first proposal would be moot; and I have to say I like the idea of allowing this a lot more than tinkering with the operator names. There are syntactic reasons not to do that. It'd be a lot easier just to provide a commutator operator for ~. My suggestion would be the add a commutator for ~ as a short-term solution (preferably in 9.1). I don't think we want to bump catversion again before release if we can avoid it. And I don't see this as being a terribly urgent problem - it's not like this is a new regression, and I can't remember hearing any complaints about it prior to two days ago. Since ~ doesn't inspire any obvious names for a possible commutator, I suggest adding =~ and ~=. Is there any support for that proposal? I'm OK with adding a commutator but I guess I don't see the point of adding a synonym for ~ along the way. The existing use of ~ is consistent with, for example, awk, so it's not like we've dreamed up something utterly crazy that we now need to fix. I'd suggest we just come up with some arbitrary variant, like ~~ or ~ or #~ or !#!%@~bikeshed++!. -- 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] 9.1 beta1 error
Hass, there problem are fixed in 9.1beta2, and now works. thanks 2011/6/14 Robert Haas robertmh...@gmail.com On Tue, Jun 14, 2011 at 1:17 AM, Franklin Haut franklin.h...@gmail.com wrote: hi people, postgresql goes down on a WindowsXP SP3 machine. there a script to reproduce. (on XP with 8.4 works fine) There is a crash related to time zone handling that was fixed in 9.1beta2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Creating new remote branch in git?
Robert Haas wrote: On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011: Andrew Dunstan wrote: Is putting remotes in your ~/.gitconfig ?good practice? I certainly don't have any in mine. Putting 'github' in there allows me to push/pull from github branches without having to specify the github URL. I think his point is that they are more properly specified in each repo's .git/config file, not the global $HOME/.gitconfig. ?If you were to check out some other, unrelated project, you could end up pushing unrelated branches to PG's repo ... ?Not sure if this is really possible, but it certainly seems scary to do things that way. I understand now --- that it is risky to create an origin branch in ~/.gitconfig. ?I am now using an alias: ? ? ? ?[alias] ? ? ? ? ? ? ? ?pgclone = clone ssh://g...@gitmaster.postgresql.org/postgresql.git I assume the 'github' branch in ~/.gitconfig is fine. That, too, would better off inside $REPO/.git/config, although it's certainly less risky than the other one. It doesn't make much sense to have an upstream that applies across every repository you have checked out. Wouldn't I conceivably use github with a variety of projects? I try to use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall my PG git tree. -- 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] Creating new remote branch in git?
Magnus Hagander wrote: I understand now --- that it is risky to create an origin branch in ~/.gitconfig. ?I am now using an alias: ? ? ? ?[alias] ? ? ? ? ? ? ? ?pgclone = clone ssh://g...@gitmaster.postgresql.org/postgresql.git I assume the 'github' branch in ~/.gitconfig is fine. That, too, would better off inside $REPO/.git/config, although it's certainly less risky than the other one. ?It doesn't make much sense to have an upstream that applies across every repository you have checked out. Wouldn't I conceivably use github with a variety of projects? ?I try to use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall my PG git tree. Yes, but your reference goes to a specific repository at github (bmomjian/postgres). Which wouldn't be correct for any other project... Ah, I see your point. Thanks. I renamed it 'pggithub'. I think I need to read Pro Git (http://progit.org/book/), though I am not sure that would have helped me in this exact case. -- 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] Creating new remote branch in git?
On Tue, Jun 14, 2011 at 14:40, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Jun 13, 2011 at 10:54 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of lun jun 13 18:38:46 -0400 2011: Andrew Dunstan wrote: Is putting remotes in your ~/.gitconfig ?good practice? I certainly don't have any in mine. Putting 'github' in there allows me to push/pull from github branches without having to specify the github URL. I think his point is that they are more properly specified in each repo's .git/config file, not the global $HOME/.gitconfig. ?If you were to check out some other, unrelated project, you could end up pushing unrelated branches to PG's repo ... ?Not sure if this is really possible, but it certainly seems scary to do things that way. I understand now --- that it is risky to create an origin branch in ~/.gitconfig. ?I am now using an alias: ? ? ? ?[alias] ? ? ? ? ? ? ? ?pgclone = clone ssh://g...@gitmaster.postgresql.org/postgresql.git I assume the 'github' branch in ~/.gitconfig is fine. That, too, would better off inside $REPO/.git/config, although it's certainly less risky than the other one. It doesn't make much sense to have an upstream that applies across every repository you have checked out. Wouldn't I conceivably use github with a variety of projects? I try to use ~/.gitconfig so I don't have to redo a lot of stuff when I reinstall my PG git tree. Yes, but your reference goes to a specific repository at github (bmomjian/postgres). Which wouldn't be correct for any other project... -- 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] ITYM DROP TABLE
On Mon, Jun 13, 2011 at 5:44 PM, David E. Wheeler da...@kineticode.com wrote: I was reading the partitioning docs when I spotted this. I think it means to highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE. I guess they might mean ALTER TABLE .. NO INHERIT. But I think I agree that DROP TABLE would be better. -- 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] SSI patch renumbered existing 2PC resource managers??
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: No, pg_upgrade should not be unilaterally refusing that. Uh, isn't there some physical files in pg_twophase/ that stick around to keep prepared transactions --- if so, pg_upgrade does not copy them from the old cluster to the new one. I am also hesistant to do so because there might be data in there that isn't portable. This argument seems a tad peculiar, since the *entire* *point* of pg_upgrade is to push physical files from one installation into another even though compatibility isn't guaranteed. It is the program's duty to understand enough to know whether it can transport the cluster's state safely. Not to arbitrarily discard state because it might possibly not be transportable. Well, pg_upgrade succeeds because it does as little as necessary to do the migration, relying on pg_dump to do much of the migration work at the catalog level. pg_upgrade tries to be involved as little as possible with the Postgres code so it doesn't have to be changed regularly between major versions. The prepared transaction case seems ugly enough that we don't want pg_upgrade to have to check every major release if anything changed about the data stored in prepared transactions. This is the same reason pg_upgrade doesn't transfer WAL files from the old cluster, just pg_clog files (which rarely changes its format). -- 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] Boolean operators without commutators vs. ALL/ANY
On Jun14, 2011, at 14:29 , Robert Haas wrote: On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug f...@phlo.org wrote: On Jun13, 2011, at 05:44 , Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: (B) There should be a way to use ANY()/ALL() with the array elements becoming the left arguments of the operator. It seems to me that if we provided some way of handling this, your first proposal would be moot; and I have to say I like the idea of allowing this a lot more than tinkering with the operator names. There are syntactic reasons not to do that. It'd be a lot easier just to provide a commutator operator for ~. My suggestion would be the add a commutator for ~ as a short-term solution (preferably in 9.1). I don't think we want to bump catversion again before release if we can avoid it. And I don't see this as being a terribly urgent problem - it's not like this is a new regression, and I can't remember hearing any complaints about it prior to two days ago. Hm, OK, that makes sense... Since ~ doesn't inspire any obvious names for a possible commutator, I suggest adding =~ and ~=. Is there any support for that proposal? I'm OK with adding a commutator but I guess I don't see the point of adding a synonym for ~ along the way. The existing use of ~ is consistent with, for example, awk, so it's not like we've dreamed up something utterly crazy that we now need to fix. I'd suggest we just come up with some arbitrary variant, like ~~ or ~ or #~ or !#!%@~bikeshed++!. That, however, I'm not at all happy with. Quite frankly, operator naming is already a bit of a mess, and readability of queries suffers as a result. The geometric types are especially vile offenders in this regard, but the various array-related operators aren't poster children either. I think we should try to work towards more mnemonic operator naming, not add to the mess by defining commutator pairs whose names bear no visual resemblance whatsoever to one each other. I'm not wedded to =~, it's just the only name I could come up which (a) has a natural commutator (b) gives visual indication of which argument constitutes the text and which the pattern (c) there is precedent for. BTW, there's actually precedent for a commutator of ~, namely @. Some of the geometric types (polygon, box, circle, point, path) use ~ as a commutator for @ (which stands for contains). But IMHO that mainly proves that the geometric types are vile offenders when it comes to readability... The pair (@, ~ ) is also the only pair of commutators whose names are totally unrelated to each other. Given a suitable definition of a reverse() function for text [1], the following query select o1.oprleft::regtype || ' ' || o1.oprname || ' ' || o1.oprright::regtype as opr, o2.oprleft::regtype || ' ' || o2.oprname || ' ' || o2.oprright::regtype as com, o1.oprcode as opr_code, o2.oprcode as com_code from pg_operator o1 join pg_operator o2 on o1.oprcom = o2.oid or o2.oprcom = o1.oid where o1.oid o2.oid and o1.oprname reverse(translate(o2.oprname, '', '')) and o1.oprname translate(o2.oprname, '', ''); produces opr|com| opr_code | com_code ---+---+-+--- polygon @ polygon | polygon ~ polygon | poly_contained | poly_contain box @ box | box ~ box | box_contained | box_contain circle @ circle | circle ~ circle | circle_contained| circle_contain point @ path | path ~ point | on_ppath| path_contain_pt point @ polygon | polygon ~ point | pt_contained_poly | poly_contain_pt point @ circle| circle ~ point| pt_contained_circle | circle_contain_pt (6 rows) best regards, Florian Pflug [1] I used create or replace function reverse(text) returns text as $$ select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, -1) i $$ language sql immutable; -- 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: CreateComments: use explicit indexing for ``values''
Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 2011: Apologies - I meant to CC in the list but forgot. I have gone through and changed all the related functions except ``update_attstats''. Do you have any advice of how to handle the inner loops, such as those initializing ``stakindN''. The entries before can be handled just like in this patch, by using the symbolic constants. Based on Tom's comments, I'd submit the patch without that bit, at least as a first step. Again, this is based on master and all existing tests pass. Please post the patch and add it here: https://commitfest.postgresql.org/action/commitfest_view?id=10 -- Á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] WIP: collect frequency statistics for arrays
Version of patch with few more comments and some fixes. -- With best regards, Alexander Korotkov. arrayanalyze-0.4.patch.gz Description: GNU Zip compressed 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] [WIP] cache estimates, cache access cost
2011/5/16 Greg Smith g...@2ndquadrant.com: Cédric Villemain wrote: http://git.postgresql.org/gitweb?p=users/c2main/postgres.git;a=shortlog;h=refs/heads/analyze_cache This rebases easily to make Cedric's changes move to the end; I just pushed a version with that change to https://github.com/greg2ndQuadrant/postgres/tree/analyze_cache if anyone wants a cleaner one to browse. I've attached a patch too if that's more your thing. I'd recommend not getting too stuck on the particular hook Cédric has added here to compute the cache estimate, which uses mmap and mincore to figure it out. It's possible to compute similar numbers, albeit less accurate, using an approach similar to how pg_buffercache inspects things. And I even once wrote a background writer extension that collected this sort of data as it was running the LRU scan anyway. Discussions of this idea seem to focus on how the what's in the cache? data is collected, which as far as I'm concerned is the least important part. There are multiple options, some work better than others, and there's no reason that can't be swapped out later. The more important question is how to store the data collected and then use it for optimizing queries. Attached are updated patches without the plugin itself. I've also added the cache_page_cost GUC, this one is not per tablespace, like others page_cost. There are 6 patches: 0001-Add-reloscache-column-to-pg_class.patch 0002-Add-a-function-to-update-the-new-pg_class-cols.patch 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch 0004-Add-a-Hook-to-handle-OSCache-stats.patch 0005-Add-reloscache-to-Index-Rel-OptInfo.patch 0006-Add-cache_page_cost-GUC.patch I have some comments on my own code: * I am not sure of the best datatype to use for 'reloscache' * I didn't include the catalog number change in the patch itself. * oscache_update_relstats() is very similar to vac_update_relstats(), maybe better to merge them but reloscache should not be updated at the same time than other stats. * There is probably too much work done in do_oscache_analyze_rel() because I kept vac_open_indexes() (not a big drama atm) * I don't know so much how gram.y works, so I am not sure my changes cover all cases. * No tests; similar columns and GUC does not have test either, but it lacks a test for ANALYZE OSCACHE -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support From d2fe7e85aea31cfe8cd6559a060f71c424fe03af Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9dric=20Villemain?= ced...@2ndquadrant.fr Date: Wed, 25 May 2011 23:17:36 +0200 Subject: [PATCH 1/7] Add reloscache column to pg_class 1 column reloscache to contain the percentage of pages in cache per relation. May be used by the planner and updated with ANALYZE OSCACHE; (not done yet, see next commits) --- doc/src/sgml/catalogs.sgml | 11 + src/backend/catalog/heap.c |1 + src/backend/utils/cache/relcache.c |2 + src/include/catalog/pg_class.h | 44 ++- 4 files changed, 37 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index 8504555..4cfad39 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *** *** 1634,1639 --- 1634,1650 /row row + entrystructfieldreloscache/structfield/entry + entrytypefloat4/type/entry + entry/entry + entry +Percentage of the files in OS cache. This is only an estimate used by +the planner. It is updated by commandANALYZE OSCACHE/command. +By default, the value is not updated and an extension is required. + /entry + /row + + row entrystructfieldreltoastrelid/structfield/entry entrytypeoid/type/entry entryliterallink linkend=catalog-pg-classstructnamepg_class/structname/link.oid/literal/entry diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c new file mode 100644 index a6e541d..2043c40 *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *** InsertPgClassTuple(Relation pg_class_des *** 764,769 --- 764,770 values[Anum_pg_class_reltablespace - 1] = ObjectIdGetDatum(rd_rel-reltablespace); values[Anum_pg_class_relpages - 1] = Int32GetDatum(rd_rel-relpages); values[Anum_pg_class_reltuples - 1] = Float4GetDatum(rd_rel-reltuples); + values[Anum_pg_class_reloscache - 1] = Float4GetDatum(rd_rel-reloscache); values[Anum_pg_class_reltoastrelid - 1] = ObjectIdGetDatum(rd_rel-reltoastrelid); values[Anum_pg_class_reltoastidxid - 1] = ObjectIdGetDatum(rd_rel-reltoastidxid); values[Anum_pg_class_relhasindex - 1] = BoolGetDatum(rd_rel-relhasindex); diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c new file mode 100644 index d7e94ff..ca09e3b *** a/src/backend/utils/cache/relcache.c ---
Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values''
Alvaro Herrera alvhe...@commandprompt.com writes:
Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 2011:
Do you have any advice of how to handle the inner loops, such as those
initializing ``stakindN''. The entries before can be handled just like in
this patch, by using the symbolic constants.
Based on Tom's comments, I'd submit the patch without that bit, at least
as a first step.
He already did no?
I did think of a possible way to rewrite update_attstats: instead of
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[i++] = ObjectIdGetDatum(stats-staop[k]);/* staopN */
}
do
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[Anum_pg_statistic_staop1 - 1 + k] =
ObjectIdGetDatum(stats-staop[k]);
}
etc. However, it's not clear to me whether this is really an
improvement. Opinions?
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] ITYM DROP TABLE
Excerpts from David E. Wheeler's message of lun jun 13 17:44:05 -0400 2011:
I was reading the partitioning docs when I spotted this. I think it means to
highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.
I think the point of the existing wording is to point out
ALTER TABLE / NO INHERIT. I wonder if it's worth expanding the text to
mention both, such as
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2320,7 +2320,9 @@ VALUES ('New York', NULL, NULL, 'NY');
para
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning design.
- commandALTER TABLE/ is far faster than a bulk operation.
+ commandALTER TABLE/ (to split out a sub-table from the partitioned
+ table) and commandDROP TABLE/ (to remove a partition altogether) are
+ both far faster than a bulk operation.
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
/para
However, this introductory text is supposed to be very brief; maybe we
should remove mention of specific commands here.
--
Á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] PATCH: CreateComments: use explicit indexing for ``values''
On Tue, Jun 14, 2011 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Alvaro Herrera alvhe...@commandprompt.com writes:
Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400
2011:
Do you have any advice of how to handle the inner loops, such as those
initializing ``stakindN''. The entries before can be handled just like in
this patch, by using the symbolic constants.
Based on Tom's comments, I'd submit the patch without that bit, at least
as a first step.
He already did no?
I did think of a possible way to rewrite update_attstats: instead of
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[i++] = ObjectIdGetDatum(stats-staop[k]); /* staopN */
}
do
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[Anum_pg_statistic_staop1 - 1 + k] =
ObjectIdGetDatum(stats-staop[k]);
}
etc. However, it's not clear to me whether this is really an
improvement. Opinions?
I don't care that much, but IMV that's just gilding the lily.
--
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: CreateComments: use explicit indexing for ``values''
Excerpts from Tom Lane's message of mar jun 14 10:30:28 -0400 2011:
Alvaro Herrera alvhe...@commandprompt.com writes:
Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400
2011:
Do you have any advice of how to handle the inner loops, such as those
initializing ``stakindN''. The entries before can be handled just like in
this patch, by using the symbolic constants.
Based on Tom's comments, I'd submit the patch without that bit, at least
as a first step.
He already did no?
I don't see the patch attached anywhere ...
I did think of a possible way to rewrite update_attstats: instead of
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[i++] = ObjectIdGetDatum(stats-staop[k]);/* staopN */
}
do
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[Anum_pg_statistic_staop1 - 1 + k] =
ObjectIdGetDatum(stats-staop[k]);
}
etc. However, it's not clear to me whether this is really an
improvement. Opinions?
I guess the other option is
i = Anum_pg_statistic_staop1 - 1;
for (k = 0; k STATISTIC_NUM_SLOTS; k++)
{
values[i++] = ObjectIdGetDatum(stats-staop[k]);
}
(I also tried moving the i initialization to the for first arg, but it
seems better this way)
Not sure what's better.
--
Á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] Re: patch review : Add ability to constrain backend temporary file space
2011/6/3 Mark Kirkwood mark.kirkw...@catalyst.net.nz: On 02/06/11 18:34, Jaime Casanova wrote: - the patch adds this to serial_schedule but no test has been added... diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bb654f9..325cb3d 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -127,3 +127,4 @@ test: largeobject test: with test: xml test: stats +test: resource Corrected v4 patch with the test files, for completeness. Note that discussion has moved on and there will be a v5 :-) Mark, can you submit your updated patch ? -- Cédric Villemain 2ndQuadrant 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] SSI work for 9.1
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
I did some further changes, refactoring SkipSerialization so that
it's hopefully more readable, and added a comment about the
side-effects. See attached. Let me know if I'm missing something.
I do think the changes improve readability. I don't see anything
missing, but there's something we can drop. Now that you've split
the read and write tests, this part can be dropped from the
SerializationNeededForWrite function:
+
+ /* Check if we have just become RO-safe. */
+ if (SxactIsROSafe(MySerializableXact))
+ {
+ ReleasePredicateLocks(false);
+ return false;
+ }
If it's doing a write, it can't be a read-only transaction
-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] ITYM DROP TABLE
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from David E. Wheeler's message of lun jun 13 17:44:05 -0400 2011: I was reading the partitioning docs when I spotted this. I think it means to highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE. I think the point of the existing wording is to point out ALTER TABLE / NO INHERIT. I wonder if it's worth expanding the text to mention both, such as - commandALTER TABLE/ is far faster than a bulk operation. + commandALTER TABLE/ (to split out a sub-table from the partitioned + table) and commandDROP TABLE/ (to remove a partition altogether) are + both far faster than a bulk operation. I think you need to spell out ALTER TABLE NO INHERIT if you are going to do that. This formulation seems to imply that *any* form of ALTER TABLE is fast, which surely ain't the truth. However, this introductory text is supposed to be very brief; maybe we should remove mention of specific commands here. No, I don't think it needs to be that brief. But if you think your version is too long, remove the parenthetical remarks. 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] [WIP] cache estimates, cache access cost
On Tue, Jun 14, 2011 at 10:29 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 0001-Add-reloscache-column-to-pg_class.patch 0002-Add-a-function-to-update-the-new-pg_class-cols.patch 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch 0004-Add-a-Hook-to-handle-OSCache-stats.patch 0005-Add-reloscache-to-Index-Rel-OptInfo.patch 0006-Add-cache_page_cost-GUC.patch It seems to me that posting updated versions of this patch gets us no closer to addressing the concerns I (and Tom, on other threads) expressed about this idea previously. Specifically: 1. ANALYZE happens far too infrequently to believe that any data taken at ANALYZE time will still be relevant at execution time. 2. Using data gathered by ANALYZE will make plans less stable, and our users complain not infrequently about the plan instability we already have, therefore we should not add more. 3. Even if the data were accurate and did not cause plan stability, we have no evidence that using it will improve real-world performance. Now, it's possible that you or someone else could provide some experimental evidence refuting these points. But right now there isn't any, and until there is, -1 from me on applying any of this. -- 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] use less space in xl_xact_commit patch
On Wed, May 25, 2011 at 3:05 PM, Simon Riggs si...@2ndquadrant.com wrote: Yes, that's correct. We can remove them from the normal commit record when nmsgs == 0. Leonardo, can you submit an updated version of this patch today that incorporates Simon's suggestion? The CommitFest starts tomorrow. If not, please feel free to resubmit to the next CommitFest. Simon or I may find the time to review it sooner rather than later even if it misses the deadline for this CommitFest, because I think it's an important optimization and I know you have other work that depends on it. But for now we should mark it Returned with Feedback if you don't have an updated version ready to go. -- 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: add GiST support for BOX @ POINT queries
On Fri, Jun 10, 2011 at 6:16 AM, Hitoshi Harada umi.tan...@gmail.com wrote: 2011/2/24 Andrew Tipton andrew.t.tip...@gmail.com: While playing around with the BOX and POINT datatypes, I was surprised to note that BOX @ POINT (and likewise POINT @ BOX) queries were not using the GiST index I had created on the BOX column. The attached patch adds a new strategy @(BOX,POINT) to the box_ops opclass. Internally, gist_box_consistent simply transforms the POINT into its corresponding BOX. This is my first Postgres patch, and I wasn't able to figure out how to go about creating a regression test for this change. (All existing tests do pass, but none of them seem to specifically test index behaviour.) I reviewed the patch and worried about hard-wired magic number as StrategyNumber. At least you should use #define to indicate the number's meaning. In addition, the modified gist_box_consistent() is too dangerous; q_box is declared in the if block locally and is referenced, which pointer is passed to the outer process of the block. AFAIK if the local memory of each block is alive outside if block is platform-dependent. Isn't it worth adding new consistent function for those purposes? The approach in the patch as stands looks kludge to me. Andrew - in case it's not clear, we're waiting on you to respond to Hitoshi's comments or provide an updated patch. Thanks, -- 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: CreateComments: use explicit indexing for ``values''
I have left update_attstat which I'm unsure about, and have attached the updated patch handling the other cases. This will be linked in via the commitfest page. I picked commands/comment.c randomly and found the i = 0, i++ method of initializing the array made it harder for me to visualize it's structure, or verify each value was in the correct place in the array. Obviously we can assume each value is in the correct place because it's been like that in working code. This patch makes the intent of each initialization clear by using the constants directly instead of in a comment, and has the effect of being able to verify each line on it's own. The original requires verification of the preceding i++'s. I expanded upon my original patch of only handling CreateComments to include the other cases for consistency - but only so far as update_attstats as I found it too complex for me and left it. I don't like to break anything. I'm not going to push for it if most people prefer the original code for readability, or maintainability across versions; I just thought I'd post my thoughts with a patch. An easier route, might be for me to submit a patch which just changes comment.c by adding in the constants via a comment like the other places. What do you think ? Richard --- On Tue, 14/6/11, Alvaro Herrera alvhe...@commandprompt.com wrote: From: Alvaro Herrera alvhe...@commandprompt.com Subject: Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values'' To: richhguard-monotone richhguard-monot...@yahoo.co.uk Cc: pgsql-hackers pgsql-hackers@postgresql.org Date: Tuesday, 14 June, 2011, 15:20 Excerpts from richhguard-monotone's message of lun jun 13 16:10:17 -0400 2011: Apologies - I meant to CC in the list but forgot. I have gone through and changed all the related functions except ``update_attstats''. Do you have any advice of how to handle the inner loops, such as those initializing ``stakindN''. The entries before can be handled just like in this patch, by using the symbolic constants. Based on Tom's comments, I'd submit the patch without that bit, at least as a first step. Again, this is based on master and all existing tests pass. Please post the patch and add it here: https://commitfest.postgresql.org/action/commitfest_view?id=10 -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support commit 7a689fc96b624de5de589cd9e2ef1c42ae542a16 Author: Richard Hopkins richhguard-monot...@yahoo.co.uk Date: Mon Jun 13 20:44:15 2011 +0100 Initialize arrays using Anum_pg symbolic constants This clarifies the intent of the code by using the already defined symbolic constants; the constants were referenced in the related comments for each column anyway. However, ``update_attstats'' still uses the old style of i=0, and then i++ with each element initialization. I have left this for now as it seems too tricky to include with these changes. ``update_attstats'' has a few loops using ``STATISTIC_NUM_SLOTS'' and I'm unsure whether to remove them, and use the symbolic constants such as ``Anum_pg_statistic_stakind1'' at the expense of adding extra lines. diff --git a/src/backend/catalog/pg_operator.c b/src/backend/catalog/pg_operator.c index ccd0fe1..4d2d7b7 100644 --- a/src/backend/catalog/pg_operator.c +++ b/src/backend/catalog/pg_operator.c @@ -234,22 +234,21 @@ OperatorShellMake(const char *operatorName, * initialize values[] with the operator name and input data types. Note * that oprcode is set to InvalidOid, indicating it's a shell. */ - i = 0; namestrcpy(oname, operatorName); - values[i++] = NameGetDatum(oname); /* oprname */ - values[i++] = ObjectIdGetDatum(operatorNamespace); /* oprnamespace */ - values[i++] = ObjectIdGetDatum(GetUserId()); /* oprowner */ - values[i++] = CharGetDatum(leftTypeId ? (rightTypeId ? 'b' : 'r') : 'l'); /* oprkind */ - values[i++] = BoolGetDatum(false); /* oprcanmerge */ - values[i++] = BoolGetDatum(false); /* oprcanhash */ - values[i++] = ObjectIdGetDatum(leftTypeId); /* oprleft */ - values[i++] = ObjectIdGetDatum(rightTypeId); /* oprright */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprresult */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprcom */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprnegate */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprcode */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprrest */ - values[i++] = ObjectIdGetDatum(InvalidOid); /* oprjoin */ + values[Anum_pg_operator_oprname - 1] = NameGetDatum(oname); + values[Anum_pg_operator_oprnamespace - 1] = ObjectIdGetDatum(operatorNamespace); + values[Anum_pg_operator_oprowner - 1] = ObjectIdGetDatum(GetUserId()); + values[Anum_pg_operator_oprkind - 1] = CharGetDatum(leftTypeId ? (rightTypeId ? 'b' : 'r') : 'l'); + values[Anum_pg_operator_oprcanmerge
Re: [HACKERS] [WIP] cache estimates, cache access cost
2011/6/14 Robert Haas robertmh...@gmail.com: On Tue, Jun 14, 2011 at 10:29 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 0001-Add-reloscache-column-to-pg_class.patch 0002-Add-a-function-to-update-the-new-pg_class-cols.patch 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch 0004-Add-a-Hook-to-handle-OSCache-stats.patch 0005-Add-reloscache-to-Index-Rel-OptInfo.patch 0006-Add-cache_page_cost-GUC.patch It seems to me that posting updated versions of this patch gets us no closer to addressing the concerns I (and Tom, on other threads) expressed about this idea previously. Specifically: 1. ANALYZE happens far too infrequently to believe that any data taken at ANALYZE time will still be relevant at execution time. ANALYZE happens when people execute it, else it is auto-analyze and I am not providing auto-analyze-oscache. ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The frequency is not important here, I believe. 2. Using data gathered by ANALYZE will make plans less stable, and our users complain not infrequently about the plan instability we already have, therefore we should not add more. Again, it is hard to do a UPDATE pg_class SET reloscache, so I used ANALYZE logic. Also I have taken into account the fact that someone may want to SET the values like it was also suggested, so my patches allow to do : 'this table is 95% in cache, the DBA said' (it is stable, not based on OS stats). This case has been suggested several times and is covered by my patch. 3. Even if the data were accurate and did not cause plan stability, we have no evidence that using it will improve real-world performance. I have not finish my work on cost estimation and I believe this work will take some time and can be done in another commitfest. At the moment my patches do not change anything on the dcision of the planner, just offers the tools I need to hack cost estimates. Now, it's possible that you or someone else could provide some experimental evidence refuting these points. But right now there isn't any, and until there is, -1 from me on applying any of this. I was trying to split the patch size by group of features to reduce its size. The work is in progress. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Cédric Villemain 2ndQuadrant 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] psql describe.c cleanup
On Sat, May 7, 2011 at 2:40 PM, Josh Kupershmidt schmi...@gmail.com wrote: Hi all, I use psql's -E mode every now and then, copy-and-pasting and further tweaking the SQL displayed. Most queries are displayed terminated by a semicolon, but quite a few aren't, making copy-and-paste just a bit more tedious. Attached is a patch to fix every SQL query I saw in describe.c. There were also a few queries with trailing newlines, and I fixed those too. I did a quick review and test of your patch. It didn't quite apply cleanly due to recent non-related describe.c changes -- updated patch attached. First, I'll give you a thumbs up on the original inspiration for the patch. The output should be standardized, and I see no reason not to append a semicolon on usability basis. Beyond that, the changes are mostly cosmetic and I can't see how it will break things outside of terminating a query early by accident (I didn't see any). What I do wonder though is if the ; appending should really be happening in printQuery() instead of in each query -- the idea being that formatting for external consumption should be happening in one place. Maybe that's over-thinking it though. merlin psql_describe.v3.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] ITYM DROP TABLE
On Jun 14, 2011, at 8:03 AM, Tom Lane wrote: - commandALTER TABLE/ is far faster than a bulk operation. + commandALTER TABLE/ (to split out a sub-table from the partitioned + table) and commandDROP TABLE/ (to remove a partition altogether) are + both far faster than a bulk operation. I think you need to spell out ALTER TABLE NO INHERIT if you are going to do that. This formulation seems to imply that *any* form of ALTER TABLE is fast, which surely ain't the truth. However, this introductory text is supposed to be very brief; maybe we should remove mention of specific commands here. No, I don't think it needs to be that brief. But if you think your version is too long, remove the parenthetical remarks. +1 I think that would be perfect. 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] ITYM DROP TABLE
Excerpts from David E. Wheeler's message of mar jun 14 12:33:27 -0400 2011: On Jun 14, 2011, at 8:03 AM, Tom Lane wrote: - commandALTER TABLE/ is far faster than a bulk operation. + commandALTER TABLE/ (to split out a sub-table from the partitioned + table) and commandDROP TABLE/ (to remove a partition altogether) are + both far faster than a bulk operation. I think you need to spell out ALTER TABLE NO INHERIT if you are going to do that. This formulation seems to imply that *any* form of ALTER TABLE is fast, which surely ain't the truth. However, this introductory text is supposed to be very brief; maybe we should remove mention of specific commands here. No, I don't think it needs to be that brief. But if you think your version is too long, remove the parenthetical remarks. +1 I think that would be perfect. Done that way (9.0 and beyond). -- Á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] procpid?
On Jun 13, 2011, at 10:56 AM, Simon Riggs wrote: If we were going to make changes like this, I'd suggest we save them up in a big bag for when we change major version number. Everybody in the world thinks that PostgreSQL v8 is compatible across all versions (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we would still have forward progress, but in more sensible sized steps. Otherwise we just break the code annually for all the people that support us. If we had a more stable environment for tools vendors, maybe people wouldn't need to be manually typing procpid anyway... Wouldn't it be better still to have both the new and old columns available for a while? That would produce the minimum amount of disruption to tools, etc. The only downside is some potential confusion, but that would just serve to drive people to the documentation to see why there were two fields, where they would find out one was deprecated. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] procpid?
Jim Nasby wrote: On Jun 13, 2011, at 10:56 AM, Simon Riggs wrote: If we were going to make changes like this, I'd suggest we save them up in a big bag for when we change major version number. Everybody in the world thinks that PostgreSQL v8 is compatible across all versions (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we would still have forward progress, but in more sensible sized steps. Otherwise we just break the code annually for all the people that support us. If we had a more stable environment for tools vendors, maybe people wouldn't need to be manually typing procpid anyway... Wouldn't it be better still to have both the new and old columns available for a while? That would produce the minimum amount of disruption to tools, etc. The only downside is some potential confusion, but that would just serve to drive people to the documentation to see why there were two fields, where they would find out one was deprecated. Well, someone doing SELECT *, which is probably 90% of the users, are going to be pretty confused by duplicate columns, asking, What is the difference? For those people this would make things worse than they are now. I would say 90% of users are doing SELECT *, and 10% are joining to other tables or displaying specific columns. We want to help that 10% without making that 90% confused. -- 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] Why polecat and colugos are failing to build back branches
On Jun 13, 2011, at 6:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: I looked into $SUBJECT. There appear to be two distinct issues: 1. On colugos (OS X with LLVM), the ... However, because when using gcc that only results in a warning, we didn't back-patch it. Now it appears that it's an error when using LLVM, so maybe we oughta back-patch it, at least to whichever releases we think will build with LLVM. FYI, in the latest beta developer XCode release, gcc is llvm. That's not my test machine though. 2. Pre-9.0, the installation step is failing like this: ... alternatives seem to be to ask Robert to rename the volume, or stop testing pre-9.0 branches on that machine. Any change is no problem, just let me know. Later, Rob -- 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] ITYM DROP TABLE
Alvaro Herrera alvhe...@commandprompt.com writes: Done that way (9.0 and beyond). Re-reading the actual commit, I notice that there's now a grammatical problem: the following sentence says It also entirely avoids the commandVACUUM/command overhead caused by a bulk commandDELETE/. which was okay when it referred to ALTER TABLE, but now that there are two commands mentioned in the previous sentence, it doesn't match. Perhaps These commands also avoid the 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] [WIP] cache estimates, cache access cost
On 06/14/2011 11:04 AM, Robert Haas wrote: Even if the data were accurate and did not cause plan stability, we have no evidence that using it will improve real-world performance. That's the dependency Cédric has provided us a way to finally make progress on. Everyone says there's no evidence that this whole approach will improve performance. But we can't collect such data, to prove or disprove it helps, without a proof of concept patch that implements *something*. You may not like the particular way the data is collected here, but it's a working implementation that may be useful for some people. I'll take data collected at ANALYZE time as a completely reasonable way to populate the new structures with realistic enough test data to use initially. Surely at least one other way to populate the statistics, and possibly multiple other ways that the user selects, will be needed eventually. I commented a while ago on this thread: every one of these discussions always gets dragged into the details of how the cache statistics data will be collected and rejects whatever is suggested as not good enough. Until that stops, no progress will ever get made on the higher level details. By its nature, developing toward integrating cached percentages is going to lurch forward on both collecting the cache data and using the cache knowledge in queries fronts almost independently. This is not a commit candidate; it's the first useful proof of concept step for something we keep talking about but never really doing. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] cache estimates, cache access cost
On Tue, Jun 14, 2011 at 1:10 PM, Greg Smith g...@2ndquadrant.com wrote: On 06/14/2011 11:04 AM, Robert Haas wrote: Even if the data were accurate and did not cause plan stability, we have no evidence that using it will improve real-world performance. That's the dependency Cédric has provided us a way to finally make progress on. Everyone says there's no evidence that this whole approach will improve performance. But we can't collect such data, to prove or disprove it helps, without a proof of concept patch that implements *something*. You may not like the particular way the data is collected here, but it's a working implementation that may be useful for some people. I'll take data collected at ANALYZE time as a completely reasonable way to populate the new structures with realistic enough test data to use initially. But there's no reason that code (which may or may not eventually prove useful) has to be incorporated into the main tree. We don't commit code so people can go benchmark it; we ask for the benchmarking to be done first, and then if the results are favorable, we commit the code. -- 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] procpid?
On 06/14/2011 11:44 AM, Jim Nasby wrote: Wouldn't it be better still to have both the new and old columns available for a while? That would produce the minimum amount of disruption to tools, etc. Doing this presumes the existence of a large number of tools where the author is unlikely to be keeping up with PostgreSQL development. I don't believe that theorized set of users actually exists. There are people who use pg_stat_activity simply, and there are tool authors who are heavily involved enough that they will see a change here coming far enough in advance to adopt it without disruption. If there's a large base of casual tool authors, who wrote something using pg_stat_activity once and will never update it again, I don't know where they are. Anyway, I want a larger change to pg_stat_activity than this one, and I would just roll fixing this column name into that more disruptive and positive change. Right now the biggest problem with this view is that you have to parse the text of the query to figure out what state the connection is in. This is silly; there should be boolean values exposed for idle and in transaction. I want to be able to write things like this: SELECT idle,in_trans,count(*) FROM pg_stat_activity GROUP BY idle,in_trans; SELECT min(backend_start) FROM pg_stat_activity WHERE idle; Right now the standard approach to this is to turn current_query into a derived state value using CASE statements. It's quite unfriendly, and a bigger problem than this procpid mismatch. Fix that whole mess at once, and now you've got something useful enough to justify breaking tools. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] cache estimates, cache access cost
On Tue, Jun 14, 2011 at 12:06 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 1. ANALYZE happens far too infrequently to believe that any data taken at ANALYZE time will still be relevant at execution time. ANALYZE happens when people execute it, else it is auto-analyze and I am not providing auto-analyze-oscache. ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The frequency is not important here, I believe. Well, I'm not saying you have to have all the answers to post a WIP patch, certainly. But in terms of getting something committable, it seems like we need to have at least an outline of what the long-term plan is. If ANALYZE OSCACHE is an infrequent operation, then the data isn't going to be a reliable guide to what will happen at execution time... 2. Using data gathered by ANALYZE will make plans less stable, and our users complain not infrequently about the plan instability we already have, therefore we should not add more. ...and if it is a frequent operation then it's going to result in unstable plans (and maybe pg_class bloat). There's a fundamental tension here that I don't think you can just wave your hands at. I was trying to split the patch size by group of features to reduce its size. The work is in progress. Totally reasonable, but I can't see committing any of it without some evidence that there's light at the end of the tunnel. No performance tests *whatsoever* have been done. We can debate the exact amount of evidence that should be required to prove that something is useful from a performance perspective, but we at least need some. I'm beating on this point because I believe that the whole idea of trying to feed this information back into the planner is going to turn out to be something that we don't want to do. I think it's going to turn out to have downsides that are far larger than the upsides. I am completely willing to be be proven wrong, but right now I think this will make things worse and you think it will make things better and I don't see any way to bridge that gap without doing some measurements. For example, if you run this patch on a system and subject that system to a relatively even workload, how much do the numbers bounce around between runs? What if you vary the workload, so that you blast it with OLTP traffic at some times and then run reporting queries at other times? Or different tables become hot at different times? Once you've written code to make the planner do something with the caching % values, then you can start to explore other questions. Can you generate plan instability, especially on complex queries, which are more prone to change quickly based on small changes in the cost estimates? Can you demonstrate a workload where bad performance is inevitable with the current code, but with your code, the system becomes self-tuning and ends up with good performance? What happens if you have a large cold table with a small hot end where all activity is concentrated? -- 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] procpid?
On Tue, Jun 14, 2011 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote: Anyway, I want a larger change to pg_stat_activity than this one Well, Simon recomended to have a big bag of changes that justify break tools... and you have presented a good one item for that bag... Maybe we should start a wiki page for this and put there all the changes we want to see before break anything? for example, a change i want to see is in csvlog: i want a duration field there because tools like pgfouine, pgsi and others parse the message field for a duration string which is only usefull if the message is in english which non-english dba's won't have -- 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
[HACKERS] startupBufferPinWaitBufId vs. ProcGlobalShmemSize
ProcGlobalShmemSize() currently includes code to allow space for startupBufferPinWaitBufId. But I think that's redundant, because that variable is stored in PROC_HDR *ProcGlobal, for which this function is separately allocating space. So I propose to apply the attached patch, barring objections. I see no reason to back-patch this, since allocating an extra 4 bytes of shared memory is hardly anything to get excited about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company procglobalshmemsize-cleanup.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] procpid?
On Tue, Jun 14, 2011 at 1:43 PM, Jaime Casanova ja...@2ndquadrant.com wrote: On Tue, Jun 14, 2011 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote: Anyway, I want a larger change to pg_stat_activity than this one Well, Simon recomended to have a big bag of changes that justify break tools... and you have presented a good one item for that bag... Maybe we should start a wiki page for this and put there all the changes we want to see before break anything? for example, a change i want to see is in csvlog: i want a duration field there because tools like pgfouine, pgsi and others parse the message field for a duration string which is only usefull if the message is in english which non-english dba's won't have There are real problems with the idea of having one release where we break everything that we want to break - mostly from a process standpoint. We aren't always good at being organized and disciplined, and coming up with a multi-year plan to break everything all at once in 2014 for release in 2015 may be difficult, because it requires a consensus on release management to hold together for years, and sometimes we can't even manage days. But I don't think it's a bad idea to try. So +1 for creating a list of things that we think we might like to break at some point. It might be worth trying to do this in the context of the Todo list - come up with some special badge or flag that we can put on items that require a compatibility break, so that we can scan for them there easily. -- 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] procpid?
Greg Smith g...@2ndquadrant.com wrote: Doing this presumes the existence of a large number of tools where the author is unlikely to be keeping up with PostgreSQL development. I don't believe that theorized set of users actually exists. There could be a number of queries used for monitoring or administration which will be affected. Just on our Wiki pages we have some queries available for copy/paste which would need multiple versions while both column names were in supported versions of the software: http://wiki.postgresql.org/wiki/Lock_dependency_information http://wiki.postgresql.org/wiki/Lock_Monitoring http://wiki.postgresql.org/wiki/Backend_killer_function I agree that these are manageable, but not necessarily trivial. (You should see how long it can take to get them to install new monitoring software to our centralized system here.) I think that's consistent with the save up our breaking changes to do them all at once approach. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] One-Shot Plans
Currently, the planner and executor are mostly independent of each other: the planner doesn't really know when the plan will be executed, and the executor doesn't know how recently the plan was made. We can work out the various paths through the traffic cop to see when a plan will be a one-shot - planned and then executed immediately, then discarded. In those cases we can take advantage of better optimisations. Most interestingly, we can evaluate stable functions at plan time, to allow us to handle partitioning and partial indexes better. Patch attached. Works... SET constraint_exclusion = on; ALTER TABLE table ADD CHECK (dt current_date - 5); SELECT * FROM table WHERE datecolumn = current_date - 1; QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) WIP in the sense that we might want to change the special case parameter handling as well. Comments? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services oneshot_plans.v2.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] One-Shot Plans
Simon Riggs wrote: Currently, the planner and executor are mostly independent of each other: the planner doesn't really know when the plan will be executed, and the executor doesn't know how recently the plan was made. We can work out the various paths through the traffic cop to see when a plan will be a one-shot - planned and then executed immediately, then discarded. I was also hoping someday allow plans that are to be immediately executed to probe the buffer cache to determine how expensive index scans would be. -- 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] use less space in xl_xact_commit patch
On Tue, Jun 14, 2011 at 4:21 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 25, 2011 at 3:05 PM, Simon Riggs si...@2ndquadrant.com wrote: Yes, that's correct. We can remove them from the normal commit record when nmsgs == 0. Leonardo, can you submit an updated version of this patch today that incorporates Simon's suggestion? The CommitFest starts tomorrow. If not, please feel free to resubmit to the next CommitFest. Simon or I may find the time to review it sooner rather than later even if it misses the deadline for this CommitFest, because I think it's an important optimization and I know you have other work that depends on it. But for now we should mark it Returned with Feedback if you don't have an updated version ready to go. We don't need to be in a hurry here. As the reviewer I'm happy to give Leonardo some time, obviously no more than the end of the commit fest. If he doesn't respond at all, I'll do it, but I'd like to give him the chance and the experience if 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] One-Shot Plans
On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: Currently, the planner and executor are mostly independent of each other: the planner doesn't really know when the plan will be executed, and the executor doesn't know how recently the plan was made. We can work out the various paths through the traffic cop to see when a plan will be a one-shot - planned and then executed immediately, then discarded. I was also hoping someday allow plans that are to be immediately executed to probe the buffer cache to determine how expensive index scans would be. Yes, it opens up many optimizations, both for cache sensitivity and dynamic data access. But those are later ideas based on the existence of this first step. -- 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
[HACKERS] Crash dumps
Hello, Because, I work a little bit on streaming protocol and from time to time I have crashes. I want ask if you wont crash reporting (this is one of minors products from mmap playing) those what I have there is mmaped areas, and call stacks, and some other stuff. This based reports works for Linux with gdb, but there is some pluggable architecture, which connects with segfault - one thing that should be considered is to kill other processes immediately when reporting started (as taking report takes some time) so some IPC will be required. I may polish this a little bit, and send patch for this (currently without IPC killing of others). Regards, Radosław Smogura -- 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] One-Shot Plans
Simon Riggs wrote: On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: Currently, the planner and executor are mostly independent of each other: the planner doesn't really know when the plan will be executed, and the executor doesn't know how recently the plan was made. We can work out the various paths through the traffic cop to see when a plan will be a one-shot - planned and then executed immediately, then discarded. I was also hoping someday allow plans that are to be immediately executed to probe the buffer cache to determine how expensive index scans would be. Yes, it opens up many optimizations, both for cache sensitivity and dynamic data access. But those are later ideas based on the existence of this first step. Agreed. -- 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] ITYM DROP TABLE
On Mon, Jun 13, 2011 at 10:44 PM, David E. Wheeler da...@kineticode.com wrote:
I was reading the partitioning docs when I spotted this. I think it means to
highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.
Best,
David
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4c9fc5d..0cdb800 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*** VALUES ('New York', NULL, NULL, 'NY');
*** 2332,2338
para
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning
design.
! commandALTER TABLE/ is far faster than a bulk operation.
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
/para
--- 2332,2338
para
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning
design.
! commandDROP TABLE/ is far faster than a bulk operation.
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
/para
That looks weird. I'm sure that *used* to say DROP TABLE.
--
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] SSI patch renumbered existing 2PC resource managers??
Bruce Momjian wrote:
This argument seems a tad peculiar, since the *entire* *point* of
pg_upgrade is to push physical files from one installation into another
even though compatibility isn't guaranteed. It is the program's duty to
understand enough to know whether it can transport the cluster's state
safely. Not to arbitrarily discard state because it might possibly not
be transportable.
Well, pg_upgrade succeeds because it does as little as necessary to do
the migration, relying on pg_dump to do much of the migration work at
the catalog level. pg_upgrade tries to be involved as little as
possible with the Postgres code so it doesn't have to be changed
regularly between major versions.
The prepared transaction case seems ugly enough that we don't want
pg_upgrade to have to check every major release if anything changed
about the data stored in prepared transactions. This is the same reason
pg_upgrade doesn't transfer WAL files from the old cluster, just pg_clog
files (which rarely changes its format).
I have applied the attached pg_upgrade patch to head and 9.1 to fail if
prepared transactions are in the old or new cluster.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index fdec6e3..376d25a
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** static void check_old_cluster_has_new_cl
*** 16,21
--- 16,22
static void check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl);
static void check_is_super_user(ClusterInfo *cluster);
+ static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
*** check_old_cluster(bool live_check,
*** 65,70
--- 66,72
* Check for various failure cases
*/
check_is_super_user(old_cluster);
+ check_for_prepared_transactions(old_cluster);
check_for_reg_data_type_usage(old_cluster);
check_for_isn_and_int8_passing_mismatch(old_cluster);
*** check_new_cluster(void)
*** 117,122
--- 119,125
get_db_and_rel_infos(new_cluster);
check_new_cluster_is_empty();
+ check_for_prepared_transactions(new_cluster);
check_old_cluster_has_new_cluster_dbs();
check_loadable_libraries();
*** check_is_super_user(ClusterInfo *cluster
*** 507,512
--- 510,545
/*
+ * check_for_prepared_transactions()
+ *
+ * Make sure there are no prepared transactions because the storage format
+ * might have changed.
+ */
+ static void
+ check_for_prepared_transactions(ClusterInfo *cluster)
+ {
+ PGresult *res;
+ PGconn *conn = connectToServer(cluster, template1);
+
+ prep_status(Checking for prepared transactions);
+
+ res = executeQueryOrDie(conn,
+ SELECT *
+ FROM pg_catalog.pg_prepared_xact());
+
+ if (PQntuples(res) != 0)
+ pg_log(PG_FATAL, The %s cluster contains prepared transactions\n,
+ CLUSTER_NAME(cluster));
+
+ PQclear(res);
+
+ PQfinish(conn);
+
+ check_ok();
+ }
+
+
+ /*
* check_for_isn_and_int8_passing_mismatch()
*
* contrib/isn relies on data type int8, and in 8.4 int8 can now be passed
--
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] use less space in xl_xact_commit patch
On Tue, Jun 14, 2011 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote: We don't need to be in a hurry here. As the reviewer I'm happy to give Leonardo some time, obviously no more than the end of the commit fest. Well, we certainly have the option to review and commit the patch any time up until feature freeze. However, I don't want the CommitFest application to be full of entries for patches that are not actually being worked on, because it makes it hard for reviewers to figure out which patches in a state where they can be usefully looked at. AIUI, this one is currently not, because it was reviewed three weeks ago and hasn't been updated. -- 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] [WIP] cache estimates, cache access cost
2011/6/14 Robert Haas robertmh...@gmail.com: On Tue, Jun 14, 2011 at 12:06 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 1. ANALYZE happens far too infrequently to believe that any data taken at ANALYZE time will still be relevant at execution time. ANALYZE happens when people execute it, else it is auto-analyze and I am not providing auto-analyze-oscache. ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The frequency is not important here, I believe. Well, I'm not saying you have to have all the answers to post a WIP patch, certainly. But in terms of getting something committable, it seems like we need to have at least an outline of what the long-term plan is. If ANALYZE OSCACHE is an infrequent operation, then the data isn't going to be a reliable guide to what will happen at execution time... Ok. 2. Using data gathered by ANALYZE will make plans less stable, and our users complain not infrequently about the plan instability we already have, therefore we should not add more. ...and if it is a frequent operation then it's going to result in unstable plans (and maybe pg_class bloat). There's a fundamental tension here that I don't think you can just wave your hands at. I don't want to hide that point, which is just correct. The idea is not to have something (which need to be) updated too much but it needs to be taken into account. I was trying to split the patch size by group of features to reduce its size. The work is in progress. Totally reasonable, but I can't see committing any of it without some evidence that there's light at the end of the tunnel. No performance tests *whatsoever* have been done. We can debate the exact amount of evidence that should be required to prove that something is useful from a performance perspective, but we at least need some. I'm beating on this point because I believe that the whole idea of trying to feed this information back into the planner is going to turn out to be something that we don't want to do. I think it's going to turn out to have downsides that are far larger than the upsides. it is possible, yes. I try to do changes in a way that if the reloscache values is the one by default then the planner keep the same behavior than in the past. I am completely willing to be be proven wrong, but right now I think this will make things worse and you think it will make things better and I don't see any way to bridge that gap without doing some measurements. correct. For example, if you run this patch on a system and subject that system to a relatively even workload, how much do the numbers bounce around between runs? What if you vary the workload, so that you blast it with OLTP traffic at some times and then run reporting queries at other times? Or different tables become hot at different times? This is all true, this is *already* true. Like the thread about random_page_cost vs index_page_cost where the good option is to change the parameters at certain moment in the day (IIRC the use case). I mean that I agree that those benchs need to be done, hopefully I can fix some usecases, while not breaking others too much or not at all, or ... Once you've written code to make the planner do something with the caching % values, then you can start to explore other questions. Can you generate plan instability, especially on complex queries, which are more prone to change quickly based on small changes in the cost estimates? Can you demonstrate a workload where bad performance is inevitable with the current code, but with your code, the system My next step is cost estimation changes. I have already some very small usecases where the minimum changes I did so far are interesting but it is not enought to come with that as evidences. becomes self-tuning and ends up with good performance? What happens if you have a large cold table with a small hot end where all activity is concentrated? We are at step 3 here :-) I have already some ideas to handle those situations but not yet polished. The current idea is to be conservative, like PostgreSQL used to be, for example: /* * disk and cache costs * this assumes an agnostic knowledge of the data repartition and query * usage despite large tables may have a hot part of 10% which is the only * requested part or that we select only (c)old data so the cache useless. * We keep the original strategy to not guess too much and just ponderate * the cost globaly. */ run_cost += baserel-pages * ( spc_seq_page_cost * (1 - baserel-oscache) + cache_page_cost * baserel-oscache); -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent
Re: [HACKERS] ITYM DROP TABLE
Excerpts from Tom Lane's message of mar jun 14 13:04:30 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Done that way (9.0 and beyond). Re-reading the actual commit, I notice that there's now a grammatical problem: the following sentence says It also entirely avoids the commandVACUUM/command overhead caused by a bulk commandDELETE/. which was okay when it referred to ALTER TABLE, but now that there are two commands mentioned in the previous sentence, it doesn't match. Perhaps These commands also avoid the Yeah, fixed. -- Á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] psql describe.c cleanup
Excerpts from Josh Kupershmidt's message of sáb may 07 16:40:35 -0300 2011: And while I'm griping about describe.c, is it just me or is the source code indentation in that file totally screwy? I'm using emacs and I've loaded the snippet for pgsql-c-mode from ./src/tools/editors/emacs.samples into my ~/.emacs, but the indentation of multiline strings still looks inconsistent. See attached screenshot, e.g. the start of line 80 has four tabs and one space, while line 79 has six tabs and two spaces? pgindent moves strings back to the left when it thinks they fit within 80 columns. Yes, that seems pretty screwy. -- Á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
[HACKERS] perltidy
How much do we care about applying perltidy, as described in src/tools/msvc/README, everywhere? I just ran it across the entire tree, using perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm and it generated 6531 lines of (unified) diff, of which 357 are in src/tools/msvc/ itself. So clearly it's not being applied very consistently. Given how easily this appears to work and how we're sneakily expanding the use of Perl, I think we ought to add this to the standard pgindent routine. -- 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] perltidy
Peter Eisentraut wrote: How much do we care about applying perltidy, as described in src/tools/msvc/README, everywhere? I just ran it across the entire tree, using perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm and it generated 6531 lines of (unified) diff, of which 357 are in src/tools/msvc/ itself. So clearly it's not being applied very consistently. Given how easily this appears to work and how we're sneakily expanding the use of Perl, I think we ought to add this to the standard pgindent routine. Yes, I would support that. -- 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] perltidy
On Tue, Jun 14, 2011 at 21:39, Bruce Momjian br...@momjian.us wrote: Peter Eisentraut wrote: How much do we care about applying perltidy, as described in src/tools/msvc/README, everywhere? I just ran it across the entire tree, using perltidy -b -bl -nsfs -naws -l=100 -ole=unix **/*.pl **/*.pm and it generated 6531 lines of (unified) diff, of which 357 are in src/tools/msvc/ itself. So clearly it's not being applied very consistently. Given how easily this appears to work and how we're sneakily expanding the use of Perl, I think we ought to add this to the standard pgindent routine. Yes, I would support that. I think I suggested that before at some point, but can't find the reference. But that means, +1. -- 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: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Mon, Jun 13, 2011 at 9:09 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I noticed that pgbench's doCustom (the function highest in the profile posted) returns doing nothing if the connection is supposed to be sleeping; seems an open door for busy waiting. I didn't check the rest of the code to see if there's something avoiding that condition. Yes, there is a select in threadRun that avoids that. Also, I don't think anyone would but in a sleep in this particular type of pgbench run. I also noticed that it seems to be very liberal about calling INSTR_TIME_SET_CURRENT in the same function which perhaps could be optimizing by calling it a single time at entry and reusing the value, but I guess that would show up in the profile as a kernel call so it's maybe not a problem. I think that only gets called when you specifically asked for latencies or for logging, or when making new connection (which should be rare) 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] Detailed documentation for external calls (threading, shared resources etc)
Seref Arikan serefari...@kurumsalteknoloji.com writes: This is actually a request for documentation guidance. I intend to develop an extension to postgresql. Basically I'd like to place calls to network using ZeroMQ, and I need to have detailed information about You didn't tell us about what you want to achieve, only how. It could be that your problem could be solved with a custom PGQ consumer, that leaves well outside the database server. PGQ is the generic queue layer on which the replication system Londiste is built. http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial http://wiki.postgresql.org/wiki/Londiste_Tutorial 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
[HACKERS] Polecat quit unexpectdly
Stack trace, nothing else. Now that the horse has left the barn, I changed to keep error builds...Process: postgres [17457]Path: /Volumes/High Usage/usr/local/src/build-farm-4.4/builds/HEAD/pgsql.6849/src/test/regress/tmp_check/install/usr/local/src/build-farm-4.4/builds/HEAD/inst/bin/postgresIdentifier: postgresVersion: ??? (???)Code Type:X86-64 (Native)Parent Process: postgres [17136]Date/Time:2011-06-14 05:00:27.466 -0600OS Version: Mac OS X 10.6.7 (10J869)Report Version: 6Interval Since Last Report: 658000 secCrashes Since Last Report: 1Per-App Crashes Since Last Report: 1Anonymous UUID: 77053C10-A2B5-4078-A796-5862E233A1ACException Type: EXC_CRASH (SIGABRT)Exception Codes: 0x, 0xCrashed Thread: 0 Dispatch queue: com.apple.main-threadApplication Specific Information:abort() calledThread 0 Crashed: Dispatch queue: com.apple.main-thread0 libSystem.B.dylib 0x7fff821815d6 __kill + 101 libSystem.B.dylib 0x7fff82221cd6 abort + 832 postgres 0x0001002fcd52 0x1 + 31327543 postgres 0x00010005cafa multixact_twophase_postcommit + 74 (multixact.c:1367)4 postgres 0x00010005deab ProcessRecords + 91 (twophase.c:1407)5 postgres 0x00010005f78a FinishPreparedTransaction + 1610 (twophase.c:1368)6 postgres 0x000100236a5a PortalRunUtility + 298 (palloc.h:88)7 postgres 0x0001002381c5 PortalRunMulti + 597 (pquery.c:1315)8 postgres 0x000100238b78 PortalRun + 984 (pquery.c:817)9 postgres 0x000100234b2d exec_simple_query + 589 (postgres.c:1025)10 postgres 0x0001002356f1 PostgresMain + 1937 (postgres.c:3926)11 postgres 0x0001001e82ec ServerLoop + 2780 (postmaster.c:3600)12 postgres 0x0001001e9251 PostmasterMain + 2753 (postmaster.c:1120)13 postgres 0x00010017c305 main + 965 (main.c:199)14 postgres 0x00010b14 start + 52Thread 0 crashed with X86 Thread State (64-bit):rax: 0x rbx: 0x0018 rcx: 0x7fff5fbfd3b8 rdx: 0xrdi: 0x4431 rsi: 0x0006 rbp: 0x7fff5fbfd3d0 rsp: 0x7fff5fbfd3b8 r8: 0x0004 r9: 0x r10: 0x7fff8217d616 r11: 0xff80002e28b0r12: 0x006a r13: 0x000100508980 r14: 0x0aba r15: 0x0001004ffb20rip: 0x7fff821815d6 rfl: 0x0202 cr2: 0x7fff70419230Binary Images: 0x1 -0x1004fbfef +postgres ??? (???) E4B56AD5-5ABD-37F5-8D5A-ADE9F24372F2 /Volumes/High Usage/usr/local/src/build-farm-4.4/builds/HEAD/pgsql.6849/src/test/regress/tmp_check/install/usr/local/src/build-farm-4.4/builds/HEAD/inst/bin/postgres 0x7fff5fc0 - 0x7fff5fc3bdef dyld 132.1 (???) 486E6C61-1197-CC7C-2197-82CE505102D7 /usr/lib/dyld 0x7fff80b92000 - 0x7fff80cb3fe7 libcrypto.0.9.8.dylib 0.9.8 (compatibility 0.9.8) 48AEAFE1-21F4-B3C8-4199-35AD5E8D0613 /usr/lib/libcrypto.0.9.8.dylib 0x7fff80cb4000 - 0x7fff80cc5ff7 libz.1.dylib 1.2.3 (compatibility 1.0.0) 97019C74-161A-3488-41EC-A6CA8738418C /usr/lib/libz.1.dylib 0x7fff80df1000 - 0x7fff80f08fef libxml2.2.dylib 10.3.0 (compatibility 10.0.0) 1B27AFDD-DF87-2009-170E-C129E1572E8B /usr/lib/libxml2.2.dylib 0x7fff82132000 - 0x7fff822f3fff libSystem.B.dylib 125.2.10 (compatibility 1.0.0) 9BAEB2F2-B485-6349-E1AB-637FE12EE770 /usr/lib/libSystem.B.dylib 0x7fff8330 - 0x7fff834befff libicucore.A.dylib 40.0.0 (compatibility 1.0.0) 2C6ECACF-CD56-1714-6F63-CB6F5EE7A1E2 /usr/lib/libicucore.A.dylib 0x7fff835a7000 - 0x7fff835b9fe7 libsasl2.2.dylib 3.15.0 (compatibility 3.0.0) 76B83C8D-8EFE-4467-0F75-275648AFED97 /usr/lib/libsasl2.2.dylib 0x7fff83c6c000 - 0x7fff83c70ff7 libmathCommon.A.dylib 315.0.0 (compatibility 1.0.0) 95718673-FEEE-B6ED-B127-BCDBDB60D4E5 /usr/lib/system/libmathCommon.A.dylib 0x7fff843c3000 - 0x7fff843c4ff7 com.apple.TrustEvaluationAgent 1.1 (1) 5952A9FA-BC2B-16EF-91A7-43902A5C07B6 /System/Library/PrivateFrameworks/TrustEvaluationAgent.framework/Versions/A/TrustEvaluationAgent 0x7fff85c2e000 - 0x7fff85cabfef libstdc++.6.dylib 7.9.0 (compatibility 7.0.0) 35ECA411-2C08-FD7D-11B1-1B7A04921A5C /usr/lib/libstdc++.6.dylib 0x7fff85d27000 - 0x7fff85d48fff libresolv.9.dylib 41.0.0 (compatibility 1.0.0) 9F322F47-0584-CB7D-5B73-9EBD670851CD /usr/lib/libresolv.9.dylib 0x7fff85e5c000 - 0x7fff85e99fff com.apple.LDAPFramework 2.0 (120.1) 54A6769E-D7E2-DBE2-EA61-87B9EA355DA4 /System/Library/Frameworks/LDAP.framework/Versions/A/LDAP 0x7fff87cdd000 - 0x7fff87d17fff libssl.0.9.8.dylib 0.9.8 (compatibility 0.9.8) C7153747-50E3-32DA-426F-CC4C505D1D6C /usr/lib/libssl.0.9.8.dylib 0x7fe0 - 0x7fe01fff libSystem.B.dylib ??? (???) 9BAEB2F2-B485-6349-E1AB-637FE12EE770 /usr/lib/libSystem.B.dylibModel: MacBookPro5,1, BootROM MBP51.007E.B05, 2 processors, Intel Core 2 Duo, 2.53 GHz, 6 GB, SMC 1.33f8Graphics: NVIDIA GeForce
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 06/14/2011 02:27 AM, Jeff Janes wrote: On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: ... so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. Hi Stefan, pgbench sends each query (per connection) and waits for the reply before sending another. Do we know whether sysbench does that, or if it just stuffs the kernel's IPC buffer full of queries without synchronously waiting for individual replies? I can't get sysbench to make for me, or I'd strace in single client mode and see what kind of messages are going back and forth. yeah sysbench compiled from a release tarball needs some autoconf/makefile hackery to get running on a modern system - but I can provide you with the data you are interested in if you tell me exactly what you are looking for... Stefan -- 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] Polecat quit unexpectdly
Robert Creager robert.crea...@oracle.com wrote: Stack trace, nothing else. 3 postgres 0x00010005cafa multixact_twophase_postcommit + 74 (multixact.c:1367) 4 postgres 0x00010005deab ProcessRecords + 91 (twophase.c:1407) 5 postgres 0x00010005f78a FinishPreparedTransaction + 1610 (twophase.c:1368) If this was a checkout from more than about 7 hours ago and less than about 10 hours ago, please get a fresh copy of the source and try again. -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] procpid?
On 06/14/2011 02:20 PM, Kevin Grittner wrote: Just on our Wiki pages we have some queries available for copy/paste which would need multiple versions while both column names were in supported versions of the software: http://wiki.postgresql.org/wiki/Lock_dependency_information http://wiki.postgresql.org/wiki/Lock_Monitoring http://wiki.postgresql.org/wiki/Backend_killer_function ...and most of these would actually be simplified if they could just JOIN on pid instead of needing this common idiom: join pg_catalog.pg_stat_activity ka on kl.pid = ka.procpid Yes, there are a lot of these floating around. I'd bet that in an hour of research I could find 95% of them though, and make sure they were all updated in advance of the release. (I already did most of this search as part of stealing every good idea I could find in this area for my book) I think that's consistent with the save up our breaking changes to do them all at once approach. I don't actually buy into this whole idea at all. We already have this big wall at 8.3 because changes made in that release are too big for people on the earlier side to upgrade past. I'd rather see a series of smaller changes in each release, even if they are disruptive, so that no one version turns into a frustrating hurdle seen as impossible to clear. This adjustment is a perfect candidate for putting into 9.2 to me, because I'd rather reduce max(breakage) across releases than intentionally aim at increasing it but bundling them into larger clumps. For me, the litmus test is whether the change provides enough improvement that it outweighs the disruption when the user runs into it. This is why I suggested a specific, useful, and commonly requested (to me at least) change to pg_stat_activity go along with this. If people discover their existing pg_stat_activity tools break, presumably they're going to look at the view again to see what changed. When they do that, I don't want the reaction to be why was this random change made? I want it to be look, there are useful new fields in here; let me see if I can use them too here. That's how you make people tolerate disruption in upgrades. If they see a clear improvement in the same spot when forced to fix around it, the experience is much more pleasant if they get something new out of it too. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] procpid?
Excerpts from Bruce Momjian's message of mar jun 14 12:59:15 -0400 2011: Well, someone doing SELECT *, which is probably 90% of the users, are going to be pretty confused by duplicate columns, asking, What is the difference? For those people this would make things worse than they are now. I would say 90% of users are doing SELECT *, and 10% are joining to other tables or displaying specific columns. We want to help that 10% without making that 90% confused. I think if you had column synonyms, you would get only a single one when doing select *. The other name would still be accepted in a query that explicitely asked for it. -- Á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] [WIP] cache estimates, cache access cost
Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011: Attached are updated patches without the plugin itself. I've also added the cache_page_cost GUC, this one is not per tablespace, like others page_cost. There are 6 patches: 0001-Add-reloscache-column-to-pg_class.patch Hmm, do you really need this to be a new column? Would it work to have it be a reloption? -- Á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
[HACKERS] possible connection leak in dblink?
With gcc 4.6, I get this warning: dblink.c: In function ‘dblink_send_query’: dblink.c:620:7: warning: variable ‘freeconn’ set but not used [-Wunused-but-set-variable] I don't know much about the internals of dblink, but judging from the surrounding code, I guess that this fix is necessary: diff --git i/contrib/dblink/dblink.c w/contrib/dblink/dblink.c index 19b98fb..e014c1a 100644 --- i/contrib/dblink/dblink.c +++ w/contrib/dblink/dblink.c @@ -634,6 +634,10 @@ dblink_send_query(PG_FUNCTION_ARGS) if (retval != 1) elog(NOTICE, %s, PQerrorMessage(conn)); + /* if needed, close the connection to the database and cleanup */ + if (freeconn) + PQfinish(conn); + PG_RETURN_INT32(retval); } Otherwise the connection might not get freed. Could someone verify that? -- 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] One-Shot Plans
Simon Riggs si...@2ndquadrant.com writes: Currently, the planner and executor are mostly independent of each other: the planner doesn't really know when the plan will be executed, and the executor doesn't know how recently the plan was made. We can work out the various paths through the traffic cop to see when a plan will be a one-shot - planned and then executed immediately, then discarded. I have already got plans for a significantly more sophisticated approach to this. In those cases we can take advantage of better optimisations. Most interestingly, we can evaluate stable functions at plan time, to allow us to handle partitioning and partial indexes better. I don't believe that's correct in detail. 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] Polecat quit unexpectdly
Robert Creager robert.crea...@oracle.com wrote: You believe it was related to the flurry of errors that popped up then. I haven't looked at all the error in the flurry. I think your particular report is consistent with being caused by this commit: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=b81831acbc671445061ed41a55fb1cc21d8e2979 and solved by this one: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=85ea93384ae21ff59f5e5b292884a86f9c10b852 I suspect that other builds based on checkouts in this window of time would probably show similar errors. -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] procpid?
On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote: There are real problems with the idea of having one release where we break everything that we want to break - mostly from a process standpoint. We aren't always good at being organized and disciplined, and coming up with a multi-year plan to break everything all at once in 2014 for release in 2015 may be difficult, because it requires a consensus on release management to hold together for years, and sometimes we can't even manage days. I have had this fantasy of a break-everything release for a long time as well, but frankly, experience from other projects such as Python 3, Perl 6, KDE 4, Samba 4, add-yours-here, indicates that such things might not work out so well. OK, some of those were rewrites as well as interface changes, but the effect visible to the end user is mostly the same. -- 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] cache estimates, cache access cost
2011/6/14 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011: Attached are updated patches without the plugin itself. I've also added the cache_page_cost GUC, this one is not per tablespace, like others page_cost. There are 6 patches: 0001-Add-reloscache-column-to-pg_class.patch Hmm, do you really need this to be a new column? Would it work to have it be a reloption? If we can have ALTER TABLE running on heavy workload, why not. I am bit scared by the effect of such reloption, it focus on HINT oriented strategy when I would like to allow a dynamic strategy from the server. This work is not done and may not work, so a reloption is good at least as a backup (and is more in the idea suggested by Tom and others) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Cédric Villemain 2ndQuadrant 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] creating CHECK constraints as NOT VALID
Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:
I think that you also need to update the constraint exclusion code
(get_relation_constraints() or nearby), otherwise the planner might
exclude a relation on the basis of a CHECK constraint that is not
currently VALID.
Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix
this is quite simple. I don't have it handy right now but I'll post it
soon.
Here's the complete patch.
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***
*** 1898,1904
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
! entryHas the constraint been validated? Can only be false for foreign
keys/entry
/row
row
--- 1898,1904
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
! entryHas the constraint been validated? Can only be false for foreign
keys and CHECK constraints/entry
/row
row
*** a/doc/src/sgml/ref/alter_domain.sgml
--- b/doc/src/sgml/ref/alter_domain.sgml
***
*** 28,37 ALTER DOMAIN replaceable class=PARAMETERname/replaceable
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
{ SET | DROP } NOT NULL
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
DROP CONSTRAINT replaceable
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
OWNER TO replaceable class=PARAMETERnew_owner/replaceable
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
--- 28,39
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
{ SET | DROP } NOT NULL
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable [ NOT
VALID ]
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
DROP CONSTRAINT replaceable
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
+ VALIDATE CONSTRAINT replaceable
class=PARAMETERconstraint_name/replaceable
+ ALTER DOMAIN replaceable class=PARAMETERname/replaceable
OWNER TO replaceable class=PARAMETERnew_owner/replaceable
ALTER DOMAIN replaceable class=PARAMETERname/replaceable
SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
***
*** 70,82 ALTER DOMAIN replaceable class=PARAMETERname/replaceable
/varlistentry
varlistentry
! termADD replaceable
class=PARAMETERdomain_constraint/replaceable/term
listitem
para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
! This will only succeed if all columns using the domain satisfy the
! new constraint.
/para
/listitem
/varlistentry
--- 72,88
/varlistentry
varlistentry
! termADD replaceable class=PARAMETERdomain_constraint/replaceable
[ NOT VALID ]/term
listitem
para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
! If NOT VALID is not specified,
! the command will only succeed if all columns using the
! domain satisfy the new constraint.
! The constraint is going to be enforced on new data inserted into
columns
! using the domain in all cases, regardless of literalNOT VALID/.
! literalNOT VALID/ is only accepted for literalCHECK/
constraints.
/para
/listitem
/varlistentry
***
*** 91,96 ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 97,113
/varlistentry
varlistentry
+ termVALIDATE CONSTRAINT/term
+ listitem
+ para
+ This form validates a constraint previously added, that is, verify that
+ all data in columns using the domain satisfy the specified constraint.
+ /para
+ /listitem
+/varlistentry
+
+
+varlistentry
termOWNER/term
listitem
para
***
*** 156,161 ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 173,188
/varlistentry
varlistentry
+ termreplaceable class=PARAMETERNOT VALID/replaceable/term
+ listitem
+para
+ Do not verify existing column data for constraint validity.
+/para
+ /listitem
+ /varlistentry
+
+
+ varlistentry
termliteralCASCADE/literal/term
listitem
para
***
*** 251,257 ALTER DOMAIN zipcode SET SCHEMA customers;
para
Re: [HACKERS] [WIP] cache estimates, cache access cost
Excerpts from Cédric Villemain's message of mar jun 14 17:10:20 -0400 2011: If we can have ALTER TABLE running on heavy workload, why not. I am bit scared by the effect of such reloption, it focus on HINT oriented strategy when I would like to allow a dynamic strategy from the server. This work is not done and may not work, so a reloption is good at least as a backup (and is more in the idea suggested by Tom and others) Hmm, sounds like yet another use case for pg_class_nt. Why do these keep popping up? -- Á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] creating CHECK constraints as NOT VALID
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011: Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011: I think that you also need to update the constraint exclusion code (get_relation_constraints() or nearby), otherwise the planner might exclude a relation on the basis of a CHECK constraint that is not currently VALID. Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix this is quite simple. I don't have it handy right now but I'll post it soon. Here's the complete patch. this doesn't apply -- 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: [HACKERS] Why polecat and colugos are failing to build back branches
Andrew Dunstan and...@dunslane.net writes: On 06/13/2011 08:05 PM, Tom Lane wrote: I looked into $SUBJECT. There appear to be two distinct issues: ... I think we can be a bit more liberal about build patches than things that can affect the runtime behaviour. So +1 for fixing both of these. I've committed patches that fix these issues on my own OS X machine, though it remains to be seen whether polecat and colugos will like them. It turns out that whatever setup Robert has got with '/Volumes/High Usage/' is really *not* fully exercising the system as far as space-containing paths go, because I found bugs in all active branches when I tried to do builds and installs underneath '/Users/tgl/foo bar/'. Is it worth setting up a buildfarm critter to exercise the case on a long-term basis? If we don't, I think we can expect that it'll break regularly. (I wouldn't care to bet that the MSVC case works yet, either.) 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] procpid?
Peter Eisentraut wrote: On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote: There are real problems with the idea of having one release where we break everything that we want to break - mostly from a process standpoint. We aren't always good at being organized and disciplined, and coming up with a multi-year plan to break everything all at once in 2014 for release in 2015 may be difficult, because it requires a consensus on release management to hold together for years, and sometimes we can't even manage days. I have had this fantasy of a break-everything release for a long time as well, but frankly, experience from other projects such as Python 3, Perl 6, KDE 4, Samba 4, add-yours-here, indicates that such things might not work out so well. OK, some of those were rewrites as well as interface changes, but the effect visible to the end user is mostly the same. Funny you mentioned Perl 6 because I just blogged about that: http://momjian.us/main/blogs/pgblog/2011.html#June_14_2011 -- 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] procpid?
Peter Eisentraut pete...@gmx.net writes: On tis, 2011-06-14 at 13:50 -0400, Robert Haas wrote: There are real problems with the idea of having one release where we break everything that we want to break - mostly from a process standpoint. We aren't always good at being organized and disciplined, and coming up with a multi-year plan to break everything all at once in 2014 for release in 2015 may be difficult, because it requires a consensus on release management to hold together for years, and sometimes we can't even manage days. I have had this fantasy of a break-everything release for a long time as well, but frankly, experience from other projects such as Python 3, Perl 6, KDE 4, Samba 4, add-yours-here, indicates that such things might not work out so well. OK, some of those were rewrites as well as interface changes, but the effect visible to the end user is mostly the same. Good point. I think the case that has actually been discussed is the idea of saving up binary-compatibility breaks (on-disk format changes). That seems sensible. It doesn't create a bigger problem for users, since a dump/reload is a dump/reload no matter how many individual format changes happened underneath. But we should be wary of applying that approach to application-visible incompatibilities. As far as Greg's proposal is concerned, I don't see how a proposed addition of two columns would justify renaming an existing column. Additions should not break any sanely-implemented application, but renamings certainly will. 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] Why polecat and colugos are failing to build back branches
On 06/14/2011 05:45 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 06/13/2011 08:05 PM, Tom Lane wrote: I looked into $SUBJECT. There appear to be two distinct issues: ... I think we can be a bit more liberal about build patches than things that can affect the runtime behaviour. So +1 for fixing both of these. I've committed patches that fix these issues on my own OS X machine, though it remains to be seen whether polecat and colugos will like them. It turns out that whatever setup Robert has got with '/Volumes/High Usage/' is really *not* fully exercising the system as far as space-containing paths go, because I found bugs in all active branches when I tried to do builds and installs underneath '/Users/tgl/foo bar/'. Is it worth setting up a buildfarm critter to exercise the case on a long-term basis? If we don't, I think we can expect that it'll break regularly. (I wouldn't care to bet that the MSVC case works yet, either.) Well, OSX is just using our usual *nix paraphernalia, so if it's broken won't all such platforms probably be broken too? I'd actually bet a modest amount MSVC is less broken because it uses perl modules like File::Copy to do most of its work and so will be less prone to shell parsing breakage. But yes, we should check regularly. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] cache estimates, cache access cost
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Cédric Villemain's message of mar jun 14 10:29:36 -0400 2011: 0001-Add-reloscache-column-to-pg_class.patch Hmm, do you really need this to be a new column? Would it work to have it be a reloption? If it's to be updated in the same way as ANALYZE updates reltuples and relpages (ie, an in-place non-transactional update), I think it'll have to be a real column. 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] Why polecat and colugos are failing to build back branches
Andrew Dunstan and...@dunslane.net writes: On 06/14/2011 05:45 PM, Tom Lane wrote: I've committed patches that fix these issues on my own OS X machine, Well, OSX is just using our usual *nix paraphernalia, so if it's broken won't all such platforms probably be broken too? Yes, certainly. The reason I specified OS X in particular is that I only tested the darwin branch of Makefile.shlib. The -install_name switch that was the problem there is specific to OS X, but I wouldn't be surprised if some of the other branches have their own platform- specific issues. I'd actually bet a modest amount MSVC is less broken Very possibly, but unless it's being tested it's no sure bet. 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] [WIP] cache estimates, cache access cost
On 06/14/2011 01:16 PM, Robert Haas wrote: But there's no reason that code (which may or may not eventually prove useful) has to be incorporated into the main tree. We don't commit code so people can go benchmark it; we ask for the benchmarking to be done first, and then if the results are favorable, we commit the code. Who said anything about this being a commit candidate? The WIP in the subject says it's not intended to be. The community asks people to submit design ideas early so that ideas around them can be explored publicly. One of the things that needs to be explored, and that could use some community feedback, is exactly how this should be benchmarked in the first place. This topic--planning based on cached percentage--keeps coming up, but hasn't gone very far as an abstract discussion. Having a patch to test lets it turn to a concrete one. Note that I already listed myself as the reviewer here, so it's not even like this is asking explicitly for a community volunteer to help. Would you like us to research this privately and then dump a giant patch that is commit candidate quality on everyone six months from now, without anyone else getting input to the process, or would you like the work to happen here? I recommended Cédric not ever bother soliciting ideas early, because I didn't want to get into this sort of debate. I avoid sending anything here unless I already have a strong idea about the solution, because it's hard to keep criticism at bay even with that. He was more optimistic about working within the community contribution guidelines and decided to send this over early instead. If you feel this is too rough to even discuss, I'll mark it returned with feedback and we'll go develop this ourselves. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] cache estimates, cache access cost
Greg Smith wrote: On 06/14/2011 01:16 PM, Robert Haas wrote: But there's no reason that code (which may or may not eventually prove useful) has to be incorporated into the main tree. We don't commit code so people can go benchmark it; we ask for the benchmarking to be done first, and then if the results are favorable, we commit the code. Who said anything about this being a commit candidate? The WIP in the subject says it's not intended to be. The community asks people to submit design ideas early so that ideas around them can be explored publicly. One of the things that needs to be explored, and that could use some community feedback, is exactly how this should be benchmarked in the first place. This topic--planning based on cached percentage--keeps coming up, but hasn't gone very far as an abstract discussion. Having a patch to test lets it turn to a concrete one. Note that I already listed myself as the reviewer here, so it's not even like this is asking explicitly for a community volunteer to help. Would you like us to research this privately and then dump a giant patch that is commit candidate quality on everyone six months from now, without anyone else getting input to the process, or would you like the work to happen here? I recommended C?dric not ever bother soliciting ideas early, because I didn't want to get into this sort of debate. I avoid sending anything here unless I already have a strong idea about the solution, because it's hard to keep criticism at bay even with that. He was more optimistic about working within the community contribution guidelines and decided to send this over early instead. If you feel this is too rough to even discuss, I'll mark it returned with feedback and we'll go develop this ourselves. I would like to see us continue researching in this direction. I think perhaps the background writer would be ideal for collecting this information because it scans the buffer cache already, and frequently. (Yes, I know it can't access databases.) I think random_page_cost is a dead-end --- it will never be possible for it to produce the right value for us. Its value is tied up in caching, e.g. the default 4 is not the right value for a physical drive (it should be much higher), but kernel and shared buffer caching require it to be a hybrid number that isn't really realistic. And once we have caching in that number, it is not going to be even caching for all tables, obviously. Hence, there is no way for random_page_cost to be improved and we have to start thinking about alternatives. Basically, random_page_cost is a terrible setting and we have to admit that and move forward. I realize the concerns about unstable plans, and we might need to give users the option of stable plans with a fixed random_page_cost, but at this point we don't even have enough data to know we need that. What we do know is that random_page_cost is inadequate. -- 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
