Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
On 7 April 2011 16:56, Tom Lane t...@sss.pgh.pa.us wrote: Brendan Jurd dire...@gmail.com writes: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) [ scratches head ... ] That implies that heap_fill_tuple came to a different conclusion about a tuple's data size than the immediately preceding heap_compute_data_size. Which I would sure want to believe is impossible. Have you checked for flaky memory on this machine? Memtest didn't report any errors. I intend to try swapping out the RAM tomorrow, but in the meantime we got a *different* assertion failure today. The fact that we are tripping over various different assertions seems to lend further weight to the flaky hardware hypothesis. TRAP: FailedAssertion(!(((lpp)-lp_flags == 1)), File: heapam.c, Line: 727) #0 0x7f2773f23a75 in *__GI_raise (sig=value optimised out) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64 #1 0x7f2773f275c0 in *__GI_abort () at abort.c:92 #2 0x006f9eed in ExceptionalCondition (conditionName=value optimised out, errorType=value optimised out, fileName=value optimised out, lineNumber=value optimised out) at assert.c:57 #3 0x00473641 in heapgettup_pagemode (scan=0x2366da8, dir=value optimised out, nkeys=value optimised out, key=value optimised out) at heapam.c:727 #4 0x00474b16 in heap_getnext (scan=0x2366da8, direction=1495) at heapam.c:1322 #5 0x00590fcb in SeqNext (node=value optimised out) at nodeSeqscan.c:66 #6 0x005808ff in ExecScanFetch (node=0x22d5ff8, accessMtd=value optimised out, recheckMtd=value optimised out) at execScan.c:82 #7 ExecScan (node=0x22d5ff8, accessMtd=value optimised out, recheckMtd=value optimised out) at execScan.c:164 #8 0x00578d58 in ExecProcNode (node=0x22d5ff8) at execProcnode.c:378 #9 0x0058abf7 in ExecHashJoinOuterGetTuple (node=0x22d4a60) at nodeHashjoin.c:562 #10 ExecHashJoin (node=0x22d4a60) at nodeHashjoin.c:187 #11 0x00578ca8 in ExecProcNode (node=0x22d4a60) at execProcnode.c:427 #12 0x0058abf7 in ExecHashJoinOuterGetTuple (node=0x22d3430) at nodeHashjoin.c:562 #13 ExecHashJoin (node=0x22d3430) at nodeHashjoin.c:187 #14 0x00578ca8 in ExecProcNode (node=0x22d3430) at execProcnode.c:427 #15 0x00590021 in ExecNestLoop (node=0x22d26d8) at nodeNestloop.c:120 #16 0x00578cc8 in ExecProcNode (node=0x22d26d8) at execProcnode.c:419 #17 0x00590021 in ExecNestLoop (node=0x22c0c88) at nodeNestloop.c:120 #18 0x00578cc8 in ExecProcNode (node=0x22c0c88) at execProcnode.c:419 #19 0x00591bf9 in ExecSort (node=0x22c0a50) at nodeSort.c:102 #20 0x00578c88 in ExecProcNode (node=0x22c0a50) at execProcnode.c:438 #21 0x0057795e in ExecutePlan (queryDesc=0x23151f0, direction=1495, count=0) at execMain.c:1187 #22 standard_ExecutorRun (queryDesc=0x23151f0, direction=1495, count=0) at execMain.c:280 #23 0x00643d67 in PortalRunSelect (portal=0x229bf78, forward=value optimised out, count=0, dest=0x218a120) at pquery.c:952 #24 0x00645210 in PortalRun (portal=value optimised out, count=value optimised out, isTopLevel=value optimised out, dest=value optimised out, altdest=value optimised out, completionTag=value optimised out) at pquery.c:796 #25 0x006428dc in exec_execute_message (argc=value optimised out, argv=value optimised out, username=value optimised out) at postgres.c:2003 #26 PostgresMain (argc=value optimised out, argv=value optimised out, username=value optimised out) at postgres.c:3988 #27 0x00607351 in BackendRun () at postmaster.c:3555 #28 BackendStartup () at postmaster.c:3242 #29 ServerLoop () at postmaster.c:1431 #30 0x00609c6d in PostmasterMain (argc=35406528, argv=0x2185160) at postmaster.c:1092 #31 0x005a99a0 in main (argc=5, argv=0x2185140) at main.c:188 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] switch UNLOGGED to LOGGED
Hi, I read the discussion at http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php From what I can understand, going from/to unlogged to/from logged in the wal_level == minimal case is not too complicated. Suppose I try to write a patch that allows ALTER TABLE tablename SET LOGGED (or UNLOGGED) (proper sql wording to be discussed...) only in the wal_level == minimal case: would it be accepted as a first step? Or rejected because it doesn't allow it in the other cases? From what I got in the discussion, handling the other wal_level cases can be very complicated (example: the issues in case we *crash* without writing an abort record). Leonardo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET vacuum_freeze_table_age = 0; VACUUM toasttbl. Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more work than strictly necessary. We don't actually need earlier-than-usual freezing. We need only ensure that the relfrozenxid will guide future VACUUMs to do that freezing early enough. However, I'm not sure how to do that without directly updating relfrozenxid, so it's probably just as well to cause some extra work and stick to the standard interface. 2) The true relfrozenxid is no longer covered by available clog files. The fix for case 1 will get file foo doesn't exist, reading as zeroes log messages, and we will treat all transactions as uncommitted. Uh, are you sure? I think it would return an error message about a missing clog file for the query; here is a report of a case not related to pg_upgrade: http://archives.postgresql.org/pgsql-admin/2010-09/msg00109.php My statement was indeed incorrect. (Was looking at the reading as zeroes message in slru.c, but it only applies during recovery.) Not generally fixable after that has happened. We could probably provide a recipe for checking whether it could have happened given access to a backup from just before the upgrade. The IRC folks pulled the clog files off of backups. Since we do get the error after all, that should always be enough. One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. Yes. On a similar note, would it be worth having your prototype fixup script sort the VACUUM FREEZE calls in descending relfrozenxid order? 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] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 09:32:08PM -0400, Noah Misch wrote:
... ALTER TYPE mistakenly
only touches the first table-of-type:
create type t as (x int, y int);
create table is_a of t;
create table is_a2 of t;
alter type t drop attribute y cascade, add attribute z int cascade;
\d is_a
Table public.is_a
Column | Type | Modifiers
+-+---
x | integer |
z | integer |
Typed table of type: t
\d is_a2
Table public.is_a2
Column | Type | Modifiers
+-+---
x | integer |
y | integer |
Typed table of type: t
Might be a simple fix; looks like find_typed_table_dependencies() only grabs
the
first match.
This is a fairly independent one-liner, so here's that patch. I didn't
incorporate the test case, because it seems distinctly unlikely to recur.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4a97819..bd18db3 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***
*** 4014,4020 find_typed_table_dependencies(Oid typeOid, const char
*typeName, DropBehavior be
scan = heap_beginscan(classRel, SnapshotNow, 1, key);
! if (HeapTupleIsValid(tuple = heap_getnext(scan, ForwardScanDirection)))
{
if (behavior == DROP_RESTRICT)
ereport(ERROR,
--- 4014,4020
scan = heap_beginscan(classRel, SnapshotNow, 1, key);
! while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
if (behavior == DROP_RESTRICT)
ereport(ERROR,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] k-neighbourhood search in databases
Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. 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] pg_upgrade bug found!
Noah Misch wrote: On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET vacuum_freeze_table_age = 0; VACUUM toasttbl. Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more work than strictly necessary. We don't actually need earlier-than-usual freezing. We need only ensure that the relfrozenxid will guide future VACUUMs to do that freezing early enough. However, I'm not sure how to do that without directly updating relfrozenxid, so it's probably just as well to cause some extra work and stick to the standard interface. Looking at the code, it seems VACUUM FREEZE will freeze any row it can, and because we restarted the cluster after the upgrade, all the rows we care about are visible or dead to all transactions. pg_upgrade certainly relies on that fact already. 2) The true relfrozenxid is no longer covered by available clog files. The fix for case 1 will get file foo doesn't exist, reading as zeroes log messages, and we will treat all transactions as uncommitted. Uh, are you sure? I think it would return an error message about a missing clog file for the query; here is a report of a case not related to pg_upgrade: http://archives.postgresql.org/pgsql-admin/2010-09/msg00109.php My statement was indeed incorrect. (Was looking at the reading as zeroes message in slru.c, but it only applies during recovery.) No problem. Thanks for the review. Not generally fixable after that has happened. We could probably provide a recipe for checking whether it could have happened given access to a backup from just before the upgrade. The IRC folks pulled the clog files off of backups. Since we do get the error after all, that should always be enough. That was my thought too. One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. Yes. On a similar note, would it be worth having your prototype fixup script sort the VACUUM FREEZE calls in descending relfrozenxid order? Good question. I don't think the relfrozenxid ordering would make sense because I think it is unlikely problems will happen while they are running the script. The other problem is that because of wraparound it is unclear which xid is earliest. What I did add was to order by the oid, so at least the toast numbers are in order and people can more easily track its progress. New version; I made some other small adjustments: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql -- 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] switch UNLOGGED to LOGGED
On Fri, Apr 8, 2011 at 6:01 AM, Leonardo Francalanci m_li...@yahoo.it wrote: I read the discussion at http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php From what I can understand, going from/to unlogged to/from logged in the wal_level == minimal case is not too complicated. Suppose I try to write a patch that allows ALTER TABLE tablename SET LOGGED (or UNLOGGED) (proper sql wording to be discussed...) only in the wal_level == minimal case: would it be accepted as a first step? Or rejected because it doesn't allow it in the other cases? 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. -- 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] k-neighbourhood search in databases
hello ... i have put some research into that some time ago and as far as i have seen there is a 99% chance that no other database can do it the way we do it. it seems nobody comes even close to it (especially not in the flexibility-arena). oracle: disgusting workaround ... http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/11/03/0083.htm db2: disgusting workaround (no server side code it seems) sybase: disgusting workaround (no serverside code it seems) microsoft: there seems to be something coming out (or just out) but i have not seen anything working yet. regards, hans On Apr 8, 2011, at 2:21 PM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. 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 -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] workaround for expensive KNN?
hello all ...
given oleg's posting before i also wanted to fire up some KNN related question.
let us consider a simple example. i got some million lines and i want all rows
matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'iphone') ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual time=36391.717..45542.590
rows=10 loops=1)
Buffers: shared hit=9 read=5004
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..13251.91 rows=3224 width=16) (actual time=
36391.715..45542.573 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''iphon'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=9 read=5004
Total runtime: 45542.676 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'handy') ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
-
Limit (cost=0.00..41.03 rows=10 width=16) (actual time=7243.526..10935.227
rows=10 loops=1)
Buffers: shared hit=3 read=2316
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..29762.61 rows=7255 width=16) (actual time=
7243.524..10935.217 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''handy'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=3 read=2316
Total runtime: 10935.265 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'handy') ORDER BY int_price - 0 LIMIT 1;
QUERY
PLAN
-
---
Limit (cost=0.00..4.10 rows=1 width=16) (actual time=28.527..28.528 rows=1
loops=1)
Buffers: shared hit=1 read=1577
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..29762.61 rows=7255 width=16) (actual time=
28.525..28.525 rows=1 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''handy'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=1 read=1577
Total runtime: 28.558 ms
(7 rows)
under any circumstances - there is no way to reduce the number of buffers
needed for a query like that.
if everything is cached this is still ok but as soon as you have to take a
single block from disk you will die a painful random I/O death.
is there any alternative which does not simply die when i try to achieve what i
want?
the use case is quite simple: all products with a certain word (10 cheapest or
so).
is there any alternative approach to this?
i was putting some hope into KNN but it seems it needs too much random I/O :(.
many thanks,
hans
--
Cybertec Schönig Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
--
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] workaround for expensive KNN?
Probably, you miss two-columnt index. From my early post:
http://www.sai.msu.su/~megera/wiki/knngist
=# CREATE INDEX spots_idx ON spots USING knngist (coordinates,
to_tsvector('french',address));
=# SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point) AS dist
FROM spots WHERE coordinates '(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
id| address | dist
-+-+-
366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France |
2.32488941293945e-05
4356328 | r Champ de Mars 75007 PARIS |
0.00421854756964406
5200167 | Champ De Mars 75007 Paris |
0.00453564562587288
9301676 | Champ de Mars, 75007 Paris, |
0.00453564562587288
2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |
0.00624152097590896
1923818 | Champ de Mars Paris, France |
0.00838214733539654
5165953 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
7395870 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
4358671 | 32 Rue Champ De Mars Paris, France |
0.00876089659276339
1923742 | 12 rue du Champ de Mars Paris, France |
0.00876764731845995
(10 rows)
Time: 7.859 ms
=# EXPLAIN (COSTS OFF)
SELECT id, address FROM spots WHERE coordinates '(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
QUERY PLAN
--
Limit
- Index Scan using spots_idx on spots
Index Cond: ((coordinates
'(2.29470491409302,48.858263472125)'::point) AND (to_tsvector('french'::regconfig,
address) @@ '''mar'''::tsquery))
(3 rows)
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello all ...
given oleg's posting before i also wanted to fire up some KNN related question.
let us consider a simple example. i got some million lines and i want all rows
matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'iphone') ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual time=36391.717..45542.590
rows=10 loops=1)
Buffers: shared hit=9 read=5004
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..13251.91 rows=3224 width=16) (actual time=
36391.715..45542.573 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''iphon'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=9 read=5004
Total runtime: 45542.676 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german', 'handy')
ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
-
Limit (cost=0.00..41.03 rows=10 width=16) (actual time=7243.526..10935.227
rows=10 loops=1)
Buffers: shared hit=3 read=2316
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..29762.61 rows=7255 width=16) (actual time=
7243.524..10935.217 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''handy'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=3 read=2316
Total runtime: 10935.265 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german', 'handy')
ORDER BY int_price - 0 LIMIT 1;
QUERY
PLAN
-
---
Limit (cost=0.00..4.10 rows=1 width=16) (actual time=28.527..28.528 rows=1
loops=1)
Buffers: shared hit=1 read=1577
- Index Scan using idx_product_t_product_titleprice on t_product
Re: [HACKERS] k-neighbourhood search in databases
Hans, thanks a lot. I've heard about Oracle Spatial, but I don't know if it's knn is just syntactic sugar for workarounds. Oleg On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote: hello ... i have put some research into that some time ago and as far as i have seen there is a 99% chance that no other database can do it the way we do it. it seems nobody comes even close to it (especially not in the flexibility-arena). oracle: disgusting workaround ... http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/11/03/0083.htm db2: disgusting workaround (no server side code it seems) sybase: disgusting workaround (no serverside code it seems) microsoft: there seems to be something coming out (or just out) but i have not seen anything working yet. regards, hans On Apr 8, 2011, at 2:21 PM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. 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 -- Cybertec Sch?nig Sch?nig GmbH Gr?hrm?hlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de 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] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
Excerpts from Brendan Jurd's message of vie abr 08 06:00:22 -0300 2011: Memtest didn't report any errors. I intend to try swapping out the RAM tomorrow, but in the meantime we got a *different* assertion failure today. The fact that we are tripping over various different assertions seems to lend further weight to the flaky hardware hypothesis. TRAP: FailedAssertion(!(((lpp)-lp_flags == 1)), File: heapam.c, Line: 727) Yep. -- Á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: Allow SQL-language functions to reference parameters by parameter name
On 04/07/2011 09:58 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstanand...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... I think the discussion went off into the weeds somewhat, and I'm guilty of responding to suggestions that don't refer to the original subject. For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. 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: Allow SQL-language functions to reference parameters by parameter name
Hello Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... I think the discussion went off into the weeds somewhat, and I'm guilty of responding to suggestions that don't refer to the original subject. For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. same mechanism works well in plpgsql and nobody requested a some special shortcut. Regards Pavel 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] SSI bug?
hi, YAMAMOTO Takashi y...@mwd.biglobe.ne.jp wrote: LOG: could not truncate directory pg_serial: apparent wraparound Did you get a warning with this text?: memory for serializable conflict tracking is nearly exhausted there is not such a warning near the above aparent wraparound record. not sure if it was far before the record as i've lost the older log files. YAMAMOTO Takashi If not, there's some sort of cleanup bug to fix in the predicate locking's use of SLRU. It may be benign, but we won't really know until we find it. I'm investigating. -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] WIP: Allow SQL-language functions to reference parameters by parameter name
On 04/08/2011 10:53 AM, Pavel Stehule wrote: For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. same mechanism works well in plpgsql and nobody requested a some special shortcut. I get annoyed by it there too, that's why I mentioned it :-) 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: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule pavel.steh...@gmail.com wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. -- 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: Allow SQL-language functions to reference parameters by parameter name
On Friday, April 08, 2011 04:53:27 PM Pavel Stehule wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. Well, for one it sucks there as well. For another it has been introduced for quite some time and most people have introduced naming like p_param or v_param for parameternames. That has not been the case for sql functions. So I find it way much more painfull there... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] workaround for expensive KNN?
Hans,
what if you create index (price,title) ?
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello ...
i got that one ...
idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig,
title), int_price)
so, i have a combined index on text + number.
to me the plan seems fine ... it looks like a prober KNN traversal.
the difference between my plan and your plan seems to be the fact that i have, say, 1 mio
rows which have handy or so in it (1 mio out of 11 mio or so). you are moving
out from one specific place.
my maths is like that:
11 mio in total
1 mio matching iphone
cheapest / most expensive 10 out of this mio needed.
operator classes are all nice and in place:
SELECT 10 - 4 as distance;
distance
--
6
(1 row)
what does buffers true in your case say?
many thanks,
hans
On Apr 8, 2011, at 3:22 PM, Oleg Bartunov wrote:
Probably, you miss two-columnt index. From my early post:
http://www.sai.msu.su/~megera/wiki/knngist
=# CREATE INDEX spots_idx ON spots USING knngist (coordinates,
to_tsvector('french',address));
=# SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point)
AS dist FROM spots WHERE coordinates '(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
id| address |
dist
-+-+-
366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France |
2.32488941293945e-05
4356328 | r Champ de Mars 75007 PARIS |
0.00421854756964406
5200167 | Champ De Mars 75007 Paris |
0.00453564562587288
9301676 | Champ de Mars, 75007 Paris, |
0.00453564562587288
2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |
0.00624152097590896
1923818 | Champ de Mars Paris, France |
0.00838214733539654
5165953 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
7395870 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
4358671 | 32 Rue Champ De Mars Paris, France |
0.00876089659276339
1923742 | 12 rue du Champ de Mars Paris, France |
0.00876764731845995
(10 rows)
Time: 7.859 ms
=# EXPLAIN (COSTS OFF) SELECT id, address FROM spots WHERE coordinates
'(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
QUERY PLAN
--
Limit
- Index Scan using spots_idx on spots
Index Cond: ((coordinates
'(2.29470491409302,48.858263472125)'::point) AND (to_tsvector('french'::regconfig,
address) @@ '''mar'''::tsquery))
(3 rows)
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello all ...
given oleg's posting before i also wanted to fire up some KNN related question.
let us consider a simple example. i got some million lines and i want all rows
matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'iphone') ORDER BY int_price - 0 LIMIT 10;
QUERY
PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual time=36391.717..45542.590
rows=10 loops=1)
Buffers: shared hit=9 read=5004
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..13251.91 rows=3224 width=16) (actual time=
36391.715..45542.573 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''iphon'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=9 read=5004
Total runtime: 45542.676 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german', 'handy')
ORDER BY int_price - 0 LIMIT 10;
QUERY
PLAN
-
-
Limit (cost=0.00..41.03 rows=10 width=16) (actual time=7243.526..10935.227
rows=10 loops=1)
Buffers: shared hit=3 read=2316
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..29762.61 rows=7255
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. I think I've worked around that in PL/pgSQL using ALIAS… 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] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler da...@kineticode.com wrote: On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. I think I've worked around that in PL/pgSQL using ALIAS… I've worked around it, too, using various techniques. That doesn't mean it doesn't suck. -- 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] workaround for expensive KNN?
Oleg Bartunov o...@sai.msu.su writes: what if you create index (price,title) ? I think that SELECT ... WHERE ... ORDER BY ... LIMIT is basically an intractable problem. We've recognized the difficulty in connection with btree indexes for a long time, and there is no reason at all to think that KNNGist will somehow magically dodge it. You can either visit *all* of the rows satisfying WHERE (and then sort them), or you can visit the rows in ORDER BY order and hope that you find enough of them satisfying the WHERE in a reasonable amount of time. Either of these strategies loses badly in many real-world cases. Maybe with some sort of fuzzy notion of ordering it'd be possible to go faster, but as long as you insist on an exact ORDER BY result, I don't see any way out of it. One way to be fuzzy is to introduce a maximum search distance: SELECT ... WHERE x limit AND other-conditions ORDER BY x LIMIT n which essentially works by limiting the damage in the visit-all-the-rows approach. Hans didn't do that in his example, but I wonder how much it'd help (and whether the existing GIST support is adequate for it). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... +1, as long as you are 100.0% sure this is not going to break any existing code. For example, what happens if the argument is named the same as a table? merlin -- 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: Allow SQL-language functions to reference parameters by parameter name
Merlin Moncure mmonc...@gmail.com writes: On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... +1, as long as you are 100.0% sure this is not going to break any existing code. For example, what happens if the argument is named the same as a table? I was a bit sloppy in my statement above --- what the code is actually doing (or should be doing) is matching to parameters only after the core parser fails to find any match. So unqualified reference to whole-row would take precedence too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] getting carriage return character in vacuumo
Hi, While using the vacuumlo utility I encountered a redundant carriage return(\r') character in the output. It is required in any scenario? If not, please find attached a tiny patch which will get rid of that extra '\r' character. Regards, Usama carriage_return_in_vacuumlo.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] Headcount for PL Summit, Saturday May 21, 2011 at PgCon
Hello again! On Thu, Apr 7, 2011 at 10:22 AM, Selena Deckelmann sel...@chesnok.com wrote: We need to get a headcount for the PL Summit at PgCon on Saturday, May 21, 2011. Please sign up using this form: http://chesnok.com/u/1r A wiki page has been started here: http://wiki.postgresql.org/wiki/PgCon_2011_PL_Summit It was brought to my attention that no one was listed other than me for the meeting. :) I've updated the wiki with the names I have so far - really, people will be there! Thanks, -selena -- http://chesnok.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] pg_upgrade bug found!
On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can you elaborate? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] workaround for expensive KNN?
hello ...
i got that one ...
idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig,
title), int_price)
so, i have a combined index on text + number.
to me the plan seems fine ... it looks like a prober KNN traversal.
the difference between my plan and your plan seems to be the fact that i have,
say, 1 mio rows which have handy or so in it (1 mio out of 11 mio or so). you
are moving out from one specific place.
my maths is like that:
11 mio in total
1 mio matching iphone
cheapest / most expensive 10 out of this mio needed.
operator classes are all nice and in place:
SELECT 10 - 4 as distance;
distance
--
6
(1 row)
what does buffers true in your case say?
many thanks,
hans
On Apr 8, 2011, at 3:22 PM, Oleg Bartunov wrote:
Probably, you miss two-columnt index. From my early post:
http://www.sai.msu.su/~megera/wiki/knngist
=# CREATE INDEX spots_idx ON spots USING knngist (coordinates,
to_tsvector('french',address));
=# SELECT id, address, (coordinates -
'(2.29470491409302,48.858263472125)'::point) AS dist FROM spots WHERE
coordinates '(2.29470491409302,48.858263472125)'::point AND
to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
id| address |
dist
-+-+-
366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France |
2.32488941293945e-05
4356328 | r Champ de Mars 75007 PARIS |
0.00421854756964406
5200167 | Champ De Mars 75007 Paris |
0.00453564562587288
9301676 | Champ de Mars, 75007 Paris, |
0.00453564562587288
2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |
0.00624152097590896
1923818 | Champ de Mars Paris, France |
0.00838214733539654
5165953 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
7395870 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
4358671 | 32 Rue Champ De Mars Paris, France |
0.00876089659276339
1923742 | 12 rue du Champ de Mars Paris, France |
0.00876764731845995
(10 rows)
Time: 7.859 ms
=# EXPLAIN (COSTS OFF) SELECT id, address FROM spots WHERE coordinates
'(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
QUERY PLAN
--
Limit
- Index Scan using spots_idx on spots
Index Cond: ((coordinates
'(2.29470491409302,48.858263472125)'::point) AND
(to_tsvector('french'::regconfig, address) @@ '''mar'''::tsquery))
(3 rows)
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello all ...
given oleg's posting before i also wanted to fire up some KNN related
question.
let us consider a simple example. i got some million lines and i want all
rows matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'iphone') ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual time=36391.717..45542.590
rows=10 loops=1)
Buffers: shared hit=9 read=5004
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..13251.91 rows=3224 width=16) (actual time=
36391.715..45542.573 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''iphon'''::tsquery)
Order By: (int_price - 0::bigint)
Buffers: shared hit=9 read=5004
Total runtime: 45542.676 ms
(7 rows)
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'handy') ORDER BY int_price - 0 LIMIT 10;
QUERY PLAN
-
-
Limit (cost=0.00..41.03 rows=10 width=16) (actual time=7243.526..10935.227
rows=10 loops=1)
Buffers: shared hit=3 read=2316
- Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..29762.61 rows=7255 width=16) (actual
Re: [HACKERS] workaround for expensive KNN?
Oops, my previous example was fromm early prototype :) I just
recreated test environment for 9.1:
knn=# select count(*) from spots;
count
908846
(1 row)
knn=# explain (analyze true, buffers true) SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point) AS dist FROM spots
WHERE to_tsvector('french',address) @@ to_tsquery('french','mars')
ORDER BY coordinates - '(2.29470491409302,48.858263472125)'::point
LIMIT 10;
QUERY PLAN
Limit (cost=0.00..33.63 rows=10 width=58) (actual time=1.541..1.875 rows=10
loops=1)
Buffers: shared hit=251
- Index Scan using spots_idx on spots (cost=0.00..15279.12 rows=4544
width=58) (actual time=1.540..1.874 rows=10 loops=1)
Index Cond: (to_tsvector('french'::regconfig, address) @@
'''mar'''::tsquery)
Order By: (coordinates - '(2.29470491409302,48.858263472125)'::point)
Buffers: shared hit=251
Total runtime: 1.905 ms
(7 rows)
Time: 2.372 ms
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello ...
i got that one ...
idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig,
title), int_price)
so, i have a combined index on text + number.
to me the plan seems fine ... it looks like a prober KNN traversal.
the difference between my plan and your plan seems to be the fact that i have, say, 1 mio
rows which have handy or so in it (1 mio out of 11 mio or so). you are moving
out from one specific place.
my maths is like that:
11 mio in total
1 mio matching iphone
cheapest / most expensive 10 out of this mio needed.
operator classes are all nice and in place:
SELECT 10 - 4 as distance;
distance
--
6
(1 row)
what does buffers true in your case say?
many thanks,
hans
On Apr 8, 2011, at 3:22 PM, Oleg Bartunov wrote:
Probably, you miss two-columnt index. From my early post:
http://www.sai.msu.su/~megera/wiki/knngist
=# CREATE INDEX spots_idx ON spots USING knngist (coordinates,
to_tsvector('french',address));
=# SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point)
AS dist FROM spots WHERE coordinates '(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
id| address |
dist
-+-+-
366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France |
2.32488941293945e-05
4356328 | r Champ de Mars 75007 PARIS |
0.00421854756964406
5200167 | Champ De Mars 75007 Paris |
0.00453564562587288
9301676 | Champ de Mars, 75007 Paris, |
0.00453564562587288
2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |
0.00624152097590896
1923818 | Champ de Mars Paris, France |
0.00838214733539654
5165953 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
7395870 | 39 Rue Champ De Mars Paris, France |
0.00874410234569529
4358671 | 32 Rue Champ De Mars Paris, France |
0.00876089659276339
1923742 | 12 rue du Champ de Mars Paris, France |
0.00876764731845995
(10 rows)
Time: 7.859 ms
=# EXPLAIN (COSTS OFF) SELECT id, address FROM spots WHERE coordinates
'(2.29470491409302,48.858263472125)'::point
AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
QUERY PLAN
--
Limit
- Index Scan using spots_idx on spots
Index Cond: ((coordinates
'(2.29470491409302,48.858263472125)'::point) AND (to_tsvector('french'::regconfig,
address) @@ '''mar'''::tsquery))
(3 rows)
On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
hello all ...
given oleg's posting before i also wanted to fire up some KNN related question.
let us consider a simple example. i got some million lines and i want all rows
matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@ to_tsquery('german',
'iphone') ORDER BY int_price - 0 LIMIT 10;
QUERY
PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual
Re: [HACKERS] pg_upgrade bug found!
On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more work than strictly necessary. We don't actually need earlier-than-usual freezing. We need only ensure that the relfrozenxid will guide future VACUUMs to do that freezing early enough. However, I'm not sure how to do that without directly updating relfrozenxid, so it's probably just as well to cause some extra work and stick to the standard interface. If there are tuples in a toast table containing xids that are older than the toast table's relfrozenxid, then there are only two options: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to find the oldest xid in a table with a normal snapshot. 2. Get rid of those xids older than relfrozenxid (i.e. VACUUM FREEZE). I don't know what you mean about VACUUM FREEZE doing extra work. I suppose you could set the vacuum_freeze_min_age to be exactly the right value such that it freezes everything before the existing (and wrong) relfrozenxid, but in practice I think it would be the same amount of work. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. I am not sure how to interpret the lack of replies to this email. Either it is confidence, shock, or we told you so. ;-) Anyway, the attached patch fixes the problem. The fix is for pg_dump's binary upgrade mode. This would need to be backpatched back to 8.4 because pg_migrator needs this too. OK, I have applied the attached three patches to 8.4, 9.0, and 9.1. They are all slightly different because of code drift, and I created a unified diff which I find is clearer for single-line changes. I was very careful about the patching of queries because many of these queries are only activated when dumping an older database, and are therefore hard to test for SQL query errors. I included all the version patches in case someone sees something I missed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3842895..c5057f7 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3185,6 +3185,8 @@ getTables(int *numTables) int i_relhasrules; int i_relhasoids; int i_relfrozenxid; + int i_toastoid; + int i_toastfrozenxid; int i_owning_tab; int i_owning_col; int i_reltablespace; @@ -3226,7 +3228,8 @@ getTables(int *numTables) (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, - c.relfrozenxid, + c.relfrozenxid, tc.oid AS toid, + tc.relfrozenxid AS tfrozenxid, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, @@ -3259,6 +3262,8 @@ getTables(int *numTables) relchecks, (reltriggers 0) AS relhastriggers, relhasindex, relhasrules, relhasoids, relfrozenxid, + 0 AS toid, + 0 AS tfrozenxid, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, @@ -3290,6 +3295,8 @@ getTables(int *numTables) relchecks, (reltriggers 0) AS relhastriggers, relhasindex, relhasrules, relhasoids, 0 AS relfrozenxid, + 0 AS toid, + 0 AS tfrozenxid, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, @@ -3321,6 +3328,8 @@ getTables(int *numTables) relchecks, (reltriggers 0) AS relhastriggers, relhasindex, relhasrules, relhasoids, 0 AS relfrozenxid, + 0 AS toid, + 0 AS tfrozenxid, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, NULL AS reltablespace, @@ -3348,6 +3357,8 @@ getTables(int *numTables) relchecks, (reltriggers 0) AS relhastriggers, relhasindex, relhasrules, relhasoids, 0 AS relfrozenxid, + 0 AS toid, + 0 AS tfrozenxid, NULL::oid AS owning_tab, NULL::int4 AS owning_col, NULL AS reltablespace, @@ -3370,6 +3381,8 @@ getTables(int *numTables) relhasindex, relhasrules, 't'::bool AS relhasoids, 0 AS relfrozenxid, + 0 AS toid, + 0 AS tfrozenxid, NULL::oid AS owning_tab, NULL::int4 AS owning_col, NULL AS reltablespace, @@ -3446,6 +3459,8 @@ getTables(int *numTables) i_relhasrules = PQfnumber(res, relhasrules); i_relhasoids = PQfnumber(res, relhasoids); i_relfrozenxid = PQfnumber(res, relfrozenxid); + i_toastoid = PQfnumber(res, toid); + i_toastfrozenxid = PQfnumber(res, tfrozenxid); i_owning_tab = PQfnumber(res, owning_tab); i_owning_col = PQfnumber(res, owning_col); i_reltablespace = PQfnumber(res, reltablespace); @@ -3484,6 +3499,8
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can you elaborate? Well, when you initially run pg_upgrade, your heap relfrozenxid is preserved, and we only remove clog files when _all_ relations in all database do not need them, so for a time the heap tables will keep the clogs around. Over time, the heap files will be vacuum frozen, and their relfrozenxid advanced. Once that happens to all heaps, the system thinks it can remove clog files, and doesn't realize the toast tables also need vacuuming. This is the it might become more of a problem in the future concern I have. The script I posted does fix this, and the code changes prevent it from happening completely. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more work than strictly necessary. We don't actually need earlier-than-usual freezing. We need only ensure that the relfrozenxid will guide future VACUUMs to do that freezing early enough. However, I'm not sure how to do that without directly updating relfrozenxid, so it's probably just as well to cause some extra work and stick to the standard interface. If there are tuples in a toast table containing xids that are older than the toast table's relfrozenxid, then there are only two options: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to find the oldest xid in a table with a normal snapshot. Right, this is all to complicated. 2. Get rid of those xids older than relfrozenxid (i.e. VACUUM FREEZE). I don't know what you mean about VACUUM FREEZE doing extra work. I suppose you could set the vacuum_freeze_min_age to be exactly the right value such that it freezes everything before the existing (and wrong) relfrozenxid, but in practice I think it would be the same amount of work. We don't know how far back to go with freezing, so we just have to freeze it all. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 08, 2011 at 10:05:01AM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more work than strictly necessary. We don't actually need earlier-than-usual freezing. We need only ensure that the relfrozenxid will guide future VACUUMs to do that freezing early enough. However, I'm not sure how to do that without directly updating relfrozenxid, so it's probably just as well to cause some extra work and stick to the standard interface. If there are tuples in a toast table containing xids that are older than the toast table's relfrozenxid, then there are only two options: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to find the oldest xid in a table with a normal snapshot. Couldn't you set relfrozenxid and datfrozenxid to txid_current() - 11 (the highest possible vacuum_freeze_min_age, plus some slop), then run SET vacuum_freeze_table_age = 0; VACUUM tbl on all tables for which you did this? There's no need to set relfrozenxid back to a particular right value. Not suggesting we recommend this, but I can't think offhand why it wouldn't suffice. 2. Get rid of those xids older than relfrozenxid (i.e. VACUUM FREEZE). I don't know what you mean about VACUUM FREEZE doing extra work. I suppose you could set the vacuum_freeze_min_age to be exactly the right value such that it freezes everything before the existing (and wrong) relfrozenxid, but in practice I think it would be the same amount of work. Suppose that your next xid at pg_upgrade time was 500M, and it's now 505M. If you're using the default vacuum_freeze_min_age = 50M, SET vacuum_freeze_table_age = 0; VACUUM tbl will only freeze tuples covering 5M transaction ids. VACUUM FREEZE tbl (a.k.a SET vacuum_freeze_table_age = 0; SET vacuum_freeze_min_age = 0; VACUUM tbl) will freeze tuples covering 55M transaction ids. 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] pg_upgrade bug found!
Bruce Momjian wrote: New version; I made some other small adjustments: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql OK, now that I have committed the fixes to git, I think it is time to consider how we are going to handle this fix for people who have already used pg_upgrade, or are using it in currently released versions. I am thinking an announce list email with this query would be enough, and state that we are planning a minor release with this fix in the next few weeks. I can provide details on the cause and behavior of the bug. -- 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] Open issues for collations
Robert Haas robertmh...@gmail.com writes: Reading through this thread... On Sat, Mar 26, 2011 at 12:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: ** Selecting a field from a record-returning function's output. Currently, we'll use the field's declared collation; except that if the field has default collation, we'll replace that with the common collation of the function's inputs, if any. Is either part of that sane? Do we need to make this work for functions invoked with other syntax than a plain function call, eg operator or cast syntax? There were a couple of different ideas about which way we ought to go with this, but I'm happy to defer to what Tom and Martijn hashed out: MO That seems all a bit weird. I spent some time reading through the SQL MO spec to see if I could came up with a few ideas about what they thought MO relevent. I think the gist of it is that I think the result row should MO have for each column its declared collation in all cases. TL That interpretation would be fine with me. It would let us get rid of TL the special-case code at lines 307-324 of parse_collate.c, which I put TL in only because there are cases in the collate.linux.utf8.sql regression TL test that fail without it. But I'm perfectly happy to conclude that TL those test cases are mistaken. I'm not sure whether that's been done, though, or whether we're even going to do it. I looked a bit more closely at this, and I think I finally get the point of what those regression test cases involving the dup() function are about. Consider a trivial polymorphic function such as create function dummy(anyelement) returns anyelement as 'select $1' language sql; When applied to a textual argument, this is a function taking and returning string, and so collation does (and should, I think) propagate through it. Thus in select dummy(x) from tbl order by 1; you will get ordering by the declared collation of tbl.x, whatever that is. But now consider create function dup(in anyelement, a out anyelement, b out anyelement) as 'select $1, $2' language sql; select dup(x).a from tbl order by 1; It's not unreasonable to think that this should also order by tbl.x's collation --- if collation propagates through dummy(), why not through dup()? And in fact those regression test cases are expecting that it does propagate in such a case. Now the discussion that we had earlier in this thread was implicitly assuming that we were talking about FieldSelect from a known composite type. If dup() were declared to return a named composite type, then using the collation that is declared for that type's a column seems reasonable. But when you're dealing with an anonymous record type, which is what dup() actually returns here, there is no such declaration; and what's more, the fact that there's a record type at all is just an implementation detail to most users. If we take out the kluge in parse_collate.c's handling of FieldSelects, then what we will get in this example is ordering by the database default collation. We can justify that on a narrow language-lawyering basis by saying dup() returns a composite type, which has no collation, therefore collation does not propagate through from its arguments to any column you might select from its result. But it's going to feel a bit surprising to anyone who thinks of this in terms of OUT arguments rather than an anonymous composite type. I'm inclined to think that we should take out the kluge and rest on the language-lawyering viewpoint, because otherwise there are going to be umpteen other corner cases where people are going to expect collation to propagate and it's not going to work without very major kluging. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sync rep and smart shutdown
There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php The issue is not straightforward, however, so I want to get some broader input before proceeding. In short, the problem is that if synchronous replication is in use, no standbys are connected, and a smart shutdown is requested, any future commits will wait for a wake-up that will never come, because by that point postmaster is no longer accepting connections - thus no standby can reconnect to release waiters. Or, if there is a standby connected when the smart shutdown is requested, but it subsequently gets disconnected, it won't be able to reconnect, and again all waiters will get stuck. There are a couple of plausible ways to proceed here: 1. Do nothing. If this happens to you, you will need to request fast or immediate shutdown to get the system unstuck. Since it's pretty easy for this to happen already anyway (all you need is one connection to sit open doing nothing), most people probably already have provision for this and likely wouldn't be terribly inconvenienced by one more corner case. On the flip side, I would rather that we were moving in the direction of making it more likely for smart shutdown to actually shut down the system, rather than less likely. 2. When a smart shutdown is initiated, shut off synchronous replication. This definitely makes sure you won't get stuck waiting for sync rep, but on the other hand you probably configured sync rep because you wanted, uh, sync rep. Or alternatively, continue to allow sync rep for as long as there is a sync standby connected, but if the last sync standby drops off then shut it off. 3. Accept new replication connections even when the system is undergoing a smart shutdown. This is the approach that the above-linked patch tries to take, and it seems superficially sensible, but it doesn't really work. Currently, once a shutdown has been initiated and any on-line backup has been stopped, we stop creating regular backends; we instead only create dead-end backends that just return an error message and exit. Once no regular backends remain, we then stop accepting connections AT ALL and wait for the dead end backends to drain out. What this patch proposes to do (though it isn't real clear from the way it's written) is continue creating regular backends but boot out all but superuser and replication connections as soon as possible. However, that misses the reason why the current code works the way that it does: to make sure that even in the face of a continuing stream of connection requests, we actually eventually manage to stop talking and shut down. Basically, this patch would fix the smart-shutdown-sync-rep interaction at the expense of making smart shutdown considerably more fragile in other cases, which does not seem like a good trade-off. AFAICT, this whole approach is doomed to failure. Anyone else have an idea or opinion? -- 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] getting carriage return character in vacuumo
Muhammad Usama m.us...@gmail.com writes: While using the vacuumlo utility I encountered a redundant carriage return(\r') character in the output. It is required in any scenario? If not, please find attached a tiny patch which will get rid of that extra '\r' character. I think the idea there is to overwrite successive progress messages on the same line. It's maybe not going to work in all environments, though, so perhaps we should reconsider that bit of cuteness. 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] Open issues for collations
On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem scenario? Or should we just wait around and see what actual problems, if any, will be reported? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: OK, now that I have committed the fixes to git, I think it is time to consider how we are going to handle this fix for people who have already used pg_upgrade, or are using it in currently released versions. I am thinking an announce list email with this query would be enough, and state that we are planning a minor release with this fix in the next few weeks. I can provide details on the cause and behavior of the bug. OK, here is a draft email announcement: --- A bug has been discovered in all released Postgres versions that performed major version upgrades using pg_upgrade and (formerly) pg_migrator. The bug can cause queries to return the following error: ERROR: could not access status of transaction ## DETAIL: could not open file pg_clog/: No such file or directory This error prevents access to very wide values stored in the database. To prevent such failures, users are encourage to run the following psql script in all upgraded databases as soon as possible; a fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql -- 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] Open issues for collations
Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem scenario? Or should we just wait around and see what actual problems, if any, will be reported? Well, my opinion is that normal users never see the output of initdb at all, so I don't think there's that much value in complaining there. But I don't have a serious objection to complaining if we couldn't find any usable locales at all, 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] sync rep and smart shutdown
Robert Haas robertmh...@gmail.com writes: There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php 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. A #4 idea did just come to mind: once we realize that there are no working replication connections, automatically do a fast shutdown instead, ie, forcibly roll back those transactions that are never gonna complete. Or at least have the postmaster bleat about it. But I'm not sure what it'd take to code that, and am also unsure that it's something to undertake at this stage of the cycle. 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] pg_upgrade bug found!
Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): --- A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator should take the following corrective actions as soon as possible. This bug can cause queries to return the following error: ERROR: could not access status of transaction ## DETAIL: could not open file pg_clog/: No such file or directory This error prevents access to very wide values stored in the database. To prevent such failures users need to run the following psql script, as the superuser, in all upgraded databases as soon as possible: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. The fixed version of pg_uprade will remove the need for the above script by correctly updating the TOAST tables in the migrated database. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] sync rep and smart shutdown
On Fri, Apr 8, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php 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. A #4 idea did just come to mind: once we realize that there are no working replication connections, automatically do a fast shutdown instead, ie, forcibly roll back those transactions that are never gonna complete. Or at least have the postmaster bleat about it. But I'm not sure what it'd take to code that, and am also unsure that it's something to undertake at this stage of the cycle. Well, you certainly can't do that. By the time a transaction is waiting for sync rep, it's too late to roll back; the commit record is already, and necessarily, on disk. But in theory we could notice that all of the remaining backends are waiting for sync rep, and switch to a fast shutdown. Several people have suggested refinements for smart shutdown in general, such as switching to fast shutdown after a certain number of seconds, or having backends exit at the end of the current transaction (or immediately if idle). Such things would both make this problem less irksome and increase the overall utility of smart shutdown tremendously. So maybe it's not worth expending too much effort on it right now. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Stephen Frost wrote: -- Start of PGP signed section. Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in pg_dump, and hence a new pg_upgrade binary will not work; you need a new install. --- A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator should take the following corrective actions as soon as possible. This bug can cause queries to return the following error: ERROR: could not access status of transaction ## DETAIL: could not open file pg_clog/: No such file or directory=20 This error prevents access to very wide values stored in the database. To prevent such failures users need to run the following psql script, as the superuser, in all upgraded databases as soon as possible: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n=20 WHERE c.relnamespace =3D n.oid AND=20 n.nspname =3D 'pg_toast' AND c.relkind =3D 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
* Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in pg_dump, and hence a new pg_upgrade binary will not work; you need a new install. Err, right, good point. You might even want to call that out specifically, so no one is confused. Also this: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. 'that was' should be 'that were'. A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. My suggestion: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will include an updated pg_dump which will remove the need for the above script by correctly dumping all TOAST tables in the migrated databases. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Open issues for collations
Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem scenario? Or should we just wait around and see what actual problems, if any, will be reported? Well, my opinion is that normal users never see the output of initdb at all, so I don't think there's that much value in complaining there. Those users are not going to have those problems anyway. The problematic users are going to be those running on unusual platforms. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to find the oldest xid in a table with a normal snapshot. Couldn't you set relfrozenxid and datfrozenxid to txid_current() - 11 (the highest possible vacuum_freeze_min_age, plus some slop), then run SET vacuum_freeze_table_age = 0; VACUUM tbl on all tables for which you did this? There's no need to set relfrozenxid back to a particular right value. That's a good point that we don't need relfrozenxid to really be the right value; we just need it to be less than or equal to the right value. I don't think you need to mess around with vacuum_freeze_table_age though -- that looks like it's taken care of in the logic for deciding when to do a full table vacuum. This has the additional merit that transaction IDs are not needlessly removed; therefore leaving some forensic information if there are further problems. Suppose that your next xid at pg_upgrade time was 500M, and it's now 505M. If you're using the default vacuum_freeze_min_age = 50M, SET vacuum_freeze_table_age = 0; VACUUM tbl will only freeze tuples covering 5M transaction ids. If the pg_upgrade time was at txid 500M, then the relfrozenxid of the toast table will be about 500M. That means you need to get rid of all xids less than about 500M (unless you already fixed relfrozenxid, perhaps using the process you mention above). So if you only freeze tuples less than about 455M (505M - 50M), then that is wrong. The only difference really is that you don't really need to freeze those last 5M transactions since the upgrade happened. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch n...@leadboat.com wrote: Incidentally, this led me to notice that you can hang a typed table off a table row type. ALTER TABLE never propagates to such typed tables, allowing them to get out of sync: create table t (x int, y int); create table is_a of t; create table is_a2 of t; alter table t drop y, add z int; \d is_a Table public.is_a Column | Type | Modifiers +-+--- x | integer | y | integer | Typed table of type: t Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? Yes, I think we need to do that. It looks like Noah Misch might have found another problem in this area. We'll have to investigate that. Your bits in dumpCompositeType() are most of what's needed to fix that, I think. Most? -- 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] psql \dt and table size
On Thu, Apr 7, 2011 at 3:03 PM, Bernd Helmle maili...@oopsware.de wrote: --On 28. März 2011 13:38:23 +0100 Bernd Helmle maili...@oopsware.de wrote: But I think we can just call pg_table_size() regardless in 9.0+; I believe it'll return the same results as pg_relation_size() on non-tables. Anyone see a problem with that? Hmm yeah, seems i was thinking too complicated...here is a cleaned up version of this idea. Do we consider this for 9.1 or should I add this to the CF-Next for 9.2? Since there were quite a few votes for doing this in 9.1, no dissenting votes, and it's a very small change, I went ahead and committed it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
But breaking people's code is not a better answer. We still have people on 8.2 because the pain of upgrading to 8.3 is more than they can bear, and how many releases have we spent trying to get standard_conforming_strings worked out? I admit this probably wouldn't be as bad, but we've managed to put out several releases in a row now that are relatively painless to upgrade between, and I think that's a trend we should try to keep going. I guess I'm not understanding the backwards compatibility problem. I've looked up the thread, and I still don't see a real-world issue. If we (by default) throw an error on ambiguity, and have GUC to turn that off (in which case, it resolves column-first), I really don't see what problem anyone could have upgrading. Can you explain it to me? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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 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. Nobody I've talked to, and I asked both Couch and Oracle devs. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] pg_upgrade bug found!
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in pg_dump, and hence a new pg_upgrade binary will not work; you need a new install. Err, right, good point. You might even want to call that out specifically, so no one is confused. Also this: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. 'that was' should be 'that were'. A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. My suggestion: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will include an updated pg_dump which will remove the need for the above script by correctly dumping all TOAST tables in the migrated databases. I am worried if I mention pg_dump that people will think pg_dump is broken, when in fact it is only the --binary-upgrade mode of pg_dump that is broken. I adjusted the wording of the last paragraph slighly to be clearer, but hopefully not confuse. We don't actually check the pg_dump version and I am hesistant to add such a check. I was thinking of sending this out on Monday, but now think people might like to have the weekend to fix this so I am thinking of sending it to announce tonight, in 8 hours. OK? --- A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator should take the following corrective actions as soon as possible. This bug can cause queries to return the following error: ERROR: could not access status of transaction ## DETAIL: could not open file pg_clog/: No such file or directory=20 This error prevents access to very wide values stored in the database. To prevent such failures users need to run the following psql script, as the superuser, in all upgraded databases as soon as possible: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n=20 WHERE c.relnamespace =3D n.oid AND=20 n.nspname =3D 'pg_toast' AND c.relkind =3D 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly dumping all TOAST tables in the migrated databases. -- 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] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 12:50 PM, Peter Eisentraut pete...@gmx.net wrote:
On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote:
ERROR: cannot drop column from typed table
which probably is because test_type2 has a dropped column.
It should call
ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE;
instead. That will propagate to the table.
Here is a patch that addresses this problem.
It looks like Noah Misch might have found another problem in this area.
We'll have to investigate that.
There's something wrong with this patch - it never arranges to
actually drop the phony column. Consider:
create type foo as (a int, b int);
alter table foo drop attribute b;
create table x (a int, b int);
alter table x drop column b;
Then pg_dump --binary-upgrade emits, in relevant part, the following for x:
CREATE TABLE x (
a integer,
pg.dropped.2 INTEGER /* dummy */
);
-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 4, attalign = 'i', attbyval = false
WHERE attname = 'pg.dropped.2'
AND attrelid IN ('x'::pg_catalog.regclass);
ALTER TABLE ONLY x DROP COLUMN pg.dropped.2;
But for t we get only:
CREATE TYPE foo AS (
a integer,
pg.dropped.2 INTEGER /* dummy */
);
...which is no good.
--
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: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus j...@agliodbs.com wrote:
But breaking people's code is not a better answer. We still
have people on 8.2 because the pain of upgrading to 8.3 is more than
they can bear, and how many releases have we spent trying to get
standard_conforming_strings worked out? I admit this probably
wouldn't be as bad, but we've managed to put out several releases in a
row now that are relatively painless to upgrade between, and I think
that's a trend we should try to keep going.
I guess I'm not understanding the backwards compatibility problem. I've
looked up the thread, and I still don't see a real-world issue. If we
(by default) throw an error on ambiguity, and have GUC to turn that off
(in which case, it resolves column-first), I really don't see what
problem anyone could have upgrading.
Can you explain it to me?
Consider:
rhaas=# CREATE TABLE developer (id serial primary key, name text not null);
NOTICE: CREATE TABLE will create implicit sequence developer_id_seq
for serial column developer.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
developer_pkey for table developer
CREATE TABLE
rhaas=# insert into developer (name) values ('Tom'), ('Bruce');
INSERT 0 2
rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
sql STABLE;
CREATE FUNCTION
rhaas=# SELECT developer_lookup(1);
developer_lookup
--
Tom
(1 row)
Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks id is ambiguous, it
doesn't work.
--
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] pg_upgrade bug found!
Bruce Momjian wrote: I am worried if I mention pg_dump that people will think pg_dump is broken, when in fact it is only the --binary-upgrade mode of pg_dump that is broken. I adjusted the wording of the last paragraph slighly to be clearer, but hopefully not confuse. We don't actually check the pg_dump version and I am hesistant to add such a check. I was thinking of sending this out on Monday, but now think people might like to have the weekend to fix this so I am thinking of sending it to announce tonight, in 8 hours. OK? Updated version with IRC user suggestions: --- Critical Fix for pg_upgrade/pg_migrator Users - A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator should take the following corrective actions as soon as possible. This bug can cause queries to return the following error: ERROR: could not access status of transaction ## DETAIL: could not open file pg_clog/: No such file or directory=20 This error prevents access to very wide values stored in the database. To prevent such failures users need to run the following psql script, as the superuser, in all upgraded databases as soon as possible: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that were upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except 'template0', e.g.: -- psql -U postgres -a -f pg_upgrade_fix.sql dbname -- This must be run from a writable directory. -- It will not lock any tables but will generate I/O. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly restoring all TOAST tables in the migrated databases. 2011-04-08 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 08, 2011 at 12:16:50PM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to find the oldest xid in a table with a normal snapshot. Couldn't you set relfrozenxid and datfrozenxid to txid_current() - 11 (the highest possible vacuum_freeze_min_age, plus some slop), then run SET vacuum_freeze_table_age = 0; VACUUM tbl on all tables for which you did this? There's no need to set relfrozenxid back to a particular right value. That's a good point that we don't need relfrozenxid to really be the right value; we just need it to be less than or equal to the right value. I don't think you need to mess around with vacuum_freeze_table_age though -- that looks like it's taken care of in the logic for deciding when to do a full table vacuum. Actually, I think the only reason to VACUUM at all after hacking relfrozenxid is to visit every tuple and see whether you need to restore any clog segments from backup. Suppose your postgresql.conf overrides vacuum_freeze_table_age to the maximum of 2B. If you hacked relfrozenxid and just VACUUMed without modifying vacuum_freeze_table_age, you wouldn't get a full table scan. In another ~1B transactions, you'll get that full-table VACUUM, and it might then discover missing clog segments. Though you wouldn't risk any new clog loss in the mean time, by doing the VACUUM with vacuum_freeze_table_age=0 on each affected table, you can go away confident that any clog restoration is behind you. This has the additional merit that transaction IDs are not needlessly removed; therefore leaving some forensic information if there are further problems. Suppose that your next xid at pg_upgrade time was 500M, and it's now 505M. If you're using the default vacuum_freeze_min_age = 50M, SET vacuum_freeze_table_age = 0; VACUUM tbl will only freeze tuples covering 5M transaction ids. If the pg_upgrade time was at txid 500M, then the relfrozenxid of the toast table will be about 500M. That means you need to get rid of all xids less than about 500M (unless you already fixed relfrozenxid, perhaps using the process you mention above). So if you only freeze tuples less than about 455M (505M - 50M), then that is wrong. Agreed. If you don't fix relfrozenxid, you can't win much in that example. The only difference really is that you don't really need to freeze those last 5M transactions since the upgrade happened. But change the numbers somewhat. Say you ran pg_upgrade at xid 110M. Your TOAST table had relfrozenxid = 100M before pg_upgrade and 110M+epsilon after. The next xid now sits at 170M. Without any manual relfrozenxid changes, any full-table VACUUM will bump the relfrozenxid to 120M. A VACUUM FREEZE would freeze tuples covering 70M transactions, while a VACUUM with vacuum_freeze_table_age = 0 would freeze tuples across only 20M transactions. An unadorned VACUUM wouldn't even perform a full-table scan. All that being said, recommending VACUUM FREEZE seems sensibly conservative. 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] pg_upgrade bug found!
-- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load. -- CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't' ORDER by c.oid; \copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql'; \i pg_upgrade_tmp.sql A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly restoring all TOAST tables in the migrated databases. add: However, users of databases which have been already migrated still need to run the script, even if they upgrade to 9.0.4. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Typed-tables patch broke pg_upgrade
On Fri, Apr 08, 2011 at 03:43:39PM -0400, Robert Haas wrote: On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch n...@leadboat.com wrote: Incidentally, this led me to notice that you can hang a typed table off a table row type. ?ALTER TABLE never propagates to such typed tables, allowing them to get out of sync: create table t (x int, y int); create table is_a of t; create table is_a2 of t; alter table t drop y, add z int; \d is_a ? ? Table public.is_a ?Column | ?Type ? | Modifiers +-+--- ?x ? ? ?| integer | ?y ? ? ?| integer | Typed table of type: t Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? Yes, I think we need to do that. Having thought about it some more, that would be unfortunate. We rarely distinguish between table row types and CREATE TYPE AS types. Actually, I'm not aware of any place we distinguish other than in ALTER TABLE/ALTER TYPE, to instruct you to use the other. But depending on how hard it is to fix, that might be a good stopgap. It looks like Noah Misch might have found another problem in this area. We'll have to investigate that. Your bits in dumpCompositeType() are most of what's needed to fix that, I think. Most? I think it will just fall out of the completed fix for the original reported problem. Will keep you posted. 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] WIP: Allow SQL-language functions to reference parameters by parameter name
Now, when this person attempts to recreate this function on a hypothetical version of PostgreSQL that thinks id is ambiguous, it doesn't work. Hence the GUC. Where's the issue? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Now, when this person attempts to recreate this function on a hypothetical version of PostgreSQL that thinks id is ambiguous, it doesn't work. Hence the GUC. Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems. If you need one GUC setting for your application to work, and the extension you have installed needs the other setting, you're screwed. In the worst case, if a security-definer function is involved, you can create a security hole, for example by convincing the system that id = $1 is intended to mean $1 = $1, or some such. You can of course attach the GUC settings to each individual function, but that doesn't really work either unless you do it for every function in the system. The fundamental problem here is that GUCs are dynamically scoped, while this problem is lexically scoped. -- 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] gincostestimate
Dear Hackers,
A gin index created on an initially empty table will never get used
until the table is vacuumed, which for a table with no update or
delete activity could be forever unless someone manually intervenes.
The problem is that numEntries in src/backend/utils/adt/selfuncs.c is
zero and eventually causes a division by zero and a cost estimate of
nan.
The code below does not save the day, because nTotalPages and
nEntryPages are 2 and 1 respectively when an index is created on an
empty table, or when an indexed table is truncated.
if (ginStats.nTotalPages == 0 || ginStats.nEntryPages == 0)
{
numEntryPages = numPages;
numDataPages = 0;
numEntries = numTuples; /* bogus, but no other info available */
}
I don't know what the solution is. Simply setting numEntries to 1 if
ginStats.nEntries zero solves this particular problem, but I don't
know what other consequences it might have.
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] pg_upgrade bug found!
Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load. It might be worth suggesting that people can adjust their vacuum delay parameters to control the I/O load. 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] getting to beta
On Wed, Apr 6, 2011 at 12:16 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 6, 2011 at 12:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 06.04.2011 18:02, Tom Lane wrote: I agree. But again, that's not really what I'm focusing on - the collations stuff, the typed tables patch, and SSI all need serious looking at, and I'm not sure who is going to pick all that up. Well, I'll take responsibility for collations. If I get done with that before the 14th, I can see what's up with typed tables. I'm not willing to do anything with SSI at this stage. I can look at the SSI patches, but not until next week, I'm afraid. Robert, would you like to pick that up before then? Kevin Dan have done all the heavy lifting, but it's nevertheless pretty complicated code to review. I'll try, and see how far I get with it. If you can pick up whatever I don't get to by early next week, that would be a big help. I am going to be in Santa Clara next week for the MySQL conference (don't worry, I'll be talking about PostgreSQL!) and that's going to cut into my time quite a bit. I think I've cleared out most of the small stuff. The two SSI related issues still on the open items list are: * SSI: failure to clean up some SLRU-summarized locks * SSI: three different HTABs contend for shared memory in a free-for-all If you can pick those two up, that would be very helpful; I suspect you can work your way through them faster and with fewer mistakes than I would be able to manage. The other two items are: * Typed-tables patch broke pg_upgrade * assorted collation issues Tom said he'd take care of the collation issues. Peter Eisentraut, Noah Misch, and I have been exchanging emails on the typed tables problems, of which there appear to be several, but it's not real clear to me that we're converging on a comprehensive solution. -- 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] getting to beta
Robert Haas robertmh...@gmail.com wrote: I think I've cleared out most of the small stuff. Thanks! The two SSI related issues still on the open items list are: * SSI: failure to clean up some SLRU-summarized locks This one is very important. Not only could it lead to unnecessary false positive serialization failures, but (more importantly) it leaks shared memory by not clearing some locks, leading to potential out of shared memory errors. While this isn't as small as most of the SSI patches, I'm going to point out (to reassure those who haven't been reading the patches) that this one modifies two lines, adds six Assert statements which Dan found useful in debugging the issue, and adds (if you ignore white space and braces) four lines of code. Big is a relative term here. The problem is that the code in which these tweaks fall is hard to get one's head around. * SSI: three different HTABs contend for shared memory in a free-for-all I think we're pretty much agreed that something should be done about this, but the main issue here is that if either heavyweight locks or SIRead predicate locks exhaust memory, the other might be unlucky enough to get the error, making it harder to identify the cause. Without the above bug or an unusual workload, it would tend not to make a difference. If things come down to the wire and this is the only thing holding up the beta release, I'd suggest going ahead and cutting the beta. -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] pg_upgrade bug found!
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load. It might be worth suggesting that people can adjust their vacuum delay parameters to control the I/O load. I talked to Tom about this and I am worried people will adjust it so it takes days to complete. Is that a valid concern? Does anyone have a suggested value? -- 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
[HACKERS] pgindent
So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that 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] pg_upgrade bug found!
Bruce Momjian wrote: Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load. It might be worth suggesting that people can adjust their vacuum delay parameters to control the I/O load. I talked to Tom about this and I am worried people will adjust it so it takes days to complete. Is that a valid concern? Does anyone have a suggested value? Josh Berkus helped me get lots more details on a wiki page for this: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix I will reference the wiki in the email announcement. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. You might want to clarify that the fix may be required if you ever used pg_upgrade before. Using the new version of pg_upgrade/dump when you still have a bad relfrozenxid doesn't help. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lowering privs in SECURITY DEFINER function
Excerpts from Jeff Davis's message of mié abr 06 19:39:27 -0300 2011: 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. The point is precisely to let the caller to execute whatever operation he is already authorized to execute, given his regular permissions. (The actual request from the customer says with londiste triggers removed, in case it makes any difference. I am not familiar with Londiste.) So there's a desire to check for permissions to execute the arbitrary SQL call; the security-definer wrapper is really only needed to remove the londiste triggers, not the operation in the middle. One idea we floated around was to make the before and after operations be part of security-definer function, and the user function would call those. But the problem with this is that the user is then able to call (say) only the before function and forget to call the after function to cleanup, which would be a disaster. Note that another possible option to go about this would be to have some sort of commit trigger; the before function would set a flag stating that the transaction is in unclean mode, and this commit trigger would raise an error if the after function was not called to cleanup properly. The same customer has asked for commit triggers in the past, so perhaps we should explore that option instead. Thoughts? In other words, if you wrap an unprivileged operation inside of privileged operations, it seems like the unprivileged operation then becomes privileged. Right? Well, it's in the hands of the creator of the overall wrapper function to ensure that the before/after functions are safe in that sense. -- Á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] lowering privs in SECURITY DEFINER function
Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer role is not the super user? As I said to Jeff, it's up to the creator of the wrapper function to ensure that things are safe. Perhaps this new operation should only be superuser-callable, for example. How would you prevent general SQL attacks when manually popping the authentication stack is allowed? The popping and pushing operations would be restricted. You can only pop a single frame, and pushing it back before returning is mandatory. -- Á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: Allow SQL-language functions to reference parameters by parameter name
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Hence the GUC. Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems. If you need one GUC setting for your application to work, and the extension you have installed needs the other setting, you're screwed. In the worst case, if a security-definer function is involved, you can create a security hole, for example by convincing the system that id = $1 is intended to mean $1 = $1, or some such. You can of course attach the GUC settings to each individual function, but that doesn't really work either unless you do it for every function in the system. The fundamental problem here is that GUCs are dynamically scoped, while this problem is lexically scoped. Yeah. In the plpgsql case, we did make provisions to control the behavior per-function. In principle we could do the same for SQL functions, but it'd be rather a PITA I think. (In particular, the easy way out of attaching SET clauses to the functions would be a bad idea because it would defeat inlining.) 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] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 4:00 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. You might want to clarify that the fix may be required if you ever used pg_upgrade before. Using the new version of pg_upgrade/dump when you still have a bad relfrozenxid doesn't help. Regards, Jeff Davis I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is in? This is our production db with two hot standby's running off it. grep -i 'could not access status of transaction' postgresql-2011-04*.log postgresql-2011-04-06.log:2011-04-06 07:28:27 PDT [15882]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 postgresql-2011-04-07.log:2011-04-07 07:27:14 PDT [29790]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 postgresql-2011-04-08.log:2011-04-08 07:26:35 PDT [2402]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 4:51 PM, bricklen brick...@gmail.com wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is in? This is our production db with two hot standby's running off it. grep -i 'could not access status of transaction' postgresql-2011-04*.log postgresql-2011-04-06.log:2011-04-06 07:28:27 PDT [15882]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 postgresql-2011-04-07.log:2011-04-07 07:27:14 PDT [29790]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 postgresql-2011-04-08.log:2011-04-08 07:26:35 PDT [2402]: [1-1] (user=postgres) (rhost=[local]) ERROR: could not access status of transaction 1273385235 version 9.03, if that helps -- 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/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that out? Yeah. There are three animals reporting (running Linux, FreeBSD and MinGW builds). My Cygwin animal should report within the hour, and I'm working on getting an MSVC build into the mix for the first time ever. That should be done within the next 24 hours. As soon as it is I'll commit the consolidated list. 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] pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE
On Wed, Mar 30, 2011 at 09:37:56PM -0400, Robert Haas wrote:
On Wed, Mar 30, 2011 at 9:30 PM, Noah Misch n...@leadboat.com wrote:
Perhaps it would be reasonable to extend ALTER TABLE .. [NO]
INHERIT to accept a type name as the final argument. ?If used in this
way, it converts a typed table into a regular table or visca versa.
Why extend ALTER TABLE ... INHERIT? ?I would have guessed independent
syntax.
I just didn't feel the need to invent something new, but we could if
someone would rather.
We could also do it with a direct catalog change, but there are some
dependencies that would need to be frobbed, which makes me a bit
reluctant to go that way.
Agreed; it's also an independently-useful capability to have.
Yep.
Implemented as attached. The first patch just adds the ALTER TABLE subcommands
to attach and detach a table from a composite type. A few open questions
concerning typed tables will probably yield minor changes to these subcommands.
I implemented them to be agnostic toward the outcome of those decisions.
The second patch updates pg_dump to use those new subcommands. It's based
significantly on Peter's recent patch. The new bits follow pg_dump's design for
table inheritance.
I tested pg_upgrade of these previously-mentioned test cases:
create type t as (x int, y int);
create table has_a (tcol t);
insert into has_a values ('(1,2)');
table has_a; -- (1,2)
alter type t drop attribute y cascade, add attribute z int cascade;
table has_a; -- (1,)
table has_a; -- after pg_upgrade: (1,2)
create type t as (x int, y int);
create table is_a of t;
alter type t drop attribute y cascade;
create table is_a2 of t;
select * from pg_attribute where attrelid = 'is_a'::regclass;
select * from pg_attribute where attrelid = 'is_a2'::regclass;
create type unused as (x int);
alter type unused drop attribute x;
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.
nm
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index c194862..4e02438 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***
*** 63,68 ALTER TABLE replaceable class=PARAMETERname/replaceable
--- 63,70
RESET ( replaceable class=PARAMETERstorage_parameter/replaceable [,
... ] )
INHERIT replaceable class=PARAMETERparent_table/replaceable
NO INHERIT replaceable class=PARAMETERparent_table/replaceable
+ OF replaceable class=PARAMETERtype_name/replaceable
+ NOT OF
OWNER TO replaceable class=PARAMETERnew_owner/replaceable
SET TABLESPACE replaceable class=PARAMETERnew_tablespace/replaceable
***
*** 491,496 ALTER TABLE replaceable class=PARAMETERname/replaceable
--- 493,522
/varlistentry
varlistentry
+ termliteralOF replaceable
class=PARAMETERtype_name/replaceable/literal/term
+ listitem
+ para
+ This form links the table to a composite type as though commandCREATE
+ TABLE OF/ had formed it. The table's list of column names and types
+ must precisely match that of the composite type; the presence of
+ an literaloid/ system column is permitted to differ. The table must
+ not inherit from any other table. These restrictions ensure
+ that commandCREATE TABLE OF/ would permit an equivalent table
+ definition.
+ /para
+ /listitem
+/varlistentry
+
+varlistentry
+ termliteralNOT OF/literal/term
+ listitem
+ para
+ This form dissociates a typed table from its type.
+ /para
+ /listitem
+/varlistentry
+
+varlistentry
termliteralOWNER/literal/term
listitem
para
diff --git a/src/backend/commands/tablecindex bd18db3..0d657a3 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***
*** 81,86
--- 81,87
#include utils/snapmgr.h
#include utils/syscache.h
#include utils/tqual.h
+ #include utils/typcache.h
/*
***
*** 357,362 static void ATExecEnableDisableRule(Relation rel, char
*rulename,
--- 358,366
static void ATPrepAddInherit(Relation child_rel);
static void ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE
lockmode);
static void ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode);
+ static void drop_parent_dependency(Oid relid, Oid refclassid, Oid refobjid);
+ static void ATExecAddOf(Relation rel, const TypeName
[HACKERS] \dO versus collations for other encodings
I've noticed that psql's \dO command for showing collations is a bit schizophrenic about whether it shows entries for collations that are irrelevant in the current database (because they use a different encoding). For example: regression=# \dOS aa* List of collations Schema | Name | Collate | Ctype +--+--+-- pg_catalog | aa_DJ| aa_DJ.utf8 | aa_DJ.utf8 pg_catalog | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8 pg_catalog | aa_ER| aa_ER| aa_ER pg_catalog | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8 pg_catalog | aa_ER.utf8@saaho | aa_ER.utf8@saaho | aa_ER.utf8@saaho pg_catalog | aa_ER@saaho | aa_ER@saaho | aa_ER@saaho pg_catalog | aa_ET| aa_ET| aa_ET pg_catalog | aa_ET.utf8 | aa_ET.utf8 | aa_ET.utf8 (8 rows) regression=# \dOS pg_catalog.aa* List of collations Schema | Name | Collate | Ctype +--+--+-- pg_catalog | aa_DJ| aa_DJ.utf8 | aa_DJ.utf8 pg_catalog | aa_DJ| aa_DJ| aa_DJ pg_catalog | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591 pg_catalog | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8 pg_catalog | aa_ER| aa_ER| aa_ER pg_catalog | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8 pg_catalog | aa_ER.utf8@saaho | aa_ER.utf8@saaho | aa_ER.utf8@saaho pg_catalog | aa_ER@saaho | aa_ER@saaho | aa_ER@saaho pg_catalog | aa_ET| aa_ET| aa_ET pg_catalog | aa_ET.utf8 | aa_ET.utf8 | aa_ET.utf8 (10 rows) The second display is including collations that are not actually available for use in this database. The reason for the weird discrepancy is that processSQLNamePattern is making use of pg_collation_is_visible() in the first case but not the second, and pg_collation_is_visible() rejects incompatible collations (cf CollationGetCollid). Given that this display doesn't include any encoding column, I'm thinking that the intention was to show only relevant collation entries. Which we could do by adding a WHERE clause about the encoding. If the intention was to not restrict that way, don't we need an encoding column? (But I'm not actually sure how we could make that work unsurprisingly without changes in CollationGetCollid, which would likely break other things, so I don't really want to hear suggestions that we should do it the other way ...) 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 Apr 8, 2011, at 7:20 PM, Alvaro Herrera wrote: Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer role is not the super user? As I said to Jeff, it's up to the creator of the wrapper function to ensure that things are safe. Perhaps this new operation should only be superuser-callable, for example. How would you prevent general SQL attacks when manually popping the authentication stack is allowed? The popping and pushing operations would be restricted. You can only pop a single frame, and pushing it back before returning is mandatory. It might be worth thinking about extending this functionality to cover the case for connection pooling. If some SQL can re-tool an existing connection to have the properties of a new connection by a different role, then that would reduce the use-case of connection pooling. If that authorization chain can be pushed and popped by a password or some security token, for example, then that would cover the use cases I mention in this thread: http://archives.postgresql.org/pgsql-general/2006-04/msg00917.php Cheers, M -- 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] using a lot of maintenance_work_mem
Tom, all, Having run into issues caused by small work_mem, again, I felt the need to respond to this. * Tom Lane (t...@sss.pgh.pa.us) wrote: You would break countless things. It might be okay anyway in a trusted environment, ie, one without users trying to crash the system, but there are a lot of security-critical implications of that test. I really don't see work_mem or maintenance_work_mem as security-related parameters. Amusingly, the Postgres95 1.01 release apparently attmpted to make the cap 16GB (but failed and made it 256M instead). After a bit of poking around, I found this commit: commit 85c17dbff8ade0c5237e3ac1ece7cacacfdde399 Author: Tom Lane t...@sss.pgh.pa.us Date: Tue Feb 6 01:53:53 2001 + Out-of-bounds memory allocation request sizes should be treated as just elog(ERROR) not an Assert trap, since we've downgraded out-of-memory to elog(ERROR) not a fatal error. Also, change the hard boundary from 256Mb to 1Gb, just so that anyone who's actually got that much memory to spare can play with TOAST objects approaching a gigabyte. If we want to implement a system to limit what users can request with regard to work_mem then we can do that, but a smart user could probably circumvent such a system by building huge queries.. A system which monitered actual usage and ERROR'd out would probably be better to address that concern. If we were actually trying to support such large allocations, what I'd be inclined to do is introduce a separate call along the lines of MemoryContextAllocLarge() that lacks the safety check. This sounds like the right approach to me. Basically, I'd like to have MemoryContextAllocLarge(), on 64bit platforms, and have it be used for things like sorts and hash tables. We'd need to distinguish that usage from things which allocate varlena's and the like. But before expending time on that, I'd want to see some evidence that it's actually helpful for production situations. I'm a bit dubious that you're going to gain much here. I waited ~26hrs for a rather simple query: explain select bunch-of-columns, bunch-of-aggregates from really_big_table where customer_code ~ '^CUST123' group by bunch-of-columns ; QUERY PLAN --- GroupAggregate (cost=37658456.68..42800117.89 rows=10546998 width=146) - Sort (cost=37658456.68..37922131.61 rows=105469973 width=146) Sort Key: bunch-of-columns - Seq Scan on really_big_table (cost=0.00..15672543.00 rows=105469973 width=146) Filter: ((customer_code)::text ~ '^CUST123'::text) (5 rows) This query ran for ~26 hours, where ~20 hours was spent sorting the ~30G which resulted from the Seq-Scan+filter (the raw table is ~101G). The resulting table (after the GroupAgg) was only 30MB in size (~80k rows instead of the estimated 10M above). Another query against the same 101G table, which used a HashAgg, completed just a bit faster than the 26 hours: QUERY PLAN HashAggregate (cost=19627666.99..19631059.80 rows=90475 width=116) (actual time=1435604.737..1435618.293 rows=4869 loops=1) - Seq Scan on really_big_table (cost=0.00..15672543.00 rows=105469973 width=116) (actual time=221029.805..804802.329 rows=104616597 loops=1) Filter: ((agency_hierarchy_code)::text ~ '^CUST123'::text) Total runtime: 1435625.388 ms (4 rows) Now, this query had fewer columns in the group by (required to convince PG to use a HashAgg), but, seriously, it only took 23 minutes to scan through the entire table. It could have taken 3 hours and I would have been happy. Admittedly, part of the problem here is the whole cross-column correllation stats problem, but I wouldn't care if the stats were right and I ended up with a 1.5G hash table and 10M records result, I'm pretty sure generating that would be a lot faster using a HashAgg than a sort+GroupAgg. Also, I feel like we're pretty far from having the cross-column statistics fixed and I'm not 100% convinced that it'd actually come up with a decent result for this query anyway (there's 18 columns in the group by clause for the first query...). Anyhow, I just wanted to show that there are definitely cases where the current limit is making things difficult for real-world PG users on production systems. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade bug found!
bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is in? This is our production db with two hot standby's running off it. Unfortunately, I don't think the script that Bruce posted will help if the clog files have been removed (which appears to be the case here). Do you have a backup which includes older files which existed under the 'pg_clog' directory under your database's root? Hopefully you do and can restore those and restart the database. If you restore and then restart then Bruce's script could be run and hopefully would clear out these errors. Bruce, please correct me if I got any part of this wrong. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade bug found!
Hi Stephen, On Fri, Apr 8, 2011 at 6:57 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is in? This is our production db with two hot standby's running off it. Unfortunately, I don't think the script that Bruce posted will help if the clog files have been removed (which appears to be the case here). Do you have a backup which includes older files which existed under the 'pg_clog' directory under your database's root? Hopefully you do and can restore those and restart the database. If you restore and then restart then Bruce's script could be run and hopefully would clear out these errors. Bruce, please correct me if I got any part of this wrong. Thanks, Stephen I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. It exists in an untouched backup directory that I originally made when I set up the backup and ran pg_upgrade. I'm not sure if it is from version 8.4 or 9.0.2 though. Is it safe to just copy it into my production pg_clog dir and restart? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. Great! And there's no file in pg_clog which matches that name (or exist which are smaller in value), right? It exists in an untouched backup directory that I originally made when I set up the backup and ran pg_upgrade. I'm not sure if it is from version 8.4 or 9.0.2 though. Is it safe to just copy it into my production pg_clog dir and restart? It should be, provided you're not overwriting any files or putting a clog file in place which is greater than the other clog files in that directory. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pgindent
On Fri, Apr 8, 2011 at 8:05 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that out? Yeah. There are three animals reporting (running Linux, FreeBSD and MinGW builds). My Cygwin animal should report within the hour, and I'm working on getting an MSVC build into the mix for the first time ever. That should be done within the next 24 hours. As soon as it is I'll commit the consolidated list. 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] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. Great! And there's no file in pg_clog which matches that name (or exist which are smaller in value), right? It exists in an untouched backup directory that I originally made when I set up the backup and ran pg_upgrade. I'm not sure if it is from version 8.4 or 9.0.2 though. Is it safe to just copy it into my production pg_clog dir and restart? It should be, provided you're not overwriting any files or putting a clog file in place which is greater than the other clog files in that directory. It appears that there are no files lower. Missing clog: 04BE production pg_clog dir: ls -lhrt 9.0/data/pg_clog total 38M -rw--- 1 postgres postgres 256K Jan 25 21:04 04BF -rw--- 1 postgres postgres 256K Jan 26 12:35 04C0 -rw--- 1 postgres postgres 256K Jan 26 20:58 04C1 -rw--- 1 postgres postgres 256K Jan 27 13:02 04C2 -rw--- 1 postgres postgres 256K Jan 28 01:00 04C3 ... old backup pg_clog dir (possibly v8.4) ... -rw--- 1 postgres postgres 256K Jan 23 21:11 04BB -rw--- 1 postgres postgres 256K Jan 24 08:56 04BC -rw--- 1 postgres postgres 256K Jan 25 06:32 04BD -rw--- 1 postgres postgres 256K Jan 25 10:58 04BE -rw--- 1 postgres postgres 256K Jan 25 20:44 04BF -rw--- 1 postgres postgres 8.0K Jan 25 20:54 04C0 So, if I have this right, my steps to take are: - copy the backup 04BE to production pg_clog dir - restart the database - run Bruce's script Does that sound right? Has anyone else experienced this? I'm leery of testing this on my production db, as our last pg_dump was from early this morning, so I apologize for being so cautious. Thanks, Bricklen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 7:20 PM, bricklen brick...@gmail.com wrote: On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. Great! And there's no file in pg_clog which matches that name (or exist which are smaller in value), right? It exists in an untouched backup directory that I originally made when I set up the backup and ran pg_upgrade. I'm not sure if it is from version 8.4 or 9.0.2 though. Is it safe to just copy it into my production pg_clog dir and restart? It should be, provided you're not overwriting any files or putting a clog file in place which is greater than the other clog files in that directory. It appears that there are no files lower. Missing clog: 04BE production pg_clog dir: ls -lhrt 9.0/data/pg_clog total 38M -rw--- 1 postgres postgres 256K Jan 25 21:04 04BF -rw--- 1 postgres postgres 256K Jan 26 12:35 04C0 -rw--- 1 postgres postgres 256K Jan 26 20:58 04C1 -rw--- 1 postgres postgres 256K Jan 27 13:02 04C2 -rw--- 1 postgres postgres 256K Jan 28 01:00 04C3 ... old backup pg_clog dir (possibly v8.4) ... -rw--- 1 postgres postgres 256K Jan 23 21:11 04BB -rw--- 1 postgres postgres 256K Jan 24 08:56 04BC -rw--- 1 postgres postgres 256K Jan 25 06:32 04BD -rw--- 1 postgres postgres 256K Jan 25 10:58 04BE -rw--- 1 postgres postgres 256K Jan 25 20:44 04BF -rw--- 1 postgres postgres 8.0K Jan 25 20:54 04C0 So, if I have this right, my steps to take are: - copy the backup 04BE to production pg_clog dir - restart the database - run Bruce's script Does that sound right? Has anyone else experienced this? I'm leery of testing this on my production db, as our last pg_dump was from early this morning, so I apologize for being so cautious. Thanks, Bricklen What I've tested and current status: When I saw the announcement a few hours ago, I started setting up a 9.0.3 hot standby. I brought it live a few minutes ago. - I copied over the 04BE clog from the original backup, - restarted the standby cluster - ran the script against the main database and turned up a bunch of other transactions that were missing: psql:pg_upgrade_tmp.sql:539: ERROR: could not access status of transaction 1248683931 DETAIL: Could not open file pg_clog/04A6: No such file or directory. psql:pg_upgrade_tmp.sql:540: ERROR: could not access status of transaction 1249010987 DETAIL: Could not open file pg_clog/04A7: No such file or directory. psql:pg_upgrade_tmp.sql:541: ERROR: could not access status of transaction 1250325059 DETAIL: Could not open file pg_clog/04A8: No such file or directory. psql:pg_upgrade_tmp.sql:542: ERROR: could not access status of transaction 1252759918 DETAIL: Could not open file pg_clog/04AA: No such file or directory. psql:pg_upgrade_tmp.sql:543: ERROR: could not access status of transaction 1254527871 DETAIL: Could not open file pg_clog/04AC: No such file or directory. psql:pg_upgrade_tmp.sql:544: ERROR: could not access status of transaction 1256193334 DETAIL: Could not open file pg_clog/04AD: No such file or directory. psql:pg_upgrade_tmp.sql:556: ERROR: could not access status of transaction 1268739471 DETAIL: Could not open file pg_clog/04B9: No such file or directory. I checked, and found that each one of those files exists in the original backup location. - scp'd those files to the hot standby clog directory, - pg_ctl stop -m fast - pg_ctl start - ran the script Hit a bunch of missing clog file errors like above, repeated the scp + bounce + script process 4 or 5 more times until no more missing clog file messages surfaced. Now, is this safe to run against my production database? **Those steps again, to run against prod: cp the clog files from the original backup to dir to my production pg_clog dir bounce the database run the script against all database in the cluster Anyone have any suggestions or changes before I commit myself to this course of action? Thanks, Bricklen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues during the process. Also, a database-wide 'VACUUM FREEZE;' should be lower-risk, if you can afford it (it will cause a lot of i/o on the system). The per-table 'VACUUM FREEZE table;' that the script does can end up removing clog files prematurely. Anyone have any suggestions or changes before I commit myself to this course of action? If you run into problems, and perhaps even before starting, you may want to pop in to #postgresql on irc.freenode.net, there are people there who can help you with this process who are very familiar with PG. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade bug found!
Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues during the process. Also, a database-wide 'VACUUM FREEZE;' should be lower-risk, if you can afford it (it will cause a lot of i/o on the system). The per-table 'VACUUM FREEZE table;' that the script does can end up removing clog files prematurely. Anyone have any suggestions or changes before I commit myself to this course of action? If you run into problems, and perhaps even before starting, you may want to pop in to #postgresql on irc.freenode.net, there are people there who can help you with this process who are very familiar with PG. Stephen is 100% correct and we have updated the wiki to explain recovery details: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues during the process. Also, a database-wide 'VACUUM FREEZE;' should be lower-risk, if you can afford it (it will cause a lot of i/o on the system). The per-table 'VACUUM FREEZE table;' that the script does can end up removing clog files prematurely. Anyone have any suggestions or changes before I commit myself to this course of action? If you run into problems, and perhaps even before starting, you may want to pop in to #postgresql on irc.freenode.net, there are people there who can help you with this process who are very familiar with PG. Stephen is 100% correct and we have updated the wiki to explain recovery details: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix Thanks guys, I really appreciate your help. For the vacuum freeze, you say database-wide, should I run vacuumdb -a -v -F ? Will freezing the other tables in the cluster help (not sure how that works with template0/1 databases?) -- 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: Allow SQL-language functions to reference parameters by parameter name
2011/4/9 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Hence the GUC. Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems. If you need one GUC setting for your application to work, and the extension you have installed needs the other setting, you're screwed. In the worst case, if a security-definer function is involved, you can create a security hole, for example by convincing the system that id = $1 is intended to mean $1 = $1, or some such. You can of course attach the GUC settings to each individual function, but that doesn't really work either unless you do it for every function in the system. The fundamental problem here is that GUCs are dynamically scoped, while this problem is lexically scoped. Yeah. In the plpgsql case, we did make provisions to control the behavior per-function. In principle we could do the same for SQL functions, but it'd be rather a PITA I think. (In particular, the easy way out of attaching SET clauses to the functions would be a bad idea because it would defeat inlining.) what about a new language like SQLc? - like SQL compatibility. pg_upgrade can move old code into this compatibility language when detect some posible problems. Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent
On 04/08/2011 10:12 PM, Robert Haas wrote: On Fri, Apr 8, 2011 at 8:05 PM, Andrew Dunstanand...@dunslane.net wrote: On 04/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that out? Yeah. There are three animals reporting (running Linux, FreeBSD and MinGW builds). My Cygwin animal should report within the hour, and I'm working on getting an MSVC build into the mix for the first time ever. That should be done within the next 24 hours. As soon as it is I'll commit the consolidated list. Thanks! 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). 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] pg_upgrade bug found!
bricklen, * bricklen (brick...@gmail.com) wrote: Thanks guys, I really appreciate your help. For the vacuum freeze, you say database-wide, should I run vacuumdb -a -v -F ? Will freezing the other tables in the cluster help (not sure how that works with template0/1 databases?) Yes, using the command-line 'vacuumdb -a -v -F' would work. It won't try to vacuum template0, and doing template1 is correct. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade bug found!
bricklen wrote: On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. ?One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues during the process. ?Also, a database-wide 'VACUUM FREEZE;' should be lower-risk, if you can afford it (it will cause a lot of i/o on the system). ?The per-table 'VACUUM FREEZE table;' that the script does can end up removing clog files prematurely. Anyone have any suggestions or changes before I commit myself to this course of action? If you run into problems, and perhaps even before starting, you may want to pop in to #postgresql on irc.freenode.net, there are people there who can help you with this process who are very familiar with PG. Stephen is 100% correct and we have updated the wiki to explain recovery details: ? ? ? ?http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix Thanks guys, I really appreciate your help. For the vacuum freeze, you say database-wide, should I run vacuumdb -a -v -F ? Will freezing the other tables in the cluster help (not sure how that works with template0/1 databases?) Exactly. Internally pg_upgrade uses: vacuumdb --all --freeze in the empty new cluster to remove any links to pg_clog files (because we copy the old pg_clog files into the new cluster directory). (This is proof that the old and new clog files are the same format.) If you run vacuumdb as above in the new cluster, it will again remove any requirement on pg_clog, which is our goal. -- 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] k-neighbourhood search in databases
On Fri, 8 Apr 2011, Josh Berkus 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. Nobody I've talked to, and I asked both Couch and Oracle devs. That's great to know :) 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] pgindent
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? -- 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] pg_upgrade bug found!
Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were leftover from aborted transactions or deleted, no? So you could just fill those files with the 0x55 pattern (signalling all transactions are committed) and the net result should be the same. No? Forgive me if I'm missing something. I haven't been following this thread and I'm more than a little tired (but wanted to shoot this today because I'm gonna be able to, until Monday). -- Á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] Open issues for collations
On fre, 2011-04-08 at 16:14 -0300, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem scenario? Or should we just wait around and see what actual problems, if any, will be reported? Well, my opinion is that normal users never see the output of initdb at all, so I don't think there's that much value in complaining there. Those users are not going to have those problems anyway. The problematic users are going to be those running on unusual platforms. So what is your opinion on the original question? -- 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 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 -- Jeremiah Peschka Microsoft SQL Server MVP MCITP: Database Developer, DBA
Re: [HACKERS] Open issues for collations
Excerpts from Peter Eisentraut's message of sáb abr 09 01:32:28 -0300 2011: On fre, 2011-04-08 at 16:14 -0300, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem scenario? Or should we just wait around and see what actual problems, if any, will be reported? Well, my opinion is that normal users never see the output of initdb at all, so I don't think there's that much value in complaining there. Those users are not going to have those problems anyway. The problematic users are going to be those running on unusual platforms. So what is your opinion on the original question? I feel throwing a warning is appropriate here, because people for which problems are most likely are going to see them. -- Á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] alpha5
Excerpts from Peter Eisentraut's message of lun mar 28 17:00:01 -0300 2011: On mån, 2011-03-28 at 09:35 -0400, Robert Haas wrote: Actually those are all my fault. Sorry, I'm still learning the ropes. I didn't realize xref couldn't be used in the release notes; it looks like Bruce used link rather than xref for the things I used xref for. This is the sort of thing for which make maintainer-check would be very useful. And/or we could add the creation of these files to make doc or make world or something. I suggest having them be created in doc/src/sgml all target. This gets them in make docs and make world AFAICT. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Fri, Apr 8, 2011 at 9:28 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were leftover from aborted transactions or deleted, no? So you could just fill those files with the 0x55 pattern (signalling all transactions are committed) and the net result should be the same. No? Forgive me if I'm missing something. I haven't been following this thread and I'm more than a little tired (but wanted to shoot this today because I'm gonna be able to, until Monday). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support Update on the status of the steps we took, which were: - test on a hot standby by bringing it live, running the script, determing the missing clog files, copying them into the live (hot standby) pg_clog dir Now, on the master, copied the same old clog files into the production *master*, ran vacuumdb -a -v -F. The step I should have taken on the master before the vacuumdb -F would have been to run the http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix script to see if I was missing any clog files on the master. That vacuum freeze step pointed out a clog file, I copied that into the master pg_clog dir, ran the aforementioned script. It didn't fail on any of the clog files this time, so now I am rerunning the vacuum freeze command and hoping like hell it works! If the current run of the vacuum freeze fails, I'll report back. Thanks again for everyone's help. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
