Re: [HACKERS] MERGE vs REPLACE
> > And yes merge CAN be used to do REPLACE (oracle uses their dummy table > for this, we can use the fact that FROM clause isn't required in postgres). > the FROM clause is required by default (starting with 8.1) unless you change a postgresql.conf parameter. and i don't think that idea will have any fan... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 5:50 PM, Tom Lane wrote:
Bob Ippolito <[EMAIL PROTECTED]> writes:
I don't touch pg_class at all... this is what I'm doing (over and
over again).
-- clone_table is almost always a no-op, but once a day it creates a
new table
SELECT clone_table('ping', 'ping_%s', '')
SELECT drop_ping_constraints('ping_%s')
-- stuff that doesn't effect DDL
SELECT add_ping_constraints('ping_%s')
Hm, do the drop/add constraint functions get executed even when
clone_table decides not to make a new table? If so, that would
probably
explain the pattern I'm seeing in the dump of many updates of the
pg_class row.
Yes, they do. The constraints are there for constraint exclusion.
This still doesn't give us a hint why the row disappeared, but
maybe we
can try running these functions for awhile and see if anyone can
reproduce a failure.
If it matters, I have had the same code running on Bizgres 0.7.4 for
quite some time with no issues at all. I may just have to migrate
the test server to Bizgres 0.8 if we can't figure out why PostgreSQL
8.1.0 choked here.
-bob
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Use of 8192 as BLCKSZ in xlog.c
Qingqing Zhou wrote: "Mark Kirkwood" <[EMAIL PROTECTED]> wrote In two of the sections covered by #ifdef WAL_DEBUG there are declarations like: charbuf[8192]; Those two 8192 have nothing to do with BLCKSZ, it is just an arbitrary buffer size as long as it is big enough to hold debug information. Thanks - of course, different sort of buffer! It is a bit more obvious now that I'm running with WAL_DEBUG enabled, and can see that nature of the output. As has been suggested, maybe a comment about the size and nature of 'buf' might be a nice addition. cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Are NULLs in Arrays compressed?
I thought NULLs don't work in arrays yet? :-) http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php http://developer.postgresql.org/docs/postgres/arrays.html Someone's checked that this NULLs in arrays stuff doesn't affect indexes over array elements, etc.? Or indexes that don't store nulls, etc. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Bug in predicate indexes?
<(integer,bigint) <(bigint,integer) <(smallint,integer) <(integer,smallint) <(real,double precision) <(double precision,real) <(smallint,bigint) <(bigint,smallint) <(date,timestamp without time zone) <(date,timestamp with time zone) <(timestamp without time zone,date) <(timestamp with time zone,date) <(timestamp without time zone,timestamp with time zone) <(timestamp with time zone,timestamp without time zone) I'm not sure this is worth documenting given that it's likely to change by 8.2 anyway. I think that we should be explicit as possible. This is a limitation that effects every release that is currently supported by the community. Which is as least 7.4, 8.0, 8.1 and possibly 7.3 (did we ever come to conclusion on that?). The limitation should be addressed and patched to each documentation set. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Are NULLs in Arrays compressed?
On Mon, Nov 21, 2005 at 08:12:10PM -0700, Michael Fuhr wrote: > On Mon, Nov 21, 2005 at 04:46:40PM -0500, [EMAIL PROTECTED] wrote: > > On Mon, Nov 21, 2005 at 08:51:32PM +, Simon Riggs wrote: > > > Are NULLs in Arrays compressed? > > > Just as NULLs are with normal unary datatypes. > > I thought NULLs don't work in arrays yet? :-) > http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php > http://developer.postgresql.org/docs/postgres/arrays.html Ahh... Thanks. And you did it right. Good. :-) mark P.S. I followed the discussions regarding what to do, but missed the conclusion and the resulting patch. -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
Jim C. Nasby wrote: On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote: I don't think MERGE can really be made to be both though, in which case it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON DUPLICATE UPDATE something else. Perhaps a special form of MERGE where you know it's going to be doing that locking. I really don't like the idea of making the SQL2003 version of MERGE be the MERGE special case (by requiring someone to take a table lock ahead of time or do something else odd). Anyone know off-hand what the big 3 do? If the industry consensus is that merge should actually be REPLACE/INSERT ON DUPLICATE UPDATE then it's probably better to follow that lead. It was already said here that oracle and db2 both use MERGE, dunno about mssql. And yes merge CAN be used to do REPLACE (oracle uses their dummy table for this, we can use the fact that FROM clause isn't required in postgres). -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Are NULLs in Arrays compressed?
On Mon, Nov 21, 2005 at 04:46:40PM -0500, [EMAIL PROTECTED] wrote: > On Mon, Nov 21, 2005 at 08:51:32PM +, Simon Riggs wrote: > > Are NULLs in Arrays compressed? > > Just as NULLs are with normal unary datatypes. > > I thought NULLs don't work in arrays yet? :-) http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php http://developer.postgresql.org/docs/postgres/arrays.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Use of 8192 as BLCKSZ in xlog.c
On Mon, 21 Nov 2005, Michael Glaesemann wrote: > > Would it make sense to abstract that out so it's clear that it's > *not* related to BLCKSZ? Or maybe just a comment would be enough. > "Insprite of incremental improvement", I think rename "buf" to "str" would work, Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Use of 8192 as BLCKSZ in xlog.c
On Nov 22, 2005, at 11:44 , Qingqing Zhou wrote: "Mark Kirkwood" <[EMAIL PROTECTED]> wrote In two of the sections covered by #ifdef WAL_DEBUG there are declarations like: charbuf[8192]; Those two 8192 have nothing to do with BLCKSZ, it is just an arbitrary buffer size as long as it is big enough to hold debug information. Would it make sense to abstract that out so it's clear that it's *not* related to BLCKSZ? Or maybe just a comment would be enough. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Use of 8192 as BLCKSZ in xlog.c
"Mark Kirkwood" <[EMAIL PROTECTED]> wrote > In two of the sections covered by #ifdef WAL_DEBUG there are declarations > like: > > charbuf[8192]; > > It seems to me that these should be: > > charbuf[BLCKSZ]; > Those two 8192 have nothing to do with BLCKSZ, it is just an arbitrary buffer size as long as it is big enough to hold debug information. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Use of 8192 as BLCKSZ in xlog.c
In two of the sections covered by #ifdef WAL_DEBUG there are declarations like: charbuf[8192]; It seems to me that these should be: charbuf[BLCKSZ]; - or have I misunderstood what is going on here? I realize that it's probably not terribly significant, as most people will do development with BLCKSZ=8192 anyway - I'm just trying to understand the code ... :-). regards Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes:
> I don't touch pg_class at all... this is what I'm doing (over and
> over again).
> -- clone_table is almost always a no-op, but once a day it creates a
> new table
> SELECT clone_table('ping', 'ping_%s', '')
> SELECT drop_ping_constraints('ping_%s')
> -- stuff that doesn't effect DDL
> SELECT add_ping_constraints('ping_%s')
Hm, do the drop/add constraint functions get executed even when
clone_table decides not to make a new table? If so, that would probably
explain the pattern I'm seeing in the dump of many updates of the
pg_class row.
This still doesn't give us a hint why the row disappeared, but maybe we
can try running these functions for awhile and see if anyone can
reproduce a failure.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Practical error logging for very large COPY statements
Seems similar to the pgloader project on pgfoundry.org. Chris Simon Riggs wrote: If you've ever loaded 100 million rows, you'll know just how annoying it is to find that you have a duplicate row somewhere in there. Experience shows that there is always one, whatever oath the analyst swears beforehand. It's hard to find out which row is the duplicate, plus you've just screwed up a big table. It needs a VACUUM, then a reload. I'd like to find a way to handle this manual task programmatically. What I'd like to do is add an ERRORTABLE clause to COPY. The main problem is how we detect a duplicate row violation, yet prevent it from aborting the transaction. What I propose is to log uniqueness violations only when there is only a single unique index on a table. Flow of control would be to: locate page of index where value should go lock index block _bt_check_unique, but don't error if violation then insert row into ERRORTABLE else insert row into data block insert row into unique index unlock index block do other indexes Which is very similar code to the recently proposed MERGE logic. With that logic, a COPY will run to completion, yet be able to report the odd couple of unique index violations in found along the way. More importantly we can then handle rows those with another program to locate where those errors came from and resolve them. In most cases with a single unique index, the index inserts are rightmost index entries anyway, so there is scope here for an additional optimisation: keep both index and data blocks locked across multiple row inserts until either the unique index or the data block fills. Thats better than taking a full table lock, since it allows concurrent access to the rest of the table, but its also more efficient than continually re-requesting the same blocks (which looks like about 10-15% saving on performance from hash lookups, lock/unlock, etc). Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug in predicate indexes?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Fri, Nov 18, 2005 at 09:58:24AM -0500, Tom Lane wrote: >> This is a known (although perhaps not well documented) limitation of the >> predicate testing logic. You do not need a cast in the query, though, >> only in the index's WHERE condition. > I'm working on a docs patch for this (attached, but un-tested); is > bigint the only datatype this applies to or are there others? You should find out what the problem is before you start writing documentation about it ;-). This has nothing whatever to do with bigint. The problem is here in predtest.c: * Try to find a btree opclass containing the needed operators. * * We must find a btree opclass that contains both operators, else the * implication can't be determined. Also, the pred_op has to be of * default subtype (implying left and right input datatypes are the same); * otherwise it's unsafe to put the pred_const on the left side of the * test. Also, the opclass must contain a suitable test operator matching * the clause_const's type (which we take to mean that it has the same * subtype as the original clause_operator). What the code is trying to do is prove that "X op C1" implies "X op C2" where the constants aren't necessarily the same and the operators are drawn from the same btree opclass, but might themselves be different. Some examples: X = 4 implies X > 3, because 4 > 3 X <= 7 implies X < 3, because 7 < 3 X > 7 doesn't imply X < 14 To make the proof, we need to compare C1 to C2, which means we have to find the required operator for that in the same btree opclass as the two given operators. If X, C1, and C2 are all of different datatypes then this is not going to work in the current design of operator classes, because *all* the operators in a given opclass have the same lefthand-side datatype. (I've been thinking about ways to relax that in future PG versions, but don't yet have a proposal to make.) The current code requires X and C2 to be of the same type, which means that the needed operator for "C2 op C1" will be in the same opclass in which we can find the operator for X op C1. The bottom line is that if you want the predicate prover to be at all smart about a comparison in the index WHERE clause, the comparison can't be cross-type. Otherwise, the only way it will match it is with an exact match to the query's WHERE clause. Example: this will still work query: WHERE bigintcol = 42 index: WHERE bigintcol = 42 but not this: query: WHERE bigintcol = 42 index: WHERE bigintcol >= 4 The last case needs "bigintcol >= 4::bigint" in the index predicate in order to be provable from a related-but-not-identical query condition. This applies to anyplace where we have cross-type comparisons, which in a quick look in pg_operator seems to be <(integer,bigint) <(bigint,integer) <(smallint,integer) <(integer,smallint) <(real,double precision) <(double precision,real) <(smallint,bigint) <(bigint,smallint) <(date,timestamp without time zone) <(date,timestamp with time zone) <(timestamp without time zone,date) <(timestamp with time zone,date) <(timestamp without time zone,timestamp with time zone) <(timestamp with time zone,timestamp without time zone) I'm not sure this is worth documenting given that it's likely to change by 8.2 anyway. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Should libedit be preferred to libreadline?
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Also, I suspect we'd want to enable the libedit preference with a switch > rather than just force it, if we want to go this way. Quite. My recollection is that there are other platforms on which readline works and libedit is broken. (Readline used to work just fine even on AIX ;-)) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 4:33 PM, Alvaro Herrera wrote:
Tom Lane wrote:
Bob Ippolito <[EMAIL PROTECTED]> writes:
On Nov 21, 2005, at 3:56 PM, Tom Lane wrote:
Well, I count at least a couple hundred deleted versions of that
table
row :-(. What the heck were you doing with it?
The ETL process keeps trying until it succeeds or someone stops it,
so I guess that's why there's so much churn in there for that table.
Kept trying to create it, and ran into the issue. I'd estimate
around 1700 to 1800 dead versions of that table, because it ran for
some time before I noticed and stopped it... this is just a test box
after all, I don't have 8.1 in production yet (thankfully!).
Um, no, that theory doesn't seem to explain the evidence. A failed
insertion would result in a row with an uncommitted XMIN and no XMAX.
All of the entries I'm seeing have both XMIN and XMAX set. A good-
size
fraction have the same XMIN and XMAX (but different CMIN and
CMAX), but
I see some that have different XMIN and XMAX. It looks to me like
the
table was definitely created successfully, and it survived across
multiple transactions ... but something was doing a lot of DDL
changes
on it. If we could find out what, maybe we could reproduce the
problem.
Maybe the UPDATE pg_class SET relhastriggers='f' that people is so
fond
of doing to deactivate triggers? Or something similar?
I don't touch pg_class at all... this is what I'm doing (over and
over again).
-- clone_table is almost always a no-op, but once a day it creates a
new table
SELECT clone_table('ping', 'ping_%s', '')
SELECT drop_ping_constraints('ping_%s')
-- stuff that doesn't effect DDL
SELECT add_ping_constraints('ping_%s')
and the referenced UDFs are as follows:
CREATE OR REPLACE FUNCTION
clone_table(parent text, child text, extra text) RETURNS boolean
AS $$
DECLARE
tmprec record;
user_index record;
parent_constraint record;
user_index_column record;
indexname text;
i integer;
columns text[];
BEGIN
-- are we done?
FOR tmprec IN
SELECT 1 FROM pg_sysviews.pg_user_tables WHERE
table_name=child
LOOP
RETURN FALSE;
END LOOP;
-- inherit the table
EXECUTE 'CREATE TABLE '
|| quote_ident(child)
|| '('
|| extra
|| ') INHERITS ('
|| quote_ident(parent)
|| ')';
FOR parent_constraint IN
SELECT *
FROM pg_sysviews.pg_user_table_constraints A
WHERE A.table_name = parent
LOOP
EXECUTE 'ALTER TABLE '
|| quote_ident(child)
|| ' ADD '
|| parent_constraint.definition;
END LOOP;
i := 0;
FOR user_index IN
SELECT *
FROM pg_sysviews.pg_user_indexes A
WHERE
A.table_name = parent
AND A.index_name != (parent || '_pkey')
LOOP
i := i + 1;
indexname := child;
columns := '{}'::text[];
FOR user_index_column IN
SELECT B.column_name, quote_ident(B.column_name) AS col
FROM pg_sysviews.pg_user_index_columns B
WHERE
B.table_name = user_index.table_name
AND B.index_name = user_index.index_name
ORDER BY B.column_position
LOOP
indexname := indexname || '_' ||
user_index_column.column_name;
columns := array_append(columns, user_index_column.col);
END LOOP;
IF user_index.predicate IS NOT NULL THEN
indexname := indexname || '_p' || i::text;
END IF;
-- this is not complete, but works
-- missing tablespace, index_method, is_clustered,
EXECUTE ('CREATE '
|| (CASE WHEN user_index.is_unique THEN 'UNIQUE ' ELSE
'' END)
|| 'INDEX '
|| quote_ident(indexname)
|| ' ON '
|| quote_ident(child)
|| ' USING '
|| quote_ident(user_index.index_method)
|| ' ('
|| array_to_string(columns, ',')
|| ')'
|| (CASE WHEN user_index.predicate IS NOT NULL
THEN ' WHERE ' || user_index.predicate
ELSE '' END)
);
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
drop_ping_constraints(ping_table text) RETURNS void
AS $drop_ping_constraints$
DECLARE
next_sql text;
constraint_rec record;
BEGIN
next_sql := $sql$
SELECT
"constraint_name"
FROM pg_sysviews.pg_user_table_constraints
WHERE "constraint_name" IN ($sql$
|| quote_literal(ping_table || '_timestamp_check')
|| ', '
|| quote_literal(ping_table || '_id_check')
|| ')';
-- RAISE NOTICE 'SQL: %', next_sql;
FOR constraint_rec IN EXECUTE next_sql LOOP
next_sql := 'ALTER TABLE '
Re: [HACKERS] Bug in predicate indexes?
On Fri, Nov 18, 2005 at 09:58:24AM -0500, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > It appears that predicate indexes won't uses indexes on int8 columns > > unless they are casted: > > This is a known (although perhaps not well documented) limitation of the > predicate testing logic. You do not need a cast in the query, though, > only in the index's WHERE condition. I'm working on a docs patch for this (attached, but un-tested); is bigint the only datatype this applies to or are there others? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 Index: doc/src/sgml/indices.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/indices.sgml,v retrieving revision 1.55 diff -u -r1.55 indices.sgml --- doc/src/sgml/indices.sgml 7 Nov 2005 17:36:44 - 1.55 +++ doc/src/sgml/indices.sgml 22 Nov 2005 00:30:55 - @@ -525,6 +525,16 @@ feature, but there are several situations in which they are useful. + + +Predicate indexes on bigint () columns will +not be used unless they are casted: + +CREATE INDEX foo ON test_key (id) WHERE id >= 5::bigint; + + + + One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Should libedit be preferred to libreadline?
Nice analysis, but we can't hack configure like that. It has to be able to be fully generated from its sources. I think the other source file you would need to look at is config/programs.m4. (Not sure about quoting $ac_popdir - why only that one?) Also, I suspect we'd want to enable the libedit preference with a switch rather than just force it, if we want to go this way. cheers andrew Seneca Cunningham wrote: It would certainly seem so on AIX. In tracking down why postgres 8.x would segfault on AIX 5.3, it became apparent to me that libreadline.a is a problematic library to link against and that libedit.a is preferable (and for reasons other than that readline is GPL while postgres is BSD-licensed). With AIX 5, the easiest way to get a shared object is to pass "-bexpall" to the linker. This results in all symbols being exported. The problem with this is that the linker will export all of libreadline's and libhistory's symbols. In the case of libreadline.so.4 (and .5) on AIX 5 this includes symbols like strncpy and memmove, but on .4, not memcpy. This is likely because libc.a does not export them. What results from this is that when postgres is linked against readline on AIX, it gets these memory functions through readline instead of its own code. When readline 4.3 is used (what IBM provides in their "AIX Toolbox for Linux"), postgres is known to crash. These segfaults (if postgres was compiled with gcc) have occurred on AIX 5.3ML3, AIX 5.3ML1, and AIX 5.2ML7. With readline 5.0, postgres merely gets these functions through the shared library memory segments instead of the user memory segments[6]. While it is possible to build libreadline in a manner that doesn't export strncpy, neither of the prebuilt readlines for AIX 5 that I checked were both shared and did not export strncpy. IBM's readline[5] exports strncpy, UCLA's readline[4] is static. Building a shared readline that doesn't export strncpy requires creating export files for libreadline and libhistory that only list the symbols that they are supposed to export and editing the shared library Makefile to add the exports flags to the appropriate linker calls. Whatever strategy we might take, using readline on AIX requires considerable trickery and hacking around with the build environments. Simply put, it's ghastly. On the other hand, the port of NetBSD's editline that I tried[1] works without build-hackery to the library and has reasonable exports. The only changes to postgres that I needed to make were confined to telling the configure script to check for libedit before libreadline and adding a test for histedit.h. The attached patch contains my modifications. It is also possible to use a wrapper like rlwrap[2] instead of linking postgres against libreadline or libedit. [1] port of NetBSD's editline http://www.thrysoee.dk/editline/ [2] rlwrap http://utopia.knoware.nl/~hlub/uck/software/ [3] IBM Redbook "AIX 5L Porting Guide", section 9.2 http://www.redbooks.ibm.com/abstracts/sg246034.html?Open http://www.redbooks.ibm.com/redbooks/pdfs/sg246034.pdf [4] UCLA's readline package http://aixpdslib.seas.ucla.edu/packages/readline.html [5] IBM's readline package http://www-03.ibm.com/servers/aix/products/aixos/linux/download.html [6] IBM Redbook "Developing and Porting C and C++ Applications on AIX", page 110 http://www.redbooks.ibm.com/abstracts/sg245674.html?Open http://www.redbooks.ibm.com/redbooks/pdfs/sg245674.pdf [patch snipped] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Practical error logging for very large COPY statements
Tom Lane wrote: Simon Riggs <[EMAIL PROTECTED]> writes: What I'd like to do is add an ERRORTABLE clause to COPY. The main problem is how we detect a duplicate row violation, yet prevent it from aborting the transaction. If this only solves the problem of duplicate keys, and not any other kind of COPY error, it's not going to be much of an advance. Yeah, and I see errors from bad data as often as from violating constraints. Maybe the best way if we do something like this would be to have the error table contain a single text, or maybe bytea, field which contained the raw offending input line. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Tom Lane wrote: > Bob Ippolito <[EMAIL PROTECTED]> writes: > > On Nov 21, 2005, at 3:56 PM, Tom Lane wrote: > >> Well, I count at least a couple hundred deleted versions of that table > >> row :-(. What the heck were you doing with it? > > > The ETL process keeps trying until it succeeds or someone stops it, > > so I guess that's why there's so much churn in there for that table. > > Kept trying to create it, and ran into the issue. I'd estimate > > around 1700 to 1800 dead versions of that table, because it ran for > > some time before I noticed and stopped it... this is just a test box > > after all, I don't have 8.1 in production yet (thankfully!). > > Um, no, that theory doesn't seem to explain the evidence. A failed > insertion would result in a row with an uncommitted XMIN and no XMAX. > All of the entries I'm seeing have both XMIN and XMAX set. A good-size > fraction have the same XMIN and XMAX (but different CMIN and CMAX), but > I see some that have different XMIN and XMAX. It looks to me like the > table was definitely created successfully, and it survived across > multiple transactions ... but something was doing a lot of DDL changes > on it. If we could find out what, maybe we could reproduce the problem. Maybe the UPDATE pg_class SET relhastriggers='f' that people is so fond of doing to deactivate triggers? Or something similar? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] why is gist index taking so much space on the disc
On Mon, 21 Nov 2005, Martijn van Oosterhout wrote: On Mon, Nov 21, 2005 at 08:14:44PM +0100, Grzegorz Jaskiewicz wrote: You mean you sometimes put the same elements in the two halves? You shouldn't do that. The whole point is that the search will descend any node that matches consistant, but any single key should only appear once in each index. picksplit should *split* the set, not return two sets about the same size as you started... Nope, I mean that 'masks' created to match either 'half' sometimes match elements in the other one. This shouldn't be a big deal, just one level to go down on query to much more specific result set. I have fixed that with, somewhat hack. It's not a hack, that's how it's supposed to work. An entry should only appear once in the index, but it could appear in multiple places. Like you say, some entries can go into either half. B-Trees are the rather special case that you can always split a set of values into two non-overlapping sets. With geometric types (like your bitmasks) you can't avoid overlap sometimes so you have to follow multiple branches to check if an element is there or not. Your pseudo code is good and will work fine. However, ideally you want to divide the overlap in such a way that later splits work better. Maybe by trying to decide which mask is "closer". The better the splitting the more efficient your tree will become. Ofcourse, perfect splitting may be expensive but then it depends on how many inserts vs how many selects. If you do a lot of searches it may be worth the time. Martijn is perfectly right here. You, probably, need to read a bit some classical papers, for example, "R-TREES: A dynamic index structure for spatial searching" by Antonin Guttman. _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > On Nov 21, 2005, at 3:56 PM, Tom Lane wrote: >> Well, I count at least a couple hundred deleted versions of that table >> row :-(. What the heck were you doing with it? > The ETL process keeps trying until it succeeds or someone stops it, > so I guess that's why there's so much churn in there for that table. > Kept trying to create it, and ran into the issue. I'd estimate > around 1700 to 1800 dead versions of that table, because it ran for > some time before I noticed and stopped it... this is just a test box > after all, I don't have 8.1 in production yet (thankfully!). Um, no, that theory doesn't seem to explain the evidence. A failed insertion would result in a row with an uncommitted XMIN and no XMAX. All of the entries I'm seeing have both XMIN and XMAX set. A good-size fraction have the same XMIN and XMAX (but different CMIN and CMAX), but I see some that have different XMIN and XMAX. It looks to me like the table was definitely created successfully, and it survived across multiple transactions ... but something was doing a lot of DDL changes on it. If we could find out what, maybe we could reproduce the problem. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 3:56 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: Sure, here it is: http://undefined.org/mochi.pg_class-1.filedump.gz Well, I count at least a couple hundred deleted versions of that table row :-(. What the heck were you doing with it? As far as I can think offhand, only a schema modification would cause an update of the pg_class row. There's an ETL process that does the following: - Create a temp table - COPY into the temp table - do some transforms on the data - create a dated table if it doesn't already exist - fail miserably because the catalog is busted** **: it normally does something else here, namely inserting a bunch of rows into the table The ETL process keeps trying until it succeeds or someone stops it, so I guess that's why there's so much churn in there for that table. Kept trying to create it, and ran into the issue. I'd estimate around 1700 to 1800 dead versions of that table, because it ran for some time before I noticed and stopped it... this is just a test box after all, I don't have 8.1 in production yet (thankfully!). So what do I do now? Kill the database, start over from a dump, and cross fingers that this doesn't pop up again? -bob ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Improving count(*)
On 11/21/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > What about Greg Stark's idea of combining Simon's idea of storing > per-heap-block xmin/xmax with using that information in an index scan? > ISTM that's the best of everything that's been presented: it allows for > faster index scans without adding a lot of visibility overhead to the > index heap, and it also allows VACUUM to hit only pages that need > vacuuming. Presumably this could also be used as the on-disk backing for > the FSM, or it could potentially replace the FSM. This should be a big win all around, especially now since in memory bitmaps make it more likely that some classes of queries will be pure index. I still think it would be useful to have a estimated_count() which switches to whatever method is needed to get a reasonably accurate count quickly (stats when there are no wheres we can't predict, sampling otherwise if the involved tables are large, and a normal count in other cases.) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Practical error logging for very large COPY statements
Simon Riggs <[EMAIL PROTECTED]> writes: > What I'd like to do is add an ERRORTABLE clause to COPY. The main > problem is how we detect a duplicate row violation, yet prevent it from > aborting the transaction. If this only solves the problem of duplicate keys, and not any other kind of COPY error, it's not going to be much of an advance. > Flow of control would be to: > locate page of index where value should go > lock index block > _bt_check_unique, but don't error > if violation then insert row into ERRORTABLE > else > insert row into data block > insert row into unique index > unlock index block > do other indexes Ugh. Do you realize how many levels of modularity violation are implied by that sketch? Have you even thought about the fact that we have more than one kind of index? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Using FSM to trigger vacuum
While replying to the "Improving count(*)" thread, the following occured to me: Vacuuming a table is only useful if we're nearing xid-wrap or if new tuples are being created in the table. One way to detect the later case is to monitor how many pages that table has in the FSM. Of course there's other ways to do this, but I think there's a distinct advantage to monitoring FSM: it allows us to vacuum at the rate that the space marked as being available by a vacuum is actually being used. So for example, we could set a threshold of keeping X pages is the FSM for each table. When the number of pages in the FSM falls below X for a table, a vacuum would be run against that table. But if we only want X pages in the FSM for that table, we could stop the vacuum once we reach X pages in the FSM. Unfortunately, I think that might leave us pretty succeptable to index bloat from deleted tuples, but maybe there's some clever way around that. If the proposal to track heap block-level metadata happens, that might make this idea a lot more doable. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > Sure, here it is: > http://undefined.org/mochi.pg_class-1.filedump.gz Well, I count at least a couple hundred deleted versions of that table row :-(. What the heck were you doing with it? As far as I can think offhand, only a schema modification would cause an update of the pg_class row. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Improving count(*)
On Fri, Nov 18, 2005 at 02:56:52PM -0500, Gregory Maxwell wrote: > However, some great ideas have been proposed here which would not only > help in that case but would otherwise be quite useful. > > *Inclusion of a 'MVCC inflight' bit in indexes which would allow > skipping MVCC checks in clumps of an index scan which have no pending > changes. This would further close the performance gap between PG and > non-MVCC databases for some workloads. > *Introduction of high performance table sampling, which would be > useful in many applications (including counting where there is a where > clause) as well as for testing and adhoc queries. > and > *a estimate_count() that provides the planner estimate, which would > return right away and provide what is really needed most of the time > people try to count(*) on a large table. What about Greg Stark's idea of combining Simon's idea of storing per-heap-block xmin/xmax with using that information in an index scan? ISTM that's the best of everything that's been presented: it allows for faster index scans without adding a lot of visibility overhead to the index heap, and it also allows VACUUM to hit only pages that need vacuuming. Presumably this could also be used as the on-disk backing for the FSM, or it could potentially replace the FSM. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving count(*)
On Fri, Nov 18, 2005 at 12:08:03AM +, Simon Riggs wrote:
> The trouble is, people moan and constantly. Perhaps we should stick to
> our guns and say, why do you care? From here, I think we should say,
> "show me an application package that needs this so badly we'll change
> PostgreSQL just for them". Prove it and we'll do it. Kinda polite in the
> TODO, but I think we should put something in there that says "things we
> haven't yet had any good reason to improve".
FWIW, this is one of Tom Kyte's (of http://asktom.oracle.com fame) big
complaints: if you have a query where count(*) isn't nearly instant then
you probably don't need an exact count in the first place and should be
happy enough with an estimate. He constantly cites Google ('Result 1-10
of about 38,923') as an example of this.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 3:12 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: Ok, here's the pg_filedump for the pg_class table in the mochi database that is having the issue: Thanks. I don't see any live tuples that look like they could have been the one we want, but there's a whole lot of deleted rows, which pg_filedump won't show with those options. Could you try a pg_filedump with -d option too? Sure, here it is: http://undefined.org/mochi.pg_class-1.filedump.gz -bob ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [ADMIN] ERROR: could not read block
""Magnus Hagander"" <[EMAIL PROTECTED]> wrote > > The way I read it, a delay should help. It's basically running out of > kernel buffers, and we just delay, somebody else (another process, or an > IRQ handler, or whatever) should get finished with their I/O, free up > the buffer, and let us have it. Looking around a bit I see several > references that you should retry on it, but nothing in the API docs. > I do think it's probably a good idea to do a short delay before retrying > - at least to yield the CPU for one slice. That would greatly increase > the probability of someone else finishing their I/O... > More I read on the second thread: " NTBackupread and NTBackupwrite both use buffered I/O. This means that Windows NT caches the I/O that is performed against the stream. It is also the only API that will back up the metadata of a file. This cache is pulled from limited resources: namely, pool and nonpaged pool. Because of this, extremely large numbers of files or files that are very large may cause the pool resources to run low. " So does it imply that if we use unbuffered I/O in Windows system will elminate this problem? If so, just add FILE_FLAG_NO_BUFFERING when we open data file will solve the problem -- but this change in fact very invasive, because it will make the strategy of server I/O optimization totally different from *nix. Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CLUSTER and clustered indices
+1, and I know Sybase had this in 11.0.3, which IIRC is over 10 years old now. BTW, http://archives.postgresql.org/pgsql-performance/2004-08/msg00492.php is one discussion about this from the past. I seem to recall that there was an objection to true Index Organized Tables because it would be too dificult to make that work with MVCC. If that's the case then what I laid out in that email might get some of the benefit without the difficulty. But hopefully it's easy to just store heap values in the leaf nodes of an index. FWIW, I know that Sybase required that an IOT be clustered on a unique index. I think Oracle has the same requirement as well. On Fri, Nov 18, 2005 at 08:30:14AM +, Simon Riggs wrote: > On Thu, 2005-11-17 at 21:57 -0300, Alvaro Herrera wrote: > > > Personally I'd prefer to see index-ordered heaps, where the heap is > > itself an index, so the ordering it automatically kept. > > Agreed. (I think thats case-closed on the previous proposal.) > > As an aside, Index Organized Tables (IOTs) isn't just an Oracle term. > They first used the term, but the concept had already been implemented > in both Tandem (value-ordered) and Teradata (hash-ordered) before this, > as well as numerous OLAP systems. The concept doesn't look to be > patented. > > If anybody is looking for a justification for IOTs, the reduction in > table volume for large tables is very high. IOTs are the equivalent of > removing all of the leaf blocks of the clustered index. > > Best Regards, Simon Riggs > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > Ok, here's the pg_filedump for the pg_class table in the mochi > database that is having the issue: Thanks. I don't see any live tuples that look like they could have been the one we want, but there's a whole lot of deleted rows, which pg_filedump won't show with those options. Could you try a pg_filedump with -d option too? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] ERROR: could not read block
On Thu, Nov 17, 2005 at 07:56:21PM +0100, Magnus Hagander wrote: > The way I read it, a delay should help. It's basically running out of > kernel buffers, and we just delay, somebody else (another process, or an > IRQ handler, or whatever) should get finished with their I/O, free up > the buffer, and let us have it. Looking around a bit I see several > references that you should retry on it, but nothing in the API docs. > I do think it's probably a good idea to do a short delay before retrying > - at least to yield the CPU for one slice. That would greatly increase > the probability of someone else finishing their I/O... If that makes it into code, ISTM it would be good if it also threw a NOTICE so that users could see if this was happening; kinda like the notice about log files being recycled frequently. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bind variables, soft vs hard parse
On Wed, Nov 16, 2005 at 10:19:21AM +0100, Marcus Engene wrote: > Martijn van Oosterhout wrote: > >>This sql cache I think is a really good thing. Is there a reason > >>Postgres hasn't got it? Would it be very hard to implement? From > >>a naive perspective; make a hashvalue from the sql-string to > >>quickly find the cached one, a "last used"-list for keeping > >>track of which to delete when cache full etc seems close to > >>trivial. Does the architecture/internal flow make it hard > >>actually reuse the query data structure? > > > >It's hard to reuse the structure. Also, things like search_path mean > >that the same query text can mean completely different things in > >different backends. Most of the time it's planning that dominates, not > >parsing so storing just the parser output seems somewhat useless. > > Of course I didn't mean only the parse was to be saved. The planning > goes there too. It might be more useful to look at caching only planning and not parsing. I'm not familiar with the output of the parsing stage, but perhaps that could be hashed to use as a lookup into a cache of planned queries. I suspect that would remove issues of different search_paths. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 2:42 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: Ok, so how do I figure out which file(s) are associated with pg_class so I can feed this thing? See contrib/oid2name and/or read http://www.postgresql.org/docs/8.1/static/storage.html Ok, here's the pg_filedump for the pg_class table in the mochi database that is having the issue: (it's also there without gz, but the gz is 228KB versus 2352KB uncompressed) http://undefined.org/mochi.pg_class.filedump.gz -bob ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OS X 7.4 failure
On Thu, Nov 17, 2005 at 12:51:47AM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=cuckoo&dt=2005-11-15%2023:56:22 > > I took a closer look at this, and noticed something interesting: > > ccache gcc -no-cpp-precomp -O2 -fno-strict-aliasing -g -Wall > -Wmissing-prototypes -Wmissing-declarations -bundle execute.o typename.o > descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o > -L../pgtypeslib -L../../../../src/interfaces/libpq -L../../../../src/port > -L/opt/local/lib -lpgtypes -lpq -lintl -lm -o libecpg.so.4.1 > ld: warning can't open dynamic library: /opt/local/lib/libssl.0.9.7.dylib > (checking for undefined symbols may be affected) (No such file or directory, > errno = 2) > ld: warning can't open dynamic library: /opt/local/lib/libcrypto.0.9.7.dylib > (checking for undefined symbols may be affected) (No such file or directory, > errno = 2) > ld: warning multiple definitions of symbol _pg_strncasecmp > /opt/local/lib/libpgtypes.dylib(pgstrcasecmp.o) definition of _pg_strncasecmp > /opt/local/lib/libpq.dylib(pgstrcasecmp.o) definition of _pg_strncasecmp > > You should be asking yourself "what the heck is it doing pulling in > libpgtypes and libpq from /opt/local/lib instead of the current build? > That's way down the -L search list." > > I am not sure about Darwin's linker search rules, but it could easy be > that it first looks through the entire search path for a .dylib and only > upon failing looks for a .so. If so, a .dylib lurking in /opt/local/lib > could capture the build away from the .so that the 7.4 build process > tries to make. > > Solution would be to remove the PG libraries from /opt/local/lib, or > else remove /opt/local/lib from the search path for the 7.4 build > (which'd probably mean removing --with-tcl etc, but I'm not sure they > would work anyway). Excellent catch, it seems that could be what's happening: [EMAIL PROTECTED]:28]~:5%otool -L /opt/local/lib/libpq.dylib /opt/local/lib/libpq.dylib: /opt/local/lib/libpq.4.dylib (compatibility version 4.0.0, current version 4.0.0) /opt/local/lib/libssl.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /opt/local/lib/libcrypto.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 324.9.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 71.1.3) [EMAIL PROTECTED]:29]~:6%ll /opt/local/lib/libssl.* -r-xr-xr-x 2 root admin 322596 22 Jul 02:12 /opt/local/lib/libssl.0.9.8.dylib* -rw-r--r-- 2 root admin 468100 22 Jul 02:12 /opt/local/lib/libssl.a -r-xr-xr-x 2 root admin 322596 22 Jul 02:12 /opt/local/lib/libssl.dylib* [EMAIL PROTECTED]:30]~:7% What's interesting (at least to me) is that psql still works fine, even though it's calling for a version of sibssl that doesn't exist on my laptop: [EMAIL PROTECTED]:30]~:7%otool -L `which psql` /opt/local/bin/psql: /opt/local/lib/libpq.4.dylib (compatibility version 4.0.0, current version 4.0.0) /opt/local/lib/libssl.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /opt/local/lib/libcrypto.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /opt/local/lib/libz.1.dylib (compatibility version 1.0.0, current version 1.2.2) /opt/local/lib/libreadline.5.0.dylib (compatibility version 5.0.0, current version 5.0.0) /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 324.9.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 71.1.3) [EMAIL PROTECTED]:31]~:8% Do you happen to know how Apple's linker gets it's search path? There doesn't seem to be ldconfig or ldconf, and the few things in my environment that reference /opt seem innocent. I can obviously fix the library issue by re-compiling the main PostgreSQL install on this box, but ISTM it would be best if the buildfarm stuff was as seperated from that as possible... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] why is gist index taking so much space on the disc
On Mon, Nov 21, 2005 at 08:14:44PM +0100, Grzegorz Jaskiewicz wrote: > >You mean you sometimes put the same elements in the two halves? You > >shouldn't do that. The whole point is that the search will descend any > >node that matches consistant, but any single key should only appear > >once in each index. > > > >picksplit should *split* the set, not return two sets about the same > >size as you started... > > Nope, I mean that 'masks' created to match either 'half' sometimes > match elements in the other one. > This shouldn't be a big deal, just one level to go down on query to > much more specific result set. > I have fixed that with, somewhat hack. It's not a hack, that's how it's supposed to work. An entry should only appear once in the index, but it could appear in multiple places. Like you say, some entries can go into either half. B-Trees are the rather special case that you can always split a set of values into two non-overlapping sets. With geometric types (like your bitmasks) you can't avoid overlap sometimes so you have to follow multiple branches to check if an element is there or not. Your pseudo code is good and will work fine. However, ideally you want to divide the overlap in such a way that later splits work better. Maybe by trying to decide which mask is "closer". The better the splitting the more efficient your tree will become. Ofcourse, perfect splitting may be expensive but then it depends on how many inserts vs how many selects. If you do a lot of searches it may be worth the time. BTW, I glad you're making progress and hopefully you might be able to publish some code. PostgreSQL could do with some example GiST indexes on bitmaps. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpQxK4MLf7f6.pgp Description: PGP signature
Re: [HACKERS] [pgsql-hackers] Daily digest v1.5568 (24 messages)
On Mon, 21 Nov 2005, Marc Munro wrote: I wonder if this idea might be taken a little further, to allow read-only tablespaces? This would allow old partitions in very large databases to be kept on read-only media, and would allow normal backups to ignore this unchanging set of data. I guess you could do that, but it's really quite a different problem. It also allows for certain specific optimisations for this type of data, as the MVCC rules are now relaxed. Inclusion of a row in a read-only index is now enough to guarantee the visibility of that row to all backends, and fetches based solely on the index now become possible. There's this TODO: Allow data to be pulled directly from indexes Currently indexes do not have enough tuple visibility information to allow data to be pulled from the index without also accessing the heap. One way to allow this is to set a bit to index tuples to indicate if a tuple is currently visible to all transactions when the first valid heap lookup happens. This bit would have to be cleared when a heap tuple is expired. That method doesn't require read-only tablespaces. - Heikki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpython and bytea
Am Montag, den 21.11.2005, 09:08 -0700 schrieb James William Pye:
> On Mon, 2005-11-21 at 15:18 +0200, Hannu Krosing wrote:
> > The project seems quite interesting, will surely take a deeper look
> >
...
>
> The 'layout' package needs to be installed first.
>
> See this quick start section:
> http://python.projects.postgresql.org/quick.html#Fetch+and+Install+the
> +Backend
> ('be' depends on 'lo' and 'ex')
There is:
cvs -d :pserver:anonymous:@cvs.pgfoundry.org co lo ex be
which should be:
cvs -d :pserver:anonymous:@cvs.pgfoundry.org:/cvsroot/python co lo ex be
to work.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > Ok, so how do I figure out which file(s) are associated with pg_class > so I can feed this thing? See contrib/oid2name and/or read http://www.postgresql.org/docs/8.1/static/storage.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Practical error logging for very large COPY statements
If you've ever loaded 100 million rows, you'll know just how annoying it is to find that you have a duplicate row somewhere in there. Experience shows that there is always one, whatever oath the analyst swears beforehand. It's hard to find out which row is the duplicate, plus you've just screwed up a big table. It needs a VACUUM, then a reload. I'd like to find a way to handle this manual task programmatically. What I'd like to do is add an ERRORTABLE clause to COPY. The main problem is how we detect a duplicate row violation, yet prevent it from aborting the transaction. What I propose is to log uniqueness violations only when there is only a single unique index on a table. Flow of control would be to: locate page of index where value should go lock index block _bt_check_unique, but don't error if violation then insert row into ERRORTABLE else insert row into data block insert row into unique index unlock index block do other indexes Which is very similar code to the recently proposed MERGE logic. With that logic, a COPY will run to completion, yet be able to report the odd couple of unique index violations in found along the way. More importantly we can then handle rows those with another program to locate where those errors came from and resolve them. In most cases with a single unique index, the index inserts are rightmost index entries anyway, so there is scope here for an additional optimisation: keep both index and data blocks locked across multiple row inserts until either the unique index or the data block fills. Thats better than taking a full table lock, since it allows concurrent access to the rest of the table, but its also more efficient than continually re-requesting the same blocks (which looks like about 10-15% saving on performance from hash lookups, lock/unlock, etc). Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Heading to Mexico
On Wed, Nov 16, 2005 at 06:29:42PM +, Simon Riggs wrote: > On Wed, 2005-11-16 at 13:09 -0500, Bruce Momjian wrote: > > I leaving for Mexico in a few hours to speak at a conference. I return > > on Monday. > > > > Is it helpful to tell hackers when I am not around? I was in NYC last > > week for four days and didn't publicize it. Of course, I didn't finish > > my backlog of email until yesterday. I guess the question is whether my > > not processing email for a few days is something I should announce here. > > Helpful, and it brings colour to the proceedings here... and reminds > everybody its a global project. Plus it gives anyone in that area a chance to see if they can meet up and buy you a beer/coke/name_your_poison. Of course maybe that's reason not to publicize this info... ;P -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Should libedit be preferred to libreadline?
It would certainly seem so on AIX.
In tracking down why postgres 8.x would segfault on AIX 5.3, it became
apparent to me that libreadline.a is a problematic library to link
against and that libedit.a is preferable (and for reasons other than
that readline is GPL while postgres is BSD-licensed).
With AIX 5, the easiest way to get a shared object is to pass "-bexpall"
to the linker. This results in all symbols being exported. The problem
with this is that the linker will export all of libreadline's and
libhistory's symbols. In the case of libreadline.so.4 (and .5) on AIX 5
this includes symbols like strncpy and memmove, but on .4, not memcpy.
This is likely because libc.a does not export them.
What results from this is that when postgres is linked against readline
on AIX, it gets these memory functions through readline instead of its
own code. When readline 4.3 is used (what IBM provides in their "AIX
Toolbox for Linux"), postgres is known to crash. These segfaults (if
postgres was compiled with gcc) have occurred on AIX 5.3ML3, AIX 5.3ML1,
and AIX 5.2ML7. With readline 5.0, postgres merely gets these functions
through the shared library memory segments instead of the user memory
segments[6].
While it is possible to build libreadline in a manner that doesn't
export strncpy, neither of the prebuilt readlines for AIX 5 that I
checked were both shared and did not export strncpy. IBM's readline[5]
exports strncpy, UCLA's readline[4] is static. Building a shared
readline that doesn't export strncpy requires creating export files for
libreadline and libhistory that only list the symbols that they are
supposed to export and editing the shared library Makefile to add the
exports flags to the appropriate linker calls.
Whatever strategy we might take, using readline on AIX requires
considerable trickery and hacking around with the build environments.
Simply put, it's ghastly.
On the other hand, the port of NetBSD's editline that I tried[1] works
without build-hackery to the library and has reasonable exports. The
only changes to postgres that I needed to make were confined to telling
the configure script to check for libedit before libreadline and adding
a test for histedit.h. The attached patch contains my modifications.
It is also possible to use a wrapper like rlwrap[2] instead of linking
postgres against libreadline or libedit.
[1] port of NetBSD's editline
http://www.thrysoee.dk/editline/
[2] rlwrap
http://utopia.knoware.nl/~hlub/uck/software/
[3] IBM Redbook "AIX 5L Porting Guide", section 9.2
http://www.redbooks.ibm.com/abstracts/sg246034.html?Open
http://www.redbooks.ibm.com/redbooks/pdfs/sg246034.pdf
[4] UCLA's readline package
http://aixpdslib.seas.ucla.edu/packages/readline.html
[5] IBM's readline package
http://www-03.ibm.com/servers/aix/products/aixos/linux/download.html
[6] IBM Redbook "Developing and Porting C and C++ Applications on AIX",
page 110
http://www.redbooks.ibm.com/abstracts/sg245674.html?Open
http://www.redbooks.ibm.com/redbooks/pdfs/sg245674.pdf
--
Seneca Cunningham
[EMAIL PROTECTED]
diff -wu postgresql-8.1.0.orig/configure postgresql-8.1.0/configure
--- postgresql-8.1.0.orig/configure 2005-11-04 23:01:38.0 -0500
+++ postgresql-8.1.0/configure 2005-11-21 12:47:28.0 -0500
@@ -998,7 +998,7 @@
else
echo "$as_me: WARNING: no configuration information is in $ac_dir" >&2
fi
-cd $ac_popdir
+cd "$ac_popdir"
done
fi
@@ -6498,7 +6498,7 @@
else
pgac_cv_check_readline=no
pgac_save_LIBS=$LIBS
-for pgac_rllib in -lreadline -ledit ; do
+for pgac_rllib in -ledit -lreadline ; do
for pgac_lib in "" " -ltermcap" " -lncurses" " -lcurses" ; do
LIBS="${pgac_rllib}${pgac_lib} $pgac_save_LIBS"
cat >conftest.$ac_ext <<_ACEOF
@@ -9646,6 +9646,152 @@
else
+for ac_header in histedit.h
+do
+as_ac_Header=`echo "ac_cv_header_$ac_header" | $as_tr_sh`
+if eval "test \"\${$as_ac_Header+set}\" = set"; then
+ echo "$as_me:$LINENO: checking for $ac_header" >&5
+echo $ECHO_N "checking for $ac_header... $ECHO_C" >&6
+if eval "test \"\${$as_ac_Header+set}\" = set"; then
+ echo $ECHO_N "(cached) $ECHO_C" >&6
+fi
+echo "$as_me:$LINENO: result: `eval echo '${'$as_ac_Header'}'`" >&5
+echo "${ECHO_T}`eval echo '${'$as_ac_Header'}'`" >&6
+else
+ # Is the header compilable?
+echo "$as_me:$LINENO: checking $ac_header usability" >&5
+echo $ECHO_N "checking $ac_header usability... $ECHO_C" >&6
+cat >conftest.$ac_ext <<_ACEOF
+/* confdefs.h. */
+_ACEOF
+cat confdefs.h >>conftest.$ac_ext
+cat >>conftest.$ac_ext <<_ACEOF
+/* end confdefs.h. */
+$ac_includes_default
+#include <$ac_header>
+_ACEOF
+rm -f conftest.$ac_objext
+if { (eval echo "$as_me:$LINENO: \"$ac_compile\"") >&5
+ (eval $ac_compile) 2>conftest.er1
+ ac_status=$?
+ grep -v '^ *+' conftest.er1 >conftest.err
+ rm -f conftest.er1
+ cat conftest.err >&5
+ echo "$as_me:$LINENO: \$? = $ac_status" >&5
+ (exit $ac_status); } &&
+ { ac_try='test -z
Re: [HACKERS] [COMMITTERS] pgsql: make_restrictinfo() failed to attach
Jim C. Nasby wrote: isn't a test for a specific case better than none at all? Is the concern how long make check takes? It shouldn't be, since we can (and do) have multiple regression test sets. If there are tests that take too long for normal use, let's make a "takes a long time" set and a new Makefile target for it - we'll add it to the buildfarm suite so automated testing (which shouldn't care how long it takes) will do the heavy work for us. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 1:59 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: The attributes look like the names of all the columns in the table, and reindexing didn't help. So at this point it seems that the pg_class row disappeared, but there probably wasn't any actual DROP operation --- you'd think at least some of those other entries would have been deleted by a DROP. My next guess is that the pg_class row simply got clobbered somehow, eg its xmin field got set to something ridiculous. The only way I can think of to investigate that is to dump out the contents of pg_class with pg_filedump --- are you game for that? If so, get the right version of pg_filedump from http://sources.redhat.com/rhdb/utilities.html and run it with the -i -f options (usually produces the most helpful output, in my experience). This is 8.1.0, can I use pg_dump 4.0 with that? The entire database is 39GB, there's a way to just get pg_class, right? -bob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: make_restrictinfo() failed to attach the specified
On Wed, Nov 16, 2005 at 11:05:11PM -0300, Alvaro Herrera wrote: > Christopher Kings-Lynne wrote: > > >I've never been a fan of "regression tests" in the narrow sense of > > >"let's test for this specific mistake we made once". If you can devise > > >a test that catches a class of errors including the one you actually > > >made, that's a different story, because it's much more likely to catch a > > >real future problem. > > > > Heh. See what I do is envision a future 10 years from now when the guy > > who truly understands the planner and executor (Tom) has long gone and > > the rest of us poor buggers keep on trying to change and fix things, > > thereby recreating all these 10 year old bugs :) > > That's why someone else should be studying the planner and executor code > right now ... I've long wanted to start doing it but I've been always > distracted with other minutia ... Sure, but people make mistakes. Incredibly, I think you can even find evidence of Tom making mistakes if you dig deep enough into commit logs and list archives! ;) I certainly agree that a test that will catch multiple errors is better than one that catches few (or only one), but isn't a test for a specific case better than none at all? Is the concern how long make check takes? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > The attributes look like the names of all the columns in the table, > and reindexing didn't help. So at this point it seems that the pg_class row disappeared, but there probably wasn't any actual DROP operation --- you'd think at least some of those other entries would have been deleted by a DROP. My next guess is that the pg_class row simply got clobbered somehow, eg its xmin field got set to something ridiculous. The only way I can think of to investigate that is to dump out the contents of pg_class with pg_filedump --- are you game for that? If so, get the right version of pg_filedump from http://sources.redhat.com/rhdb/utilities.html and run it with the -i -f options (usually produces the most helpful output, in my experience). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 1:43 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: I don't know how to get the oid of a type.. but there are certainly entries in pg_depend with the other criteria: Hmph, looks like you still have a pretty full set of dependencies for the table. What about attributes --- try select attname from pg_attribute where attrelid = 211174567; It might be interesting to try reindexing pg_class, too ... maybe the pg_class row is still there but isn't being found because of a corrupt index? The attributes look like the names of all the columns in the table, and reindexing didn't help. mochi=# select attname from pg_attribute where attrelid = 211174567; attname tableoid cmax xmax cmin xmin oid ctid id timestamp ip_address mochiTag mochiGUID mochiVersion movieURL movieURLHost movieReferrer movieReferrerHost movieUserAgent movieSWFVersion movieBytesLoaded movieQuality movieStageHeight movieStageWidth movieBytesTotal movieStageAlign movieScaleMode movieShowMenu userScreenReader userCameras userMicrophones userSystemCapabilities userTimeZoneOffset userTicks userUTCTime (34 rows) mochi=# reindex table pg_class; REINDEX mochi=# create table ping_1132387200(); ERROR: type "ping_1132387200" already exists mochi=# drop table ping_1132387200; ERROR: table "ping_1132387200" does not exist mochi=# drop type ping_1132387200; ERROR: cache lookup failed for relation 211174567 -bob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Data directory on read-only media
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > There's also this TODO: > Allow a warm standby system to also allow read-only queries [pitr] > In fact, I was originally thinking of that. I should've mentioned it. > It has the same issues with transactions and WAL as running from > read-only media, so I decided to start with the seemingly easier case. No, it hasn't got anywhere near the same issues, primarily because you don't have to have a 100% rock solid guarantee of no disk writes. Thus for example there's no problem with updating hint bits, nor with preventing use of temp files. I concur with Peter's opinion that a PG demo that is hard-core read-only is not going to be very useful. It certainly won't have a lot to do with either the behavior or performance of the system in live use, which would render it not only not very interesting but close to being false advertising. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Are NULLs in Arrays compressed?
On Mon, Nov 21, 2005 at 08:51:32PM +, Simon Riggs wrote: > Are NULLs in Arrays compressed? > Just as NULLs are with normal unary datatypes. I thought NULLs don't work in arrays yet? :-) mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote: > I don't think MERGE can really be made to be both though, in which case > it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON > DUPLICATE UPDATE something else. Perhaps a special form of MERGE where > you know it's going to be doing that locking. I really don't like the > idea of making the SQL2003 version of MERGE be the MERGE special case > (by requiring someone to take a table lock ahead of time or do something > else odd). Anyone know off-hand what the big 3 do? If the industry consensus is that merge should actually be REPLACE/INSERT ON DUPLICATE UPDATE then it's probably better to follow that lead. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
On Thu, Nov 17, 2005 at 09:30:43PM -0500, Bruce Momjian wrote: > > Is the requirement for predicate locking, over and above a unique > > constraint on an index that involves the record key, to deal with > > the scenario of two inserts executing at the same time, both before > > commit? > > No. If you have a primary key you can easily prevent duplicates. You > need a table lock or predicate locking to prevent duplicates if you do > not have a primary key. AFAIK you can also accomplish this without a table lock as long as you have a unique index on the right set of fields and those fields are also NOT NULL. ISTM it would be good to support that case as well, since you might want to MERGE based on something other than the PK. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] why is gist index taking so much space on the disc
On Mon, Nov 21, 2005 at 04:58:25PM +0100, Grzegorz Jaskiewicz wrote: > my conquers with Gist index for custom type are nearly finished. It > is working as it is now, but there are few problems here and there. > One of em, being amount of disc space index it self takes. The type > stucture it self takes 160bytes. Adding 100.000 rows into table - > CREATE TABLE blah (a serial, b customType); Let's see, 160bytes means you'll get aboud 50 keys per page. So you would expect 2000 leaf page, 40 level 1 pages. This should be less than 20-30MB > Is it normal that index is so hudge ? Even tho my type has built in > masks (element that can match few different values), and %. up front > the string (which behaves just like the sql % in b ~ '%.something'). > And both are used to build "unions" for pick-split, and other > operations. Is it because of pick-split it self ? It does good work > in splitting up table of elements into two separate ones, by sorting > them first, than creating common "mask" for L and P. And by scanning > whole table again, and putting elements matching into L or P. L and P > elements sometimes overlap, but so far I can't find better solution. You mean you sometimes put the same elements in the two halves? You shouldn't do that. The whole point is that the search will descend any node that matches consistant, but any single key should only appear once in each index. picksplit should *split* the set, not return two sets about the same size as you started... Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgptp7gozSTRC.pgp Description: PGP signature
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > I don't know how to get the oid of a type.. but there are certainly > entries in pg_depend with the other criteria: Hmph, looks like you still have a pretty full set of dependencies for the table. What about attributes --- try select attname from pg_attribute where attrelid = 211174567; It might be interesting to try reindexing pg_class, too ... maybe the pg_class row is still there but isn't being found because of a corrupt index? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Data directory on read-only media
Heikki Linnakangas wrote: > > I think that a read-only installation of PostgreSQL would be a very > > poor demonstration of its capabilities. Better put the data in a > > RAM disk. > > RAM space is limited. Nowadays, CD space is often more limited than RAM. You could of course now talk about a DVD instead. I wonder what kind of performance you'd get out of that anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 1:14 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: On Nov 21, 2005, at 12:44 PM, Tom Lane wrote: Try dropping the type. I did try that, I guess it didn't make it to the list yet: mochi=# drop type ping_1132387200; ERROR: cache lookup failed for relation 211174567 Hmm, apparently there are still entries in pg_depend for these objects. Would you look to see what is in pg_depend with either objid or refobjid equal to either 211174567 or the type's oid (possibly 211174568, but check)? I don't know how to get the oid of a type.. but there are certainly entries in pg_depend with the other criteria: mochi=# select * from pg_depend where objid=211174567; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -+---+--++--- +-+- 1259 | 211174567 |0 | 2615 | 2200 | 0 | n 1259 | 211174567 |0 | 1259 | 103327322 | 0 | n (2 rows) mochi=# select * from pg_depend where refobjid=211174567; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -+---+--++--- +-+- 2606 | 212119648 |0 | 1259 | 211174567 | 1 | a 2606 | 212119648 |0 | 1259 | 211174567 | 1 | n 2606 | 212119649 |0 | 1259 | 211174567 | 2 | a 2606 | 212119649 |0 | 1259 | 211174567 | 2 | n 1247 | 211174568 |0 | 1259 | 211174567 | 0 | i 2604 | 211174569 |0 | 1259 | 211174567 | 1 | a 1259 | 211174570 |0 | 1259 | 211174567 | 0 | i 2606 | 211174574 |0 | 1259 | 211174567 | 1 | a 1259 | 211174575 |0 | 1259 | 211174567 | 2 | a 1259 | 211174576 |0 | 1259 | 211174567 | 4 | a 1259 | 211174576 |0 | 1259 | 211174567 | 2 | a 1259 | 211174577 |0 | 1259 | 211174567 | 4 | a 1259 | 211174577 |0 | 1259 | 211174567 | 7 | a 1259 | 211174577 |0 | 1259 | 211174567 | 2 | a 1259 | 211174578 |0 | 1259 | 211174567 | 4 | a 1259 | 211174578 |0 | 1259 | 211174567 | 8 | a 1259 | 211174578 |0 | 1259 | 211174567 | 2 | a (17 rows) -bob ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Data directory on read-only media
On Sun, 20 Nov 2005, Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: 5. Don't try to write buffers with commit hint modifications. Just discard them. The performance costs of that alone are astonishing (ie, repeated verifications of commit status). You'd vacuum first to avoid that, of course. I think what you are doing is a completely wrongheaded way to approach it... it sounds incredibly invasive, messy, and fragile. A database or tablespace that has been frozen (per VACUUM FREEZE) could sensibly be put on read-only media, but I can't see doing the rest of this. Have you thought about putting the more dynamic stuff onto a RAM disk? Invasive: Most of the changes are small modifications to startup and shutdown routines. I wouldn't call it too invasive. Probably less invasive than enforcing that a tablespace is read-only, which would be a nice feature of it's own. Messy: Well, I guess it's in the eye of the beholder. It depends a lot on the implementation details. Fragile: It's certainly something you have to keep in mind whenever you need to write something to disk. There's not that many places that do that, I enumerated them in the original mail. Using a RAM disk is harder for the user. You need to set up the RAM disk, figure out what to copy to RAM disk and what not, and then be careful not to change anything that's on the frozen tablespace. What would a script look like that setups a RAM disk and runs postgres from that? It's probably doable, but hard to do in a portable way. There's also this TODO: Allow a warm standby system to also allow read-only queries [pitr] In fact, I was originally thinking of that. I should've mentioned it. It has the same issues with transactions and WAL as running from read-only media, so I decided to start with the seemingly easier case. - Heikki ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > On Nov 21, 2005, at 12:44 PM, Tom Lane wrote: >> Try dropping the type. > I did try that, I guess it didn't make it to the list yet: > mochi=# drop type ping_1132387200; > ERROR: cache lookup failed for relation 211174567 Hmm, apparently there are still entries in pg_depend for these objects. Would you look to see what is in pg_depend with either objid or refobjid equal to either 211174567 or the type's oid (possibly 211174568, but check)? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Data directory on read-only media
On Mon, 21 Nov 2005, Peter Eisentraut wrote: Heikki Linnakangas wrote: I've been thinking about running postgres from read-only media. It's handy for creating demo CDs etc. I think that a read-only installation of PostgreSQL would be a very poor demonstration of its capabilities. Better put the data in a RAM disk. RAM space is limited. I was thinking more of a demonstration CD of some other software that uses PostgreSQL as kind of an embedded database. Of course, there's other databases more suited for embedding. - Heikki ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] ERROR: could not read block
"Tom Lane" <[EMAIL PROTECTED]> wrote > > Would a simple retry loop actually help? It's not clear to me how > persistent such a failure would be. > [with reply to all followup threads] Yeah, this is the key and we definitely have no 100% guarantee that several retries will solve the problem - just as the situation in pg_unlink/pg_rename. But shall we do something now? If Kevin could help on testing(you may have to revert the registry changes :-() , I would like to send a patch in the retry style. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Are NULLs in Arrays compressed?
Are NULLs in Arrays compressed? Just as NULLs are with normal unary datatypes. Thanks, Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > mochi=# drop table ping_1132387200; > ERROR: table "ping_1132387200" does not exist > mochi=# create table ping_1132387200(); > ERROR: type "ping_1132387200" already exists > I'm not sure what to do about this.. Try dropping the type. We've seen at least one prior report of a table's rowtype not being dropped with the table, but nothing solid enough to debug. If you can find a way to reproduce this, I'm all ears. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 11:31 AM, Bob Ippolito wrote: I've been running 8.1.0 on a test box since the FreeBSD port has been available, and it appears that the catalog has become corrupted. There's plenty of free space on all volumes, so it hasn't run out of space or anything. $ uname -a FreeBSD shi.mochibot.com 6.0-RELEASE FreeBSD 6.0-RELEASE #2: Mon Nov 7 14:34:52 PST 2005 [EMAIL PROTECTED]:/usr/obj/usr/src/ sys/CUSTOM i386 $ psql mochi Welcome to psql 8.1.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit mochi=# drop table ping_1132387200; ERROR: table "ping_1132387200" does not exist mochi=# create table ping_1132387200(); ERROR: type "ping_1132387200" already exists I'm not sure what to do about this.. Oh, and trying to drop the type doesn't work either: mochi=# drop type ping_1132387200; ERROR: cache lookup failed for relation 211174567 -bob ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito wrote: > I've been running 8.1.0 on a test box since the FreeBSD port has been > available, and it appears that the catalog has become corrupted. > There's plenty of free space on all volumes, so it hasn't run out of > space or anything. > > $ uname -a > FreeBSD shi.mochibot.com 6.0-RELEASE FreeBSD 6.0-RELEASE #2: Mon Nov > 7 14:34:52 PST 2005 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/ > CUSTOM i386 > > $ psql mochi > Welcome to psql 8.1.0, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help with psql commands >\g or terminate with semicolon to execute query >\q to quit > > mochi=# drop table ping_1132387200; > ERROR: table "ping_1132387200" does not exist > mochi=# create table ping_1132387200(); > ERROR: type "ping_1132387200" already exists Well, try DROP TYPE ping_1132387200; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
On Nov 21, 2005, at 12:44 PM, Tom Lane wrote: Bob Ippolito <[EMAIL PROTECTED]> writes: mochi=# drop table ping_1132387200; ERROR: table "ping_1132387200" does not exist mochi=# create table ping_1132387200(); ERROR: type "ping_1132387200" already exists I'm not sure what to do about this.. Try dropping the type. We've seen at least one prior report of a table's rowtype not being dropped with the table, but nothing solid enough to debug. If you can find a way to reproduce this, I'm all ears. I did try that, I guess it didn't make it to the list yet: mochi=# drop type ping_1132387200; ERROR: cache lookup failed for relation 211174567 -bob ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] PostgreSQL 8.1.0 catalog corruption
I've been running 8.1.0 on a test box since the FreeBSD port has been available, and it appears that the catalog has become corrupted. There's plenty of free space on all volumes, so it hasn't run out of space or anything. $ uname -a FreeBSD shi.mochibot.com 6.0-RELEASE FreeBSD 6.0-RELEASE #2: Mon Nov 7 14:34:52 PST 2005 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/ CUSTOM i386 $ psql mochi Welcome to psql 8.1.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit mochi=# drop table ping_1132387200; ERROR: table "ping_1132387200" does not exist mochi=# create table ping_1132387200(); ERROR: type "ping_1132387200" already exists I'm not sure what to do about this.. -bob ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bind variables, soft vs hard parse
Jim C. Nasby wrote: It might be more useful to look at caching only planning and not parsing. I'm not familiar with the output of the parsing stage, but perhaps that could be hashed to use as a lookup into a cache of planned queries. I suspect that would remove issues of different search_paths. A really stupid question, in the cached query-string, wouldn't it be possible to add the env specifics? Ie the string to check against is something like search_paths=...\n SELECT ... Or would there be too much stuff to append/prepend? Best regards, Marcus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] why is gist index taking so much space on the disc
So final question, what should I do to make that index much smaller on the disc. Tune your penalty and picksplit function. Gevel module can help you to look inside of index ( http://www.sai.msu.su/~megera/postgres/gist/gevel ). Usially, index becomes big when picksplit works bad: during split it place one key on one page and all other keys on another page. So you have a huge number of page with single value. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Time for pgindent?
Tom Lane wrote: > I see Alvaro and Andrew have landed the patches they were working on > last week, so maybe today is a good time to do that re-pgindent we > were discussing. I return home at 9pm EST. If that is a good time I will run it tonight. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] why is gist index taking so much space on the disc
Take the query. select a,b from dupa where b::text in (select b::text from dupa group by b::text having count(b) > 2); This is acceptable to create a unique constraint, however, we cannot mark the column unique, without defining btree operators, which clearly are not possible for sorting. Is there any way to base the operators based on the text representation of the type for strict equality (not to be confused with same or equivilent) and thus use that not as an ordering method, but as a simple equality for uniqueness. Kevin McArthur - Original Message - From: "Grzegorz Jaskiewicz" <[EMAIL PROTECTED]> To: Sent: Monday, November 21, 2005 7:58 AM Subject: [HACKERS] why is gist index taking so much space on the disc Hi folks my conquers with Gist index for custom type are nearly finished. It is working as it is now, but there are few problems here and there. One of em, being amount of disc space index it self takes. The type stucture it self takes 160bytes. Adding 100.000 rows into table - CREATE TABLE blah (a serial, b customType); with my gist index takes around 2GB on disc ! 100.000 is a large number, but the purpose of having gist in first place is defeated if that machine can't handle fast I/O or has at least 3GB of ram, first to hold index in cache, secondly to operate postgres caching (shared memory). Is it normal that index is so hudge ? Even tho my type has built in masks (element that can match few different values), and %. up front the string (which behaves just like the sql % in b ~ '%.something'). And both are used to build "unions" for pick-split, and other operations. Is it because of pick-split it self ? It does good work in splitting up table of elements into two separate ones, by sorting them first, than creating common "mask" for L and P. And by scanning whole table again, and putting elements matching into L or P. L and P elements sometimes overlap, but so far I can't find better solution. Having to iterate 10 or 20 times using k-means (the type holds tree a like structure) isn't going to boost efficiency either. This index works, and it is very fast, but still large. So final question, what should I do to make that index much smaller on the disc. -- GJ "If we knew what we were doing, it wouldn't be called Research, would it?" - AE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers] Daily digest v1.5568 (24 messages)
I wonder if this idea might be taken a little further, to allow read-only tablespaces? This would allow old partitions in very large databases to be kept on read-only media, and would allow normal backups to ignore this unchanging set of data. It also allows for certain specific optimisations for this type of data, as the MVCC rules are now relaxed. Inclusion of a row in a read-only index is now enough to guarantee the visibility of that row to all backends, and fetches based solely on the index now become possible. FWIW, Oracle does something like this. Just a thought. __ Marc On Mon, 2005-11-21 at 09:53 -0400, [EMAIL PROTECTED] wrote: > Date: Sun, 20 Nov 2005 22:39:49 +0200 (EET) > From: Heikki Linnakangas <[EMAIL PROTECTED]> > To: pgsql-hackers@postgresql.org > Subject: Data directory on read-only media > Message-ID: <[EMAIL PROTECTED]> > > Hi, > > I've been thinking about running postgres from read-only media. It's > handy for creating demo CDs etc. I hacked together a patch that > allows > you to run Postgres without write permissions to the data directory. > signature.asc Description: This is a digitally signed message part
Re: [HACKERS] why is gist index taking so much space on the disc
On 2005-11-21, at 19:32, Martijn van Oosterhout wrote:
On Mon, Nov 21, 2005 at 04:58:25PM +0100, Grzegorz Jaskiewicz wrote:
my conquers with Gist index for custom type are nearly finished. It
is working as it is now, but there are few problems here and there.
One of em, being amount of disc space index it self takes. The type
stucture it self takes 160bytes. Adding 100.000 rows into table -
CREATE TABLE blah (a serial, b customType);
Let's see, 160bytes means you'll get aboud 50 keys per page. So you
would expect 2000 leaf page, 40 level 1 pages. This should be less
than
20-30MB
yep;
You mean you sometimes put the same elements in the two halves? You
shouldn't do that. The whole point is that the search will descend any
node that matches consistant, but any single key should only appear
once in each index.
picksplit should *split* the set, not return two sets about the same
size as you started...
Nope, I mean that 'masks' created to match either 'half' sometimes
match elements in the other one.
This shouldn't be a big deal, just one level to go down on query to
much more specific result set.
I have fixed that with, somewhat hack.
Here's some pseudo code
sort_data(input);
find_split_point(input);
mask1 = generate_two_masks(input[0]);
mask2 = generate_two_masks(input[1]);
foreach(input) {
bool a = matches1(input);
bool b = matches2(input);
if ( a && b ) {
if ( left_index == 0 ) {
left[left_index++] = input;
}
else {
if ( right_index == 0 ) {
right[right_index++] = input;
continue;
}
/* this part is new code, and helped a lot, now gist index takes much
less space
and is much faster, because of lower I/O consumption*/
if ( loop_index % 2 ) {
right[right_index++] = input;
}
else {
left[left_index++] = input;
}
}
}
else {
if ( a) left[left_index++] = input;
if ( b) right[right_index++] = input;
}
}
mask1 = generate(left );
mask2 = generate(right);
return (left, right, blah, blih, others);
Ok, so the part with i%2 helped a lot, it distributes elements
matching both masks evenly.
Thanks guys.
I will play with k-means, and see if they will work better with no
hacks. Either way, I have to have some code that will handle "matches
both" case.
Thanks again.
--
GJ
"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] plpython and bytea
On Mon, 2005-11-21 at 15:18 +0200, Hannu Krosing wrote:
> The project seems quite interesting, will surely take a deeper look
>
> It looks quite alpha, seems to mess to deeply with backend, and the cvs
> checkout of module be does not build, so I will probably not be able to
> use it in production for quite a while :(
>
> [EMAIL PROTECTED] be]$ python setup.py build
> running build
> running config
> Traceback (most recent call last):
> File "setup.py", line 256, in ?
> setup(defaults)
> File "setup.py", line 249, in setup
> d = distutils.core.setup(**kw)
> File "/usr/lib/python2.4/distutils/core.py", line 149, in setup
> dist.run_commands()
> File "/usr/lib/python2.4/distutils/dist.py", line 946, in run_commands
> self.run_command(cmd)
> File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
> cmd_obj.run()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 193, in
> run
> self.run_command('config')
> File "/usr/lib/python2.4/distutils/cmd.py", line 333, in run_command
> self.distribution.run_command(command)
> File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
> cmd_obj.run()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 166, in
> run
> self.distribution.config = cfg = self.load()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 129, in
> load
> cfgsrc(self)
> File "setup.py", line 19, in configure
> import postgresql.utility.config
> ImportError: No module named postgresql.utility.config
The 'layout' package needs to be installed first.
See this quick start section:
http://python.projects.postgresql.org/quick.html#Fetch+and+Install+the
+Backend
('be' depends on 'lo' and 'ex')
--
Regards, James William Pye
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] why is gist index taking so much space on the disc
Hi folks my conquers with Gist index for custom type are nearly finished. It is working as it is now, but there are few problems here and there. One of em, being amount of disc space index it self takes. The type stucture it self takes 160bytes. Adding 100.000 rows into table - CREATE TABLE blah (a serial, b customType); with my gist index takes around 2GB on disc ! 100.000 is a large number, but the purpose of having gist in first place is defeated if that machine can't handle fast I/O or has at least 3GB of ram, first to hold index in cache, secondly to operate postgres caching (shared memory). Is it normal that index is so hudge ? Even tho my type has built in masks (element that can match few different values), and %. up front the string (which behaves just like the sql % in b ~ '%.something'). And both are used to build "unions" for pick-split, and other operations. Is it because of pick-split it self ? It does good work in splitting up table of elements into two separate ones, by sorting them first, than creating common "mask" for L and P. And by scanning whole table again, and putting elements matching into L or P. L and P elements sometimes overlap, but so far I can't find better solution. Having to iterate 10 or 20 times using k-means (the type holds tree a like structure) isn't going to boost efficiency either. This index works, and it is very fast, but still large. So final question, what should I do to make that index much smaller on the disc. -- GJ "If we knew what we were doing, it wouldn't be called Research, would it?" - AE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Time for pgindent?
I see Alvaro and Andrew have landed the patches they were working on last week, so maybe today is a good time to do that re-pgindent we were discussing. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] drop database if exists
here's a patch for "drop database if exists". Barring objections I will apply it in a day or two. Should we use the IF EXISTS syntax in pg_dump output? For all DROP commands in clean mode? Might make it easier to wrap pg_dump output in a transaction? Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Materialized views (Was Re: [HACKERS] Improving count(*))
On 11/20/05, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > On Sat, 19 Nov 2005, Nicolas Barbier wrote: > > > You might want to take a look at the pages that I set up to track the > > progress on my master's thesis: > > > > http://www.nicolas.barbier.easynet.be/itsme/thesis/> > > > > especially the literature page: > > > > http://www.nicolas.barbier.easynet.be/itsme/thesis/literature/> > > > > IMO, GL95, Qua97 and GM99 are the ones that are most applicable to > > view maintenance with bag-semantics (thus, SQL). You should be able to > > find all these papers with Google (Scholar) in case my computer is > > shut down, otherwise you can download them directly from me. > > Thanks, interesting stuff. > > BTW: Does the GL95 algorithm handle outer joins? No, but GM99 does (although only in the cases where it can be applied). I guess that a slightly adapted version of the technique from Qua97 can also be used. Investigating :-). greetings, Nicolas -- Nicolas Barbier http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Tablespace on ramdisk
[EMAIL PROTECTED] schrieb: I'd like to mimic MySQL's in-memory tables (engine=memory), which structure survives a server restart (data lost of course). I suspected that a server restart would be a problem in this case. Thank you anyway. you could use temp tables... but usually it isnt worth the trouble. Adjust your cache mem and stuff and often used data will be in memory automatically. HTH Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpython and bytea
Hannu Krosing schrieb: On Mon, 2005-11-21 at 08:37 +0100, Tino Wildenhain wrote: Am Montag, den 21.11.2005, 02:11 +0200 schrieb Hannu Krosing: Hi It seems that plpython is unable to return bytea string when it contains NUL bytes: ... Did you also try: http://python.projects.postgresql.org/project/be.html ? Afaic it works a little different. The project seems quite interesting, will surely take a deeper look It looks quite alpha, seems to mess to deeply with backend, and the cvs checkout of module be does not build, so I will probably not be able to use it in production for quite a while :( Well I had it running and it was very impressive. However it seems no easy install yet - tried a cvs head with similar problems. I hope the author reads this here. At least I met him on freenode #postgresql ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tablespace on ramdisk
I'd like to mimic MySQL's in-memory tables (engine=memory), which structure survives a server restart (data lost of course). I suspected that a server restart would be a problem in this case. Thank you anyway. Paolo Tino Wildenhain <[EMAIL PROTECTED]> ha scritto > [EMAIL PROTECTED] schrieb: > > Hi, > > does anyone have experiences about putting a tablespace on ramdisk? Does it > > work (and keep working after a restart of the server)? > > Thanks in advance for any insight. > > > Yes it does work as long as you dont restart your server. > Postgres does not appreciate disappearing cluster data. > > What are you trying to solve btw? > > ++Tino > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpython and bytea
On Mon, 2005-11-21 at 08:37 +0100, Tino Wildenhain wrote:
> Am Montag, den 21.11.2005, 02:11 +0200 schrieb Hannu Krosing:
> > Hi
> >
> > It seems that plpython is unable to return bytea string when it contains
> > NUL bytes:
> >
...
> Did you also try:
>
> http://python.projects.postgresql.org/project/be.html
>
> ? Afaic it works a little different.
The project seems quite interesting, will surely take a deeper look
It looks quite alpha, seems to mess to deeply with backend, and the cvs
checkout of module be does not build, so I will probably not be able to
use it in production for quite a while :(
[EMAIL PROTECTED] be]$ python setup.py build
running build
running config
Traceback (most recent call last):
File "setup.py", line 256, in ?
setup(defaults)
File "setup.py", line 249, in setup
d = distutils.core.setup(**kw)
File "/usr/lib/python2.4/distutils/core.py", line 149, in setup
dist.run_commands()
File "/usr/lib/python2.4/distutils/dist.py", line 946, in run_commands
self.run_command(cmd)
File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
cmd_obj.run()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 193, in
run
self.run_command('config')
File "/usr/lib/python2.4/distutils/cmd.py", line 333, in run_command
self.distribution.run_command(command)
File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
cmd_obj.run()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 166, in
run
self.distribution.config = cfg = self.load()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 129, in
load
cfgsrc(self)
File "setup.py", line 19, in configure
import postgresql.utility.config
ImportError: No module named postgresql.utility.config
-
Hannu Krosing
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tablespace on ramdisk
[EMAIL PROTECTED] schrieb: Hi, does anyone have experiences about putting a tablespace on ramdisk? Does it work (and keep working after a restart of the server)? Thanks in advance for any insight. Yes it does work as long as you dont restart your server. Postgres does not appreciate disappearing cluster data. What are you trying to solve btw? ++Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Tablespace on ramdisk
Hi, does anyone have experiences about putting a tablespace on ramdisk? Does it work (and keep working after a restart of the server)? Thanks in advance for any insight. Paolo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Data directory on read-only media
Heikki Linnakangas wrote: > I've been thinking about running postgres from read-only media. It's > handy for creating demo CDs etc. I think that a read-only installation of PostgreSQL would be a very poor demonstration of its capabilities. Better put the data in a RAM disk. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Data directory on read-only media
Josh Berkus wrote: > Well, it eliminates transaction isolation. Completely. If the data is read-only, you presumably don't need that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
