Re: [HACKERS] k-neighbourhood search in databases
Wow, custom solution for 2008 still much faster Denali 2011 solution. Also, what's about not spatial data types ? In our approach, we can provide knn for any datatype, which has GiST index and distance method. Oleg On Fri, 8 Apr 2011, Jeremiah Peschka wrote: On 4/8/11 5:21 AM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. SQL Server provides some knn search functionality[1] with enhancements coming this November in SQL 11[2]. [1]: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx [2]: http://www.sqlskills.com/BLOGS/BOBB/post/The-nearest-neighbor-optimization-in-SQL-Server-Denali.aspx Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] BUG #5856: pg_attribute.attinhcount is not correct.
On Sun, Apr 03, 2011 at 09:53:57PM -0400, Robert Haas wrote:
On Fri, Apr 1, 2011 at 12:56 AM, Noah Misch n...@leadboat.com wrote:
On Thu, Mar 31, 2011 at 11:11:49AM -0400, Robert Haas wrote:
On Thu, Mar 31, 2011 at 6:06 AM, Noah Misch n...@leadboat.com wrote:
The best way I can see is to make ATExecAddColumn more like
ATExecDropColumn,
ATAddCheckConstraint, and ATExecDropConstraint. ?Namely, recurse at
Exec-time
rather than Prep-time, and cease recursing when we satisfy the ADD
COLUMN with a
merge. ?Did you have something else in mind?
I had exactly what you just said in mind.
Patch attached, then.
Committed.
Thanks. This turns out to have caused that TOAST creation regression:
On Fri, Apr 08, 2011 at 08:12:19PM -0400, Noah Misch wrote:
[pg_upgrade/typed table business]
I also tested a regular dump+reload of the regression database, and a
pg_upgrade
of the same. The latter failed further along, due (indirectly) to this
failure
to create a TOAST table:
create table p ();
create table ch () inherits (p);
alter table p add column a text;
select oid::regclass,reltoastrelid from pg_class where oid::regclass IN
('p','ch');
insert into ch values (repeat('x', 100));
If I drop table a_star cascade in the regression database before attempting
pg_upgrade, it completes cleanly.
Since ATExecAddColumn now handles the recursion, child table work queue entries
no longer have AT_PASS_ADD_COL subcommands. Consequently, this heuristic in
ATRewriteCatalogs skips over them:
if (tab-relkind == RELKIND_RELATION
(tab-subcmds[AT_PASS_ADD_COL] ||
tab-subcmds[AT_PASS_ALTER_TYPE] ||
tab-subcmds[AT_PASS_COL_ATTRS]))
AlterTableCreateToastTable(tab-relid, (Datum) 0);
SET STORAGE uses AT_PASS_MISC, so this test case also omits a TOAST table:
set client_min_messages = debug1; -- display toast creation
create table t (a text); -- makes toast
alter table t alter a set storage plain;
alter table t add b int default 0; -- rewrite the table - no toast
alter table t alter a set storage extended; -- no toast added?
insert into t (a) values (repeat('x', 100)); -- fails
My first thought was to figure that the cost of needs_toast_table() is not a
concern and simply remove the pass-usage heuristic. However, we don't want
AlterTableCreateToastTable acquiring an AccessExclusiveLock for ALTER TABLE
recipes that only acquired ShareUpdateExclusiveLock. I see these options:
1. Upgrade AT_SetStorage to take AccessExclusiveLock. Add a maybe_create_toast
field to AlteredTableInfo. Have the AT_SetStorage, AT_AlterColumnType and
AT_AddColumn implementations set it.
2. Upgrade AT_SetStorage to take AccessExclusiveLock. In ATRewriteCatalogs,
replace the pass-usage heuristic with a test for locklevel ==
AccessExclusiveLock. This smells more like a hack, but it might be less
vulnerable to omissions. On the other hand, the set of operations that could
add TOAST tables are not particularly liable to grow.
3. Make AlterTableCreateToastTable acquire only ShareUpdateExclusiveLock and
remove the pass-usage heuristic from ATRewriteCatalogs. For this to be valid,
toast_insert_or_update() must behave reasonably in the face of a relation
concurrently acquiring a TOAST table. Since it takes reltoastrelid from the
relcache, toast_insert_or_update() will not act on the change in the middle of a
single call. Even if it did, I don't see any risks.
I'd lean toward #3 if someone else is also confident in its correctness.
Otherwise, #1 seems like the way to go. Preferences? Other ideas?
Thanks,
nm
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature request: pg_basebackup --force
On Sat, Apr 9, 2011 at 20:26, Joshua Berkus j...@agliodbs.com wrote: Magnus, all: It seems a bit annoying to have to do an rm -rf * $PGDATA/ before resynching a standby using pg_basebackup. This means that I still need to wrap basebackup in a shell script, instead of having it do everything for me ... especially if I have multiple tablespaces. Couldn't we have a --force option which would clear all data and tablespace directories before resynching? That could certainly be useful, yes. But I have a feeling whomever tries to get that into 9.1 will be killed - but it's certainly good to put ont he list of things for 9.2. -- 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] switch UNLOGGED to LOGGED
On Sat, Apr 9, 2011 at 3:29 AM, Leonardo Francalanci m_li...@yahoo.it wrote: I'm pretty sure we wouldn't accept a patch for a feature that would only work with wal_level=minimal, but it might be a useful starting point for someone else to keep hacking on. I understand. Reading your post at http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php I thought I got the part: what happens if we *crash* without writing an abort record? It seems like that could leave a stray file around on a standby, because the current code only cleans things up on the standby at the start of recovery But re-reading it, I don't understand: what's the difference in creating a new regular table and crashing before emitting the abort record, and converting an unlogged table to logged and crashing before emitting the abort record? How do the standby servers handle a CREATE TABLE followed by a ROLLBACK if the master crashes before writing the abort record? I thought that too would leave a stray file around on a standby. I've been thinking about the same thing. And AFAICS, your analysis is correct, though there may be some angle to it I'm not seeing. -- 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] BUG #5856: pg_attribute.attinhcount is not correct.
On Sun, Apr 10, 2011 at 6:36 AM, Noah Misch n...@leadboat.com wrote:
I had exactly what you just said in mind.
Patch attached, then.
Committed.
Thanks. This turns out to have caused that TOAST creation regression:
Crap. I am not going to be able to look at this today; I am getting
on a plane to Santa Clara. I will look at it while I'm there if I
can, but it's going to be a busy week for me so if anyone else can
step in...
On Fri, Apr 08, 2011 at 08:12:19PM -0400, Noah Misch wrote:
[pg_upgrade/typed table business]
I also tested a regular dump+reload of the regression database, and a
pg_upgrade
of the same. The latter failed further along, due (indirectly) to this
failure
to create a TOAST table:
create table p ();
create table ch () inherits (p);
alter table p add column a text;
select oid::regclass,reltoastrelid from pg_class where oid::regclass IN
('p','ch');
insert into ch values (repeat('x', 100));
If I drop table a_star cascade in the regression database before attempting
pg_upgrade, it completes cleanly.
Since ATExecAddColumn now handles the recursion, child table work queue
entries
no longer have AT_PASS_ADD_COL subcommands. Consequently, this heuristic in
ATRewriteCatalogs skips over them:
if (tab-relkind == RELKIND_RELATION
(tab-subcmds[AT_PASS_ADD_COL] ||
tab-subcmds[AT_PASS_ALTER_TYPE] ||
tab-subcmds[AT_PASS_COL_ATTRS]))
AlterTableCreateToastTable(tab-relid, (Datum) 0);
SET STORAGE uses AT_PASS_MISC, so this test case also omits a TOAST table:
set client_min_messages = debug1; -- display toast creation
create table t (a text); -- makes toast
alter table t alter a set storage plain;
alter table t add b int default 0; -- rewrite the table - no toast
alter table t alter a set storage extended; -- no toast added?
insert into t (a) values (repeat('x', 100)); -- fails
My first thought was to figure that the cost of needs_toast_table() is not a
concern and simply remove the pass-usage heuristic. However, we don't want
AlterTableCreateToastTable acquiring an AccessExclusiveLock for ALTER TABLE
recipes that only acquired ShareUpdateExclusiveLock. I see these options:
1. Upgrade AT_SetStorage to take AccessExclusiveLock. Add a
maybe_create_toast
field to AlteredTableInfo. Have the AT_SetStorage, AT_AlterColumnType and
AT_AddColumn implementations set it.
2. Upgrade AT_SetStorage to take AccessExclusiveLock. In ATRewriteCatalogs,
replace the pass-usage heuristic with a test for locklevel ==
AccessExclusiveLock. This smells more like a hack, but it might be less
vulnerable to omissions. On the other hand, the set of operations that could
add TOAST tables are not particularly liable to grow.
3. Make AlterTableCreateToastTable acquire only ShareUpdateExclusiveLock and
remove the pass-usage heuristic from ATRewriteCatalogs. For this to be valid,
toast_insert_or_update() must behave reasonably in the face of a relation
concurrently acquiring a TOAST table. Since it takes reltoastrelid from the
relcache, toast_insert_or_update() will not act on the change in the middle
of a
single call. Even if it did, I don't see any risks.
I'd lean toward #3 if someone else is also confident in its correctness.
Otherwise, #1 seems like the way to go. Preferences? Other ideas?
I haven't scrutinized the code but I would prefer #3 if it's viable
without too much of a code footprint.
--
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] BUG #5856: pg_attribute.attinhcount is not correct.
On Sun, Apr 10, 2011 at 07:35:53AM -0400, Robert Haas wrote:
On Sun, Apr 10, 2011 at 6:36 AM, Noah Misch n...@leadboat.com wrote:
3. Make AlterTableCreateToastTable acquire only ShareUpdateExclusiveLock and
remove the pass-usage heuristic from ATRewriteCatalogs. For this to be
valid,
toast_insert_or_update() must behave reasonably in the face of a relation
concurrently acquiring a TOAST table. Since it takes reltoastrelid from the
relcache, toast_insert_or_update() will not act on the change in the middle
of a
single call. Even if it did, I don't see any risks.
I'd lean toward #3 if someone else is also confident in its correctness.
Otherwise, #1 seems like the way to go. Preferences? Other ideas?
I haven't scrutinized the code but I would prefer #3 if it's viable
without too much of a code footprint.
It's certainly compact; patch attached.
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 5d5496d..cb0df40 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -59,11 +59,11 @@ AlterTableCreateToastTable(Oid relOid, Datum reloptions)
Relationrel;
/*
-* Grab an exclusive lock on the target table, which we will NOT release
-* until end of transaction. (This is probably redundant in all present
-* uses...)
+* Grab a DDL-exclusive lock on the target table, since we'll update the
+* pg_class tuple. This is redundant for all present users. Tuple
toasting
+* behaves safely in the face of a concurrent TOAST table add.
*/
- rel = heap_open(relOid, AccessExclusiveLock);
+ rel = heap_open(relOid, ShareUpdateExclusiveLock);
/* create_toast_table does all the work */
(void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 886b656..03d1efa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -3014,18 +3014,12 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode)
}
}
- /*
-* Check to see if a toast table must be added, if we executed any
-* subcommands that might have added a column or changed column storage.
-*/
+ /* Check to see if a toast table must be added. */
foreach(ltab, *wqueue)
{
AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
- if (tab-relkind == RELKIND_RELATION
- (tab-subcmds[AT_PASS_ADD_COL] ||
-tab-subcmds[AT_PASS_ALTER_TYPE] ||
-tab-subcmds[AT_PASS_COL_ATTRS]))
+ if (tab-relkind == RELKIND_RELATION)
AlterTableCreateToastTable(tab-relid, (Datum) 0);
}
}
diff --git a/src/test/regress/expected/alter_table.out
b/src/test/regress/expected/alter_table.out
index d7d1b64..315b915 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1522,6 +1522,19 @@ ERROR: composite type recur1 cannot be made a member of
itself
alter table recur1 add column f2 int;
alter table recur1 alter column f2 type recur2; -- fails
ERROR: composite type recur1 cannot be made a member of itself
+-- SET STORAGE may need to add a TOAST table
+create table test_storage (a text);
+alter table test_storage alter a set storage plain;
+alter table test_storage add b int default 0; -- rewrite table to remove its
TOAST table
+alter table test_storage alter a set storage extended; -- re-add TOAST table
+select reltoastrelid 0 as has_toast_table
+from pg_class
+where oid = 'test_storage'::regclass;
+ has_toast_table
+-
+ t
+(1 row)
+
--
-- lock levels
--
diff --git a/src/test/regress/input/misc.source
b/src/test/regress/input/misc.source
index 0930a6a..7cd26cb 100644
--- a/src/test/regress/input/misc.source
+++ b/src/test/regress/input/misc.source
@@ -153,6 +153,12 @@ SELECT * FROM e_star*;
ALTER TABLE a_star* ADD COLUMN a text;
+-- That ALTER TABLE should have added TOAST tables.
+SELECT relname, reltoastrelid 0 AS has_toast_table
+ FROM pg_class
+ WHERE oid::regclass IN ('a_star', 'c_star')
+ ORDER BY 1;
+
--UPDATE b_star*
-- SET a = text 'gazpacho'
-- WHERE aa 4;
diff --git a/src/test/regress/output/misc.source
b/src/test/regress/output/misc.source
index c225d0f..34bde31 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -376,6 +376,17 @@ SELECT * FROM e_star*;
ALTER TABLE a_star* ADD COLUMN a text;
NOTICE: merging definition of column a for child d_star
+-- That ALTER TABLE should have added TOAST tables.
+SELECT relname, reltoastrelid 0 AS has_toast_table
+ FROM pg_class
+ WHERE oid::regclass IN ('a_star', 'c_star')
+ ORDER BY 1;
+ relname | has_toast_table
+-+-
+ a_star | t
+ c_star |
Re: [HACKERS] k-neighbourhood search in databases
On 2011-04-10 12:18, Oleg Bartunov wrote: Wow, custom solution for 2008 still much faster Denali 2011 solution. Also, what's about not spatial data types ? In our approach, we can provide knn for any datatype, which has GiST index and distance method. Can you share some insight about how it would work if the distance method is expensive (as in 100ms)? -- Jesper -- 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] BUG #5856: pg_attribute.attinhcount is not correct.
On Sun, Apr 10, 2011 at 6:36 AM, Noah Misch n...@leadboat.com wrote:
On Sun, Apr 03, 2011 at 09:53:57PM -0400, Robert Haas wrote:
On Fri, Apr 1, 2011 at 12:56 AM, Noah Misch n...@leadboat.com wrote:
On Thu, Mar 31, 2011 at 11:11:49AM -0400, Robert Haas wrote:
On Thu, Mar 31, 2011 at 6:06 AM, Noah Misch n...@leadboat.com wrote:
The best way I can see is to make ATExecAddColumn more like
ATExecDropColumn,
ATAddCheckConstraint, and ATExecDropConstraint. ?Namely, recurse at
Exec-time
rather than Prep-time, and cease recursing when we satisfy the ADD
COLUMN with a
merge. ?Did you have something else in mind?
I had exactly what you just said in mind.
Patch attached, then.
Committed.
Thanks. This turns out to have caused that TOAST creation regression:
On Fri, Apr 08, 2011 at 08:12:19PM -0400, Noah Misch wrote:
[pg_upgrade/typed table business]
I also tested a regular dump+reload of the regression database, and a
pg_upgrade
of the same. The latter failed further along, due (indirectly) to this
failure
to create a TOAST table:
create table p ();
create table ch () inherits (p);
alter table p add column a text;
select oid::regclass,reltoastrelid from pg_class where oid::regclass IN
('p','ch');
insert into ch values (repeat('x', 100));
If I drop table a_star cascade in the regression database before attempting
pg_upgrade, it completes cleanly.
Since ATExecAddColumn now handles the recursion, child table work queue
entries
no longer have AT_PASS_ADD_COL subcommands. Consequently, this heuristic in
ATRewriteCatalogs skips over them:
if (tab-relkind == RELKIND_RELATION
(tab-subcmds[AT_PASS_ADD_COL] ||
tab-subcmds[AT_PASS_ALTER_TYPE] ||
tab-subcmds[AT_PASS_COL_ATTRS]))
AlterTableCreateToastTable(tab-relid, (Datum) 0);
SET STORAGE uses AT_PASS_MISC, so this test case also omits a TOAST table:
set client_min_messages = debug1; -- display toast creation
create table t (a text); -- makes toast
alter table t alter a set storage plain;
alter table t add b int default 0; -- rewrite the table - no toast
alter table t alter a set storage extended; -- no toast added?
insert into t (a) values (repeat('x', 100)); -- fails
Checking my understanding here, the first of these is a regression
introduced by the patch for $SUBJECT, but the second one is a
pre-existing bug. Is that right?
--
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] pgindent
Bruce Momjian wrote: Robert Haas wrote: On Fri, Apr 8, 2011 at 11:21 PM, Andrew Dunstan and...@dunslane.net wrote: We've got more work to do before that works, so I have committed what we have. Some symbols have disappeared, some because of code changes and some probably because Cygwin has changed the way it does objdump. This is probably harmless, but whoever does the pgindent run needs to look at the results carefully before committing them (as always). Well, that's normally Bruce. Bruce? I can run it tonight, in 15 hours. 27 hours later, done. I ran all the tests outlined in the pgindent README. -- 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] pgindent
On Sun, Apr 10, 2011 at 4:42 PM, Bruce Momjian br...@momjian.us wrote: 27 hours later, done. I ran all the tests outlined in the pgindent README. What's with things like: -void _PG_init(void); +void _PG_init(void); - Datum diff = DirectFunctionCall2(date_mi, + Datum diff = DirectFunctionCall2(date_mi, const TimeADT *aa = (const TimeADT *) a; const TimeADT *bb = (const TimeADT *) b; - Interval *i; + Interval *i; Note that in the last one someone carefully made the variable names line up and pgindent is changing the spacing to an arbitrary amount. -- greg -- 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] pgindent
And this doesn't seem helpful:
/* Format options */
/* oids option is not supported */
- { format, ForeignTableRelationId },
- { header, ForeignTableRelationId },
- { delimiter, ForeignTableRelationId },
- { quote, ForeignTableRelationId },
- { escape, ForeignTableRelationId },
- { null, ForeignTableRelationId },
- { encoding, ForeignTableRelationId },
+ {format, ForeignTableRelationId},
+ {header, ForeignTableRelationId},
+ {delimiter, ForeignTableRelationId},
+ {quote, ForeignTableRelationId},
+ {escape, ForeignTableRelationId},
+ {null, ForeignTableRelationId},
+ {encoding, ForeignTableRelationId},
--
greg
--
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] pgindent
On Sun, Apr 10, 2011 at 11:55 AM, Greg Stark gsst...@mit.edu wrote: On Sun, Apr 10, 2011 at 4:42 PM, Bruce Momjian br...@momjian.us wrote: 27 hours later, done. I ran all the tests outlined in the pgindent README. What's with things like: -void _PG_init(void); +void _PG_init(void); - Datum diff = DirectFunctionCall2(date_mi, + Datum diff = DirectFunctionCall2(date_mi, const TimeADT *aa = (const TimeADT *) a; const TimeADT *bb = (const TimeADT *) b; - Interval *i; + Interval *i; Note that in the last one someone carefully made the variable names line up and pgindent is changing the spacing to an arbitrary amount. Well, it's the same arbitrary amount that we use throughout our code, presumably. I am not sure whether pgident is the best tool for the job, but at least it makes the code relatively consistent throughout, which is mostly a good thing. -- 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] Feature request: pg_basebackup --force
Magnus, That could certainly be useful, yes. But I have a feeling whomever tries to get that into 9.1 will be killed - but it's certainly good to put ont he list of things for 9.2. Oh, no question. At some point in 9.2 we should also discuss how basebackup considers emtpy directories. Because the other thing I find myself constantly scripting is replacing the conf files on the replica after the base backup sync. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] pgindent
Robert Haas robertmh...@gmail.com writes: On Sun, Apr 10, 2011 at 11:55 AM, Greg Stark gsst...@mit.edu wrote: Note that in the last one someone carefully made the variable names line up and pgindent is changing the spacing to an arbitrary amount. Well, it's the same arbitrary amount that we use throughout our code, presumably. I am not sure whether pgident is the best tool for the job, but at least it makes the code relatively consistent throughout, which is mostly a good thing. Yes. pgindent has never been about preserving somebody else's idea of what's appropriate formatting. This is sometimes bad but on the whole it seems to be a win. What I was a bit surprised by is the volume of changes in wparser_def.c --- so far as I can see, that file hardly changed since 9.0, so why did pgindent suddenly whack it around so much? The other files that changed a lot are mostly new code so widespread changes are unsurprising. 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] pgindent
Robert Haas wrote: On Sun, Apr 10, 2011 at 11:55 AM, Greg Stark gsst...@mit.edu wrote: On Sun, Apr 10, 2011 at 4:42 PM, Bruce Momjian br...@momjian.us wrote: 27 hours later, done. ? I ran all the tests outlined in the pgindent README. What's with things like: -void _PG_init(void); +void ? ? ? _PG_init(void); - ? Datum diff = DirectFunctionCall2(date_mi, + ? Datum ? ? ? diff = DirectFunctionCall2(date_mi, ? ?const TimeADT *aa = (const TimeADT *) a; ? ?const TimeADT *bb = (const TimeADT *) b; - ? Interval ? ? ?*i; + ? Interval ? *i; Note that in the last one someone carefully made the variable names line up and pgindent is changing the spacing to an arbitrary amount. Well, it's the same arbitrary amount that we use throughout our code, presumably. I am not sure whether pgident is the best tool for the job, but at least it makes the code relatively consistent throughout, which is mostly a good thing. Yes, there are going to be negative aspects of pgindent --- the big question is whether it is a net gain or loss. I have always felt it is radical that we do this --- good, but radical. -- 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] pgindent
On 04/10/2011 12:11 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Sun, Apr 10, 2011 at 11:55 AM, Greg Starkgsst...@mit.edu wrote: Note that in the last one someone carefully made the variable names line up and pgindent is changing the spacing to an arbitrary amount. Well, it's the same arbitrary amount that we use throughout our code, presumably. I am not sure whether pgident is the best tool for the job, but at least it makes the code relatively consistent throughout, which is mostly a good thing. Yes. pgindent has never been about preserving somebody else's idea of what's appropriate formatting. This is sometimes bad but on the whole it seems to be a win. What I was a bit surprised by is the volume of changes in wparser_def.c --- so far as I can see, that file hardly changed since 9.0, so why did pgindent suddenly whack it around so much? The other files that changed a lot are mostly new code so widespread changes are unsurprising. I had a dim and possibly erroneous recollection that was one of the files we excluded from pgindent runs. If you look at the history it hasn't been touched by pgindent since the 8.3 run. But the changes to it all look fairly kosher at first glance. 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] Feature request: pg_basebackup --force
On Sat, Apr 9, 2011 at 2:26 PM, Joshua Berkus j...@agliodbs.com wrote: It seems a bit annoying to have to do an rm -rf * $PGDATA/ before resynching a standby using pg_basebackup. This means that I still need to wrap basebackup in a shell script, instead of having it do everything for me ... especially if I have multiple tablespaces. Couldn't we have a --force option which would clear all data and tablespace directories before resynching? What would be even more useful us some kind of support for differential copy, a la rsync. (Now I'm waiting for someone to tell me this is a pipe dream.) -- 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] pgindent
Andrew Dunstan and...@dunslane.net writes: On 04/10/2011 12:11 PM, Tom Lane wrote: What I was a bit surprised by is the volume of changes in wparser_def.c --- so far as I can see, that file hardly changed since 9.0, so why did pgindent suddenly whack it around so much? The other files that changed a lot are mostly new code so widespread changes are unsurprising. I had a dim and possibly erroneous recollection that was one of the files we excluded from pgindent runs. If you look at the history it hasn't been touched by pgindent since the 8.3 run. But the changes to it all look fairly kosher at first glance. Oh, I bet you're right -- see commit 97116ca4170b974d734cea364789c389b30e6ce1. That removed the exclusion but I don't see any evidence that Bruce actually pgindent'd it then. 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] pgindent
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 04/10/2011 12:11 PM, Tom Lane wrote: What I was a bit surprised by is the volume of changes in wparser_def.c --- so far as I can see, that file hardly changed since 9.0, so why did pgindent suddenly whack it around so much? The other files that changed a lot are mostly new code so widespread changes are unsurprising. I had a dim and possibly erroneous recollection that was one of the files we excluded from pgindent runs. If you look at the history it hasn't been touched by pgindent since the 8.3 run. But the changes to it all look fairly kosher at first glance. Oh, I bet you're right -- see commit 97116ca4170b974d734cea364789c389b30e6ce1. That removed the exclusion but I don't see any evidence that Bruce actually pgindent'd it then. Right. The fix for that file was some time after the pgindent run and I didn't want to run just that file. This is the first time I ever had no pgindent failure/warning messages. -- 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] Feature request: pg_basebackup --force
Robert Haas robertmh...@gmail.com writes: On Sat, Apr 9, 2011 at 2:26 PM, Joshua Berkus j...@agliodbs.com wrote: Couldn't we have a --force option which would clear all data and tablespace directories before resynching? What would be even more useful us some kind of support for differential copy, a la rsync. (Now I'm waiting for someone to tell me this is a pipe dream.) Not so much a pipe dream as reinventing the wheel. Why not use rsync? 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] lowering privs in SECURITY DEFINER function
On Wed, Apr 6, 2011 at 6:39 PM, Jeff Davis pg...@j-davis.com wrote: On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: (Consider, for example, that you may want to enable a user to run some operation to which he is authorized, but you want to carry out some privileged operation before/after doing so: for example, disable triggers, run an update, re-enable triggers.) I'm not sure I understand the use case. If it's within one function, why not just do it all as the privileged user in the security definer function? The only reason I can think of it if you wanted to make the unprivileged operation arbitrary SQL. But in the example you give, with triggers disabled, it's not safe to allow the user to execute arbitrary operations. In other words, if you wrap an unprivileged operation inside of privileged operations, it seems like the unprivileged operation then becomes privileged. Right? It's maybe worth noting here that what's being asked for is roughly what you get from UNIX's distinction between euid and ruid. Many programs that run setuid root perform a few operations that require root privileges up front, and then drop privs. To what degree that model applies in an SQL environment I'm not sure, but it might be worth looking at some of the parallels, as well as some of the ways that the UNIX mechanism has managed to cause all sorts of privilege escalation bugs over the years, to make sure we don't repeat those mistakes. I *think* Windows has some kind of similar privilege-dropping mechanism. -- 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] Feature request: pg_basebackup --force
On Sun, Apr 10, 2011 at 12:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Apr 9, 2011 at 2:26 PM, Joshua Berkus j...@agliodbs.com wrote: Couldn't we have a --force option which would clear all data and tablespace directories before resynching? What would be even more useful us some kind of support for differential copy, a la rsync. (Now I'm waiting for someone to tell me this is a pipe dream.) Not so much a pipe dream as reinventing the wheel. Why not use rsync? It's not integrated and I doubt it's conveniently available on Windows. One of the biggest problems with our replication functionality right now is that it's hard to set up. We've actually done a good job making the very simplest case (one slave, no archive) reasonably simple, but how many PostgreSQL users do you think can manage to set up SR + HS + archiving, with two slaves that can use the archive if they fall too far behind the master, but with the archive regularly trimmed to the farthest-back segment that is still needed? We have pg_archivecleanup, but AIUI that's only smart enough to handle the one-standby case. Admittedly, the above is a slightly different problem, but I think it all points in the direction of needing more automation and more ease of use. -- 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] Feature request: pg_basebackup --force
On Sun, Apr 10, 2011 at 12:41 PM, Robert Haas robertmh...@gmail.com wrote: It's not integrated and I doubt it's conveniently available on Windows. One of the biggest problems with our replication functionality right now is that it's hard to set up. We've actually done a good job making the very simplest case (one slave, no archive) reasonably simple, but how many PostgreSQL users do you think can manage to set up SR + HS + archiving, with two slaves that can use the archive if they fall too far behind the master, but with the archive regularly trimmed to the farthest-back segment that is still needed? We have pg_archivecleanup, but AIUI that's only smart enough to handle the one-standby case. Admittedly, the above is a slightly different problem, but I think it all points in the direction of needing more automation and more ease of use. And let me also note that the difficulty of getting this all exactly right is one of the things that causes people to come up with creative solutions like this: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02514.php That's why we need to put it in a box, tie a bow around it, and put up a big sign that says do not look into laser with remaining eye. -- 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] How would sorting work with millions of rows in a huge DB with PG?
Thanks a lot for the help. Regards, Vaibhav On Sun, Apr 10, 2011 at 11:07 AM, to...@tuxteam.de wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, Apr 10, 2011 at 05:20:02AM +0530, Vaibhav Kaushal wrote: Hello all, I was going through some papers related to sorting and since I am studying PG code side by side, I wondered how sorting would be done on a DB with millions of rows on disk with GBs of data. Since holding everything in memory would not be the possible solution, how do we actually sort the results in such conditions. Look for external sort and external merge Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFNoUIyBcgs9XrR2kYRAua/AJ4sIw54Mq6EgCsJzGqqmYELLPnSOwCeN0+S f19mq0vePoCC9rAWLtWpUUE= =8XVk -END PGP SIGNATURE-
Re: [HACKERS] Feature request: pg_basebackup --force
On 10.04.2011 20:06, Robert Haas wrote: On Sun, Apr 10, 2011 at 12:41 PM, Robert Haasrobertmh...@gmail.com wrote: Admittedly, the above is a slightly different problem, but I think it all points in the direction of needing more automation and more ease of use. And let me also note that the difficulty of getting this all exactly right is one of the things that causes people to come up with creative solutions like this: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02514.php That's why we need to put it in a box, tie a bow around it, and put up a big sign that says do not look into laser with remaining eye. That's exactly what pg_basebackup does. Once you move into more complicated scenarios with multiple standbys and WAL archiving, it's inevitably going to be more complicated to set up. That doesn't mean that we can't make it easier - we can and we should - but I don't think the common complaint that replication is hard to set up is true anymore. -- 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] k-neighbourhood search in databases
On Sun, 10 Apr 2011, Jesper Krogh wrote: On 2011-04-10 12:18, Oleg Bartunov wrote: Wow, custom solution for 2008 still much faster Denali 2011 solution. Also, what's about not spatial data types ? In our approach, we can provide knn for any datatype, which has GiST index and distance method. Can you share some insight about how it would work if the distance method is expensive (as in 100ms)? I don't understand how does your question connected with my statement :) Slow distance calculation affects gist-based ordered heap output as well as seqscan output from heap, but in the first case you need to calculate just a few distances (something like height of gist tree), while in the naive way one have to calculate n^2 distances. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Back branch update releases this week; beta postponed
In view of the recently-discovered data loss bug in pg_upgrade, it seems imperative to push out update releases fixing that as soon as possible. The core team has therefore decided to wrap back-branch update releases this Thursday for release Monday 4/18. We were previously targeting wrapping 9.1beta1 on Thursday, but it would not be a good idea to do that at the same time as the update releases. Aside from the risk of overburdening packagers, we would like to make a PR splash for the beta release, and not dilute that message with the update-releases-to-fix-data-loss issue. And with assorted major contributors off at a conference this week, it was far from clear that we'd be ready for beta by Thursday anyway. Accordingly, the beta release will have to be postponed. Since the following weekend is Easter holidays for many, it's not very practical to consider a one-week slip for beta. The tentative plan is to slip two weeks (ie, beta wrap will be April 28). We could possibly do something creative with a midweek release date, but that could be problematic from both packagers' and PR standpoints. 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] BUG #5856: pg_attribute.attinhcount is not correct.
On Sun, Apr 10, 2011 at 11:19:26AM -0400, Robert Haas wrote:
On Sun, Apr 10, 2011 at 6:36 AM, Noah Misch n...@leadboat.com wrote:
On Sun, Apr 03, 2011 at 09:53:57PM -0400, Robert Haas wrote:
On Fri, Apr 1, 2011 at 12:56 AM, Noah Misch n...@leadboat.com wrote:
On Thu, Mar 31, 2011 at 11:11:49AM -0400, Robert Haas wrote:
On Thu, Mar 31, 2011 at 6:06 AM, Noah Misch n...@leadboat.com wrote:
The best way I can see is to make ATExecAddColumn more like
ATExecDropColumn,
ATAddCheckConstraint, and ATExecDropConstraint. ?Namely, recurse at
Exec-time
rather than Prep-time, and cease recursing when we satisfy the ADD
COLUMN with a
merge. ?Did you have something else in mind?
I had exactly what you just said in mind.
Patch attached, then.
Committed.
Thanks. ?This turns out to have caused that TOAST creation regression:
On Fri, Apr 08, 2011 at 08:12:19PM -0400, Noah Misch wrote:
[pg_upgrade/typed table business]
I also tested a regular dump+reload of the regression database, and a
pg_upgrade
of the same. ?The latter failed further along, due (indirectly) to this
failure
to create a TOAST table:
? create table p ();
? create table ch () inherits (p);
? alter table p add column a text;
? select oid::regclass,reltoastrelid from pg_class where oid::regclass IN
('p','ch');
? insert into ch values (repeat('x', 100));
If I drop table a_star cascade in the regression database before
attempting
pg_upgrade, it completes cleanly.
Since ATExecAddColumn now handles the recursion, child table work queue
entries
no longer have AT_PASS_ADD_COL subcommands. ?Consequently, this heuristic in
ATRewriteCatalogs skips over them:
? ? ? ? ? ? ? ?if (tab-relkind == RELKIND_RELATION
? ? ? ? ? ? ? ? ? ? ? ?(tab-subcmds[AT_PASS_ADD_COL] ||
? ? ? ? ? ? ? ? ? ? ? ? tab-subcmds[AT_PASS_ALTER_TYPE] ||
? ? ? ? ? ? ? ? ? ? ? ? tab-subcmds[AT_PASS_COL_ATTRS]))
? ? ? ? ? ? ? ? ? ? ? ?AlterTableCreateToastTable(tab-relid, (Datum) 0);
SET STORAGE uses AT_PASS_MISC, so this test case also omits a TOAST table:
?set client_min_messages = debug1; -- display toast creation
?create table t (a text); -- makes toast
?alter table t alter a set storage plain;
?alter table t add b int default 0; -- rewrite the table - no toast
?alter table t alter a set storage extended; -- no toast added?
?insert into t (a) values (repeat('x', 100)); -- fails
Checking my understanding here, the first of these is a regression
introduced by the patch for $SUBJECT, but the second one is a
pre-existing bug. Is that right?
The patch for $SUBJECT just introduced the first regression; commit
04e17bae50a73af524731fa11210d5c3f7d8e1f9 introduced the second regression near
the beginning of the 9.1 development cycle.
--
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] Teaching regex operators about collations
I wrote: Since ILIKE now responds to collations, it would be nice if the case-insensitive regex operators did too. The hard part of that is getting the information from src/backend/utils/adt/regexp.c to src/backend/regex/regc_locale.c. In principle we could probably add a field to the data structures carried around in the regex library, but that is looking a bit invasive, and since we share that code with the Tcl project I'm loath to change it too much. So what I'm thinking about is just having a couple of static variables in regc_locale.c that we initialize before each use of the regex library. This is a bit grotty, but there's no need for the regex library to be re-entrant, so it wouldn't cause any problems until that improbable day when somebody succeeds in multi-threading the backend. In the event, it seemed least messy to store a collation Oid in struct regex_t, but not to pass it down via the regex library's struct vars private data structure. So the interface to the regex library is clean and if anyone ever wants to get rid of the static variables, it'll just be necessary to fix the innards. David E. Wheeler da...@kineticode.com writes: Sounds reasonable. Is this something that CITEXT could take advantage of somehow? Right now, its using a nasty hack to make ILIKE and friends work properly You mean the alias operators? Doesn't seem that bad, and anyway you'd still need aliases to inject a non-default collation, I think. But more to the point, we're still a long way from being able to allow a collation that has special equality semantics, so I don't foresee being able to replace citext with a case insensitive collation anytime soon. 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] k-neighbourhood search in databases
On Sunday, April 10, 2011 at 3:18 AM, Oleg Bartunov wrote: Wow, custom solution for 2008 still much faster Denali 2011 solution. Also, what's about not spatial data types ? In our approach, we can provide knn for any datatype, which has GiST index and distance method. There are a number of workarounds (custom data types, mainly) that can be done in SQL Server, but the spatial datatypes themselves are .NET datatypes that ship with SQL Server and I'm pretty sure the methods will only work with the spatial types. The other types are the usual primitives that we all know and love and won't respond to .NET method invocation in the database. -- Jeremiah Peschka Microsoft SQL Server MVP MCITP: Database Developer, DBA Oleg On Fri, 8 Apr 2011, Jeremiah Peschka wrote: On 4/8/11 5:21 AM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. SQL Server provides some knn search functionality[1] with enhancements coming this November in SQL 11[2]. [1]: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx [2]: http://www.sqlskills.com/BLOGS/BOBB/post/The-nearest-neighbor-optimization-in-SQL-Server-Denali.aspx Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
[HACKERS] Re: [COMMITTERS] pgsql: Don't make replication magical as a user name, only as a datab
On Mon, Apr 11, 2011 at 3:53 AM, Andrew Dunstan and...@dunslane.net wrote: Don't make replication magical as a user name, only as a database name, in pg_hba.conf. Is it worth backporting this change to 9.0? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Don't make replication magical as a user name, only as a datab
On 04/10/2011 09:47 PM, Fujii Masao wrote: On Mon, Apr 11, 2011 at 3:53 AM, Andrew Dunstanand...@dunslane.net wrote: Don't make replication magical as a user name, only as a database name, in pg_hba.conf. Is it worth backporting this change to 9.0? I didn't because it's a behaviour change, but arguably it's just us being ever so slightly more permissive, and nothing that now works would change in any way, so we possibly could. 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] sync rep and smart shutdown
On Sat, Apr 9, 2011 at 3:53 AM, Robert Haas robertmh...@gmail.com wrote: There are a couple of plausible ways to proceed here: 1. Do nothing. 2. When a smart shutdown is initiated, shut off synchronous replication. 3. Accept new replication connections even when the system is undergoing a smart shutdown. I agree that #3 is impractical and #2 is a bad idea, which seems to leave us with #1 (unless anyone has a #4)? This is probably just something we should figure is going to be one of the rough edges in the first release of sync rep. That's kind of where my mind was headed too, although I was (probably vainly) hoping for a better option. Though I proposed #3, I can live with #1 for now. Even if smart shutdown gets stuck, we can resolve that by requesting fast shutdown or emptying synchronous_standby_names. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
